Oracle 备份与恢复学习笔记（11）-白红宇

第十一章： 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

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

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;

----------

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;

----------

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

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

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

本文转自客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/792534，如需转载请自行联系原作者