DB - Data Guard 12c - Create Physical Standby
Have been thinking for awile to post about Data Guard, more specifically about to created a physical standby. So, today is the day, the aim of this post is not to destille Data Guard, for better undestanding lets recap some Data Guard concepts. Data Guard allows database replication sending and applying redo log info from a site to another, keeping in that way the data replicated and available in the case the main site is not available.
Data Guard potential is huge, could be used not only as DR solution but also for other business activities, as make a database available for testing or proper business reporting ( I mean to run reports in live data).
Keeping the subject at high level, it has 2 database roles :
primary – active database for business transactions
standby – secondary database (physical, logical, snapshot)
To create any standby type the the first step is to create a bit by bit database, that type od database is know as physical standby. From there its possible to create a logical or a snapshot standby [these two are out of this post scope]. It has a few aways to create a standby database, some of them are complex. In this post i'm showing the one that in my opinion is the easy way and the simplest on, in commnd line. To use the broker.
As its a demo i'm using a single VM, so in this case both the databases are in the same server, I will also use the same listener for both.
Hostname : oretail.example.com
Primary : PRODDB
Physical Standby : DRSITECDB
Steps to follow
1 . PRODDB - Check and setup primary configurations
2 . NETWORK – Setup listener and tns
3 . DRSITECDB – Create Physical Standby
4 . DGMGRL – Create Broker
5 . DGMGRL – Test
Data Guard potential is huge, could be used not only as DR solution but also for other business activities, as make a database available for testing or proper business reporting ( I mean to run reports in live data).
Keeping the subject at high level, it has 2 database roles :
primary – active database for business transactions
standby – secondary database (physical, logical, snapshot)
To create any standby type the the first step is to create a bit by bit database, that type od database is know as physical standby. From there its possible to create a logical or a snapshot standby [these two are out of this post scope]. It has a few aways to create a standby database, some of them are complex. In this post i'm showing the one that in my opinion is the easy way and the simplest on, in commnd line. To use the broker.
As its a demo i'm using a single VM, so in this case both the databases are in the same server, I will also use the same listener for both.
Hostname : oretail.example.com
Primary : PRODDB
Physical Standby : DRSITECDB
Steps to follow
1 . PRODDB - Check and setup primary configurations
2 . NETWORK – Setup listener and tns
3 . DRSITECDB – Create Physical Standby
4 . DGMGRL – Create Broker
5 . DGMGRL – Test
1 . PRODDB - Check and setup primary configurations
In this section is shown some usefull and key information about primary database and how to setup the primary for data guard.
Are 3 tasks imporant tasks to stetup a data guard environment
- activate force logging
- activate the archive log mode
- add standby log files
Are 3 tasks imporant tasks to stetup a data guard environment
- activate force logging
- activate the archive log mode
- add standby log files
[oracle@oretail Desktop]$ tail /etc/oratab
#..
PRODCDB:/u01/app/oracle/product/12.1.0/dbhome_1:N
[oracle@oretail Desktop]$ . oraenv
ORACLE_SID = [PRODCDB] ? PRODCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oretail Desktop]$ sqlplus / as sysdba
SQL> set pages 50
SQL> set lines 200
SQL> col name form a10
SQL> col db_unique_name form a15
SQL> col log_mode form a15
SQL> col switchover_status form a20
SQL> col force_logging form a20
SQL> select name, db_unique_name, switchover_status, log_mode, flashback_on , force_logging from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS LOG_MODE FLASHBACK_ON FORCE_LOGGING
---------- ------------- -------------------- ------------- ------------------ --------------------
PRODCDB PRODCDB NOT ALLOWED NOARCHIVELOG NO NO
-- Enable logging
SQL> alter database force logging;
Database altered.
-- Enaable archivelog mode
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 2924640 bytes
Variable Size 973082528 bytes
Database Buffers 520093696 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
-- Check again
SQL> set pages 50
SQL> set lines 200
SQL> col name form a10
SQL> col db_unique_name form a15
SQL> col log_mode form a15
SQL> col switchover_status form a20
SQL> col force_logging form a20
SQL> select name, db_unique_name, switchover_status, log_mode, flashback_on , force_logging from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS LOG_MODE FLASHBACK_ON FORCE_LOGGING
---------- --------------- -------------------- --------------- ------------------ --------------------
PRODCDB PRODCDB NOT ALLOWED ARCHIVELOG NO YES
-- addd standby logs
SQL> col member form a100
SQL> select l.group#, l.bytes, f.type, f.member from
v$log l , v$logfile f where l.group#=f.group#
union all
select l.group#, l.bytes, f.type, f.member
from v$standby_log l , v$logfile f where l.group#=f.group#;
GROUP# BYTES TYPE MEMBER
------ ---------- ------- ----------------------------------------------------
3 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log3.log
3 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log3.log
2 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log2.log
2 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log2.log
1 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log1.log
1 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log1.log
6 rows selected..
SQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_1.log','/u01/app/oracle/fra/PRODCDB/stdb_1.log') size 52428800;
SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_2.log','/u01/app/oracle/fra/PRODCDB/stdb_2.log') size 52428800;
SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_3.log','/u01/app/oracle/fra/PRODCDB/stdb_3.log') size 52428800;
SQL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_4.log','/u01/app/oracle/fra/PRODCDB/stdb_4.log') size 52428800;
SQL> select l.group#, l.bytes, f.type, f.member
from v$log l , v$logfile f where l.group#=f.group#
union all
select l.group#, l.bytes, f.type, f.member from
v$standby_log l , v$logfile f where l.group#=f.group#
GROUP# BYTES TYPE MEMBER
---------- ---------- ------- ------------------------------------------------
3 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log3.log
3 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log3.log
2 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log2.log
2 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log2.log
1 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log1.log
1 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log1.log
11 52428800 STANDBY /u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_1.log
11 52428800 STANDBY /u01/app/oracle/fra/PRODCDB/stdb_1.log
12 52428800 STANDBY /u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_2.log
12 52428800 STANDBY /u01/app/oracle/fra/PRODCDB/stdb_2.log
13 52428800 STANDBY /u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_3.log
13 52428800 STANDBY /u01/app/oracle/fra/PRODCDB/stdb_3.log
14 52428800 STANDBY /u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_4.log
14 52428800 STANDBY /u01/app/oracle/fra/PRODCDB/stdb_4.log
14 rows selected.
-- other configuration - data files location, is important to have all datafiles in the same folder
SQL> col name form a100
SQL> select * from v$dbfile;
FILE# NAME CON_ID
---------- ----------------------------------------------------------- ------
6 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_users_fvdyknoc_.dbf 1
4 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_undotbs1_fvdykotw_.dbf 1
1 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_system_fvdyhmf2_.dbf 1
3 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_sysaux_fvdyfw4s_.dbf 1
5 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_system_fvdylynm_.dbf 2
7 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_sysaux_fvdylyn3_.dbf 2
8 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_system_fvdysy1f_.dbf 3
9 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_sysaux_fvdysy17_.dbf 3
10 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_users_fvdysy1m_.dbf 3
11 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_example_fvdysy0z_.dbf 3
-- Other info - containers
SQL> col name form a10
SQL> select con_id,name, open_mode from v$containers;
CON_ID NAME OPEN_MODE
---------- ---------- ---------------
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PRDPDB1 MOUNTED
-- Check instance parameters - I will not change any
SQL> col name form a30
SQL> col value form a80
SQL> select name,value from v$parameter where name in ('log_archive_dest_2','log_archive_dest_state_2','log_archive_config','db_name','db_unique_name','dg_broker_start','db_recovery_file_dest','db_recovery_file_dest_size','control_files','db_file_name_convert','log_file_name_convert','pdb_file_name_convert');
NAME VALUE
------------------------------ ---------------------------------------------------------------------
control_files /u01/app/oracle/oradata/PRODCDB/controlfile/o1_mf_fvdyll4b_.ctl,
/u01/app/oracle/fra/PRODCDB/controlfile/o1_mf_fvdyll6s_.ctl
db_file_name_convert
log_file_name_convert
log_archive_dest_2
log_archive_dest_state_2 enable
log_archive_config
db_recovery_file_dest /u01/app/oracle/fra
db_recovery_file_dest_size 4781506560
db_name PRODCDB
db_unique_name PRODCDB
dg_broker_start FALSE
pdb_file_name_convert
#..
PRODCDB:/u01/app/oracle/product/12.1.0/dbhome_1:N
[oracle@oretail Desktop]$ . oraenv
ORACLE_SID = [PRODCDB] ? PRODCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oretail Desktop]$ sqlplus / as sysdba
SQL> set pages 50
SQL> set lines 200
SQL> col name form a10
SQL> col db_unique_name form a15
SQL> col log_mode form a15
SQL> col switchover_status form a20
SQL> col force_logging form a20
SQL> select name, db_unique_name, switchover_status, log_mode, flashback_on , force_logging from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS LOG_MODE FLASHBACK_ON FORCE_LOGGING
---------- ------------- -------------------- ------------- ------------------ --------------------
PRODCDB PRODCDB NOT ALLOWED NOARCHIVELOG NO NO
-- Enable logging
SQL> alter database force logging;
Database altered.
-- Enaable archivelog mode
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size 2924640 bytes
Variable Size 973082528 bytes
Database Buffers 520093696 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> set pages 50
SQL> set lines 200
SQL> col name form a10
SQL> col db_unique_name form a15
SQL> col log_mode form a15
SQL> col switchover_status form a20
SQL> col force_logging form a20
SQL> select name, db_unique_name, switchover_status, log_mode, flashback_on , force_logging from v$database;
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS LOG_MODE FLASHBACK_ON FORCE_LOGGING
---------- --------------- -------------------- --------------- ------------------ --------------------
PRODCDB PRODCDB NOT ALLOWED ARCHIVELOG NO YES
-- addd standby logs
SQL> col member form a100
SQL> select l.group#, l.bytes, f.type, f.member from
v$log l , v$logfile f where l.group#=f.group#
union all
select l.group#, l.bytes, f.type, f.member
from v$standby_log l , v$logfile f where l.group#=f.group#;
GROUP# BYTES TYPE MEMBER
------ ---------- ------- ----------------------------------------------------
3 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log3.log
3 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log3.log
2 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log2.log
2 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log2.log
1 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log1.log
1 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log1.log
6 rows selected..
SQL> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_1.log','/u01/app/oracle/fra/PRODCDB/stdb_1.log') size 52428800;
SQL> alter database add standby logfile group 12 ('/u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_2.log','/u01/app/oracle/fra/PRODCDB/stdb_2.log') size 52428800;
SQL> alter database add standby logfile group 13 ('/u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_3.log','/u01/app/oracle/fra/PRODCDB/stdb_3.log') size 52428800;
SQL> alter database add standby logfile group 14 ('/u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_4.log','/u01/app/oracle/fra/PRODCDB/stdb_4.log') size 52428800;
SQL> select l.group#, l.bytes, f.type, f.member
from v$log l , v$logfile f where l.group#=f.group#
union all
select l.group#, l.bytes, f.type, f.member from
v$standby_log l , v$logfile f where l.group#=f.group#
GROUP# BYTES TYPE MEMBER
---------- ---------- ------- ------------------------------------------------
3 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log3.log
3 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log3.log
2 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log2.log
2 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log2.log
1 52428800 ONLINE /u01/app/oracle/oradata/PRODCDB/onlinelog/log1.log
1 52428800 ONLINE /u01/app/oracle/fra/PRODCDB/onlinelog/log1.log
11 52428800 STANDBY /u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_1.log
11 52428800 STANDBY /u01/app/oracle/fra/PRODCDB/stdb_1.log
12 52428800 STANDBY /u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_2.log
12 52428800 STANDBY /u01/app/oracle/fra/PRODCDB/stdb_2.log
13 52428800 STANDBY /u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_3.log
13 52428800 STANDBY /u01/app/oracle/fra/PRODCDB/stdb_3.log
14 52428800 STANDBY /u01/app/oracle/oradata/PRODCDB/onlinelog/stdb_4.log
14 52428800 STANDBY /u01/app/oracle/fra/PRODCDB/stdb_4.log
14 rows selected.
-- other configuration - data files location, is important to have all datafiles in the same folder
SQL> col name form a100
SQL> select * from v$dbfile;
FILE# NAME CON_ID
---------- ----------------------------------------------------------- ------
6 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_users_fvdyknoc_.dbf 1
4 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_undotbs1_fvdykotw_.dbf 1
1 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_system_fvdyhmf2_.dbf 1
3 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_sysaux_fvdyfw4s_.dbf 1
5 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_system_fvdylynm_.dbf 2
7 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_sysaux_fvdylyn3_.dbf 2
8 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_system_fvdysy1f_.dbf 3
9 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_sysaux_fvdysy17_.dbf 3
10 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_users_fvdysy1m_.dbf 3
11 /u01/app/oracle/oradata/PRODCDB/datafile/o1_mf_example_fvdysy0z_.dbf 3
-- Other info - containers
SQL> col name form a10
SQL> select con_id,name, open_mode from v$containers;
CON_ID NAME OPEN_MODE
---------- ---------- ---------------
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PRDPDB1 MOUNTED
-- Check instance parameters - I will not change any
SQL> col name form a30
SQL> col value form a80
SQL> select name,value from v$parameter where name in ('log_archive_dest_2','log_archive_dest_state_2','log_archive_config','db_name','db_unique_name','dg_broker_start','db_recovery_file_dest','db_recovery_file_dest_size','control_files','db_file_name_convert','log_file_name_convert','pdb_file_name_convert');
NAME VALUE
------------------------------ ---------------------------------------------------------------------
control_files /u01/app/oracle/oradata/PRODCDB/controlfile/o1_mf_fvdyll4b_.ctl,
/u01/app/oracle/fra/PRODCDB/controlfile/o1_mf_fvdyll6s_.ctl
db_file_name_convert
log_file_name_convert
log_archive_dest_2
log_archive_dest_state_2 enable
log_archive_config
db_recovery_file_dest /u01/app/oracle/fra
db_recovery_file_dest_size 4781506560
db_name PRODCDB
db_unique_name PRODCDB
dg_broker_start FALSE
pdb_file_name_convert
2 . NETWORK – Setup listener and tns
Now lets make setup the network side, first the listener (listener.ora) e is need to register statically two services the one for the database and another for broker _DGMGRL :
Primary: PRODCDB, PRODCDB_DGMGRL
Standby : DRSITECDB, DRSITECDB_DGMGRL
TNS (tnsnames.ora):
Primary : PRODCDB
Standby :DRSITECDB
Note : Is possible to edit the network files directlally with a text editor, but i would recomend to use netmgr, the file contents ate end should be something like.
Primary: PRODCDB, PRODCDB_DGMGRL
Standby : DRSITECDB, DRSITECDB_DGMGRL
TNS (tnsnames.ora):
Primary : PRODCDB
Standby :DRSITECDB
Note : Is possible to edit the network files directlally with a text editor, but i would recomend to use netmgr, the file contents ate end should be something like.
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRODCDB)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = PRODCDB)
)
(SID_DESC =
(GLOBAL_DBNAME = PRODCDB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = PRODCDB)
)
(SID_DESC =
(GLOBAL_DBNAME = DRSITECDB)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = DRSITECDB)
)
(SID_DESC =
(GLOBAL_DBNAME = DRSITECDB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = DRSITECDB)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
$ cat tnsnames.ora
PRODCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODCDB)
)
)
DRSITECDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRSITECDB)
)
)
$ lsnrctl stop; lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-OCT-2018 14:52:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oretail.example.com)(PORT=1521)))
The command completed successfully
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-OCT-2018 14:52:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oretail/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oretail.example.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oretail.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 05-OCT-2018 14:52:08
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oretail/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oretail.example.com)(PORT=1521)))
Services Summary...
Service "PRODCDB" has 1 instance(s).
Instance "PRODCDB", status UNKNOWN, has 1 handler(s) for this service...
Service "DRSITECDB" has 1 instance(s).
Instance "DRSITECDB", status UNKNOWN, has 1 handler(s) for this service...
Service "DRSITECDB_DGMGRL" has 1 instance(s).
Instance "DRSITECDB", status UNKNOWN, has 1 handler(s) for this service...
Service "PRODCDB_DGMGRL" has 1 instance(s).
Instance "PRODCDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
$ tnsping PRODCDB
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-OCT-2018 10:49:55
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRODCDB)))
OK (0 msec)
$ tnsping DRSITECDB
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-OCT-2018 10:49:57
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DRSITECDB)))
OK (0 msec)
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRODCDB)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = PRODCDB)
)
(SID_DESC =
(GLOBAL_DBNAME = PRODCDB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = PRODCDB)
)
(SID_DESC =
(GLOBAL_DBNAME = DRSITECDB)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = DRSITECDB)
)
(SID_DESC =
(GLOBAL_DBNAME = DRSITECDB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = DRSITECDB)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
$ cat tnsnames.ora
PRODCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODCDB)
)
)
DRSITECDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DRSITECDB)
)
)
$ lsnrctl stop; lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-OCT-2018 14:52:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oretail.example.com)(PORT=1521)))
The command completed successfully
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-OCT-2018 14:52:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oretail/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oretail.example.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oretail.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 05-OCT-2018 14:52:08
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oretail/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oretail.example.com)(PORT=1521)))
Services Summary...
Service "PRODCDB" has 1 instance(s).
Instance "PRODCDB", status UNKNOWN, has 1 handler(s) for this service...
Service "DRSITECDB" has 1 instance(s).
Instance "DRSITECDB", status UNKNOWN, has 1 handler(s) for this service...
Service "DRSITECDB_DGMGRL" has 1 instance(s).
Instance "DRSITECDB", status UNKNOWN, has 1 handler(s) for this service...
Service "PRODCDB_DGMGRL" has 1 instance(s).
Instance "PRODCDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
$ tnsping PRODCDB
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-OCT-2018 10:49:55
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRODCDB)))
OK (0 msec)
$ tnsping DRSITECDB
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 05-OCT-2018 10:49:57
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oretail.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DRSITECDB)))
OK (0 msec)
3 . DRSITECDB – Create Physical Standby
In standby server, lets run the follwing :
- create an entry for the standby in file : /etc/oratab
- copy orapw file
- create the folders for the standby database files
- create a pfile with minimum parameters
- make um spfile
- startup standby as nomount
- Run RMAN duplicate to create the standby
$ echo "DRSITECDB:/u01/app/oracle/product/12.1.0/dbhome_1:N" >> /etc/oratab
$ tail -n 2 /etc/oratab
#...
PRODCDB:/u01/app/oracle/product/12.1.0/dbhome_1:N
DRSITECDB:/u01/app/oracle/product/12.1.0/dbhome_1:N
$ cp $ORACLE_HOME/dbs/orapwPRODCDB $ORACLE_HOME/dbs/orapwDRSITECDB
$ mkdir -p /u01/app/oracle/oradata/DRSITECDB/controlfile
$ mkdir -p /u01/app/oracle/oradata/DRSITECDB/onlinelog
$ mkdir -p /u01/app/oracle/oradata/DRSITECDB/datafile
$# create a pfile
$ echo "db_name=PRODCDB
db_unique_name=DRSITECDB
control_files='/u01/app/oracle/oradata/DRSITECDB/controlfile/o1_mf_fvdyll4b_.ctl', '/u01/app/oracle/fra/DRSITECDB/controlfile/o1_mf_fvdyll6s_.ctl'
db_file_name_convert='PRODCDB','DRSITECDB'
log_file_name_convert='PRODCDB','DRSITECDB'
pdb_file_name_convert='PRODCDB','DRSITECDB'
db_recovery_file_dest='/u01/app/oracle/fra'
db_recovery_file_dest_size=4560M
compatible='12.1.0.2.0'
enable_pluggable_database = true
">/tmp/pfileDRSITECDB
12c - only
$ . oraenv
ORACLE_SID = [PRODCDB] ? DRSITECDB
The Oracle base remains unchanged with value /u01/app/oracle
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 5 21:43:29 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/tmp/pfileDRSITECDB' ;
File created.
SQL> startup nomount;
ORACLE instance started.
$ rman target sys/oracle@PRODCDB auxiliary sys/oracle@DRSITECDB
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 5 11:54:22 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODCDB (DBID=2986740707)
connected to auxiliary database: PRODCDB (not mounted)
RMAN> backup database;
.... < trunc output >
Finished backup at 05-OCT-18
Starting Control File and SPFILE Autobackup at 05-OCT-18
.... < trunc output >
Finished Control File and SPFILE Autobackup at 05-OCT-18
RMAN> duplicate target database for standby ;
.... < trunc output >
Finished Duplicate Db at 05-OCT-18
4 DGMGRL – Create Broker
Next activate the broker, lets check the dg_broker parameters:
Activate the broker in both : dg_broker_start = TRUE , then with DGMGRL create and activate the configuration.
- dg_broker_config_file1
- dg_broker_config_file2
- dg_broker_start
Activate the broker in both : dg_broker_start = TRUE , then with DGMGRL create and activate the configuration.
$ sqlplus sys/oracle@PRODCDB as sysdba
SQL> set lines 200
SQL> show parameter dg_brok
TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1PRODCDB.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2PRODCDB.dat
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start = TRUE scope=both;
SQL> System altered.
SQL> show parameter dg_brok
TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1PRODCDB.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2PRODCDB.dat
dg_broker_start boolean TRUE
SQL> exit
$ sqlplus sys/oracle@DRSITECDB as sysdba
SQL> show parameter dg_brok
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1DRSITECDB.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2DRSITECDB.dat
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start = TRUE scope=both;
System altered.
SQL> show parameter dg_brok
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1DRSITECDB.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2DRSITECDB.dat
dg_broker_start boolean TRUE
SQL> exit
$ dgmgrl sys/oracle@PRODCDB
DGMGRL> CREATE CONFIGURATION PRODCDB_CONFIG AS PRIMARY DATABASE IS "PRODCDB" CONNECT IDENTIFIER IS "PRODCDB";
Configuration "prodcdb_config" created with primary database "PRODCDB"
DGMGRL> ADD DATABASE "DRSITECDB" AS CONNECT IDENTIFIER IS "DRSITECDB";
Database "DRSITECDB" added
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - prodcdb_config
Protection Mode: MaxPerformance
Members:
PRODCDB - Primary database
DRSITECDB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS (status updated 54 seconds ago)
5 . DGMGRL – Testing
OK - is beeing a long post, lets check the database configurations, note the log_archive parameters where properlly set.
SQL> select NAME,VALUE from v$parameter where name in ('log_archive_dest_2','log_archive_dest_state_2','log_archive_config');
NAME VALUE
--------------------------- -------------------------------------------------------------------------
log_archive_dest_2 service="DRSITECDB", ASYNC NOAFFIRM delay=0 optional
compression=disable max_failure=0 max_connections=1 reopen=300
db_unique_name="DRSITECDB" net_timeout=30, valid_for=(online_logfile,all_roles)
log_archive_dest_state_2 ENABLE
log_archive_config dg_config=(PRODCDB,DRSITECDB)
SQL> set pages 50
set lines 200
col name form a10
col db_unique_name form a15
col log_mode form a15
col switchover_status form a20
col force_logging form a20
select name, db_unique_name, switchover_status, log_mode, flashback_on , force_logging from v$database;
SQL> SQL> SQL> SQL> SQL> SQL> SQL>
NAME DB_UNIQUE_NAME SWITCHOVER_STATUS LOG_MODE FLASHBACK_ON FORCE_LOGGING
---------- --------------- -------------------- --------------- ------------------ --------------------
PRODCDB PRODCDB TO STANDBY ARCHIVELOG NO YES
Test : switchover roles .
[oracle@oretail admin]$ dgmgrl sys/oracle@PRODCDB
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - prodcdb_config
Protection Mode: MaxPerformance
Members:
PRODCDB - Primary database
DRSITECDB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 14 seconds ago)
DGMGRL> switchover to "DRSITECDB";
Performing switchover NOW, please wait...
Operation requires a connection to instance "DRSITECDB" on database "DRSITECDB"
Connecting to instance "DRSITECDB"...
Connected as SYSDBA.
New primary database "DRSITECDB" is opening...
Operation requires start up of instance "PRODCDB" on database "PRODCDB"
Starting instance "PRODCDB"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "DRSITECDB"
DGMGRL> show configuration;
Configuration - prodcdb_config
Protection Mode: MaxPerformance
Members:
DRSITECDB - Primary database
PRODCDB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 41 seconds ago)
DGMGRL> switchover to "PRODCDB";
Performing switchover NOW, please wait...
Operation requires a connection to instance "PRODCDB" on database "PRODCDB"
Connecting to instance "PRODCDB"...
Connected as SYSDBA.
New primary database "PRODCDB" is opening...
Operation requires start up of instance "DRSITECDB" on database "DRSITECDB"
Starting instance "DRSITECDB"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "PRODCDB"
DGMGRL> show configuration;
Configuration - prodcdb_config
Protection Mode: MaxPerformance
Members:
PRODCDB - Primary database
DRSITECDB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 21 seconds ago)
Thank you for reading, hope this post was helpful.
Comments