第十一章： RMAN 备份与恢复
----------------非归档模式 RMAN 备份恢复
一、查看测试环境
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 28 08:40:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
08:40:17 SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 201328380 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
08:40:42 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string lx01
db_unique_name string lx01
global_names boolean FALSE
instance_name string lx01
lock_name_space string
log_file_name_convert string
service_names string lx01
08:41:29 SQL> !clear
08:41:45 SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /disk4/arch/lx01
Oldest online log sequence 1
Current log sequence 3
08:41:57 SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string lx01
db_unique_name string lx01
global_names boolean FALSE
instance_name string lx01
lock_name_space string
log_file_name_convert string
service_names string lx01
08:42:10 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 1 10485760 2 NO INACTIVE 1278963 2011-07-22 12:00:15
4 1 0 10485760 2 YES UNUSED 0
3 1 3 10485760 2 NO CURRENT 1321065 2011-07-28 08:40:32
2 1 2 10485760 2 NO INACTIVE 1300729 2011-07-28 08:20:06
08:42:42 SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/lx01/system01.dbf
/u01/app/oracle/oradata/lx01/rtbs01.dbf
/u01/app/oracle/oradata/lx01/sysaux01.dbf
/u01/app/oracle/oradata/lx01/user01.dbf
/u01/app/oracle/oradata/lx01/small_undo01.dbf
/u01/app/oracle/oradata/lx01/test01.dbf
/u01/app/oracle/oradata/lx01/users02.dbf
/u01/app/oracle/oradata/lx01/undo01.dbf
/u01/app/oracle/oradata/lx01/indexes01.dbf
9 rows selected.
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 28 08:45:39 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
08:45:39 SQL> conn scott/tiger
Connected.
08:45:41 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ADMIN_EXT_EMPLOYEES TABLE
SYS_TEMP_FBT TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP_EXT TABLE
EXCEPTIONS TABLE
SALGRADE TABLE
EMP1 TABLE
TEST TABLE
10 rows selected.
二、利用rman备份
RMAN> run {
2> shutdown immediate;
3> startup mount;
4> backup database format='/disk4/rman/lx/%d_%s.bak';
5> alter database open;
6> }
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 201328380 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
Starting backup at 22-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/lx01/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/lx01/rtbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/lx01/sysaux01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/lx01/user01.dbf
input datafile fno=00010 name=/u01/app/oracle/oradata/lx01/undo01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/lx01/test01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/lx01/users02.dbf
input datafile fno=00011 name=/u01/app/oracle/oradata/lx01/indexes01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/lx01/small_undo01.dbf
channel ORA_DISK_1: starting piece 1 at 22-JUL-11
channel ORA_DISK_1: finished piece 1 at 22-JUL-11
piece handle=/disk4/rman/lx/LX01_115.bak tag=TAG20110722T114920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 22-JUL-11
Starting Control File and SPFILE Autobackup at 22-JUL-11
piece handle=/disk4/recovery/lx01/LX01/autobackup/2011_07_22/o1_mf_s_757165750_72m71539_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-JUL-11
database opened
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
111 Full 319.06M DISK 00:00:29 22-JUL-11
BP Key: 111 Status: AVAILABLE Compressed: NO Tag: TAG20110722T114920
Piece Name: /disk4/rman/lx/LX01_115.bak
List of Datafiles in backup set 111
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/system01.dbf
2 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/rtbs01.dbf
3 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/sysaux01.dbf
4 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/user01.dbf
5 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/small_undo01.dbf
6 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/test01.dbf
7 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/users02.dbf
10 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/undo01.dbf
11 Full 1278135 22-JUL-11 /u01/app/oracle/oradata/lx01/indexes01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
112 Full 7.27M DISK 00:00:01 22-JUL-11
BP Key: 112 Status: AVAILABLE Compressed: NO Tag: TAG20110722T114956
Piece Name: /disk4/recovery/lx01/LX01/autobackup/2011_07_22/o1_mf_s_757165750_72m71539_.bkp
Control File Included: Ckp SCN: 1278135 Ckp time: 22-JUL-11
SPFILE Included: Modification time: 22-JUL-11
三、模拟测试环境
08:45:48 SQL> select * from test order by 1;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
08:45:53 SQL> conn /as sysdba
Connected.
08:46:01 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 1 10485760 2 NO INACTIVE 1278963 2011-07-22 12:00:15
4 1 4 10485760 2 NO CURRENT 1321633 2011-07-28 08:45:01
3 1 3 10485760 2 NO ACTIVE 1321065 2011-07-28 08:40:32
2 1 2 10485760 2 NO INACTIVE 1300729 2011-07-28 08:20:06
08:46:06 SQL> insert into scott.test values (9);
1 row created.
08:46:17 SQL> insert into scott.test values (10);
1 row created.
08:46:20 SQL> commit;
Commit complete.
08:46:21 SQL> alter system switch logfile;
System altered.
08:46:25 SQL> /
System altered.
08:46:27 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 5 10485760 2 NO ACTIVE 1321688 2011-07-28 08:46:25
4 1 4 10485760 2 NO ACTIVE 1321633 2011-07-28 08:45:01
3 1 3 10485760 2 NO ACTIVE 1321065 2011-07-28 08:40:32
2 1 6 10485760 2 NO CURRENT 1321690 2011-07-28 08:46:27
08:46:31 SQL> insert into scott.test values (11);
1 row created.
08:46:48 SQL> insert into scott.test values (12);
1 row created.
08:46:50 SQL> commit;
Commit complete.
08:46:51 SQL> alter system switch logfile;
System altered.
08:46:56 SQL> /
System altered.
08:47:02 SQL> /
System altered.
08:47:03 SQL> /
System altered.
08:47:05 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 9 10485760 2 NO INACTIVE 1321706 2011-07-28 08:47:03
4 1 8 10485760 2 NO INACTIVE 1321704 2011-07-28 08:47:02
3 1 7 10485760 2 NO INACTIVE 1321702 2011-07-28 08:46:56
2 1 10 10485760 2 NO CURRENT 1321708 2011-07-28 08:47:05
/
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 9 10485760 2 NO INACTIVE 1321706 2011-07-28 08:47:03
4 1 8 10485760 2 NO INACTIVE 1321704 2011-07-28 08:47:02
3 1 7 10485760 2 NO INACTIVE 1321702 2011-07-28 08:46:56
2 1 10 10485760 2 NO CURRENT 1321708 2011-07-28 08:47:05
08:47:13 SQL> alter system switch logfile;
System altered.
08:47:16 SQL> select * from scott.test;
ID
----------
6
7
8
1
2
3
4
5
9
10
11
12
12 rows selected.
08:47:22 SQL> shutdown abort
ORACLE instance shut down.
08:47:41 SQL> !
[oracle@oracle ~]$
[oracle@oracle ~]$ rm /u01/app/oracle/oradata/lx01/*.dbf
[oracle@oracle ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 28 08:50:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
08:50:16 SQL> startup
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 201328380 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/lx01/system01.dbf'
08:50:25 SQL> select file# ,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
2 FILE NOT FOUND
3 FILE NOT FOUND
4 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND
7 FILE NOT FOUND
10 FILE NOT FOUND
11 FILE NOT FOUND
9 rows selected.
08:50:37 SQL> exit
ERROR:
ORA-03135: connection lost contact
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options (with complications)
四、用rman restore database
08:52:45 SQL> select dbid from v$database;
DBID
----------
1559643052
08:54:04 SQL>
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
------- ---- -- ---------- ----------- ------------ ---------------
113 Full 142.50M DISK 00:00:26 28-JUL-11
BP Key: 113 Status: AVAILABLE Compressed: NO Tag: TAG20110728T084406
Piece Name: /disk4/rman/lx/lx01_118_757673047.bak
List of Datafiles in backup set 113
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/system01.dbf
5 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/small_undo01.dbf
10 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/undo01.dbf
11 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/indexes01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
114 Full 176.95M DISK 00:00:34 28-JUL-11
BP Key: 114 Status: AVAILABLE Compressed: NO Tag: TAG20110728T084406
Piece Name: /disk4/rman/lx/lx01_119_757673047.bak
List of Datafiles in backup set 114
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/rtbs01.dbf
3 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/sysaux01.dbf
4 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/user01.dbf
6 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/test01.dbf
7 Full 1321454 28-JUL-11 /u01/app/oracle/oradata/lx01/users02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
115 Full 7.27M DISK 00:00:01 28-JUL-11
BP Key: 115 Status: AVAILABLE Compressed: NO Tag: TAG20110728T084443
Piece Name: /disk4/recovery/lx01/LX01/autobackup/2011_07_28/o1_mf_s_757673037_732pfw39_.bkp
Control File Included: Ckp SCN: 1321454 Ckp time: 28-JUL-11
SPFILE Included: Modification time: 22-JUL-11
RMAN> shutdown
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 251658240 bytes
Fixed Size 1218820 bytes
Variable Size 201328380 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
RMAN> set dbid=1559643052;
executing command: SET DBID
RMAN> restore controlfile from '/disk4/recovery/lx01/LX01/autobackup/2011_07_28/o1_mf_s_757673037_732pfw39_.bkp';
Starting restore at 28-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/disk1/lx01/oradata/control01.ctl
output filename=/disk1/lx01/oradata/control02.ctl
output filename=/disk2/lx01/oradata/control03.ctl
Finished restore at 28-JUL-11
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 28-JUL-11
Starting implicit crosscheck backup at 28-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 28-JUL-11
Starting implicit crosscheck copy at 28-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 28-JUL-11
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /disk4/recovery/lx01/LX01/autobackup/2011_07_28/o1_mf_s_757673037_732pfw39_.bkp
using channel ORA_DISK_1
skipping datafile 1; already restored to file /u01/app/oracle/oradata/lx01/system01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/lx01/small_undo01.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/lx01/undo01.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/lx01/indexes01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/lx01/rtbs01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/lx01/sysaux01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/lx01/user01.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/lx01/test01.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/lx01/users02.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 28-JUL-11
五、查看restore 结果
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 28 08:52:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
08:52:16 SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1321454
08:52:31 SQL> select checkpoint_change#,file# from v$datafile;
CHECKPOINT_CHANGE# FILE#
------------------ ----------
1321454 1
1321454 2
1321454 3
1321454 4
1321454 5
1321454 6
1321454 7
1321454 10
1321454 11
9 rows selected.
08:52:38 SQL> select checkpoint_change#,file# from v$datafile_header;
CHECKPOINT_CHANGE# FILE#
------------------ ----------
1321454 1
1321454 2
1321454 3
1321454 4
1321454 5
1321454 6
1321454 7
1321454 10
1321454 11
9 rows selected.
六、用rman 进行恢复
RMAN> recover database;
Starting recover at 28-JUL-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 8 is already on disk as file /disk1/lx01/oradata/redo04b.log
archive log thread 1 sequence 9 is already on disk as file /disk1/lx01/oradata/redo01b.log
archive log thread 1 sequence 10 is already on disk as file /disk1/lx01/oradata/redo02b.log
archive log thread 1 sequence 11 is already on disk as file /disk1/lx01/oradata/redo03b.log
unable to find archive log
archive log thread=1 sequence=3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/28/2011 08:59:30
RMAN-06054: media recovery requesting unknown log: thread 1 seq 3 lowscn 1321454
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/28/2011 08:59:57
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened
RMAN>
alter_lx01.log 告警日志信息：
alter database mount
Thu Jul 28 08:58:15 2011
Setting recovery target incarnation to 20
Thu Jul 28 08:58:15 2011
Successful mount of redo thread 1, with mount id 1560169571
Thu Jul 28 08:58:15 2011
Database mounted in Exclusive Mode
Completed: alter database mount
Thu Jul 28 08:59:29 2011
alter database recover datafile list clear
Thu Jul 28 08:59:29 2011
Completed: alter database recover datafile list clear
Thu Jul 28 08:59:29 2011
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 10 , 11
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 10 , 11
Thu Jul 28 08:59:29 2011
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
Thu Jul 28 08:59:29 2011
alter database recover cancel
Thu Jul 28 08:59:30 2011
Media Recovery Canceled
Completed: alter database recover cancel