Oracle 12c-Step by Step Manual Data Guard Switchover

Oracle 12c-Step by Step Manual Switchover Data Guard

Switchover Operation Procedure: 

After configuring data guard, databases would be either primary and standby database role, and we can altered these roles without loss of data or without resetting logs  called Switchover.

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

Dataguard Configuration Details:-

Step 1:- Check database role and database name

Primary database:-

select name,open_mode,database_role from v$database

Standby database:-

select name,open_mode,database_role from v$database

Precheck for Switchover:-

Before performing switchover, kindly verify the state of data guard on both the instances by following SQL queries:

Step 2:-
ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Step 3:- select switchover_status from v$database;


Note: This command will give you appropriate message about the data guard current status.

After getting confirmation on data guard smooth activity, we can instantiate switchover activity by issuing following sort of commands:

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

On Primary database:-

Step 4:

SQL> alter database commit to switchover to standby;

Step 5:-

shutdown immediate

startup nomount

alter database mount standby database

Step 6:-select name,open_mode,database_role from v$database;

alter database recover managed standby database disconnect from session;

On Standby database:

Step 7:-alter database commit to switchover to primary;

Step 8:-select name,open_mode,database_role from v$database;

Switch over activity have completed successfully, our old primary database has become standby and old standby database has become primary database.
Note:-To test above switchover activity, generate multiple archive logs on primary database and verify those archive logs being transferred on standby database.

After switchover 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 Database 12cR2-Step by Step configuration Oracle Dataguard Using Rman

Oracle Database 12cR2-Step by Step Configuration Oracle Dataguard Using Rman 

Description:-

In this is the article we are going to see step-by-step to create a physical standby  database using RMAN in Oracle Database 12c Release 2.

Environment Details

Parameters needs to configure both side for Dataguard,

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

PRIMARY SIDE CONFIGURATION:-

Step 1:-Check FORCE LOGGING is enabled,

SQL> select force_logging from v$database

If no means,enable FORCE LOGGING mode,

SQL> ALTER DATABASE FORCE LOGGING

Step 2:- Make sure primary is in archivelog mode

ARCHIVELOG mode, that automatic archiving is enabled.

SQL> archive log list

Step 3:- PFILE creation and copy to standby database:

SQL> create pfile=’/home/oracle/initorcl.ora’ from spfile;

scp /home/oracle/initorcl.ora  oracle@clone:/home/oracle

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

#Change the Audit dump location#
*.audit_file_dest=’/oradb/app/oracle/admin/orclstby/adump’
#Change the controlfile location#
*.control_files=’/oradb/app/oracle/oradata/orclstby/control01.ctl’,’/oradb/app/oracle/oradata/orclstby/control02.ctl’
#Change the DISPATCHER service name#
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclstbyXDB)’
#Change the local listener name#
*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=clone.localdomain.com)(PORT=1521)))’
#Change the LOCAL DEST location#
*.log_archive_dest_1=’LOCATION=/oradb/app/oracle/oradata/orclstby/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
#Add DB_UNIQUE_NAME for standby database#
DB_UNIQUE_NAME=ORCLSTBY’
#Change the service name for standby#
*.service_names=’orclstby.localdomain.com’
#Add FAL entries#
fal_server=ORCL;
fal_client=ORCLSTBY
#Add the below to the created pfile from ORCL database, 
*.db_file_name_convert=’/oradb/app/oracle/oradata/orcl/’,’/oradb/app/oracle/oradata/orcl/’
*.log_file_name_convert=’/oradb/app/oracle/oradata/orcl/’,’/oradb/app/oracle/oradata/orcl/’

Step 5:- Parameters for primary database,

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=ORCLSTBY
NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)’ DB_UNIQUE_NAME=’ORCLSTBY’ scope=spfile;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

ALTER SYSTEM SET FAL_SERVER=ORCLSTBY;

ALTER SYSTEM SET FAL_CLIENT=ORCL;

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

scp orapworcl oracle@clone:/oradb/app/oracle/product/12.2.0.1/db_1

STANDBY SIDE CONFIGURATION:-

Step 7:- Make the respective directories for standby database,

mkdir -p /oradb/app/oracle/oradata/orclstby
mkdir -p /oradb/app/oracle/oradata/orclstby/arch
mkdir -p /oradb/app/oracle/admin/orclstby/adump

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

export ORACLE_HOME=/oradb/app/oracle/product/12.2.0.1/db_1
export ORACLE_BASE=/oradb/app/oracle
export ORACLE_SID=orcl

sqlplus / as sysdba

SQL> startup nomount pfile=’/home/oracle/initorcl.ora’;

Step 9:- Create spfile for standby database,

SQL> create spfile from pfile=’/home/oracle/initorcl.ora’;

Step 10:- Listener and TNS Configuration :-

Primary listener configuration:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /oradb/app/oracle/product/12.2.0.1/db_1)
)
)
LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =orcl.localdomain.com )(PORT = 1521))
)
)

Primary connection string configuration:

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.localdomain.com)
)
)

Standby listener configuration:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /oradb/app/oracle/product/12.2.0.1/db_1)
)
)
LISTENER_ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =clone.localdomain.com )(PORT = 1521))
)
)

Standby Connection string configuration:

ORCLSTBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = clone.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
(UR = A)
)
)

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

[oracle@clone:admin orclstby] lsnrctl status

[oracle@clone:~ orcl] lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 06-JUN-2018 10:11:29

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 – Production
Start Date 06-JUN-2018 02:51:26
Uptime 0 days 7 hr. 20 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/clone/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=clone.localdomain.com)(PORT=1521)))
Services Summary…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
Service “orclstby.localdomain.com” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
The command completed successfully

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

[oracle@clone:dbs orclstby] rman target sys/oracle@orcl auxiliary sys/oracle@orclstby
Recovery Manager: Release 12.2.0.1.0 – Production on Wed Jun 6 02:52:20 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1504791090)
connected to auxiliary database: ORCL (not mounted)

Step 13:-Active Duplication for creating standby database:

RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 06-JUN-18 04:17:38
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/12.2.0.1/db_1/dbs/orapworcl' auxiliary format
'/oradb/app/oracle/product/12.2.0.1/db_1/dbs/orapworcl' ;
}
executing Memory Script
Starting backup at 06-JUN-18 04:17:39
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK
Finished backup at 06-JUN-18 04:17:40
contents of Memory Script:
{
restore clone from service 'orcl' standby controlfile;
}
executing Memory Script
Starting restore at 06-JUN-18 04:17:41
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 orcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradb/app/oracle/oradata/orcl/control01.ctl
output file name=/oradb/app/oracle/oradata/orcl/control02.ctl
Finished restore at 06-JUN-18 04:17:42
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
"/oradb/app/oracle/oradata/orcl/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradb/app/oracle/oradata/orcl/system01.dbf";
set newname for datafile 3 to
"/oradb/app/oracle/oradata/orcl/sysaux01.dbf";
set newname for datafile 4 to
"/oradb/app/oracle/oradata/orcl/undotbs01.dbf";
set newname for datafile 7 to
"/oradb/app/oracle/oradata/orcl/users01.dbf";
restore
from nonsparse from service
'orcl' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradb/app/oracle/oradata/orcl/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 06-JUN-18 04:17:47
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 orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradb/app/oracle/oradata/orcl/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:52
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradb/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradb/app/oracle/oradata/orcl/undotbs01.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 orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradb/app/oracle/oradata/orcl/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 06-JUN-18 04:21:01
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=978063665 file name=/oradb/app/oracle/oradata/orcl/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=978063665 file name=/oradb/app/oracle/oradata/orcl/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=978063665 file name=/oradb/app/oracle/oradata/orcl/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=978063665 file name=/oradb/app/oracle/oradata/orcl/users01.dbf
Finished Duplicate Db at 06-JUN-18 04:22:41

Step 14:- Post check status for Standby database,

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

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

Step 15:- Enable the recovery:

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

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

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                       25                                                        25                                                   0

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