[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语句在相应表空间,如果执行了会存在与系统记录不一致的情况,这样会出现什么情况
呢?下午继续测试.
