29
Aug
07

How to perform and recover from a cold backup

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;

Advertisements

27 Responses to “How to perform and recover from a cold backup”


  1. 1 KENTOY
    September 28, 2007 at 6:24 pm

    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

  2. October 3, 2007 at 2:25 am

    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

  3. 3 Diva
    October 31, 2007 at 11:11 am

    Nicolas., the new look of ur blog is superb., and this cold back topic was very much useful for me.,

  4. November 1, 2007 at 3:41 am

    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

  5. 5 Idetrorce
    December 15, 2007 at 3:16 pm

    very interesting, but I don’t agree with you
    Idetrorce

  6. 6 sreenivas
    July 7, 2008 at 11:19 am

    Hi,

    nice article you done good job.

    regards
    sreeni

  7. 7 Prashant Pathak
    August 31, 2008 at 2:10 pm

    Nice Article.
    I don’t think only oradata folder is sufficient for a cold backup.

  8. August 31, 2008 at 2:24 pm

    Right.

    You would need to backup the control files and the spfile for a complete backup.

    Nico

  9. 9 jojo
    January 13, 2009 at 7:30 am

    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

  10. 10 jojo
    January 13, 2009 at 7:35 am

    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

  11. January 13, 2009 at 11:49 am

    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:
    https://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

  12. 12 jojo
    January 13, 2009 at 9:24 pm

    Thank you. I will try it out

  13. 13 Dorin
    March 24, 2009 at 5:54 am

    thanks for this useful post.

  14. 14 SHABBIR
    February 2, 2010 at 10:25 am

    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

  15. 15 jhony
    April 15, 2010 at 10:35 am

    ne pooku laga undi answer

  16. 16 syed ali raza zaidi
    December 8, 2011 at 7:33 pm

    you described every thing very nice.

    I want to know how to recover if you have only data files.
    no other files.

  17. 17 Godfrey
    February 8, 2012 at 12:35 pm

    I got an ORA 600 After doing the above steps.

  18. December 9, 2012 at 1:24 am

    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;

  19. February 18, 2013 at 4:36 am

    At this moment I am ready to do my breakfast,
    after having my breakfast coming again to read other news.

  20. April 19, 2013 at 10:05 pm

    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

  21. April 23, 2013 at 11:51 pm

    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!

  22. April 25, 2013 at 4:36 pm

    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.

  23. April 26, 2013 at 1:49 pm

    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!

  24. July 19, 2013 at 5:59 am

    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.

  25. July 24, 2014 at 8:07 pm

    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!

  26. September 20, 2014 at 11:42 pm

    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!

  27. 27 rizwan
    July 15, 2015 at 3:27 pm

    Great article.
    it helped me lot.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: