Oracle Data Guard (Standby Database) ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage
The standby database will be kept in MANAGED RECOVERY MODE in normal times. The standby database can be opened in Read only mode for reporting purposes and will be put back in managed recovery mode. By keeping the standby database in managed recovery mode, it applies the archived redo logs automatically.
Basic Requirements:
ØThe primary database must run in ARCHIVELOG mode and Force Logging enabled.
ØUse the same database release on the primary and standby databases. The operating system on the primary and standby sites must be the same, but the operating system release does not need to be the same. In addition, the standby sites can use a different directory structure from the primary site.
ØThe hardware and operating system architecture on the primary and standby locations must be the same.
ØThe primary database can be a single instance database or a multi-instance Real Application Clusters (RAC) database. The standby databases can be single instance databases or multi-instance Real Application Clusters databases, and these standby databases can be a mix of both physical and logical types.
ØThe hardware (for example, the number of CPUs, memory size, storage configuration) can be different between the primary and standby systems. If the standby system is smaller than the primary system, we may have to restrict the work that can be done on the standby system after a switchover operation.
ØEach primary database and standby database must have its own control file.
ØIf you place your primary and standby databases on the same system, you must adjust the initialization parameters accordingly.
ØTo protect against un logged direct writes in the primary database that cannot be propagated to the standby database, turn on FORCE LOGGING at the primary database before taking data file backups for standby creation. Keep the database in FORCE LOGGING mode as long as the standby database is required.
ØSet the COMPATIBLE parameter to 10.2.0.0 or higher.
ØIf flashback database was not enabled prior to the failover, and the original primary database needs to be brought back as a standby after the failover, it has to be recreated from a backup copy of the new primary database.
Setting up and maintaining Oracle Dataguard:
Step1. Setup the same Unix user accounts (Oracle software owner) and groups (dba)
on the standby server, preferably with the same uid and the same gid.
Step2. Recreate the same Oracle directory structure on the standby server as on the primary
server.
Step3. Install Oracle software on the standby server preferably in the same location as on the
primary server.
Step4. Verify the Primary database log mode and switch to archive log mode if needed.
a. Verify the database log mode by,
SQL> archived log list;
b. Switch to archive log mode by,
b1. Modify the following init.ora parameters
*.LOG_ARCHIVE_DEST_1='LOCATION=xxxxxxx'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_format='$ORACLE_SID_%t_%s_%r.arc'
b2. SQL> shutdown immediate;
b3. SQL>startup mount
b4. SQL>alter database archivelog;
b5. SQL>alter database open;
Step5. Verify forced logging and enable if needed in Primary database.
a. Verify the forced logging by,
SQL> select force_logging from v$database;
b. Enable the forced logging by,
SQL>alter database force logging;
Step6. Copy the init.ora file from the Primary host server to the standby host server.
Step7. Copy the /var/opt/oracle/oratab file to the standby server.
Step8. Copy the password file $ORACLE_HOME/dbs/orapw$ORACLE_SID to the standby
server. You may have to create a new password file using the orapwd utility with same
SYS password if needed.
Step9. Create the standby control file in the primary database as SYS.
SQL> alter database create standby controlfile as ‘/xxxxxxx/stbycf.ctl';
Step10: Switch the redo logs few times
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
Step11. Shutdown the Primary database and copy all the database related files to the
Secondary database server.
a. Shutdown the Primary database.
b. Copy the datafiles.
c. Copy the online redolog files.
d. Copy the standby control file
e. Copy the archived log files
Step12. Make the needed changes in the listener.ora and tnsnames.ora files in both Primary and
Secondary servers.
a. Create listeners if they are not available.
b. Add entries for the services in tnsnames.ora files in both the servers.
c. Enable dead connection detection by setting SQLNET.EXPIRE_TIME=2 in SQLNET.ORA parameter file on the standby server.
Step13. Configure the init.ora on primary database
*.LOG_ARCHIVE_DEST_2='SERVICE=standby_db_servicename optional reopen=60 lgwr sync affirm'
*. LOG_ARCHIVE_DEST_STATE_2=enable
*. DG_BROKER_START=FALSE
*.STANDBY_FILE_MANAGEMENT=AUTO
Step14. Configure the init.ora files on Standby
*.CONTROL_FILES='$standby_controlfile_location/stbycf.ctl'
*.STANDBY_ARCHIVE_DEST='LOCATION=$archivelog_location/’
*. LOG_ARCHIVE_DEST_1= 'LOCATION=$archivelog_location/ ‘
*.LOG_ARCHIVE_DEST_2='SERVICE=primary_db_servicename optional reopen=60 lgwr sync affirm'
*. LOG_ARCHIVE_DEST_STATE_1=ENABLE
*. LOG_ARCHIVE_DEST_STATE_2=defer
*. DG_BROKER_START=FALSE
*.STANDBY_FILE_MANAGEMENT=AUTO
*.AUDIT_TRAIL=FALSE
Step15. Start the Standby Listener and Standby Database in nomount and then mount the database.
a. Start the Listener
$lsnrctl start
b.Start the standby database in nomount mode
SQL> connect / as sysdba
SQL> startup nomount pfile=init$ORACLE_SID.ora
c. Mount the standby database.
SQL> alter database mount standby database;
Step16. Place the Standby Database in Managed Recovery Mode.
SQL> alter database recover managed standby database disconnect from session;
Step17. Start the Listener and Primary database.
a. Start the Listener
$lsnrctl start
b.Start the Primary database using pfile and create the spfile.
SQL> connect / as sysdba
SQL> startup
SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>startup
Step18. Check the archived log transport and apply by, running the below sql in both Primary and
Secondary.
SQL>select thread#,max(sequence#) “last_applied_log” from v$log_history group by thread#;
Also check the alert log files in both the databases.
Step19. To open the standby database in READ ONLY mode
SQL> alter database recover managed standby database cancel;
SQL>alter database open read only;
Step20. To switch the standby database to managed recovery from Read only mode
a. Terminate all active user sessions.
b. SQL> alter database recover managed standby database disconnect from session;
SWITCHOVER(ROLE CHANGE):
A switchover is a reversible role transition between the primary database and one of its standby databases. Oracle Data Guard switchover and failover operations are not invoked automatically. We must initiate switchover or failover operations manually using a SQL statement or a Data Guard broker interface.
Step1. Identify the initialization parameters that we must change to complete the role
transition. Change the init.ora parameters accordingly.
Step2.
locations. Each location in the Data Guard configuration should have connectivity
through Oracle Net to the primary database and to all associated standby
databases.
Step3. Verify that there are no active users connected to the databases.
Step4. For switchover operations involving a physical standby database, the primary
database instance is open and the standby database instance is mounted.
The standby database that we plan to transition to the primary role must be
mounted before we begin the switchover operation. Ideally, the physical standby
database will also be actively recovering archived redo logs when the database
roles are switched. If the physical standby database is open for read-only access,
the switchover operation still will take place, but will require additional time.
Step5. Verify whether the primary will be able to perform a switchover .
SQL> select switchover_status from v$database;
‘TO STANDBY’ indicates that it is possible to switch over to standby from primary.
‘SESSIONS ACTIVE’ indicates active sessions in the primary database and we have to
terminate all the active sql sessions.
Step6. Convert the primary database to standby database.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Step7. Shut down and restart the primary database. (the recently switched over).
SQL> shutdown immediate;
Make sure audit_trail=none and log_archive_dest_state_2=defer
SQL> startup nomount pfile=init$ORACLE_SID
Mount the database as a physical standby database:
SQL> alter database mount;
Defer the remote archive destination on the new standby database.
SQL> alter system set log_archive_dest_state_2=defer;
Note: At this point in the switchover process, both databases are configured as standby databases.
Step8. Verify the switchover status in the V$DATABASE view on old standby db.
SQL> select switchover_status from v$database;
The TO PRIMARY value of the SWITCHOVER_STATUS column indicates the standby
database is about to switch from the standby role to the primary role.
Step9. Switch the physical standby database (old standby) role to the primary role.
Copy the existing archived logs from the old primary database to the old standby database.
Run this on the old standby db to switch it to primary.
SQL> alter database commit to switchover to primary;
Step10. Shut down and restart the new primary database.
SQL> shutdown immediate;
(Modify audit_trail, LOG_ARCHIVE_DEST_STATE_2 parameters)
SQL> startup pfile=init$ORACLE_SID.ora;
Enable remote archiving on the new primary to the new standby database.
SQL> alter system set log_archive_dest_state_2=enable;
Step11. Start managed recovery operations and log apply services on the new standby.
SQL> alter database recover managed standby database disconnect from session;
Step12. From new Primary Database begin sending redo data to the standby databases
SQL> alter system archive log start;
SQL> alter system switch logfile;
FAILOVER: DO ONLY ON EMERGENCY
A failover, transitions a standby database to the primary role in response to a failure of the primary database.
During a failover operation, a standby database transitions to the primary role and the old primary database is rendered unable to participate in the configuration. Depending on the protection mode under which the old primary database was operating before the failover, there might be little or no data loss during a failover. A failover is typically used only when a primary database becomes unavailable and there is no possibility of restoring it to service within a reasonable amount of time.
If possible, before performing a failover, you should transfer as much of the available and unapplied primary database redo data as possible to the standby database.
After performing a failover operation,
1. Re-create the failed primary database as a new standby database using a copy of the new primary database.
2. Add the database to the configuration as a new standby database.
3. Perform a switchover to transition the database to the primary role and restore the configuration to its original pre-failure state.
Before starting a failover operation:
1. Identify the parameters that must be changed to complete the role transition.
2. Verify that there is network connectivity between the primary and standby locations.
3. Place the physical standby database in maximum performance mode by issuing the following statement on the physical standby database:
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Graceful Failover with No Data Loss:
1. Initiate the graceful failover operation on the physical standby database.
SQL> alter database recover managed standby database finish force;
Check the managed recovery by,
SQL> select process from v$managed_standby;
2. Change the standby database into the new primary database by issuing the following statement:
SQL> alter database commit to switchover to primary;
SQL> alter database open;
SQL> Shutdown immediate;
(Backup the database if possible)
SQL> Startup;
3. Register the missing redo logs by copying from the old primary database.
SQL> alter database register logfile ‘log file name’;
Graceful Failover with Unavoidable Data Loss:
1. Begin the graceful failover and managed recovery operations.
SQL> alter database recover managed standby database finish skip
2. Switch the physical standby database to the primary role.
SQL> alter database commit to switchover to primary;
3. Drop old standby redo logs and create a standby database from the new primary database.
Graceful Failover with Minimal Data Loss:
1. Run
SQL> alter database recover managed standby database finish;
2. Run the below sql in physical standby database.
SQL> alter database commit to switchover to primary;
3. Copy the archive log files from the old primary database and register them.
SQL> alter database register logfile ‘log file name’;
Maintaining Standby Database:
A. Adding a new tablespace or data file to the primary database:
1. Create the tablespace on the primary database as usual.
2. Issue SQL>alter system switch logfile; on primary db.
3. Issue SQL>recover managed standby database cancel;
4. Issue the below command only if the standby_file_management= manual;
Create the data file on the standby database by issuing
SQL>alter database create datafile ‘/path/filename.dbf’ as ‘/path/filename.dbf’;
5. Place the standby database in managed recovery mode.
SQL>alter database recover managed standby database disconnect from session;
B. Renaming Datafiles on the Primary Database:
Datafile renames on the primary database do not take effect at the standby database until we refresh the standby database control file.
1.After renaming the file on the primary site, connect to the standby site. Stop the managed recovery process and shutdown the standby database.
2.Rename the file on the host using operating system utility.
3.Start and mount the standby database.
4. Use ALTER DATABASE RENAME FILE statement to rename the datafile in the controlfile of the standby database.
5. Start the managed recovery process.
C. Resetting or clearing Unarchived redo logs on Primary database:
Clear Log files at the primary database by issuing
SQL>alter database clear unarchived logfile;
( opening the primary database using the resetlogs option will INVALIDATE the standby database. We need to recreate the standby database.)
D. Refreshing the standby Database Control file:
Refresh the standby control file after making major structural changes to the primary database, such as adding or dropping file.
1. On Standby database,
SQL>recover managed standby database cancel;
2. Shutdown standby database.
SQL> Shutdown immediate;
3. On Primary database,
SQL> Alter database create standby controlfile as ‘filename’;
4. Copy the standby control file and archive log files to the standby db.
5. start up nomount and Mount the standby database.
SQL> startup nomount pfile=init$sid.ora;
SQL> alter database mount standby database;
6. Place it in managed recovery mode.
SQL> alter database recover managed standby database disconnect from session;
No comments:
Post a Comment