Oracle10g数据库恢复（RMAN 恢复）教程

作者：袖梨 2022-06-29

Oracle10g数据库恢复（RMAN 恢复）教程

一、自动管理文件

二、使用RMAN功能，包括所有资料档案库维护和报告功能可通过使用Oracle Enterprise Manager来完成【调用RMAN恢复的脚本】

RMAN恢复：restore和recover命令

restore：是RMAN从我们备份的地方找一下备份集，然后把这些备

份集移动到现在数据文件的位置。【相当于用户管理中的copy命令】

recover：开始使用归档日志文件或者联机重做日志文件把你变化的数据恢复回来。

首先用RMAN做一下备份，备份到自己所建目录rback下；

RMAN> backup database format '/u01/rback/rm_%U' plus archivelog delete input;

测试1（通过RMAN恢复一个表空间）：

【创建一些模拟的数据，再模拟破坏数据库】

SQL>conn scott/tiger

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID

------------------------------ ------- ----------

E1                             TABLE

E22                            TABLE

USER1                          TABLE

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

删除一些信息

SQL> delete from e22;

56 rows deleted.

SQL> select * from e22;

no rows selected

【注意：在第一次做RMAN备份的时候e22中有数据，备份完成之后删除e22表里的信息】

【模拟数据文件丢失】

$cd /u01/Oracle/oradata/orcl

$rm -rf users01.dbf

【到sysdba下清理一下内存】

SQL> alter system flush buffer_cache;

SQL> alter system flush shared_pool;

【进入scott查看e22表】

SQL> select * from e22;

select * from e22

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/Oracle/oradata/orcl/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

【不只是e22，其他表都不能查，因为数据文件没了】

执行脚本

RMAN>

run{

sql"alter tablespace users offline immediate";

restore tablespace users;

recover tablespace users delete archivelog;

sql"alter tablespace users online";

}

sql statement: alter tablespace users offline immediate

Starting restore at 17-APR-14

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from

backup set

restoring datafile 00004 to

/u01/Oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: reading from backup piece

/u01/rback/rm_4op5u3sf_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/rback/rm_4op5u3sf_1_1

tag=TAG20140417T032503

channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

Finished restore at 17-APR-14

Starting recover at 17-APR-14

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

media recovery complete, elapsed time: 00:00:06

Finished recover at 17-APR-14

sql statement: alter tablespace users online

恢复完成，发现scott用户下e22表里任然没有数据

SQL> select * from e22;

no rows selected

恢复成功。

测试2 （恢复一个数据文件）

【在e22表里插入一些数据并查看】

SQL> select count(*) from e22;

COUNT(*)

----------

42

【再删除数据文件】

$cd /u01/Oracle/oradata/orcl

$ rm -rf users01.dbf

【到sysdba下清理一下内存】

SQL> alter system flush buffer_cache;

SQL> alter system flush shared_pool;

【查看数据】

SQL> select * from e22;

select * from e22

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u01/Oracle/oradata/orcl/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

【再次恢复数据】

run{

sql"alter database datafile 4 offline";

restore datafile 4;

recover datafile 4 delete archivelog;

sql"alter database datafile 4 online";

}

sql statement: alter database datafile 4 offline

Starting restore at 17-APR-14

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from

backup set

restoring datafile 00004 to

/u01/Oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: reading from backup piece

/u01/rback/rm_4op5u3sf_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/rback/rm_4op5u3sf_1_1

tag=TAG20140417T032503

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 17-APR-14

Starting recover at 17-APR-14

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

media recovery complete, elapsed time: 00:00:04

Finished recover at 17-APR-14

sql statement: alter database datafile 4 online

RMAN>

进入文件$cd /u01/Oracle/oradata/orcl查看发现有user01.dbf

再次进入scott用户下，查看

SQL> select count(*) from e22;

COUNT(*)

----------

42

发现数据恢复了。

恢复成功。

当然如果你有异地备份我们可以直接使用异地恢复来帮助我们实现了,下面就一直来看看

oracle10g数据库恢复（异地恢复）

丢失所有的数据文件、参数文件、控制文件、密码文件

打开控制文件与参数文件的自动备份
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;

做一个RMAN完全备份
RMAN>backup database format'/u01/rback/rm_22 _%U' plus archivelog delete input;

切换日志文件
SQL>alter system switch logfile；

关闭数据库
SQL> shutdown immediate;

***********
【模拟丢失所有文件】
删除所有文件
cd /u01/oracle/oradata/orcl
$rm -f *

删除所有参数文件
cd $ORACLE_HOME/dbs
$ rm -f spfileorcl.ora
$rm -f pfileorcl.ora
删除密码文件
$ rm -f orapworcl
**************

1.找任意一个参数文件启动数据库
$ /u01/oracle/admin/orcl/pfile

2.启动到mount状态前设置一下环境变量
$ export ORACLE_SID=orcl

3.启动到nomount【注意用init.ora启动】
SQL> startup nomount pfile='/u01/oracle/admin/orcl/pfile/init.ora';

4.还原参数文件。
RMAN> restore spfile from autobackup;

Starting restore at 20-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /u01/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/oracle/flash_recovery_area/ORCL/autobackup/2014_03_20/o1_mf_s_842721192_9lod9bdd_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 20-MAR-14

5.重启数据库【用恢复的参数文件启动数据库】
RMAN> shutdown immediate;

RMAN> startup nomount;

6.恢复控制文件
RMAN> restore controlfile from autobackup;

Starting restore at 20-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: /u01/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/oracle/flash_recovery_area/ORCL/autobackup/2014_03_20/o1_mf_s_842721192_9lod9bdd_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/oracle/oradata/orcl/control01.ctl
output filename=/u01/oracle/oradata/orcl/control02.ctl
output filename=/u01/oracle/oradata/orcl/control03.ctl
Finished restore at 20-MAR-14

7.启动到mount状态
RMAN> alter database mount;

8.还原数据库
RMAN> restore database;

Starting restore at 20-MAR-14
Starting implicit crosscheck backup at 20-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 20-MAR-14
Starting implicit crosscheck copy at 20-MAR-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-MAR-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oracle/flash_recovery_area/ORCL/autobackup/2014_03_20/o1_mf_s_842721192_9lod9bdd_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmbak/rm_22_03p3lprb_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rmbak/rm_22_03p3lprb_1_1 tag=TAG20140320T171211
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 20-MAR-14

9.恢复数据库
RMAN> recover database;

Starting recover at 20-MAR-14
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/ORCL/backupset/2014_03_20/o1_mf_annnn_TAG20140320T171310_9lod97wc_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oracle/flash_recovery_area/ORCL/backupset/2014_03_20/o1_mf_annnn_TAG20140320T171310_9lod97wc_.bkp tag=TAG20140320T171310
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/arch/1_8_842690420.dbf thread=1 sequence=8
archive log filename=/u01/arch/1_9_842690420.dbf thread=1 sequence=9
archive log filename=/u01/arch/1_9_842690420.dbf thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/20/2014 17:34:53
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/arch/1_9_842690420.dbf'
ORA-00310: archived log contains sequence 9; sequence 10 required
ORA-00334: archived log: '/u01/arch/1_9_842690420.dbf'
【这个错误可以不用考虑】