[20121105]重建控制檔案少一個資料檔案的情況.txt

[20121105]重建控制檔案少一個資料檔案的情況.txt

我的測試資料庫經常做各種測試,resetlogs很多次,產生很多incarnation.今天想透過重建控制檔案來清除這些資訊.

我的控制檔案的指令碼是以前建立的,少包括一個資料檔案.自己就拿這個做一個測試看看.

建立控制檔案少包括'/u01/app/oracle11g/oradata/test/test01.dbf'

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 1600

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle11g/oradata/test/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle11g/oradata/test/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle11g/oradata/test/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle11g/oradata/test/system01.dbf',

  '/u01/app/oracle11g/oradata/test/sysaux01.dbf',

  '/u01/app/oracle11g/oradata/test/undotbs01.dbf',

  '/u01/app/oracle11g/oradata/test/users01.dbf',

  '/u01/app/oracle11g/oradata/test/example01.dbf',

  '/u01/app/oracle11g/oradata/test/rman01.dbf',

  '/u01/app/oracle11g/oradata/test/tools01.dbf'

--  '/u01/app/oracle11g/oradata/test/test01.dbf'

CHARACTER SET ZHS16GBK

;

執行以上後開啟正常:

SQL> @cr.sql

ORACLE instance started.

SQL> alter database open ;

Database altered.

--建立臨時檔案.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle11g/oradata/test/temp01.dbf' SIZE 355467264  REUSE AUTOEXTEND OFF;

Tablespace altered.

SQL> column name format a56

SQL> SELECT file#,rfile#,name,checkpoint_change#  FROM v$datafile;

     FILE#     RFILE# NAME                                                     CHECKPOINT_CHANGE#

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

         1          1 /u01/app/oracle11g/oradata/test/system01.dbf                     3011110032

         2          2 /u01/app/oracle11g/oradata/test/sysaux01.dbf                     3011110032

         3          3 /u01/app/oracle11g/oradata/test/undotbs01.dbf                    3011110032

         4          4 /u01/app/oracle11g/oradata/test/users01.dbf                      3011110032

         5          5 /u01/app/oracle11g/oradata/test/example01.dbf                    3011110032

         6          6 /u01/app/oracle11g/oradata/test/rman01.dbf                       3011110032

         7          7 /u01/app/oracle11g/oradata/test/tools01.dbf                      3011110032

         8          8 /u01/app/oracle11g/product/11.2.0/db_1/dbs/MISSING00008                   0

8 rows selected.

SQL> select * from V$RECOVER_FILE;

     FILE# ONLINE  ONLINE_ ERROR            CHANGE# TIME

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

         8 OFFLINE OFFLINE FILE MISSING           0

--可以發現檔案被定義在/u01/app/oracle11g/product/11.2.0/db_1/dbs/MISSING00008.

$ ls -l /u01/app/oracle11g/product/11.2.0/db_1/dbs/MISSING00008

ls: /u01/app/oracle11g/product/11.2.0/db_1/dbs/MISSING00008: No such file or directory

2.僅僅需要改名到原來的位置就ok了.

SQL> alter database rename file '/u01/app/oracle11g/product/11.2.0/db_1/dbs/MISSING00008' to '/u01/app/oracle11g/oradata/test/test01.dbf';

Database altered.

SQL> recover datafile 8;

Media recovery complete.

SQL> select * from V$RECOVER_FILE;

no rows selected

--OK,恢復完成!

SQL>  SELECT file#,rfile#,name,checkpoint_change#  FROM v$datafile;

     FILE#     RFILE# NAME                                                     CHECKPOINT_CHANGE#

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

         1          1 /u01/app/oracle11g/oradata/test/system01.dbf                     3011110032

         2          2 /u01/app/oracle11g/oradata/test/sysaux01.dbf                     3011110032

         3          3 /u01/app/oracle11g/oradata/test/undotbs01.dbf                    3011110032

         4          4 /u01/app/oracle11g/oradata/test/users01.dbf                      3011110032

         5          5 /u01/app/oracle11g/oradata/test/example01.dbf                    3011110032

         6          6 /u01/app/oracle11g/oradata/test/rman01.dbf                       3011110032

         7          7 /u01/app/oracle11g/oradata/test/tools01.dbf                      3011110032

         8          8 /u01/app/oracle11g/oradata/test/test01.dbf                       3011110059

8 rows selected.

SQL> alter database datafile 8 online ;

Database altered.

SQL>  SELECT file#,rfile#,name,checkpoint_change#  FROM v$datafile;

     FILE#     RFILE# NAME                                                     CHECKPOINT_CHANGE#

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

         1          1 /u01/app/oracle11g/oradata/test/system01.dbf                     3011110032

         2          2 /u01/app/oracle11g/oradata/test/sysaux01.dbf                     3011110032

         3          3 /u01/app/oracle11g/oradata/test/undotbs01.dbf                    3011110032

         4          4 /u01/app/oracle11g/oradata/test/users01.dbf                      3011110032

         5          5 /u01/app/oracle11g/oradata/test/example01.dbf                    3011110032

         6          6 /u01/app/oracle11g/oradata/test/rman01.dbf                       3011110032

         7          7 /u01/app/oracle11g/oradata/test/tools01.dbf                      3011110032

         8          8 /u01/app/oracle11g/oradata/test/test01.dbf                       3011110385

8 rows selected.

3.測試:

SQL> select tablespace_name from dba_tables where wner='SCOTT' and table_name='DEPT1';

TABLESPACE_NAME

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

TEST

SQL> select * from scott.dept1;

    DEPTNO DNAME          LOC

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

        50 TEST           BBBBB

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--OK,正常!

RMAN> list incarnation ;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

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

1       1       TEST     2071943378       CURRENT 15808173   2012-08-10 11:04:07

RMAN> quit

--僅僅保留一個Incarnations.