Database cloning using cold backup

The following are the steps for performing database cloning using cold backup

Assumptions : You are using Linux flavour OS and following same directory structure

1. Take the cold backup of source database

2. Take controlfile trace and pfile or spfile (that was using by the source database)

3. Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database

4. Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp

5. Place pfile or spfile in “dbs” directory on target

6. Copy the remaining files to their respective locations (If any directories are missing, do create them)

7. Open bash_profile file and set ORACLE_HOME and ORACLE_SID

8. Connect as sysdba and Startup the database

Database cloning with cold backup when using diff directory structure

Hi Folks, Long back i posted steps for database cloning using cold backup. In that post, we assumed that directory structure is same on both the server (ofcourse OS version also :-))

Now, the below steps will let you understand how we can perform database cloning using cold backup, when you are not following the same directory structure in the target machine (Remember, here also OS is same)

1. Take the cold backup of source database

2. Take controlfile trace and pfile or spfile (that was using by the source database)

3. Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database

4. Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp
Note: Not necessary in copying control files

5. Place pfile or spfile in “dbs” directory on target

6. Copy the remaining files to their respective locations (If any directories are missing, do create them)

7. Open bash_profile file and set ORACLE_HOME and ORACLE_SID

8. Connect as sysdba and Startup the database in nomount stage

9. Edit the trace file (that was copied) and generate a create controlfile script from it. Modify the script and specify the new locations of  the files.

10. Execute the controlfile script which will create controlfiles in the location specified in CONTROL_FILES parameter. Once control files are created, database will be forwarded to MOUNT state.

11. Finally, Open the database.

Database cloning with hot backup (if directory structure is same)

Here are the steps for performing database cloning using hot backup to a different server. I am assuming you are using same directory structure in the target server also.
Assumed Oracle version : 10.2.0.4, OS version : Linux 5

Step 1 : Take database hot backup as follows
sql> alter system switch logfile;
sql> alter database begin backup;
$ cp *.dbf to backup location (as it is hot backup, we will not take backup of redolog files)
sql> alter database end backup;
sql> alter system switch logfile;
$ cp *.ctl to backup location
Note: If you are using 9i database, use “tablespace begin backup/end backup” clauses

step 2 : Take backup of spfile or pfile of source database and also archives

step 3 : Install oracle software in target server (select “software only” option in OUI)

step 4 : copy the files to target server either using FTP or any methods

step 5 : place pfile or spfile in dbs directory

step 6 : copy all files (datafiles, controlfiles and archives) to respective locations

step 7 : do the following
sql> startup nomount
sql> alter database mount;
sql> recover database using backup controlfile until cancel;
here it will ask to apply archives and will give suggestion as file name and path. apply required archives

step 8 : finally, open your database with resetlogs option
sql> alter database open resetlogs;

Sometimes you may get following error while doing hot backup cloning

ORA-01194 file 1 needs more recovery to be consistent error

in such cases, do a switch logfile in source server and copy & apply that archive logfile in target server

Database cloning using hot backup (if directory structure is different)

Below steps helps you in performing database cloning using hot backup
Assumptions:
1. directory structure is different in both source and target servers
2. Oracle version : 10.2.0.4
3. OS version : Linux 5
4. target database name is same as source database name

step 1 :  Take the hot backup of source database
sql> alter database begin backup;
$ copy datafiles to backup location
sql> alter database end backup;

step 2 : Take controlfile trace and pfile or spfile (that was using by the source database)

step 3 : Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database

step 4 : Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp
Note: Not necessary in copying control files and redologfiles

step 5 : Place pfile or spfile in “dbs” directory on target

step 6 : Copy the remaining files to their respective locations (If any directories are missing, do create them)

step 7 : Connect as sysdba and Startup the database in nomount stage

step 8 : Edit the trace file (that was copied) and generate a create controlfile script from it. Modify the script and specify the new locations of  the files.

step 9 : Execute the controlfile script which will create controlfiles in the location specified in CONTROL_FILES parameter. Once control files are created, database will be forwarded to MOUNT state.
sql> @create_controlfile.sql

step 10 : Finally, Open the database with resetlogs option
sql> alter database open resetlogs;

How to solve ORA-19505: failed to identify file in RMAN?

DBA’s will feel bad when they got any error. It’s common for any :)

Lets say you got below error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch00 channel at 01/17/2011 13:01:03
ORA-19505: failed to identify file “/dwh1/oraarch/arch/1_1100721_664058960.dbf”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Don’t be panic, nothing happened to your backup or database. If you read the error, its saying particular archive logfile is not available. This may be due to file deleted at OS level due to some reason.

In this situation, do the following

1. Run crosscheck command against archivelogs

RMAN> crosscheck archivelog all;

2. If you find any archives marked as EXPIRED, then delete those expired archives using below command

RMAN> delete expired archivelog all;
This will prompt you YES or NO. If you don’t want a prompt, use below command

RMAN> delete noprompt archivelog all;

3. Take a fresh archivelog backup

RMAN> backup archivelog all; (you  can use delete input clause also)