分享程序员开发的那些事...
更新时间：2022-09-13 23:25:09
[20150408]只读表空间以及数据库恢复2.txt
--昨天检查时发现1个小问题,就是有1个表空间设置只读,也许某个时间打开变成读写,又设置会只读,而备份仅仅在第1次设置只读时做过1次,
--按照道理如果使用原来的备份恢复会存在问题的,自己测试1下,顺便看看有什么变通的方法解决这个问题.
1.建立测试环境:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
CREATE TABLESPACE MSSM DATAFILE
'/mnt/ramdisk/test/mssm01.dbf' SIZE 16M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
create table scott.deptx as selct * from scott.dept ;
RMAN> report schema ;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 510 SYSTEM *** /mnt/ramdisk/test/system01.dbf
2 350 UNDOTBS1 *** /mnt/ramdisk/test/undotbs01.dbf
3 370 SYSAUX *** /mnt/ramdisk/test/sysaux01.dbf
4 100 USERS *** /mnt/ramdisk/test/users01.dbf
5 100 EXAMPLE *** /mnt/ramdisk/test/example01.dbf
6 16 MSSM *** /mnt/ramdisk/test/mssm01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /mnt/ramdisk/test/temp01.dbf
2.设置mssm表空间为只读,做1个拷贝.
SCOTT@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 11997899460 5 2804928 ONLINE 868465042 YES
2 11997899460 600647 2804928 ONLINE 868465042 YES
3 11997899460 6678 2804928 ONLINE 868465042 YES
4 11997899460 10685 2804928 ONLINE 868465044 YES
5 11997899460 625439 2804928 ONLINE 868465042 YES
6 11997899460 11997383136 2804928 ONLINE 29 YES
6 rows selected.
SCOTT@test> ALTER TABLESPACE MSSM READ ONLY;
Tablespace altered.
SCOTT@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 11997899460 5 2804928 ONLINE 868465042 YES
2 11997899460 600647 2804928 ONLINE 868465042 YES
3 11997899460 6678 2804928 ONLINE 868465042 YES
4 11997899460 10685 2804928 ONLINE 868465044 YES
5 11997899460 625439 2804928 ONLINE 868465042 YES
6 12688018925 11997383136 2804928 ONLINE 30 NO
6 rows selected.
--可以发现设置read only后, CHECKPOINT_CHANGE#发生了改变,CHECKPOINT_COUNT增加1.
--建立备份.
$ cp /mnt/ramdisk/test/mssm01.dbf /mnt/ramdisk/backup/
SCOTT@test> alter system archive log current ;
System altered.
SCOTT@test> alter system archive log current ;
System altered.
SCOTT@test> alter system archive log current ;
System altered.
SCOTT@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688019011 5 2804928 ONLINE 868465044 YES
2 12688019011 600647 2804928 ONLINE 868465044 YES
3 12688019011 6678 2804928 ONLINE 868465044 YES
4 12688019011 10685 2804928 ONLINE 868465046 YES
5 12688019011 625439 2804928 ONLINE 868465044 YES
6 12688018925 11997383136 2804928 ONLINE 30 NO
6 rows selected.
--MSSM表空间设置read only后, CHECKPOINT_CHANGE#,CHECKPOINT_COUNT不再变化.
3.模拟再次打开mssm表空间为读写,再设置为只读.
SCOTT@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688019011 5 2804928 ONLINE 868465044 YES
2 12688019011 600647 2804928 ONLINE 868465044 YES
3 12688019011 6678 2804928 ONLINE 868465044 YES
4 12688019011 10685 2804928 ONLINE 868465046 YES
5 12688019011 625439 2804928 ONLINE 868465044 YES
6 12688018925 11997383136 2804928 ONLINE 30 NO
6 rows selected.
SCOTT@test> ALTER TABLESPACE MSSM READ write;
Tablespace altered.
SCOTT@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688019011 5 2804928 ONLINE 868465044 YES
2 12688019011 600647 2804928 ONLINE 868465044 YES
3 12688019011 6678 2804928 ONLINE 868465044 YES
4 12688019011 10685 2804928 ONLINE 868465046 YES
5 12688019011 625439 2804928 ONLINE 868465044 YES
6 12688019350 11997383136 2804928 ONLINE 32 YES
6 rows selected.
--可以发现mssm设置read write后, CHECKPOINT_CHANGE#发生了改变,CHECKPOINT_COUNT增加2.
SCOTT@test> ALTER TABLESPACE MSSM READ only;
Tablespace altered.
SCOTT@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688019011 5 2804928 ONLINE 868465044 YES
2 12688019011 600647 2804928 ONLINE 868465044 YES
3 12688019011 6678 2804928 ONLINE 868465044 YES
4 12688019011 10685 2804928 ONLINE 868465046 YES
5 12688019011 625439 2804928 ONLINE 868465044 YES
6 12688019384 11997383136 2804928 ONLINE 33 NO
6 rows selected.
--再做1次备份:
$ cp /mnt/ramdisk/test/mssm01.dbf /mnt/ramdisk/backup/mssm01.dbf_good
4.现在假设数据库破坏的情况:
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cd /mnt/ramdisk/test
$ mv mssm01.dbf mssm01.dbf_good
$ cp /mnt/ramdisk/backup/mssm01.dbf .
===上午已经完成了修复,可以参考链接:
http://blog.itpub.net/267265/viewspace-1544583/
我现在想还原使用新的数据文件.
======================
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ cp mssm01.dbf_good mssm01.dbf
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 226492504 bytes
Database Buffers 234881024 bytes
Redo Buffers 10498048 bytes
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688027064 5 2804928 ONLINE 868465056 NO
2 12688027064 600647 2804928 ONLINE 868465056 NO
3 12688027064 6678 2804928 ONLINE 868465056 NO
4 12688027064 10685 2804928 ONLINE 868465058 NO
5 12688027064 625439 2804928 ONLINE 868465056 NO
6 12688019384 11997383136 2804928 ONLINE 33 NO
6 rows selected.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
------------ ------------------ --------------------- ------------ --------------- -------------- -------
1 12688027064 0 12688027064 0 0 SYSTEM
2 12688027064 0 12688027064 0 0 ONLINE
3 12688027064 0 12688027064 0 0 ONLINE
4 12688027064 0 12688027064 0 0 ONLINE
5 12688027064 0 12688027064 0 0 ONLINE
6 12688018925 0 12688018925 0 0 ONLINE
6 rows selected.
--控制文件CHECKPOINT_CHANGE#记录的是12688018925,而数据文件CHECKPOINT_CHANGE#记录的是12688019384. 数据文件6的scn大于控制文件记录的.
SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--依旧报错!
5.如何恢复呢?真实的环境一般是表空间下有许多数据文件,如果通过修改文件头的方式太复杂,而且数据文件太多.
--当然如果有全部archive log,可以选择recover datafile 6.我这里选择它应该是可行的.
--另外的方式最先想到的是建立新的控制文件.
SYS@test> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 90
Next log sequence to archive 92
Current log sequence 92
--为了避免archive log的影响,我改名archive log文件的目录.
$ cd /u01/app/oracle/flash_recovery_area/TEST/archivelog
$ mv 2015_04_08 2015_04_08_xxx
--建立建立控制文件脚本.
SYS@test> alter database backup controlfile to trace as '/tmp/control.ctl';
Database altered.
--抽取建立控制文件脚本,注意没有包括read only的表空间数据文件(根据前面的测试,这里不能包含它).
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/mnt/ramdisk/test/redo01.log' SIZE 50M,
GROUP 2 '/mnt/ramdisk/test/redo02.log' SIZE 50M,
GROUP 3 '/mnt/ramdisk/test/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/mnt/ramdisk/test/system01.dbf',
'/mnt/ramdisk/test/undotbs01.dbf',
'/mnt/ramdisk/test/sysaux01.dbf',
'/mnt/ramdisk/test/users01.dbf',
'/mnt/ramdisk/test/example01.dbf'
CHARACTER SET ZHS16GBK
;
--在建立新的控制文件前要做好旧控制文件的备份.
SYS@test> column name format a40
SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
NAME FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
---------------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/system01.dbf 1 12688027064 0 12688027064 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688027064 0 12688027064 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688027064 0 12688027064 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688027064 0 12688027064 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688027064 0 12688027064 0 0 ONLINE
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688027064 5 2804928 ONLINE 868465056 NO
2 12688027064 600647 2804928 ONLINE 868465056 NO
3 12688027064 6678 2804928 ONLINE 868465056 NO
4 12688027064 10685 2804928 ONLINE 868465058 NO
5 12688027064 625439 2804928 ONLINE 868465056 NO
--看不见数据文件6.
SYS@test> alter database open ;
Database altered.
SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
NAME FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
---------------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/system01.dbf 1 12688027066 0 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688027066 0 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688027066 0 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688027066 0 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688027066 0 0 0 ONLINE
/u01/app/oracle/product/10.2.0/db_1/dbs/ 6 12688018925 0 12688018925 0 0 OFFLINE
MISSING00006
6 rows selected.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688027066 5 2804928 ONLINE 868465057 YES
2 12688027066 600647 2804928 ONLINE 868465057 YES
3 12688027066 6678 2804928 ONLINE 868465057 YES
4 12688027066 10685 2804928 ONLINE 868465059 YES
5 12688027066 625439 2804928 ONLINE 868465057 YES
6 0 0 0 OFFLINE 0
6 rows selected.
SYS@test> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006' to '/mnt/ramdisk/test/mssm01.dbf';
Database altered.
SYS@test> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--重启看看.
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 226492504 bytes
Database Buffers 234881024 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> alter database datafile 6 online ;
Database altered.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688027449 5 2804928 ONLINE 868465058 NO
2 12688027449 600647 2804928 ONLINE 868465058 NO
3 12688027449 6678 2804928 ONLINE 868465058 NO
4 12688027449 10685 2804928 ONLINE 868465060 NO
5 12688027449 625439 2804928 ONLINE 868465058 NO
6 12688019384 11997383136 2804928 ONLINE 33 NO
6 rows selected.
SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
NAME FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
---------------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/system01.dbf 1 12688027449 0 12688027449 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688027449 0 12688027449 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688027449 0 12688027449 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688027449 0 12688027449 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688027449 0 12688027449 0 0 ONLINE
/mnt/ramdisk/test/mssm01.dbf 6 12688018925 0 12688018925 0 0 ONLINE
6 rows selected.
--可以发现这个时候数据文件6两者记录的不一致.
SYS@test> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> alter database datafile 6 offline ;
Database altered.
SYS@test> alter database open ;
Database altered.
SYS@test> select 12688019384-2*power(2,32) from dual ;
12688019384-2*POWER(2,32)
-------------------------
4098084792
--修改ts$表的相应记录:
SYS@test> select name,scnwrp,scnbas from ts$;
NAME SCNWRP SCNBAS
---------------------------------------- ------------ ------------
SYSTEM 0 0
UNDOTBS1 0 0
SYSAUX 0 0
TEMP 0 0
USERS 0 0
UNDOTBS2 0 0
EXAMPLE 0 0
MSSM 2 4098084333
8 rows selected.
SYS@test> update ts$ set scnbas=4098084792 where name='MSSM';
1 row updated.
SYS@test> commit ;
Commit complete.
SYS@test> select name,scnwrp,scnbas from ts$;
NAME SCNWRP SCNBAS
---------------------------------------- ------------ ------------
SYSTEM 0 0
UNDOTBS1 0 0
SYSAUX 0 0
TEMP 0 0
USERS 0 0
UNDOTBS2 0 0
EXAMPLE 0 0
MSSM 2 4098084792
8 rows selected.
6.重新建立控制文件继续测试.
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 226492504 bytes
Database Buffers 234881024 bytes
Redo Buffers 10498048 bytes
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/mnt/ramdisk/test/redo01.log' SIZE 50M,
GROUP 2 '/mnt/ramdisk/test/redo02.log' SIZE 50M,
GROUP 3 '/mnt/ramdisk/test/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/mnt/ramdisk/test/system01.dbf',
'/mnt/ramdisk/test/undotbs01.dbf',
'/mnt/ramdisk/test/sysaux01.dbf',
'/mnt/ramdisk/test/users01.dbf',
'/mnt/ramdisk/test/example01.dbf'
CHARACTER SET ZHS16GBK
;
SYS@test> alter database open ;
Database altered.
SYS@test> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00006' to '/mnt/ramdisk/test/mssm01.dbf';
Database altered.
SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
NAME FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
---------------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/system01.dbf 1 12688027832 0 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688027832 0 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688027832 0 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688027832 0 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688027832 0 0 0 ONLINE
/mnt/ramdisk/test/mssm01.dbf 6 12688019384 0 12688019384 0 0 OFFLINE
6 rows selected.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688027832 5 2804928 ONLINE 868465061 YES
2 12688027832 600647 2804928 ONLINE 868465061 YES
3 12688027832 6678 2804928 ONLINE 868465061 YES
4 12688027832 10685 2804928 ONLINE 868465063 YES
5 12688027832 625439 2804928 ONLINE 868465061 YES
6 0 0 0 OFFLINE 0
6 rows selected.
SYS@test> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@test> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 226492504 bytes
Database Buffers 234881024 bytes
Redo Buffers 10498048 bytes
Database mounted.
SYS@test> alter database datafile 6 online ;
Database altered.
--很奇怪,在open情况下设置online,会出现上面的提示:ORA-01113: file 6 needs media recovery.
SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile;
NAME FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
---------------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/system01.dbf 1 12688028415 0 12688028415 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688028415 0 12688028415 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688028415 0 12688028415 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688028415 0 12688028415 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688028415 0 12688028415 0 0 ONLINE
/mnt/ramdisk/test/mssm01.dbf 6 12688019384 0 12688019384 0 0 ONLINE
6 rows selected.
SYS@test> SELECT file#, CHECKPOINT_CHANGE#, CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ
------------ ------------------ ---------------- ----------------- ------- ---------------- ---
1 12688028415 5 2804928 ONLINE 868465064 NO
2 12688028415 600647 2804928 ONLINE 868465064 NO
3 12688028415 6678 2804928 ONLINE 868465064 NO
4 12688028415 10685 2804928 ONLINE 868465066 NO
5 12688028415 625439 2804928 ONLINE 868465064 NO
6 12688019384 11997383136 2804928 ONLINE 33 NO
6 rows selected.
SYS@test> alter database open ;
Database altered.
SYS@test> select * from scott.deptx ;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
总结:
1.使用新建控制文件可行.建立时不包含只读表空间的数据文件.
2.open,修改相应sys.ts$记录.
3.再重建控制文件,必须在回到mount,online数据文件.在打开就ok了.
4.补充1点,实际上在open状态,应该使用如下命令就没有这么麻烦了.
SYS@test> alter tablespace mssm online;
Tablespace altered.
使用alter database datafile 6 online ;一定要恢复的.
--我后来又做了一次测试:
SYS@test> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
SYS@test> alter tablespace mssm online;
Tablespace altered.
SYS@test> select * from scott.deptx ;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON