【Oracle数据库重定向恢复】Oracle 12.2.0.1备份恢复测试

目录

​​实验环境​​

​​创建Oracle主目录结构​​

​​准备参数文件​​

​​确认备份信息​​

​​恢复控制文件​​

​​恢复数据文件​​

​​还原归档日志文件​​

​​重定向与清理日志组​​

​​重建临时表空间​​

​​重建控制文件​​

​​开启数据库保护​​

---

实验环境

操作系统：SUSE Linux Enterprise Server 11 & 12 & 15

数据库版本：Oracle 12.2.0.1

数据库实例编号SID：T73

数据库用户：orat73

数据库根目录：/oracle/T73

本文默认已安装Oracle 12.2.0.1（采用SAP标准安装方式），并使用NBU（NetBackup）备份恢复的方式进行测试，原数据库的实例编号SID是P73。

创建Oracle主目录结构

Oracle目录结构的创建要参考原Oracle数据库的目录结构，否则用备份恢复会失败。

切换到orat73用户和Oracle数据库根目录/oracle/T73：

su - orat73
cd /oracle/T73
ls -l

 创建符合SAP标准的Oracle主目录（除Oracle软件安装后自动创建的目录之外）：

mkdir admin audit mirrlogA mirrlogB oraarch oraflash orawallet origlogA origlogB saparch sapbackup sapcheck sapdata1 sapdata2 sapdata7 sapreorg saptrace

 创建目录/oracle/T73/origlogA下的子目录：

cd /oracle/T73/origlogA
mkdir cntrl

创建目录/oracle/T73/origlogB下的子目录：

cd /oracle/T73/origlogB
mkdir cntrl

 

创建目录/oracle/T73/sapdata1下的子目录：

cd /oracle/T73/sapdata1
mkdir cntrl sysaux_1 system_1 temp_{1,2} undo_1

创建目录/oracle/T73/sapdata2下的子目录：

cd /oracle/T73/sapdata2
ls -l
mkdir sr3_{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25} sr3usr_1

 创建目录/oracle/T73/sapdata7下的子目录（SAP标准目录应该是/oracle/T73/sapdata3和/oracle/T73/sapdata4，本处由于历史遗留问题使用/oracle/T73/sapdata7）：

cd /oracle/T73/sapdata7
mkdir sr3701i_{1,2,3,4} sr3701x_{1,2,3,4,5,6,7,8}

 创建目录/oracle/T73/saptrace下的子目录：

cd /oracle/T73/saptrace
mkdir audit background diag usertrace

准备参数文件

拷贝原Oracle数据库P73的参数文件initP73.ora文件到目录/oracle/T73/12201/dbs下，并创建新的参数文件initT73.ora：

cp -pr initP73.ora initT73.ora
chown orat73:dba initT73.ora

更改参数文件：/oracle/T73/12201/dbs/initT73.ora中的'P73'改成'T73'（除db_name之外）；删除无效参数*.local_listener。

cat initT73.ora

vi initT73.ora
cat initT73.ora

cat initT73.ora|grep P73

查看Oracle监听运行情况：

su - orat73
lsnrctl status

创建参数文件spfile：

sqlplus / as sysdba
SQL > create spfile from pfile;

参考参数文件spfile：

cd /oracle/T73/12201/dbs

确认备份信息

查看NBU配置信息：

cat /usr/openv/netbackup/bp.conf

 查看NBU中原数据库P73的备份信息（SAPPRD是原数据库P73所在服务器）：

/usr/openv/netbackup/bin/bplist -C SAPPRD -t 4 -R -l / | more

选择要恢复的控制文件/ctrl_dP73_up40p6b75_s417572_p1_t1100164325

恢复控制文件

将Oracle数据库启动到nomount状态：

sqlplus / as sysdba
SQL> startup nomount;

用rman恢复控制文件（NB_ORA_SERV的值是NBU服务端主机名NETBACKUPAP01，NB_ORA_CLIENT是原数据库P73所在服务器SAPPRD）：

su - orat73
rman target /
RMAN>
run{
allocate channel c1 type sbt;
send 'NB_ORA_SERV=NETBACKUPAP01,NB_ORA_CLIENT=SAPPRD';
restore controlfile from '/ctrl_dP73_up40p6b75_s417572_p1_t1100164325';
release channel c1;}

RMAN恢复控制文件出现报错：

ORA-19554: error allocating device, device type: SBT_TAPE, device name:

ORA-27211: Failed to load Media Management Library

该错误的解决方法如下：

su - orat73
cd /usr/openv/netbackup/bin
./oracle_link

问题修复后，再次使用rman恢复控制文件（NB_ORA_SERV的值是NBU服务端主机名NETBACKUPAP01，NB_ORA_CLIENT是原数据库P73所在服务器SAPPRD）：

su - orat73
rman target /
RMAN>
run{
allocate channel c1 type sbt;
send 'NB_ORA_SERV=NETBACKUPAP01,NB_ORA_CLIENT=SAPPRD';
restore controlfile from '/ctrl_dP73_up40p6b75_s417572_p1_t1100164325';
release channel c1;}

控制文件恢复完成后将数据库启动到mount状态：

alter database mount;

恢复数据文件

通过RMAN恢复数据文件。由于数据库实例ID由P73变更未T73，需要将数据文件的存放目录进行调整（NB_ORA_SERV的值是NBU服务端主机名NETBACKUPAP01，NB_ORA_CLIENT是原数据库P73所在服务器SAPPRD）：

run{
allocate channel c1 type sbt;
allocate channel c2 type sbt;
allocate channel c3 type sbt;
allocate channel c4 type sbt;
allocate channel c5 type sbt;
send 'NB_ORA_SERV=NETBACKUPAP01,NB_ORA_CLIENT=SAPPRD,NB_ORA_PC_STREAMS=5';
set newname for datafile 1 to "/oracle/T73/sapdata1/system_1/system.data1";
set newname for datafile 2 to "/oracle/T73/sapdata1/sysaux_1/sysaux.data1";
set newname for datafile 3 to "/oracle/T73/sapdata1/undo_1/undo.data1";
set newname for datafile 4 to "/oracle/T73/sapdata2/sr3_1/sr3.data1";
set newname for datafile 5 to "/oracle/T73/sapdata2/sr3_2/sr3.data2";
set newname for datafile 6 to "/oracle/T73/sapdata2/sr3_3/sr3.data3";
set newname for datafile 7 to "/oracle/T73/sapdata2/sr3_4/sr3.data4";
set newname for datafile 8 to "/oracle/T73/sapdata2/sr3_5/sr3.data5";
set newname for datafile 9 to "/oracle/T73/sapdata2/sr3_6/sr3.data6";
set newname for datafile 10 to "/oracle/T73/sapdata2/sr3_7/sr3.data7";
set newname for datafile 11 to "/oracle/T73/sapdata2/sr3_8/sr3.data8";
set newname for datafile 12 to "/oracle/T73/sapdata2/sr3_9/sr3.data9";
set newname for datafile 13 to "/oracle/T73/sapdata2/sr3_10/sr3.data10";
set newname for datafile 14 to "/oracle/T73/sapdata2/sr3_11/sr3.data11";
set newname for datafile 15 to "/oracle/T73/sapdata2/sr3_12/sr3.data12";
set newname for datafile 16 to "/oracle/T73/sapdata7/sr3701i_1/sr3701i.data1";
set newname for datafile 17 to "/oracle/T73/sapdata7/sr3701i_2/sr3701i.data2";
set newname for datafile 18 to "/oracle/T73/sapdata7/sr3701i_3/sr3701i.data3";
set newname for datafile 19 to "/oracle/T73/sapdata7/sr3701i_4/sr3701i.data4";
set newname for datafile 20 to "/oracle/T73/sapdata7/sr3701x_1/sr3701x.data1";
set newname for datafile 21 to "/oracle/T73/sapdata7/sr3701x_2/sr3701x.data2";
set newname for datafile 22 to "/oracle/T73/sapdata7/sr3701x_3/sr3701x.data3";
set newname for datafile 23 to "/oracle/T73/sapdata7/sr3701x_4/sr3701x.data4";
set newname for datafile 24 to "/oracle/T73/sapdata7/sr3701x_5/sr3701x.data5";
set newname for datafile 25 to "/oracle/T73/sapdata7/sr3701x_6/sr3701x.data6";
set newname for datafile 26 to "/oracle/T73/sapdata7/sr3701x_7/sr3701x.data7";
set newname for datafile 27 to "/oracle/T73/sapdata7/sr3701x_8/sr3701x.data8";
set newname for datafile 28 to "/oracle/T73/sapdata2/sr3usr_1/sr3usr.data1";
set newname for datafile 29 to "/oracle/T73/sapdata2/sr3_13/sr3.data13";
set newname for datafile 30 to "/oracle/T73/sapdata2/sr3_14/sr3.data14";
set newname for datafile 31 to "/oracle/T73/sapdata2/sr3_15/sr3.data15";
set newname for datafile 32 to "/oracle/T73/sapdata2/sr3_16/sr3.data16";
set newname for datafile 33 to "/oracle/T73/sapdata2/sr3_17/sr3.data17";
set newname for datafile 34 to "/oracle/T73/sapdata2/sr3_18/sr3.data18";
set newname for datafile 35 to "/oracle/T73/sapdata2/sr3_19/sr3.data19";
set newname for datafile 36 to "/oracle/T73/sapdata2/sr3_20/sr3.data20";
set newname for datafile 37 to "/oracle/T73/sapdata2/sr3_21/sr3.data21";
set newname for datafile 38 to "/oracle/T73/sapdata2/sr3_22/sr3.data22";
set newname for datafile 39 to "/oracle/T73/sapdata2/sr3_23/sr3.data23";
set newname for datafile 40 to "/oracle/T73/sapdata2/sr3_24/sr3.data24";
set newname for datafile 41 to "/oracle/T73/sapdata2/sr3_25/sr3.data25";
set newname for datafile 42 to "/oracle/T73/sapdata2/sr3_26/sr3.data26";
set newname for datafile 43 to "/oracle/T73/sapdata2/sr3_27/sr3.data27";
set newname for datafile 44 to "/oracle/T73/sapdata2/sr3_28/sr3.data28";
set newname for datafile 45 to "/oracle/T73/sapdata2/sr3_29/sr3.data29";
set newname for datafile 46 to "/oracle/T73/sapdata2/sr3_30/sr3.data30";
set newname for datafile 47 to "/oracle/T73/sapdata2/sr3_31/sr3.data31";
set newname for datafile 48 to "/oracle/T73/sapdata2/sr3_32/sr3.data32";
set newname for datafile 49 to "/oracle/T73/sapdata2/sr3_33/sr3.data33";
restore database;
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}

还原归档日志文件

通过NBU将数据库还原到2022年3月24日09:00:00（NB_ORA_SERV的值是NBU服务端主机名NETBACKUPAP01，NB_ORA_CLIENT是原数据库P73所在服务器SAPPRD）：

run{
allocate channel c1 type sbt;
allocate channel c2 type sbt;
allocate channel c3 type sbt;
allocate channel c4 type sbt;
send 'NB_ORA_SERV=NETBACKUPAP01,NB_ORA_CLIENT=SAPPRD,NB_ORA_PC_STREAMS=4';
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time = '2022-03-24 09:00:00';
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;}

 

重定向与清理日志组

将数据库切换到open状态：

alter database open resetlogs;

出现报错：

ORA-00344: unable to re-create online log '/oracle/P73/origlogA/log_g11m1.dbf'

ORA-27040: file create error, unable to create file

处理方法是重定向日志组文件：

set linesize 1000;
select GROUP#,MEMBER,STATUS from v$logfile;

alter database rename file '/oracle/P73/origlogA/log_g11m1.dbf' to '/oracle/T73/origlogA/log_g11m1.dbf';
alter database rename file '/oracle/P73/mirrlogA/log_g11m2.dbf' to '/oracle/T73/mirrlogA/log_g11m2.dbf';
alter database rename file '/oracle/P73/origlogB/log_g12m1.dbf' to '/oracle/T73/origlogB/log_g12m1.dbf';
alter database rename file '/oracle/P73/mirrlogB/log_g12m2.dbf' to '/oracle/T73/mirrlogB/log_g12m2.dbf';
alter database rename file '/oracle/P73/origlogA/log_g13m1.dbf' to '/oracle/T73/origlogA/log_g13m1.dbf';
alter database rename file '/oracle/P73/mirrlogA/log_g13m2.dbf' to '/oracle/T73/mirrlogA/log_g13m2.dbf';
alter database rename file '/oracle/P73/origlogB/log_g14m1.dbf' to '/oracle/T73/origlogB/log_g14m1.dbf';
alter database rename file '/oracle/P73/mirrlogB/log_g14m2.dbf' to '/oracle/T73/mirrlogB/log_g14m2.dbf';

select GROUP#,MEMBER,STATUS from v$logfile;

再次尝试将数据库切换到open状态：

alter database open resetlogs;

出现报错：

ORA-00392: log 2 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 2 thread 1: '/oracle/T73/origlogB/log_g12m1.dbf'

ORA-00312: online log 2 thread 1: '/oracle/T73/mirrlogB/log_g12m2.dbf'

处理方法是清理日志组：

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;

再次尝试将数据库切换到open状态并查看数据库状态：

alter database open resetlogs;
select name,open_mode from v$database;

重建临时表空间

查看临时表空间数据文件：

col NAME format a50;
select file#, ts#, name, status from v$tempfile;

select tablespace_name, file_name from dba_temp_files;

出现报错：

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '/oracle/P73/sapdata1/temp_1/temp.data1'

处理方法是重建临时表空间数据文件：

alter database tempfile '/oracle/P73/sapdata1/temp_1/temp.data1' drop;
alter database tempfile '/oracle/P73/sapdata1/temp_2/temp.data2' drop;
alter tablespace PSAPTEMP add tempfile '/oracle/T73/sapdata1/temp_1/temp.data1' size 20g;
alter tablespace PSAPTEMP add tempfile '/oracle/T73/sapdata1/temp_2/temp.data2' size 20g;

查看临时表空间数据文件：

select tablespace_name, file_name from dba_temp_files;
select file#, ts#, name, status from v$tempfile;

重建控制文件

生成控制文件脚本：

alter database backup controlfile to trace;

查看控制文件脚本：

cd /oracle/T73/saptrace/diag/rdbms/p73/T73/trace
tail -10 alert_T73.log

tail -300 /oracle/T73/saptrace/diag/rdbms/p73/T73/trace/T73_ora_50228.trc

关闭数据库：

sqlplus / as sysdba
shutdown immediate;

修改参数文件/oracle/T73/12201/dbs/initT73.ora，将*.db_name='P73'改成*.db_name='T73'：

cd /oracle/T73/12201/dbs
ls -l

vi initT73.ora
cat initT73.ora

cat initT73.ora|grep P73
cat initT73.ora|grep T73

 重命名控制文件：

cd /oracle/T73/sapdata1/cntrl
mv cntrlT73.dbf cntrlT73.dbf.bak.20220324
ls -l

cd /oracle/T73/origlogB/cntrl
mv cntrlT73.dbf cntrlT73.dbf.bak.20220324
ls -l

cd /oracle/T73/origlogA/cntrl
mv cntrlT73.dbf cntrlT73.dbf.bak.20220324
ls -l

创建参数文件spfile：

su - orat73
sqlplus / as sysdba
create spfile from pfile;

 重建参数文件（脚本中的语句是CREATE CONTROLFILE REUSE DATABASE "P73" RESETLOGS ARCHIVELOG，需要修改为CREATE CONTROLFILE SET DATABASE "T73" RESETLOGS ARCHIVELOG）：

--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "T73" RESETLOGS ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 1000
    MAXINSTANCES 50
    MAXLOGHISTORY 18688
LOGFILE
  GROUP 1 (
    '/oracle/T73/origlogA/log_g11m1.dbf',
    '/oracle/T73/mirrlogA/log_g11m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '/oracle/T73/origlogB/log_g12m1.dbf',
    '/oracle/T73/mirrlogB/log_g12m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '/oracle/T73/origlogA/log_g13m1.dbf',
    '/oracle/T73/mirrlogA/log_g13m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 4 (
    '/oracle/T73/origlogB/log_g14m1.dbf',
    '/oracle/T73/mirrlogB/log_g14m2.dbf'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/T73/sapdata1/system_1/system.data1',
  '/oracle/T73/sapdata1/sysaux_1/sysaux.data1',
  '/oracle/T73/sapdata1/undo_1/undo.data1',
  '/oracle/T73/sapdata2/sr3_1/sr3.data1',
  '/oracle/T73/sapdata2/sr3_2/sr3.data2',
  '/oracle/T73/sapdata2/sr3_3/sr3.data3',
  '/oracle/T73/sapdata2/sr3_4/sr3.data4',
  '/oracle/T73/sapdata2/sr3_5/sr3.data5',
  '/oracle/T73/sapdata2/sr3_6/sr3.data6',
  '/oracle/T73/sapdata2/sr3_7/sr3.data7',
  '/oracle/T73/sapdata2/sr3_8/sr3.data8',
  '/oracle/T73/sapdata2/sr3_9/sr3.data9',
  '/oracle/T73/sapdata2/sr3_10/sr3.data10',
  '/oracle/T73/sapdata2/sr3_11/sr3.data11',
  '/oracle/T73/sapdata2/sr3_12/sr3.data12',
  '/oracle/T73/sapdata7/sr3701i_1/sr3701i.data1',
  '/oracle/T73/sapdata7/sr3701i_2/sr3701i.data2',
  '/oracle/T73/sapdata7/sr3701i_3/sr3701i.data3',
  '/oracle/T73/sapdata7/sr3701i_4/sr3701i.data4',
  '/oracle/T73/sapdata7/sr3701x_1/sr3701x.data1',
  '/oracle/T73/sapdata7/sr3701x_2/sr3701x.data2',
  '/oracle/T73/sapdata7/sr3701x_3/sr3701x.data3',
  '/oracle/T73/sapdata7/sr3701x_4/sr3701x.data4',
  '/oracle/T73/sapdata7/sr3701x_5/sr3701x.data5',
  '/oracle/T73/sapdata7/sr3701x_6/sr3701x.data6',
  '/oracle/T73/sapdata7/sr3701x_7/sr3701x.data7',
  '/oracle/T73/sapdata7/sr3701x_8/sr3701x.data8',
  '/oracle/T73/sapdata2/sr3usr_1/sr3usr.data1',
  '/oracle/T73/sapdata2/sr3_13/sr3.data13',
  '/oracle/T73/sapdata2/sr3_14/sr3.data14',
  '/oracle/T73/sapdata2/sr3_15/sr3.data15',
  '/oracle/T73/sapdata2/sr3_16/sr3.data16',
  '/oracle/T73/sapdata2/sr3_17/sr3.data17',
  '/oracle/T73/sapdata2/sr3_18/sr3.data18',
  '/oracle/T73/sapdata2/sr3_19/sr3.data19',
  '/oracle/T73/sapdata2/sr3_20/sr3.data20',
  '/oracle/T73/sapdata2/sr3_21/sr3.data21',
  '/oracle/T73/sapdata2/sr3_22/sr3.data22',
  '/oracle/T73/sapdata2/sr3_23/sr3.data23',
  '/oracle/T73/sapdata2/sr3_24/sr3.data24',
  '/oracle/T73/sapdata2/sr3_25/sr3.data25',
  '/oracle/T73/sapdata2/sr3_26/sr3.data26',
  '/oracle/T73/sapdata2/sr3_27/sr3.data27',
  '/oracle/T73/sapdata2/sr3_28/sr3.data28',
  '/oracle/T73/sapdata2/sr3_29/sr3.data29',
  '/oracle/T73/sapdata2/sr3_30/sr3.data30',
  '/oracle/T73/sapdata2/sr3_31/sr3.data31',
  '/oracle/T73/sapdata2/sr3_32/sr3.data32',
  '/oracle/T73/sapdata2/sr3_33/sr3.data33'
CHARACTER SET UTF8
;

执行恢复数据库命令：

-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/oracle/T73/oraarch/T73arch1_1_1026013502.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/oracle/T73/oraarch/T73arch1_1_1100187090.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

 用RESETLOGS的模式将数据库修改为open状态：

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

出现报错：

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oracle/T73/sapdata1/system_1/system.data1'

处理方法是还原数据库归档日志并再尝试用RESETLOGS的模式将数据库修改为open状态：

recover database using backup controlfile until cancel;
alter database open resetlogs;

添加临时表空间数据文件：

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/T73/sapdata1/temp_1/temp.data1'
     SIZE 20480M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/T73/sapdata1/temp_2/temp.data2'
     SIZE 20480M REUSE AUTOEXTEND OFF;

查询数据库状态：

-- End of tempfile additions.
--
SQL> select name,open_mode from v$database;

开启数据库保护

数据库命令行下修改表rfcdes，禁用RFC连接，修改所有使用IP地址连接的RFC Destination，让连接地址变得无效：在所有的“H=”后面加入字符串“.copy”

update sapsr3.rfcdes set rfcoptions = 'H=.copy' || substr(rfcoptions,3) where substr(rfcoptions,1,2) = 'H=';

update sapsr3.rfcdes set rfcoptions = 'H=.copy' || substr(rfcoptions,3) where substr(rfcoptions,1,2) = 'G=';

commit;