oracle 11g dgbroker搭建dataguard

前提是主安装上oracle软件和数据库，备库安装好oracle软件即可--略
主库：90.10.10.51 charles51.example.com PROD1  PROD1.example.com
备库：90.10.10.52 charles52.example.com SBDB1  SBDB1.example.com


1.配置主备的hostname
vi /etc/hosts
90.10.10.51 charles51.example.com
90.10.10.52 charles52.example.com




2.配置listener与tnsname


2.1 配置主库的listener与tnsname


vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST = 
     (SID_DESC =
       (GLOBAL_DBNAME = PROD1_DGMGRL.example.com)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = PROD1)
     )
     (SID_DESC =
       (GLOBAL_DBNAME = PROD1.example.com)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = PROD1)
     )
  )




vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora  --添加主备的alias






PROD1=
(DESCRIPTION= 
 (ADDRESS=(PROTOCOL=tcp)(HOST=PROD1.example.com)(PORT=1521))
 (CONNECT_DATA=(SERVER=shared)(SERVICE_NAME=PROD1.example.com)))


SBDB1=
(DESCRIPTION= 
 (ADDRESS=(PROTOCOL=tcp)(HOST=SBDB1.example.com)(PORT=1521))
 (CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=SBDB1.example.com)))




2.2 增加备库的listener和tnsname
 
vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST = 
     (SID_DESC =
       (GLOBAL_DBNAME = SBDB1_DGMGRL.example.com)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = SBDB1)
     )
     (SID_DESC =
       (GLOBAL_DBNAME = SBDB1.example.com)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = SBDB1)
     )
  )




vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora  --添加主备的alias






PROD1=
(DESCRIPTION= 
 (ADDRESS=(PROTOCOL=tcp)(HOST=PROD1.example.com)(PORT=1521))
 (CONNECT_DATA=(SERVER=shared)(SERVICE_NAME=PROD1.example.com)))


SBDB1=
(DESCRIPTION= 
 (ADDRESS=(PROTOCOL=tcp)(HOST=SBDB1.example.com)(PORT=1521))
 (CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=SBDB1.example.com)))




3.配置主库的broker相关参数


3.1 配置dg_broker需要的参数


shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
alter database force logging;
alter database flashback on;
alter database add standby logfile group 10 '/u01/app/oracle/oradata/PROD1/sredo10.log' size 256m;
alter database add standby logfile group 11 '/u01/app/oracle/oradata/PROD1/sredo11.log' size 256m;
alter database add standby logfile group 12 '/u01/app/oracle/oradata/PROD1/sredo12.log' size 256m;
alter database add standby logfile group 13 '/u01/app/oracle/oradata/PROD1/sredo13.log' size 256m;
alter system set db_file_name_convert='/u01/app/oracle/oradata/SBDB1/','/u01/app/oracle/oradata/PROD1/'  scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/SBDB1/','/u01/app/oracle/oradata/PROD1/' scope=spfile;
alter system set standby_file_management=auto;
alter database open;


3.2 一致性关闭并备份主库


shutdown immediate;
startup
rman target /
backup full database plus archivelog;




4.建立备库
4.1  建立密码文件


cd $ORACLE_HOME/dbs


orapwd file=orapwSBDB1 password=oracle ignorecase=y


4.2 拷贝主库的参数文件到备库


strings spfilePROD1.ora >initSBDB1.ora
scp initSBDB1.ora charles52:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/


4.3 修改参数文件


vi initSBDB1
*.db_name='PROD1' --除了这个参数不变以外其他的都需要把PROD1改变成SBDB1
*.db_unique_name='SBDB1' --添加此行
*.db_file_name_convert='/u01/app/oracle/oradata/PROD1/','/u01/app/oracle/oradata/SBDB1/' ;  --修改
*.log_file_name_convert='/u01/app/oracle/oradata/PROD1/','/u01/app/oracle/oradata/SBDB1/' ; --修改




4.4 建立相关目录 --一定要把参数文件里用到的目录建立完


mkdir /u01/app/oracle/admin/SBDB1/adump -p
mkdir /u01/app/oracle/oradata/PROD1 -p


5.复制主数据库到备数据库


5.1 启动被数据库到nomount


export ORACLE_SID=SBDB1
sqlplus /  as sysdba
create spfile from pfile;
startup nomount;


5.2 复制主数据库到备数据库


rman target sys/oracle@prod1 auxiliary sys/oracle@sbdb1
duplicate target database for standby nofilenamecheck from active database;--复制完成后备库就已经启动到mount






6.配置dg_broker


6.1 主备库都要执行
sqlplus sys/oracle@prod1 as sysdba
alter system set dg_broker_start=true;
sqlplus sys/oracle@sbdb1 as sysdba
alter system set dg_broker_start=true;


6.2 配置configuration


dgmgrl sys/oracle@prod1
create configuration dg123 as primary database is prod1 connect identifier is prod1;
add database sbdb1 as connect identifier si sbdb1 MAINTAINED AS PHYSICAL;
enable configuration;
show configuration;--看见SUCESS，那么dg就搭建成功了


7.测试数据同步




8.切换备库模式
dgmgrl sys/oracle@prod1


convert database sbdb1 to snapshot standby;--更改快照备库模式
convert database sbdb1 to physical standby;--更改到物理备库模式


switchover to SBDB1;--切换主到SBDB1;


9.打开备库到只读模式并实现同步[ADG] （11g才有的新功能）


sqlplus sys/oracle@sbdb1 as sysdba
select open_mode from v$database;
 OPEN_MODE
 --------
 MOUNTED
 
alter database open;
 
select open_mode from v$database;
 OPEN_MODE
----------
READ ONLY
  
alter database recover managed standby database using current logfile disconnect;--报错也不管
 
select open_mode from v$database; --确认是否真的正常，看见返回如下结果即正常打开了ADG
 OPEN_MODE
--------------------
READ ONLY WITH APPLY
 
10.激活fast_failover
sqlplus sys/oracle@sbdb1 as sysdba
alter database flashback on;
sqlplus sys/oracle@prod1 as sysdba
alter database flashback on;


dbmgrl sys/oracle@prod1
enable fast_tart failover
show configuration;--看见failover enable即可，若是不肯定就做如下测试即可


start observer; --不关闭终端，另外打开dbmgrl
dbmgrl sys/oracle@prod1
shutdown abort;--关闭完成后，查看observer终端正在切换主库到sbdb1，那么failover开启成功。恢复时只需启动prod1即可




11.优化dg
rman target sys/oracle@prod1
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
rman target sys/oracle@SBDB1
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;


sqlplus sys/oracle@prod1
alter database enable block change tracking using file '/u01/app/oracle/oradata/PROD1/block.txt';
sqlplus sys/oracle@sbdb1
alter database enable block change tracking using file '/u01/app/oracle/oradata/SBDB1/block.txt';