Copy 8.0.5 Oracle DB on Same Server

[Tom Jackson]

Hi:

I have an Orcale 8.0.5 DB running on an NT server that I want to copy. Both DBs will run on the same server. The new DB needs to have the same tables, views, users, etc. The only differences should be the sid and data. I don't want to copy the data.

Is there an easy way to do this rather than create the DB from scratch?

Tom

[Jean-Luc Oudart]

One to do it is :
1) COLD backup of your existing database
2) restore to different location
3) clone the database

As you clone the database on same server, you must shut down 1st instance during the cloning operation.

Rgds,
Jean-Luc

[Jean-Luc Oudart]

How to Clone the database ?

sqlplus system/
SQL> alter database backup controlfile to TRACE;
SQL> exit

this will produce a trace file in your user dump directory.
Edit the file and replace database name, database component location.
Comment out "RECOVER DATABASE"
and replace "ALTER DATABASE OPEN" with "ALTER DATABASE OPEN RESETLOGS"

Shutdown 1st instance
change ORACLE_SID
svrmgrl (or whatever it is for NT)
connect internal
@

JL

[Stan_17]

Tom,

you could do this with a hotbackup of the original database. here is what i would do.

1. take a hotbackup of the primary db
2. backup controlfile to trace via svrmgrl
alter backup controlfile to trace;
3. modify controlfile with the fol.
-- remove header info
-- modify logfiles to point different location
-- modify datafiles to point different location
-- importantly, change
CREATE CONTROLFILE SET DATABASE 2nddb_name RESETLOGS NOARCHIVELOG
or if the database is in archive log mode:
CREATE CONTROLFILE SET DATABASE 2nddb_name RESETLOGS ARCHIVELOG
-- Remove everything from tracefile after the end of the create controlfile statement.

4. Copy primary db's parameter files to 2nd db parameter files by doing the fol.
-- Copy initDB1.ora to initDB2.ora.
-- Modify parameters in initDB2.ora
CONTROL_FILE = new control files names
DB_NAME = new database name
log_archive_dest = to right location
*_dump_* paramterts = to right location

5. Set the following environment variables:
ORACLE_SID = DB2 and ORACLE_HOME

6. Run the trace file script from 3) via SVRMGRL as a privileged user.

7. do a incomplete recovery via
restore database using backup controlfile until cancel

8. open the database with resetlogs
alter database open resetlogs

9. change global name
alter database rename global_name to DB2.xyz.com;

hth,
stan

[Tom Jackson]

Stan/Jean-Luc:

Thanks for the quick replies. But I'm a little confused. I'll start with Stan since I have the fewest questions.

Stan:

In your step 1, you want me to "Take a hotbackup of the primary DB." Do you just want me to copy all of DB1 files to another directory? The are currently in ORACLE_HOME\database. Should I copy them to ORACLE_HOME\DB2? Do I use this copy later? Can you give a bit more detail?

In number 5/6 you want me to set ORACLE_SID to DB2. When I call srvmgr30.exe it complains with:
TNS: Unable to connect to destination.

Is the above error normal?

In number 7, you want me to do an incomplete recovery via
restore database using backup controlfile until cancel

Is the above a command? Should it be recover?

Tom

[Brian Crabtree]

Tom,

If you are really looking for a database with no data, your best option is to create another database from scratch, and run an export with "ROWS=N". This will export just the structure of the tables, which you can import into the new database and populate with data.

Brian

[Stan_17 · Accepted solution]

In your step 1, you want me to "Take a hotbackup of the primary DB." Do you just want me to copy all of DB1 files to another directory?
@@ take a loot at http://www.experts-exchange.com/Databases/Oracle/Q_20004248.html for how to take hotbackup a database on NT.

The are currently in ORACLE_HOME\database. Should I copy them to ORACLE_HOME\DB2? Do I use this copy later? Can you give a bit more detail?
@@ where to copy is up to you. assuming if your DB1 dbf files are in $ORACLE_HOME/database/DB1/ then copy them to $ORACLE_HOME/database/DB2 (realistically mount points would be different in a production enviroment so i will leave it you to decide.)

In number 5/6 you want me to set ORACLE_SID to DB2. When I call srvmgr30.exe it complains with:
TNS: Unable to connect to destination.

Is the above error normal?
@@ actually in NT you need to set 'service name' via oradimxx.exe before setting oracle_sid.

for e.g. oradimxx -new -sid -intpwd - startmode auto -pfile

In number 7, you want me to do an incomplete recovery via restore database using backup controlfile until cancel

Is the above a command? Should it be recover?
@@yep. that would do it. i strongly suggest you to take a hotbackup of DB1 and copy to tapes before you start cloning DB2. if you have access to metalink take a look at 73301.1 for complete steps to clone a database on the same NT box however that article takes coldbackup route to clone it but the steps are pretty much same.

stan

[Tom Jackson]

Stan:

Thanks for the details. However, I'm still not clear on #7. Would you please give me the syntax of the recover/restore command?

Thanks again,

Tom

[Rory R Hammond]

Tom,

I not sure if I am going to help. But a Createdb isn't that hard to do. You stated that you didn't want to copy the data. I always have a create db script which I use to create my DB. The problem is it gets out sync when I add table spaces. Attahed is a sql script which will create a
script that will create all of the table spaces. If you are on the same system you will have to slight modify the db names.

Steps
1. Run the attached script (cretablespace.sql) on your "from" system.
2 export data from you system
3. Modify the tables space names found in the created sql (tables.sql)
4. Do createdb
5 run the create table spaces script (tabgles.sql) that you modified in step 3.
5 Run the Oracle import. full=y rows=n

I used this techniqe to move a oracle DB from HPUX to NT. In a few weeks, I will be using the same technique to move the DB from NT to a AIX box. The difference is that I also moved the data. rows=y

[Stan_17]

tom,

here is syntax.

svrmgrl> recover database using backup controlfile until cancel;

hth,
stan