OVERVIEW...2
PREPARATION STEPS...3
IMPLEMENTATION STEPS...4
STEPS TO BE DONE ON THE PRIMARY SERVER...4
S
TEP1: B
ACKUPTHEPRIMARYDATABASEDATAFILES...4
S
TEP3: C
ONFIGUREO
RACLEN
ET...5
S
TEP4: C
ONFIGUREDATABASEINITIALISATION PARAMETERFILE...5
STEPS TO BE DONE ON THE STANDBY SERVER...6
S
TEP1: C
ONFIGUREO
RACLEN
ET...6
S
TEP2: C
ONFIGURETHEPHYSICALSTANDBYDATABASEINITIALISATION PARAMETER...6
S
TEP2: R
ESTOREDATABASEF
ILESFROMTHEB
ACKUPL
OCATION...6
S
TEP3: S
TARTA
UTOMATICS
TANDBYDATABASER
ECOVERY...7
SWITCHOVER DATABASE ROLE...8
S
TEP1: C
HECKTHEPRIMARYDATABASEREADINESSFORS
WITCHOVER...8
S
TEP2: S
WITCHINGTHEPRIMARYTOSTANDBY...8
S
TEP3: S
WITCHFROMSTANDBYTOPRIMARY...9
S
TEP4: S
TARTA
UTOMATICS
TANDBYDATABASER
ECOVERY...10
APPENDIX A: COMMANDS...11
APPENDIX B: PROTECTION MODES...12
APPENDIX C: DATA GUARD USEFUL COMMANDS...14
S
WITCHOVERD
ATABASER
OLE...14
R
EADO
NLYD
ATABASER
OLE...14
F
AILOVERD
ATABASER
OLE– I
NCASEOFTOTALDISASTER...15
S
TARTS
TANDBYR
ECOVERY...15
Version 5
Page 1
Overview
These Note Describe a physical standby database configuration for a Real Application Clusters (RAC) primary environment.
A physical standby database is physically identical to the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The physical standby database is updated by performing recovery. It can either be recovering data or open for read-only reporting.
The environment that we have is as shown in the diagram below,
WAN
WAN
IBM P595 AIX 5.3 ML3 16x1.9GHz 32GB RAM IBM P670 AIX 5.3 ML3 16x1500 MHz 16GB RAM IBM P595 AIX 5.3 ML3 16x1.9GHz 32GB RAM Private Network SANSwitches SAN Switches
KFR Datacenter
Primary Site
OLAYA Datacenter
DR Site
DS4800 Storage
Preparation Steps
Done
Version 5
Page 3
Implementation Steps
Assumption:
1. We have 'etrade' database having two instances etrade1 and etrade2 running on two different server on AIX 5.3 platform.
2. The RMAN directory for backup is /orahome/backup with enough space for the full database backup
3. ASM database is created in the standby environment with similar names like productions and the init.ora parameter "processes" equal to 150
4. hosts file is update in the primary and standby machines correctly
Steps To Be Done On the Primary Server
Step 1: Backup the primary database datafiles1. Shutdown the primary database RAC instances
srvctl stop database –d etrade
2. Start up one instance in mount state, e.g. etrade1
# export ORACLE_SID=etrade1 # rman target /
RMAN> STARTUP MOUNT
3. Backup database using RMAN
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/orahome/backup/DF_%s_%p_%c';
RMAN> BACKUP DATABASE;
4. Reopen the Primary database.
RMAN> ALTER DATABASE OPEN;
5. Create the standby database control file by issuing the following command,
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/orahome/backup/etrade_stdbycf.ctl';
6. Transfer the datafiles and control files from /orahome/backup directory to the physical standby machine/site with same directory path (so that the restore database step can find it).
Step 3: Configure Oracle Net Done
Step 4: Configure database initialisation parameter file Done
Note: Special Consideration if Primary Database is in RAC Environment Done
F
Version 5
Page 5
Steps To Be Done On The Standby Server
Step 1: Configure Oracle NetDone
Step 2: Configure the physical standby database initialisation parameter
Use initialization parameter of the primary site as a base for the standby database init.ora after performing the below changes on it.
Done
The LOG_ARCHIVE_DEST & LOG_ARCHIVE_DUPLEX_DEST parameters should be removed from the init file.
Note 1: when using a RAC based init.ora then remove all cluster related entries, e.g. cluster_database, remote_listener, instance_number, thread…
Note 2: make sure to create all dump directories, e.g. adump, udump, cdump, bdump Step 2: Restore database Files from the Backup Location
7. Startup Database after creating a password file using the same sys password used in production site (the format of file name is orapwSID for unix platform, while it is PWDSID.ora for NT)
set ORACLE_SID=etradedr
orapwd file=/orahome/oracle/product/10.2.0/db/dbs/orapwetradedr password=<password> entries=5
SQL> CONNECT sys/password as sysdba SQL> STARTUP NOMOUNT PFILE=prodinit.ora 8. Mount the standby database
SQL> ALTER DATABASE MOUNT STANDBY DATABASE; 9. Restore Database Files
set ORACLE_SID=etradedr Rman target /
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN>Restore Database;
10. Add a new standby redo log file group to a standby database using the following formula (in our case the it will be 6)
(number of log files per thread + 1) * number of threads
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('+DGREDO1/standby_redo05a.log') SIZE 400M, GROUP 6 ('+DGREDO1/standby_redo06a.log') SIZE 400M, GROUP 7 ('+DGREDO1/standby_redo07a.log') SIZE 400M; SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 ('+DGREDO2/standby_redo08a.log') SIZE 400M, GROUP 9 ('+DGREDO2/standby_redo09a.log') SIZE 400M, GROUP 10 ('+DGREDO2/standby_redo10a.log') SIZE 400M;
Step 3: Start Automatic Standby database Recovery. 11. Start standby database recovery,
Before this step set LOG_ARCHIVE_DEST_STATE_2=Enable On primary Database (scope=both)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; (will only send data after logfile switch) For realtime sending of logfile data use:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Version 5
Page 7
SwitchOver Database Role
Switching database roles is always initiated from the primary database and is always completed on a standby database.
Step 1: Check the primary database readiness for Switchover
In order to switch the primary database to standby all sessions should be disconnected. When issuing the statement below the output should be “TO STANDBY”.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS
--- TO STANDBY
Step 2: Switching the primary to standby
This can be done for the primary in open or mount phase, one standby database must be active in the Data Guard configuration.
1. The standby database must be mounted and have proper network connectivity. If the standby database is open for read-only access, the standby switchover operation will take longer, only one session allowed:
If this is not the case first all sessions have to be closed (terminated), an indication is that the above query for the primary shows:
SWITCHOVER_STATUS ---
SESSIONS ACTIVE
After terminating the other sessions the status will show: TO STANDBY. 2. Issue the following command on the primary database,
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
The command closes the primary database (sets it in nomount state), archives any unarchived log files and applies them to the physical standby database, adds an end-of-redo marker to the header of the last log file being archived, creates a backup of the current control file (udump destination) and converts the current control file into a standby control file.
3. Switch the primary database to standby role SQL> shutdown immediate;
SQL> Startup nomount
SQL> alter database mount standby database; Step 3: Switch from standby to primary
Standby instance should be mounted, only one session is allowed and the primary must have switched role to standby and the standby database must have applied the switchover notification from the primary database.
1. Check that the standby database is ready to be switched to primary role, SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS ---
SESSIONS ACTIVE
Terminate all other sessions until result is:
SWITCHOVER_STATUS ---
TO PRIMARY
2. Issue the following command on the standby database,
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
This command guarantees the last log file has been received and applied through the end-of-redo marker, closes the database if it has been opened for read-only transactions and converts the standby control file to the current control file.
3. Switch the standby database to primary role
Add the following parameters to the standby database init file,
log_archive_dest_2='SERVICE=EXIMDBD1 LGWR SYNC MANDATORY REOPEN=30' log_archive_dest_state_2 =ENABLE
Perform the following, SQL> shutdown SQL> Startup
Version 5
Page 9
Step 4: Start Automatic Standby database Recovery. 12. Start standby database recovery,
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Note: The standby database must have been in managed recovery mode prior to starting the switchover operation, so that the primary database switchover operation request can be coordinated. If managed recovery was not active, or the primary database switchover notification was unable to be processed, the switchover operation will not be allowed to proceed.
Remarks
A failover should not be performed except in case of emergencies. After failover the standby becomes primary and loses standby functionality. A new standby databse has to be setup.
APPENDIX A: Commands
Command Description
RECOVER MANAGED STANDBY DATABASE CANCEL; To stop the Automatic Recovery ALTER DATABASE OPEN READ ONLY; To open the database in READ ONLY
mode STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Startup the standby database and start automatic recovery
SELECT * FROM V$STANDBY_LOG; Query v$managed_standby to see the status of processes involved in the configuration.
To determine if real-time apply is enabled, query the RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view. It will contain the value MANAGED REAL TIME
APPLY when real-time apply is enabled, as shown in the following example: SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ; RECOVERY_MODE
---MANAGED REAL TIME APPLY
Version 5
Page 11
APPENDIX B: Protection Modes
Maximum Protection Mode
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 at least one remote standby redo log. For multiple-instance RAC databases, Data Guard shuts down the primary database if it is unable to write the redo records to at least one properly configured database instance. The maximum protection mode requires that at least one standby instance has a standby redo log and the LGWR, SYNC, and AFFIRM attributes be used on the LOG_ARCHIVE_DEST_n parameter for this destination.
Maximum Availability Mode
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 at least one remote standby redo log. 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.
Like maximum protection mode, the maximum availability mode requires that you: Configure standby redo log files on at least one standby database.
Set the SYNC, LGWR, and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least 1 standby database.
Maximum Performance Mode
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 commitment of 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.
The maximum performance mode enables you to either set the LGWR and ASYNC attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database destination. If the primary database fails, you can reduce the amount of data that is not received on the standby destination by setting the LGWR and ASYNC attributes.
Version 5
Page 13
APPENDIX C: Data Guard Useful Commands
Switchover Database Role 1) ON PRIMARY
a. Select switchover_status from v$database;
SWITCHOVER_STATUS
---
TO STANDBY
b. ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN; c. shutdown immediate;
d. Startup nomount
e. alter database mount standby database;
f. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
2) ON STANDBY
a. SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS
--- TO PRIMARY
b. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; - Add to init file
c. ALTER SYSTEM SET log_archive_dest_2='SERVICE=EXIMDBD1 LGWR SYNC OPTIONAL REOPEN=30';
d. ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE – Add to init file e. shutdown
f. startup
3) ON PRIMARY
a. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Read Only Database Role 1) ON PRIMARY
a. ALTER SYSTEM SWITCH LOGFILE;
4) ON STANDBY
a. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; B. ALTER DATABASE OPEN READ ONLY;
Failover Database Role – In case of total disaster
a. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE Cancel; b. ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
c. SHUTDOWN IMMEDIATE; d. STARTUP MOUNT
e. ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; f. ALTER DATABASE OPEN;
Start Standby Recovery
a. startup nomount
b. ALTER DATABASE MOUNT STANDBY DATABASE;
c. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
