just follow the below steps top setup dataguard:
Environment Server Database Name
Production Primary 192.168.31.100 ORACLE
Production Standby 192.168.31.101 ORACLE
2.2 Setup the Primary Site (192.168.31.100)
Assumptions: The db_unique_name parameter has to be set to ORACLE_A in the initORACLE.ora.
Step Procedure
1.Logon to the dm3cvs server has the oradpi user
Confirm that the ORACLE_SID and ORACLE_BASE environment variables are set properly
ORACLE_BASE= /u01/app/oracle
ORACLE_SID= ORACLE
$ env | grep ORA
2.Start a sqlplus session connected as sysdba
$ sqlplus / as sysdba
SQL> show parameter db_unique_name
Complete the following step only if: db_unique_name is not set to ORACLE_A
- Create pfile from spfile
- SQL> create pfile=’/u01/app/oracle/admin/ORACLE/pfile/initORACLE.ora’ from spfile=’/d01/app/oracle/product/12.2.0/dbs/spfileORACLE.ora’;
- Modify the db_unique_name parameter in the initORACLE.ora
- Shut down the database
SQL> shutdown immediate
- Startup using newly created and modified pfile
SQL> startup pfile=’/u01/app/oracle/admin/ORACLE/pfile/initORACLE.ora’;
- Create spfile from pfileSQL> create spfile from pfile=’/u01/app/oracle/admin/ORACLE/pfile/initORACLE.ora’;
- Confirm Modification
SQL> show parameter db_unique_name
- Shutdown and Startup (to use spfile so following commands will not fail)
SQL> shutdown
SQL> startup
3 Apply Data Guard configuration
SQL> alter database force logging;
SQL> alter system set log_archive_config=’DG_CONFIG=(ORACLE_A,ORACLE_B)’ scope=both;
For the following command, ensure that the log_archive_dest location folder is created
SQL> HOST mkdir -p /backup/raddpi/arch/ORACLE/BK_STB
SQL> alter system set log_archive_dest_1=’LOCATION=/backup/raddpi/arch/ORACLE/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORACLE_A’ scope=both;
SQL> alter system set log_archive_dest_2=’SERVICE=ORACLE_B LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORACLE_B’ scope=both;
SQL> alter system set log_archive_dest_state_1=’ENABLE’ scope=both;
SQL> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;
SQL> alter system set remote_login_passwordfile=’EXCLUSIVE’ scope=spfile;
SQL> alter system set log_archive_format=’%t_%s_%r.arc’ scope=spfile;
SQL> alter system set standby_file_management=auto scope=both;
SQL> alter system set log_archive_max_processes=3 scope=spfile;
SQL> alter system set log_archive_min_succeed_dest=1 scope=both;
SQL> alter system set fal_server=ORACLE_B scope=both;
SQL> alter system set fal_client=ORACLE_A scope=both;
4.Update pfile with spfile (will be used later for the standby database)
SQL> create pfile=’/u01/app/oracle/admin/ORACLE/pfile/initORACLE.ora’ from spfile=’/u01/app/oracle/product/12.2.0/dbs/spfileORACLE.ora’;
5.Backup the Primary database using this rman script. (See appendix).
Verify if the database is in archive log mode with the following SQL:
SQL> select name, log_mode from v$database;
If the log_mode = “NOARCHIVELOG” then execute the following SQL.
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> select name, log_mode from v$database;
Create rman directory (if it doesn’t exist)
$ mkdir -p /u01/app/oracle/admin/ORACLE/rman
Copy the backup_db_for_standby.rcv rman script from the appendix to the rman folder
Execute the RMAN procedure.
$ rman target / cmdfile=backup_db_for_standby.rcv
Backup files will be located in the /backup/raddpi/arch/ORACLE/BK_STB directory.
6.Add the following entries to the tnsnames.ora file located in (/u01/app/oracle/product/12.2.0/network/admin) on the dm3cvs server
ORACLE_A =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLE)
)
)
ORACLE_B =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLE)
)
)
6 Make sure the following entries in the listener.ora file located in (/u01/app/oracle/product/12.2.0/network/admin) are the same as shown below. If not then add these entries to the listener.ora file on the dm3cvs server
LISTENER_ORACLE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.100)(PORT = 1521)
)
SID_LIST_LISTENER_ORACLE =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.2.0)
(SID_NAME = ORACLE)
)
)
7. Check the status of the Listener
$ lsnrctl status LISTENER_ORACLE
8. Depending of the message you get you might need to Stop and or Start the Listener
$ lsnrctl stop LISTENER_ORACLE
$ lsnrctl start LISTENER_ORACLE
9. Add standby redo log
$ sqlplus / as sysdba
SQL> alter database add standby logfile
group 5 (‘/u02/oradata/ORACLE/standby_redo01a.log’,
‘/u03/oradata/ORACLE/standby_redo01b.log’) SIZE 200M;
SQL> alter database add standby logfile
group 6 (‘/u02/oradata/ORACLE/standby_redo02a.log’
,’/u03/oradata/ORACLE/standby_redo02b.log’) SIZE 200M;
SQL> alter database add standby logfile
group 7 (‘/u02/oradata/ORACLE/standby_redo03a.log’
,’/u03/oradata/ORACLE/standby_redo03b.log’) SIZE 200M;
SQL> alter database add standby logfile
group 8 (‘/u02/oradata/ORACLE/standby_redo04a.log’
,’/u03/oradata/ORACLE/standby_redo04b.log’) SIZE 200M;
Notes: The standby logfile sizes must be identical on the primary and standby databases and the number of standby redo logfile groups should be at least the same as the redo logfile groups on the primary database.
10. Bounce the database
SQL> shutdown immediate
SQL> startup
SQL> exit
2.3 Set up the Standby Site (192.168.31.101)
Step Procedure Login to the dcocu2 server as the oradpi user
Confirm that the ORACLE_SID and ORACLE_BASE environment variables are set properly
ORACLE_BASE= /u01/app/oracle
ORACLE_SID= ORACLE
$ env | grep ORA
1.Obtain the backup sets from the Primary site.
$ mkdir -p /backup/raddpi/arch/ORACLE/BK_STB
$ cd /backup/raddpi/arch/ORACLE/BK_STB
$ sftp oradpi@192.168.31.100
Password: ************
sftp> cd /backup/raddpi/arch/ORACLE/BK_STB
sftp> ls
sftp> get <filename1>
sftp> get <filename2>
……
sftp> get <filenamex>
sftp> exit
Notes: Get all the files starting with inc0 and cf. You can also use the mget command with a combination of asterisk (ex: mget inc0*.*)
Obtain the archived logs from the Primary site.
cd /backup/raddpi/arch/ORACLE
sftp oradpi@192.168.31.100
Password: ************
sftp> cd /backup/raddpi/arch/ORACLE
sftp> ls
sftp> get <filename1>
sftp> get <filename2>
……
sftp> get <filenamex>
sftp> exit
Notes: Need to get the recent archived logs, these generated after finishing backup with RMAN. Most of the archive logs are already included in the RMAN backup, but if the database is still open with activities, some archive logs following the last one included in the RMAN backup could be necessary to complete recovery. You can also use the mget command with a combination of asterisk (ex: mget *.arc)
2. Obtain the pfile from the Primary (dm3cvs) site.
From the dcuco2 server execute the following commands
$ cd /u01/app/oracle/admin/ORACLE/pfile
$ sftp oradpi@192.168.31.100
Password: *************
sftp> cd /u01/app/oracle/admin/ORACLE/pfile
sftp> get initORACLE.ora
sftp> exit
3. From the file obtained at the previous step modify the following entries to initORACLE.ora :
*.db_domain=’***’
*.db_name=’ORACLE’
*.db_unique_name=’ORACLE_B’
*.fal_client=’ORACLE_B’
*.fal_server=’ORACLE_A’
*.log_archive_config=’DG_CONFIG=(ORACLE_A,ORACLE_B)’
*.log_archive_dest_1=’LOCATION=/backup/raddpi/arch/ORACLE/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORACLE_B’
*.log_archive_dest_2=’SERVICE=ORACLE_A LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORACLE_A’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.arc’
*.log_archive_max_processes=3
*.log_archive_min_succeed_dest=1
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
4. Add the following entries to the tnsnames.ora file located in
(/u01/app/oracle/product/12.2.0/network/admin)
ORACLE_A =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLE)
)
)
ORACLE_B =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLE)
)
)
- Make sure the following entries in the listener.ora file located in (/u01/app/oracle/product/12.2.0/network/admin) is as the same as shown below.
If not then add these entries to the listener.ora file
LISTENER_ORACLE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.101)(PORT = 1521))
)
SID_LIST_LISTENER_ORACLE =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.2.0)
(SID_NAME = ORACLE)
)
)
6.Start the Listener
$ lsnrctl start LISTENER_ORACLE
7. Create password file (enter the sys password in the password parameter)
$ /u01/app/oracle/product/12.2.0/bin/orapwd File=/u01/app/oracle/product/12.2.0/dbs/orapwORACLE password=<password
8. Create spfile
SQL> sqlplus / as sysdba
SQL> create spfile=’/d01/app/oracle/product/12.2.0/dbs/spfileORACLE.ora’ from pfile=’/u01/app/oracle/admin/ORACLE/pfile/initORACLE.ora’;
- Restore database
SQL> startup nomount
SQL> alter system set disk_asynch_io=false scope=spfile;
SQL> exit
Create rman directory (if it doesn’t exist)
$ mkdir -p /u01/app/oracle/admin/ORACLE/rman
Copy the restore_db_for_standby.rcv script from the appendix to the rman folder
Execute the RMAN procedure.
$ cd /u01/app/oracle/admin/ORACLE/rman
$ nohup rman target sys/<password>@ORACLE_A auxiliary sys/<password>@ORACLE_B log=restore_db_for_standby.log
cmdfile=restore_db_for_standby.rcv &
- Add standby redo log
sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database add standby logfile group 5 (‘/u02/oradata/ORACLE/standby_redo01a.log’
,’/u03/oradata/ORACLE/standby_redo01b.log’) SIZE 200M;
SQL> alter database add standby logfile group 6 (‘/u02/oradata/ORACLE/standby_redo02a.log’
,’/u03/oradata/ORACLE/standby_redo02b.log’) SIZE 200M;
SQL> alter database add standby logfile group 7 (‘/u02/oradata/ORACLE/standby_redo03a.log’
,’/u03/oradata/ORACLE/standby_redo03b.log’) SIZE 200M;
SQL> alter database add standby logfile group 8 (‘/u02/oradata/ORACLE/standby_redo04a.log’
,’/u03/oradata/ORACLE/standby_redo04b.log’) SIZE 200M;
11.Add TEMP tablespace
SQL> alter database open read only;
SQL> alter tablespace “TEMP” add tempfile
‘/u03/oradata/ORACLE/temp01.dbf’ SIZE 2048M;
SQL> shutdown immediate
Applying Redo Data to Standby Database
Step Procedure
1 Standby database: 192.168.31.101
$ sqlplus / as sysdba
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session parallel 4;
2 Primary database:192.168.31.100
SQL> alter system archive log current;
Appendixes
Appendix A : backup_db_for_standby.rcv
RMAN script to be used at Primary server:
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
setlimit channel d1 kbytes 2097150;
setlimit channel d2 kbytes 2097150;
setlimit channel d3 kbytes 2097150;
setlimit channel d4 kbytes 2097150;
backup
incremental level 0
database
tag inc0_db_open
filesperset=5
format ‘/backup/raddpi/arch/ORACLE/BK_STB/inc0_df_%U.dat’
include current controlfile for standby;
sql “alter system switch logfile”;
sql “alter system archive log current”;
backup
format ‘/backup/raddpi/arch/ORACLE/BK_STB/cf_standby.%T.%u’
current controlfile for standby;
sql “alter system switch logfile”;
sql “alter system archive log current”;
backup
format ‘/backup/raddpi/arch/ORACLE/BK_STB/cf_backup.%T.%u’
current controlfile;
}
Appendix B : restore_db_for_standby.rcv
RMAN restore script to be used at Standby server:
run {
allocate auxiliary channel d1 type disk;
allocate auxiliary channel d2 type disk;
allocate auxiliary channel d3 type disk;
allocate auxiliary channel d4 type disk;
duplicate target database for standby nofilenamecheck dorecover;
}
