[20150408]只讀表空間以及資料庫恢復.txt
[20150408]只讀表空間以及資料庫恢復.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.
ALTER TABLESPACE MSSM READ ONLY;
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 .
--如果現在啟動資料庫,一定存在問題,因為mssm表空間的資料檔案CHECKPOINT_CHANGE#,CHECKPOINT_COUNT與控制檔案不一致.
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> 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 12688019467 5 2804928 ONLINE 868465045 NO
2 12688019467 600647 2804928 ONLINE 868465045 NO
3 12688019467 6678 2804928 ONLINE 868465045 NO
4 12688019467 10685 2804928 ONLINE 868465047 NO
5 12688019467 625439 2804928 ONLINE 868465045 NO
6 12688018925 11997383136 2804928 ONLINE 30 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 12688019467 0 12688019467 0 0 SYSTEM
2 12688019467 0 12688019467 0 0 ONLINE
3 12688019467 0 12688019467 0 0 ONLINE
4 12688019467 0 12688019467 0 0 ONLINE
5 12688019467 0 12688019467 0 0 ONLINE
6 12688019384 0 12688019384 12688018925 12688019345 ONLINE
6 rows selected.
--控制檔案CHECKPOINT_CHANGE#記錄的是12688019384,而資料檔案CHECKPOINT_CHANGE#記錄的是12688018925.
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',
'/mnt/ramdisk/test/mssm01.dbf'
CHARACTER SET ZHS16GBK
;
--在建立新的控制檔案前要做好舊控制檔案的備份.
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> 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 12688019467 0 12688019467 0 0 SYSTEM
2 12688019467 0 12688019467 0 0 ONLINE
3 12688019467 0 12688019467 0 0 ONLINE
4 12688019467 0 12688019467 0 0 ONLINE
5 12688019467 0 12688019467 0 0 ONLINE
6 12688019467 0 12688019467 0 0 RECOVER
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 12688019467 5 2804928 ONLINE 868465045 NO
2 12688019467 600647 2804928 ONLINE 868465045 NO
3 12688019467 6678 2804928 ONLINE 868465045 NO
4 12688019467 10685 2804928 ONLINE 868465047 NO
5 12688019467 625439 2804928 ONLINE 868465045 NO
6 12688018925 11997383136 2804928 ONLINE 30 NO
6 rows selected.
--實際上這樣系統認為mssm不是隻讀的,要恢復到12688019467.
6.重新測試建立新控制檔案不包括mssm表空間的資料檔案看看.
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> 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 12688019469 0 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688019469 0 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688019469 0 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688019469 0 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688019469 0 0 0 ONLINE
/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING000 6 12688019384 0 12688019384 0 0 OFFLINE
06
6 rows selected.
--這樣6號檔案指向存在問題.
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 12688019469 0 0 0 SYSTEM
/mnt/ramdisk/test/undotbs01.dbf 2 12688019469 0 0 0 ONLINE
/mnt/ramdisk/test/sysaux01.dbf 3 12688019469 0 0 0 ONLINE
/mnt/ramdisk/test/users01.dbf 4 12688019469 0 0 0 ONLINE
/mnt/ramdisk/test/example01.dbf 5 12688019469 0 0 0 ONLINE
/mnt/ramdisk/test/mssm01.dbf 6 12688019384 0 12688019384 0 0 OFFLINE
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'
--透過上面的查詢v$datafile,可以發現資料檔案6的CHECKPOINT_CHANGE#=12688019384.我建立的控制檔案是新的,這麼講這些資訊在恢復時從哪裡取出來放入控制檔案的呢?
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.
SYS@test> select 2*power(2,32)+4098084792 from dual ;
POWER(2,33)+4098084792
----------------------
12688019384
--正好對上.
SYS@test> select 12688018925-power(2,33) from dual ;
12688018925-POWER(2,33)
-----------------------
4098084333
--如果我修改ts#的SCNBAS=4098084333是否可行呢?
SYS@test> update ts$ set scnbas=4098084333 where name='MSSM';
1 row updated.
SYS@test> commit ;
Commit complete.
SYS@test> alter system checkpoint ;
System altered.
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> 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> 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> 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 12688021949 5 2804928 ONLINE 868465054 NO
2 12688021949 600647 2804928 ONLINE 868465054 NO
3 12688021949 6678 2804928 ONLINE 868465054 NO
4 12688021949 10685 2804928 ONLINE 868465056 NO
5 12688021949 625439 2804928 ONLINE 868465054 NO
6 0 0 0 OFFLINE 0
6 rows selected.
--依舊不行.看到的資訊都是0,估計mssm01.dbf已經被改寫了.關閉資料庫,然後重新覆蓋
$ cp /mnt/ramdisk/backup/mssm01.dbf .
/bin/cp: overwrite `./mssm01.dbf'? y
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> 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 12688021949 5 2804928 ONLINE 868465054 NO
2 12688021949 600647 2804928 ONLINE 868465054 NO
3 12688021949 6678 2804928 ONLINE 868465054 NO
4 12688021949 10685 2804928 ONLINE 868465056 NO
5 12688021949 625439 2804928 ONLINE 868465054 NO
6 0 0 0 OFFLINE 0
6 rows selected.
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 12688021949 5 2804928 ONLINE 868465054 NO
2 12688021949 600647 2804928 ONLINE 868465054 NO
3 12688021949 6678 2804928 ONLINE 868465054 NO
4 12688021949 10685 2804928 ONLINE 868465056 NO
5 12688021949 625439 2804928 ONLINE 868465054 NO
6 12688018925 11997383136 2804928 ONLINE 30 NO
6 rows selected.
--OK,現在應該可以了.
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
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 12688021950 5 2804928 ONLINE 868465055 YES
2 12688021950 600647 2804928 ONLINE 868465055 YES
3 12688021950 6678 2804928 ONLINE 868465055 YES
4 12688021950 10685 2804928 ONLINE 868465057 YES
5 12688021950 625439 2804928 ONLINE 868465055 YES
6 12688018925 11997383136 2804928 ONLINE 30 NO
6 rows selected.
--總結:
1.有點亂.我自己在重複1次.
2.採用新建立控制檔案的方法的恢復,比較簡單的方法修改系統sys.ts$相應記錄.
3.我的測試環境在設定讀寫時,沒有執行任何ddl,dml語句在相應表空間,如果執行了會存在與系統記錄不一致的情況,這樣會出現什麼情況
呢?下午繼續測試.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1544583/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20150408]只讀表空間以及資料庫恢復2.txt資料庫
- 【Oracle】rman 恢復只讀表空間資料庫Oracle資料庫
- 設定EXCLUDE後STANDBY資料庫只讀表空間的恢復資料庫
- Oracle 11g RMAN恢復-只讀表空間的恢復Oracle
- Oracle 11g RMAN恢復-只讀表空間的恢復（備份是在表空間只讀狀態下做的)Oracle
- Oracle 11g RAN恢復-表空間在只讀時做了資料庫的備份Oracle資料庫
- 表空間級資料庫備份恢復資料庫
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 只讀表空間的恢復問題--預備知識
- rman恢復資料檔案 恢復表空間
- [20170623]利用傳輸表空間恢復資料庫2.txt資料庫
- [20170623]利用傳輸表空間恢復部分資料.txt
- 備份與恢復--一個表空間能否被多個資料庫讀寫？資料庫
- 【資料庫資料恢復】SQL Server資料庫磁碟空間不足的資料恢復案例資料庫資料恢復SQLServer
- 非歸檔庫誤刪表空間後的資料恢復資料恢復
- [20170627]使用TSPITR恢復表空間.txt
- 如何用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資料庫
- 檢視資料庫表空間資料庫