oracle DG搭建方式两种总结

dg玩了很久了，一直没总结下，每次东拉西扯很多文档拼起来搭，比较麻烦，总结下，节省效率

一共两种方式，duplicate 和rman 备份恢复

一、前期准备

1.开启forcelogging select force_logging from v$database;

SQL> select force_logging from v$database;

SQL> alter database force logging;

2.开启归档（这个就不记说了，百度一堆）

3. vim /etc/hosts

加入备库ip 方便拷贝文件 

4.备份监听文件和tnsnames,参数文件，控制文件

cp listener.ora listener0527.bak

cd $ORACLE_HOME/network/admin ;cp tnsnames.ora tnsnames.ora.bak

create pfile='/home/oracle/pfile.txt' from spfile;

alter system backup controlfile to trace;   (select * from v$diag_info里找trace就有控制文件文本)

5. 

su - grid

配置静态监听（注意更改ip和服务名）

主库
LISTENER_DG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.**.**)(PORT = 1523))
)
)

SID_LIST_LISTENER_DG=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl2)
)
)

lsnrctl start LISTENER_DG
===================备库
LISTENER_DG=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.**.**)(PORT = 1523))
)
)

SID_LIST_LISTENER_DG=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_dg)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = orcldg1)
)
)

=====将LISTENER_DG加入 crs管理

su - grid

srvctl add listener  -l LISTENER_DG -p "TCP:1523" -o $ORACLE_HOME

srvctl start listener  -l LISTENER_DG

lsnrctl status LISTENER_DG  （查看监听状态是否有unknow状态的静态监听）

6. 添加tnsnames
###########tnsnames
orclpri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.**.**)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcldg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.**.**)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_dg)
)
)

服务名确认配置正确
tnsping orclpri
tnsping orcldg

7.  -----修改sys密码
orapwd file=$ORACLE_HOME/dbs/orapw${ORACLE_SID} password=Oracle force=y;
alter user sys identified by Oracle;
scp $ORACLE_HOME/dbs/orapw${ORACLE_SID} 10.0.**.**:$ORACLE_HOME/dbs/orapw${ORACLE_SID}
---test
sqlplus sys/Oracle@orclpri as sysdba
sqlplus sys/Oracle@orcldg as sysdba

8.  ###primary更改参数
alter system set log_archive_dest_state_2=defer;

alter system set log_archive_config='dg_config=(orcl,orcldg)';
alter system set log_archive_dest_2='service=orcldg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';
alter system set standby_file_management=auto;
alter system set fal_server=orcldg;
alter system set fal_client=orcl;
alter system set log_archive_format='%t_%s_%r.arch' scope=spfile;
----都是一样的就不用了
--alter system set db_file_name_convert='/oracle/app/oracle/oradata/ORCL11G/datafile','/oradata/orcl11gdg' scope=spfile;
--alter system set log_file_name_convert='/oracle/app/oracle/oradata/ORCL11G/onlinelog','/oradata/orcl11gdg' scope=spfile;

将之前备份的spfile copy到备库 (然后按需更改，删掉无用参数)

scp /home/oracle/pfile.txt host03:/home/oracle/pfile.txt

racdb1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
racdb2.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
racdb2.__pga_aggregate_target=339738624
racdb1.__pga_aggregate_target=436207616
racdb2.__sga_target=503316480
racdb1.__sga_target=406847488
*.archive_lag_target=1800
*.audit_file_dest='/oracle/app/oracle/admin/racdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.log_archive_dest_2='service="orcl11gdg"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="orcl11gdg" net_timeout=30','valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_state_2='ENABLE'
*.fal_client='ORCL11G'
*.fal_server='orcl11gdg'
*.standby_file_management='AUTO'
*.log_archive_config='dg_config=(orcl11g,orcl11gdg)'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_domain=''
*.db_name='racdb'
*.db_securefile='PERMITTED'
*.diagnostic_dest='/oracle/app/oracle'
racdb1.instance_number=1
racdb2.instance_number=2
*.log_archive_dest_1='location=+DATADG'
*.memory_target=839909376
*.open_cursors=300
*.optimizer_features_enable='11.2.0.4'
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
racdb1.thread=1
racdb2.thread=2
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'

9. ------备库创建目录结构（就是参数文件里audit_file_dest路径得有）

cd $ORACLE_BASE

mkdir adump

10.  启动到 nomount 

sqlplus / as sysdba

startup nomount

上边准备工作都一样，下面两种方法视情况使用

（duplicate方式）

11. 在standby节点、主库也可以，确保tnsnames连接对  

rman target sys/Oracle@orclpri auxiliary sys/Oracle@orcldg

duplicate target database for standby from active database nofilenamecheck

没报错等待即可，可以从每次copy完成一个数据文件时间和大小估算总时长需要多久

完成后数据库就copy完了

12. 启用实时同步

sqlplus / as sysdba

select open_mode from v$database;

alter database recover managed standby database disconnect;

alter database open;

alter database recover managed standby database disconnect using current logfile disconnect from session parallel 8;

 select open_mode from v$database;

13. 检查内存参数等

配置归档清理脚本

检查同步状态

更改参数文件位置 create spfile='+DATA' from pfile='/home/oracle/pfile.txt';

（rman 方式）

 第10步完成后，备库启动到nomount后

1. 主库备份（注意备份存放位置和空间）