RMAN远程复制搭建物理DG过程小结

本文仅记录搭建的过程，具体详细的参数意义和配置原理请参考之前的总结 http://www.linuxidc.com/Linux/2015-07/119932.htm搭建环境前配置主备库的tns，确保两数据库能正常彼此通信primary确定数据库开启强制归档startup mount;alter database archivelog;alter database force logging;alter database open;修改配置，并导出pfile，将pfile复制到目标备库alter system set db_unique_name=pri scope=spfile;alter system set log_archive_config = "DG_CONFIG=（pri,sty）" scope=spfile;alter system set log_archive_dest_1 = "LOCATION=/opt/app/Oracle/product/11.2.0/dbhome_1/dbs/arch VALID_FOR=（ALL_LOGFILES,ALL_ROLES） DB_UNIQUE_NAME=pri" scope=spfile;alter system set log_archive_dest_2 = "SERVICE=sty LGWR SYNC VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=sty" scope=spfile;alter system set log_archive_dest_state_1 = ENABLE;alter system set log_archive_dest_state_2 = ENABLE;alter system set fal_server=sty scope=spfile;alter system set fal_client=pri scope=spfile;alter system set standby_file_management=AUTO scope=spfile;create pfile="/home/oracle/pripfile.ora" from spfile;standby 安装数据库软件，无需安装数据库复制元库的sys密码文件，确保两库的密码一致scp 192.168.20.46:$ORACLE_HOME/dbs/orapwxtttestdb $ORACLE_HOME/dbs/复制目标库导出的pfile，并添加 *.log_file_name_convert参数选项（10g之后必须添加，即使路径没有改变） scp 192.168.20.46:/home/oracle/pripfile.ora /home/oracle/*.log_file_name_convert="/opt/app/oracle/oradata/xtttestdb/","/opt/app/oracle/oradata/xtttestdb/"创建要恢复备库的必要目录mkdir -p /opt/app/oracle/admin/xtttestdb/adump
mkdir -p /opt/app/oracle/oradata/xtttestdb
mkdir -p /opt/app/oracle/product/11.2.0/dbhome_1/dbs/arch设置SID登入数据库export $ORACLE_SID=xtttestdbsqlplus / as sysdba利用copy并修改后的pfile创建spfile，并启动到nomount
SQL> create spfile from pfile="/home/oracle/pripfile.ora";
File created. SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2213696 bytes
Variable Size 1174407360 bytes
Database Buffers 385875968 bytes
Redo Buffers 7512064 bytes修改备库的参数配置alter system set db_unique_name=sty scope=spfile;alter system set log_archive_config="DG_CONFIG=（pri,dg）" scope=spfile;alter system set log_archive_dest_1 ="LOCATION=/opt/app/oracle/product/11.2.0/dbhome_1/dbs/arch VALID_FOR=（ALL_LOGFILES,ALL_ROLES） DB_UNIQUE_NAME=sty" scope=spfile;alter system set log_archive_dest_2 ="SERVICE=pri LGWR SYNC VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=pri" scope=spfile;alter system set fal_server=pri scope=spfile;alter system set fal_client=sty scope=spfile;重启数据库到nomount，是配置生效（这些配置也可以在pfile中修改完成后再启动数据库库）SQL> shutdown immediate;SQL> startup mount;primary利用rman复制数据库rman target sys/oraclepwd@XTTTESTDB.46 auxiliary sys/oraclepwd@XTTTESTDB.54
RMAN> duplicate target database for standby from active database nofilenamecheck;复制完成后在主备库天剑standby redo（至少要三组）alter database add standby logfilegroup 4 （"/opt/app/oracle/oradata/xtttestdb/styredo04.log"） size 50m,group 5 （"/opt/app/oracle/oradata/xtttestdb/styredo05.log"） size 50m,group 6 （"/opt/app/oracle/oradata/xtttestdb/styredo06.log"） size 50m,group 7 （"/opt/app/oracle/oradata/xtttestdb/styredo07.log"） size 50m;启动standby的redo应用的两种方式①、默认的物理DG启动应用后，在主库arch日志被完整写入后才会开始应用该arch logSQL> alter database recover managed standby database disconnect from session;②、可以添加current logfile参数，使得应用当前正在读写，还没有完成归档的日志SQL> alter database recover managed standby database using current logfile disconnect from session;关闭REDO应用SQL> alter database recover managed standby database cancel;查看standby log状态select group#,thread#,sequence#,archived,status from v$standby_log;查看应用日志情况
select name,creator,sequence#,applied,completion_time from v$archived_log;验证：primary端创建测试表，并添加数据SQL> select count（*） from test; COUNT（*）---------- 7SQL> insert into test select * from test;7 rows created.SQL> commit;Commit complete.SQL> select count（*） from test; COUNT（*）---------- 14standby端验证数据是否同步SQL> select count（*） from test; COUNT（*）---------- 14搭建过程问题小结：1、在备库启动到nomount后用tns测试连接时发现数无法连接
ORA-12528: TNS:listener: all appropriate instances are blocking new connections原因是11g之后动态监听不支持在nomount状态下远程的tns访问，自己的服务器中配置的监听一直是动态的添加listener.ora 文件，为standby设置静态监听SID_LIST_LISTENER = （SID_LIST = （SID_DESC = （SID_NAME = PLSExtProc）（ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1）（PROGRAM = extproc））（SID_DESC = （GLOBAL_DBNAME = xtttestdb）（ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1）（SID_NAME = xtttestdb）））之后再测试连接正常2、RMAN远程复制数据库完成后有redo的报错ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamedORA-00312: online log 1 thread 1: "/opt/app/oracle/oradata/xtttestdb/redo01.log"根据错误提示，加上网上搜索一下，原来10g之后的DG即使日志的原备库路径一样，为了区分开来，还是要设置log_file_name_convert参数，创建备库的pfile文件，并添加该参数进去，利用pfile启动数据库，问题解决create pfile="/home/oracle/stypfile.ora" from spfile;添加*.log_file_name_convert="/opt/app/oracle/oradata/xtttestdb/","/opt/app/oracle/oradata/xtttestdb/"creaet spfile from pfile="/home/oracle/stypfile.ora"startup--------------------------------------分割线 --------------------------------------Oracle Data Guard 重要配置参数 http://www.linuxidc.com/Linux/2013-08/88784.htm基于同一主机配置 Oracle 11g Data Guard http://www.linuxidc.com/Linux/2013-08/88848.htm探索Oracle之11g DataGuard http://www.linuxidc.com/Linux/2013-08/88692.htmOracle Data Guard （RAC+DG）归档删除策略及脚本 http://www.linuxidc.com/Linux/2013-07/87782.htmOracle Data Guard 的角色转换 http://www.linuxidc.com/Linux/2013-06/86190.htmOracle Data Guard的日志FAL gap问题 http://www.linuxidc.com/Linux/2013-04/82561.htmOracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法 http://www.linuxidc.com/Linux/2013-03/82009.htm--------------------------------------分割线 --------------------------------------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址