Netsoftmate

Tag: ORA-19602

  • Rman Duplicate from noarchivelog mode target database

    Overview
    Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies.
    In this scenario we are performing active duplication and source database is in no archive log mode.
    When you issue the command to duplicate database from target database where source Database is running in noarchivelog mode, you will receive following error.
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode     
     On this situation we have one solution put the target database in mount stage and perform the steps.

    Target (source) details:
    Database Name
    dhstg
    Hostname
    Nsmdev01
    Ip Address
    172.16.110.18
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data1/dhstg
    Backup Location
    /backup/rman_backup
    Tns details:
    DHSTG =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = Nsmdev01.corp.netsoftmate.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = dhstg.corp.netsoftmate.com)
        )
      )
    Destination details:
    Database Name
    dhstg
    Hostname
    Nsmstg01
    Ip Address
    172.16.110.16
    OS
    Linux
    Version
    x86_64
    Datafile Location
    /data1/diff/dhstg
    Backup Location
    /backup/rman_backup
    Tns details:
    dhstg_dup =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = dhstg_dup)
        )
      )

    Pre-requisites:

    • Oracle net configuration:
    • Both target and destination server should have tns entries.
    • Static listener registration on auxiliary site.
    • Service should be register on aux listener.
    • Password files from target database.
    • Sqlnet.ora should have correct parameters.
    • Target database should be running through spfile.
    • Check connectivity between target and destination server.
    • Target database instance should be in mount mode.
    Steps on target (source) server:
    1. Put tns entry of auxiliary database into target $ORACLE_HOME/network/admin directory
    [oracle@Nsmdev01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin
    [oracle@Nsmdev01 admin]$ vi tnsnames.ora
    dhstg_dup =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = dhstg_dup)
        )
      )
    [[oracle@Nsmdev01 admin]$ tnsping dhstg_dup
    TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:34:39
    Copyright (c) 1997, 2009, Oracle.  All rights reserved.
    Used parameter files:
    /data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dhstg_dup)))
    OK (0 msec)
    2. Check database whether it is running in archive log mode.
    SQL> select name,open_mode,log_mode from v$database;
    NAME      OPEN_MODE            LOG_MODE
    ——— ——————– ————
    DHSTG    READ WRITE           NOARCHIVELOG
    3. Check database is running from spfile. While duplicating database with different directory structure target database must be running with spfile.
    SQL> select value from v$parameter where name=’spfile’;
    VALUE
    ——————————————————————————–
    /data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora
    SQL> show parameter pfile
    NAME                                 TYPE        VALUE
    ———————————— ———– ——————————
    spfile                               string      /data2/app/oracle/product/11.2                                                 .0/dbs/spfiledhstg.ora
    4. Create pfile from spfile
    SQL> create pfile=’/backup/rman_backup/initdhstg.ora’ from spfile;
    File created.
    5. Create password file.
    [oracle@Nsmdev01 dbs]$ orapwd file=orapwdhstg password=oracle
    6. Copy the password file and init file to destination $ORACLE_HOME/dbs directory using scp or ftp
    [oracle@Nsmdev01 dbs]$ scp orapwdhstg oracle@172.16.110.16:/data2/app/oracle/product/11.2.0/dbs/
    oracle@172.16.110.16’s password:
    orapwdhstg                                                                                                                                                        100% 1536     1.5KB/s   00:00
    [oracle@Nsmdev01 rman_backup]$ pwd
    /backup/rman_backup
    [oracle@Nsmdev01 rman_backup]$ scp initdhstg.ora oracle@172.16.110.16:/data2/app/oracle/product/11.2.0/dbs/
    oracle@172.16.110.16’s password:
    initdhstg.ora                                                                                                                                                     100%  861     0.8KB/s   00:00

    7. Shutdown and start the instance in mount stage.
    SQL> select name,open_mode from v$database;
    NAME      OPEN_MODE
    ——— ——————–
    DHSTG    READ WRITE
    SQL> shut immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    Total System Global Area 1068937216 bytes
    Fixed Size                  2220200 bytes
    Variable Size             838864728 bytes
    Database Buffers          222298112 bytes
    Redo Buffers                5554176 bytes
    Database mounted.
    SQL> select name,open_mode from v$database;
    NAME      OPEN_MODE
    ——— ——————–
    DHSTG    MOUNTED
    Steps on destination server:
    8. Put static listener entry into listener.ora file in $ORACLE_HOME/netwrok/admin directory.
    oracle@Nsmstg01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin
    [oracle@Nsmstg01 admin]$
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = dhstg_dup)
          (ORACLE_HOME = /data2/app/oracle/product/11.2.0)
          (SID_NAME = dhstg)
        )
      )
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
        )
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
     Here global_name is nothing but service name and sid_name is instance name
    now reload the listener and check the services
    [oracle@Nsmstg01 admin]$ lsnrctl
    LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:13:54
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    Welcome to LSNRCTL, type “help” for information.
    [oracle@Nsmstg01 admin]$ lsnrctl
    LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:43:43
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    Welcome to LSNRCTL, type “help” for information.
    LSNRCTL> reload
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521)))
    The command completed successfully
    LSNRCTL> services
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521)))
    Services Summary…
    Service “aux.corp.netsoftmate.com” has 1 instance(s).
      Instance “aux”, status READY, has 1 handler(s) for this service…
        Handler(s):
          “DEDICATED” established:0 refused:0 state:ready
             LOCAL SERVER
    Service “auxXDB.corp.netsoftmate.com” has 1 instance(s).
      Instance “aux”, status READY, has 1 handler(s) for this service…
        Handler(s):
          “D000” established:0 refused:0 current:0 max:1022 state:ready
             DISPATCHER <machine: Nsmstg01, pid: 4392>
             (ADDRESS=(PROTOCOL=tcp)(HOST=Nsmstg01)(PORT=38403))
    Service “dhstg.corp.netsoftmate.com” has 1 instance(s).
      Instance “dhstg”, status BLOCKED, has 1 handler(s) for this service…
        Handler(s):
          “DEDICATED” established:0 refused:0 state:ready
             LOCAL SERVER
    Service “dhstg_dup” has 1 instance(s).
      Instance “dhstg”, status UNKNOWN, has 1 handler(s) for this service…
        Handler(s):
          “DEDICATED” established:0 refused:0
             LOCAL SERVER
    Service “dhstg_dup.corp.netsoftmate.com” has 1 instance(s).
      Instance “dhstg”, status BLOCKED, has 1 handler(s) for this service…
        Handler(s):
          “DEDICATED” established:0 refused:0 state:ready
             LOCAL SERVER
    The command completed successfully

    9. put Tns entry for target(source) database in $ORACLE_HOME/network/admin directory
    [oracle@Nsmstg01 admin]$ vi tnsnames.ora
    DHSTG =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = dhstg.corp.netsoftmate.com)
        )
      )
    [oracle@Nsmstg01 admin]$ tnsping DHSTG
    TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:15:52
    Copyright (c) 1997, 2009, Oracle.  All rights reserved.
    Used parameter files:
    /data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dhstg.corp.netsoftmate.com)))
    OK (0 msec)

    10. Create the directory structure different from target (source) server.
    [oracle@Nsmstg01 dhstg]$ mkdir -p /data1/diff/dhstg

    11. Set the oracle sid and start the instance in nomount stage with pfile, here start the instance with pfile is must or else you will get error.
    RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance
    is started with spfile cannot use SPFILE clause
    [oracle@Nsmstg01 dbs]$ export ORACLE_SID=DHSTG
    [oracle@Nsmstg01 dbs]$ sqlplus
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 6 12:22:14 2015
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    Enter user-name: sys as sysdba
    Enter password:
    Connected to an idle instance.
    SQL> startup nomount pfile=’/data2/app/oracle/product/11.2.0/dbs/initdhstg.ora’;
    ORACLE instance started.
    Total System Global Area 1068937216 bytes
    Fixed Size                  2220200 bytes
    Variable Size             838864728 bytes
    Database Buffers          222298112 bytes
    Redo Buffers                5554176 bytes
    SQL>
    12. Now connect to Rman by issuing following command
    [oracle@Nsmstg01 admin]$ rman target sys/oracle@dhstg auxiliary sys/oracle@dhstg_dup
    Recovery Manager: Release 11.2.0.1.0 – Production on Mon Apr 6 12:58:08 2015
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    connected to target database: DHSTG (DBID=112149522, not open)
    connected to auxiliary database: DHSTG (not mounted)
    RMAN>
    13. Issue the command to duplicate the database with same name but different directory structure from noarchivelog mode target DB.
    RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE
    2> spfile
    3> parameter_value_convert
    4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    5> set db_file_name_convert
    6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    7> set log_file_name_convert
    8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    9> nofilenamecheck;
    Starting Duplicate Db at 06-APR-15
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=63 device type=DISK
    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  ‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’ auxiliary format
    ‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’   ;
       sql clone “alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora””;
    }
    executing Memory Script
    Starting backup at 06-APR-15
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=129 device type=DISK
    Finished backup at 06-APR-15
    sql statement: alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora”
    contents of Memory Script:
    {
       sql clone “alter system set  db_name =
    ”DHSTG” comment=
    ”duplicate” scope=spfile”;
       sql clone “alter system set  control_files =
    ”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment=
    ”” scope=spfile”;
       sql clone “alter system set  db_file_name_convert =
    ”/data1/dhstg”, ”/data1/diff/dhstg” comment=
    ”” scope=spfile”;
       sql clone “alter system set  log_file_name_convert =
    ”/data1/dhstg”, ”/data1/diff/dhstg” comment=
    ”” scope=spfile”;
       shutdown clone immediate;
       startup clone nomount;
    }
    executing Memory Script
    sql statement: alter system set  db_name =  ”DHSTG” comment= ”duplicate” scope=spfile
    sql statement: alter system set  control_files =  ”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment= ”” scope=spfile
    sql statement: alter system set  db_file_name_convert =  ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile
    sql statement: alter system set  log_file_name_convert =  ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile
    Oracle instance shut down
    connected to auxiliary database (not started)
    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                     2220200 bytes
    Variable Size                838864728 bytes
    Database Buffers             222298112 bytes
    Redo Buffers                   5554176 bytes
    contents of Memory Script:
    {
       sql clone “alter system set  db_name =
    ”DHSTG” comment=
    ”Modified by RMAN duplicate” scope=spfile”;
       sql clone “alter system set  db_unique_name =
    ”DHSTG” comment=
    ”Modified by RMAN duplicate” scope=spfile”;
       shutdown clone immediate;
       startup clone force nomount
       backup as copy current controlfile auxiliary format  ‘/data1/diff/dhstg/control01.ctl’;
       restore clone controlfile to  ‘/data1/diff/dhstg/control02.ctl’ from
    ‘/data1/diff/dhstg/control01.ctl’;
       alter clone database mount;
    }
    executing Memory Script
    sql statement: alter system set  db_name =  ”DHSTG” comment= ”Modified by RMAN duplicate” scope=spfile
    sql statement: alter system set  db_unique_name =  ”DHSTG” comment= ”Modified by RMAN duplicate” scope=spfile
    Oracle instance shut down
    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                     2220200 bytes
    Variable Size                838864728 bytes
    Database Buffers             222298112 bytes
    Redo Buffers                   5554176 bytes
    Starting backup at 06-APR-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    copying current control file
    output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_dhstg.f tag=TAG20150406T125217 RECID=2 STAMP=876315139
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    Finished backup at 06-APR-15
    Starting restore at 06-APR-15
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=63 device type=DISK
    channel ORA_AUX_DISK_1: copied control file copy
    Finished restore at 06-APR-15
    database mounted
    contents of Memory Script:
    {
       set newname for datafile  1 to
    “/data1/diff/dhstg/system01.dbf”;
       set newname for datafile  2 to
    “/data1/diff/dhstg/sysaux01.dbf”;
       set newname for datafile  3 to
    “/data1/diff/dhstg/undotbs01.dbf”;
       set newname for datafile  4 to
    “/data1/diff/dhstg/users01.dbf”;
       set newname for datafile  5 to
    “/data1/diff/dhstg/example01.dbf”;
       set newname for datafile  6 to
    “/data1/diff/dhstg/data01.dbf”;
       backup as copy reuse
       datafile  1 auxiliary format
    “/data1/diff/dhstg/system01.dbf”   datafile
    2 auxiliary format
    “/data1/diff/dhstg/sysaux01.dbf”   datafile
    3 auxiliary format
    “/data1/diff/dhstg/undotbs01.dbf”   datafile
    4 auxiliary format
    “/data1/diff/dhstg/users01.dbf”   datafile
    5 auxiliary format
    “/data1/diff/dhstg/example01.dbf”   datafile
    6 auxiliary format
    “/data1/diff/dhstg/data01.dbf”   ;
    }
    executing Memory Script
    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 06-APR-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00001 name=/data1/dhstg/system01.dbf
    output file name=/data1/diff/dhstg/system01.dbf tag=TAG20150406T125229
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00002 name=/data1/dhstg/sysaux01.dbf
    output file name=/data1/diff/dhstg/sysaux01.dbf tag=TAG20150406T125229
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00003 name=/data1/dhstg/undotbs01.dbf
    output file name=/data1/diff/dhstg/undotbs01.dbf tag=TAG20150406T125229
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00006 name=/data1/dhstg/data01.dbf
    output file name=/data1/diff/dhstg/data01.dbf tag=TAG20150406T125229
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00005 name=/data1/dhstg/example01.dbf
    output file name=/data1/diff/dhstg/example01.dbf tag=TAG20150406T125229
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting datafile copy
    input datafile file number=00004 name=/data1/dhstg/users01.dbf
    output file name=/data1/diff/dhstg/users01.dbf tag=TAG20150406T125229
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 06-APR-15
    contents of Memory Script:
    {
       switch clone datafile all;
    }
    executing Memory Script
    datafile 1 switched to datafile copy
    input datafile copy RECID=2 STAMP=876315893 file name=/data1/diff/dhstg/system01.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=3 STAMP=876315894 file name=/data1/diff/dhstg/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=4 STAMP=876315894 file name=/data1/diff/dhstg/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=5 STAMP=876315894 file name=/data1/diff/dhstg/users01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=6 STAMP=876315895 file name=/data1/diff/dhstg/example01.dbf
    datafile 6 switched to datafile copy
    input datafile copy RECID=7 STAMP=876315895 file name=/data1/diff/dhstg/data01.dbf
    contents of Memory Script:
    {
       recover
       clone database
       noredo
        delete archivelog
       ;
    }
    executing Memory Script
    Starting recover at 06-APR-15
    using channel ORA_AUX_DISK_1
    Finished recover at 06-APR-15
    contents of Memory Script:
    {
       shutdown clone immediate;
       startup clone nomount;
       sql clone “alter system set  db_name =
    ”DHSTG” comment=
    ”Reset to original value by RMAN” scope=spfile”;
       sql clone “alter system reset  db_unique_name scope=spfile”;
       shutdown clone immediate;
       startup clone nomount;
    }
    executing Memory Script
    database dismounted
    Oracle instance shut down
    connected to auxiliary database (not started)
    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                     2220200 bytes
    Variable Size                838864728 bytes
    Database Buffers             222298112 bytes
    Redo Buffers                   5554176 bytes
    sql statement: alter system set  db_name =  ”DHSTG” comment= ”Reset to original value by RMAN” scope=spfile
    sql statement: alter system reset  db_unique_name scope=spfile
    Oracle instance shut down
    connected to auxiliary database (not started)
    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                     2220200 bytes
    Variable Size                838864728 bytes
    Database Buffers             222298112 bytes
    Redo Buffers                   5554176 bytes
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE “DHSTG” RESETLOGS NOARCHIVELOG
      MAXLOGFILES     16
      MAXLOGMEMBERS      3
      MAXDATAFILES      100
      MAXINSTANCES     8
      MAXLOGHISTORY      292
    LOGFILE
      GROUP  1 ( ‘/data1/diff/dhstg/redo01.log’ ) SIZE 50 M  REUSE,
      GROUP  2 ( ‘/data1/diff/dhstg/redo02.log’ ) SIZE 50 M  REUSE,
      GROUP  3 ( ‘/data1/diff/dhstg/redo03.log’ ) SIZE 50 M  REUSE
    DATAFILE
      ‘/data1/diff/dhstg/system01.dbf’
    CHARACTER SET AL32UTF8
    contents of Memory Script:
    {
       set newname for tempfile  1 to
    “/data1/diff/dhstg/temp01.dbf”;
       switch clone tempfile all;
       catalog clone datafilecopy  “/data1/diff/dhstg/sysaux01.dbf”,
    “/data1/diff/dhstg/undotbs01.dbf”,
    “/data1/diff/dhstg/users01.dbf”,
    “/data1/diff/dhstg/example01.dbf”,
    “/data1/diff/dhstg/data01.dbf”;
       switch clone datafile all;
    }
    executing Memory Script
    executing command: SET NEWNAME
    renamed tempfile 1 to /data1/diff/dhstg/temp01.dbf in control file
    cataloged datafile copy
    datafile copy file name=/data1/diff/dhstg/sysaux01.dbf RECID=1 STAMP=876315921
    cataloged datafile copy
    datafile copy file name=/data1/diff/dhstg/undotbs01.dbf RECID=2 STAMP=876315921
    cataloged datafile copy
    datafile copy file name=/data1/diff/dhstg/users01.dbf RECID=3 STAMP=876315922
    cataloged datafile copy
    datafile copy file name=/data1/diff/dhstg/example01.dbf RECID=4 STAMP=876315922
    cataloged datafile copy
    datafile copy file name=/data1/diff/dhstg/data01.dbf RECID=5 STAMP=876315922
    datafile 2 switched to datafile copy
    input datafile copy RECID=1 STAMP=876315921 file name=/data1/diff/dhstg/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=2 STAMP=876315921 file name=/data1/diff/dhstg/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=3 STAMP=876315922 file name=/data1/diff/dhstg/users01.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=4 STAMP=876315922 file name=/data1/diff/dhstg/example01.dbf
    datafile 6 switched to datafile copy
    input datafile copy RECID=5 STAMP=876315922 file name=/data1/diff/dhstg/data01.dbf
    contents of Memory Script:
    {
       Alter clone database open resetlogs;
    }
    executing Memory Script
    database opened
    Finished Duplicate Db at 06-APR-15
    14. Once database replicated open the target database.
    SQL> alter database open;
    Database altered.
    SQL> select name,open_mode from v$database;
    NAME      OPEN_MODE
    ——— ——————–
    DHSTG    READ WRITE
    Issues:
    • When you replicate database where target database is in noarchivelog mode. You will receive following error.
    RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE
    2> SPFILE
    3> PARAMETER_VALUE_CONVERT
    4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    5> SET DB_FILE_NAME_CONVERT
    6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    7> SET LOG_FILE_NAME_CONVERT
    8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    9> NOFILENAMECHECK;
    Starting Duplicate Db at 06-APR-15
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=63 device type=DISK
    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  ‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’ auxiliary format
    ‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’   ;
       sql clone “alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora””;
    }
    executing Memory Script
    Starting backup at 06-APR-15
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=129 device type=DISK
    Finished backup at 06-APR-15
    sql statement: alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora”
    contents of Memory Script:
    {
       sql clone “alter system set  db_name =
    ”DHSTG” comment=
    ”duplicate” scope=spfile”;
       sql clone “alter system set  control_files =
    ”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment=
    ”” scope=spfile”;
       sql clone “alter system set  db_file_name_convert =
    ”/data1/dhstg”, ”/data1/diff/dhstg” comment=
    ”” scope=spfile”;
       sql clone “alter system set  LOG_FILE_NAME_CONVERT =
    ”/data1/dhstg”, ”/data1/diff/dhstg” comment=
    ”” scope=spfile”;
       shutdown clone immediate;
       startup clone nomount;
    }
    executing Memory Script
    sql statement: alter system set  db_name =  ”DHSTG” comment= ”duplicate” scope=spfile
    sql statement: alter system set  control_files =  ”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment= ”” scope=spfile
    sql statement: alter system set  db_file_name_convert =  ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile
    sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile
    Oracle instance shut down
    connected to auxiliary database (not started)
    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                     2220200 bytes
    Variable Size                838864728 bytes
    Database Buffers             222298112 bytes
    Redo Buffers                   5554176 bytes
    contents of Memory Script:
    {
       sql clone “alter system set  db_name =
    ”DHSTG” comment=
    ”Modified by RMAN duplicate” scope=spfile”;
       sql clone “alter system set  db_unique_name =
    ”DHSTG” comment=
    ”Modified by RMAN duplicate” scope=spfile”;
       shutdown clone immediate;
       startup clone force nomount
       backup as copy current controlfile auxiliary format  ‘/data1/diff/dhstg/control01.ctl’;
       restore clone controlfile to  ‘/data1/diff/dhstg/control02.ctl’ from
    ‘/data1/diff/dhstg/control01.ctl’;
       alter clone database mount;
    }
    executing Memory Script
    sql statement: alter system set  db_name =  ”DHSTG” comment= ”Modified by RMAN duplicate” scope=spfile
    sql statement: alter system set  db_unique_name =  ”DHSTG” comment= ”Modified by RMAN duplicate” scope=spfile
    Oracle instance shut down
    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                     2220200 bytes
    Variable Size                838864728 bytes
    Database Buffers             222298112 bytes
    Redo Buffers                   5554176 bytes
    Starting backup at 06-APR-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    copying current control file
    output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_dhstg.f tag=TAG20150406T141312 RECID=3 STAMP=876319993
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
    Finished backup at 06-APR-15
    Starting restore at 06-APR-15
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=63 device type=DISK
    channel ORA_AUX_DISK_1: copied control file copy
    Finished restore at 06-APR-15
    database mounted
    contents of Memory Script:
    {
       set newname for datafile  1 to
    “/data1/diff/dhstg/system01.dbf”;
       set newname for datafile  2 to
    “/data1/diff/dhstg/sysaux01.dbf”;
       set newname for datafile  3 to
    “/data1/diff/dhstg/undotbs01.dbf”;
       set newname for datafile  4 to
    “/data1/diff/dhstg/users01.dbf”;
       set newname for datafile  5 to
    “/data1/diff/dhstg/example01.dbf”;
       set newname for datafile  6 to
    “/data1/diff/dhstg/data01.dbf”;
       backup as copy reuse
       datafile  1 auxiliary format
    “/data1/diff/dhstg/system01.dbf”   datafile
    2 auxiliary format
    “/data1/diff/dhstg/sysaux01.dbf”   datafile
    3 auxiliary format
    “/data1/diff/dhstg/undotbs01.dbf”   datafile
    4 auxiliary format
    “/data1/diff/dhstg/users01.dbf”   datafile
    5 auxiliary format
    “/data1/diff/dhstg/example01.dbf”   datafile
    6 auxiliary format
    “/data1/diff/dhstg/data01.dbf”   ;
       sql ‘alter system archive log current’;
    }
    executing Memory Script
    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 06-APR-15
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
    continuing other job steps, job failed will not be re-run
    channel ORA_DISK_1: starting datafile copy
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
    continuing other job steps, job failed will not be re-run
    channel ORA_DISK_1: starting datafile copy
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
    continuing other job steps, job failed will not be re-run
    channel ORA_DISK_1: starting datafile copy
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
    continuing other job steps, job failed will not be re-run
    channel ORA_DISK_1: starting datafile copy
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
    continuing other job steps, job failed will not be re-run
    channel ORA_DISK_1: starting datafile copy
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 04/06/2015 14:21:34
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:34
    ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
    Solution: Work around for this is shutdown the target database and start in mount stage.

    • When you start the auxiliary instance with spfile.
    [oracle@Nsmstg01 dbs]$ export ORACLE_SID=dhstg
    [oracle@Nsmstg01 dbs]$ sqlplus
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 6 15:15:22 2015
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    Enter user-name: sys as sysdba
    Enter password:
    Connected to an idle instance.
    SQL> startup nomount
    ORACLE instance started.
    Total System Global Area 1068937216 bytes
    Fixed Size                  2220200 bytes
    Variable Size             838864728 bytes
    Database Buffers          222298112 bytes
    Redo Buffers                5554176 bytes
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@Nsmstg01 dbs]$ rman target sys/oracle@dhstg auxiliary sys/oracle@dhstg_dup
    Recovery Manager: Release 11.2.0.1.0 – Production on Mon Apr 6 15:16:07 2015
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    connected to target database: DHSTG (DBID=112149522, not open)
    connected to auxiliary database: DHSTG (not mounted)
    RMAN>
    RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE
    2> SPFILE
    3> PARAMETER_VALUE_CONVERT
    4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    5> SET DB_FILE_NAME_CONVERT
    6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    7> SET LOG_FILE_NAME_CONVERT
    8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’
    9> NOFILENAMECHECK;
    Starting Duplicate Db at 06-APR-15
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 04/06/2015 15:47:03
    RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause
    solution: Start the auxiliary instance with pfile

    Conclusion
    In the above article, we have learned that Active Duplication using Rman utility with same database name and different directory structure, and our source Database is in noarchive log mode, and there are some vital things we need remember while performing active duplication of database running in no archive log mode, we have came a cross two errors and their work arounds.

    BY
    Name: Mirza Sarmadullah Baig
    Designation: Senior Database Engineer
    Organization: Netsoftmate IT Solutions.
    Email: info@netsoftmate.com