Oracle11g ADG 搭建

环境：
Oracle 11.2.0.4 single instance 两套
备库只安装Oracle软件及监听。
一、主库操作
1、主库备份pfile以便记录原参数

SQL>create pfile='/home/oracle/pfilebak.ora'  from spfile;

2、修改数据库参数

 更改force logging： alter database force logging; 归档模式：archive log list; ###为归档模式 查看：select log_mode,force_logging from v$database;alter system set log_archive_config='DG_CONFIG=(orcl,prod)' scope=spfile;alter system set log_archive_dest_1='location=/u01/app/archivelog valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'  scope=both sid='*';alter system set log_archive_dest_2='service=prod LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=prod'  scope=both sid='*';alter system set log_archive_dest_state_1='enable' scope=both sid='*';alter system set log_archive_dest_state_2='enable' scope=both sid='*';alter system set fal_client='orcl'  scope=both sid='*';alter system set fal_server='prod'  scope=both sid='*';alter system set standby_file_management='AUTO'  scope=both sid='*';文件路径转换参数需要重启数据库生效alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/prod/' scope=spfile sid='*';alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/prod/'  scope=spfile sid='*';创建最新pfile文件SQL> create pfile='/home/oracle/pfile.ora' from spfile;

二、备库操作
检查目录ORALCE_BASE,ORACLE_HOME,archive_log,orainventory,controlfile,datafile,adump
三、主库备份

vi /u01/backup/rman.shchmod 777 rman.shnohup sh /u01/backup/rman.sh &export ORACLE_SID=orclrman target / <

四、主库操作
scp 密码文件（需要将的sid改为备库sid)，最新pfile文件，备份文件，redo
五、备库操作

更改pfile文件db_name='orcl'应与主库一致*.db_unique_name='prod'*.audit_file_dest='/u01/app/oracle/admin/prod/adump' 注意路径log_archive_dest_1='location=/u01/archivelog'*.db_recovery_file_dest修改oracle_base删除log_archive_dest_2控制文件路径检查文件中的所有路径是否正确

六、恢复备库

SQL>startup nomount pfile='/home/oracle/pfile.ora';rman target / nocatalogRMAN> restore standby controlfile from '/u01/backup/control01.ctl';SQL>alter database mount;catalog start with '/u01/backup/';run{allocate channel c1 device type disk;allocate channel c2 device type disk;allocate channel c3 device type disk;allocate channel c4 device type disk;set newname for datafile  1 to '/u01/app/oracle/oradata/prod/system01.dbf';set newname for datafile  2 to '/u01/app/oracle/oradata/prod/sysaux01.dbf';set newname for datafile  3 to '/u01/app/oracle/oradata/prod/undotbs01.dbf';set newname for datafile  4 to '/u01/app/oracle/oradata/prod/users01.dbf';set newname for datafile  5 to '/u01/app/oracle/oradata/prod/example01.dbf';restore database;release channel c1;release channel c2;release channel c3;release channel c4;}recover database;

七、主备库添加standby日志(比online log至少多一个)

主库alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/orcl/standby04.log') size 50M;alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/orcl/standby05.log') size 50M;alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/orcl/standby06.log') size 50M;alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/orcl/standby07.log') size 50M;alter database add standby logfile thread 1 group 8 ('/u01/app/oracle/oradata/orcl/standby08.log') size 50M;备库alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/prod/standby04.log') size 50M;alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/prod/standby05.log') size 50M;alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/prod/standby06.log') size 50M;alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/prod/standby07.log') size 50M;alter database add standby logfile thread 1 group 8 ('/u01/app/oracle/oradata/prod/standby08.log') size 50M;

八、主备库tnsnames一致
测试 tnsping orcl tnsping prod
九、备库操作

SQL>alter database recover managed standby database disconnect from session;SQL> recover managed standby database cancel;SQL>alter database open read only;SQL>alter database recover managed standby database using current logfile disconnect from session;

十、验证

主库 v$archived_logSQL> select thread#,max(sequence#) from v$archived_log where applied='NO' group by thread#;备库 v$archived_logSQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;备库 v$managed_standby;select process,status,thread#,sequence# from v$managed_standby;或者SELECT 'RECEIVED'||chr(9)||rtrim(received1)||'-> '||received_time1||chr(9)||rtrim(received2)||'-> '||received_time2FROM(select max(sequence#) received1, to_char(max(next_time),'YYYY/MM/DD HH24:MI:SS') RECEIVED_TIME1from V$ARCHIVED_LOGwhere thread#=1),(select max(sequence#) received2, to_char(max(next_time),'YYYY/MM/DD HH24:MI:SS') RECEIVED_TIME2from V$ARCHIVED_LOGwhere thread#=2)/SELECT 'APPLIED '||chr(9)||rtrim(applied1)||'-> '||applied_time1||chr(9)||rtrim(applied2)||'-> '||applied_time2from(select MAX(SEQUENCE#) applied1, TO_CHAR(MAX(COMPLETION_TIME),'YYYY/MM/DD HH24:MI:SS') APPLIED_TIME1from V$ARCHIVED_LOG where applied='YES' and thread#=1),(select MAX(SEQUENCE#) applied2, TO_CHAR(MAX(COMPLETION_TIME),'YYYY/MM/DD HH24:MI:SS') APPLIED_TIME2from V$ARCHIVED_LOG where applied='YES' and thread#=2)/