{"id":110,"date":"2020-10-24T11:58:21","date_gmt":"2020-10-24T11:58:21","guid":{"rendered":"https:\/\/maboc.nl\/?p=110"},"modified":"2020-10-24T12:23:10","modified_gmt":"2020-10-24T12:23:10","slug":"restore-and-recover-a-database-up-to-a-point-in-time","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=110","title":{"rendered":"Restore and recover a database up to a point in time"},"content":{"rendered":"\n

At (let’s say) around 08:30 this morning something (probably someone) messed up the database. I would like to go get back the database to 08:00 this morning:<\/p>\n\n\n\n

  1. Startup the database in nomount<\/li>
  2. For future reference: look at the incarnations we know<\/li>
  3. Set an until time (this must be done in a run block)<\/li>
  4. Restore the database<\/li>
  5. Recover the database<\/li>
  6. Open the database with resetlogs <\/li><\/ol>\n\n\n\n

    <\/p>\n\n\n\n

    RMAN> startup force mount;\n\nOracle instance started\ndatabase mounted\n\nTotal System Global Area     620756648 bytes\n\nFixed Size                     9137832 bytes\nVariable Size                339738624 bytes\nDatabase Buffers             268435456 bytes\nRedo Buffers                   3444736 bytes\n\n\nRMAN> list incarnation;\r\n\r\n\rList of Database Incarnations\r\nDB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time\r\n------- ------- -------- ---------------- --- ---------- ----------\r\n1       1       CDB01    1590200411       PARENT  1          17-APR-19\r\n2       2       CDB01    1590200411       PARENT  1920977    07-OCT-20\r\n\r\nRMAN> run {\n2> set until time \"to_date('2020-10-24 08:00:00', 'yyyy-mm-dd hh24:mi:ss')\";\n3> restore database;\n4> recover database;\n5> }\n\nexecuting command: SET until clause\n\nStarting restore at 24-OCT-20\nflashing back control file to SCN 7644445\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=442 device type=DISK\n\nskipping datafile 5; already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/system01.dbf\nskipping datafile 6; already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/sysaux01.dbf\nskipping datafile 8; already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/undotbs01.dbf\nchannel ORA_DISK_1: starting datafile backup set restore\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set\nchannel ORA_DISK_1: restoring datafile 00001 to \/u02\/datafiles\/CDB01\/system01.dbf\nchannel ORA_DISK_1: restoring datafile 00003 to \/u02\/datafiles\/CDB01\/sysaux01.dbf\nchannel ORA_DISK_1: restoring datafile 00004 to \/u02\/datafiles\/CDB01\/undotbs01.dbf\nchannel ORA_DISK_1: restoring datafile 00007 to \/u02\/datafiles\/CDB01\/users01.dbf\nchannel ORA_DISK_1: reading from backup piece \/u04\/backups\/bloemkool\/sgvdajcc_1_1\nchannel ORA_DISK_1: piece handle=\/u04\/backups\/bloemkool\/sgvdajcc_1_1 tag=TAG20201019T000012\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:25\nchannel ORA_DISK_1: starting datafile backup set restore\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set\nchannel ORA_DISK_1: restoring datafile 00009 to \/u02\/datafiles\/CDB01\/PDB01\/system01.dbf\nchannel ORA_DISK_1: restoring datafile 00010 to \/u02\/datafiles\/CDB01\/PDB01\/sysaux01.dbf\nchannel ORA_DISK_1: restoring datafile 00011 to \/u02\/datafiles\/CDB01\/PDB01\/undotbs01.dbf\nchannel ORA_DISK_1: restoring datafile 00012 to \/u02\/datafiles\/CDB01\/PDB01\/users01.dbf\nchannel ORA_DISK_1: restoring datafile 00013 to \/u02\/datafiles\/CDB01\/PDB01\/martijn01.dnf\nchannel ORA_DISK_1: reading from backup piece \/u04\/backups\/bloemkool\/shvdaje6_1_1\nchannel ORA_DISK_1: piece handle=\/u04\/backups\/bloemkool\/shvdaje6_1_1 tag=TAG20201019T000012\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:15\nFinished restore at 24-OCT-20\n\nStarting recover at 24-OCT-20\nusing channel ORA_DISK_1\n\nstarting media recovery\n\nchannel ORA_DISK_1: starting archived log restore to default destination\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=2090\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=2091\n\n... restoring a lot of archivelogs\n\narchived log thread=1 sequence=2133\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=2134\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=2135\nchannel ORA_DISK_1: reading from backup piece \/u04\/backups\/bloemkool\/ttvdp86t_1_1\nchannel ORA_DISK_1: piece handle=\/u04\/backups\/bloemkool\/ttvdp86t_1_1 tag=TAG20201024T132132\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:35\narchived log file name=\/u03\/archiving\/CDB01\/1_2090_1053215134.dbf thread=1 sequence=2090\narchived log file name=\/u03\/archiving\/CDB01\/1_2091_1053215134.dbf thread=1 sequence=2091\n\n.... Recovering the just restored archivelogs\n\narchived log file name=\/u03\/archiving\/CDB01\/1_2132_1053215134.dbf thread=1 sequence=2132\narchived log file name=\/u03\/archiving\/CDB01\/1_2133_1053215134.dbf thread=1 sequence=2133\narchived log file name=\/u03\/archiving\/CDB01\/1_2134_1053215134.dbf thread=1 sequence=2134\narchived log file name=\/u03\/archiving\/CDB01\/1_2135_1053215134.dbf thread=1 sequence=2135\nmedia recovery complete, elapsed time: 00:01:10\nFinished recover at 24-OCT-20\n\nRMAN> alter database open resetlogs;\n\nStatement processed\n\nRMAN><\/code><\/pre>\n\n\n\n
    Just for fun..let’s have a look at the incarnations:<\/p>\n\n\n\n
    RMAN> list incarnation;\r\n\r\n\r\nList of Database Incarnations\r\nDB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time\r\n------- ------- -------- ---------------- --- ---------- ----------\r\n1       1       CDB01    1590200411       PARENT  1          17-APR-19\r\n2       2       CDB01    1590200411       PARENT  1920977    07-OCT-20\r\n3       3       CDB01    1590200411       CURRENT 7836659    24-OCT-20\r\n\r\nRMAN><\/code><\/pre>\n\n\n\n
    We’re in a new incarnation….The work of the “open database resetlogs”<\/p>\n","protected":false},"excerpt":{"rendered":"
    At (let’s say) around 08:30 this morning something (probably someone) messed up the database. I would like to go get back the database to 08:00 this morning: Startup the database in nomount For future reference: look at the incarnations we know Set an until time (this must be done in a run block) Restore the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,20],"tags":[24,25,22,26],"class_list":["post-110","post","type-post","status-publish","format-standard","hentry","category-oracle","category-rman","tag-oracle","tag-recover","tag-restore","tag-rman"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/110","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=110"}],"version-history":[{"count":3,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/110\/revisions"}],"predecessor-version":[{"id":115,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/110\/revisions\/115"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}