備份&恢復之八:RMAN備份歸檔模式下損壞(丟失)多個資料檔案,進行整個資料庫的恢復

mengzhaoliang發表於2008-05-08
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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章