随堂笔记新053课堂

第一章：备份恢复概述第二章：第三章：手工完全恢复）第五章：闪回flashback第六章：RMAN第七章：RMAN第八章：RMAN完全恢复第九章：RMAN不完全恢复 第十一章：RMAN第十二章：Oracle资源管理userprocessfailure：pmoninstance smonusererrors media 1）传统的导入导出：exp/imp：2）恢复就是还原备份，没有recover的概念。原与恢复过程自动完成，可以备份恢复ASMFILE。11gflashback归档模式：redologarchive非归档模式：没有archivelog,redolog RMAN冷备 RMAN热备 2）恢复用sqlplus命令:recover备份前要对数据库进行检查:有关的视图：SQL>selectnamefromSQL>selectfile_id,file_name,tablespace_namefromdba_data_files;SQL>selectnamefromv$controlfile;3）redo日志不需要做备份SQL>alterdatabasebeginbackup; SQL>alterdatabaseendbackup;SQL>altertablespaceusersbeginbackup; SQL>altertablespaceusersendbackup;备份期间利用v$backupSQL>altertablespacetestbeginSQL>selectfile#,checkpoint_change#fromFILE#1234567SQL>select*from

FILE# CHANGE#10203040506 2012-07-307NOT $cptest01.dbfSQL>altertablespacetestSQL>select*fromFILE#1NOT02NOT03NOT04NOT05NOT0NOTNOT02012-07-30splitOracleblockOSblock,，当手工热备时，OScpblcok就是splitblock。以保证所有被恢复的oracleblock是一个完整的版本。 [oracle@timranadmin]$ helpDBVERIFY:Release11.1.0.6.0-ProductiononMonJul3011:11:072012Copyright(c)1982,2007,Oracle. s. Fileto Start (FirstBlockof End (LastBlockof LogicalBlock Output Disy Parameter SEGMENT_IDSegmentID(tsn.relfile.block)(NONE)HighestBlockSCNToVerify _baseORscn)admin]$[oracle@timranadmin]$dbvfile=/u01/oradata/timran11g/users01.dbfDBVERIFYFILE=DBVERIFY00000 :最高块 :695469后一次commit状态。1）restore:OS拷贝命令还原所有或部分datafile2）recoverdatabase：datafilemountrecoverdatafile：mountopen考点：哪些文件是关键文件：四个：1）system01.dbf,2)undotablespace，3）controlfile4）currentlogfile恢复过程可以查看的视图：1）v$recover_file：查看需要恢复的datafile件和控制文件mount下发出alterdatabaseclearlogfilegroup<组号>.（RMANSQL>select*from1SQL>shutdown[oracle@timran~]$cp/u01/oradata/timran11g/*.dbf/u01/back1[oracle@timran~]$cp/u01/oradata/timran11g/*.ctl[oracle@timran~]SQL>insertintoscott.testvalues(2);SQL>commit;SQL>select*from1SQL>insertintoscott.testvalues(3);SQL>commit;SQL>select*from31[oracle@timran~]$rm/u01/oradata/timran11g/*.dbf 删掉了所有dbf文件SQL>shutdown ORACLEinstanceshutSQL>startupORACLEinstanceORA-01157:cannotidentify/lockdatafile1-seeDBWRtracefileORA-01110:datafile1:'/u01/oradata/timran11g/system01.dbf'SQL>selectfile#,errorfromFILE#FILENOTFILENOTFILENOTFILENOTFILENOTFILENOTFILENOTSQL>selectfile#,checkpoint_change#fromv$datafile;FILE#CHECKPOINT_CHANGE#1234567SQL>selectfile#,checkpoint_change#fromv$datafile_header;FILE#CHECKPOINT_CHANGE#1234567scnscn，归档日志+当前日志都完整，[oracle@timran~]$cp/u01/back1/*.dbfSQL>recoverdatabase;SQL>alterdatabaseopen;SQL>select*from31,只需针对个别有问题的tablespace去做单独的恢复操作，也就是说恢复时数据库整体是online的,而局部表空间是offline的，数据库不需要shutdown。1)了解一下当前状态，有个test表空间，SQL>selectfile_id,file_name,tablespace_namefromFILE_ID 4 3 2 1 5 7 6 SQL>connscott/scottSQL>createtablet1(namechar(10))tablespacetest;SQL>insertintot1values('a');SQL>SQL>select*froma[oracle@timran~]$rm/u01/oradata/timran11g/test01.dbf[oracle@timran~]$SQL>altersystemflush SQL>conn/as SQL>select*fromscott.t1;select*fromscott.t1*ERRORatlineORA-01116:errorinopeningdatabasefileORA-01110:datafile6:'/u01/oradata/timran11g/test01.dbf'ORA-27041:unabletoopenfileLinuxError:2:NosuchfileordirectoryAdditionalinformation:3SQL>selectfile#,checkpoint_change#fromv$datafile;FILE#CHECKPOINT_CHANGE#123456SQL>selectfile#,checkpoint_change#fromv$datafile_header;FILE#CHECKPOINT_CHANGE#12345 SQL>altertablespacetestofflineimmediate; 机，不等Oracle对任何数据文件做检查[oracle@timran~]$cp/u01/back1/test01.dbf/u01/oradata/timran11g[oracle@timran~]$SQL>recovertablespaceSQL>altertablespacetest 9)SQL>select*fromscott.t1;a3（recover以只能在mount状态下恢复。SQL>insertintoscott.t1values('b'); //一行记录是为了使t1和备SQL>SQL>select*fromSQL>delete open[oracle@timran~]$SQL>shtudown SQL>startupmountORA-01157:无法标识/锁定数据文件3-请参阅DBWR文ORA-01110:3:[oracle@timrantimran11g]$cp/u01/back1/undotbs01.dbf./SQL>recoverdatafile3;SQL>alterdatabase7)SQL>select*fromrecover recoveralterdatabaserecovertablespaceopenOS:使用cp还原该表空间XXX下的所有数据文件altertablespaceXXXrecovertablespacealtertablespaceXXX3.6.3recoverdatafile 键文件在open下进行)recoverdatafilealterdatabasealterdatabasedatafile6,8recoverdatafilealterdatabasedatafile6,83）必须以sysdba连接进行不完全恢复，普通用户或sysoper都不行（考点。4）recoverdatabaseuntil 4）（复基于时间点（until 基于scn（untilchange： 基于cancel(untilcancel)： otroil还原该时间点前所有的datafile将恢复出来的table4.5、logminer工具的使用scnsqlSQL>connSQL>createtablet1(idint)tablespacetest;SQL>insertintot1values(1);SQL>insertintot1values(2);SQL>insertintot1values(3);SQL>commit;SQL>select*from23SQL>droptablet1purge;SQL>select*from MEMBERSARCHIVEDSTATUSFIRST_CHANGE#FIRST_TIME111-6-13211-6-13311-7-18SQL>altersystemswitchlogfile;SQL>/SQL>SQL>selectnamefromv$archived_log; _133.log//droptablet1purge 116rowsSQL>sho rameterutl

SQL>select v$logmnr_contentsWHERElower(sql_redo)like'droptable%'; SCNTO_CHAR(TIMESTAMP,'YYYY-MM-DDH -07-18 tabletesttablestudenttablet1-08-SQL>executeSQL>shutdown[oracle@timran~]$cd[oracle@timran~]$rm[oracle@timran~]$cp/u01/back1/*.dbf17:31:43SQL>startupORACLEinstancestarted.TotalSystemGlobal Fixed Variable Database Redo DatabaseORA-01113:file1needsmediaORA-01110:datafile1:'/u01/oradata/timran11g/system01.dbf'17:33:07SQL>recoverdatabaseuntiltime'2012-08-0117:28:29';ORA-00279:change generatedat07/18/201216:46:34neededforthread1ORA-00289:suggestion:/u01/disk1/timran/arch_1_ ORA-00280: forthread1isinsequence17:33:17Specifylog:{<RET>=suggested|filename|AUTO|CANCEL}LogMediarecoverySQL>alterdatabaseopenresetlogs;SQL>select*from23SQL>select*from MEMBERSARCHIVEDSTATUSFIRST_CHANGE#FIRST_TIME 1 0 1 0 1 -8-1其命令格式只要将recover命令换成下面即可：SQL>recoverdatabaseuntil recoverdatabaseuntil[time|change]usingbackupSpecifylog:{<RET>=suggested|filename|AUTO|件可能是老的或尚未更新的（shutdwonabort过)。 archivelogcurrentlog;filename：currentlogCANCEL：。2）使用backupcontrolfile子句的恢复数据库，一律要使用alterdatabaseopenresetlogs打开数据库。SQL>select*fromFLA0146823NOSQL>select*fromMEMBERSARC 2013-01-17 YES2013-01-17 YES2013-01-17SQL>createtablespaceabcddatafile'/u01/oradata/timran11g/abcd01.dbf'size5m;SQL>createtablescott.a1(namechar(10))tablespaceabcd;SQL>insertintoscott.a1values('a');SQL>commit;SQL>select*fromaSQL>altersystemswitchlogfile; [oracle@timran~]$rm/u01/oradata/timran11g/abcd01.dbf open态，删除abcd01.dbf数据文件SQL>altersystemflush //dbbufferSQL>conn/assysdba SQL>select*fromscott.a1;select*fromscott.a1*1ORA-00376:此时无法文件ORA-01110:3:'/u01/oradata/timran11g/abcd01.dbf'SQL>shutdown[oracle@timrantimran11g]$cdu01/oradata/timran11g[oracle@timrantimran11g]$rm*.ctl[oracle@timrantimran11g]$rm*.dbf[oracle@timrantimran11g]$cp/u01/back1/*.dbf./[oracle@timrantimran11g]$cpcon.bak1control01.ctl[oracle@timrantimran11g]$cpcon.bak1control02.ctl[oracle@timrantimran11g]$cpcon.bak1control03.ctlSQL>TotalSystemGlobal Fixed Variable Database Redo ORA-01589:RESETLOGSNORESETLOGSSQL>colnameforSQL>selectfile#,checkpoint_change#,namefromFILE#CHECKPOINT_CHANGE#SQL>selectfile#,checkpoint_change#fromFILE#121234567可以看出：1）file3abcd01.dbf,3的，2）备份的数据备份的scn比控制文件scn的还老。SQL>recoverdatabaseusingbackupcontrolfile;ORA-00283:恢复会话因错误而取消ORA-01110:3:'/u01/oradata/timran11g/abcd01.dbf'ORA-01157:无法标识/锁定数据文件3-请参阅DBWR文件ORA-01110:3:一个datafile的空文件，而其中内容可由日志文件recover(前滚)时填补出来。SQL>alterdatabasecreatedatafileSQL>recoverdatabaseusingbackupORA-00308:无法打开归档日志'/u01/disk1/timran/arch_1_ ORA-27037:无法获得文件状态LinuxError:2:NosuchfileordirectoryAdditionalinformation:3SQL>recoverdatabaseusingbackupcontrolfile; 指定日志:{<RET>=suggested|filename|AUTO|CANCEL} 完成介质恢复。7）SQL>alterdatabaseopenSQL>select*from timran,控制文件备份里没有此表空间记录，但日志里有。SQL>droptablespaceabcdludingcontentsandSQL>alterdatabasebackupcontrolfileto'/u01/oradata/timran11g/con.bak2';SQL>createtablespacetimrandatafile'/u01/oradata/timran11g/timran01.dbf'size5m;SQL>createtablescott.r1(idint)tablespacetimran;SQL>insertintoscott.r1values(1);SQL>commit;SQL>select*fromTS# BIGFLAENC0 YESNO1 YESNO14 YESNO4 YESNO6 YESNO8 YESNO3 NONO2 YESNOSQL>select*fromscott.r1;1SQL>select*fromMEMBERS11112101003101[oracle@timrantimran11g]rmSQL>altersystemflushbuffer_cache;SQL>conn/assysdbaSQL>select*from1ORA-01116:3ORA-01110:3:'/u01/oradata/timran11g/timran01.dbf'ORA-27041:无法打开文件LinuxError:2:NosuchfileordirectoryAdditionalinformation:33)[oracle@timrantimran11g]$cd/u01/oradata/timran11g[oracle@timrantimran11g]$rm*.ctl[oracle@timrantimran11g]$rm*.dbf[oracle@timrantimran11g]$cp/u01/back1/*.dbf./[oracle@timrantimran11g]$cpcon.bak2control01.ctl[oracle@timrantimran11g]$cpcon.bak2control02.ctl[oracle@timrantimran11g]$cpcon.bak2control03.ctlSQL>ORA-01589:RESETLOGSNORESETLOGSSQL>selectfile#,checkpoint_change#,namefromv$datafile;FILE#CHECKPOINT_CHANGE#NAMESQL>selectfile#,checkpoint_change#fromv$datafile_header;FILE#124567SQL>recoverdatabaseusingbackupORA-00279:更 (在01/16/201314:11:39生成)对于线程1是必需ORA-00289:建议:/u01/disk1/timran/arch_1_ ORA-00280:更改 (用于线程1)在序列#4中{<RET>=suggested|filename|AUTO|CANCEL}{<RET>=suggested|filename|AUTO|ORA-01110:3:'/u01/oradata/timran11g/timran01.dbf'ORA-01112:未启动介质恢复SQL>selectfile#,checkpoint_change#,namefromFILE#CHECKPOINT_CHANGE# 老控制文件不知道的timran01.dbfSQL>selectfile#,checkpoint_change#fromv$datafile_header;FILE#12 4567 名控制文件UNNAMED00003为timran01.dbfSQL>selectfile#,checkpoint_change#,namefromv$datafile;FILE#CHECKPOINT_CHANGE#NAMESQL>recoverdatabaseusingbackupORA-00279:更 (在01/17/201314:20:50生成)对于线程1是必需ORA-00289:建议:/u01/disk1/timran/arch_1_ ORA-00280:更改 (用于线程1)在序列#1中{<RET>=suggested|filename|AUTO|完成介质恢复。8）SQL>alterdatabaseopenresetlogs;SQL>select*from23 分析：当用户使用droptablesapcexxxludingcontentsanddatafiles这条DDL语c）SQL>select*from0154683SQL>createtablescott.t1(idint)tablespacetest;SQL>insertintoscott.t1values(1);SQL>SQL>altersystemswitchlogfile;SQL>/SQL>SQL> fromSQL>alterdatabasebackupcontrolfiletoSQL>droptablespacetestludingcontentsanddatafiles;SQL>shutdownabort[oracle@timrantimran11g]$rm*.dbf[oracle@timrantimran11g]$rm*.ctl[oracle@timrantimran11g]$cp/u01/back1/*.dbf./[oracle@timrantimran11g]$cpcon.bakcontrol01.ctl[oracle@timrantimran11g]$cpcon.bakcontrol02.ctl[oracle@timrantimran11g]$cpcon.bakcontrol03.ctlSQL>startupORA-01589:RESETLOGSNORESETLOGSSQL>recoverdatabaseuntilchange usingbackupcontrolfile;SQL>alterdatabaseopenSQL>select*fromflashbackflashbacklogundodatadatabase 1）flashbackdropflashbackquery（flashbackdatabaseflashbacktable4）flashbackversionquery5）flashbacktransaction6）flashbackdatabasepurgeSQL>shorameterrecyclebin SQL>createtablespacetestdatafile'/u01/oradata/timran11g/test01.dbf'size1m;SQL>createtablescott.t1(idint)tablespacetest;SQL>selectsegment_namefromdba_segmentswhere-SQL>selectsum(bytes)fromdba__spacewhereSQL>insertintoscott.t1SQL>insertintoscott.t1select*from //1ORA-01653:SCOTT.T1stringTESTSQL>selectcount(*)fromscott.t1;SQL>selectsum(bytes)fromdba__spacewheretablespace_name='TEST'; SQL>droptableSQL>selectsegment_namefromdba_segmentswheretablespace_name='TEST';SQL>selectsum(bytes)fromdba__spacewheretablespace_name='TEST';autoextendOracleSQL>createtablescott.emp1tablespacetestasselect*fromscott.emp;//挤占test表空间SQL>selectsum(bytes)fromdba__spacewheretablespace_name='TEST';SQL>selectsegment_namefromdba_segmentswheretablespace_name='TEST';/t1SQL>flashbacktablet1tobefore SQL>purgetablet1; t1（考点。SQL>flashbacktable"BIN$qrJLbL74ZgvgQKjA8Agb/A==$0"tobefroedrop;SQL>purgetable"BIN$qrJLbL74ZgvgQKjA8Agb/A==$0";SQL>purge 如何恢复同一个schema下准备闪回的表已有同名的对象存在，闪回drop需要重命名. SQL>createtablet1asselect*fromemp;SQL>*TABTYPEBIN$qrJLbL76ZgvgQKjA8Agb/A==$0TABLE 06:56:50SQL>showORIGINAL RECYCLEBIN OBJECTTYPEDROP SQL>flashbacktabletesttobeforedrop;flashbacktabletesttobeforedrop*ERRORatlineORA-38312:originalnameisusedbyanexistingSQL>flashbacktablet1tobeforedroprenameto SQL>select*from TABTYPE createtablet(idint,namealtertabletaddconstraintpk_tprimarykey(id);insertintotvalues(1,'');insertintotvalues(2,'sina');SQL>select*from 12SQL>select*fromuser_indexes;SQL>select*fromuser_constraints;SQL>droptablet;SQL>select*fromtab; TABTYPEBIN$yF3hbvIcioTgQAB/AQAJlg==$0TABLE SQL>select*from SQL>select*from SQL>flashbacktablettobeforeSQL>select*fromI12SQL>select*from SQL>select*from SQL>alterindex"BIN$yF3hbvIbioTgQAB/AQAJlg==$0"renametoSQL>altertabletrenameconstraint"BIN$yF3hbvIaioTgQAB/AQAJlg==$0"toquery（1) asofscn|timestampcreatetablescott.student(snoint,snamechar(10),sageint);insertintoscott.studentvalues(1,'Tom',21);insertintoscott.studentvalues(2,'Kite',22);insertintoscott.studentvalues(3,'Bob',23);insertintoscott.studentvalues(4,'Mike',24);/SQL>select*from1234selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual; fromv$database;//取scn1deletescott.studentwheresno=1;select*from234selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual; fromv$database; //取scn2updatescott.studentsetsage=50;select*from234selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual; fromv$database;取scn3select*fromstudentasofscnscn1;createtablestudent2asselect*fromscott.studentasofscnscn1;droptablestudent;renamestudent2tostudent;select*fromstudent;*考点：可以查询以前某个时间点的数据库，但是不能对过去时间点得数据库做DML操闪回表(PPT-II-flashbacktable<table_name>totimestamp|altertablestudentenablerowmovement;flashbacktablestudenttoscnXXXXX是可行的，但Oracle建议它们与常规的数据分开存放更好。flashbackarchiveadminister//授予用户创建，修改或删除闪回回档flashbackarchive//授予用户对表进行归档。altertableflashbackarchive闪回归档enable也有个前提条件，undo_management要设为on,否则修改表 createtablespaceftafile'/u01/oradata/timran11g/fda01.dbf'size5m;createflashbackarchivefla1tablespacefdaquota2mretention1year;grantflashbackarchiveonfla1toaltertableemp1flashbackarchive尝试做一些DDL操作会报错：altertableemp1dropcolumncomm;truncatetableemp1;droptableORA-55610:针对记录表的DDL语句无效SQL>select*from SQL>select*fromdba_flashback_archive_ts; SQL>select*fromdba_flashback_archive_tables; //查看表emp1和归档名的关系。SQL>altertablescott.emp1noflashbackarchive; scott.emp1例2：通过一道考题 NotetheoutputofthefollowingSQL>SELECTflashback_archieve_name,statusFROMFLASHBACK_ARCHIEVE_NAMESTATUSYouexecutedthefollowingcommandtoenableFlashbackDataArchiveontheEXCHANGB_PATEtable:ALTERTABLEexchange_rateFLASHBACKARCHIEVE;Whatisthe eofthiscommand?ThetableusesthedefaultFlashbackDataTheFlashbackDataArchiveIscreatedIntheSYSAUXTheFlashbackDataArchiveiscreatedinthesametablespacewherethetablesarestored.ThecommandgeneratesanerrorbecausenoflashbackDataArchivenamespecifiedandthereisnodefaultFlashbackDataSQL>connassysdbaSQL>alterflashbackarchivefla1setSQL>selectFLASHBACK_ARCHIVE_NAME,STATUSfromSQL>altertablescott.emp1flashback FLA1（DEFAULTSQL>select*fromSQL>dropflashbackarchiveASSM …from versionscreatetablet3(idint,namechar(10));insertintot3values(1,'tim');insertintot3values(2,'mike');insertintot3values(3,'brain');insertintot3values(4,'cade');updatet3setname='nelson'whereid=4;deletet3whereid=2;updatet3setid=id+100;SQL>select ,versions_xid,versions_operation,imefromscott.t3versionsbetweenscnminvalueandSQL>selectversions_xid,versions_operation,id,namefromt3versionsbetweenscnminvalueandmaxvalue;VERSIONS_XID I

101 2

1234时表的撤销是基于session的。起数据变化的事务，和撤销事务的SQL语句SQL>descSQL>selectundo_sqlfromflashback_transaction_querywherexid=hextoraw('事务号 update"SYS"."T3"set"ID"='4'whereROWID='AAANByAABAAAO/yAAD';update"SYS"."T3"set"ID"='3'whereROWID='AAANByAABAAAO/yAAC';update"SYS"."T3"set"ID"='1'whereROWID='AAANByAABAAAO/yAAA';SQL>update"SYS"."T3"set"ID"='4'whereROWID='AAANByAABAAAO/yAAD';SQL>update"SYS"."T3"set"ID"='3'whereROWID='AAANByAABAAAO/yAAC';SQL>update"SYS"."T3"set"ID"='1'whereROWID='AAANByAABAAAO/yAAA';SQL>EnableSupplementalLogging10.0即闪回缓冲区，然后再由进程(RecoverWriteRVWR)将此闪回恢复区的内容刷新到磁盘闪回恢复区(flashrecoveryarea)是一个非常重要的概念,它不仅存放闪回日志，还有许SQL> rameter biginteger2GSQL>sho rameterflash 闪回日志存放的保留期，缺省值1440，单位是分钟。SQL>STARTUPMOUNTSQL>ALTERDATABASE设置参数 SQL>selectflashback_onfromv$database; alterdatabaseopen;SQL> fromSQL>dropuserscottSQL>shutdownSQL>startupmount SQL>flashbackdatabasetoscn SQL>alterdatabaseopenreadonly;select*fromstartupalterdatabaseopenresetlogs; 只能闪回比当前更早的scn（见PPT-315page。flashbackdatabasetotimestampto_char('2012-03-0219:11:11','yyyy-mm-ddflashbackdatabaseto droptablexxxdropuserxxx错误的truncate表SQL>altertablespace<tablespace_name>flashbackoff;//可以在open下设置SQL>altertablespace<tablespace_name>flashbackon;//可以在mount下设置SQL>select*fromv$tablesapce //有个字段FLA可显示状态LogSQL>select*from 2014-02-080考点 SQL>select*from DB_DATAREDO_DATA2014-02-0819:49:152014-02-0802014-02-0818:41:582014-02-08Recovery备份blockORACLEBLOCK据库),catalogdatabase(数据库)。rman（controlfile）6）catalogdatabase:集中管理、存放备份的元数据，还可以备份7）MML:mediamanagelayer介质管理层：用于管理磁带机的库文件或驱动[oracle@timran~]$rmantargetRMAN客户端通过ORACLE连接targetdatabase在targetdatabase启动，在配置tnsnames.ora。C:\sandSettings\timran>rmantargetRMAN>showdb_unique_nameTIMRAN11GRMANCONFIGURERETENTIONTOREDUNDANCY1;#defaultCONFIGUREBACKUPOPTIMIZATIONOFF;#defaultCONFIGUREDEFAUEVICETYPETODISK;#defaultCONFIGURECONTROLFILEAUTOBACKUPON;#defaultCONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO#CONFIGUREDEVICETYPEDISKPARALLELISM1BACKUPTYPETOBACKUPSET;#defaultCONFIGUREDATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1;#defaultCONFIGUREARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;#defaultCONFIGUREMAXSETSIZETOUNLIMITED;#defaultCONFIGUREENCRYPTIONFORDATABASEOFF;#defaultCONFIGUREENCRYPTIONALGORITHM'AES128';#defaultCONFIGURECOMPRESSIONALGORITHM'BZIP2';#defaultCONFIGUREARCHIVELOGDELETIONTONONE;#defaultCONFIGURESNAPSHOTCONTROLFILENAMETO'/u01/oracle/dbs/snapcf_timran11g.f';#第一行：CONFIGURERETENTIONTOREDUNDANCY1;# RMAN>configureretentiontorecoverywindowof7策略设置为2：RMAN>configureretentiontoredundancyRMAN>configure RMAN>configureretentionRMAN>configureretentiontorecoverywindowof5RMAN>configureretentiontoredundancy第二行：CONFIGUREBACKUPOPTIMIZATIONOFF;RMAN>configurebackupoptimization数设为on可以避免重复备份，可空间和时间。(PPT103)本的情况下(r+1)，RMAN将不创建额外的文件副本。第三行：CONFIGUREDEFAUEVICETYPETODISK;#（DISK（STB第四行：CONFIGURECONTROLFILEAUTOBACKUPOFF;#default设置控制文件自动备份(autobackupon)RMAN>CONFIGURECONTROLFILEAUTOBACKUPON;on:spfile，并且数据库的物理结构发生变化时，也自动备份controlfile。第五行：CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'%F%dIIIIIIIIIIDBID，YYYYMMDDQQ1-%s第六行：CONFIGUREDEVICETYPEDISKPARALLELISM2BACKUPTYPETORUN{}中指定通道来决定备份与恢复的并RUN{}中指定了通道配置，将覆盖掉配置参数中指定的默认并行度(考点。PARALLELISM：保存在RMAN库BACKUP:限于本次语句，不保存。 在BACKUP语句中描述 CHANNEL或CONFIGURECHANNEL中描述 CHANNEL或CONFIGURECHANNEL中描述BACKUP 在BACKUP语句中描1：RMAN>RUN2>ALLOCATECHANNELc1DEVICETYPE3>ALLOCATECHANNELc2DEVICETYPE4>ALLOCATECHANNELc3DEVICETYPE5>6>REMENTALLEVEL=7>FORMAT8>(DATAFILE1,4,5CHANNEL9>(DATAFILE2,3,9CHANNEL10>(DATAFILE6,7,8CHANNEL11>ALTERSYSTEMARCHIVELOG2：RMAN>2>ALLOCATECHANNELt1DEVICETYPE3>ALLOCATECHANNELt2DEVICETYPE4>ALLOCATECHANNELt3DEVICETYPE5>ALLOCATECHANNELt4DEVICETYPE6>BACKUPDATAFILEFILESPERSET}1，20FILESPERSET=883：CONFIGURECHANNELDEVICETYPEDISKMAXPIECESIZEBACKUPdatafile2format}CONFIGURECHANNELDEVICETYPEDISKMAXPIECESIZEALLOCATECHANNELc1DEVICETYPEDISKMAXPIECESIZERMAN>backupdatabasemaxsetsize1GformatRMAN>ALLOCATECHANNELd1DEVICETYPEdisk;ALLOCATECHANNELd2DEVICETYPEdisk;ALLOCATECHANNELd3DEVICETYPEdisk;BACKUPASCOMPRESSEDBACKUPSETDATAFILE2SECTIONSIZE300M}RMAN>CONFIGURECHANNELDEVICETYPEDISK第七行：CONFIGUREDATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1; 第八行：CONFIGUREARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;第九行：CONFIGUREMAXSETSIZETOUNLIMITED;第十行：CONFIGUREENCRYPTIONFORDATABASEOFF第十一行：CONFIGUREENCRYPTIONALGORITHM加密，Transparentencryption，Passwordencryption:不带钱夹，第十二行：CONFIGURECOMPRESSIONALGORITHM'BZIP2';binary第十三行：CONFIGUREARCHIVELOGDELETIONTONONE;#对于归档文件，一般来说,如果你仅是单实例的话（DataGuard，archivefile备份完就没有什么用处了，可以删除，但很多人不习惯在这里设置参数，更愿意使用脚本删除归档文件。（见最后一章Oracle一周备份） '/u01/oracle/dbs/snapcf_timran11g.f';#tapebackupset里，并且生成一个备份片（backuppieceOS下看到）copy（cpdatafile RMAN>RMAN>sql'altersystemswitchRMAN>sqlselect*from {shutdownimmediate;startupallocatechannelc1typedisk;allocatechannelc2typebackupdatabaseformat'/u01/myrman/%d_%s.bak';alterdatabaseopen;}第七章：rmanRMAN>backupdatabaseformat='/u01/myrman/timran_%s.bak'filespersetRMAN>backupdatabaseplusarchivelogdeleteinput;//备份全库及控制文件、RMAN>backupdatabaseformat'/u01/myrman/%s_bak'plusarchivelogdeleteinputskipinaccessible;backupdatabase紧接format可以使datafile的备份片指定到format的目的地，记录了而实际归档中又不存在，则会报错，skipinaccessible的含义是跳过物理上的restorearchivelogRMAN>backuptablespaceusersformat'/u01/myrman/users_%s.bak'RMAN>backuptablespacesystemplusarchivelogdeleteinput;//备份指定表空间RMAN>backupdatafile3,5format'/u01/myrman/%d_%s.bak';//备份数据文件，可RMAN>backuparchivelogalldeleteRMAN>backupcurrentRMAN>backupspfile;RMAN>backuprecovry8)RMAN>backupbackupset18; 意不产生新的备份集，类似duplex.RMAN>list RMAN>copydatafile4toRMAN>backupascopytablespace'TEST'RMAN>listdatafilecopy 须先restore。RMAN映像副本备份不包括spfile份时通过比较前次备份数据块上的scn来确定该数据块是否发生变化）backup：Level Level || |||| ||||||||||||0111110不能作为1级增量的父备份。startupmount;restoredatabase;alterdatabaseopen增量备份总是小于完整备份，但是备份时节省的时间并不像那样少，是增量备份地址。如果启用了块，增量备份时就去检查块文件，这比遍历整个数据文件块要快的多。当然缺点是数据库块文件会付出性能代价，所以实际工作中此特性通常不在生产库上配置，而在DATAGUARD物理备库上配置则比较合适。SQL>alterdatabaseenableblockchangetrackingusingfileSQL>colfilenameforSQL>select*fromSTATUS RMAN> rementallevel0format'/u01/myrman/%s.bak'datafileORA_DISK_1:RMAN> rementallevel1format'/u01/myrman/%s.bak'datafileORA_DISK_1:SQL>alterdatabasedisableblockchangeRMAN>backuprementallevel1format'/u01/myrman/%s.bak'datafileORA_DISK_1:SQL>alterdatabaseenableblockchangetrackingusingfileRMAN>backuprementallevel1format'/u01/myrman/%s.bak'datafileORA_DISK_1:RMAN>backuprementallevel1format'/u01/myrman/%s.bak'datafileORA_DISK_1:copy，imcrementallevel1imagecopy数据库需要还原时仅需要这个累积的imagecopyimcrementallevel1。runrecovercopyofdatabasewithtag'backuprementallevel1forrecoverofcopywithtag r_update'datafile}第二天，backuplevel1backupset。第三天以及以后的每一天，recovercopylevel1imagecopy，从而不断数据恢复顾问DRA(PPT-II-218-依赖于ADR的自动诊断信息以及HealthMonitorRMAN>backuptablespacesysauxformatSQL>shutdown[oracle@timrantimran11g]$mvsysaux01.dbfsysaux01.bakSQL>startupORA-01157:无法标识/锁定数据文件2-请参阅DBWR文ORA-01110:2:[oracle@timrantimran11g]$rmantarget/RMAN>listfailure;失败ID优先级状 CRITICAL|HIGH|HIGH更改为LOW或从LOW更改为HIGH（从HIGH更改为LOW的一个是为了避免该故障在LISTFAILURE命令中显示出来。RMAN>changefailure202priority失败ID优先级状 YESNO)?1LOWRMAN>listRMAN>changefailure202priority失败ID优先级状 是否确实要更改以上故障(输入YES或NO)?y1HIGHRMAN>list失败ID优先级状 RMAN>advise修复: RMAN>repair修复: #restoreandrecoverdatafilerestoredatafile2;recoverdatafileYESNO)?restore2013-03-12使用通道ORA_DISK_1:00002通道ORA_DISK_1:正在备份片段通道ORA_DISK_1:段句柄=/u01/myrman/TIMRAN11_1.bak标记= ORA_DISK_1:ORA_DISK_1:restore2013-03-12recover2013-03-12使用通道recover2013-03-12YESNO先listfailure,然后再advisefailure.recover换一种形式，下面的run{}写到linux的中，叫run{allocatechannelc1typedisk;allocatechannelc2typebackupdatabaseformat}rmantarget/可以RMAN备份，RMAN使用字符串rman和通道名称填充v$session中 每个RMAN通道有一行信息,备份结束后进程信息.setcommandidtoXXXv$sessionfromv$processpjoinv$sessionson(p.addr=s.paddr)where_infolike'rman%';SID 113 rman11112995 RMAN>listbackup;BS关键字类型LV大 设备类型经过时间完成时 2013-01-15BP关键字: 状态:AVAILABLE已压缩:NO标记: /u01/myrman/TIMRAN11_24.bak文件LVCkp文件LVCkpCkp1 2013-01-1515:47:2962013-01-1515:47:29/BS关键字类型LV大 设备类型经过时间完成时 2013-01-15BP关键字: 状态:AVAILABLE已压缩:NO标记: /u01/myrman/TIMRAN11_25.bak23文件LVCkpCkp2 BS关键字类型LV大 设备类型经过时间完成时 2013-01-15BP关键字: 状态:AVAILABLE已压缩:NO标记: /u01/myrman/c- SPFILE:2013-01-15SPFILEdb_unique_name:包括的控制文件:CkpSCN: Ckp时间:2013-01-1515:47:29[oracle@timran~]$sqlplusscott/scottSQL>createtablet1(idint);SQL>insertintot1values(1);SQL>commit;SQL>select*fromSQL>altersystemswitchlogfile;SQL>/SQL>SQL>insertintot1values(2);SQL>commit;SQL>select*from2[oracle@timran~]$rm/u01/oradata/timran11g/system01.dbfSQL>startupfroceORACLEinstanceTotalSystemGlobal Fixed Variable Database Redo DatabaseORA-01157:cannotidentify/lockdatafile1-seeDBWRtraceORA-01110:datafile1:EMSQL>createtablescott.emp1asselect*fromscott.empwhererownum<3;SQL>select*fromscott.emp1;EMPNOENAME MGRHIREDATE 7369 790217-DEC- 7499 769820-FEB- [oracle@timran~]$rm3)清除dbbuffer，证实物理读失败SQL>altersystemflushbuffer_cache;SQL>conn/assysdbaSQL>select*fromscott.emp1;select*fromscott.emp1*ERRORatlineSQL>altersystemcheckpoint ，[oracle@timrantimran11g]$mkdir/u01/oradata/timran11g/dir15）使用RMAN恢复表空间sql'alterdatabasedatafile4setnewnamefordatafile4to'/u01/oradata/timran11g/dir1/users01.dbf';restoretablespaceusers;switchdatafile4;recovertablespaceusers;sql'alterdatabasedatafile4}SQL>select*fromEMPNOENAME MGRHIREDATE 7369 79021980-12-17 7499 76981981-02-20 SQL>altertablespaceusers usersrename SQL>altertablespaceusersonline;SQL>select*fromSQL>createtablespacelxdatafile'/u01/oradata/timran11g/lx01.dbf'size5m;SQL>createtablescott.t2(idint)tablespacelx;SQL>insertintoscott.t2values(1);SQL>commit;SQL>select*from[oracle@timran~]$rmu01/oradata/timran11g/lx01.dbf3）清除dbbuffer，t2表物理读失败。SQL>altersystemflushbuffer_cache;SQL>conn/assysdbaSQL>select*fromscott.t2;select*fromscott.t2*1ORA-01116:3ORA-01110:3:'/u01/oradata/timran11g/lx01.dbf'ORA-27041:无法打开文件RMAN>run{sql'alterdatabasedatafile3restoredatafilerecoverdatafilesql'alterdatabasedatafile3}sqlalterdatabasedatafile3restore2013-01-18ORA_DISK_1:SID=130创建数据文件,文件号=3名称= 完成restore于2013-01-1811:23:35recover2013-01-18使用通道recover2013-01-18sqlalterdatabasedatafile3onlineSQL>select*from范例1：恢复过去某个时间点误操作，一般使用基于time或scn。RMAN>showCONFIGURECONTROLFILEAUTOBACKUPON; RMAN>deleteRMAN>backupdatabaseformatSQL>createtablescott.t1(idint);SQL>insertintoscott.t1values(1);SQL>commit;select*fromscott.t1;SQL>select*fromscott.t1;1SQL>select*fromMEMBERSARC 2013-01-18 YES2013-01-18 YES2013-01-18SQL>selectsysdatefromdual;2013-01-18SQL>truncatetable SQL>altersystemswitchlogfile;SQL>/SQL>select*from YES2013-01-18 YES2013-01-18 2013-01-18SQL>insertintoscott.t1 sequence9SQL>SQL>select*fromrunstartupallocatechannelc1typedisk;allocatechannelc2typesetuntiltime'2013-01-1813:47:04';restoredatabase;recoveralterdatabaseopen} 字Fixed Variable Database Redo 分配的通道:c1c1:SID=154c2:SID=151=DISKSETuntilrestore2013-01-18通道c1:正在备份片段通道c1:段句柄=/u01/myrman/51.bak标记= c1:c1:restore2013-01-18recover2013-01-18线程1序列4的归档日志已作为文件/u01/disk1/timran/arch_1_ _4.log存在线程1序列5的归档日志已作为文件/u01/disk1/timran/arch_1_ _5.log存在线程1序列6的归档日志已作为文件/u01/disk1/timran/arch_1_ _6.log存在线程1序列7的归档日志已作为文件/u01/disk1/timran/arch_1_ _7.log存在归档日志文件名=/u01/disk1/timran/arch_1__4.log=1=4归档日志文件名=/u01/disk1/timran/arch_1__5.l