利用归档恢复数据文件

开启归档

SYS@PROD>startup mount;

ORACLE instance started.

 

Total System Global Area  417546240 bytes

Fixed Size                  2253824 bytes

Variable Size             268438528 bytes

Database Buffers          142606336 bytes

Redo Buffers                4247552 bytes

Database mounted.

SYS@PROD>alter database archivelog;

 

Database altered.

 

SYS@PROD>alter database open;

 

创建表空间，并作若干组组日志切换

SYS@PROD>create tablespace tbs1

datafile'/u01/app/oracle/oradata/PROD/tbs1_1.dbf' size 10m,

'/u01/app/oracle/oradata/PROD/tbs1_2.dbf' size 10m

  2    3    4  ;

 

Tablespace created.

 

SYS@PROD>alter system switch logfile;

 

System altered.

 

SYS@PROD>/

 

System altered.

 

SYS@PROD>/

 

System altered.

 

SYS@PROD>/

 

System altered.

 

在表空间上创建一个测试表t_tbs

 

SYS@PROD>create table t_tbs(x int)tablespace tbs1;

 

Table created.

 

SYS@PROD>desc t_tbs;

 Name                                      Null?    Type

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

 X                                                  NUMBER(38)

 

断开连接，使数据库释放句柄，重新连接并检查表。

 

SYS@PROD>quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 17 22:15:21 2014

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@PROD>desc tbs1;

ERROR:

ORA-04043: object tbs1 does not exist

发现对象不存在

 

下面来重建数据文件

SYS@PROD>select FILE_NAME,FILE_ID,TABLESPACE_NAME,STATUS from dba_data_files;

 

FILE_NAME

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

   FILE_ID TABLESPACE_NAME                STATUS

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

/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b0btt0o2_.dbf

         1 SYSTEM                         AVAILABLE

 

/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b0bttrn5_.dbf

         2 SYSAUX                         AVAILABLE

 

/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b0btvhov_.dbf

         3 UNDOTBS1                       AVAILABLE

 

 

FILE_NAME

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

   FILE_ID TABLESPACE_NAME                STATUS

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

/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b0btwnom_.dbf

         4 USERS                          AVAILABLE

 

/u01/app/oracle/oradata/PROD/test.dbf

         5 TEST                           AVAILABLE

 

/u01/app/oracle/oradata/PROD/test2.dbf

         6 TEST2                          AVAILABLE

 

 

FILE_NAME

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

   FILE_ID TABLESPACE_NAME                STATUS

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

/u01/app/oracle/oradata/PROD/test3.dbf

         7 TEST3                          AVAILABLE

 

/u01/app/oracle/oradata/PROD/test1.dbf

         8 TEST1                          AVAILABLE

 

/u01/app/oracle/oradata/PROD/tbs1_1.dbf

         9 TBS1                           AVAILABLE

 

 

FILE_NAME

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

   FILE_ID TABLESPACE_NAME                STATUS

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

/u01/app/oracle/oradata/PROD/tbs1_2.dbf

        10 TBS1                           AVAILABLE

 

10 rows selected.

 

SYS@PROD>alter database  datafile 9 offline;

 

Database altered.

 

SYS@PROD>alter database create datafile '/u01/app/oracle/oradata/PROD/tbs1_1.dbf' as '/u01/app/oracle/oradata/PROD/tbs1_1.dbf';

 

Database altered.

 

SYS@PROD>alter database  datafile 10  offline;

 

Database altered.

 

SYS@PROD>alter database create datafile '/u01/app/oracle/oradata/PROD/tbs1_2.dbf' as '/u01/app/oracle/oradata/PROD/tbs1_2.dbf';

 

Database altered.

 

恢复datafile 9与datafile 10

SYS@PROD>recover datafile 9,10;

ORA-00279: change 327938 generated at 09/17/2014 22:09:41 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_19_857228474.dbf

ORA-00280: change 327938 for thread 1 is in sequence #19

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto                        此处为手工输入

ORA-00279: change 328638 generated at 09/17/2014 22:12:58 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_20_857228474.dbf

ORA-00280: change 328638 for thread 1 is in sequence #20

 

 

Log applied.

Media recovery complete.

 

将数据文件online

SYS@PROD>alter database datafile 9,10 online;

 

Database altered.

 

查看表，发现对象存在

SYS@PROD>desc t_tbs;

 Name                                      Null?    Type

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

 X                                                  NUMBER(38)