最后更新时间:2018/12/29
前置条件
已准备一台安装好Centos6+oracle11gr2 软件的服务器;
只安装了 oracle 数据库软件,需要手工创建以下目录:
#环境变量
export ORACLE_SID=orcl
#由于只安装软件未创建数据库所以需创建对应文件夹
mkdir -p $ORACLE_BASE/admin/orcl/adump/
mkdir -p $ORACLE_BASE/admin/orcl/dpdump/
mkdir -p $ORACLE_BASE/admin/orcl/pfile/
mkdir -p $ORACLE_BASE/oradata/orcl/
mkdir -p $ORACLE_BASE/flash_recovery_area
mkdir -p $ORACLE_BASE/flash_recovery_area/orcl
备份集放置目录:/u02/rmans
data放置备份集文件;
init_files 放置备份集生成后,手工备份的 spfile 及 control 文件。
[oracle@localhost rmans]$ ls /u02/rmans
data expdp.sh init_files logs startrmans.sh
本次恢复主要难点:
1. 备份集的数据库版本不同,由 11.2.0.1 升级为 11.2.0.4;
2. 数据库安装路径也不一样,数据文件、日志文件、控制文件路径都重定义;
正常情况下恢复,一般是安装的数据库版本、软件安装路径、数据文件路径都一致,会少很多步骤。
恢复操作
[oracle@localhost rmans]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 19 09:28:53 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 285213576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5517312 bytes
恢复spfile: 需注意对应spfile oracle用户需要有权限访问
RMAN> restore spfile from '/u02/rmans/init_files/ORCL_spfile_1atl4iog_1_1_20181219';
Starting restore at 19-DEC-18
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u02/rmans/init_files/ORCL_spfile_1atl4iog_1_1_20181219
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 19-DEC-18
重启实例
RMAN> startup nomount force;
恢复控制文件
RMAN> restore controlfile from '/u02/rmans/init_files/ctl_file_19tl4iod_1_1_20181219';
Starting restore at 19-DEC-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oradata/orcl/control01.ctl
output file name=/u01/app/flash_recovery_area/orcl/control02.ctl
Finished restore at 19-DEC-18
挂载数据库
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 3.29G DISK 00:00:35 18-DEC-18
BP Key: 24 Status: EXPIRED Compressed: NO Tag: TAG20181218T143246
Piece Name: /home/oracle/rmans/data/fullback_ORCL_20181218_35
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/system01.dbf
2 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/sysaux01.dbf
3 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/undotbs01.dbf
4 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/users01.dbf
5 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/userdat.dbf
6 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/useridx.dbf
7 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/qcdat.dbf
8 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/qcidx.dbf
9 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/hisdat.dbf
10 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/hisidx.dbf
11 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/archdat.dbf
12 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/archidx.dbf
13 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/suitdat.dbf
14 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/suitidx.dbf
15 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/sbcdat.dbf
16 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/sbcidx.dbf
17 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/gszqdat.dbf
18 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/gszqidx.dbf
19 Full 62638762 18-DEC-18 /home/oracle/oradata/zginfo/ZG_INFO_SPACE.dbf
20 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/businessdat.dbf
21 Full 62638762 18-DEC-18 /home/oracle/oradata/orcl/businessidx.dbf
路径转换
rman备份集在新主机路径: /u02/rmans/data,备份集生成是在源主机的 /home/oracle/rmans/data ,所以需转换。
注册备份集路径
RMAN> catalog start with '/u02/rmans/data';
Starting implicit crosscheck backup at 19-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 19-DEC-18
Starting implicit crosscheck copy at 19-DEC-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-DEC-18
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u02/rmans/data
List of Files Unknown to the Database
=====================================
File Name: /u02/rmans/data/fullback_ORCL_20181219_39
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/rmans/data/fullback_ORCL_20181219_39
数据文件路径转换
run{
set newname for datafile 1 to '/u01/app/oradata/orcl/system01.dbf';
set newname for datafile 2 to '/u01/app/oradata/orcl/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oradata/orcl/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oradata/orcl/users01.dbf';
set newname for datafile 5 to '/u01/app/oradata/orcl/userdat.dbf';
set newname for datafile 6 to '/u01/app/oradata/orcl/useridx.dbf';
set newname for datafile 7 to '/u01/app/oradata/orcl/qcdat.dbf';
set newname for datafile 8 to '/u01/app/oradata/orcl/qcidx.dbf';
set newname for datafile 9 to '/u01/app/oradata/orcl/hisdat.dbf';
set newname for datafile 10 to '/u01/app/oradata/orcl/hisidx.dbf';
set newname for datafile 11 to '/u01/app/oradata/orcl/archdat.dbf';
set newname for datafile 12 to '/u01/app/oradata/orcl/archidx.dbf';
set newname for datafile 13 to '/u01/app/oradata/orcl/suitdat.dbf';
set newname for datafile 14 to '/u01/app/oradata/orcl/suitidx.dbf';
set newname for datafile 15 to '/u01/app/oradata/orcl/sbcdat.dbf';
set newname for datafile 16 to '/u01/app/oradata/orcl/sbcidx.dbf';
set newname for datafile 17 to '/u01/app/oradata/orcl/gszqdat.dbf';
set newname for datafile 18 to '/u01/app/oradata/orcl/gszqidx.dbf';
set newname for datafile 19 to '/u01/app/oradata/zginfo/ZG_INFO_SPACE.dbf';
set newname for datafile 20 to '/u01/app/oradata/orcl/businessdat.dbf';
set newname for datafile 21 to '/u01/app/oradata/orcl/businessidx.dbf';
set until scn 62754360;
restore database;
SWITCH DATAFILE ALL;
recover database;
}
....
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET until clause
Starting restore at 19-DEC-18
using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oradata/orcl/system01.dbf
skipping datafile 2; already restored to file /u01/app/oradata/orcl/sysaux01.dbf
skipping datafile 3; already restored to file /u01/app/oradata/orcl/undotbs01.dbf
skipping datafile 4; already restored to file /u01/app/oradata/orcl/users01.dbf
skipping datafile 5; already restored to file /u01/app/oradata/orcl/userdat.dbf
skipping datafile 6; already restored to file /u01/app/oradata/orcl/useridx.dbf
skipping datafile 7; already restored to file /u01/app/oradata/orcl/qcdat.dbf
skipping datafile 8; already restored to file /u01/app/oradata/orcl/qcidx.dbf
skipping datafile 9; already restored to file /u01/app/oradata/orcl/hisdat.dbf
skipping datafile 10; already restored to file /u01/app/oradata/orcl/hisidx.dbf
skipping datafile 11; already restored to file /u01/app/oradata/orcl/archdat.dbf
skipping datafile 12; already restored to file /u01/app/oradata/orcl/archidx.dbf
skipping datafile 13; already restored to file /u01/app/oradata/orcl/suitdat.dbf
skipping datafile 14; already restored to file /u01/app/oradata/orcl/suitidx.dbf
skipping datafile 15; already restored to file /u01/app/oradata/orcl/sbcdat.dbf
skipping datafile 16; already restored to file /u01/app/oradata/orcl/sbcidx.dbf
skipping datafile 17; already restored to file /u01/app/oradata/orcl/gszqdat.dbf
skipping datafile 18; already restored to file /u01/app/oradata/orcl/gszqidx.dbf
skipping datafile 19; already restored to file /u01/app/oradata/zginfo/ZG_INFO_SPACE.dbf
skipping datafile 20; already restored to file /u01/app/oradata/orcl/businessdat.dbf
skipping datafile 21; already restored to file /u01/app/oradata/orcl/businessidx.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 19-DEC-18
Starting recover at 19-DEC-18
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 19-DEC-18
日志目录转换
SQL> alter database rename file '/home/oracle/oradata/orcl/redo01.log' to '/u01/app/oradata/onlinelogs/redo01.log';
Database altered.
SQL>
SQL> alter database rename file '/home/oracle/oradata/orcl/redo02.log' to '/u01/app/oradata/onlinelogs/redo02.log';
Database altered.
SQL>
SQL> alter database rename file '/home/oracle/oradata/orcl/redo03.log' to '/u01/app/oradata/onlinelogs/redo03.log';
Database altered.
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/app/oradata/onlinelogs/redo01.log'
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 4356
Session ID: 1144 Serial number: 3
--下面尝试upgrade,因为当前基于一个11.2.0.1的备份恢复到11.2.0.4,所以需要upgrade
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 24 16:03:15 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 704643976 bytes
Database Buffers 352321536 bytes
Redo Buffers 9711616 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql; --执行升级脚本
注意
spfile 不存在
spfile 是实例启动时必要的参数文件,如果不存在,无法使用 startup nomount 启动到 nomount 状态。
如果没有spfile,需要自己编辑一个pfile,然后启动时使用:
RMAN> startup pfile='/tmp/initorcl.ora' nomount;
启动完后创建spfile
SQL> create spfile from pfile='/tmp/initorcl.ora';
File created.
control 文件trace出创建脚本
control文件不存在或者有问题时,数据库无法 mount。
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
执行完后可到系统 trace 日志目录下找到 alert_orcl.log,在里面可看到trc文件,trc文件包含了创建control文件的命令。
类似以下内容:
Backup controlfile written to trace file /u01/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4146.trc
Completed: ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Wed Dec 19 12:09:15 2018
参考文章: