Fast way to copy/duplicate Oracle database

10 Apr

Here are the steps to copy/duplicate database (AA- original, BB – copy/duplicate):

1: Login into AA with sys (user with sysdba role) – run this query:

alter database backup controlfile to trace;

A trace file will be generated to udump (user_dump_dest) dir for that database. Copy the content from that trc-file so it looks like this to a new file:

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE “AA” RESETLOGS
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘F:\ORACLE\PRODUCT\ORADATA\AA\REDO01.LOG’ SIZE 50M,
GROUP 2 ‘F:\ORACLE\PRODUCT\ORADATA\AA\REDO02.LOG’ SIZE 50M,
GROUP 3 ‘F:\ORACLE\PRODUCT\ORADATA\AA\REDO03.LOG’ SIZE 50M
DATAFILE
‘F:\ORACLE\PRODUCT\ORADATA\AA\SYSTEM01.DBF’,
‘F:\ORACLE\PRODUCT\ORADATA\AA\UNDOTBS01.DBF’,
‘F:\ORACLE\PRODUCT\ORADATA\AA\SYSAUX01.DBF’,
‘F:\ORACLE\PRODUCT\ORADATA\AA\USERS01.DBF’,
‘F:\ORACLE\PRODUCT\ORADATA\AA\INDX01.DBF’,

2: Shutdown database AA

3: Copy all datafile for AA to a new directory – you can change the database files, but remember to change the lines above to reflect the changes

4: Change the following in the file

Org: CREATE CONTROLFILE REUSE DATABASE “AA” NORESETLOGS

New: CREATE CONTROLFILE SET DATABASE “BB” RESETLOGS

Org: ‘F:\ORACLE\PRODUCT\ORADATA\AA\SYSTEM01.DBF’, ‘F:\ORACLE\PRODUCT\ORADATA\AA\UNDOTBS01.DBF’

New: ‘F:\ORACLE\PRODUCT\ORADATA\BB\SYSTEM01.DBF’,’F:\ORACLE\PRODUCT\ORADATA\BB\UNDOTBS01.DBF’,

5: Save the file with e.g CreateControlfileBB.sql

6: Create adump,bdump,cdump,ddump,pfile,scripts,udump dir for the new database (under same structure as the other databases)

7: Either create pfile or copy from the AA database

create pfile from spfile (this command will create initAA.ora file, which you can copy and edit to fit your new BB database)

8: Run the CreateControlfileBB.sql as sysdba

9: Put BB database in archivelog mode, or just open database:

alter database open resetlogs;

 

Read more

http://www.dba-oracle.com/oracle_tips_db_copy.htm

 

 

 

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: