Uncategorized

Rman Duplicate from noarchivelog mode target database

February 12, 2017

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



Tags: , , , , , , , , , , , , ,