{"id":6090,"date":"2021-08-04T00:03:04","date_gmt":"2021-08-04T00:03:04","guid":{"rendered":"https:\/\/swiv.com.br\/performing-database-point-in-time-recovery-dbpitr-when-the-recovery-point-is-not-definite\/"},"modified":"2026-05-27T20:02:30","modified_gmt":"2026-05-27T19:02:30","slug":"performing-database-point-in-time-recovery-dbpitr-when-the-recovery-point-is-not-definite","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/08\/04\/performing-database-point-in-time-recovery-dbpitr-when-the-recovery-point-is-not-definite\/","title":{"rendered":"Performing database point-in-time recovery (DBPITR) when the recovery point is not definite"},"content":{"rendered":"\n

Algumas vezes, devido algum processo indevido, precisamos recuperar o banco de dados em um ponto no tempo n\u00e3o conhecido, e este artigo explorar\u00e1 como podemos proceder nessas situa\u00e7\u00f5es.<\/p>\n\n\n\n

Reconhecendo nosso 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 20:44:55 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\n
Fazendo o backup nosso de cada dia:<\/p>\n\n\n
\nRMAN> BACKUP DATABASE;\n \nStarting backup at 2021-08-03:20:46:57\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=72 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:20:46:58\nchannel ORA_DISK_1: finished piece 1 at 2021-08-03:20:47:23\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_03\/o1_mf_nnndf_TAG20210803T204658_jjmoclj6_.bkp tag=TAG20210803T204658 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25\nFinished backup at 2021-08-03:20:47:23\n \nStarting Control File and SPFILE Autobackup at 2021-08-03:20:47:23\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_08_03\/o1_mf_s_1079642843_jjmodcr6_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-08-03:20:47:24\n<\/pre><\/div>\n\n\n
Temos uma tabela j\u00e1 criada no ambiente que ser\u00e1 a nossa refer\u00eancia. Vamos coletar o hor\u00e1rio atual, o considerando o TEMPO1:<\/p>\n\n\n
\nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> SELECT * FROM BSS.PITR;\n \nDESCRICAO\n--------------------------------------------------\nPITR DO BRUNO\n \nRMAN> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS') FROM DUAL;\n \nTO_CHAR(SYSDATE,'YY\n-------------------\n2021-08-03:20:49:39\n<\/pre><\/div>\n\n\n
Agora droparemos a tabela e coletaremos o TEMPO2:<\/p>\n\n\n
\nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> DROP TABLE BSS.PITR;\n \nStatement processed\n \nRMAN> ALTER SYSTEM SWITCH LOGFILE;\n \nStatement processed\n \nRMAN> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS') FROM DUAL;\n \nTO_CHAR(SYSDATE,'YY\n-------------------\n2021-08-03:20:50:41\n<\/pre><\/div>\n\n\n
Imaginemos que o ponto no tempo escolhido para a recupera\u00e7\u00e3o seja o TEMPO2 (ou seja, aquele que j\u00e1 n\u00e3o tem a tabela no banco de dados). Realizando processo de restore\/recover:<\/p>\n\n\n
\nSHUTDOWN IMMEDIATE;\nSTARTUP NOMOUNT;\nSET DBID &lt;DBID>;\nRESTORE CONTROLFILE FROM AUTOBACKUP;\nALTER DATABASE MOUNT;\nRUN { SET UNTIL TIME '&lt;YYYY-MM-DD:HH24:MI:SS>';\nRESTORE DATABASE;\nRECOVER DATABASE;}\n<\/pre><\/div>\n\n\n
Logs:<\/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:20:53:51\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_1079642843_jjmodcr6_.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_1079642843_jjmodcr6_.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:20:53:52\n \nRMAN> ALTER DATABASE MOUNT;\n \nreleased channel: ORA_DISK_1\nStatement processed\n \nRMAN> RUN { SET UNTIL TIME '2021-08-03:20:50:41';\nRESTORE DATABASE;\nRECOVER DATABASE;}2> 3>\n \nexecuting command: SET until clause\n \nStarting restore at 2021-08-03:20:54:18\nStarting implicit crosscheck backup at 2021-08-03:20:54:18\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=44 device type=DISK\nCrosschecked 1 objects\nFinished implicit crosscheck backup at 2021-08-03:20:54:18\n \nStarting implicit crosscheck copy at 2021-08-03:20:54:18\nusing channel ORA_DISK_1\nFinished implicit crosscheck copy at 2021-08-03:20:54:18\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_08_03\/o1_mf_s_1079642843_jjmodcr6_.bkp\n \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_TAG20210803T204658_jjmoclj6_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_03\/o1_mf_nnndf_TAG20210803T204658_jjmoclj6_.bkp tag=TAG20210803T204658\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:55:13\n \nStarting recover at 2021-08-03:20:55:14\nusing channel ORA_DISK_1\n \nstarting media recovery\n \narchived log for thread 1 with sequence 2 is already on disk as file \/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_2_jjml062p_.log\narchived log for thread 1 with sequence 3 is already on disk as file \/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_3_jjml0g5l_.log\narchived log for thread 1 with sequence 4 is already on disk as file \/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_1_jjmkzz6r_.log\narchived log file name=\/oracle\/archives\/1_1_1079640180.dbf thread=1 sequence=1\narchived log file name=\/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_2_jjml062p_.log thread=1 sequence=2\narchived log file name=\/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_3_jjml0g5l_.log thread=1 sequence=3\narchived log file name=\/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_1_jjmkzz6r_.log thread=1 sequence=4\nmedia recovery complete, elapsed time: 00:00:01\nFinished recover at 2021-08-03:20:55:15\n<\/pre><\/div>\n\n\n
O pulo do gato agora \u00e9 abrirmos o banco de dados em READ ONLY para validar se o tempo definido na recupera\u00e7\u00e3o \u00e9 o correto (ou seja, que nos habilite a recuperar a tabela dropada):<\/p>\n\n\n
\nRMAN> ALTER DATABASE OPEN READ ONLY;\n \nStatement processed\n \nRMAN> SELECT * FROM BSS.PITR;\n \nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03002: failure of sql statement command at 08\/03\/2021 20:56:46\nORA-00942: table or view does not exist\n<\/pre><\/div>\n\n\n
Como o tempo definido n\u00e3o era o correto, vamos tentar com o TEMPO1:<\/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:20:58:41\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_1079642843_jjmodcr6_.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_1079642843_jjmodcr6_.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:20:58:42\n \nRMAN> ALTER DATABASE MOUNT;\n \nreleased channel: ORA_DISK_1\nStatement processed\n \nRMAN> RUN { SET UNTIL TIME '2021-08-03:20:49:39';\nRESTORE DATABASE;\nRECOVER DATABASE;}2> 3>\n \nexecuting command: SET until clause\n \nStarting restore at 2021-08-03:20:59:08\nStarting implicit crosscheck backup at 2021-08-03:20:59:08\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=44 device type=DISK\nCrosschecked 1 objects\nFinished implicit crosscheck backup at 2021-08-03:20:59:09\n \nStarting implicit crosscheck copy at 2021-08-03:20:59:09\nusing channel ORA_DISK_1\nFinished implicit crosscheck copy at 2021-08-03:20:59:09\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_08_03\/o1_mf_s_1079642843_jjmodcr6_.bkp\n \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_TAG20210803T204658_jjmoclj6_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_03\/o1_mf_nnndf_TAG20210803T204658_jjmoclj6_.bkp tag=TAG20210803T204658\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:21:00:04\n \nStarting recover at 2021-08-03:21:00:04\nusing channel ORA_DISK_1\n \nstarting media recovery\n \narchived log for thread 1 with sequence 2 is already on disk as file \/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_2_jjml062p_.log\narchived log file name=\/oracle\/archives\/1_1_1079640180.dbf thread=1 sequence=1\narchived log file name=\/oracle\/dados\/RMANDB\/onlinelog\/o1_mf_2_jjml062p_.log thread=1 sequence=2\nmedia recovery complete, elapsed time: 00:00:00\nFinished recover at 2021-08-03:21:00:05\n<\/pre><\/div>\n\n\n
Como o banco em modo de leitura, constatamos que a tabela est\u00e1 l\u00e1, recuperada:<\/p>\n\n\n
\nRMAN> ALTER DATABASE OPEN READ ONLY;\n \nStatement processed\n \nRMAN> SELECT * FROM BSS.PITR;\n \nDESCRICAO\n--------------------------------------------------\nPITR DO BRUNO\n<\/pre><\/div>\n\n\n
Com isso, podemos concluir a recupera\u00e7\u00e3o abrindo o banco de dados em resetlogs:<\/p>\n\n\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 \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\n
Obs: 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":"
Algumas vezes, devido algum processo indevido, precisamos recuperar o banco de dados em um ponto no tempo n\u00e3o conhecido, e este artigo explorar\u00e1 como podemos proceder nessas situa\u00e7\u00f5es. Reconhecendo nosso laborat\u00f3rio: Fazendo o backup nosso de cada dia: Temos uma tabela j\u00e1 criada no ambiente que ser\u00e1 a nossa refer\u00eancia. Vamos coletar o hor\u00e1rio atual, […]<\/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-6090","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\/6090","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=6090"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6090\/revisions"}],"predecessor-version":[{"id":9067,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6090\/revisions\/9067"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}