The Application DBA  

Standby Database Management

Overview

This section describes procedures to manage a physical standby database environment.

Start the Standby Instance

The standby instance must be started and placed in standby mount mode. The database must be mounted in order to access the controlfile to allow file name changes and subsequent recovery. The following commands are used:

1. Use SQL*Plus to connect to the standby database instance. For example, enter:

SQL> CONNECT / AS SYSDBA

2. Start the Oracle instance at the standby database without mounting the database. For example, enter:

SQL> STARTUP NOMOUNT;

3. Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Modes and Recovery

Once the standby database has been recovered up to the last archived redo log file, it must be kept current by the application of log files once they have been archived from the primary database. This can be done in "manual recovery" mode or in "managed recovery" mode. The standby database can also be opened in "read only" mode which allows query access to the standby database. No recovery can take place while the database is open in read only mode through 10g Release 2, and so time must be arranged for periodic recoveries to take place in order to keep the standby in sync with the primary database.

Starting Recovery

For Oracle9i and above the standby database can be "caught up" and run in managed recovery mode. Managed recovery mode allows for the automatic application of archived redo log files to the standby database. Managed recovery mode should be initiated with a disconnected session. The following syntax will accomplish this:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Since Release 10.1 Oracle has supported real time apply, the following will enable real time apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

It is still possible to perform manual recovery (this was required in 8i standby database) with the command:

SQL> RECOVER STANDBY DATABASE;

or

SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

This will invoke standard cancel based recovery. Once all available archived log files have been applied then cancel the recovery. At this point managed recovery can be resumed.

Canceling Recovery

Cancel managed recovery with the command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Cancel manual recovery with the command:

SQL> RECOVER CANCEL;

Startup and Shutdown

In order to maintain managed recovery of the standby database, the following guidelines must be followed:

"To avoid creating gap sequences, follow these rules:

  • Start the standby databases and listeners before starting the primary database.
  • Shut down the primary database before shutting down the standby database.

If you violate either of these two rules, then the standby database is down while the primary database is open and archiving. Consequently, Oracle can create a gap sequence. When you restart the standby database later, you must synchronize the standby database manually with the primary database before you can initiate managed recovery."

If Data Guard is being implemented then this is will not be a problem since Data Guard will take care of re-synchronization.

Troubleshooting

Managed Recovery Errors

In order to initiate managed recovery all archived redo log files must be applied to the standby database. Managed recovery will not retroactively apply archived redo log files.

Archiver Errors

If archived redo log files are not being transferred to the standby database then check the "state" of the archive destination in the v$archive_dest view on the primary database. If this state is in error then the standby listener service may have failed or the tns alias or connect string may not be defined correctly. Once the problem is corrected, you can force re-transmission with a command like the following (not the register at the end):

SQL> alter system set log_archive_dest_2='service=DBSID_DG2 ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register';

If Data Guard is enabled then this should be issued automatically.

If the primary archive destination has stopped, possibly due to a full disk, fix the problem and issue:

archive log start

Standby Recovery Commands

The following shows a case of "manually" recovering the standby database outside of "managed recovery mode". This might be used when performing manual time lag recovery of a standby database.

SQL> connect / as sysdba
Connected.
SQL> alter database recover automatic standby database until cancel;
alter database recover automatic standby database until cancel
*
ERROR at line 1:
ORA-00279: change 158556003 generated at 01/03/2006 11:42:22 needed for thread
1
ORA-00289: suggestion : /u01/app/oracle/admin/DSID/arch/DBSID_1_10886.arc
ORA-00280: change 158556003 for thread 1 is in sequence #10886
ORA-00278: log file '/u01/app/oracle/admin/DBSID/arch/DBSID_1_10886.arc' no
longer needed for this recovery
ORA-16145: archival for thread# 1 sequence# 10886 in progress
 
 
SQL> alter database recover cancel;
 
Database altered.
 
SQL>
 
 
Manual Recovery
 
startup nomount;
alter database mount standby database;
recover standby database;

 

Copyright 1998-2013 AppsDBA Consulting. All Rights Reserved.