Oracledna

    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           0

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

                  )

                  )


    Tnsnames.ora==
             
      PROD1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.144)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = prod1)
        )
      )

    LISTENER_PROD =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.144)(PORT = 1521))


    PROD2 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.145)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = prod2)
          (UR = A)
        )
      )


    —-

    In Standby Srever===


    Listener.ora==

    LISTENER_STDBY =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.145)(PORT = 1521))

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

        )

      )



    tnsnames.ora===

    PROD1 =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.144)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = prod1)

        )

      )


    PROD2 =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.145)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = prod2)

          (UR = A)

        )

      )


    LISTENER_STDBY =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.145)(PORT = 1521))



    —-


    Check the tnsping on both nodes===


    [oracle@prim admin]$ tnsping prod1

    TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 30-APR-2025 05:21:24

    Copyright (c) 1997, 2019, Oracle.  All rights reserved.

    Used parameter files:

    /u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.144)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod1)))

    OK (0 msec)



    [oracle@prim admin]$ tnsping prod2

    TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 30-APR-2025 05:21:27

    Copyright (c) 1997, 2019, Oracle.  All rights reserved.

    Used parameter files:

    /u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.145)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod2) (UR = A)))

    OK (0 msec)



    [oracle@stdby admin]$ tnsping prod1

    TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 30-APR-2025 05:21:09

    Copyright (c) 1997, 2019, Oracle.  All rights reserved.

    Used parameter files:

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.144)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod1)))

    OK (0 msec)



    [oracle@stdby admin]$ tnsping prod2

    TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 30-APR-2025 05:21:13

    Copyright (c) 1997, 2019, Oracle.  All rights reserved.

    Used parameter files:

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.145)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod2) (UR = A)))

    OK (0 msec)


    5. Copy the password file from Primary to Standby server.
     
    [oracle@prim ~]$ cd $ORACLE_HOME/dbs
     
    [oracle@prim dbs]$ scp orapwprod oracle@192.168.56.145:/u01/app/oracle/product/19.0.0/db_1/dbs
    oracle@192.168.56.145’s password:
    orapwprod  
                                                                                                        100% 2048   739.8KB/s   00:00

    6. Create pfile for the Physical Standby DB


    [oracle@stdby ~]$ cd $ORACLE_HOME/dbs


    vi initprod.ora


    prod.__data_transfer_cache_size=0
    prod.__db_cache_size=1476395008
    prod.__inmemory_ext_roarea=0
    prod.__inmemory_ext_rwarea=0
    prod.__java_pool_size=0
    prod.__large_pool_size=16777216
    prod.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
    prod.__pga_aggregate_target=687865856
    prod.__sga_target=2063597568
    prod.__shared_io_pool_size=100663296
    prod.__shared_pool_size=452984832
    prod.__streams_pool_size=0
    prod.__unified_pga_pool_size=0
    *.audit_file_dest=’/u01/app/oracle/admin/prod/adump’
    *.audit_trail=’db’
    *.compatible=’19.0.0′
    *.control_files=’/u01/app/oracle/oradata/PROD/control01.ctl’,’/u01/app/oracle/oradata/PROD/control02.ctl’
    *.db_block_size=8192
    *.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/pdbseed’,’/u01/app/oracle/oradata/PROD/prodpdb1′,’/u01/app/oracle/oradata/PROD/prodpdb1′
    *.db_name=’prod’
    *.db_unique_name=’PROD2′
    *.diagnostic_dest=’/u01/app/oracle’
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)’
    *.enable_pluggable_database=true
    *.fal_client=’PROD2′
    *.fal_server=’PROD1′
    *.local_listener=’LISTENER_STDBY’
    *.log_archive_config=’DG_CONFIG=(prod1,prod2)’
    *.log_archive_dest_1=’LOCATION=/patch/prod/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod2′
    *.log_archive_dest_2=’SERVICE=prod1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=prod1′
    *.log_archive_dest_state_1=’ENABLE’
    *.log_archive_dest_state_2=’ENABLE’
    *.log_archive_format=’%t_%s_%r.arc’
    *.log_file_name_convert=’/u01/app/oracle/oradata/PROD’,’/u01/app/oracle/oradata/PROD’
    *.nls_language=’AMERICAN’
    *.nls_territory=’INDIA’
    *.open_cursors=300
    *.pga_aggregate_target=654m
    *.processes=300
    *.remote_login_passwordfile=’EXCLUSIVE’
    *.sga_target=1962m
    *.undo_tablespace=’UNDOTBS1′
    *.standby_file_management=’AUTO’


    :wq!

    7. Create necessary directories in standby server


    mkdir -p /u01/app/oracle/admin/prod/adump
    mkdir -p /u01/app/oracle/oradata/PROD
    mkdir -p /u01/app/oracle/oradata/PROD
    mkdir -p /u01/app/oracle/oradata/PROD/pdbseed
    mkdir -p /patch/prod/arch

    8. Start the physical standby database using pfile.


    [oracle@stdby ~]$ sqlplus / as sysdba
    SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 30 05:50:52 2025
    Version 19.3.0.0.0
    Copyright (c) 1982, 2019, Oracle. All rights reserved.
    Connected to an idle instance.


    SQL> startup nomount pfile=/u01/app/oracle/product/19.0.0/db_1/dbs/initprod.ora
    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
    SQL>


    Check the communication between both servers by sqlplus command ==


    [oracle@prim ~]$ sqlplus sys/manager@prod1 as sysdba
    SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 30 05:54:14 2025
    Version 19.3.0.0.0
    Copyright (c) 1982, 2019, Oracle. All rights reserved.
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0



    [oracle@prim ~]$ sqlplus sys/manager@prod2 as sysdba
    SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 30 05:54:21 2025
    Version 19.3.0.0.0
    Copyright (c) 1982, 2019, Oracle. All rights reserved.
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0



    [oracle@stdby ~]$ sqlplus sys/manager@prod1 as sysdba
    SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 30 05:54:34 2025
    Version 19.3.0.0.0
    Copyright (c) 1982, 2019, Oracle. All rights reserved.
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0



    [oracle@stdby ~]$ sqlplus sys/manager@prod2 as sysdba
    SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 30 05:56:04 2025
    Version 19.3.0.0.0
    Copyright (c) 1982, 2019, Oracle. All rights reserved.
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0

    10. Invoke the RMAN in standby server to start building the physical standby by RMAN active duplicate method i.e., over the network


    [oracle@stdby ~]$ rman target sys/manager@prod1 auxiliary sys/manager@prod2
    Recovery Manager: Release 19.0.0.0.0 – Production on Wed Apr 30 05:59:56 2025
    Version 19.3.0.0.0
    Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
    connected to target database: PROD (DBID=611435848)
    connected to auxiliary database: PROD (not mounted)



    RMAN> run

    {

    allocate channel prmy1 type disk;

    allocate channel prmy2 type disk;

    allocate channel prmy3 type disk;

    allocate channel prmy4 type disk;

    allocate auxiliary channel stby type disk;

    duplicate target database for standby from active database nofilenamecheck;

    }2> 3> 4> 5> 6> 7> 8> 9>


    using target database control file instead of recovery catalog

    allocated channel: prmy1

    channel prmy1: SID=73 device type=DISK


    allocated channel: prmy2

    channel prmy2: SID=82 device type=DISK


    allocated channel: prmy3

    channel prmy3: SID=80 device type=DISK


    allocated channel: prmy4

    channel prmy4: SID=92 device type=DISK


    allocated channel: stby

    channel stby: SID=38 device type=DISK


    Starting Duplicate Db at 30-APR-2025 06:13:01


    contents of Memory Script:

    {

       backup as copy reuse

       passwordfile auxiliary format  ‘/u01/app/oracle/product/19.0.0/db_1/dbs/orapwprod’   ;

    }

    executing Memory Script


    Starting backup at 30-APR-2025 06:13:01

    Finished backup at 30-APR-2025 06:13:02


    contents of Memory Script:

    {

       backup as copy current controlfile for standby auxiliary format  ‘/u01/app/oracle/oradata/PROD/control01.ctl’;

       restore clone primary controlfile to  ‘/u01/app/oracle/oradata/PROD/control02.ctl’ from

     ‘/u01/app/oracle/oradata/PROD/control01.ctl’;

    }

    executing Memory Script


    Starting backup at 30-APR-2025 06:13:02

    channel prmy1: starting datafile copy

    copying standby control file

    output file name=/u01/app/oracle/product/19.0.0/db_1/dbs/snapcf_prod.f tag=TAG20250430T061302

    channel prmy1: datafile copy complete, elapsed time: 00:00:01

    Finished backup at 30-APR-2025 06:13:04


    Starting restore at 30-APR-2025 06:13:04


    channel stby: copied control file copy

    Finished restore at 30-APR-2025 06:13:05


    contents of Memory Script:

    {

       sql clone ‘alter database mount standby database’;

    }

    executing Memory Script


    sql statement: alter database mount standby database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/system01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/sysaux01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/undotbs01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/pdbseed/system01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/users01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/prodpdb1/system01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/prodpdb1/sysaux01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/prodpdb1/undotbs01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/PROD/prodpdb1/users01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/PROD/temp01.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/PROD/pdbseed/temp012025-04-28_05-41-16-184-AM.dbf conflicts with a file used by the target database

    RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/PROD/prodpdb1/temp01.dbf conflicts with a file used by the target database


    contents of Memory Script:

    {

       set newname for tempfile  1 to

     “/u01/app/oracle/oradata/PROD/temp01.dbf”;

       set newname for tempfile  2 to

     “/u01/app/oracle/oradata/PROD/pdbseed/temp012025-04-28_05-41-16-184-AM.dbf”;

       set newname for tempfile  3 to

     “/u01/app/oracle/oradata/PROD/prodpdb1/temp01.dbf”;

       switch clone tempfile all;

       set newname for datafile  1 to

     “/u01/app/oracle/oradata/PROD/system01.dbf”;

       set newname for datafile  3 to

     “/u01/app/oracle/oradata/PROD/sysaux01.dbf”;

       set newname for datafile  4 to

     “/u01/app/oracle/oradata/PROD/undotbs01.dbf”;

       set newname for datafile  5 to

     “/u01/app/oracle/oradata/PROD/pdbseed/system01.dbf”;

       set newname for datafile  6 to

     “/u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf”;

       set newname for datafile  7 to

     “/u01/app/oracle/oradata/PROD/users01.dbf”;

       set newname for datafile  8 to

     “/u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf”;

       set newname for datafile  9 to

     “/u01/app/oracle/oradata/PROD/prodpdb1/system01.dbf”;

       set newname for datafile  10 to

     “/u01/app/oracle/oradata/PROD/prodpdb1/sysaux01.dbf”;

       set newname for datafile  11 to

     “/u01/app/oracle/oradata/PROD/prodpdb1/undotbs01.dbf”;

       set newname for datafile  12 to

     “/u01/app/oracle/oradata/PROD/prodpdb1/users01.dbf”;

       backup as copy reuse

       datafile  1 auxiliary format

     “/u01/app/oracle/oradata/PROD/system01.dbf”   datafile

     3 auxiliary format

     “/u01/app/oracle/oradata/PROD/sysaux01.dbf”   datafile

     4 auxiliary format

     “/u01/app/oracle/oradata/PROD/undotbs01.dbf”   datafile

     5 auxiliary format

     “/u01/app/oracle/oradata/PROD/pdbseed/system01.dbf”   datafile

     6 auxiliary format

     “/u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf”   datafile

     7 auxiliary format

     “/u01/app/oracle/oradata/PROD/users01.dbf”   datafile

     8 auxiliary format

     “/u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf”   datafile

     9 auxiliary format

     “/u01/app/oracle/oradata/PROD/prodpdb1/system01.dbf”   datafile

     10 auxiliary format

     “/u01/app/oracle/oradata/PROD/prodpdb1/sysaux01.dbf”   datafile

     11 auxiliary format

     “/u01/app/oracle/oradata/PROD/prodpdb1/undotbs01.dbf”   datafile

     12 auxiliary format

     “/u01/app/oracle/oradata/PROD/prodpdb1/users01.dbf”   ;

       sql ‘alter system archive log current’;

    }

    executing Memory Script


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    renamed tempfile 1 to /u01/app/oracle/oradata/PROD/temp01.dbf in control file

    renamed tempfile 2 to /u01/app/oracle/oradata/PROD/pdbseed/temp012025-04-28_05-41-16-184-AM.dbf in control file

    renamed tempfile 3 to /u01/app/oracle/oradata/PROD/prodpdb1/temp01.dbf in control file


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    executing command: SET NEWNAME


    Starting backup at 30-APR-2025 06:13:10

    channel prmy1: starting datafile copy

    input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/system01.dbf

    channel prmy2: starting datafile copy

    input datafile file number=00003 name=/u01/app/oracle/oradata/PROD/sysaux01.dbf

    channel prmy3: starting datafile copy

    input datafile file number=00010 name=/u01/app/oracle/oradata/PROD/prodpdb1/sysaux01.dbf

    channel prmy4: starting datafile copy

    input datafile file number=00006 name=/u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf

    output file name=/u01/app/oracle/oradata/PROD/system01.dbf tag=TAG20250430T061310

    channel prmy1: datafile copy complete, elapsed time: 00:00:16

    channel prmy1: starting datafile copy

    input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/undotbs01.dbf

    output file name=/u01/app/oracle/oradata/PROD/sysaux01.dbf tag=TAG20250430T061310

    channel prmy2: datafile copy complete, elapsed time: 00:00:16

    channel prmy2: starting datafile copy

    input datafile file number=00005 name=/u01/app/oracle/oradata/PROD/pdbseed/system01.dbf

    output file name=/u01/app/oracle/oradata/PROD/prodpdb1/sysaux01.dbf tag=TAG20250430T061310

    channel prmy3: datafile copy complete, elapsed time: 00:00:16

    channel prmy3: starting datafile copy

    input datafile file number=00009 name=/u01/app/oracle/oradata/PROD/prodpdb1/system01.dbf

    output file name=/u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf tag=TAG20250430T061310

    channel prmy4: datafile copy complete, elapsed time: 00:00:16

    channel prmy4: starting datafile copy

    input datafile file number=00011 name=/u01/app/oracle/oradata/PROD/prodpdb1/undotbs01.dbf

    output file name=/u01/app/oracle/oradata/PROD/undotbs01.dbf tag=TAG20250430T061310

    channel prmy1: datafile copy complete, elapsed time: 00:00:09

    channel prmy1: starting datafile copy

    input datafile file number=00008 name=/u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf

    output file name=/u01/app/oracle/oradata/PROD/pdbseed/system01.dbf tag=TAG20250430T061310

    channel prmy2: datafile copy complete, elapsed time: 00:00:10

    channel prmy2: starting datafile copy

    input datafile file number=00007 name=/u01/app/oracle/oradata/PROD/users01.dbf

    output file name=/u01/app/oracle/oradata/PROD/prodpdb1/system01.dbf tag=TAG20250430T061310

    channel prmy3: datafile copy complete, elapsed time: 00:00:09

    channel prmy3: starting datafile copy

    input datafile file number=00012 name=/u01/app/oracle/oradata/PROD/prodpdb1/users01.dbf

    output file name=/u01/app/oracle/oradata/PROD/prodpdb1/undotbs01.dbf tag=TAG20250430T061310

    channel prmy4: datafile copy complete, elapsed time: 00:00:09

    output file name=/u01/app/oracle/oradata/PROD/users01.dbf tag=TAG20250430T061310

    channel prmy2: datafile copy complete, elapsed time: 00:00:01

    output file name=/u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf tag=TAG20250430T061310

    channel prmy1: datafile copy complete, elapsed time: 00:00:02

    output file name=/u01/app/oracle/oradata/PROD/prodpdb1/users01.dbf tag=TAG20250430T061310

    channel prmy3: datafile copy complete, elapsed time: 00:00:01

    Finished backup at 30-APR-2025 06:13:39


    sql statement: alter system archive log current

    contents of Memory Script:

    {

       switch clone datafile all;

    }

    executing Memory Script

    datafile 1 switched to datafile copy

    input datafile copy RECID=4 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/system01.dbf

    datafile 3 switched to datafile copy

    input datafile copy RECID=5 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/sysaux01.dbf

    datafile 4 switched to datafile copy

    input datafile copy RECID=6 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/undotbs01.dbf

    datafile 5 switched to datafile copy

    input datafile copy RECID=7 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/pdbseed/system01.dbf

    datafile 6 switched to datafile copy

    input datafile copy RECID=8 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/pdbseed/sysaux01.dbf

    datafile 7 switched to datafile copy

    input datafile copy RECID=9 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/users01.dbf

    datafile 8 switched to datafile copy

    input datafile copy RECID=10 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/pdbseed/undotbs01.dbf

    datafile 9 switched to datafile copy

    input datafile copy RECID=11 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/prodpdb1/system01.dbf

    datafile 10 switched to datafile copy

    input datafile copy RECID=12 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/prodpdb1/sysaux01.dbf

    datafile 11 switched to datafile copy

    input datafile copy RECID=13 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/prodpdb1/undotbs01.dbf

    datafile 12 switched to datafile copy

    input datafile copy RECID=14 STAMP=1199772819 file name=/u01/app/oracle/oradata/PROD/prodpdb1/users01.dbf

    RMAN-05535: warning: All redo log files were not defined properly.

    RMAN-05535: warning: All redo log files were not defined properly.

    RMAN-05535: warning: All redo log files were not defined properly.

    RMAN-05535: warning: All redo log files were not defined properly.

    RMAN-05535: warning: All redo log files were not defined properly.

    RMAN-05535: warning: All redo log files were not defined properly.

    RMAN-05535: warning: All redo log files were not defined properly.

    Finished Duplicate Db at 30-APR-2025 06:13:40

    released channel: prmy1

    released channel: prmy2

    released channel: prmy3

    released channel: prmy4

    released channel: stby

    11. Start the recovery in newly created physical standby database.


    NAME       DB_UNIQUE_NAME  OPEN_MODE  DATABASE_ROLE      INSTANCE_NAME   HOST_NAME        STARTUP_TIME

    ———- ————— ———- —————— ————— —————- ——————–

    PROD       PROD2           MOUNTED    PHYSICAL STANDBY   prod            stdby            30-APR-2025 07:20

    NAME           OPEN_MODE          CON_ID       DBID    CON_UID GUID

    ————– ————– ———- ———- ———- ——————————–

    CDB$ROOT       MOUNTED                 1  611435848          1 86B637B62FDF7A65E053F706E80A27CA

    PDB$SEED       MOUNTED                 2 1551427537 1551427537 33CC85C9D7F617DEE0652045D1650C47

    PRODPDB1       MOUNTED                 3 1750973443 1750973443 33CCBBDAD9DA21EFE0652045D1650C47



    SQL> alter database recover managed standby database disconnect from session;

    Database altered.

     
    Check the archive sync==


    SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
    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#)) APPL
    WHERE ARCH.THREAD# = APPL.THREAD#;

        Thread Last Sequence Received Last Sequence Applied Difference
        ———— ———————- ——————— ————-
             1                         19                                  19                               0

    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
    Categories