oraclednaThis post demonstrate the Convert the Snapshot Standby Database to the Physical Standby Database.
1. Shutdown the snapshot standby database.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
———- ———- ——————
PROD READ WRITE SNAPSHOT STANDBY
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2. Start the database to the mount stage.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2113927120 bytes
Fixed Size 8898512 bytes
Variable Size 486539264 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
———- ———- ——————
PROD MOUNTED SNAPSHOT STANDBY
3. Convert the Snapshot Standby Database to the Physical Standby Database.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
———- ———- ——————
PROD MOUNTED PHYSICAL STANDBY
Alert Log==
=========
2026-03-03T01:53:59.955179+05:30
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
2026-03-03T01:53:59.955319+05:30
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (stdby)
2026-03-03T01:53:59.955838+05:30
…. (PID:4767): Killing 2 processes (PIDS:4800,4802) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 4767
2026-03-03T01:53:59.956015+05:30
Process termination requested for pid 4800 [source = rdbms], [info = 2] [request issued by pid: 4767, uid: 543232]
2026-03-03T01:53:59.956121+05:30
Process termination requested for pid 4802 [source = rdbms], [info = 2] [request issued by pid: 4767, uid: 543232]
2026-03-03T01:54:01.984469+05:30
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
2026-03-03T01:54:03.834186+05:30
Deleted Oracle managed file /u01/arch/stdby/STDBY/flashback/o1_mf_ntcshyfj_.flb
Deleted Oracle managed file /u01/arch/stdby/STDBY/flashback/o1_mf_ntcsj1ow_.flb
Guaranteed restore point dropped
2026-03-03T01:54:03.934045+05:30
…. (PID:4767): Database role cleared from SNAPSHOT STANDBY [kcvs.c:8837]
Clearing standby activation ID 638690225 (0x2611a3b1)
The primary database controlfile was created using the
‘MAXLOGFILES 16’ clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f’ SIZE 209715200;
…. (PID:4767): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8842]
…. (PID:4767): RT: Role transition work is not done
…. (PID:4767): Redo network throttle feature is disabled at mount time
Physical Standby Database mounted.
2026-03-03T01:54:03.986541+05:30
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
CONVERT TO PHYSICAL STANDBY: Complete – Database mounted as physical standby
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY
2026-03-03T01:54:04.202028+05:30
rfs (PID:5525): Primary database is in MAXIMUM PERFORMANCE mode
2026-03-03T01:54:04.288359+05:30
rfs (PID:5525): Selected LNO:4 for T-1.S-73 dbid 608515774 branch 1196677442
4. Shutdown the physical standby database and mount it.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2113927120 bytes
Fixed Size 8898512 bytes
Variable Size 486539264 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
———- ———- ——————
PROD MOUNTED PHYSICAL STANDBY
5. Start the media recovery process.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Alert Log==
=========
2026-03-03T01:57:57.181240+05:30
alter database recover managed standby database disconnect from session
2026-03-03T01:57:57.184921+05:30
Attempt to start background Managed Standby Recovery process (stdby)
Starting background process MRP0
2026-03-03T01:57:57.210656+05:30
MRP0 started with pid=43, OS id=7373
2026-03-03T01:57:57.211904+05:30
Background Managed Standby Recovery process started (stdby)
2026-03-03T01:58:02.216540+05:30
Serial Media Recovery started
MRP0 (PID:7373): Managed Standby Recovery starting Real Time Apply
stopping change tracking
2026-03-03T01:58:02.349934+05:30
MRP0 (PID:7373): Media Recovery Log /u01/arch/stdby/71_1_1196677442.arc
2026-03-03T01:58:02.350798+05:30
TT02 (PID:7441): Waiting for all non-current ORLs to be archived
2026-03-03T01:58:02.350882+05:30
TT02 (PID:7441): All non-current ORLs have been archived
TT02 (PID:7441): Clearing ORL LNO:1 /u01/app/oracle/oradata/STDBY/redo01.log
Clearing online log 1 of thread 1 sequence number 1
2026-03-03T01:58:02.428799+05:30
MRP0 (PID:7373): Media Recovery Log /u01/arch/stdby/72_1_1196677442.arc
MRP0 (PID:7373): Media Recovery Waiting for T-1.S-73 (in transit)
2026-03-03T01:58:02.454530+05:30
Recovery of Online Redo Log: Thread 1 Group 4 Seq 73 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/STDBY/redo04.log
2026-03-03T01:58:02.940762+05:30
TT02 (PID:7441): Clearing ORL LNO:1 complete
2026-03-03T01:58:03.216073+05:30
Completed: alter database recover managed standby database disconnect from session
2026-03-03T01:58:08.008019+05:30
TT02 (PID:7441): Waiting for all non-current ORLs to be archived
2026-03-03T01:58:08.008081+05:30
TT02 (PID:7441): All non-current ORLs have been archived
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
