Tuesday, 5 August 2014

Clone Oracle 11g database using cold backup


Prerequisite: U should have valid Cold backup of your all *.dbf Datafiles and control file.

1. Create pfile for new database. Using copy the existing pfile. Using a spfile. SQL> create pfile=’init.ora’ from spfile; Create a new pfile in the $ORACLE_HOME/dbs directory. The new pfile will need to be edited as per new enviornment.

2. Create the controlfile. SQL>alter database backup controlfile to trace as ‘/home/oracle/control.sql’; The .sql file requires editing. Remove all lines from the top of the file up to first STARTUP NOMOUNT line. Remove all lines from the second ‘STARTUP MOUNT’ line till end. Remove any lines that start with — and #. Remove any blank lines. Remove the line ‘RECOVER DATABASE USING BACKUP CONTROLFILE’ Remove the line ‘ALTER DATABASE OPEN RESETLOGS;’ Make a copy of the ‘ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u02/accounts/repodb/oradata/temp01.dbf’ SIZE 524288000 REUSE AUTOEXTEND OFF;’ lines and then remove them from the file. In the ‘CREATE CONTROLFILE’ line the word ‘REUSE’ needs to be changed to ‘SET’. The database name needs changed with new database name. Change the file paths if any. Change the Log mode archivelog/noarchivelog. e.g
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "REPODB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8 MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/repodb/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/repodb/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/repodb/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/repodb/system01.dbf',
'/u01/app/oracle/oradata/repodb/sysaux01.dbf',
'/u01/app/oracle/oradata/repodb/undotbs01.dbf',
'/u01/app/oracle/oradata/repodb/users01.dbf',
'/u01/app/oracle/oradata/repodb/example01.dbf'
CHARACTER SET WE8MSWIN1252 ;

3. Create the Enviornment File or make new entry in oratab and source the environment. Enviornment File: export ORACLE_SID=repodb export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0 export PATH=$PATH:$ORACLE_HOME/bin oratab: $cd /etc/ vi oratab and make entry in oratab in below format. $ORACLE_SID:$ORACLE_HOME:: Now check if below command shows your database name. $echo $ORACLE_SID

4. Create password file for sqlplus. Use the orapwd utility orapwd file=initrepodb password=*******

5. Start The Database Cloning process. $sqlplus “/ as sysdba” @”/home/oracle/control.sql”; SQL>Control file created.

6. Open Database. SQL>Alter Database open resetlogs;
7. Create temp files. Using the ‘ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u02/accounts/repodb/oradata/temp01.dbf’ SIZE 524288000 REUSE AUTOEXTEND OFF;’ command from step 3.

8. Check Database parameters status Check that the database has opened with the ‘OPEN’ Status. SQL>select status from v$instance; All datafiles Should be ok.i.e Output only be ONLINE and SYSTEM. SQL>select distinct status from v$datafile; Check if alert log are generating.

9. Set new global Database name SQL>alter database rename global_name to ;

10. Create a spfile. SQL>create spfile from pfile;

11. Change the database ID If you are using RMAN then the database ID must be changed. SQL>shutdown immediate; SQL>startup mount; SQL>exit; $nid target=/ Enter ‘Y’. SQL>shutdown immediate SQL>startup mount SQL>alter database open resetlogs

12. Configure TNS Add entries for new database in the listener.ora and tnsnames.ora.

Photo: Clone Oracle 11g database using cold backup Prerequisite: U should have valid Cold backup of your all *.dbf Datafiles and control file. 1. Create pfile for new database. Using copy the existing pfile. Using a spfile. SQL> create pfile=’init.ora’ from spfile; Create a new pfile in the $ORACLE_HOME/dbs directory. The new pfile will need to be edited as per new enviornment. 2. Create the controlfile. SQL>alter database backup controlfile to trace as ‘/home/oracle/control.sql’; The .sql file requires editing. Remove all lines from the top of the file up to first STARTUP NOMOUNT line. Remove all lines from the second ‘STARTUP MOUNT’ line till end. Remove any lines that start with — and #. Remove any blank lines. Remove the line ‘RECOVER DATABASE USING BACKUP CONTROLFILE’ Remove the line ‘ALTER DATABASE OPEN RESETLOGS;’ Make a copy of the ‘ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u02/accounts/repodb/oradata/temp01.dbf’ SIZE 524288000 REUSE AUTOEXTEND OFF;’ lines and then remove them from the file. In the ‘CREATE CONTROLFILE’ line the word ‘REUSE’ needs to be changed to ‘SET’. The database name needs changed with new database name. Change the file paths if any. Change the Log mode archivelog/noarchivelog. e.g STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "REPODB" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/repodb/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/repodb/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/repodb/redo03.log' SIZE 50M BLOCKSIZE 512 DATAFILE '/u01/app/oracle/oradata/repodb/system01.dbf', '/u01/app/oracle/oradata/repodb/sysaux01.dbf', '/u01/app/oracle/oradata/repodb/undotbs01.dbf', '/u01/app/oracle/oradata/repodb/users01.dbf', '/u01/app/oracle/oradata/repodb/example01.dbf' CHARACTER SET WE8MSWIN1252 ; 3. Create the Enviornment File or make new entry in oratab and source the environment. Enviornment File: export ORACLE_SID=repodb export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0 export PATH=$PATH:$ORACLE_HOME/bin oratab: $cd /etc/ vi oratab and make entry in oratab in below format. $ORACLE_SID:$ORACLE_HOME:: Now check if below command shows your database name. $echo $ORACLE_SID 4. Create password file for sqlplus. Use the orapwd utility orapwd file=initrepodb password=******* 5. Start The Database Cloning process. $sqlplus “/ as sysdba” @”/home/oracle/control.sql”; SQL>Control file created. 6. Open Database. SQL>Alter Database open resetlogs; 7. Create temp files. Using the ‘ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u02/accounts/repodb/oradata/temp01.dbf’ SIZE 524288000 REUSE AUTOEXTEND OFF;’ command from step 3. 8. Check Database parameters status Check that the database has opened with the ‘OPEN’ Status. SQL>select status from v$instance; All datafiles Should be ok.i.e Output only be ONLINE and SYSTEM. SQL>select distinct status from v$datafile; Check if alert log are generating. 9. Set new global Database name SQL>alter database rename global_name to ; 10. Create a spfile. SQL>create spfile from pfile; 11. Change the database ID If you are using RMAN then the database ID must be changed. SQL>shutdown immediate; SQL>startup mount; SQL>exit; $nid target=/ Enter ‘Y’. SQL>shutdown immediate SQL>startup mount SQL>alter database open resetlogs 12. Configure TNS Add entries for new database in the listener.ora and tnsnames.ora.