Create a physical standby database

Summary

This is the basic procedure from the Data Guard manual. This does not use RMAN DUPLICATE or Cloud Control.

The primary database is on PADSTOW with the physical standby database on BOTANY.

References

Linux Documentation

Linux man pages online

• cp
• diff
• mkdir
• mv
• scp
• tar
• vi

Oracle Manuals

• Oracle^® 12.1 Data Guard Concepts and Administration
  • 3 Creating a Physical Standby Database
    • 3.1 Preparing the Primary Database for Standby Database Creation
      • 3.1.1 Enable Forced Logging
      • 3.1.2 Configure Redo Transport Authentication
      • 3.1.3 Configure the Primary Database to Receive Redo Data
      • 3.1.4 Set Primary Database Initialization Parameters
      • 3.1.5 Enable Archiving
    • 3.2 Step-by-Step Instructions for Creating a Physical Standby Database
      • 3.2.1 Creating a Physical Standby Task 1: Create a Backup Copy of the Primary Database Data Files
      • 3.2.2 Creating a Physical Standby Task 2: Create a Control File for the Standby Database
      • 3.2.3 Creating a Physical Standby Task 3: Create a Parameter File for the Standby Database
      • 3.2.4 Creating a Physical Standby Task 4: Copy Files from the Primary System to the Standby System
      • 3.2.5 Creating a Physical Standby Task 5: Set Up the Environment to Support the Standby Database
      • 3.2.6 Creating a Physical Standby Task 6: Start the Physical Standby Database
      • 3.2.7 Creating a Physical Standby Task 7: Verify the Physical Standby Database Is Performing Properly
• Oracle^® 12.1 Database Backup and Recovery Reference
  • ALLOCATE CHANNEL
  • BACKUP
  • DELETE
  • RUN
• Oracle^® 12.1 Database Reference
  • 1 Initialization Parameters
    • DB_FILE_NAME_CONVERT
    • DB_NAME
    • DB_UNIQUE_NAME
    • FAL_SERVER
    • LOG_ARCHIVE_CONFIG
    • LOG_ARCHIVE_DEST_n
    • LOG_ARCHIVE_FORMAT
    • LOG_FILE_NAME_CONVERT
    • REMOTE_LOGIN_PASSWORDFILE
  • Part III Dynamic Performance Views
    • V$DATABASE
    • V$DATAFILE
    • V$LOGFILE
    • V$MANAGED_STANDBY
    • V$PARAMETER
• Oracle^® 12.1 Database Net Services Administrator's Guide
  • 15 Testing Connections
    • Using the TNSPING Utility to Test Connectivity from the Client
• Oracle^® 12.1 Database SQL Reference
  • ALTER DATABASE
  • ALTER SYSTEM
  • CREATE PFILE
  • CREATE SPFILE
• Oracle^® 12.1 Database Net Services Reference
  • 1 Listener Control Utility
• Oracle^® 12.1 SQL*Plus^® User's Guide and Reference
  • SQL*Plus Command Reference
    • STARTUP

Preparation

Create Database

I created the OCM12PRI CDB on PADSTOW using dbca. It has one (1) PDB: EXAMPLES.

Enable Force Logging

Following the procedure in “3.1.1 Enable Forced Logging”, I ran the following commands on PADSTOW as the ORACLE user:

sqlplus / as sysdba

The output is:

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 4 20:44:38 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance. 

Startup the database instance in MOUNT mode:

startup mount 

The output is:

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size          2923920 bytes

Variable Size         822084208 bytes

Database Buffers      419430400 bytes

Redo Buffers           13852672 bytes

Database mounted.

Determine the current settings:

select name, log_mode, force_logging from v$database; 

The output is:

NAME      LOG_MODE     FORCE_LOGGING

--------- ------------ ---------------------------------------

OCM12PRI NOARCHIVELOG NO

Change the settings with the following commands:

ALTER DATABASE FORCE LOGGING;

ALTER DATABASE ARCHIVELOG; 

Verify that the settings are correct:

select name, log_mode, force_logging from v$database;

The output is:

NAME      LOG_MODE     FORCE_LOGGING

--------- ------------ ---------------------------------------

OCM12PRI ARCHIVELOG YES

In addition to enabling FORCE LOGGING, I also enabled ARCHIVELOG mode.

Redo Transport Authentication

Since I do not have OID configured in my environment, there is no need to Configure Redo Transport Authentication.

Configure the Primary Database to Receive Redo Data

Since this environment is only used for a fast fail-over test, there is no need to Configure the Primary Database to Receive Redo Data.

Set Primary Database Initialization Parameters

For the list of parameters described in “3.1.4 Set Primary Database Initialization Parameters”, I ran the following query to get current values:

COLUMN name   FORMAT A25

COLUMN value  FORMAT A50

SET PAGESIZE 100

SELECT

     name,

     value

   FROM

     v$parameter

   WHERE

     NAME IN (

       'db_name',

       'db_unique_name',

       'log_archive_config',

       'control_files',

       'log_archive_dest_1',

       'log_archive_dest_2',

       'remote_login_passwordfile',

       'log_archive_format'

     )

   ORDER BY

     name

/

The result was:

NAME                      VALUE

------------------------- --------------------------------------------------

control_files             /opt/app/oracle/oradata/ocm12pri/control01.ctl, /o

                           pt/app/oracle/fast_recovery_area/ocm12pri/control0

                           2.ctl

db_name                   ocm12pri

db_unique_name            ocm12pri_padstow

log_archive_config

log_archive_dest_1

log_archive_dest_2

log_archive_format        %t_%s_%r.dbf

remote_login_passwordfile EXCLUSIVE

8 rows selected.

Added the following lines to /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora on PADSTOW:

OCM12PRI_BOTANY =

   (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST = botany.yaocm.id.au)(PORT = 1521))

     (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = ocm12pri_botany.yaocm.id.au)

     )

   ) 

Verified the changes as follows:

tnsping ocm12pri_botany 

The output was:

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 14-JUL-2018 21:34:53

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files: /opt/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 = botany.yaocm.id.au)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ocm12pri_botany.yaocm.id.au)))

OK (10 msec)

In all, I only had to make the following three (3) parameter changes:

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(OCM12PRI_PADSTOW,OCM12PRI_BOTANY)';

ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OCM12PRI_PADSTOW';

ALTER SYSTEM SET log_archive_dest_2='SERVICE=OCM12PRI_BOTANY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OCM12PRI_BOTANY';

The new set of parameters are:

NAME                      VALUE

------------------------- --------------------------------------------------

control_files             /opt/app/oracle/oradata/ocm12pri/control01.ctl, /o

                          pt/app/oracle/fast_recovery_area/ocm12pri/control0

                          2.ctl

db_name                   ocm12pri

db_unique_name            ocm12pri_padstow

log_archive_config        DG_CONFIG=(OCM12PRI_PADSTOW,OCM12PRI_BOTANY)

log_archive_dest_1        LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_

                          LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OCM12PRI_PADSTO

                          W

log_archive_dest_2        SERVICE=OCM12PRI_BOTANY ASYNC VALID_FOR=(ONLINE_LO

                          GFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OCM12PRI_BOTAN

                          Y

log_archive_format        %t_%s_%r.dbf

remote_login_passwordfile EXCLUSIVE

  8 rows selected. 

Since I only plan to do fail-over tests, there is now need to configure the other parameters or to allocate standby redo log files.

Enable Archiving

I had already enabled archiving earlier (see 3.1.5 Enable Archiving for procedure).

Creating a Physical Standby

Task 1: Create a Backup Copy of the Primary Database Data Files

Followed the procedure in “3.2.1 Creating a Physical Standby Task 1: Create a Backup Copy of the Primary Database Data Files”.

I used the following RMAN commands to backup the database:

run {

   allocate channel d1 device type disk;

   allocate channel d2 device type disk;

   backup database archivelog all delete input;

   delete noprompt obsolete;

} 

The RMAN output is as follows:

using target database control file instead of recovery catalog

allocated channel: d1 channel d1: SID=85 device type=DISK

allocated channel: d2 channel d2: SID=82 device type=DISK

Starting backup at 14-JUL-18

channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set input datafile file number=00011 name=/opt/app/oracle/oradata/ocm12pri/examples/example01.dbf input datafile file number=00010 name=/opt/app/oracle/oradata/ocm12pri/examples/SAMPLE_SCHEMA_users01.dbf channel d1: starting piece 1 at 14-JUL-18 channel d2: starting full datafile backup set channel d2: specifying datafile(s) in backup set input datafile file number=00001 name=/opt/app/oracle/oradata/ocm12pri/system01.dbf input datafile file number=00004 name=/opt/app/oracle/oradata/ocm12pri/undotbs01.dbf channel d2: starting piece 1 at 14-JUL-18 channel d1: finished piece 1 at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsb4nm_.bkp tag=TAG20180714T215755 comment=NONE channel d1: backup set complete, elapsed time: 00:00:27 channel d1: starting archived log backup set channel d1: specifying archived log(s) in backup set input archived log thread=1 sequence=34 RECID=1 STAMP=981485203 input archived log thread=1 sequence=35 RECID=2 STAMP=981485482 channel d1: starting piece 1 at 14-JUL-18 channel d1: finished piece 1 at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T215755_fnmsbz5y_.bkp tag=TAG20180714T215755 comment=NONE channel d1: backup set complete, elapsed time: 00:00:08 channel d1: deleting archived log(s) RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/archivelog/2018_07_14/o1_mf_1_34_fnmg3lps_.arc thread=1 sequence=34 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/archivelog/2018_07_14/o1_mf_1_35_fnmgd9mw_.arc thread=1 sequence=35 channel d1: starting archived log backup set channel d1: specifying archived log(s) in backup set input archived log thread=1 sequence=36 RECID=3 STAMP=981485640 input archived log thread=1 sequence=37 RECID=4 STAMP=981495926 channel d1: starting piece 1 at 14-JUL-18 channel d1: finished piece 1 at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T215755_fnmsc7k9_.bkp tag=TAG20180714T215755 comment=NONE channel d1: backup set complete, elapsed time: 00:00:07 channel d1: deleting archived log(s) RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/archivelog/2018_07_14/o1_mf_1_36_fnmgk7xc_.arc thread=1 sequence=36 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/archivelog/2018_07_14/o1_mf_1_37_fnmrlokk_.arc thread=1 sequence=37 channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set input datafile file number=00009 name=/opt/app/oracle/oradata/ocm12pri/examples/sysaux01.dbf input datafile file number=00008 name=/opt/app/oracle/oradata/ocm12pri/examples/system01.dbf channel d1: starting piece 1 at 14-JUL-18 channel d2: finished piece 1 at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsb7dy_.bkp tag=TAG20180714T215755 comment=NONE channel d2: backup set complete, elapsed time: 00:01:18 channel d2: starting full datafile backup set channel d2: specifying datafile(s) in backup set input datafile file number=00003 name=/opt/app/oracle/oradata/ocm12pri/sysaux01.dbf input datafile file number=00006 name=/opt/app/oracle/oradata/ocm12pri/users01.dbf channel d2: starting piece 1 at 14-JUL-18 channel d1: finished piece 1 at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmschh2_.bkp tag=TAG20180714T215755 comment=NONE channel d1: backup set complete, elapsed time: 00:01:02 channel d1: starting full datafile backup set channel d1: specifying datafile(s) in backup set input datafile file number=00007 name=/opt/app/oracle/oradata/ocm12pri/pdbseed/sysaux01.dbf channel d1: starting piece 1 at 14-JUL-18 channel d2: finished piece 1 at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsdowp_.bkp tag=TAG20180714T215755 comment=NONE channel d2: backup set complete, elapsed time: 00:00:42 channel d2: starting full datafile backup set channel d2: specifying datafile(s) in backup set input datafile file number=00005 name=/opt/app/oracle/oradata/ocm12pri/pdbseed/system01.dbf channel d2: starting piece 1 at 14-JUL-18 channel d1: finished piece 1 at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsfftt_.bkp tag=TAG20180714T215755 comment=NONE channel d1: backup set complete, elapsed time: 00:00:42 channel d2: finished piece 1 at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsfykn_.bkp tag=TAG20180714T215755 comment=NONE channel d2: backup set complete, elapsed time: 00:00:25 Finished backup at 14-JUL-18  Starting Control File and SPFILE Autobackup at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/autobackup/2018_07_14/o1_mf_s_981496824_fnmsgwtq_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 14-JUL-18  RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/archivelog/2018_07_14/o1_mf_1_34_fnmg3lps_.arc thread=1 sequence=34 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/archivelog/2018_07_14/o1_mf_1_35_fnmgd9mw_.arc thread=1 sequence=35 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/archivelog/2018_07_14/o1_mf_1_36_fnmgk7xc_.arc thread=1 sequence=36 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/archivelog/2018_07_14/o1_mf_1_37_fnmrlokk_.arc thread=1 sequence=37 Deleting the following obsolete backups and copies: Type                 Key    Completion Time    Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set           2      14-JUL-18            Backup Piece       2      14-JUL-18          /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T215755_fnmsbz5y_.bkp Backup Set           3      14-JUL-18            Backup Piece       3      14-JUL-18          /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T215755_fnmsc7k9_.bkp deleted backup piece backup piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T215755_fnmsbz5y_.bkp RECID=2 STAMP=981496702 deleted backup piece backup piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T215755_fnmsc7k9_.bkp RECID=3 STAMP=981496711 Deleted 2 objects  released channel: d1 released channel: d2 

Note:

The above warnings are caused by the Data Guard settings I had configured earlier in that the marked archived redo log files have not been yet applied the non-existent standby database as specified in the parameter, log_archive_dest_2.

To make the copying of the RMAN backup files easier, I used the following RMAN command to backup the entire recovery area to /tmp:

backup recovery area to destination '/tmp';

The RMAN output is as follows:

Starting backup at 14-JUL-18 using channel ORA_DISK_1 specification does not match any datafile copy in the repository channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=34 RECID=1 STAMP=981485203 input archived log thread=1 sequence=35 RECID=2 STAMP=981485482 input archived log thread=1 sequence=36 RECID=3 STAMP=981485640 input archived log thread=1 sequence=37 RECID=4 STAMP=981495926 channel ORA_DISK_1: starting piece 1 at 14-JUL-18 channel ORA_DISK_1: finished piece 1 at 14-JUL-18 piece handle=/tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T222835_fnmv3mlz_.bkp tag=TAG20180714T222835 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: input backup set: count=1, stamp=981496675, piece=1 channel ORA_DISK_1: starting piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsb4nm_.bkp piece handle=/tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv3to3_.bkp comment=NONE channel ORA_DISK_1: finished piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:07 channel ORA_DISK_1: input backup set: count=2, stamp=981496676, piece=1 channel ORA_DISK_1: starting piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsb7dy_.bkp piece handle=/tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv41p9_.bkp comment=NONE channel ORA_DISK_1: finished piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:25 channel ORA_DISK_1: input backup set: count=5, stamp=981496718, piece=1 channel ORA_DISK_1: starting piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmschh2_.bkp piece handle=/tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv4tr2_.bkp comment=NONE channel ORA_DISK_1: finished piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:25 channel ORA_DISK_1: input backup set: count=6, stamp=981496755, piece=1 channel ORA_DISK_1: starting piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsdowp_.bkp piece handle=/tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv5mt2_.bkp comment=NONE channel ORA_DISK_1: finished piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:15 channel ORA_DISK_1: input backup set: count=7, stamp=981496781, piece=1 channel ORA_DISK_1: starting piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsfftt_.bkp piece handle=/tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv62vw_.bkp comment=NONE channel ORA_DISK_1: finished piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:15 channel ORA_DISK_1: input backup set: count=8, stamp=981496797, piece=1 channel ORA_DISK_1: starting piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmsfykn_.bkp piece handle=/tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv6kxr_.bkp comment=NONE channel ORA_DISK_1: finished piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:07 channel ORA_DISK_1: input backup set: count=9, stamp=981496824, piece=1 channel ORA_DISK_1: starting piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece /opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/autobackup/2018_07_14/o1_mf_s_981496824_fnmsgwtq_.bkp piece handle=/tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_ncsnf_TAG20180714T220024_fnmv6s0g_.bkp comment=NONE channel ORA_DISK_1: finished piece 1 at 14-JUL-18 channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:04 Finished backup at 14-JUL-18 Starting Control File and SPFILE Autobackup at 14-JUL-18 piece handle=/opt/app/oracle/fast_recovery_area/OCM12PRI_PADSTOW/autobackup/2018_07_14/o1_mf_s_981498620_fnmv706q_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 14-JUL-18

Created a compressed TAR archive as follows:

tar cvzf /tmp/padstow.gz /tmp/OCM12PRI_PADSTOW

The output is as follows:

tar: Removing leading `/' from member names /tmp/OCM12PRI_PADSTOW/ /tmp/OCM12PRI_PADSTOW/backupset/ /tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/ /tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv41p9_.bkp /tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T222835_fnmv3mlz_.bkp /tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_ncsnf_TAG20180714T220024_fnmv6s0g_.bkp /tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv5mt2_.bkp /tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/ /tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/ /tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/ /tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv6kxr_.bkp /tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv62vw_.bkp /tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/ /tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/ /tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/ /tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv4tr2_.bkp /tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv3to3_.bkp

Task 2: Create a Control File for the Standby Database

Followed the procedure in “3.2.2 Creating a Physical Standby Task 2: Create a Control File for the Standby Database”.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/botany.ctl';

Task 3: Create a Parameter File for the Standby Database

Followed the procedure in “3.2.3 Creating a Physical Standby Task 3: Create a Parameter File for the Standby Database”.

CREATE PFILE='/tmp/initpadstow.ora' FROM SPFILE;

I used the following commands to make changes to the proposed parameter file for the standby database on BOTANY:

[oracle@padstow ~]$ cp /tmp/initpadstow.ora /tmp/initbotany.ora [oracle@padstow ~]$ vi /tmp/initbotany.ora [oracle@padstow ~]$ diff /tmp/initpadstow.ora /tmp/initbotany.ora 21c21 < *.db_unique_name='ocm12pri_padstow' --- > *.db_unique_name='ocm12pri_botany' 24a25 > *.fal_server='ocm12pri_padstow' 27,28c28 < *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OCM12PRI_PADSTOW' < *.log_archive_dest_2='SERVICE=OCM12PRI_BOTANY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OCM12PRI_BOTANY' --- > *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OCM12PRI_BOTANY'

Note:

The above changes are much simpler than the ones given in “3.2.3 Creating a Physical Standby Task 3: Create a Parameter File for the Standby Database” because I am not going to do a switchover.

I did not include the following system parameters because the current files do not include the DB unique name:

• DB_FILE_NAME_CONVERT
• LOG_FILE_NAME_CONVERT

The values currently are:

SQL> SELECT name FROM v$datafile ORDER BY 1; NAME -------------------------------------------------------------------------------- /opt/app/oracle/oradata/ocm12pri/examples/SAMPLE_SCHEMA_users01.dbf /opt/app/oracle/oradata/ocm12pri/examples/example01.dbf /opt/app/oracle/oradata/ocm12pri/examples/sysaux01.dbf /opt/app/oracle/oradata/ocm12pri/examples/system01.dbf /opt/app/oracle/oradata/ocm12pri/pdbseed/sysaux01.dbf /opt/app/oracle/oradata/ocm12pri/pdbseed/system01.dbf /opt/app/oracle/oradata/ocm12pri/sysaux01.dbf /opt/app/oracle/oradata/ocm12pri/system01.dbf /opt/app/oracle/oradata/ocm12pri/undotbs01.dbf /opt/app/oracle/oradata/ocm12pri/users01.dbf 10 rows selected. SQL> SELECT member FROM v$logfile ORDER BY 1; MEMBER -------------------------------------------------------------------------------- /opt/app/oracle/oradata/ocm12pri/redo01.log /opt/app/oracle/oradata/ocm12pri/redo02.log /opt/app/oracle/oradata/ocm12pri/redo03.log

Task 4: Copy Files from the Primary System to the Standby System

Followed the procedure in “3.2.4 Creating a Physical Standby Task 4: Copy Files from the Primary System to the Standby System”.

Ran the following commands to copy the files from PADSTOW to BOTANY:

scp /tmp/botany.ctl botany:/tmp scp /tmp/initbotany.ora botany:/tmp scp /tmp/padstow.gz botany:/tmp scp /opt/app/oracle/product/12.1.0/dbhome_1/dbs/orapwocm12pri botany:/opt/app/oracle/product/12.1.0/dbhome_1/dbs

Used the following command to restore the backup of the recovery area from PADSTOW into the current directory on BOTANY:

tar xvzf /tmp/padstow.gz

The output was:

tmp/OCM12PRI_PADSTOW/ tmp/OCM12PRI_PADSTOW/backupset/ tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/ tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv41p9_.bkp tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_annnn_TAG20180714T222835_fnmv3mlz_.bkp tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_ncsnf_TAG20180714T220024_fnmv6s0g_.bkp tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv5mt2_.bkp tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/ tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/ tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/ tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv6kxr_.bkp tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv62vw_.bkp tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/ tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/ tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/ tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv4tr2_.bkp tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv3to3_.bkp

Move the backups from the current directory back to the /tmp directory:

mv tmp/OCM12PRI_PADSTOW /tmp

Create the required directories on BOTANY:

mkdir -p /opt/app/oracle/oradata/ocm12pri mkdir -p /opt/app/oracle/fast_recovery_area/ocm12pri mkdir -p /opt/app/oracle/oradata/ocm12pri/examples mkdir -p /opt/app/oracle/oradata/ocm12pri/pdbseed

Update /etc/oratab as follows:

cat >>/etc/oratab <<DONE ocm12pri:/opt/app/oracle/product/12.1.0/dbhome_1:N DONE

Task 5: Set Up the Environment to Support the Standby Database

Followed the procedure in “3.2.5 Creating a Physical Standby Task 5: Set Up the Environment to Support the Standby Database”.

2. Copy Remote Password File to Botany

Ran the following command on PADSTOW:

scp $ORACLE_HOME/dbs/orapwocm12pri botany:$ORACLE_HOME/dbs/

3. Configure and Start Listener on Botany

Listener was already configure when BOTANY was cloned from PADSTOW. This is confirmed as follows:

lsnrctl status

The output is:

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JUL-2018 19:58:53 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 23-JUL-2018 11:39:43 Uptime 0 days 8 hr. 19 min. 9 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /opt/app/oracle/diag/tnslsnr/botany/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=botany)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully

4. Create Oracle Net service names

Added the following lines to /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora on BOTANY:

OCM12PRI_PADSTOW = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = padstow.yaocm.id.au)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ocm12pri_padstow.yaocm.id.au) ) )

Verified the changes as follows:

tnsping ocm12pri_padstow

The output was:

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 23-JUL-2018 21:28:12 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /opt/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 = padstow.yaocm.id.au)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ocm12pri_padstow.yaocm.id.au))) OK (10 msec)

5. Create SPFILE

Configure an environment for an idle instance (on BOTANY) as follows:

. oraenv

Use the following parameters:

ORACLE_SID = [personal] ? ocm12pri ORACLE_HOME = [/home/oracle] ? /opt/app/oracle/product/12.1.0/dbhome_1 The Oracle base remains unchanged with value /opt/app/oracle

Start session with an idle instance as follows:

sqlplus / as sysdba

The output is:

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 23 21:31:35 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance.

Convert the parameter file into a SPFILE as follows:

create spfile from pfile='/tmp/initbotany.ora';

The expected output is:

File created.

Exit from SQL*Plus as follows as all following commands will be done through RMAN:

exit

The expected output is:

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

Task 6: Start the Physical Standby Database

Note:

There is a missing step here: I did not record how I used the standby control (/tmp/botany.ctl) to create the cotrolfiles on the physical standby.

Followed the procedure in “3.2.6 Creating a Physical Standby Task 6: Start the Physical Standby Database”.

Start a RMAN session (on BOTANY):

rman target /

Sample output is:

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 3 21:45:27 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database (not started)

Mount the database:

startup mount

Sample output is:

Oracle instance started database mounted Total System Global Area 1224736768 bytes Fixed Size 2923824 bytes Variable Size 771752656 bytes Database Buffers 436207616 bytes Redo Buffers 13852672 bytes

Restore the database as follows:

restore database;

Sample output is:

Starting restore at 04-AUG-18 Starting implicit crosscheck backup at 04-AUG-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=23 device type=DISK Crosschecked 16 objects Finished implicit crosscheck backup at 04-AUG-18 Starting implicit crosscheck copy at 04-AUG-18 using channel ORA_DISK_1 Crosschecked 2 objects Finished implicit crosscheck copy at 04-AUG-18 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /opt/app/oracle/fast_recovery_area/OCM12PRI_BOTANY/archivelog/2018_07_31/o1_mf_1_49_fp0j6kf2_.arc File Name: /opt/app/oracle/fast_recovery_area/OCM12PRI_BOTANY/archivelog/2018_07_31/o1_mf_1_50_fp0j6hr5_.arc using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/ocm12pri/examples/SAMPLE_SCHEMA_users01.dbf channel ORA_DISK_1: restoring datafile 00011 to /opt/app/oracle/oradata/ocm12pri/examples/example01.dbf channel ORA_DISK_1: reading from backup piece /tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv3to3_.bkp channel ORA_DISK_1: piece handle=/tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv3to3_.bkp tag=TAG20180714T215755 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/ocm12pri/system01.dbf channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/ocm12pri/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv41p9_.bkp channel ORA_DISK_1: piece handle=/tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv41p9_.bkp tag=TAG20180714T215755 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/ocm12pri/examples/system01.dbf channel ORA_DISK_1: restoring datafile 00009 to /opt/app/oracle/oradata/ocm12pri/examples/sysaux01.dbf channel ORA_DISK_1: reading from backup piece /tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv4tr2_.bkp channel ORA_DISK_1: piece handle=/tmp/OCM12PRI_PADSTOW/708F04B2A880250FE0536E01A8C01CF7/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv4tr2_.bkp tag=TAG20180714T215755 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:26 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/ocm12pri/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/ocm12pri/users01.dbf channel ORA_DISK_1: reading from backup piece /tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv5mt2_.bkp channel ORA_DISK_1: piece handle=/tmp/OCM12PRI_PADSTOW/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv5mt2_.bkp tag=TAG20180714T215755 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/ocm12pri/pdbseed/sysaux01.dbf channel ORA_DISK_1: reading from backup piece /tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv62vw_.bkp channel ORA_DISK_1: piece handle=/tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv62vw_.bkp tag=TAG20180714T215755 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/ocm12pri/pdbseed/system01.dbf channel ORA_DISK_1: reading from backup piece /tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv6kxr_.bkp channel ORA_DISK_1: piece handle=/tmp/OCM12PRI_PADSTOW/708E7D80672721D0E0536E01A8C0111B/backupset/2018_07_14/o1_mf_nnndf_TAG20180714T215755_fnmv6kxr_.bkp tag=TAG20180714T215755 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 04-AUG-18

Start the recovery process as follows:

alter database recover managed standby database disconnect from session;;

Sample output is:

Statement processed

Task 7: Verify the Physical Standby Database Is Performing Properly

Followed the procedure in “3.2.7 Creating a Physical Standby Task 7: Verify the Physical Standby Database Is Performing Properly”.

Run the following query to see the status of the apply process (on BOTANY):

SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';

Sample output is:

CLIENT_P PROCESS THREAD# SEQUENCE# STATUS -------- --------- ---------- ---------- ------------ LGWR RFS 1 53 IDLE N/A MRP0 1 53 WAIT_FOR_LOG