Back to basic.
The cold backup is the easiest backup that could be performed.
It can’t really be used in a backup strategy for businesses with critical systems because there is a risk of loss of data up to 24h.
It is useful anyway to know how to perform it because it makes duplicating a database useful and can save lives sometimes.
Perform a cold backup
A cold back requires a clean shutdown of the database.
shutdown immediate or normal are the only shutdown allowed.
A database cold backed up after a shutdown abort will result into fuzzy files which can’t be recovered.
The reason is that the datafiles must have had a checkpoint performed (which is done with shutdown immediate).
When the clean shutdown is done, copy the oradata folders to a new location.
After the files are copied, the database can be started up.
Recover from a cold backup
Depending on the structure given on the server to recover, the recovery can involve renaming datafiles and controlfiles.
1) If the service doesn’t exist yet, issue the oradim command:
oradim -NEW -SID SAME_AS_BACKED_UP_DB -pfile FULLPATH\init.ora -syspass somepassword
If you have taken the measure to copy the password file and initialization parameter from your primary server to the server to recover in ORACLE_HOME\database, then the options -pfile and -syspass are not required.
2) Copy the oradata folders to the exact same location from primary to the server to recover.
If the oradata files are located in the same drives, same folder structure, the recovery stops here and a startup can be issued.
3) If their are hard disk constraints (more drive on primary than on recovered server or not enough disk space), it must be required to move some datafiles from one drive to another.
In the case a data file has been moved to new location, the database must be started in mount mode:
C:\set oracle_sid= mySID
C:\sqlplus / as sysdba
SQL> startup mount; (if the database doesn’t mount, the location to thepfile can be specified by adding the parameter spfile=’LOCATION\SPFILESSID.ora’)
and a rename command must be issued for each files relocated:
SQL> alter database rename file ‘OLD_LOCATION\DATAFILE_1.ora’ to ‘NEW_LOCATION\DATAFIE_1.ora’;
SQL>alter database datafile ‘NEW_LOCATION\DATAFILE1.ora’ online;
SQL>alter database open;
Hi Nico,
Your post is outstanding! I was able to recreate a restored datafile to another new set of server using your steps and a little scouring on the internet which when you relocate the datafiles to another drive or path one should recreate a new set of control files by running “alter database backup controlfile to trace;” from the original server.
Now i know how very important are the control files are in oracle db. Oracle database are not for a faint of heart…
god bless.
KENTOY
Hi Kentoy,
the step you mention is not really necessary.
With my method, I would copy the original control files from production to the server to duplicate.
The secret lies into the command ‘alter database rename file’.
That command would modify the control files accordingly to take into account the new location. That’s what I do in the end of this article.
I’m happy that it worked out for you : )
Thanks for the nice comment.
Nico
Nicolas., the new look of ur blog is superb., and this cold back topic was very much useful for me.,
Hi Diva.
Thanks a lot for your comment.
I’ve changed the look because I wanted to have a larger column for the content. Sometimes I got paths described and it goes on 2 lines.
It’s better like this.
I’m glade the cold backup explanation worked out for you.
If you are on Oracle you might want to switch to archivelog mode and perform hot backups instead. They are much more reliable.
See you.
Nico
very interesting, but I don’t agree with you
Idetrorce
Hi,
nice article you done good job.
regards
sreeni
Nice Article.
I don’t think only oradata folder is sufficient for a cold backup.
Right.
You would need to backup the control files and the spfile for a complete backup.
Nico
Hello ,
Nico, I am yet to implement the cold backup steps, but I wish to know one thing. Where can I find all the Oradata folders – are they in one folder or are scattered in the Oracle home directory. Is it possible to give me a hint?
regards
Hello Nico,
I refer to your answer to “Prashant Pathak”
“You would need to backup the control files and the spfile for a complete backup.”
I am a newbie when it comes to Oracle bakup.
Question 1: How do I backup a control file and an spfile – by just making a copy of them?
Question 2: Path to these files would be greatly appreciated.
Regards
Hi.
If you got an administrator user,
you can query the location of all your data files with the following table: dba_data_file (or something close) I don’t have oracle anymore but googling in that direction should yield good results ^^
The way data files are organized is very arbitrary. I refer you in return to the article I wrote a while ago on this same blog:
http://thetendjee.wordpress.com/2006/08/03/to-the-developer-turned-dba-plan-a-little-bit/
If you have created a default database using a wizard, then your oracle data files (oradata) are most probably located on C:\oracle\oradata (most generally [oracle_home]\oradata).
For the controlfile and spfile, if you use spfile, I assumre you use oracle 10g.
If you use oracle 10g, you should be able to connect to recovery manager that way:
1) Either you are logged on the server with a username belonging to ora_dba group:
set oracle_sid = [oraclesid]
rman target / as sysdba
2) You can use a user with rights to connect to rman
then connect using rman username/password@[some tnsnames.ora connection]
using rman you can perform cold and hot backups. You should run hot backups. YOu have no loss of data if it’s not a hardware problem (24h data loss max for cold backup)
here’s an rman script for cold backup in any cases. Got that on some site:
run {
# Shutdown database for backups and put into MOUNT mode
shutdown immediate
startup mount
# Allocate channel. Important: This must be done after
# database has been mounted!!!
allocate channel t1;
# Perform full database backup
backup full format “%d_FULL_%U” (database) ;
# Open database after backup complete
sql ‘alter database open’;
backup current controlfile;
sql ‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’;
release channel t1;
}
good luck
Nico
Thank you. I will try it out
thanks for this useful post.