13
SQL Statements

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

13.1 ALTER DATABASE ACTIVATE STANDBY DATABASE

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.

Table 13-1 Keywords for the ACTIVATE STANDBY DATABASE Clause

Keyword	Description
PHYSICAL	Activates a physical standby database. This is the default.
LOGICAL	Activates a logical standby database. If you have more than one logical standby database, you should first ensure that the same log data is available on all the logical standby sites.
SKIP [STANDBY LOGFILE] (Physical standby databases only)	Forces the failover operation to proceed even if standby redo logs contain data that could be recovered using the RECOVER MANAGED STANDBY DATABASE FINISH clause. Using the SKIP [STANDBY LOGFILE] clause indicates that it is acceptable to discard the contents of the standby redo log.

13.2 ALTER DATABASE ADD [STANDBY] LOGFILE

This statement adds one or more redo log 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 [THREAD integer] [GROUP integer] [REUSE] SIZE 
filespec;

Table 13-2 describes the keywords for this statement.

Table 13-2 Keywords for the ADD STANDBY LOGFILE Clause

Keyword	Description
STANDBY	Indicates that the redo log created is for use by standby databases only.
THREAD integer	Applicable only if you are using the Real Application Clusters option in parallel mode. The integer variable is the thread number.
GROUP integer	Uniquely identifies the redo log group among all groups in all threads and can range from 1 to the MAXLOGFILES value. You cannot add multiple redo log groups having the same GROUP value.
filespec	Specifies a redo log group containing one or more members.
REUSE	If the log already exists, you can specify REUSE to allow Data Guard to overwrite the header information in the log.
SIZE	Specify the size of the log in bytes. Use K or M to specify the size in kilobytes or megabytes.

See Section 5.3.3.3 for more information about this SQL statement.

13.3 ALTER DATABASE ADD [STANDBY] LOGFILE MEMBER

This statement adds new members to existing redo log groups. The SQL statement syntax is:

ALTER DATABASE ADD [STANDBY] LOGFILE MEMBER 'filename' [REUSE] TO logfile-descriptor;

Table 13-3 describes the keywords for this statement.

Table 13-3 Keywords for the ADD STANDBY LOGFILE MEMBER Clause

Keyword	Description
STANDBY	Indicates that the log member is for use only by a standby database. The STANDBY keyword is not required, but you can use it for symmetry in scripts, if necessary.
LOGFILE MEMBER 'filename'	Adds new members to existing redo log groups. Each new member is specified by 'filename'.
REUSE	If the file specified by 'filename' already exists, the log must be the same size as the other group members, and you must specify REUSE to allow the Oracle server to overwrite the existing log. If the log does not already exist, the Oracle server creates a log of the correct size.
logfile_descriptor	Specify an existing log group using either of these ways: Specify the GROUP integer parameter, where integer is the number of the existing log group. If this log group was added for standby database use, all members of the log group will be used only for standby databases. List the full filename specification for each member of the redo log group. Specify the filename according to the conventions for your operating system.

See Section 5.3.3.4 for more information about this SQL statement.

13.4 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

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 the PRIMARY KEY COLUMNS or UNIQUE INDEX COLUMNS keyword. 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.

Table 13-4 Keywords for the ADD SUPPLEMENTAL LOG DATA Clause

Keyword	Description
PRIMARY KEY	Ensures, for all tables with a primary key, that all columns of the primary key are placed into the redo log whenever an update operation is performed. If no primary key is defined, Oracle software places into the redo log a set of columns that uniquely identifies the row.
UNIQUE INDEX	Ensures, for all tables with a unique index, that if any unique index columns are modified, all other columns belonging to the unique index are also placed into the redo log.

See Section 4.1.6 for more information about this SQL statement.

13.5 ALTER DATABASE COMMIT TO SWITCHOVER

Use this statement to perform a switchover operation to change the current primary database to the standby database role and to change one standby database to the primary database role. The SQL statement clauses you specify differ depending on if you issue the statement on the primary database, a physical standby database, or a logical standby database:

• On the primary database that you want to change to run in a physical standby database role, use the following SQL statement syntax:

  ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [[WITH | WITHOUT] 
  SESSION SHUTDOWN ] [WAIT | NOWAIT];
  
  

• On the primary database that you want to change to run in a logical standby database role, use the following SQL statement syntax:

  ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY [WAIT | NOWAIT];
  
  

• On a physical standby database that you want to change to run in the primary database role, use the following SQL statement syntax:

  ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [[WITH | WITHOUT] SESSION 
  SHUTDOWN ] [WAIT | NOWAIT];
  
  

• On a logical standby database that you want to change to run in the primary database role, use the following SQL statement syntax:

  ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WAIT | NOWAIT];
  
  

Table 13-5 describes the keywords for this statement.

Table 13-5 Keywords for the COMMIT TO SWITCHOVER Clause

Keyword	Description
COMMIT TO SWITCHOVER TO PHYSICAL STANDBY	Transitions the primary database to run in the role of a physical standby database.The physical standby database must be mounted and can be open in READ ONLY mode.
COMMIT TO SWITCHOVER TO LOGICAL STANDBY	Transitions the primary database to run in the role of a logical standby database. This option must be followed by an ALTER DATABASE START LOGICAL STANDBY APPLY statement.
COMMIT TO SWITCHOVER TO [PHYSICAL | LOGICAL] PRIMARY	Transitions the standby database to run in the primary database role. For physical standby databases only, the standby database must be mounted and can be open in READ ONLY mode. You can specify the PHYSICAL or LOGICAL parameters for symmetry (in scripts, for example), but these keywords are not required.
WITH SESSION SHUTDOWN (Physical standby databases only)	Shuts down any open application sessions and rolls back uncommitted transactions as part of the execution of this statement. Logical standby databases do not support the WITH SESSION SHUTDOWN option.
WITHOUT SESSION SHUTDOWN (Physical standby databases only)	Causes the switchover operation to fail if any application sessions are open. This is the default. Logical standby databases do not support the WITHOUT SESSION SHUTDOWN option.
WAIT	Waits for the completion of the switchover operation before returning control to the user. This is the default.
NOWAIT	Returns control to the user before the switchover operation is complete.

See Section 7.2 and Section 7.3 for additional information about this SQL statement.

13.6 ALTER DATABASE CREATE STANDBY CONTROLFILE AS

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.

Table 13-6 Keywords for the CREATE STANDBY CONTROLFILE AS Clause

Keyword	Description
CONTROLFILE AS 'filename'	Specifies the name of the control file to be created and used to maintain a standby database.
REUSE	If the control file specified by the 'filename' parameter already exists, you must specify REUSE to allow the Oracle server to overwrite the existing file.

See Section 3.2.3 for more information about this SQL statement.

13.7 ALTER DATABASE DROP [STANDBY] LOGFILE

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.

Table 13-7 Keywords for the DROP [STANDBY] LOGFILE Clause

Keyword	Description
STANDBY	Drops all members of a redo log group. You can specify STANDBY for symmetry, but this keyword is not required.
logfile_descriptor	Specify an existing redo log group using either of these ways: Specify the GROUP integer parameter, whereinteger is the number of the existing log group. List the full filename specification for the redo log group. Specify the filename according to the conventions for your operating system.

See Section 8.4.4 for an example using this SQL statement.

13.8 ALTER DATABASE DROP [STANDBY] LOGFILE MEMBER

This statement drops one or more redo log members. The SQL statement syntax is:

ALTER DATABASE DROP [STANDBY] LOGFILE MEMBER 'filename';

Table 13-8 describes the keywords for this statement.

Table 13-8 Keywords for the DROP LOGFILE MEMBER Clause

Keyword	Description
STANDBY	Drops one or more standby redo log members. You can specify STANDBY for symmetry, but this keyword is not required.
'filename'	Specifies filenames, separated by commas, for one or more log members. Each filename must specify the fully-qualified file specification according to the conventions for your operating system.

13.9 ALTER DATABASE [NO]FORCE LOGGING

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 is:

• Required for physical standby databases to prevent inconsistent standby databases
• Recommended for logical standby databases to ensure data availability at the standby database

  Note: Oracle Corporation recommends setting the FORCE LOGGING clause before performing the backup operation to create the standby database and remaining in force logging mode for as long as the standby database is active. Also, to prevent performance degradation when in force logging mode, the database should be running in ARCHIVELOG mode.

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.

Table 13-9 Keywords for the [NO]FORCE LOGGING Clause

Keyword	Description
FORCE LOGGING	Logs all changes in the database except for changes in temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces and any NOLOGGING setting you specify for individual database objects. All ongoing, unlogged operations must finish before forced logging can begin.
NOFORCE LOGGING	Cancels the force logging mode. NOFORCE LOGGING is the default.

13.10 ALTER DATABASE MOUNT STANDBY DATABASE

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;

13.11 ALTER DATABASE OPEN READ ONLY

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 8.2.2 for more information about this SQL statement.

13.12 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

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 ];

startup_clause

When you start managed recovery operations, you can start log apply services in a foreground or a background session:

• To start a foreground session, the SQL statement syntax is:

  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE [TIMEOUT | NO TIMEOUT];
  

• To start a background session, the SQL statement syntax is:

  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT [FROM SESSION] [NO TIMEOUT];
  

modify_clause

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 some particular failover and switchover operations.

Keywords can be placed in any order in the SQL statement except when you start a 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  [ THREAD n ] SEQUENCE n ]
[ FINISH [ SKIP [STANDBY LOGFILE] [NOWAIT | WAIT] ] ] 
]

cancel_clause

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.

Table 13-10 Keywords for the RECOVER MANAGED STANDBY DATABASE Clause

Keyword	Description	Incompatible with
CANCEL [IMMEDIATE] [NOWAIT]	Terminates managed recovery. By default, log apply services will finish applying the current archived redo log before terminating. Specify IMMEDIATE to terminate managed recovery either before reading another block from the archived redo log or before opening the next archived redo log, whichever occurs first. Specify NOWAIT to return control to the process that issued the CANCEL statement without waiting for the managed recovery process to terminate.	All other keywords
DELAY integer	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 are selected for recovery.	CANCEL, FINISH, NODELAY
DEFAULT DELAY	Reverts the delay interval to the number of minutes that was specified in the LOG_ARCHIVE_DEST_n initialization parameter on the primary database, if any.	CANCEL, DELAY, FINISH, NODELAY
DISCONNECT [FROM SESSION]	Starts the managed recovery process (MRP) and log apply services in a background server process.	CANCEL, TIMEOUT
NEXT integer	Specifies the number of delayed archived redo logs that log apply services should apply as soon as possible after log transport services archive them.	CANCEL, FINISH
EXPIRE integer	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.	CANCEL, FINISH, NO EXPIRE
FINISH [SKIP [STANDBY LOGFILE]] [NOWAIT | WAIT]	Invokes a failover operation that first applies all available archived redo logs and then recovers available standby redo logs. Specify SKIP [STANDBY LOGFILE] to indicate that it is acceptable to skip applying the contents of the standby redo logs. Specify NOWAIT to return control to the foreground process before the recovery completes. Specify WAIT to return control after recovery completes.	CANCEL, DELAY, EXPIRE, NEXT, THROUGH..., TIMEOUT
NODELAY	Disables a previously specified DELAY option so that log apply services will apply the archived redo logs to the standby database without delay.	CANCEL, DELAY
NO EXPIRE	Disables a previously specified EXPIRE option.	CANCEL, EXPIRE
NO TIMEOUT	Disables a previously specified TIMEOUT option.	CANCEL
NOPARALLEL	Disables a previously specified PARALLEL option so that log apply services use a single process to apply all of the archived redo logs sequentially. This is the default.	CANCEL
PARALLEL [integer]	Starts additional parallel recovery processes to spread the workload of applying the archived redo logs simultaneously to the standby datafiles. By default, Oracle software selects a number of parallel processes that equal the number of CPUs available on all active instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter. However, you can specify integer to indicate the number of parallel processes to use in the parallel operation. Each parallel thread can use one or two parallel execution servers.	CANCEL
THROUGH [THREAD n] SEQUENCEn	Specifies the thread number and sequence number of the archived redo log through which you want to recover. Once the specified archived redo log is applied, managed recovery terminates. The THREAD n keyword is optional. If you do not specify THREAD n, it defaults to thread 1.	CANCEL, FINISH
THROUGH ALL ARCHIVELOG	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 THROUGH THREAD n SEQUENCE n keyword so that it does not stop after applying the specified archived redo log.	CANCEL, FINISH
THROUGH {ALL|NEXT|LAST} SWITCHOVER	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.) ALL - Continues managed recovery until you explicitly cancel it. Managed recovery continues through (ignoring) all switchover (end-of-redo) indicators. The ALL option is useful when there are other standby databases that are not participating in the switchover operation, and you do not want to stop and restart the recovery process on each one. NEXT - Stops managed recovery at the first switchover (end-of-redo) indicator encountered. This is the default behavior. LAST - Continues managed recovery through all switchover (end-of-redo) indicators, stopping only when an end-of-redo marker is encountered in the last archived redo log received.	CANCEL, FINISH
TIMEOUT integer	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 TIMEOUT only when starting a managed recovery in a foreground session.	CANCEL, DISCONNECT, FINISH

.

13.13 ALTER DATABASE REGISTER LOGFILE

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.

Table 13-11 Keywords for the REGISTER LOGFILE Clause

Keyword	Description
OR REPLACE	Allows updates to an existing archived redo log entry for the standby database (for example, when the location or file specification for the archived redo log changes). The SCNs of the entries must match exactly, and the original entry must have been created by log transport services.
PHYSICAL	Indicates that the archived redo log will be registered in the control file for the physical standby database.
LOGICAL	Indicates that the archived redo log will be registered in the dictionary for the logical standby database.
LOGFILE filespec	Specifies a redo log group containing one or more members. Each new member is specified by filespec.

See Section 7.2.2.1 for an example using this SQL statement.

13.14 ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE}

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.

Table 13-12 Keywords for the SET STANDBY TO MAXIMIZE Clause

Keyword	Description
PROTECTION (Physical standby databases only)	Offers the highest level of data protection. A transaction does not commit until all data needed to recover that transaction is written to at least one physical standby database that is configured to use the SYNC log transport mode. If the primary database is unable to write the redo records to at least one such standby database, the primary database is shut down. This mode guarantees no data loss, but it has the highest potential impact on the performance and availability of the primary database.
AVAILABILITY	Offers the next highest level of data protection. This mode guarantees there will be no data loss between the primary site and at least one standby site in the configuration unless a primary database failure occurs before recovery from a network outage. Then, no data is lost up to the last transaction that was shipped to the site. (Transactions that continued on the primary site after the network went down could be lost.) Unlike maximum protection mode, the primary database will not shut down if it is unable to write the redo records to at least one such standby database. Instead, the protection will be lowered to maximum performance mode until the situation is corrected and the standby database catches up with the primary database. This mode guarantees no data loss unless the primary database fails while in maximum performance mode. Maximum availability mode provides the highest level of data protection that is possible without affecting the availability of the primary database.
PERFORMANCE	This is the default protection mode. A primary database transaction commits before the data needed to recover the transaction is written to a standby database. Therefore, some data might be lost if the primary database fails, and you are unable to recover the redo records from the primary database. This mode provides the highest level of data protection that is possible without affecting the performance of the primary database.

See Section 5.2 for additional information about the data protection modes.

13.15 ALTER DATABASE START LOGICAL STANDBY APPLY

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 PRIMARY dblink];

Table 13-13 describes the keywords for this statement.

Table 13-13 Keywords for the START LOGICAL STANDBY APPLY Clause

Keyword	Description
INITIAL [scn-value]	Specify this keyword the first time you apply the logs to the logical standby database. It recovers the database to a transaction-consistent state immediately before the system change number (SCN) specified by an integer.
NEW PRIMARY dblink	Starts log apply services after a database switchover takes place. This statement ensures that all transactions in the archived redo logs are properly applied to the logical standby database. Specify this keyword after the ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY statement or when a logical standby database has completed processing logs from one primary database and a new database becomes the primary database. It uses the database link (dblink) provided in the command line to access tables on the primary database. The link must reference a privileged account that can read and lock the table on the primary database.

See Section 4.2.17 for additional information about this SQL statement.

13.16 ALTER DATABASE {STOP | ABORT} LOGICAL STANDBY APPLY

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.

Table 13-14 Keywords for the {STOP | ABORT} LOGICAL STANDBY APPLY Clause

Keyword	Description
STOP	Stops log apply services in an orderly fashion so that you can make changes to logical standby settings or perform planned maintenance. This clause is useful for refreshing materialized views or function-based indexes. Log transport services will continue to send archived redo logs to the logical standby database.
ABORT	Stops log apply services immediately. If the DBA_LOGSTDBY_PARAMETERS view shows the TRANSACTION_CONSISTENCY option is set to READ_ONLY or NONE, an ABORT might leave transactions in an inconsistent manner. Only use the ABORT keyword when an immediate shutdown is necessary.