11 Replies Latest reply: Feb 14, 2013 11:24 PM by Hemant K Chitale RSS

    Disaster recovery

    845641
      This is 11G R2 on Oracle Linux 5.6 , I cant understand that i deleted all the datafiles, controlfile and logfiles, when i restart and do the following why system datafile is searched, isnt it searched in open state ?
      SQL> startup force nomount
      ORACLE instance started.
      
      Total System Global Area  313159680 bytes
      Fixed Size                  2212936 bytes
      Variable Size             125832120 bytes
      Database Buffers          180355072 bytes
      Redo Buffers                4759552 bytes
      SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
          MAXLOGFILES 16
          MAXLOGMEMBERS 3
          MAXDATAFILES 100
          MAXINSTANCES 8
          MAXLOGHISTORY 292
      LOGFILE
        GROUP 1 '/u01/app/oracle/oradata/test/redo01.log'  SIZE 50M BLOCKSIZE 512,
        GROUP 2 '/u01/app/oracle/oradata/test/redo02.log'  SIZE 50M BLOCKSIZE 512,
        GROUP 3 '/u01/app/oracle/oradata/test/redo03.log'  SIZE 50M BLOCKSIZE 512
      -- STANDBY LOGFILE
      DATAFILE
        '/u01/app/oracle/oradata/test/system01.dbf',
        '/u01/app/oracle/oradata/test/sysaux01.dbf',
        '/u01/app/oracle/oradata/test/undotbs01.dbf',
        '/u01/app/oracle/oradata/test/users01.dbf',
        '/u01/app/oracle/oradata/test/example01.dbf'
      CHARACTER SET WE8MSWIN1252
      ;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19  
      CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
      *
      ERROR at line 1:
      ORA-01503: CREATE CONTROLFILE failed
      ORA-01565: error in identifying file '/u01/app/oracle/oradata/test/system01.dbf'
      ORA-27037: unable to obtain file status
      Linux-x86_64 Error: 2: No such file or directory
      Additional information: 3
      Edited by: 842638 on Feb 14, 2013 7:38 PM
        • 1. Re: Disaster recovery
          Hemant K Chitale
          The CREATE CONTROLFILE statement verifies the presence of the datafiles. Datafiles must exist for the CREATE CONTROLFILE to succeed.

          (yes, I have seen references on forums and the internet which recommend a CREATE CONTROLFILE statement .... I wonder if the people who recommend a CREATE CONTROLFILE have ever tested it in the absence of one or more datafiles -- or even looked at the documentation on the CREATE CONTROLFILE statement. The documentation on CREATE CONTROLFILE, under the datafile clause states "Specify the data files of the database. You must list all data files. _These files must all exist_, although they may be restored backups that require media recovery.")


          Hemant K Chitale
          • 2. Re: Disaster recovery
            Krishna-Oracle
            Hi,
            You have deleted all the db files
            You need to create new database. control file creation will look for datafiles mentioned in control file creation statement.
            Instead create control file statement, execute create database command

            Thanks,
            Krishna
            • 3. Re: Disaster recovery
              845641
              thanks for ur reply hemant, So you mean disaster recovery happened ? And for that we need to get the datafiles from somwhere and put them in their respective locations, right ? and also the redo log files ?? Now hemant i hope you mean that there is no way to rescue fromd isaster recovery, means to create controlfile you need datafiles , i was testing to see if i loose all files what should happen
              • 4. Re: Disaster recovery
                CKPT
                842638 wrote:
                thanks for ur reply hemant, So you mean disaster recovery happened ? And for that we need to get the datafiles from somwhere and put them in their respective locations, right ? and also the redo log files ?? Now hemant i hope you mean that there is no way to rescue fromd isaster recovery, means to create controlfile you need datafiles , i was testing to see if i loose all files what should happen
                Disaster Recovery word terminology can be any way. Usually If database is not available then suppose you are going to restore control file + database from the RMAN backups.

                And the procedure you are using, This is applicable if you lost any control files or either corrupted. Hemanth already provided answer you must have all the datafiles before you create control file.


                DATAFILE Clause
                Specify the data files of the database. You must list all data files. These files must all exist, although they may be restored backups that require media recovery.

                i suggest you to go through with this link http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_5003.htm

                Some information from the above link:-

                DATAFILE Clause
                Specify the data files of the database. You must list all data files. These files must all exist, although they may be restored backups that require media recovery.
                • 5. Re: Disaster recovery
                  Hemant K Chitale
                  In a Disaster Recovery, the proper course of action is to start with RESTORE CONTROLFILE. That does not require the datafiles to be present.
                  After the controlfile is restored you can proceed with ALTER DATABASE MOUNT and then RESTORE DATABASE.


                  Hemant K Chitale
                  • 6. Re: Disaster recovery
                    845641
                    To restore datafiles from rman you need to mount the database, My datafiles are gone suppose, disk crashed lets say, to mount the database i need to create control file , to create the controlfile i need the datafiles on their respective locations, So CKPT :) what do you suggest should take image copies in future to avoid this situation ?? so that i can place them on their locations when i will create controlfile in case i loose all of them ...
                    • 7. Re: Disaster recovery
                      Hemant K Chitale
                      to mount the database i need to create control file
                      To MOUNT the database, you need to RESTORE the controlfile -- from a controlfile backup.

                      If you have RMAN Full Database backups, these would automatically include controlfile backups.
                      If you have configured CONTROLFILE AUTOBACKUP ON, there would be controlfile backups created with database backups and any changes to the database structure (new tablespace / datafiles added)
                      If you are running your own backup scripts, these scripts should include controlfile backups.

                      Bottom line : You need to have controlfile backups -- in most cases controlfile backups are created.


                      Hemant K Chitale
                      • 8. Re: Disaster recovery
                        CKPT
                        842638 wrote:
                        To restore datafiles from rman you need to mount the database, My datafiles are gone suppose, disk crashed lets say, to mount the database i need to create control file , to create the controlfile i need the datafiles on their respective locations,
                        You cannot have option to create control file, when you dont have anything with you. You have to restore only from Backups.
                        , So CKPT :) what do you suggest should take image copies in future to avoid this situation ?? so that i can place them on their locations when i will create controlfile in case i loose all of them ...
                        Either way, Taking image copies then create controlfile, which is time taking[lot of manual work]. Use RMAN backups to restore Control file + Data files, which makes your life simpler.
                        • 9. Re: Disaster recovery
                          845641
                          I know that hemant , and you are right, but i was taking the worst case scenario of lossing the controlfile backup also, and also with create controlfile command once the controlfile is created database is mounted, thats what i meant... but anyways, if something more comes out please tell me, have a nice day all of you
                          • 10. Re: Disaster recovery
                            Hemant K Chitale
                            the worst case scenario of lossing the controlfile backup also
                            That's like losing the database backup.

                            We have to assume that controlfile backup and database backup are present. If you don't have the database backup, any action plan is useless.


                            Hemant K Chitale
                            • 11. Re: Disaster recovery
                              845641
                              Thanks a lot hemant, i appreciate your time , and thanks CKPT