Synopsis
1. Decision on the kind of data guard build to be implemented
2. Setup changes on the primary database
3. Database backup to be taken on the primary database
4. Data guard build on the target host (Physical standby database)
5. Post-build checks
6. Failover / Switchover steps involved in case of a disaster on primary database
1. Decision on the kind of data guard build to be implemented
Data guard is able to provide an efficient disaster recovery solution by maintaining transactionally consistent copies of the production database at a remote site.
It can be done in two ways:
1. Physical standby
2. Logical standby
For any enterprise level build where the business impact is critical, we normally go for physical standby database. For more details on logical standby database, please refer the link below:
Physical Standby Database
A physical standby database is kept in sync with the primary database by using media recovery to apply redo / archive that was generated on the primary database. Because media recovery is used, we can be assured that a physical standby is a block-for-block identical copy of the primary database.
There are three protection modes available.
1. Maximum protection
2. Maximum availability
3. Maximum performance
1.1 Maximum protection
This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to the standby redo log of at least one transactionally consistent standby database.
1.2 Maximum availability
This protection mode provides the highest level of data protection that is possible without compromising the availability of the primary database. Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one transactionally consistent standby database. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected, and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availability mode.
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.
1.3 Maximum performance
This protection mode (the default) provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the transactions that create the redo data.
When network links with sufficient bandwidth are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.
2. Setup changes on the primary database
2.1 Archive log mode
The primary database should be in archive log mode in order to build a data guard setup for it.
Login to SQLPLUS, execute 'archive log list' and verify if your database is in archive log mode. If not, enable it.
2.2 Force Logging
Place the primary database in FORCE LOGGING mode.
SQL> ALTER DATABASE FORCE LOGGING;
2.3 Create a password file
Create a password file using SYS password if one does not exist under $ORACLE_HOME/dbs on the primary database.
syntax: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=
2.4 Create standby redo log files
a) The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.
b) However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads
c) Make sure that you create the standby redo log file members under a new group (which is not used by the actual archive online redo log members).
d) Create the standby redo log files.
Syntax:
Adding a Standby Redo Log File Group to a Specific Group Number
You can also specify a number that identifies the group using the GROUP clause:
Example: SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/appl/oracle/r01/std_log1.log','/appl/oracle/r02/std_log2.log') SIZE 500M;
Adding a Standby Redo Log File Group to a Specific Thread
The following statement adds a new standby redo log file group to a standby database and assigns it to THREAD 5:
Example: SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5 ('/appl/oracle/r01/std_log1.log','/appl/oracle/r02/std_log2.log') SIZE 500M;
e) Perform a log switch on the primary database, and execute the following query to know the standby redo log files status.
Syntax: SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
2.5 Set Primary Database Initialization Parameters for Data Guard
If the primary database is using spfile, create a pfile from spfile and add / edit the following parameters:
############################# Parameters For Data Guard ########################################
DB_NAME='P'
DB_UNIQUE_NAME=P
LOG_ARCHIVE_CONFIG='DG_CONFIG=(P,S)'
LOG_ARCHIVE_DEST_1=
'LOCATION=
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=P'
LOG_ARCHIVE_DEST_2=
'SERVICE=S LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=S'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
####### Standby Parameters ###############################################
FAL_SERVER=S
FAL_CLIENT=P
DB_FILE_NAME_CONVERT='
LOG_FILE_NAME_CONVERT='
STANDBY_FILE_MANAGEMENT=AUTO
REMOTE_ARCHIVE_ENABLE=TRUE
##############################################################################################
* P = Primary database name
* S = Physical Standby database name
2.5.1 Description of the Data Guard parameters
DB_NAME - This is the actual primary database name. Use the same name for all standby databases.
DB_UNIQUE_NAME - Specify a unique name for each database (standby). This name stays with the database and does not change, even if the primary and standby databases reverse roles.
LOG_ARCHIVE_CONFIG - Specify the DG_CONFIG attribute on this parameter to list the DB_UNIQUE_NAME of the primary and standby databases in the Data Guard configuration; this enables the dynamic addition of a standby database to a Data Guard configuration that has a Real Application Clusters primary database running in either maximum protection or maximum availability mode. By default, the LOG_ARCHIVE_CONFIG parameter enables the database to send and receive redo; after a role transition you may need to specify these settings again using the SEND, NOSEND, RECEIVE, or NORECEIVE keywords.
LOG_ARCHIVE_DEST_n - Specify where the redo data is to be archived on the primary and standby systems.
LOG_ARCHIVE_DEST_STATE_n - Specify ENABLE to allow redo transport services to transmit redo data to the specified destination.
FAL_SERVER - Specify the Oracle Net service name of the FAL server (typically this is the database running in the primary role).
FAL_CLIENT - Specify the Oracle Net service name of the primary database.
DB_FILE_NAME_CONVERT - Specify the path name and filename location of the primary database data files followed by the standby location.
LOG_FILE_NAME_CONVERT - Specify the location of the primary database online redo log files followed by the standby location.
STANDBY_FILE_MANAGEMENT - Set to AUTO so when data files are added to or dropped from the primary database, corresponding changes are made automatically to the standby database.
REMOTE_ARCHIVE_ENABLE - When set to TRUE enables the sending of redo archival to remote destinations and the receipt of remotely archived redo.
2.5.2 LOG_ARCHIVE_DEST SERVICE (to set the protection mode)
The service for log_archive_dest parameter varies with the kind of protection mode decided for the data guard build.
For maximum performance
This is the default protection mode.
LGWR ASYNC NOAFFIRM
(or)
ARCH SYNC NOAFFIRM
For maximum protection
LGWR SYNC AFFIRM
Once this service parameter is set, we need to perform the following by connecting to SQLPLUS as the default protection mode is maximum performance.
SQ> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database set standby to maximize protection;
SQL> alter database open;
Now, the protection mode is set to maximum protection.
For maximum availability
LGWR SYNC AFFIRM
Once this service parameter is set, we need to perform the following by connecting to SQLPLUS as the default protection mode is maximum performance.
SQ> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database set standby to maximize availability;
SQL> alter database open;
Now, the protection mode is set to maximum availability;
The following table will give more clarity:
Minimum Requirements for Data Protection Modes
Maximum Protection Maximum Availability Maximum Performance
Redo Archival Process LGWR LGWR LGWR or ARCH
Network Transmission Mode SYNC SYNC ASYNC when using LGWR process. Not applicable when using ARCH process.
Disk Write Option AFFIRM AFFIRM NOAFFIRM
Standby Redo Logs Required? Yes Required for physical standby databases only. Standby redo logs are not supported for logical standby databases. Required for physical standby databases using the LGWR process.
Database Type Physical only Physical and Logical Physical and Logical
Description of the protection mode parameters:
LGWR SYNC - The primary will wait for an acknowledgment from the standby server that it has received the redo previously sent before the commit is allowed to complete. The advantage is that this mechanism makes it possible to achieve zero data loss protection. But doing so requires sufficient network bandwidth and low latency to avoid impacting the performance of the primary database.
LGWR ASYNC - The primary does not wait for the network acknowledgment before processing the next transaction. But it is still possible, usually during peak processing periods, for the primary database to generate redo faster than the network can ship. If redo transmission falls too far behind, the LGWR ASYNC buffer becomes full and Data Guard automatically reverts back to using ARCH based log shipping until LGWR ASYNC can reconnect to the standby and reinitiate the redo shipping process at a later time.
ARCH - The primary will wait for an acknowledgment from the standby server that it has received the archive logs previously sent before the commit is allowed to complete.
AFFIRM - Specifies that all disk I/O to archived redo log files and standby redo log files is performed synchronously and completes successfully before the log writer process continues.
NOAFFIRM - Specifies that all disk I/O to archived redo log files and standby redo log files is performed asynchronously; the log writer process on the primary database does not wait until the disk I/O completes before continuing.
Other data guard parameters
Please verify the link below for other data gurad parameters like DELAY, DEPENDENCY, REOPEN etc.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_arch_dest_param.htm
2.6 Enable archiving
If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
2.7 Oracle Net Services
a) Reconfigure the LISTENER services on the primary database using the netca (available under $ORACLE_HOME/bin). (Oracle recommended)
b) Add the entries of both primary and physical standby databases in the TNSNAMES.ORA
c) Bounce the LISTENER services.
3. Database backup to be taken on primary database
Now, on the primary database kindly take a full database backup using any of the following methods:
1. Cold backup (Shutdown the database and copy the pfile, datafiles, redologfiles and controlfiles to the target server. Configure and bring up the target).
2. Hot backup (Put the database in begin backup mode, copy all database files to the target server and then end backup mode).
3. RMAN backup (Copy the RMAN backup pieces to the target server, catalog the backup pieces, restore the controfiles, mount the database and restore the datafiles).
4. Data guard build on the target host (Physical standby database)
4.1 Create a standby controlfile for the physical standby database on the primary database
On primary database, execute
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/
Copy this standby controfile backup to the target server.
4.2 Add / Edit the Data Guard and standby parameters in the PFILE
As performed on the primary database, we need to add / edit the following parameters in the PFILE of the physical standby database. (We can also copy the pfile of Primary, edit it to suite the physical standby database and then include the parameters given below).
############################# Parameters For Data Guard ########################################
DB_NAME='P'
DB_UNIQUE_NAME=S
LOG_ARCHIVE_CONFIG='DG_CONFIG=(P,S)'
LOG_ARCHIVE_DEST_1=
'LOCATION=
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=S'
LOG_ARCHIVE_DEST_2=
'SERVICE=S LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=P'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
####### Standby Parameters ###############################################
FAL_SERVER=P
FAL_CLIENT=S
DB_FILE_NAME_CONVERT='
LOG_FILE_NAME_CONVERT='
STANDBY_FILE_MANAGEMENT=AUTO
REMOTE_ARCHIVE_ENABLE=TRUE
##############################################################################################
* P = Primary database name
* S = Physical Standby database name
4.3 Restore physical standby database using the backup taken on primary database
a) From SQLPLUS,
SQL> startup nomount pfile=$ORACLE_HOME/dbs/PFILE (new one with the data guard and standby parameters)
b) Restore the standby controlfile from the backup taken in the Step 4.1
c) From SQLPLUS,
SQL> alter database mount standby database;
d) Restore the datafiles from the backup taken on primary database.
4.4 Oracle Net Services
a) Reconfigure the LISTENER services on the physical standby database using the netca (available under $ORACLE_HOME/bin). (Oracle recommended)
b) Add the entries of both primary and physical standby databases in the TNSNAMES.ORA
c) Bounce the LISTENER services.
4.5 Create a password file
Create a password file using SYS password if one does not exist under $ORACLE_HOME/dbs on the primary database.
syntax: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=
4.6 Create PFILE from SPFILE
Create the new pfile from spfile
SQL> create pfile from spfile;
SQL> shutdown immediate;
This pfile will contain all the required data guard and physical standby parameters for the physical standby database.
4.7 Bring up the physical standby database
SQL> startup nomount pfile=$ORACLE_HOME/dbs/
SQL> alter database mount standby database;
SQL> recover managed standby database disconnect from session;
(or)
SQL> recover managed standby database nodely disconnect from session;
(or)
SQL> recover managed standby database using current logfile nodelay disconnect from session;
Note: The recovery mode depends on the type of protection mode chosen.
Now, the physical standby database has been built successfully.
Note: The standby database will always be in 'MOUNT' status and should not be opened unless there is a failover / switchover situation.
5. Post-build checks
a) We can use the following query to see if any archive logs are still pending which need to be shipped to the standby database.
SQL> select * from v$archived_gap;
b) On both primary and standby database, we can you the following query to find out the current database role.
SQL> select name,open_mode,protection_mode,protection_level,database_role,switchover_status from v$database;
c) On both primary and standby database, we can you the following query to find out the standby redo log files details.
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
d) On both primary and standby database, we can you the following query to find out the online redo log files details.
SQL> select lf.MEMBER,l.GROUP#,THREAD#,SEQUENCE#,MEMBERS,bytes/1024/1024 "BYTES IN MB",ARCHIVED,l.status from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#;
e) If we need to check the redo log sync between online and standby, first take the current sequence details, perform a log switch and check on the standby if the new sequence is getting reflected.
On both primary database and physical standby database,
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
On primary database,
SQL> alter system switch logfile;
On physical standby database,
SQL> SELECT SEQUENCE#,ARCHIVED,APPLIED from V$ARCHIVED_LOG ORDER BY SEQUENCE#;
If the new archive sequence is in sync, then data guard is working fine.
6. Failover / Switchover steps involved in case of a disaster on primary database
Due to some corruption / disaster, if we decide to failover by changing physical standby database as 'Primary' and the current primary database as 'Physical Standby', we need to follow the procedure below:
6.1 Database role change on Primary database (to Physical Standby)
a) On primary database login to SQLPLUS and execute the following command:
SELECT NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
b) If the column 'SWITCHOVER_STATUS' shows as 'PHYSICAL STANDBY', execute the following command:
alter database commit to switchover to physical standby;
c) If the column 'SWITCHOVER_STATUS' shows as 'SESSIONS ACTIVE', execute the following command:
alter database commit to switchover to physical standby with session shutdown;
d) Shutdown the database;
shutdown immediate;
6.2 Database role change on Physical Standby database (to Primary)
a) On physical standby database login to SQLPLUS and execute the following command:
SELECT NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
b) If the column 'SWITCHOVER_STATUS' shows as 'PRIMARY', execute the following command:
alter database commit to switchover to primary;
c) If the column 'SWITCHOVER_STATUS' shows as 'SESSIONS ACTIVE', execute the following command:
alter database commit to switchover to primary with session shutdown;
d) Stop the media recovery.
recover managed standby database cancel;
e) Shutdown the database.
shutdown immediate;
f) startup the database.
startup;
g) Execute the following command:
SELECT NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
In the result of the abpve query if
DATABASE_ROLE = PRIMARY
and
SWITCHOVER_STATUS = PHYSICAL STANDBY
then, it means that the actual physical standby database is now acting as the primary database after the failover.
The PROTECTION_MODE, PROTECTION_LEVEL depends on the kind of protection mode chosen at the time of the build.
6.3 Bringing up the actual primary database as the physical standby database
a) On the actual primary database, login to SQLPLUS and execute the following commands:
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER MANAGED STANDBY DATABASE (USING CURRENT LOGFILE) NODELAY DISCONNECT FROM SESSION;
b) Execute the following command:
SELECT NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
In the result of the above query if
DATABASE_ROLE = PHYSICAL STANDBY
and
SWITCHOVER_STATUS = PRIMARY
then, it means that the actual primary database is now acting as the physical standby database after the failover.
The PROTECTION_MODE, PROTECTION_LEVEL depends on the kind of protection mode chosen at the time of the build.
c) Perform a log swicth on the current primary and verify if the new archive sequence is being shipped to the physical standby database as mentioned in Step 5.
No comments:
Post a Comment