重建控制檔案之後，只讀表空間的狀態變化

SQL> alter tablespace staging read only;

SQL> alter database backup controlfile to trace;

Database altered.

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

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              79694068 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oradata/test/redo01.log',
    '/oradata/test/redo01b.log'
  ) SIZE 5M,
  GROUP 2 (
    '/oradata/test/redo02.log',
    '/oradata/test/redo02b.log'
  ) SIZE 5M,
  GROUP 3 (
    '/oradata/test/redo03.log',
    '/oradata/test/redo03b.log'
  ) SIZE 5M,
  GROUP 4 '/oradata/test/redo04.log'  SIZE 10M
-- STANDBY LOGFILE
DATAFILE
  '/oradata/test/system01.dbf',
  '/oradata/test/undotbs01.dbf',
  '/oradata/test/sysaux01.dbf',
  '/oradata/test/users01.dbf',
  '/oradata/test/ecc_view.dbf'
CHARACTER SET ZHS16GBK
;
Control file created.

SQL>  select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;
STATUS  LAST_TIME      FILE# CHECKPOIN ENABLED
------- --------- ---------- --------- ----------
SYSTEM  10-APR-08          1 10-APR-08 DISABLED
ONLINE  10-APR-08          2 10-APR-08 DISABLED
ONLINE  10-APR-08          3 10-APR-08 DISABLED
ONLINE  10-APR-08          4 10-APR-08 DISABLED
ONLINE  10-APR-08          5 10-APR-08 DISABLED

SQL> alter database open;

Database altered.

SQL>  select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;
STATUS  LAST_TIME      FILE# CHECKPOIN ENABLED
------- --------- ---------- --------- ----------
SYSTEM                     1 10-APR-08 READ WRITE
ONLINE                     2 10-APR-08 READ WRITE
ONLINE                     3 10-APR-08 READ WRITE
ONLINE                     4 10-APR-08 READ WRITE
ONLINE                     5 10-APR-08 READ WRITE
OFFLINE 10-APR-08          6           READ ONLY

SQL> select file_id, file_name from dba_data_files;
   FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------         5 /oradata/test/ecc_view.dbf
         4 /oradata/test/users01.dbf
         3 /oradata/test/sysaux01.dbf
         2 /oradata/test/undotbs01.dbf
         1 /oradata/test/system01.dbf
         6 /home/oracle/oracle/product/10.2.0/db_1/dbs/MISSING00006

6 rows selected.

--注意：在/home/oracle/oracle/product/10.2.0/db_1/dbs/下是沒有MISSING00006這個檔案存在的
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------/oradata/test/ecc_view.dbf
/oradata/test/users01.dbf
/oradata/test/sysaux01.dbf
/oradata/test/undotbs01.dbf
/oradata/test/system01.dbf
/home/oracle/oracle/product/10.2.0/db_1/dbs/MISSING00006

6 rows selected.

SQL> alter database rename file 'MISSING00006' to '/oradata/test/staging.dbf';

Database altered.

SQL> select file_id, file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- ---------------------------------------------------------------------         5 /oradata/test/ecc_view.dbf
         4 /oradata/test/users01.dbf
         3 /oradata/test/sysaux01.dbf
         2 /oradata/test/undotbs01.dbf
         1 /oradata/test/system01.dbf
         6 /oradata/test/staging.dbf

6 rows selected.
SQL>  select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;
STATUS  LAST_TIME      FILE# CHECKPOIN ENABLED
------- --------- ---------- --------- ----------
SYSTEM                     1 10-APR-08 READ WRITE
ONLINE                     2 10-APR-08 READ WRITE
ONLINE                     3 10-APR-08 READ WRITE
ONLINE                     4 10-APR-08 READ WRITE
ONLINE                     5 10-APR-08 READ WRITE
OFFLINE 10-APR-08          6           READ ONLY

6 rows selected.

SQL> alter tablespace staging online;

Tablespace altered.

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

STATUS  LAST_TIME                FILE# CHECKPOINT_TIME     ENABLED
------- ------------------- ---------- ------------------- ----------
SYSTEM                               1 2008-04-10 15:37:18 READ WRITE
ONLINE                               2 2008-04-10 15:37:18 READ WRITE
ONLINE                               3 2008-04-10 15:37:18 READ WRITE
ONLINE                               4 2008-04-10 15:37:18 READ WRITE
ONLINE                               5 2008-04-10 15:37:18 READ WRITE
ONLINE  2008-04-10 15:20:30          6                     READ ONLY

6 rows selected.

SQL> alter tablespace staging read write;

Tablespace altered.

SQL>  select status, last_time, file#, CHECKPOINT_TIME, ENABLED from v$datafile;
STATUS  LAST_TIME                FILE# CHECKPOINT_TIME     ENABLED
------- ------------------- ---------- ------------------- ----------
SYSTEM                               1 2008-04-10 16:06:20 READ WRITE
ONLINE                               2 2008-04-10 16:06:20 READ WRITE
ONLINE                               3 2008-04-10 16:06:20 READ WRITE
ONLINE                               4 2008-04-10 16:06:20 READ WRITE
ONLINE                               5 2008-04-10 16:06:20 READ WRITE
ONLINE                               6 2008-04-10 16:10:51 READ WRITE

6 rows selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/175005/viewspace-429391/，如需轉載，請註明出處，否則將追究法律責任。