ORACLE恢复案例

一、数据库恢复案例

1、丢失或损坏一个数据文件

1.) 连接数据库，创建测试表并插入记录

[oracle@web ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 29 08:58:51 2011

test@ORCL> conn test/admin

Connected.

test@ORCL> create table test(a int);

Table created

TEST@ORCL> insert into test values(1);

1 row inserted

TEST@ORCL> commit;

Commit complete

2.) 备份数据库表空间users

[oracle@web ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期二 11月 29 09:12:48 2011

connected to target database: ORCL (DBID=1284637334)

RMAN> run{

allocate channel c1 type disk;

backup tag 'tsusers' format '/software/rman_bak/tsusers_%u_%s_%p' tablespace users;

release channel c1;

}

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=52 device type=DISK

Starting backup at 29-11月-11

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00005 name=/home/oracle/oradata/users02.dbf

input datafile file number=00004 name=/home/oracle/oradata/users01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/tsusers_2fmsrvss_79_1 tag=TSUSERS comment=NONE

channel c1: backup set complete, elapsed time: 00:01:25

Finished backup at 29-11月-11

Starting Control File and SPFILE Autobackup at 29-11月-11

piece handle=/home/oracle/dbbackup/ctl_20111129_c-1284637334-20111129-01 comment=NONE

Finished Control File and SPFILE Autobackup at 29-11月-11

released channel: c1

RMAN>

3.) 继续在测试表中插入记录

TEST@ORCL> insert into test values(2);

1 row inserted

TEST@ORCL> commit;

Commit complete

TEST@ORCL> select * from test;

A

---------------------------------------

1

2

TEST@ORCL> alter system switch logfile;

System altered.

TEST@ORCL> r

1* alter system switch logfile;

System altered.

4.) 关闭数据库，模拟丢失数据文件

SYS@ORCL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

[oracle@web oradata]$ rm users02.dbf

5.) 启动数据库，检查错误

idle> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-01157: 无法标识/锁定数据文件 5 - 请参阅 DBWR 跟踪文件

ORA-01110: 数据文件 5: '/home/oracle/oradata/users02.dbf'

6.) 先打开数据库

TEST@ORCL> alter database datafile 5 offline drop;

Database altered.

TEST@ORCL> alter database open;

Database altered.

7.) 恢复该数据文件表空间

恢复脚本可以是恢复单个数据文件

run{

allocate channel c1 type disk;

restore datafile 5;

recover datafile 5;

sql 'alter database datafile 5 online';

release channel c1;

}

也可以是,恢复表空间

run{

allocate channel c1 type disk;

restore tablespace users;

recover tablespace users;

sql 'alter database datafile 5 online';

release channel c1;

}

过程如下：

[oracle@web oradata]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期二 11月 29 09:32:23 2011

connected to target database: ORCL (DBID=1284637334)

RMAN> run{

allocate channel c1 type disk;

restore datafile 5;

recover datafile 5;

sql 'alter database datafile 5 online';

release channel c1;

}

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=42 device type=DISK

Starting restore at 29-11月-11

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00005 to /home/oracle/oradata/users02.dbf

channel c1: reading from backup piece /software/rman_bak/tsusers_2fmsrvss_79_1

channel c1: piece handle=/software/rman_bak/tsusers_2fmsrvss_79_1 tag=TSUSERS

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:01:26

Finished restore at 29-11月-11

Starting recover at 29-11月-11

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 29-11月-11

sql statement: alter database datafile 5 online

released channel: c1

8.) 检查数据是否完整

TEST@ORCL> select * from test;

A

---------------------------------------

1

2

说明：

1、RMAN也可以实现单个表空间或数据文件的恢复，恢复过程可以在mount下或open方式下，如果在open方式下恢复，可以减少down机时间。

2、如果损坏的是一个数据文件，建议offline并在open方式下恢复。

3、这里可以看到，RMAN进行数据文件与表空间恢复的时候，代码都比较简单，而且能保证备份与恢复的可靠性，所以建议采用RMAN的备份与恢复。

2、多数据文件丢失或损坏

1.) 连接数据库，创建测试表并插入记录

TEST@ORCL> create table test(a int);

Table created

TEST@ORCL> insert into test values(1);

1 row inserted

TEST@ORCL> commit;

Commit complete

2.) 备份数据库

[oracle@web oradata]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期二 11月 29 09:48:36 2011

connected to target database: ORCL (DBID=1284637334)

RMAN> run {

allocate channel c1 type disk;

backup database format ‘/software/rman_bak/full_%U’;

release channel c1;

}

released channel: ORA_DISK_1

allocated channel: c1

channel c1: SID=48 device type=DISK

Starting backup at 29-11月-11

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00005 name=/home/oracle/oradata/users02.dbf

input datafile file number=00001 name=/opt/oracle/oradata/orcl/system01.dbf

input datafile file number=00003 name=/opt/oracle/oradata/orcl/undotbs01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_2kmss2d7_1_1 tag=TAG20111129T100007 comment=NONE

channel c1: backup set complete, elapsed time: 00:01:55

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00004 name=/home/oracle/oradata/users01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_2lmss2gq_1_1 tag=TAG20111129T100007 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:35

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00002 name=/home/oracle/oradata/sysaux01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_2mmss2hu_1_1 tag=TAG20111129T100007 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:25

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00006 name=/home/oracle/oradata/undotbs02.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_2nmss2in_1_1 tag=TAG20111129T100007 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:07

Finished backup at 29-11月-11

Starting Control File and SPFILE Autobackup at 29-11月-11

piece handle=/home/oracle/dbbackup/ctl_20111129_c-1284637334-20111129-03 comment=NONE

Finished Control File and SPFILE Autobackup at 29-11月-11

released channel: c1

3.) 继续在测试表中插入记录

TEST@ORCL> insert into test values(2);

1 row inserted

TEST@ORCL> commit;

Commit complete

TEST@ORCL> select * from test;

A

---------------------------------------

1

2

TEST@ORCL> alter system switch logfile;

System altered.

TEST@ORCL> r

1* alter system switch logfile

System altered.

4.) 关闭数据库，模拟丢失数据文件

SQL@ORCL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

[oracle@web oradata]$ ll

total 3396900

-rw-r----- 1 oracle oinstall 629153792 Nov 29 10:08 sysaux01.dbf

-rw-r----- 1 oracle oinstall 629153792 Nov 29 10:08 undotbs02.dbf

-rw-r----- 1 oracle oinstall 643833856 Nov 29 10:08 users01.dbf

-rw-r----- 1 oracle oinstall 1572872192 Nov 29 10:08 users02.dbf

[oracle@web oradata]$ rm -rf *.dbf

5.) 启动数据库，检查错误

sys@ORCL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-01157: 无法标识/锁定数据文件 2 - 请参阅 DBWR 跟踪文件

ORA-01110: 数据文件 2: '/home/oracle/oradata/sysaux01.dbf'

查询v$recover_file

TEST@ORCL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

--------- ------- ------- --------------------------------------------------------- ---------- --------------

2 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

可以知道有四个数据文件需要恢复

6.) 利用RMAN进行恢复

RMAN> run{

allocate channel c1 type disk;

restore database;

recover database;

sql 'alter database open';

release channel c1;

}

Starting restore at 29-11月-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=17 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/users02.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_2kmss2d7_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_2kmss2d7_1_1 tag=TAG20111129T100007

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:45

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/users01.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_2lmss2gq_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_2lmss2gq_1_1 tag=TAG20111129T100007

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_2mmss2hu_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_2mmss2hu_1_1 tag=TAG20111129T100007

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/undotbs02.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_2nmss2in_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_2nmss2in_1_1 tag=TAG20111129T100007

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 29-11月-11

Starting recover at 29-11月-11

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:04

Finished recover at 29-11月-11

sql statement: alter database open

RMAN>

7.) 检查数据库的数据（完全恢复）

TEST@ORCL> select * from test;

A

---------------------------------------

1

2

说明：

1、只要有备份与归档存在，RMAN也可以实现数据库的完全恢复（不丢失数据）。

2、同OS备份恢复，适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复。

3、目标数据库在mount下进行，如果恢复成功，再打开数据库。

4、RMAN的备份与恢复命令相对比较简单并可靠，建议有条件的话，都采用RMAN进行数据库的备份。

3、基于时间的不完全恢复案例

OS热备份下的基于时间的恢复

不完全恢复可以分为基于时间的恢复，基于改变的恢复与基于撤消的恢复，这里已基于时间的恢复为例子来说明不完全恢复过程。

基于时间的恢复可以不完全恢复到现在时间之前的某一个时间，对于某些误操作，如删除了一个数据表，可以在备用恢复环境上恢复到表的删除时间之前，然后把该表导出到正式环境，避免一个人为的错误。

1.) 连接数据库，创建测试表并插入记录

TEST@ORCL> create table test(a int);

Table created

TEST@ORCL> insert into test values(1);

1 row inserted

TEST@ORCL> commit;

Commit complete

2.) 备份数据库(最好备份所有的数据文件，包括临时数据文件)

RMAN> run {

Allocate channel c1 type disk;

Backup database format ‘/software/rman_bak/full_%U’;

Release channel c1;

}

或冷备份也可以

3.) 删除测试表

假定删除前的时间为T1，在删除之前，便于测试，继续插入数据并应用到归档。

TEST@ORCL> insert into test values(2);

1 row inserted

TEST@ORCL> commit;

Commit complete

TEST@ORCL> select * from test;

A

---------------------------------------

1

2

TEST@ORCL> alter system switch logfile;

Statement processed.

TEST@ORCL> alter system switch logfile;

Statement processed.

TEST@ORCL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2011-11-29 14:07:04

TEST@ORCL> drop table test;

Table dropped.

4.) 准备恢复到时间点

时间点为T1，找回删除的表，先关闭数据库

RMAN> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

RMAN> exit

Recovery Manager complete.

5.) restore所有的数据文件

不完全恢复需要还原所有的数据库文件，所以还原数据库是比较快捷的做法，而且要在mount状态下进行。

[oracle@web oradata]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 星期二 11月 29 14:12:51 2011

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 243273120 bytes

Database Buffers 12582912 bytes

Redo Buffers 4980736 bytes

RMAN> restore database;

Starting restore at 29-11月-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oradata/users02.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_30mssgj3_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_30mssgj3_1_1 tag=TAG20111129T140210

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:32

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/users01.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_31mssgnl_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_31mssgnl_1_1 tag=TAG20111129T140210

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_32mssgof_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_32mssgof_1_1 tag=TAG20111129T140210

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:36

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/oradata/undotbs02.dbf

channel ORA_DISK_1: reading from backup piece /software/rman_bak/full_33mssgp8_1_1

channel ORA_DISK_1: piece handle=/software/rman_bak/full_33mssgp8_1_1 tag=TAG20111129T140210

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 29-11月-11

如果是冷备份，则拷贝刚才备份的所有数据文件回来。

6.) 开始不完全恢复

数据库恢复到T1时间。

RMAN> run {

sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';

set until time '2011-11-29 14:07:04';

recover database;

};

sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"

executing command: SET until clause

Starting recover at 29-11月-11

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 29-11月-11

7.) 打开数据库检查数据

RMAN> alter database open resetlogs;

Database altered.

TEST@ORCL> select * from test;

A

---------------------------------------

1

2

说明注意：

1、不完全恢复最好备份所有的数据，冷备份亦可，因为恢复过程是从备份点往后恢复的，如果因为其中一个数据文件的时间戳(SCN)大于要恢复的时间点，那么恢复都是不可能成功的。

2、不完全恢复有三种方式，过程都一样，仅仅是recover命令有所不一样，这里用基于时间的恢复作为示例。

3、不完全恢复之后，都必须用resetlogs的方式打开数据库，建议马上再做一次全备份，因为resetlogs之后再用以前的备份恢复是很难了。

4、以上是在删除之前获得时间，但是实际应用中，很难知道删除之前的实际时间，但可以采用大致时间即可，或可以采用分析日志文件(logmnr)，取得精确的需要恢复的时间。

5、一般都是在测试机后备用机器上采用这种不完全恢复，恢复之后导出/导入被误删的表回生产系统。

4、基于SCN的不完全恢复案例

以上用OS备份说明了一个基于时间的恢复，现在用RMAN说明一个基于改变的恢复。

1.) 连接数据库，创建测试表并插入记录

TEST@ORCL> create table test(a int);

Table created

TEST@ORCL> insert into test values(1);

1 row inserted

TEST@ORCL> commit;

Commit complete

2.) 备份数据库

RMAN> run {

allocate channel c1 type disk;

backup database format '/software/rman_bak/full_%U';

release channel c1;

};

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=44 device type=DISK

Starting backup at 29-11月-11

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00005 name=/home/oracle/oradata/users02.dbf

input datafile file number=00001 name=/opt/oracle/oradata/orcl/system01.dbf

input datafile file number=00003 name=/opt/oracle/oradata/orcl/undotbs01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_30mssgj3_1_1 tag=TAG20111129T140210 comment=NONE

channel c1: backup set complete, elapsed time: 00:02:26

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00004 name=/home/oracle/oradata/users01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_31mssgnl_1_1 tag=TAG20111129T140210 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:25

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00002 name=/home/oracle/oradata/sysaux01.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_32mssgof_1_1 tag=TAG20111129T140210 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:25

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00006 name=/home/oracle/oradata/undotbs02.dbf

channel c1: starting piece 1 at 29-11月-11

channel c1: finished piece 1 at 29-11月-11

piece handle=/software/rman_bak/full_33mssgp8_1_1 tag=TAG20111129T140210 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:03

Finished backup at 29-11月-11

Starting Control File and SPFILE Autobackup at 29-11月-11

piece handle=/home/oracle/dbbackup/ctl_20111129_c-1284637334-20111129-07 comment=NONE

Finished Control File and SPFILE Autobackup at 29-11月-11

released channel: c1

RMAN>

3.) 删除测试表

在删除之前，便于测试，继续插入数据并应用到归档，并获取删除前的scn号。

TEST@ORCL> insert into test values(2);

1 row inserted

TEST@ORCL> commit;

Commit complete

TEST@ORCL> select * from test;

A

---------------------------------------

1

2

TEST@ORCL> alter system switch logfile;

Statement processed.

TEST@ORCL> alter system switch logfile;

Statement processed.

Oracle 9i之后用：

test@ORCL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

21069993

Oracle 9i之前用：

TEST@ORCL> select max(ktuxescnw * power(2, 32) + ktuxescnb) scn from x$ktuxe;

SCN

----------

21069993

TEST@ORCL> drop table test;

Table dropped.

4.) 准备恢复数据库

恢复到SCN 21069993，先关闭数据库，然后启动到mount下。

RMAN> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 247467424 bytes

Database Buffers 8388608 bytes

Redo Buffers 4980736 bytes

5.) 开始恢复到改变点

RMAN> run{

allocate channel c1 type disk;

restore database;

recover database until scn 21069993;

sql 'ALTER DATABASE OPEN RESETLOGS';

release channel c1;

}

allocated channel: c1

channel c1: SID=18 device type=DISK

Starting restore at 29-11月-11

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00001 to /opt/oracle/oradata/orcl/system01.dbf

channel c1: restoring datafile 00003 to /opt/oracle/oradata/orcl/undotbs01.dbf

channel c1: restoring datafile 00005 to /home/oracle/oradata/users02.dbf

channel c1: reading from backup piece /software/rman_bak/full_30mssgj3_1_1

channel c1: piece handle=/software/rman_bak/full_30mssgj3_1_1 tag=TAG20111129T140210

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:01:56

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00004 to /home/oracle/oradata/users01.dbf

channel c1: reading from backup piece /software/rman_bak/full_31mssgnl_1_1

channel c1: piece handle=/software/rman_bak/full_31mssgnl_1_1 tag=TAG20111129T140210

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:35

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00002 to /home/oracle/oradata/sysaux01.dbf

channel c1: reading from backup piece /software/rman_bak/full_32mssgof_1_1

channel c1: piece handle=/software/rman_bak/full_32mssgof_1_1 tag=TAG20111129T140210

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:35

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00006 to /home/oracle/oradata/undotbs02.dbf

channel c1: reading from backup piece /software/rman_bak/full_33mssgp8_1_1

channel c1: piece handle=/software/rman_bak/full_33mssgp8_1_1 tag=TAG20111129T140210

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:15

Finished restore at 29-11月-11

Starting recover at 29-11月-11

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_1_7f8xm8fo_.arc

archived log for thread 1 with sequence 2 is already on disk as file /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_7f8xmgm3_.arc

archived log for thread 1 with sequence 3 is already on disk as file /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_3_7f8yd42f_.arc

archived log file name=/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_1_7f8xm8fo_.arc thread=1 sequence=1

archived log file name=/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_7f8xmgm3_.arc thread=1 sequence=2

archived log file name=/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_3_7f8yd42f_.arc thread=1 sequence=3

media recovery complete, elapsed time: 00:00:05

Finished recover at 29-11月-11

sql statement: alter database open resetlogs

released channel: c1

6.) 检查数据

TEST@ORCL> select * from test;

A

---------------------------------------

1

2

可以看到，表依然存在。说明：

1、RMAN也可以实现不完全恢复，方法比OS备份恢复的方法更简单可靠

2、RMAN可以基于时间，基于改变与基于日志序列的不完全恢复，基于日志序列的恢复可以指定恢复到哪个日志序列，如

run {

allocate channel ch1 type disk;

allocate channel ch2 type 'sbt_tape';

set until logseq 1234 thread 1;

restore controlfile to '$ORACLE_HOME/dbs/cf1.f'

replicate controlfile from '$ORACLE_HOME/dbs/cf1.f';

alter database mount;

restore database;

recover database;

sql "ALTER DATABASE OPEN RESETLOGS";

}

3、与所有的不完全恢复一样，必须在mount下，restore所有备份数据文件，需要resetlogs。

4、基于改变的恢复比基于时间的恢复更可靠，但是可能也更复杂，需要知道需要恢复到哪一个改变号(SCN)，在正常生产中，获取SCN的办法其实也有很多，如查询数据库字典表(V$archived_log or v$log_history)，或分析归档与联机日志(logmnr)等。

5、损坏联机日志的恢复方法

1.) 损坏非当前联机日志

联机日志分为当前联机日志和非当前联机日志，非当前联机日志的损坏是比较简单的，一般通过clear命令就可以解决问题。

a.) 启动数据库

sys@ORCL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 251661728 bytes

Database Buffers 4194304 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-03113: 通信通道的文件结尾

进程 ID: 16373

会话 ID: 1 序列号: 5

遇到ORA-00312 or ORA-00313错误，从这里我们知道日志组1的数据文件损坏了，从报警文件可以看到更详细的信息。如：

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: ' /opt/oracle/oradata/orcl/redo03.log'

b.) 查看V$log视图

TEST@ORCL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

------ --------- --- ----------------

1 1 NO CURRENT

2 0 YES UNUSED

3 0 YES UNUSED

可以知道，该组是非当前状态，而且已经归档。

c.) 用CLEAR命令重建该日志文件

SYS@ORCL> alter database clear logfile group 3;

如果是该日志组还没有归档，则需要用

SYS@ORCL> alter database clear unarchived logfile group 3;

d.) 打开数据库，重新备份数据库

SYS@ORCL> alter database open;

说明：

1、如果损坏的是非当前的联机日志文件，一般只需要clear就可以重建该日志文件，但是如果该数据库处于归档状态但该日志还没有归档，就需要强行clear。

2、建议clear，特别是强行clear后作一次数据库的全备份。

3、此方法适用于归档与非归档数据库。

2.) 损坏当前联机日志

归档模式下当前日志的损坏有两种情况：

一、是数据库是正常关闭，日志文件中没有未决的事务需要实例恢复，当前日志组的损坏就可以直接用alter database clear unarchived logfile group n来重建。

二、是日志组中有活动的事务，数据库需要媒体恢复，日志组需要用来同步，有两种补救办法：

A.最好的办法就是通过不完全恢复，可以保证数据库的一致性，但是这种办法要求在归档方式下，并且有可用的备份。

B.通过强制性恢复，但是可能导致数据库不一致。

下面分别用来说明这两种恢复方法

a.) 通过备份来恢复

1、打开数据库，会遇到一个类似的错误

SQL@ORCL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-03113: 通信通道的文件结尾

进程 ID: 19294

会话 ID: 1 序列号: 5

Alert日志报错：

Errors in file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19294.trc:

ORA-00313: 无法打开日志组 1 (用于线程 ) 的成员

ORA-00312: 联机日志 1 线程 1: '/opt/oracle/oradata/orcl/redo01.log'

USER (ospid: 19294): terminating the instance due to error 313

Tue Nov 29 15:35:13 2011

ARC1 started with pid=21, OS id=19298

Instance terminated by USER, pid = 19294

2、查看V$log，发现是当前日志

TEST@ORCL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

------ --------- --- ----------------

1 1 NO CURRENT

3 0 YES UNUSED

2 0 YES UNUSED

3、发现clear不成功

TEST@ORCL> alter database clear unarchived logfile group 1;

alter database clear unarchived logfile group 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of thread 1

ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/orcl/redo01.log '

4、拷贝有效的数据库的全备份，并不完全恢复数据库

可以采用获取最近的SCN的办法用until scn恢复或用until cnacel恢复

RMAN> recover database until cancel

先选择auto，尽量恢复可以利用的归档日志，然后重新。

RMAN> recover database until cancel

这次输入cancel，完成不完全恢复，也就是说恢复两次。如：

RMAN> recover database until cancel;

Auto

……

RMAN> recover database until cancel;

Cancel;

5、利用alter database open resetlogs打开数据库

说明：

1、这种办法恢复的数据库是一致的不完全恢复，会丢失当前联机日志中的事务数据。

2、这种方法适合于归档数据库并且有可用的数据库全备份。

3、恢复成功之后，记得再做一次数据库的全备份。

4、建议联机日志文件一定要实现镜相在不同的磁盘上，避免这种情况的发生，因为任何数据的丢失对于生产来说都是不容许的。

b.) 如果没有备份，进行强制性恢复

1、打开数据库，会遇到一个类似的错误

sys@ORCL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@ORCL> startup

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

ORA-03113: 通信通道的文件结尾

进程 ID: 21358

会话 ID: 1 序列号: 5

Alert日志：

Tue Nov 29 15:49:39 2011

ARC1 started with pid=21, OS id=21362

Errors in file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_21321.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/orcl/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_21321.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/orcl/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /opt/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_21358.trc:

ORA-00313: 无法打开日志组 1 (用于线程 ) 的成员

ORA-00312: 联机日志 2 线程 1: '/opt/oracle/oradata/orcl/redo02.log'

USER (ospid: 21358): terminating the instance due to error 313

Tue Nov 29 15:49:39 2011

ARC2 started with pid=22, OS id=21364

Instance terminated by USER, pid = 21358

2、查看V$log，发现是当前日志

TEST@ORCL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

------ --------- --- ----------------

1 3 YES INACTIVE

3 4 YES INACTIVE

2 5 NO CURRENT

3、发现clear不成功

TEST@ORCL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of thread 1

ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/orcl/redo02.log '

4、把数据库down掉

TEST@ORCL> shutdown immediate

5、在init.ora中加入如下参数

_allow_resetlogs_corruption=TRUE

6、重新启动数据库,利用until cancel恢复

TEST@ORCL> recover database until cancel;

Cancel

如果出错，不再理会，发出

TEST@ORCL> alter database open resetlogs;

7、数据库被打开后，马上执行一个full export

8、shutdown数据库,去掉_all_resetlogs_corrupt参数

9、重建库

10、import并完成恢复

11、建议执行一下ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;

说明：

1、该恢复方法是没有办法之后的恢复方法，一般情况下建议不要采用，因为该方法可能导致数据库的不一致。

2、该方法也丢失数据，但是丢失的数据没有上一种方法的数据多，主要是未写入数据文件的已提交或未提交数据。

3、建议成功后严格执行以上的7到11步，完成数据库的检查与分析

4、全部完成后做一次数据库的全备份

5、建议联机日志文件一定要实现镜相在不同的磁盘上，避免这种情况的发生，因为任何数据的丢失对于生产来说都是不容许的。

6、损坏控制文件的恢复方法

1.) 损坏单个控制文件

损坏单个控制文件是比较容易恢复的，因为一般的数据库系统，控制文件都不是一个，而且所有的控制文件都互为镜相，只要拷贝一个好的控制文件替换坏的控制文件就可以了。

1、控制文件损坏，最典型的就是启动数据库出错，不能mount数据库。

SQL@ORCL> startup

ORA-00205: error in identifying controlfile, check alert log for more info

查看报警日志文件，有如下信息

alter database mount

Mon May 26 11:59:52 2003

ORA-00202: controlfile: '/opt/oracle/oradata/orcl /control01.ctl'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

2、停止数据库

TEST@ORCL> shutdown immediate

3、拷贝一个好的控制文件替换坏的控制文件或修改init.ora中的控制文件参数，取消这个坏的控制文件。

4、重新启动数据

TEST@ORCL> startup

说明：

1、损失单个控制文件是比较简单的，因为数据库中所有的控制文件都是镜相的，只需要简单的拷贝一个好的就可以了。

2、建议镜相控制文件在不同的磁盘上。

3、建议多做控制文件的备份，长期保留一份由alter database backup control file to trace产生的控制文件的文本备份。

2.) 损坏全部控制文件

损坏多个控制文件，或者人为的删除了所有的控制文件，通过控制文件的复制已经不能解决问题，这个时候需要重新建立控制文件。同时注意，alter database backup control file to trace可以产生一个控制文件的文本备份。以下是详细重新创建控制文件的步骤

1、关闭数据库

TEST@ORCL> shutdown immediate;

2、删除所有控制文件，模拟控制文件的丢失。

3、启动数据库，出现错误，并不能启动到mount下

TEST@ORCL> startup

ORA-00205: error in identifying controlfile, check alert log for more info

查看报警日志文件，有如下信息:

Tue Nov 29 16:02:42 2011

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/opt/oracle/flash_recovery_area/orcl/control02.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/opt/oracle/oradata/orcl/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Tue Nov 29 16:02:44 2011

Checker run found 2 new persistent data failures

ORA-205 signalled during: ALTER DATABASE MOUNT...

4、关闭数据库

TEST@ORCL> shutdown immediate;

5、在internal或sys下运行如下创建控制文件的脚本，注意完整列出联机日志或数据文件的路径，或修改由alter database backup control file to trace备份控制文件时产生的脚本，去掉多余的注释即可。

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 '/opt/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/opt/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/opt/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/opt/oracle/oradata/orcl/system01.dbf',

'/home/oracle/oradata/sysaux01.dbf',

'/opt/oracle/oradata/orcl/undotbs01.dbf',

'/home/oracle/oradata/users01.dbf',

'/home/oracle/oradata/users02.dbf',

'/home/oracle/oradata/undotbs02.dbf'

CHARACTER SET ZHS16GBK;

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/temp01.dbf' SIZE 3408704 REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

或者：

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL

-- statement will place the database in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 '/opt/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/opt/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/opt/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/opt/oracle/oradata/orcl/system01.dbf',

'/home/oracle/oradata/sysaux01.dbf',

'/opt/oracle/oradata/orcl/undotbs01.dbf',

'/home/oracle/oradata/users01.dbf',

'/home/oracle/oradata/users02.dbf',

'/home/oracle/oradata/undotbs02.dbf'

CHARACTER SET ZHS16GBK;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/temp01.dbf'

SIZE 30408704 REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

6、ORA-00283和ORA-01610

idle> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

idle> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: 文件 1 需要介质恢复

ORA-01110: 数据文件 1: '/opt/oracle/oradata/orcl/system01.dbf'

idle> recover datafile 1;

ORA-00283: 恢复会话因错误而取消

ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成

idle> recover database using backup controlfile until cancel;

ORA-00279: 更改 21081244 (在 11/29/2011 15:39:54 生成) 对于线程 1 是必需的

ORA-00289: 建议: /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc

ORA-00280: 更改 21081244 (用于线程 1) 在序列 #2 中

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: 无法打开归档日志 '/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc'

ORA-27037: 无法获得文件状态

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: 无法打开归档日志 '/opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc'

ORA-27037: 无法获得文件状态

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误

ORA-01194: 文件 1 需要更多的恢复来保持一致性

ORA-01110: 数据文件 1: '/opt/oracle/oradata/orcl/system01.dbf'

这里有几个选项：

suggested:在上面ORA-00289: 建议:....会按这个文件去恢复

filename:自己指定日志文件

auto:自动选择，个人觉得好像和suggestted一样。

cancel:这个可以不从介质恢复。

这里使用suggested和auto都报错，因为归档日志已经不存在。查看联机日志文件：

idle> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

------ ------- --------- ------------ --------- ------- --- ---------------- ------------- -------------- -------------------- ----------

1 1 0 52,428,800 512 1 YES UNUSED 0 0

3 1 0 52,428,800 512 1 YES CURRENT 0 0

2 1 0 52,428,800 512 1 YES UNUSED 0 0

发现这里的理解有问题，因为采用的是resetlogs，所以日志文件肯定是unused的。如果在线日志未损坏，则可以指定在线日志文件执行恢复。

idle> recover database using backup controlfile until cancel;

ORA-00279: 更改 21081244 (在 11/29/2011 15:39:54 生成) 对于线程 1 是必需的

ORA-00289: 建议: /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc

ORA-00280: 更改 21081244 (用于线程 1) 在序列 #2 中

Specify log: {=suggested | filename | AUTO | CANCEL}

/opt/oracle/oradata/orcl/redo01.log

ORA-00339: 归档日志未包含任何重做

ORA-00334: 归档日志: '/opt/oracle/oradata/orcl/redo01.log'

ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误

ORA-01194: 文件 1 需要更多的恢复来保持一致性

ORA-01110: 数据文件 1: '/opt/oracle/oradata/orcl/system01.dbf'

idle> recover database using backup controlfile until cancel;

ORA-00279: 更改 21081244 (在 11/29/2011 15:39:54 生成) 对于线程 1 是必需的

ORA-00289: 建议: /opt/oracle/flash_recovery_area/ORCL/archivelog/2011_11_29/o1_mf_1_2_%u_.arc

ORA-00280: 更改 21081244 (用于线程 1) 在序列 #2 中

Specify log: {=suggested | filename | AUTO | CANCEL}

/opt/oracle/oradata/orcl/redo02.log

Log applied.

Media recovery complete.

idle> alter database open resetlogs;

Database altered.

sys@ORCL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND OFF;

Tablespace altered.

7、如果没有错误，数据库将启动到open状态下。

说明：

1、重建控制文件用于恢复全部数据文件的损坏，需要注意其书写的正确性，保证包含了所有的数据文件与联机日志。

2、经常有这样一种情况，因为一个磁盘损坏，我们不能再恢复(store)数据文件到这个磁盘，因此在store到另外一个盘的时候，我们就必须重新创建控制文件，用于识别这个新的数据文件，这里也可以用这种方法用于恢复。

二、其它恢复案例

1、flashback table

1.Flashback Table语法

FLASHBACK TABLE tablename TO TIMESTAMP to_timestamp('2011-05-07 08:23:48','yyyy-mm-dd hh24:mi:ss');

FLASHBACK TABLE employee TO SCN 123456;

FLASHBACK TABLE t1 TO TIMESTAMP to_timestamp('2011-05-07 08:23:48','yyyy-mm-dd hh24:mi:ss'); ENABLE TRIGGERS;

2.闪回的前提条件

需要有flashback any table的系统权限或者是该表的flashback对象权限。

需要有该表的SELECT, INSERT, DELETE, ALTER权限

必须保证该表ROW MOVEMENT

3.验内容及目的

l 使用flashback table闪回到之前某个时间点。

l 验证flashback table不能够闪回被drop掉的索引。

l 验证flashback table不能够闪回truncate的表内容。

4.创建测试表

test@ORCL> set time on

15:18:07 test@ORCL> create table t1 as select * from dba_objects;

Table created.

15:19:10 test@ORCL> create table t2 as select * from t1;

Table created.

15:19:46 test@ORCL> select count(*) from t1;

COUNT(*)

----------

73861

15:20:19 test@ORCL> select count(*) from t2;

COUNT(*)

----------

73861

15:23:01 test@ORCL> create index inx_test1 on t1(object_name);

Index created.

15:23:07 test@ORCL> create index inx_test2 on t1(object_id);

Index created.

15:23:55 test@ORCL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2011-11-30 15:23:56

5.模拟删除

删除表的索引，delete方式删除数据，truncate方式删除数据。

15:26:34 test@ORCL> drop index inx_test1;

Index dropped.

15:26:36 test@ORCL> delete from t1;

73861 rows deleted.

15:26:54 test@ORCL> commit;

Commit complete.

15:28:02 test@ORCL> truncate table t2;

Table truncated.

15:28:13 test@ORCL> select count(*) from t1;

COUNT(*)

----------

0

15:28:21 test@ORCL> select count(*) from t2;

COUNT(*)

----------

0

6.Flashback Table闪回测试

15:28:28 test@ORCL> alter table t1 enable row movement;

Table altered.

15:30:55 test@ORCL> alter table t2 enable row movement;

Table altered.

15:32:41 test@ORCL> flashback table t1 to timestamp to_timestamp('2011-11-30 15:26:36','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

15:33:53 test@ORCL> flashback table t2 to timestamp to_timestamp('2011-11-30 15:28:02','yyyy-mm-dd hh24:mi:ss');

flashback table t2 to timestamp to_timestamp('2011-11-30 15:28:02','yyyy-mm-dd hh24:mi:ss')

*

ERROR at line 1:

ORA-01466: 无法读取数据 - 表定义已更改 ----说明truncate的数据无法flashback。

15:35:19 test@ORCL> select count(*) from t1;

COUNT(*)

----------

73861

15:35:30 test@ORCL> select count(*) from t2;

COUNT(*)

----------

0

15:36:54 test@ORCL> select index_name from user_indexes where table_name = 'T1';

INDEX_NAME

------------------------------

INX_TEST2 --- 说明drop的索引无法flashback。

7.小结

l 使用flashback table可以将delete方式删除的表闪回到之前某个时间点。

l flashback table功能不能够将被drop掉的索引恢复出来，因为drop索引的过程属于DDL操作，不记录undo信息。

l flashback table功能不能够将被truncate的表内容恢复出来，原因也是truncate操作过程是不记录undo信息。

8. Flashback Versions Query

从oracle 10g开始，Flashback Technologies有了很大的改进，其中的Flashback Versions Query技术可以让你轻松查看到两个时间点或scn点之间的同一数据的变动情况(必须是在flashback范围以内)。例如下面的语句可以轻松查看t_dept表中09:00:00到09:16:00之间的数据变化情况：

SQL> SELECT * FROM mis.t_sys_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

--------------------------------------------------------------------------------------------------------------------------------

deptid deptcode deptname deptlevel terminated parent flag manager short depttype sort u8code

5522 101004 投资公司 2 0 1 1 5697 0 7 TZ

5522 101004 投资公司 2 0 1 0 5697 0 7 TZ

2 rows selected

SQL> SELECT * FROM mis.t_sys_dept d

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:16:00','yyyy-mm-dd hh24:mi:ss')

WHERE D.DEPTid=5522;

---------------------------------------------------------------------------------------------------------------------------------

VERSIONS BETWEEN TIMESTAMP

*

ERROR AT line 2:

ORA-00933: SQL command NOT properly ended

伪列：

如果使用伪列，则不能使用*来统配表中所有的字段：

SQL> SELECT versions_operation,* FROM oa.t_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

SELECT versions_operation,* FROM oa.t_dept

*

ERROR at line 1:

ORA-00936: missing expression

SQL> SELECT *,versions_operation FROM oa.t_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

SELECT *,versions_operation FROM oa.t_dept

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

使用伪列和字段名：

SQL> SELECT deptid,deptname,versions_operation FROM oa.t_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

DEPTID DEPTNAME V

------------------------------------------------------------------------------------------------------------------------- -

5522 投资公司 U

5522 投资公司

如果要使用*来统配表中所有的字段，则必须在*前加上表名：

SQL> SELECT oa.t_dept.*,versions_operation FROM oa.t_dept

VERSIONS BETWEEN TIMESTAMP

to_timestamp('2011-10-26 09:00:00','yyyy-mm-dd hh24:mi:ss') AND

to_timestamp('2011-10-26 09:26:00','yyyy-mm-dd hh24:mi:ss')

WHERE DEPTid=5522;

---------------------------------------------------------------------------------------------------------------------------------

deptid deptcode deptname deptlevel terminated parent flag manager short depttype sort u8code

5522 101004 投资公司 2 0 1 1 5697 0 7 TZ

5522 101004 投资公司 2 0 1 0 5697 0 7 TZ

2 rows selected

注：关于Flashback Pseudocolumns介绍

VERSIONS_STARTSCN

Starting SCN when the row was first created. This identifies the SCN when the data first took on the values displayed in the row version.If NULL, the row version was created before the lower time bound of the query BETWEEN clause.

VERSIONS_STARTTIME

Starting TIMESTAMP when the row version was first created. This identifies the time when the data first took on the values displayed in the row version. If NULL, the row version was created before the lower time bound of the query BETWEEN clause.

VERSIONS_ENDSCN

Ending SCN when the row version expired. This identifies the row expiration SCN. If NULL, then either the row version is still current or the row corresponds to a DELETE operation.

VERSIONS_ENDTIME

Ending TIMESTAMP when the row version expired. This identifies the row expiration time. If NULL, then either the row version is still current or the row corresponds to a DELETE operation.

VERSIONS_XID

Identifier of the transaction that created the row version.

VERSIONS_OPERATION

This is the operation performed by the transaction that modified the data. The values are I for insertion, D for deletion, or U for update.

2、flashback database

闪回技术通常用于快速简单恢复数据库中出现的认为误操作等逻辑错误，从闪回的方式可以分为基于数据库级别闪回、表级别闪回、事务级别闪回，根据闪回对数据的影响程度又可以分为闪回恢复，闪回查询。闪回恢复将修改数据，闪回点之后的数据将全部丢失。而闪回查询则可以查询数据被DML的不同版本，也可以在此基础之上确定是否进行恢复等。本文主要描述flashback database的使用。

1、flashback database特性

l flashback database闪回到过去的某一时刻。

l 闪回点之后的工作全部丢失。

l 使用resetlogs创建新的场景并打开数据库(一旦resetlogs之后，将不能再flashback至resetlogs之前的时间点)。

常用的场景

l truncate table恢复。

l 多表发生意外错误需要恢复。

l 使用闪回日志来实现数据库闪回，闪回点之后的数据将丢失。

2、flashback database的组成

闪回缓冲区:当启用flashback database,则sga中会开辟一块新区域作为闪回缓冲区,大小由系统分配

启用新的rvwr进程:rvwr进程将闪回缓冲区的内容写入到闪回日志中，注意闪回日志不同于联机重做日志，闪回日志在联机重做日志基础之上生成，是完整数据块映像的日志。联机日志则是变化的日志。闪回日志不能复用，也不能归档。闪回日志使用循环写方式。

3、flashback database的配置

flashback database要求数据库必须处于归档模式，且闪回之后必须使用resetlogs打开数据库。

a. 查看数据库的归档模式及闪回是否启用

test@ORCL> select log_mode,open_mode,flashback_on from v$database;

LOG_MODE OPEN_MODE FLASHBACK_ON

------------ -------------------- ------------------

ARCHIVELOG READ WRITE NO --FLASHBACK_ON为NO，则表示闪回特性尚未启用。

b.查看及设置闪回目录、闪回目录空间大小等

--可以使用alter system set db_recovery_file_dest 来设置新路径

--可以使用alter system set db_recovery_file_dest_size来设定新的大小

test@ORCL> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /opt/oracle/flash_recovery_area

db_recovery_file_dest_size big integer 3882M

c.设置闪回保留目标生存期

test@ORCL> show parameter db_flashback

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target integer 1440 --缺省为分钟，即24小时。

test@ORCL> alter system set db_flashback_retention_target=30;

System altered. --设定保留时间为半小时

d.启用flashback

如果是oracle 10g，必须在mount状态下开启，否则会报错。Oracle 10g以后可以在open状态直接打开。

sys@ORCL> conn / as sysdba

Connected.

sys@ORCL> alter database flashback on;

Database altered.

sys@ORCL> ho ps -ef|grep rvw --可以看到新增了后台进程rvwr

oracle 5212 1 0 14:00 ? 00:00:00 ora_rvwr_orcl

oracle 6167 440 0 14:03 pts/1 00:00:00 /bin/bash -c ps -ef|grep rvw

--下面查看闪回区分配的大小为大约M，闪回分钟以内的数据则需要M左右的空间

--注意列oldest_flashback_time说明了允许返回的最早的时间点

test@ORCL> select oldest_flashback_scn old_flhbck_scn,oldest_flashback_time old_flhbck_tim,retention_target rete_trgt,flashback_size/1024/1024 flhbck_siz, estimated_flashback_size/1024/1024 est_flhbck_size from v$flashback_database_log;

OLD_FLHBCK_SCN OLD_FLHBCK_TIM RETE_TRGT FLHBCK_SIZ EST_FLHBCK_SIZE

-------------- -------------- ---------- ---------- ---------------

21298440 01-12月-11 30 7.8125 0

test@ORCL> select * from v$flashback_database_stat; --查看闪回

BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE

-------------- -------------- -------------- ---------- ---------- ------------------------

01-12月-11 01-12月-11 2859008 3702784 761856 0

test@ORCL> select * from v$sgastat where name like 'flashback%'; --查看sga中分配的闪回空间大小

POOL NAME BYTES

------------ ---------------------------------------- ------------

shared pool flashback generation buff 3,981,120

shared pool flashback_marker_cache_si 9,200

test@ORCL> ho ls -hlt $ORACLE_BASE/flash_recovery_area/ORCL/flashback --查看生成的闪回日志

total 7.9M

-rw-r----- 1 oracle oinstall 7.9M Dec 1 14:08 o1_mf_7fg603wq_.flb

test@ORCL> select * from v$flash_recovery_area_usage; --查看闪回空间使用情况

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

-------------------- ------------------ ------------------------- ---------------

CONTROL FILE 0 0 0

REDO LOG 0 0 0

ARCHIVED LOG 7.05 0 7

BACKUP PIECE 0 0 0

IMAGE COPY 0 0 0

FLASHBACK LOG .2 0 1

FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

4、模拟闪回数据库

1.模拟用户错误

sys@ORCL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 8

Next log sequence to archive 10

Current log sequence 10

sys@ORCL> select sysdate from dual;

SYSDATE

-------------------

2011-12-01 14:55:02

sys@ORCL> drop table test.t3;

Table dropped.

SYS AS SYSDBA on 2005-03-29 17:02:22 >select sysdate from dual;

SYSDATE

-------------------

2011-12-01 14:56:03

14:57:48 sys@ORCL> create table test.t1 as select * from dba_users;

Table created.

14:59:20 sys@ORCL> select sysdate from dual;

SYSDATE

-------------------

2011-12-01 14:59:26

15:00:33 sys@ORCL> create table test.t2 as select * from dba_tablespaces;

Table created.

15:00:55 sys@ORCL> select sysdate from dual;

SYSDATE

-------------------

2011-12-01 15:01:01

2.进行闪回操作

15:07:02 sys@ORCL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

15:09:08 sys@ORCL> startup mount;

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 251661728 bytes

Database Buffers 4194304 bytes

Redo Buffers 4980736 bytes

Database mounted.

15:09:23 sys@ORCL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

15:10:20 sys@ORCL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE

-------------------- ------------------- ---------------- -------------- ------------------------

21298440 2011-12-01 14:00:58 30 12288000 4339712

15:13:29 sys@ORCL> flashback database to timestamp to_timestamp ('2011-12-01 14:56:03','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

3.只读打开，验证数据

15:14:25 sys@ORCL> alter database open read only;

Database altered.

15:14:36 sys@ORCL> select * from test.t3;

select * from test.t3

*

ERROR at line 1:

ORA-00942: 表或视图不存在

4.继续修正恢复

15:15:07 sys@ORCL> alter database close;

Database altered.

15:16:05 sys@ORCL> flashback database to timestamp to_timestamp ('2011-12-01 14:36:03','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

15:16:12 sys@ORCL> alter database open;

alter database open

*

ERROR at line 1:

ORA-16196: 以前曾打开和关闭过数据库

15:16:41 sys@ORCL> alter database dismount;

Database altered.

15:17:10 sys@ORCL> shutdown immediate

ORA-01507: 未装载数据库

ORACLE instance shut down.

15:17:59 sys@ORCL> startup mount

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 251661728 bytes

Database Buffers 4194304 bytes

Redo Buffers 4980736 bytes

Database mounted.

15:18:47 sys@ORCL> alter database open read only;

Database altered.

15:19:31 sys@ORCL> select * from test.t3;

no rows selected ---test.t3表已经成功找回。

5.resetlogs打开数据库

15:21:40 sys@ORCL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

15:21:51 sys@ORCL> startup mount;

ORACLE instance started.

Total System Global Area 263049216 bytes

Fixed Size 2212448 bytes

Variable Size 239078816 bytes

Database Buffers 16777216 bytes

Redo Buffers 4980736 bytes

Database mounted.

15:22:08 sys@ORCL> alter database open resetlogs;

Database altered.

注意：一旦resetlogs之后，将不能再flashback至resetlogs之前的时间点。

3、imp恢复

1、导入类型

1、导入表

imp scott/tiger TABLES=(dept,emp) file=/opt/oracle/tab.dmp log=/opt/oracle/tab.log

2、导入方案

imp scott/tiger SCHEMAS=scott file=/opt/oracle/tab.dmp log=/opt/oracle/tab.log

3、导入表空间

imp system/manager TABLESPACES=user01 file=/opt/oracle/tab.dmp log=/opt/oracle/tab.log

4、导入数据库

imp system/manager FULL=y file=/opt/oracle/tab.dmp log=/opt/oracle/tab.log

2、 imp 选项

E:\>imp help=y

可以通过输入 IMP 命令和您的用户名/口令

跟有您的用户名 / 口令的命令:

实例: IMP SCOTT/TIGER

或者, 可以通过输入 IMP 命令和各种自变量来控制“导入”按照不同参数。

要指定参数，您可以使用关键字:

格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,...,vlaueN)

实例: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

或 TABLES=(T1: P1,T1: P2)，如果 T1 是分区表

USERID 必须是命令行中的第一个参数。

关键字说明（默认）

----------------------------------------------

USERID 用户名/口令

FULL 导入整个文件 (N)

BUFFER 数据缓冲区大小

FROMUSER 所有人用户名列表

FILE 输入文件 (EXPDAT.DMP)

TOUSER 用户名列表

SHOW 只列出文件内容 (N)

TABLES 表名列表

IGNORE 忽略创建错误 (N)

RECORDLENGTH IO记录的长度

GRANTS 导入权限 (Y)

INCTYPE 增量导入类型

INDEXES 导入索引 (Y)

COMMIT 提交数组插入 (N)

ROWS 导入数据行 (Y)

PARFILE 参数文件名

LOG 屏幕输出的日志文件

CONSTRAINTS 导入限制 (Y)

DESTROY 覆盖表空间数据文件 (N)

INDEXFILE 将表/索引信息写入指定的文件

ANALYZE 执行转储文件中的 ANALYZE 语句 (Y)

FEEDBACK 显示每 x 行 (0) 的进度

TOID_NOVALIDATE 跳过指定类型 id 的校验

FILESIZE 各转储文件的最大尺寸

RESUMABLE 在遇到有关空间的错误时挂起 (N)

RESUMABLE_NAME 用来标识可恢复语句的文本字符串

RESUMABLE_TIMEOUT RESUMABLE 的等待时间

COMPILE 编译过程, 程序包和函数 (Y)

STREAMS_CONFIGURATION 导入 Streams 的一般元数据 (Y)

STREAMS_INSTANITATION 导入 Streams 的实例化元数据 (N)

RECALCULATE_STATISTICS 重新计算统计值 (N)

SKIP_UNUSABLE_INDEXES 跳过不可用索引的维护 (N)

下列关键字仅用于可传输的表空间

TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)

TABLESPACES 将要传输到数据库的表空间

DATAFILES 将要传输到数据库的数据文件

TTS_OWNERS 拥有可传输表空间集中数据的用户

【听海日志】之ORACLE恢复案例

一、数据库恢复案例

1、丢失或损坏一个数据文件

1.) 连接数据库，创建测试表并插入记录

2.) 备份数据库表空间users

3.) 继续在测试表中插入记录

4.) 关闭数据库，模拟丢失数据文件

5.) 启动数据库，检查错误

6.) 先打开数据库

7.) 恢复该数据文件表空间

8.) 检查数据是否完整

2、多数据文件丢失或损坏

1.) 连接数据库，创建测试表并插入记录

2.) 备份数据库

3.) 继续在测试表中插入记录

4.) 关闭数据库，模拟丢失数据文件

5.) 启动数据库，检查错误

6.) 利用RMAN进行恢复

7.) 检查数据库的数据（完全恢复）

3、基于时间的不完全恢复案例

1.) 连接数据库，创建测试表并插入记录

2.) 备份数据库(最好备份所有的数据文件，包括临时数据文件)

3.) 删除测试表

4.) 准备恢复到时间点

5.) restore所有的数据文件

6.) 开始不完全恢复

7.) 打开数据库检查数据

4、基于SCN的不完全恢复案例

1.) 连接数据库，创建测试表并插入记录

2.) 备份数据库

3.) 删除测试表

4.) 准备恢复数据库

5.) 开始恢复到改变点

6.) 检查数据

5、损坏联机日志的恢复方法

1.) 损坏非当前联机日志

2.) 损坏当前联机日志

a.) 通过备份来恢复

b.) 如果没有备份，进行强制性恢复

6、损坏控制文件的恢复方法

1.) 损坏单个控制文件

2.) 损坏全部控制文件

二、其它恢复案例

1、flashback table

1.Flashback Table语法

2.闪回的前提条件

3.验内容及目的

4.创建测试表

5.模拟删除

6.Flashback Table闪回测试

7.小结

8. Flashback Versions Query

2、flashback database

1、flashback database特性

2、flashback database的组成

3、flashback database的配置

a. 查看数据库的归档模式及闪回是否启用

b.查看及设置闪回目录、闪回目录空间大小等

c.设置闪回保留目标生存期

d.启用flashback

4、模拟闪回数据库

1.模拟用户错误

2.进行闪回操作

3.只读打开，验证数据

4.继续修正恢复

5.resetlogs打开数据库

3、imp恢复

1、导入类型

1、导入表

2、导入方案

3、导入表空间

4、导入数据库

2、 imp 选项