Standby Database Management_2 by aashu4uiit


This section describes procedurs to manage a standby database environment.

Start the Standby Instance
The standby instance must be started and placed in standby mount mode. The database should never be "opened" because this will invalidate the "standby" status. In addition, 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:


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

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

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

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:

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.

Events That Affect the Standby Database
The following events can cause standby database issues: Add datafile of create tablespace Drop or delete a tablespace or datafile Rename a datafile Alter the primary database control file (using the SQL ALTER DATABASE CREATE CONTROLFILE statement) Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause Change initialization parameter

See the Oracle Data Guard Concepts and Administration manual for more details.

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 retrospectively 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. Once that is corrected, you must force the listener to begin retransmission with the command: 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. This is the scenario used by the "stby_maint" script when implementing the "restore" option as part of a 10g AS high availability environment using DataGuard.
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;

System Management
oratab File
The oratab file should be updated so that the standby database entry has an "N" for the third field. This will insure that the "dbstart" utility does not inadvertently start and open the database and invalidate the standby status. An example is: DBSID:/u01/app/oracle/product/9.2.0:N

In order to facilitate the management of standby databases the dbcontrol utility has been modified to be standby database aware. The standby database's oratab entry must still be set with the third field set to "N" in order to prevent the accidental opening of the database at boot time. A new comment is supported in the oratab file to identify the standby database to the dbcontrol utility. The format is #STANDBY:<SID>:<OPTION> where SID is the standby database SID and option is NOSTART, MOUNT, MANAGED, or READ. With this comment identifying a corresponding standby database entry, dbcontrol will not open the standby database and will only perform operations using the

"standby" option. The following has been added to the oratab file for database "stby": #STANDBY:DBSID:MANAGED orasetup orasetup is a korn/bash shell script that replaces the Oracle supplied script "oraenv". This file should be located in the ORACLE_BASE/local/script directory. This script will set an Oracle environment properly for all versions of the database. It also recognizes the "Y" or “W” flag for databases and sets ORACLE_SID or the "N" flag and sets TWO_TASK. This is also done for standby databases to insure that the database is not accidentally damaged. Since the TWO_TASK variable is set by default, the user must set the ORACLE_SID variable explicitly in order to connect "/ as sysdba". The orasetup utility can be run from any user with the following syntax:

orasetup <SID>

This assumes a korn or bash shell and that $ORACLE_BASE/local/script is set in the PATH. dbcontrol dbcontrol is a korn/bash shell script that replaces dbstart and dbshut and should be located in the ORACLE_BASE/local/script directory. A "log" file directory should also exist at ORACLE_BASE/local/log as dbcontrol will create a log file if invoked by another process (e.g. if called by dbora at startup or shutdown). dbcontrol has been enhanced to manage all database utilities as defined in the oratab file and can be called during system startup or shutdown to start or stop all database services. dbcontrol has a help menu and can be used with a single command line or interactively. dbora dbora is an enhanced version of the Oracle bourne shell script that is called by the init process at startup and shutdown. The enhanced version calls dbcontrol with “START ALL” or “STOP ALL” parameters depending on whether it’s being called during a startup or shutdown of the machine. The file should be located in the /etc/init.d directory during database installation. stby_maint

stby_maint is a custom script to manage the deletion of applied archived redo log files at the standby site. This file should be located in the ORACLE_BASE/local/script directory and an entry should be placed in the crontab file to run once a day.

To top