Oracle Study Note

ALTER DATABASE

+mB&qv+qq wH0Open an existing database, and /or modify associated files.51TestingÈí¼þ²âÊÔÍø$L#j^L2u
51TestingÈí¼þ²âÊÔÍø'J?9MX ?O+z7K&hS
Syntax:51TestingÈí¼þ²âÊÔÍø/j"jRU4lK M

   ALTER DATABASE database_name options

Options: 51TestingÈí¼þ²âÊÔÍøF R3J }jh

open / mount options:
   MOUNT
   MOUNT STANDBY DATABASE
   MOUNT CLONE DATABASE
   MOUNT PARALLEL
   MOUNT STANDBY DATABASE
   CONVERT
   OPEN [READ ONLY]
   OPEN [READ WRITE] RESETLOGS|NORESETLOGS [MIGRATE]
   ACTIVATE STANDBY DATABASE
   [NATIONAL] CHARACTER SET char_set

archivelog options:
   ARCHIVELOG
   NOARCHIVELOG

backup and recovery options:
   BACKUP CONTROLFILE TO 'filename' [REUSE]
   BACKUP CONTROLFILE TO TRACE [RESETLOGS] [AS 'filename' [REUSE]]
   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]
   RECOVER recover_clause
   RECOVER MANAGED STANDBY standby_recover_clause
   END BACKUP

Datafile options:
   CREATE DATAFILE 'filename' AS filespec
   DATAFILE 'filename' ONLINE
   DATAFILE 'filename' OFFLINE [DROP]
   DATAFILE 'filename' RESIZE int K | M
   DATAFILE 'filename' AUTOEXTEND OFF
   DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED]
   DATAFILE 'filename' END BACKUP
   RENAME FILE 'data_file_name' TO 'data_file_name'

   TEMPFILE 'filename' ONLINE
   TEMPFILE 'filename' OFFLINE
   TEMPFILE 'filename' DROP [INCLUDING DATAFILES]
   TEMPFILE 'filename' RESIZE int K | M
   TEMPFILE 'filename' AUTOEXTEND OFF
   TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED]

redo log options:
   ADD LOGFILE [THREAD int] [GROUP int] filespec
   ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int
   ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename'
   DROP LOGFILE GROUP int
   DROP LOGFILE ('filename')
   DROP LOGFILE MEMBER 'filename'
   RENAME FILE 'redolog_file_name' TO 'redolog_file_name'
   CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE]
   CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE]

Parallel server options:
   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]
   SET DBLOW = 'text'
   SET DBHIGH = 'text'
   SET DBMAC = ON | OFF
   ENABLE [PUBLIC] THREAD int
   DISABLE THREAD int

Backwards compatibility options:
   RENAME GLOBAL_NAME TO database [domain]
   RESET COMPATIBILITY

Apart from RENAME, any option above that includes a 'filename' can be extended to cover multiple files using the syntax: ('filename1', 'filename2')

database_name is defined when the database is created - it is normally set to the same as the database SID.51TestingÈí¼þ²âÊÔÍø9vW*V1}%y)hIjS^5Q{s
51TestingÈí¼þ²âÊÔÍøbxq;zD4F6W
Some of the commands above can only be used when the database is in a particular state:51TestingÈí¼þ²âÊÔÍø.p-Qv b-g2{ e

|,x:n|;af/D5R0U0MOUNT, CONVERT - Require that the db is Not Mounted.51TestingÈí¼þ²âÊÔÍøR)m-AOK p'q'x
51TestingÈí¼þ²âÊÔÍø},v"B,g#{
ARCHIVELOG, NOARCHIVLOG, RECOVER - Require that the db is Mounted but not open (must be mount exclusive - not mount parallel).51TestingÈí¼þ²âÊÔÍø|9sM\;K~\E

%p ~{/z3_,F9my0ENABLE, DISABLE, RENAME GLOBAL_NAME, RESET, SET - Require that the db is Open.51TestingÈí¼þ²âÊÔÍø
g0["QGM#iG+T}9E
51TestingÈí¼þ²âÊÔÍø{P8L)h+I%CpCc-T

51TestingÈí¼þ²âÊÔÍøgRu4wuS2`;f

!N*qnXkX&y%av
sC0ALTER SYSTEM51TestingÈí¼þ²âÊÔÍø'`{'u7i#R

3g/L5E
z8?a!N0Modify system settings.51TestingÈí¼þ²âÊÔÍø0]HWy1^.q
51TestingÈí¼þ²âÊÔÍød'vwS'i*E0@
Syntax:51TestingÈí¼þ²âÊÔÍøE]&R_"kdv6E

   ALTER SYSTEM ARCHIVE LOG archive_log_clause
   ALTER SYSTEM CHECKPOINT [GLOBAL | LOCAL]
   ALTER SYSTEM CHECK DATAFILES [GLOBAL | LOCAL]
   ALTER SYSTEM FLUSH SHARED POOL
   ALTER SYSTEM {ENABLE | DISABLE} DISTRIBUTED RECOVERY
   ALTER SYSTEM {ENABLE | DISABLE} RESTRICTED SESSION
   ALTER SYSTEM RESUME
   ALTER SYSTEM SUSPEND
   ALTER SYSTEM SHUTDOWN [IMMEDIATE] dispatcher_name
   ALTER SYSTEM SWITCH LOGFILE
   ALTER SYSTEM KILL SESSION 'int1, int2' [POST TRANSACTION] [IMMEDIATE]
   ALTER SYSTEM DISCONNECT SESSION 'int1, int2' [IMMEDIATE]
   ALTER SYSTEM SET parameter = value [COMMENT 'text'] [DEFERRED] [Scope_options]
   ALTER SYSTEM RESET parameter(s) [scope_options]
   ALTER SYSTEM QUIESCE RESTRICTED
!w o(S5N_KeX7Lb+V0   ALTER SYSTEM UNQUIESCE

SET Parameters:
   Full list of Static and dynamic parameters.

   Scope_options:
   SCOPE = {MEMORY|SPFILE|BOTH} [SID = 'sid' [,SID =...]]

   DEFERRED - Set parameter values for future connecting sessions.
              Currently active sessions are not affected and they retain the old parameter value.
              This is required for parameters that have the ISSSYS_MODIFIABLE =DEFERRED (in V$PARAMETER) 
              This is optional for parameters that have the ISSSYS_MODIFIABLE =IMMEDIATE (in V$PARAMETER)
              For static parameters, DEFERRED cannot be specified.

   COMMENT text is visible in the UPDATE_COMMENT column of V$SpPARAMETER

   The SID clause is for RAC, where it's possible to change a parameter value for one instance only.
   SID= '*' will affect all instances on the cluster
   If the instance is started with an SpFILE the default = * (all instances)
   If the instance is started with a PFILE then default SID = the current instance.

Quiesce

Putting the database into a quiesced state will prevent inactive user sessions from becoming active, Oracle then waits for existing transactions to finish. In shared server mode, Oracle will also block user logins.
%\X(HWK"tTEc#@0This allows SYS/SYSTEM to perform some maintenance tasks without a full shutdown. 51TestingÈí¼þ²âÊÔÍø C%k3px$p#B EQx

Notes
6XSN&g3dv
}6M0All Byte values can also be specified in K or M or G 51TestingÈí¼þ²âÊÔÍø!DHnkc v#\

k*F
ld_}Bk3K!nJ0e.g. you can enter 8388608 or 8192 K or 8M51TestingÈí¼þ²âÊÔÍø']0O
H"@Bh n'A

*Ier3U/^0All directory paths follow standard notation i.e UNIX 'quotes' or Windows "double quotes"51TestingÈí¼þ²âÊÔÍø)~ R?i7kR6k!S