{"id":592,"date":"2024-04-04T11:44:51","date_gmt":"2024-04-04T11:44:51","guid":{"rendered":"https:\/\/maboc.nl\/?p=592"},"modified":"2024-04-04T11:44:51","modified_gmt":"2024-04-04T11:44:51","slug":"recovering-a-database-further-then-the-last-backupped-controlfile","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=592","title":{"rendered":"Recovering a database further then the last backupped controlfile"},"content":{"rendered":"

A question on forums.oracle.com asked: can we recover a database further in time then the time of backup of the last controlfile. Nice question…my gut feeling immediately says : YES we can. But…I do not have a proof of it, and one quick search on the internet did not supply a adequate answer. So…then I have to demonstrate it myself.<\/p>\n

Plan of attack:<\/p>\n

    \n
  1. Preparation\n
      \n
    1. Create backup\n
        \n
      1. datafiles<\/li>\n
      2. archives<\/li>\n
      3. control- and spfile autobackup<\/li>\n<\/ol>\n<\/li>\n
      4. Create some more archives\n
          \n
        1. these archives are not known to the allready backedup controlfile (step 1.1.3)<\/li>\n
        2. These archives are still on disk. They are not in the backup. (If they would be in a backup you could easily restore them :-))<\/li>\n
        3. Check which sequence is the last sequence generated<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
        4. Restore\/recover\n
            \n
          1. get the old controlfile back\n
              \n
            1. startup noumount<\/li>\n
            2. restore controlefile from backupfile<\/li>\n<\/ol>\n<\/li>\n
            3. restart the database in mount mode\n
                \n
              1. restore the database<\/li>\n<\/ol>\n<\/li>\n
              2. Recover the database until the last generated archivelog<\/li>\n
              3. alter database open resetlog<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

                Let’s see how that works:<\/p>\n

                Preparation: Backing up the database<\/strong><\/p>\n

                RMAN> backup database plus archivelog delete input;<\/span>\r\n\r\nStarting backup at 2024\/04\/04 13:13:49<\/span>\r\ncurrent log archived<\/span>\r\nusing channel ORA_DISK_1<\/span>\r\nchannel ORA_DISK_1: starting archived log backup set<\/span>\r\nchannel ORA_DISK_1: specifying archived log(s) in backup set<\/span>\r\ninput archived log thread=1 sequence=4 RECID=3 STAMP=1165410707<\/span>\r\n...\r\n...<\/span>\r\ninput archived log thread=1 sequence=15 RECID=14 STAMP=1165410739<\/span>\r\ninput archived log thread=1 sequence=16 RECID=15 STAMP=1165410832<\/span>\r\nchannel ORA_DISK_1: starting piece 1 at 2024\/04\/04 13:13:52<\/span>\r\nchannel ORA_DISK_1: finished piece 1 at 2024\/04\/04 13:13:53<\/span>\r\npiece handle=\/u04\/bloemkool.bos\/backup\/072ndggg_7_1_1 tag=TAG20240404T131352 comment=NONE<\/span>\r\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01<\/span>\r\nchannel ORA_DISK_1: deleting archived log(s)<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_4_1165409047.dbf RECID=3 STAMP=1165410707<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_5_1165409047.dbf RECID=4 STAMP=1165410708<\/span>\r\n...\r\n...<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_15_1165409047.dbf RECID=14 STAMP=1165410739<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_16_1165409047.dbf RECID=15 STAMP=1165410832<\/span>\r\nFinished backup at 2024\/04\/04 13:13:53<\/span>\r\n\r\nStarting backup at 2024\/04\/04 13:13:53<\/span>\r\nusing channel ORA_DISK_1<\/span>\r\nchannel ORA_DISK_1: starting full datafile backup set<\/span>\r\nchannel ORA_DISK_1: specifying datafile(s) in backup set<\/span>\r\ninput datafile file number=00001 name=\/u02\/oradata\/LIES\/datafile\/o1_mf_system_m0x0zlqp_.dbf<\/span>\r\n...\r\n...<\/span>\r\ninput datafile file number=00007 name=\/u02\/oradata\/LIES\/datafile\/o1_mf_users_m0x10vyd_.dbf<\/span>\r\nchannel ORA_DISK_1: starting piece 1 at 2024\/04\/04 13:13:53<\/span>\r\nchannel ORA_DISK_1: finished piece 1 at 2024\/04\/04 13:16:38<\/span>\r\npiece handle=\/u04\/bloemkool.bos\/backup\/082ndggh_8_1_1 tag=TAG20240404T131353 comment=NONE<\/span>\r\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:02:45<\/span>\r\nchannel ORA_DISK_1: starting full datafile backup set<\/span>\r\nchannel ORA_DISK_1: specifying datafile(s) in backup set<\/span>\r\ninput datafile file number=00010 name=\/u02\/oradata\/LIES\/154444752F0C4E6CE0630203A8C0091D\/datafile\/o1_mf_sysaux_m0x253w6_.dbf<\/span>\r\n...\r\n...<\/span>\r\ninput datafile file number=00012 name=\/u02\/oradata\/LIES\/154444752F0C4E6CE0630203A8C0091D\/datafile\/o1_mf_users_m0x25qqk_.dbf<\/span>\r\nchannel ORA_DISK_1: starting piece 1 at 2024\/04\/04 13:16:38<\/span>\r\nchannel ORA_DISK_1: finished piece 1 at 2024\/04\/04 13:17:33<\/span>\r\npiece handle=\/u04\/bloemkool.bos\/backup\/092ndglm_9_1_1 tag=TAG20240404T131353 comment=NONE<\/span>\r\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55<\/span>\r\nchannel ORA_DISK_1: starting full datafile backup set<\/span>\r\nchannel ORA_DISK_1: specifying datafile(s) in backup set<\/span>\r\ninput datafile file number=00006 name=\/u02\/oradata\/LIES\/datafile\/o1_mf_sysaux_m0x17jvv_.dbf<\/span>\r\ninput datafile file number=00005 name=\/u02\/oradata\/LIES\/datafile\/o1_mf_system_m0x17jvq_.dbf<\/span>\r\ninput datafile file number=00008 name=\/u02\/oradata\/LIES\/datafile\/o1_mf_undotbs1_m0x17jvx_.dbf<\/span>\r\nchannel ORA_DISK_1: starting piece 1 at 2024\/04\/04 13:17:33<\/span>\r\nchannel ORA_DISK_1: finished piece 1 at 2024\/04\/04 13:18:28<\/span>\r\npiece handle=\/u04\/bloemkool.bos\/backup\/0a2ndgnd_10_1_1 tag=TAG20240404T131353 comment=NONE<\/span>\r\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55<\/span>\r\nFinished backup at 2024\/04\/04 13:18:28<\/span>\r\n\r\nStarting backup at 2024\/04\/04 13:18:28<\/span>\r\ncurrent log archived<\/span>\r\nusing channel ORA_DISK_1<\/span>\r\nchannel ORA_DISK_1: starting archived log backup set<\/span>\r\nchannel ORA_DISK_1: specifying archived log(s) in backup set<\/span>\r\ninput archived log thread=1 sequence=17 RECID=16 STAMP=1165411109<\/span>\r\nchannel ORA_DISK_1: starting piece 1 at 2024\/04\/04 13:18:29<\/span>\r\nchannel ORA_DISK_1: finished piece 1 at 2024\/04\/04 13:18:30<\/span>\r\npiece handle=\/u04\/bloemkool.bos\/backup\/0b2ndgp5_11_1_1 tag=TAG20240404T131829 comment=NONE<\/span>\r\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01<\/span>\r\nchannel ORA_DISK_1: deleting archived log(s)<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_17_1165409047.dbf RECID=16 STAMP=1165411109<\/span>\r\nFinished backup at 2024\/04\/04 13:18:30<\/span>\r\n\r\nStarting Control File and SPFILE Autobackup at 2024\/04\/04 13:18:30<\/span>\r\npiece handle=\/u04\/bloemkool.bos\/backup\/c-2295896278-20240404-01 comment=NONE<\/span>\r\nFinished Control File and SPFILE Autobackup at 2024\/04\/04 13:18:33<\/span>\r\n\r\nRMAN><\/span><\/pre>\n
                Create some more\u00a0 archivelogs (wich will not be backed up)<\/strong><\/p>\n
                RMAN> alter system switch logfile;<\/span>\r\n\r\nStatement processed<\/span>\r\n\r\nRMAN> alter system switch logfile;<\/span>\r\n\r\nStatement processed<\/span>\r\n\r\nRMAN> alter system switch logfile;<\/span>\r\n\r\nStatement processed<\/span>\r\n\r\nRMAN><\/span>\r\n\r\nCheck which archivelogs are not backupped<\/span>\r\n\r\nRMAN> list archivelog all;<\/span>\r\n\r\nList of Archived Log Copies for database with db_unique_name LIES<\/span>\r\n=====================================================================<\/span>\r\n\r\nKey Thrd Seq S Low Time<\/span>\r\n------- ---- ------- - -------------------<\/span>\r\n17 1 18 A 2024\/04\/04 13:18:28<\/span>\r\nName: \/u03\/LIES\/archivelog\/1_18_1165409047.dbf<\/span>\r\n\r\n...<\/span>\r\n...<\/span>\r\n\r\n29 1 30 A 2024\/04\/04 13:28:23<\/span>\r\nName: \/u03\/LIES\/archivelog\/1_30_1165409047.dbf<\/span>\r\n\r\nRMAN><\/span><\/pre>\n
                As you can see : tha last created archivelog (which is still on disk) is sequence 30.<\/p>\n
                Restore the (old) Controlfile<\/strong><\/p>\n
                RMAN> shutdown immediate;<\/span>\r\n\r\ndatabase closed<\/span>\r\ndatabase dismounted<\/span>\r\nOracle instance shut down<\/span>\r\n\r\nRMAN> startup nomount;<\/span>\r\n\r\nconnected to target database (not started)<\/span>\r\nOracle instance started<\/span>\r\n\r\nTotal System Global Area 3221225344 bytes<\/span>\r\n\r\nFixed Size 9691008 bytes<\/span>\r\nVariable Size 654311424 bytes<\/span>\r\nDatabase Buffers 2550136832 bytes<\/span>\r\nRedo Buffers 7086080 bytes<\/span>\r\n\r\nRMAN> restore controlfile from '\/u04\/bloemkool.bos\/backup\/c-2295896278-20240404-01';<\/span>\r\n\r\nStarting restore at 2024\/04\/04 13:33:49<\/span>\r\nallocated channel: ORA_DISK_1<\/span>\r\nchannel ORA_DISK_1: SID=28 device type=DISK<\/span>\r\n\r\nchannel ORA_DISK_1: restoring control file<\/span>\r\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:03<\/span>\r\noutput file name=\/u03\/LIES\/controlfile\/o1_mf_m0wy0s3s_.ctl<\/span>\r\nFinished restore at 2024\/04\/04 13:33:52<\/span>\r\n\r\nRMAN><\/span><\/pre>\n
                Restore the database<\/strong><\/p>\n
                RMAN> shutdown immediate;<\/span>\r\n\r\nOracle instance shut down<\/span>\r\n\r\nRMAN> startup mount;<\/span>\r\n\r\nconnected to target database (not started)<\/span>\r\nOracle instance started<\/span>\r\ndatabase mounted<\/span>\r\n\r\nTotal System Global Area 3221225344 bytes<\/span>\r\n\r\nFixed Size 9691008 bytes<\/span>\r\nVariable Size 654311424 bytes<\/span>\r\nDatabase Buffers 2550136832 bytes<\/span>\r\nRedo Buffers 7086080 bytes<\/span>\r\n\r\nRMAN> restore database;<\/span>\r\n\r\nStarting restore at 2024\/04\/04 13:34:36<\/span>\r\nallocated channel: ORA_DISK_1<\/span>\r\nchannel ORA_DISK_1: SID=31 device type=DISK<\/span>\r\n\r\nskipping datafile 5; already restored to file \/u02\/oradata\/LIES\/datafile\/o1_mf_system_m0x17jvq_.dbf<\/span>\r\nskipping datafile 6; already restored to file \/u02\/oradata\/LIES\/datafile\/o1_mf_sysaux_m0x17jvv_.dbf<\/span>\r\nskipping datafile 8; already restored to file \/u02\/oradata\/LIES\/datafile\/o1_mf_undotbs1_m0x17jvx_.dbf<\/span>\r\nskipping datafile 9; already restored to file \/u02\/oradata\/LIES\/154444752F0C4E6CE0630203A8C0091D\/datafile\/o1_mf_system_m0x253w1_.dbf<\/span>\r\nskipping datafile 10; already restored to file \/u02\/oradata\/LIES\/154444752F0C4E6CE0630203A8C0091D\/datafile\/o1_mf_sysaux_m0x253w6_.dbf<\/span>\r\nskipping datafile 11; already restored to file \/u02\/oradata\/LIES\/154444752F0C4E6CE0630203A8C0091D\/datafile\/o1_mf_undotbs1_m0x253w6_.dbf<\/span>\r\nskipping datafile 12; already restored to file \/u02\/oradata\/LIES\/154444752F0C4E6CE0630203A8C0091D\/datafile\/o1_mf_users_m0x25qqk_.dbf<\/span>\r\nchannel ORA_DISK_1: starting datafile backup set restore<\/span>\r\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set<\/span>\r\nchannel ORA_DISK_1: restoring datafile 00001 to \/u02\/oradata\/LIES\/datafile\/o1_mf_system_m0x0zlqp_.dbf<\/span>\r\nchannel ORA_DISK_1: restoring datafile 00003 to \/u02\/oradata\/LIES\/datafile\/o1_mf_sysaux_m0x10ct0_.dbf<\/span>\r\nchannel ORA_DISK_1: restoring datafile 00004 to \/u02\/oradata\/LIES\/datafile\/o1_mf_undotbs1_m0x10tw5_.dbf<\/span>\r\nchannel ORA_DISK_1: restoring datafile 00007 to \/u02\/oradata\/LIES\/datafile\/o1_mf_users_m0x10vyd_.dbf<\/span>\r\nchannel ORA_DISK_1: reading from backup piece \/u04\/bloemkool.bos\/backup\/082ndggh_8_1_1<\/span>\r\nchannel ORA_DISK_1: piece handle=\/u04\/bloemkool.bos\/backup\/082ndggh_8_1_1 tag=TAG20240404T131353<\/span>\r\nchannel ORA_DISK_1: restored backup piece 1<\/span>\r\nchannel ORA_DISK_1: restore complete, elapsed time: 00:02:36<\/span>\r\nFinished restore at 2024\/04\/04 13:37:12<\/span>\r\n\r\nRMAN><\/span><\/pre>\n
                Recover the database (until sequence 31)<\/strong><\/p>\n
                RMAN> recover database until sequence 31;<\/span>\r\n\r\nStarting recover at 2024\/04\/04 13:38:09<\/span>\r\nusing channel ORA_DISK_1<\/span>\r\n\r\nstarting media recovery<\/span>\r\n\r\narchived log for thread 1 with sequence 29 is already on disk as file \/u03\/LIES\/onlinelog\/o1_mf_2_m0x13q8r_.log<\/span>\r\narchived log for thread 1 with sequence 30 is already on disk as file \/u03\/LIES\/onlinelog\/o1_mf_3_m0x13q9p_.log<\/span>\r\nchannel ORA_DISK_1: starting archived log restore to default destination<\/span>\r\nchannel ORA_DISK_1: restoring archived log<\/span>\r\narchived log thread=1 sequence=17<\/span>\r\nchannel ORA_DISK_1: reading from backup piece \/u04\/bloemkool.bos\/backup\/0b2ndgp5_11_1_1<\/span>\r\nchannel ORA_DISK_1: piece handle=\/u04\/bloemkool.bos\/backup\/0b2ndgp5_11_1_1 tag=TAG20240404T131829<\/span>\r\nchannel ORA_DISK_1: restored backup piece 1<\/span>\r\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_17_1165409047.dbf thread=1 sequence=17<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_18_1165409047.dbf thread=1 sequence=18<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_19_1165409047.dbf thread=1 sequence=19<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_20_1165409047.dbf thread=1 sequence=20<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_21_1165409047.dbf thread=1 sequence=21<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_22_1165409047.dbf thread=1 sequence=22<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_23_1165409047.dbf thread=1 sequence=23<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_24_1165409047.dbf thread=1 sequence=24<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_25_1165409047.dbf thread=1 sequence=25<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_26_1165409047.dbf thread=1 sequence=26<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_27_1165409047.dbf thread=1 sequence=27<\/span>\r\narchived log file name=\/u03\/LIES\/archivelog\/1_28_1165409047.dbf thread=1 sequence=28<\/span>\r\narchived log file name=\/u03\/LIES\/onlinelog\/o1_mf_2_m0x13q8r_.log thread=1 sequence=29<\/span>\r\narchived log file name=\/u03\/LIES\/onlinelog\/o1_mf_3_m0x13q9p_.log thread=1 sequence=30<\/span>\r\nmedia recovery complete, elapsed time: 00:00:02<\/span>\r\nFinished recover at 2024\/04\/04 13:38:12<\/span>\r\n\r\nRMAN><\/span><\/pre>\n
                Alter database open resetlogs<\/strong><\/p>\n
                RMAN> alter database open resetlogs;<\/span>\r\n\r\nStatement processed<\/span>\r\n\r\nRMAN><\/span><\/pre>\n
                 <\/p>\n
                So…I say…yes we can recover past the time of backup of the controlfile \ud83d\ude42<\/p>\n
                 <\/p>\n","protected":false},"excerpt":{"rendered":"
                A question on forums.oracle.com asked: can we recover a database further in time then the time of backup of the last controlfile. Nice question…my gut feeling immediately says : YES we can. But…I do not have a proof of it, and one quick search on the internet did not supply a adequate answer. So…then I […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[78,5,20],"tags":[21,24,26],"class_list":["post-592","post","type-post","status-publish","format-standard","hentry","category-backup","category-oracle","category-rman","tag-backup","tag-oracle","tag-rman"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/592","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=592"}],"version-history":[{"count":4,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/592\/revisions"}],"predecessor-version":[{"id":596,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/592\/revisions\/596"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=592"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=592"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=592"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}