Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Copy 8.0.5 Oracle DB on Same Server

SOLVED
Go to solution
Tom Jackson
Valued Contributor

Copy 8.0.5 Oracle DB on Same Server

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
10 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

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
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

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
fiat lux
Stan_17
Valued Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

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
Valued Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

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
Honored Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

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
Valued Contributor
Solution

Re: Copy 8.0.5 Oracle DB on Same Server

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
Valued Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

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
Trusted Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

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

There are a 100 ways to do things and 97 of them are right
Stan_17
Valued Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

tom,

here is syntax.

svrmgrl> recover database using backup controlfile until cancel;

hth,
stan
Indira Aramandla
Honored Contributor

Re: Copy 8.0.5 Oracle DB on Same Server

Hi Tom,

Here is how you make a copy of an existing database with a different name. For example you want to copy the TEST1 database and call it TEST2 database.

1. create the oracle directoties under /oracle/admin/ bdump, udunmp, pfile, adump, arch......
then copy the init.ora file from TEST1.ora to TEST2.ORA and change the db_name, bdump_destination and UDUMP_DESTINATION.
2. On TEST1 database, logon to svrmgl and type this
svrmgrl>alter database backup controlfile to trace.

3. This will output a trace file in the UDUMP directory.

4. Now in TEST1 database shutdown. Then copy the datafiles and redo logs to the new place where you want (eg: d:\oradata\test2\) with the new name for the data file. (eg: cp d:\oradata\test1\system_test1.dbf d:\oradata\test2\system_test2.dbf).

5. Then you can startup the TEST1 database.

6. The backup control file in the UDUMP directory needs to be edited as to change the sid name and the location and name of the datafiles.

7. Then set your environment to point to the new sid as (set ORACLE_SID=test2).
Then logon as svrmgrl. type in startup mount.
and then execute the saved backup control file script.
Attached is an example of the backup control file script.

Hope this helps.
Never give up, Keep Trying