• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Data Guard 12c – Creating a Physical Standby from Primary

by admin

For the purposes of this post, the following environment is used as an example to describe the procedure:

Primary db_name  and db_unique_name: BOSTON
Standby db_name:  BOSTON (same as primary db_name)
Standby db_unique_name:  CHICAGO
Directory:  /u01/app/oracle
Note: We cannot create a physical standby at PDB(plugable Database) level. It should be at CDB(Container Database) level i.e entire primary CDB should be cloned/created as physical Standby.

Steps includes:

  • Create necessary Directory on standby server.
  • Create pfile and password.
  • Make static listener entry on standby server.
  • Use RMAN Duplicate ..from Active Database to clone Primary CDB.
  • Configure primary for remote log shipping and verify MRP progress on standby.

1. Create addition Directory on standby server:

$ mkdir -p /u01/app/oracle/admin/chicago/adump
$ mkdir -p /u01/app/oracle/oradata/chicago
$ mkdir -p /u01/app/oracle/fast_recovery_area/chicago
$ mkdir -p /u01/app/oracle/oradata/chicago1/pdbseed
$ mkdir -p /u01/app/oracle/oradata/chicago/db1
NOTE: Direcories like pdbseed,chicago1 are specific to 12c.

2. Create pfile:

$ echo 'db_name=boston' > /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initchicago.ora

3. Create Password file or copy from primary:

$ orapwd file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwchicago password=[password]

4. Make static listener entry:

$ cd $ORACLE_HOME/network/admin
$ vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC=
    (GLOBAL_DBNAME = chicago)
    (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
    (SID_NAME = chicago)
  )
)

reload or restart listener.

$ lsnrctl stop
$ lsnrctl start

5. Start the AUX instance at standby side.

$ sqlplus / as sysdba
sql> startup nomount pfile='$ORACLE_HOME/dbs/initchicago.ora';

6. Initiate RMAN DUPLICATE from.. active database to create standby with target connected to primary instance.

$ rman target sys/[password]@boston auxiliary sys/[password]@chicago
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel prim type disk;
allocate auxiliary channel aux type disk;
duplicate target database for standby from active database spfile
parameter_value_convert 'boston','chicago'
set db_file_name_convert='boston','chicago'
set log_file_name_convert='boston','chicago'
set log_archive_max_processes='10'
set db_unique_name='chicago'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(boston,chicago)'
set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=chicago'
set log_Archive_dest_2='service=boston async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=boston'
set log_archive_config='dg_config=(boston,chicago)';
sql channel aux "alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo1.log'' size 50M";
sql channel aux "alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo2.log'' size 50M";
sql channel aux "alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo3.log'' size 50M";
sql channel aux "alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo4.log'' size 50M";
sql channel prim "alter system archive log current";
sql channel aux "alter database recover managed standby database disconnect";
}

Output:

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jul 11 16:11:51 2013

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

connected to target database: BOSTON (DBID=2509966093)
connected to auxiliary database: BOSTON (not mounted)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=38 device type=DISK

allocated channel: c2
channel c2: SID=61 device type=DISK
.
.
Starting Duplicate Db at 11-JUL-13

contents of Memory Script:

{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwboston' auxiliary format
 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwchicago'   targetfile
 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileboston.ora' auxiliary format
 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilechicago.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilechicago.ora''";
}

executing Memory Script:

Starting backup at 11-JUL-13
Finished backup at 11-JUL-13

sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilechicago.ora''

contents of Memory Script:

{
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle/admin/chicago/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/chicago/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/chicago/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=chicagoXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''boston'', ''chicago'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''boston'', ''chicago'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 10 comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''chicago'' 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=(boston,chicago)'' 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=chicago'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_Archive_dest_2 =
 ''service=boston async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=boston'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}

executing Memory Script:

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/chicago/adump'' comment= '''' scope=spfile
sql statement: alter system set  control_files =  ''/oradata/chicago/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/chicago/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=chicagoXDB)'' comment= '''' scope=spfile
sql statement: alter system set  db_file_name_convert =  ''boston'', ''chicago'' comment= '''' scope=spfile
sql statement: alter system set  log_file_name_convert =  ''boston'', ''chicago'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_max_processes =  10 comment= '''' scope=spfile
sql statement: alter system set  db_unique_name =  ''chicago'' 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=(boston,chicago)'' 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=boston'' comment= '''' scope=spfile
sql statement: alter system set  log_Archive_dest_2 =  ''service=chicago async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=chicago'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config="dg_config=(boston,chicago)" comment=""scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1043886080 bytes

Fixed Size                     2296280 bytes
Variable Size                696256040 bytes
Database Buffers             339738624 bytes
Redo Buffers                   5595136 bytes
allocated channel: aux
channel aux: SID=19 device type=DISK

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
 "/oradata/chicago/temp01.dbf";
   set newname for tempfile  2 to
 "/oradata/chicago/pdbseed/pdbseed_temp01.dbf";
   set newname for tempfile  3 to
 "/oradata/chicago/chicago1/chicago1_temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oradata/chicago/system01.dbf";
   set newname for datafile  3 to
 "/oradata/chicago/sysaux01.dbf";
   set newname for datafile  4 to
 "/oradata/chicago/undotbs01.dbf";
   set newname for datafile  5 to
 "/oradata/chicago/pdbseed/system01.dbf";
   set newname for datafile  6 to
 "/oradata/chicago/users01.dbf";
   set newname for datafile  7 to
 "/oradata/chicago/pdbseed/sysaux01.dbf";
   set newname for datafile  8 to
 "/oradata/chicago/chicago1/system01.dbf";
   set newname for datafile  9 to
 "/oradata/chicago/chicago1/sysaux01.dbf";
   set newname for datafile  10 to
 "/oradata/chicago/chicago1/SAMPLE_SCHEMA_users01.dbf";
   set newname for datafile  11 to
 "/oradata/chicago/chicago1/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata/chicago/system01.dbf"   datafile
 3 auxiliary format
 "/oradata/chicago/sysaux01.dbf"   datafile
 4 auxiliary format
 "/oradata/chicago/undotbs01.dbf"   datafile
 5 auxiliary format
 "/oradata/chicago/pdbseed/system01.dbf"   datafile
 6 auxiliary format
 "/oradata/chicago/users01.dbf"   datafile
 7 auxiliary format
 "/oradata/chicago/pdbseed/sysaux01.dbf"   datafile
 8 auxiliary format
 "/oradata/chicago/chicago1/system01.dbf"   datafile
 9 auxiliary format
 "/oradata/chicago/chicago1/sysaux01.dbf"   datafile
 10 auxiliary format
 "/oradata/chicago/chicago1/SAMPLE_SCHEMA_users01.dbf"   datafile
 11 auxiliary format
 "/oradata/chicago/chicago1/example01.dbf"   ;
   sql 'alter system archive log current';
}

executing Memory Script:

executing command: SET NEWNAME
.
.
executing command: SET NEWNAME
executing command: SET NEWNAME

renamed tempfile 1 to /oradata/chicago/temp01.dbf in control file
renamed tempfile 2 to /oradata/chicago/pdbseed/pdbseed_temp01.dbf in control file
renamed tempfile 3 to /oradata/chicago/chicago1/chicago1_temp01.dbf in control file

executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 11-JUL-13
channel c1: starting datafile copy
input datafile file number=00001 name=/oradata/boston/system01.dbf
channel c2: starting datafile copy
input datafile file number=00003 name=/oradata/boston/sysaux01.dbf
channel c3: starting datafile copy
input datafile file number=00009 name=/oradata/boston/boston1/sysaux01.dbf
channel c4: starting datafile copy
input datafile file number=00007 name=/oradata/boston/pdbseed/sysaux01.dbf
channel prim: starting datafile copy
input datafile file number=00011 name=/oradata/boston/boston1/example01.dbf
output file name=/oradata/chicago/chicago1/example01.dbf tag=TAG20130711T161217
channel prim: datafile copy complete, elapsed time: 00:02:16
channel prim: starting datafile copy
input datafile file number=00008 name=/oradata/boston/boston1/system01.dbf
output file name=/oradata/chicago/chicago1/sysaux01.dbf tag=TAG20130711T161217
channel c3: datafile copy complete, elapsed time: 00:03:42
channel c3: starting datafile copy
input datafile file number=00005 name=/oradata/boston/pdbseed/system01.dbf
output file name=/oradata/chicago/pdbseed/sysaux01.dbf tag=TAG20130711T161217
channel c4: datafile copy complete, elapsed time: 00:03:43
channel c4: starting datafile copy
input datafile file number=00004 name=/oradata/boston/undotbs01.dbf
output file name=/oradata/chicago/chicago1/system01.dbf tag=TAG20130711T161217
channel prim: datafile copy complete, elapsed time: 00:01:52
channel prim: starting datafile copy
input datafile file number=00006 name=/oradata/boston/users01.dbf
output file name=/oradata/chicago/users01.dbf tag=TAG20130711T161217
channel prim: datafile copy complete, elapsed time: 00:00:07
channel prim: starting datafile copy
input datafile file number=00010 name=/oradata/boston/boston1/SAMPLE_SCHEMA_users01.dbf
output file name=/oradata/chicago/undotbs01.dbf tag=TAG20130711T161217
channel c4: datafile copy complete, elapsed time: 00:00:47
output file name=/oradata/chicago/chicago1/SAMPLE_SCHEMA_users01.dbf tag=TAG20130711T161217
channel prim: datafile copy complete, elapsed time: 00:00:03
output file name=/oradata/chicago/sysaux01.dbf tag=TAG20130711T161217
channel c2: datafile copy complete, elapsed time: 00:04:55
output file name=/oradata/chicago/pdbseed/system01.dbf tag=TAG20130711T161217
channel c3: datafile copy complete, elapsed time: 00:01:12
output file name=/oradata/chicago/system01.dbf tag=TAG20130711T161217
channel c1: datafile copy complete, elapsed time: 00:05:05
Finished backup at 11-JUL-13

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=7 STAMP=820513044 file name=/oradata/chicago/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=820513044 file name=/oradata/chicago/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=820513044 file name=/oradata/chicago/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=820513044 file name=/oradata/chicago/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=820513044 file name=/oradata/chicago/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=820513044 file name=/oradata/chicago/pdbseed/sysaux01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=13 STAMP=820513044 file name=/oradata/chicago/chicago1/system01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=14 STAMP=820513044 file name=/oradata/chicago/chicago1/sysaux01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=820513045 file name=/oradata/chicago/chicago1/SAMPLE_SCHEMA_users01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=16 STAMP=820513045 file name=/oradata/chicago/chicago1/example01.dbf
Finished Duplicate Db at 11-JUL-13

sql statement: alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo1.log'' size 50M
sql statement: alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo2.log'' size 50M
sql statement: alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo3.log'' size 50M
sql statement: alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo4.log'' size 50M
sql statement: alter system archive log current
sql statement: alter database recover managed standby database disconnect

released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: prim
released channel: aux

RMAN>

Recovery Manager complete.

7. Prepare Primary database to ship REDO to standby.

a. Define log_archive_config value.

This parameter should have all the participating databases in the same DG configuration. Lets say this parameter tells about primary and all the participating standby(physical,logical,far sync) in DG configuration.

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(boston,Chicago)' SCOPE=BOTH;

System altered.
NOTE: log_archive_config value is not case sensitive.

b. Set remote log shipping.

@primary:

SQL>alter system set log_archive_dest_6='service=chicago async valid_for=(all_logfiles,primary_role) db_unique_name=chicago';

System altered.
NOTE: Here primary use ASYNC redo transport method to ship REDO to standby.

c. Verify redo log shipping,

SQL> alter system switch logfile;

System altered.
SQL> col error for a30
SQL> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where dest_id=2;

   DEST_ID ERROR                          STATUS    LOG_SEQUENCE APPLIED_SCN
---------- ------------------------------ --------- ------------ -----------
         2                                VALID               63     2133221

8. Verify Standby is applying.

NOTE: MRP already started after the RMAN duplicate.
SQL> select process,status,thread#,sequence#,blocks from v#managed_standby where process like '%MRP%';

process        status                  thread#          sequence#     blocks
-------------  ----------------------  --------------  -------------  --------
MRP            WAITING_FOR_LOG           1                 64            0

Filed Under: Data Guard, oracle, oracle 12c

Some more articles you might also be interested in …

  1. Background Processes Specific to Oracle RAC
  2. Beginners Guide to Automatic Storage Management (ASM)
  3. What is the Search Order for TNS files – listener.ora, sqlnet.ora, tnsnames.ora
  4. Oracle Database 18c: Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command
  5. How to Disable Oracle Net Tracing on a Server Process without Stopping the Process
  6. Oracle Home Relinking Interview Questions and Answers
  7. How to Restore ASM Based OCR After Complete Loss of the CRS Diskgroup on Linux/UNIX
  8. How to check and repair ACFS with FSCK
  9. Beginners Guide to Monitoring Oracle Database Restore/Recovery Progress
  10. How to Restore and Recover files over network in Oracle 12c

You May Also Like

Primary Sidebar

Recent Posts

  • ncat Command Examples in Linux
  • ncat: command not found
  • nautilus Command Examples in Linux
  • namei: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright