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.开启归档

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; 

 
posted @   悠游~~~  阅读(3396)  评论(0)  编辑  收藏  举报
编辑推荐：
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· 没有源码，如何修改代码逻辑？
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
历史上的今天：
2020-05-28 inset插入时间不一致问题记录
点击右上角即可分享
微信分享提示