O recurso de TSPITR \u00e9 ideal para os casos onde precisamos recuperar uma ou mais tablespaces em um ponto no tempo sem afetar o funcionamento do resto do banco de dados (logicamente para user tablespaces). A execu\u00e7\u00e3o do processo \u00e9 simples, mas nos bastidores temos as seguintes etapas sendo realizadas pelo RMAN:<\/p>\n\n\n\n
O processo em si tem os modos Fully Automated (padr\u00e3o), Autometed e Non-Autometed. Neste artigo vamos explorar o primeiro cen\u00e1rio.<\/p>\n\n\n\n
Vamos realizar o reconhecimento ao nosso ambiente laborat\u00f3rio:<\/p>\n\n\n
\n[oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Aug 9 20:04:15 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 banco de dados:<\/p>\n\n\n
\nRMAN> BACKUP DATABASE;\n \nStarting backup at 2021-08-09:20:06:17\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=59 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-09:20:06:18\nchannel ORA_DISK_1: finished piece 1 at 2021-08-09:20:08:03\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_09\/o1_mf_nnndf_TAG20210809T200618_jk3f7fbd_.bkp tag=TAG20210809T200618 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:45\nFinished backup at 2021-08-09:20:08:03\n \nStarting Control File and SPFILE Autobackup at 2021-08-09:20:08:03\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_08_09\/o1_mf_s_1080158883_jk3fbn9x_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-08-09:20:08:06\n<\/pre><\/div>\n\n\nA tabela que usaremos como refer\u00eancia est\u00e1 hospedada na tablespace USERS, conforme \u00e9 demonstrado:<\/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 DISTINCT(TABLESPACE_NAME) FROM DBA_SEGMENTS WHERE SEGMENT_NAME='PITR';\n \nTABLESPACE_NAME\n------------------------------\nUSERS\n<\/pre><\/div>\n\n\nColetando o hor\u00e1rio que usaremos em nossa 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-09:20:11:06\n<\/pre><\/div>\n\n\nVamos dropar a nossa tabela para simular o evento gatilho da recupera\u00e7\u00e3o:<\/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<\/pre><\/div>\n\n\nPara demonstrarmos uma situa\u00e7\u00e3o espec\u00edfica, vamos criar outra tabela ap\u00f3s o “incidente”:<\/p>\n\n\n
\nRMAN> CREATE TABLE BSS.BRUNO AS SELECT * FROM DBA_USERS;\n\nStatement processed\n<\/pre><\/div>\n\n\nNesse ponto, podemos iniciar o processo de recupera\u00e7\u00e3o em si. Como identificamos que a tablespace afetada \u00e9 a USERS, devemos nos certificar que a mesma \u00e9 classificada como “self-contained”, ou seja, que n\u00e3o tenha depend\u00eancia de outras tablespaces em seus objetos. Para isso, rodamos o comando abaixo:<\/p>\n\n\n
\n[oracle@oel8 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Mon Aug 9 20:17:49 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> exec DBMS_TTS.TRANSPORT_SET_CHECK('USERS', TRUE,TRUE);\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\nSe algo n\u00e3o estiver conforme o esperado, ser\u00e1 reportado na consulta abaixo:<\/p>\n\n\n
\nSQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;\n\nno rows selected\n<\/pre><\/div>\n\n\nCom a consulta abaixo, podemos ver todos os objetos criados ap\u00f3s o incidente, ou seja, que n\u00e3o ser\u00e3o contemplados na recupera\u00e7\u00e3o. Caso esses objetos sejam importantes, devemos realizar o backup dos mesmos (com o Data Pump, por exemplo), e ap\u00f3s o TSPITR, import\u00e1-los. Como o intuito aqui \u00e9 apenas did\u00e1tico, o simples fato de este item ser exposto j\u00e1 vale. N\u00e3o precisarei deste objeto:<\/p>\n\n\n
\nSQL> col OWNER for a10\ncol NAME for a10\ncol TABLESPACE_NAME for a10\nSELECT OWNER, NAME, TABLESPACE_NAME FROM TS_PITR_OBJECTS_TO_BE_DROPPED WHERE TABLESPACE_NAME IN ('USERS') AND CREATION_TIME > TO_DATE('2021-08-09:20:11:06','YYYY-MM-DD:HH24:MI:SS') ORDER BY 1,2;SQL> SQL> SQL>\n \nOWNER NAME TABLESPACE\n---------- ---------- ----------\nBSS BRUNO USERS\n<\/pre><\/div>\n\n\nAgora vamos criar um diret\u00f3rio espec\u00edfico que ser\u00e1 usado por nossa inst\u00e2ncia auxiliar:<\/p>\n\n\n
\n[oracle@oel8 oracle]$ pwd\n\/oracle\n[oracle@oel8 oracle]$ mkdir TSPITR\n[oracle@oel8 oracle]$ cd TSPITR\/\n[oracle@oel8 TSPITR]$ pwd\n\/oracle\/TSPITR\n<\/pre><\/div>\n\n\nComo o modo escolhido \u00e9 o totalmente autom\u00e1tico, para iniciarmos o processo de recupera\u00e7\u00e3o, basta rodarmos uma \u00fanica linha de c\u00f3digo, conforme exemplo e log abaixo:<\/p>\n\n\n
\n[oracle@oel8 TSPITR]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Aug 9 20:40:06 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> RECOVER TABLESPACE USERS UNTIL TIME "to_date('2021-08-09:20:11:06','YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '\/oracle\/TSPITR';\n \nStarting recover at 2021-08-09:20:40:18\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=106 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 UNDOTBS1\n \nCreating automatic instance, with SID='veas'\n \ninitialization parameters used for automatic instance:\ndb_name=RMANDB\ndb_unique_name=veas_pitr_RMANDB\ncompatible=18.0.0\ndb_block_size=8192\ndb_files=200\ndiagnostic_dest=\/oracle\/18.0.0\/base\n_system_trig_enabled=FALSE\nsga_target=1536M\nprocesses=200\ndb_create_file_dest=\/oracle\/TSPITR\nlog_archive_dest_1='location=\/oracle\/TSPITR'\n#No auxiliary parameter file used\n \n \nstarting up automatic instance RMANDB\n \nOracle instance started\n \nTotal System Global Area 1610612016 bytes\n \nFixed Size 8658224 bytes\nVariable Size 402653184 bytes\nDatabase Buffers 1191182336 bytes\nRedo Buffers 8118272 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 time "to_date('2021-08-09:20:11:06','YYYY-MM-DD:HH24:MI:SS')";\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 2021-08-09:20:40:36\nallocated channel: ORA_AUX_DISK_1\nchannel ORA_AUX_DISK_1: SID=34 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\/RMANDB\/autobackup\/2021_08_09\/o1_mf_s_1080158883_jk3fbn9x_.bkp\nchannel ORA_AUX_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_08_09\/o1_mf_s_1080158883_jk3fbn9x_.bkp tag=TAG20210809T200803\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\/TSPITR\/RMANDB\/controlfile\/o1_mf_jk3h7o4v_.ctl\nFinished restore at 2021-08-09:20:40:38\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 time "to_date('2021-08-09:20:11:06','YYYY-MM-DD:HH24:MI:SS')";\n# set destinations for recovery set and auxiliary set datafiles\nset newname for clone datafile 1 to new;\nset newname for clone datafile 4 to new;\nset newname for clone datafile 3 to new;\nset newname for clone tempfile 1 to new;\nset newname for datafile 7 to\n "\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.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, 4, 3, 7;\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 \nrenamed tempfile 1 to \/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_temp_%u_.tmp in control file\n \nStarting restore at 2021-08-09:20:40:43\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\/TSPITR\/RMANDB\/datafile\/o1_mf_system_%u_.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00004 to \/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_undotbs1_%u_.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00003 to \/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_sysaux_%u_.dbf\nchannel ORA_AUX_DISK_1: restoring datafile 00007 to \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_AUX_DISK_1: reading from backup piece \/oracle\/fra\/RMANDB\/backupset\/2021_08_09\/o1_mf_nnndf_TAG20210809T200618_jk3f7fbd_.bkp\nchannel ORA_AUX_DISK_1: piece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_09\/o1_mf_nnndf_TAG20210809T200618_jk3f7fbd_.bkp tag=TAG20210809T200618\nchannel ORA_AUX_DISK_1: restored backup piece 1\nchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:45\nFinished restore at 2021-08-09:20:43:28\n \ndatafile 1 switched to datafile copy\ninput datafile copy RECID=49 STAMP=1080161008 file name=\/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_system_jk3h7vm3_.dbf\ndatafile 4 switched to datafile copy\ninput datafile copy RECID=50 STAMP=1080161008 file name=\/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_undotbs1_jk3h7vos_.dbf\ndatafile 3 switched to datafile copy\ninput datafile copy RECID=51 STAMP=1080161008 file name=\/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_sysaux_jk3h7vop_.dbf\n \ncontents of Memory Script:\n{\n# set requested point in time\nset until time "to_date('2021-08-09:20:11:06','YYYY-MM-DD:HH24:MI:SS')";\n# online the datafiles restored or switched\nsql clone "alter database datafile 1 online";\nsql clone "alter database datafile 4 online";\nsql clone "alter database datafile 3 online";\nsql clone "alter database datafile 7 online";\n# recover and open resetlogs\nrecover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "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 4 online\n \nsql statement: alter database datafile 3 online\n \nsql statement: alter database datafile 7 online\n \nStarting recover at 2021-08-09:20:43:29\nusing channel ORA_AUX_DISK_1\n \nstarting media recovery\n \narchived log for thread 1 with sequence 1 is already on disk as file \/oracle\/archives\/1_1_1079643731.dbf\narchived log for thread 1 with sequence 2 is already on disk as file \/oracle\/archives\/1_2_1079643731.dbf\narchived log file name=\/oracle\/archives\/1_1_1079643731.dbf thread=1 sequence=1\narchived log file name=\/oracle\/archives\/1_2_1079643731.dbf thread=1 sequence=2\nmedia recovery complete, elapsed time: 00:00:00\nFinished recover at 2021-08-09:20:43:31\n \ndatabase opened\n \ncontents of Memory Script:\n{\n# make read only the tablespace that will be exported\nsql clone 'alter tablespace USERS read only';\n# create directory for datapump import\nsql "create or replace directory TSPITR_DIROBJ_DPDIR as ''\n\/oracle\/TSPITR''";\n# create directory for datapump export\nsql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''\n\/oracle\/TSPITR''";\n}\nexecuting Memory Script\n \nsql statement: alter tablespace USERS read only\n \nsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''\/oracle\/TSPITR''\n \nsql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''\/oracle\/TSPITR''\n \nPerforming export of metadata...\n EXPDP> Starting "SYS"."TSPITR_EXP_veas_Fofq":\n EXPDP> Processing object type TRANSPORTABLE_EXPORT\/INDEX\/STATISTICS\/INDEX_STATISTICS\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> Processing object type TRANSPORTABLE_EXPORT\/INDEX\/INDEX\n EXPDP> Processing object type TRANSPORTABLE_EXPORT\/CONSTRAINT\/CONSTRAINT\n EXPDP> Processing object type TRANSPORTABLE_EXPORT\/COMMENT\n EXPDP> Processing object type TRANSPORTABLE_EXPORT\/CONSTRAINT\/REF_CONSTRAINT\n EXPDP> Processing object type TRANSPORTABLE_EXPORT\/TRIGGER\n EXPDP> Master table "SYS"."TSPITR_EXP_veas_Fofq" successfully loaded\/unloaded\n EXPDP> ******************************************************************************\n EXPDP> Dump file set for SYS.TSPITR_EXP_veas_Fofq is:\n EXPDP> \/oracle\/TSPITR\/tspitr_veas_10076.dmp\n EXPDP> ******************************************************************************\n EXPDP> Datafiles required for transportable tablespace USERS:\n EXPDP> \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\n EXPDP> Job "SYS"."TSPITR_EXP_veas_Fofq" successfully completed at Mon Aug 9 20:44:40 2021 elapsed 0 00:00:46\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 'drop tablespace 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_veas_qlri" successfully loaded\/unloaded\n IMPDP> Starting "SYS"."TSPITR_IMP_veas_qlri":\n IMPDP> Processing object type TRANSPORTABLE_EXPORT\/PLUGTS_BLK\n IMPDP> Processing object type TRANSPORTABLE_EXPORT\/TABLE\n IMPDP> Processing object type TRANSPORTABLE_EXPORT\/INDEX\/INDEX\n IMPDP> Processing object type TRANSPORTABLE_EXPORT\/CONSTRAINT\/CONSTRAINT\n IMPDP> Processing object type TRANSPORTABLE_EXPORT\/INDEX\/STATISTICS\/INDEX_STATISTICS\n IMPDP> Processing object type TRANSPORTABLE_EXPORT\/COMMENT\n IMPDP> Processing object type TRANSPORTABLE_EXPORT\/CONSTRAINT\/REF_CONSTRAINT\n IMPDP> Processing object type TRANSPORTABLE_EXPORT\/TRIGGER\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_veas_qlri" successfully completed at Mon Aug 9 20:45:20 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 'alter tablespace USERS read write';\nsql 'alter tablespace 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\/TSPITR\/RMANDB\/datafile\/o1_mf_temp_jk3hfms7_.tmp deleted\nauxiliary instance file \/oracle\/TSPITR\/RMANDB\/onlinelog\/o1_mf_3_jk3hfcxs_.log deleted\nauxiliary instance file \/oracle\/TSPITR\/RMANDB\/onlinelog\/o1_mf_2_jk3hf3qn_.log deleted\nauxiliary instance file \/oracle\/TSPITR\/RMANDB\/onlinelog\/o1_mf_1_jk3hf3q1_.log deleted\nauxiliary instance file \/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_sysaux_jk3h7vop_.dbf deleted\nauxiliary instance file \/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_undotbs1_jk3h7vos_.dbf deleted\nauxiliary instance file \/oracle\/TSPITR\/RMANDB\/datafile\/o1_mf_system_jk3h7vm3_.dbf deleted\nauxiliary instance file \/oracle\/TSPITR\/RMANDB\/controlfile\/o1_mf_jk3h7o4v_.ctl deleted\nauxiliary instance file tspitr_veas_10076.dmp deleted\nFinished recover at 2021-08-09:20:45:23\n \nRMAN>\n<\/pre><\/div>\n\n\nComo podemos ver, a tablespace recuperado est\u00e1 OFFLINE. Isso acontece pois, como \u00e9 recomenda\u00e7\u00e3o da pr\u00f3pria Oracle, \u00e9 mister realizarmos um backup m\u00ednimo na estrutura rec\u00e9m-recuperada:<\/p>\n\n\n
\nRMAN> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='USERS';\n \nTABLESPACE_NAME STATUS\n------------------------------ ---------\nUSERS OFFLINE\n<\/pre><\/div>\n\n\nPor isso vou realizar um backup simples da tablespace:<\/p>\n\n\n
\nRMAN> BACKUP TABLESPACE USERS;\n \nStarting backup at 2021-08-09:20:49:05\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=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-08-09:20:49:05\nchannel ORA_DISK_1: finished piece 1 at 2021-08-09:20:49:08\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_09\/o1_mf_nnndf_TAG20210809T204905_jk3hql56_.bkp tag=TAG20210809T204905 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nFinished backup at 2021-08-09:20:49:08\n \nStarting Control File and SPFILE Autobackup at 2021-08-09:20:49:08\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_08_09\/o1_mf_s_1080161348_jk3hqp3z_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-08-09:20:49:11\n<\/pre><\/div>\n\n\nDeixando a tablespace ONLINE:<\/p>\n\n\n
\nRMAN> ALTER TABLESPACE USERS ONLINE;\n\nStatement processed\n<\/pre><\/div>\n\n\nValidando que nossa tabela est\u00e1 devidamente recuperada:<\/p>\n\n\n
\nRMAN> SELECT * FROM BSS.PITR;\n\nDESCRICAO\n--------------------------------------------------\nPITR DO BRUNO\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":"
O recurso de TSPITR \u00e9 ideal para os casos onde precisamos recuperar uma ou mais tablespaces em um ponto no tempo sem afetar o funcionamento do resto do banco de dados (logicamente para user tablespaces). A execu\u00e7\u00e3o do processo \u00e9 simples, mas nos bastidores temos as seguintes etapas sendo realizadas pelo RMAN: A tablespace \u00e9 […]<\/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-6106","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\/6106","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=6106"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6106\/revisions"}],"predecessor-version":[{"id":9065,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6106\/revisions\/9065"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}