Data Guard Implementation
There are two ways to configure a Data Guard environment.
· Using Oracle9i Data Guard broker.
· Manual configuration of a Data Guard environment.
The best way to understand Data Guard implementation is to setup one manually.
1. The production database name is LIVENCSS.
2. One primary database instance called LIVENCSS on host PLUTO; one physical standby database instance called LIVENCSS on host NEPTUNE.
3. Listener livelistener is on host PLUTO, and pointed by TNS entry PLUTO.
4. Listener livelistener is on host NEPTUNE, and pointed by TNS entry NEPTUNE.
5. The purpose of TNS entry on PLUTO and NEPTUNE are used for LGWR/ARCH process to ship redo logs to the standby site, and for FAL process to fetch redo logs from the primary site.
6. Since Data Guard broker is not used here, we set dg_broker_start to false.
7. The protection mode is set to High Availability. Therefore, only local archive destination (log_archive_dest_1) is set to mandatory; the standby archive destination (log_archive_dest_2) is set to optional for LGWR process, with network transmission method of asynchronous and disk write option of no affirm.
8. The standby site is not using standby online redo logs. Therefore, the redo log reception option is archived logs.
Steps to Set Up a Data Guard Environment
The following eight steps show how to set up a Data Guard environment:
Step 1: The Preparation
- Plan the Data Guard environment.
- Understand the business requirements and demands
- Decide the number of standby databases (logical/physical)
- Decide file system layout for standby (If feasible, prefer identical layout of primary database)
- Ensure primary database is in ARCHIVELOG mode
- Choose the proper redo log size and groups
- Choose the proper protection mode
- Setup the initLIVENCSS.ora file for both primary and standby databases. (Parameter files are attached)
- Setup the listener.ora file for both primary and standby databases.
- Setup the tnsnames.ora file for both primary and standby sites.
- Standby Server: PDB net service on standby database.
- Primary database: STD1 net service on primary database.
Step 2: Back Up the Primary Database Datafiles
- Shut down the primary database (on PLUTO machine).
- Backup the primary database datafiles.
- Startup the primary database with the modified parameters file (new file is attached).
Step 3: Set Protection Mode and Create the Physical Standby Database Control File
- Ensure the primary database is in ARCHIVELOG mode and automatic archiving is enabled.
- Issue the following command to set the protection mode and create the standby control file:
SQL> alter database set standby database to maximize availability
SQL> alter database create standby controlfile as
'/dbdata1/LIVENCSS/cont01.ctl'
Step 4: Transfer the Datafiles and Control File to the Standby Site
Through FTP or NFS, with the help of SMG, transfer all files on NEPTUNE machine (preferable same file systems /dbdata1, /dbdata2 and /dbdata3.
Step 5: Start the Listeners on Both Primary and Standby Site
- Start the primary database listener (PLUTO).
lsnrctl start livelistener
- Start the standby database listener (NEPTUNE).
lsnrctl start listener
Step 6: Start the Standby Database (NEPTUNE)
- Set the correct Oracle environment. (.profile)
- Connect as sysdba.
SQL> connect user/password sysdba
- Bring the database in nomount mode first (with modified parameter file for standby database.)
SQL> startup nomount pfile=PFILENAME;
- Mount the standby database.
SQL> alter database mount standby database;
Step 7: Place the Standby Database in Managed Recovery Mode
- Issue the following command to bring the standby database in managed recover mode.
SQL> alter database recover managed standby database disconnect from session;
Step 8: Monitor the Log Transport Services and Log Apply Services
- Issue a few log switches on the primary database.
SQL> alter system switch logfile;
- Confirm the log files received on the standby archive destination.
- Check the standby alert log file to see if the new logs have applied to the standby database.
- Monitor the managed recovery.
SQL> select max(sequence#) from v$archived_log where applied=’YES’;
Switchover Steps
Unlike failover, a switchover operation is a planned operation. All the archive logs required bringing the standby to the primary's point in time need to be available. The primary database's online redo logs also must be available and intact. During switchover operation, primary and standby databases switch roles. The old standby database (NEPTUNE) becomes the new primary, and the old primary (PLUTO) becomes the new standby database.
The following are steps for switchover operation:
Step 1: Switchover Preparation for Formal Primary Database
- End all activities on the primary and standby database
- Check primary database switchover status
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
------------------------- ----------------------
PRIMARY TO STANDBY
- Initiate the switchover operation on the primary database
SQL> alter database commit to switchover to physical standby;
Step 2: Shut Down the Primary Database and Bring Up as the New Standby Database
- Shutdown the primary database normally
SQL> shutdown normal;
- Modify the former primary database's initialization file
- Add the following two parameters. These two parameters can also be set on the primary database ahead of time for future switchover operation.
- We have one more parameter for that is already configure for this scenario.
fal_server = "LIVENCSS"
fal_client = "LIVENCSS"
- Remove parameters log_archive_dest_2 and log_archive_dest_state_2. Or, just defer it if you like
- Bring the former primary database in mount mode
SQL> startup nomount [new parameter file];
SQL> alter database mount standby database;
Step 3: Switchover Preparation for the Former Standby Database
- Check standby database switchover status
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
------------------------- ----------------------
PHYSICAL STANDBY TO PRIMARY
- Initiate the switchover operation on the standby database
SQL> alter database commit to switch over to physical primary;
Step 4: Shutdown the Standby Database and Bring Up as the New Primary Database
- Shutdown the standby database
SQL> shutdown normal;
§ Modify the former standby database's initialization file
§ We have separate parameter for that is already configure for this scenario.
fal_server = "LIVENCSS"
fal_client = "LIVENCSS"
- Add parameters log_archive_dest_2 and log_archive_dest_state_2
- Bring up the former standby database as the new primary database
SQL> startup [new parameter file];
Step 5: Add Temp Tablespace
- Issue the following command to add TEMP tablespace
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/prod/temp01.dbf' SIZE 3072M reuse AUTOEXTEND OFF;
Step 6: Put the New Standby Database in Managed Recovery Mode
- Issue the following command on the new standby database.
SQL> alter database recover managed standby database disconnect;
Step 7: Change TNS Entry for the New Primary Database
- Change the TNS entry on all application hosts to point to the new primary
Stb-new =
(description =
(address = (protocol=tcp) (host = pluto) (port = 1555)
(connect_data = (sid = LIVENCSS))
)
Failover Steps
In 9.2.0, you can gracefully Failover even without standby redo log files. Issue the following command on the standby site to Failover to a new primary database.
SQL> alter database recover managed standby database skip standby logfiles;
This will apply all available redo and make the standby available to become a Primary. Complete the operation by switching the standby over to the primary role with the following command:
SQL> alter database commit to switchover to primary;
The old primary (PLUTO) has to be discarded and can not be used as the new standby database. You need to create a new standby database by backing up the new primary and restore it on host PLUTO. The time to create a new standby database exposes the risk of having no standby database for protection.
