Data Guard Setup Step by Step

Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

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)

 )

 )

  1. 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’;

  1. 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 &

  1. 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; 

Leave a Reply

Your email address will not be published. Required fields are marked *