1 2 Previous Next 15 Replies Latest reply on Jul 18, 2012 2:07 AM by 949599

    Not able to resetlogs after recovery

    949599
      Hi,

      I am performing testing on the database recovery after upgrading my backup solution software. I attempted to restore from tape media that is dated back at April 1st. My colleague have perform the same recovery from the same tape media during middle of April.

      After restore, I encounter some problem and I could no longer up the database anymore.

      Please be patient with me as I am still fairly new to this.

      Regards,
      Wilson

      These are the steps I have taken to perform recovery. It is being perform when the database in mount mode

      SQL> recover database using backup controlfile until TIME '2012-04-01:21:55:00'
      ORA-00283: recovery session canceled due to errors
      ORA-19909: datafile 1 belongs to an orphan incarnation
      ORA-01110: data file 1: '/oracle/AP1/sapdata1/system_1/system.data1'

      SQL> shutdown immediate;
      ORA-01109: database not open


      Database dismounted.
      ORACLE instance shut down.
      SQL> startup;
      ORACLE instance started.

      Total System Global Area 6794772480 bytes
      Fixed Size 2078256 bytes
      Variable Size 3372222928 bytes
      Database Buffers 3405774848 bytes
      Redo Buffers 14696448 bytes
      Database mounted.
      ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-01190: control file or data file 1 is from before the last RESETLOGS
      ORA-01110: data file 1: '/oracle/AP1/sapdata1/system_1/system.data1'

      Edited by: 946596 on Jul 16, 2012 2:32 AM
        • 1. Re: Not able to resetlogs after recovery
          mBk77
          Welcome to Oracle Discussion Forums!!!

          Before posting any question you need to provide your 4 digit Database Version, your OS version.

          Did you restore the control file from the old backup...

          If not then first you need to restore the old control file....

          and post the contents of your alert log file....

          Edited by: mBk77 on Jul 16, 2012 5:28 PM
          • 2. Re: Not able to resetlogs after recovery
            a resetlogs occurred after the april backup. You need to recover past the resetlogs.
            How to do this is version dependent.

            --------------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: Not able to resetlogs after recovery
              949599
              Currently on HP-UX 11.23 using
              Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
              PL/SQL Release 10.2.0.5.0 - Production

              Edited by: 946596 on Jul 16, 2012 6:51 PM
              • 4. Re: Not able to resetlogs after recovery
                949599
                This is the log when I try to trigger alter database open resetlogs
                Tue Jul 17 09:54:47 MAL 2012
                Starting ORACLE instance (normal)
                LICENSE_MAX_SESSION = 0
                LICENSE_SESSIONS_WARNING = 0
                Picked latch-free SCN scheme 3
                LICENSE_MAX_USERS = 0
                SYS auditing is disabled
                ksdpec: called for event 13740 prior to event group initialization
                Starting up ORACLE RDBMS Version: 10.2.0.5.0.
                System parameters with non-default values:
                  processes                = 120
                  sessions                 = 137
                  event                    = 10191 trace name context forever, level 1
                  sga_max_size             = 6794772480
                  __shared_pool_size       = 1056964608
                  shared_pool_size         = 1056964608
                  filesystemio_options     = setall
                  control_files            = /oracle/AP1/origlogA/cntrl/cntlrAP1.dbf, /oracle/AP1/origlogB
                /cntrl/cntrlAP1.dbf, /oracle/AP1/sapdata1/cntrl/cntrlAP1.dbf
                  control_file_record_keep_time= 30
                  db_block_size            = 8192
                  db_cache_size            = 3405774848
                  compatible               = 10.2.0
                  log_archive_dest         = /oracle/AP1/oraarch/AP1arch
                  log_buffer               = 14469120
                  log_checkpoint_interval  = 0
                  db_files                 = 254
                  log_checkpoints_to_alert = TRUE
                dml_locks                = 4000
                  replication_dependency_tracking= FALSE
                  undo_management          = AUTO
                  undo_tablespace          = PSAPUNDO
                  _in_memory_undo          = FALSE
                  undo_retention           = 43200
                  recyclebin               = off
                  remote_os_authent        = TRUE
                  remote_login_passwordfile= EXCLUSIVE
                  job_queue_processes      = 1
                  parallel_max_servers     = 20
                  parallel_execution_message_size= 16384
                  background_dump_dest     = /oracle/AP1/saptrace/background
                  user_dump_dest           = /oracle/AP1/saptrace/usertrace
                  max_dump_file_size       = 20000
                  core_dump_dest           = /oracle/AP1/saptrace/background
                  optimizer_features_enable= 10.2.0.1
                  sort_area_size           = 2097152
                  sort_area_retained_size  = 0
                  db_name                  = AP1
                  open_cursors             = 800
                  _sort_elimination_cost_ratio= 10
                  _b_tree_bitmap_plans     = FALSE
                  star_transformation_enabled= TRUE
                  parallel_threads_per_cpu = 1
                  optimizer_index_cost_adj = 20
                  optimizer_index_caching  = 50
                  query_rewrite_enabled    = FALSE
                  _optim_peek_user_binds   = FALSE
                  pga_aggregate_target     = 4520033648
                  workarea_size_policy     = AUTO
                  statistics_level         = typical
                PMON started with pid=2, OS id=25403
                PSP0 started with pid=3, OS id=25405
                MMAN started with pid=4, OS id=25407
                DBW0 started with pid=5, OS id=25409
                LGWR started with pid=6, OS id=25411
                CKPT started with pid=7, OS id=25413
                SMON started with pid=8, OS id=25415
                RECO started with pid=9, OS id=25417
                CJQ0 started with pid=10, OS id=25419
                MMON started with pid=11, OS id=25421
                MMNL started with pid=12, OS id=25423
                Tue Jul 17 09:54:53 MAL 2012
                ALTER DATABASE   MOUNT
                Tue Jul 17 09:54:59 MAL 2012
                Setting recovery target incarnation to 1
                Tue Jul 17 09:54:59 MAL 2012
                Successful mount of redo thread 1, with mount id 2355240749
                Tue Jul 17 09:54:59 MAL 2012
                Database mounted in Exclusive Mode
                Completed: ALTER DATABASE   MOUNT
                Tue Jul 17 09:54:59 MAL 2012
                ALTER DATABASE OPEN
                ORA-1589 signalled during: ALTER DATABASE OPEN...
                Tue Jul 17 09:55:08 MAL 2012
                alter database open resetlogs
                ORA-1190 signalled during: alter database open resetlogs...
                Some unique errors.
                Thu Jul 12 18:37:20 MAL 2012
                Media Recovery Start
                WARNING! Recovering data file 1 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 2 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 3 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 4 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 5 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 6 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 7 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 8 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 9 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 10 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 11 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 17 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 18 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 19 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 20 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                WARNING! Recovering data file 21 from a fuzzy file. If not the current file
                it might be an online backup taken without entering the begin backup command.
                 parallel recovery started with 2 processes
                Thu Jul 12 18:37:20 MAL 2012
                Errors in file /oracle/AP1/saptrace/background/ap1_ora_5513.trc:
                Thu Jul 12 18:37:20 MAL 2012
                Errors in file /oracle/AP1/saptrace/background/ap1_ora_5515.trc:
                Thu Jul 12 18:37:20 MAL 2012
                Media Recovery failed with error 19907
                ORA-283 signalled during: ALTER DATABASE RECOVER  database using backup controlfile until
                TIME '2012-04-02:00:00:00'  ...
                Fri Jul 13 09:26:22 MAL 2012
                Shutting down instance (immediate)
                Edited by: 946596 on Jul 16, 2012 6:58 PM

                Edited by: 946596 on Jul 16, 2012 7:02 PM
                • 5. Re: Not able to resetlogs after recovery
                  905966
                  After recover your database using resetlogs (which your colleague already did in mid April), you should take full backup of your database immediately.
                  Once your resetlog your database, it belongs to a new incarnation, which means the backup of an earlier incarnation couldn't be used.
                  1 person found this helpful
                  • 6. Re: Not able to resetlogs after recovery
                    949599
                    I see. There is no way to reset back to the incarnation? I tried to recover the database back to the same point twice due to logistic reasons. The backup tape had to be delivered from HQ to this remote site.
                    • 7. Re: Not able to resetlogs after recovery
                      905966
                      If you can find the controlfile belonging to the earlier incarnation, I think you can make a successful recover.
                      • 8. Re: Not able to resetlogs after recovery
                        949599
                        One thing I do not understand about this, why my last incarnation date is at April 07 instead of April 2012 ?

                        RMAN> LIST INCARNATION OF DATABASE;

                        using target database control file instead of recovery catalog

                        List of Database Incarnations
                        DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
                        ------- ------- -------- ---------------- --- ---------- ----------
                        1 1 AP1 2186778316 CURRENT 1 20-APR-07

                        At OS shell, I verify the date through command and found out that the current date is correct.
                        • 9. Re: Not able to resetlogs after recovery
                          mBk77
                          ORA-19907: recovery time or SCN does not belong to recovered incarnation
                          Cause:      A point-in-time recovery to an SCN or timestamp prior to the last resetlogs was requested.
                          Action:      Either change the specified recovery time/scn, or change the recovery destination using RMAN's RESET DATABASE command.

                          Or

                          You can recreate the controlfile manually(Use resetlogs option) and then do a dummy recovery and open the database with resetlogs...
                          • 10. Re: Not able to resetlogs after recovery
                            BelMan
                            Hi ,

                            What is the last time you take a backup?
                            You can restore from last backup,because the current backup is missing archive redo logs and the database will not open until you restore the missing archive log file .
                            • 11. Re: Not able to resetlogs after recovery
                              Hemant K Chitale
                              Query V$DATAFILE and V$DATAFILE_HEADER and compare these with V$DATABASE.

                              Look for the CHECKPOINT_CHANGE#, CHECKPOINT_TIME columns and compare them.


                              Hemant K Chitale
                              • 12. Re: Not able to resetlogs after recovery
                                949599
                                Hi,

                                This problematic database is hosted at my company's Disaster Recovery site thus there is no live database and backup running. I just upgraded HP Data Protector Client, now I am testing to make sure the recovery still works after the upgrade. However, my colleague had successfully done a recovery from the same recovery tape during month of April. That means I am restoring back my database back to the state on 01-April for the second time.
                                • 13. Re: Not able to resetlogs after recovery
                                  949599
                                  Hi Hemant,

                                  could please interpret this for me ?

                                  SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE;

                                  SQL> SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE;

                                  CHECKPOINT_CHANGE# CHECKPOINT_TIME
                                  ------------------ ---------------
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12

                                  CHECKPOINT_CHANGE# CHECKPOINT_TIME
                                  ------------------ ---------------
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12
                                  514470905 01-APR-12

                                  21 rows selected.


                                  SQL> SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;

                                  CHECKPOINT_CHANGE# CHECKPOINT_TIME
                                  ------------------ ---------------
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12

                                  CHECKPOINT_CHANGE# CHECKPOINT_TIME
                                  ------------------ ---------------
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12
                                  612283742 13-JUL-12

                                  21 rows selected.


                                  SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;

                                  SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;

                                  CHECKPOINT_CHANGE#
                                  ------------------
                                  514470905
                                  • 14. Re: Not able to resetlogs after recovery
                                    Hemant K Chitale
                                    The controlfile says that the datafile are as of 01-Apr-12. But the datafiles themselves have actually been checkpointed 13-Jul-12.

                                    The database incarnation is from a resetlogs of 20-Apr-07, much older. So, there has been no resetlogs since then. The controlfile and datafiles are from the same incarnation.

                                    When you attempted "recover database using backup controlfile until TIME '2012-04-01:21:55:00'" are you sure that 21:55 is a timestamp after the last datafile checkpoint ?

                                    These warnings are curious :
                                    WARNING! Recovering data file 1 from a fuzzy file. If not the current file
                                    it might be an online backup taken without entering the begin backup command.
                                    How was the backup made ? Not using RMAN ? Using OS commands without a BEGIN BACKUP command ?


                                    Hemant K Chitale
                                    1 2 Previous Next