oracle 利用dg搭建dg

1、dg搭建背景

**传统搭建方式duplicate和rman、冷备搭建，已经不适用于大库或者并发高的场景了；

**在近期我遇到一个用户，主服务器和网络环境都不太稳定的情况，为了不影响主库，利用原来的备库再搭建一套备库的方式。

2、dg搭建过程

2.1 第三台dg库安装操作系统

**跳过此步骤

2.2 第三台dg库安装oracle软件和补丁

**跳过此步骤

2.3 查看目前1+1主备参数

col name for a30
col value for a60set line 200 pages 200select name,value from v$parameter where  lower(name) 
in ('log_archive_format','log_archive_dest_1','db_unique_name','log_archive_config','log_archive_dest_2','db_file_name_convert','log_file_name_convert','orcldg_file_management','fal_client','fal_server','log_archive_dest_state_1','log_archive_dest_state_2','redo_transport_user','service_names');

2.4 停掉第一个备库

sqlplus / as sysdba
SQL> create pfile='/tmp/1.txt' from spfile;
shutdown immediateexit

2.5 从第一个备库拷贝数据到第二个备库

[oracle@hisdg62 oradata]$ cd /u01/app/oracle/oradata
[oracle@hisdg62 oradata]$ scp -r orcldg/ 192.168.0.146:/u01/app/oracle/oradata

2.6 上面拷贝执行完成后，就可以着手修改和增加第二个备库的spfile、密码文件、tnsnames.ora文件、listener.ora文件

**spfile调整关键内容
*.db_unique_name='orcldg3'
*.fal_server='orcldg','orcldg2'
*.log_archive_config='DG_CONFIG=(orcldg2,orcldg,orcldg3)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arc/'
*.log_archive_dest_2='SERVICE=orcldg2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2'
*.log_archive_dest_3='SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg'
*.log_file_name_convert='/u01/app/oracle/oradata/orcldg2/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg/',,'/u01/app/oracle/oradata/orcldg/'
*.db_file_name_convert='/u01/app/oracle/oradata/orcldg2/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg/',,'/u01/app/oracle/oradata/orcldg/'
*.service_names='orcl'



**密码文件准备，从现有1+1随便一个服务器复制到新备库
[oracle@hisdg62 dbs]$ scp orapworcl 192.168.0.146:/u01/app/oracle/product/11.2.0/db_1/dbs



**增加和修改所有节点tnsnames.ora
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.62)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.177)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.146)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )



** listener.ora文件修改
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )
lsnrctl stop 
lsnrctl start

2.7 从第一个备库拷贝数据到第二个备库完成后，修改之前1+1主备两个节点的参数

**主库
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg2/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg/' scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg2/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg/' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(orcldg2,orcldg,orcldg3)';
alter system set fal_server='orcldg','orcldg3';
alter system set log_archive_dest_3='SERVICE=orcldg3 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg3';



**备库
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcldg2/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg/' scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcldg2/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/oradata/orcldg/' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(orcldg2,orcldg,orcldg3)';
alter system set fal_server='orcldg2','orcldg3';
alter system set log_archive_dest_3='SERVICE=orcldg3 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg3';

2.8 启动第一个备库和第二个备库

**第一个主和备依次执行如下命令即可。startup nomount;
alter database mount standby database;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;

3、总结

** 从上面可以看到，整个搭建过程都是一样的，很简单，只需要把备库停掉，然后冷备数据到对应的节点即可。

** 如果是sync affim的方式同步，建议先把修改为async，或者关闭对应的日志传输进程，然后调整到最大性能，防止影响业务。