Dataguard Setup with Broker

Container Database: cdbst -> cdbstdg
Primary: odasys12
StandBy: odasys11

1.) Primary – Check Archive Log Mode:

sqlplus / as sysdba

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17

2.) Primary – FORCE LOGGING

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;

3.) Primary – Standby Redo Log Files (SUM of Redo Logs + 1 with the max size of redo log files)

col member format A60
select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_ CON_ID
———- ——- ——- ———————————————————— — ———-
1 ONLINE +REDO/CDBST/ONLINELOG/group_1.334.942934501 NO 0
2 ONLINE +REDO/CDBST/ONLINELOG/group_2.287.942934507 NO 0
3 ONLINE +REDO/CDBST/ONLINELOG/group_3.292.942934511 NO 0

SQL> select max(bytes) / (1024*1024) mb from v$log;

MB
———-
1024

ALTER DATABASE ADD STANDBY LOGFILE (‘+REDO/CDBST/ONLINELOG/standby_redo01.log’) SIZE 1g;
ALTER DATABASE ADD STANDBY LOGFILE (‘+REDO/CDBST/ONLINELOG/standby_redo02.log’) SIZE 1g;
ALTER DATABASE ADD STANDBY LOGFILE (‘+REDO/CDBST/ONLINELOG/standby_redo03.log’) SIZE 1g;
ALTER DATABASE ADD STANDBY LOGFILE (‘+REDO/CDBST/ONLINELOG/standby_redo04.log’) SIZE 1g;

4.) Primary – Enable Flashback Database
ALTER DATABASE FLASHBACK ON;

5.) Primary – Init Parameters

SQL> show parameter db_name

NAME TYPE VALUE
———————————— ———– ——————————
db_name string cdbst

SQL> show parameter db_unique_name

NAME TYPE VALUE
———————————— ———– ——————————
db_unique_name string cdbst

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

6.) Primary/StandBy – Listener.ora

Specific Listener for DRP
PORT: 1527
Name: LISTENER_DG
ORACLE_HOME: /u01/app/oracle/product/12.1.0.2/dbhome_1

srvctl add listener -l LISTENER_DG -o $ORACLE_HOME -p 1527
srvctl start listener -l LISTENER_DG -n odasys12
srvctl start listener -l LISTENER_DG -n odasys11

a.) Primary – include into Listener.ora

SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cdbst_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
(SID_NAME = cdbst)
)
)

lsnrctl reload LISTENER_DG
lsnrctl status LISTENER_DG

Service “cdbst_DGMGRL” has 1 instance(s).
Instance “cdbst”, status UNKNOWN, has 1 handler(s) for this service…

— Include the Host/Port into the LOCAL_LISTENER to listener on the new port
alter system set LOCAL_LISTENER=”(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=odasys12)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=odasys12)(PORT=1527)))” scope=BOTH;

b.) StandBy – include into Listener.ora

SID_LIST_LISTENER_DG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cdbstdg_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
(SID_NAME = cdbstdg)
)
)

lsnrctl reload LISTENER_DG
lsnrctl status LISTENER_DG

Service “cdbstdg_DGMGRL” has 1 instance(s).
Instance “cdbst”, status UNKNOWN, has 1 handler(s) for this service…

7.) TNS Entries – Include this TNS for both servers

CDBSTP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = odasys12.bgl.lu)(PORT = 1527))
(CONNECT_DATA =
(SERVER = DEDICATED)
(sid = cdbst)
)
)

CDBSTDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = odasys11.bgl.lu)(PORT = 1527))
(CONNECT_DATA =
(SERVER = DEDICATED)
(sid = cdbstdg)
)
)

Note: After configure both tnsnames.ora test with tnsping the entries.

8.) Duplicate
StandBy Database Name: cdbstdg

a.) On the primary database:

create pfile=’initcdbstdg.ora’ from spfile;

scp initcdbstdg.ora odasys11:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs
scp orapwcdbst odasys11:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs

b.) On the Standby Server:
cd /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs
cp orapwcdbst orapwcdbstdg

vi initcdbstdg.ora
:%s/cdbst/cdbstdg/g

– Check the Parameters to see if there are some specific configurations to fix
*.db_name=’cdbst’ –> must keep the same for both sites
*.db_unique_name=’cdbstdg’ –> include

mkdir -p /u01/app/oracle/admin/cdbstdg/adump

c.) Preparing the ASM to Receive the CDB and PDBs:

– Controlfile:
inside of the init.ora for the standby there is:
*.control_files=’+REDO/CDBST/CONTROLFILE/current.331.942934501′

asmcmd
cd +REDO
mkdir CDBSTDG
cd CDBSTDG
mkdir CONTROLFILE

Change the controlfile parameter to:

*.control_files=’+REDO/CDBSTDG/CONTROLFILE/control_cdbstdg.ctl’

– Datafiles on PRIMARY:
Directories to CONVERT CLAUSE
col DIRECTORY format a60
select distinct substr(file_name,1,instr(file_name,’/’,-1)) directory from cdb_data_files;

+DATA/NSCDB2/DATAFILE/
+DATA/NSCDB1/datafile/
+DATA/CDBST/DATAFILE/
+DATA/NSCDB1/DATAFILE/
+DATA/NSCDB3/DATAFILE/

select distinct substr(member,1,instr(member,’/’,-1)) directory from v$logfile;

+REDO/CDBST/ONLINELOG/

– Create the directories on ASM to receive the files

+DATA/DG/NSCDB2/DATAFILE/
+DATA/DG/NSCDB1/datafile/
+DATA/DG/CDBST/DATAFILE/
+DATA/DG/NSCDB1/DATAFILE/
+DATA/DG/NSCDB3/DATAFILE/

+REDO/DG/CDBST/ONLINELOG/

– create the file DupDG.rman

cd /home/oracle/scripts

vi DupDG.rman

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;

d.) Start the standby instance:

– Include into /etc/oratab
cdbstdg:/u01/app/oracle/product/12.1.0.2/dbhome_1:N

. oraenv
cdbstdg

sqlplus / as sysdba
create spfile=’+DATA/DG/spfilecdbstdg.ora’ from pfile;
– change the initcdbstdg.ora
— > SPFILE=’+DATA/DG/spfilecdbstdg.ora’

startup nomount;

alter system set LOCAL_LISTENER=”(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=odasys11)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=odasys11)(PORT=1527)))” scope=BOTH;
alter system SET db_file_name_convert=’+DATA/NSCDB2/DATAFILE/’,’+DATA/DG/NSCDB2/DATAFILE/’,’+DATA/NSCDB1/datafile/’,’+DATA/DG/NSCDB1/datafile/’,’+DATA/CDBST/DATAFILE/’,’+DATA/DG/CDBST/DATAFILE/’,’+DATA/NSCDB1/DATAFILE/’,’+DATA/DG/NSCDB1/DATAFILE/’,’+DATA/NSCDB3/DATAFILE/’,’+DATA/DG/NSCDB3/DATAFILE/’ scope=spfile;
alter system SET log_file_name_convert=’+REDO/CDBST/ONLINELOG/’,’+REDO/DG/CDBST/ONLINELOG/’ scope=spfile;
alter system SET job_queue_processes=0 scope=both;

shut immediate
startup nomount;

— On this case as the CONVERT parameter is used, is need to set on the primary also;
alter system SET db_file_name_convert=’+DATA/DG/NSCDB2/DATAFILE/’,’+DATA/NSCDB2/DATAFILE/’,’+DATA/DG/NSCDB1/datafile/’,’+DATA/NSCDB1/datafile/’,’+DATA/DG/CDBST/DATAFILE/’,’+DATA/CDBST/DATAFILE/’,’+DATA/DG/NSCDB1/DATAFILE/’,’+DATA/NSCDB1/DATAFILE/’,’+DATA/DG/NSCDB3/DATAFILE/’,’+DATA/NSCDB3/DATAFILE/’ scope=spfile;
alter system SET log_file_name_convert=’+REDO/DG/CDBST/ONLINELOG/’,’+REDO/CDBST/ONLINELOG/’ scope=spfile;

— If the parameter needs to be changed after the BROKER is already configured use the commands below:
edit database ‘cdbst’ set property ‘DbFileNameConvert’=’+DATA/DG/NSCDB2/DATAFILE/,+DATA/NSCDB2/DATAFILE/,+DATA/DG/NSCDB1/datafile/,+DATA/NSCDB1/datafile/,+DATA/DG/CDBST/DATAFILE/,+DATA/CDBST/DATAFILE/,+DATA/DG/NSCDB1/DATAFILE/,+DATA/NSCDB1/DATAFILE/,+DATA/DG/NSCDB3/DATAFILE/,+DATA/NSCDB3/DATAFILE/’;
edit database ‘cdbst’ set property ‘LogFileNameConvert’=’+REDO/DG/CDBST/ONLINELOG/,+REDO/CDBST/ONLINELOG/’;
show database ‘cdbst’ ‘InconsistentProperties’;

—> Test the connections using TNS
rman target sys/welcome1@CDBSTP AUXILIARY sys/welcome1@odasys11:1527/cdbstdg_DGMGRL
— It should works in both servers

e.) Execute the Duplicate
cd $ORACLE_HOME/dbs

rman target sys/welcome1@CDBSTP AUXILIARY sys/welcome1@odasys11:1527/cdbstdg_DGMGRL @DupDG.rman log DupDG.log

Finished recover at 04/05/2017 14:23:22
Finished Duplicate Db at 04/05/2017 14:23:49

9.) Broker

— Both Servers:
ALTER SYSTEM SET dg_broker_start=true;

— PRIMARY
dgmgrl sys/welcome1@cdbst
CREATE CONFIGURATION cdbst_config AS PRIMARY DATABASE IS cdbst CONNECT IDENTIFIER IS cdbstp;
ADD DATABASE cdbstdg AS CONNECT IDENTIFIER IS cdbstdg MAINTAINED AS PHYSICAL;
ENABLE CONFIGURATION;
SHOW CONFIGURATION;
show database cdbst;
show database cdbstdg;

— Choose the best option for the Environment: maxavailability (Enabled)
edit database ‘cdbst’ set property ‘LogXptMode’=’SYNC’;
edit database ‘cdbstdg’ set property ‘LogXptMode’=’SYNC’;
edit configuration set protection mode as maxavailability;

— To change to maxperformance
edit configuration set protection mode as maxperformance;
edit database ‘cdbst’ set property ‘LogXptMode’=’ASYNC’;
edit database ‘cdbstdg’ set property ‘LogXptMode’=’ASYNC’;

10.) Configure FAL / FLASHBACK

– FAL

Primary:
alter system set fal_client=cdbstp scope=both;
alter system set fal_server=cdbstdg scope=both;

StandBy:
alter system set fal_server=cdbstp scope=both;
alter system set fal_client=cdbstdg scope=both;

– FLASHBACK

dgmgrl sys/welcome1
edit database ‘cdbstdg’ set state=’APPLY-OFF’;
exit
sqlplus / as sysdba
alter database flashback on;
exit
dgmgrl sys/welcome1
edit database ‘cdbstdg’ set state=’APPLY-ON’;

11.) Including the database into the ocr

srvctl add database -d cdbstdg -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -r physical_standby -s mount -a DATA,REDO,RECO -p ‘+DATA/DG/spfilecdbstdg.ora’
srvctl add instance -d cdbstdg -i cdbstdg -n odasys11

– test on Standby:

srvctl stop database -d cdbstdg
srvctl start database -d cdbstdg
dgmgrl sys/welcome1@cdbstp
show configuration

12.) Switchover

— Check if is possible execute the switchover

validate database verbose ‘cdbst’;
Database Role: Primary database
Ready for Switchover: Yes

— PRIMARY

salplus / as sysdba

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 NSCDB1 READ WRITE NO
4 NSCDB2 READ WRITE NO
5 NSCDB3 READ WRITE NO

SQL> alter session set container=NSCDB1;

Session altered.

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
USERS
TS_TEST

SQL> create user dr identified by dr;

User created.

SQL> grant dba to dr;

Grant succeeded.

SQL> alter user dr default tablespace TS_TEST;

User altered.

SQL> create table dr.teste as select * from dba_users;

Table created.

SQL> select count(*) from dr.teste;

COUNT(*)
———-
21

exit

dgmgrl sys/welcome1@cdbstp
switchover to cdbstdg;
Performing switchover NOW, please wait…
Operation requires a connection to instance “cdbstdg” on database “cdbstdg”
Connecting to instance “cdbstdg”…
Connected as SYSDBA.
New primary database “cdbstdg” is opening…
Oracle Clusterware is restarting database “cdbst” …
Switchover succeeded, new primary is “cdbstdg”

DGMGRL> show configuration

Configuration – cdbst_config

Protection Mode: MaxAvailability
Members:
cdbstdg – Primary database
cdbst – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 7 seconds ago)

— StandBy (Now Primary)

sqlplus / as sysdba

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 NSCDB1 READ WRITE NO
4 NSCDB2 READ WRITE NO
5 NSCDB3 READ WRITE NO

alter session set container=NSCDB1;

SQL> select count(*) from dr.teste;

COUNT(*)
———-
21
–> Switching Back
odasys12:

dgmgrl sys/welcome1

DGMGRL> show configuration;

Configuration – cdbst_config

Protection Mode: MaxAvailability
Members:
cdbstdg – Primary database
cdbst – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 57 seconds ago)

DGMGRL> validate database cdbstdg;

Database Role: Primary database

Ready for Switchover: Yes

Flashback Database Status:
cdbstdg: Off

DGMGRL> switchover to cdbst;
Performing switchover NOW, please wait…
Operation requires a connection to instance “cdbst” on database “cdbst”
Connecting to instance “cdbst”…
Connected as SYSDBA.
New primary database “cdbst” is opening…
Oracle Clusterware is restarting database “cdbstdg” …
Switchover succeeded, new primary is “cdbst”
DGMGRL> show configuration

Configuration – cdbst_config

Protection Mode: MaxAvailability
Members:
cdbst – Primary database
cdbstdg – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 11 seconds ago)

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 NSCDB1 READ WRITE NO
4 NSCDB2 READ WRITE NO
5 NSCDB3 READ WRITE NO

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s