11g ADG搭建手册

最新推荐文章于 2023-12-11 14:50:03 发布
最新推荐文章于 2023-12-11 14:50:03 发布
阅读量922 收藏 5
点赞数 2
分类专栏： ORACLE 文章标签： adg duplicate adg搭建手册
无版权，随便玩儿
本文链接：https://blog.csdn.net/qq_40687433/article/details/85625266
版权

环境：11.2.0.4  ，主库双节点rac+asm，备库单节点文件系统

数据恢复方式：duplicate或者rman

 

备库先搭好环境，不用建库。

尽量保持主备库psu版本一致，

（以上步骤略）

准备阶段

1.ntp时间同步
#date
#设置主备库的ntp时间同步
client:
server xx.xx.xx.147  prefer
driftfile /var/lib/ntp/drift
broadcastdelay 0.008

server:
server xx.xx.xx.0
fudge xx.xx.xx.0 stratum 11
driftfile /var/lib/ntp/drift
broadcatdelay 0.008

#手动同步
ntpdate   xx.xx.xx.147 

 

2.归档和force logging

#主库开启归档和force logging
#archive
archive log list
#force logging
select name,log_mode,FORCE_LOGGING from v$database;
alter database force logging;

3.standby log

#standby logs要比redo logs多一组
 select INST_ID,group#,thread#,bytes/1024/1024 mb,members,status from  gv$log order by 3,2;
 select INST_ID,group#,thread#,bytes/1024/1024 mb,status from gv$standby_log order by 3,2;
 
alter database add standby logfile thread 1 group 13 '+DATA' size 2048m;
alter database add standby logfile thread 1 group 14 '+DATA' size 2048m;
alter database add standby logfile thread 1 group 15 '+DATA' size 2048m;
alter database add standby logfile thread 1 group 16 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 17 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 18 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 19 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 20 '+DATA' size 2048m;
alter database add standby logfile thread 1 group 21 '+DATA' size 2048m;
alter database add standby logfile thread 2 group 22 '+DATA' size 2048m;


4.主库参数设置
alter system set db_block_checking=FULL scope=both sid='*';
alter system set db_block_checksum=FULL scope=both sid='*';
alter system set db_lost_write_protect=TYPICAL scope=both sid='*';

alter system set log_archive_config='DG_CONFIG=(lzldb,lzldbadg)' scope=both sid='*';
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lzldb' scope=both sid='*';
alter system set log_archive_dest_2='SERVICE=lzldbadg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lzldbadg' scope=both sid='*';

alter system set fal_server=lzldbadg scope=both sid='*';
alter system set fal_client=lzldb scope=both sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';

5.备库的静态监听

#备库listener.ora
#必须是静态监听, 如果pfile启动时有service_name，那么会注册blocked类型的动态监听
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = lzldbadg)
      (ORACLE_HOME = /u01/oracle/app/product/11.2.0/dbhome_1)
      (SID_NAME = lzldb)
    )
  )
ADR_BASE_LISTENER = /u01/oracle/ap
lsnrctl stop
lsnrctl start

6.主备库的tns
#tnsnames.ora
#因为做了备库地址转换，主备tns略有不同
lzldb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.151 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = lzldb)
    )
)

lzldbadg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.16 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = lzldbadg)
    )
)
#stddb
lzldb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.151 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = lzldb)
    )
)

lzldbadg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.16 )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = lzldbadg)
    )
)

7.密码文件和pfile

#orapwfile，主库最好也scp一下，保持多个节点的密码文件一致
scp $ORACLE_HOME/dbs/orapwlzldb1 xxx.xxx.xx.16:/home/oracle/orapwlzldb
cp ~/orapwlzldb $ORACLE_HOME/dbs

#pfile pwdfile

create pfile='/home/oracle/enmo/pfile.ora' from spfile;
#scp /home/oracle/enmo/pfile.ora xxx.xxx.xx.16:/home/oracle/pfile.ora
#备库打开拷贝过来的pfile，直接进行修改
#备库环境与主库不同，无grid，内存较小,base和home不同
lzldb.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/oracle/app/admin/lzldb/adump'
*.audit_trail='NONE'
*.control_files='/u01/oracle/app/oradata/lzldb/control01.ctl'  --standby controlfile的位置
*.db_create_file_dest='/u01/oracle/app/oradata/lzldb'
*.db_create_online_log_dest_1='/u01/oracle/app/oradata/lzldb'
*.db_name='lzldb'
*.db_unique_name='lzldbadg'
*.db_recovery_file_dest='/u01/oracle/fast_recovery'
*.db_recovery_file_dest_size=536870912000
*.diagnostic_dest='/u01/oracle/app'
*.fal_client='lzldbadg'  --备库的fal参数与主库相反
*.fal_server='lzldb'
*.instance_number=1  --备库实例个数可能与主库不一样
*.job_queue_processes=300
*.log_archive_config='DG_CONFIG=(lzldb,lzldbadg)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lzldbadg'
*.log_archive_dest_2='SERVICE=lzldb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lzldb'
*.db_file_name_convert='+DATA','/u01/oracle/app/oradata/lzldb','+DATA/lzldb/datafile','/u01/oracle/app/oradata/lzldb','+DATA/lzldb/tempfile','/u01/oracle/app/oradata/lzldb'
*.log_file_name_convert='+DATA/lzldb/onlinelog','/u01/oracle/app/oradata/lzldb'
*.open_cursors=2000  --备库的配置可能比主库低，所以内存等参数需要调整
*.pga_aggregate_target=10995367936
*.session_cached_cursors=400
*.sessions=3000
*.sga_max_size=27514634240
*.sga_target=27514634240
*.standby_file_management='AUTO'
*.thread=1
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

8.创建目录

#mkdir
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{adump,dpdump,pfile}
mkdir -p /u01/oracle/app/admin/lzldb/adump
mkdir -p /u01/oracle/app/admin/lzldb/dpdump
mkdir -p /u01/oracle/app/admin/lzldb/pfile
mkdir -p /u01/oracle/app/oradata/lzldb

9.启动备库到nomount

#startup nomount
SQL> startup nomount pfile='/home/oracle/pfile.ora'
SQL> create spfile from pfile='/home/oracle/pfile.ora';
SQL> !ls $ORACLE_HOME/dbs/spfile*
/u01/oracle/app/product/11.2.0/dbhome_1/dbs/spfilelzldb.ora

10.standby controlfile

#control file
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/control01.ctl'
scp /home/oracle/control01.ctl xxx.xxx.xx.16:/u01/oracle/app/oradata/lzldb/control01.ctl

 

数据恢复阶段

11.拉数据

#2个办法，一个是duplicate一个数据库，还有就是rman备份恢复
#duplicate
#建议放后台执行
script /dev/null 
screen -S rman_dup
screen -ls 
screen -d -r rman_dup
#duplicate
rman target sys/Oracle1234@lzldb auxiliary  sys/Oracle1234@lzldbadg nocatalog
run{
    allocate channel c1 device type disk;
    allocate channel c2 device type disk;
    allocate channel c3 device type disk;
    allocate channel c4 device type disk;
    allocate auxiliary channel aux1 device type disk;
    allocate auxiliary channel aux2 device type disk;
    allocate auxiliary channel aux3 device type disk;
    allocate auxiliary channel aux4 device type disk;
    SET NEWNAME FOR DATABASE TO new; 
    duplicate target database for standby from active database nofilenamecheck;
}

=========================================================================

#rman备份恢复

#准备nfs

vi /etc/exports
service nfs restart
mount /dev/mapper/data1p3 /xdump
showmount -e xxx.xxx.xx.148
mount -t nfs -o nolock xxx.xxx.xx.148:/xdump /xdump
chown oracle:oinstall /xdump
     
rman target /

#放后台执行
run {
#Hot database whole backup
    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;
    configure controlfile autobackup off;
     Allocate channel c1 type disk;
       Allocate channel c2 type disk;
       Allocate channel c3 type disk;
       Allocate channel c4 type disk;
       Allocate channel c5 type disk;
       Allocate channel c6 type disk;
       Allocate channel c7 type disk;
       Allocate channel c8 type disk;
######backup database
      Backup as compressed backupset database format '/xdump/db_cp_%U';
  #delete obsolete;
######backup all archive logs
sql 'alter system archive log current';
backup as compressed backupset
  skip inaccessible
  filesperset 20
  format '/xdump/arclogback_%s_%p_%t_%d'
  archivelog all delete input;
  #delete obsolete;
######backup controlfile
backup current controlfile format  '/xdump/control_bak_%T';
     Release channel c1;
       Release channel c2;
       Release channel c3;
       Release channel c4;
       Release channel c5;
       Release channel c6;
       Release channel c7;
       Release channel c8;
}

#restore
#如果cp了standby controlfile就不需要restore 了
restore standby controlfile from '/xdump/control_bak_20181225';
RMAN> alter database mount;

catalog start with '/xdump/';
list backup of datafile 1;

#配置channel
run{
     Allocate channel c1 type disk;
       Allocate channel c2 type disk;
       Allocate channel c3 type disk;
       Allocate channel c4 type disk;
       Allocate channel c5 type disk;
       Allocate channel c6 type disk;
       Allocate channel c7 type disk;
       Allocate channel c8 type disk;
restore database;
     Release channel c1;
       Release channel c2;
       Release channel c3;
       Release channel c4;
       Release channel c5;
       Release channel c6;
       Release channel c7;
       Release channel c8;
}


#检查rman恢复日志，可能会遇到某些数据文件没有恢复的情况，可以使用下面的命令指定恢复数据文件
run{
     Allocate channel c1 type disk;
       Allocate channel c2 type disk;
       Allocate channel c3 type disk;
       Allocate channel c4 type disk;
       Allocate channel c5 type disk;
       Allocate channel c6 type disk;
       Allocate channel c7 type disk;
       Allocate channel c8 type disk;
restore datafile 117;
restore datafile 116;
restore datafile 112;
restore datafile 108;
restore datafile 104;
restore datafile 102;
restore datafile 101;
restore datafile 88 ;
restore datafile 85 ;
restore datafile 84 ;
restore datafile 83 ;
restore datafile 77 ;
restore datafile 76 ;
restore datafile 74 ;
restore datafile 71 ;
restore datafile 70 ;
restore datafile 67 ;
restore datafile 66 ;
restore datafile 63 ;
restore datafile 61 ;
restore datafile 55 ;
restore datafile 45 ;
restore datafile 44 ;
restore datafile 43 ;
restore datafile 42 ;
restore datafile 41 ;
restore datafile 40 ;
restore datafile 39 ;
restore datafile 38 ;
restore datafile 37 ;
restore datafile 35 ;
restore datafile 32 ;
restore datafile 28 ;
restore datafile 16 ;
restore datafile 9     ;
restore datafile 5     ;
     Release channel c1;
       Release channel c2;
       Release channel c3;
       Release channel c4;
       Release channel c5;
       Release channel c6;
       Release channel c7;
       Release channel c8;
}     

#也可以指定数据文件的名字进行恢复
#restore datafile '/u01/oracle/app/oradata/lzldb/lzldb/datafile/users_default.366.995324027';
#restore datafile '/u01/oracle/app/oradata/lzldb/lzldb/datafile/sysaux.257.994159463';
#restore datafile '/u01/oracle/app/oradata/lzldb/lzldb/datafile/system.256.994159463';


#recover
#rman不会备份redo，使用 using current logfile自动创建redo
#dg redo不可create redo手动创建，使用clear logfile可以直接创建logfile

 

recover和检查dg问题阶段

12.recover

alter database recover managed standby database using current logfile disconnect from session;
#or
alter database recover managed standby database disconnect;
#停日志应用alter database recover managed standby database cancel;

#如果仍有redo没有创建，需手动创建redo
alter database clear logfile group 13;
alter database clear logfile group 14;
alter database clear logfile group 15;
alter database clear logfile group 16;
alter database clear logfile group 21;


--查看归档应用情况，检查是否有gap,如有gap需要基于scn号做增量（主库也没有归档的情况下），或者追归档
col name for a100
select sequence#,applied,NAME from v$archived_log order by sequence#;  
select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024 mb ,status from  v$standby_log;
#检查
#检查进程情况
select process,status,client_process,thread#,sequence# from v$managed_standby;
--主库，查看archive dest是否有报错
 SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST ;
#手动全库归档
alter system archive log current;


 #检查log dest是否有报错，2个节点都检查
 select dest_id,error from v$archive_dest
#检查日志间隔
select thread#,low_sequence#,high_sequence# from v$archive_gap;

col name for a25
 col value for a20
col unit for a30
 set lines 120
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

#查看dg模式
select protection_mode,protection_level from v$database;

 

13.read only
# alter database flashback on;
 alter database open read only; 
 
 
 
 
 问题一：修复gap
 #这里遇到了主库归档丢失的情况，只能通过基于scn号增量备份的方式恢复dg
 #http://blog.itpub.net/9240380/viewspace-1424363/
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
 #std
 SQL> SELECT CURRENT_SCN FROM V$DATABASE;

  CURRENT_SCN
-------------
  16170506639
  
#pri
mkdir -p /xdump/incre

rman target /
run
{
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
BACKUP INCREMENTAL FROM SCN 16170506639 database format '/xdump/incre/forstandby_%d_%T_%s_%p.bkp';
release channel d1;
 release channel d2;
release channel d3;
 release channel d4;
}

#pri 
 alter database create standby controlfile as '/xdump/incre/standby.ctl';
 
#std
#chown -R oracle:oinstall /xdump/incre
startup nomount

#restore
rman target /
restore standby controlfile  from '/xdump/incre/standby.ctl';

 alter database mount;
 
 #recover
 catalog start with '/xdump/incre' noprompt;
 
 run
 {
 allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
 recover database noredo;
 release channel d1;
 release channel d2;
release channel d3;
 release channel d4;
}

#dg
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
 
 
 

 
 
 
 

liuzhilongDBA
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
专栏目录
oracle 11g ADG实施手册（亲测，已成功部署多次）
12-13
oracle 11g ADG实施手册（亲测，已成功部署多次）；根据文档步骤实施可以确保成功
Oracle12c+adg搭建完美手册
09-18
Oracle12c+adg搭建完美手册
ADG指导手册.docx
08-20
笔者在网上见过很多adg的搭建手册，但是终会遇到坑，经过实战经验测试总结，编写一个adg的指导手册，也是最近我在做机房搬迁中总结的经验
（绝对干货）Oracle ADG数据库完整部署、切换、备份、运维手册
09-11
阿里云上部署Oracle ADG，并且带有节点故障自动切换功能。同时，也包括数据库异常故障的处理，比如死锁、数据误删除进行恢复
rac+单实例adg搭建步骤
10-20
在Linux环境下，如何实现RAC+单实例adg的搭建步骤详情
ADG主库 FAILED DESTINATION
喝醉酒的小白
01-27 3613
ADG切换
Data Guard查看和检查
weixin_39568073的博客
09-30 847
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS, DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS, LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$d..
ADG搭建时归档dest报错ORA-01033: ORACLE initialization or shutdown in progress
liuzhilong的博客
06-11 1743
我有2个备库，docare5pri和docare5adg 其中2节点的实例都没有传输日志成功，1节点都没有问题 SQL> SELECT inst_id,DESTINATION, STATUS, ERROR FROM gV$ARCHIVE_DEST ; INST_ID DESTINATION STATUS ERROR ---------- ----------------------------------...
Oracle11g ADG部署 duplicate方式（超级详细）
weixin_52444902的博客
09-14 3156
oracle11g版本ADG详细部署
oracle ADG平台搭建
最新发布
2301_78992416的博客
12-11 1513
搭建一套oracle 19C ADG 容灾环境，操作系统oracle linux7.6. oracle软件版本19.3.0.0。
ADG902规格手册.pdf
12-22
ADG902/901规格手册
构建最高可用Oracle数据库系统 RAC和ADG环境搭建文档
01-14
构建最高可用Oracle数据库系统 环境搭建文档，包括RAC、dataguard、broker
基于linux5.6的Oracle11gr2的rac安装+ADG配置
11-07
在vmware上安装11g的rac+adg，为自己搭建实验环境
数据库监控SQL
青不青空间
11-24 594
<br />查看长时间会话<br />select SID, SERIAL#, (select P.SPID from GV$SESSION S join GV$PROCESS P on P.ADDR = S.PADDR and P.INST_ID = S.INST_ID where S.TYPE != 'BACKGROUND' and S
ORA-16191: Primary log shipping client not logged on standby
weixin_40913898的博客
11-28 846
ORA-16191: Primary log shipping client not logged on standby This error usually is because of password file mismatch: someone updated in primary and forgot to pass across to standby nodes, missed a node… but what if password file in primary and standby is
Oracle 11g Dataguard 配置，维护与详解 (ADG)
weixin_30631587的博客
02-23 633
一、前言： 本手册主要记录如何配置，还介绍了配置原因，以及注意要点，已经主备切换，以及故障转移等重要操作步骤，我希望这个文章可以作为进行dataguard配置的一个参考手册。 二、前提 1.主库是归档模式： 如果我们不清楚为什么是归档模式，那我们就应该也不会清楚dataguard是用来做什么的。透过很多修饰的官方语言，我们需要明确DG（dataguard简...
Oracle 11g ADG 搭建
djwnb666的博客
11-21 1041
Oracle的ADG搭建，11g的，希望可以帮到你，很多照片传不上来不知道为什么，见谅
ADG一些参数
喝醉酒的小白
11-24 2911
log_archive_dest_n : 设置主库归档日志路径以及redo日志传输 fal_server : 定备库的FAL (Fetch Archive Log) Server名称，该参数值为DG环境中发送日志端的网络服务名，方便FAL Client接受 fal_cilent : DG环境当前节点的网络服务名 ，用于接受日志或处理归档日志的GAP FAL : 创建物理备库或逻辑备库过程中，自动获取到在备份过程中产生的归档日志并传送到备库 standby_archive_dest (已经被弃用) ....
ADG搭建
喝醉酒的小白
07-16 2384
1 查看主库状态 select database_role, protection_mode from v$database; select force_logging from v$database; select group#, bytes/1024/1024 from v$log; 2 备库-配置静态监听 vi listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl19c)
oracle 11g adg实施手册(亲测,已成功部署多次)
07-15
Oracle 11g ADG（Active Data Guard）实施手册是一份操作指南，旨在帮助用户成功部署和配置Oracle 11g ADG。该手册经过亲身实测，并已成功部署多次，可以提供可靠的实施建议和步骤，确保用户获得良好的使用体验。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

liuzhilongDBA

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值