Oracle 12c Physical Standby Database Creation using Rman
Prerequisites for standby built for primary side.
- Ø Configure network files for both side (Primary,standby).
- Ø Parameter change in primary side.
- Ø Copy the Password file from primary side to standby side.
- Ø Create Pfile on Standby Database.
- Ø Using Rman create standby database using Active duplication.
- Ø Enable MRP process.
- Ø Post checks.
| Details | Primary Side | Standby Side |
| Server Name | Primary | Standby |
| DB Name | Warangal | Mumbai |
| IP Adresses | 192.168.182.128 | 192.168.182.129 |
Prerequisites for standby built for primary side.
- Change Archive Log
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Current log sequence 4
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2835349504 bytes
Database Buffers 2097152000 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
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 USE_DB_RECOVERY_FILE_DEST
- Enable force logging.
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
- Adding Standby Redolog Files
SQL> select member from v$logfile;—-add standby redolog files
MEMBER
——————————————————————————–
/u01/app/oracle/oradata/warangal/redo03.log
/u01/app/oracle/oradata/warangal/redo02.log
/u01/app/oracle/oradata/warangal/redo01.log
alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/warangal/redo4.log’ size 50m;
alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/warangal/redo5.log’ size 50m;
alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/warangal/redo6.log’ size 50m;
alter database add standby logfile group 7 ‘/u01/app/oracle/oradata/warangal/redo7.log’ size 50m;
select * from v$standby_log;
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/u01/app/oracle/oradata/warangal/redo03.log
/u01/app/oracle/oradata/warangal/redo02.log
/u01/app/oracle/oradata/warangal/redo01.log
/u01/app/oracle/oradata/warangal/redo4.log
/u01/app/oracle/oradata/warangal/redo5.log
/u01/app/oracle/oradata/warangal/redo6.log
/u01/app/oracle/oradata/warangal/redo7.log
Configure network files for both side (Primary,standby).
- Listener entry in Primary and Standby
[oracle@ranesh admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ranesh.doyensys.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = warangal)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = warangal)
)
(SID_DESC=
(GLOBAL_DBNAME = mumbai)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_2)
(SID_NAME = mumbai)
)
)
ADR_BASE_LISTENER=/u01/app/oracle
- Tnsname Entry in Primary and Standby
[oracle@ranesh admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
warangal =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ranesh.doyensys.com)(PORT = 1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME = warangal)
)
)
mumbai =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ranesh2.doyensys.com)(PORT = 1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME = mumbai)
)
)
- Testing at both Primary and Standby.
[oracle@ranesh dbs]$ tnsping warangal
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 22-JAN-2020 23:40:35
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ranesh.doyensys.com)(PORT = 1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME = warangal)))
OK (10 msec)
[oracle@ranesh dbs]$ tnsping mumbai
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 22-JAN-2020 23:40:45
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ranesh2.doyensys.com)(PORT = 1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME = mumbai)))
OK (0 msec)
Changing Parameters at Primary Side
ALTER SYSTEM SET db_unique_name=’warangal’ SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_config=’dg_config=(warangal,mumbai)’ SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=warangal’ SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_2=’service=mumbai async valid_for=(online_logfiles,primary_role) db_unique_name=mumbai’ SCOPE=SPFILE;
ALTER SYSTEM SET fal_server=’mumbai’ SCOPE=SPFILE;
ALTER SYSTEM SET fal_client=’warangal’ SCOPE=SPFILE;
ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=SPFILE;
ALTER SYSTEM SET db_file_name_convert=’/u01/app/oracle/oradata/warangal’,’/u01/app/oracle/oradata/mumbai’ SCOPE=SPFILE;
Output.
*.db_name=’warangal’
*.db_unique_name=’warangal’
*.fal_client=’warangal’
*.fal_server=’mumbai’
*.log_archive_config=’dg_config=(warangal,mumbai)’
*.log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=warangal’
*.log_archive_dest_2=’service=mumbai async valid_for=(online_logfiles,primary_role) db_unique_name=mumbai’
*.standby_file_management=’AUTO’
*.db_file_name_convert=’/u01/app/oracle/oradata/warangal’,’/u01/app/oracle/oradata/mumbai’
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=4560m
Ø Copying Password file from Primary database to Standby
[oracle@ranesh dbs]$ scp orapwwarangal oracle@192.168.182.129:/u01/app/oracle/product/12.1.0/dbhome_2/dbs
At standby we need to change the password file name
[oracle@ranesh2 dbs]$ ls
hc_hyd21.dat hc_TEST.dat init.ora orapwmumbai pfile.ora spfileTEST.ora
Create pfile in standby and start it in nomount.
[oracle@ranesh2 dbs]$ mkdir initwarangal
[oracle@ranesh2 dbs]$ cat initwarangal.ora
DB_NAME=Warangal
- Create directory Structure in Standby database
[oracle@ranesh2 dbs]$ cd /u01/app/oracle/admin/
[oracle@ranesh2 warangal]$ mkdir adump pfile dpdump
[oracle@ranesh2 warangal]$ ls
adump dpdump pfile xdb_wallet
[oracle@ranesh2 mumbai]$ pwd
/u01/app/oracle/oradata/mumbai
[oracle@ranesh2 mumbai]$ mkdir -p /u01/app/oracle/oradata/mumbai/onlinelog
[oracle@ranesh2 mumbai]$ mkdir -p /u01/app/oracle/oradata/mumbai/datafile
- start the standby database using pfile
[oracle@ranesh2 mumbai]$ export ORACLE_SID=Mumbai——-Standby
[oracle@ranesh2 mumbai]$ echo $ORACLE_SID
Mumbai
[oracle@ranesh2 dbs]$ sqlplus / as sysdba
SQL> startup pfile=’initwarangal.ora’ nomount
ORACLE instance started.
Total System Global Area 2835349504 bytes
Database Buffers 2097152000 bytes
Redo Buffers 13848576 bytes
[oracle@ranesh2 dbs]$ export ORACLE_SID=Warangal———-Primary
[oracle@ranesh2 dbs]$ echo $ORACLE_SID
warangal
[oracle@ranesh2 dbs]$ mkdir /u01/app/oracle/fast_recovery_dest
Using Rman create standby database using Active duplication.
[oracle@ranesh2 dbs]$ rman target sys/oracle@warangal
Recovery Manager: Release 12.1.0.2.0 – Production on Wed Jan 22 12:11:22 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: WARANGAL (DBID=4024448274)
RMAN> connect auxiliary sys/oracle@mumbai
connected to auxiliary database: WARANGAL (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 ‘warangal’,’mumbai’
set db_name=’warangal’
set db_unique_name=’mumbai’
set db_file_name_convert=’/u01/app/oracle/oradata/warangal’,’/u01/app/oracle/oradata/mumbai’
set log_file_name_convert=’/u01/app/oracle/oradata/warangal’,’/u01/app/oracle/oradata/mumbai’
set control_files=’/u01/app/oracle/oradata/mumbai/standby1.ctl’
set log_archive_max_processes=’5′
set fal_client=’mumbai’
set fal_server=’warangal’
set DIAGNOSTIC_DEST=’/u01/app/oracle’
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(warangal,mumbai)’
set compatible=’12.1.0.2.0′
set sga_target=’1500m’
nofilenamecheck;
}
OUTPUT
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=46 device type=DISK
allocated channel: p2
channel p2: SID=58 device type=DISK
allocated channel: p3
channel p3: SID=45 device type=DISK
allocated channel: p4
channel p4: SID=54 device type=DISK
allocated channel: s1
channel s1: SID=22 device type=DISK
Starting Duplicate Db at 22-JAN-20
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwwarangal’ auxiliary format
‘/u01/app/oracle/product/12.1.0/dbhome_2/dbs/orapwmumbai’ targetfile
‘/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilewarangal.ora’ auxiliary format
‘/u01/app/oracle/product/12.1.0/dbhome_2/dbs/spfilemumbai.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/12.1.0/dbhome_2/dbs/spfilemumbai.ora””;
}
executing Memory Script
Starting backup at 22-JAN-20
Finished backup at 22-JAN-20
sql statement: alter system set spfile= ”/u01/app/oracle/product/12.1.0/dbhome_2/dbs/spfilemumbai.ora”
contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/mumbai/adump” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=mumbaiXDB)” 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=mumbai” comment=
”” scope=spfile”;
sql clone “alter system set db_name =
”warangal” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”mumbai” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”/u01/app/oracle/oradata/warangal”, ”/u01/app/oracle/oradata/mumbai” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”/u01/app/oracle/oradata/warangal”, ”/u01/app/oracle/oradata/mumbai” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”/u01/app/oracle/oradata/mumbai/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 =
”mumbai” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”warangal” comment=
”” scope=spfile”;
sql clone “alter system set DIAGNOSTIC_DEST =
”/u01/app/oracle” 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=(warangal,mumbai)” comment=
”” scope=spfile”;
sql clone “alter system set compatible =
”12.1.0.2.0” comment=
”” scope=spfile”;
sql clone “alter system set sga_target =
1500m comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/mumbai/adump” comment= ”” scope=spfile
sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=mumbaiXDB)” 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=mumbai” comment= ”” scope=spfile
sql statement: alter system set db_name = ”warangal” comment= ”” scope=spfile
sql statement: alter system set db_unique_name = ”mumbai” comment= ”” scope=spfile
sql statement: alter system set db_file_name_convert = ”/u01/app/oracle/oradata/warangal”, ”/u01/app/oracle/oradata/mumbai” comment= ”” scope=spfile
sql statement: alter system set log_file_name_convert = ”/u01/app/oracle/oradata/warangal”, ”/u01/app/oracle/oradata/mumbai” comment= ”” scope=spfile
sql statement: alter system set control_files = ”/u01/app/oracle/oradata/mumbai/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 = ”mumbai” comment= ”” scope=spfile
sql statement: alter system set fal_server = ”warangal” comment= ”” scope=spfile
sql statement: alter system set DIAGNOSTIC_DEST = ”/u01/app/oracle” 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=(warangal,mumbai)” comment= ”” scope=spfile
sql statement: alter system set compatible = ”12.1.0.2.0” comment= ”” scope=spfile
sql statement: alter system set sga_target = 1500m comment= ”” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1577058304 bytes
Fixed Size 2924832 bytes
Variable Size 503320288 bytes
Database Buffers 1056964608 bytes
Redo Buffers 13848576 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/mumbai/standby1.ctl’;
}
executing Memory Script
Starting backup at 22-JAN-20
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_warangal.f tag=TAG20200123T002240
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 22-JAN-20
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/mumbai/temp01.dbf”;
switch clone tempfile all;
set newname for datafile 1 to
“/u01/app/oracle/oradata/mumbai/system01.dbf”;
set newname for datafile 2 to
“/u01/app/oracle/oradata/ramya.dbf”;
set newname for datafile 3 to
“/u01/app/oracle/oradata/mumbai/sysaux01.dbf”;
set newname for datafile 4 to
“/u01/app/oracle/oradata/mumbai/undotbs01.dbf”;
set newname for datafile 5 to
“/u01/app/oracle/oradata/ranesh.dbf”;
set newname for datafile 6 to
“/u01/app/oracle/oradata/mumbai/users01.dbf”;
backup as copy reuse
datafile 1 auxiliary format
“/u01/app/oracle/oradata/mumbai/system01.dbf” datafile
2 auxiliary format
“/u01/app/oracle/oradata/ramya.dbf” datafile
3 auxiliary format
“/u01/app/oracle/oradata/mumbai/sysaux01.dbf” datafile
4 auxiliary format
“/u01/app/oracle/oradata/mumbai/undotbs01.dbf” datafile
5 auxiliary format
“/u01/app/oracle/oradata/ranesh.dbf” datafile
6 auxiliary format
“/u01/app/oracle/oradata/mumbai/users01.dbf” ;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/mumbai/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 22-JAN-20
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/warangal/system01.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/warangal/sysaux01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/warangal/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ranesh.dbf
output file name=/u01/app/oracle/oradata/ranesh.dbf tag=TAG20200123T002251
channel p4: datafile copy complete, elapsed time: 00:00:08
channel p4: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/warangal/users01.dbf
output file name=/u01/app/oracle/oradata/mumbai/undotbs01.dbf tag=TAG20200123T002251
channel p3: datafile copy complete, elapsed time: 00:00:09
channel p3: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ramya.dbf
output file name=/u01/app/oracle/oradata/ramya.dbf tag=TAG20200123T002251
channel p3: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/mumbai/users01.dbf tag=TAG20200123T002251
channel p4: datafile copy complete, elapsed time: 00:00:04
output file name=/u01/app/oracle/oradata/mumbai/system01.dbf tag=TAG20200123T002251
channel p1: datafile copy complete, elapsed time: 00:02:34
output file name=/u01/app/oracle/oradata/mumbai/sysaux01.dbf tag=TAG20200123T002251
channel p2: datafile copy complete, elapsed time: 00:02:34
Finished backup at 22-JAN-20
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=1030364527 file name=/u01/app/oracle/oradata/mumbai/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1030364527 file name=/u01/app/oracle/oradata/ramya.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1030364527 file name=/u01/app/oracle/oradata/mumbai/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1030364527 file name=/u01/app/oracle/oradata/mumbai/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1030364527 file name=/u01/app/oracle/oradata/ranesh.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=1030364527 file name=/u01/app/oracle/oradata/mumbai/users01.dbf
Finished Duplicate Db at 22-JAN-20
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
[oracle@ranesh2 dbs]$ export ORACLE_SID=mumbai
[oracle@ranesh2 dbs]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 22 12:23:42 2020
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 open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> Alter database recover managed standby database cancel;
Database altered.
- Primary Database
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
TO SEE IN STANDBY ARCHIVE LOG FILES APPLIED SUCCESSFUL OR NOT
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;
o/p
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 18 18 0
1 18 18 0
Standy checking present scn
select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied=’YES’;
