dg玩了很久了，欧博abg一直没总结下，每次东拉西扯很多文档拼起来搭，比较麻烦，总结下，节省效率
一共两种方式，欧博官网duplicate 和rman 备份恢复
一、前期准备
1.开启forcelogging select force_logging from v$database;
SQL> select force_logging from v$database;
SQL> alter database force logging;
2.开启归档
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
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 database backup controlfile to trace; (select * from v$diag_info里找trace就有控制文件文本)
5.
su - grid
配置静态监听（注意更改ip和服务名）两个节点都要加，注意修改SID_NAME
主库
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) --oracle_home
(SID_NAME = orcl2)
)
)
注意rac的监听一定要用grid启动，单实例使用oracle启动 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=enable;
alter system set log_archive_config='dg_config=(orcl,orcldg)';----db_unique_name
alter system set log_archive_dest_2='service=orcldg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';---service是连备库连接串tnsname名
alter system set standby_file_management=auto;
alter system set fal_server=orcldg;---备库连接串tnsname名
alter system set fal_client=orcl;---主库tnsnames名
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到备库 (然后按需更改，删掉无用参数,需要注意是否是rac-rac的dg，参数文件里部分参数需要更改，不然起不来第二个节点)
scp /home/oracle/pfile.txt host03:/home/oracle/pfile.txt
-----rac参数模板，单实例要按需去除thread等
*.aq_tm_processes=0
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_create_file_dest=''
*.db_domain=''
*.db_files=2000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldgXDB)'
*.event=''
orcl1.instance_number=1
orcl2.instance_number=2
*.job_queue_processes=0
*.db_create_online_log_dest_1=''
*.db_create_online_log_dest_2=''
*.open_cursors=300
*.processes=2000
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
orcl2.thread=2
orcl1.thread=1
*.audit_file_dest='/u01/app/oracle/adump'
orcl2.undo_tablespace='UNDOTBS2'
orcl1.undo_tablespace='UNDOtBS1'
*.db_name='orcl'
*.db_unique_name='orcldg'
*.fal_client='ORCLDG'
*.fal_server='ORCLTEST'
*.pga_aggregate_target=40G
*.sga_target=200G
*.sga_max_size=200G
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=+ARCHDG'
*.log_archive_dest_2='service=orcltest lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.arch'
*.log_file_name_convert='+DATA/orcl/onlinelog/','+DATADG/orcldg/onlinelog/'
*.db_file_name_convert='+DATA/orcl/datafile/','+DATADG/orcldg/datafile/','+DATA/','+DATADG/'---源库多个路径需要提前创建到asm里
*.control_files='+DATADG/orcldg/controlfile/control01.ctl','+ARCHDG/orcldg/controlfile/control02.ctl'---路径也需要提前创建到asmcmd里
9. ------备库创建目录结构（就是参数文件里audit_file_dest路径得有）
cd $ORACLE_BASE
mkdir adump
10. 启动到 nomount
sqlplus / as sysdba
startup nomount pfile='/home/oracle/pfile.txt'
上边准备工作都一样，下面两种方法视情况使用
（duplicate方式）
11. 在standby节点、主库也可以，确保tnsnames连接对
测试链接
rman target sys/Oracle@orclpri auxiliary sys/Oracle@orcldg
用脚本调用
vi dg.sh
#!/bin/bash
export ORACLE_SID=orcl1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib
rman target sys/oracle@orcltest auxiliary sys/oracle@orcldg<<EOF >> /home/oracle/dg.log
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate channel prmy5 type disk;
allocate auxiliary channel stby1 type disk;
allocate auxiliary channel stby2 type disk;
allocate auxiliary channel stby3 type disk;
allocate auxiliary channel stby4 type disk;
allocate auxiliary channel stby5 type disk;
set db_file_name_convert='+DATA/orcl/datafile/','+DATADG/orcldg/datafile/','+DATA/','+DATADG/';
duplicate target database for standby from active database nofilenamecheck;
}
EOF
sh dg.sh &后台执行 监控dg.log就行
没报错等待即可，可以从每次copy完成一个数据文件时间和大小估算总时长需要多久
完成后数据库就copy完了
添加standby logfile---要更改位置
alter database add standby logfile thread 1
group 20 '+DATA' size 200M,
group 21 '+DATA' size 200M,
group 22 '+DATA' size 200M,
group 23 '+DATA' size 200M,
group 24 '+DATA' size 200M;
alter database add standby logfile thread 2
group 25 '+DATA' size 200M,
group 26 '+DATA' size 200M,
group 27 '+DATA' size 200M,
group 28 '+DATA' size 200M,
group 29 '+DATA' size 200M;
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 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. 主库备份（注意备份存放位置和空间）
###primary
rman target / <<EOF
run{allocate channel c1 type disk;
allocate channel c2 type disk;
backup full database format '/oradata/backup/full_%U.bak' plus archivelog;
sql 'alter system switch logfile';
backup current controlfile for standby format '/oradata/backup/c_%U.bak';
release channel c1;
release channel c2;
}
EOF
2. 将备份好的文件scp到备库
3. 还原standby controlfile
sqlplus / as sysdba
修改控制文件位置
alter system set control_files='/oradata/orcl11ggj/cntrlorcl11ggj.dbf' scope=spfile;
还原
restore standby controlfile from '/oradata/backup/c_16v13hnh_1_1.bak';
4. 还原数据库
restore database;
如果数据文件路径变了的话就需要 set newname再还原
sql 'alter database mount standby database';
run
{set newname for datafile 1 to '/oradata/orcl11ggj/system01.dbf';
set newname for datafile 2 to '/oradata/orcl11ggj/sysaux01.dbf';
set newname for datafile 3 to '/oradata/orcl11ggj/undotbs1.dbf';
set newname for datafile 4 to '/oradata/orcl11ggj/user01.dbf';
set newname for datafile 5 to '/oradata/orcl11ggj/example01.dbf';
set newname for datafile 6 to '/oradata/orcl11ggj/tbs2.dbf';
set newname for datafile 7 to '/oradata/orcl11ggj/tbs3.dbf';
set newname for datafile 8 to '/oradata/orcl11ggj/ogg.dbf';
set newname for tempfile 1 to '/oradata/orcl11ggj/temp01.dbf';
set newname for tempfile 2 to '/oradata/orcl11ggj/temp02.dbf';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}
5. 备库添加standby logfile
alter database add standby logfile thread 1
group 5 '+DATA' size 50M,
group 6 '+DATA' size 50M,
group 7 '+DATA' size 50M;
alter database add standby logfile thread 2
group 8 '+DATA' size 50M,
group 9 '+DATA' size 50M,
group 10 '+DATA' size 50M;
启用实时同步
sqlplus / as sysdba
select open_mode from v$database;
alter database recover managed standby database disconnect using current logfile disconnect from session parallel 8;
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;
===========检查dg同步状态脚本
col dest_name for a30
select t.*,arched-applied gap,sysdate etime from (select thread#,max(sequence#) arched, max(decode(applied,'YES',sequence#,1)) applied, max(decode(DELETED,'YES',sequence#,1)) DELETED from v$archived_log group by thread#) t;
select name,value,unit,time_computed from v$dataguard_stats;
select process,pid,status,thread#,sequence#,delay_mins from v$managed_standby;
select to_char(start_time,'yyyymmdd hh24:mi') start_time,type,item,units,total, to_char(timestamp,'yyyymmdd hh24:mi') timestap from v$recovery_progress;
select inst_id,dest_id,dest_name,status,type,recovery_mode, error from GV$ARCHIVE_DEST_STATUS where DESTINATION is not null;
