STEPS TO TURN ON PHISICAL STANBY TO ACTIVE STANDBY DATABASE

Please follow below steps.

NOTE: Here primary & standby already configuared. The below are the steps we are performing on standby database, to make it active standby database.

-------------------------------------------------------------
1. Connect to the standby database.
-------------------------------------------------------------
2. Stop redo apply using the below script

SQL>recover managed standby database cancel;
---------------------------------------------------------------------------------------------------
3. Issue the following query to find out if the standby database is performing Redo Apply or real-time apply. If the MRP0 or MRP process exists, then the standby database is applying redo

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

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

4. Check database tole, open mode using the below script. 

SQL>select name, database_role,open_mode from v$database;

NAME      DATABASE_ROLE        OPEN_MODE

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

TESTP     PHYSICAL STANDBY      MOUNTED  

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

5. Now open the database in OPEN READ ONLY mode. Use below SQL

SQL> Alter database open read only;

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

6. Check database tole, open mode using the below script. 

SQL>select name, database_role,open_mode from v$database;

NAME      DATABASE_ROLE       OPEN_MODE

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

TESTP    PHYSICAL STANDBY     READ ONLY

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

7. start redo apply using below SQL on standby database.

SQL>alter database recover managed standby database using current logfile disconnect;

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

8. Check database tole, open mode using the below script. 

SQL>select name, database_role,open_mode from v$database;

NAME      DATABASE_ROLE       OPEN_MODE

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

TESTP    PHYSICAL STANDBY     READ ONLY WITH APPLY

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

NOTE:

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

Start Redo Apply or Real-Time Apply:

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

- To start Redo Apply, issue the following statement: - 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

- To start real-time apply, issue the following statement: - 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;