August 26, 2015 / Shivananda Rao P

RMAN ACTIVE Database Duplicate with Source database in NOARCHIVELOG mode

I came across a question in OTN forums, wherein the OP wanted to do an RMAN “active” duplicate with source database in NOARCHIVELOG mode. This article is a small test or demo on how this can be done. Basically, when RMAN is used, the only thing that comes into our mind is that the database needs to be in ARCHIVELOG mode.

But, to duplicate a database using RMAN active method, Oracle document states that this method can be used while having the source database in either OPEN mode or MOUNT mode. Given this fact, let’s place the source database in NOARCHIVELOG mode and try the duplication.

Source database : SRPRIM
Auxiliary Database : NEWDB
Database Version : 11gR2

Let’s place the source database in NOARCHIVELOG mode and place it in MOUNT stage.


[oracle@ora1-1 ~]$ sqlplus sys/oracle@srprim as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 14 20:07:59 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> shut immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>
SQL> startup mount

ORACLE instance started.
Total System Global Area  943669248 bytes
Fixed Size                  2232128 bytes
Variable Size             591397056 bytes
Database Buffers          343932928 bytes
Redo Buffers                6107136 bytes

Database mounted.
SQL>
SQL> alter database noarchivelog;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      srprim           PRIMARY          MOUNTED

TNS Entry used for the Auxiliary database is as follows


NEWDB =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora1-3.mydomain)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = newdb)
      )
   )

Have the listener and the TNS configuration setup on both the Source and Auxiliary database servers. Check the connectivity, start the Auxiliary instance in NOMOUNT stage and initiate the duplication.

[oracle@ora1-3 ~]$ rman target sys/oracle@srprim auxiliary sys/oracle@newdb

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Aug 14 20:10:49 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SRPRIM (DBID=298418015, not open)
connected to auxiliary database: NEWDB (not mounted)

RMAN> duplicate target database to 'newdb' from active database nofilenamecheck;

Starting Duplicate Db at 14-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK

contents of Memory Script:
{
sql clone "create spfile from memory";
}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     943669248 bytes
Fixed Size                     2232128 bytes
Variable Size                595591360 bytes
Database Buffers             339738624 bytes
Redo Buffers                   6107136 bytes

contents of Memory Script:
{
sql clone "alter system set  control_files =
''+DATA1/newdb/controlfile/current.266.887746275'', ''+FRA1/newdb/controlfile/current.266.887746275'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set  db_name =
''SRPRIM'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set  db_unique_name =
''NEWDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format  '+DATA1/newdb/controlfile/current.267.887746275';
restore clone controlfile to  '+FRA1/newdb/controlfile/current.267.887746275' from
'+DATA1/newdb/controlfile/current.267.887746275';
sql clone "alter system set  control_files =
''+DATA1/newdb/controlfile/current.267.887746275'', ''+FRA1/newdb/controlfile/current.267.887746275'' comment=
''Set by RMAN'' scope=spfile";

shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}

executing Memory Script

sql statement: alter system set  control_files =   ''+DATA1/newdb/controlfile/current.266.887746275'', ''+FRA1/newdb/controlfile/current.266.887746275'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''SRPRIM'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''NEWDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     943669248 bytes
Fixed Size                     2232128 bytes
Variable Size                595591360 bytes
Database Buffers             339738624 bytes
Redo Buffers                   6107136 bytes

Starting backup at 14-AUG-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0.2/db_1/dbs/snapcf_srprim.f tag=TAG20150814T201138 RECID=4 STAMP=887746299
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-AUG-15

Starting restore at 14-AUG-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 14-AUG-15

sql statement: alter system set  control_files =   ''+DATA1/newdb/controlfile/current.267.887746275'', ''+FRA1/newdb/controlfile/current.267.887746275'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     943669248 bytes

Fixed Size                     2232128 bytes
Variable Size                595591360 bytes
Database Buffers             339738624 bytes
Redo Buffers                   6107136 bytes

database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
set newname for datafile  1 to "+data1";
set newname for datafile  2 to "+data1";
set newname for datafile  3 to "+data1";
set newname for datafile  4 to "+data1";
backup as copy reuse datafile  1 auxiliary format "+data1"
datafile 2 auxiliary format "+data1"
datafile 3 auxiliary format "+data1"
datafile 4 auxiliary format "+data1" ;
}

executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting backup at 14-AUG-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/srprim/datafile/system.256.882476449
output file name=+DATA1/newdb/datafile/system.268.887746333 tag=TAG20150814T201211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/srprim/datafile/sysaux.257.882476449
output file name=+DATA1/newdb/datafile/sysaux.269.887746359 tag=TAG20150814T201211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/srprim/datafile/undotbs1.258.882476449
output file name=+DATA1/newdb/datafile/undotbs1.270.887746413 tag=TAG20150814T201211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/srprim/datafile/users.259.882476449
output file name=+DATA1/newdb/datafile/users.271.887746421 tag=TAG20150814T201211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-AUG-15

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=887746422 file name=+DATA1/newdb/datafile/system.268.887746333
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=887746422 file name=+DATA1/newdb/datafile/sysaux.269.887746359
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=887746422 file name=+DATA1/newdb/datafile/undotbs1.270.887746413
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=887746422 file name=+DATA1/newdb/datafile/users.271.887746421

contents of Memory Script:
{
recover
clone database
noredo
delete archivelog
;
}

executing Memory Script

Starting recover at 14-AUG-15

allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

Finished recover at 14-AUG-15
Oracle instance started

Total System Global Area     943669248 bytes
Fixed Size                     2232128 bytes
Variable Size                595591360 bytes
Database Buffers             339738624 bytes
Redo Buffers                   6107136 bytes

contents of Memory Script:
{
sql clone "alter system set  db_name =
''NEWDB'' 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

sql statement: alter system set  db_name =  ''NEWDB'' 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     943669248 bytes

Fixed Size                     2232128 bytes
Variable Size                595591360 bytes
Database Buffers             339738624 bytes
Redo Buffers                   6107136 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( '+data1', '+fra1' ) SIZE 50 M  REUSE,
GROUP  2 ( '+data1', '+fra1' ) SIZE 50 M  REUSE,
GROUP  3 ( '+data1', '+fra1' ) SIZE 50 M  REUSE
DATAFILE
'+DATA1/newdb/datafile/system.268.887746333'
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for tempfile  1 to
"+data1";
set newname for tempfile  2 to
"+data1";
switch clone tempfile all;
catalog clone datafilecopy  "+DATA1/newdb/datafile/sysaux.269.887746359",
"+DATA1/newdb/datafile/undotbs1.270.887746413",
"+DATA1/newdb/datafile/users.271.887746421";
switch clone datafile all;
}

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +data1 in control file
renamed tempfile 2 to +data1 in control file

cataloged datafile copy
datafile copy file name=+DATA1/newdb/datafile/sysaux.269.887746359 RECID=1 STAMP=887746456
cataloged datafile copy
datafile copy file name=+DATA1/newdb/datafile/undotbs1.270.887746413 RECID=2 STAMP=887746456
cataloged datafile copy
datafile copy file name=+DATA1/newdb/datafile/users.271.887746421 RECID=3 STAMP=887746456

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=887746456 file name=+DATA1/newdb/datafile/sysaux.269.887746359
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=887746456 file name=+DATA1/newdb/datafile/undotbs1.270.887746413
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=887746456 file name=+DATA1/newdb/datafile/users.271.887746421

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 14-AUG-15

Now let’s connect to the new database and check the status.

[oracle@ora1-3 ~]$ sqlplus sys/oracle@newdb as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 14 20:18:32 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN         newdb            PRIMARY          READ WRITE

SQL>
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           1

We can see that the new database too is in NOARCHIVELOG mode. It’s recommended to have this changed to the Archive log mode.

Here we go !!

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

← OPatch Auto fails with “Bareword “ValidateCRSCTL” not allowed while “strict subs” in use” error

ORA-00392: log 1 of thread 1 is being cleared, operation not allowed →

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

RMAN ACTIVE Database Duplicate with Source database in NOARCHIVELOG mode

Leave a Reply

Recent Posts

Archives

Categories

Blog Stats

Blogroll

RMAN ACTIVE Database Duplicate with Source database in NOARCHIVELOG mode

Share this:

Like this:

Related

Leave a Reply

Recent Posts

Archives

Categories

Blog Stats

Blogroll