【干货攻略】基于NBU备份软件进行Oracle数据库异机恢复

关键字1：restore database、recover database

关键字2：switch datafile all

关键字3：NBU、ORA-19554、ORA-27211

当数据库服务器宕机损坏且不能正常提供服务的情况下，通过常规的技术手段难以完成排除数据库宕机故障。因此需要通过备份进行异机恢复数据库。本次采用异机恢复的模式，将RAC集群的备份恢复到单机环境，不对现有业务产生任何影响。以下步骤描述如何使用NBU备份恢复数据库的过程。

二、数据库恢复步骤

1、恢复环境准备

（1）找一台服务器（主机名为：YWZD-DB）安装同版本的操作系统和数据库软件，此处不讨论安装步骤；

（2）安装好NBU备份代理软件，此处不讨论安装步骤；

2、在YWZD-DB数据库服务器上执行以下操作，获取控制文件备份集。

cd u01/app/oracle/product/12.1.0.2/dbhome_1/dbs
vi initywzd.ora
*.audit_file_dest='/u01/app/oracle/admin/ywzd/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.db_block_size=8192
*.control_files='/u01/app/oracle/oradata/ywzd/current.ctl'
*.db_create_file_dest='/u01/app/oracle/oradata/ywzd'
*.db_domain=''
*.db_name='ywzd'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ywzdXDB)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/ywzd'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=8192m
*.processes=600
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'

rman targer /
run
{
allocate channel ch00 type 'SBT_TAPE';
send 'nb_ora_serv=NBU_MASTER服务器主机名';
send 'nb_ora_client=NBU客户端主机名';
restore controlfile from '/ctrl_s27488_p1_t1162988767' ;
release channel ch00;
}

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** +ywzd/ywzd/DATAFILE/system.261.950009243
2 0 SYSAUX *** +ywzd/ywzd/DATAFILE/sysaux.262.950009245
3 0 UNDOTBS1 *** +ywzd/ywzd/DATAFILE/undotbs1.263.950009247
4 0 UNDOTBS2 *** +ywzd/ywzd/DATAFILE/undotbs2.265.950009253
5 0 USERS *** +ywzd/ywzd/DATAFILE/users.266.950009253

col sql for a200
set linesize 1000
set pagesize 900
select 'set newname for datafile '||FILE#||' to '||'''/u01/app/oracle/oradata/ywzd/datafile/'||substr( name,INSTR(name, '/', -1)+1)||'.dbf'';' sql from v$datafile;

RUN {
allocate channel ch00 type 'sbt_tape';
allocate channel ch01 type 'sbt_tape';
allocate channel ch02 type 'sbt_tape';
allocate channel ch03 type 'sbt_tape';
allocate channel ch04 type 'sbt_tape';
send 'nb_ora_serv=NBU_MASTER服务器主机名';
send 'nb_ora_client=NBU客户端主机名';
set until time "to_date('2024-03-06 20:00:00','yyyy-mm-dd hh24:mi:ss')";
set newname for datafile 1 to '/u01/app/oracle/oradata/ywzd/datafile/system.261.950009243.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/ywzd/datafile/sysaux.262.950009245.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/ywzd/datafile/undotbs1.263.950009247.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/ywzd/datafile/undotbs2.265.950009253.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/ywzd/datafile/users.266.950009253.dbf';
set newname for datafile 6 to '/u01/app/oracle/oradata/ywzd/datafile/tseveflow_data.271.950012331.dbf';
set newname for datafile 7 to '/u01/app/oracle/oradata/ywzd/datafile/tseveflow_data2.272.950012331.dbf';
set newname for datafile 8 to '/u01/app/oracle/oradata/ywzd/datafile/tseveflow_data3.273.950012331.dbf';
restore database;
switch datafile all;
recover database;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
}

set linesize 1000
select file#,to_char(checkpoint_change#),to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') from v$datafile;
FILE# TO_CHAR(CHECKPOINT_CHANGE#) TO_CHAR(CHECKPOINT_
---------- ---------------------------------------- -------------------
1 275724628 2024-03-06 23:04:39
2 275724628 2024-03-06 23:04:39
3 275724628 2024-03-06 23:04:39
4 275724628 2024-03-06 23:04:39
5 275724628 2024-03-06 23:04:39
6 275724628 2024-03-06 23:04:39
7 275724628 2024-03-06 23:04:39
8 275724628 2024-03-06 23:04:39

select file#,to_char(checkpoint_change#),to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss') from v$datafile_header;
FILE# TO_CHAR(CHECKPOINT_CHANGE#) TO_CHAR(CHECKPOINT_
---------- ---------------------------------------- -------------------
1 275724628 2024-03-06 23:04:39
2 275724628 2024-03-06 23:04:39
3 275724628 2024-03-06 23:04:39
4 275724628 2024-03-06 23:04:39
5 275724628 2024-03-06 23:04:39
6 275724628 2024-03-06 23:04:39
7 275724628 2024-03-06 23:04:39
8 275724628 2024-03-06 23:04:39

rman target /
list INCARNATION;
using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ywzd 699325977 CURRENT 1 2017-07-22 11:27:21

alter database disable block change tracking;

14、打开数据库

alter database open resetlogs;

15、数据库正常打开，恢复完成

select status from v$instance;

三、恢复后环境清理

1、删除参数文件信息
ps -ef|grep ywzd
cd /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs
rm hc_ywzd.dat
rm initywzd.ora
rm spfileywzd.ora


2、删除数据文件信息
cd /u01/app/oracle/oradata/ywzd
rm *.dbf


3、删除审计文件信息
cd /u01/app/oracle/admin/ywzd/adump
rm *.aud

四、恢复场景拓展

RUN {
allocate channel ch00 type 'sbt_tape';
send 'nb_ora_serv=NBU_MASTER服务器主机名';
send 'nb_ora_client=NBU客户端主机名';
set until time "to_date('2024-02-25 15:00:00','yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
RELEASE CHANNEL ch00;
}

RUN {
allocate channel ch01 type 'sbt_tape';
send 'nb_ora_serv=NBU_MASTER服务器主机名';
send 'nb_ora_client=NBU客户端主机名';
set archivelog destination to '/home/oracle/arch';
restore archivelog from sequence 1152 thread 2;
restore archivelog from logseq 15143 until logseq 15146 thread 2;
restore archivelog time between "to_date('20220103 00','yyyymmdd hh24')" and "to_date('20220106 23','yyyymmdd hh24')";
restore archivelog all;
RELEASE CHANNEL ch01;
}

若NBU恢复控制文件出现以下错误，可以采取2种方法解决。

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch00 channel at 01/18/2022 23:58:26
ORA-19554: error allocating device, device type: SBT_TAPE, device name: 
ORA-27211: Failed to load Media Management Library
Additional information: 2


方法一：通过oracle_link加载Library库文件
$/usr/openv/netbackup/bin/oracle_link 
Fri Mar  8 01:01:09 CST 2024
All Oracle instances should be shutdown before running this script.
Please log into the Unix system as the Oracle owner for running this script
Do you want to continue? (y/n) [n] y
LIBOBK path: /usr/openv/netbackup/bin
ORACLE_HOME: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle version: 12.1.0.2.0
Platform type: x86_64
Linking LIBOBK:
Moving 64-bit libobk.so to libobk.so.back
ln -s /usr/openv/netbackup/bin/libobk.so64 /u01/app/oracle/product/12.1.0.2/dbhome_1/lib/libobk.so
Done
Please check the trace file located in /tmp/make_trace.26440 
to make sure the linking process was successful.


方法二：在rman恢复命令中设置PARAMS参数
修改前：allocate channel ch00 type 'SBT_TAPE';
修改后：allocate channel ch00 type 'sbt_tape' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';

以上就是本期关于“基于NBU备份软件进行Oracle数据库异机恢复”的应急恢复方法。希望能给大家带来帮助。

欢迎关注“数据库运维之道”公众号，一起学习数据库技术。

欢迎加我的微信，拉你进数据库微信群。

推荐阅读

【干货攻略】Oracle报错篇 ORA-01378 磁盘扇区大小不兼容问题

【干货攻略】基于NBU备份软件进行Oracle数据库异机恢复

评论