Hi Tom,

I am trying to clone one of my database and getting errors. I am on WIN2K and Oracle 8.1.7. I am giving here the steps that I followed.

. On the source machine, I stopped the services.
. Zipped and copied d:\oracle\admin and d:\oracle\oradata folders to the target machine.
. Unzipped the files to c:\oracle\admin and c:\oracle\oradata folders. ( Oracle is installed in C on target machine)
. I tried to start the service and I got this error.
ORA-27101 shared memory realm does not exist
. Later I realized the variation in C: and D: drives. So, I edited the init.ora file to replaced d: with c: for control files and also dump files.
. After restarting my machine and try to connect, this time I am getting
ORA-01033: ORACLE initialization or shutdown in progress.
. I copied my original init.ora file (install version) to c:\oracle\admin\<SID>\pfile and still getting the same error.

I have only one version of oracle installed and only one DB on both the machines.

In your previous answer I noticed you mentioning "exactly the same directory structure". Is that what I am messing in this situation? If so, what should I do?

Please help.

Thanks,
Ravi.

Hi Tom,

On the target machine, as part of the installation, I created the database. Backedup the files. I am able to start the database using those files.

I checked c:\oracle\ora817\database\oradim.log and it showed
ORA-01991: invalid password file 'C:\Oracle\Ora817\DATABASE\PWDeqed.ORA'

I copied the same file from source machine to the target machine. After restarting I checked the same file and now it is showing
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\<sid>\SYSTEM01.DBF'

It shows that it is still looking at d:\oracle\oradata\ folder where as I don't have D: drive but only C:
Does it store the hardcoded paths somewhere?

Thanks,
Ravi.

please tell me how i create passwrod file .
i have very difficulty in it . thanks. when i going to create database it give the error of password file

I am faceing problem in the creation of New database.
here i write the proper way of New database creation .and i request you to check my staps and make correction in it .
First create forder on ":\kar_dba"
(1) copy parameter file in c:\kar_dba
(2) open copy of parameter file and write database name=pak and give names and path of control files.
(3)startup oracle in nomount through pfile=c:\kar_dba\init.ora
(4) create database pak
datafile
'c:\kar_dba\ka_db1.dbf ' size 20m
logfile
group 1 'c:\kar_dba\ka_lo1.log' size 10m,
group 2 'c:\kar_dba\ka_lo2.log' size 10m;

(error) when i execute my database creation query .It create control files,log files and in the end give me error that password file is not found. how i add password file .

Hi Tom,

I always find asktom to be the most useful source...


I went thru your document for cloning DB at
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:318216852435 <code>

I'm getting the following error in (step # 12. Run the "ccf.sql" script )

My old DB name is SAMI
New DB name is CLONEDB

ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name SAMI in file header does not match given name of CLONEDB
ORA-01110: Data file 1: '/oracle/oradata/clonedb/system01.dbf'

Could you please help me to resolve this issue.

I have changed
a) "olddbname" to "newdbname"
b) "NORESETLOGS" to "RESETLOGS"

but did not change
c) REUSE DATABASE to SET DATABASE

It is working now, Thanks Tom

Tom

We are buying a new server that will be a mirror image
of the current Production Server.

question:
if we create the exact same directory structure and install
the exact same OS (Windows 2000 Server)...can we simply
restore, from the Production backup tapes, all the Oracle files to the new server. We would also import a copied
version of the registry from the Production Server to the
new server.

appreciate any feedback and thanks for the links on
how to change a SID....very ambitious and challenging.

Tom,

If the same situation applies to Solaris 8.0, can i restore the file systems and relink oracle without doing any installation??

Thanks

Thanks Tom....

for the feedback regarding the Registry import on a new
server installation.

I oftentimes wish we were using Unix or Linux.

I will look into the oradim command.

Tom,
Is it possible to clone a database A running in archivelog mode as another database B which runs in noarchivelog mode? We want to clone production database as pre-production test database on a weekly basis. This will give us the same data volume as production for query tuning. The constraints are:
a) Production database cannot be shutdown.
b) The production database runs in archivelog mode whereas pre-production test database runs in noarchivelog mode.
c) The cloning process has to be unattended, if possible.

We have Oracle 9ir2 and RMAN for backup.
Thanks.

Tom,
I am cloning database A as database B, both exactly identical, running in NOARCHIVELOG mode. Database A will be shutdown before copying files. I am using the CREATE CONTROLFILE statement to clone.
a) Do I need to copy redo log files from A to B if I need to open B with RESETLOGS option? My understanding is that if I use RESETLOGS option in CREATE CONTROLFILE, the redo log files will be created by Oracle as per the specifications given in the create controlfile statement.
b) Do I need to copy control files from A to B since I will be creating controlfile for B?
Thanks

"restore the file systems and
relink oracle without doing any installation??"

a.I have a new sol server. What is the best way I can have the same oracle setup that is there on a prodn db.?
b. How do I go about relinking oracle? Does this mean that there is no need to go through the steps for oracle installation from a CD and just copy all the directories from the prodn. server?

Hope I am clear of what I am looking for.

Hi Tom,

I tried to create duplicate db by reading chapter 12 of Recovery manager user’s guide (920).

Here is the name convention:
Target db: trg (host1) (In archive mode)
Catalog db: cat
Auxiliary db: aux (host2)

(1) create the same directory in host2 as in host1

(2) create rman backup file in trg.
rman target sys/he1@trg catalog rman/rman@cat
rman>register database;
rman>backup database;

(3) copy rman backup file (03fsola1_1_1) from $ORACLE_HOME/dbs (host1) to $ORACLE_HOME/dbs (host1).

(4) Copy initTRG.ora to host2 and change the name to initAUX.ora.
Set db_name=’aux’, instance_name=’aux’ in initAUX.ora
create spfile from pfile

(5) startup aux database
>startup nomount

(6) Issue the following command in host2.
$rman TARGET sys/he1@trg CATALOG rman/rman@cat AUXILIARY SYS/he1@aux

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: TRG (DBID=3951124335)
connected to recovery catalog database
connected to auxiliary database: AUX (not mounted)

(7) create duplicate db
RMAN>DUPLICATE TARGET DATABASE TO aux NOFILENAMECHECK;

The error I got: "no backup or copy of datafile". But I put duplicate copy of rman backup file in
the same location of host2 as in host1. And I issued "List backup" and all datafiles are there in rman backup file.

Thanks so much for you help.

-------------------------------------------------------------------------------------
-- Here is the error message
Starting Duplicate Db at 06-AUG-04
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=13 devtype=DISK

printing stored script: Memory Script
{
set until scn 355657;
set newname for datafile 1 to
"/export/home/oracle/9.2.0/oradata/cbtest2/system01.dbf";
set newname for datafile 2 to
"/export/home/oracle/9.2.0/oradata/cbtest2/undotbs01.dbf";
set newname for datafile 3 to
"/export/home/oracle/9.2.0/oradata/cbtest2/cwmlite01.dbf";
set newname for datafile 4 to
"/export/home/oracle/9.2.0/oradata/cbtest2/drsys01.dbf";
set newname for datafile 5 to
"/export/home/oracle/9.2.0/oradata/cbtest2/example01.dbf";
set newname for datafile 6 to
"/export/home/oracle/9.2.0/oradata/cbtest2/indx01.dbf";
set newname for datafile 7 to
"/export/home/oracle/9.2.0/oradata/cbtest2/odm01.dbf";
set newname for datafile 8 to
"/export/home/oracle/9.2.0/oradata/cbtest2/tools01.dbf";
set newname for datafile 9 to
"/export/home/oracle/9.2.0/oradata/cbtest2/users01.dbf";
set newname for datafile 10 to
"/export/home/oracle/9.2.0/oradata/cbtest2/xdb01.dbf";
restore
check readonly
clone database
;
}
executing script: Memory Script

executing command: SET until clause

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 restore at 06-AUG-04

using channel ORA_AUX_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/06/2004 15:45:56
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
------------------------------------------------------------------------

Hi Tom,

Thanks so much for your help.

By looking at metalink as you mentioned, I find two scripts to backup and restore db, it WORKS, but still gave me some error messages when creating duplicating db (see the bottom of the screen shot)

I am using the same procedure as in my previous question.

In step (2)

run {allocate channel bak1 type disk;
allocate channel bak2 type disk;
backup
format '$ORACLE_HOME/dbs/lvl0_%s%p.%d'
(database
tag = 'weekly_lvl0_bak'
include current controlfile);
sql "ALTER SYSTEM ARCHIVE LOG CURRENT";
backup
format '/$ORACLE_HOME/dbs/log_%s%p.%d'
(archivelog all delete input);
}


In step (7)
-- Time is after backup time and before the current time.

run{
set until time "to_date('2004/08/09 18:50:00','YYYY/MM/DD HH24:MI:SS')";
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
DUPLICATE TARGET DATABASE TO cbtest3 NOFILENAMECHECK;
sql "alter database open resetlogs";
}

-- I have to manaully run sql "alter database open resetlogs";


--------------------------------
-- Here is the output (Error is at the bottom).
RMAN> run{
set until time "to_date('2004/08/09 18:52:00','YYYY/MM/DD HH24:MI:SS')";
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
DUPLICATE TARGET DATABASE TO cbtest3 NOFILENAMECHECK;
sql "alter database open resetlogs";}

executing command: SET until clause

allocated channel: ch1
channel ch1: sid=13 devtype=DISK

allocated channel: ch2
channel ch2: sid=14 devtype=DISK

Starting Duplicate Db at 09-AUG-04

printing stored script: Memory Script
{
set until scn 1296044;
set newname for datafile 1 to
"/export/home/oracle/9.2.0/oradata/cbtest2/system01.dbf";
set newname for datafile 2 to
"/export/home/oracle/9.2.0/oradata/cbtest2/undotbs01.dbf";
set newname for datafile 3 to
"/export/home/oracle/9.2.0/oradata/cbtest2/cwmlite01.dbf";
set newname for datafile 4 to
"/export/home/oracle/9.2.0/oradata/cbtest2/drsys01.dbf";
set newname for datafile 5 to
"/export/home/oracle/9.2.0/oradata/cbtest2/example01.dbf";
set newname for datafile 6 to
"/export/home/oracle/9.2.0/oradata/cbtest2/indx01.dbf";
set newname for datafile 7 to
"/export/home/oracle/9.2.0/oradata/cbtest2/odm01.dbf";
set newname for datafile 8 to
"/export/home/oracle/9.2.0/oradata/cbtest2/tools01.dbf";
set newname for datafile 9 to
"/export/home/oracle/9.2.0/oradata/cbtest2/users01.dbf";
set newname for datafile 10 to
"/export/home/oracle/9.2.0/oradata/cbtest2/xdb01.dbf";
restore
check readonly
clone database
;
}
executing script: Memory Script

executing command: SET until clause

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 restore at 09-AUG-04

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /export/home/oracle/9.2.0/oradata/cbtest2/system01.
bf
restoring datafile 00002 to /export/home/oracle/9.2.0/oradata/cbtest2/undotbs01
dbf
restoring datafile 00003 to /export/home/oracle/9.2.0/oradata/cbtest2/cwmlite01
dbf
restoring datafile 00004 to /export/home/oracle/9.2.0/oradata/cbtest2/drsys01.d
f
restoring datafile 00005 to /export/home/oracle/9.2.0/oradata/cbtest2/example01
dbf
restoring datafile 00006 to /export/home/oracle/9.2.0/oradata/cbtest2/indx01.db
restoring datafile 00007 to /export/home/oracle/9.2.0/oradata/cbtest2/odm01.dbf
restoring datafile 00008 to /export/home/oracle/9.2.0/oradata/cbtest2/tools01.d
f
restoring datafile 00009 to /export/home/oracle/9.2.0/oradata/cbtest2/users01.d
f
restoring datafile 00010 to /export/home/oracle/9.2.0/oradata/cbtest2/xdb01.dbf
channel ch1: restored backup piece 1
piece handle=/export/home/oracle/9.2.0/dbs/lvl0_261.CBTEST2 tag=WEEKLY_LVL0_BAK
params=NULL
channel ch1: restore complete
Finished restore at 09-AUG-04
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "cbtest3" RESETLOGS ARCHIV
LOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ( '/export/home/oracle/9.2.0/oradata/cbtest2/redo01.log' ) SIZE 104
57600 REUSE,
GROUP 2 ( '/export/home/oracle/9.2.0/oradata/cbtest2/redo02.log' ) SIZE 104
57600 REUSE,
GROUP 3 ( '/export/home/oracle/9.2.0/oradata/cbtest2/redo03.log' ) SIZE 104
57600 REUSE
DATAFILE
'/export/home/oracle/9.2.0/oradata/cbtest2/system01.dbf'
CHARACTER SET WE8ISO8859P1


printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/cwmlite01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/drsys01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/example01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/indx01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/odm01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/tools01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/users01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=533760648 filename=/export/home/oracle/9.2.0/o
adata/cbtest2/xdb01.dbf

printing stored script: Memory Script
{
set until time "to_date('2004/08/09 18:50:00','YYYY/MM/DD HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing script: Memory Script

executing command: SET until clause

Starting recover at 09-AUG-04

starting media recovery

channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=21
channel ch1: restoring archive log
archive log thread=1 sequence=22
channel ch1: restored backup piece 1
piece handle=/export/home/oracle/9.2.0/dbs/log_271.CBTEST2 tag=TAG20040809T1842
6 params=NULL
channel ch1: restore complete
archive log filename=/export/home/oracle/9.2.0/oradata/cbtest2/archive/1_21.dbf
thread=1 sequence=21
channel clone_default: deleting archive log(s)
archive log filename=/export/home/oracle/9.2.0/oradata/cbtest2/archive/1_21.dbf
recid=1 stamp=533760652
archive log filename=/export/home/oracle/9.2.0/oradata/cbtest2/archive/1_22.dbf
thread=1 sequence=22
channel clone_default: deleting archive log(s)
archive log filename=/export/home/oracle/9.2.0/oradata/cbtest2/archive/1_22.dbf
recid=2 stamp=533760652
unable to find archive log
archive log thread=1 sequence=23
released channel: ch1
released channel: ch2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/09/2004 18:50:54
RMAN-03015: error occurred in stored script Memory Script
RMAN-06054: media recovery requesting unknown log: thread 1 scn 1296048
-----------------------------------------------------------------------------

sometimes just cloning a database is not enough.

if moving it to another machine you also have to :

1. ensure the environment on the new machine is setup, to match the cloned system.
this would include memory & disc allocation space.
2. ensure the "new" machine time is the same or greater than the machine you were cloning from

Hi Tom,

(1) I delete the time portion, it works without error.

run{
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
DUPLICATE TARGET DATABASE TO cbtest3 NOFILENAMECHECK;
sql "alter database open resetlogs";}


(2) But Metalink did show one script with time. How can I make it work without error. I set anytime after backup, it always give error message. Since the note in Metalink didn't mention how to backup in details, I guess I still have some problem with my backup script.

I got this script from Note:228257.1:

RMAN> RUN { # the DUPLICATE command uses an automatic disk channel
set until time "to_date('Jan 29 2003 10:50:00','Mon DD YYYY HH24:MI:SS')";
SET NEWNAME FOR DATAFILE 1 TO '/export/home/oracle/AUX/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/export/home/oracle/AUX/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/export/home/oracle/AUX/example01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/export/home/oracle/AUX/tools01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/export/home/oracle/AUX/users01.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/export/home/oracle/AUX/logmnrts.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/export/home/oracle/AUX/marius01.dbf';
DUPLICATE TARGET DATABASE TO AUX
SKIP TABLESPACE indx
LOGFILE
GROUP 1 ('/export/home/oracle/AUX/redo01a.log',
'/export/home/oracle/AUX/redo01b.log') SIZE 200K REUSE,
GROUP 2 ('/export/home/oracle/AUX/redo02a.log',
'/export/home/oracle/AUX/redo02b.log') SIZE 200K REUSE; }


(3)I used change number, it works. I used the change number in the error message minus 1. Actually I would like to know how to get the change number after backup, so it won't generate error during duplication.

run{
set until change 1296047;
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
DUPLICATE TARGET DATABASE TO cbtest3 NOFILENAMECHECK;
sql "alter database open resetlogs";}

Thanks so much for your help.

How much space doest it take to clone a database
I have 2 dev enviroment d1 and d2 and P1 is prodxn
P1 used space is 12 gb and d1 and d2 allocated space is 20gb for both.

Do i need extra space for d1 and d2 to refresh from p1

The document was very useful..
I have a query regarding Resetlogs option
While cloning a database we use Resetlogs twice
First in controlfile script
create controlfile set database "SID" resetlogs archivelog

Secondly alter database open resetlogs.

Why do we need to specify resetlogs option twice? At the time of opening the database we can simply reset the logs.

Thanks Tom !!it cleared my doubts.. though it might sound very elementary to many.. but i couldn't find it's answer anywhere..

When a db is cloned to a machine with a different hardware or configuration profile (ie less physical memory or different init.ora settings) is there a need or benefit to regenerate statistics?

what is the (best) way to determine *when* a database has been cloned?

Hi Tom,

We are creating duplicate database for testing using rman.
Here is what we did:
(1) using rman to backup production to disk.
(2) using rman to create duplicate database.
Here is the command:
rman catalog rman/rman@cbtest target sys/cb56cbdw auxiliary sys/cb56cbdw2@cbdw2
run{
allocate auxiliary channel ch1 type disk;
DUPLICATE TARGET DATABASE TO itprod until time 'sysdate - 1' NOFILENAMECHECK;}

We issue “duplicate target
database”command right after full backup. It will take about 20 hrs to restore all datafiles, then rman start to apply archive log files to duplicate database, sometimes it takes long time to apply all archive logfiles. Since we only need to duplicate database for testing, we would like to restore database without applying archive log file. But if we don’t include until clause, the restoration would fail because rman could not find necessary archive logfile.

My question is whether there is command which would let us create duplicate database without applying archive log file (or at least minimizing the amount of archive log applied).

By the way, we would like to use script to avoid manual process.

Thanks so much for your help.

Hi Tom,

It is hot backup. (db version 9206).

It is 500 GB database, rman spent most of time to copy rman backup from shared disk to local disk.

Since we only want to create duplicate db with latest full backup, we would like to apply archive log files as less as we can. With "until with time" command, it is hard to calculate the right time, we simply wasted a lot time to apply a lot of archive log files.

I just need a command to tell rman "Please create duplicate database and apply as less archive logfile as possible", so we can decrease the total time to create duplicate db.

Thanks so much for your help.

Sean

Hi Tom,

We want to have a job to run every week to create duplicate db, which means the operation without human involvement. We don't know when full backup finish before hand, then sysdate in "until time" command only considers when it starts to do recovery, which means after restoring of all data files. (Time to restore all data files vary).

In short, I just don't have a good command to tell rman to create duplicate db each week, without human involvement, with applying minimum archive log files.

Thanks so much for your help.

Sean

Sure. But the result is a little confusing. Besides, how could I pass the time to rmna script?

Here is my query:

select to_char(completion_time, 'HH:MI MM:DD:YYYY'), incremental_level from rc_backup_set where db_key=120775 and incremental_level is null order by completion_time

TO_CHAR(COMPLETI INCREMENTAL_LEVEL
---------------- -----------------
01:25 11:15:2005
01:25 11:15:2005
01:25 11:15:2005
09:54 11:15:2005
10:03 11:15:2005
10:03 11:15:2005
10:03 11:15:2005
10:03 11:15:2005
12:46 11:15:2005
12:47 11:15:2005
12:47 11:15:2005

TO_CHAR(COMPLETI INCREMENTAL_LEVEL
---------------- -----------------
12:47 11:15:2005



Only 1:25 is the entry for full backup, but it didn't show incremental_level=0.

Thanks so much for your help.

Sean

Hi Tom,

YOu are right. I should be able to generate a script by script.

Thanks so much for your help.

Sean

Hi Tom,

How do I recover a datafile that has not physically been backed up since its creation and has been deleted. Please provide syntax example.

Yes I have the archive log.

"rman"

Tom,

Two machines (A and B) are of same architecture with same operating system and with HP-UX but with different directory structure. Oracle version is 8.1.7.4 is running on machine A.

[1] My fellow DBA copied "oracle home" of machine A to machine B in different folder then ran "relink all" on machine B then rectified errors related to path and other errors. Now oracle is working on machine B

But somehow I think this is not the right approach to copy "oracle home" and rectify errors and while relinking.

[1] Is above a right approach?
[2] Is "relink all" method made for such cases?
[3] Will above method work with Oracle 8i, 9i and 10g.

Tom,

If you can please clarify what do you mean by "tiny bit different"

As you said "It is so rare to have the "same os"
too - I'd bet they are a tiny bit different"

But if we are doing "relink all" (re-compiling) does "os" difference makes any impact

Tom,

Apologies, Not getting

If uname -a returns same results for both machines.

How does it makes difference in "relink all"

Tom,

I agree with you that os can be different. But my question is still unanswered.


"But if we are doing "relink all" (re-compiling) does "os" difference makes any impact."

Base of above is "Doc ID: Note:1014014.6". which tells that executables store location of stared libraries in it and use them while executing. I don't understand that if "relink all" refresh all executables then where "os difference" comes in between.

1. Login to target database   ( TSTP )  and run backup through rman

c:\rman
rman> backup database;  (  if tstp is in noarchive log mode then make sure it should not be open mode)



2.

NoMount auxiliary database

SQL> startup nomount
ORACLE instance started.

Total System Global Area   97589952 bytes
Fixed Size                   453312 bytes
Variable Size              46137344 bytes
Database Buffers           50331648 bytes
Redo Buffers                 667648 bytes
SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


3. Run Rman clone process
C:\>rman

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> connect target system/manager@tstp

connected to target database: TSTP (DBID=422537837)

RMAN> CONNECT AUXILIARY SYS/tstrmn

connected to auxiliary database: tstrmn (not mounted)

RMAN>  connect target system/manager@tstp

connected to target database: TSTP (DBID=422537837)

RMAN> CONNECT AUXILIARY SYS/tstrmn

connected to auxiliary database: tstrmn (not mounted)

RMAN> CONFIGURE CHANNEL DEVICE TYPE disk CLEAR;

using target database controlfile instead of recovery catalog
old RMAN configuration parameters are successfully deleted

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> run
2> {
3> SET NEWNAME FOR DATAFILE 1 TO 'c:\tstrmn\system01.dbf';
4>     DUPLICATE TARGET DATABASE TO tstrmn
5>        LOGFILE
6>       GROUP 1 ('c:\tstrmn\redo01a.log',
7>                'c:\tstrmn\redo01b.log') SIZE 200K REUSE,
8>       GROUP 2 ('c:\tstrmn\redo02a.log',
9>                'c:\tstrmn\redo02b.log') SIZE 200K REUSE;
10> }

executing command: SET NEWNAME

Starting Duplicate Db at 16-JAN-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=10 devtype=DISK

printing stored script: Memory Script
{
   set newname for datafile  1 to
 "C:\TSTRMN\SYSTEM01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing script: Memory Script

executing command: SET NEWNAME

Starting restore at 16-JAN-06

using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\TSTRMN\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\ORA92\DATABASE\02H90PPG_1_1 tag=TAG20060116T161544 params
=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 16-JAN-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "tstrmn" RESETLOGS NOARCHIV
ELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      2
  MAXDATAFILES     1048
  MAXINSTANCES     1
  MAXLOGHISTORY      907
 LOGFILE
  GROUP  1 ( 'c:\tstrmn\redo01a.log', 'c:\tstrmn\redo01b.log' ) SIZE     204800
 REUSE,
  GROUP  2 ( 'c:\tstrmn\redo02a.log', 'c:\tstrmn\redo02b.log' ) SIZE     204800
 REUSE
 DATAFILE
  'C:\TSTRMN\SYSTEM01.DBF'
 CHARACTER SET US7ASCII


printing stored script: Memory Script
{
   switch clone datafile all;
}
executing script: Memory Script


printing stored script: Memory Script
{
   recover
   clone database
   noredo
   ,
    delete archivelog
   ;
}
executing script: Memory Script

Starting recover at 16-JAN-06
using channel ORA_AUX_DISK_1
Finished recover at 16-JAN-06

printing stored script: Memory Script
{
   shutdown clone;
   startup clone nomount ;
}
executing script: Memory Script

Now here process is hang :(


alert log last entry 
Mon Jan 16 16:21:26 2006
Successful mount of redo thread 1, with mount id 2901142661.
Mon Jan 16 16:21:26 2006
Completed: CREATE CONTROLFILE REUSE SET DATABASE "tstrmn" RES
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water mark = 4
Mon Jan 16 16:26:33 2006
Active process 5152 user 'SYSTEM' program 'ORACLE.EXE'
SHUTDOWN: waiting for logins to complete.

why it hung? 

:)  My bad

I exit from other session

My 2cents:




1. Login to target database   ( TSTP )  and run backup through rman

c:\rman
rman> backup database;  (  if tstp is in noarchive log mode then make sure it should not be open mode)



2.

NoMount auxiliary database

SQL> startup nomount
ORACLE instance started.

Total System Global Area   97589952 bytes
Fixed Size                   453312 bytes
Variable Size              46137344 bytes
Database Buffers           50331648 bytes
Redo Buffers                 667648 bytes
SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


3. Run Rman clone process
C:\>rman

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> connect target system/manager@tstp

connected to target database: TSTP (DBID=422537837)

RMAN> CONNECT AUXILIARY SYS/tstrmn

connected to auxiliary database: tstrmn (not mounted)

RMAN>  connect target system/manager@tstp

connected to target database: TSTP (DBID=422537837)

RMAN> CONNECT AUXILIARY SYS/tstrmn

connected to auxiliary database: tstrmn (not mounted)

RMAN> CONFIGURE CHANNEL DEVICE TYPE disk CLEAR;

using target database controlfile instead of recovery catalog
old RMAN configuration parameters are successfully deleted

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;

old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> run
2> {
3> SET NEWNAME FOR DATAFILE 1 TO 'c:\tstrmn\system01.dbf';
4>     DUPLICATE TARGET DATABASE TO tstrmn
5>        LOGFILE
6>       GROUP 1 ('c:\tstrmn\redo01a.log',
7>                'c:\tstrmn\redo01b.log') SIZE 200K REUSE,
8>       GROUP 2 ('c:\tstrmn\redo02a.log',
9>                'c:\tstrmn\redo02b.log') SIZE 200K REUSE;
10> }

executing command: SET NEWNAME

Starting Duplicate Db at 16-JAN-06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=10 devtype=DISK

printing stored script: Memory Script
{
   set newname for datafile  1 to
 "C:\TSTRMN\SYSTEM01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing script: Memory Script

executing command: SET NEWNAME

Starting restore at 16-JAN-06

using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\TSTRMN\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\ORA92\DATABASE\02H90PPG_1_1 tag=TAG20060116T161544 params
=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 16-JAN-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "tstrmn" RESETLOGS NOARCHIV
ELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      2
  MAXDATAFILES     1048
  MAXINSTANCES     1
  MAXLOGHISTORY      907
 LOGFILE
  GROUP  1 ( 'c:\tstrmn\redo01a.log', 'c:\tstrmn\redo01b.log' ) SIZE     204800
 REUSE,
  GROUP  2 ( 'c:\tstrmn\redo02a.log', 'c:\tstrmn\redo02b.log' ) SIZE     204800
 REUSE
 DATAFILE
  'C:\TSTRMN\SYSTEM01.DBF'
 CHARACTER SET US7ASCII


printing stored script: Memory Script
{
   switch clone datafile all;
}
executing script: Memory Script


printing stored script: Memory Script
{
   recover
   clone database
   noredo
   ,
    delete archivelog
   ;
}
executing script: Memory Script

Starting recover at 16-JAN-06
using channel ORA_AUX_DISK_1
Finished recover at 16-JAN-06

printing stored script: Memory Script
{
   shutdown clone;
   startup clone nomount ;
}
executing script: Memory Script



database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area      97589952 bytes

Fixed Size                      453312 bytes
Variable Size                 46137344 bytes
Database Buffers              50331648 bytes
Redo Buffers                    667648 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "tstrmn" RESETLOGS NOARCHIV
ELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      2
  MAXDATAFILES     1048
  MAXINSTANCES     1
  MAXLOGHISTORY      907
 LOGFILE
  GROUP  1 ( 'c:\tstrmn\redo01a.log', 'c:\tstrmn\redo01b.log' ) SIZE     204800
 REUSE,
  GROUP  2 ( 'c:\tstrmn\redo02a.log', 'c:\tstrmn\redo02b.log' ) SIZE     204800
 REUSE
 DATAFILE
  'C:\TSTRMN\SYSTEM01.DBF'
 CHARACTER SET US7ASCII


printing stored script: Memory Script
{
   Alter clone database open resetlogs;
}
executing script: Memory Script

database opened
Finished Duplicate Db at 16-JAN-06

 

Tom,

I have serious problem here and My manager is one of the nuts in this world.

She is asking me to clone the database with following procedure;
1. Restore all datafiles ,log files ( DB name is TP )
2. Startup mount
3. create controlfile set "TB" noresetlogs noarchivelog
4. alter database open;

Please comment on above procedure

it is a cold copy, there is no need for applying archive logs. Copy all the data files and redo logs, then do


Recreate the control file SET TP to TB,

1. startup mount

2. recover database until cancel

3. when it comes back with a prompt type cancel and hit return

4. Then type, alter database open resetlogs;



Above #2 and #3 can be skipped to #4, alter database open resetlogs. You always can try to open database, if it prompt for recover, then you do recover database. For cold copy, it is no need to apply archives.


Now, Appreciate if you please help me to understand How to explain that above procedure is wrong to my manager?

Thanks!

Database copied with hotbackup which included all datafiles,logfile and controlfiles then somebody validated the hot backup and did shutdown abort

I copied all datafiles and rename it to TDEV but while opening the database it was asking for archive logs so I had to startup backup database and then shutdown immediate during shutdown 8 archivelogs got generated which I applied to TDEV to bring it up

My manage asking why did you apply archive logs ?

How to restore file in remorte sever with rman set newfilename

Agree, after database is up, all fuzzy transactions are in synch. There is no need to apply archives. If this database is in non-archive mode, you shutdown abort, does that mean you can't open database? There are no archive logs to apply.

TP was in non-archive mode for a while, just for your information
Tom,
Can you please comment on above statement

Tom,

At the moment when I install a new database, I install the software and then run dbca to create the database. Installing the software is quick [20 mins or so] but installing the database takes about 1hr.

I was wondering if instead I could do one install and back it up, then instead of using dbca, just register the instance and recover the backup as this would be must faster and less error prone.

My questions are what needs to be the same between the backed up machine and the restored machine

1. Oracle software [is this base version or patch level]?
2. Operating system version [again is patch level important]?
3. Location of oracle software?

If there is a doc covering this, please just point me to it!

Thanks

Tom,

I wasn't using one of the custom seed databases as I wanted to remove all the functionality our application doesn't use such as xdb,data mining,etc. I had however forgotten that you could create your own seed databases from an existing database.

I couldn't find anything in the documents which states the restrictions on the portability of these seed databases. Are the restrictions on this the same as for cloning a database [same db version and patchset, same os version]?

Tom,

Thanks for your feedback on this. I spent yesterday playing with the seed databases to see whether they would fit what I want them to do and have a couple of questions.

1. If I download the install CD for Oracle 10g it comes with a seed database [Transaction Processing for example]. Now I use the same installer on windows 2000 that I use for windows 2003 and the seed database still works. Does the OUI install a different set of seed databases depending on the version of the OS, and if not, how do these seed databases work independently of the OS?

2. The seed database files [.djf] files are absolutely tiny compared with a full database backup....what format are they stored in?

Tom,

I agree completely that conceptually, I want to create template database, backup and then restore onto the new machine [this is just to make life easy for developers to be honest and to make sure everyone has a consistent database on their machine].

The nice thing about dbca is it makes this process transparent to the person installing....all they know is that they choose a template, fill in some values and get a consistent database with all setup done for them.

It sounds like I doing this would just require me to keep a template database up to date. I'll check with support as to whether seed databases created with dbca are portable across windows versions.

Thanks for your suggestions.....I think with a bit of investigation this could do exactly what I need. If not, we'll go the manual restore route.

Hi Tom,

I am trying to do duplicate database with rman ( Oracle 9.2 ) on same server. Database is in noarchive log mode.

I would like to know does RMAN requires target database backup for duplication ? if not I am getting following error

RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 24 found to restore
RMAN-06023: no backup or copy of datafile 23 found to restore
....
....
...
Kindly tell me what is the cause of an error and how to rectify it. ( my process of duplicate database thru rman was successful few times, but I am not sure why )

thanks & regards
pjp

Oracle9i Recovery Manager Reference
Release 2 (9.2)
Part Number A96565-01 says:

If you can make an operating system copy of a database file on one platform and then restore it to another platform, then it is also possible to duplicate a database from one platform to another. Otherwise, duplication is not possible.


What does this mean? Does it mean RMAN can be used for cross platform datamigration?

If I have a db in solaris, can I use RMAN backups and create the same db on Linux?

Hi Tom,

I am cloning Production Database A as B on other server, both exactly identical, running in ARCHIVELOG mode. For cloning first I shutdown the Database A and copy all the datafiles, redologs, init.ora, controlfile to the other server. Directory stucture on both servers is identical. On other server I just give startup command and it starts the database. This cloned database we are using to generate reports only(DSS).

To make the cloned database B updated every night we are doing the same thing. Now I want to ask that is there any way by which we can take incremental backup using RMAN of production database A and restore it to B.

I have already tried to take incremental level 1 backup and try to restore it to B. But in database B dosen't have the metadata for RMAN so it doesen't recognize the backup.

So if is there any other way that we can take an incremental backup of Database A and retore it to cloned Database B which resides on other server? please give me solution.

Hi Tom,

I have a cold backup of a standalone database.
I want to clone it as a 2 node rac database.My RAC setup is complete , it is on IBM AIX and cluster services etc everything is working.
As the cold backup is huge so I wanted to convert this into as 2 node rac.

I cloned it on one node of the RAC without any problems but when I try to clone it another node , it gives an error saying that can not identfiy the control files.

could you please suggest the correct way to do it.
consider me a newbie with RAC and I am just going through the various documents on RAC.

Regards,
Ajeet

Hi Tom,
We are using 9iR2 database as our production database ( eg.- prod).
We are using RMAN/Tivoli to backup our database straight to tape.
We want to create a duplicate database( with different name, eg.- test) on different server and are using the following command to do the cloning:

First connect to target, catalog and auxiliary database(test)

>duplicate target database to test;


Is there any chance at all that the rman can overwrite the production database ?

Regards,
Aru.

On a Linux system, if I copy the contents of the entire disks to another server, would this, in effect, create a database clone?

I'm cloning a production RAC database to a test server running in non cluster mode using the following script:

connect target sys/mypassword@edwprd1;
connect auxiliary /;
run {
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
duplicate target database to edwtst until time "to_date('25-SEP-06 21:05', 'DD-MON-YY HH24:MI')";
}
exit;

The database on production is 30Gb. I run the script in nohup and I can watch the rman log file. I wait over 4 hours for there to be anything written to the log file. Is this normal? How can I see what Rman is doing? I only can see inactive sessions connected to production and test. I thought the clone was hung during these 4 hours until voila', rman finally writes to the log file.

you wrote
"cross platform transports are supported in 10g."

what does this mean ?
anywhere a matrix which cross platforms are supported ?

for example, trying to duplicate a 10.2.0.2 DB from
Linux x86_64 to Linux ends up in
ORA-06553: PLS-801: internal error [56319]
on the cloned DB

Couldn't find a matrix of supported "cross platform transports" on metalink either

"... trying to duplicate a 10.2.0.2 DB from
Linux x86_64 to Linux x86 (32 Bit) ... "

I wanted to say

Created a clone database via dbca in a 9.2.0.8 environment, and successfully ran dbca with that clone template in a 9.2.0.6 environment. I was surprised when the database appeared to function just fine ... but suspect some issues may occur further on down the line (assume clone/seed dbj copies dictionary info, thus reason for same version requirement and the reason creation is so fast).

Until we can get everyone on 9208 in our production/development environment, assume I need to:
1. Re-create the clone in a 9206 environment?
2. Run $OH/rdbms/admin/catpatch.sql against a 9206-dbca-template-created database built in a 9208 environment to migrate the newly created database and dictionary objects up to the proper level?

If option 2 would work, would running 9206 catpatch.sql against a 9208-dbca-template-created database built in a 9206 environment also work to migrate database down to 9206?

thanks,
Dodie

Tom,

Thanks for looking at this, I know you are flooded with questions, and very much appreciate your time and expertise.

Let me try to clarify.
1. Created a database containing schemas and initial data in a 9.2.0.8 development environment.
2. Created a clone template with data from the 9.2.0.8 database above using the dbca tool (dbca generates new template dbname.dbc and data dbname.dfj).
3. Used the newly created clone template above to create this same database with initial schemas and data in a 9.2.0.6 development environment.

Result: The database created above seemed to function fine, so this seemed like a quick way to re-deploy initial database (this particular database is used by a COTS product to display information from other sources, so we do not need to backup/restore or even preserve data; we just need to rebuild it quickly in production with initial configuration data).

While looking for some other issue, I found this thread, and gave additional thought to how the clone template is likely created. Since it builds so quickly, am I correct in guessing that it contains the 9206 level dictionary objects and data?

Based on the contents of this thread, I will rebuild the clone template from a 9206 level database to match current production level.

However, I wondered if running the appropriate level catpatch.sql would migrate a 9206 clone database clone up to 9208 level in some of our development environments, or if I need to create two clones, one for each 9206 and 9208 level.

thanks again,
Dodie

Hi tom,

I need to clone a two node RAC environment and I am trying to use rman. In the new servers I brought the two new instances in nomount and then I ran the script that does the rman duplication, after the restore was finished the duplication failed while trying to create the control file saying:
RMAN-03002: failure of Duplicate Db command at 01/13/2007 16:22:51
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-01618: thread 2 is not enabled - cannot mount

Base on this error I am wondering if I need to run the duplication having only one instance in nomount with the cluster_database=false, and then after the duplication is finished I have to manually set up the cluster environment again. Am I right on this? or is there any way to run a duplication using rman that will work having two instances in nomount? If that is the case what do I need to do in order to resolve the error and have the second thread enabled so the rman process will not failed while trying to recreate the control file?

thanks, for any help you can give on this.

If we want to clone an 8.1.7 database to a 10.2.0 database server, is there any way to do it? Obviously the normal cloning procedures will not work, i.e. copying a backup etc, as the versions of Oracle are different. Also, the database in question is too big to do an Oracle export.
Are there any other options?
I can get away with copying the relevant schema bit-by-bit using the sqlplus Copy command across a link or something similar but this would be very time consuming.

Just wanted to follow up on my original question.
My rman clone process was taking 40 hours for a 50Gb database. Now it takes just 12 hours if you add:
sql "alter session set optimizer_mode=RULE";

Tom

I ran an RMAN warm backup as follows on the Prod server.

run {
delete noprompt backup;
set controlfile autobackup format for device type disk to 'D:\TestDB10G\Sunday_Controlfile%F';
backup incremental level 0 database format='D:\TestDB10G\SUNDAY_%u%p%c' tag=sunday_backup;
}

The directory structure on the Production server is something like this :

D:\TestDB10G\DATAFILES
D:\TestDB10G\SYSTEMFILES
D:\TestDB10G\CONTROLFILES
D:\TestDB10G\REDOLOGS
D:\TestDB10G\ARCHIVEDLOGS

(pls don't ask me why, but that is how the structure is)


On a new Server, after using ORADIM, etc,
I attempted to restore the above to the file system (the directory structure for the datafiles do not exist yet), expecting RMAN to automatically create the directories for me.

It doesn't. Why ?

Thanks for your response.

Hi Tom,
I try to find the answer long time but can't get the answer anywhere.
Rman duplication require connect target to the source database, why does it need to connect to the source and how is the overhead inccured in the source db during the duplication?

Tom,

I like your web site, and read your comments all the time, it helpled me a lot.
Now I have a question:
I want to clone an Oracle Home to a different directory, e.g. from /u01/app/oracle/product/10.2.0/db_1 to /u02/app/oracle/product/10.2.0/db_2
Can I do it like:
1. copy all the files from /u01/app/oracle/product/10.2.0/db_1 to /u02/app/oracle/product/10.2.0/db_2
2. $ORACLE_HOME/oui/bin/runInstaller -silent -clone ORACLE_HOME="/u02/app/oracle/product/10.2.0/db_2" ORACLE_HOME_NAME="db_2"

Thanks!

Hi Tom, I searchd for Note 444437.1 and found nothing. I would like to clone databases without connecting to the source db. Looking through the log of DUPLICATE DATABASE command, i see it executing several memory scripts running some commands that are not in rman doc:
restore clone database
switch clone database
recover clone database
alter clone database
They all are using CLONE DATABASE clause, explanation of which I could not find. Is there any doc explaining the internals of the DUPLICATE DATABASE and the purpose of CLONE DATABASE clause? I understand the process, but would like to know more at the level of each operation I see in the log.

Tom,
Thanks for your response, but I was looking for the description of the CLONE DATABASE clause and wanted to know if I can use it or if it is only an rman internal operation.
How do I get a copy of Note 444437.1, since I couldnt find ot on Metalink.
I would like to be able to clone the database without any access to the source db. Since I have a backup, including controlfile and archivelogs, I should be able to do this.

Sorry, I missed the new Note # you provided. I got it now and will review.
Thanks.

Hello Tom,

I am using Oracle 10.2.0.4.0 on Solaris 5.10. I am trying to clone the database 'DB1' to 'DB2' using RMAN. I have only full backup of DB1 including archive logs, taken on weekends. The DB1 was recovered recently, so there is a new incarnation in DB1.

I am trying to clone DB2 from the 'before-resetlogs' backup of DB1. The cloning is failing as it says the backup is from before-resetlogs.

I was told to reset the incarnation in DB1 database and then try cloning onto DB2. But DB1 is a production database, and I cannot reset the incarnation to previous one.

Is there any other solutions for this problem? Can I reset the incarnation of DB1 to previous one (so that it clones without error) and after cloning, again move the incarnation to the current (incarnation which is 'current' now) - what will be the effect of this?

Hope I am clear.

Thanks in advance

Tom,

Thanks..these are Very useful info.
I need some help on a task that I am planning.
I am duplicating a database from rac -asm enviroment to a new rac asm environment. This is for Oracle 11.1.0.7 database on Linux.

Here is the detail.
Can you please comment, if I have all the required steps.

=====================================================================

PRODUCTION DATABASE - MMHP1 on server mtshlp311/312
New Copy of database - MMHPC1 on server ghthlc311/312

To duplicate RAC database , first duplicate RAC Database to a single instance using RMAN and convert the single instance into a RAC cluster
==========================================================================================

1) Login to mtshp311 as oracle user.

Set database environment.

. oraenv
ORACLE_SID = [MMHP11] ? MMHP11

2) Ensure there is RMAN database backup available for MMHP1.
Use backup generated via daily backup jobs.

In case backup is not available then take full database & archivelog backup for MMHP1 as shown.

-------------------------------------------------------------------------------------------------

Create directory to take backup of MMHP1 on local NAS.

cd /u950/backup
mkdir CR660075

cd /u950/backup/CR660075
mkdir rmanbkup

Ensure same directory structure is created on ghthlc321.

cd /u950/backup/CR660075

Run the following commands on database MMHP1 only.

-------------------------------------------------------------------------------------------------

Create rman backup script as below.

vi /u950/backup/CR660075/backup_mmhp1.rman

run
{
configure default device type to disk;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u950/backup/CR660075/rmanbkup/%U' parallelism 4 backup type to compressed backupset;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/u950/backup/CR660075/rmanbkup/%F';
configure maxsetsize to 5G;
configure snapshot controlfile name to '/u950/backup/CR660075/rmanbkup/snapf_mmhp1.ora';
sql 'alter system switch logfile';
sql 'alter system archive log current';
backup full database plus archivelog;
}

:wq

-------------------------------------------------------------------------------------------------

Create rman backup shell script as below.

vi /u950/backup/CR660075/run_backup.ksh

#!/bin/ksh
export $ORACLE_SID=MMHP11
rman target=/ @/u950/backup/CR660075/backup_mmhp1.rman

:wq

chmod 755 run_backup.ksh

-------------------------------------------------------------------------------------------------

sqlplus '/as sysdba'
Alter system switch logfile;
Alter system archive log current
exit

-------------------------------------------------------------------------------------------------

Run the Backup

nohup ./run_backup.bsh > rman_mmhp1.log &

Check the backup log.

tail -f rman_mmhp1.log

3) Copy backup from mtshlp311 to ghthlc321 to same directory structure.

scp oracle@mtshlp311:/u950/backup/CR660075/rmanbkup/* oracle@ghthlc321:/u950/backup/CR660075/rmanbkup/

4) Prepare the Auxiliary Host for the Duplicate Database (MMHPC1).

Login to ghthlc321 as oracle user.

Update /etc/oratab file.

echo "mmhpc11:/u01/app/oracle/product/11.1.0/db_1:N" >> /etc/oratab

5) Create a password file for the Database (MMHPC1).

cd $ORACLE_HOME/dbs

orapwd file=orapwmmhpc11 password=xxxxx

6) Create any Required Directories for the Database (MMHPC1).

mkdir -p /u01/app/oracle/diag/rdbms/mmhpc1/MMHPC11/trace
mkdir -p /u01/app/oracle/diag/rdbms/mmhpc1/MMHPC11/cdump
mkdir -p /u01/app/oracle/admin/MMHP1/adump

Create Necessary ASM Directories on the Host of the New Database (MMHPC1) for storing controlfile and redologs.

export ORACLE_SID=+ASM1
asmcmd

ASMCMD> cd MTS_DATA
ASMCMD> mkdir mmhpc1
ASMCMD> cd mmhpc1
ASMCMD> mkdir onlinelog
ASMCMD> mkdir controlfile
ASMCMD: exit

asmcmd

ASMCMD> cd MTS_ARCHIVE
ASMCMD> mkdir mmhpc1
ASMCMD> cd mmhpc1
ASMCMD> mkdir onlinelog
ASMCMD> mkdir controlfile

7) Update the Oracle Networking Files as appropriate (MMHPC1)

cd $TNS_ADMIN

vi tnsnames.ora

MMHPC1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ghthlc321-vip) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MMHPC1) (INSTANCE_NAME = MMHPC1)))
MMHPC11 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ghthlc321-vip) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MMHPC1) (INSTANCE_NAME = MMHPC11)))
MMHPC12 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ghthlc322-vip) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MMHPC1) (INSTANCE_NAME = MMHPC12)))
MMHP12 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = mtshlp312-vip.sabre.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MMHP1) (INSTANCE_NAME = MMHP12)))
MMHP11 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = mtshlp311-vip.sabre.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MMHP1) (INSTANCE_NAME = MMHP11)))

Test connectivity to all services involved in the DUPLICATE operation

tnsping MMHP11

8) Create/Update the Listener with a Static Entry (MMHPC1)

vi /u01/app/asm/product/11.1.0/asm_1/network/admin/listener.ora

LISTENER = (DESCRIPTION_LIST
LISTENER_GHTHLC321 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ghthlc321-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.210.108.17)(PORT = 1521)(IP = FIRST))
)
)

SID_LIST_LISTENER_GHTHLC321 =
(SID_LIST =
(SID_DESC =
(SID_NAME = +ASM1)
(ORACLE_HOME = /u01/app/asm/product/11.1.0/asm_1)
)
(SID_DESC =
(SID_NAME = MMHPC1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
)
)

9) Copy the Parameter File from the TARGET to the AUXILIARY (MMHP1 to MMHPC1).

Login as oracle user on mtshlp311.

export ORACLE_SID=MMHP11

sqlplus "/ as sysdba"

create pfile='/u950/backup/CR660075/initMMHP11.ora' from spfile;

scp /u950/backup/CR660075/initMMHP11.ora oracle@ghthlc321:/u01/app/oracle/product/11.1.0/db_1/dbs/

10) Edit the Parameter File, Changing Necessary Values on Auxiliary Host (MMHPC1)

Login to ghthlc321 as oracle user.

. oraenv
ORACLE_SID = [oracle] ? MMHPC1

cd /u01/app/oracle/product/11.1.0/db_1/dbs/

mv initMMHP11.ora initMMHPC1.ora

In this example, the Diskgroup is switched from DATA to +MTS_DATA and FLASH to MTS_ARCHIVE.

The online redo logs shall be created as part of the DUPLICATE clause.

Comment all RAC related parameters for ex:- cluster_database, cluster_instances, thread ...Etc

vi /u01/app/oracle/product/11.1.0/db_1/dbs/initMMHPC1.ora

background_dump_dest='/u01/app/oracle/diag/rdbms/mmhpc1/MMHPC11/trace'

core_dump_dest='/u01/app/oracle/diag/rdbms/mmhpc1/MMHPC11/cdump'

user_dump_dest='/u01/app/oracle/diag/rdbms/mmhpc1/MMHPC11/trace'

control_files='+MTS_DATA/mmhpc1/controlfile/control01.ctl','+MTS_ARCHIVE/mmhpc1/controlfile/control02.ctl'

db_file_name_convert=('+DATA','+MTS_DATA')

log_file_name_convert=('+DATA','+MTS_DATA')

audit_file_dest='/u01/app/oracle/admin/MMHPC1/adump'

audit_sys_operations=TRUE

audit_trail='db'

compatible='11.1.0.7.0'

db_block_size=8192

db_domain=''

db_name='MMHPC1'

db_recovery_file_dest='+MTS_ARCHIVE'

db_recovery_file_dest_size=2147483648

db_create_file_dest='+MTS_DATA'

db_create_online_log_dest_1='+MTS_DATA'

db_create_online_log_dest_1='+MTS_ARCHIVE'

diagnostic_dest='/u01/app/oracle'

log_archive_dest_1='location=+MTS_ARCHIVE/'

db_unique_name=MMHPC1'

log_archive_format='%t_%s_%r.dbf'

log_archive_trace=511

max_dump_file_size='4000'

sga_target=3G

sga_max_size=3G

open_cursors=300

processes=500

remote_login_passwordfile='EXCLUSIVE'

undo_retention=86400

undo_tablespace='UNDOTBS1'

11) Create an SPFILE and startup nomount the Auxiliary Instance

export ORACLE_SID=MMHPC1

sqlplus / as sysdba

create spfile from pfile;

startup nomount;

exit

12) Run rman duplicate command

export ORACLE_SID=MMHPC1

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

rman

CONNECT TARGET SYS/xxxxx@MMHP11
CONNECT AUXILIARY;

RUN

{
ALLOCATE AUXILIARY CHANNEL D1 DEVICE TYPE disk format '/u950/backup/CR660075/rmanbkup/%U';
ALLOCATE AUXILIARY CHANNEL D2 DEVICE TYPE disk format '/u950/backup/CR660075/rmanbkup/%U';
ALLOCATE AUXILIARY CHANNEL D3 DEVICE TYPE disk format '/u950/backup/CR660075/rmanbkup/%U';
ALLOCATE AUXILIARY CHANNEL D4 DEVICE TYPE disk format '/u950/backup/CR660075/rmanbkup/%U';
ALLOCATE AUXILIARY CHANNEL D5 DEVICE TYPE disk format '/u950/backup/CR660075/rmanbkup/%U';
ALLOCATE AUXILIARY CHANNEL D6 DEVICE TYPE disk format '/u950/backup/CR660075/rmanbkup/%U';

duplicate target database to MMHPC1 logfile group 1 ('+MTS_DATA/mmhpc1/onlinelog/redo_0101.log','+MTS_ARCHIVE/mmhpc1/onlinelog/redo_0102.log') SIZE 500M REUSE,
group 2 ('+MTS_DATA/mmhpc1/onlinelog/redo_0201.log','+MTS_ARCHIVE/mmhpc1/onlinelog/redo_0202.log') SIZE 500M REUSE,
group 3 ('+MTS_DATA/mmhpc1/onlinelog/redo_0301.log','+MTS_ARCHIVE/mmhpc1/onlinelog/redo_0302.log') SIZE 500M REUSE,
group 4 ('+MTS_DATA/mmhpc1/onlinelog/redo_0401.log','+MTS_ARCHIVE/mmhpc1/onlinelog/redo_0402.log') SIZE 500M REUSE;
}

13) If duplication of database is successful, then turn the auxiliary database into a RAC database by adding back all the necessary parameters,
such as cluster_database, cluster_instances, thread , undo_tablespace etc.

14) Add second thread of online redo logs and enable that thread:

alter database add logfile thread 2 group 5 ('+MTS_DATA/mmhpc1/onlinelog/redo_0501.log','+MTS_ARCHIVE/mmhpc1/onlinelog/redo_0502.log') SIZE 500M REUSE;

alter database add logfile thread 2 group 6 ( '+MTS_DATA/mmhpc1/onlinelog/redo_0601.log','+MTS_ARCHIVE/mmhpc1/onlinelog/redo_0602.log') SIZE 500M REUSE;

alter database add logfile thread 2 group 7 ( '+MTS_DATA/mmhpc1/onlinelog/redo_0701.log','+MTS_ARCHIVE/mmhpc1/onlinelog/redo_0702.log') SIZE 500M REUSE;

alter database add logfile thread 2 group 8 ( '+MTS_DATA/mmhpc1/onlinelog/redo_0801.log','+MTS_ARCHIVE/mmhpc1/onlinelog/redo_0802.log') SIZE 500M REUSE;

alter database enable public thread 2;

15) Uncomment or add all RAC related parameters such as cluster_database, cluster_instances, thread , undo_tablespace etc.
Shutdown the single database instance and startup both Instances.

16) Create spfile on the shared storage, because all instances must use the same server parameter file.

create spfile from pfile;

17) Register RAC instances with CRS

srvctl add database -d MMHPC1 -o /u01/app/oracle/product/11.1.0/db_1

srvctl add instance -d MMHPC1 -i MMHPC11 -n ghthlc321

srvctl add instance -d MMHPC1 -i MMHPC12 -n ghthlc322

18) Shutdown and startup instances using srvctl

srvctl start database ?d MMHPC1

19) Validation of restored database.

Logon to the the Database (MMHPC1) and Verify.

select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;
select name from v$tempfile;

20) Check database alert logs and crs_stat -t output.

helo tom
i am new in orcle dba field

I am trying to clone one of my database and getting the following situation..

C:\Documents and Settings\neerajs>set ORACLE_SID=singh

C:\Documents and Settings\neerajs>rman target sys/sys@orcl auxiliary sys/sys@neeraj

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 27 12:31:41 2010

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

connected to target database: ORCL (DBID=1258436464)
connected to auxiliary database: NEERAJ (not mounted)

RMAN> duplicate target database to neeraj;

Starting Duplicate Db at 27-SEP-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
set until scn 589173;
set newname for datafile 1 to
"C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\USERS01.DBF";
set newname for datafile 5 to
"C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\EXAMPLE01.DBF";
set newname for datafile 6 to
"C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\ORCL.DBF";
set newname for datafile 7 to
"C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SINGH.DBF";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

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 restore at 27-SEP-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\USERS01.DBF
restoring datafile 00005 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\EXAMPLE01.DBF
restoring datafile 00006 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\ORCL.DBF
restoring datafile 00007 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SINGH.DBF
channel ORA_AUX_DISK_1: reading from backup piece D:\ORCL_BACKUP\0BLOULGF_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=D:\ORCL_BACKUP\0BLOULGF_1_1 tag=TAG20100927T115246
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 27-SEP-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "NEERAJ" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252


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

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=730816451 filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=730816451 filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=730816452 filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=730816452 filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\EXAMPLE01.DBF
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=730816452 filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\ORCL.DBF
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=730816453 filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\NEERAJ\SINGH.DBF

contents of Memory Script:
{
set until scn 589173;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27-SEP-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

starting media recovery

archive log thread 1 sequence 7 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_09_27\O1_MF_1_7_6B0G5C2K_.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_09_27\O1_MF_1_7_6B0G5C2K_.ARC thread=1 sequence=7
media recovery complete, elapsed time: 00:00:02
Finished recover at 27-SEP-10

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


after this is it restore all the file except redologs file in clone database
and after this it is not terminating.......
my clone db is in nomount stage....

guide me

hello ...
pliz rply the above question ..
i m waiting 4 rply..

Tom,

I have a question about this:

</ http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1006946/ >

What happens to the existing database files during this process? Let's say I ran it once successfully, then I wanted to run this regularly from then on. The files will all be there next time, does RMAN overwrite them? Do they get deleted? I'm hoping I don't have to drop the database every time.

You can run duplicate database against an existing database, if you initially created it by using duplicate database.

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1006946

Tom, did you see above? Where you aware of that? On a related note, I'm seeing inconsistent clone times, it's much quicker when you run one a 2nd time, as if RMAN is smart enough to know nothing has changed since the first clone.

That's cool. Can you define "updated datafiles" though, does mean changes since the last duplicate? That seems too good to be true, how would it know that.

Tom,

When you are duplicating a database you have to use static listener registration for the listener correct? Is there anything special you have to do vs dynamic to allow clients to connect? I can't for the life of me connect from my desktop using sqlplus.

This is the server's listener.ora + tnsnames, and my local entry:

(lx-cpora-t01k:oracle)> cat /oracle/product/11.2/network/admin/listener.ora
# listener.ora Network Configuration File: /oracle/product/11.2/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lx-cpora-t01k)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/11.2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = OCP36T.LMIG.COM)
      (ORACLE_HOME = /oracle/product/11.2)
    )
    (SID_DESC =
      (SID_NAME = OCP37T.LMIG.COM)
      (ORACLE_HOME = /oracle/product/10.2.0.3)
    )
  )

ADR_BASE_LISTENER = /oracle/product


/database/oracle
(lx-cpora-t01k:oracle)> cat /oracle/product/11.2/network/admin/tnsnames.ora

OCP36T =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lx-cpora-t01k)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OCP36T.LMIG.COM)
    )
  )

OCP37T =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lx-cpora-t01k)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = OCP37T.LMIG.COM)
    )
  )

OCP25X1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = x223kdc-v)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OCP25X.LMIG.COM)
      (INSTANCE_NAME = OCP25X1)
    )
  )

ocp37t = 
  (description = 
  (address_list = 
  (address = 
  (protocol = tcp) 
  (host = lx-cpora-t01k) 
  (port = 1521)) ) 
  (connect_data = 
  (service_name = ocp37p.lmig.com) ) )

Are you saying my tnsnames on the server also needs to be using SID not a service?

By the way I'm jealous you are in Nuremburg. If I were you I'd rent a Porsche to take around the Nuremburg track.

http://goeurope.about.com/od/germany/a/nurburgring.htm

Tom,

Is the an option when duplicating a database to tell it to remove the archivelogs it used from the target when it's done?

Tom, do you have any suggestions about the best way to cleanup archivelogs after a duplicate? I could add a step to do 'rm *' in my archlog directory, but I was hoping there was something a bit cleaner than that. I tried cataloging them so I could blow them away via rman, but they have different dbids.

Hi Tom,
why must have an odd number of voting disks, such as three, five,and so on.
why not 2,4,6, and so on for voting disks.
Thanks

i did rman cloneing with different servers for catalog,target,auxilary.I took thr onlinr backup of taget database.When iam connected to 3 database in auxilary level its shows error like no backup of datafile 5,4...????????

Hi Tom,

I've a project running on Oracle 10.2.0.4 enterprise edition.

The DEV1 database is refreshed on a recurring basis from PROD there. So, I got let's say DEV1 (dbid 1), DEV1 (dbid 2), DEV1 (dbid 3) and DEV1 (dbid 4) currently.

Development wants DEV2 to be equal to DEV1 (dbid 1) now. Is there a way to to this directly without restoring DEV1 (dbid 1) before?

I know with Oracle 11g there is a new feature where it is not required to connect to the target database anymore as there's a DBID clause with the duplicate command.

Cheers,
Michael

hi tom,

i'm in the same situation again. development wanted a duplicate from prod to dev (dbid 1) followed by a cold offline long-term backup.

then they ordered repeated duplicates from prod to dev (dbid 2), prod to dev (dbid 3), ..., prod to dev (dbid n).

now they want the cold, offline, long-term backup duplicated to another test database (as dev (dbid n) is currently used by another project).

question: is there a way to directly duplicate this old cold offline long-term backup of dev (dbid 1) _directly_ to the new test database? version = 10.2.0.4 EE

i tried serveral ways but rman always comes backup with some errors:
...
executing command: SET DBID
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of CSET command at 12/02/2011 14:50:53
RMAN-06188: cannot use command when connected to a mounted target database
...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 12/02/2011 15:10:40
RMAN-06189: current DBID 1407326645 does not match target mounted database (1430462119)
...
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 12/02/2011 15:11:48
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
...

thanks, michael

Dear Tom,
We are upgrading from Oracle 9i to 11g. For testing purpose we need to setup a windows server with 11g database. we also want data (tables, packages) present in 9i database to be copied in 11g database. Can cloning help me here or I need to go for exp/imp utilities?

Thanks in advance.

take hotbackup of database with sid orcl1. applying it ot another database on same machine with different directory/path of files with sid orcl. How i change control file. Is there need to change pfile of this new database.

Sorry i did not provided complete information. Actually I have two oracle home on same machine, each home has its own directory structure and database name and sid. I have take hotback of oracle_home1 and restoring it in oracle_home2 which have diffent settings (path for database files, databse name, sid). So if i dont change in pfile and control file how it will identify the locations in Oracle_Home2. Althoug it is corrent that the pfile of oracle_home2 dont need to change as it has already complete correct information but the controlfile which currently copied from oracle_home1 will need to be changed. am i right?

the option to change in control file is to generate sql file by ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'd:\backup\control.sql' and then change the relevant information in it. it means we dont need to get copy of control file of Oracle_home1 just sql file and change it. through this we lost other information which control file contains about databse state so from where oracle will rocover that information.

the archieve file which we need during database recovery are only those which are generated during our backup time. correct? How we identify these files?

How often we should delete archieve log files.

Dear Tom:
If I duplicate a DB to a virtual machine, i.e. with VMWARE, Virtualbox...: is significant to locate all datafiles in one and only one directory?
If the db is in a virtual machine, I think that it isn't relevant to maintain the same original structure (i.e., OFA), and would be better in terms of performance to have all in one and only one directory, what do you think?
All archives, datafiles, redos... together in one directory.
In contrast, do you recommend to maintain in the new db the original structured organization in differents directories? Or is there something like OFA relevant to virtualizated db's?

Thanks in advance

Thank you very much, sorry bothering you again. You specified "You'll have to basically rename every file, you can do this either by recreating your control files or by doing a startup mount and issuing alter database rename file 'old-name' to 'new-name'; ", in my case the file name will be same but their location will be different. How this can be changed in coltrol file without generating to trace?

I am doing this for testing purposes, i dont have backup from Production therefore created two installations from one home get backup and apply on other. The acutall task is to create copy of production database which is Oracle 10.2 on windows and restore it for development on Oracle 11.2 on widnows.

I'm very pleasant for your answer. Sorry, maybe I didn't explain well or completely: when you have a db with OFA, you have different disks and different directories where files (data, index, arc's,redos..) are located. If you have a db in a virtual machine, surely (well, it's possible..) that this db is located in a one and only one disk, so, file access is different: one disk versus. different disks. So, performance, file access (read-write) must be different, don't you think?
Thanks again in advance!!

Thanks a lot, we are always learning witht your comments (and books!!)We have only one disk, so I identified one disk as one mount point. I think you don't recommend that..
Only one question more: where can I find more about this theme (ubication, mount points, virtualization...) in your greats publications? It's only a matter of a system administrator? (like a fellow worker told me)
Finally, thanks again!!

Backup of 10.2G(Windows Server 2003 R2 Enterprise x64 Edition.) taken and then restoring it on 11.2G (Widnows Server 2008 R2 Standard). When dbua launched it gives error "ora-04023 object sys.standard could not be validated or authorized". on web ( https://kr.forums.oracle.com/forums/thread.jspa?threadID=2336603 ) some people are advising to execute script on 10G before taking backup, it does not seem logical.

14:51:26 DOC>#######################################################################
14:51:26 DOC>#######################################################################
14:51:26 DOC> The following error is generated if the pre-upgrade tool has not been
14:51:26 DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
14:51:26 DOC>
14:51:26 DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
14:51:26 DOC> *
14:51:26 DOC> ERROR at line 1:
14:51:26 DOC> ORA-01722: invalid number
14:51:26 DOC>
14:51:26 DOC> o Action:
14:51:26 DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
14:51:26 DOC> Revert to the original oracle home and start the database.
14:51:26 DOC> Run pre-upgrade tool against the database.
14:51:26 DOC> Review and take appropriate actions based on the pre-upgrade
14:51:26 DOC> output before opening the datatabase in the new software version.
14:51:26 DOC>
14:51:26 DOC>#######################################################################
14:51:26 DOC>#######################################################################
14:51:26 DOC>#

Database intalled on both machine are enterpise edition. The scenerio is i have taken backup from 10G and restoring it on 11G. When dbua launched it gives error "ora-04023 objectsys.standard could not be validated or authorized". Is it not possible to take backup from 10 G and then restore it on 11G?

Sorry i did not provide complete detail. I have taken backup from 10.2.0.1.0 - 64bit enterprise edition OS Windows. In another machine there is oracle 11.2.0.1.0 enterprise edition OS windows. In 11 G i have followed these steps
shutdown database
copied data files and control file
adjusted init.ora
Start the database in mount mode
changed file location in control file by alter database rename file to new path.
Recover database until cancel using backup controlfile;
cancel
When dbua launched it gives error "ora-04023 object
sys.standard could not be validated or authorized".

Tom,
One question

Suppose I have the following datafiles in a DB

system01.dbf
undo01.dbf
temp01.dbf
redo01.dbf

users01.dbf --- 100GB
users02.dbf --5GB

at time t1 : I have the RMAN backup of all above files
-- and archive logs are being archived continously


I want to clone the db but NOT interested in biggest file users01.dbf ( 100GB) ... so that my cloned DB is just become small.

would it be possible
- that I just restore file ( excluding the biggy users01.dbf ) and do recover the remaining to some point in time t2 by doing incomplete recovery?

Hello Reader,

For restoring only a subset of tablespaces, you can use the "skip tablespace" command. I am not sure from which version they introduced this command, but it is working in 11.2.0.1

I used it once to successfully restore a subset of Tablespaces. System, Sysaux cannot be skipped of course.

Here an Example:

RUN {
set until time "to_date('02.03.2011 20:00','DD.MM.YYYY HH24:MI')";
SET NEWNAME FOR DATABASE TO '/some/folder/on/server/%U';
DUPLICATE TARGET DATABASE TO AUX SKIP TABLESPACE 'USERS01'
LOGFILE GROUP 1 ('...../aux/oradata/redo01a.log',
'...../aux/oradata/redo01b.log') SIZE 256M REUSE,
GROUP 2 ('...../aux/oradata/redo02a.log',
'....../aux/oradata/redo02b.log') SIZE 256M REUSE;
}

Regards,

Pasko

Hi Tom,

1) I have tried duplicating a database using rman clone but i realize i need to install oracle 1st on the target computer.

2) I have also tried to clone a oracle home on the target computer so that i do not need to install oracle again.

-------------------------------------------

Either way, i would need to do the 2 steps to have a fully duplicated database.

Is there any shortcut on 10g so that i can perform both at the same time ?

What is the norm now in 12c for doing such duplication, can we do 2 steps at once (literally copying the whole database + oracle software) ?

Regards,
Noob

Hi Tom, about cloning a database with thin provisioning. I know there's Direct NFS and some other features on NetApp that can create many 'snapshots' of a database and only write the block changes do disk.

Does it work on ASM databases? Does OEM "automagically" clone a database this way?

I'm puzzled now...

The document says:
"When you want to clone a database that uses Oracle Automatic Storage Management (Oracle
ASM), you cannot mount a copy of the ASM disks on the same server as the source database. You can
use Volume Copy to clone an ASM database, but you must mount the ASM disks on another server
that is not using Oracle ASM."

The copied volume should be mounted on a second server not as ASM, but as FS? Is that correct?

Also, the document talks about Sun storage, but I assume it would be the same for NetApp, right?

Using a different approach that is not storage specifc, when using an RMAN full backup set as source and Direct NFS to write the block changes, is it ok to clone an ASM based database?

Again, thanks a lot for your help! I'm setting up a new dev/qa enviroment and a copy on right approach would help me save up a lot of storage!

Which one would you recommend? An storage specifc approach or a pure database approach like RMAN + Direct NFS?

From the top of my mind, I'd think: storage specifc would be a lot faster (no network needed), but would tie me to that specific brand and would not work on a heterogeneous enviroment (storage-wise).

hi tom,
I know duplicate can clone the db with a new dbid.
Is it possible to clone the db with a new dbid but do it incrementaly?
what i mean is that i will do duplicate in the first time and then i will update my new db (with the new dbid) with incremental backup?
what i need is a new db with a new dbid that will be updated every day with the changes that occur in the previous day (i don't want to do every time duplicate because i have a large DB).
thanks.

Hi Tom:

here is situation and Question.
1) version 10g and below
2) we have backup of a database A
3) I want to clone a database B with DUPLICATE in RMAN

1) Why do we allocate a channel to the source database A when we have a backup of A to clone B?
2) What does that channel do in Source database A?

Tom -

Thanks again for all the great insights into Oracle!

We're moving one of our customer databases from their datacenter to ours. It's a standalone (not RAC), on ASM, under 11.2.0.3. We'd like to rename their database and its files to our naming standards during the migration.

We used their RMan copy to create the database on our storage in our +ASM instance. So now ASM has the datafiles under '+DATA/ABC', and we'd like to change that to '+DATA/XYZ' to meet our standards. All the information I've found so far does this via RMan or DBMS_FILE_TRANSFER, but in both cases it's done by copying the files rather than renaming the directory entry. In this case, we can't rename the directory entry because Oracle created it while building the database.

We want to avoid copying the datafiles because the database in question is somewhat over 4 TB. We don't have another 4 TB of disk for use during the copy operation, nor will we have the time on our go-live weekend to perform the copies.

When we initially build the db, we created an 'ABC' directory in ASM. Should we have created the directory as 'XYZ' and then managed this via the "set newname" during the initial creation of the database? Or is there another preferred method to use?

We're assuming that, if we can get the db in the preferred directory structure, we can simply create a backup controlfile and then create a new set of controlfiles, specifying
create controlfile set database "XYZ"...
instead of
create controlfile reuse database "ABC"...
This would then be used to change the name of the database.

Tom -

Sorry, I forgot to mention: we have one last chance to do this from the start. We hit this problem when creating their acceptance test db in our datacenter. We have not yet begun to create their production db. We want to get it right this time, when we stand up their prod instance. If necessary, we can consider the acceptance test db a throwaway.