Cloning Database

CLONING DATABASE

CLONING DATABASE...................................................................................................I Pre clone steps.....................................................................................................................II Think of Database name......................................................................................................II Create respective filesystem as same as previous one........................................................II Copy all dbf files to cloned location..................................................................................III Create initialization parameter file.....................................................................................III Create Environmental file..................................................................................................III Run Clonedb.sql script ......................................................................................................III Obervations after installation..............................................................................................V Problems faced....................................................................................................................V

Document Prepared by: Nikhil Rangaraju Oracle NAIO

NOTE: Database cloning was done with the same user.

I

Pre clone steps Log in to database to which you need to clone and startup the database. Command: Sqlplus ‘/as sysdba’ Startup Create a script to create controlfile. Command: Alter database backup controlfile to trace; Cd /u01/oracle9i/admin/db01/ Cp db01_ora_24633.trc /u01/oracle9i/clonedb.sql Edit clonedb.sql with option RESETLOGS as cloned database name is different from original one. Think of Database name Oracle_Sid = clonedb Create respective filesystem as same as previous one Oracle9i

Admin db01

Oradata clonedb

bdump udump cdump

db01

clonedb

bdump udump cdump

Command: Mkdir clonedb Mkdir bdump Mkdir cdump Mkdir udump II

Copy all dbf files to cloned location Command: Cd /u01/oracle9i/oradata/db01/ Cp *.dbf /u01/oracle9i/oradata/clonedb/ See that all destinations of database files are changes in clonedb.sql script. Create initialization parameter file Go to ORACLE_HOME/dbs and create initclonedb.ora file. Command: Cp initdb01.ora initclonedb.ora Vi initclonedb.ora %s/db01/clonedb/g

Create Environmental file Su – oracle9i Vi clonedb.env export ORACLE_BASE=/u01/oracle9i export ORACLE_HOME=$ORACLE_BASE/product export ORACLE_SID=clonedb export LD_ASSUME_KERNEL=2.4.19 export LD_LIBRARY_PATH=$ORACLE_HOME/lib export PATH=$ORACLE_HOME/bin:$PATH export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data umask 022 :wq--- save and quit Now run the script using following command . db02.env Run Clonedb.sql script Clonedb.sql located in ORACLE_BASE CREATE CONTROLFILE SET DATABASE "CLONEDB" RESETLOGS NOARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE

III

MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE GROUP 1 '/u01/oracle9i/oradata/clonedb/redo01.log' SIZE 100M, GROUP 2 '/u01/oracle9i/oradata/clonedb/redo02.log' SIZE 100M, GROUP 3 '/u01/oracle9i/oradata/clonedb/redo03.log' SIZE 100M -- STANDBY LOGFILE DATAFILE '/u01/oracle9i/oradata/clonedb/system01.dbf', '/u01/oracle9i/oradata/clonedb/undotbs01.dbf', '/u01/oracle9i/oradata/clonedb/cwmlite01.dbf', '/u01/oracle9i/oradata/clonedb/drsys01.dbf', '/u01/oracle9i/oradata/clonedb/example01.dbf', '/u01/oracle9i/oradata/clonedb/indx01.dbf', '/u01/oracle9i/oradata/clonedb/odm01.dbf', '/u01/oracle9i/oradata/clonedb/tools01.dbf', '/u01/oracle9i/oradata/clonedb/users01.dbf', '/u01/oracle9i/oradata/clonedb/xdb01.dbf' CHARACTER SET WE8ISO8859P1 ; Login as SYSDBA Command: Sqlplus ‘/as sysdba’ Startup the database in nomount mode Command: Startup nomount

Command: @Clonedb.sql Controlfile created. SQL > ALTER DATABASE OPEN RESETLOGS; Database altered. SQL> ALTER TABLESPACE TEMP ADD TEMPFILE IV

'/u01/oracle9i/oradata/clonedb/temp01.dbf' SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; Tablespace altered. Obervations after installation Oracle User: oracle9i Database/ SID name: clonedb Global SID name: clonedb.us.oracle.com LISTENER name: LISTENER Spfile/Pfile naming convention: spfileclonedb.ora/initclonedb.ora Location of Script: /u01/oracle9i/clonedb01.sql Location of Software: /u01/oracle9i/product Naming convention of Env file: clonedb.env (/u01/oracle9i/clonedb.env) Location of Database: /u01/oracle9i/oradata Location of Web server: ORACLE_HOME/Apache/Apache/http Problems faced When i ran the command ALTER DATABASE OPEN RESET LOGS; ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/oracle9i/oradata/db01/system01.dbf' When i ran clonedb.sql script. ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-00200: controlfile could not be created ORA-00202: controlfile: '/u01/oracle9i/oradata/clonedb/control01.ctl' ORA-27038: skgfrcre: file exists SQL> @clonedb01.sql SP2-0734: unknown command beginning "need to re..." - rest of line ignored. SP2-0158: unknown SET option "#1." SP2-0734: unknown command beginning "The follow..." - rest of line ignored. SP2-0734: unknown command beginning "to open th..." - rest of line ignored. SP2-0734: unknown command beginning "Data used ..." - rest of line ignored. SP2-0734: unknown command beginning "be require..." - rest of line ignored. SP2-0044: For a list of known commands enter HELP and to leave enter EXIT. SP2-0734: unknown command beginning "only if th..." - rest of line ignored.

V

CREATE CONTROLFILE SET DATABASE "CLONEDB" NORESETLOGS NOARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01223: RESETLOGS must be specified to set a new database name ORA-01507: database not mounted SP2-0734: unknown command beginning "Database c..." - rest of line ignored. ALTER DATABASE OPEN * ERROR at line 1: ORA-01507: database not mounted SP2-0734: unknown command beginning "Commands t..." - rest of line ignored. SP2-0734: unknown command beginning "Online tem..." - rest of line ignored. SP2-0734: unknown command beginning "Other temp..." - rest of line ignored. # SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M * ERROR at line 2: ORA-00911: invalid character

VI