Create Logical Standby Database in Oracle 19c oracledna DataGuard March 4, 2026 This post demonstrates the create logical standby database in Oracle 19c. The logical standby is maintained by applying data changes as SQL statements via SQL Apply, which extracts these changes from the redo data received from the primary. Pre-requisites:=========== 1. Create a physical standby database. 2. Before converting the physical standby database to logical standby database, see the below document for a complete list of data type and storage type considerations. https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-logical-standby.html#GUID-3666CA35-D993-44B6-8D70-A2B8B9EC8B2E 3. Ensure Table Rows in the Primary Database Can Be Uniquely Identified. This is because, Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database. In primary database, run the below query to display a list of tables that SQL Apply may not be able to uniquely identify. SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = ‘Y’;no rows selected 1. Ensure that Physical Standby is in Sync with Primary Database. Run the below query in Physical Standby database to check the archive log sync. SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied” FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPLWHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied———- ———————- ———————1 78 78 2. Stop Redo Apply (MRP) on the Physical Standby Database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered. 3. Set Parameters for Logical Standby in Primary database. 3.1 Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES,ALL_ROLES) Change the VALID_FOR attribute in the original LOG_ARCHIVE_DEST_1 destination to archive redo data only from the online redo log and not from the standby redo log. SQL> show parameter LOG_ARCHIVE_DEST_1NAME TYPE VALUE———————————— ———– ——————————log_archive_dest_1 string LOCATION=/u01/arch/prod VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod SQL> alter system set LOG_ARCHIVE_DEST_1= ‘LOCATION=/u01/arch/prod VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod’ scope=both;System altered. SQL> show parameter LOG_ARCHIVE_DEST_1NAME TYPE VALUE———————————— ———– ——————————log_archive_dest_1 string LOCATION=/u01/arch/prod VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod 3.2 set the LOG_ARCHIVE_DEST_3 destination on the primary database and for logs which will received on Standby from Primary. This parameter will be in effective only when the primary database is transitioned to the logical standby role. SQL> alter system set LOG_ARCHIVE_DEST_3= ‘LOCATION=/u01/arch/log_stby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prod’ scope=both;System altered. SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;System altered. 4. Build a Dictionary in the Redo Data on Primary Database. Building a LogMiner dictionary is mandatory, so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. This will enable the supplemental logging automatically set up to log primary key and unique-constraint/index columns. To build the LogMiner dictionary, execute the below statement. The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. SQL> EXECUTE DBMS_LOGSTDBY.BUILD;PL/SQL procedure successfully completed. From Primary alert log:=================2026-03-05T00:54:51.014293+05:30alter database add supplemental log data (primary key, unique index) columns2026-03-05T00:54:51.065128+05:30SUPLOG: Previous supplemental logging attributes at scn = 2341923SUPLOG: minimal = OFF, primary key = OFFSUPLOG: unique = OFF, foreign key = OFF, all column = OFFSUPLOG: procedural replication = OFF, subset db replication = OFF2026-03-05T00:54:51.163148+05:30SUPLOG: New supplemental logging attributes at scn = 2341923SUPLOG: minimal = ON, primary key = ONSUPLOG: unique = ON, foreign key = OFF, all column = OFFSUPLOG: procedural replication = OFF, subset db replication = OFFSUPPLEMENTAL LOG: Waiting for completion of transactions started at or before scn 2341923 (0x000000000023bc23)SUPPLEMENTAL LOG: All transactions started at or before scn 2341923 (0x000000000023bc23) have completedCompleted: alter database add supplemental log data (primary key, unique index) columnsalter database add supplemental log data for procedural replication2026-03-05T00:54:51.196423+05:30SUPLOG: Previous supplemental logging attributes at scn = 2341934SUPLOG: minimal = ON, primary key = ONSUPLOG: unique = ON, foreign key = OFF, all column = OFFSUPLOG: procedural replication = OFF, subset db replication = OFF2026-03-05T00:54:51.196860+05:30SUPLOG: New supplemental logging attributes at scn = 2341934SUPLOG: minimal = ON, primary key = ONSUPLOG: unique = ON, foreign key = OFF, all column = OFFSUPLOG: procedural replication = ON, subset db replication = OFFCompleted: alter database add supplemental log data for procedural replicationThu Mar 05 00:54:51 2026Logminer Bld: Build started2026-03-05T00:54:51.269958+05:30ALTER SYSTEM SWITCH ALL LOGFILE start (prod)2026-03-05T00:54:51.304773+05:30Thread 1 advanced to log sequence 80 (LGWR switch)Current log# 3 seq# 80 mem# 0: /u01/app/oracle/oradata/PROD/redo03.log2026-03-05T00:54:51.304859+05:30ALTER SYSTEM SWITCH ALL LOGFILE complete (prod)2026-03-05T00:54:51.344150+05:30Thu Mar 05 00:54:51 2026Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 2341945 LockdownSCN is 23419452026-03-05T00:54:51.620756+05:30TT02 (PID:3145): SRL selected for T-1.S-80 for LAD:22026-03-05T00:54:51.827520+05:30ARC1 (PID:3139): Archived Log entry 138 added for T-1.S-79 ID 0x24475078 LAD:12026-03-05T00:54:51.851286+05:30Resize operation completed for file# 1, old size 921600K, new size 931840K2026-03-05T00:54:52.169900+05:30Resize operation completed for file# 1, old size 931840K, new size 942080K2026-03-05T00:54:52.622072+05:30ALTER SYSTEM ARCHIVE LOG2026-03-05T00:54:52.663766+05:30Thread 1 advanced to log sequence 81 (LGWR switch)Current log# 1 seq# 81 mem# 0: /u01/app/oracle/oradata/PROD/redo01.log2026-03-05T00:54:52.712405+05:30NET (PID:4304): Archived Log entry 139 added for T-1.S-80 ID 0x24475078 LAD:1Thu Mar 05 00:54:52 2026Logminer Bld: DoneLOGMINER: Dictionary Build: Waiting for completion of transactions started at or before scn 2342475 (0x000000000023be4b)LOGMINER: Dictionary Build: All transactions started at or before scn 2342475 (0x000000000023be4b) have completed 5. Convert the Physical Standby Database to a Logical Standby Database. In below SQL, specify the new db_name for logical standby database other than the db_name of primary database. If database was opened with spfile, the nelow SQL update the SPFILE with new db_name. SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <db_name>; SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY logstdby;Database altered. From Standby alert log:=================2026-03-05T01:07:11.416494+05:30ALTER DATABASE RECOVER TO LOGICAL STANDBY logstdby2026-03-05T01:07:11.416824+05:30Media Recovery Start: Managed Standby Recovery (stdby)2026-03-05T01:07:11.418994+05:30Serial Media Recovery startedNET (PID:4240): Managed Standby Recovery not using Real Time Applystopping change tracking2026-03-05T01:07:11.522669+05:30NET (PID:4240): Media Recovery Log /u01/arch/stdby/79_1_1196677442.arc2026-03-05T01:07:11.669361+05:30NET (PID:4240): Media Recovery Log /u01/arch/stdby/80_1_1196677442.arcResize operation completed for file# 1, old size 921600K, new size 931840KResize operation completed for file# 1, old size 931840K, new size 942080K2026-03-05T01:07:11.917822+05:30NET (PID:4240): Media Recovery Log /u01/arch/stdby/81_1_1196677442.arc2026-03-05T01:07:12.472000+05:30Incomplete Recovery applied until change 2342487 time 03/05/2026 00:54:522026-03-05T01:07:12.483939+05:30Media Recovery Complete (stdby)stopping change trackingNET (PID:4240): Killing 3 processes (PIDS:4287,4300,4291) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 42402026-03-05T01:07:12.545859+05:30Process termination
Convert the Snapshot Standby Database to the Physical Standby Database
Convert the Snapshot Standby Database to the Physical Standby Database oracledna DataGuard March 2, 2026 This 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 immediateDatabase closed.Database dismounted.ORACLE instance shut down. 2. Start the database to the mount stage. SQL> startup mountORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase 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:30ALTER DATABASE CONVERT TO PHYSICAL STANDBY2026-03-03T01:53:59.955319+05:30ALTER 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 47672026-03-03T01:53:59.956015+05:30Process termination requested for pid 4800 [source = rdbms], [info = 2] [request issued by pid: 4767, uid: 543232]2026-03-03T01:53:59.956121+05:30Process termination requested for pid 4802 [source = rdbms], [info = 2] [request issued by pid: 4767, uid: 543232]2026-03-03T01:54:01.984469+05:30Flashback Restore StartFlashback Restore CompleteDrop guaranteed restore pointStopping background process RVWR2026-03-03T01:54:03.834186+05:30Deleted Oracle managed file /u01/arch/stdby/STDBY/flashback/o1_mf_ntcshyfj_.flbDeleted Oracle managed file /u01/arch/stdby/STDBY/flashback/o1_mf_ntcsj1ow_.flbGuaranteed restore point dropped2026-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 logfilesUse the following SQL commands on the standby database to createstandby 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 timePhysical Standby Database mounted.2026-03-03T01:54:03.986541+05:30In-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 solutionsCONVERT TO PHYSICAL STANDBY: Complete – Database mounted as physical standbyCompleted: ALTER DATABASE CONVERT TO PHYSICAL STANDBY2026-03-03T01:54:04.202028+05:30rfs (PID:5525): Primary database is in MAXIMUM PERFORMANCE mode2026-03-03T01:54:04.288359+05:30rfs (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 immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down. SQL> startup mountORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase 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:30alter database recover managed standby database disconnect from session2026-03-03T01:57:57.184921+05:30Attempt to start background Managed Standby Recovery process (stdby)Starting background process MRP02026-03-03T01:57:57.210656+05:30MRP0 started with pid=43, OS id=73732026-03-03T01:57:57.211904+05:30Background Managed Standby Recovery process started (stdby)2026-03-03T01:58:02.216540+05:30Serial Media Recovery startedMRP0 (PID:7373): Managed Standby Recovery starting Real Time Applystopping change tracking2026-03-03T01:58:02.349934+05:30MRP0 (PID:7373): Media Recovery Log /u01/arch/stdby/71_1_1196677442.arc2026-03-03T01:58:02.350798+05:30TT02 (PID:7441): Waiting for all non-current ORLs to be archived2026-03-03T01:58:02.350882+05:30TT02 (PID:7441): All non-current ORLs have been archivedTT02 (PID:7441): Clearing ORL LNO:1 /u01/app/oracle/oradata/STDBY/redo01.logClearing online log 1 of thread 1 sequence number 12026-03-03T01:58:02.428799+05:30MRP0 (PID:7373): Media Recovery Log /u01/arch/stdby/72_1_1196677442.arcMRP0 (PID:7373): Media Recovery Waiting for T-1.S-73 (in transit)2026-03-03T01:58:02.454530+05:30Recovery of Online Redo Log: Thread 1 Group 4 Seq 73 Reading mem 0Mem# 0: /u01/app/oracle/oradata/STDBY/redo04.log2026-03-03T01:58:02.940762+05:30TT02 (PID:7441): Clearing ORL LNO:1 complete2026-03-03T01:58:03.216073+05:30Completed: alter database recover managed standby database disconnect from session2026-03-03T01:58:08.008019+05:30TT02 (PID:7441): Waiting for all non-current ORLs to be archived2026-03-03T01:58:08.008081+05:30TT02 (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. Popular Posts Convert the Snapshot Standby Database to the Physical Standby Database Oracle Database 19c (non-cdb) Switchover using SQL*Plus Clone Oracle Grid Infrastructure Home Using Golden Images Enabling Flash Recovery Area In Oracle RAC 19c Database Restore OCR from backup in Oracle RAC 19c Categories ASM (2) DataGuard (4) News (1) Oracle Standalone Database (8) Patching (1) RAC (3)
Convert Oracle Physical Standby Database to the Snapshot Standby Database
Convert Oracle Physical Standby Database to the Snapshot Standby Database oracledna DataGuard March 2, 2026 This post demonstrates the convert the Oracle Physical Standby Database to the Snapshot Standby Database. The snapshot standby database allows write operations and developers can perform their testing activities. The Snapshot standby database will accept incoming archive redo logs, but does not apply the redo data . This redo data applied automatically after reverting the snapshot standby database to the physical standby database. 1. Configure flash recovery area in physical standby database, if not already configured. SQL> @dbNAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME———- ————— ———- —————— ————— —————- ——————PROD stdby MOUNTED PHYSICAL STANDBY stdby stdby 03-MAR-2026 00:17 SQL> show parameter db_recoveryNAME TYPE VALUE———————————— ———– ——————————db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0 SQL> alter system set db_recovery_file_dest_size=10G scope=both;System altered. SQL> alter system set db_recovery_file_dest=’/u01/arch/stdby’ scope=both;System altered. SQL> show parameter db_recovery NAME TYPE VALUE———————————— ———– ——————————db_recovery_file_dest string /u01/arch/stdbydb_recovery_file_dest_size big integer 10G 2. Stop the managed recovery (MRP), if it is active. SQL> alter database recover managed standby database cancel;Database altered. 3. Convert physical standby database to snapshot standby database. SQL>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED PHYSICAL STANDBY SQL> alter database convert to snapshot standby;Database altered. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD MOUNTED SNAPSHOT STANDBY SQL> alter database open;Database altered. SQL> select name,open_mode,database_role from v$database;NAME OPEN_MODE DATABASE_ROLE———- ———- ——————PROD READ WRITE SNAPSHOT STANDBY From Standby alert log====================alter database convert to snapshot standbyStarting background process RVWR2026-03-03T01:03:18.416253+05:30RVWR started with pid=26, OS id=165232026-03-03T01:03:19.626142+05:30Allocating 4194304 bytes in shared pool for flashback generation buffer.Allocated 4194304 bytes in shared pool for flashback generation bufferCreated guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/03/2026 01:03:18…. (PID:13893): Killing 3 processes (PIDS:3310,3382,3314) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 138932026-03-03T01:03:19.764938+05:30Process termination requested for pid 3310 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]2026-03-03T01:03:19.765139+05:30Process termination requested for pid 3382 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]2026-03-03T01:03:19.765285+05:30Process termination requested for pid 3314 [source = rdbms], [info = 2] [request issued by pid: 13893, uid: 543232]2026-03-03T01:03:21.790486+05:30…. (PID:13893): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3111]…. (PID:13893): Begin: SRL archival…. (PID:13893): End: SRL archivalRESETLOGS after incomplete recovery UNTIL CHANGE 2275208 time 03/03/2026 00:59:45Resetting resetlogs activation ID 608653432 (0x24475078)Online log /u01/app/oracle/oradata/STDBY/redo01.log: Thread 1 Group 1 was previously clearedOnline log /u01/app/oracle/oradata/STDBY/redo02.log: Thread 1 Group 2 was previously clearedOnline log /u01/app/oracle/oradata/STDBY/redo03.log: Thread 1 Group 3 was previously clearedStandby became primary SCN: 22752062026-03-03T01:03:22.088801+05:30Setting recovery target incarnation to 32026-03-03T01:03:22.343169+05:30…. (PID:13893): Redo network throttle feature is disabled at mount timeCONVERT TO SNAPSHOT STANDBY: Complete – Database mounted as snapshot standbyCompleted: alter database convert to snapshot standby 4. Perform testing. SQL> create user ram identified by ram123 default tablespace users temporary tablespace temp;User created. SQL> grant create session to ram;Grant succeeded. SQL> grant create table to ram;Grant succeeded. SQL> conn ram/ram123Connected. SQL> create table test (id number(2), name varchar(20));Table created. SQL> insert into test (id,name) values(1,’RAM’);1 row created. SQL> commit;Commit complete. SQL> select * from test; ID NAME———- ———-1 RAM 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. Popular Posts Convert the Snapshot Standby Database to the Physical Standby Database Convert Oracle Physical Standby Database to the Snapshot Standby Database Oracle Database 19c (non-cdb) Switchover using SQL*Plus Clone Oracle Grid Infrastructure Home Using Golden Images Enabling Flash Recovery Area In Oracle RAC 19c Database Categories ASM (2) DataGuard (5) News (1) Oracle Standalone Database (8) Patching (1) RAC (3)
Oracle Database 19c (non-cdb) Switchover using SQL*Plus
Oracle Database 19c (non-cdb) Switchover using SQL*Plus – Copy oracledna DataGuard March 1, 2026 This blog demonstrate the Oracle Database 19c (non-cdb) Switchover using SQL*Plus. 1. Verify redo log apply is good and there are no archve log sync gap between primary and standby databases. SQL> select thread#, max(sequence#) “Last Standby Seq Applied” from gv$archived_log val, gv$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in (‘YES’,’IN-MEMORY’) group by thread# order by 1; THREAD# Last Standby Seq Applied———- ———————— 1 67 2. Check the MRP(Managed Recovery Process) process status (it should be started, running and applying the logs). SQL> col status for a14col process for a10select process,thread#,sequence#,status,pid,delay_mins from V$managed_standby where process like ‘%MRP%’; PROCESS THREAD# SEQUENCE# STATUS PID DELAY_MINS———- ———- ———- ————– ———————— ———-MRP0 1 68 APPLYING_LOG 4676 0 3. If standby database recovery (MRP) started with delay, cancel the MRP and start the MRP with nodelay. This will minimize the duration of switchover. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY; 4. Check the datafiles & Tempfiles status All the datafiles should be online in primary and standby, incase if there are files in offline or not in online status, then restore the file and recover. So standby database datafiles are same as primary database datafiles. SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=’OFFLINE’;no rows selected The temp files should be in equal number in primary and standby databases. Run below SQL in both primary and standby databases. After comparing, add temp files accordingly. SQL> col filename for a60select tf.name filename, bytes, ts.name tablespace from v$tempfile tf, v$tablespace ts where tf.ts#=ts.ts#;SQL> FILENAME BYTES TABLESPACE———————————————————— ———- ——————————/u01/app/oracle/oradata/PROD/temp01.dbf 33554432 TEMP SQL> col filename for a60select tf.name filename, bytes, ts.name tablespace from v$tempfile tf, v$tablespace ts where tf.ts#=ts.ts#;SQL> FILENAME BYTES TABLESPACE———————————————————— ———- ——————————/u01/app/oracle/oradata/STDBY/temp01.dbf 33554432 TEMP 5. Check the Online and standby redo logfile configuration Run the below SQL for Online redo logfile status in primary database. we may get a.status in (INACTIVE,ACTIVE,CURRENT). SQL> set lines 150col member for a50select a.thread#,a.group#,a.bytes,a.blocksize,b.type,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;SQL> SQL> THREAD# GROUP# BYTES BLOCKSIZE TYPE STATUS MEMBER———- ———- ———- ———- ——- —————- —————————————1 3 209715200 512 ONLINE CURRENT /u01/app/oracle/oradata/PROD/redo03.log1 2 209715200 512 ONLINE INACTIVE /u01/app/oracle/oradata/PROD/redo02.log1 1 209715200 512 ONLINE INACTIVE /u01/app/oracle/oradata/PROD/redo01.log Also, execute above SQl in Standby database. Expected a.status from Standby is UNUSED, CLEARING or CLEARING_CURRENT, if output has different result, then manually clear redo logfiles. SQL> set lines 150col member for a50select a.thread#,a.group#,a.bytes,a.blocksize,b.type,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;SQL> SQL> THREAD# GROUP# BYTES BLOCKSIZE TYPE STATUS MEMBER———- ———- ———- ———- ——- ————– ——————————————1 3 209715200 512 ONLINE UNUSED /u01/app/oracle/oradata/STDBY/redo03.log1 2 209715200 512 ONLINE UNUSED /u01/app/oracle/oradata/STDBY/redo02.log1 1 209715200 512 ONLINE UNUSED /u01/app/oracle/oradata/STDBY/redo01.log For Standby redo logfile(SRL) status, run below SQL in standby database. The status would be in UNASSIGNED OR ACTIVE. SQL> select s.thread#,s.group#,s.status,s.bytes,l.type,l.member from v$logfile l,v$standby_log s where s.group#=l.group#; THREAD# GROUP# STATUS BYTES TYPE MEMBER———- ———- ————– ———- ——- ————————————————–1 4 UNASSIGNED 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo04.log1 5 ACTIVE 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo05.log1 6 UNASSIGNED 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo06.log0 7 UNASSIGNED 209715200 STANDBY /u01/app/oracle/oradata/STDBY/redo07.log 6. Check the alert logs. Check the alert logs of primary and physical standby databases. If any errors found, resolve them. 7. Check Archive log GAP & Redo Delay apply. Execute below SQL in primary database to get the archive log sync gap status. Expected result for GAP_STATUS is “NO GAP” and status should be “VALID”. SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2; STATUS GAP_STATUS——— ————————VALID NO GAP If result is other than above, the switchover should not be started. 8. Start the switchover. 8.a Verify the switchover by running below SQL in primary database. The expected result is “Database altered.” . If result is other than this or any errors reported in alert log, resolve them and ruin the verify again. SQL> ALTER DATABASE SWITCHOVER TO stdby VERIFY;Database altered. — 8.b If switchover verify is successful, then execute the below command in current primary database to switchover the database. SQL> ALTER DATABASE SWITCHOVER TO stdby;Database altered. — 8.c If 8.b step is successful, open new primary database in open mode. SQL> ALTER DATABASE OPEN;Database altered. NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME———- ————— ———- —————— ————— —————- ——————-PROD stdby READ WRITE PRIMARY stdby stdby 02-MAR-2026 06:22 — 8.d Mount the new standby database in mount state and start the MRP SQL> STARTUP MOUNT;ORACLE instance started.Total System Global Area 2113927120 bytesFixed Size 8898512 bytesVariable Size 486539264 bytesDatabase Buffers 1610612736 bytesRedo Buffers 7876608 bytesDatabase mounted. SQL> @db NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME———- ————— ———- —————— ————— —————- ——————-PROD prod MOUNTED PHYSICAL STANDBY prod prod 02-MAR-2026 06:32 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Database altered. 9. Post switchover, check whether archivelogs are shipped from primary and applying in standby standbydatabse or not. Run below SQL in Primary database: SQL> alter system switch logfile;System altered. SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/arch/stdbyOldest online log sequence 70Next log sequence to archive 72Current log sequence 72 Run below SQL in standby database:SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE#
Creating multitenant physical standby database in oracle 19c
Creating multitenant physical standby database in oracle 19c oracledna DataGuard May 1, 2025 This post is to demonstrate the creating multitenant physical standby for the multitenant primary database in Oracle 19c. Below are the environment details: Primary Server: prim, 192.168.56.144 DB Name: Prod PDB Name: prodpdb1 Primary db_unique_name: prod1 Standby Server: stdby, 192.168.56.145 DB Name: Prod PDB Name: prodpdb1 Standby db_unique_name: prod2 1. Enable archive log and force logging in Primary DB SQL> startup mount ORACLE instance started. Total System Global Area 2063596016 bytes Fixed Size 9136624 bytes Variable Size 469762048 bytes Database Buffers 1577058304 bytes Redo Buffers 7639040 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /patch/prod/arch Oldest online log sequence 6 Next log sequence to archive 8 Current log sequence 8 SQL> SQL> alter database force logging; Database altered. SQL> select name,force_logging,log_mode,flashback_on from v$database; NAME FORCE_LOGGING LOG_MODE FLASHBACK_ON ————– ————————————— ———— —————— PROD YES ARCHIVELOG NO 2. Create standby redo logs in Primary database. Note: Make sure to create standby redo log files of same size of online redo logs of Primary DB. Also, create one additional standby redo logs i.e number of online redo logs +1 SQL> select group#,thread#,sequence#,bytes/1024/1024 as MB,members,status from v$log; GROUP# THREAD# SEQUENCE# MB MEMBERS STATUS ———- ———- ———- ———- ———- —————- 1 1 7 200 1 INACTIVE 2 1 8 200 1 CURRENT 3 1 6 200 1 INACTIVE SQL> Set lines 1234 pages 1234 Col member for a50 select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ———- ——- ——- ————————————————– — ———- 3 ONLINE /u01/app/oracle/oradata/PROD/redo03.log NO 0 2 ONLINE /u01/app/oracle/oradata/PROD/redo02.log NO 0 1 ONLINE /u01/app/oracle/oradata/PROD/redo01.log NO 0 SQL> alter database add standby logfile group 4 (‘/u01/app/oracle/oradata/PROD/redo04.log’) size 200m; Database altered. SQL> alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/PROD/redo05.log’) size 200m; Database altered. SQL> alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/PROD/redo06.log’) size 200m; Database altered. SQL> alter database add standby logfile group 7 (‘/u01/app/oracle/oradata/PROD/redo07.log’) size 200m; Database altered. SQL> set lines 1234 pages 1234 SQL> col member for a50 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID———- ——- ——- ————————————————– — ———- 3 ONLINE /u01/app/oracle/oradata/PROD/redo03.log NO 0 2 ONLINE /u01/app/oracle/oradata/PROD/redo02.log NO 0 1 ONLINE /u01/app/oracle/oradata/PROD/redo01.log NO 0 4 STANDBY /u01/app/oracle/oradata/PROD/redo04.log NO 0 5 STANDBY /u01/app/oracle/oradata/PROD/redo05.log NO 0 6 STANDBY /u01/app/oracle/oradata/PROD/redo06.log NO 0 7 STANDBY /u01/app/oracle/oradata/PROD/redo07.log NO 07 rows selected. 3. Update the init file of primary DB below parameters SQL> alter system set db_unique_name=’PROD1′ scope=spfile; System altered. SQL> alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prod1,prod2)’ scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=/patch/prod/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod1′ scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=prod2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=prod2′ scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; System altered. SQL> alter system SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; System altered. SQL> alter system set FAL_SERVER=prod2 scope=both; System altered. SQL> alter system set FAL_CLIENT=prod1 scope=both; System altered. SQL> alter system set DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/PROD/’,’/u01/app/oracle/oradata /PROD/’,’/u01/app/oracle/oradata/PROD/pdbseed’,’/u01/app/oracle/oradata/PROD/pd bseed’,’/u01/app/oracle/oradata/PROD/prodpdb1′,’/u01/app/oracle/oradata/PROD/pro dpdb1′ scope=spfile; System altered. SQL> alter system set LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/PROD’,’/u01/app/oracle/oradat a/PROD’ scope=spfile; System altered. SQL> alter system set standby_file_management=AUTO scope=both; System altered. Bounce the Database== SQL> shut immediate SQL> startup 4. Update the TNS entries for both Primary and Standby databases. In Primary Server= Listener.ora LISTENER_PRIM = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.144)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) )
Recover the lost datafile of physical standby database using a copy of a primary database’s datafile
oracledna RAC April 20, 2025 Recover the lost datafile of physical standby database using a copy of a primary database’s datafile This post is to demonstrate how to recover lost (at OS level) datafile of physical standby database using a copy of datafile of primary database in 19c (19.3.0.0.0). Before replacing the lost datafile with the copy of Production datafile, please make sure to have the necessary archive log files which are needed for the recovery of the lost datafile. Ensure to check that source (Primary DB) datafile is corruption free. Run DBV and RMAN validate to check for corruption. On Primary Server, verify whether the source datafile is corruption free not by dbv utility, dbv must return with zero corrupted pages. dbv file= /u01/app/oracle/oradata/PROD/users01.dbf blocksize=8192 logfile=/home/oracle/dbv.log [oracle@prod ~]$ dbv file= /u01/app/oracle/oradata/PROD/users01.dbf blocksize=8192 logfile=/home/oracle/dbv.log DBVERIFY: Release 19.0.0.0.0 – Production on Sun Apr 20 17:01:02 2025 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Also, validate the datafile with RMAN RMAN> backup validate check logical datafile 7; Starting backup at 20-APR-2025 17:07:24 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=51 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/PROD/users01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN —- —— ————– ———— ————— ———- 7 OK 0 101 641 1252497 File Name: /u01/app/oracle/oradata/PROD/users01.dbf Block Type Blocks Failing Blocks Processed ———- ————– —————- Data 0 60 Index 0 15 Other 0 464 Finished backup at 20-APR-2025 17:07:26 1. Take the backup of Primary database datafile RMAN> backup datafile 7 format ‘/tmp/users_df7_prim.bkp’; Starting backup at 20-APR-2025 17:14:30 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/PROD/users01.dbf channel ORA_DISK_1: starting piece 1 at 20-APR-2025 17:14:30 channel ORA_DISK_1: finished piece 1 at 20-APR-2025 17:14:31 piece handle=/tmp/users_df7_prim.bkp tag=TAG20250420T171430 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 20-APR-2025 17:14:31 Starting Control File and SPFILE Autobackup at 20-APR-2025 17:14:31 piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-608515774-20250420-00 comment=NONE Finished Control File and SPFILE Autobackup at 20-APR-2025 17:14:33 2. Transfer the backup pieces to standby server 3. In Standby Server, catalog the backup piece RMAN> catalog backuppiece ‘/tmp/users_df7_prim.bkp’; channel default: cataloged backup piece backup piece handle=/tmp/users_df7_prim.bkp RECID=2 STAMP=1198949299 RMAN> list backuppiece ‘/tmp/users_df7_prim.bkp’; List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ——- ——- — — ———– ———– ———- 2 2 1 1 AVAILABLE DISK /tmp/users_df7_prim.bkp RMAN> list backup of datafile 7; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ——- —- — ———- ———– ———— ——————– 2 Full 2.38M DISK 00:00:00 20-APR-2025 17:14:30 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20250420T171430 Piece Name: /tmp/users_df7_prim.bkp List of Datafiles in backup set 2 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name —- — —- ———- ——————– ———– —— —- 7 Full 2287979 20-APR-2025 17:14:30 NO /u01/app/oracle/oradata/STDBY/users01.dbf 4. Stop the MRP and Standby database should be in mount state. In active data guard environment, restart the standby DB in mount state. SQL> alter database recover managed standby database cancel; 5. On Standby DB, restore the datafile from backup piece RMAN> restore datafile 7; Starting restore at 20-APR-2025 17:41:47 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=26 device type=DISK 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 /u01/app/oracle/oradata/STDBY/users01.dbf channel ORA_DISK_1: reading from backup piece /tmp/users_df7_prim.bkp channel ORA_DISK_1: piece handle=/tmp/users_df7_prim.bkp tag=TAG20250420T171430 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-APR-2025 17:41:49 6. Start the MRP on the physical standby database and check the archive sync. SQL> alter database recover managed standby database disconnect from session; Database altered. 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. Popular Posts Recover the lost datafile of physical standby database using a copy of a primary database's datafile Migrate Oracle Database from ASM to Non-ASM Manual GI/DB Patching on Oracle Restart- 19.3 to 19.18 Migrate Oracle database from Non-ASM to ASM Categories ASM (2) Patching (1) RAC (1)