用冷备份+归档日志执行不完全恢复一例

本文可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本声明

http://blog.itpub.net/post/11/3523

用冷备份+归档日志执行不完全恢复一例

windows 2000

oracle 10.1.0.2

有冷备份,归档日志,当前的控制文件和联机重做日志均已丢失.需要把数据库恢复到指定的时间点上.

SQL> select *from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

PL/SQL Release 10.1.0.2.0 - Production

CORE 10.1.0.2.0 Production

TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

NLSRTL Version 10.1.0.2.0 - Production

SQL>

SQL> set time on

14:00:33 SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

14:00:57 SQL>

-- 在这里进行冷备份

-- 重新启动数据库,输入测试数据

14:00:57 SQL> set timing on

14:06:09 SQL> startup

ORACLE 例程已经启动。

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

数据库装载完毕。

数据库已经打开。

14:06:31 SQL> conn test/test

已连接。

14:06:38 SQL> create table t2 (n1 number,n2 number);

表已创建。

已用时间: 00: 00: 00.81

14:06:57 SQL> insert into t2 values(1,1);

已创建 1 行。

已用时间: 00: 00: 00.01

14:07:07 SQL> insert into t2 values(2,2);

已创建 1 行。

已用时间: 00: 00: 00.01

14:07:11 SQL> commit;

提交完成。

已用时间: 00: 00: 00.09

14:07:12 SQL>

-- 执行日志切换

-- 在14:10分数据库数据文件/控制文件/在线重做日志全部丢失,

SQL> startup

ORACLE instance started.

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

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

SQL>

-- alert.log日志中的错误信息

ALTER DATABASE MOUNT

Thu Oct 21 14:13:52 2004

ORA-00202: ????: 'F:ORACLEPRODUCT10.1.0ORADATATESTCONTROL01.CTL'

ORA-27041: ??????

OSD-04002: 无法打开文件

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

Thu Oct 21 14:13:52 2004

Controlfile identified with block size 0

Thu Oct 21 14:13:55 2004

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

-- 现需要用冷备份+归档日志把数据库恢复到14:08:00

以下为操作步骤:

1. 还原数据文件冷备份(注意:无需还原冷备份备份的控制文件和联机重做日志文件)

2. 创建控制文件

D:>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

D:>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Oct 21 14:13:36 2004

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 454

7 LOGFILE

8 GROUP 1 'F:ORACLEPRODUCT10.1.0ORADATATESTREDO01.LOG' SIZE 10M,

9 GROUP 2 'F:ORACLEPRODUCT10.1.0ORADATATESTREDO02.LOG' SIZE 10M,

10 GROUP 3 'F:ORACLEPRODUCT10.1.0ORADATATESTREDO03.LOG' SIZE 10M

11 -- STANDBY LOGFILE

12 DATAFILE

13 'F:ORACLEPRODUCT10.1.0ORADATATESTSYSTEM01.DBF',

14 'F:ORACLEPRODUCT10.1.0ORADATATESTUNDOTBS01.DBF',

15 'F:ORACLEPRODUCT10.1.0ORADATATESTSYSAUX01.DBF',

16 'F:ORACLEPRODUCT10.1.0ORADATATESTUSERS01.DBF',

17 'F:ORACLEPRODUCT10.1.0ORADATATESTTEST.DBF'

18 CHARACTER SET ZHS16GBK

19 ;

Control file created.

SQL>

3. 执行数据库恢复

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE until time '2004-10-21 14:08:00';

ORA-00279: change 423708 generated at 10/21/2004 14:00:53 needed for thread 1

ORA-00289: suggestion : E:ARCHIVETESTTEST_18_1_539975300.ARC

ORA-00280: change 423708 for thread 1 is in sequence #18

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

auto

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

4. 检验恢复是否成功

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination e:archivetest

Oldest online log sequence 0

Next log sequence to archive 1

Current log sequence 1

SQL> conn test/test

Connected.

SQL> select *From t2;

N1 N2

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

1 1

2 2

SQL>

至此,成功完成数据库不完全恢复.

ITPub博客

用冷备份+归档日志执行不完全恢复一例

最新文章