一个通过BBED强制恢复offline状态的tablespace的例子

By cui hua on October 28, 2009 6:58 PM | No Comments

在这篇文章里，我们通过BBED强制让丢失了一个datafile的offline状态（且这个offline的datafile已经被rm掉了）的tablespace恢复成了online，并且成功抢救出来了数据。

 

SQL_astca>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /dras20/astca/arch

Oldest online log sequence     0

Next log sequence to archive   1

Current log sequence           1

 

SQL_astca>create tablespace testscn datafile '/dras20/astca/testscn_01.dbf' size 100M extent management local uniform size 1M segment space management auto;

 

Tablespace created.

 

SQL_astca>create table tbtestscn tablespace testscn as select * from dba_users;

 

Table created.

 

SQL_astca>select count(*) from tbtestscn;

 

  COUNT(*)

----------

        32

 

SQL_astca>select username from tbtestscn where rownum<5;

 

USERNAME

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

SYS

SCOTT

SYSTEM

DBSNMP

 

SQL_astca>alter tablespace testscn add datafile '/dras20/astca/testscn_02.dbf' size 100M;

 

Tablespace altered.

 

SQL_astca>alter database datafile '/dras20/astca/testscn_02.dbf' offline drop;

 

Database altered.

 

SQL_astca>alter system switch logfile;

 

System altered.

 

SQL_astca>alter system switch logfile;

 

System altered.

 

SQL_astca>alter system switch logfile;

 

System altered.

 

SQL_astca>alter system switch logfile;

 

System altered.

 

$ cd /dras20/astca/arch

$ ls -l

total 864

-rw-r-----   1 oracle   dba          423424 Oct 28 16:51 1_1.dbf

-rw-r-----   1 oracle   dba            1024 Oct 28 16:51 1_2.dbf

-rw-r-----   1 oracle   dba            3072 Oct 28 16:51 1_3.dbf

-rw-r-----   1 oracle   dba            1024 Oct 28 16:51 1_4.dbf

-rw-r--r--   1 oracle   dba              17 Oct 28 14:31 login.sql

 

$ rm *.dbf

$ ls -l

total 8

-rw-r--r--   1 oracle   dba              17 Oct 28 14:31 login.sql

 

SQL_astca>select file_id from dba_data_files where file_name='/dras20/astca/testscn_01.dbf';

 

   FILE_ID

----------

       139

 

SQL_astca>select file_id from dba_data_files where file_name='/dras20/astca/testscn_02.dbf';

 

   FILE_ID

----------

       140

 

SQL_astca>select status from v$datafile where file#=139;

 

STATUS

-------

ONLINE

 

SQL_astca>select status from v$datafile where file#=140;

 

STATUS

-------

RECOVER

 

SQL_astca>alter tablespace testscn offline immediate;

 

Tablespace altered.

 

$ cd /dras20/astca

$ ls -l

total 9242752

drwxr-xr-x   2 oracle   dba             256 Oct 28 16:52 arch

-rw-r--r--   1 oracle   dba            7366 Oct 28 15:42 createcontrolfile.txt

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 drassda0701_03.dbf

-rw-r-----   1 oracle   dba       314580992 Oct 28 16:52 drassdc0610_03.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 drassdc0701_02.dbf

-rw-r-----   1 oracle   dba       314580992 Oct 28 16:52 drasupd0610_03.dbf

-rw-r-----   1 oracle   dba       314580992 Oct 28 16:52 drasupd0705_01.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 drasupd0706_02.dbf

-rw-r--r--   1 oracle   dba              17 Oct 28 16:46 login.sql

-rw-r--r--   1 oracle   dba         1056768 Oct 28 16:52 test01.dbf

-rw-r--r--   1 oracle   dba            8192 Jun 12 14:39 test01.dd

-rw-r--r--   1 oracle   dba            8192 Feb 18 2009  test01_01.txt

-rw-r--r--   1 oracle   dba          958464 Feb 18 2009  test01_02.txt

-rw-r-----   1 oracle   dba         1056768 Mar 03 2009  test01old.dbf

-rw-r-----   1 oracle   dba        10493952 Oct 28 16:52 testlmt01.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 testscn_01.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:50 testscn_02.dbf

-rw-r-----   1 oracle   dba      1048584192 Oct 28 16:52 testtbs1_01.dbf

-rw-r-----   1 oracle   dba      1048584192 Oct 28 16:52 testtbs1_02.dbf

-rw-r-----   1 oracle   dba      1048584192 Oct 28 16:52 testtbs2_01.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 testtbs3_01.dbf

$ mv testscn_02.dbf testscn_02.bck

 

SQL_astca>select status from v$datafile where file#=139;

 

STATUS

-------

RECOVER

 

SQL_astca>select status from v$datafile where file#=140;

 

STATUS

-------

RECOVER

 

SQL_astca>select status from dba_tablespaces where tablespace_name='TESTSCN';

 

STATUS

---------

OFFLINE

 

SQL_astca>alter tablespace testscn online;

alter tablespace testscn online

*

ERROR at line 1:

ORA-01113: file 139 needs media recovery

ORA-01110: data file 139: '/dras20/astca/testscn_01.dbf'

 

SQL_astca>alter database datafile '/dras20/astca/testscn_01.dbf' online;

alter database datafile '/dras20/astca/testscn_01.dbf' online

*

ERROR at line 1:

ORA-01113: file 139 needs media recovery

ORA-01110: data file 139: '/dras20/astca/testscn_01.dbf'

 

SQL_astca>recover datafile 139;

Media recovery complete.

 

SQL_astca>alter database datafile '/dras20/astca/testscn_01.dbf' online;

 

Database altered.

 

SQL_astca>alter tablespace testscn online;

alter tablespace testscn online

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 140 - see DBWR trace file

ORA-01110: data file 140: '/dras20/astca/testscn_02.dbf'

 

SQL_astca>select count(*) from tbtestscn;

select count(*) from tbtestscn

*

ERROR at line 1:

ORA-00376: file 139 cannot be read at this time

ORA-01110: data file 139: '/dras20/astca/testscn_01.dbf'

 

这里我们可以看到：

表空间testscn无法online，因为其下的datafile 140先被offline drop了，然后datafile 140 recover所需要的archive log也被rm掉了，且datafile 140也被rm掉了。

 

朋友们知道在这种情况下如何恢复吗？

 

事实上，在上述这种情况下，用BBED还是可以恢复的，这里我们来看一下恢复后的效果：

SQL_astca>alter database open resetlogs;

 

Database altered.

 

SQL_astca>select status from v$datafile where file#=139;

 

STATUS

-------

ONLINE

 

SQL_astca>select status from v$datafile where file#=140;

 

STATUS

-------

ONLINE

 

SQL_astca>select status from dba_tablespaces where tablespace_name='TESTSCN';

 

STATUS

---------

OFFLINE

 

SQL_astca>alter tablespace testscn online;

 

Tablespace altered.

 

SQL_astca>select count(*) from tbtestscn;

 

  COUNT(*)

----------

        32

 

SQL_astca>select username from tbtestscn where rownum<5;

 

USERNAME

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

SYS

SCOTT

SYSTEM

DBSNMP

 

聪明的朋友们一定知道我这里是怎么做的了吧。

Categories:

Leave a comment