Step-By-Step Setup of Oracle Active Data Guard for Physical Standby Database 12c on Linux
Goal: Creating a physical standby database for the primary database, and setting up Oracle Active Data Guard 12.1 in Red Hat Linux
OS: Red Hat Enterprise Linux 6, 7
File Systems: /u01, /u02, /u03
Oracle Database: 12.1.0.2.0
ORACLE_HOME: /u01/app/oracle/product/12.1.0/dbhome_1
Steps:
· Prep the primary database
· Configure Oracle Net for the primary and standby databases
· Copy the primary database password file to the standby host
· Create standby database pfile
· RMAN Duplicate to create the standby database from the active primary database
· Configure Data Guard Broker
· Verify Data Guard configuration by checking log transport / apply
· Test switchover and switchback using Data Guard Broker
· Turn on READ-ONLY mode on the standby database
1. Prep the primary database for standby database creation
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_database_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL> alter database force logging;
Database altered.
SQL> select log_mode,database_role,open_mode from v$database;
LOG_MODE database_ROLE OPEN_MODE
---------- ------------- ----------
ARCHIVELOG PRIMARY READ WRITE
SQL> select log_mode,database_role,open_mode,force_logging from v$database;
LOG_MODE database_ROLE OPEN_MODE FORCE_LOGGING
----------- ------------- ----------- -------------
ARCHIVELOG PRIMARY READ WRITE YES
SQL> alter system switch logfile; -- To ensure at least one logfile is present
System altered.
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_database_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL> alter database force logging;
Database altered.
SQL> select log_mode,database_role,open_mode from v$database;
LOG_MODE database_ROLE OPEN_MODE
---------- ------------- ----------
ARCHIVELOG PRIMARY READ WRITE
SQL> select log_mode,database_role,open_mode,force_logging from v$database;
LOG_MODE database_ROLE OPEN_MODE FORCE_LOGGING
----------- ------------- ----------- -------------
ARCHIVELOG PRIMARY READ WRITE YES
SQL> alter system switch logfile; -- To ensure at least one logfile is present
System altered.
2. Turn on flashback database
Failover converts the primary database to a standby database, making the original primary database useless. If flashback is not enabled on the primary database, the original primary will be scrapped and recreated as a standby database. If flashback database is enabled on the primary (and the standby if desired), in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database.
SQL> alter database flashback on;
Database altered.
3. Configure the primary database to receive redo data
It is highly recommended to have one more standby redo log group than the online redo log groups in the primary database. The files must be the same size or larger than the primary database online redo logs.
SQL> select group#,thread#,bytes/1024/1024 mb from v$log;
GROUP# THREAD# MB
---------- ---------- ----------
1 1 50
2 1 50
3 1 50
SQL> select group#,thread#,bytes from v$standby_log;
no rows selected
SQL> ALTER database ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/proddb/sredo01.log' SIZE 50M;
SQL> ALTER database ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/proddb/sredo02.log' SIZE 50M;
SQL> ALTER database ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/proddb/sredo03.log' SIZE 50M;
SQL> ALTER database ADD STANDBY LOGFILE THREAD 1 '/u02/oradata/proddb/sredo04.log' SIZE 50M;
SQL> select group#,thread#,bytes/1024/1024 mb from v$standby_log;
GROUP# THREAD# MB
---------- ---------- ----------
4 1 50
5 1 50
6 1 50
7 1 50
4. Verify db_name and db_unique_name
The standby will have the same
db_name as that of the primary, but must have a different db_unique_name.
SQL> show parameter db_name;
NAME TYPE VALUE
------- ------ ------
db_name string proddb
SQL> show parameter db_unique_name
NAME TYPE VALUE
-------------- ------ ------
db_unique_name string proddb
5. Set primary database init parameters that control redo transport services while the database is in the primary role.
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(proddb,stdbydb)';
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_2='service=stdbydb async valid_for=(online_logfile,primary_role) db_unique_name=stdbydb';
System altered.
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------ ------ --------------------------
log_archive_config string dg_config=(proddb,stdbydb)
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------ ------ -------------------------------------
log_archive_dest_2 string service=stdbydb async valid_for=
(online_logfile,primary_role) db_unique_name=stdbydb
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------- ------ ---------
remote_login_passwordfile string EXCLUSIVE
6. Add TNS entry for both primary and standby databases
TNS entries for both the primary database (PRIMDB) and the standby database (STDBYDB) are needed in tnsnames.ora on both primary and standby hosts, with
SID, rather than the SERVICE_NAME used, as the Data Guard broker needs to connect to the databases when they are down, so the services are unavailable.
PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc1_db1.mycom.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = proddb)
)
)
STDBYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc2_db1.mycom.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = stdbydb)
)
)
7. Configure listener for both primary and standby DATABASEs
Add the explicit database entries for the primary and the standby in the listener.ora on both the primary and the standby hosts for the broker which needs to connect to the database when it’s down (so auto-registration with the listener can’t be relied on), as well as for RMAN to create the standby database using duplicate.
Ø On the primary host
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc1_db1.mycom.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DATABASENAME = proddb)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = proddb)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Ø On the standby host
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc2_db1.mycom.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DATABASENAME = stdbydb)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = stdbydb)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Ø Re-start the listener on both hosts.
oracle@dc1_db1> lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2017 09:48:46
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/dc1_db1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc1_db1.mycom.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 21-SEP-2017 09:48:46
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/dc1_db1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc1_db1.mycom.com)(PORT=1521)))
Services Summary...
Service "proddb" has 1 instance(s).
Instance "proddb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@dc2_db1> lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2017 09:51:06
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/dc2_db1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc2_db1.mycom.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 21-SEP-2017 09:51:06
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/dc2_db1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc2_db1.mycom.com)(PORT=1521)))
Services Summary...
Service "stdbydb" has 1 instance(s).
Instance "stdbydb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Ø Test the TNS entries on both hosts
oracle@dc1_db1> tnsping proddb
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-SEP-2017 14:39:04
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc1_db1.mycom.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = proddb)))
OK (10 msec)
oracle@dc1_db1> tnsping stdbydb
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-SEP-2017 14:38:57
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc2_db1.mycom.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stdbydb)))
OK (0 msec)
oracle@dc2_db1> tnsping proddb
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-SEP-2017 15:27:15
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc1_db1.mycom.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = proddb)))
OK (10 msec)
oracle@dc2_db1> tnsping stdbydb
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-SEP-2017 15:27:19
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc2_db1.mycom.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stdbydb)))
OK (20 msec)
8. Copy the password file from primary to standby and rename it
(Note: The password file must be re-copied each time the SYSDATABASEA or SYSOPER privilege is granted or revoked and whenever the login password of a user with these privileges is changed.)
Oracle@ dc1_db1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs
> scp orapwproddb dc2_db1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwstdbydb
9. Create the necessary directories on the standby host
oracle@dc2_db1> mkdir -p /u01/app/oracle/admin/stdbydb/adump
oracle@dc2_db1> mkdir -p /u01/app/oracle/admin/stdbydb/dpdump
oracle@dc2_db1> mkdir -p /u01/app/oracle/audit/stdbydb
oracle@dc2_db1> mkdir -p /u02/oradata/stdbydb
oracle@dc2_db1> mkdir -p /u03/FRA
10. Create standby pfile
Create a pfile on the standby host for the standby database (containing a single parameter database_NAME):
oracle@dc2_db1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs> cat initstdbydb.ora
DB_NAME=stdbydb
11. Create the RMAN script file on the primary host
oracle@dc1_db1> cat /home/oracle/cr_stdbydb.txt
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'proddb','stdbydb'
set db_unique_name='stdbydb'
set db_file_name_convert='/proddb/','/stdbydb/'
set log_file_name_convert='/proddb/','/stdbydb/'
set control_files='/u02/oradata/stdbydb/scontrol01.ctl'
set log_archive_max_processes='5'
set fal_client='stdbydb'
set fal_server='proddb'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(proddb,stdbydb)'
set log_archive_dest_2='service=proddb ASYNC valid_for=(ONLINE_LOGFILE, PRIMARY_ROLE) db_unique_name=proddb'
;
}
- FOR STANDBY: The DUPLICATE command is to be used for a standby, so no force change in DBID.
- FROM ACTIVE DATABASE: The standby database will be created directly from the source datafiles, without an additional backup step.
- SPFILE: For resetting the values in the spfile when it is copied from the source server.
12. Mount the auxiliary instance on the standby host using the pfile
oracle@dc2_db1> export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
oracle@dc2_db1> export ORACLE_SID=stdbydb
oracle@dc2_db1> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 20 15:38:46 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initstdbydb.ora
ORACLE instance started.
Total System Global Area 343932928 bytes
Fixed Size 2924160 bytes
Variable Size 285213056 bytes
Database Buffers 50331648 bytes
Redo Buffers 5464064 bytes
SQL> exit
13. Run RMAN on the primary database host to create the physical standby database by duplicating the active primary database
Ø Set up environment
oracle@dc1_db1> export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
oracle@dc1_db1> export ORACLE_SID=proddb
Ø Start RMAN on the primary host, connect to both primary and standby instances by specifying the full connect string for both TARGET and AUXILLIARY instances (not attempting to use OS authentication).
oracle@dc1_db1:/home/oracle> rman
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 20 16:12:40 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/Pswd4SYS@prpddb
connected to target database: PRODDB (DBID=1744947765)
RMAN> connect auxiliary sys/Pswd4SYS@stdbydb
connected to auxiliary database: STDBYDB (not mounted)
Ø Run RMAN to create the physical standby database by duplicating the active primary database
RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate auxiliary channel stby type disk;
7> duplicate target database for standby from active database
8> spfile
9> parameter_value_convert 'proddb','stdbydb'
10> set db_unique_name='stdbydb'
11> set db_file_name_convert='/proddb/','/stdbydb/'
12> set log_file_name_convert='/proddb/','/stdbydb/'
13> set control_files='/u02/oradata/stdbydb/scontrol01.ctl'
14> set log_archive_max_processes='5'
15> set fal_client='stdbydb'
16> set fal_server='proddb'
17> set standby_file_management='AUTO'
18> set log_archive_config='dg_config=(proddb,stdbydb)'
19> set log_archive_dest_2='service=proddb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=proddb'
20> ;
21> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=580 device type=DISK
allocated channel: prmy2
channel prmy2: SID=772 device type=DISK
allocated channel: prmy3
channel prmy3: SID=964 device type=DISK
allocated channel: prmy4
channel prmy4: SID=1153 device type=DISK
allocated channel: stby
channel stby: SID=3 device type=DISK
Starting Duplicate Database at 20-SEP-17
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwproddb' auxiliary format
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwstdbydb' targetfile
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileproddb.ora' auxiliary format
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilestdbydb.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilestdbydb.ora''";
}
executing Memory Script
Starting backup at 20-SEP-17
Finished backup at 20-SEP-17
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilestdbydb.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/stdbydb/adump'' comment= '''' scope=spfile";
sql clone "alter system set db_create_file_dest =
''/u02/oradata/stdbydb'' comment= '''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=stdbydbXDATABASE)'' comment= '''' scope=spfile";
sql clone "alter system set db_unique_name = ''stdbydb'' comment= '''' scope=spfile";
sql clone "alter system set database_file_name_convert =
''/proddb/'', ''/stdbydb/'' comment= '''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/proddb/'', ''/stdbydb/'' comment= '''' scope=spfile";
sql clone "alter system set control_files =
''/u02/oradata/stdbydb/scontrol01.ctl'' comment= '''' scope=spfile";
sql clone "alter system set log_archive_max_processes = 5 comment= '''' scope=spfile";
sql clone "alter system set fal_client = ''stdbydb'' comment= '''' scope=spfile";
sql clone "alter system set fal_server = ''proddb'' comment= '''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment= '''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(proddb,stdbydb)'' comment= '''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=proddb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=proddb'' comment= '''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/stdbydb/adump'' comment= '''' scope=spfile
sql statement: alter system set db_create_file_dest = ''/u02/oradata/stdbydb'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=stdbydbXDATABASE)'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stdbydb'' comment= '''' scope=spfile
sql statement: alter system set database_file_name_convert = ''/proddb/'', ''/stdbydb/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/proddb/'', ''/stdbydb/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u02/oradata/stdbydb/scontrol01.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''stdbydb'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''proddb'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(proddb,stdbydb)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=proddb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=proddb'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 10737418240 bytes
Fixed Size 3722144 bytes
Variable Size 5872026720 bytes
Database Buffers 4831838208 bytes
Redo Buffers 29831168 bytes
allocated channel: stby
channel stby: SID=577 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u02/oradata/stdbydb/scontrol01.ctl';
}
executing Memory Script
Starting backup at 20-SEP-17
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_proddb.f tag=TAG20170920T161631
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-SEP-17
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u02/oradata/stdbydb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u02/oradata/stdbydb/system01.dbf";
set newname for datafile 3 to
"/u02/oradata/stdbydb/sysaux01.dbf";
set newname for datafile 4 to
"/u02/oradata/stdbydb/undotbs01.dbf";
set newname for datafile 5 to
"/u02/oradata/stdbydb/ogg_data.dbf";
set newname for datafile 6 to
"/u02/oradata/stdbydb/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u02/oradata/stdbydb/system01.dbf" datafile
3 auxiliary format
"/u02/oradata/stdbydb/sysaux01.dbf" datafile
4 auxiliary format
"/u02/oradata/stdbydb/undotbs01.dbf" datafile
5 auxiliary format
"/u02/oradata/stdbydb/ogg_data.dbf" datafile
6 auxiliary format
"/u02/oradata/stdbydb/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u02/oradata/stdbydb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-SEP-17
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/proddb/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00003 name=/u02/oradata/proddb/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00005 name=/u02/oradata/proddb/ogg_data.dbf
channel prmy4: starting datafile copy
input datafile file number=00004 name=/u02/oradata/proddb/undotbs01.dbf
output file name=/u02/oradata/stdbydb/undotbs01.dbf tag=TAG20170920T161637
channel prmy4: datafile copy complete, elapsed time: 00:00:03
channel prmy4: starting datafile copy
input datafile file number=00006 name=/u02/oradata/proddb/users01.dbf
output file name=/u02/oradata/stdbydb/ogg_data.dbf tag=TAG20170920T161637
channel prmy3: datafile copy complete, elapsed time: 00:00:04
output file name=/u02/oradata/stdbydb/users01.dbf tag=TAG20170920T161637
channel prmy4: datafile copy complete, elapsed time: 00:00:03
output file name=/u02/oradata/stdbydb/system01.dbf tag=TAG20170920T161637
channel prmy1: datafile copy complete, elapsed time: 00:00:18
output file name=/u02/oradata/stdbydb/sysaux01.dbf tag=TAG20170920T161637
channel prmy2: datafile copy complete, elapsed time: 00:00:18
Finished backup at 20-SEP-17
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=955210624 file name=/u02/oradata/stdbydb/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=955210624 file name=/u02/oradata/stdbydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=955210624 file name=/u02/oradata/stdbydb/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=955210624 file name=/u02/oradata/stdbydb/ogg_data.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=955210624 file name=/u02/oradata/stdbydb/users01.dbf
Finished Duplicate Database at 20-SEP-17
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN
RMAN> **end-of-file**
RMAN> exit
Recovery Manager complete.
14. Switch log on primary database to start sending redo logs to the standby database
SQL> show parameter db_unique_name
NAME TYPE VALUE
-------------- ------ ------
db_unique_name string proddb
SQL> alter system switch logfile;
System altered.
15. Start the managed recovery process on the standby database
SQL> show parameter db_unique_name
NAME TYPE VALUE
-------------- ------ -------
db_unique_name string stdbydb
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
16. Verify that the standby database is performing correctly
Ø On the standby
SQL> select instance_name from v$instance;
INSTANCE_NAME
-------------
stdbydb
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
26 20-SEP-17 20-SEP-17 YES
27 20-SEP-17 20-SEP-17 NO
Ø Switch log multiple times on the primary
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_database_RECOVERY_FILE_DEST
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
Ø Check the log apply on the standby again
SQL> /
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
26 20-SEP-17 20-SEP-17 YES
27 20-SEP-17 20-SEP-17 YES
28 20-SEP-17 20-SEP-17 YES
29 20-SEP-17 20-SEP-17 YES
30 20-SEP-17 20-SEP-17 IN-MEMORY
17. Enable Data Guard broker
Ø On both the primary and standby databases
SQL> ALTER SYSTEM SET dg_broker_start=true;
Ø Clear existing remote redo transport destinations on both the primary and standby databases (Ref).
ü On the primary
SQL> show parameter db_unique_name
NAME TYPE VALUE
-------------- ------ -------
db_unique_name string proddb
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ------- -----------------------------------------------
log_archive_dest_2 string service=proddb ASYNC valid_for=(ONLINE_LOGFILE,
PRIMARY_ROLE) db_unique_name=proddb
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
ü On the standby
SQL> select instance_name from v$instance;
INSTANCE_NAME
-------------
Stdbydb
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ------- ------------------------------------------------
log_archive_dest_2 string service=proddb ASYNC valid_fo r=(ONLINE_LOGFILE,
PRIMARY_ROLE) db_unique_name=proddb
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
ü On the primary
racle@dc1_db1> dgmgrl
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.
DGMGRL> connect sys/Pswd4SYS@proddb
Connected as SYSDABA.
DGMGRL> CREATE CONFIGURATION proddb_dg_config AS PRIMARY DATABASE IS proddb CONNECT IDENTIFIER IS proddb;
Configuration "proddb_dg_config" created with primary database "proddb"
DGMGRL> ADD database stdbydb AS CONNECT IDENTIFIER IS stdbydb MAINTAINED AS PHYSICAL;
Database "stdbydb" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - proddb_dg_config
Protection Mode: MaxPerformance
Members:
proddb - Primary database
stdbydb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 2 seconds ago)
DGMGRL> show database proddb
Database - proddb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
proddb
Database Status:
SUCCESS
DGMGRL> show database stdbydb
Database - stdbydb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: OFF
Instance(s):
stdbydb
Database Status:
SUCCESS
SQL> show parameter db_unique_name
NAME TYPE VALUE
-------------- ------ -------
db_unique_name string proddb
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ------- ------------------------------------------------------
log_archive_dest_2 string service="stdbydb", ASYNC NOAF FIRM delay=0
optional compression=disable max_failure=0
max_connections=1 reopen=300 db_unique_name="stdbydb"
net_timeout=30, valid_for=(online_logfi le,all_roles)
ü On the standby
SQL> show parameter db_unique_name
NAME TYPE VALUE
-------------- ------ --------
db_unique_name string stdbydb
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ------ ------------
log_archive_dest_2 string
18. Update listener.ora
Ø On the primary host
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DATABASENAME = proddb_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = proddb)
)
)
Ø On the standby host
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DATABASENAME = stdbydb_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = stdbydb)
)
)
Ø Restart the listener on both hosts
oracle@dc1_db1> lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-SEP-2017 09:19:30
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/dc1_db1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc1_db1.mycom.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 22-SEP-2017 09:19:30
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/dc1_db1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc1_db1.mycom.com)(PORT=1521)))
Services Summary...
Service "proddb_DGMGRL" has 1 instance(s).
Instance "proddb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@dc2_db1> lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-SEP-2017 09:20:52
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/dc2_db1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc2_db1.mycom.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 22-SEP-2017 09:20:52
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/dc2_db1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc2_db1.mycom.com)(PORT=1521)))
Services Summary...
Service "stdbydb_DGMGRL" has 1 instance(s).
Instance "stdbydb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
19. Test switchover
oracle@dc1_db1> dgmgrl
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.
DGMGRL> connect sys/Pswd4SYS@proddb
Connected as SYSDBA.
DGMGRL> connect sys/Pswd4SYS@stdbydb
Connected as SYSDBA.
DGMGRL> show configuration verbose
Configuration - proddb_dg_config
Protection Mode: MaxPerformance
Members:
proddb - Primary database
stdbydb - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database verbose proddb
Database - proddb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
proddb
Properties:
DGConnectIdentifier = 'proddb'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DatabaseFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc1_db1.mycom.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=proddb_DGMGRL) (INSTANCE_NAME=proddb)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_database_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> show database verbose stdbydb
Database - stdbydb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.00 KByte/s
Active Apply Rate: 915.00 KByte/s
Maximum Apply Rate: 938.00 KByte/s
Real Time Query: OFF
Instance(s):
stdbydb
Properties:
DGConnectIdentifier = 'stdbydb'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '5'
LogArchiveMinSucceedDest = '1'
DatabaseFileNameConvert = '/proddb/, /stdbydb/'
LogFileNameConvert = '/proddb/, /stdbydb/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc2_db1.mycom.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stdbydb_DGMGRL) (INSTANCE_NAME=stdbydb)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_database_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> validate database verbose proddb
Database Role: Primary database
Ready for Switchover: Yes
Capacity Information:
Database Instances Threads
proddb 1 1
Temporary Tablespace File Information:
proddb TEMP Files: 1
Flashback Database Status:
proddb: On
Data file Online Move in Progress:
proddb: No
Transport-Related Information:
Transport On: Yes
Log Files Cleared:
proddb Standby Redo Log Files: Cleared
Automatic Diagnostic Repository Errors:
Error proddb
No logging operation NO
Control file corruptions NO
System data file missing NO
System data file corrupted NO
System data file offline NO
User data file missing NO
User data file corrupted NO
User data file offline NO
Block Corruptions found NO
DGMGRL> validate database verbose stdbydb
Database Role: Physical standby database
Primary Database: proddb
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Capacity Information:
Database Instances Threads
proddb 1 1
stdbydb 1 1
Temporary Tablespace File Information:
proddb TEMP Files: 1
stdbydb TEMP Files: 1
Flashback Database Status:
proddb: On
stdbydb: Off
Data file Online Move in Progress:
proddb: No
stdbydb: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 0 seconds ago)
Transport Status: Success
Log Files Cleared:
proddb Standby Redo Log Files: Cleared
stdbydb Online Redo Log Files: Cleared
stdbydb Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(proddb) (stdbydb)
1 3 2 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(stdbydb) (proddb)
1 3 4 Sufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(proddb) (stdbydb)
1 50 MBytes 50 MBytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(stdbydb) (proddb)
1 50 MBytes 50 MBytes
Apply-Related Property Settings:
Property proddb Value stdbydb Value
DelayMins 0 0
ApplyParallel AUTO AUTO
Transport-Related Property Settings:
Property proddb Value stdbydb Value
LogXptMode ASYNC ASYNC
RedoRoutes
Dependency
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
MaxConnections 1 1
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
LogShipping ON ON
Automatic Diagnostic Repository Errors:
Error proddb stdbydb
No logging operation NO NO
Control file corruptions NO NO
SRL Group Unavailable NO NO
System data file missing NO NO
System data file corrupted NO NO
System data file offline NO NO
User data file missing NO NO
User data file corrupted NO NO
User data file offline NO NO
Block Corruptions found NO NO
DGMGRL> switchover to stdbydb
Performing switchover NOW, please wait...
New primary database "stdbydb" is opening...
Operation requires start up of instance "proddb" on database "proddb"
Starting instance "proddb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "stdbydb"
20. Checking log transport / apply after switchover
Ø On the new standby
SQL> select db_unique_name,OPEN_MODE,database_ROLE,SWITCHOVER_STATUS from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------- --------- ----------------- -----------------
proddb MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> SELECT sequence#,first_time,next_time,applied FROM v$archived_log order by 1 desc;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
43 21-SEP-17 21-SEP-17 IN-MEMORY
42 21-SEP-17 21-SEP-17 YES
41 21-SEP-17 21-SEP-17 YES
40 21-SEP-17 21-SEP-17 YES
Ø On the new primary
SQL> select db_unique_name,OPEN_MODE,database_ROLE,SWITCHOVER_STATUS from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------- ----------- -------------- -----------------
stdbydb READ WRITE PRIMARY TO STANDBY
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
Ø On the new standby
SQL> /
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
46 21-SEP-17 21-SEP-17 IN-MEMORY
45 21-SEP-17 21-SEP-17 YES
44 21-SEP-17 21-SEP-17 YES
43 21-SEP-17 21-SEP-17 YES
42 21-SEP-17 21-SEP-17 YES
41 21-SEP-17 21-SEP-17 YES
40 21-SEP-17 21-SEP-17 YES
21. Turn on flashback on the new primary database
SQL> alter database flashback on;
Database altered.
22. Test switchback
oracle@dc2_db1> dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
DGMGRL> connect sys/Pswd4SYS@stdbydb
Connected as SYSDATABASEA.
DGMGRL> connect sys/Pswd4SYS@proddb
Connected as SYSDATABASEA.
DGMGRL> show configuration verbose
Configuration - proddb_dg_config
Protection Mode: MaxPerformance
Members:
stdbydb - Primary database
proddb - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database verbose stdbydb
Database - stdbydb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
stdbydb
Properties:
DGConnectIdentifier = 'stdbydb'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '5'
LogArchiveMinSucceedDest = '1'
DatabaseFileNameConvert = '/proddb/, /stdbydb/'
LogFileNameConvert = '/proddb/, /stdbydb/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc2_db1.mycom.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stdbydb_DGMGRL) (INSTANCE_NAME=stdbydb)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_database_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> show database verbose proddb
Database - proddb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 22.00 KByte/s
Active Apply Rate: 996.00 KByte/s
Maximum Apply Rate: 1003.00 KByte/s
Real Time Query: OFF
Instance(s):
proddb
Properties:
DGConnectIdentifier = 'proddb'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DatabaseFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dc1_db1.mycom.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=proddb_DGMGRL) (INSTANCE_NAME=proddb)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_database_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> validate database verbose stdbydb
Database Role: Primary database
Ready for Switchover: Yes
Capacity Information:
Database Instances Threads
stdbydb 1 1
Temporary Tablespace File Information:
stdbydb TEMP Files: 1
Flashback Database Status:
stdbydb: On
Data file Online Move in Progress:
stdbydb: No
Transport-Related Information:
Transport On: Yes
Log Files Cleared:
stdbydb Standby Redo Log Files: Cleared
Automatic Diagnostic Repository Errors:
Error stdbydb
No logging operation NO
Control file corruptions NO
System data file missing NO
System data file corrupted NO
System data file offline NO
User data file missing NO
User data file corrupted NO
User data file offline NO
Block Corruptions found NO
DGMGRL> validate database verbose proddb
Database Role: Physical standby database
Primary Database: stdbydb
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Capacity Information:
Database Instances Threads
stdbydb 1 1
proddb 1 1
Temporary Tablespace File Information:
stdbydb TEMP Files: 1
proddb TEMP Files: 1
Flashback Database Status:
stdbydb: On
proddb: On
Data file Online Move in Progress:
stdbydb: No
proddb: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 1 second ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 1 second ago)
Transport Status: Success
Log Files Cleared:
stdbydb Standby Redo Log Files: Cleared
proddb Online Redo Log Files: Cleared
proddb Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(stdbydb) (proddb)
1 3 4 Sufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(proddb) (stdbydb)
1 3 4 Sufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(stdbydb) (proddb)
1 50 MBytes 50 Mbytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(proddb) (stdbydb)
1 50 MBytes 50 MBytes
Apply-Related Property Settings:
Property stdbydb Value proddb Value
DelayMins 0 0
ApplyParallel AUTO AUTO
Transport-Related Property Settings:
Property stdbydb Value proddb Value
LogXptMode ASYNC ASYNC
RedoRoutes
Dependency
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
MaxConnections 1 1
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
LogShipping ON ON
Automatic Diagnostic Repository Errors:
Error stdbydb proddb
No logging operation NO NO
Control file corruptions NO NO
SRL Group Unavailable NO NO
System data file missing NO NO
System data file corrupted NO NO
System data file offline NO NO
User data file missing NO NO
User data file corrupted NO NO
User data file offline NO NO
Block Corruptions found NO NO
DGMGRL> switchover to proddb
Performing switchover NOW, please wait...
New primary database "proddb" is opening...
Operation requires start up of instance "stdbydb" on database "stdbydb"
Starting instance "stdbydb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "proddb"
23. Check log transport / apply after switchback
Ø On the standby
SQL> select db_unique_name,OPEN_MODE,database_ROLE,SWITCHOVER_STATUS from v$database;
DB_UNIQUE_NAME OPEN_MODE database_ROLE SWITCHOVER_STATUS
-------------- ---------- ----------------- -----------------
stdbydb MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
50 21-SEP-17 21-SEP-17 NO
49 21-SEP-17 21-SEP-17 YES
48 21-SEP-17 21-SEP-17 YES
Ø On the primary
SQL> select db_unique_name,OPEN_MODE,database_ROLE,SWITCHOVER_STATUS from v$database;
DB_UNIQUE_NAME OPEN_MODE database_ROLE SWITCHOVER_STATUS
-------------- ----------- ------------- -----------------
proddb READ WRITE PRIMARY TO STANDBY
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
Ø On the standby
SQL> /
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
53 21-SEP-17 21-SEP-17 IN-MEMORY
52 21-SEP-17 21-SEP-17 YES
51 21-SEP-17 21-SEP-17 YES
50 21-SEP-17 21-SEP-17 YES
49 21-SEP-17 21-SEP-17 YES
48 21-SEP-17 21-SEP-17 YES
24. Turn on READ-ONLY mode on the standby database (Active Data Guard)
DGMGRL> show configuration
Configuration - proddb_dg_config
Protection Mode: MaxPerformance
Members:
proddb - Primary database
stdbydb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 0 seconds ago)
SQL> select name,db_unique_name,database_role,open_mode from v$database
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------- -------------- ----------------- ---------
PRIMDB stdbydb PHYSICAL STANDBY MOUNTED
SQL> alter database open;
Database altered.
SQL> select name,db_unique_name,database_role,open_mode from v$database
NAME DB_UNIQUE_NAME database_ROLE OPEN_MODE
-------- -------------- ----------------- --------------------
PRIMDB stdbydb PHYSICAL STANDBY READ ONLY WITH APPLY
oracle@dc2_db1 stdbydb> dgmgrl
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.
DGMGRL> connect /
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - proddb_dg_config
Protection Mode: MaxPerformance
Members:
proddb - Primary database
stdbydb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 0 seconds ago)
No errors in database alert log files on both hosts.
posted by Kyle H @ December 30, 2017
0 Comments
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home