Oracle 9i Data Guard is the new name for Oracle 8i Standby Server, incorporating a large number of new features. In this article I shall only focus on those relevant to the "Oracle9i Database: New Features For Administrators" OCP exam. For more detailed information read the Oracle 9i Data Guard Concepts and Administration documentation.
Related articles.
The Oracle 9i Data Guard architecture incorporates the following items:
The services required on the primary database are:
The services required on the standby database are:
Data Guard can be configured to run with varying synchronization modes indicating the potential for data loss.
To setup no-data-divergence, the most extreme level of data protection, then do the following.
SHUTDOWN IMMEDIATE
stby1= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1512)) ) (CONNECT_DATA = (SERVICE_NAME = stby1.world) ) )
ARCHIVELOG
mode one of the archive destinations will be set.
Add the other entries.
CONTROL_FILES=primary.ctl COMPATIBLE=9.0.1.0.0 LOG_ARCHIVE_START=true LOG_ARCHIVE_DEST_1='LOCATION=C:\Oracle\Oradata\TSH1\Archive MANDATORY REOPEN=30' LOG_ARCHIVE_DEST_2='SERVICE=stby1 LGWR SYNC AFFIRM' LOG_ARCHIVE_DEST_STATE_1=enable LOG_ARCHIVE_DEST_STATE_2=enable LOG_ARCHIVE_FORMAT=arc%t_%s.arc REMOTE_ARCHIVE_ENABLE=true
The LGWR SYNC AFFIRM
keywords indicate that the Logwriter should synchronously write updates to the online
redo logs to this location and wait for confirmation of the write before proceeding. The remote site will process and archive
these standby redo logs to keep the databases synchronized. This whole process can impact performance greatly but
provides maximum data security.
STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f';
SERVICE_NAMES = stby1 CONTROL_FILES=standby.ctl COMPATIBLE=9.0.1.0.0 LOG_ARCHIVE_START=true LOCK_NAME_SPACE=stby1 FAL_SERVER=prim1 FAL_CLIENT=stby1 # Uncomment if filename conversion is needed #DB_FILE_NAME_CONVERT=("/primary","/standby") #LOG_FILE_NAME_CONVERT=("/primary","/standby") STANDBY_ARCHIVE_DEST=C:\Oracle\Oradata\TSH1\Archive LOG_ARCHIVE_DEST_1='LOCATION=C:\Oracle\Oradata\TSH1\Archive' LOG_ARCHIVE_TRACE=127 LOG_ARCHIVE_FORMAT=arc%t_%s.arc STANDBY_FILE_MANAGEMENT=auto REMOTE_ARCHIVE_ENABLE=true
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1512)) ) STANDBY_LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost)) )
The file should resemble the following.
# LISTENER.ORA Network Configuration File: C:\Oracle\Ora901\network\admin\listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1521)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1512)) ) ) (DESCRIPTION = (PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW) ) (ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 2481)) ) ) STANDBY_LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc)(ORACLE_HOME = C:\Oracle\Ora901)(PROGRAM = extproc)) (SID_DESC = (ORACLE_HOME = C:\Oracle\Ora901) (SID_NAME = TSH1) ) )
lsnrctl reload
from the command prompt.stby1= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1512)) ) (CONNECT_DATA = (SERVICE_NAME = stby1.world) ) )
Create standby redo logs on the standby database to receive online redo information from the Logwriter on the primary database. The minimum number of groups required is an exact match, number and size, of the primary database, but performance may be increased by adding more.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('C:\Oracle\Oradata\TSH1\redo1a.log','C:\Oracle\Oradata\TSH1\redo1b.log') SIZE 500K;
During managed recovery the transfer of archivelogs is controlled by the servers without user intervention.
SQL> CONNECT sys/password AS SYSDBA SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora SQL> ALTER DATABASE MOUNT STANDBY DATABASE; SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Now that Data Guard is configured and running the primary database can be prevented from applying updates unless the update has been sent to at least one standby location. Connect to the primary database and execute the following.
ALTER DATABASE SET STANDBY DATABASE PROTECTED;
To stop managed standby recovery do the following.
SQL> -- Cancel protected mode on primary SQL> CONNECT sys/password@primary1 AS SYSDBA SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED; SQL> SQL> -- Cancel recovery if necessary SQL> CONNECT sys/password@standby1 AS SYSDBA SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE OPEN READ ONLY;
The database can subsequently be switched back to recovery mode as follows.
SQL> -- Startup managed recovery SQL> CONNECT sys/password@standby1 AS SYSDBA SQL> SHUTDOWN IMMEDIATE SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora SQL> ALTER DATABASE MOUNT STANDBY DATABASE; SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; SQL> -- Protect primary database SQL> CONNECT sys/password@primary1 AS SYSDBA SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;
If the primary database is not available the standby database can be activated as a primary database using the following statements.
SQL> -- Cancel recovery if necessary SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up immediately. The previous primary database can then be configured as a standby.
Backups of the standby database can only be performed if the database is shut down or in read only mode. Read only mode is best for managed recovery systems as archive logs will still be transfered during the backup process, thus preventing gap sequences. Once the server is in the desired mode simply copy the appropriate database files.
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.
-- Convert primary database to standby CONNECT sys/change_on_install@prim1 AS SYSDBA ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; -- Shutdown primary database SHUTDOWN IMMEDIATE; -- Mount old primary database as standby database STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; -- Convert standby database to primary CONNECT sys/change_on_install@stby1 AS SYSDBA ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; -- Shutdown standby database SHUTDOWN IMMEDIATE; -- Open old standby database as primary STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
This process has no affect on alternative standby locations. The process of converting the instances back to their original roles is known as a Switchback. The switchback is accomplished by performing another switchover.
Graceful Database Failover occurs when database failover causes a standby database to be converted to a primary database.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ALTER DATABASE ACTIVATE STANDBY DATABASE;
This process will recovery all or some of the application data using the standby redo logs, therefore avoiding reinstantiation of other standby databases. If completed successfully, only the primary database will need to be reinstatiated as a standby database.
Forced Database Failover changes one standby database to a primary database. Application data may be lost neccessitating the reinstantiation of the primary and all standby databases.
Gaps in the sequence of archive logs can be created when changes are applied to the primary database while the standby
database is unavailable. In Oracle8i the archive redo logs associated with these gaps had to be identified using the
V$ARCHIVE_GAP
view and copied manually to the standby server before managed recovery could be initiated again.
In Oracle9i most of these gap sequences can be resolved automatically. The following parameters must be added to the
standby init.ora file where the values indicate net services names.
FAL_SERVER = 'primary_db1' FAL_CLIENT = 'standby_db1'
The FAL server is normally the primary database, but can be another standby database. Once the standby database is placed in managed recovery mode it will automatically check for gap sequences. If it finds any it will request the appropriate files from the primary database via the FAL server. If the gap sequences cannot be resolved the files have to be recovered manually.
In Oracle8i managed recovery caused the user session to hang until the process was stopped by the user. This type of recovery is still available along with a background recovery that spawns a new background process and frees the user session
-- User session hangs ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; -- User session released ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Application of the archived redo logs to the standby database can be delayed using the DELAY
keyword. If a rogue statement significantly damages the primary database the DBA can choose to switch to the standby
database, which will be in a state prior to this action
-- Delay application of archived redo logs by 30 minutes. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30; -- Return to no delay (Default). ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/9i/data-guard