备份＆恢复之八：RMAN备份归档模式下损坏（丢失）多个数据文件，进行整个数据库的恢复...

2008/05/08    
备份＆恢复之八：RMAN备份归档模式下损坏（丢失）多个数据文件，进行整个数据库的恢复


测试环境：
1      操作系统：Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007

2     数据库版本：Oracle10g
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


3  设置成归档模式.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     16
Current log sequence           18
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     16
Next log sequence to archive   18
Current log sequence           18
SQL>


4 创建表插入数据
SQL> drop table test;

Table dropped.

SQL> create table test(a int) tablespace users;

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

         A
----------
         1



5  查看表test在哪个表空间
SQL> select table_name,tablespace_name from dba_tables
  2  where table_name='TEST';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS



在users表空间内


6  备份数据库
[oracle@mzl BackupDatabase]$ pwd
/home/mzl/BackupDatabase
[oracle@mzl BackupDatabase]$ vi backup.rcv
#   script.:bakup.rcv
#   creater:mengzhaoliang
#   date:7.5.2008
#   desc:backup all database datafile in archive with rman

# connect database
export ORACLE_SID=ORCL
export PATH=/u01/app/oracle/product/10.2.0/db_1/bin
rman target/ << EOF_RMAN
run{
allocate channel c1 type disk;
backup full tag 'dbfull' format '/home/mzl/BackupDatabase/full_%u_%s_%p' database
include current controlfile;
sql 'alter system archive log current';
release channel c1;
}
# end



赋予权限，执行脚本
[oracle@mzl BackupDatabase]$ chmod +x backup.rcv
[oracle@mzl BackupDatabase]$ ./backup.rcv

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 10:21:25 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1172558471)

RMAN> 2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=154 devtype=DISK

Starting backup at 08-MAY-08
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/perfstat.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/orcl/risenet.dbf
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel c1: starting piece 1 at 08-MAY-08
channel c1: finished piece 1 at 08-MAY-08
piece handle=/home/mzl/BackupDatabase/full_09jfrnh8_9_1 tag=DBFULL comment=NONE
channel c1: backup set complete, elapsed time: 00:01:36
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 08-MAY-08
channel c1: finished piece 1 at 08-MAY-08
piece handle=/home/mzl/BackupDatabase/full_0ajfrnk8_10_1 tag=DBFULL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-MAY-08

sql statement: alter system archive log current

released channel: c1

RMAN> 2>

Recovery Manager complete.


7  继续插入数据.
SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

         A
----------
         1
         2

SQL> alter system switch logfile;

System altered.

8  关闭数据库，模拟丢失数据文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@mzl orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@mzl orcl]$ mv *.dbf Old
[oracle@mzl orcl]$ ls
control01.ctl  control03.ctl  Old         redo02.log  sqlnet.log
control02.ctl  create.sql     redo01.log  redo03.log


9 启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area  268435456 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'


文件alert_ORCL.log的 信息
ALTER DATABASE   MOUNT
Thu May  8 10:27:56 2008
Setting recovery target incarnation to 2
Thu May  8 10:27:56 2008
Successful mount of redo thread 1, with mount id 1181903143
Thu May  8 10:27:56 2008
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu May  8 10:27:56 2008
ALTER DATABASE OPEN
Thu May  8 10:27:56 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:27:56 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:27:56 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:27:56 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:27:56 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:27:56 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/perfstat.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:27:56 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl/risenet.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
Thu May  8 10:28:18 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:28:18 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:28:18 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:28:18 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:28:18 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:28:18 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/perfstat.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May  8 10:28:18 2008
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_3638.trc:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl/risenet.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3



查看哪些文件需要恢复：
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                             CHANGE# TIME
---------- ------- ------- ------------------------------ ---------- ---------
         1 ONLINE  ONLINE  FILE NOT FOUND                          0
         2 ONLINE  ONLINE  FILE NOT FOUND                          0
         3 ONLINE  ONLINE  FILE NOT FOUND                          0
         4 ONLINE  ONLINE  FILE NOT FOUND                          0
         5 ONLINE  ONLINE  FILE NOT FOUND                          0
         6 ONLINE  ONLINE  FILE NOT FOUND                          0
         7 ONLINE  ONLINE  FILE NOT FOUND                          0

7 rows selected.



10  利用RMAN进行恢复
[oracle@mzl BackupDatabase]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 10:40:06 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1172558471, not open)

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    613.34M    DISK        00:01:33     08-MAY-08      
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: DBFULL
        Piece Name: /home/mzl/BackupDatabase/full_09jfrnh8_9_1
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1146700    08-MAY-08 /u01/app/oracle/oradata/orcl/system01.dbf
  2       Full 1146700    08-MAY-08 /u01/app/oracle/oradata/orcl/undotbs01.dbf
  3       Full 1146700    08-MAY-08 /u01/app/oracle/oradata/orcl/sysaux01.dbf
  4       Full 1146700    08-MAY-08 /u01/app/oracle/oradata/orcl/users01.dbf
  5       Full 1146700    08-MAY-08 /u01/app/oracle/oradata/orcl/example01.dbf
  6       Full 1146700    08-MAY-08 /u01/app/oracle/oradata/orcl/perfstat.dbf
  7       Full 1146700    08-MAY-08 /u01/app/oracle/oradata/orcl/risenet.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    7.08M      DISK        00:00:01     08-MAY-08      
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: DBFULL
        Piece Name: /home/mzl/BackupDatabase/full_0ajfrnk8_10_1
  Control File Included: Ckp SCN: 1146738      Ckp time: 08-MAY-08

RMAN> run{
2> allocate channel c1 type disk;
3> restore database;
4> recover database;
5> sql 'alter database open';
6> release channel c1;
7> }

allocated channel: c1
channel c1: sid=156 devtype=DISK

Starting restore at 08-MAY-08

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/orcl/perfstat.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/orcl/risenet.dbf
channel c1: reading from backup piece /home/mzl/BackupDatabase/full_09jfrnh8_9_1
channel c1: restored backup piece 1
piece handle=/home/mzl/BackupDatabase/full_09jfrnh8_9_1 tag=DBFULL
channel c1: restore complete, elapsed time: 00:01:25
Finished restore at 08-MAY-08

Starting recover at 08-MAY-08

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 08-MAY-08

sql statement: alter database open

released channel: c1

RMAN>


11  检查数据库的数据（完全恢复）

SQL> select * from test;

         A
----------
         1
         2






说明：
1、只要有备份与归档存在，RMAN也可以实现数据库的完全恢复（不丢失数据）
2、同OS备份数据库恢复，适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复
3、目标数据库在mount下进行，如果恢复成功，再打开数据库。
4、RMAN的备份与恢复命令相对比较简单并可靠，建议有条件的话，都采用RMAN进行数据库的备份。

 
参考itpub上piner的备份与恢复案例：
http://www.itpub.net/viewthread.php?tid=126320&extra=page%3D4%26amp%3Bfilter%3Ddigest
在服务器测试成功
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-263153/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-263153/