Step by Step Configure Oracle 12c Data Guard Far Sync Standby Database

Description:-

  • In this article we are going to see Step by Step Configure Oracle 12c Data Guard Far Sync Standby Database
  • An Oracle Data Guard far sync instance is a remote Oracle Data Guard destination that accepts redo from the primary database and then ships that redo to other members of the Oracle Data Guard configuration.
  • A far sync instance manages a control file, receives redo into standby redo logs (SRLs), and archives those SRLs to local archived redo logs, but that is where the similarity with standbys ends. A far sync instance does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database.
  • Far sync instances are part of the Oracle Active Data Guard Far Sync feature, which requires an Oracle Active Data Guard license.
  • Creating a far sync instance close to the primary has the benefit of minimizing impact on commit response times to an acceptable threshold (due to the smaller network latency between primary and far sync instance) while allowing for higher data protection guarantees — if the primary were to fail, and assuming the far sync instance was synchronized at the time of the failure, the far sync instance and the terminal standby would coordinate a final redo shipment from the far sync instance to the standby to ship any redo not yet available to the standby and then perform a zero-data-loss failover.
  • Summary:-
    • A light-weight/remote standby instance
    • It contains instance, standby controlfile and set of redo logs only Not datafile
    • Far sync standby database consumes minimal resources over the server (CPU, Memory, I/O etc)
    • Overcome network latency issues while shipping redo synchronously
      Guarantees zero data loss capabilities
    • Keep it close to the primary site, 40-150 miles
    • Far sync instance doesn’t have any physical structure
    • Multiple Far sync instance can be configured for active/passive purpose
    • Supports a maximum of 29 remote standby databases

Far Sync Standby Architecture:-

High Level Steps:-

  1. Adding TNS Entry and listener
  2. Creating the Standby Database
  3. create Far Sync Instance
  4. Adding Standby Logfiles in all the Databases
  5. Creating our Data Guard configuration with far sync instance

Let’s start the demo:-

Environment Details:-

Adding TNS entry (check all the nodes)

[oracle@prod1 admin]$ tnsping oradbwrp

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:25
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwr)))
OK (10 msec)

[oracle@prod1 admin]$ tnsping oradbwrs

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:28
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.26)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwrs)))
OK (10 msec)

[oracle@prod1 admin]$ tnsping oradbwrf

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:31
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwrf)))
OK (10 msec)

Adding  Listener (check all the nodes) Mark Red colour (DG Broker) 

[oracle@prod1 dbs]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-JUL-2018 04:18:57
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/prod1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 19-JUL-2018 04:18:58
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.2.0.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/prod1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradbwr" has 1 instance(s).
Instance "oradbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwr_DGMGRL" has 1 instance(s).
Instance "oradbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrf" has 1 instance(s).
Instance "oradbwrf", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrf_DGMGRL" has 1 instance(s).
Instance "oradbwrf", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrs" has 1 instance(s).
Instance "oradbwrs", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrs_DGMGRL" has 1 instance(s).
Instance "oradbwrs", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Enabling archivelog & flashback

[oracle@prod1 ~]$ export ORACLE_SID=oradbwr
[oracle@prod1 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:27:26 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> startup mount
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 8627344 bytes
Variable Size 343935856 bytes
Database Buffers 578813952 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database force logging;
Database altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 8627344 bytes
Variable Size 343935856 bytes
Database Buffers 578813952 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/oradbwr/redo04.log' size 50m;
Database altered.

SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/oradbwr/redo05.log' size 50m;
Database altered.

SQL>alter database add standby logfile group 6 '/u01/app/oracle/oradata/oradbwr/redo06.log' size 50m; 
Database altered.

Changing parameters in primary database

SQL> ALTER SYSTEM SET db_unique_name='oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(oradbwr,oradbwrs)' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2='service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrs' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_server='oradbwrs' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_client='oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_format='oradbwr_%t_%s_%r.arc' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/oradbwrs','/u01/app/oracle/oradata/oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/oradbwrs','/u01/app/oracle/oradata/oradbwr' SCOPE=SPFILE;
System altered.

Standby database side

Step:1 Password file creation

copy the remote login password file (orapworadbwr) from the primary database server to the $ORACLE_HOME/dbs directory on the
standby database server, renaming it to orapworadbwrs.

Step:2 Changing parameters in standby database

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstand.ora
Containing a single parameter: DB_NAME=oradbwrs

Step:3 Create directory Structure in Standby database

cd $ORACLE_BASE/admin/
mkdir oradbwrs
cd oradbwrs
mkdir adump pfile dpdump
mkdir -p /u01/app/oracle/oradata/oradbwrs

Step:4 start the standby database using pfile

startup nomount pfile=$ORACLE_HOME/dbs/initoradbwrs.ora

Step:5 connect to the rman

[oracle@prod3 ~]$ rman target sys/oracle@oradbwrp auxiliary sys/oracle@oradbwrs

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jul 18 23:34:41 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBWR (DBID=1408230468)
connected to auxiliary database: ORADBWR (not mounted)

RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'oradbwr','oradbwrs'
set db_name='oradbwr'
set db_unique_name='oradbwrs' 
set db_file_name_convert='/u01/app/oracle/oradata/oradbwr','/u01/app/oracle/oradata/oradbwrs'
set log_file_name_convert='/u01/app/oracle/oradata/oradbwr','/u01/app/oracle/oradata/oradbwrs'
set control_files='/u01/app/oracle/oradata/oradbwrs/standby1.ctl'
set log_archive_max_processes='5'
set fal_client='oradbwrs'
set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/oradbwrs'
set db_recovery_file_dest_size='8000m'
set fal_server='oradbwr'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(oradbwrs,oradbwr)'
set compatible='12.2.0.1.0'
set sga_target='1500m'
nofilenamecheck;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=75 device type=DISK
allocated channel: p2
channel p2: SID=76 device type=DISK
allocated channel: p3
channel p3: SID=71 device type=DISK
allocated channel: p4
channel p4: SID=63 device type=DISK
allocated channel: s1
channel s1: SID=36 device type=DISK
Starting Duplicate Db at 18-JUL-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapworadbwr' auxiliary format 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapworadbwrs' targetfile 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwr.ora' auxiliary format 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora''";
}
executing Memory Script
Starting backup at 18-JUL-18
Finished backup at 18-JUL-18
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest = 
''/u01/app/oracle/admin/oradbwrs/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers = 
''(PROTOCOL=TCP) (SERVICE=oradbwrsXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 = 
''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 = 
''service=oradbwrs async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrss'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_format = 
''oradbwrs_%t_%s_%r.arc'' comment=
'''' scope=spfile";
sql clone "alter system set db_name = 
''oradbwr'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name = 
''oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert = 
''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert = 
''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set control_files = 
''/u01/app/oracle/oradata/oradbwrs/standby1.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes = 
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client = 
''oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest = 
''/u01/app/oracle/fast_recovery_area/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest_size = 
8000m comment=
'''' scope=spfile";
sql clone "alter system set fal_server = 
''oradbwr'' 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=(oradbwrs,oradbwr)'' comment=
'''' scope=spfile";
sql clone "alter system set compatible = 
''12.2.0.1.0'' comment=
'''' scope=spfile";
sql clone "alter system set sga_target = 
1500m comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/oradbwrs/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=oradbwrsXDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrss'' comment= '''' scope=spfile
sql statement: alter system set log_archive_format = ''oradbwrs_%t_%s_%r.arc'' comment= '''' scope=spfile
sql statement: alter system set db_name = ''oradbwr'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/oradbwrs/standby1.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''/u01/app/oracle/fast_recovery_area/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest_size = 8000m comment= '''' scope=spfile
sql statement: alter system set fal_server = ''oradbwr'' 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=(oradbwrs,oradbwr)'' comment= '''' scope=spfile
sql statement: alter system set compatible = ''12.2.0.1.0'' comment= '''' scope=spfile
sql statement: alter system set sga_target = 1500m comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1577058304 bytes
Fixed Size 8621136 bytes
Variable Size 503317424 bytes
Database Buffers 1056964608 bytes
Redo Buffers 8155136 bytes
allocated channel: s1
channel s1: SID=34 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/oradbwrs/standby1.ctl';
}
executing Memory Script
Starting backup at 18-JUL-18
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.2.0.1/db_1/dbs/snapcf_oradbwr.f tag=TAG20180718T233622
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-JUL-18
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 
"/u01/app/oracle/oradata/oradbwrs/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/oradbwrs/system01.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf";
set newname for datafile 7 to 
"/u01/app/oracle/oradata/oradbwrs/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/system01.dbf" datafile 
3 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf" datafile 
4 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf" datafile 
7 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/oradbwrs/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-JUL-18
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/oradbwr/system01.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/oradbwr/sysaux01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/oradbwr/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/oradbwr/users01.dbf
output file name=/u01/app/oracle/oradata/oradbwrs/users01.dbf tag=TAG20180718T233628
channel p4: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf tag=TAG20180718T233628
channel p3: datafile copy complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf tag=TAG20180718T233628
channel p2: datafile copy complete, elapsed time: 00:01:15
output file name=/u01/app/oracle/oradata/oradbwrs/system01.dbf tag=TAG20180718T233628
channel p1: datafile copy complete, elapsed time: 00:01:25
Finished backup at 18-JUL-18
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=981848275 file name=/u01/app/oracle/oradata/oradbwrs/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/users01.dbf
Finished Duplicate Db at 18-JUL-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step6 :- Post check status for Standby database,

SQL> select name,database_role,open_mode from v$database;

NAME               DATABASE_ROLE                           OPEN_MODE
——— —————- ——————–
ORADBWR             PHYSICAL STANDBY                        MOUNTED

Step 7:- Enable the recovery:

SQL> alter database recover managed standby database disconnect from session;

Step 8:- Check the Standby database sync status with primary:

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

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                 24                   24                 0

Deploying Far Sync instance

Step1:-We need to create a controlfile in primary database that will be used to create far sync instance

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/home/oracle/stbyfarsync.ctl';
Database altered.

Step2:-Copy the far sync controlfile & passwordfile

Copy the far sync controlfile & passwordfile
[oracle@prod1 ~]$ scp stbyfarsync.ctl oracle@192.168.1.25:/u01/app/oracle/oradata/oradbwrf
oracle@192.168.1.25's password: 
stbyfarsync.ctl 100% 10MB 10.1MB/s 00:00 
[oracle@prod1 ~]$ cd $ORACLE_HOME/dbs
[oracle@prod1 dbs]$ scp orapworadbwr oracle@192.168.1.25:$ORACLE_HOME/dbs
oracle@192.168.1.25's password: 
orapworadbwr

step3:-Farsync instance Pfile

audit_file_dest='/u01/app/oracle/oradbwrf/adump'
audit_trail='db'
compatible='12.2.0.1.0'
control_files='/u01/app/oracle/oradata/oradbwrf/stbyfarsync.ctl'
db_block_size=8192
db_domain=''
db_name='oradbwr'
db_unique_name='oradbwrf'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/oradbwrf'
db_recovery_file_dest_size=4800m
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=oradbwrfXDB)'
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrf'
log_archive_dest_2='service=oradbwrs ASYNC valid_for=(standby_logfiles,standby_role) db_unique_name=oradbwrs'
log_archive_config='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)'
open_cursors=300
pga_aggregate_target=300m
processes=300
remote_login_passwordfile='EXCLUSIVE'
sga_target=1300m
undo_tablespace='UNDOTBS1'
fal_server='oradbwr'
db_file_name_convert='/u01/app/oracle/oradata/oradbwr/','/u01/app/oracle/oradata/oradbwrf/'
log_file_name_convert='/u01/app/oracle/oradata/oradbwr/','/u01/app/oracle/oradata/oradbwrf/'

Step4:- startup the Far sync instance and check

[oracle@prod2 ~]$ sqlplus ‘/as sysdba’;

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 19 03:54:43 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1375731712 bytes
Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

SQL> alter database mount;
Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
FAR SYNC

SQL> select DB_UNIQUE_NAME,DEST_ROLE from V$DATAGUARD_CONFIG;

DB_UNIQUE_NAME                    DEST_ROLE
------------------------------ -----------------
oradbwrf                        FAR SYNC INSTANCE
oradbwrs                        PHYSICAL STANDBY
oradbwr                         PRIMARY DATABASE

Data Guard configuration with far sync instance

Parameters change 

Primary:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbwr' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oradbwrf SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbwrf' scope=both;

Far sync Standby:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ordbwrf' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ordbwrs ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=ordbwrs' scope=both;

Standby:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbwrs' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oradbwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbwr' scope=both;
[oracle@prod1 admin]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Jul 19 06:26:35 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@oradbwrp
Connected to "oradbwr"
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION 'chennai' AS PRIMARY DATABASE IS 'oradbwr' CONNECT IDENTIFIER IS 'oradbwr';
Configuration "chennai" created with primary database "oradbwr"
DGMGRL> ADD DATABASE 'oradbwrs' AS CONNECT IDENTIFIER IS 'oradbwrs' maintained as physical;
Database "oradbwrs" added
DGMGRL> ADD FAR_SYNC 'oradbwrf' AS CONNECT IDENTIFIER IS 'oradbwrf';
far sync instance "oradbwrf" added
DGMGRL> show configuration;

Configuration - chennai

Protection Mode: MaxAvailability
Members:
oradbwr - Primary database
oradbwrs - Physical standby database 
oradbwrf - Far sync instance 
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

Check the Standby database sync status with primary:

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

Thread Last Sequence Received Last Sequence Applied     Difference
---------- ---------------------- --------------------- ----------
1                 37                     37                0

Successfully configured Far sync standby database.

Reference:-

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sbydb/creating-oracle-data-guard-far-sync-instance.html#GUID-8AD7FBA2-42B0-46CF-852B-1AF0CB4A36E8

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)

Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)

Description:-

In this article we are going to configure Oracle Heterogeneous Standby with Primary in Linux & Standby in Windows and  see the below configuration details.Primary Server side Configurations:-

Step1:-Change Archivelog mode
SQL> archive log listSQL> shut immediate
SQL> startup mount
SQL> alter system set log_archive_dest_1=’LOCATION=/oradb/app/oracle/archive/orcl_prim’ scope=spfile;
SQL> alter database archivelog;
SQL> alter database open;

Step2:-Change force logging mode
SQL> alter database force logging;
SQL> select force_logging,log_mode from v$database;Step3:-Adding Redologfile for standby database
SQL> alter database add standby logfile group 4 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo04.log’ size 50m;
SQL> alter database add standby logfile group 5 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo05.log’ size 50m;
SQL> alter database add standby logfile group 6 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo06.log’ size 50m;

Now check the all the database files location:-

SQL> @all_db_files.sqlSQL>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step4:-Adding the network entry in primary and standby side(Both servers)
Listener Entry(Primary):-
*******************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRIM)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1624))
)
)

Listener Entry(Standby):-
*******************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY)
(ORACLE_HOME = D:\app\oracle\product\11.2.0.4\db_1)
(SID_NAME = ORCL)
)
)

Tnsnames entry(Both):-
******************
ORCL_PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_PRIM)
)
)

ORCL_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY)
)
)

Start the listener in both primary & standby and tnsping to primary & standby:-
$ tnsping ORCL_PRIM

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 24-JUN-2018 20:47:15
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))) (CONNECT_DATA = (SERVICE_NAME = ORCL_PRIM)))
OK (10 msec)

$ tnsping ORCL_STBY

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 24-JUN-2018 20:48:18
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1624))) (CONNECT_DATA = (SERVICE_NAME = ORCL_STBY)))
OK (10 msec)

Step5:-Changing parameters in primary database
SQL> ALTER SYSTEM SET db_unique_name=’ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(ORCL_PRIM,ORCL_STBY)’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1=’LOCATION=/oradb/app/oracle/archive/orcl_prim valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=ORCL_STBY async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_server=’ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_client=’ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM’,’D:\app\oracle\oradata\ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM’,’D:\app\oracle\oradata\ORCL_STBY’ SCOPE=SPFILE;
SQL> SHUT IMMEDIATE
SQL> STARTUP

Verify All the required parameters are set properly with the below script:-SQL> @stby_parameters.sql

Standby Server side Configurations:-

Step1:-Password file creation
Set ORACLE_BASE,ORACLE_HOME & PATH:-
$ set ORACLE_SID=ORCL
$ set ORACLE_BASE=D:\app\oracle
$ set ORACLE_HOME=%ORACLE_BASE%\product\11.2.0.4\db_1
$ set PATH=%ORACLE_HOME%\bin;%PATH%

Copy the remote login password file (orapwORCL) from the primary database server in $ORACLE_HOME/dbs directory to the
standby database server in $ORACLE_HOME/database:-

$ pscp $ORACLE_HOME/dbs/orapwORCL administrator@192.168.1.106:D:\app\oracle\product\11.2.0.4\db_1\database

Step2:-Changing parameters in standby database:-

In the $ORACLE_HOME/database directory of the standby system, create an initialization parameter file named initORCL.ora with the below parameter.

ORCL.__oracle_base=’D:\app\oracle’#ORACLE_BASE set from environment
*.audit_file_dest=’D:\app\oracle\admin\ORCL_STBY\adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4′
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM/’,’D:\app\oracle\oradata\ORCL_STBY\’
*.db_name=’ORCL’
*.db_recovery_file_dest=’D:\app\oracle\fast_recovery_area’
*.db_recovery_file_dest_size=4385144832
*.db_unique_name=’ORCL_STBY’
*.diagnostic_dest=’D:\app\oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
*.fal_client=’ORCL_STBY’
*.fal_server=’ORCL_PRIM’
*.log_archive_config=’dg_config=(ORCL_PRIM,ORCL_STBY)’
*.log_archive_dest_1=’LOCATION=D:\app\oracle\archive\orcl_stby valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_STBY’
*.log_archive_dest_2=’service=ORCL_PRIM async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_PRIM’
*.log_archive_max_processes=5
*.log_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM/’,’D:\app\oracle\oradata\ORCL_STBY\’
*.memory_target=1509949440
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step3:-Create directory Structure in Standby database

$ cd %ORACLE_BASE%
$ mkdir admin\ORCL_STBY
$ cd admin\ORCL_STBY
$ mkdir adump pfile dpdump
$ mkdir -p D:\app\oracle\oradata\ORCL_STBY
$ mkdir -p D:\app\oracle\fast_recovery_area\ORCL_STBY

Step4:-Setup the windows service for oracle and start the standby database using pfile

$ oradim -new -sid ORCL -SYSPWD manager -pfile D:\app\oracle\product\11.2.0.4\db_1\database\initORCL.ora

$ sqlplus ‘/ as sysdba’
SQL> create spfile from pfile=’D:\app\oracle\product\11.2.0.4\db_1\database\INITorcl.ora’;
SQL> startup nomount

Step5:-Connect to the rman(Make sure the tnsnames.ora file has the primary & standby database entry in %ORACLE_HOME%\network\admin location)

$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 27 00:57:14 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1507437661)
connected to auxiliary database: ORCL (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=44 device type=DISK
allocated channel: p2
channel p2: SID=28 device type=DISK
allocated channel: p3
channel p3: SID=48 device type=DISK
allocated channel: p4
channel p4: SID=47 device type=DISK
allocated channel: s1
channel s1: SID=134 device type=DISK
Starting Duplicate Db at 27-JUN-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format
'D:\app\oracle\product\11.2.0.4\db_1\DATABASE\PWDorcl.ORA' ;
}
executing Memory Script
Starting backup at 27-JUN-18
Finished backup at 27-JUN-18
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format 'D:\APP\ORACLE\ORADATA\ORCL_STBY\CONTROL01.CTL';
}
executing Memory Script
Starting backup at 27-JUN-18
channel p1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180627T063140 RECID=3 STAMP=979885900
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-18
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
"D:\APP\ORACLE\ORADATA\ORCL_STBY\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF";
set newname for datafile 5 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF";
backup as copy reuse
datafile 1 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF" datafile
2 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF" datafile
3 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF" datafile
4 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF" datafile
5 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to D:\APP\ORACLE\ORADATA\ORCL_STBY\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 27-JUN-18
channel p1: starting datafile copy
input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01.dbf
channel p2: starting datafile copy
input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf
channel p3: starting datafile copy
input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf
channel p4: starting datafile copy
input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF tag=TAG20180627T063150
channel p4: datafile copy complete, elapsed time: 00:02:22
channel p4: starting datafile copy
input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/example01.dbf
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF tag=TAG20180627T063150
channel p3: datafile copy complete, elapsed time: 00:03:03
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF tag=TAG20180627T063150
channel p4: datafile copy complete, elapsed time: 00:01:32
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF tag=TAG20180627T063150
channel p2: datafile copy complete, elapsed time: 00:04:27
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF tag=TAG20180627T063150
channel p1: datafile copy complete, elapsed time: 00:04:48
Finished backup at 27-JUN-18
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=3 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF
Finished Duplicate Db at 27-JUN-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN> exit
Recovery Manager complete.

Step6:-Copy the control file to FRA

SQL> shut immediate
$ copy D:\app\oracle\oradata\ORCL_STBY\CONTROL01.CTL D:\app\oracle\fast_recovery_area\ORCL_STBY\CONTROL02.CTL
SQL> create pfile from spfile;

Change the control_files parameter value using any text editor in the pfile:

$ notepad D:\app\oracle\product\11.2.0.4\db_1\database\INITorcl.ora

From:-
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’

To:-
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’,’D:\app\oracle\fast_recovery_area\ORCL_STBY\CONTROL02.CTL’

Step7:-Startup the standby database and start the media recovery process

SQL> create spfile from pfile;
SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect;

Now the Primary & Standby database status and roles:-

Primary:Standby:Now, check the current sequence of archive log in both primary & standby.

Primary:

SQL> archive log listStandby:

SQL> archive log listFrom the above two snaps from primary & standby has the same archive log sequence applied. Now, switch a redo log in primary and check the alert log in standby.

Primary:

SQL> alter system switch logfile;Standby:

SQL> @taln.sql 20Using the above script, we can see the alert log from sqlplus and from the above snap, the sequence 125 has been shipped from primary to standby.

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba

Oracle12c-Step by step How To Applying PSU Patch on Oracle Dataguard Environment

Description:-

In this article we are going to see Oracle12c-Step by step How To Applying PSU Patch on Oracle Dataguard Environment

High Level steps for applying the Patch 

  • Primary site disable the log shipping to the standby database and Stop MRP on standby site
  • Shutdown Standby Database and Listener
  • Apply PSU patch on Standby site
  • Shutdown Primary database and Listener.
  • Apply PSU Patch on Primary Site
  • Enable Log shipping on Primary
  • execute Post installation Scripts (datapatch)
  • Monitor the redo apply from Primary to Standby

Environment Details:-Primary Database Details:
prime> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ———–
OPEN            prime                       PRIMARY                   READ WRITE

Standby Database Details:
Stand> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ———–
OPEN           stand       PHYSICAL STANDBY          READ ONLY WITH APPLY

Let’s Start the Demo:-

Opatch Version

Oracle recommends that you use the latest released OPatch 12.1 tool      which is available to download and apply the  patch: 6880880 from support.oracle.com or from https://updates.oracle.com/download/6880880.html

Current version of Opatch Tool in our environment 

export PATH=$ORACLE_HOME/OPatch:$PATH

[oracle@primary ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@primary ~]$ opatch version
OPatch Version: 12.1.0.1.3
OPatch succeeded.

Pre-steps:-

install Opatch utility (both Primary & Standby Side)

1. Download the latest Opatch utility from oracle support
[oracle@primary u01]$ ls -lrt
total 424836
drwxrwxr-x. 2 oracle oinstall 16384 Jun 11 23:28 lost+found
drwxrwxr-x. 4 oracle oinstall 4096 Jun 12 01:32 app
-rwxr-xr-x. 1 oracle oinstall 337586889 Jun 15 13:40 p25755742_121020_Linux-x86-64.zip
-rwxr-xr-x. 1 oracle oinstall 97422646 Jun 15 13:40 p6880880_121010_Linux-x86-64.zip

2. Take a backup of older version of OPatch utility under $ORACLE_HOME and unzip the downloaded file
[oracle@primary ~]$ cd $ORACLE_HOME
[oracle@primary db_1]$ mv OPatch OPatch.bkp.15.06.18
[oracle@primary db_1]$ unzip p6880880_121010_Linux-x86-64.zip
cd OPatch
oracle@primary OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.13
OPatch succeeded.

3. Include OPatch in PATH .bash_profile
export PATH=$ORACLE_HOME/OPatch:$PATH

[oracle@primary db_1]$ export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH
[oracle@primary db_1]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.1.0.2/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version : 12.2.0.1.13
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2018-06-15_14-29-03PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/lsinv/lsinventory2018-06-15_14-29-03PM.txt
——————————————————————————–
Local Machine Information::
Hostname: primary
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c 12.1.0.2.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
——————————————————————————–
OPatch succeeded.

Take Backup of ORACLE_HOME (both primary & Standby side) (Rollback plan)

[oracle@primary u01]$ tar -cvf oracle_home_jun1518.tar $ORACLE_HOME

Step1:-Primary site disable the log shipping to the standby database and Stop MRP on standby site

DGMGRL> show configuration
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 13 seconds ago)

DGMGRL> edit database prime set state=’LOG-TRANSPORT-OFF’;
Succeeded.

prime> show parameter log_archive_dest_state_2

NAME                                                          TYPE             VALUE
———————— ———– ——-
log_archive_dest_state_2                string         RESE

stand> alter database recover managed standby database cancel;
Database altered.

Step2:-Shutdown Standby Database and Listener

[oracle@standby ~]$ export ORACLE_SID=stand
[oracle@standby ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jun 15 16:39:06 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@standby ~]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-JUN-2018 16:39:44
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1539)))
The command completed successfully

Step3:-Apply PSU patch on Standby site

[oracle@standby u01]$ unzip p25755742_121020_Linux-x86-64.zip

[oracle@standby u01]$ ls -lrt
total 424936
drwxrwxr-x. 13 oracle oinstall 4096 Jun 29 2017 25755742
-rw-rw-r–. 1 oracle oinstall 96270 Aug 7 2017 PatchSearch.xml
drwxrwxr-x. 2 oracle oinstall 16384 Jun 11 23:28 lost+found
drwxrwxr-x. 4 oracle oinstall 4096 Jun 12 01:32 app
-rwxr-xr-x. 1 oracle oinstall 337586889 Jun 15 13:41 p25755742_121020_Linux-x86-64.zip
-rwxr-xr-x. 1 oracle oinstall 97422646 Jun 15 13:41 p6880880_121010_Linux-x86-64.zip

[oracle@standby u01]$ export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH
[oracle@standby u01]$ cd 25755742/
[oracle@standby 25755742]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.1.0.2/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version : 12.2.0.1.13
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2018-06-15_16-50-22PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 19769480 20299023 20831110 21359755 21948354 22291127 23054246 24006101 24732082 25171037 25755742
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/12.1.0.2/db_1’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying sub-patch ‘19769480’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms.deconfig, 12.1.0.2.0…
Patching component oracle.xdk, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms.util, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.xdk.parser.java, 12.1.0.2.0…
Patching component oracle.oraolap, 12.1.0.2.0…
Patching component oracle.xdk.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.ldap.rsf.ic, 12.1.0.2.0…
Applying sub-patch ‘20299023’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms.deconfig, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.rdbms.rsf.ic, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.ldap.rsf.ic, 12.1.0.2.0…
Applying sub-patch ‘20831110’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.oraolap.dbscripts, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Applying sub-patch ‘21359755’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.assistants.server, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Applying sub-patch ‘21948354’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms.deconfig, 12.1.0.2.0…
Patching component oracle.xdk, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.xdk.parser.java, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.xdk.rsf, 12.1.0.2.0…
Applying sub-patch ‘22291127’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
ApplySession: Optional component(s) [ oracle.oid.client, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.ldap.client, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.oraolap, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.oraolap.dbscripts, 12.1.0.2.0…
Applying sub-patch ‘23054246’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms.dv, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.install.deinstalltool, 12.1.0.2.0…
Patching component oracle.ldap.rsf.ic, 12.1.0.2.0…
Patching component oracle.oracore.rsf, 12.1.0.2.0…
Patching component oracle.ctx, 12.1.0.2.0…
Patching component oracle.xdk, 12.1.0.2.0…
Patching component oracle.nlsrtl.rsf, 12.1.0.2.0…
Patching component oracle.xdk.parser.java, 12.1.0.2.0…
Patching component oracle.ctx.atg, 12.1.0.2.0…
Applying sub-patch ‘24006101’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.sqlplus, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.network.listener, 12.1.0.2.0…
Patching component oracle.network.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.dv, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.sqlplus.ic, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Applying sub-patch ‘24732082’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms.install.plugins, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Applying sub-patch ‘25171037’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.sqlplus.ic, 12.1.0.2.0…
Patching component oracle.sqlplus, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.ordim.client, 12.1.0.2.0…
Patching component oracle.ordim.client, 12.1.0.2.0…
Patching component oracle.ordim.jai, 12.1.0.2.0…
Patching component oracle.ordim.server, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.rdbms.install.plugins, 12.1.0.2.0…
Patching component oracle.javavm.containers, 12.1.0.2.0…
Applying sub-patch ‘25755742’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
ApplySession: Optional component(s) [ oracle.oid.client, 12.1.0.2.0 ] , [ oracle.has.crs, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.network.rsf, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.ldap.client, 12.1.0.2.0…
Patching component oracle.oracore.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.deconfig, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.network.listener, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Composite patch 25755742 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2018-06-15_16-50-22PM_1.log
OPatch succeeded.

Step4:-Shutdown Primary database and Listener.

[oracle@primary ~]$ export ORACLE_SID=prime
[oracle@primary ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jun 15 17:02:39 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@primary ~]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-JUN-2018 17:03:26
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539)))
The command completed successfully

Step5:-Apply PSU Patch on Primary Site

[oracle@primary ~]$ cd /u01/25755742/
[oracle@primary 25755742]$ export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH
[oracle@primary 25755742]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.1.0.2/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version : 12.2.0.1.13
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2018-06-15_17-06-26PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 19769480 20299023 20831110 21359755 21948354 22291127 23054246 24006101 24732082 25171037 25755742
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/12.1.0.2/db_1’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying sub-patch ‘19769480’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms.deconfig, 12.1.0.2.0…
Patching component oracle.xdk, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms.util, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.xdk.parser.java, 12.1.0.2.0…
Patching component oracle.oraolap, 12.1.0.2.0…
Patching component oracle.xdk.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.ldap.rsf.ic, 12.1.0.2.0…
Applying sub-patch ‘20299023’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms.deconfig, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.rdbms.rsf.ic, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.ldap.rsf.ic, 12.1.0.2.0…
Applying sub-patch ‘20831110’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.oraolap.dbscripts, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Applying sub-patch ‘21359755’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.assistants.server, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Applying sub-patch ‘21948354’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms.deconfig, 12.1.0.2.0…
Patching component oracle.xdk, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.xdk.parser.java, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.xdk.rsf, 12.1.0.2.0…
Applying sub-patch ‘22291127’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
ApplySession: Optional component(s) [ oracle.oid.client, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.ldap.client, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.oraolap, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.oraolap.dbscripts, 12.1.0.2.0…
Applying sub-patch ‘23054246’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms.dv, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.install.deinstalltool, 12.1.0.2.0…
Patching component oracle.ldap.rsf.ic, 12.1.0.2.0…
Patching component oracle.oracore.rsf, 12.1.0.2.0…
Patching component oracle.ctx, 12.1.0.2.0…
Patching component oracle.xdk, 12.1.0.2.0…
Patching component oracle.nlsrtl.rsf, 12.1.0.2.0…
Patching component oracle.xdk.parser.java, 12.1.0.2.0…
Patching component oracle.ctx.atg, 12.1.0.2.0…
Applying sub-patch ‘24006101’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.sqlplus, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.network.listener, 12.1.0.2.0…
Patching component oracle.network.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.dv, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.sqlplus.ic, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Applying sub-patch ‘24732082’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.rdbms.install.plugins, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Applying sub-patch ‘25171037’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
Patching component oracle.sqlplus.ic, 12.1.0.2.0…
Patching component oracle.sqlplus, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.ordim.client, 12.1.0.2.0…
Patching component oracle.ordim.client, 12.1.0.2.0…
Patching component oracle.ordim.jai, 12.1.0.2.0…
Patching component oracle.ordim.server, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Patching component oracle.rdbms.install.plugins, 12.1.0.2.0…
Patching component oracle.javavm.containers, 12.1.0.2.0…
Applying sub-patch ‘25755742’ to OH ‘/u01/app/oracle/product/12.1.0.2/db_1’
ApplySession: Optional component(s) [ oracle.oid.client, 12.1.0.2.0 ] , [ oracle.has.crs, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.network.rsf, 12.1.0.2.0…
Patching component oracle.ldap.rsf, 12.1.0.2.0…
Patching component oracle.ldap.client, 12.1.0.2.0…
Patching component oracle.oracore.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.deconfig, 12.1.0.2.0…
Patching component oracle.tfa, 12.1.0.2.0…
Patching component oracle.network.listener, 12.1.0.2.0…
Patching component oracle.rdbms, 12.1.0.2.0…
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0…
Patching component oracle.rdbms.rsf, 12.1.0.2.0…
Patching component oracle.rdbms.rman, 12.1.0.2.0…
Composite patch 25755742 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2018-06-15_17-06-26PM_1.log
OPatch succeeded.

Step6:-Enable Log shipping on Primary

Start the database and listener both side

DGMGRL> edit database prime set state=’ONLINE’;
Succeeded.

Step7:-execute Post installation Scripts (datapatch)

[oracle@primary OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Fri Jun 15 18:27:13 2018
Copyright (c) 2012, 2016, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_10585_2018_06_15_18_27_13/sqlpatch_invocation.log
Connecting to database…OK
Bootstrapping registry and package to current versions…done
Determining current state…done
Current state of SQL patches:
Bundle series PSU:
ID 170718 in the binary registry and not installed in the SQL registry
Adding patches to installation queue and performing prereq checks…
Installation queue:
Nothing to roll back
The following patches will be applied:
25755742 (DATABASE PATCH SET UPDATE 12.1.0.2.170718)
Installing patches…
Patch installation complete. Total patches installed: 1
Validating logfiles…
Patch 25755742 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/25755742/21385018/25755742_apply_PRIME_2018Jun15_18_27_37.log (no errors)
SQL Patching tool complete on Fri Jun 15 18:28:34 2018

Step8:-Monitor the redo apply from Primary to Standby

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”,
APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1; 2 3 4 5 6 7 8 9 10

Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1                                    18                           18                                            0

Step9:-Opatch Status 

[oracle@primary ~]$ export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH
[oracle@primary ~]$ opatch lsinventory|grep 25755742
Patch 25755742 : applied on Fri Jun 15 17:11:29 IST 2018
Patch description: “Database Patch Set Update : 12.1.0.2.170718 (25755742)”

[oracle@standby 25755742]$ export PATH=/u01/app/oracle/product/12.1.0.2/db_1/OPatch:$PATH
[oracle@standby 25755742]$ opatch lsinventory|grep 25755742
Patch 25755742 : applied on Fri Jun 15 16:55:37 IST 2018
Patch description: “Database Patch Set Update : 12.1.0.2.170718 (25755742)”

SQL> select PATCH_ID,ACTION_TIME,STATUS,DESCRIPTION from registry$sqlpatch;

PATCH_ID     ACTION_TIME                          STATUS            DESCRIPTION
———- —————————— ————— —
25755742  15-JUN-18 06.28.34.330133 PM SUCCESS DATABASE PATCH SET UPDATE 12.1.0.2.170718

Successfully applied PSU Patch on Dataguard Environment

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 12c-Testing the Oracle Data Guard Fast-Start Failover

Oracle 12c-Testing the Oracle Data Guard Fast-Start FailoverDescription:-

We have already enabled the Fast Start Failover,now we are going to testing the Failover.

Oracle 12c-Configure Oracle Data Guard Fast-Start Failover Here

Environment Details:-

Let’s Start the Demo:-

Step1:-Show the configuration

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxAvailability
Members:
prime – Primary database
stand – (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 10 seconds ago)

Step2:-check the observer session is running to monitor the primary & standby 

oracle@primary ~]$ export ORACLE_SID=prime
[oracle@primary ~]$ 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/oracle@prime
Connected as SYSDBA.
DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> start observer;
Observer started

SQL> select FS_FAILOVER_STATUS,FS_FAILOVER_OBSERVER_PRESENT from v$database;

FS_FAILOVER_STATUS FS_FAIL
———————- ——-
SYNCHRONIZED YES

Step3:-kill the Smon process in primary database

To simulate a fast start failover,i will kill the smon proceess in primary database so that it looses the connectivity with the observer and the standby database.

[oracle@primary ~]$ ps -ef|grep smon
oracle 4528 1 0 00:13 ? 00:00:00 ora_smon_prime
oracle 7150 7136 0 03:48 pts/4 00:00:00 grep smon
[oracle@primary ~]$ kill -9 4528
[oracle@primary ~]$

Step4:-observer start the failover

DGMGRL> start observer;
Observer started

03:49:34.53 Thursday, June 14, 2018
Initiating Fast-Start Failover to database “stand”…
Performing failover NOW, please wait…
Failover succeeded, new primary is “stand”
03:49:41.17 Thursday, June 14, 2018

Step5:-Automatically Initiating the reinstate command in observer session(just mount the old primary database)

03:52:49.20 Thursday, June 14, 2018
Initiating reinstatement for database “prime”…
Reinstating database “prime”, please wait…
Reinstatement of database “prime” succeeded
03:53:10.82 Thursday, June 14, 2018

Step6:-show the configuration 

DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxAvailability
Members:
stand – Primary database
prime – (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 54 seconds ago)

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 12c-Configure Oracle Data Guard Fast-Start Failover

Description:-

  • This feature increases the availability of the database by eliminating the need for DBA involvement as part of the failover process.
  •  Fast-Start Failover enables the broker to perform these tasks automatically failover to standby database
  • The key to this feature is a monitoring process appropriately named the Observer.
  • The Observer is a component of the DGMGRL interface that is configured on a system outside the systems actually running the Oracle Data Guard configuration, which monitors the availability of the primary database.
  • it will issue a failover after waiting the number of seconds specified by the FastStartFailoverThreshold property

Some of the below circumstance it will start Failover

Datafile Offline Failover is initiated if a datafile on the primary database experiences an I/O error resulting in a datafile being taken offline. This option is enabled by default.
Corrupted Dictionary Failover is initiated if corruption of a critical database object is found. This option is enabled by default.
Corrupted Controlfile Enabled by default, the detection of controlfile corruption will result in immediate failover.
Inaccessible Log File This parameter, disabled by default, allows for failover to be initiated in the event that LGWR is unable to write to a member of a log group.
Stuck Archiver Failover is initiated should the archiver on the primary database become hung. The default setting of this parameter is disabled.

High level steps to configure Data Guard Fast-Start Failover

  1. Configure Oracle Network
  2. Prepare the primary database
  3. Create a physical standby
  4. Enable Flashback Database
  5. Create a Broker configuration
  6. Configure the observer
  7. Enable and test FSFO

Enable the Fast-Start Failover, Oracle Data Guard configuration must meet the following requirements:

  1. Flashback Database must be enabled for both the primary and standby database target.

select flashback_on from v$database;
FLASHBACK_ON
——————
YES

2. An observer server (server outside of the Oracle Data Guard configuration) should be established to monitor the environment.

See the below URL’s  for Dataguard configuration   

Step by Step Configuration Of Data Guard Broker in Oracle 12c Here
Oracle 12c-Performing Dataguard Switchover Using DGMGRL Utility Here
Oracle 12c-Performing Dataguard Failover Using DGMGRL Utility Here
Oracle 12c-Performing Reinstating a Failed Primary Database Using DGMGRL Utility Here
Oracle 12c-Converting a Physical Standby to a Snapshot Standby using DGMGRL Utility Here

Environment Details:-

Let’s start the Demo:-(Before enable the Fast start Failover we need configure the dataguard broker)

Step by Step Configuration Of Data Guard Broker in Oracle 12c Here

Step1:-Show the Configuration

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 48 seconds ago)

Step2:-Enabling Fast-Start Failover (changing the Maximum Performance mode to Max Availability mode)

Fast-start failover can be enabled for either maximum availability mode or maximum performance mode

DGMGRL> EDIT DATABASE ‘prime’ SET PROPERTY ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated
DGMGRL> EDIT DATABASE ‘stand’ SET PROPERTY ‘LogXptMode’=’SYNC’;
Property “LogXptMode” updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

Step3:- Check the Data Guard Protection Modes

DGMGRL> show configuration verbose;
Configuration – hari
Protection Mode: MaxAvailability
Members:
prime – Primary database
stand – 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

Step4:-Enable the observer

  • The observer is integrated in the DGMGRL client-side component of the broker and typically runs on a different computer from the primary or standby databases and from the computer where you manage the broker configuration.
  • Idealy we need to configure the observer should be started on another host, to observe the primary, as if the host goes down it can monitor the outage and do specify actions.
  • Note:-we start the observer in primary node here(192.168.1.24-primary) I am not configure 3 node for the observer)

Open in Another Terminal in primary side

[oracle@primary ~]$ export ORACLE_SID=prime
[oracle@primary ~]$ 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/oracle@prime
Connected as SYSDBA.
DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> start observer;
Observer started

The session will seem to hang. You can do a control C. Or create and run the script as nohup. (other way to start the observer)

nohup dgmgrl sys/oracle@orcl “start observer file=’/home/oracle/fsfo.dat'” -logfile $HOME/observer.log &

Step5:-check the fast start failover status

DGMGRL> show fast_start failover
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: stand
Observer: primary
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxAvailability
Members:
prime – Primary database
stand – (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 10 seconds ago)

Successfully configured the Oracle Data Guard Fast-Start Failover

Oracle 12c-Testing the Oracle Data Guard Fast-Start Failover here

Reference:-

https://docs.oracle.com/database/121/DGBKR/sofo.htm#DGBKR392

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 12c-Converting a Physical Standby to a Snapshot Standby using DGMGRL Utility

Oracle 12c-Converting a Physical Standby to a Snapshot Standby using DGMGRL UtilityDescription:-

  • Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode.
  • When switched back into standby mode,All changes made whilst in read-write mode are lost. This is achieved using flashback database,
    Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions.
    Quite often we have the standby database opened in READ Only mode for reporting purposes and optionally have the active dataguard implemented,
    but a snapshot standby database can be used to perform both READ and WRITE activities. Most importantly, a snapshot standby database keeps receiving the redo data from the primary database but does not apply them.
    These redo data received from the primary database would be applied only when the snapshot standby database is converted back to the Physical standby mode.
    There by the snapshot standby database provides data protection on primary database.

Environment Details:-

Let’s Start the Demo:-

Step1:-Show the Configuration

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 20 seconds ago)

Step2:-Issue the convert Command

DGMGRL> convert database ‘stand’ to snapshot standby;
Converting database “stand” to a Snapshot Standby database, please wait…
Database “stand” converted successfully

Step3:-Show the Configuration

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 25 seconds ago)

Step4:-Convert backup to Physical standby database

DGMGRL> CONVERT DATABASE ‘stand’ to PHYSICAL STANDBY;
Converting database “stand” to a Physical Standby database, please wait…
Operation requires shut down of instance “stand” on database “stand”
Shutting down instance “stand”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance “stand” on database “stand”
Starting instance “stand”…
ORACLE instance started.
Database mounted.
Continuing to convert database “stand” …
Database “stand” converted successfully

Step5:-Show the Configuration

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 48 seconds ago)

Successfully completed the Converted a Physical Standby database to a Snapshot Standby database using DGMGRL Utlility

Reference:-

https://docs.oracle.com/database/121/DGBKR/cli.htm#DGBKR495

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 12c-Performing Reinstating a Failed Primary Database Using DGMGRL Utility

Oracle 12c-Performing Reinstating a Failed Primary Database Using DGMGRL Utility

Description:-

  • If your former primary database was configured with Flashback Database, you can easily reinstate the failed primary database as a standby database of the new primary database.
  • The failed primary database will be reinstated as a standby type that matches the old standby database.
  •  For example, if you failed over to a physical standby database, the old primary will be reinstated as a physical standby database.

Let’s start the Demo:-

Step1:-Restart the Old Primary Database.

[oracle@primary ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 13 02:16:25 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1660944384 bytes
Fixed Size 2925072 bytes
Variable Size 1056968176 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.

Step2:-Reinstate the old primary database.

[oracle@standby ~]$ 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/oracle@stand
Connected as SYSDBA.
DGMGRL> REINSTATE DATABASE ‘prime’;
Reinstating database “prime”, please wait…
Reinstatement of database “prime” succeeded

Step3:-Show the Configuration and Databases.

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
stand – Primary database
prime – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 19 seconds ago)

Step4:-Verify the standby status

DGMGRL> show database ‘prime’;
Database – prime
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: 10.00 KByte/s
Real Time Query: OFF
Instance(s):
prime
Database Status:
SUCCESS

DGMGRL> show database ‘stand’;
Database – stand
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
stand
Database Status:
SUCCESS

Successfully completed the Reinstating a Failed Primary Database using DGMGRL Utlility

Reference:-

http://oradb-srv.wlv.ac.uk/E16655_01/server.121/e17641/cli.htm#i1007698

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 12c-Performing Dataguard Failover Using DGMGRL Utility

Oracle 12c-Performing Dataguard Failover Using DGMGRL Utility

Description:-

  • We invoke a failover operation in response to an emergency situation, usually when the primary database cannot be accessed or is unavailable.
  • We need to enable the flashback database for both side.So that we will REINSTATE easily after failover.

Environment Details:-

Note:-Now primary database is Prime After the failover primary database is Stand

Let’s start the Demo:-

Step1:-Show the configuration (login into standby  side)

[oracle@standby ~]$ 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/oracle@stand
Connected as SYSDBA.

Step2:-Issue the SHOW CONFIGURATION command to verify the failover.
DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 11 seconds ago)

Step3:-Check the readiness of the target standby.

To validate the target standby database to ensure that it’s ready to become the new primary database, use the VALIDATE DATABASE command

DGMGRL> VALIDATE DATABASE ‘stand’;
Database Role: Physical standby database
Primary Database: prime
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)

Step4:-Connect to the target standby database

To perform the failover operation, you must connect to the standby database

[oracle@standby ~]$ 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/oracle@stand
Connected as SYSDBA.

Step5:-Issue the failover command

Now you can issue the failover command to make the target standby database the new primary database for the configuration.

DGMGRL> FAILOVER TO ‘stand’;
Performing failover NOW, please wait…
Failover succeeded, new primary is “stand”

Step6:-Show the configuration.

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
stand – Primary database
prime – Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 1 second ago)

Successfully Failover completed using DGMGRL Utlility

Reference:-

https://docs.oracle.com/database/121/DGBKR/cli.htm#DGBKR495

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 12c-Performing Dataguard Switchover Using DGMGRL Utility

Oracle 12c-Performing Dataguard Switchover Using DGMGRL UtilityDescription:-

  • We are going to switch the role of the primary database and a standby database using the SWITCHOVER command
  • The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON
  • Standby redo log files are configured on the primary database.
  •  The broker configuration file is updated to reflect the change in roles.
  • Restarts the new standby database and begins the redo apply service.
  •  The new primary database is opened in read/wirte mode and the redo transport service is started.

Environment Details:-

Note:-Now primary database is Prime After the switchover primary database is Stand

Let’s Start the Demo:-

Step1:-Check the Primary Database

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database

DGMGRL> SHOW DATABASE VERBOSE ‘prime’;
Database – prime
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prime
Properties:
DGConnectIdentifier = ‘prime’
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’
DbFileNameConvert = ‘/u01/app/oracle/oradata/STAND/datafile, /u01/app/oracle/oradata/PRIME/datafile’
LogFileNameConvert = ‘/u01/app/oracle/oradata/STAND/onlinelog, /u01/app/oracle/oradata/PRIME/onlinelog’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539))(CONNECT_DATA=(SERVICE_NAME=PRIME_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t_%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
SUCCESS

Step2:-Check the Standby Database That is the Target of the Switchover

Use the SHOW DATABASE command to check the status of the standby database that is the target of the switchover

DGMGRL> SHOW DATABASE ‘stand’;
Database – stand
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
Real Time Query: OFF
Instance(s):
stand
Database Status:
SUCCESS

Step3:-Confirm That the Database Is Ready for a Role Change

12c New Feature

Prior to performing a role change, you can use the VALIDATE DATABASE command to perform
an exhaustive set of checks on the database to confirm that it is ready for a role change.

DGMGRL> VALIDATE DATABASE ‘prime’;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
prime: Off
DGMGRL> VALIDATE DATABASE ‘stand’;
Database Role: Physical standby database
Primary Database: prime
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
prime: Off
stand: Off

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 10 seconds ago)

Step4:-Issue the Switchover Command

Issue the SWITCHOVER command to swap the roles of the primary and standby databases

DGMGRL> switchover to ‘stand’;
Performing switchover NOW, please wait…
Operation requires a connection to instance “stand” on database “stand”
Connecting to instance “stand”…
Connected as SYSDBA.
New primary database “stand” is opening…
Operation requires start up of instance “prime” on database “prime”
Starting instance “prime”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “stand”

Step5:-Show the Configuration

DGMGRL> SHOW CONFIGURATION;
Configuration – hari
Protection Mode: MaxPerformance
Members:
stand – Primary database   (Now primary database is Stand)
prime – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 28 seconds ago)

Successfully Switchover completed using DGMGRL Utlility

Reference:-

https://docs.oracle.com/database/121/DGBKR/cli.htm#DGBKR495

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Step by Step Configuration Of Data Guard Broker in Oracle 12c

Step by Step Configuration Of Data Guard Broker in Oracle 12c

Description:- 

  • Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration
  • Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration.
  • DGMGRL does not have the ability to create standby (GUI can do it). CLI is used mostly for configuration and management.
  • Easy switchover/failover with one command thereby minimizing overall downtime associated with the planned/unplanned outage
  • Instead of managing primary and standby databases with various SQL*Plus statements, broker provides a single unified configuration
  • Broker keeps its configuration details in flat file. The files are stored at each database nodes in the Data Guard configuration.Additionally two copies of the configuration files are always stored on each database for redundancy.
    Below parameters control where the configuration files will be stored.
    DG_BROKER_CONFIG_FILE1 & DG_BROKER_CONFIG_FILE2

Step by Step to configure Oracle 12c Data Guard Physical Standby Here 

Environment Details:-

Let’s Start the Demo:-

Step1:-Creating a Service Listener in primary and standby side(Both Side)

[oracle@primary dbs]$ cd /u01/app/oracle/product/12.1.0.2/db_1/network/admin/
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prime)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = prime)
)
(SID_DESC =
(GLOBAL_DBNAME = stand)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = stand)
)
(SID_DESC =
(GLOBAL_DBNAME = prime_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = prime)
)
(SID_DESC =
(GLOBAL_DBNAME = stand_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = stand)
)
)

After the changes in the listener file reload the listener

[oracle@primary dbs]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 12-JUN-2018 08:16:51
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539)))
The command completed successfully
[oracle@primary dbs]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 12-JUN-2018 08:16:53
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_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.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1539)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 12-JUN-2018 08:16:53
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.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/primary/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1539)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “prime” has 1 instance(s).
Instance “prime”, status UNKNOWN, has 1 handler(s) for this service…
Service “prime_DGMGRL” has 1 instance(s).
Instance “prime”, status UNKNOWN, has 1 handler(s) for this service…
Service “stand” has 1 instance(s).
Instance “stand”, status UNKNOWN, has 1 handler(s) for this service…
Service “stand_DGMGRL” has 1 instance(s).
Instance “stand”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

Step2:-Changing the parameters for both side

Primary

SQL> ALTER SYSTEM SET dg_broker_start=true scope=spfile;
System altered.

SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prime)))’;
System altered.

Standby

SQL> ALTER SYSTEM SET dg_broker_start=true scope=spfile;
System altered.

SQL>ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stand)))’;
System altered.

Step3:- login in to the primary database through DGMGRL 

[oracle@primary ~]$ export ORACLE_SID=prime
[oracle@primary ~]$ 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/oracle@prime
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL

Step4:-Configure Dataguard Broker for primary

DGMGRL> create configuration ‘hari’ as primary database is ‘prime’ connect identifier is prime;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added Failed.

Fix for this issue

SQL> alter system set LOG_ARCHIVE_DEST_2= ‘ ‘; (both Side)
System altered.

DGMGRL> create configuration ‘hari’ as primary database is ‘prime’ connect identifier is prime;
Configuration “hari” created with primary database “prime”

DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

Step5:-Configure Dataguard Broker for standby

DGMGRL> Add database ‘stand’ as connect identifier is stand maintained as physical;
Database “stand” added

DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

Step6:-Check using Verbose command

DGMGRL> show database verbose ‘stand’

Database – stand
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Active Apply Rate: (unknown)
Maximum Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
stand
Properties:
DGConnectIdentifier = ‘stand’
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’
DbFileNameConvert = ‘/u01/app/oracle/oradata/PRIME/datafile/, /u01/app/oracle/oradata/STAND/datafile/’
LogFileNameConvert = ‘/u01/app/oracle/oradata/PRIME/onlinelog/, /u01/app/oracle/oradata/STAND/onlinelog/’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.25)(PORT=1539))(CONNECT_DATA=(SERVICE_NAME=stand_DGMGRL)(INSTANCE_NAME=stand)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t_%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
DISABLED

DGMGRL> show database verbose ‘prime’

Database – prime
Role: PRIMARY
Intended State: OFFLINE
Instance(s):
prime
Properties:
DGConnectIdentifier = ‘prime’
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’
DbFileNameConvert = ‘/u01/app/oracle/oradata/STAND/datafile, /u01/app/oracle/oradata/PRIME/datafile’
LogFileNameConvert = ‘/u01/app/oracle/oradata/STAND/onlinelog, /u01/app/oracle/oradata/PRIME/onlinelog’
FastStartFailoverTarget = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1539))(CONNECT_DATA=(SERVICE_NAME=PRIME_DGMGRL)(INSTANCE_NAME=prime)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’
AlternateLocation = ”
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t_%s_%r.dbf’
TopWaitEvents = ‘(monitor)’
Database Status:
DISABLED

Step7:-enable the Dataguard broker

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxPerformance
Members:
prime – Primary database
stand – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 12 seconds ago)

DGMGRL> enable database ‘stand’;
Enabled.
DGMGRL> show database ‘stand’;
Database – stand
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: 3.00 KByte/s
Real Time Query: OFF
Instance(s):
stand
Database Status:
SUCCESS

Successfully configured the Dataguard Broker in 12c database.

Reference:-

https://docs.oracle.com/database/121/DGBKR/concepts.htm#DGBKR001

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba