Primary ---- PROD1, PROD2
Stand by ----- STBY
- Check Primary Database is in archive log mode or not.
sql> archive log list;
If primary is not in archive mode then put in archive mode. Below are the steps to enable the archive mode.
1. ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;2. ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile;3. ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;
4. ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;5. srvctl stop database –d PROD
6. STARTUP MOUNT;7. ARCHIVE LOG START;8. ALTER DATABASE ARCHIVELOG;9. ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;10.SHUTDOWN IMMEDIATE;11.srvctl start database -d PROD2. Gather files and perform backup—
1. On the primary node, create a staging directory.
(mkdir –p /u01/oracle)
2. Create the same exact path on the standby host:
(mkdir –p /u01/oracle)
3. On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory.
(create pfile =’/u01/oracle’ from spfile;)
3. On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory.
1. rman target /
2. rman> BACKUP DEVICE TYPE DISK FORMAT '/u01/oracle/%U' DATABASE PLUS ARCHIVELOG;
3. rman> BACKUP DEVICE TYPE DISK FORMAT '$ORACLE_HOME/stage/%U' CURRENT CONTROLFILE FOR STANDBY;
4. Copy the contents of the staging directory on the RAC node to the staging directory on the standby host.
(scp –rf /u01/oracle/* oracle@standby.123.com:/u01/oracle)
2. CREATE THE PHYSICAL STANDBY INSTANCE AND DATABASE
1. Installed the Oracle_HOME on standby host (Install software only).
2. Create standby password file (orapwd file=$ORACLE_HOME/dbs/orapwSTBY password=******) NOTE --- Password should be same on PRIMARY and
STANDBY).
3. Copy and rename the primary database PFILE from the staging area on the standby host to the $ORACLE_HOME/dbs directory on the standby host. (cp
/u01/oracle/initPROD.ora $ORACLE_HOME/dbs/initSTBY.ora).
4. Modify the standby initialization parameter file copied from the primary node to remove RAC parameters and to include Data Guard parameters
*.cluster_database=false *.db_unique_name=STBY *.instance_name=STBY *.thread=1 *.undo_tablespace=UNDOTBS1 |
*.log_archive_config='dg_config= (STBY,PROD)' *.log_archive_dest_2='service=PROD1_SERV valid_for=(online_logfiles,primary_role) db_unique_name=STBY' *.db_file_name_convert='+DATA/PROD/', '+DATA/STBY/' *.log_file_name_convert='+DATA/PROD/', '+DATA/STBY/'' *.standby_file_management=auto *.fal_server='PROD1_SERV','PROD2_SERV' *.fal_client='STBY' *.service_names='STBY' |
*.background_dump_dest= $ORACLE_HOME/admin/STBY/bdump *.core_dump_dest= $ORACLE_HOME/admin/STBY/cdump *.user_dump_dest= $ORACLE_HOME/admin/STBY/udump *.audit_file_dest= $ORACLE_HOME/admin/STBY/adump *.dispatchers=STBYXDB |
5. Configure the ASM instance through DBCA. But for configure the ASM we need to enable the CSS (cluster synchronization service) service. Below are
the steps that how to enable the CSS service without using the CRS?
1. run localconfig add from root user ($ORACLE_HOME/bin). It will create CSS services. Check CSS services are up or not (ps –ef|grep css).
2. Run DBCA and configure the ASM instance.
.
6. Connect to the ASM instance on the standby host, and create a directory within the DATA disk group that has the same name as the
DB_UNIQUE_NAME of the physical standby database.
(ALTER DISKGROUP data ADD DIRECTORY '+DATA/STBY'; ).
7. Connect to the physical standby database, with the standby in the IDLE state, and create an SPFILE in the standby DATA disk group:
1. export ORACLE_HOME=path
2. export PATH=$PATH:$ORACLE_HOME/bin
3. export LD_LIBRARY_PATH=$ORACLE_HOME/lib
4. export ORACLE_SID=STBY
5. sqlplus ‘/as sysdba’
6. CREATE SPFILE='+DATA/STBY/spfileSTBY.ora' FROM PFILE='$ORACLE_HOME/dbs/initSTBY.ora';
8. In the $ORACLE_HOME/dbs directory on the standby host, create a PFILE that is named initoracle_sid.ora that contains a pointer to the SPFILE.
1. cd $ORACLE_HOME/dbs
2. vi initSTBY.ora and put the SPFILE entry (SPFILE=+DATA/STBY/spfileSTBY.ora)).
9. Create the dump directories on the standby host as referenced in the standby initialization parameter file.
(mkdir –p $ORACLE_HOME/admin/STBY/bdump)
10. CONFIGURE ORACLE NET SERVICES ON THE STANDBY (use netca and netmgr)
11. Modify the tnsnames.ora file on each node, including the primary RAC nodes and standby host, to contain all primary and standby net service names.
12. STARTUP NOMOUNT
13. From the standby host, duplicate the primary database as a standby into the ASM disk group.
1. rman target sys/oracle@PROD1_SERV auxiliary /
2. DUPLICATE TARGET DATABASE FOR STANDBY;
14. Connect to the physical standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as the primary
database online logs.
(SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M; )
15. Start managed recovery and real-time apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
3. CONFIGURE THE PRIMARY DATABASE FOR DATA GUARD
1. Configure the primary database initialization parameters to support both the primary and standby roles.
*.log_archive_config='dg_config=(STBY,PROD)'
*.log_archive_dest_2='service=STBY
valid_for=(online_logfiles,primary_role)
db_unique_name=STBY'
*.db_file_name_convert='+DATA/STBY/',’+DATA/PROD/'
*.log_file_name_convert='+DATA/STBY/',’+DATA/PROD/'
*.standby_file_management=auto
*.fal_server='STBY'
PROD1.fal_client='PROD1_SERV'
PROD2.fal_client='PROD2_SERV'
*.service_names=STBY
2. Create standby redo logs on the primary database to support the standby role. The standby redo logs are the same size as the primary database online logs. The recommended number of
standby redo logs is one more than the number of online redo logs for each thread.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;
4. VERIFY DATA GUARD CONFIGURATION
1. On the physical standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
2. On the primary database, issue the following SQL statement to force a log switch and archive the current online redo log file group:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
3. On the physical standby database, query the V$ARCHIVED_LOG view to verify that the redo data was received and archived on the standby database:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
