ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 各种文件丢失的恢复

Oracle 各种文件丢失的恢复

原创 Linux操作系统 作者:tian1982tian 时间:2012-05-31 10:29:53 0 删除 编辑

一、控制文件损坏的恢复(一个文件)

损坏单个控制文件是比较容易恢复的,因为一般的数据库系统,控制文件都不是一个,而且所有的控制文件都互为镜像,只要拷贝一个好的控制文件替换坏的控制文件就可以了。
1、控制文件损坏,最典型的就是启动数据库出错,不能mount数据库
SQL>startup
ORA-00205: error in identifying controlfile, check alert log for more info
查看报警日志文件,有如下信息
alter database  mount
Mon May 26 11:59:52 2003
ORA-00202: controlfile: 'D:\Oracle\oradata\chen\control01.ctl'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。

2、停止数据库
SQL>shutdown immediate

3、拷贝一个好的控制文件替换坏的控制文件或修改init.ora中的控制文件参数,取消这个坏的控制文件。

4、重新启动数据
SQL>startup
说明:
1、损失单个控制文件是比较简单的,因为数据库中所有的控制文件都是镜像的,只需要简单的拷贝一个好的就可以了
2、建议镜像控制文件在不同的磁盘上
3、建议多做控制文件的备份,长期保留一份由alter database backup control file to trace产生的控制文件的文本备份
二、Control文件损坏的恢复(全部文件)

损坏多个控制文件,或者人为的删除了所有的控制文件,通过控制文件的复制已经不能解决问题,这个时候需要重新建立控制文件。
同时注意,alter database backup control file to trace可以产生一个控制文件的文本备份。
以下是详细重新创建控制文件的步骤
1、关闭数据库
SQL>shutdown immediate;
2、删除所有控制文件,模拟控制文件的丢失

3、启动数据库,出现错误,并不能启动到mount下
SQL>startup
ORA-00205: error in identifying controlfile, check alert log for more info
查看报警日志文件,有如下信息
alter database  mount
Mon May 26 11:53:15 2003
ORA-00202: controlfile: 'D:\Oracle\oradata\chen\control01.ctl'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。

4、关闭数据库
SQL>shutdown immediate;

5、在internal或sys下运行如下创建控制文件的脚本,注意完整列出联机日志或数据文件的路径,或修改由alter database backup control file to trace备份控制文件时产生的脚本,去掉多余的注释即可。
STARTUP NOMOUNT;
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'  SIZE 50M,
  GROUP 2 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'  SIZE 50M,
  GROUP 3 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'  SIZE 50M
DATAFILE
  'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
  'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
  'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
  'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
CHARACTER SET AL32UTF8;

RECOVER DATABASE;

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
6、如果没有错误,数据库将启动到open状态下。

说明:
1、重建控制文件用于恢复全部数据文件的损坏,需要注意其书写的正确性,保证包含了所有的数据文件与联机日志
2、经常有这样一种情况,因为一个磁盘损坏,我们不能再恢复(store)数据文件到这个磁盘,因此在store到另外一个盘的时候,我们就必须重新创建控制文件,用于识别这个新的数据文件,这里也可以用这种方法用于恢复
三、无备份数据文件的恢复

流程:
1.  新建表空间及表,向表里插入一条数据;
2.  关闭数据库;
3.  在操作系统删除新建表空间的数据文件;
4.  重新启动数据库,报错;
5.  手动将数据文件下线;
6.  打开数据库,先让数据正常对外工作;
7.  recover datafile ..(前提是从数据文件诞生起日志文件都存在)
8.  手动将数据文件上线;

 

[root@test ~]#su - oracle
-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 26 10:25:23 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             301990488 bytes
Database Buffers         1258291200 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> create tablespace app1 datafile '/oracle/ora10/oradata/ora10g/app02.dbf' size 10m;

Tablespace created.

SQL> conn test1/test1
Connected.
SQL> create table test2(id int,name char(10)) tablespace app1;

Table created.

SQL> insert into test2 select 0,'test0' from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test2 select 1,'test1' from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test2;

        ID NAME
---------- ----------
         0 test0
         1 test1

SQL>

删除数据文件
[root@test ora10g]#ll
total 1601624
-rw-r----- 1 oracle oinstall  20979712 May 26 10:25 app01.dbf
-rw-r----- 1 oracle oinstall  10493952 May 26 10:26 app02.dbf
drwxr-xr-x 2 oracle oinstall      4096 May 26 10:25 archive
-rw-r--r-- 1 oracle oinstall   1056768 May 26 10:25 block.dbf
-rw-r----- 1 oracle oinstall   7159808 May 26 10:30 control01.ctl
-rw-r----- 1 oracle oinstall   7159808 May 26 10:30 control02.ctl
-rw-r----- 1 oracle oinstall   7159808 May 26 10:30 control03.ctl
-rw-r----- 1 oracle oinstall   7159808 May 26 10:30 control04.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 18 19:17 mytemp01.dbf
-rw-r----- 1 oracle oinstall 209723392 May 26 10:25 sysaux01.dbf
-rw-r----- 1 oracle oinstall 387981312 May 26 10:25 system01.dbf
-rw-r----- 1 oracle oinstall 443555840 May 26 09:43 temp01.dbf
-rw-r----- 1 oracle oinstall 536879104 May 26 10:25 users01.dbf
[root@test ora10g]#rm -f app02.dbf

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> col name format a40
SQL> select file#,status,name from v$datafile;

     FILE# STATUS  NAME
---------- ------- --------------------------------------------------
         1 SYSTEM  /oracle/ora10/oradata/ora10g/system01.dbf
         2 ONLINE  /oraundo/undotbs01.dbf
         3 ONLINE  /oracle/ora10/oradata/ora10g/sysaux01.dbf
         4 ONLINE  /oracle/ora10/oradata/ora10g/users01.dbf
         5 ONLINE  /oradatab/task01.dbf
         6 ONLINE  /oradataa/task01.dbf
         7 ONLINE  /oracle/ora10/oradata/ora10g/block.dbf
         8 ONLINE  /oraundo/test01.dbf
         9 ONLINE  /oraundo/DP01.dbf
        10 ONLINE  /oradataa/TBS_TESTDATA01.dbf
        11 ONLINE  /oracle/ora10/oradata/ora10g/app01.dbf

     FILE# STATUS  NAME
---------- ------- --------------------------------------------------
        12 ONLINE  /oracle/ora10/oradata/ora10g/app02.dbf

12 rows selected.

SQL> alter database datafile 12 offline;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             301990488 bytes
Database Buffers         1258291200 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> conn test1/test1
Connected.
SQL>

SQL> conn / as sysdba
Connected.
SQL> select file#,status,name from v$datafile;

     FILE# STATUS  NAME
---------- ------- --------------------------------------------------
         1 SYSTEM  /oracle/ora10/oradata/ora10g/system01.dbf
         2 ONLINE  /oraundo/undotbs01.dbf
         3 ONLINE  /oracle/ora10/oradata/ora10g/sysaux01.dbf
         4 ONLINE  /oracle/ora10/oradata/ora10g/users01.dbf
         5 ONLINE  /oradatab/task01.dbf
         6 ONLINE  /oradataa/task01.dbf
         7 ONLINE  /oracle/ora10/oradata/ora10g/block.dbf
         8 ONLINE  /oraundo/test01.dbf
         9 ONLINE  /oraundo/DP01.dbf
        10 ONLINE  /oradataa/TBS_TESTDATA01.dbf
        11 ONLINE  /oracle/ora10/oradata/ora10g/app01.dbf

     FILE# STATUS  NAME
---------- ------- --------------------------------------------------
        12 RECOVER /oracle/ora10/oradata/ora10g/app02.dbf

12 rows selected.

SQL> recover datafile 12;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'


SQL> alter database datafile 12 offline;

Database altered.

SQL> select file#,status,name from v$datafile;

     FILE# STATUS  NAME
---------- ------- --------------------------------------------------
         1 SYSTEM  /oracle/ora10/oradata/ora10g/system01.dbf
         2 ONLINE  /oraundo/undotbs01.dbf
         3 ONLINE  /oracle/ora10/oradata/ora10g/sysaux01.dbf
         4 ONLINE  /oracle/ora10/oradata/ora10g/users01.dbf
         5 ONLINE  /oradatab/task01.dbf
         6 ONLINE  /oradataa/task01.dbf
         7 ONLINE  /oracle/ora10/oradata/ora10g/block.dbf
         8 ONLINE  /oraundo/test01.dbf
         9 ONLINE  /oraundo/DP01.dbf
        10 ONLINE  /oradataa/TBS_TESTDATA01.dbf
        11 ONLINE  /oracle/ora10/oradata/ora10g/app01.dbf

     FILE# STATUS  NAME
---------- ------- --------------------------------------------------
        12 RECOVER /oracle/ora10/oradata/ora10g/app02.dbf

12 rows selected.

SQL> recover datafile 12;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'

--重构数据文件
SQL> alter database create datafile '/oracle/ora10/oradata/ora10g/app02.dbf';

Database altered.

SQL> recover datafile 12;
Media recovery complete.
SQL> select * from test1.test2;
select * from test1.test2
                    *
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'


SQL> alter database datafile 12 online;

Database altered.

SQL> select * from test1.test2;

        ID NAME
---------- --------------------------------------------------
         0 test0
         1 test1

SQL>
已将所有的数据找回来

四、有备份数据文件的恢复

步骤:
1.  新建表空间及表,向表里插入一条数据;
2.  关闭数据库,冷备此表空间;
3.  重启数据库,再向表里插入一条数据,并手工发生检查点及日志切换;
4.  关闭数据库,在操作系统删除新建表空间的数据文件;
5.  启动数据库,报错;
6.  手动将数据文件下线;
7.  打开数据库,先让数据库正常对外工作;
8.  将原备份的文件拷贝到目录下;
9.  recover datafile ..
10. 手动将数据文件上线;
以上前提是数据库处于归档模式


[root@test ~]#su - oracle
-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 26 09:21:22 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             301990488 bytes
Database Buffers         1258291200 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/ora10/oradata/ora10g/archive
Oldest online log sequence     203
Next log sequence to archive   205
Current log sequence           205

SQL> create tablespace app datafile '/oracle/ora10/oradata/ora10g/app01.dbf' size 20m;

Tablespace created.

SQL>

SQL> create user test1 identified by test1
  2  default tablespace app;

User created.

SQL> conn test1/test1
ERROR:
ORA-01045: user TEST1 lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to test1;

Grant succeeded.

SQL> conn test1/test1
Connected.
SQL> create table test (id int,name char(20));

Table created.

SQL> insert into test select 1,'test' from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

备份数据文件
[root@test ora10g]#pwd
/oracle/ora10/oradata/ora10g
[root@test ora10g]#ll
total 1591360
-rw-r----- 1 oracle oinstall  20979712 May 26 09:49 app01.dbf
drwxr-xr-x 2 oracle oinstall      4096 May 26 09:32 archive
-rw-r--r-- 1 oracle oinstall   1056768 May 26 09:43 block.dbf
-rw-r----- 1 oracle oinstall   7159808 May 26 09:56 control01.ctl
-rw-r----- 1 oracle oinstall   7159808 May 26 09:56 control02.ctl
-rw-r----- 1 oracle oinstall   7159808 May 26 09:56 control03.ctl
-rw-r----- 1 oracle oinstall   7159808 May 26 09:56 control04.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 18 19:17 mytemp01.dbf
-rw-r----- 1 oracle oinstall 209723392 May 26 09:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall 387981312 May 26 09:56 system01.dbf
-rw-r----- 1 oracle oinstall 443555840 May 26 09:43 temp01.dbf
-rw-r----- 1 oracle oinstall 536879104 May 26 09:43 users01.dbf
[root@test oracle]#cd cold
[root@test cold]#cp /oracle/ora10/oradata/ora10g/app01.dbf .

SQL> startup;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             301990488 bytes
Database Buffers         1258291200 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> conn test1/test1
Connected.
SQL> select * from test;

        ID NAME
---------- --------------------
         1 test

SQL> insert into test select 2,'test' from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> conn test1/test1
Connected.
SQL> select * from test;

        ID NAME
---------- --------------------
         1 test
         2 test
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

删除数据文件
[root@test ora10g]#rm -f app01.dbf

SQL> startup;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             301990488 bytes
Database Buffers         1258291200 bytes
Redo Buffers               14692352 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/oracle/ora10/oradata/ora10g/app01.dbf'

在操作系统修改权限
[root@test ora10g]#chown oracle.oinstall app01.dbf

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                 CHANGE# TIME
---------- ------- ------- ------------------ ---------- ------------------
        11 ONLINE  ONLINE  FILE NOT FOUND              0
SQL> alter database datafile 11 offline;

Database altered.

SQL> alter database open;

Database altered.

将备份的数据文件拷贝回来
[root@test cold]#cp app01.dbf /oracle/ora10/oradata/ora10g

SQL> recover datafile 11;
Media recovery complete.

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE
        11 OFFLINE

11 rows selected.

SQL> alter database datafile 11 online;

Database altered.

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE
        11 ONLINE

11 rows selected.

SQL> select * from test1.test;

        ID NAME
---------- --------------------
         1 test
         2 test

SQL>

第二条数据已找回

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

下一篇: 表分区总结
请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    70
  • 访问量
    162730