{"id":4867,"date":"2021-05-27T09:21:13","date_gmt":"2021-05-27T09:21:13","guid":{"rendered":"https:\/\/swiv.com.br\/performing-pitr-on-a-tablespace-in-a-pdb\/"},"modified":"2026-05-27T20:02:32","modified_gmt":"2026-05-27T19:02:32","slug":"performing-pitr-on-a-tablespace-in-a-pdb","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/05\/27\/performing-pitr-on-a-tablespace-in-a-pdb\/","title":{"rendered":"Performing PITR on a Tablespace in a PDB"},"content":{"rendered":"\n

Podemos ter um cen\u00e1rio de necessidade de recupera\u00e7\u00e3o mais controlada, ou seja, que n\u00e3o precisemos recuperar um PDB todo, mas sim alguma(s) tablespace(s) dele. Esse recurso \u00e9 \u00f3timo, pois preserva a opera\u00e7\u00e3o nas demais tablespaces do PDB, enquanto o processo de recupera\u00e7\u00e3o \u00e9 executado. Mas o \u00f4nus disso \u00e9 precisarmos de mais disco no ambiente, uma vez que uma inst\u00e2ncia auxiliar \u00e9 criada pelo RMAN para o recover tablespace (exigindo mais espa\u00e7o e tempo se comparado com o restore\/recover do PDB completo).<\/p>\n\n\n\n

Para iniciar, vamos identificar qual \u00e9 a tablespace afetada pela tabela que usaremos como teste neste artigo:<\/p>\n\n\n

\n[oracle@oel7 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu May 27 05:34:42 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> SELECT TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='TESTE1';\n \nTABLESPACE_NAME\n------------------------------\nUSERS\n<\/pre><\/div>\n\n\n
Confirmando se a tablespace USERS \u00e9 a default do nosso PDB. Nesse caso, precisaremos criar uma tablespace paralela para que exer\u00e7a essa fun\u00e7\u00e3o durante a recupera\u00e7\u00e3o da USERS:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';\n \nPROPERTY_VALUE\n--------------------------------------------------------------------------------\nUSERS\n \nSQL> SELECT STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='USERS';\n \nSTATUS\n---------\nONLINE\n \nSQL> CREATE TABLESPACE BSS;\n \nTablespace created.\n \nSQL> ALTER DATABASE DEFAULT TABLESPACE BSS;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
Realizando um backup FULL do CDB$ROOT e seus respectivos PDBs:<\/p>\n\n\n
\n[oracle@oel7 ~]$ rman target \/\n \nRecovery Manager: Release 19.0.0.0.0 - Production on Thu May 27 05:41:44 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: ASWN (DBID=3842619206)\n \nRMAN> BACKUP DATABASE PLUS ARCHIVELOG;\n \n \nStarting backup at 27-MAY-21\ncurrent log archived\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=448 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=1073538273\ninput archived log thread=1 sequence=7 RECID=2 STAMP=1073538438\ninput archived log thread=1 sequence=8 RECID=3 STAMP=1073538583\ninput archived log thread=1 sequence=9 RECID=4 STAMP=1073538589\ninput archived log thread=1 sequence=10 RECID=5 STAMP=1073538671\ninput archived log thread=1 sequence=11 RECID=6 STAMP=1073626917\nchannel ORA_DISK_1: starting piece 1 at 27-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 27-MAY-21\npiece handle=\/oracle\/fra\/ASWN\/backupset\/2021_05_27\/o1_mf_annnn_TAG20210527T054158_jbyptpk3_.bkp tag=TAG20210527T054158 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:17\nFinished backup at 27-MAY-21\n \nStarting backup at 27-MAY-21\nusing channel ORA_DISK_1\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\/ASWN\/datafile\/o1_mf_system_jbvxxtmm_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/ASWN\/datafile\/o1_mf_sysaux_jbvy05r2_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/ASWN\/datafile\/o1_mf_undotbs1_jbvy18tj_.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/ASWN\/datafile\/o1_mf_users_jbvy1cwc_.dbf\nchannel ORA_DISK_1: starting piece 1 at 27-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 27-MAY-21\npiece handle=\/oracle\/fra\/ASWN\/backupset\/2021_05_27\/o1_mf_nnndf_TAG20210527T054215_jbypv7jt_.bkp tag=TAG20210527T054215 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:15\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00010 name=\/oracle\/dados\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_sysaux_jbw01lh8_.dbf\ninput datafile file number=00009 name=\/oracle\/dados\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_system_jbw01lh3_.dbf\ninput datafile file number=00011 name=\/oracle\/dados\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_undotbs1_jbw01lh9_.dbf\ninput datafile file number=00012 name=\/oracle\/dados\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_users_jbw042o2_.dbf\ninput datafile file number=00013 name=\/oracle\/dados\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_bss_jbypnbl0_.dbf\nchannel ORA_DISK_1: starting piece 1 at 27-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 27-MAY-21\npiece handle=\/oracle\/fra\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/backupset\/2021_05_27\/o1_mf_nnndf_TAG20210527T054215_jbypxlgh_.bkp tag=TAG20210527T054215 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00006 name=\/oracle\/dados\/ASWN\/datafile\/o1_mf_sysaux_jbvyhq7z_.dbf\ninput datafile file number=00005 name=\/oracle\/dados\/ASWN\/datafile\/o1_mf_system_jbvyhq5r_.dbf\ninput datafile file number=00008 name=\/oracle\/dados\/ASWN\/datafile\/o1_mf_undotbs1_jbvyhq89_.dbf\nchannel ORA_DISK_1: starting piece 1 at 27-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 27-MAY-21\npiece handle=\/oracle\/fra\/ASWN\/C337AF22DF9E22E0E0536C00A8C0D589\/backupset\/2021_05_27\/o1_mf_nnndf_TAG20210527T054215_jbypycl2_.bkp tag=TAG20210527T054215 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:35\nFinished backup at 27-MAY-21\n \nStarting backup at 27-MAY-21\ncurrent log archived\nusing channel ORA_DISK_1\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=12 RECID=7 STAMP=1073627070\nchannel ORA_DISK_1: starting piece 1 at 27-MAY-21\nchannel ORA_DISK_1: finished piece 1 at 27-MAY-21\npiece handle=\/oracle\/fra\/ASWN\/backupset\/2021_05_27\/o1_mf_annnn_TAG20210527T054430_jbypzh1o_.bkp tag=TAG20210527T054430 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 27-MAY-21\n \nStarting Control File and SPFILE Autobackup at 27-MAY-21\npiece handle=\/oracle\/fra\/ASWN\/autobackup\/2021_05_27\/o1_mf_s_1073627072_jbypzk2r_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 27-MAY-21\n<\/pre><\/div>\n\n\n
Observando o conte\u00fado de nossa tabela de teste para podermos comparar ap\u00f3s a recupera\u00e7\u00e3o da tablespace:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> SELECT * FROM BSS1.TESTE1;\n \nDESCRICAO\n--------------------\nPITR PDB 1\nPITR PDB 2\nPITR PDB 3\n<\/pre><\/div>\n\n\n
Coletando o SCN atual do banco de dados:<\/p>\n\n\n
\nSQL> SELECT CURRENT_SCN FROM V$DATABASE;\n \nCURRENT_SCN\n-----------\n    2182658\n<\/pre><\/div>\n\n\n
Gerando alguns archives:<\/p>\n\n\n
\nSQL> conn \/ as sysdba\nConnected.\nSQL> ALTER SYSTEM SWITCH LOGFILE;\n\nSystem altered.\n\nSQL> \/\n\nSystem altered.\n<\/pre><\/div>\n\n\n
Deletando os registros da nossa tabela de testes:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> DELETE FROM BSS1.TESTE1;\n \n3 rows deleted.\n \nSQL> COMMIT;\n \nCommit complete.\n \nSQL> SELECT * FROM BSS1.TESTE1;\n \nno rows selected\n<\/pre><\/div>\n\n\n
Para nos prepararmos para a recupera\u00e7\u00e3o, vamos colocar a tablespace em quest\u00e3o no modo Offline:<\/p>\n\n\n
\nSQL> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;\n \nTablespace altered.\n<\/pre><\/div>\n\n\n
Como uma inst\u00e2ncia auxiliar ser\u00e1 criada automaticamente pelo RMAN, vou disponibilizar um diret\u00f3rio espec\u00edfico para que possa ser usado no processo:<\/p>\n\n\n
\n[oracle@oel7 BACKUP]$ pwd\n\/oracle\/BACKUP\n<\/pre><\/div>\n\n\n
Agora podemos disparar a recupera\u00e7\u00e3o da tablespace conforme comando abaixo no RMAN:<\/p>\n\n\n
\n[oracle@oel7 BACKUP]$ rman target \/\n \nRecovery Manager: Release 19.0.0.0.0 - Production on Thu May 27 05:51:33 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: ASWN (DBID=3842619206)\n \nRMAN> RECOVER TABLESPACE HIPOFISE1:USERS UNTIL SCN 2182658 AUXILIARY DESTINATION='\/oracle\/BACKUP';\n \nStarting recover at 27-MAY-21\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=29 device type=DISK\nRMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time\n \nList of tablespaces expected to have UNDO segments\nTablespace SYSTEM\nTablespace HIPOFISE1:SYSTEM\nTablespace UNDOTBS1\nTablespace HIPOFISE1:UNDOTBS1\n \nCreating automatic instance, with SID='jcoy'\n \ninitialization parameters used for automatic instance:\ndb_name=ASWN\ndb_unique_name=jcoy_pitr_HIPOFISE1_ASWN\ncompatible=19.0.0\ndb_block_size=8192\ndb_files=200\ndiagnostic_dest=\/oracle\/19.3.0\/base\n_system_trig_enabled=FALSE\ndb_domain=localdomain\nsga_target=1024M\nprocesses=200\ndb_create_file_dest=\/oracle\/BACKUP\nlog_archive_dest_1='location=\/oracle\/BACKUP'\nenable_pluggable_database=true\n_clone_one_pdb_recovery=true\n#No auxiliary parameter file used\n \n \nstarting up automatic instance ASWN\n \nOracle instance started\n \nTotal System Global Area    1073738288 bytes\n \nFixed Size                     9142832 bytes\nVariable Size                276824064 bytes\nDatabase Buffers             784334848 bytes\nRedo Buffers                   3436544 bytes\nAutomatic instance created\nRunning TRANSPORT_SET_CHECK on recovery set tablespaces\nTRANSPORT_SET_CHECK completed successfully\n \ncontents of Memory Script:\n{\n# set requested point in time\nset until  scn 2182658;\n# restore the controlfile\nrestore clone controlfile;\n \n# mount the controlfile\nsql clone 'alter database mount clone database';\n \n# archive current online log\nsql 'alter system archive log current';\n# avoid unnecessary autobackups for structural changes during TSPITR\nsql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';\n}\nexecuting Memory Script\n \nexecuting command: SET until clause\n \nStarting restore at 27-MAY-21\nallocated channel: ORA_AUX_DISK_1\nchannel ORA_AUX_DISK_1: SID=38 device type=DISK\n \nchannel ORA_AUX_DISK_1: starting datafile backup set restore\nchannel ORA_AUX_DISK_1: restoring control file\nchannel ORA_AUX_DISK_1: reading from backup piece \/oracle\/fra\/ASWN\/autobackup\/2021_05_27\/o1_mf_s_1073627072_jbypzk2r_.bkp\nchannel ORA_AUX_DISK_1: piece handle=\/oracle\/fra\/ASWN\/autobackup\/2021_05_27\/o1_mf_s_1073627072_jbypzk2r_.bkp tag=TAG20210527T054432\nchannel ORA_AUX_DISK_1: restored backup piece 1\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01\noutput file name=\/oracle\/BACKUP\/ASWN\/controlfile\/o1_mf_jbyqfg13_.ctl\nFinished restore at 27-MAY-21\n \nsql statement: alter database mount clone database\n \nsql statement: alter system archive log current\n \nsql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;\n \ncontents of Memory Script:\n{\n# set requested point in time\nset until  scn 2182658;\n# set destinations for recovery set and auxiliary set datafiles\nset newname for clone datafile  1 to new;\nset newname for clone datafile  9 to new;\nset newname for clone datafile  4 to new;\nset newname for clone datafile  11 to new;\nset newname for clone datafile  3 to new;\nset newname for clone datafile  10 to new;\nset newname for clone tempfile  1 to new;\nset newname for clone tempfile  3 to new;\nset newname for datafile  12 to\n "\/oracle\/dados\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_users_jbw042o2_.dbf";\n# switch all tempfiles\nswitch clone tempfile all;\n# restore the tablespaces in the recovery set and the auxiliary set\nrestore clone datafile  1, 9, 4, 11, 3, 10, 12;\n \nswitch clone datafile all;\n}\nexecuting Memory Script\n \nexecuting command: SET until clause\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nexecuting command: SET NEWNAME\n \nrenamed tempfile 1 to \/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_temp_%u_.tmp in control file\nrenamed tempfile 3 to \/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_temp_%u_.tmp in control file\n \nStarting restore at 27-MAY-21\nusing channel ORA_AUX_DISK_1\n \nchannel ORA_AUX_DISK_1: starting datafile backup set restore\nchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set\nchannel ORA_AUX_DISK_1: restoring datafile 00001 to \/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_system_%u_.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00004 to \/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_undotbs1_%u_.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00003 to \/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_sysaux_%u_.dbf\nchannel ORA_AUX_DISK_1: reading from backup piece \/oracle\/fra\/ASWN\/backupset\/2021_05_27\/o1_mf_nnndf_TAG20210527T054215_jbypv7jt_.bkp\nchannel ORA_AUX_DISK_1: piece handle=\/oracle\/fra\/ASWN\/backupset\/2021_05_27\/o1_mf_nnndf_TAG20210527T054215_jbypv7jt_.bkp tag=TAG20210527T054215\nchannel ORA_AUX_DISK_1: restored backup piece 1\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55\nchannel ORA_AUX_DISK_1: starting datafile backup set restore\nchannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set\nchannel ORA_AUX_DISK_1: restoring datafile 00009 to \/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_system_%u_.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00011 to \/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_undotbs1_%u_.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00010 to \/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_sysaux_%u_.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00012 to \/oracle\/dados\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_users_jbw042o2_.dbf\nchannel ORA_AUX_DISK_1: reading from backup piece \/oracle\/fra\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/backupset\/2021_05_27\/o1_mf_nnndf_TAG20210527T054215_jbypxlgh_.bkp\nchannel ORA_AUX_DISK_1: piece handle=\/oracle\/fra\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/backupset\/2021_05_27\/o1_mf_nnndf_TAG20210527T054215_jbypxlgh_.bkp tag=TAG20210527T054215\nchannel ORA_AUX_DISK_1: restored backup piece 1\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25\nFinished restore at 27-MAY-21\n \ndatafile 1 switched to datafile copy\ninput datafile copy RECID=10 STAMP=1073627604 file name=\/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_system_jbyqfn9v_.dbf\ndatafile 9 switched to datafile copy\ninput datafile copy RECID=11 STAMP=1073627604 file name=\/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_system_jbyqhccb_.dbf\ndatafile 4 switched to datafile copy\ninput datafile copy RECID=12 STAMP=1073627604 file name=\/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_undotbs1_jbyqfncn_.dbf\ndatafile 11 switched to datafile copy\ninput datafile copy RECID=13 STAMP=1073627604 file name=\/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_undotbs1_jbyqhccb_.dbf\ndatafile 3 switched to datafile copy\ninput datafile copy RECID=14 STAMP=1073627604 file name=\/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_sysaux_jbyqfncm_.dbf\ndatafile 10 switched to datafile copy\ninput datafile copy RECID=15 STAMP=1073627604 file name=\/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_sysaux_jbyqhc93_.dbf\n \ncontents of Memory Script:\n{\n# set requested point in time\nset until  scn 2182658;\n# online the datafiles restored or switched\nsql clone "alter database datafile  1 online";\nsql clone 'HIPOFISE1' "alter database datafile\n 9 online";\nsql clone "alter database datafile  4 online";\nsql clone 'HIPOFISE1' "alter database datafile\n 11 online";\nsql clone "alter database datafile  3 online";\nsql clone 'HIPOFISE1' "alter database datafile\n 10 online";\nsql clone 'HIPOFISE1' "alter database datafile\n 12 online";\n# recover and open resetlogs\nrecover clone database tablespace  "HIPOFISE1":"USERS", "SYSTEM", "HIPOFISE1":"SYSTEM", "UNDOTBS1", "HIPOFISE1":"UNDOTBS1", "SYSAUX", "HIPOFISE1":"SYSAUX" delete archivelog;\nalter clone database open resetlogs;\n}\nexecuting Memory Script\n \nexecuting command: SET until clause\n \nsql statement: alter database datafile  1 online\n \nsql statement: alter database datafile  9 online\n \nsql statement: alter database datafile  4 online\n \nsql statement: alter database datafile  11 online\n \nsql statement: alter database datafile  3 online\n \nsql statement: alter database datafile  10 online\n \nsql statement: alter database datafile  12 online\n \nStarting recover at 27-MAY-21\nusing channel ORA_AUX_DISK_1\n \nstarting media recovery\n \narchived log for thread 1 with sequence 12 is already on disk as file \/oracle\/archives\/1_12_1073536073.dbf\narchived log for thread 1 with sequence 13 is already on disk as file \/oracle\/archives\/1_13_1073536073.dbf\narchived log file name=\/oracle\/archives\/1_12_1073536073.dbf thread=1 sequence=12\narchived log file name=\/oracle\/archives\/1_13_1073536073.dbf thread=1 sequence=13\nmedia recovery complete, elapsed time: 00:00:01\nFinished recover at 27-MAY-21\n \ndatabase opened\n \ncontents of Memory Script:\n{\nsql clone 'alter pluggable database  HIPOFISE1 open';\n}\nexecuting Memory Script\n \nsql statement: alter pluggable database  HIPOFISE1 open\n \ncontents of Memory Script:\n{\n# make read only the tablespace that will be exported\nsql clone 'HIPOFISE1' 'alter tablespace\n USERS read only';\n# create directory for datapump import\nsql 'HIPOFISE1' "create or replace directory\nTSPITR_DIROBJ_DPDIR as ''\n\/oracle\/BACKUP''";\n# create directory for datapump export\nsql clone 'HIPOFISE1' "create or replace directory\nTSPITR_DIROBJ_DPDIR as ''\n\/oracle\/BACKUP''";\n}\nexecuting Memory Script\n \nsql statement: alter tablespace  USERS read only\n \nsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''\/oracle\/BACKUP''\n \nsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''\/oracle\/BACKUP''\n \nPerforming export of metadata...\n   EXPDP> Starting "SYS"."TSPITR_EXP_jcoy_hAvn":\n   EXPDP> Processing object type TRANSPORTABLE_EXPORT\/STATISTICS\/TABLE_STATISTICS\n   EXPDP> Processing object type TRANSPORTABLE_EXPORT\/STATISTICS\/MARKER\n   EXPDP> Processing object type TRANSPORTABLE_EXPORT\/PLUGTS_BLK\n   EXPDP> Processing object type TRANSPORTABLE_EXPORT\/POST_INSTANCE\/PLUGTS_BLK\n   EXPDP> Processing object type TRANSPORTABLE_EXPORT\/TABLE\n   EXPDP> Master table "SYS"."TSPITR_EXP_jcoy_hAvn" successfully loaded\/unloaded\n   EXPDP> ******************************************************************************\n   EXPDP> Dump file set for SYS.TSPITR_EXP_jcoy_hAvn is:\n   EXPDP>   \/oracle\/BACKUP\/tspitr_jcoy_47509.dmp\n   EXPDP> ******************************************************************************\n   EXPDP> Datafiles required for transportable tablespace USERS:\n   EXPDP>   \/oracle\/dados\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_users_jbw042o2_.dbf\n   EXPDP> Job "SYS"."TSPITR_EXP_jcoy_hAvn" successfully completed at Thu May 27 05:54:26 2021 elapsed 0 00:00:24\nExport completed\n \n \ncontents of Memory Script:\n{\n# shutdown clone before import\nshutdown clone abort\n# drop target tablespaces before importing them back\nsql 'HIPOFISE1' 'drop tablespace\n USERS including contents keep datafiles cascade constraints';\n}\nexecuting Memory Script\n \nOracle instance shut down\n \nsql statement: drop tablespace  USERS including contents keep datafiles cascade constraints\n \nPerforming import of metadata...\n   IMPDP> Master table "SYS"."TSPITR_IMP_jcoy_Eaov" successfully loaded\/unloaded\n   IMPDP> Starting "SYS"."TSPITR_IMP_jcoy_Eaov":\n   IMPDP> Processing object type TRANSPORTABLE_EXPORT\/PLUGTS_BLK\n   IMPDP> Processing object type TRANSPORTABLE_EXPORT\/TABLE\n   IMPDP> Processing object type TRANSPORTABLE_EXPORT\/STATISTICS\/TABLE_STATISTICS\n   IMPDP> Processing object type TRANSPORTABLE_EXPORT\/STATISTICS\/MARKER\n   IMPDP> Processing object type TRANSPORTABLE_EXPORT\/POST_INSTANCE\/PLUGTS_BLK\n   IMPDP> Job "SYS"."TSPITR_IMP_jcoy_Eaov" successfully completed at Thu May 27 05:55:01 2021 elapsed 0 00:00:23\nImport completed\n \n \ncontents of Memory Script:\n{\n# make read write and offline the imported tablespaces\nsql 'HIPOFISE1' 'alter tablespace\n USERS read write';\nsql 'HIPOFISE1' 'alter tablespace\n USERS offline';\n# enable autobackups after TSPITR is finished\nsql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';\n}\nexecuting Memory Script\n \nsql statement: alter tablespace  USERS read write\n \nsql statement: alter tablespace  USERS offline\n \nsql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;\n \nRemoving automatic instance\nAutomatic instance removed\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_temp_jbyqjtbn_.tmp deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_temp_jbyqjpwb_.tmp deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/onlinelog\/o1_mf_3_jbyqjh1l_.log deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/onlinelog\/o1_mf_2_jbyqj78m_.log deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/onlinelog\/o1_mf_1_jbyqj766_.log deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_sysaux_jbyqhc93_.dbf deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_sysaux_jbyqfncm_.dbf deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_undotbs1_jbyqhccb_.dbf deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_undotbs1_jbyqfncn_.dbf deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/C3380A6B7D7F3074E0536C00A8C08618\/datafile\/o1_mf_system_jbyqhccb_.dbf deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/datafile\/o1_mf_system_jbyqfn9v_.dbf deleted\nauxiliary instance file \/oracle\/BACKUP\/ASWN\/controlfile\/o1_mf_jbyqfg13_.ctl deleted\nauxiliary instance file tspitr_jcoy_47509.dmp deleted\nFinished recover at 27-MAY-21\n<\/pre><\/div>\n\n\n
De forma resumida, o que conseguimos abstrair do Log de recupera\u00e7\u00e3o: a inst\u00e2ncia auxiliar \u00e9 criada automaticamente, e percebemos que a sua defini\u00e7\u00e3o de mem\u00f3ria \u00e9 baseada na inst\u00e2ncia original. Desse modo, devemos garantir que nossa m\u00e1quina possuir\u00e1 o recurso de mem\u00f3ria necess\u00e1rio. Depois disso, o control file \u00e9 recuperado, o banco montado, e \u00e9 iniciado o restore dos datafiles para o diret\u00f3rio tempor\u00e1rio que definimos. O recover dos datafiles \u00e9 realizado e o banco \u00e9 aberto em noresetlogs. Um directory \u00e9 criado para o mesmo diret\u00f3rio que definimos, e um expdp da tablespace recuperada \u00e9 disparado. Por fim, o banco e inst\u00e2ncia auxiliares s\u00e3o removidos e \u00e9 realizado um IMPDP da tablespace no ambiente original.<\/p>\n\n\n\n
Agora podemos colocar a tablespace USERS recuperada em status de online:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> SELECT STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='USERS';\n \nSTATUS\n---------\nOFFLINE\n \nSQL> ALTER TABLESPACE USERS ONLINE;\n \nTablespace altered.\n<\/pre><\/div>\n\n\n
Confirmando que nossa tabela de teste est\u00e1 com o conte\u00fado desejado:<\/p>\n\n\n
\nSQL> SELECT * FROM BSS1.TESTE1;\n \nDESCRICAO\n--------------------\nPITR PDB 1\nPITR PDB 2\nPITR PDB 3\n<\/pre><\/div>\n\n\n
Em ambientes corporativos, \u00e9 indicado realizar um backup completo do ambiente ap\u00f3s esse procedimento.<\/p>\n\n\n\n
Tornando a tablespace USERS novamente a default do nosso PDB, e caso a tablespace criada n\u00e3o possua segmentos, podemos remov\u00ea-la. Caso contr\u00e1rio, basta fazermos o move dos segmentos para a tablespace USERS:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE1;\n \nSession altered.\n \nSQL> SELECT COUNT(*) FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='BSS';\n \n  COUNT(*)\n----------\n         0\n \nSQL> ALTER DATABASE DEFAULT TABLESPACE USERS;\n \nDatabase altered.\n \nSQL> DROP TABLESPACE BSS INCLUDING CONTENTS AND DATAFILES;\n \nTablespace dropped.\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":"
Podemos ter um cen\u00e1rio de necessidade de recupera\u00e7\u00e3o mais controlada, ou seja, que n\u00e3o precisemos recuperar um PDB todo, mas sim alguma(s) tablespace(s) dele. Esse recurso \u00e9 \u00f3timo, pois preserva a opera\u00e7\u00e3o nas demais tablespaces do PDB, enquanto o processo de recupera\u00e7\u00e3o \u00e9 executado. Mas o \u00f4nus disso \u00e9 precisarmos de mais disco no ambiente, […]<\/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-4867","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\/4867","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=4867"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4867\/revisions"}],"predecessor-version":[{"id":9128,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4867\/revisions\/9128"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4867"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4867"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4867"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}