Temos no RMAN uma op\u00e7\u00e3o interessante de catalogar certos tipos de arquivos, para que o mesmo tenha ci\u00eancia da exist\u00eancia desses arquivos e suas localiza\u00e7\u00f5es, permitindo assim que possa os utilizar em algum caso de restaura\u00e7\u00e3o ou solicita\u00e7\u00e3o de informa\u00e7\u00f5es (via LIST, por exemplo). Os 4 tipos de artefatos aceitos para essa opera\u00e7\u00e3o s\u00e3o: CONTROLFILECOPY, DATAFILECOPY, BACKUPPIECE e ARCHIVELOG. Algumas situa\u00e7\u00f5es que podem necessitar do catalog seria altera\u00e7\u00e3o do local onde os arquivos ficam (seja file system ou DiskGroup), altera\u00e7\u00e3o do archiving destination, backups que n\u00e3o est\u00e3o mais na reten\u00e7\u00e3o do RMAN, ou arquivos de backup gerados por terceiros e que devem ser usados por n\u00f3s para restaura\u00e7\u00e3o. Neste artigo vamos explorar algumas op\u00e7\u00f5es do Catalog.<\/p>\n\n\n\n
Realizando alguns backups no ambiente:<\/p>\n\n\n
\n[oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Tue Jun 8 04:40:56 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> CONFIGURE CONTROLFILE AUTOBACKUP OFF;\n \nusing target database control file instead of recovery catalog\nnew RMAN configuration parameters:\nCONFIGURE CONTROLFILE AUTOBACKUP OFF;\nnew RMAN configuration parameters are successfully stored\n \nRMAN> BACKUP AS BACKUPSET TABLESPACE users TAG 'USERS_BS';\n \nStarting backup at 2021-06-08:04:41:07\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=103 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=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-06-08:04:41:07\nchannel ORA_DISK_1: finished piece 1 at 2021-06-08:04:41:10\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_06_08\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp tag=USERS_BS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nFinished backup at 2021-06-08:04:41:10\n \nRMAN> BACKUP AS COPY TABLESPACE users TAG 'USERS_DF';\n \nStarting backup at 2021-06-08:04:41:18\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting datafile copy\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\noutput file name=\/oracle\/fra\/RMANDB\/datafile\/o1_mf_users_jcy7ryy0_.dbf tag=USERS_DF RECID=44 STAMP=1074660079\nchannel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01\nFinished backup at 2021-06-08:04:41:19\n \nRMAN> BACKUP AS BACKUPSET ARCHIVELOG ALL TAG 'ARC_BS';\n \nStarting backup at 2021-06-08:04:41:26\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=68 RECID=75 STAMP=1069389781\ninput archived log thread=1 sequence=69 RECID=77 STAMP=1069390191\ninput archived log thread=1 sequence=70 RECID=78 STAMP=1069391841\ninput archived log thread=1 sequence=71 RECID=79 STAMP=1069392032\ninput archived log thread=1 sequence=72 RECID=80 STAMP=1069392053\ninput archived log thread=1 sequence=73 RECID=83 STAMP=1069392224\ninput archived log thread=1 sequence=74 RECID=84 STAMP=1069392226\ninput archived log thread=1 sequence=75 RECID=85 STAMP=1069430460\ninput archived log thread=1 sequence=76 RECID=86 STAMP=1069567218\ninput archived log thread=1 sequence=77 RECID=87 STAMP=1069581356\ninput archived log thread=1 sequence=78 RECID=88 STAMP=1070953640\ninput archived log thread=1 sequence=79 RECID=89 STAMP=1070992854\ninput archived log thread=1 sequence=80 RECID=90 STAMP=1071054025\ninput archived log thread=1 sequence=81 RECID=91 STAMP=1071075652\ninput archived log thread=1 sequence=82 RECID=92 STAMP=1071117108\ninput archived log thread=1 sequence=83 RECID=93 STAMP=1071140420\ninput archived log thread=1 sequence=84 RECID=94 STAMP=1071180017\ninput archived log thread=1 sequence=85 RECID=95 STAMP=1071190844\ninput archived log thread=1 sequence=86 RECID=96 STAMP=1071205211\ninput archived log thread=1 sequence=87 RECID=97 STAMP=1071205218\ninput archived log thread=1 sequence=88 RECID=98 STAMP=1071219611\ninput archived log thread=1 sequence=89 RECID=99 STAMP=1071376111\ninput archived log thread=1 sequence=90 RECID=100 STAMP=1071381640\ninput archived log thread=1 sequence=91 RECID=101 STAMP=1071396592\ninput archived log thread=1 sequence=92 RECID=102 STAMP=1071411059\ninput archived log thread=1 sequence=93 RECID=103 STAMP=1071552261\ninput archived log thread=1 sequence=94 RECID=104 STAMP=1071553535\ninput archived log thread=1 sequence=95 RECID=105 STAMP=1071553599\ninput archived log thread=1 sequence=96 RECID=106 STAMP=1071553824\ninput archived log thread=1 sequence=97 RECID=107 STAMP=1071554229\ninput archived log thread=1 sequence=98 RECID=108 STAMP=1071590436\ninput archived log thread=1 sequence=99 RECID=109 STAMP=1071636084\ninput archived log thread=1 sequence=100 RECID=110 STAMP=1071811216\ninput archived log thread=1 sequence=101 RECID=111 STAMP=1073371215\ninput archived log thread=1 sequence=102 RECID=112 STAMP=1073372104\ninput archived log thread=1 sequence=103 RECID=113 STAMP=1073374794\ninput archived log thread=1 sequence=104 RECID=114 STAMP=1073404855\ninput archived log thread=1 sequence=105 RECID=115 STAMP=1074660088\nchannel ORA_DISK_1: starting piece 1 at 2021-06-08:04:41:30\nchannel ORA_DISK_1: finished piece 1 at 2021-06-08:04:42:45\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_06_08\/o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp tag=ARC_BS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:15\nFinished backup at 2021-06-08:04:42:45\n \nRMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;\n \nold RMAN configuration parameters:\nCONFIGURE CONTROLFILE AUTOBACKUP OFF;\nnew RMAN configuration parameters:\nCONFIGURE CONTROLFILE AUTOBACKUP ON;\nnew RMAN configuration parameters are successfully stored\n<\/pre><\/div>\n\n\nDefinindo um diret\u00f3rio alternativo onde moverei os arquivos de backup:<\/p>\n\n\n
\n[oracle@oel8 BACKUP]$ pwd\n\/oracle\/BACKUP\n<\/pre><\/div>\n\n\nRecuperando os backuppieces gerados para mov\u00ea-los de diret\u00f3rio:<\/p>\n\n\n
\nRMAN> LIST BACKUPSET TAG 'USERS_BS';\n \nusing target database control file instead of recovery catalog\n \nList of Backup Sets\n===================\n \n \nBS Key Type LV Size Device Type Elapsed Time Completion Time\n------- ---- -- ---------- ----------- ------------ -------------------\n329 Full 13.25M DISK 00:00:01 2021-06-08:04:41:08\n BP Key: 355 Status: AVAILABLE Compressed: NO Tag: USERS_BS\n Piece Name: \/oracle\/fra\/RMANDB\/backupset\/2021_06_08\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp\n List of Datafiles in backup set 329\n File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name\n ---- -- ---- ---------- ------------------- ----------- ------ ----\n 7 Full 2747657 2021-06-08:04:41:07 NO \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\n \nRMAN> LIST BACKUPSET TAG 'ARC_BS';\n \n \nList of Backup Sets\n===================\n \n \nBS Key Size Device Type Elapsed Time Completion Time\n------- ---------- ----------- ------------ -------------------\n330 1.02G DISK 00:00:21 2021-06-08:04:41:51\n BP Key: 356 Status: AVAILABLE Compressed: NO Tag: ARC_BS\n Piece Name: \/oracle\/fra\/RMANDB\/backupset\/2021_06_08\/o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp\n \n List of Archived Logs in backup set 330\n Thrd Seq Low SCN Low Time Next SCN Next Time\n ---- ------- ---------- ------------------- ---------- ---------\n 1 68 2255756 2021-03-28:10:39:58 2262113 2021-04-09:04:43:00\n 1 69 2262113 2021-04-09:04:43:00 2262505 2021-04-09:04:49:50\n 1 70 2262505 2021-04-09:04:49:50 2265296 2021-04-09:05:17:21\n 1 71 2265296 2021-04-09:05:17:21 2265473 2021-04-09:05:20:32\n 1 72 2265473 2021-04-09:05:20:32 2265531 2021-04-09:05:20:53\n 1 73 2265531 2021-04-09:05:20:53 2265643 2021-04-09:05:23:29\n 1 74 2265643 2021-04-09:05:23:29 2265663 2021-04-09:05:23:44\n 1 75 2265663 2021-04-09:05:23:44 2290054 2021-04-09:16:01:00\n 1 76 2290054 2021-04-09:16:01:00 2314554 2021-04-11:06:00:15\n 1 77 2314554 2021-04-11:06:00:15 2328698 2021-04-11:09:55:53\n 1 78 2328698 2021-04-11:09:55:53 2332924 2021-04-27:07:07:17\n 1 79 2332924 2021-04-27:07:07:17 2358174 2021-04-27:18:00:52\n 1 80 2358174 2021-04-27:18:00:52 2381091 2021-04-28:11:00:22\n 1 81 2381091 2021-04-28:11:00:22 2394102 2021-04-28:17:00:49\n 1 82 2394102 2021-04-28:17:00:49 2500292 2021-04-29:04:31:47\n 1 83 2500292 2021-04-29:04:31:47 2519661 2021-04-29:11:00:18\n 1 84 2519661 2021-04-29:11:00:18 2546021 2021-04-29:22:00:14\n 1 85 2546021 2021-04-29:22:00:14 2559326 2021-04-30:01:00:41\n 1 86 2559326 2021-04-30:01:00:41 2570072 2021-04-30:05:00:08\n 1 87 2570072 2021-04-30:05:00:08 2570544 2021-04-30:05:00:15\n 1 88 2570544 2021-04-30:05:00:15 2580734 2021-04-30:09:00:09\n 1 89 2580734 2021-04-30:09:00:09 2607950 2021-05-01:04:28:27\n 1 90 2607950 2021-05-01:04:28:27 2620519 2021-05-01:06:00:37\n 1 91 2620519 2021-05-01:06:00:37 2634806 2021-05-01:10:09:50\n 1 92 2634806 2021-05-01:10:09:50 2648651 2021-05-01:14:10:57\n 1 93 2648651 2021-05-01:14:10:57 2655511 2021-05-03:05:24:19\n 1 94 2655511 2021-05-03:05:24:19 2656517 2021-05-03:05:45:34\n 1 95 2656517 2021-05-03:05:45:34 2656617 2021-05-03:05:46:39\n 1 96 2656617 2021-05-03:05:46:39 2656827 2021-05-03:05:50:24\n 1 97 2656827 2021-05-03:05:50:24 2657118 2021-05-03:05:57:09\n 1 98 2657118 2021-05-03:05:57:09 2679421 2021-05-03:16:00:34\n 1 99 2679421 2021-05-03:16:00:34 2688357 2021-05-04:04:41:23\n 1 100 2688357 2021-05-04:04:41:23 2696947 2021-05-06:05:20:14\n 1 101 2696947 2021-05-06:05:20:14 2708591 2021-05-24:06:40:11\n 1 102 2708591 2021-05-24:06:40:11 2716825 2021-05-24:06:55:02\n 1 103 2716825 2021-05-24:06:55:02 2718910 2021-05-24:07:39:52\n 1 104 2718910 2021-05-24:07:39:52 2740096 2021-05-24:16:00:54\n 1 105 2740096 2021-05-24:16:00:54 2747712 2021-06-08:04:41:26\n \nRMAN> HOST 'mv \/oracle\/fra\/RMANDB\/backupset\/2021_06_08\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp \/oracle\/BACKUP\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp';\n \nhost command complete\n \nRMAN> HOST 'mv \/oracle\/fra\/RMANDB\/backupset\/2021_06_08\/o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp \/oracle\/BACKUP\/o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp';\n \nhost command complete\n<\/pre><\/div>\n\n\nFazendo o mesmo processo com o backup as image copy:<\/p>\n\n\n
\nRMAN> LIST COPY TAG 'USERS_DF';\n \nspecification does not match any control file copy in the repository\nList of Datafile Copies\n=======================\n \nKey File S Completion Time Ckp SCN Ckp Time Sparse\n------- ---- - ------------------- ---------- ------------------- ------\n44 7 A 2021-06-08:04:41:19 2747684 2021-06-08:04:41:18 NO\n Name: \/oracle\/fra\/RMANDB\/datafile\/o1_mf_users_jcy7ryy0_.dbf\n Tag: USERS_DF\n \n \nRMAN> HOST 'mv \/oracle\/fra\/RMANDB\/datafile\/o1_mf_users_jcy7ryy0_.dbf \/oracle\/BACKUP\/o1_mf_users_jcy7ryy0_.dbf';\n \nhost command complete\n<\/pre><\/div>\n\n\nChecando o conte\u00fado do novo diret\u00f3rio:<\/p>\n\n\n
\n[oracle@oel8 BACKUP]$ pwd\n\/oracle\/BACKUP\n[oracle@oel8 BACKUP]$ ll\ntotal 1103440\n-rw-r-----. 1 oracle oinstall 1098969600 Jun 8 04:41 o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp\n-rw-r-----. 1 oracle oinstall 13901824 Jun 8 04:41 o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp\n-rw-r-----. 1 oracle oinstall 17047552 Jun 8 04:41 o1_mf_users_jcy7ryy0_.dbf\n<\/pre><\/div>\n\n\nRealizando o Catalog de um backuppiece:<\/p>\n\n\n
\nRMAN> CATALOG BACKUPPIECE '\/oracle\/BACKUP\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp';\n \nusing target database control file instead of recovery catalog\ncataloged backup piece\nbackup piece handle=\/oracle\/BACKUP\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp RECID=357 STAMP=1074661035\n<\/pre><\/div>\n\n\nCatalogando um Datafile Copy:<\/p>\n\n\n
\nRMAN> CATALOG DATAFILECOPY '\/oracle\/BACKUP\/o1_mf_users_jcy7ryy0_.dbf';\n \nusing target database control file instead of recovery catalog\ncataloged datafile copy\ndatafile copy file name=\/oracle\/BACKUP\/o1_mf_users_jcy7ryy0_.dbf RECID=45 STAMP=1074661118\n<\/pre><\/div>\n\n\nCaso tenhamos um grande n\u00famero de arquivos para serem catalogados, podemos realizar o processo considerando o diret\u00f3rio, conforme exemplo abaixo. Como 2 arquivos j\u00e1 haviam sido catalogados, o RMAN s\u00f3 pede autoriza\u00e7\u00e3o do terceiro. Caso n\u00e3o deseje que o RMAN fique confirmando os arquivos a serem catalogados, basta adicionar a op\u00e7\u00e3o NOPROMPT no comando:<\/p>\n\n\n
\nRMAN> CATALOG START WITH '\/oracle\/BACKUP\/';\n \nsearching for all files that match the pattern \/oracle\/BACKUP\/\n \nList of Files Unknown to the Database\n=====================================\nFile Name: \/oracle\/BACKUP\/o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp\n \nDo you really want to catalog the above files (enter YES or NO)? Y\ncataloging files...\ncataloging done\n \nList of Cataloged Files\n=======================\nFile Name: \/oracle\/BACKUP\/o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp\n<\/pre><\/div>\n\n\nAgora copiar um dos backuppieces para dentro da FRA:<\/p>\n\n\n
\n[oracle@oel8 BACKUP]$ ll\ntotal 1103440\n-rw-r-----. 1 oracle oinstall 1098969600 Jun 8 04:41 o1_mf_annnn_ARC_BS_jcy7sbrx_.bkp\n-rw-r-----. 1 oracle oinstall 13901824 Jun 8 04:41 o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp\n-rw-r-----. 1 oracle oinstall 17047552 Jun 8 04:41 o1_mf_users_jcy7ryy0_.dbf\n[oracle@oel8 BACKUP]$ cp o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp \/oracle\/fra\/RMANDB\/\narchivelog\/ autobackup\/ backupset\/ controlfile\/ datafile\/ onlinelog\/\n[oracle@oel8 BACKUP]$ cp o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp \/oracle\/fra\/RMANDB\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp\n[oracle@oel8 BACKUP]$\n<\/pre><\/div>\n\n\nNo momento do catalog, podemos pedir ao RMAN que realize o processo direto na FRA:<\/p>\n\n\n
\nRMAN> CATALOG RECOVERY AREA;\n \nusing target database control file instead of recovery catalog\nsearching for all files in the recovery area\n \nList of Files Unknown to the Database\n=====================================\nFile Name: \/oracle\/fra\/RMANDB\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp\n \nDo you really want to catalog the above files (enter YES or NO)? y\ncataloging files...\ncataloging done\n \nList of Cataloged Files\n=======================\nFile Name: \/oracle\/fra\/RMANDB\/o1_mf_nnndf_USERS_BS_jcy7rmz4_.bkp\n<\/pre><\/div>\n\n\nPara finalizar, realizo uma c\u00f3pia manual do archive (sequence 107) para nosso diret\u00f3rio alternativo e tento realizar o catalog ARCHIVELOG:<\/p>\n\n\n
\n[oracle@oel8 archives]$ pwd\n\/oracle\/archives\n[oracle@oel8 archives]$ cp 1_107_1036964654.dbf \/oracle\/BACKUP\/1_107_1036964654.dbf\n[oracle@oel8 archives]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Tue Jun 8 05:14:33 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> CATALOG ARCHIVELOG '\/oracle\/BACKUP\/1_107_1036964654.dbf';\n \nusing target database control file instead of recovery catalog\ncataloged archived log\narchived log file name=\/oracle\/BACKUP\/1_107_1036964654.dbf RECID=118 STAMP=1074662083\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":"
Temos no RMAN uma op\u00e7\u00e3o interessante de catalogar certos tipos de arquivos, para que o mesmo tenha ci\u00eancia da exist\u00eancia desses arquivos e suas localiza\u00e7\u00f5es, permitindo assim que possa os utilizar em algum caso de restaura\u00e7\u00e3o ou solicita\u00e7\u00e3o de informa\u00e7\u00f5es (via LIST, por exemplo). Os 4 tipos de artefatos aceitos para essa opera\u00e7\u00e3o s\u00e3o: CONTROLFILECOPY, […]<\/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-5264","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\/5264","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=5264"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5264\/revisions"}],"predecessor-version":[{"id":9108,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5264\/revisions\/9108"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}