{"id":4768,"date":"2021-05-20T07:06:18","date_gmt":"2021-05-20T07:06:18","guid":{"rendered":"https:\/\/swiv.com.br\/performing-pitr-of-a-whole-cdb\/"},"modified":"2026-05-27T20:02:32","modified_gmt":"2026-05-27T19:02:32","slug":"performing-pitr-of-a-whole-cdb","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/05\/20\/performing-pitr-of-a-whole-cdb\/","title":{"rendered":"Performing PITR of a whole CDB"},"content":{"rendered":"\n

Na arquitetura Multitenant tamb\u00e9m temos a possibilidade de realizar um PITR (Point-in-Time Recovery), que nos permite recuperar um ambiente em um ponto espec\u00edfico do passado, utilizando SCN, tempo (data\/hora), Log Sequence Number ou um pr\u00f3prio restore point. Neste artigo vamos explorar na pr\u00e1tica uma recupera\u00e7\u00e3o do CDB todo, considerando que o mesmo esteja no modo Archivelog e tenha os backups dispon\u00edveis para uso.<\/p>\n\n\n\n

Realizando backup full do ambiente:<\/p>\n\n\n

\n[oracle@quiasma ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Thu May 20 03:37:02 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: ASWAN (DBID=1340416544)\n \nRMAN> BACKUP DATABASE;\n \nStarting backup at 20-MAY-21\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=57 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\/ASWAN\/datafile\/o1_mf_system_j9yzl519_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_j9wdsb2f_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_j9wc1bk9_.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_users_j9wc1clt_.dbf\nchannel ORA_DISK_1: starting piece 1 at 20-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 20-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_20\/o1_mf_nnndf_TAG20210520T033709_jbd0womn_.bkp tag=TAG20210520T033709 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00009 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_system_jb463o46_.dbf\ninput datafile file number=00010 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_sysaux_jb487ow0_.dbf\ninput datafile file number=00011 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_undotbs1_j9wcyoh8_.dbf\ninput datafile file number=00012 name=\/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_users_j9yz06wp_.dbf\nchannel ORA_DISK_1: starting piece 1 at 20-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 20-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/backupset\/2021_05_20\/o1_mf_nnndf_TAG20210520T033709_jbd0x4pr_.bkp tag=TAG20210520T033709 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00005 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_j9wc9px6_.dbf\ninput datafile file number=00006 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_j9wc9px1_.dbf\ninput datafile file number=00008 name=\/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_j9wc9px7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 20-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 20-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/C2469670772D160AE0536A00A8C00FFA\/backupset\/2021_05_20\/o1_mf_nnndf_TAG20210520T033709_jbd0xcsc_.bkp tag=TAG20210520T033709 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nFinished backup at 20-MAY-21\n \nStarting Control File and SPFILE Autobackup at 20-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/autobackup\/2021_05_20\/o1_mf_s_1073014654_jbd0xh3m_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 20-MAY-21\n<\/pre><\/div>\n\n\n
Criando uma tabela dentro do CDB$ROOT para usarmos como refer\u00eancia na recupera\u00e7\u00e3o:<\/p>\n\n\n
\n[oracle@quiasma ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Thu May 20 03:42:13 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;\n \nNAME      OPEN_MODE            CDB\n--------- -------------------- ---\nASWAN     READ WRITE           YES\n \nSQL> CREATE TABLE C##BSS.TESTE (DESCRICAO VARCHAR2(20));\n \nTable created.\n \nSQL> INSERT INTO C##BSS.TESTE (DESCRICAO) VALUES ('RPIT DO CDB');\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n<\/pre><\/div>\n\n\n
Coletando o atual SCN, que ser\u00e1 o par\u00e2metro de recupera\u00e7\u00e3o que vamos utilizar neste artigo:<\/p>\n\n\n
\nRMAN> SELECT CURRENT_SCN FROM V$DATABASE;\n \nCURRENT_SCN\n-----------\n    2169033\n<\/pre><\/div>\n\n\n
Gerando alguns archives:<\/p>\n\n\n
\nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n<\/pre><\/div>\n\n\n
Executando backup dos archivelogs:<\/p>\n\n\n
\n[oracle@quiasma ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Thu May 20 03:46:20 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: ASWAN (DBID=1340416544)\n \nRMAN> BACKUP ARCHIVELOG ALL DELETE ALL INPUT;\n \nStarting backup at 20-MAY-21\ncurrent log archived\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=90 device type=DISK\nchannel ORA_DISK_1: starting archived log backup set\nchannel ORA_DISK_1: specifying archived log(s) in backup set\ninput archived log thread=1 sequence=6 RECID=1 STAMP=1072501770\ninput archived log thread=1 sequence=7 RECID=2 STAMP=1072501809\ninput archived log thread=1 sequence=8 RECID=3 STAMP=1072503370\ninput archived log thread=1 sequence=9 RECID=4 STAMP=1072503403\ninput archived log thread=1 sequence=10 RECID=5 STAMP=1072586228\ninput archived log thread=1 sequence=11 RECID=6 STAMP=1072586257\ninput archived log thread=1 sequence=12 RECID=7 STAMP=1072586635\ninput archived log thread=1 sequence=13 RECID=8 STAMP=1072586846\ninput archived log thread=1 sequence=14 RECID=9 STAMP=1072586861\ninput archived log thread=1 sequence=15 RECID=10 STAMP=1072587344\ninput archived log thread=1 sequence=16 RECID=11 STAMP=1072756615\ninput archived log thread=1 sequence=17 RECID=12 STAMP=1072756632\ninput archived log thread=1 sequence=18 RECID=13 STAMP=1072757472\ninput archived log thread=1 sequence=19 RECID=14 STAMP=1072757489\ninput archived log thread=1 sequence=20 RECID=15 STAMP=1072758669\ninput archived log thread=1 sequence=21 RECID=16 STAMP=1072758684\ninput archived log thread=1 sequence=22 RECID=17 STAMP=1072759485\ninput archived log thread=1 sequence=23 RECID=18 STAMP=1072759502\ninput archived log thread=1 sequence=24 RECID=19 STAMP=1073015120\ninput archived log thread=1 sequence=25 RECID=20 STAMP=1073015121\ninput archived log thread=1 sequence=26 RECID=21 STAMP=1073015122\ninput archived log thread=1 sequence=27 RECID=22 STAMP=1073015192\nchannel ORA_DISK_1: starting piece 1 at 20-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 20-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_20\/o1_mf_annnn_TAG20210520T034632_jbd1g92o_.bkp tag=TAG20210520T034632 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nchannel ORA_DISK_1: deleting archived log(s)\narchived log file name=\/oracle\/archives\/1_6_1072500770.dbf RECID=1 STAMP=1072501770\narchived log file name=\/oracle\/archives\/1_7_1072500770.dbf RECID=2 STAMP=1072501809\narchived log file name=\/oracle\/archives\/1_8_1072500770.dbf RECID=3 STAMP=1072503370\narchived log file name=\/oracle\/archives\/1_9_1072500770.dbf RECID=4 STAMP=1072503403\narchived log file name=\/oracle\/archives\/1_10_1072500770.dbf RECID=5 STAMP=1072586228\narchived log file name=\/oracle\/archives\/1_11_1072500770.dbf RECID=6 STAMP=1072586257\narchived log file name=\/oracle\/archives\/1_12_1072500770.dbf RECID=7 STAMP=1072586635\narchived log file name=\/oracle\/archives\/1_13_1072500770.dbf RECID=8 STAMP=1072586846\narchived log file name=\/oracle\/archives\/1_14_1072500770.dbf RECID=9 STAMP=1072586861\narchived log file name=\/oracle\/archives\/1_15_1072500770.dbf RECID=10 STAMP=1072587344\narchived log file name=\/oracle\/archives\/1_16_1072500770.dbf RECID=11 STAMP=1072756615\narchived log file name=\/oracle\/archives\/1_17_1072500770.dbf RECID=12 STAMP=1072756632\narchived log file name=\/oracle\/archives\/1_18_1072500770.dbf RECID=13 STAMP=1072757472\narchived log file name=\/oracle\/archives\/1_19_1072500770.dbf RECID=14 STAMP=1072757489\narchived log file name=\/oracle\/archives\/1_20_1072500770.dbf RECID=15 STAMP=1072758669\narchived log file name=\/oracle\/archives\/1_21_1072500770.dbf RECID=16 STAMP=1072758684\narchived log file name=\/oracle\/archives\/1_22_1072500770.dbf RECID=17 STAMP=1072759485\narchived log file name=\/oracle\/archives\/1_23_1072500770.dbf RECID=18 STAMP=1072759502\narchived log file name=\/oracle\/archives\/1_24_1072500770.dbf RECID=19 STAMP=1073015120\narchived log file name=\/oracle\/archives\/1_25_1072500770.dbf RECID=20 STAMP=1073015121\narchived log file name=\/oracle\/archives\/1_26_1072500770.dbf RECID=21 STAMP=1073015122\narchived log file name=\/oracle\/archives\/1_27_1072500770.dbf RECID=22 STAMP=1073015192\nFinished backup at 20-MAY-21\n \nStarting Control File and SPFILE Autobackup at 20-MAY-21\npiece handle=\/oracle\/fra\/ASWAN\/autobackup\/2021_05_20\/o1_mf_s_1073015196_jbd1gdh6_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 20-MAY-21\n<\/pre><\/div>\n\n\n
Baixando o CDB e montando-o:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL> STARTUP MOUNT;\nORACLE instance started.\n \nTotal System Global Area 2466249080 bytes\nFixed Size                  8898936 bytes\nVariable Size             671088640 bytes\nDatabase Buffers         1778384896 bytes\nRedo Buffers                7876608 bytes\nDatabase mounted.\n<\/pre><\/div>\n\n\n
Definindo o valor do SCN que deve ser respeitado no restore e recover do ambiente:<\/p>\n\n\n
\nRMAN> RUN {\n        SET UNTIL SCN 2169033;\n        RESTORE DATABASE;\n        RECOVER DATABASE;\n}2> 3> 4> 5>\n \nexecuting command: SET until clause\n \nStarting restore at 20-MAY-21\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=45 device type=DISK\n \nskipping datafile 5; already restored to file \/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_j9wc9px6_.dbf\nskipping datafile 6; already restored to file \/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_j9wc9px1_.dbf\nskipping datafile 8; already restored to file \/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_j9wc9px7_.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 \/oracle\/dados\/ASWAN\/datafile\/o1_mf_system_j9yzl519_.dbf\nchannel ORA_DISK_1: restoring datafile 00003 to \/oracle\/dados\/ASWAN\/datafile\/o1_mf_sysaux_j9wdsb2f_.dbf\nchannel ORA_DISK_1: restoring datafile 00004 to \/oracle\/dados\/ASWAN\/datafile\/o1_mf_undotbs1_j9wc1bk9_.dbf\nchannel ORA_DISK_1: restoring datafile 00007 to \/oracle\/dados\/ASWAN\/datafile\/o1_mf_users_j9wc1clt_.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/ASWAN\/backupset\/2021_05_20\/o1_mf_nnndf_TAG20210520T033709_jbd0womn_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_20\/o1_mf_nnndf_TAG20210520T033709_jbd0womn_.bkp tag=TAG20210520T033709\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:07\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 \/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_system_jb463o46_.dbf\nchannel ORA_DISK_1: restoring datafile 00010 to \/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_sysaux_jb487ow0_.dbf\nchannel ORA_DISK_1: restoring datafile 00011 to \/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_undotbs1_j9wcyoh8_.dbf\nchannel ORA_DISK_1: restoring datafile 00012 to \/oracle\/dados\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/datafile\/o1_mf_users_j9yz06wp_.dbf\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/backupset\/2021_05_20\/o1_mf_nnndf_TAG20210520T033709_jbd0x4pr_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/ASWAN\/C246BD61C42A1E80E0536A00A8C076F1\/backupset\/2021_05_20\/o1_mf_nnndf_TAG20210520T033709_jbd0x4pr_.bkp tag=TAG20210520T033709\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:03\nFinished restore at 20-MAY-21\n \nStarting recover at 20-MAY-21\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=24\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=25\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=26\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=27\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/ASWAN\/backupset\/2021_05_20\/o1_mf_annnn_TAG20210520T034632_jbd1g92o_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_20\/o1_mf_annnn_TAG20210520T034632_jbd1g92o_.bkp tag=TAG20210520T034632\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\narchived log file name=\/oracle\/archives\/1_24_1072500770.dbf thread=1 sequence=24\narchived log file name=\/oracle\/archives\/1_25_1072500770.dbf thread=1 sequence=25\narchived log file name=\/oracle\/archives\/1_26_1072500770.dbf thread=1 sequence=26\nmedia recovery complete, elapsed time: 00:00:01\nchannel ORA_DISK_1: starting archived log restore to default destination\nchannel ORA_DISK_1: restoring archived log\narchived log thread=1 sequence=28\nchannel ORA_DISK_1: reading from backup piece \/oracle\/fra\/ASWAN\/backupset\/2021_05_20\/o1_mf_annnn_TAG20210520T035017_jbd1o9lf_.bkp\nchannel ORA_DISK_1: piece handle=\/oracle\/fra\/ASWAN\/backupset\/2021_05_20\/o1_mf_annnn_TAG20210520T035017_jbd1o9lf_.bkp tag=TAG20210520T035017\nchannel ORA_DISK_1: restored backup piece 1\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\nFinished recover at 20-MAY-21\n<\/pre><\/div>\n\n\n
Abrindo o ambiente com a op\u00e7\u00e3o de Resetlogs, uma vez que utilizamos o RPIT:<\/p>\n\n\n
\nRMAN> ALTER DATABASE OPEN RESETLOGS;\n \nStatement processed\n<\/pre><\/div>\n\n\n
Validando se o CDB e seus PDBs est\u00e3o abertos:<\/p>\n\n\n
\n[oracle@quiasma ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Thu May 20 03:59:47 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;\n \nNAME      OPEN_MODE            CDB\n--------- -------------------- ---\nASWAN     READ WRITE           YES\n \nSQL> SELECT NAME,OPEN_MODE FROM V$PDBS;\n \nNAME\n--------------------------------------------------------------------------------\nOPEN_MODE\n----------\nPDB$SEED\nREAD ONLY\n \nHIPOFISE1\nREAD WRITE\n<\/pre><\/div>\n\n\n
Validando se nossa tabela que foi recentemente criada aparece no banco:<\/p>\n\n\n
\nSQL> SELECT * FROM C##BSS.TESTE;\n \nDESCRICAO\n--------------------\nRPIT DO CDB\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":"
Na arquitetura Multitenant tamb\u00e9m temos a possibilidade de realizar um PITR (Point-in-Time Recovery), que nos permite recuperar um ambiente em um ponto espec\u00edfico do passado, utilizando SCN, tempo (data\/hora), Log Sequence Number ou um pr\u00f3prio restore point. Neste artigo vamos explorar na pr\u00e1tica uma recupera\u00e7\u00e3o do CDB todo, considerando que o mesmo esteja no modo […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,8],"tags":[],"class_list":["post-4768","post","type-post","status-publish","format-standard","hentry","category-backup-recovery","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4768","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=4768"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4768\/revisions"}],"predecessor-version":[{"id":9130,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4768\/revisions\/9130"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4768"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4768"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4768"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}