backup control file

Posted on 2009-04-24
Last Modified: 2013-11-16
I need to backup my control file in oracle and recover it back if any issues occur, can any one provide me with the commands required for backing up and control file and recovering it when necessary
Question by:saravanan_k
    LVL 2

    Assisted Solution

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    the statement would be, with either sql plus or rman:
    alter database backup controlfile to trace;

    Open in new window

    LVL 2

    Expert Comment

    U can see the entire syntax in that url..

    Hope this helps u...

    LVL 68

    Accepted Solution

    RMAN considerations -
    If you use RMAN, you should have set
    This way the controlfile and spfile will be backed up automatically with each database/archivelog backup run.
    Else issue
    backup current controlfile;
    to backup the controlfile and spfile. Add that statement to your standard RMAN backup procedure, if not already present.
    angelIII's statement is needed in case you must have the controlfile in clear text, perhaps in order to modify it.
    The appropriate RMAN commands to restore the controlfile/spfile [to a new location] would be -

    restore spfile [TO 'filename'];  
    restore controlfile [TO 'filename'];

    LVL 47

    Expert Comment

    To backup a control file simply copy the file on a backup device.
    This device should not be placed on the computer and even be
    in the same room where the Primary DB resides.
    More interesting is when to backup the Control file.
    One hint is: before and after major changes in the logical and physical structure of the DB
    like - creating new tablespaces, dropping tablespaces, adding new files, dropping files, moving files (.dbf)
    from one location to  other  disk location.
    The reason is that the contol file keeps the location of all files of the data base.
    So some recovery scenarous need old control files (example - lost or damaged database file)
    or possible damage (lose) of the control files needs reestablising.

    Author Comment

    Thanks, can i backup the pfile, spfile with the same way that is used for  control file. can i use the following command
    backup current pfile
    backup current spfile
    LVL 68

    Expert Comment

    backup spfile;
    sorry, i forgot to mention this above.

    LVL 18

    Assisted Solution

    saravanan -
    Backing it up in a binary format:
    alter database backup controlfile to '/some/arbitrary/path';
    alter database backup controlfile to '/some/arbitrary/path' reuse;

    Backing it up in a human readable format:
    alter database backup controlfile to trace;
    alter database backup controlfile to trace as '/some/arbitrary/path';
    alter database backup controlfile to trace as '/some/arbitrary/path' reuse;

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    oem-lookup 9 37
    Limit Database Table to a Single Row 6 48
    Solar Winds Database performance Analyzer 2 61
    oracle query help 29 50’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
    "Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now