Steps to Setup / Configure Data Guard and Broker on Oracle 12c RAC ASM Multi Node Database

_____________________________________________________________________________________________________________________

Assumptions
Primary
Scan              : CLUSCANPROD01
DB Name           : ADMPROD
DB Unique Name    : ADMPROD
Instances         : ADMPROD1 & ADMPROD2
PORT              : 1521
Version & Storage : Oracle 12c with ASM

Standby
Scan              : CLUSCANSTY01
DB Name           : ADMPROD
DB Unique Name    : ADMPRODSTBY
Instances         : ADMPROD1 & ADMPROD2
PORT              : 1521
Version & Storage : Oracle 12c with ASM

Setup Primary Database
1.      Enable force logging
SQL> select force_logging from V$DATABASE;

FORCE_LOGGING
-------------
NO

SQL> alter database force logging;

Database altered.

SQL>  select force_logging from V$DATABASE;

FORCE_LOGGING
---------------------------------------
YES

2.      Setup Primary init Parameters

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ADMPROD,ADMPRODSTBY)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ADMPROD' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ADMPRODSTBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ADMPRODSTBY' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile sid='*';
alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*';
alter system set FAL_SERVER=ADMPRODSTBY scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='ADMPRODSTBY','ADMPROD' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='ADMPRODSTBY','ADMPROD' scope=spfile sid='*';

Note: Concept of convert parameter is as follows
In primary site: Standby Name -> primary Name('ADMPRODSTBY','ADMPROD')
In Standby Site: Primary Name -> Standby Name('ADMPROD','ADMPRODSTBY')

The above commands which specified as scope=spfile requires database bounce; but we have a database bounce planned in step 3.

3.      Change the primary database to archive log mode.

This step requires database bounce which will make the above alter system commands to effect. In case your database is already in archive log mode you have to bounce explicitly to make above changes to effect.

srvctl stop database -d ADMPROD
sqlplus / as sysdba
startup mount;
alter database archivelog;
alter database open;
archive log list;
scripts]$ srvctl start database -d ADMPROD
scripts]$ srvctl status database -d admprod

4.      Create directory for standby arch log in Primary and Create standby archive logs

Concepts of standby redo log file groups
a.    The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.
b.    Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads
But in my example case I have created same number standby redo log groups as redo log groups

ASMCMD> mkdir +DATA/ADMPROD/STANDBYLOG
ASMCMD> mkdir +DATA/ADMPROD/PASSWORD
ASMCMD> mkdir +DATA/ADMPROD/DATAGUARDCONFIG


alter system set standby_file_management=manual scope=both sid='*';

alter database add standby logfile thread 1 group 7 ('+DATA/ADMPROD/STANDBYLOG/stbylog_group_07_a.log','+DATA/ADMPROD/STANDBYLOG/stbylog_group_07_b.log') size 500M;
alter database add standby logfile thread 2 group 8 ('+DATA/ADMPROD/STANDBYLOG/stbylog_group_08_a.log','+DATA/ADMPROD/STANDBYLOG/stbylog_group_08_b.log') size 500M;
alter database add standby logfile thread 1 group 9 ('+DATA/ADMPROD/STANDBYLOG/stbylog_group_09_a.log','+DATA/ADMPROD/STANDBYLOG/stbylog_group_09_b.log') size 500M;
alter database add standby logfile thread 2 group 10 ('+DATA/ADMPROD/STANDBYLOG/stbylog_group_10_a.log','+DATA/ADMPROD/STANDBYLOG/stbylog_group_10_b.log') size 500M;

alter system set standby_file_management=auto scope=both sid='*';

SQL>  select member,type from v$logfile;

MEMBER                                                       TYPE
------------------------------------------------------------ -------
+DATA/ADMPROD/ONLINELOG/group_1.615.923394927             ONLINE
+DATA/ADMPROD/ONLINELOG/group_1.614.923394929             ONLINE
+DATA/ADMPROD/ONLINELOG/group_2.607.923394929             ONLINE
+DATA/ADMPROD/ONLINELOG/group_2.606.923394931             ONLINE
+DATA/ADMPROD/ONLINELOG/group_3.626.923394933             ONLINE
+DATA/ADMPROD/ONLINELOG/group_3.627.923394933             ONLINE
+DATA/ADMPROD/ONLINELOG/group_4.628.923394935             ONLINE
+DATA/ADMPROD/ONLINELOG/group_4.629.923394937             ONLINE
+DATA/ADMPROD/STANDBYLOG/stbylog_group_07_a.log           STANDBY
+DATA/ADMPROD/STANDBYLOG/stbylog_group_07_b.log           STANDBY
+DATA/ADMPROD/STANDBYLOG/stbylog_group_08_a.log           STANDBY
+DATA/ADMPROD/STANDBYLOG/stbylog_group_08_b.log           STANDBY
+DATA/ADMPROD/STANDBYLOG/stbylog_group_09_a.log           STANDBY
+DATA/ADMPROD/STANDBYLOG/stbylog_group_09_b.log           STANDBY
+DATA/ADMPROD/STANDBYLOG/stbylog_group_10_a.log           STANDBY
+DATA/ADMPROD/STANDBYLOG/stbylog_group_10_b.log           STANDBY

16 rows selected.

5.      Backup the primary database for standby
rman target / nocatalog
run
{
     sql "alter system switch logfile";
     allocate channel ch1 type disk format '/u01/DBBKUP/ADMPROD/Primary_bkp24Sep_for_stndby_%U';
     backup database;
     backup current controlfile for standby;
     sql "alter system archive log current";
}

6.      Create parameter for standby database. This will be modified with standby database related parameters
SQL> create pfile='/u01/DBBKUP/ADMPROD/standby_pfile.txt' from spfile;

File created.
7.      Copy Backup and pfile (step 5 & 6) to standby database server.
$  pwd
/u01/DBBKUP/ADMPROD
$ scp * oracle@node1:/u01/DBBKUP/ADMPROD

8.      Set TNS entries in both primary nodes
Open $ORACLE_HOME/network/admin/tnsnames.ora file and add tns entries.

vi /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
ADMPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CLUSCANPROD01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ADMPROD)
    )
  )
ADMPRODSTBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CLUSCANSTY01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ADMPRODSTBY)
    )
  )

Setup Standby Database
9.      Modify below parameter in standby_pfile.txt file which we created and copied in step 6 & 7

*.audit_file_dest='/u01/app/oracle/admin/ADMPRODSTBY/adump'
*.control_files='+DATA/ADMPRODSTBY/CONTROLFILE/control01.ctl','+DATA/ADMPRODSTBY/CONTROLFILE/control02.ctl'
*.db_file_name_convert='ADMPROD','ADMPRODSTBY'
*.db_unique_name='ADMPRODSTBY'
*.fal_server='ADMPROD'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ADMPRODSTBY'
*.log_archive_dest_2='SERVICE=ADMPROD ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ADMPROD'
*.log_file_name_convert='ADMPROD','ADMPRODSTBY'
*.remote_listener='CLUSCANSTY01:1521'

10. Setting up ASM & Directory

Filesystem
mkdir /u01/app/oracle/admin/ADMPRODSTBY/adump
ASM
ASMCMD> mkdir ADMPRODSTBY
ASMCMD> cd ADMPRODSTBY
ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG STANDBYLOG PASSWORD DATAGUARDCONFIG

11. Startup (nomount mode) standby instance in RAC Node 1 by setting the environment variables and new parameter file.

ORACLE_SID=ADMPROD1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

sqlplus / as sysdba
startup nomount pfile='/u01/DBBKUP/ADMPROD/standby_pfile.txt'

12. Restore primary database backup in standby node

rman target sys/paygate1@admprod auxiliary /
DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;


13. Add tnsnames.ora entries in Standby Server

vi /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
ADMPRODSTBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CLUSCANSTY01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ADMPRODSTBY)
    )
  )

ADMPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CLUSCANPROD01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ADMPROD)
    )
  )
14. Setting up the Password file. Name: orapwd<dbname>
Check where is your database password file located. If it is 12c RAC database which is created by DBCA, the password file generally to ASM location. You can check the location by using following command.

$ srvctl config database -d ADMPROD|grep Password
Password file: +DATA/ADMPROD/PASSWORD/orapwadmprod
Primary
orapwd file='+DATA/ADMPROD/PASSWORD/orapwadmprod' password=paygate1 force=y ignorecase=y entries=5 dbuniquename=ADMPROD

Standby
In Standby database initially you create the password file in the file system and further you can move to ASM when the ASM is ready with both instance and spfile.
orapwd file='$ORACLE_HOME/dbs/orapwADMPROD1' password=paygate1 force=y ignorecase=y entries=5

15. Start the recovery in Standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Switch the logfile in Primary
SQL> alter system switch logfile;

System altered.

Check the error if any by using following query in primary
set pages 1000 lines 120
select DEST_NAME,INST_ID,ERROR from gV$ARCHIVE_DEST_STATUS where DEST_NAME='LOG_ARCHIVE_DEST_2';

16. Create SPFILE in Standby database.

create spfile='+DATA' from pfile='/u01/DBBKUP/ADMPROD/standby_pfile.txt';

Validate the file in the Standby ASM
ASMCMD> pwd
+DATA/ADMPRODSTBY/PARAMETERFILE
ASMCMD> ls
spfile.428.923407977
Get the SPFILE location from the ASM as below and update the content of the parameter (init) file

/u01/app/oracle/product/12.1.0/db_1/dbs/initADMPROD1.ora with
spfile='+DATA/ADMPRODSTBY/PARAMETERFILE/spfile.428.923407977'

[ dbs]$ cat /u01/app/oracle/product/12.1.0/db_1/dbs/initADMPROD1.ora
spfile='+DATA/ADMPRODSTBY/PARAMETERFILE/spfile.428.923407977'

17. Copy init and password file from standby node 1 to node 2. Note the init file name with instance numbers.
$ scp /u01/app/oracle/product/12.1.0/db_1/dbs/initADMPROD1.ora oracle@node2:/u01/app/oracle/product/12.1.0/db_1/dbs/initADMPROD2.ora
$ scp /u01/app/oracle/product/12.1.0/db_1/dbs/orapwADMPROD1 oracle@node2:/u01/app/oracle/product/12.1.0/db_1/dbs/orapwADMPROD2

18. Add the database to cluster using srvctl command
Note add the DB Unique name to the cluster using srvctl command.

·         srvctl add database -d ADMPRODSTBY -n ADMPROD -o /u01/app/oracle/product/12.1.0/db_1 -r physical_standby
·         srvctl add instance -d ADMPRODSTBY -i ADMPROD1 -n node1
·         srvctl add instance -d ADMPRODSTBY -i ADMPROD2 -n node2

19. Create the adump directory in Standby Node 2
mkdir /u01/app/oracle/admin/ADMPRODSTBY/adump

20. Move the password file to the ASM in standby database
orapwd file='+DATA/ADMPRODSTBY/PASSWORD/orapwadmprodstby' password=paygate1 force=y ignorecase=y entries=5 dbuniquename=ADMPRODSTBY

21. Bounce the database using srvctl
$ srvctl stop database -d admprodstby
$ srvctl start database -d admprodstby

22. Following queries can be used to validate the database.
a.      To check the database mode and status.
SQL> select name, open_mode, database_role from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ADMPROD   READ ONLY            PHYSICAL STANDBY
ADMPROD   READ ONLY            PHYSICAL STANDBY

b.     To check the standby database gap
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     26                    26          0
         2                      7                     7          0

c.      To check the Primary log_archive_dest status
select DEST_NAME,INST_ID,ERROR from gV$ARCHIVE_DEST_STATUS where DEST_NAME='LOG_ARCHIVE_DEST_2'

DEST_NAME                         INST_ID ERROR
------------------------------ ---------- -----------
LOG_ARCHIVE_DEST_2                      2
LOG_ARCHIVE_DEST_2                      1

23. To change the standby protection mode to maximum availability
Primary:
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ONLPRODSTBY SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ADMPRODSTBY' scope=both sid='*';

alter database set standby database to maximize availability;

SQL> select protection_mode,name, open_mode, database_role from gv$database;

PROTECTION_MODE      NAME      OPEN_MODE            DATABASE_ROLE
-------------------- --------- -------------------- ----------------
MAXIMUM AVAILABILITY ADMPROD   READ WRITE           PRIMARY
MAXIMUM AVAILABILITY ADMPROD   READ WRITE           PRIMARY

Standby:
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ADMPROD SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ADMPRODSTBY' scope=both sid='*';
alter database set standby database to maximize availability;

SQL> select protection_mode,name, open_mode, database_role from gv$database;

PROTECTION_MODE      NAME      OPEN_MODE            DATABASE_ROLE
-------------------- --------- -------------------- ----------------
MAXIMUM AVAILABILITY ADMPROD   READ ONLY WITH APPLY PHYSICAL STANDBY

MAXIMUM AVAILABILITY ADMPROD   READ ONLY WITH APPLY PHYSICAL STANDBY

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google