Creation of Physical Standby in Oracle One Node

Introduction:- Data plays an important role in business operations on a day to day life. Without data, we can not perform our business operations. Data helps to generate business reports, With the help of data, all kind of business’s departments( Hr, Top management, Technical Departments, Accounts Departments etc.) can achieve their goals on time. Loss of data causes business loss. For reducing the loss of data we can find new technology in the market. Oracle Corporation provides  Oracle Data Gaurd as disaster recovery options. In this article, we are going to have the journey of  12c Physical stand by Database creation.

What is Physical stand by  Database?

A physical standby database is an accurate block-for-block copy of a primary database. A physical standby is maintained as an exact copy through a process called Redo Apply, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms.A physical standby database can be opened for read-only access and used to offload queries from a primary database.

We have three option on how to configure a physical standby database.

• Enterprise Manager Grid Control
• Data Guard Broker
• SQL*Plus

Creation of 12c Physical Stand by Database

1. Start up the standby instance

# in srv2:
su - oracle
export ORACLE_SID=ORADB_S2
# now start up the instance
sqlplus '/ as sysdba'
STARTUP NOMOUNT
exit

2. Create the standby database online from the primary database
It can be run from the standby database, i.e. the data will be pulled from the primary, or it can be run from the primary, where the data will be pushed to the standby.
In this example, we run the command on the standby:

su - oracle
export ORACLE_SID=ORADB_S2
rman
CONNECT TARGET sys/oracle@ORADB;
CONNECT AUXILIARY sys/oracle@ORADB_S2;
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby1 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
SPFILE
set 'db_unique_name'='ORADB_S2'
set control_files='+DATA/ORADB_S2/control.ctl'
set db_create_file_dest='+DATA'
set db_create_online_log_dest_1='+FRA'
set db_create_online_log_dest_2='+DATA'
set db_recovery_file_dest='+FRA'
set DB_RECOVERY_FILE_DEST_SIZE='10G'
set audit_file_dest='/u01/app/oracle/admin/ORADB_S2/adump'
set core_dump_dest='/u01/app/oracle/admin/ORADB_S2/cdump'
nofilenamecheck;
}

3. Verify the instance is running fine:

sqlplus / as sysdba
SELECT NAME FROM V$DATAFILE;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
# you will notice the SRL members got multiplexed:
SELECT TYPE, count(*) FROM V$LOGFILE GROUP BY TYPE;

4. Remove the multiplexed copy of each SRL group on the standby

COLUMN MEMBER FORMAT A50
SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;
# drop all the members in DATA diskgroup:
ALTER DATABASE DROP STANDBY LOGFILE MEMBER '+DATA/ORADB_S2/ONLINELOG/group_5.2xxx;

Note: One of them may return ORA-00261 because it is being used by the transport service. To drop it, on the primary database, switch the logfile:

alter system switch logfile;

The current standby redo log file will be archived and then the multiplexed member can be dropped:

ALTER DATABASE DROP STANDBY LOGFILE MEMBER '+DATA/ORADB_S2/ONLINELOG/group_4.276.905630767';

Verify the SRL group multiplexed members were removed:

SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;

5. Set the necessary Data Guard related initialization parameters for the standby database:

ALTER SYSTEM SET FAL_SERVER=ORADB;
ALTER SYSTEM SET FAL_CLIENT=ORADB_S2;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORADB,ORADB_S2)';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORADB ASYNC DB_UNIQUE_NAME=ORADB VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=both;

6. Start the Apply process in the Standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

7. Verify Data Guard configuration:
On the standby:

SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME,
replace( NAME ,'+FLASH/ORADB_S2/archivelog/','') FILENAME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

On the primary:

ALTER SYSTEM SWITCH LOGFILE;

On the standby:

SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME,
replace( NAME ,'+FLASH/ORADB_S2/archivelog/','') FILENAME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Query the physical standby database to monitor Redo Apply and Redo Transport services activity at the standby site:

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

8. Post Creation Steps:
8.1 Configure ARCHIVELOG DELETION POLICY in RMAN in both systems

rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

8.2 In the standby system, set ORACLE_SID variable in the oracle os profile:

vi ~/.bash_profile
ORACLE_SID=ORADB_S2; export ORACLE_SID

8.3 On the standby database, enable the Flashback Database:

sqlplus sys/oracle@oradb_s2 as sysdba
SELECT LOG_MODE,FLASHBACK_ON FROM V$DATABASE;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET =2880;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE FLASHBACK ON;

8.4 Register the standby database in the Oracle Restart:

sqlplus / as sysdba
shutdown immediate
exit
srvctl add database -db ORADB_S2 -instance ORADB_S2 -oraclehome /u01/app/oracle/product/12.1.0/db_1 -startoption MOUNT -stopoption IMMEDIATE -policy AUTOMATIC -role physical_standby -spfile /u01/app/oracle/product/12.1.0/db_1/dbs/spfileORADB_S2.ora
srvctl start database -d ORADB_S2
srvctl status database -d ORADB_S2

Notes
Standby Database Status

Notice:- that the standby database is running in MOUNT status. Therefore, no normal user can connect to it. This is the default initial behavior of a freshly created standby database. However, it can be opened to the normal users for read-only operations.
Shutting Down the Databases
1. Stop the standby database:

# on srv2
srvctl stop database -d oradb_s2

2. Stop the primary database.
3. Shutdown the appliances.
Note: Every time you start this created standby database (in MOUNT mode), you should issue the command to star the apply process (MRP):

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

I hope this article is useful . Thanks for spend valuable time to read.