oracle在非归档模式下,oracle教程之oralce非归档模式下的恢复（一）

案例1： 历史日志没有被覆盖(可以完全恢复)

1)切换到非归档模式

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /disk1/arch/anny

Oldest online log sequence     7

Next log sequence to archive   10

Current log sequence           10

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1279964 bytes

Variable Size              58722340 bytes

Database Buffers          251658240 bytes

Redo Buffers                2912256 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /disk1/arch/anny

Oldest online log sequence     7

Current log sequence           10

SQL> !

[oracle@solaris10 ~]$rm /disk1/backup/anny/cold_bak/*

[oracle@solaris10 ~]$rm /disk1/arch/anny/*

[oracle@solaris10 ~]$exit

exit

2)重新做数据库的全备(一致性备份-冷备份)

-------备份所有的datafile 和 controlfile

SQL> @/export/home/oracle/sqlplus/anny_cold_bak ——执行冷备份脚本

3)模拟环境

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS     FIRST_CHANGE# FIRST_TIME

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

1          1         10  104857600          3 NO        CURRENT           845923 2012-03-22 14:53:47

4          1          8  104857600          3 YES       INACTIVE          801096 2012-03-21 18:22:29

3          1          9  104857600          3 YES       INACTIVE          821972 2012-03-22 11:42:07

2          1          7  104857600          3 YES       INACTIVE          779349 2012-03-21 17:13:00

4 rows selected.

SQL> select * from scott.tb01;

ID

----------

111

222

333

3 rows selected.

SQL> insert into scott.tb01 values (444);

1 row created.

SQL> insert into scott.tb01 values (555);

1 row created.

SQL> insert into scott.tb01 values (666);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS     FIRST_CHANGE# FIRST_TIME

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

1          1         10  104857600          3 NO        CURRENT           845923 2012-03-22 14:53:47

4          1          8  104857600          3 YES       INACTIVE          801096 2012-03-21 18:22:29

3          1          9  104857600          3 YES       INACTIVE          821972 2012-03-22 11:42:07

2          1          7  104857600          3 YES       INACTIVE          779349 2012-03-21 17:13:00

4 rows selected.

SQL> select segment_name,tablespace_name from dba_segments where segment_name='TB01';

SEGMENT_NAME    TABLESPACE_NAME

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

TB01            TEST

1 row selected.

SQL> SHUTDOWN ABORT

ORACLE instance shut down.

4) 删除数据文件

[oracle@solaris10 ~]$rm /u01/app/oracle/oradata/anny/test*.dbf

5)启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  1279964 bytes

Variable Size              58722340 bytes

Database Buffers          251658240 bytes

Redo Buffers                2912256 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/app/oracle/oradata/anny/test01.dbf'

SQL> select file#,error from V$recover_file;

FILE# ERROR

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

5 FILE NOT FOUND

11 FILE NOT FOUND

6)恢复

——restore datafile

[oracle@solaris10 ~]$cp /disk1/backup/anny/cold_bak/test*.dbf /u01/app/oracle/oradata/anny/

——recover datafile

SQL> recover datafile 5,11

Media recovery complete.

告警日志信息：

ALTER DATABASE RECOVER  datafile 5,11

Thu Mar 22 15:21:26 2012

Media Recovery Start

Thu Mar 22 15:21:26 2012

Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0

Mem# 0 errs 0: /disk3/oradata/anny/redo01a.log

Mem# 1 errs 0: /disk1/oradata/anny/redo01b.log

Mem# 2 errs 0: /disk2/oradata/anny/redo01c.log

Thu Mar 22 15:21:27 2012

Media Recovery Complete (anny)

Completed: ALTER DATABASE RECOVER  datafile 5,11

5)验证

SQL> alter database open;

Database altered.

SQL> select * from scott.tb01;

ID

----------

444

555

666

111

222

333

6 rows selected.——完全恢复