[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.

上一篇:《从缺陷中学习C/C++》——6.6 指针变量的传值和传址


下一篇:大白话说清楚 Java 中的 this 和 super 关键字!