[20150408]只讀表空間以及資料庫恢復2.txt
[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1548059/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20150408]只讀表空間以及資料庫恢復.txt資料庫
- 【Oracle】rman 恢復只讀表空間資料庫Oracle資料庫
- 設定EXCLUDE後STANDBY資料庫只讀表空間的恢復資料庫
- Oracle 11g RMAN恢復-只讀表空間的恢復Oracle
- Oracle 11g RMAN恢復-只讀表空間的恢復（備份是在表空間只讀狀態下做的)Oracle
- [20170623]利用傳輸表空間恢復資料庫2.txt資料庫
- Oracle 11g RAN恢復-表空間在只讀時做了資料庫的備份Oracle資料庫
- 表空間級資料庫備份恢復資料庫
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 只讀表空間的恢復問題--預備知識
- rman恢復資料檔案 恢復表空間
- 備份與恢復--一個表空間能否被多個資料庫讀寫？資料庫
- 【資料庫資料恢復】SQL Server資料庫磁碟空間不足的資料恢復案例資料庫資料恢復SQLServer
- 非歸檔庫誤刪表空間後的資料恢復資料恢復
- 如何用rman 不備份只讀表空間的資料
- Oracle RMAN 表空間恢復Oracle
- mysql 無備份恢復drop資料-共享表空間MySql
- 撤消表空間資料檔案丟失的恢復.
- 記一次undo表空間資料塊恢復
- 【資料庫資料恢復】磁碟空間不足導致sql server錯誤的資料恢復資料庫資料恢復SQLServer
- DB2 使用表空間備份恢復庫DB2
- PostgreSQL:表空間-->資料庫-->表SQL資料庫
- 【管理篇備份恢復】rman恢復測試（一） 表空間資料檔案
- 【Oracle 恢復表空間】 實驗Oracle
- 恢復Oracle表空間的方法Oracle
- SYSAUX表空間管理及恢復UX
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- Backup And Recovery User's Guide-執行完全資料庫恢復-執行表空間的完全恢復GUIIDE資料庫
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- ORACLE 只讀資料檔案備份與恢復Oracle
- 【RMAN】SYSTEM表空間資料檔案丟失恢復模擬
- 【資料庫資料恢復】SqlServer資料庫無法讀取的資料恢復案例資料庫資料恢復SQLServer
- 表空間TSPITR恢復-實驗
- Oracle RMAN 表空間的完全恢復Oracle
- 資料庫恢復中需要大量儲存空間的原因HQ資料庫
- Oracle12c多租戶資料庫備份與恢復 - 備份表空間Oracle資料庫
- Oracle 11g資料庫恢復：場景10：新建表空間沒有備份Oracle資料庫
- 檢視資料庫表空間資料庫