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