Tuesday, June 28, 2011

Clone 10g DB manually without RMAN & NID utility


This post assumes that you are cloning "prod" to "clone".

1. Take cold backup of database:
    a. select name from v$datafile;
    b. Shutdown the database
    c. copy the files from step 1a) to the destination server and location.
Note: We dont have to copy controlfiles and redolog files, these will be created fresh.

2. Copy and edit the init.ora. If using spfile, create pfile first.
copy the init.ora to destination $OH/dbs/initclone.ora and do :
modify db_name
modify path for adump, udump, cdump and bdump
modify path for control_files


3. After backup you can start the "prod" db and execute:
alter database backup controlfile to trace; ==> This will generate a trace file in udump of prod db. Find the trace.

4. Use the trace generated in step#3 to create a script to create controfile. e.g. recreatecontrol.sql
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u02/oralog/clone/redo01a.log',
    '/u04/oralog/clone/redo01b.log'
  ) SIZE 100M,
  GROUP 2 (
    '/u03/oralog/clone/redo02a.log',
    '/u04/oralog/clone/redo02b.log'
  ) SIZE 100M,
  GROUP 3 (
    '/u03/oralog/clone/redo03a.log',
    '/u04/oralog/clone/redo03b.log'
  ) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/u04/oradata/clone/system01.dbf',
  '/u04/oradata/clone/undo01.dbf',
  '/u04/oradata/clone/sysaux01.dbf',
  '/u04/oradata/clone/users01.dbf'
CHARACTER SET AL32UTF8
;

5. Set Oracle env for clone db
export ORACLE_HOME
export ORACLE_SID=clone
sqlplus /nolog
sql> conn / as sysdba
sql> startup nomount
SQL>@recreatecontrol

SQL>alter database open resetlogs;

6. Add tempfile to TEMP tablespace.

7. Restart clone db and monitor alert log to make sure everything is ok.

No comments:

Post a Comment