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))               )    

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)