database_role， elapsed time: 00:05:25channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=+DG_DATA1/primary1/datafile/sysaux.262.915031285output file name=+DG_DATA1/standsty1/datafile/sysaux.259.915105649 tag=TAG20160621T114948channel ORA_DISK_1: datafile copy complete， elapsed time: 00:02:45channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=+DG_DATA1/primary1/datafile/users.265.915031353output file name=+DG_DATA1/standsty1/datafile/users.260.915105815 tag=TAG20160621T114948channel ORA_DISK_1: datafile copy complete。

applied from v$archived_log where DEST_ID=2 order by sequence#;-------------------至此 ADG已基本搭建完毕11、主库、备库 的删除归档日志策略，primary1)*.log_archive_dest_1=LOCATION=+DG_ARCH VALID_FOR=(ALL_LOGFILES，为read only只读模式#SQL select open_mode， member from v$logfile;9、在备库开启到read only状态#查看当前备库状态为MOUNTED#SQL select open_mode， elapsed time: 00:05:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=+DG_DATA1/primary1/datafile/undotbs1.263.915031305output file name=+DG_DATA1/standsty1/datafile/undotbs1.258.915105325 tag=TAG20160621T114948channel ORA_DISK_1: datafile copy complete，ALL_ROLES) DB_UNIQUE_NAME=primary1 *.log_archive_dest_2=SERVICE=standsty1 OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES，db_unique_name from v$database;OPEN_MODEDATABASE_ROLE DB_UNIQUE_NAME-------------------- ---------------- ------------------------------READ ONLYPHYSICAL STANDBY primary1#开启实时日志应用#SQL alter database recover managed standby database disconnect from session;Database altered.--备库：standsty1#切换到primary主库模式#SQL alter database commit to switchover to primary;Database altered./* 遭遇的问题在物理DG主备库切换时遇到ORA-16139: media recovery required错误SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY*ERROR at line 1:ORA-16139: media recovery required解决办法： --如果报ORA-16139: media recovery required，可能是由于未应用日志引起。

db_unique_name from v$database;OPEN_MODEDATABASE_ROLE DB_UNIQUE_NAME-------------------- ---------------- ------------------------------READ ONLYPHYSICAL STANDBY standsty110、在备库开启实时日志应用SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Database altered.#取消实时日志应用的语句#--ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;#查看备库是否应用redo，primary1*.log_file_name_convert=standsty1。

database_role，PRIMARY_ROLE) DB_UNIQUE_NAME=standsty1*.log_archive_dest_state_1=ENABLE *.log_archive_dest_state_2=ENABLE *.log_archive_format=%t_%s_%r.arc*.standby_file_management=auto*.log_archive_max_processes=30*.db_file_name_convert=standsty1， group 6 size 5G。

并切换主库standsty1 日志文件 #SQL select max(sequence#) from v$archived_log;MAX(SEQUENCE#)--------------38SQL alter system switch logfile;System altered.SQL select max(sequence#) from v$archived_log;MAX(SEQUENCE#)--------------39#备库primary1 查看日志文件和主库的相同， group 8 size 5G;#因每组两个成员，database_role， elapsed time: 00:00:03Finished backup at 2016-06-21 11:49:12sql statement: create spfile from memoryOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 42757922816 bytesFixed Size2255784 bytesVariable Size20669531224 bytesDatabase Buffers21474836480 bytesRedo Buffers611299328 bytessql statement: alter system set control_files = +DG_DATA1/standsty1/controlfile/current.256.915104949 comment= Set by RMAN scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 42757922816 bytesFixed Size2255784 bytesVariable Size20669531224 bytesDatabase Buffers21474836480 bytesRedo Buffers611299328 bytescontents of Memory Script:{ sql clone alter database mount standby database;}executing Memory Scriptsql statement: alter database mount standby databaseRMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.contents of Memory Script:{ set newname for tempfile 1 to +dg_data1; switch clone tempfile all; set newname for datafile 1 to +dg_data1; set newname for datafile 2 to +dg_data1; set newname for datafile 3 to +dg_data1; set newname for datafile 4 to +dg_data1; backup as copy reuse datafile 1 auxiliary format +dg_data1 datafile 2 auxiliary format +dg_data1 datafile 3 auxiliary format +dg_data1 datafile 4 auxiliary format +dg_data1 ; sql alter system archive log current;}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to +dg_data1 in control fileexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 2016-06-21 11:49:48using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=+DG_DATA1/primary1/datafile/system.261.915031245output file name=+DG_DATA1/standsty1/datafile/system.257.915104989 tag=TAG20160621T114948channel ORA_DISK_1: datafile copy complete。

测试是否成功--当前主备库信息：主库：primary1备库：standsty1--切换后主备库信息： 主库：standsty1备库：primary1--主库：primary1#切换到standby备库模式#SQL alter database commit to switchover to physical standby;Database altered.#重启数据库#SQL shutdown immediateORA-01012: not logged onSQL startupORACLE instance started.Total System Global Area 4.2758E+10 bytesFixed Size2255784 bytesVariable Size2.0670E+10 bytesDatabase Buffers2.1475E+10 bytesRedo Buffers611299328 bytesDatabase mounted.Database opened.#查看状态为备库#SQL select open_mode，默认情况下就是NONE##主库设置 SHIPPED TO ALL STANDBY ：设置为该值时。

PRIMARY_ROLE) DB_UNIQUE_NAME=primary1*.log_archive_dest_state_1=ENABLE *.log_archive_dest_state_2=ENABLE *.log_archive_format=%t_%s_%r.arc*.standby_file_management=auto*.log_archive_max_processes=30*.db_file_name_convert=primary1，ALL_ROLES) DB_UNIQUE_NAME=standsty1 *.log_archive_dest_2=SERVICE=primary1 OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES， Oracle and/or its affiliates. All rights reserved.connected to target database: primary1 (DBID=32832276)using target database control file instead of recovery catalogconnected to auxiliary database: primary1 (not mounted)#duplicate到备库中#RMAN duplicate target database for standby from active database nofilenamecheck;Starting Duplicate Db at 2016-06-21 11:49:06allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=9391 device type=DISKcontents of Memory Script:{ backup as copy reuse targetfile /oracle/app/oracle/product/11.2.0/db/dbs/orapwprimary1 auxiliary format /oracle/app/oracle/product/11.2.0/db/dbs/orapwstandsty1 ;}executing Memory ScriptStarting backup at 2016-06-21 11:49:07allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=4037 device type=DISKFinished backup at 2016-06-21 11:49:08contents of Memory Script:{ backup as copy current controlfile for standby auxiliary format +DG_DATA1/standsty1/controlfile/current.256.915104949; sql clone create spfile from memory; shutdown clone immediate; startup clone nomount; sql clone alter system set control_files = +DG_DATA1/standsty1/controlfile/current.256.915104949 comment= Set by RMAN scope=spfile; shutdown clone immediate; startup clone nomount;}executing Memory ScriptStarting backup at 2016-06-21 11:49:08using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copycopying standby control fileoutput file name=/oracle/app/oracle/product/11.2.0/db/dbs/snapcf_primary1.f tag=TAG20160621T114908 RECID=1 STAMP=915104949channel ORA_DISK_1: datafile copy complete，替换以下内容*.audit_file_dest=/oracle/app/oracle/admin/standsty1/adump*.db_name=primary1*.db_unique_name=standsty1 *.dispatchers=(PROTOCOL=TCP) (SERVICE=standsty1XDB) *.fal_client=standsty1 *.fal_server=primary1*.log_archive_config=DG_CONFIG=(standsty1，username from dba_users;DEFAULT_TABLESPACEUSERNAME------------------------------ ------------------------------SYSTEMSYSSYSTEMSYSTEMTBS_DPUBCYSYSTEMOUTLNSYSAUXAPPQOSSYSSYSAUXDBSNMPSYSAUXWMSYSUSERSDIPUSERSORACLE_OCM-------------------------------------------------测试1：建立用户 建立表空间 数据文件- 结束----------------------------------------------------------------------------------------------------------------测试2：主备库切换-开始-----------------------------------------------------------------测试目的：主备库切换，standsty17、在主库通过Rman Duplicate创建备库，db_unique_name from v$database;OPEN_MODEDATABASE_ROLE DB_UNIQUE_NAME-------------------- ---------------- ------------------------------READ WRITEPRIMARYstandsty1--测试一下是否切换成功#查看。

用rman配置 CONFIGURE ARCHIVELOG DELETION POLICY 参数#NONE ：设置为该值时，会强制检查待删除的log 是否已经在备库apply，standsty1*.log_file_name_convert=primary1，增加以下内容*.db_name=primary1*.db_unique_name=primary1 *.dispatchers=(PROTOCOL=TCP) (SERVICE=primary1XDB) *.fal_client=primary1 *.fal_server=standsty1*.log_archive_config=DG_CONFIG=(primary1， 2011，primary1以spfile启动数据库SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startup pfile=/oracle/app/oracle/product/11.2.0/db/dbs/initprimary1.oraORACLE instance started.Total System Global Area 3.6081E+10 bytesFixed Size2253536 bytesVariable Size1.3959E+10 bytesDatabase Buffers2.1475E+10 bytesRedo Buffers644853760 bytesDatabase mounted.Database opened.SQL create spfile=+DG_DATA1/primary1/spfileprimary1.ora from pfile=/oracle/app/oracle/product/11.2.0/db/dbs/initprimary1.ora;File created.SQL shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 3.6081E+10 bytesFixed Size2253536 bytesVariable Size1.3959E+10 bytesDatabase Buffers2.1475E+10 bytesRedo Buffers644853760 bytesDatabase mounted.Database opened.SQL show parameter spfile;NAMETYPEVALUE------------------------------------ ----------- ------------------------------spfilestring+DG_DATA1/primary1/spfileprimary1.ora5、拷贝主库的pfile和密码文件到备库上[ythisbilldbn1] cd /oracle/app/oracle/product/11.2.0/db/dbs/[ythisbilldbn1] scp initprimary1.ora 10.143.53.33:/oracle/app/oracle/product/11.2.0/db/dbs[ythisbilldbn1] scp orapwprimary1 10.143.53.33:/oracle/app/oracle/product/11.2.0/db/dbs6、备库修改参数文件修改从主库拷贝过来的pfile文件 /oracle/app/oracle/product/11.2.0/db/dbs/initprimary1.ora，增加备用日志的成员#SQL alter database add standby logfile member +DG_DATA1 to group 5;SQL alter database add standby logfile member +DG_DATA1 to group 6;SQL alter database add standby logfile member +DG_DATA1 to group 7;SQL alter database add standby logfile member +DG_DATA1 to group 8; #查看日志文件是否添加成功#SQL select group#， group 7 size 5G， 环境说明： 主库： ip：10.143.183.31 sid：primary1 建立 GI 、ASM、oracle数据库软件、oracle数据库 备库： ip：10.143.183.33 sid：standsty1 建立 GI 、ASM、oracle数据库软件、无需建立oracle数据库 ASM磁盘组说明： 数据： +DG_DATA1 +DG_DATA2 、 索引：+DG_INDEX 、 归档： +DG_ARCH 安装步骤： 1、修改监听主库：修改tnsnames.ora：primary1 = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.53.31)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = primary1)) ) standsty1 = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.53.33)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = standsty1)) )修改listener.ora：SID_LIST_LISTENER=(SID_LIST =(SID_DESC =(GLOBAL_DBNAME= primary1)(ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db)(SID_NAME=primary1)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.53.31)(PORT = 1521))))备库：修改tnsnames.ora：primary1 = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.53.31)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = primary1)) ) standsty1 = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.53.33)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = standsty1)) )修改listener.ora：SID_LIST_LISTENER=(SID_LIST =(SID_DESC =(GLOBAL_DBNAME= primary1)(ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db)(SID_NAME=standsty1)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.53.33)(PORT = 1521))))2、主库设置归档：开启归档SQL alter database archivelog;Database altered.设置归档路径SQL alter system set log_archive_dest_1=location=+DG_ARCH scope=both;System altered.切换日志SQL alter system archive log current;System altered.查询归档日志ASMCMD ls -lTypeRedund Striped TimeSys NameARCHIVELOG UNPROT COARSE JUN 20 20:00:00 Y thread_1_seq_1.256.915051109ARCHIVELOG UNPROT COARSE JUN 20 20:00:00 Y thread_1_seq_2.257.915051111ARCHIVELOG UNPROT COARSE JUN 20 20:00:00 Y thread_1_seq_3.258.9150512313、主库设置force logging模式SQL alter database force logging; Database altered.4、主库修改参数文件SQL create pfile=/oracle/app/oracle/product/11.2.0/db/dbs/initprimary1.ora from spfile;File created.修改pfile文件 /oracle/app/oracle/product/11.2.0/db/dbs/initprimary1.ora，db_unique_name from v$database;OPEN_MODEDATABASE_ROLE DB_UNIQUE_NAME-------------------- ---------------- ------------------------------MOUNTEDPHYSICAL STANDBY standsty1#关闭数据库#SQL shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.#启动数据库#SQL startupORACLE instance started.Total System Global Area 4.2758E+10 bytesFixed Size2255784 bytesVariable Size2.0670E+10 bytesDatabase Buffers2.1475E+10 bytesRedo Buffers611299328 bytesDatabase mounted.Database opened.#查看数据库状态，主库执行[ythisbilldbn1:oracle:/home/oracle]rman target sys/xxxxxx@primary1 auxiliary sys/xxxxxx@standsty1 nocatalogRecovery Manager: Release 11.2.0.4.0 - Production on Tue Jun 21 09:44:29 2016Copyright (c) 1982，database_role，standsty1)*.log_archive_dest_1=LOCATION=+DG_ARCH VALID_FOR=(ALL_LOGFILES，切换成功SQL select max(sequence#) from v$archived_log;MAX(SEQUENCE#)--------------39-------------------------------------------------测试2：主备库切换-结束--------------------------------------------------------------- ， elapsed time: 00:00:01Finished backup at 2016-06-21 12:03:35sql statement: alter system archive log currentcontents of Memory Script:{ switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copyinput datafile copy RECID=1 STAMP=915105815 file name=+DG_DATA1/standsty1/datafile/system.257.915104989datafile 2 switched to datafile copyinput datafile copy RECID=2 STAMP=915105815 file name=+DG_DATA1/standsty1/datafile/sysaux.259.915105649datafile 3 switched to datafile copyinput datafile copy RECID=3 STAMP=915105815 file name=+DG_DATA1/standsty1/datafile/undotbs1.258.915105325datafile 4 switched to datafile copyinput datafile copy RECID=4 STAMP=915105815 file name=+DG_DATA1/standsty1/datafile/users.260.915105815Finished Duplicate Db at 2016-06-21 12:04:218、备库增加4组备用日志SQL alter database add standby logfile group 5 size 5G，主备库查命令#select sequence#，则不启用归档文件的删除策略，只有apply后的log才能删除#RMAN CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;new RMAN configuration parameters:CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;new RMAN configuration parameters are successfully stored#删归档日志#RMAN delete archivelog all/*设置自动删除归档日志 crontab#设置删除归档日志脚本[ythisbilldbn1:oracle:/oracle/script]vi del_arch.shexport ORACLE_SID=primary1export ORACLE_BASE=/oracle/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbexport NAME=`date +%Y%m%d%T`$ORACLE_HOME/bin/rman target / nocatalog log=/oracle/script/delete_archivelog_log/del_arch_$NAME.logEOFrun{delete noprompt archivelog all completed before sysdate - 1;}exitEOF#设置crontab 每小时0分 删除[ythisbilldbn1:oracle:/oracle/script]crontab -e0 * * * * /oracle/script/del_arch.sh*/-------------------------------------------------测试1：建立用户 建立表空间 数据文件- 开始---------------------------------------------------------------主库：建立用户 建立表空间 数据文件SQL create tablespace TBS_DPUB datafile +DG_DATA1 size 1M autoextend on maxsize 62G;Tablespace created.SQLalter tablespace TBS_DPUB add datafile +DG_DATA2 size 1M autoextend on maxsize 62G;Tablespace altered.SQL create user cy identified by cy;SQL alter user cy default tablespace TBS_DPUB;User altered.SQL alter system switch logfile;System altered.备库：检查 是否同步 建立用户 建立表空间 数据文件SQL select name from v$tablespace;NAME------------------------------SYSTEMSYSAUXUNDOTBS1TEMPUSERSTBS_DPUB6 rows selected.SQL select DEFAULT_TABLESPACE，当归档传送到备库就可以删除#RMAN CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;new RMAN configuration parameters:CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;new RMAN configuration parameters are successfully stored#备库设置 APPLIED ON STANDBY ：设置为该值时，可先执行ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;然后再执行该命令*/#重启数据库#SQL shutdown immediateORA-01109: database not openDatabase dismounted.startupORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 4.2758E+10 bytesFixed Size2255784 bytesVariable Size2.0670E+10 bytesDatabase Buffers2.1475E+10 bytesRedo Buffers611299328 bytesDatabase mounted.Database opened.#查看状态为主库#SQL select open_mode，。