ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > 【非归档丢失控制文件恢复】

【非归档丢失控制文件恢复】

原创 Linux操作系统 作者：sub8412 时间：2012-12-05 21:22:57 0 删除 编辑
示例一：全部控制文件丢失

查看当前归档方式
SQL> archive log list
Database log mode       No Archive Mode
Automatic archival       Disabled
Archive destination       /home/oracle/oracle10g/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     0
Current log sequence       1

备份控制文件为trc形式，文件名包含此次session的pid号，可以根据此号找到备份后的文件。
SQL> alter database backup controlfile to trace;

Database altered.

SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));

SPID
------------
17290

SQL> show parameter user_dump_dest

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest     string /home/oracle/oracle10g/admin/o
dd/udump
[oracle@odd udump]$ ll
总计 44
-rw-r----- 1 oracle oinstall  612 11-13 11:42 odd_ora_17150.trc
-rw-r----- 1 oracle oinstall  669 11-13 11:42 odd_ora_17171.trc
-rw-r----- 1 oracle oinstall  797 11-13 11:43 odd_ora_17172.trc
-rw-r----- 1 oracle oinstall  612 11-13 11:43 odd_ora_17202.trc
-rw-r----- 1 oracle oinstall  797 11-13 11:43 odd_ora_17223.trc
-rw-r----- 1 oracle oinstall  612 11-13 11:44 odd_ora_17226.trc
-rw-r----- 1 oracle oinstall 2171 11-13 11:46 odd_ora_17247.trc
-rw-r----- 1 oracle oinstall  612 11-13 11:46 odd_ora_17268.trc
-rw-r----- 1 oracle oinstall  669 11-13 11:46 odd_ora_17289.trc
-rw-r----- 1 oracle oinstall 6554 11-13 12:10 odd_ora_17290.trc

查看生成的文件，重建控制文件有两种方式的创建脚本：NORESETLOGS 、 RESETLOGS ，
redolog可用时用NORESETLOGS 脚本，redolog不能用时用RESETLOGS 脚本。
[oracle@odd udump]$ cat odd_ora_17290.trc    截取片段
。。。
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script. file, edited as necessary, and executed when there is a
-- need to re-create the control file.
。。。

--     Set #1. NORESETLOGS case

CREATE CONTROLFILE REUSE DATABASE "ODD" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/oracle10g/oradata/odd/redo01.log'  SIZE 50M,
  GROUP 2 '/home/oracle/oracle10g/oradata/odd/redo02.log'  SIZE 50M,
  GROUP 3 '/home/oracle/oracle10g/oradata/odd/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/oracle10g/oradata/odd/system01.dbf',
  '/home/oracle/oracle10g/oradata/odd/undotbs01.dbf',
  '/home/oracle/oracle10g/oradata/odd/sysaux01.dbf',
  '/home/oracle/oracle10g/oradata/odd/users.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs1.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs2.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs3.dbf'
CHARACTER SET ZHS16GBK
;

--     Set #2. RESETLOGS case

CREATE CONTROLFILE REUSE DATABASE "ODD" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/oracle10g/oradata/odd/redo01.log'  SIZE 50M,
  GROUP 2 '/home/oracle/oracle10g/oradata/odd/redo02.log'  SIZE 50M,
  GROUP 3 '/home/oracle/oracle10g/oradata/odd/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/oracle10g/oradata/odd/system01.dbf',
  '/home/oracle/oracle10g/oradata/odd/undotbs01.dbf',
  '/home/oracle/oracle10g/oradata/odd/sysaux01.dbf',
  '/home/oracle/oracle10g/oradata/odd/users.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs1.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs2.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs3.dbf'
CHARACTER SET ZHS16GBK
;


关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

删掉控制文件
[oracle@odd odd]$ ls
control01.ctl  redo01.log  redo03.log    system01.dbf  tbs2.dbf  temp01.dbf     users.dbf
control02.ctl  redo02.log  sysaux01.dbf  tbs1.dbf      tbs3.dbf  undotbs01.dbf

[oracle@odd odd]$ mv control0* ~/
[oracle@odd odd]$ ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  tbs1.dbf  tbs2.dbf  tbs3.dbf  temp01.dbf  undotbs01.dbf  users.dbf

启动oracle，报错
SQL> startup 
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size    1219160 bytes
Variable Size   96470440 bytes
Database Buffers  213909504 bytes
Redo Buffers    2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

看看alert.log报错有如下：
ALTER DATABASE   MOUNT
Tue Nov 13 12:21:34 2012
ORA-00202: control file: '/home/oracle/oracle10g/oradata/odd/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Nov 13 12:21:34 2012
ORA-205 signalled during: ALTER DATABASE   MOUNT...

关闭数据库，启动到nomount状态
SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size    1219160 bytes
Variable Size   96470440 bytes
Database Buffers  213909504 bytes
Redo Buffers    2973696 bytes

执行 NORESETLOGS 方式的创建脚本
SQL> CREATE CONTROLFILE REUSE DATABASE "ODD" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/oracle10g/oradata/odd/redo01.log'  SIZE 50M,
  GROUP 2 '/home/oracle/oracle10g/oradata/odd/redo02.log'  SIZE 50M,
  GROUP 3 '/home/oracle/oracle10g/oradata/odd/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/oracle10g/oradata/odd/system01.dbf',
  '/home/oracle/oracle10g/oradata/odd/undotbs01.dbf',
  '/home/oracle/oracle10g/oradata/odd/sysaux01.dbf',
  '/home/oracle/oracle10g/oradata/odd/users.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs1.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs2.dbf',
  '/home/oracle/oracle10g/oradata/odd/tbs3.dbf'
CHARACTER SET ZHS16GBK
;
SQL>  

Control file created.

控制文件创建成功，查看次数数据库的打开模式为mounted
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

将数据库切换为打开状态，并查看生成的控制文件。
SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> !ls /home/oracle/oracle10g/oradata/odd
control01.ctl  redo01.log  redo03.log system01.dbf  tbs2.dbf  temp01.dbf users.dbf
control02.ctl  redo02.log  sysaux01.dbf  tbs1.dbf      tbs3.dbf  undotbs01.dbf

至此数据库已恢复完毕！


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20553601/viewspace-750605/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【sqlldr加载数据】
下一篇： ORACLE中修复数据块损坏
请登录后发表评论 登录
全部评论
sub8412

注册时间：2012-11-13

  • 博文量
    16
  • 访问量
    87350

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593