第二天-下午筆記oracle管理課程

5練習(xí)5-1：創(chuàng)建快速OracleEnterpriseManagerDatabaseControl配置備份優(yōu)化并啟用塊更改EM的“Database（數(shù)據(jù)庫(kù)）”主頁(yè)，導(dǎo)航到“AvailabilitySettingsPolicy（>>策略）”，并啟用備份優(yōu)化和塊更改單擊“OK（確定）”滾動(dòng)到頁(yè)面底部，然后輸入oracle作為用戶名，oracle作為口令。同時(shí)，選中將其“SaveasPreferredCredential（另存為首選明）”的選項(xiàng)。單擊“OK（確 ”確保ORACLE_SID變量已設(shè)置為ORCL$$.ORACLE_SID=[orcl]?登錄RMAN0$$rmantarget/catalogrecoverycatalogdatabasePassword:oracle_4U<<<notRMAN>backupincrementallevel0databaseplusstartingfullresyncofrecoverycatalogfullresynccomplete... T190557comment=NONEchannelORA_DISK_1:backupsetcomplete,elapsedtime:Finishedbackupat21-JUL-StartingControlFileandSPFILEAutobackupat21-JUL-09 65FinishedControlFileandSPFILEAutobackupat21-JUL-有了0級(jí)增量備份后，就可以利用塊更改功能進(jìn)行快速增量備份了。創(chuàng)1RMAN>RMAN>backupincrementallevel1databaseplusStartingbackupat21-JUL-09currentlogarchivedusingchannel...FinishedControlFileandSPFILEAutobackupat21-JUL-在另一個(gè)終端窗口中，進(jìn)入到 下$$cd復(fù)查并執(zhí)行query_block_count.sh以查詢V$BACKUP_DATAFILE，了解為創(chuàng)建1級(jí)增量備份而的塊數(shù)。$$catquery_block_count.shexportORACLE_SID=orclsqlplus/assysdba<<-EOIselectfile#,avg(datafile_blocks),avg(blocks_read/datafile_blocks)*100asfromwhereused_change_tracking='YES'andincremental_level>0groupbyfile#; 2345678PCT_READ_FOR_BACKUPAVG(BLOCKS)-1.251.141 $ 41 $練習(xí)5-2：交叉檢查 確保在 $$cd$.ORACLESID=[orcl]?使用上一個(gè)練習(xí)中的RMAN會(huì)話，或者啟動(dòng)RMAN。確保同時(shí)連接到目標(biāo)數(shù)據(jù) $$rmantarget/catalogconnectedtotargetdatabase:ORCL)recoverycatalogdatabasePassword:oracle_4U<<<notconnectedtorecoverycatalog列出數(shù)據(jù)文件5的備份，請(qǐng)注意對(duì)應(yīng)于example數(shù)據(jù)文件的條RMAN>listbackupofdatafileBSKeyTypeLVSizeDeviceTypeElapsedTimeCompletion37Full68.99MDISK00:00:132009-07-BPKey:40Status:AVAILABLECompressed:NOTag: Piece ListofDatafilesinbackupset37FileLVTypeCkpSCNCkpTime 5Full9085952009-07-BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletion-172Incr01.07GDISK00:02:472009-07-BPKey:176Status:AVAILABLECompressed:NOTag: Piece ListofDatafilesinbackupset172FileLVTypeCkpSCNCkpTime 50Incr9241522009-07-BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletion237Incr13.74MDISK00:00:022009-07-BPKey:241Status:AVAILABLECompressed:NOTag: PiecePiece ListofDatafilesinbackupsetFileLVTypeCkpSCNCkp 51Incr9249532009-07-5的唯一一份完全備份。此外，“PieceName（片段名）”是備份文件所在的ASM文件的名稱。在OS終端窗口中，刪除備份集文件。啟動(dòng)另一個(gè)終端會(huì)話，以便RMAN會(huì)話+ASMrm_asm_file.sh。將完整的“PieceName（片段名）”用作的變?cè)?$.ORACLE_SID=[orcl]?$catrm_asm_file.shasmcmd<<-EOIrm-f$1>ASMCMD>ASMCMD>$在仍處于連接狀態(tài)的RMANRMAN>RMAN>listbackupofdatafileListofBackupBSKeyTypeLVSizeDeviceTypeElapsedTimeCompletion37Full68.99MDISK00:00:132009-07-BPKey:40Status:AVAILABLECompressed:NOTag: PieceListofDatafilesinbackupset37FileLVTypeCkpSCNCkpTime 5Full9085952009-07- BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletion172Incr01.07GDISK00:02:472009-07-BPKey:176Status:AVAILABLECompressed:NOTag: Piece ListofDatafilesinbackupset172FileLVTypeCkpSCNCkpTime 50Incr9241522009-07-BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletion237Incr1374MDISK00:00:022009-07-BPKey:241Status:AVAILABLECompressed:NOTag: Piece ListofDatafilesinbackupset237FileLVTypeCkpSCNCkpTime 51Incr9249532009-07- 交叉檢查備份集，使恢 識(shí)別備份文件已丟失RMAN>RMAN>crosscheckallocatedchannel:channelORA_DISK_1:SID=48devicetype=DISKallocatedchannel:ORA_SBT_TAPE_1channelORA_SBT_TAPE_1:SID=57devicetype=SBT_TAPEchannelORA_SBT_TAPE_1:WARNING:OracleTestDiskAPIcrosscheckedbackuppiece:foundtobe'EXPIRED'backuppiece RECID=1STAMP=crosscheckedbackuppiece:foundtobe...刪除所有過(guò)期的備份集RMAN>RMAN>deleteexpiredusingchannelORA_DISK_1usingchannelORA_SBT_TAPE_1ListofBackupBPKeyBSKeyPc#Cp#StatusDeviceTypePiece403711EXPIRED.Doyoureallywanttodeletetheaboveobjects(enterYESorNO)?YESdeletedbackuppiecebackuppiece 8_0260. RECID=1STAMP=Deleted1EXPIRED再次列出備份集?？梢钥吹絼倓h除的備份已不再列出RMAN>RMAN>listbackupofdatafileListofBackupBSKeyTypeLVSizeDeviceTypeElapsedTimeCompletion172Incr0107G BPKey:176Status:AVAILABLECompressed:NOTag: Piece ListofDatafilesinbackupset172FileLVTypeCkpSCNCkpTime 50Incr9241522009-07-TypeLVDeviceTypeElapsedTime237237Incr13.74M 00:00:022009-BPKey:241Status:AVAILABLECompressed:NOTag: Piece .ListofDatafilesinbackupset237FileLVTypeCkpSCNCkpTime -51Incr9249532009-07-練習(xí)5-3：列出備份文件并創(chuàng)建繼續(xù)使用上一個(gè)練習(xí)中的RMANRMAN>reportRMANretentionpolicywillbeappliedtothecommandRMANretentionpolicyissettoredundancy1Reportofobsoletebackupsand KeyCompletionTimeBackup 382009-07-BackupPiece412009-07- Backup 682009-07-BackupPiece712009-07-21:16:29:17Backup 772009-07-BackupPiece792009-07-21:16:29:22 -Backup 932009-07-BackupPiece962009-07-21:16:32:10Archive 1512009-07-BackupSet 1052009-07-21:16:32:14BackupPiece1072009-07-21:16:32:14 -Archive 1632009-07-BackupSet 171200907-21:19:02:53BackupPiece1752009-07- .Backup 2052009-07-BackupPiece2072009-07- 1如果保留策略將冗余設(shè)置為2，而不1，則哪些備份將被視為過(guò)時(shí)？回答此RMAN>RMAN>reportobsoleteredundancyReportofobsoletebackupsand KeyCompletionTimeBackup382009-07-BackupBackupPiece412009-07-Backup 682009-07-BackupPiece712009-07-21:16:29:17Backup 772009-07-BackupPiece792009-07-21:16:29:22 -Backup BackupPiece1072009-07-21:16:32:14 -刪除所有過(guò)時(shí)備RMAN>deletenopromptRMANretentionpolicywillbeappliedtothecommandRMANretentionpolicyissettoredundancy1usingchannelORA_DISK_1usingchannelORA_SBT_TAPE_1Deletingthefollowingobsoletebackupsandcopies: KeyCompletionTimeFilename/HandleBackup 382009-07-BackupPiece412009-07- Backup 682009-07-BackupPiece712009-07-21:16:29:17Backup 772009-07-...deletedbackupbackuppiecehandle=05kkn0sa_1_1RECID=5STAMP=deletedbackuppiecebackuppiecehandle=c- -02RECID=6Deleted4列出可用來(lái)恢復(fù)到一小時(shí)前的備份歸檔日志文件SYSDATESYSDATE限定符時(shí)list命令的運(yùn)行結(jié)果與未使用該限定符時(shí)有何不同。例如，嘗試用'sysdate-05/60/24'表示5分鐘前。RMAN>listbackupofarchiveloguntiltime'sysdate-ListofBackupBSKeySizeDeviceTypeElapsedTimeCompletion--1911.05M BPKey:197Status:AVAILABLECompressed:NOTag: Piece .ListofArchivedLogsinbackupset191ThrdSeqLowSCNLowTime NextSCNNext1119241262009-07-21:19:02:4692462707-BSKeySizeDeviceTypeElapsedTimeCompletion--236277.00K BPKey:240Status:AVAILABLECompressedNOTag: Piece .ListofArchivedLogsinbackupset236ThrdSeqLowSCNLowTime NextSCNNext1129246272009-07-21:19:05:5392492807-BSBSKeySizeDeviceTypeElapsedTimeCompletion-2587.50K BPKey:264Status:AVAILABLECompressed:NOTag: Piece .ListofArchivedLogsinbackupset258ThrdSeqLowSCNLowTime NextSCNNext1139249282009-07-21:19:10:2692496007-列出USERS表空間中單個(gè)數(shù)據(jù)文件的映像生成方案的報(bào)表，確定屬于USERS表空間的數(shù)據(jù)文件RMAN>reportReportofdatabaseschemafordatabasewithdb_unique_nameListofPermanentFileSize(MB) RBsegsDatafile680 590 100 4 5100 ListListofTemporaryFileSize(MB)Maxsize(MB)Tempfile128 請(qǐng)注意，相關(guān)文件是為4的數(shù)據(jù)文件列出為4的數(shù)據(jù)文件的所有映像副本RMAN>RMAN>listcopyofdatafilespecificationdoesnotmatchanydatafilecopyintherepository請(qǐng)注 在本例中該文件沒(méi)有映像副本4的所有映像副本。RMAN>RMAN>deletecopyofdatafilereleasedchannel:ORA_DISK_1releasedchannel:ORA_SBT_TAPE1allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=105dvicetype=DISKListofDatafileCopiesKeyFileSCompletionTimeCkpSCNCkp---1154A2009-05- -Name:Doyoureallywanttodeletetheaboveobjects(enterYESorNO)?yesRMAN>listcopyofdatafilespecificationdoesnotmatchanydatafilecopyinthe為了簡(jiǎn)化培訓(xùn)環(huán)境，請(qǐng)禁用下幾個(gè)步驟的控制文件自動(dòng)備RMAN>RMAN>configurecontrolfileautobackupoldRMANconfigurationCONFIGURECONTROLFILEAUTOBACKUPnewRMANconfigurationCONFIGURECONTROLFILEAUTOBACKUPnewRMANconfigurationparametersaresuccessfullystoredstartingfullresyncofrecoverycatalogfullresync為數(shù)據(jù)文件4創(chuàng)建一個(gè)映像文件備份RMAN>RMAN>backupascopydatafileStartingbackupat2009-07-21:20:27:25releasedchannel:ORA_SBT_TAPE_1usingchannelORA_DISK_1channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00004outputfilename=+FRA/orcl/datafile/users.274. T202725RECID=2STAMP=channelORA_DISK_1:datafilecopycomplete,elapsedtime:Finishedbackupat2009-07-再為該數(shù)據(jù)文件創(chuàng)建一個(gè)映像副本，但是將其創(chuàng)建為歸檔備份RMAN>RMAN>backupascopydatafile4keepStartingbackupat2009-07-21:20:28:30currentlogarchivedusingchannelORA_DISK_1backupwillneverbeobsoletearchivedlogsrequiredtorecoverfromthisbackupwillbebackedupchannelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00004RMAN-00569:===============ERRORMESSAGESTACKFOLLOWSRMAN-03002:failureofbackupcommandat07/21/200920:28:34ORA-19811:cannothavefilesinDB_RECOVERY_FILE_DESTwithkeep 中RMAN>RMAN>backupkeepforeverascopydatafile4format'/tmp/bu_%d_%s_%pdbf';Startingbackupat2009-07-21:20:31:14currentlogarchivedusingchannelORA_DISK_1backupwillneverbeobsoletearchivedlogsrequiredtorecoverfromthisbackupwillbebackdupchannelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00004outputfilename=/tmp/bu_ORCL_17_1.dbftag=TAG RECID=3STAMP=channelORA_DISK_1:datafilecopycomplete,elapsedtime:...piecehandle=/tmp/bu_ORCL_20_1.dbftag=TAG channelORA_DISK_1:ackupsetcomplete,elapsedtime:Finishedbackupat2009-07-查看是否存在過(guò)時(shí)備份。（如果重復(fù)練習(xí)步驟，則可能獲得過(guò)時(shí)備份。RMAN>RMAN>reportRMANretentionpolicywillbeappliedtothecommandRMANretentionpolicyissettoredundancy1noobsoletebackups創(chuàng)建數(shù)據(jù)文件4的另一（非歸檔）備份RMAN>RMAN>backupascopydatafileStartingbackupat2009-07-21:20:33:52usingcha