Oracle 12c 使用RMAN搭建物理备库(RAC to RAC)

2025-04-15 技术教程

一.实例环境说明
主库环境说明
操作系统:Oracle Linux 7.1
数据库版本:12.2.0.1
IP:10.10.10.190/10.10.10.191
主机名:cs1/cs2
数据库名:cs
Db_unique_name:cs
实例名(SID)cs1/cs2

备库环境说明
操作系统:Oracle Linux 7.1
数据库版本:12.2.0.1
IP:10.10.10.171/172
主机名:jytest1/jytest2
数据库名:cs
Db_unique_nmae:cs_dg
实例名(SID)cs1/cs2

二.配置过程
2.1修改主库启用归档与force logging
首先检查主库是否启用归档

[oracle@cs1~]$sqlplus/nologSQL*Plus:Release12.2.0.1.0ProductiononFriMay1112:54:072018Copyright(c)1982,2016,Oracle.Allrightsreserved.SQL>connsys/abcd@csassysdbaConnected.SQL>archiveloglistDatabaselogmodeNoArchiveModeAutomaticarchivalDisabledArchivedestination/u01/app/oracle/product/12.2.0/db/dbs/archOldestonlinelogsequence135Currentlogsequence136SQL>[oracle@cs2~]$sqlplus/nologSQL*Plus:Release12.2.0.1.0ProductiononFriMay1112:54:222018Copyright(c)1982,2016,Oracle.Allrightsreserved.SQL>connsys/abcd@csassysdbaConnected.SQL>archiveloglistDatabaselogmodeNoArchiveModeAutomaticarchivalDisabledArchivedestination/u01/app/oracle/product/12.2.0/db/dbs/archOldestonlinelogsequence116Currentlogsequence117SQL>

现在主库没有启用归档，执行下面的命令来对主库启用归档

SQL>altersystemsetlog_archive_dest_1='location=+data/arch/'scope=bothsid='*';Systemaltered.SQL>showparameterlog_archive_forNAMETYPEVALUE-----------------------------------------------------------------------------log_archive_formatstring%t_%s_%r.dbfSQL>shutdownimmediateDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>altersystemsetlog_archive_dest_1='location=+data/arch/'scope=bothsid='*';Systemaltered.SQL>showparameterlog_archive_forNAMETYPEVALUE-----------------------------------------------------------------------------log_archive_formatstring%t_%s_%r.dbfSQL>shutdownimmediateDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>startupmountORACLEinstancestarted.TotalSystemGlobalArea2147483648bytesFixedSize8794848bytesVariableSize671092000bytesDatabaseBuffers1459617792bytesRedoBuffers7979008bytesDatabasemounted.SQL>alterdatabasearchivelog;Databasealtered.SQL>alterdatabaseopen;Databasealtered.SQL>archiveloglistDatabaselogmodeArchiveModeAutomaticarchivalEnabledArchivedestination+DATA/arch/Oldestonlinelogsequence136Nextlogsequencetoarchive137Currentlogsequence137

这里主库已经启用了归档

启用force logging

SQL>alterdatabaseforcelogging;Databasealtered.SQL>selectforce_loggingfromv$database;FORCE_LOGGING---------------------------------------YESSQL>selectforce_loggingfromv$database;FORCE_LOGGING---------------------------------------YES

2.2 给备库创建密码文件
这里通过复制主库的密码文件来创建备库密码文件

[oracle@jytest1dbs]$scporacle@10.10.10.190:/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1/u01/app/oracle/product/12.2.0/db/dbs/orapwcs2Theauthenticityofhost'10.10.10.190(10.10.10.190)'can'tbeestablished.ECDSAkeyfingerprintis7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.Areyousureyouwanttocontinueconnecting(yes/no)?yesWarning:Permanentlyadded'10.10.10.190'(ECDSA)tothelistofknownhosts.oracle@10.10.10.190'spassword:orapwcs1100%35843.5KB/s00:00[oracle@jytest2dbs]$ls-lrt[oracle@jytest2dbs]$scporacle@10.10.10.190:/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1/u01/app/oracle/product/12.2.0/db/dbs/orapwcs2Theauthenticityofhost'10.10.10.190(10.10.10.190)'can'tbeestablished.ECDSAkeyfingerprintis7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.Areyousureyouwanttocontinueconnecting(yes/no)?yesWarning:Permanentlyadded'10.10.10.190'(ECDSA)tothelistofknownhosts.oracle@10.10.10.190'spassword:orapwcs1100%35843.5KB/s00:00[oracle@jytest2dbs]$ls-lrt

2.3.给备库创建参数文件
使用主库的参数文件进行创建

SQL>createpfile='/u01/app/oracle/product/12.2.0/db/dbs/initcs.ora'fromspfile='+DATA/CS/PARAMETERFILE/spfile.287.970602765';Filecreated.[oracle@jytest1dbs]$catinitcs1_temp.ora*.audit_file_dest='/u01/app/oracle/admin/cs/adump'*.audit_trail='db'*.cluster_database=true*.compatible='12.2.0'*.control_files='+FRA/CS_DG/CONTROLFILE/control01.ctl'*.db_block_size=8192*.db_name='cs'*.db_unique_name='cs_dg'*.db_create_file_dest='+FRA'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP)(SERVICE=csXDB)'*.enable_pluggable_database=truefamily:dw_helper.instance_mode='read-only'log_archive_config='DG_CONFIG=(cs,cs_dg)'cs2.instance_number=2cs1.instance_number=1*.log_archive_format='%t_%s_%r.dbf'*.log_archive_max_processes=30*.log_archive_dest_1='LOCATION=+test/arch/cs_dg/valid_for=(ALL_LOGFILES,ALL_ROLES)db_unique_name=cs_dg'*.log_archive_dest_2='service=csLGWRASYNCvalid_for=(online_logfiles,primary_role)db_unique_name=cs'*.nls_language='AMERICAN'*.nls_territory='AMERICA'*.open_cursors=300*.pga_aggregate_target=1g*.processes=320*.remote_login_passwordfile='exclusive'*.sga_target=2gcs2.thread=2cs1.thread=1cs2.undo_tablespace='UNDOTBS2'cs1.undo_tablespace='UNDOTBS1'*.db_file_name_convert='+DATA/CS/','+FRA/CS_DG/'*.fal_client='cs_dg'*.fal_server='cs'*.log_file_name_convert='+DATA/CS/','+FRA/CS_DG/'*.standby_file_management='auto'

SQL>createspfile='+fra/cs/parameterfile/spfilecs.ora'frompfile='/u01/app/oracle/product/12.2.0/db/dbs/initcs1_temp.ora';Filecreated.[oracle@jytest1dbs]$viinitcs1.oraspfile='+fra/cs/parameterfile/spfilecs.ora'[oracle@jytest2dbs]$viinitcs2.oraspfile='+fra/cs/parameterfile/spfilecs.ora'

2.4为主库和备库配置监听
主库是rac已经配置了监听，备库也是RAC需要对其配置监听
备库:节点1

[grid@jytest1admin]$vilistener.oraLISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))#lineaddedbyAgentMGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR))))#lineaddedbyAgentLISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))#lineaddedbyAgentLISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))#lineaddedbyAgentLISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))#lineaddedbyAgentASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF#lineaddedbyAgentSID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=cs2)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db)(GLOBAL_DBNAME=cs_dg)))

备库:节点2

[grid@jytest2admin]$vilistener.oraLISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))#lineaddedbyAgentMGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR))))#lineaddedbyAgentLISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))#lineaddedbyAgentLISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))#lineaddedbyAgentLISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))#lineaddedbyAgentASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET#lineaddedbyAgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON#lineaddedbyAgentVALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF#lineaddedbyAgentSID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=cs2)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/db)(GLOBAL_DBNAME=cs_dg)))

[grid@jytest1admin]$srvctlstoplistener-njytest1[grid@jytest1admin]$srvctlstoplistener-njytest2[grid@jytest1admin]$srvctlstartlistener-njytest1[grid@jytest1admin]$srvctlstartlistener-njytest2[grid@jytest1admin]$lsnrctlstatusLSNRCTLforLinux:Version12.2.0.1.0-Productionon14-MAY-201822:33:06Copyright(c)1991,2016,Oracle.Allrightsreserved.Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))STATUSoftheLISTENER------------------------AliasLISTENERVersionTNSLSNRforLinux:Version12.2.0.1.0-ProductionStartDate14-MAY-201822:32:08Uptime0days0hr.0min.57secTraceLeveloffSecurityON:LocalOSAuthenticationSNMPOFFListenerParameterFile/u01/app/product/12.2.0/crs/network/admin/listener.oraListenerLogFile/u01/app/grid/diag/tnslsnr/jytest1/listener/alert/log.xmlListeningEndpointsSummary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.171)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.175)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest1.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW))ServicesSummary...Service"+ASM"has1instance(s).Instance"+ASM1",statusREADY,has1handler(s)forthisservice...Service"+ASM_CRS"has1instance(s).Instance"+ASM1",statusREADY,has1handler(s)forthisservice...Service"+ASM_DATA"has1instance(s).Instance"+ASM1",statusREADY,has1handler(s)forthisservice...Service"+ASM_FRA"has1instance(s).Instance"+ASM1",statusREADY,has1handler(s)forthisservice...Service"+ASM_TEST"has1instance(s).Instance"+ASM1",statusREADY,has1handler(s)forthisservice...Service"5f9ac6865e87549fe053ab828a0ade94"has1instance(s).Instance"jy1",statusREADY,has1handler(s)forthisservice...Service"cs_dg"has1instance(s).Instance"cs1",statusUNKNOWN,has1handler(s)forthisservice...Service"jy"has1instance(s).Instance"jy1",statusREADY,has1handler(s)forthisservice...Service"jyXDB"has1instance(s).Instance"jy1",statusREADY,has1handler(s)forthisservice...Service"jy_srv"has1instance(s).Instance"jy1",statusREADY,has1handler(s)forthisservice...Service"jypdb"has1instance(s).Instance"jy1",statusREADY,has1handler(s)forthisservice...Thecommandcompletedsuccessfully[grid@jytest2~]$lsnrctlstatusLSNRCTLforLinux:Version12.2.0.1.0-Productionon14-MAY-201822:33:10Copyright(c)1991,2016,Oracle.Allrightsreserved.Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))STATUSoftheLISTENER------------------------AliasLISTENERVersionTNSLSNRforLinux:Version12.2.0.1.0-ProductionStartDate14-MAY-201822:32:16Uptime0days0hr.0min.54secTraceLeveloffSecurityON:LocalOSAuthenticationSNMPOFFListenerParameterFile/u01/app/product/12.2.0/crs/network/admin/listener.oraListenerLogFile/u01/app/grid/diag/tnslsnr/jytest2/listener/alert/log.xmlListeningEndpointsSummary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.172)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.176)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest2.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW))ServicesSummary...Service"+ASM"has1instance(s).Instance"+ASM2",statusREADY,has1handler(s)forthisservice...Service"+ASM_CRS"has1instance(s).Instance"+ASM2",statusREADY,has1handler(s)forthisservice...Service"+ASM_DATA"has1instance(s).Instance"+ASM2",statusREADY,has1handler(s)forthisservice...Service"+ASM_FRA"has1instance(s).Instance"+ASM2",statusREADY,has1handler(s)forthisservice...Service"+ASM_TEST"has1instance(s).Instance"+ASM2",statusREADY,has1handler(s)forthisservice...Service"5f9ac6865e87549fe053ab828a0ade94"has1instance(s).Instance"jy2",statusREADY,has1handler(s)forthisservice...Service"cs_dg"has1instance(s).Instance"cs2",statusUNKNOWN,has1handler(s)forthisservice...Service"jy"has1instance(s).Instance"jy2",statusREADY,has1handler(s)forthisservice...Service"jyXDB"has1instance(s).Instance"jy2",statusREADY,has1handler(s)forthisservice...Service"jypdb"has1instance(s).Instance"jy2",statusREADY,has1handler(s)forthisservice...Thecommandcompletedsuccessfully

2.5为主库和备库创建Oracle Net服务名
主库:节点1

[oracle@cs1admin]$vitnsnames.ora#tnsnames.oraNetworkConfigurationFile:/u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora#GeneratedbyOracleconfigurationtools.CS=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.149)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cs)))CS_DG=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.175)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cs_dg)(UR=A)))

主库:节点2

[oracle@cs2admin]$vitnsnames.ora#tnsnames.oraNetworkConfigurationFile:/u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora#GeneratedbyOracleconfigurationtools.CS=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.134)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cs)))CS_DG=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.176)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cs_dg)(UR=A)))

备库:节点1

[oracle@jytest1admin]$vitnsnames.ora#tnsnames.ora.jytest1NetworkConfigurationFile:/u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora.jytest1#GeneratedbyOracleconfigurationtools.CS=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.149)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cs)))

备库:节点2

[oracle@jytest2admin]$vitnsnames.ora#tnsnames.ora.jytest2NetworkConfigurationFile:/u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora.jytest2#GeneratedbyOracleconfigurationtools.CS=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.134)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cs)))

使用备份创建备库

[oracle@jytest1admin]$exportORACLE_SID=cs1[oracle@jytest1admin]$sqlplus/assysdbaSQL*Plus:Release12.2.0.1.0ProductiononTueMay1500:06:432018Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedtoanidleinstance.SQL>startupnomountORACLEinstancestarted.TotalSystemGlobalArea2147483648bytesFixedSize8794848bytesVariableSize721423648bytesDatabaseBuffers1409286144bytesRedoBuffers7979008bytes

[oracle@cs1admin]$rmantargetsys/abcd@csauxiliarysys/abcd@cs_dgRecoveryManager:Release12.2.0.1.0-ProductiononMonMay1419:43:212018Copyright(c)1982,2017,Oracleand/oritsaffiliates.Allrightsreserved.connectedtotargetdatabase:CS(DBID=1386528187)connectedtoauxiliarydatabase:CS(notmounted)RMAN>duplicatetargetdatabaseforstandbyfromactivedatabasenofilenamecheck;StartingDuplicateDbat14-MAY-18usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_AUX_DISK_1channelORA_AUX_DISK_1:SID=197instance=cs1devicetype=DISKcontentsofMemoryScript:{backupascopyreusetargetfile'+DATA/CS/PASSWORD/pwdcs.271.970601731'auxiliaryformat'/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1';}executingMemoryScriptStartingbackupat14-MAY-18allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=291instance=cs1devicetype=DISKFinishedbackupat14-MAY-18contentsofMemoryScript:{restoreclonefromservice'cs'standbycontrolfile;}executingMemoryScriptStartingrestoreat14-MAY-18usingchannelORA_AUX_DISK_1channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:restoringcontrolfilechannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:03outputfilename=+FRA/CS_DG/CONTROLFILE/control01.ctlFinishedrestoreat14-MAY-18contentsofMemoryScript:{sqlclone'alterdatabasemountstandbydatabase';}executingMemoryScriptsqlstatement:alterdatabasemountstandbydatabaseRMAN-05529:warning:DB_FILE_NAME_CONVERTresultedininvalidASMnames;nameschangedtodiskgrouponly.contentsofMemoryScript:{setnewnamefortempfile1to"+FRA";setnewnamefortempfile2to"+FRA";switchclonetempfileall;setnewnamefordatafile1to"+FRA";setnewnamefordatafile3to"+FRA";setnewnamefordatafile4to"+FRA";setnewnamefordatafile5to"+FRA";setnewnamefordatafile6to"+FRA";setnewnamefordatafile7to"+FRA";setnewnamefordatafile8to"+FRA";setnewnamefordatafile9to"+FRA";restorefromnonsparsefromservice'cs'clonedatabase;sql'altersystemarchivelogcurrent';}executingMemoryScriptexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMErenamedtempfile1to+FRAincontrolfilerenamedtempfile2to+FRAincontrolfileexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEStartingrestoreat14-MAY-18usingchannelORA_AUX_DISK_1channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00001to+FRAchannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:16channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00003to+FRAchannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:45channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00004to+FRAchannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:04channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00005to+FRAchannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:08channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00006to+FRAchannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:07channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00007to+FRAchannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:02channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00008to+FRAchannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:04channelORA_AUX_DISK_1:startingdatafilebackupsetrestorechannelORA_AUX_DISK_1:usingnetworkbackupsetfromservicecschannelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_AUX_DISK_1:restoringdatafile00009to+FRAchannelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:03Finishedrestoreat14-MAY-18sqlstatement:altersystemarchivelogcurrentcontentsofMemoryScript:{switchclonedatafileall;}executingMemoryScriptdatafile1switchedtodatafilecopyinputdatafilecopyRECID=12STAMP=976150821filename=+FRA/CS_DG/DATAFILE/system.342.976150731datafile3switchedtodatafilecopyinputdatafilecopyRECID=13STAMP=976150821filename=+FRA/CS_DG/DATAFILE/sysaux.341.976150747datafile4switchedtodatafilecopyinputdatafilecopyRECID=14STAMP=976150821filename=+FRA/CS_DG/DATAFILE/undotbs1.340.976150793datafile5switchedtodatafilecopyinputdatafilecopyRECID=15STAMP=976150821filename=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/system.347.976150795datafile6switchedtodatafilecopyinputdatafilecopyRECID=16STAMP=976150822filename=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/sysaux.351.976150803datafile7switchedtodatafilecopyinputdatafilecopyRECID=17STAMP=976150822filename=+FRA/CS_DG/DATAFILE/users.350.976150811datafile8switchedtodatafilecopyinputdatafilecopyRECID=18STAMP=976150823filename=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/undotbs1.348.976150813datafile9switchedtodatafilecopyinputdatafilecopyRECID=19STAMP=976150823filename=+FRA/CS_DG/DATAFILE/undotbs2.345.976150817FinishedDuplicateDbat14-MAY-18

[oracle@jytest1admin]$sqlplus/assysdbaSQL*Plus:Release12.2.0.1.0ProductiononTueMay1501:05:352018Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>selectinstance_name,statusfromv$instance;INSTANCE_NAMESTATUS----------------------------cs1MOUNTEDSQL>selectnamefromv$datafile;NAME--------------------------------------------------------------------------------+FRA/CS_DG/DATAFILE/system.342.976150731+FRA/CS_DG/DATAFILE/sysaux.341.976150747+FRA/CS_DG/DATAFILE/undotbs1.340.976150793+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/system.347.976150795+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/sysaux.351.976150803+FRA/CS_DG/DATAFILE/users.350.976150811+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/undotbs1.348.976150813+FRA/CS_DG/DATAFILE/undotbs2.345.9761508178rowsselected.

对物理备库创建备重做日志文件,查询主库的联机重做日志文件，备重做日志文件的大小应该与主库联机重做日志文件的大小相同，备重做日志文件组的数量应该比主库联机重做日志文件组多一组，计算公式为(maximum # of logfiles +1) * maximum # of threads
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/CS/ONLINELOG/group_2.277.970601985
+DATA/CS/ONLINELOG/group_1.278.970601985
+DATA/CS/ONLINELOG/group_3.285.970602759
+DATA/CS/ONLINELOG/group_4.286.970602761

SQL> select group#,thread#,bytes/1024/1024 from v$log;

GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 2 200
4 2 200

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+FRA/CS_DG/ONLINELOG/group_2.346.976150835
+FRA/CS_DG/ONLINELOG/group_1.349.976150825
+FRA/CS_DG/ONLINELOG/group_3.344.976150843
+FRA/CS_DG/ONLINELOG/group_4.352.976150855

SQL> select group#,thread#,bytes/1024/1024 from v$log;

GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 2 200
4 2 200

SQL> alter database add standby logfile thread 1 group 5('+FRA/CS_DG/ONLINELOG/redo05.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6('+FRA/CS_DG/ONLINELOG/redo06.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7('+FRA/CS_DG/ONLINELOG/redo07.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 8('+FRA/CS_DG/ONLINELOG/redo08.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 9('+FRA/CS_DG/ONLINELOG/redo09.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 10('+FRA/CS_DG/ONLINELOG/redo10.log') size 200M;

Database altered.

设置主库相关初始化参数

log_archive_config='DG_CONFIG=(cs,cs_dg)'log_archive_dest_1='LOCATION=+data/arch/valid_for=(ALL_LOGFILES,ALL_ROLES)db_unique_name=cs'log_archive_dest_2='SERVICE=cs_dgLGWRASYNCvalid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)db_unique_name=cs_dg'log_archive_dest_state_1=enablelog_archive_dest_state_2=enableremote_login_passwordfile=exclusivelog_archive_format=%t_%s_%r.dbflog_archive_max_processes=30

主库以备库角色运行时需要额外设置的参数。这些参数当主库被转换为备库角色运行时生效:

fal_server='cs_dg'fal_client='cs'db_file_name_convert='+FRA/CS_DG/','+DATA/CS/'log_file_name_convert='+FRA/CS_DG/','+DATA/CS/'standby_file_management='auto'

SQL>altersystemsetlog_archive_dest_1='LOCATION=+data/arch/valid_for=(ALL_LOGFILES,ALL_ROLES)db_unique_name=cs'scope=bothsid='*';Systemaltered.SQL>altersystemsetlog_archive_dest_2='SERVICE=cs_dgLGWRASYNCvalid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)db_unique_name=cs_dg'scope=bothsid='*';Systemaltered.SQL>altersystemsetlog_archive_dest_state_1=enablescope=bothsid='*';Systemaltered.SQL>altersystemsetlog_archive_dest_state_2=enablescope=bothsid='*';Systemaltered.SQL>altersystemsetlog_archive_max_processes=30scope=bothsid='*';Systemaltered.SQL>altersystemsetlog_archive_config='DG_CONFIG=(cs,cs_dg)'scope=bothsid='*';Systemaltered.SQL>altersystemsetfal_server='cs_dg'scope=bothsid='*';Systemaltered.SQL>altersystemsetfal_client='cs'scope=bothsid='*';Systemaltered.SQL>altersystemsetdb_file_name_convert='+FRA/CS_DG/','+DATA/CS/'scope=spfilesid='*';Systemaltered.SQL>altersystemsetlog_file_name_convert='+FRA/CS_DG/','+DATA/CS/'scope=spfilesid='*';Systemaltered.SQL>altersystemsetstandby_file_management='auto'scope=bothsid='*';Systemaltered.

给主库创建备重做日志文件，备重做日志文件组比联机重做日志文件多一组，主库是RAC数据库，在创建备重做日志文件时需要指定thread号:

SQL>alterdatabaseaddstandbylogfilethread1group5('+DATA/CS/ONLINELOG/redo05.log')size200M;Databasealtered.SQL>alterdatabaseaddstandbylogfilethread1group6('+DATA/CS/ONLINELOG/redo06.log')size200M;Databasealtered.SQL>alterdatabaseaddstandbylogfilethread1group7('+DATA/CS/ONLINELOG/redo07.log')size200M;Databasealtered.SQL>alterdatabaseaddstandbylogfilethread2group8('+DATA/CS/ONLINELOG/redo08.log')size200M;Databasealtered.SQL>alterdatabaseaddstandbylogfilethread2group9('+DATA/CS/ONLINELOG/redo09.log')size200M;Databasealtered.SQL>alterdatabaseaddstandbylogfilethread2group10('+DATA/CS/ONLINELOG/redo10.log')size200M;Databasealtered.

Oracle用户使用srvctl在备库的另一节点注册备库和备库实例

[oracle@jytest1dbs]$srvctladddatabase-dbcs_dg-oraclehome/u01/app/oracle/product/12.2.0/db/-dbtypeRAC-spfile+FRA/CS_DG/PARAMETERFILE/spfilecs.ora-pwfile+FRA/CS_DG/PASSWORD/pwdcs-rolephysical_standby-startoptionopen-stopoptionimmediate-dbnamecs-policyautomatic-diskgroupdata,fra,test[oracle@jytest1dbs]$srvctlconfigdatabase-dbcs_dgDatabaseuniquename:cs_dgDatabasename:csOraclehome:/u01/app/oracle/product/12.2.0/db/Oracleuser:oracleSpfile:+FRA/CS_DG/PARAMETERFILE/spfilecs.oraPasswordfile:+FRA/CS_DG/PASSWORD/pwdcsDomain:Startoptions:openStopoptions:immediateDatabaserole:PHYSICAL_STANDBYManagementpolicy:AUTOMATICServerpools:DiskGroups:DATA,FRA,TESTMountpointpaths:Services:Type:RACStartconcurrency:Stopconcurrency:OSDBAgroup:dbaOSOPERgroup:operDatabaseinstances:Configurednodes:CSScritical:noCPUcount:0Memorytarget:0Maximummemory:0Defaultnetworknumberfordatabaseservices:Databaseisadministratormanaged

添加实例

[oracle@jytest1dbs]$srvctladdinstance-dbcs_dg-instancecs1-nodejytest1[oracle@jytest1dbs]$srvctladdinstance-dbcs_dg-instancecs2-nodejytest2[oracle@jytest1dbs]$srvctlconfigdatabase-dbcs_dgDatabaseuniquename:cs_dgDatabasename:csOraclehome:/u01/app/oracle/product/12.2.0/db/Oracleuser:oracleSpfile:+FRA/CS_DG/PARAMETERFILE/spfilecs.oraPasswordfile:+FRA/CS_DG/PASSWORD/pwdcsDomain:Startoptions:openStopoptions:immediateDatabaserole:PHYSICAL_STANDBYManagementpolicy:AUTOMATICServerpools:DiskGroups:DATA,FRA,TESTMountpointpaths:Services:Type:RACStartconcurrency:Stopconcurrency:OSDBAgroup:dbaOSOPERgroup:operDatabaseinstances:cs1,cs2Configurednodes:jytest1,jytest2CSScritical:noCPUcount:0Memorytarget:0Maximummemory:0Defaultnetworknumberfordatabaseservices:Databaseisadministratormanaged[grid@jytest1~]$srvctlstartdatabase-dbcs_dg[grid@jytest1~]$crsctlstatres-t--------------------------------------------------------------------------------NameTargetStateServerStatedetails--------------------------------------------------------------------------------LocalResources--------------------------------------------------------------------------------ora.ASMNET1LSNR_ASM.lsnrONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.CRS.dgONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.DATA.dgONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.FRA.dgONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.LISTENER.lsnrONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.TEST.dgONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.chadONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.net1.networkONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.onsONLINEONLINEjytest1STABLEONLINEONLINEjytest2STABLEora.proxy_advmOFFLINEOFFLINEjytest1STABLEOFFLINEOFFLINEjytest2STABLE--------------------------------------------------------------------------------ClusterResources--------------------------------------------------------------------------------ora.LISTENER_SCAN1.lsnr1ONLINEONLINEjytest2STABLEora.LISTENER_SCAN2.lsnr1ONLINEONLINEjytest1STABLEora.LISTENER_SCAN3.lsnr1ONLINEONLINEjytest1STABLEora.MGMTLSNR1ONLINEONLINEjytest1169.254.123.14588.88.88.1,STABLEora.asm1ONLINEONLINEjytest1Started,STABLE2ONLINEONLINEjytest2Started,STABLE3ONLINEOFFLINESTABLEora.cs_dg.db1ONLINEONLINEjytest1Open,Readonly,HOME=/u01/app/oracle/product/12.2.0/db/,STABLE2ONLINEONLINEjytest2Open,Readonly,HOME=/u01/app/oracle/product/12.2.0/db/,STABLEora.cvu1ONLINEONLINEjytest1STABLEora.jy.jy_srv.svc1ONLINEONLINEjytest1STABLEora.jytest1.vip1ONLINEONLINEjytest1STABLEora.jytest2.vip1ONLINEONLINEjytest2STABLEora.mgmtdb1ONLINEONLINEjytest1Open,STABLEora.qosmserver1ONLINEONLINEjytest1STABLEora.scan1.vip1ONLINEONLINEjytest2STABLEora.scan2.vip1ONLINEONLINEjytest1STABLEora.scan3.vip1ONLINEONLINEjytest1STABLE--------------------------------------------------------------------------------

[oracle@jytest1~]$exportORACLE_SID=cs1[oracle@jytest1~]$sqlplus/assysdbaSQL*Plus:Release12.2.0.1.0ProductiononTueMay1523:02:412018Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>setline120SQL>selectname,database_role,open_modefromgv$database;NAMEDATABASE_ROLEOPEN_MODE---------------------------------------------CSPHYSICALSTANDBYREADONLYCSPHYSICALSTANDBYREADONLY[oracle@jytest2dbs]$exportORACLE_SID=cs2[oracle@jytest2dbs]$sqlplus/assysdbaSQL*Plus:Release12.2.0.1.0ProductiononTueMay1523:02:472018Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>setline120SQL>selectname,database_role,open_modefromgv$database;NAMEDATABASE_ROLEOPEN_MODE---------------------------------------------CSPHYSICALSTANDBYREADONLYCSPHYSICALSTANDBYREADONLY

在备库的单个节点上执行实时重做应用

SQL>alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnectfromsession;Databasealtered.

alert日志信息如下

MediaRecoveryLog+TEST/arch/cs_dg/2_129_970601983.dbf2018-05-15T23:04:44.911711+08:00Completed:alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnectfromsession2018-05-15T23:04:45.116759+08:00MediaRecoveryLog+TEST/arch/cs_dg/1_145_970601983.dbf2018-05-15T23:04:45.676517+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_130_970601983.dbf2018-05-15T23:05:31.642093+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_131_970601983.dbf

在主库创建表空间test

SQL>createtablespacetest;Tablespacecreated.SQL>selecttablespace_namefromdba_tablespaces;TABLESPACE_NAME------------------------------SYSTEMSYSAUXUNDOTBS1TEMPUSERSUNDOTBS2TEST7rowsselected.SQL>selectcon_id,ts#,namefromv$tablespace;CON_IDTS#NAME--------------------------------------------------11SYSAUX10SYSTEM12UNDOTBS114USERS13TEMP20SYSTEM21SYSAUX22UNDOTBS123TEMP15UNDOTBS216TESTSQL>selectts#,namefromv$datafilewherets#=6andcon_id=1;TS#NAME------------------------------------------------------------------------------------------6+DATA/CS/DATAFILE/test.326.976211663

[oracle@jytest1trace]$tail-falert_cs1.log2018-05-15T23:04:44.736977+08:00NOTE:dependencybetweendatabasecs_dganddiskgroupresourceora.TEST.dgisestablished2018-05-15T23:04:44.842580+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_129_970601983.dbf2018-05-15T23:04:44.911711+08:00Completed:alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnectfromsession2018-05-15T23:04:45.116759+08:00MediaRecoveryLog+TEST/arch/cs_dg/1_145_970601983.dbf2018-05-15T23:04:45.676517+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_130_970601983.dbf2018-05-15T23:05:31.642093+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_131_970601983.dbf2018-05-15T23:06:11.885256+08:00MediaRecoveryLog+TEST/arch/cs_dg/1_146_970601983.dbf2018-05-15T23:06:26.490187+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_132_970601983.dbf2018-05-15T23:06:36.761337+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_133_970601983.dbf2018-05-15T23:06:38.014959+08:00MediaRecoveryLog+TEST/arch/cs_dg/1_147_970601983.dbf2018-05-15T23:06:38.932380+08:00MediaRecoveryLog+TEST/arch/cs_dg/1_148_970601983.dbf2018-05-15T23:06:40.372178+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_134_970601983.dbf2018-05-15T23:06:40.994801+08:00MediaRecoveryLog+TEST/arch/cs_dg/1_149_970601983.dbf2018-05-15T23:06:41.656032+08:00MediaRecoveryLog+TEST/arch/cs_dg/2_135_970601983.dbf2018-05-15T23:06:47.456319+08:00MediaRecoveryWaitingforthread2sequence136(intransit)2018-05-15T23:06:47.474190+08:00RecoveryofOnlineRedoLog:Thread2Group9Seq136Readingmem0Mem#0:+FRA/CS_DG/ONLINELOG/redo09.log2018-05-15T23:06:52.286510+08:00MediaRecoveryWaitingforthread1sequence150(intransit)2018-05-15T23:06:52.338688+08:00RecoveryofOnlineRedoLog:Thread1Group6Seq150Readingmem0Mem#0:+FRA/CS_DG/ONLINELOG/redo06.log2018-05-15T23:08:48.743309+08:00Successfullyaddeddatafile10tomediarecoveryDatafile#10:'+FRA/CS_DG/DATAFILE/test.327.976230527'

从上面信息可以看到在备库创建的表空间test的数据文件名，下面从备库进行查询来验证

SQL>selectcon_id,ts#,namefromv$tablespace;CON_IDTS#NAME--------------------------------------------------11SYSAUX10SYSTEM12UNDOTBS114USERS13TEMP20SYSTEM21SYSAUX22UNDOTBS123TEMP15UNDOTBS216TEST11rowsselected.SQL>selectts#,namefromv$datafilewherets#=6andcon_id=1;TS#NAME----------------------------------------------------------------------------------------------------------------------------------6+FRA/CS_DG/DATAFILE/test.327.976230527

验证主库

SQL>createuserc##testidentifiedby"test"defaulttablespaceuserstemporarytablespacetemp;Usercreated.SQL>grantdba,connect,resourcetoc##test;Grantsucceeded.SQL>createtablec##test.t1asselect*fromdba_objectswhere1=2;Tablecreated.

备库

SQL>descc##test.t1NameNull?Type---------------------------------------------------------------------------------------------------------------------OWNERVARCHAR2(128)OBJECT_NAMEVARCHAR2(128)SUBOBJECT_NAMEVARCHAR2(128)OBJECT_IDNUMBERDATA_OBJECT_IDNUMBEROBJECT_TYPEVARCHAR2(23)CREATEDDATELAST_DDL_TIMEDATETIMESTAMPVARCHAR2(19)STATUSVARCHAR2(7)TEMPORARYVARCHAR2(1)GENERATEDVARCHAR2(1)SECONDARYVARCHAR2(1)NAMESPACENUMBEREDITION_NAMEVARCHAR2(128)SHARINGVARCHAR2(18)EDITIONABLEVARCHAR2(1)ORACLE_MAINTAINEDVARCHAR2(1)APPLICATIONVARCHAR2(1)DEFAULT_COLLATIONVARCHAR2(100)DUPLICATEDVARCHAR2(1)SHARDEDVARCHAR2(1)CREATED_APPIDNUMBERCREATED_VSNIDNUMBERMODIFIED_APPIDNUMBERMODIFIED_VSNIDNUMBER

主库:节点1

SQL>insertintoc##test.t1select*fromdba_objects;73390rowscreated.SQL>commit;Commitcomplete.SQL>selectcount(*)fromc##test.t1;COUNT(*)----------73390

备库:节点1

SQL>selectcount(*)fromc##test.t1;COUNT(*)----------73390

主库:节点2

SQL>insertintoc##test.t1select*fromc##test.t1;73390rowscreated.SQL>commit;Commitcomplete.SQL>selectcount(*)fromc##test.t1;COUNT(*)----------146780

备库: 节点2

SQL>selectcount(*)fromc##test.t1;COUNT(*)----------146780

主库:

SQL>truncatetablec##test.t1;Tabletruncated.SQL>selectcount(*)fromc##test.t1;COUNT(*)----------0

备库:

SQL>selectcount(*)fromc##test.t1;COUNT(*)----------0

验证物理备库是否执行正确
在创建物理备库后并且设置重做传输服务，可能想要验证主库的数据库修改是否会成功的传输到备库。对于备库可以查询v$managed_standby视图来验证重做是否被从主库传输到备库并应用。

[oracle@jytest1~]$exportORACLE_SID=cs1[oracle@jytest1~]$sqlplus/assysdbaSQL*Plus:Release12.2.0.1.0ProductiononWedMay1622:41:102018Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>selectclient_process,process,thread#,sequence#,statusfromv$managed_standbywhereclient_process='LGWR'orprocess='MRP0';CLIENT_PPROCESSTHREAD#SEQUENCE#STATUS-------------------------------------------------LGWRRFS1151IDLEN/AMRP02139APPLYING_LOG

上面的查询对于使用CLIENT_PROCESS为LGWR的主库会显示一行记录，它指示重做传输工作正常并且主重做线程将会被发送到备库。如果主库是RAC数据库，那么对于使用CLIENT_PROCESS为LGWR的当前活动的每个主库实例都会显示一行记录。上面的查询对于MRP也行显示一行。如果MRP的状态显示为APPLYING_LOG并且SEQUENCE#等于主库当前正被发送的日志
序列号，那么备库已经解决了所有的日志差异并且当前处于实时应用日志模式。

查询主库当前正被发送日志的序列号为139与上面的MRP进程所显示的sequence#(139)相同

SQL>selectgroup#,thread#,sequence#,statusfromv$log;GROUP#THREAD#SEQUENCE#STATUS----------------------------------------------11151CURRENT21150INACTIVE32139CURRENT42138INACTIVE

注意MRP进程可能显示的sequence#比主库当前被发送的日志序列号小，那么这就表示正在应用的归档重做日志文件与发送的日志文件之间存在差异并且它并没有赶上。一旦所有差异被解决，相同的查询将显示MRP正在应用当前sequence#。