備份&恢復之八: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
在伺服器測試成功
備份&恢復之八: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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 備份&恢復之五:歸檔模式下丟失或損壞一個資料檔案模式
- rman恢復--歸檔模式有備份,丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份,丟失資料檔案的恢復模式
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 備份與恢復--資料檔案損壞或丟失
- 【備份恢復】非歸檔模式下丟失任意資料檔案 恢復操作模式
- rman 恢復---歸檔丟失and資料檔案損壞
- 備份恢復之資料檔案丟失
- 歸檔模式無備份丟失資料檔案後恢復模式
- 歸檔模式有備份丟失資料檔案後恢復模式
- 非系統資料檔案損壞,rman備份恢復
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- Oracle RMAN 不完全恢復(只有資料檔案備份,丟失歸檔日誌備份)Oracle
- 【備份恢復】 丟失一個控制檔案 之恢復操作
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- rman備份恢復-rman恢復資料檔案測試
- 沒備份,歸檔日誌存在,丟失資料檔案的恢復
- dg丟失歸檔,使用rman增量備份恢復
- rman備份但丟失一個資料檔案,但有歸檔備份
- 備份&恢復之十二:損壞單個控制檔案
- 備份&恢復之四:非歸檔模式下的備份與恢復模式
- 歸檔模式,恢復沒有備份的資料檔案模式
- 資料檔案丟失損壞的恢復--
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- RMAN備份恢復整個庫
- 非歸檔無備份下控制檔案丟失的恢復
- 歸檔模式下資料檔案丟失的恢復模式
- rman恢復資料庫--用備份的控制檔案資料庫
- 恢復案例:歸檔模式下丟失全部資料檔案的恢復模式
- RMAN備份恢復典型案例——資料檔案存在壞快
- 恢復之非歸檔模式下資料庫非正常關閉的備份與恢復模式資料庫
- 歸檔模式有備份丟失控制檔案和資料檔案後恢復模式
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- rman 非歸檔模式下停庫備份與恢復模式
- 備份與恢復--從備份的歸檔日誌中恢復資料
- RMAN_部分資料檔案丟失或者損壞的恢復
- RMAN備份、恢復實驗室 之 備份篇 【歸檔模式】模式