physical standby without duplicate command

create a physical standby database using RMAN without using duplicate command

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

Step 1: Backup the database that includes backup of datafiles, archivelogs and controlfile for standby
Step 2: Move the backups to the standby server
Step 3: Make proper changes in the parameter files of both primary and standby database
Step 4: Do the restore and recover on standby database

Step 5: Put the standby database in recover managed mode

Step 1:

=>> Primary:

rman target /

RMAN> run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

backup database plus archivelog;

}

Backup controlfile for standby 

rman target /

backup device type disk format

'/fast_recovery_area/TSTDB/backup/%U' current controlfile for standby;

Step 2:

Move the backups and standby controlfile to the standby server

-- parameter setting on Standby DB:
-- Standby steps 

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

archive log list;

alter system set db_recovery_file_dest='/fast_recovery_area' scope=spfile;

alter system set db_recovery_file_dest_size = 50G scope=spfile sid='*';

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'

scope=spfile;

Standby DB

rman target /

startup nomount;

-- RMAN> set dbid=3959250272; if need it. In my case no need

restore standby controlfile from

'/fast_recovery_area/TSTDB/backup/fb2q6pco_7659_1_1';

sql 'alter database mount standby database';

restore database;

RMAN> exit

SQL> alter database recover managed standby database disconnect

from session;

SQL> ALTER DATABASE RECOVER  managed standby database  cancel;  

-- cancel managed standby DB

-- Primany DB:

-- check the parameter

-- with 3 hour delay

alter system set log_archive_dest_2='SERVICE=tst_stb_01 compression=DISABLE delay=180';  

-- no delay

alter system set log_archive_dest_2='SERVICE=tst_stb_01 compression=DISABLE';  

alter system set log_archive_dest_state_2=defer;

alter system set log_archive_dest_state_2=enable;

-- Standby DB

Wait and check the alert.log files

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

You can also check the last log apply date and time:

SELECT

    CASE

        WHEN TO_CHAR(MAX(COMPLETION_TIME), 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')

        THEN TO_CHAR(MAX(COMPLETION_TIME), 'YYYY-MM-DD HH24:MI:SS')

        ELSE 'ERROR'

    END AS last_apply_time

FROM V$ARCHIVED_LOG

WHERE DEST_ID = (SELECT MIN(DEST_ID) FROM V$ARCHIVE_DEST WHERE STATUS = 'VALID');