Step by Step to configure Oracle 19c Data Guard Physical Standby

Environment Details

Parameters needs to configure both side for Data guard,

Primary side:
db_name:- Same name for all primary and standby database
db_unique_name:-a unique db name to uniquely identify primary and standby db because db_name is same
Log_archive_dest_1:-local archive destination
Log_archive_dest_2:-destination for standby database
Standby_file_management:-automatically  create file at standby db site

Standby Side:
db_unique_name:-it has to be different from DB_NAME parameter
db_file_name_convert:-specify the path name and datafile location of primary database datafile followed by standby location
log_file_name_convert:-specify the path name and redo logfile location of primary database redo logfile followed by standby location
log_archive_dest_1:-location of archives coming from primary database
fal_server:-to fetch archived log from primary site when log gap is detected at standby site.The parameter value is tns service name
fal_client:- to copies missing archive to standby database

Step 1:-Check FORCE LOGGING is enabled

Step 2:- Make sure primary is in archivelog mode

Step 3:- PFILE creation and copy to standby database

Step 4:-Edit the parameters and directories in created pfile

Step 5:- Parameters for primary database

Step 6:- Copy password file from primary to standby server

Step 7:- Make the respective directories for standby database

Step 8:- Keep the database in NOMOUNT stage to create standby database

Step 9:- Create spfile for standby database

Step 10:- Listener and TNS Configuration

Step 11:-Start the listener in Standby database and check the status

Step 12:-In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)

Step 13:-Active Duplication for creating standby database

Step 14:- Post check status for Standby database

Step 15:- Enable the recovery(MRP Process)

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

PRIMARY SIDE CONFIGURATION:-

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 13 18:58:05 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.

SQL> select force_logging from v$database;

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

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3
SQL> shut immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> create pfile from spfile;

File created.


Check the Datafiles,Log files location in Primary side:

SQL> show parameter db_name;

NAME                     TYPE            VALUE
---------------------- ----------- ------------------
db_name                  string          dbwr

SQL> show parameter db_unique_name;

NAME TYPE VALUE
---------------------- ----------- --------------------
db_unique_name            string         dbwr

SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------------------------
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_system_g78vt7wo_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_sysaux_g78vx8f6_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_users_g78vyq0c_.dbf

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/datafile/o1_mf_undotbs1_g78vyor1_.dbf

Dynamically change parameter and Configure it in Primary side:

SQL> alter system set log_archive_config='dg_config=(dbwr,standby)';

System altered.

SQL> alter system set log_archive_dest_2='service=standby noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=standby';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=STANDBY;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Create standby controlfile:

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo1.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo2.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo3.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/product/19.0.0/dbhome_1/dbs/DBWR/redo4.log') size 50m;

Database altered.

PRIMARY SIDE:

[oracle@ram admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
)
[oracle@ram admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr)
(UR = A)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
(UR = A)
)
)
[oracle@ram admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-MAR-2019 00:43:46

Copyright (c) 1991, 2018, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias DBWR
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 15-MAR-2019 22:54:24
Uptime 0 days 1 hr. 49 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ram/dbwr/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ram.doyen.com)(PORT=1521)))
Services Summary...
Service "dbwr" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Service "dbwrXDB" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ram admin]$

STANDBY SIDE:

[oracle@standby ~]$ cd $ORACLE_HOME
[oracle@standby dbhome_1]$ cd network/admin/
[oracle@standby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_STANDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dbwr)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = dbwr)
)
)

STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
)
[oracle@standby admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr)
(UR = A)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.doyen.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
(UR = A)
)
)

[oracle@standby admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-MAR-2019 00:57:37

Copyright (c) 1991, 2018, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias STANDBY
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-MAR-2019 00:00:08
Uptime 0 days 0 hr. 57 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/standby/standby/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.doyen.com)(PORT=1521)))
Services Summary...
Service "dbwr" has 1 instance(s).
Instance "dbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@standby admin]$

In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)

Active Duplication for creating standby database:

[oracle@standby ~]$ export ORACLE_SID=dbwr
[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 16 00:34:57 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2415917872 bytes
Fixed Size 8899376 bytes
Variable Size 654311424 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
SQL> !
[oracle@standby ~]$ rman target sys/oracle@dbwr auxiliary sys/oracle@standby

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 16 00:35:46 2019
Version 19.2.0.0.0

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

connected to target database: DBWR (DBID=1335353556)
connected to auxiliary database: DBWR (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 16-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=39 device type=DISK

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwdbwr' ;
}
executing Memory Script

Starting backup at 16-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=121 device type=DISK
Finished backup at 16-MAR-19

contents of Memory Script:
{
restore clone from service 'dbwr' standby controlfile;
}
executing Memory Script

Starting restore at 16-MAR-19
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/admin/standby/control1.ctl
output file name=/u01/app/oracle/admin/standby/control2.ctl
Finished restore at 16-MAR-19

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/admin/standby/o1_mf_temp_g78w14cc_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/admin/standby/o1_mf_system_g78vt7wo_.dbf";
set newname for datafile 3 to
"/u01/app/oracle/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf";
set newname for datafile 4 to
"/u01/app/oracle/admin/standby/o1_mf_undotbs1_g78vyor1_.dbf";
set newname for datafile 7 to
"/u01/app/oracle/admin/standby/o1_mf_users_g78vyq0c_.dbf";
restore
from non sparse from service
'dbwr' clone database
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/admin/standby/o1_mf_temp_g78w14cc_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-MAR-19
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/admin/standby/o1_mf_system_g78vt7wo_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:59
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/admin/standby/o1_mf_undotbs1_g78vyor1_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service dbwr
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/admin/standby/o1_mf_users_g78vyq0c_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-MAR-19

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=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_system_g78vt7wo_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_sysaux_g78vx8f6_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_undotbs1_g78vyor1_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1003020439 file name=/u01/app/oracle/admin/standby/o1_mf_users_g78vyq0c_.dbf
............
............
............
Finished Duplicate Db at 16-MAR-19

RMAN> exit


Enable the recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Post check status for Standby database

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

NAME               DATABASE_ROLE                 OPEN_MODE
---------          ----------------          ----------------
DBWR               PHYSICAL STANDBY              MOUNTED

Check the Standby database sync status with primary:

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE#         APPLIED
----------        ---------
    9               YES
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 19c-Step by Step Manual Data Guard Failover

Failover:-

In case of worst situation with data guard primary database, or not available for production than we can activated standby database as a primary production database.

Data guard Configuration details:-

Login in to Standby Server:-

Step:1 Check the database role and open_mode
Select name,open_mode,database_role from v$database;

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

Step:2 Cancel the MRP process

[oracle@standby dbs]$ ps -ef | grep mrp
oracle 79976 1 0 03:19 ? 00:00:02 ora_mrp0_dbwr
oracle 81638 81585 0 03:43 pts/2 00:00:00 grep --color=auto mrp
[oracle@standby dbs]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 16 03:43:18 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL> recover managed standby database cancel;
Media recovery complete.

Step 3:

The below commands will help to bring up standby as primary

alter database recover managed standby database finish;

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database recover managed standby database finish;

Database altered.

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

NAME        OPEN_MODE           DATABASE_ROLE
--------- -------------------- ----------------
DBWR        MOUNTED             PHYSICAL STANDBY

SQL> alter database activate standby database;

Database altered.

Managed recovery process has been stopped between primary and standby database and standby becomes primary database.

Step 5:

 Bounce your database and verify database name its open mode and its role.

shutdown immediate;

Startup

Step 6:

select name,open_mode,database_role from v$database;

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

Now your old standby database is become primary database

It is highly recommended to consider immediate full backup of primary database.

The original primary database can now be configured as a standby.

If Flashback Database was enabled on the primary database.If not, the whole setup process must be followed, but this time using the original primary server as the standby.

After failover activity,

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 19c-Step by Step Configure Snapshot Standby Database

Description:-

  • Oracle Database 11g introduced the Snapshot Standby feature in Data Guard
  • A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database.
  • A snapshot standby database receives and archives, but does not apply,
    redo data from a primary database.
  • The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

Dataguard Configuration:-

Step:1 Check both primary and standby role.

Primary:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME     DATABASE_ROLE     OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN            class             PRIMARY         READ WRITE

Standby:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS         INSTANCE_NAME    DATABASE_ROLE        OPEN_MODE
------------ ---------------- ----------------   --------------------
MOUNTED        class            PHYSICAL STANDBY     MOUNTED

Step 2:- Check the archived sequence in standby,

select thread#,max(sequence#) from v$archived_log group by thread#;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
---------- --------------
1              37

Step 3:- Check the flashback status and db_recovery_file_set location,

select flashback_on from v$database;

show parameter db_recovery_file_dest

SQL> show parameter db_recovery_file_dest

NAME                          TYPE            VALUE
-------------------------- ----------- ------------------------------
db_recovery_file_dest       string      /u01/app/oracle/recovery_dest/
db_recovery_file_dest_size  big integer            2G


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step:4 In standby side,stop the mrp process.

 alter database recover managed standby database cancel;

SQL> recover managed standby database cancel;
Media recovery complete.

Step:5 Bounce the database and keep in mount stage,

shut immediate

startup mount

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 583008256 bytes
Database Buffers 348127232 bytes
Redo Buffers 3678208 bytes
Database mounted.

Step:6 Convert to snapshot standby database

alter database convert to snapshot standby

SQL> alter database convert to snapshot standby;

Database altered.

Step:7 Open the standby database in READ/WRITE mode

alter database open

SQL> alter database open;

Database altered.

Step:8 Check the DATABASE_ROLE and OPEN_MODE,

select status,instance_name,database_role,open_mode from v$database,v$Instance

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS        INSTANCE_NAME     DATABASE_ROLE       OPEN_MODE
------------ ----------------  ----------------  --------------------
OPEN            class          SNAPSHOT STANDBY    READ WRITE

Step:9 Testing on the snapshot standby database,

create user test identified by test;
grant connect,resource to test;
conn test/test
create table t1(sno number, name varchar2(20));
insert into t1 values (1,’example’);
commit;

SQL> alter user test quota unlimited on USERS;

User altered.

SQL> conn test/test;
Connected.

SQL> create table t1(sno number, name varchar2(20));

Table created.

SQL> insert into t1 values (1,'example');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

SNO           NAME
---------- --------------------
1             example

SQL>

Step:10 Both side verify the archived sequence

Primary side
select thread#,max(sequence#) from v$archived_log group by thread#;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
----------  --------------
1              47

Standby side

select thread#,max(sequence#) from v$archived_log group by thread#;
select process,status,sequence# from v$managed_standby;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
---------- --------------
1               47

Step 11: In Standby Side,bounce and mount the database

shut immediate
startup mount

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 759169024 bytes
Database Buffers 171966464 bytes
Redo Buffers 3678208 bytes
Database mounted.

Step:12 Convert the snapshot standby database to physical standby database,

alter database convert to physical standby;

SQL> alter database convert to physical standby;

Database altered.

Step:13 Bounce the database,

shut immediate
startup

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 759169024 bytes
Database Buffers 171966464 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.

Step:14 Check the standby database_role and mode.

select status,instance_name,database_role,open_mode from v$database,v$Instance;

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS        INSTANCE_NAME    DATABASE_ROLE      OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN          class           PHYSICAL STANDBY    READ ONLY

Step:15 Enable the MRP process
alter database recover managed standby database disconnect from session;

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

Database altered.

Step:16 After convert to physical standby database,check the table status

Note:  After convert back to Physical standby database  once again restart the MRP process.

SQL> select * from test.t1;
select * from test.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

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 18c Data Guard Broker Enhancements

Description:-

These are the changes in Oracle Data Guard Broker in Oracle Database Release 18c

This release provides several new commands:

  • VALIDATE DATABASE SPFILE performs a comparison of server parameter file (SPFILE) entries between the primary database and a specified standby database.
  • VALIDATE NETWORK CONFIGURATION performs network connectivity checks between members of a broker configuration.
  •  VALIDATE STATIC CONNECT IDENTIFIER validates the static connect identifier of a database.
  •  SET ECHO controls whether or not to echo commands that are issued either at the command-line prompt or from a DGMGRL script.
  • SHOW ALL (DGMGRL CLI properties) shows the values of DGMGRL CLI properties.

The V$DATAGUARD_PROCESS view, introduced in Oracle Database 12c Release 2 (12.2.0.1), now shows broker processes. (Oracle Data Guard Monitor (DMON)
Process.)

SQL> select name,TYPE,role from V$DATAGUARD_PROCESS;

NAME  TYP ROLE
----- --- ------------------------
LGWR  KSB log writer
TMON  KSB redo transport monitor
DMON  KSB broker monitor
INSV  KSB broker instance slave
NSV2  KSB broker net slave
TT00  KSV gap manager
TT01  KSV redo transport timer
ARC0  KSB archive local
TT02  KSV async ORL multi
TT03  KSV heartbeat redo informer
ARC1  KSB archive redo
ARC2  KSB archive redo
ARC3  KSB archive redo
RSM0  KSB broker worker
drcx  NET switchover
15 rows selected.

VALIDATE DATABASE SPFILE

The VALIDATE DATABASE SPFILE command performs a comparison of server parameter file (SPFILE) entries between the primary database and a specified standby database.

The validation of the server parameter file detects parameter value discrepancies between the primary and the specified standby database so that they can be rectified before a role change, thus ensuring that after a role change the databases perform at the same level they did prior to the role change. Additionally, using this command frees you from having to restart a database to correct improperly set parameters.

Format
VALIDATE DATABASE {database-name} SPFILE;

If the database to be validated is the primary database, then a message is returned saying the command cannot be issued on a primary database.

DGMGRL> VALIDATE DATABASE dbwrprim SPFILE;
This command cannot be used for the primary database.
DGMGRL> VALIDATE DATABASE dbwrstby SPFILE;
Connecting to "dbwrprim".
Connected to "dbwrprim"
Connecting to "dbwrstby".
Connected to "dbwrstby"
Parameter settings with different values:
audit_file_dest:
dbwrprim (PRIMARY) : /oradb/app/oracle/admin/dbwrprim/adump
dbwrstby : /oradb/app/oracle/admin/dbwrstby/adump
compatible:
dbwrprim (PRIMARY) : 18.0.0
dbwrstby : 18.3.0.0.0
dispatchers:
dbwrprim (PRIMARY) : (PROTOCOL=TCP) (SERVICE=dbwrprimXDB)
dbwrstby : (PROTOCOL=TCP) (SERVICE=dbwrstbyXDB)
fal_client:
dbwrprim (PRIMARY) : dbwrprim
dbwrstby : dbwrstby
sga_target:
dbwrprim (PRIMARY) : 1201668096
dbwrstby : 943718400

VALIDATE NETWORK CONFIGURATION

The VALIDATE NETWORK CONFIGURATION command performs network connectivity checks between members of a configuration.

The connect identifier for each connectivity check is generated based on the
DGConnectIdentifier property of the associated database.

Format
VALIDATE NETWORK CONFIGURATION FOR { ALL | member name };

Validating Network Configuration for a Specific Database

DGMGRL> VALIDATE NETWORK CONFIGURATION FOR dbwrstby;
Connecting to instance "dbwrstby" on database "dbwrstby" ...
Checking connectivity from instance "dbwrstby" on database "dbwrstby" to
instance "dbwrprim" on database "dbwprim"...
Succeeded.
Oracle Clusterware is not configured on database "dbwrstby".

VALIDATE STATIC CONNECT IDENTIFIER

The VALIDATE STATIC CONNECT IDENTIFIER command validates the static connect identifier of a database.
To perform this validation, the broker makes a new connection to the database using a static connect identifier based on the StaticConnectIdentifer property of the database. A new attribute, STATIC_SERVICE=TRUE is added to the connect identifier to ensure that a connection to the database is established using only a static service, not a dynamic service.

Format
VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | database name };

Command Examples
Validation of Static Connect Identifier For a Database on Which
Oracle Clusterware Is Configured

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR dbwrstby;
Oracle Clusterware is not configured on database "dbwrstby".

SHOW ALL

The SHOW ALL command shows the values of DGMGRL CLI properties.

Format
SHOW ALL;

Command Example

DGMGRL> SHOW ALL;
debug ON
echo OFF
time OFF
observerconfigfile = observer.ora

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba 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-2 Node Rac To Single Instance Standby Database Setup

Steps for creating Single instance standby database from RAC primary database :-

  1. Change the archive log mode :
$ sqlplus / as sysdba

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode                  Archive Mode
Automatic archival                 Enabled
Archive destination                +DG01
Oldest online log sequence         299300
Next log sequence to archive       299305
Current log sequence               299305

2. Enable force logging mode:

SQL> select force_logging from v$database;

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

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

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

3.  Parameter Configuration setup:

SQL> alter system set log_archive_config='DG_CONFIG=(prod,proddr)' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prod/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=proddr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddr' SCOPE=both sid='*';
SQL> alter system set fal_server=prod SCOPE=both sid='*';

System altered.

SQL> alter system set fal_client=proddr SCOPE=both sid='*';

System altered.

SQL> alter system set standby_file_management=auto SCOPE=both sid='*';

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

4. Standby Listener Configuration:

[oracle@proddr01 ]$ export ORACLE_SID=prod
[oracle@proddr01 ]$ export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
[oracle@proddr01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@proddr01 admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = prod )

    )

  )

LISTENER_PRODDR=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = proddr01)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

5. TNS Connection string Configuration :

Standby and primary tnsnames.ora entry should be available in both nodes:

[oracle@proddr01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@proddr01 admin]$ cat tnsnames.ora

PROD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))

    )

    (CONNECT_DATA =

       (SERVER = DEDICATED)

        (SID = prod1)

    )

  )

PRODDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = proddr01)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = prod)

    )

  )

6. Create respective directories in Standby Server:

[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/ctrl
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/data
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/logs
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/oradata/proddr/arch
[oracle@proddr01 admin]$ mkdir /oracle/app/oracle/admin/proddr/adump

7. Start Standby listener :

[oracle@proddr01 admin] $lsnrctl start LISTENER_PRODDR

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2019 14:05:49

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting listener to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=proddr01.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_PRODDR
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 03-DEC-2018 14:09:08
Uptime 55 days 23 hr. 56 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/proddr01/listener_proddr/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=proddr01)(PORT=1521)))
Services Summary...
Service "proddr" has 1 instance(s).
Instance "proddr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8. Copying password & parameter file to standby server:

  • After copying pfile,only keep the parameter entry in PFILE:

db_name

[oracle@proddr01 ]$ cd $ORACLE_HOME/dbs
[oracle@prod1 dbs]$ scp initprod.ora orapwprod
oracle@proddr01:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs oracle@proddr01's password: 
initprod.ora  100% 1536     1.5KB/s   00:00
orapwprod     100% 1536     1.5KB/s   00:00                                 
[oracle@proddr01 dbs]$ cat initprod.ora

db_name='prod'

9. Check connectivity between primary and standby side :

[oracle@proddr01 ]$ tnsping prod   [In boths the nodes]

[oracle@proddr01 ]$ tnsping proddr    [In boths the nodes]

10. Standby Database Creation :

Startup in nomount stage :

[oracle@proddr01 ]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 29 01:12:25 2019

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes

11. Connect RMAN to create standby database,

Set cluster_database is FALSE.

[oracle@proddr01 ]$ rman target sys/****@prod auxiliary sys/****@proddr
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 27 16:15:10 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD (DBID=1459429229)
connected to auxiliary database: PROD (not mounted)

RMAN> run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','proddr'
set db_file_name_convert='+DG01/prod/datafile','/oradata1/proddr/data' 
set db_unique_name='proddr'
set cluster_database='false'
set log_file_name_convert='+DG01/prod/onlinelog','/oradata1/proddr/logs' 
set control_files='/oracle/app/oracle/oradata/proddr/ctrl/control.ctl'
set fal_client='proddr'
set fal_server='prod'
set audit_file_dest='/oracle/app/oracle/admin/proddr/adump'
set log_archive_config='dg_config=(proddr,prod)'
set log_archive_dest_1='location=location=/oradata1/prod/arch'
set log_archive_dest_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
set sga_target='50GB'
set sga_max_size='50GB'
set undo_tablespace='UNDOTBS1'
nofilenamecheck;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=42 device type=DISK
 
allocated channel: prmy2
channel prmy2: SID=36 device type=DISK
 
allocated channel: prmy3 
channel prmy3 : SID=45 device type=DISK

allocated channel: prmy4 
channel prmy4 : SID=45 device type=DISK
 
allocated channel: stby
channel stby: SID=20 device type=DISK
 
Starting Duplicate Db at 28-JAN-19
.
.
.
.
.
Finished Duplicate Db at 28-JAN-19
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>

12. Enable Recovery Manager in standby side:

[oracle@proddr01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jan 28 10:36:39 2019

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Database altered.

13. Check Standby SYNC Verification:

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          299314                 299314                0
2          149803                 149803                0

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba 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 GoldenGate vs Oracle Data Guard

The configuration of Oracle GoldenGate / Data Guard has the purpose of synchronizing data between two or more systems .

The basic features of the two products may look similar but takes GoldenGate uses replication while dataguard not.

Oracle Data Guard and GoldenGate involving at least two systems where transactional data from one database is required to be moved to another database. The purpose of the replication of data can be disaster recovery, migration of data or preparation of a secondary system.

Data Guard is best for disaster recovery and data protection problems, GoldenGate is a more flexible heterogeneous replication mechanism and is also able to transform the data while it is being replicated.

  • Data Guard is an Oracle specific technology while GoldenGate support heterogeneous database systems including all the major RDBMS as DB2,Sybase, MySQL .
  • Data Guard supports active-passive replication. One of the database is the primary database and the other one is in an inactive Data Guard mode.
  • GoldenGate supports an active-active replication mode and allows both systems to work simultaneously while maintaining the data integrity.
  • GoldenGate allows transformation of the data, with conflict management while it is being replicated between both database systems.
  • GoldenGate allows replication across platform. Data can be extracted from a Unix platform and replicated to an Oracle database running on platform Windows.
  • GoldenGate has many case of utilization. The use of flat files for data transportation and the support of heterogeneous systems makes the technology so very interesting

 

GoldenGate is the best solution:
  • Replicate one or many tables to a read-write database.
  • Replicate and transform tables.
  • Bi-directional replication.
  • Zero or near-zero downtime upgrade.
  • Heterogeneous replication

GoldenGate replication.

• Zero Downtime Upgrade/Migration: You may want to setup an exact replica of your database for upgrade or migration, keep the data in sync till the last moment.

High Availability: Your applications cannot afford to have downtime so you want to have two databases up and running at the same time to support those applications.

Query or Reporting: You want to offload the reporting or long query operations from your production database.

Real Time BI: For your data warehouse requirements.

Data Synchronization: You want to Synchronize data across different departments of the company (different databases).

Furthermore the GoldenGate software supports extraction from the legacy systems and does not have any limitation on the distance either. Additionally, the trail files generated by GoldenGate capture can also be compressed and encrypted.

Oracle Data Guard

Used for

  • High availability
  • Data protection
  • Disaster recovery
  • Primary database workload reduction

Database roles

In a data guard environment, one database is a primary database, the others are standby databases. These roles are mutually exclusive.
The role of a database can be queried in the column database_role in v$database.

Primary database

The primary db is the »real« production database.
The primary db must run in archive log mode and with forced logging.

Standby databases

Standby databases are (transactionally consistent) copies of the primary database.
Up to 30 standby databases are supported.

Physical and logical standby databases

A standby DB is either physical or logical standby database. (A standby database can also become a snapshot standby database).
A physical database is an exact byte-wise copy of the primary database.
A logical database has the same data as the primary database, but is not necessarily stored in the same db blocks.
The physical database is kept synchronized with redo apply, the logical database with SQL apply.

Cascading standby database

A physical standby database might be configured to receive redo from a primary database and relay it to a another standby database.
In this case, the relaying database is  called a cascading standby database. The receiving standby databases is also known as a terminal destination.
With such a configuration, the load on the primary database can be reduced.

SQL*Plus

Some SQL*Plus statements come with the standby keyword which indicates that the statement should be applied on the standby databases.

Role of redo

Data Guard basically works as follows:

  • Archived redo log files from the production DB are copied to the standby DB.
  • They are then registered with the standby DB.
  • Finally, using media recovery, the redo is used to roll forward the standby database.

Preventing physical errors

A standby database validates the redo it receives from the primary DB.
Thus, physical errors (such as data corruption) on the primary DB are not propagated to standby databases.

Changing roles of databases

The role (primary vs standby) of a database can be changed by either a

  • switch over or
  • a fail-over
A switch over is a planned role transition. In a switch over, the primary database becomes a standby database while (one of) the standby databases becomes the primary database.
A switch over is performed in two steps:
1) the primary DB becomes a standby DB
2) a standby DB becomes a primary DB.
A fail over occurs when the primary database becomes unavailable. In this case, one of the standby databases becomes the new primary database.

RAC

The primary db and the standby databases can be run as single instance or real application clusters(RAC).

Active Data Guard

Active data guard offers the same features as data guard plus that it standby databases are opened read only while the copying of data takes place
Thus, it allows to query the standby databases without impacting the performance of the production database (for example to create resource-intensive reports).
The standbase database is called physical standby database.
The buzzword is real time query.
Active data guard must be licenced seperatly.

Data protection modes

Data Guard runs in one of three data protection modes. This allows to balance data availability against system performance requirements.

These protection modes are:

  • Maximum performance (default)
  • Maximum protection
  • Maximum availability

Automatic gap detection

If connectivity is lost between the primary and one or more standby databases
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba 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

To check the LAG between Primary & Standby database

#!/bin/bash
# #################################################
# This script MUST run from the Primary DB server.
# It checks the LAG between Primary & Standby database
# To be run by ORACLE user
# ###############################################

# #####################################
# Variables MUST be edited by the user: [Otherwise the script will not work]
# #####################################

# Here you replace youremail@yourcompany.com with your Email address:
EMAIL_RECEIVER=”youremail@yourcompany.com”
export EMAIL_RECEIVER

# Replace ${ORACLE_SID} with the Primary DB instance SID:
ORACLE_SID=${ORACLE_SID}
export ORACLE_SID

# Replace STANDBY_TNS_ENTRY with the Standby Instance TNS entry you configured in the primary site tnsnames.ora file:
DRDBNAME=STANDBY_TNS_ENTRY
export DRDBNAME

# Replace ${ORACLE_HOME} with the ORACLE_HOME path on the primary server:
ORACLE_HOME=${ORACLE_HOME}
export ORACLE_HOME

# Log Directory Location:
LOG_DIR=’/tmp’
export LOG_DIR

# Here you replace SYSPASS with user SYS password on the standby DB:
CRD=’SYSPASS’
export CRD

# Replace “5” with the number of LAGGED ARCHIVELOGS if reached an Email alert will be sent to the receiver:
LAGTHRESHOLD=5
export LAGTHRESHOLD

# #############################################
# Other variables will be picked automatically:
# #############################################

SCRIPT_NAME=”check_standby_lag.sh”
export SCRIPT_NAME

SRV_NAME=`uname -n`
export SRV_NAME

LNXVER=`cat /etc/redhat-release | grep -o ‘[0-9]’|head -1`
export LNXVER

MAIL_LIST=”-r ${SRV_NAME} ${EMAIL_RECEIVER}”
export MAIL_LIST

# #########################################
# Script part to execute On the Primary:
# #########################################
# Check the current Redolog sequence number:
PRDBNAME_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
select name from v\$database;
exit;
EOF
)

PRDBNAME=`echo ${PRDBNAME_RAW} | awk ‘{print $NF}’`

PRSEQ_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
select max(sequence#) from v\$archived_log;
exit;
EOF
)

PRSEQ=`echo ${PRSEQ_RAW} | awk ‘{print $NF}’`
export PRSEQ

# #########################################
# Script part to execute On the STANDBY:
# #########################################

# Get the last applied Archive Sequence number from the Standby DB:

DRSEQ_RAW=$(${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
conn SYS/”${CRD}”@${DRDBNAME} AS SYSDBA
select max(sequence#) from v\$archived_log where applied=’YES’;
exit;
EOF
)

DRSEQ=`echo ${DRSEQ_RAW} | awk ‘{print $NF}’`
export DRSEQ

# Compare Both PRSEQ & DRSEQ to detect the lag:
# ############################################
LAG=$((${PRSEQ}-${DRSEQ}))
export LAG

if [ ${LAG} -ge ${LAGTHRESHOLD} ]
then
${ORACLE_HOME}/bin/sqlplus -S “/ as sysdba” << EOF
set linesize 1000 pages 100
spool ${LOG_DIR}/DR_LAST_APPLIED_SEQ.log
PROMPT Current Log Sequence on the Primary DB:
PROMPT ———————————————————

archive log list

PROMPT
PROMPT Last Applied Log Sequence# on the Standby DB:
PROMPT —————————————————————–

conn SYS/”${CRD}”@${DRDBNAME} AS SYSDBA
set linesize 1000 pages 100
select THREAD#,max(SEQUENCE#) from V\$ARCHIVED_LOG where APPLIED=’YES’ group by THREAD#;
exit;
EOF
# Send Email with LAG details:
mail -s “ALARM: DR DB [${DRDBNAME}] is LAGGING ${LAG} sequences behind Primary DB [${PRDBNAME}] on Server [${SRV_NAME}]” ${MAIL_LIST} < ${LOG_DIR}/DR_LAST_APPLIED_SEQ.log
fi

echo
echo Primary DB Sequence is: ${PRSEQ}
echo Standby DB Sequence is: ${DRSEQ}
echo Number of Lagged Archives Between is: ${LAG}
echo

# #############
# END OF SCRIPT
# #############

Reinstating a Failed Oracle Rac Primary Database using Dataguard Broker

In the previous article, we have performed the Oracle RAC to RAC physical standby database failover steps in 11gR2. Now, let us continue with the reinstate process of the failed primary RAC database.

Below is the environment details:-Note:-

As per the current status, the primary site ORCL_PRODRAC1 is failed and physical standby site ORCL_PRODRAC2 already converted as primary by failover process. So, now ORCL_PRODRAC2 is primary and ORCL_PRODRAC1 is failed primary which should be reinstated.

Steps involved in reinstate of failed primary RAC database:-

A) Check Database Configuration
B) Performing Reinstate
C) Verify the Reinstate of new physical standby database

A) Check Database Configuration:-

Configuration check in Cluster:-

Failed Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC2
DGMGRL> show configuration verbose;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC2 - Primary database
ORCL_PRODRAC1 - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
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

B) Performing Reinstate:-

During the reinstate using dataguard broker, the failed primary site will be flashbacked and converted as physical standby database, then the media recovery process will be started automatically started. These can be view in the alert log of the database which is to be reinstated.

DGMGRL> reinstate database 'ORCL_PRODRAC1';

Reinstating database "ORCL_PRODRAC1", please wait...
Operation requires shutdown of instance "ORCL1" on database "ORCL_PRODRAC1"
Shutting down instance "ORCL1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL_PRODRAC1" ...
Operation requires shutdown of instance "ORCL1" on database "ORCL_PRODRAC1"
Shutting down instance "ORCL1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "ORCL_PRODRAC1" ...
Reinstatement of database "ORCL_PRODRAC1" succeeded

Attached the output of alert log of instance ORCL1 of database  ORCL_PRODRAC1.

C) Verify the Reinstate of new physical standby database:-

Configuration check in Cluster:-

Failed Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Verify in DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration verbose;

Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC2 - Primary database
ORCL_PRODRAC1 - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
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

From the above we can notice that the failed primary is now converted as physical standby database. If needed we can switchback it to primary. Refer here for the switchover process.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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 Rac Database Failover using Dataguard Broker

In the previous article, we have configured the dataguard broker and performed the switchover from Oracle 11gR2 two node RAC primary to two node physical standby. In this article, let us perform the failover process. The environment details are provided below.

Steps involved in Failover using dataguard broker:-

A) Check Database Configuration before Failover
B) Performing Failover
C) Check database configuration after Failover

A) Check Database Configuration before Failover:-

Configuration check in Cluster:-

Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

B) Performing Failover:-

Here, connect to the standby site and perform the failover process.

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC2
DGMGRL> failover to 'ORCL_PRODRAC2';
Performing failover NOW, please wait...
Operation requires a connection to instance "ORCL1" on database "ORCL_PRODRAC2"
Connecting to instance "ORCL1"...
Connected.
Failover succeeded, new primary is "ORCL_PRODRAC2"

C) Check database configuration after Failover:-

Configuration check in Cluster:-

Failed Primary(ORCL_PRODRAC1):

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Failedover Primary (ORCL_PRODRAC2):

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

From the above configuration we can see that the Database role for both failed primary site(ORCL_PRODRAC1) and new primary site(ORCL_PRODRAC2) is ‘Primary’ since the failed primary site crashed due to some unplanned outage and that site should be reinstated using dataguard broker.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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 Rac Database Switchover Using Dataguard Broker

Description:-

In the previous article, we have configured dataguard broker for Oracle RAC database with physical standby in 11gR2. Here, we will continue with the switchover process. Below is the environment.Steps Involved in Switchover using dataguard broker:-

A) Check Database Configuration before Switchover
B) Performing Switchover
C) Check database configuration after Switchover

A) Check Database Configuration before Switchover:-

Configuration check in Cluster:-

Primary:

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Standby:

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Primary Node 1:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:09:45

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:33
Uptime 0 days 22 hr. 51 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac101/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.211)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.214)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGMGRL" has 1 instance(s).
Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Primary Node 2:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:11:02

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:45
Uptime 0 days 22 hr. 52 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.212)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.215)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC1_DGMGRL" has 1 instance(s).
Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Node 1:-

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:11:29

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:46
Uptime 0 days 22 hr. 52 min. 43 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac201/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.221)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.224)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGB" has 1 instance(s).
Instance "ORCL1", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGMGRL" has 1 instance(s).
Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Node 2:-

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-AUG-2018 01:12:10

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-AUG-2018 02:18:55
Uptime 0 days 22 hr. 53 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prodrac202/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.222)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.225)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGB" has 1 instance(s).
Instance "ORCL2", status READY, has 1 handler(s) for this service...
Service "ORCL_PRODRAC2_DGMGRL" has 1 instance(s).
Instance "ORCL2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Configuration check in database level (Primary):-

$ sqlplus / as sysdba
SQL> select name, db_unique_name, database_role, switchover_status, open_mode from v$database;

NAME   DB_UNIQUE_NAME  DATABASE_ROLE  SWITCHOVER_STATUS  OPEN_MODE
------ --------------  -------------  -----------------  ---------
ORCL   ORCL_PRODRAC1   PRIMARY        TO STANDBY         READ WRITE

SQL> @stby_gap

DG Broker configuration:-

$ dgmgrl
DGMGRL> connect sys/manager@ORCL_PRODRAC1
DGMGRL> show configuration verbose;


Configuration - ORACLEDBWR

Protection Mode: MaxPerformance
Databases:
ORCL_PRODRAC1 - Primary database
ORCL_PRODRAC2 - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
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

Primary database setting:-

DGMGRL> show database verbose 'ORCL_PRODRAC1';

Database - ORCL_PRODRAC1

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL1
ORCL2

Properties:
DGConnectIdentifier = 'orcl_prodrac1'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS


DGMGRL> show instance verbose 'ORCL1' on database 'ORCL_PRODRAC1';

Instance 'ORCL1' of database 'ORCL_PRODRAC1'

  Host Name: prodrac101.oracledbwr.com
  PFILE:
  Properties:
    SidName                         = 'ORCL1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.214)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC1_DGMGRL)(INSTANCE_NAME=ORCL1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+DBWR_FRA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'ORCL_%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL2' on database 'ORCL_PRODRAC1';

Instance 'ORCL2' of database 'ORCL_PRODRAC1'

Host Name: prodrac102.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.215)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC1_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

Standby database setting:-

DGMGRL> show database verbose 'ORCL_PRODRAC2';

Database - ORCL_PRODRAC2

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 187.00 KByte/s
Real Time Query: ON
Instance(s):
ORCL1
ORCL2

Properties:
DGConnectIdentifier = 'orcl_prodrac2'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL1' on database 'ORCL_PRODRAC2';

Instance 'ORCL1' of database 'ORCL_PRODRAC2'

Host Name: prodrac201.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.224)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC2_DGMGRL)(INSTANCE_NAME=ORCL1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

DGMGRL> show instance verbose 'ORCL2' on database 'ORCL_PRODRAC2';

Instance 'ORCL2' of database 'ORCL_PRODRAC2'

Host Name: prodrac202.oracledbwr.com
PFILE:
Properties:
SidName = 'ORCL2'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.225)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_PRODRAC2_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+DBWR_FRA'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ORCL_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Instance Status:
SUCCESS

B) Performing Switchover:-

It is ready for the switchover. Note that we do not need to shutdown any instances

DGMGRL> switchover to 'ORCL_PRODRAC2';
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORCL1" on database "ORCL_PRODRAC2"
Connecting to instance "ORCL1"...
Connected.
New primary database "ORCL_PRODRAC2" is opening...
Operation requires startup of instance "ORCL1" on database "ORCL_PRODRAC1"
Starting instance "ORCL1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ORCL_PRODRAC2"

C) Check database configuration after Switchover:-

Old Primary:-

$ srvctl status database -d ORCL_PRODRAC1 -v
Instance ORCL1 is running on node prodrac101. Instance status: Open.
Instance ORCL2 is running on node prodrac102. Instance status: Mounted (Closed).

$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:-

$ srvctl status database -d ORCL_PRODRAC2 -v
Instance ORCL1 is running on node prodrac201. Instance status: Open.
Instance ORCL2 is running on node prodrac202. Instance status: Open.

$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

In the above configuration we can see that database role for both the databases has been changed automatically in cluster, whereas in the manual switchover this has to be done manually. Also, we can notice that the “Start options” is open and read only in old primary and new primary respectively. It is recommended to change this in the cluster manually.

Old Primary:-

$ srvctl modify database -d ORCL_PRODRAC1 -s 'READ ONLY'
$ srvctl config database -d ORCL_PRODRAC1 -a
Database unique name: ORCL_PRODRAC1
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC1/spfileORCL_PRODRAC1.ora
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC1
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

New Primary:-

$ srvctl modify database -d ORCL_PRODRAC2 -s 'OPEN'
$ srvctl config database -d ORCL_PRODRAC2 -a
Database unique name: ORCL_PRODRAC2
Database name: ORCL
Oracle home: /oradb/app/oracle/product/11.2.0.4/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL_PRODRAC2/spfileORCL_PRODRAC2.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL_PRODRAC2
Database instances: ORCL1,ORCL2
Disk Groups: DBWR_DATA,DBWR_FRA
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed

Let us verify in the database for archive gap.

$ sqlplus / as sysdba

SQL> @stby_gap

We can also verify the log shipping from the new primary to old primary by switch log in new primary and tail the alert log in old primary.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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