Oracle9i Data Guard Concepts and Administration Release 2 (9.2) Part Number A96653-01 |
|
This chapter summarizes the SQL statements that are useful for performing operations on standby databases in a Data Guard environment. These include:
ALTER DATABASE ACTIVATE STANDBY DATABASE ALTER DATABASE ADD [STANDBY] LOGFILE ALTER DATABASE ADD [STANDBY] LOGFILE MEMBER ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ALTER DATABASE COMMIT TO SWITCHOVER ALTER DATABASE CREATE STANDBY CONTROLFILE AS ALTER DATABASE DROP [STANDBY] LOGFILE ALTER DATABASE DROP [STANDBY] LOGFILE MEMBER ALTER DATABASE [NO]FORCE LOGGING ALTER DATABASE MOUNT STANDBY DATABASE ALTER DATABASE OPEN READ ONLY ALTER DATABASE RECOVER MANAGED STANDBY DATABASE ALTER DATABASE REGISTER LOGFILE ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE} ALTER DATABASE START LOGICAL STANDBY APPLY ALTER DATABASE {STOP|ABORT} LOGICAL STANDBY APPLY
See Also:
Oracle9i SQL Reference for additional information about these and other SQL statements |
This statement performs a forced failover operation, in which the primary database is removed from the Data Guard environment and a standby database assumes the primary database role. The standby database must be mounted before it can be activated with this statement. The SQL statement syntax is:
ALTER DATABASE ACTIVATE [PHYSICAL | LOGICAL] STANDBY DATABASE [SKIP [STANDBY LOGFILE]];
Table 13-1 describes the keywords for this statement.
See Section 7.3.4 for more information and examples of this SQL statement.
This statement adds one or more redo log file groups to the specified thread, making the logs available to the instance assigned the thread. The SQL statement syntax is:
ALTER DATABASE ADD [STANDBY] LOGFILE [THREADinteger
] [GROUPinteger
]filespec
;
Table 13-2 describes the keywords for this statement.
See Section 5.8.4.3 for more information about this SQL statement.
This statement adds new members to existing redo log file groups. The SQL statement syntax is:
ALTER DATABASE ADD [STANDBY] LOGFILE MEMBER 'filename
' [REUSE] TOlogfile-descriptor
;
Table 13-3 describes the keywords for this statement.
See Section 5.8.4.4 for more information about this SQL statement.
This statement is for logical standby databases only.
You must enable full supplemental logging before you create the logical standby database. This is because supplemental logging is the source of change to a logical standby database. To implement full supplemental logging, you must specify either PRIMARY KEY
COLUMNS
or UNIQUE INDEX
COLUMNS
. The SQL statement syntax is:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA {PRIMARY KEY | UNIQUE INDEX} COLUMNS;
Table 13-4 describes the keywords for this statement.
See Section 4.1 (step 4) for more information about this SQL statement.
Use this statement to perform a switchover operation to change the current primary database to the standby role and to change one standby database to the primary role. The SQL statement clauses you specify differ depending on whether you issue the statement on the primary database, a physical standby database, or a logical standby database:
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [[WITH | WITHOUT] SESSION SHUTDOWN ] [WAIT | NOWAIT];
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY [WAIT | NOWAIT];
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [[WITH | WITHOUT] SESSION SHUTDOWN ] [WAIT | NOWAIT];
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WAIT | NOWAIT];
Table 13-5 describes the keywords for this statement.
See Section 7.2 for additional information about this SQL statement.
This statement is for physical standby databases only.
This statement creates a standby control file. Issue this statement on the primary database. The SQL statement syntax is:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename'
[REUSE];
Table 13-6 describes the keywords for this statement.
See Section 3.3.3 for more information about this SQL statement.
This clause drops all members of a redo log group. The SQL statement syntax is:
ALTER DATABASE DROP [STANDBY] LOGFILE logfile_descriptor
;
Table 13-7 describes the keywords for this statement.
See Section 10.1.3.4 for an example using this SQL statement.
This statement drops one or more redo log members. The SQL statement syntax is:
ALTER DATABASE DROP [STANDBY] LOGFILE MEMBER 'f
ilename';
Table 13-8 describes the keywords for this statement.
Controls whether or not the Oracle database server logs all changes in the database except for changes to temporary tablespaces and temporary segments. The [NO]FORCE LOGGING
clause:
The primary database must be mounted but not open when you issue this statement. The SQL statement syntax is:
ALTER DATABASE [NO]FORCE LOGGING;
Table 13-9 describes the keywords for this statement.
Mounts a physical standby database, allowing the standby instance to receive archived redo logs from the primary instance. The SQL statement syntax is:
ALTER DATABASE MOUNT STANDBY DATABASE;
See Section 6.3.2 for another example of this SQL statement.
This statement is required for physical standby databases. It can be used for logical standby databases.
Opens a physical standby database in read-only mode. This SQL statement restricts users to read-only transactions, preventing them from generating redo logs. You can use this clause to make a physical standby database available for queries even while archive logs are being copied from the primary database site.
You must mount the physical standby database before you can open it. The SQL statement syntax is:
ALTER DATABASE OPEN READ ONLY;
See Section 6.3.5 for more information about this SQL statement.
This statement is for physical standby databases only.
Use this statement to start, control, and cancel managed recovery operations and log apply services for physical standby databases. You can use the RECOVER MANAGED STANDBY DATABASE
clause on a database that is mounted, open or closed. Although this SQL statement does not require any additional clauses, it provides many options to help you control the managed recovery process. The SQL statement syntax is:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [startup_clause
|modify_clause
|cancel_ clause
];
When you start managed recovery operations, you can start log apply services in a foreground or a background session:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [TIMEOUT | NO TIMEOUT];
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT [FROM SESSION] [NO TIMEOUT];
The RECOVER MANAGED STANDBY
DATABASE
clause provides a wealth of options for controlling the managed recovery process, switchover operations, and failover operations. These keywords work the same whether managed recovery operations were started in a foreground or a background session, with the exception of graceful failover and switchover operations.
Keywords can be placed in any order in the SQL statement except when you start a graceful failover operation using the FINISH
keyword. This keyword must be specified last in the SQL statement.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [ [ NO TIMEOUT | TIMEOUT[
integer
]
] [ NODELAY | DELAY[
integer
]
] [ DEFAULT DELAY ] [ NO EXPIRE | EXPIRE[
integer
]
] [ NEXT[
integer
]
] [ NOPARALLEL | PARALLEL[
integer
]
] [ THROUGH { ALL | NEXT | LAST } SWITCHOVER ] [ THROUGH ALL ARCHIVELOG [ THREADn
] SEQUENCEn
] [ FINISH [ SKIP [STANDBY LOGFILE] [NOWAIT | WAIT] ] ] ]
To stop a managed recovery session, the SQL statement syntax is:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL [IMMEDIATE] [NOWAIT];
Table 13-10 describes all of the keywords.
Keyword | Description | Incompatible with |
---|---|---|
|
Terminates managed recovery. By default, log apply services will finish applying the current archived redo log before terminating. See Section 6.3.3.1. Specify Specify |
All other keywords |
|
Specifies an absolute delay interval (in minutes) that log apply services will wait before applying the individual archived redo logs. The apply delay interval begins once the archived redo logs have been selected for recovery. See Section 6.3.3.3. |
|
|
Reverts the delay interval to the number of minutes that was specified in the |
|
|
Starts the managed recovery process (MRP) and log apply services in a background server process. See Section 6.3.3.4. |
|
|
Specifies the number of delayed archived redo logs that log apply services should apply as soon as possible after the log transport services have archived them. See Section 6.3.3.7. |
|
|
Specifies the number of minutes, relative to the current time, after which the managed recovery operation will automatically terminate. Log apply services will finish applying the current archived redo log before stopping. See Section 6.3.3.5. |
|
Invokes a graceful failover operation that first applies all available archived redo logs and then recovers available standby redo logs. See Section 6.3.3.6. Specify Specify Specify |
|
|
|
Disables a previously specified |
|
|
Disables a previously specified |
|
|
Disables a previously specified |
|
|
Disables a previously specified |
|
Starts additional parallel recovery processes to spread the workload of applying the archived redo logs simultaneously to the standby datafiles. By default, Oracle selects a number of parallel processes that equal the number of CPUs available on all active instances times the value of the |
|
|
|
Specifies the thread number and sequence number of the archived redo log through which you want to recover. Once the specified archived redo log has been applied, managed recovery terminates. The |
|
|
Specifies the default behavior for managed recovery mode, which is to continue managed recovery until it is explicitly stopped. This clause is useful to alter a managed recovery that is currently running with a |
|
|
Keeps log apply services actively applying archived redo logs received from the new primary database after a switchover operation. (By default, log apply services stop after encountering a switchover (end-of-redo) marker within an archived redo log.) See Section 6.3.3.11 for more information.
|
|
|
Specifies the number of minutes that the managed recovery process waits for the next archived redo log to arrive from the primary database. If another log does not arrive within the specified time, log apply services automatically stop. Specify |
|
See Also:
Section 6.3.1 for complete information about controlling log apply services and the managed recovery process. |
This clause allows the registration of manually archived redo logs. The SQL statement syntax is:
ALTER DATABASE REGISTER [OR REPLACE] [PHYSICAL | LOGICAL] LOGFILE filespec
;
Table 13-11 describes the keywords for this statement.
See Section 7.3.3.3 for an example using this SQL statement.
Use this statement to specify the level of protection for the data in your database environment. Using one of these protection levels, you can protect the primary database against data loss and data divergence. The SQL statement syntax is:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE};
You execute this statement on the primary database, which must be stopped and in the mount state. Table 13-12 describes the keywords for this statement.
See Section 5.7 for additional information about this SQL statement.
This statement is for logical standby databases only.
This statement starts log apply services on the logical standby database. The SQL statement syntax is:
ALTER DATABASE START LOGICAL STANDBY APPLY [INITIAL [scn-value
] ] [NEW PRIMARYdblink
];
Table 13-13 describes the keywords for this statement.
See Section 4.2 (step 14) for additional information about this SQL statement.
This statement is for logical standby databases only.
This clause stops log apply services on a logical standby database. The SQL statement syntax is:
ALTER DATABASE { STOP | ABORT } LOGICAL STANDBY APPLY;
Table 13-14 describes the keywords for this statement.
|
Copyright © 1999, 2002 Oracle Corporation. All Rights Reserved. |
|