[20141106]建立控制檔案與丟失資料檔案問題

[20141106]建立控制檔案與丟失資料檔案問題.txt

--前一陣子,幫別人恢復系統,主資料庫硬碟損壞,dataguard能夠只讀開啟,查詢沒有問題,安全起見在另外的機器
--建立新系統,把dataguard的資料檔案複製到新機器,建立新的控制檔案,但是open resetlogs後發現,丟失一些數
--據檔案,感覺很奇怪,詢問以後才明白,有一些表空間是read only的,當然解決也很簡單,
--參考連結:
http://blog.itpub.net/267265/viewspace-748345/

alter database rename file '/u01/app/oracle11g/product/11.2.0/db_1/dbs/MISSING00008' to '/u01/app/oracle11g/oradata/test/test01.dbf';
recover datafile 8;
alter database datafile 8 online ;

--為了加強記憶,自己也做一些測試,看看錶空間只讀的情況,備份控制檔案的情況.

1.測試環境:
SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

RMAN> report schema ;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     /u01/app/oracle11g/oradata/test/system01.dbf
2    920      SYSAUX               ***     /u01/app/oracle11g/oradata/test/sysaux01.dbf
3    718      UNDOTBS1             ***     /u01/app/oracle11g/oradata/test/undotbs01.dbf
4    768      USERS                ***     /u01/app/oracle11g/oradata/test/users01.dbf
5    100      EXAMPLE              ***     /u01/app/oracle11g/oradata/test/example01.dbf
6    64       RMAN                 ***     /u01/app/oracle11g/oradata/test/rman01.dbf
7    64       TOOLS                ***     /u01/app/oracle11g/oradata/test/tools01.dbf
8    64       TEST                 ***     /u01/app/oracle11g/oradata/test/test01.dbf
9    64       TESTMSSM             ***     /u01/app/oracle11g/oradata/test/testmssm01.dbf
10   1        UNDOTBS2             ***     /u01/app/oracle11g/oradata/test/undotbs02.dbf
11   100      TEST16K              ***     /u01/app/oracle11g/oradata/test/test16k01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    339      TEMP                 32767       /u01/app/oracle11g/oradata/test/temp01.dbf

2.設定表空間只讀:
alter tablespace test16k read only;

SYS@test> alter database backup controlfile to trace ;
Database altered.

--檢查轉儲檔案:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS FORCE LOGGING 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
--   GROUP 4 '/u01/app/oracle11g/oradata/test/redostb01.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 5 '/u01/app/oracle11g/oradata/test/redostb02.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 6 '/u01/app/oracle11g/oradata/test/redostb03.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 7 '/u01/app/oracle11g/oradata/test/redostb04.log'  SIZE 50M BLOCKSIZE 512
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',
  '/u01/app/oracle11g/oradata/test/testmssm01.dbf',
  '/u01/app/oracle11g/oradata/test/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;

--可以發現並不包括資料檔案/u01/app/oracle11g/oradata/test/test16k01.dbf.

3.另外的情況:
SYS@test> alter tablespace test16k read write ;
Tablespace altered.

--實際上offline也是一樣的情況.繼續測試:
SYS@test> alter tablespace test16k offline ;
Tablespace altered.

SYS@test> alter database backup controlfile to trace ;
Database altered.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS FORCE LOGGING 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
--   GROUP 4 '/u01/app/oracle11g/oradata/test/redostb01.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 5 '/u01/app/oracle11g/oradata/test/redostb02.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 6 '/u01/app/oracle11g/oradata/test/redostb03.log'  SIZE 50M BLOCKSIZE 512,
--   GROUP 7 '/u01/app/oracle11g/oradata/test/redostb04.log'  SIZE 50M BLOCKSIZE 512
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',
  '/u01/app/oracle11g/oradata/test/testmssm01.dbf',
  '/u01/app/oracle11g/oradata/test/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;
--依舊沒有資料檔案/u01/app/oracle11g/oradata/test/test16k01.dbf.

4.總結:
--要注意這個細節,在表空間只讀以及offline的情況下,alter database backup controlfile to trace ;建立的控制檔案會丟失read
--only以及offline的資料檔案.