9 Managing Physical and Snapshot Standby Databases

9.1.1 Starting Up a Physical Standby Database

Use the SQL*Plus STARTUP command to start a physical standby database. The SQL*Plus STARTUP command starts, mounts, and opens a physical standby database in read-only mode when it is invoked without any arguments.

Once mounted or opened, a physical standby database can receive redo data from the primary database.

See Section 7.3 for information about Redo Apply and Section 9.2 for information about opening a physical standby database in read-only mode.

9.1.2 Shutting Down a Physical Standby Database

Use the SQL*Plus SHUTDOWN command to stop Redo Apply and shut down a physical standby database. Control is not returned to the session that initiates a database shutdown until shutdown is complete.

If the primary database is up and running, defer the standby destination on the primary database and perform a log switch before shutting down the physical standby database.

9.3.1 Adding a Datafile or Creating a Tablespace

The STANDBY_FILE_MANAGEMENT database initialization parameter controls whether the addition of a datafile to the primary database is automatically propagated to a physical standby databases.

• If the STANDBY_FILE_MANAGEMENT parameter on the physical standby database is set to AUTO, any new datafiles created on the primary database are automatically created on the physical standby database.

• If the STANDBY_FILE_MANAGEMENT database parameter on the physical standby database is set to MANUAL, a new datafile must be manually copied from the primary database to the physical standby databases after it is added to the primary database.

Note that if an existing datafile from another database is copied to a primary database, that it must also be copied to the standby database and that the standby control file must be re-created, regardless of the setting of STANDBY_FILE_MANAGEMENT parameter.

SQL> CREATE TABLESPACE MTS2 –
> DATAFILE '/dev/raw/raw100' size 1m;
Tablespace created.
 
SQL> ALTER SYSTEM SWITCH LOGFILE; 
System altered.

Fri Apr  8 09:49:31 2005
Media Recovery Log /u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o1_mf_1_7_15ffgt0z_.arc
Recovery created file /dev/raw/raw100
Successfully added datafile 6 to media recovery
Datafile #6: '/dev/raw/raw100'
Media Recovery Waiting for thread 1 sequence 8 (in transit)

SQL> CREATE TABLESPACE MTS3 –
> DATAFILE '/dev/raw/raw101' size 1m;
Tablespace created.
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

Fri Apr  8 10:00:22 2005
Media Recovery Log /u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o1_mf_1_8_15ffjrov_.arc
File #7 added to control file as 'UNNAMED00007'.
Originally created as:
'/dev/raw/raw101'
Recovery was unable to create the file as:
'/dev/raw/raw101'
MRP0: Background Media Recovery terminated with error 1274
Fri Apr  8 10:00:22 2005
Errors in file /u01/MILLER/MTS/dump/mts_mrp0_21851.trc:
ORA-01274: cannot add datafile '/dev/raw/raw101' - file could not be created
ORA-01119: error in creating database file '/dev/raw/raw101'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 1
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Fri Apr  8 10:00:22 2005
Errors in file /u01/MILLER/MTS/dump/mts_mrp0_21851.trc:
ORA-01274: cannot add datafile '/dev/raw/raw101' - file could not be created
ORA-01119: error in creating database file '/dev/raw/raw101'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 1
Fri Apr  8 10:00:22 2005
MTS; MRP0: Background Media Recovery process shutdown
ARCH: Connecting to console port...

9.3.2 Dropping Tablespaces and Deleting Datafiles

When a tablespace is dropped or a datafile is deleted from a primary database, the corresponding datafile(s) must be deleted from the physical standby database. The following example shows how to drop a tablespace:

SQL> DROP TABLESPACE tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;

To verify that deleted datafiles are no longer part of the database, query the V$DATAFILE view.

Delete the corresponding datafile on the standby system after the redo data that contains the previous changes is applied to the standby database. For example:

% rm /disk1/oracle/oradata/payroll/s2tbs_4.dbf

On the primary database, after ensuring the standby database applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:

% rm /disk1/oracle/oradata/payroll/tbs_4.dbf

SQL> DROP TABLESPACE INCLUDING CONTENTS –
> AND DATAFILES tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;

9.3.3 Using Transportable Tablespaces with a Physical Standby Database

You can use the Oracle transportable tablespaces feature to move a subset of an Oracle database and plug it in to another Oracle database, essentially moving tablespaces between the databases.

To move or copy a set of tablespaces into a primary database when a physical standby is being used, perform the following steps:

1. Generate a transportable tablespace set that consists of datafiles for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces.

2. Transport the tablespace set:

   1. Copy the datafiles and the export file to the primary database.

   2. Copy the datafiles to the standby database.

   The datafiles must be copied in a directory defined by the DB_FILE_NAME_CONVERT initialization parameter. If DB_FILE_NAME_CONVERT is not defined, then issue the ALTER DATABASE RENAME FILE statement to modify the standby control file after the redo data containing the transportable tablespace has been applied and has failed. The STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO.

3. Plug in the tablespace.

   Invoke the Data Pump utility to plug the set of tablespaces into the primary database. Redo data will be generated and applied at the standby site to plug the tablespace into the standby database.

For more information about transportable tablespaces, see Oracle Database Administrator's Guide.

9.3.4 Renaming a Datafile in the Primary Database

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

The following steps describe how to rename a datafile in the primary database and manually propagate the changes to the standby database.

1. To rename the datafile in the primary database, take the tablespace offline:

   SQL> ALTER TABLESPACE tbs_4 OFFLINE;
   

2. Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the datafile on the primary system:

   % mv /disk1/oracle/oradata/payroll/tbs_4.dbf 
   /disk1/oracle/oradata/payroll/tbs_x.dbf
   

3. Rename the datafile in the primary database and bring the tablespace back online:

   SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE      2> '/disk1/oracle/oradata/payroll/tbs_4.dbf' 
     3>  TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
   SQL> ALTER TABLESPACE tbs_4 ONLINE;
   

4. Connect to the standby database, query the V$ARCHIVED_LOG view to verify all of the archived redo log files are applied, and then stop Redo Apply:

   SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
   SEQUENCE# APP
   --------- ---
   8 YES
   9 YES
   10 YES
   11 YES
   4 rows selected.
   
   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   

5. Shut down the standby database:

   SQL> SHUTDOWN;
   

6. Rename the datafile at the standby site using an operating system command, such as the UNIX mv command:

   % mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf
   

7. Start and mount the standby database:

   SQL> STARTUP MOUNT;
   

8. Rename the datafile in the standby control file. Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.

   SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' 
     2> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
   

9. On the standby database, restart Redo Apply:

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
     2> DISCONNECT;
   

If you do not rename the corresponding datafile at the standby system, and then try to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:

ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock datafile 4 - see DBWR trace file
ORA-01110: datafile 4: '/Disk1/oracle/oradata/payroll/tbs_x.dbf'

9.3.5 Add or Drop a Redo Log File Group

The configuration of the redo log and standby redo log on a physical standby database should be reevaluated and adjusted as necessary after adding or dropping a redo log file group on the primary database.

Take the following steps to add or drop a redo log file group or standby redo log file group on a physical standby database:

1. Stop Redo Apply.

2. If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.

3. Add or drop a log file group.

4. Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states.

5. Restart Redo Apply.

9.3.6 NOLOGGING or Unrecoverable Operations

When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, the standby database is invalidated and may require substantial DBA administrative activities to repair. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCELOGGING clause to override the NOLOGGING setting. However, this statement will not repair an already invalidated database.

See Section 13.4 for information about recovering after the NOLOGGING clause is used.

9.3.7 Refresh the Password File

If the REMOTE_LOGIN_PASSWORDFILE database initialization parameter is set to SHARED or EXCLUSIVE, the password file on a physical standby database must be replaced with a fresh copy from the primary database after granting or revoking administrative privileges or changing the password of a user with administrative privileges.

Failure to refresh the password file on the physical standby database may cause authentication of redo transport sessions or connections as SYSDBA or SYSOPER to the physical standby database to fail.

9.3.8 Reset the TDE Master Encryption Key

The database encryption wallet on a physical standby database must be replaced with a fresh copy of the database encryption wallet from the primary database whenever the TDE master encryption key is reset on the primary database.

Failure to refresh the database encryption wallet on the physical standby database will prevent access to encrypted columns on the physical standby database that are modified after the master encryption key is reset on the primary database.

9.5.1 Using Views to Monitor Primary, Physical, and Snapshot Standby Databases

This section shows how to use dynamic performance views to monitor primary, physical standby, and snapshot standby databases.

The following dynamic performance views are discussed:

• V$DATABASE

• V$MANAGED_STANDBY

• V$ARCHIVED_LOG

• V$LOG_HISTORY

• V$DATAGUARD_STATUS

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, –
> DATABASE_ROLE ROLE, SWITCHOVER_STATUS –
> FROM V$DATABASE;

SQL> SELECT FS_FAILOVER_STATUS "FSFO STATUS", -
> FS_FAILOVER_CURRENT_TARGET TARGET, -
> FS_FAILOVER_THRESHOLD THRESHOLD, -
> FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" –
> FROM V$DATABASE;

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,-
> BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
 
PROCESS STATUS       THREAD#    SEQUENCE#  BLOCK#     BLOCKS
------- ------------ ---------- ---------- ---------- ----------
RFS     ATTACHED     1          947        72         72
MRP0    APPLYING_LOG 1          946        10         72

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, -
> NEXT_CHANGE# FROM V$ARCHIVED_LOG;
 
THREAD#    SEQUENCE#  FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1          945        74651         74739
1          946        74739         74772
1          947        74772         7474

SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, -
> NEXT_CHANGE# FROM V$LOG_HISTORY;

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

9.7.1 Converting a Physical Standby Database into a Snapshot Standby Database

A physical standby database is converted into a snapshot standby database with the following command:

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY 

All but one instance of a multi-instance physical standby must be shut down before issuing this command.

An implicit guaranteed restore point is created when a physical standby database is converted into a snapshot standby database. This guaranteed restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database. The name of this guaranteed restore point begins with 'SNAPSHOT_STANDBY_REQUIRED_'.

The database is dismounted during conversion and must be restarted before it is mounted.

9.7.2 Using a Snapshot Standby Database

Once a physical standby database has been converted into a snapshot standby database, it can be opened in read-write mode and it is fully updatable. A snapshot standby database continues to receive and archive redo data from the primary database, and this redo data will be automatically applied when the snapshot standby database is converted back into a physical standby database.

A snapshot standby database has the following characteristics:

• Redo data gap detection and resolution works just as it does on a physical standby database.

• If the primary database moves to new database branch (for example, because of a Flashback Database or an OPEN RESETLOGS), the snapshot standby database will continue accepting redo from new database branch.

• A snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.

• After a switchover or failover between the primary database and one of the physical or logical standby databases in a configuration, the snapshot standby database can receive redo data from the new primary database after the role transition.

• A snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.