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

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

Description:-

o    This article we are going to see 12.1.0.2.0 standby database creation using rman.

Environment Details:-

Primary Server side Configurations:-

Step1:-Change Archivelog mode

[oracle@primary ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 12 05:10:47 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1660944384 bytes
Fixed Size 2925072 bytes
Variable Size 1056968176 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

Step2:-Change force logging mode

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING  LOG_MODE
————        ————
YES                               ARCHIVELOG

Step3:-Adding Redologfile for standby database

SQL> alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/PRIME/onlinelog/redo04.log’ size 50m;
Database altered.

SQL> alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/PRIME/onlinelog/redo05.log’ size 50m;
Database altered.

SQL> alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/PRIME/onlinelog/redo06.log’ size 50m;
Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
———- ———- ———- — ———-
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

Step4:-Adding the network entry in primary and standby side(Both servers)

Tnsnames entry:-
**************
PRIME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1539))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prime)
)
)
STAND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1539))
)
(CONNECT_DATA =
(SERVICE_NAME = stand)
)
)
Listener Entry:-
************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prime)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = prime)
)
(SID_DESC =
(GLOBAL_DBNAME = stand)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME = stand)
)
)

Output like the below

[oracle@primary admin]$ tnsping prime
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 12-JUN-2018 05:54:29
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prime)))
OK (0 msec)

[oracle@primary admin]$ tnsping stand
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 12-JUN-2018 05:54:34
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1539))) (CONNECT_DATA = (SERVICE_NAME = stand)))
OK (0 msec)

step5:-Changing parameters in primary database

SQL> ALTER SYSTEM SET db_unique_name=’PRIME’ SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(prime,stand)’ SCOPE=SPFILE;
System altered.

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

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

SQL> ALTER SYSTEM SET fal_server=’STAND’ SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_client=’PRIME’ SCOPE=SPFILE;
System altered.

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

SQL> ALTER SYSTEM SET db_file_name_convert=’/u01/app/oracle/oradata/STAND/datafile’,’/u01/app/oracle/oradata/PRIME/datafile’ SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_file_name_convert=’/u01/app/oracle/oradata/STAND/onlinelog’,’/u01/app/oracle/oradata/PRIME/onlinelog’ SCOPE=SPFILE;
System altered.

Standby Server side Configurations:-

Step1:- Password file creation

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

[oracle@primary dbs]$ scp orapwprime oracle@192.168.1.25:$ORACLE_HOME/dbs
oracle@192.168.1.25’s password:
orapwprime 100% 7680 7.5KB/s 00:00

oracle@standby dbs]$ mv orapwprime orapwstand

Step2:- Changing parameters in standby database

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

[oracle@standby admin]$ cd $ORACLE_HOME/dbs
[oracle@standby dbs]$ cat initstand.ora
db_name=stand

Step3:- Create directory Structure in Standby database

[oracle@standby dbs]$ cd $ORACLE_BASE/admin/
[oracle@standby admin]$ mkdir stand
[oracle@standby admin]$ cd stand
[oracle@standby stand]$ mkdir adump pfile dpdump
[oracle@standby stand]$ mkdir -p /u01/app/oracle/oradata/STAND/onlinelog
[oracle@standby stand]$ mkdir -p /u01/app/oracle/oradata/STAND/datafile

Step4:- start the standby database using pfile

[oracle@standby dbs]$ export ORACLE_SID=stand
[oracle@standby dbs]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 12 06:03:47 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=’initstand.ora’ nomount
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 2922712 bytes
Variable Size 159385384 bytes
Database Buffers 50331648 bytes
Redo Buffers 5464064 bytes

Step5:- connect to the rman

[oracle@standby dbs]$ export ORACLE_SID=prime
[oracle@standby dbs]$ rman target sys/oracle@prime
Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jun 12 06:28:27 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIME (DBID=2055869989)
RMAN> connect auxiliary sys/oracle@stand
connected to auxiliary database: STAND (not mounted)
RMAN> run 
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ‘prime’,’stand’
set db_unique_name=’stand’
set db_file_name_convert=’/u01/app/oracle/oradata/PRIME/datafile/’,’/u01/app/oracle/oradata/STAND/datafile/’
set log_file_name_convert=’/u01/app/oracle/oradata/PRIME/onlinelog/’,’/u01/app/oracle/oradata/STAND/onlinelog/’
set control_files=’/u01/app/oracle/oradata/STAND/onlinelog/standby1.ctl’
set log_archive_max_processes=’5′
set fal_client=’stand’
set fal_server=’prime’
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(prime,stand)’
set compatible=’12.1.0.2.0′
set memory_target=’500m’
nofilenamecheck;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23>
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=54 device type=DISK
allocated channel: p2
channel p2: SID=42 device type=DISK
allocated channel: p3
channel p3: SID=53 device type=DISK
allocated channel: p4
channel p4: SID=50 device type=DISK
allocated channel: s1
channel s1: SID=23 device type=DISK
Starting Duplicate Db at 12-JUN-18
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapwprime’ auxiliary format
‘/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapwstand’ targetfile
‘/u01/app/oracle/product/12.1.0.2/db_1/dbs/spfileprime.ora’ auxiliary format
‘/u01/app/oracle/product/12.1.0.2/db_1/dbs/spfilestand.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/12.1.0.2/db_1/dbs/spfilestand.ora””;
}
executing Memory Script
Starting backup at 12-JUN-18
Finished backup at 12-JUN-18
sql statement: alter system set spfile= ”/u01/app/oracle/product/12.1.0.2/db_1/dbs/spfilestand.ora”
contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/stand/adump” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=standXDB)” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_dest_1 =
”location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stand” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”stand” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”/u01/app/oracle/oradata/PRIME/datafile/”, ”/u01/app/oracle/oradata/STAND/datafile/” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”/u01/app/oracle/oradata/PRIME/onlinelog/”, ”/u01/app/oracle/oradata/STAND/onlinelog/” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”/u01/app/oracle/oradata/STAND/onlinelog/standby1.ctl” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_max_processes =
5 comment=
”” scope=spfile”;
sql clone “alter system set fal_client =
”stand” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”prime” comment=
”” scope=spfile”;
sql clone “alter system set standby_file_management =
”AUTO” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_config =
”dg_config=(prime,stand)” comment=
”” scope=spfile”;
sql clone “alter system set compatible =
”12.1.0.2.0” comment=
”” scope=spfile”;
sql clone “alter system set memory_target =
500m comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/stand/adump” comment= ”” scope=spfile
sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=standXDB)” comment= ”” scope=spfile
sql statement: alter system set log_archive_dest_1 = ”location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stand” comment= ”” scope=spfile
sql statement: alter system set db_unique_name = ”stand” comment= ”” scope=spfile
sql statement: alter system set db_file_name_convert = ”/u01/app/oracle/oradata/PRIME/datafile/”, ”/u01/app/oracle/oradata/STAND/datafile/” comment= ”” scope=spfile
sql statement: alter system set log_file_name_convert = ”/u01/app/oracle/oradata/PRIME/onlinelog/”, ”/u01/app/oracle/oradata/STAND/onlinelog/” comment= ”” scope=spfile
sql statement: alter system set control_files = ”/u01/app/oracle/oradata/STAND/onlinelog/standby1.ctl” comment= ”” scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= ”” scope=spfile
sql statement: alter system set fal_client = ”stand” comment= ”” scope=spfile
sql statement: alter system set fal_server = ”prime” comment= ”” scope=spfile
sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile
sql statement: alter system set log_archive_config = ”dg_config=(prime,stand)” comment= ”” scope=spfile
sql statement: alter system set compatible = ”12.1.0.2.0” comment= ”” scope=spfile
sql statement: alter system set memory_target = 500m comment= ”” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 524288000 bytes
Fixed Size 2926320 bytes
Variable Size 444598544 bytes
Database Buffers 71303168 bytes
Redo Buffers 5459968 bytes
allocated channel: s1
channel s1: SID=22 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/oradata/STAND/onlinelog/standby1.ctl’;
}
executing Memory Script
Starting backup at 12-JUN-18
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.1.0.2/db_1/dbs/snapcf_prime.f tag=TAG20180612T064910
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-JUN-18
contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_temp_fkxw4qob_.tmp”;
switch clone tempfile all;
set newname for datafile 1 to
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_system_fkxw1toz_.dbf”;
set newname for datafile 3 to
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_sysaux_fkxw0fh2_.dbf”;
set newname for datafile 4 to
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_undotbs1_fkxw3m3q_.dbf”;
set newname for datafile 6 to
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_users_fkxw3kvr_.dbf”;
backup as copy reuse
datafile 1 auxiliary format
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_system_fkxw1toz_.dbf” datafile
3 auxiliary format
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_sysaux_fkxw0fh2_.dbf” datafile
4 auxiliary format
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_undotbs1_fkxw3m3q_.dbf” datafile
6 auxiliary format
“/u01/app/oracle/oradata/STAND/datafile/o1_mf_users_fkxw3kvr_.dbf” ;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/STAND/datafile/o1_mf_temp_fkxw4qob_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 12-JUN-18
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PRIME/datafile/o1_mf_system_fkxw1toz_.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/PRIME/datafile/o1_mf_sysaux_fkxw0fh2_.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PRIME/datafile/o1_mf_undotbs1_fkxw3m3q_.dbf
channel p4: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/PRIME/datafile/o1_mf_users_fkxw3kvr_.dbf
output file name=/u01/app/oracle/oradata/STAND/datafile/o1_mf_undotbs1_fkxw3m3q_.dbf tag=TAG20180612T064916
channel p3: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/STAND/datafile/o1_mf_users_fkxw3kvr_.dbf tag=TAG20180612T064916
channel p4: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/STAND/datafile/o1_mf_sysaux_fkxw0fh2_.dbf tag=TAG20180612T064916
channel p2: datafile copy complete, elapsed time: 00:00:56
output file name=/u01/app/oracle/oradata/STAND/datafile/o1_mf_system_fkxw1toz_.dbf tag=TAG20180612T064916
channel p1: datafile copy complete, elapsed time: 00:01:07
Finished backup at 12-JUN-18
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=978589826 file name=/u01/app/oracle/oradata/STAND/datafile/o1_mf_system_fkxw1toz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=978589826 file name=/u01/app/oracle/oradata/STAND/datafile/o1_mf_sysaux_fkxw0fh2_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=978589826 file name=/u01/app/oracle/oradata/STAND/datafile/o1_mf_undotbs1_fkxw3m3q_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=978589826 file name=/u01/app/oracle/oradata/STAND/datafile/o1_mf_users_fkxw3kvr_.dbf
Finished Duplicate Db at 12-JUN-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step6:- connect to the standby database

[oracle@standby dbs]$ export ORACLE_SID=stand
[oracle@standby dbs]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 12 06:35:29 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.

Step7:- Physical Standby Database is Performing Correctly

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
———- ——— ——— ———
6 12-JUN-18 12-JUN-18 YES
7 12-JUN-18 12-JUN-18 YES
8 12-JUN-18 12-JUN-18 IN-MEMORY

Successfully configured the Oracle 12c Data Guard Physical Standby

Source: Internet