13
SQL Statements Relevant to Data Guard

ADD [STANDBY] LOGFILE
[THREAD integer]
[GROUP integer] filespec

Adds one or more online redo log file groups or standby redo log file groups to the specified thread, making the log files available to the instance to which the thread is assigned.

See Section 8.3.5 for an example of this statement.

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

Adds new members to existing online redo log file groups or standby redo log file groups.

See Section 5.7.3.2 for an example of this statement.

[ADD|DROP] SUPPLEMENTAL LOG DATA {PRIMARY KEY|UNIQUE INDEX} COLUMNS

This statement is for logical standby databases only.

Use it to enable full supplemental logging before you create a logical standby database. This is necessary 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 the UNIQUE INDEX COLUMNS keyword on this statement.

See Section 4.2.2.1 for an example of this statement.

COMMIT TO SWITCHOVER TO [PRIMARY] |[[PHYSICAL|LOGICAL] [STANDBY]]
[WITH | WITHOUT] SESSION SHUTDOWN]
[WAIT | NOWAIT]

Performs a switchover to:

• Change the current primary database to the standby database role
• Change one standby database to the primary database role.

Note: On logical standby databases, you must issue the ALTER DATABASE PREPARE TO SWITCHOVER statement to prepare the database for the switchover before you issue the ALTER DATABASE COMMIT TO SWITCHOVER statement.

See Section 7.2.1 and Section 7.3.1 for examples of this statement.

CREATE [PHYSICAL|LOGICAL] STANDBY CONTROLFILE AS 'filename' [REUSE]

Creates a control file to be used to maintain a physical or a logical standby database. Issue this statement on the primary database.

See Section 3.2.2 and Section 4.2.3.3 for examples of this statement for physical and logical standby databases, respectively.

DROP [STANDBY] LOGFILE logfile_descriptor

Drops all members of an online redo log file group or standby redo log file group.

See Section 8.3.5 for an example of this statement.

DROP [STANDBY] LOGFILE MEMBER 'filename'

Drops one or more online redo log file members or standby redo log file members.

[NO]FORCE LOGGING

Controls whether or not the Oracle database 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.

The primary database must be mounted but not open when you issue this statement. See Section 3.1.1 for an example of this statement.

MOUNT [STANDBY DATABASE]

Mounts a standby database, allowing the standby instance to receive redo data from the primary instance.

OPEN

Opens a previously started and mounted database:

• Physical standby databases are opened in read-only mode, restricting users to read-only transactions and preventing the generating of redo data.
• Logical standby database are opened in read/write mode.

See Step 5 in Section 4.2.4 for an example of this statement.

PREPARE TO SWITCHOVER

This statement is for logical standby databases only.

It prepares the primary database and the logical standby database for a switchover by building the LogMiner dictionary before the switchover takes place. After the dictionary build has completed, issue the ALTER DATABASE COMMIT TO SWITCHOVER statement to switch the roles of the primary and logical standby databases.

See Section 7.3.1 for examples of this statements.

RECOVER MANAGED STANDBY DATABASE [
[NO TIMEOUT|TIMEOUT [integer] ]
[NODELAY|DELAY [integer] ]
[DEFAULT DELAY]
[DISCONNECT]
[NO EXPIRE|EXPIRE [integer] ]
[NEXT [integer]]
[NOPARALLEL|PARALLEL [integer]]
[THROUGH {ALL|NEXT|LAST SWITCHOVER]
[THROUGH [THREAD n] SEQUENCE n]
[ALL ARCHIVELOG]
[FINISH [SKIP[STANDBY LOGFILE]
[NOWAIT|WAIT]]
[UNTIL CHANGE scn]
[USING CURRENT LOGFILE] ]

This statement is for physical standby databases only.

Use this statement to start and control log apply services for physical standby databases. You can use the RECOVER MANAGED STANDBY DATABASE clause on a physical standby database that is mounted, open, or closed. This clause provides many options to help you control Redo Apply.

See Step 3 in Section 3.2.6 and Section 6.3 for examples of this statement.

RECOVER MANAGED STANDBY DATABASE CANCEL
[[NOWAIT]|[WAIT]|[IMMEDIATE] ]

This statement cancels Redo Apply on a physical standby database.

REGISTER [OR REPLACE]
[PHYSICAL|LOGICAL] LOGFILE filespec

Allows the registration of manually archived redo log files.

See Section 5.8.4 for an example of this statement.

RESET DATABASE TO INCARNATION integer

Resets the target recovery incarnation for the database from the current incarnation to the prior incarnation.

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

Issue this statement on any primary database that is mounted but not opened. Specifies one of the three data protection modes for the Data Guard configuration. All three modes provide a high degree of data protection, but they differ in terms of the effect that each protection mode has on the availability and performance of the primary database. The

See Section 5.6.3 for an example of this statement.

START LOGICAL STANDBY APPLY
INITIAL [scn-value] ]
[NEW PRIMARY dblink]

This statement is for logical standby databases only.

It starts SQL Apply on a logical standby database. See Section 6.4.1 for examples of this statement.

{STOP|ABORT} LOGICAL STANDBY APPLY

This statement is for logical standby databases only.

Use the STOP clause to stop SQL Apply on a logical standby database in an orderly fashion. Use the ABORT clause to stop SQL Apply abruptly. See Section 7.3.2 for an example of this statement.

ACTIVATE [PHYSICAL|LOGICAL] STANDBY DATABASE [SKIP [STANDBY LOGFILE]]

Performs a failover in which the primary database is removed from the Data Guard environment and one standby database assumes the primary database role. The standby database must be mounted before it can be activated with this statement.

Note: Do not use the ALTER DATABASE ACTIVATE STANDBY DATABASE statement to failover because it causes data loss. Instead:

• For physical standby databases, use the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement with the FINISH or FINISH SKIP keywords, which perform the role transition as quickly as possible with little or no data loss and without rendering other standby databases unusable.

  Note: The failover operation adds an end-of-redo marker to the header of the last log file being archived and sends the redo to all enabled destinations that are valid for the primary role (specified with the VALID_FOR=(PRIMARY_ROLE, *_LOGFILES) or the VALID_FOR=(ALL_ROLES, *_LOGFILES) attributes).

• For logical standby databases, use the ALTER DATABASE PREPARE TO SWITCHOVER and ALTER DATABASE COMMIT TO SWITCHOVER statements.

ALTER SESSION [ENABLE|DISABLE] GUARD

This statement is for logical standby databases only.

This statement allows privileged users to turn the database guard on and off for the current session.

See Section 7.3.2 for an example of this statement.