oracle11gR2_64之rman异机恢复(FS->FS)完整案例

1、环境明细:源库--->>操作系统:Redhat564位主机名:rman.comIP:192.168.11.112数据库版本:oracle11.2.0.1.0存储方式:文件系统ORACLE_HOME:/u01/app/oracle/product/11.2.0/db_1ORACLE_SID:standby 目标库-->>操作系统:Redhat564位主机名:dg.comIP:192.168.11.114 数据库版本:oracle11.2.0.1.0存储方式:文件系统 ORACLE_HOME:/u01/app/oracle/product/11.2.0/db_1ORACLE_SID:standby

2、主库信息及库库备份明细:颍骈城茇----->>主库信息:SQL>selectfile_id,file_namefromdba_data_filesorderby1;FILE_ID僻棍募暖FILE_NAME-------------------------------------------------------1/u01/app/oracle/oradata/standby/system01.dbf2/u01/app/oracle/oradata/standby/sysaux01.dbf3/u01/app/oracle/oradata/standby/undotbs01.dbf4/u01/app/oracle/oradata/standby/users01.dbf5/u01/app/oracle/oradata/standby/example01.dbfSQL>showuser;USERis"SYS"--创建新表空间并在其中建表对db进行更新SQL>createtablespacerecoverdatafile'/u01/app/oracle/oradata/standby/recover.dbf'size50mautoextendonmaxsize500m;Tablespacecreated.SQL>selectfile_id,file_namefromdba_data_filesorderby1;FILE_IDFILE_NAME-------------------------------------------------------1/u01/app/oracle/oradata/standby/system01.dbf2/u01/app/oracle/oradata/standby/sysaux01.dbf3/u01/app/oracle/oradata/standby/undotbs01.dbf4/u01/app/oracle/oradata/standby/users01.dbf5/u01/app/oracle/oradata/standby/example01.dbf6/u01/app/oracle/oradata/standby/recover.dbf6rowsselected.SQL>createtablerecover(idnumber,enamevarchar2(50))tablespacerecover;Tablecreated.SQL>begin2foriin1..9loop3insertintorecovervalues(i,'recover'||i);4endloop;5commit;6end;7/PL/SQLproceduresuccessfullycompleted.SQL>select*fromrecover;IDENAME------------------------------------------------------------1recover12recover23recover34recover45recover56recover67recover78recover89recover99rowsselected.SQL>selectgroup#,thread#,sequence#,statusfromv$log;GROUP#THREAD#SEQUENCE#STATUS----------------------------------------------1133INACTIVE2134CURRENT3132INACTIVESQL>selectgroup#,status,memberfromv$logfile;GROUP#STATUSMEMBER-------------------------------------------------------------------3/u01/app/oracle/oradata/standby/redo03.log2/u01/app/oracle/oradata/standby/redo02.log1/u01/app/oracle/oradata/standby/redo01.log4/u01/app/oracle/oradata/standby/stdbyredo01.log5/u01/app/oracle/oradata/standby/stdbyredo02.log6/u01/app/oracle/oradata/standby/stdbyredo03.log6rowsselected.------>>备份信息:RMAN>listbackupsummary;specificationdoesnotmatchanybackupintherepositoryRMAN>RMAN>RUN{2>allocatechannelc1typedisk;3>allocatechannelc2typedisk;4>5>backupDATABASEFORMAT'/home/oracle/back_dest/db_%U_%T'TAG='standby_db';6>7>sql'altersystemarchivelogcurrent';8>9>backupFORMAT'/home/oracle/back_dest/arch_%U_%T'ARCHIVELOGALLtag='standby_arch';10>backupcurrentcontrolfiletag='standby_ctl'format='/home/oracle/back_dest/ctl_%U_%T';11>backupspfiletag='standby_spfile'format='/home/oracle/back_dest/spfile_%U_%T';12>13>releasechannelc2;14>releasechannelc1;15>}releasedchannel:ORA_DISK_1......releasedchannel:c1RMAN>>>注意这里备份日志sequence最大为37,恢复时就应该是:set until sequence 38;RMAN>-------------------------------------------------------------------------------------------------------------------->>pfile是通过spfile生成，然后scp到目的地，没有通过restorepfilefrom‘’方式进行.

3、还原恢复过程:--颍骈城茇----->>修改scp过来的pfile文件(这里应该注意，源库可能空间较大，关于sga,pga等酌情根据目标库修改)然后startup到nomount状态，restorecontrolfile,然后restoredatabase,最后recoverdatabase[oracle@rmandbs]$catinitstandby.orastandby.__db_cache_size=276824064standby.__java_pool_size=4194304standby.__large_pool_size=4194304standby.__oracle_base='/u01/app/oracle'#ORACLE_BASEsetfromenvironmentstandby.__pga_aggregate_target=339738624standby.__sga_target=503316480standby.__shared_io_pool_size=0standby.__shared_pool_size=209715200standby.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/standby/adump'*.audit_trail='db'*.compatible='11.2.0.1.0'*.control_files='/u01/app/oracle/oradata/standby/control01.ctl'*.db_block_size=8192*.db_domain=''*.db_name='primary'*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'*.db_recovery_file_dest_size=4070572032*.db_unique_name='standby'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP)(SERVICE=primaryXDB)'*.log_archive_dest_1='location=/u01/app/oracle/oradata/arch'*.log_archive_format='%t_%s_%r.dbf'*.memory_target=842006528*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.standby_file_management='auto'*.undo_tablespace='UNDOTBS1'--------------------------------->>创建相关目录，并注意权限(我这里是用安装oracle软件有oracle用户创建，就不需要考虑目录权限问题):mkdir-p/u01/app/oracle/oradata/standbymkdir-p/u01/app/oracle/oradata/archmkdir-p/u01/app/oracle/product/11.2.0/db_1/dbsmkdir-p/u01/app/oracle/product/11.2.0/db_1/network/adminmkdir-p/u01/app/oracle/flash_recovery_areamkdir-p/u01/app/oracle/admin/standby/adump---------------------------------->>还原恢复过程:SQL>startupnomountpfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora';ORACLEinstancestarted.TotalSystemGlobalArea839282688bytesFixedSize2217992bytesVariableSize557844472bytesDatabaseBuffers276824064bytesRedoBuffers2396160bytesSQL>SQL>SQL>selectinstance_name,statusfromv$instance;INSTANCE_NAMESTATUS----------------------------standbySTARTED------------------------------------------------------->>[oracle@rmanadmin]$rmantarget/RecoveryManager:Release11.2.0.1.0-ProductiononWedMay2816:04:472014Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.connectedtotargetdatabase:STANDBY(notmounted)RMAN>setDBID1685534234;executingcommand:SETDBID---restore controlfileRMAN>restorecontrolfilefrom'/home/oracle/back_dest/ctl_0tp9e3dg_1_1_20140528';Startingrestoreat28-MAY-14......Finishedrestoreat28-MAY-14RMAN>alterdatabasemount;------restore database+recover databaseRMAN>run{2>setuntilsequence38;3>restoredatabase;4>recoverdatabase;}executingcommand:SETuntilclauseStartingrestoreat28-MAY-14......mediarecoverycomplete,elapsedtime:00:00:01Finishedrecoverat28-MAY-14RMAN>alterdatabaseopenresetlogs;databaseopened----------------------------------->>[oracle@rmandbs]$sqlplus/assysdbaSQL>selectinstance_name,statusfromv$instance;INSTANCE_NAMESTATUS----------------------------standbyOPEN---验证数据有效性SQL>select*fromrecover;IDENAME------------------------------------------------------------1recover12recover23recover34recover45recover56recover67recover78recover89recover99rowsselected.---日志序列归零SQL>selectgroup#,thread#,sequence#,statusfromv$log;GROUP#THREAD#SEQUENCE#STATUS----------------------------------------------111CURRENT210UNUSED310UNUSEDSQL>colmemberfora50;--验证logfile和standbylogSQL>selectgroup#,status,memberfromv$logfile;GROUP#STATUSMEMBER-------------------------------------------------------------------3/u01/app/oracle/oradata/standby/redo03.log2/u01/app/oracle/oradata/standby/redo02.log1/u01/app/oracle/oradata/standby/redo01.log4/u01/app/oracle/oradata/standby/stdbyredo01.log5/u01/app/oracle/oradata/standby/stdbyredo02.log6/u01/app/oracle/oradata/standby/stdbyredo03.log6rowsselected.SQL>selecttablespace_name,statusfromdba_tablespaces;TABLESPACE_NAMESTATUS---------------------------------------SYSTEMONLINESYSAUXONLINEUNDOTBS1ONLINETEMPONLINEUSERSONLINEEXAMPLEONLINERECOVERONLINE7rowsselected.--创建spfileSQL>createspfilefrompfile;Filecreated.SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>startup;ORACLEinstancestarted.TotalSystemGlobalArea839282688bytesFixedSize2217992bytesVariableSize557844472bytesDatabaseBuffers276824064bytesRedoBuffers2396160bytesDatabasemounted.Databaseopened.--确定此时目标库为spfile启动SQL>selectdecode(count(*),1,'spfile','pfile')fromv$spparameterwhererownum=1andisspecified='TRUE';DECODE------spfileSQL>------->>修改listener.ora,tnsnames.ora[oracle@recoveradmin]$morelistener.oraLISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.114)(PORT=1521))))ADR_BASE_LISTENER=/u01/app/oracle[oracle@recoveradmin]$moretnsnames.ora#tnsnames.oraNetworkConfigurationFile:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora#GeneratedbyOracleconfigurationtools.standby=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.114)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=bus)))----------------------OVER GAME