暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片

通过NBU备份搭建oracle 19c ADG (RAC-RAC)

原创 张海 云和恩墨 2022-07-13
4572

适用范围
linux7,oracle19c,NetBackup8,搭建RAC-RAC环境ADG

问题概述
通过NBU的备份搭建ADG

问题原因
在运数据库需要搭建ADG,且均有NBU实时备份,为了最大程度减少主库的操作以及节约时间,提升效率的同时降低风险,故在此记录通过NBU的备份来搭建ADG过程

解决方案
ADG数据库相关环境的准备在此不再赘述,主备库均为两节点RAC+ASM,搭建过程如下:

1、NBU客户端检查
检查客户端安装及NBU服务启动情况
[root@racdg01 bin]# /usr/openv/netbackup/bin/bpps 
root      28947      1  1 14:42 ?        00:00:00 /usr/openv/netbackup/bin/vnetd -proxy inbound_proxy -number 0
root      28948      1  2 14:42 ?        00:00:00 /usr/openv/netbackup/bin/vnetd -proxy outbound_proxy -number 0
root      29004      1  0 14:42 ?        00:00:00 /usr/openv/netbackup/bin/vnetd -standalone
root      29010      1  1 14:42 ?        00:00:00 /usr/openv/netbackup/bin/bpcd -standalone
root      29190      1  0 14:42 ?        00:00:00 /usr/openv/netbackup/bin/bpclntcmd -crl_download
root      29195      1  7 14:42 ?        00:00:00 /usr/openv/netbackup/bin/nbdisco
root      29263      1  1 14:42 ?        00:00:00 /usr/openv/netbackup/bin/bmrbd

启动/usr/openv/netbackup/bin/bp.start_all
停止/usr/openv/netbackup/bin/bp.kill_all

2、主、备库配置ADG静态监听
配置主备库静态监听(静态监听配置在grid下面$ORACLE_HOME/network/admin/listener.ora)
备节点1
LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.13)(PORT = 1521)))
ADR_BASE_LISTENER_DG = /u01/db/oracle
SID_LIST_LISTENER_DG =
(SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = testdbdg)
    (SID_NAME = testdbdg1)
    (ORACLE_HOME = /u01/db/oracle/product/19.3.0/dbhome_1)
  )
)

备节点2
LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.14)(PORT = 1521)))
ADR_BASE_LISTENER_DG = /u01/db/oracle
SID_LIST_LISTENER_DG =
(SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = testdbdg)
    (SID_NAME = testdbdg2)
    (ORACLE_HOME = /u01/db/oracle/product/19.3.0/dbhome_1)
  )
)

主节点1
LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.11)(PORT = 1521)))
ADR_BASE_LISTENER_DG = /u01/db/oracle
SID_LIST_LISTENER_DG =
(SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = testdb)
    (SID_NAME = testdb1)
    (ORACLE_HOME = /u01/db/oracle/product/19.3.0/dbhome_1)
  )
)

主节点2
LISTENER_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.12)(PORT = 1521)))
ADR_BASE_LISTENER_DG = /u01/db/oracle
SID_LIST_LISTENER_DG =
(SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = testdb)
    (SID_NAME = testdb2)
    (ORACLE_HOME = /u01/db/oracle/product/19.3.0/dbhome_1)
  )
)

#启动静态监听
su - grid
lsnrctl start LISTENER_DG

3、主、备库配置连接串tnsnames.ora
echo
"TESTDB_PRM_DG =
  (DESCRIPTION =
    (FAILOVER = ON)
    (LOAD_BALANCE = YES)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.11)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )

TESTDB_STB_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.13)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.14)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdbdg)
    )
  )">>$ORACLE_HOME/network/admin/tnsnames.ora
cat $ORACLE_HOME/network/admin/tnsnames.ora

#主备库tnsping互相测试
su - oracle
tnsping TESTDB_PRM_DG
tnsping TESTDB_STB_DG

4、复制密码文件创建目录
主库1节点:
SQL> alter user sys identified by "oracle";
pwcopy +data/testdb/PASSWORD/pwdtestdb.256.1107705063 /tmp/orapwtestdb1
scp /tmp/orapwtestdb1 oracle@10.10.10.13:$ORACLE_HOME/dbs/
备库1节点:
ASM中创建共享目录
su - grid
asmcmd
mkdir -p +data/testdbdg/PASSWORD
备库复制
pwcopy /u01/db/oracle/product/19.3.0/dbhome_1/dbs/orapwtestdb1 +data/testdbdg/PASSWORD/

5、备库所有节点创建adump目录:
su - oracle
mkdir -p /u01/db/oracle/admin/testdbdg/adump

6、修改备库参数文件(/home/oracle/inittestdbdg1.ora)
*.audit_file_dest='/u01/db/oracle/admin/testdbdg/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='19.0.0'
*.control_files='+DATA'#Set by RMAN
*.db_file_name_convert='+DATA/testdb','+DATA/testdbdg'
*.log_file_name_convert='+DATA/testdb','+DATA/testdbdg'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='testdb'
*.db_unique_name='testdbdg'
*.fal_client='TESTDB_STB_DG'
*.fal_server='TESTDB_PRM_DG'
testdb2.instance_number=2
testdb1.instance_number=1
*.log_archive_config='dg_config=(testdb,testdbdg)'
*.log_archive_dest_1='location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=testdbdg'
*.log_archive_dest_2='service=TESTDB_PRM_DG async lgwr valid_for=(online_logfile,primary_role) db_unique_name=testdb'
*.log_archive_dest_state_2='ENABLE'
*.open_cursors=1500
*.pga_aggregate_target=3000m
*.sga_target=8000m
*.standby_file_management='AUTO'
testdb2.thread=2
testdb1.thread=1
testdb2.undo_tablespace='UNDOTBS2'
testdb1.undo_tablespace='UNDOTBS1'

#数据库启动到nomount
SQL> startup nomount pfile='/home/oracle/inittestdbdg1.ora';

7、主、备库策略配置:
#检查归档删除策略
su - oracle
rman target /
show all;
configure archivelog deletion policy to applied on all standby;


8、连通性测试
在Adg环境中,所有实例执行下面操作,保证都是可以正确连接的。
sqlplus sys/oracle@TESTDB_PRM_DG as sysdba
sqlplus sys/oracle@TESTDB_STB_DG as sysdba

9、主库备份standby controlfile
alter database create standby controlfile as '/tmp/ctrl_202207.ctl';
scp /tmp/ctrl_202207.ctl oracle@10.10.10.13:/home/backup/

9、目标端恢复standby controlfile
修改备库两节点oracle文件权限
重启实例后修改
su - grid
/u01/db/grid/19.3.0/bin/setasmgidwrap -o /u01/db/oracle/product/19.3.0/dbhome_1/bin/oracle
su - oracle
rman target /
restore controlfile from '/home/oracle/ctrl_202207.ctl';
alter database mount standby database;

10、通过NBU备份Rman整库恢复
#恢复数据库
oracle@racdbdg01 ~]$ cat /home/oracle/rman_restore202207.sh 
rman target / msglog=//home/oracle/rman_restore202207.log << EOF
 run {
 allocate channel c1 type 'sbt_tape';
 send 'NB_ORA_CLIENT=racdb01-dca';
 allocate channel c2 type 'sbt_tape';
 send 'NB_ORA_CLIENT=racdb01-dca';
 allocate channel c3 type 'sbt_tape';
 send 'NB_ORA_CLIENT=racdb01-dca';
 allocate channel c4 type 'sbt_tape';
 send 'NB_ORA_CLIENT=racdb01-dca';
 set newname for database to '+data/testdbdg/DATAFILE/%b';
 restore database;
 switch datafile all;
 recover database;
 release channel c1;
 release channel c2;
 release channel c3;
 release channel c4;
}
exit;
EOF
#执行恢复库脚本
nohup sh /home/oracle/rman_restore202207.sh &
--需修改以上脚本中参数:
主库NBU连接串为racdb01-dca 
备库共享目录为+data/testdbdg/DATAFILE/%b

10、主库增加至备库的归档目录及创建standby logfile
alter system set log_archive_config='dg_config=(testdb,testdbdg)' sid='*';
alter system set log_archive_dest_2='service="TESTDB_STB_DG", LGWR ASYNC NOAFFIRM compression=enable db_unique_name="testdbdg" valid_for=(all_logfiles,primary_role)' sid='*';
alter system set log_archive_dest_state_2=enable;
alter system set fal_server=TESTDB_STB_DG;
alter system set fal_client=TESTDB_PRM_DG;
alter system set db_file_name_convert='+DATA/testdbdg','+DATA/testdb' scope=spfile;
alter system set log_file_name_convert='+DATA/testdbdg','+DATA/testdb' scope=spfile;

sqlplus / as sysdba
alter database add standby logfile thread 1 group 9 ('+DATA') size 500M;
alter database add standby logfile thread 1 group 10 ('+DATA') size 500M;
alter database add standby logfile thread 1 group 11 ('+DATA') size 500M;
alter database add standby logfile thread 1 group 12 ('+DATA') size 500M;
alter database add standby logfile thread 1 group 13 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 14 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 15 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 16 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 17 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 18 ('+DATA') size 500M;

11、查看备库GAP情况
select * from gv$archive_gap;
--如无gap,则启动MRP
alter database recover managed standby database using current logfile disconnect; 
--如存在GAP,则通过NBU恢复缺失归档

12、恢复缺失归档
在备库查询控制文件中的备份信息
rman target /

run {
 allocate channel c1 type 'sbt_tape';
 send 'NB_ORA_CLIENT=racdb01-dca';
 allocate channel c2 type 'sbt_tape';
 send 'NB_ORA_CLIENT=racdb01-dca';
 allocate channel c3 type 'sbt_tape';
 send 'NB_ORA_CLIENT=racdb01-dca';
 allocate channel c4 type 'sbt_tape';
 send 'NB_ORA_CLIENT=racdb01-dca';
 restore database  preview;
 release channel c1;
 release channel c2;
 release channel c3;
 release channel c4;
}
如无备份的al归档信息,手动catalog归档备份集信息
检查备份信息
/usr/openv/netbackup/bin/bplist -C racdb01-dca -t 4 -R -l / |more
catalog归档备份集信息(catalog控制文件恢复时间点后的al文件)

run {
CONFIGURE  CHANNEL  device  TYPE 'SBT_TAPE' PARMS'ENV=(NB_ORA_SERV=nbusvr,NB_ORA_CLIENT=racdb01-dca)';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece 'al_42_1_1108637731';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_41_1_1108637731';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_35_1_1108630546';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_34_1_1108630546';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_28_1_1108623333';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_27_1_1108623333';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_21_1_1108616133';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_20_1_1108616133';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_14_1_1108576277';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_12_1_1108576262';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_11_1_1108576262';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_1_1_1108576072';
CATALOG DEVICE TYPE 'SBT_TAPE' backuppiece'al_2_1_1108576072';
}

恢复所缺归档日志
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE'  parms='ENV=(NB_ORA_SERV=nbusvr,NB_ORA_CLIENT=racdb01-dca)';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE'  parms='ENV=(NB_ORA_SERV=nbusvr,NB_ORA_CLIENT=racdb01-dca)';
set archivelog destination to '+ARCH';
restore archivelog from logseq 17 thread 1;
restore archivelog from logseq 9 thread 2;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}

13、备库添加standby(redo同大小,组数+1),启动MRP,验证同步情况
sqlplus / as sysdba
alter database add standby logfile thread 1 group 9 ('+DATA') size 500M;
alter database add standby logfile thread 1 group 10 ('+DATA') size 500M;
alter database add standby logfile thread 1 group 11 ('+DATA') size 500M;
alter database add standby logfile thread 1 group 12 ('+DATA') size 500M;
alter database add standby logfile thread 1 group 13 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 14 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 15 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 16 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 17 ('+DATA') size 500M;
alter database add standby logfile thread 2 group 18 ('+DATA') size 500M;

alter database recover managed standby database using current logfile disconnect; 

14、修改pfile为spfile,将spfile由本地迁移至ASM
create spfile='+DATA' from pfile='/home/oracle/inittestdbdg1.ora';
ASM中检查参数文件名称+data/testdbdg/PARAMETERFILE/spfile.271.1070639177

修改spfile
cd $ORACLE_HOME/dbs/
echo "
SPFILE='+data/testdbdg/PARAMETERFILE/spfile.290.1108910633'
"> inittestdbdg1.ora

15、集群添加数据库实例
su - oracle
srvctl add database -d testdbdg -o /u01/db/oracle/product/19.3.0/dbhome_1  -p +DATA/testdbdg/PARAMETERFILE/spfile.290.1108910633
srvctl add instance -db testdbdg -instance testdb1 -node racdg01
srvctl add instance -db testdbdg -instance testdb2 -node racdg02
su - grid
crsctl stat res -t
srvctl stop database -d testdbdg
srvctl start database -d testdbdg

su - oracle
srvctl modify database -db testdbdg -pwfile +data/testdbdg/PASSWORD/orapwtestdbdg1

16、启动MRP,检查同步情况
alter database recover managed standby database using current logfile disconnect; 
--查看同步状态
set line 999
select inst_id,thread#,process,pid,status,client_process,client_pid,sequence#,block#,active_agents,known_agents from gv$managed_standby;
--同步检查
set echo off
set lines 300 pages 50
set heading on
set verify off
col name for a30
col value for a30
col TIME_COMPUTED for a20
col datum_time for a20 heading 'LAST_RECEIVED_TIME'
col inst_id for 99 heading 'ID'
break on inst_id
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select inst_id,name,value,time_computed,DATUM_TIME,sysdate from gv$dataguard_stats order by inst_id;  

17、主、备配置归档删除脚本crontab
最后修改时间:2022-07-13 17:08:03
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论