Antes de entrar especificamente no assunto do artigo, \u00e9 interessante expor o recurso interessante na tecnologia Oracle quanto \u00e0 recupera\u00e7\u00e3o em um ponto no tempo espec\u00edfico. Podemos usufruir desse conceito a n\u00edvel de database, tablespace ou tabela. A defini\u00e7\u00e3o deste ponto pode ser usada como SCN, Log Sequence, Restore Point ou Tempo (SET UNTIL TIME). Como pr\u00e9-requisito, o banco de dados deve estar rodando no modo Archivelog, e precisamos ter um backup do ambiente antes do SCN desejado. Quando realizamos o PITR, devemos abrir o banco de dados em RESETLOGS, e quando isso ocorre, temos:<\/p>\n\n\n\n
Neste artigo, vamos simular uma situa\u00e7\u00e3o de Database Point-in-time Recovery onde sabemos exatamente o momento no tempo que iremos usar na recupera\u00e7\u00e3o.<\/p>\n\n\n\n
Reconhecendo o ambiente laborat\u00f3rio:<\/p>\n\n\n
\n[oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Tue Aug 3 19:23:38 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates. All rights reserved.\n \nconnected to target database: RMANDB (DBID=3825250984)\n \nRMAN> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n \nusing target database control file instead of recovery catalog\nNAME OPEN_MODE LOG_MODE\n--------- -------------------- ------------\nRMANDB READ WRITE ARCHIVELOG\n \nRMAN> REPORT SCHEMA;\n \nReport of database schema for database with db_unique_name RMANDB\n \nList of Permanent Datafiles\n===========================\nFile Size(MB) Tablespace RB segs Datafile Name\n---- -------- -------------------- ------- ------------------------\n1 880 SYSTEM YES \/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\n2 120 TS_HIPO_CATALOG NO \/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf\n3 730 SYSAUX NO \/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\n4 305 UNDOTBS1 YES \/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\n5 200 TS_CORTEX_CATALOG NO \/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\n7 16 USERS NO \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\n \nList of Temporary Files\n=======================\nFile Size(MB) Tablespace Maxsize(MB) Tempfile Name\n---- -------- -------------------- ----------- --------------------\n1 69 TEMP 32767 \/oracle\/dados\/RMANDB\/datafile\/o1_mf_temp_h8nyvt1f_.tmp\n<\/pre><\/div>\n\n\nRealizando um backup do nosso banco de dados:<\/p>\n\n\n
\nRMAN> BACKUP DATABASE;\n \nStarting backup at 2021-08-03:19:25:27\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=70 device type=DISK\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00001 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\ninput datafile file number=00005 name=\/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\ninput datafile file number=00002 name=\/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-08-03:19:25:27\nchannel ORA_DISK_1: finished piece 1 at 2021-08-03:19:27:42\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_03\/o1_mf_nnndf_TAG20210803T192527_jjmjlrmq_.bkp tag=TAG20210803T192527 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:02:15\nFinished backup at 2021-08-03:19:27:42\n \nStarting Control File and SPFILE Autobackup at 2021-08-03:19:27:43\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_08_03\/o1_mf_s_1079638063_jjmjq0bx_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-08-03:19:27:46\n<\/pre><\/div>\n\n\nVamos criar um owner e tabela para termos o par\u00e2metro de compara\u00e7\u00e3o com o ambiente que ser\u00e1 recuperado:<\/p>\n\n\n
\nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> CREATE USER BSS IDENTIFIED BY oracle;\n \nStatement processed\n \nRMAN> GRANT DBA TO BSS;\n \nStatement processed\n \nRMAN> CREATE TABLE BSS.PITR (DESCRICAO VARCHAR2(50));\n \nStatement processed\n \nRMAN> INSERT INTO BSS.PITR (DESCRICAO) VALUES ('PITR DO BRUNO');\n \nStatement processed\n \nRMAN> COMMIT;\n \nStatement processed\n<\/pre><\/div>\n\n\nDepois disso, vamos coletar o dia e hora do nosso ambiente, que ser\u00e1 usado no processo de recupera\u00e7\u00e3o:<\/p>\n\n\n
\nRMAN> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS') FROM DUAL;\n \nTO_CHAR(SYSDATE,'YY\n-------------------\n2021-08-03:19:32:31\n<\/pre><\/div>\n\n\nPara reproduzir um problema que justifique a recupera\u00e7\u00e3o, vamos dropar nossa tabela de teste:<\/p>\n\n\n
\nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> DROP TABLE BSS.PITR;\n \nStatement processed\n<\/pre><\/div>\n\n\nPodemos baixar a inst\u00e2ncia e montar o banco de dados. Antes disso coleto o incarnation atual do banco para podermos comparar no final do processo:<\/p>\n\n\n
\nRMAN> LIST INCARNATION;\n \n \nList of Database Incarnations\nDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time\n------- ------- -------- ---------------- --- ---------- ----------\n1 1 RMANDB 3825250984 PARENT 1 2018-02-07:19:20:08\n2 2 RMANDB 3825250984 CURRENT 1477662 2020-04-05:21:44:14\n \nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> SHUTDOWN IMMEDIATE;\n \ndatabase closed\ndatabase dismounted\nOracle instance shut down\n \nRMAN> STARTUP MOUNT;\n \nconnected to target database (not started)\nOracle instance started\ndatabase mounted\n \nTotal System Global Area 1610612016 bytes\n \nFixed Size 8658224 bytes\nVariable Size 520093696 bytes\nDatabase Buffers 1073741824 bytes\nRedo Buffers 8118272 bytes\n<\/pre><\/div>\n\n\nDe posse do ponto que queremos de recupera\u00e7\u00e3o, disparo o comando abaixo para o restore e recover do banco:<\/p>\n\n\n
\nRUN { SET UNTIL TIME '2021-08-03:19:32:31'; \n RESTORE DATABASE; \n RECOVER DATABASE;}\n<\/pre><\/div>\n\n\nLog de execu\u00e7\u00e3o:<\/p>\n\n\n
\nRMAN> RUN { SET UNTIL TIME '2021-08-03:19:32:31';\n RESTORE DATABASE;\n RECOVER DATABASE;}2> 3>\n \nexecuting command: SET until clause\n \nStarting restore at 2021-08-03:19:45:57\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=45 device type=DISK\n \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 \/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\nchannel ORA_DISK_1: restoring datafile 00002 to \/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf\nchannel ORA_DISK_1: restoring datafile 00003 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\nchannel ORA_DISK_1: restoring datafile 00004 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\nchannel ORA_DISK_1: restoring datafile 00005 to \/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\nchannel ORA_DISK_1: restoring datafile 00007 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_08_03\/o1_mf_nnndf_TAG20210803T192527_jjmjlrmq_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_03\/o1_mf_nnndf_TAG20210803T192527_jjmjlrmq_.bkp tag=TAG20210803T192527\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:01:55\nFinished restore at 2021-08-03:19:47:53\n \nStarting recover at 2021-08-03:19:47:53\nusing channel ORA_DISK_1\n \nstarting media recovery\n \narchived log for thread 1 with sequence 117 is already on disk as file \/oracle\/archives\/1_117_1036964654.dbf\narchived log for thread 1 with sequence 118 is already on disk as file \/oracle\/archives\/1_118_1036964654.dbf\narchived log file name=\/oracle\/archives\/1_117_1036964654.dbf thread=1 sequence=117\nmedia recovery complete, elapsed time: 00:00:01\nFinished recover at 2021-08-03:19:47:54\n<\/pre><\/div>\n\n\nAbrindo o banco de dados em RESETLOGS e validando se nossa tabela teste est\u00e1 recuperada:<\/p>\n\n\n
\nRMAN> ALTER DATABASE OPEN RESETLOGS;\n \nStatement processed\n \nRMAN> SELECT * FROM BSS.PITR;\n \nDESCRICAO\n--------------------------------------------------\nPITR DO BRUNO\n<\/pre><\/div>\n\n\nE como era previsto, um novo incarnation foi incrementado:<\/p>\n\n\n
\nRMAN> LIST INCARNATION OF DATABASE;\n \n \nList of Database Incarnations\nDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time\n------- ------- -------- ---------------- --- ---------- ----------\n1 1 RMANDB 3825250984 PARENT 1 2018-02-07:19:20:08\n2 2 RMANDB 3825250984 PARENT 1477662 2020-04-05:21:44:14\n3 3 RMANDB 3825250984 CURRENT 3085187 2021-08-03:19:49:35\n<\/pre><\/div>\n\n\nImaginemos que o desastre em si exigisse que tamb\u00e9m recuper\u00e1ssemos o controlfile. Podemos realizar a recupera\u00e7\u00e3o conforme exemplo abaixo (e por conta do restore do controlfile, \u00e9 mister definirmos o DBID do banco):<\/p>\n\n\n
\nSHUTDOWN IMMEDIATE;\nSTARTUP NOMOUNT;\nSET DBID <DBID>;\nRESTORE CONTROLFILE FROM AUTOBACKUP;\nALTER DATABASE MOUNT;\nRUN { SET UNTIL TIME '<YYYY-MM-DD:HH24:MI:SS>';\nRESTORE DATABASE;\nRECOVER DATABASE;}\n<\/pre><\/div>\n\n\nLog de execu\u00e7\u00e3o. Vemos que um erro RMAN-20207 \u00e9 exibido ao tentar restaurar o banco de dados, pois o incarnation do backup \u00e9 diferente do atual:<\/p>\n\n\n
\nRMAN> SHUTDOWN IMMEDIATE;\n \ndatabase closed\ndatabase dismounted\nOracle instance shut down\n \nRMAN> STARTUP NOMOUNT;\n \nconnected to target database (not started)\nOracle instance started\n \nTotal System Global Area 1610612016 bytes\n \nFixed Size 8658224 bytes\nVariable Size 520093696 bytes\nDatabase Buffers 1073741824 bytes\nRedo Buffers 8118272 bytes\n \nRMAN> SET DBID 3825250984;\n \nexecuting command: SET DBID\n \nRMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;\n \nStarting restore at 2021-08-03:19:54:57\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=38 device type=DISK\n \nrecovery area destination: \/oracle\/fra\ndatabase name (or database unique name) used for search: RMANDB\nchannel ORA_DISK_1: AUTOBACKUP \/oracle\/fra\/RMANDB\/autobackup\/2021_08_03\/o1_mf_s_1079639397_jjml0or0_.bkp found in the recovery area\nchannel ORA_DISK_1: looking for AUTOBACKUP on day: 20210803\nchannel ORA_DISK_1: restoring control file from AUTOBACKUP \/oracle\/fra\/RMANDB\/autobackup\/2021_08_03\/o1_mf_s_1079639397_jjml0or0_.bkp\nchannel ORA_DISK_1: control file restore from AUTOBACKUP complete\noutput file name=\/oracle\/dados\/RMANDB\/controlfile\/o1_mf_h8nytrhd_.ctl\noutput file name=\/oracle\/fra\/RMANDB\/controlfile\/o1_mf_h8nytrty_.ctl\nFinished restore at 2021-08-03:19:55:00\n \nRMAN> ALTER DATABASE MOUNT;\n \nreleased channel: ORA_DISK_1\nStatement processed\n \nRMAN> RUN { SET UNTIL TIME '2021-08-03:19:32:31';\n RESTORE DATABASE;\n RECOVER DATABASE;}2> 3>\n \nexecuting command: SET until clause\n \nStarting restore at 2021-08-03:19:56:19\nStarting implicit crosscheck backup at 2021-08-03:19:56:19\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=44 device type=DISK\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03002: failure of restore command at 08\/03\/2021 19:56:20\nRMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time\n<\/pre><\/div>\n\n\nPara resolver, resetamos o incarnation usado no backup, conforme abaixo:<\/p>\n\n\n
\nRMAN> LIST INCARNATION;\n \n \nList of Database Incarnations\nDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time\n------- ------- -------- ---------------- --- ---------- ----------\n1 1 RMANDB 3825250984 PARENT 1 2018-02-07:19:20:08\n2 2 RMANDB 3825250984 PARENT 1477662 2020-04-05:21:44:14\n3 3 RMANDB 3825250984 CURRENT 3085187 2021-08-03:19:49:35\n \nRMAN> reset database to incarnation 2;\n \nStarting implicit crosscheck backup at 2021-08-03:20:00:55\nreleased channel: ORA_DISK_1\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=44 device type=DISK\nCrosschecked 2 objects\nFinished implicit crosscheck backup at 2021-08-03:20:00:55\n \nStarting implicit crosscheck copy at 2021-08-03:20:00:55\nusing channel ORA_DISK_1\nCrosschecked 1 objects\nFinished implicit crosscheck copy at 2021-08-03:20:00:56\n \nsearching for all files in the recovery area\ncataloging files...\ncataloging done\n \nList of Cataloged Files\n=======================\nFile Name: \/oracle\/fra\/RMANDB\/autobackup\/2021_06_25\/o1_mf_s_1076133020_jfc66f40_.bkp\nFile Name: \/oracle\/fra\/RMANDB\/autobackup\/2021_08_03\/o1_mf_s_1079639397_jjml0or0_.bkp\nFile Name: \/oracle\/fra\/RMANDB\/backupset\/2021_06_25\/o1_mf_nnndf_DUAL_jfc66cg0_.bkp\nFile Name: \/oracle\/fra\/RMANDB\/o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp\n \ndatabase reset to incarnation 2\n<\/pre><\/div>\n\n\nRecuperado com sucesso:<\/p>\n\n\n
\nRMAN> RUN { SET UNTIL TIME '2021-08-03:19:32:31';\n RESTORE DATABASE;\n RECOVER DATABASE;}2> 3>\n \nexecuting command: SET until clause\n \nStarting restore at 2021-08-03:20:01:25\nusing channel ORA_DISK_1\n \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 \/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\nchannel ORA_DISK_1: restoring datafile 00002 to \/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf\nchannel ORA_DISK_1: restoring datafile 00003 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\nchannel ORA_DISK_1: restoring datafile 00004 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\nchannel ORA_DISK_1: restoring datafile 00005 to \/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\nchannel ORA_DISK_1: restoring datafile 00007 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_08_03\/o1_mf_nnndf_TAG20210803T192527_jjmjlrmq_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_03\/o1_mf_nnndf_TAG20210803T192527_jjmjlrmq_.bkp tag=TAG20210803T192527\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:55\nFinished restore at 2021-08-03:20:02:20\n \nStarting recover at 2021-08-03:20:02:20\nusing channel ORA_DISK_1\n \nstarting media recovery\n \narchived log for thread 1 with sequence 117 is already on disk as file \/oracle\/archives\/1_117_1036964654.dbf\narchived log for thread 1 with sequence 118 is already on disk as file \/oracle\/archives\/1_118_1036964654.dbf\narchived log file name=\/oracle\/archives\/1_117_1036964654.dbf thread=1 sequence=117\narchived log file name=\/oracle\/archives\/1_118_1036964654.dbf thread=1 sequence=118\nmedia recovery complete, elapsed time: 00:00:01\nFinished recover at 2021-08-03:20:02:21\n \nRMAN> ALTER DATABASE OPEN RESETLOGS;\n \nStatement processed\n<\/pre><\/div>\n\n\nNossa tabela est\u00e1 a\u00ed:<\/p>\n\n\n
\nRMAN> SELECT * FROM BSS.PITR;\n \nDESCRICAO\n--------------------------------------------------\nPITR DO BRUNO\n \nRMAN> LIST INCARNATION;\n \n \nList of Database Incarnations\nDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time\n------- ------- -------- ---------------- --- ---------- ----------\n1 1 RMANDB 3825250984 PARENT 1 2018-02-07:19:20:08\n2 2 RMANDB 3825250984 PARENT 1477662 2020-04-05:21:44:14\n3 3 RMANDB 3825250984 ORPHAN 3085187 2021-08-03:19:49:35\n4 4 RMANDB 3825250984 CURRENT 3085187 2021-08-03:20:03:00\n<\/pre><\/div>\n\n\nObs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Antes de entrar especificamente no assunto do artigo, \u00e9 interessante expor o recurso interessante na tecnologia Oracle quanto \u00e0 recupera\u00e7\u00e3o em um ponto no tempo espec\u00edfico. Podemos usufruir desse conceito a n\u00edvel de database, tablespace ou tabela. A defini\u00e7\u00e3o deste ponto pode ser usada como SCN, Log Sequence, Restore Point ou Tempo (SET UNTIL TIME). […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-6068","post","type-post","status-publish","format-standard","hentry","category-backup-recovery"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6068","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=6068"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6068\/revisions"}],"predecessor-version":[{"id":9068,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6068\/revisions\/9068"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}