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:
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.
Hi All,
I got an backup of controlfile,redo files & datafile i want to create a database & want to import that file in that database…..may i know how can i do…steps to recover the data from thoes files……
thanks in advance
ne pooku laga undi answer
you described every thing very nice.
I want to know how to recover if you have only data files.
no other files.
I got an ORA 600 After doing the above steps.
First we have to take a missing Datafile offline. and we have recover the datafile see here example
SQL>recover datafile number(missing datafile number) or recover tablespace tablespace name;
At this moment I am ready to do my breakfast,
after having my breakfast coming again to read other news.
Fascinating blog! Is your theme custom made or did you download
it from somewhere? A theme like yours with a few simple adjustements would really make my blog jump out.
Please let me know where you got your design.
Bless you
Hi there! This blog post could not be written any better!
Going through this article reminds me of my previous roommate!
He continually kept preaching about this. I’ll send this information to him. Fairly certain he will have a great read. Thanks for sharing!
naturally like your web-site however you need to take a look
at the spelling on several of your posts.
Many of them are rife with spelling issues
and I find it very bothersome to inform the truth nevertheless I’ll certainly come again again.
My brother suggested I might like this web site. He was
totally right. This post actually made my day. You cann’t imagine just how much time I had spent for this info! Thanks!
Now, that this Torrent sites are under threat of death
it’s entirely likely how the cycle will repeat itself and Torrents will likely be replaced by something else. In internal retention, pulll the abdominal organs in or higher, and simultaneously bring the reduced spine forward. It serves as a sanctuary for tigers, big and small mammals, deer, reptiles, birds, along with a herd in the last known elephants in captivity.
Hi there! This blog post couldn’t be written much
better! Going through this article reminds me of my previous roommate!
He always kept preaching about this. I’ll send this article to him.
Pretty sure he’ll have a good read. Many thanks for sharing!
Greetings from Colorado! I’m bored to death at
work so I decided to check out your site on my iphone during lunch break.
I really like the knowledge you provide here and can’t wait to
take a look when I get home. I’m shocked at how quick your blog loaded
on my cell phone .. I’m not even using WIFI, just 3G ..
Anyhow, good site!
Great article.
it helped me lot.