Create a physical standby database

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:

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