数据库恢复一例(2)
2024-07-21 02:07:16
供稿：网友
最近通过做实验总结出一种数据库恢复方法，对今后的工作很有帮助：
数据库为非归档状态，只有一周前的数据文件的备份，无redolog，归档日志和controlfile的备份，此种情况一但数据库出故障只能做不完全恢复，会丢失一周前做备份时到出故障那一时候的所有数据，具体恢复方法如下:
操作系统为solaris8,内存2g，2颗cpu.
实验步骤：
$sqlplus /nolog
sql>connect / as sysdba
sql> archive log list
database log mode no archive mode
automatic archival enabled
archive destination /opt/oracle/arch/orcl
oldest online log sequence 895
current log sequence 897
sql> select * from v$logfile;
group# status
---------- -------
member
--------------------------------------------------------------------------------
3
/opt/oracle/db04/oradata/orcl/redo03.log
2
/opt/oracle/db03/oradata/orcl/redo02.log
1
/opt/oracle/db02/oradata/orcl/redo01.log
sql> select * from v$controlfile;
status
-------
name
--------------------------------------------------------------------------------
/opt/oracle/db02/oradata/orcl/control01.ctl
/opt/oracle/db03/oradata/orcl/control02.ctl
/opt/oracle/db04/oradata/orcl/control03.ctl
sql>alter database backup controlfile to trace;
(备份控制文件,此时会在$oracle_base/admin/orcl/udump目录里生成trace文件)
sql> shutdown immediate (关闭当前数据库)
database closed.
database dismounted.
oracle instance shut down.
sql> quit
disconnected
模拟数据丢失：
删除当前所有的数据文件,控制文件和redolog文件:
$rm -rf ?/opt/oracle/db02/oradata/orcl/*
$rm /opt/oracle/db03/oradata/orcl/redo02.log
$rm /opt/oracle/db03/oradata/orcl/control02.ctl
$rm /opt/oracle/db04/oradata/orcl/redo03.log
$rm /opt/oracle/db04/oradata/orcl/control03.ctl
把一周前备份的数据文件copy回来，目录结构和以前一样，但这时因为没有redolog和controlfile文件，数据库只能启动到nomount状态:
编辑udump目录下生成的orcl_ora_7140.trc文件，把里面的创建controlfile的那部分内容粘贴下来放在sql里执行:
（这里要注意一定要用resetlogs方式重建控制文件，resetlogs之后会生成新的redolog,并且把当前redofile的sequence置为1,否则创建控制文件会失败)：
sql>startup nomount
sql> create controlfile reuse database "orcl" resetlogs noarchivelog
2 maxlogfiles 32
3 maxlogmembers 2
4 maxdatafiles 254
5 maxinstances 8
6 maxloghistory 907
7 logfile
8 group 1 '/opt/oracle/db02/oradata/orcl/redo01.log' size 50000k,
9 group 2 '/opt/oracle/db03/oradata/orcl/redo02.log' size 50000k,
10 group 3 '/opt/oracle/db04/oradata/orcl/redo03.log' size 50000k
11 datafile
12 '/opt/oracle/db02/oradata/orcl/system01.dbf',
13 '/opt/oracle/db02/oradata/orcl/tools01.dbf',
14 '/opt/oracle/db02/oradata/orcl/rbs01.dbf',
15 '/opt/oracle/db02/oradata/orcl/temp01.dbf',
16 '/opt/oracle/db02/oradata/orcl/users01.dbf',
17 '/opt/oracle/db02/oradata/orcl/indx01.dbf',
18 '/opt/oracle/db02/oradata/orcl/drsys01.dbf',
19 '/opt/oracle/db02/oradata/orcl/wacos.dbf',
20 '/opt/oracle/db02/oradata/orcl/wacos01.dbf',
21 '/opt/oracle/db02/oradata/orcl/wacos02.dbf',
22 '/opt/oracle/db02/oradata/orcl/wacos03.dbf',
23 '/opt/oracle/db02/oradata/orcl/wacos04.dbf',
24 '/opt/oracle/db02/oradata/orcl/wacos05.dbf',
25 '/opt/oracle/db02/oradata/orcl/wacos06.dbf',
26 '/opt/oracle/db02/oradata/orcl/nms.dbf',
27 '/opt/oracle/db02/oradata/orcl/test.dbf'
28 character set we8iso8859p1;
control file created.
sql> alter database open resetlogs;(以resetlogs方式打开数据库)
database altered.
sql> select status from v$instance; (检查数据库的状态)
status
-------
open
sql> select * from v$logfile; (检查logfile的状态)
group# status
---------- -------
member
--------------------------------------------------------------------------------
3
/opt/oracle/db04/oradata/orcl/redo03.log
2
/opt/oracle/db03/oradata/orcl/redo02.log
1
/opt/oracle/db02/oradata/orcl/redo01.log
sql> select * from v$controlfile; (检查控制文件的状态)
status
-------
name
--------------------------------------------------------------------------------
/opt/oracle/db02/oradata/orcl/control01.ctl
/opt/oracle/db03/oradata/orcl/control02.ctl
/opt/oracle/db04/oradata/orcl/control03.ctl
