[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的数据文件.