阿共仔上岸了:记一次数据库恢复步骤

来源：百度文库编辑：九乡新闻网时间：2024/05/01 02:55:56

记一次数据库恢复步骤2011-01-28 10:41阅读(228)

环境：
OS:AIX 5.3
DB:10.2.0.2.0

现象：
数据库无法open
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 1: '/data01/oradata/HZAVMES/system01.dbf'

分析原因：
此数据库是韩国工厂一个生产数据库，每周一晚上1:00要做一个次热备份，方法大致如下：

1.shutdown abort database and startup
SQL>shutdown abort
SQL>startup

2.shutdown immediate database again
SQL>shutdown immediate

3.copy all datafiles to tape
COPY datafile with netbackup(NBU) to tape

4.startup database again
SQL>startup

1.alter all tablespaces to begin backup mode
SQL>alter tablespace SYSTEM begin backup;
....
SQL>alter tablespace DAVMD begin backup;

但由于数据库现在数据量很大（500G），每次热备的时间很长，值班人员在早上8点，数据库自动备份没有完成时（在第4步时）就没手工把数据作业停掉了，
于是再启就出现上面数据库无法打开的问题。

问题是，当数据库在shutdown abort之前，有人把表空间置了热备模式，而一直没有结束
SQL>alter tablespace SYSTEM begin backup;
....
SQL>alter tablespace DAVMD begin backup;

这样在数据库abort之后，再重启时需要介质恢复。

解决步骤：
1.把表空间的置回end backup模式
2.把begin backup之后生成的归档日志拷回到原来的/ARCHIVE目录。
3.恢复数据数据库。
4.重建控制文件。
5.打开数据库。

1.把表空间的置回end backup模式
SQL>startup mount;
SQL>alter tablespace SYSTEM end backup;
SQL>alter tablespace UNDOTBS1 end backup;
SQL>alter tablespace SYSAUX end backup;
SQL>alter tablespace USERS end backup;
SQL>alter tablespace DAVMD end backup;
SQL>alter tablespace DAVMX end backup;
SQL>alter tablespace TOOLS end backup;

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -----------
1 NOT ACTIVE 5352806612 2010-10-4 0
2 NOT ACTIVE 5352806612 2010-10-4 0
3 NOT ACTIVE 5352806612 2010-10-4 0
4 NOT ACTIVE 5352806612 2010-10-4 0
5 NOT ACTIVE 5352806612 2010-10-4 0
6 NOT ACTIVE 5352806612 2010-10-4 0
7 NOT ACTIVE 5352806612 2010-10-4 0
8 NOT ACTIVE 5352806612 2010-10-4 0
9 NOT ACTIVE 5352806612 2010-10-4 0
10 NOT ACTIVE 5352806612 2010-10-4 0
11 NOT ACTIVE 5352806612 2010-10-4 0
12 NOT ACTIVE 5352806612 2010-10-4 0
13 NOT ACTIVE 5352806612 2010-10-4 0
14 NOT ACTIVE 5352806612 2010-10-4 0
15 NOT ACTIVE 5352806612 2010-10-4 0
16 NOT ACTIVE 5352806612 2010-10-4 0
17 NOT ACTIVE 5352806612 2010-10-4 0
18 NOT ACTIVE 5352806612 2010-10-4 0

2.把begin backup之后生成的归档日志拷回到原来的/ARCHIVE目录。
因为每天归档在备份（NBU备份到磁带）后会删除，所以要把begin backup之后生成的归档日志（2010年10月4日到今天）拷回到原来的/ARCHIVE/HZAVMES

3.恢复数据数据库。
SQL> recover database using backup controlfile ;
ORA-00279: change 53530491621520 generated at 10/06/2010 04:52:42 needed for
thread 1
ORA-00289: suggestion : /ARCHIVE01/HZAVMES/HZAVMES_1_39053_595009654.arc
ORA-00280: change 53530491621520 for thread 1 is in sequence #39053

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

ORA-00279: change 53531623397944 generated at 10/07/2010 04:52:18 needed for
thread 1
ORA-00289: suggestion : /ARCHIVE01/HZAVMES/HZAVMES_1_39054_595009654.arc
ORA-00280: change 53531623397944 for thread 1 is in sequence #39054
ORA-00278: log file '/ARCHIVE01/HZAVMES/HZAVMES_1_39053_595009654.arc' no
longer needed for this recovery

.........

alter日志信息：

WARNING! Recovering data file 65 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 66 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 67 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 68 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 69 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 70 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 71 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
parallel recovery started with 7 processes
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...
Mon Oct 11 09:38:36 2010
ALTER DATABASE RECOVER CONTINUE DEFAULT
Mon Oct 11 09:38:36 2010
Media Recovery Log /ARCHIVE01/HZAVMES/HZAVMES_1_38813_595009654.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Mon Oct 11 09:38:45 2010
ALTER DATABASE RECOVER CONTINUE DEFAULT

Mon Oct 11 09:38:45 2010
ALTER DATABASE RECOVER CONTINUE DEFAULT
Mon Oct 11 09:38:45 2010
Media Recovery Log /ARCHIVE01/HZAVMES/HZAVMES_1_38814_595009654.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Mon Oct 11 09:38:51 2010
ALTER DATABASE RECOVER CONTINUE DEFAULT
Mon Oct 11 09:38:51 2010
Media Recovery Log /ARCHIVE01/HZAVMES/HZAVMES_1_38815_595009654.arc
Mon Oct 11 09:38:52 2010
ALTER DATABASE RECOVER database using backup controlfile until cancel

4.重建控制文件。

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> alter database backup controlfile to trace;

Database altered.

SQL> select spid from v$process
2 where addr=(select paddr from v$session
3 where sid=
4 (select sid from v$mystat where rownum=1));

SPID
------------------------
1909444

LGEAIMS:/db/app/oracle/admin/LGEAIMS/udump> ls *1909444*
lgeaims_ora_1909444.trc

在此文件中找出创建控制文件脚本，并执行之：
CREATE CONTROLFILE REUSE DATABASE "HZAVMES" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 (
'/data01/oradata/HZAVMES/redo11.log',
'/data02/oradata/HZAVMES/redo12.log'
) SIZE 50M,
GROUP 6 (
'/data01/oradata/HZAVMES/redo61.log',
'/data02/oradata/HZAVMES/redo62.log'
) SIZE 50M,
GROUP 7 (
'/data01/oradata/HZAVMES/redo71.log',
'/data02/oradata/HZAVMES/redo72.log'
) SIZE 50M,
GROUP 8 (
'/data01/oradata/HZAVMES/redo81.log',
'/data02/oradata/HZAVMES/redo82.log'
) SIZE 50M,
GROUP 9 (
'/data01/oradata/HZAVMES/redo91.log',
'/data02/oradata/HZAVMES/redo92.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data01/oradata/HZAVMES/system01.dbf',

'/data04/oradata/HZAVMES/davmd_43.dbf',
'/data04/oradata/HZAVMES/davmd_44.dbf',
'/data03/oradata/HZAVMES/davmd_45.dbf',
'/data02/oradata/HZAVMES/davmd_46.dbf',
'/data04/oradata/HZAVMES/davmd_47.dbf',
'/data03/oradata/HZAVMES/davmd_48.dbf',
'/data04/oradata/HZAVMES/davmd_49.dbf',
'/data04/oradata/HZAVMES/davmd_50.dbf',
'/data02/oradata/HZAVMES/davmx_14.dbf'
CHARACTER SET UTF8
Mon Oct 11 12:01:37 2010
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Setting recovery target incarnation to 1
Mon Oct 11 12:01:40 2010
Successful mount of redo thread 1, with mount id 2803192161
Mon Oct 11 12:01:40 2010
Completed: CREATE CONTROLFILE REUSE DATABASE "HZAVMES" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 (
'/data01/oradata/HZAVMES/redo11.log',
'/data02/oradata/HZAVMES/redo12.log'
) SIZE 50M,
GROUP 6 (
'/data01/oradata/HZAVMES/redo61.log',
'/data02/oradata/HZAVMES/redo62.log'
) SIZE 50M,
GROUP 7 (
'/data01/oradata/HZAVMES/redo71.log',
'/data02/oradata/HZAVMES/redo72.log'
) SIZE 50M,
GROUP 8 (
'/data01/oradata/HZAVMES/redo81.log',
'/data02/oradata/HZAVMES/redo82.log'
) SIZE 50M,
GROUP 9 (
'/data01/oradata/HZAVMES/redo91.log',
'/data02/oradata/HZAVMES/redo92.log'
) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data01/oradata/HZAVMES/system01.dbf',
'/data01/oradata/HZAVMES/undotbs01.dbf',
'/data01/oradata/HZAVMES/sysaux01.dbf',
'/data01/oradata/HZAVMES/users01.dbf',
'/data01/oradata/HZAVMES/davmd_01.dbf',
'/data02/oradata/HZAVMES/davmd_02.dbf',
'/data03/oradata/HZAVMES/davmd_03.dbf',
'/data01/oradata/HZAVMES/davmx_01.dbf',
'/data02/oradata/HZAVMES/davmx_02.dbf',
'/data03/oradata/HZAVMES/davmx_03.dbf',
'/data03/oradata/HZAVMES/davmd_04.dbf',
'/data03/oradata/HZAVMES/tools01.dbf',
'/data02/oradata/HZAVMES/davmd_05.dbf',
'/data03/oradata/HZAVMES/davmd_06.dbf',
'/data02/oradata/HZAVMES/davmd_07.dbf',
'/data03/oradata/HZAVMES/davmd_08.dbf',
'/data03/oradata/HZAVMES/davmd_09.dbf',
'/data01/oradata/HZAVMES/davmx_04.DBF',
'/data03/oradata/HZAVMES/davmd_10.dbf',
'/data01/oradata/HZAVMES/davmd_11.dbf',
'/data02/oradata/HZAVMES/davmd_12.dbf',
'/data02/oradata/HZAVMES/davmd_13.dbf',
'/data02/oradata/HZAVMES/davmx_05.dbf',
'/data01/oradata/HZAVMES/davmd_14.dbf',
'/data03/oradata/HZAVMES/davmd_15.dbf',
'/data01/oradata/HZAVMES/davmx_06.dbf',
'/data01/oradata/HZAVMES/davmd_16.dbf',
'/data02/oradata/HZAVMES/davmd_17.dbf',
'/data03/oradata/HZAVMES/davmx_07.dbf',
'/data03/oradata/HZAVMES/davmd_18.dbf',
'/data03/oradata/HZAVMES/davmd_19.dbf',
'/data02/oradata/HZAVMES/davmx_08.dbf',
'/data02/oradata/HZAVMES/davmd_20.dbf',
'/data03/oradata/HZAVMES/davmd_21.dbf',
'/data02/oradata/HZAVMES/davmd_22.dbf',
'/data03/oradata/HZAVMES/davmx_09.dbf',
'/data03/oradata/HZAVMES/davmd_23.dbf',
'/data02/oradata/HZAVMES/davmd_24.dbf',
'/data03/oradata/HZAVMES/davmd_25.dbf',
'/data02/oradata/HZAVMES/davmd_27.dbf',
'/data01/oradata/HZAVMES/davmd_17.dbf',
'/data03/oradata/HZAVMES/davmx_10.dbf',
'/data03/oradata/HZAVMES/davmx_11.dbf',
'/data03/oradata/HZAVMES/davmd_28.dbf',
'/data02/oradata/HZAVMES/davmd_28.dbf',
'/data02/oradata/HZAVMES/davmd_29.dbf',
'/data04/oradata/HZAVMES/davmd_30.dbf',
'/data04/oradata/HZAVMES/davmd_31.dbf',
'/data04/oradata/HZAVMES/davmd_32.dbf',
'/data04/oradata/HZAVMES/davmd_33.dbf',
'/data04/oradata/HZAVMES/davmd_34.dbf',
'/data03/oradata/HZAVMES/davmd_35.dbf',
'/data04/oradata/HZAVMES/davmx_12.dbf',
'/data04/oradata/HZAVMES/davmd_36.dbf',
'/data04/oradata/HZAVMES/davmd_37.dbf',
'/data04/oradata/HZAVMES/davmd_38.dbf',
'/data04/oradata/HZAVMES/davmd_39.dbf',
'/data04/oradata/HZAVMES/davmd_40.dbf',
'/data04/oradata/HZAVMES/davmx_13.dbf',
'/data01/oradata/HZAVMES/users02.dbf',
'/data02/oradata/HZAVMES/davmd_41.dbf',
'/data03/oradata/HZAVMES/davmd_42.dbf',
'/data04/oradata/HZAVMES/davmd_43.dbf',
'/data04/oradata/HZAVMES/davmd_44.dbf',
'/data03/oradata/HZAVMES/davmd_45.dbf',
'/data02/oradata/HZAVMES/davmd_46.dbf',
'/data04/oradata/HZAVMES/davmd_47.dbf',
'/data03/oradata/HZAVMES/davmd_48.dbf',
'/data04/oradata/HZAVMES/davmd_49.dbf',
'/data04/oradata/HZAVMES/davmd_50.dbf',
'/data02/oradata/HZAVMES/davmx_14.dbf'
CHARACTER SET UTF8;

5.打开数据库。
alter database open;

总结：
目前我们现在数据库都是用shutdown abort先关闭数据库再冷备，但这种方法很危险，可能导致数据库数据文件损坏和数据库不一致
这次就是可能由于表空间处于热备模式时，数据库采用了shutdown abort关闭数据库，导致在启动时数据库产生异常，控制文件和数据文件不致所致。

由于这些天数据库即没有物理备份也没逻辑备份，所以真的让我们害怕了一把，幸亏归档是全的，不然肯定数据库要丢失了。

相关资料：

Instance Recovery
If the database was last closed abnormally, either because the database
administrator terminated its instance or because of a power failure, then Oracle
automatically performs recovery when the database is reopened.

Abnormal Instance Shutdown
In unusual circumstances, shutdown of an instance might not occur cleanly; all
memory structures might not be removed from memory or one of the background
processes might not be terminated. When remnants of a previous instance exist, a
subsequent instance startup most likely will fail. In such situations, the database
administrator can force the new instance to start up by first removing the remnants
of the previous instance and then starting a new instance, or by issuing a SHUTDOWN
ABORT statement in SQL*Plus or using Enterprise Manager.

记一次数据库恢复步骤 oracle数据库备份与恢复之三数据文件误删除后的数据库恢复 Oracle 数据库的备份与恢复 Sql Server数据库的备份和恢复措施换季感冒，九个步骤快速恢复一键恢复及重装系统步骤 windows环境下mysql数据库的主从同步备份步骤数据库 mysql重装后，手动恢复数据库（复制Data目录备份） Oracle 9.2.0.1升级到9.2.0.6 步骤（客户端升级，如果本地有数据库需要... [原创] [原创]DB2离线和在线全备、增量备份及恢复的操作步骤卡拉﹒斯威舍-->美国在线——史蒂夫凯斯如何击败比尔盖茨-->步骤三：一次获得风险投资记一次元旦联欢会数据库介绍大型数据库数据库设计数据库教程备份数据库记一次有意的旅游 NoSQL数据库探讨 -- 非关系型数据库数据库建表原则 - [数据库系统] 中医药数据库：药粥数据库（下）中医药数据库：药粥数据库（中）