8 Replies Latest reply: Sep 30, 2013 9:46 PM by Hemant K Chitale RSS

    Control file backup

    SamikAcharya

      I have executed the following commands as I was not sure about the control file backup  :-

       

      1) ALTER DATABASE BACKUP CONTROLFILE TO  '/DBJPC/control.bkp';

      2)ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

       

      could anybody tell me whether these two statements override  my database's previous configuration of control file backup or not (I think yes...) ??? If yes, is there any chances of database non-availability??? Finally, is there any drawbacks to run  these two commands ...

        • 1. Re: Control file backup
          sybrand_b

          You should always include your four digit version number and platform, as almost all questions have a version dependent answer.

          Also your question is incomplete wrt to the following

           

          1 are you using RMAN for backups

          2 if so, do you have CONTROLFILE AUTOBACKUP ON

          3 Do you realize this are two different commands which have a different purpose. You can read about this in the online documentation.

          4 If you read documentation, why you assume 'these two statements override my database previous configuration of controlfile backup'. They DON'T.

           

          ------------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: Control file backup
            DK2010

            Hi,

             

            You can find the first command backup at  /DBJPC/ directory  with specific name you mentioned

            however second command backup goes to udump (user_dump_dest) directory and name like some <sid>_ora_<pid>

            >> is there any chances of database non-availability???

            NO

            HTH

            • 3. Re: Control file backup
              SamikAcharya

              Sir,

              I'm new in Oracle and using Oracle 10G  Enterprise Edition (10.2.0.1.0)  on Red Hat Linux 32bits OS. I worked as a J2EE developer and there is no DBA in my office right now. So, I'm working as a stop gap person.

               

              I cann't find whether RMAN is used for control file back up or not and that's the reason to execute those queries. Are these files (backup of control files) automatically backed up or not (I think not).

               

              Lastly, in case of damage/lost control files, this command will work or not-

              cp    '/DBJPC/control.bkp'   <location of control file>;

               

              Thankx for your previous advise.....

              • 4. Re: Control file backup
                SamikAcharya

                Sir,

                I'm new in Oracle and using Oracle 10G  Enterprise Edition (10.2.0.1.0)  on Red Hat Linux 32bits OS. I worked as a J2EE developer and there is no DBA in my office right now. So, I'm working as a stop gap person.

                 

                I cann't find whether RMAN is used for control file back up or not and that's the reason to execute those queries. Are these files (backup of control files) automatically backed up or not (I think not).

                 

                Lastly, in case of damage/lost control files, this command will work or not-

                cp    '/DBJPC/control.bkp'   <location of control file>;

                 

                Thankx for your previous advise.....

                • 5. Re: Control file backup
                  DK2010

                  Hi

                   

                  >>I'm new in Oracle and using Oracle 10G  Enterprise Edition (10.2.0.1.0)  on Red Hat Linux 32bits OS. I worked as a J2EE developer and there is no DBA in my office right now. So, I'm working as a stop gap person.

                   

                  In that case you should not think about the recovery scenario.. However you can play with  your test database on your personal m/c. but before doing this please read first.

                   

                  >>I cann't find whether RMAN is used for control file back up or not and that's the reason to execute those queries. Are these files (backup of control files) automatically backed up or not (I think not).

                  Connect with RMAN and the type the command

                  RMAN> SHOW ALL;

                   

                  >>Lastly, in case of damage/lost control files, this command will work or not- cp    '/DBJPC/control.bkp'   <location of control file>;

                   

                  No, It wont work.. these are the trace file format(readable) while your database control file are binary

                   

                  Start from here :Backup and Recovery

                   

                  HTH

                  • 6. Re: Control file backup
                    sybrand_b

                    contrary to what you say

                    alter database backup controlfile

                    produces a BINARY file.

                     

                    alter database backup controlfile to trace

                    produces a file with a create controlfile statement in ascii.

                     

                    -----------

                    Sybrand Bakker

                    Senior Oracle DBA

                    • 7. Re: Control file backup
                      Aman....

                      SamikAcharya wrote:

                       

                      I have executed the following commands as I was not sure about the control file backup  :-

                       

                      1) ALTER DATABASE BACKUP CONTROLFILE TO  '/DBJPC/control.bkp';

                      2)ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

                       

                      could anybody tell me whether these two statements override  my database's previous configuration of control file backup or not (I think yes...) ??? If yes, is there any chances of database non-availability??? Finally, is there any drawbacks to run  these two commands ...

                      1) This would create for you a binary copy of the control file which you can, very well use for the recovery of the same.

                      2) This would be creating a text file for you which also would be used in the recovery of the control file. The file would contain the command to recreate the control file.

                       

                      Don't think, execute the commands and see for yourself as that's not hard to do. As you said you are not a DBA so it would be better that you don't make assumptions and test things. Why would creating a backup of the control file would make your database unavailable? And what would be the drawback in running a command which is meant to run-especially when that command is creating a backup for you?

                       

                      Aman....

                      • 8. Re: Control file backup
                        Hemant K Chitale

                        >Are these files (backup of control files) automatically backed up or not (I think not).

                        No, they are not.

                         

                        > in case of damage/lost control files, this command will work or not-   cp    '/DBJPC/control.bkp'   <location of control file>;


                        If you lose only one controlfile (your database should have two or more), you would

                        1. Shutdown the database instance

                        2. Copy the good controlfile to the location of the lost / corrupted controlfile

                        3. Startup the database instance

                        You do NOT need to restore the controlfile from a backup

                         

                         

                        If, however, you lose ALL controlfiles, you can either

                        a. Use the CREATE CONTROLFILE script  from the trace file you generated with the ALTER DATABASE BACKUP CONTROLFILE TO TRACE and recreate the controlfile  (assuming that there were no new datafiles added or no tablespaces dropped between the time of the backup and the loss of the controlfiles)

                        OR

                        b. restore the controlfile backup and recover the database using backup controlfile

                        Option a. is preferred.

                        Note that in this case, you would lose information about RMAN backups which is maintained in the controlfile.

                         

                        Hemant K Chitale