Procedure to open physical standby database

1)Stop the Recovery
SQL> alter database recover managed standby database cancel;

Database altered.

2)Enable the flashback

SQL> alter database flashback on;

Database altered.

3)Set recovery size and location

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 100G
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/archivelog
db_recovery_file_dest_size           big integer 100G

4)Create Restore Point
SQL> CREATE RESTORE POINT BEFORE_OPEN_RW_12april2018_1 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> select CONTROLFILE_TYPE from v$database;

CONTROL
-------
STANDBY

SQL> col SCN format 999999999999999
col NAME format a30
SELECT NAME,SCN,TIME FROM V$RESTORE_POINT;SQL> SQL>

NAME                                        SCN
------------------------------ ----------------
TIME
---------------------------------------------------------------------------
BEFORE_OPEN_RW                          7728249
29-MAR-18 04.26.12.000000000 PM



5)Activate the standby

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> select CONTROLFILE_TYPE from v$database;

CONTROL
-------
CURRENT

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.