Configure Oracle Data Guard Part II
This is in continuation of Oracle Dataguard configuration part I which I posted long back and forgot to update next one.
One of my friend was looking for steps so I thought of finishing this (If you think I missed any post just leave comment and I’ll try to post on that topic)
Ensure that you have gone through Part I and finished following task
1. Primary Database is configured in archivelog mode
2. Configure LOG_ARCHIVE_DEST_2 on primary database
3. Create TNS alias (PROD_remote1) in tnsnames.ora on primary site
4. Install Oracle Database on Standby Site (Use same directory structure as on primary site)
* Dataguard allows you to configure primary & standby database with different directory structure but for simplicity this doc assume they are with same directory structure
Here are step by step Oracle dataguard cnfiguration in detail once again
1. Make sure your production database is in force logging.
Query from v$database if not set change to force logging by running
sqlplus> alter database force logging;
2. Set Primary Database in archivelog mode
If log mode is not “Archive Mode”, and automatic archival is not “Enabled”, you must set these parameters in your init<SID>.ora file:
log_archive_start = TRUE
log_archive_format = <format for database, using %s, %t>
log_archive_dest_1 = ‘LOCATION=<primary DB archive log dest> MANDATORY’
log_archive_dest_state_1 = enable
For these parameters to take effect, you must shut down your database, then re-start it in this manner:
sqlplus> startup mount
sqlplus> alter database archivelog
sqlplus> alter database open
3. Create standby redo logs
Query v$logfile ( If there is no standby redo log, create equal number & of same size as of online redo logs )
sqlplus> alter database add standby logfile group <> (‘<full path for first member>’,’<full path for second member’) size <> ; –
4. Define log_archive_dest_state_2 in primary database
open init<SID>.ora in primary server & add remote_archive_enable = true
—log_archive_dest_2 = ‘SERVICE=<standby_service_name> OPTIONAL REOPEN=60 LGWR AFFIRM SYNC’
—log_archive_dest_state_2 = enableIf log_archive_dest is specified in your init.ora file, you must comment out or remove that parameter.5. Add entries to your tnsnames.ora and listener.ora files for the standby database.
The tnsnames.ora file is found in $TNS_ADMIN<standby_service_name> = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(PORT=<same port as production>)
(HOST=<standby host name>))
(CONNECT_DATA=(SID=<same SID as production>)))The listener.ora file is also found in $TNS_ADMIN
<standby_service_name> =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
(PORT=<same port as production>)
(HOST=<standby host name>)))
SID_LIST_<standby_service_name>=(SID_LIST=
(SID_DESC=(SID_NAME=<same SID as production>)
(ORACLE_HOME=<path to database home>)))
Make sure the “standby_service_name” is the same in both the tnsnames.ora and listener.ora files.
6. Create the secondary database standby control file.
From SQL*Plus as sys or system, on the primary site environment, create a standby control file:
SQL> alter system archive log current;
SQL> alter database create standby controlfile as ‘<directory for control files>/stdbyctl.dbf’
Note the standby control file must have a name different from the primary database control file.
7. Restore the primary database to the database server at the secondary site
This step assumes that you have installed database on standby site with same directory structure, you can delete datafiles from database which is going to become standby site (or replace them from primary site as given below).
7.1 Shutdown your primary database
7.2 Copy your datafiles & online redologs including standby redologs
–
8. Copy the standby control file to the redundant control file directories.
On the standby database server, make redundant copies of the standby control file to the appropriate directories ( standby control file which you created in step 6 above ). Put them in the same directories your redundant production control files are kept. Log in as the database owner and use operating system utilities to make the copies.
9. Adjust the init<SID>.ora file for the standby database.
On the standby database server, edit your init<SID>.ora file to point to the standby control files by changing the CONTROL_FILES entry and set up your standby archive destination:
CONTROL_FILES = <directory 1 for control files>/stdbyctl.dbf, <directory 2 for control files>/stdbyctl.dbf, etc.
log_archive_start = true # if you want automatic archiving
FAL_SERVER = <PRIMARY_SID>
FAL_CLIENT = <TNS_ALIAS_STANDBY> (Defined above)
STANDBY_ARCHIVE_DEST = /prodarch/oracle/prodarch
STANDBY_FILE_MANAGEMENT = AUTO
10. Add entries to your tnsnames.ora and listener.ora files for the standby database on standby database.
<standby_service_name> = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(PORT=<same port as production>)
(HOST=<standby host name>))
(CONNECT_DATA=(SID=<same SID as production>)))
The listener.ora file is also found in $TNS_ADMIN
<standby_service_name> =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
(PORT=<same port as production>)
(HOST=<standby host name>)))
SID_LIST_<standby_service_name>=(SID_LIST=
(SID_DESC=(SID_NAME=<same SID as production>)
(ORACLE_HOME=<path to database home>)))
Make sure the “standby_service_name” is the same in both the tnsnames.ora and listener.ora files.
11. Start the listener on the secondary database server, for the standby database.
On the standby server, as the database owner, run the following operating system command :
$ lsnrctl start <standby_service_name
–
12. Mount the standby database
On the standby database server, from sqlplus
SQLPLUS> startup nomount
SQLPLUS > alter database mount standby database;
–
13. Place the standby database in managed recovery mode.
SQLPLUS> alter database recover managed standby database disconnect;
–
14. Modify the standby database listener.ora file ( If this parameter is not set )
Change the value of the HOST parameter in the production service entry of the listener.ora file on the standby database server to point to the secondary database server.
<production_service_name> =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
(PORT=<same port as production>)
(HOST=<standby host name>)))
SID_LIST_<production_service_name>=(SID_LIST=
(SID_DESC=(SID_NAME=<same SID as production>)
(ORACLE_HOME=<path to database home>)))
–
15. Check your protection mode .
Check from v$database ( on standby database )
If want to change to maximum availability mode by running
Sqlplus > alter database set standby database to maximize { PROTECTION | AVAILABILITY | PERFORMANCE }
Once you have set it to maximum availability mode , you can start your primary database .
Check your alert<SID>.log for any errors
–
Related