实验一：热备异常结束的恢复
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4249793 894
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4249793 921
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4249793 890
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4249793 30
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4249793 57
D:\TBS01_1.DBF NO YES 4249793 14
D:\TBS01_2.DBF NO YES 4249793 14
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4249793 123
已选择10行。
SQL> select * from t01;
SCN
----------
4206022
SQL> insert into t01 values (dbms_flashback.get_system_change_number);
已创建 1 行。
SQL> select * from t01;
SCN
----------
4206022
4250172
SQL> commit;
提交完成。
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4249793 894
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4249793 921
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4249793 890
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4249793 30
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4249793 57
D:\TBS01_1.DBF NO YES 4249793 14
D:\TBS01_2.DBF NO YES 4249793 14
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4249793 123
已选择10行。
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4249793 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4249793 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4249793 4143602 ONLINE
D:\TBS01_1.DBF 4249793 0 ONLINE
D:\TBS01_2.DBF 4249793 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4249793 3748632 ONLINE
已选择10行。
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
4249793
--热备前几个检查点信息是一致的
SQL> alter tablespace tbs_test begin backup;
表空间已更改。
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
4249793
--热备启动后，可以看到tbs_test空间的两个数据文件检查点比其他文件大，因为在热备启动的时候对这两个文件做了检查点
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4249793 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4249793 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4249793 4143602 ONLINE
D:\TBS01_1.DBF 4250230 0 ONLINE
D:\TBS01_2.DBF 4250230 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4249793 3748632 ONLINE
已选择10行。
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4249793 894
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4249793 921
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4249793 890
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4249793 30
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4249793 57
D:\TBS01_1.DBF NO YES 4250230 15
D:\TBS01_2.DBF NO YES 4250230 15
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4249793 123
已选择10行。
SQL> host copy D:\TBS01_1.DBF to D:\TBS01_1.DBF.bak
SQL> host copy 'D:\TBS01_1.DBF' to 'D:\TBS01_1.DBF.bak';
SQL> host copy 'D:\TBS01_1.DBF' 'D:\TBS01_1.DBF.bak';
SQL> host copy D:\TBS01_1.DBF D:\TBS01_1.DBF.bak
SQL> host copy D:\TBS01_2.DBF D:\TBS01_2.DBF.bak
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。
Total System Global Area 603979776 bytes
Fixed Size 1250404 bytes
Variable Size 255855516 bytes
Database Buffers 343932928 bytes
Redo Buffers 2940928 bytes
数据库装载完毕。
--shutdown abort没有做检查点，所有可以看到再次mount后，检查点还是和关闭前保持一致
CHECKPOINT_CHANGE#不一致，这个是需要恢复的。
V$RECOVER_FILE里有，但V$DATAFILE_HEADER里没有提示rec=yes。
这是因为两个的信息来自不同的地方
需要恢复，这个信息V$RECOVER_FILE来自控制文件的知道了。但V$DATAFILE_HEADER来自数据文件头的还不知道
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4249793 894
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4249793 921
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4249793 890
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4249793 30
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4249793 57
D:\TBS01_1.DBF NO YES 4250230 15
D:\TBS01_2.DBF NO YES 4250230 15
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4249793 123
已选择10行。
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4249793 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4249793 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4249793 4143602 ONLINE
D:\TBS01_1.DBF 4250230 0 ONLINE
D:\TBS01_2.DBF 4250230 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4249793 3748632 ONLINE
已选择10行。
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- --------------
8 ONLINE ONLINE 4250230 19-10月-10
9 ONLINE ONLINE 4250230 19-10月-10
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------------
1 NOT ACTIVE 4120170 17-10月-10
2 NOT ACTIVE 4120176 17-10月-10
3 NOT ACTIVE 4120181 17-10月-10
4 NOT ACTIVE 4120187 17-10月-10
5 NOT ACTIVE 4120193 17-10月-10
6 NOT ACTIVE 4120187 17-10月-10
7 NOT ACTIVE 4120199 17-10月-10
8 ACTIVE 4250230 19-10月-10
9 ACTIVE 4250230 19-10月-10
11 NOT ACTIVE 4120205 17-10月-10
已选择10行。
SQL> alter database end backup;
数据库已更改。
--结束热备后，系统自动恢复了
SQL> select * from v$recover_file;
未选定行
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4249793 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4249793 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4249793 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4249793 4143602 ONLINE
D:\TBS01_1.DBF 4250230 0 ONLINE
D:\TBS01_2.DBF 4250230 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4249793 3748632 ONLINE
已选择10行。
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4249793 894
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4249793 931
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4249793 921
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4249793 890
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4249793 30
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4249793 57
D:\TBS01_1.DBF NO YES 4250230 16
D:\TBS01_2.DBF NO YES 4250230 16
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4249793 123
已选择10行。
SQL> alter database open;
数据库已更改。
--打开数据库，所有检查点一致，cnt加了3（open加2，切归档+1）
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4270386 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4270386 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4270386 4143602 ONLINE
D:\TBS01_1.DBF 4270386 0 ONLINE
D:\TBS01_2.DBF 4270386 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4270386 3748632 ONLINE
已选择10行。
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4270386 934
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4270386 897
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4270386 934
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4270386 924
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4270386 893
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4270386 33
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4270386 60
D:\TBS01_1.DBF NO YES 4270386 19
D:\TBS01_2.DBF NO YES 4270386 19
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4270386 126
已选择10行。
SQL> spool off
实验二：热备后异常关闭，用旧备份还原
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 53 10485760 1 YES INACTIVE 4249792 19-10月-10
2 1 54 10485760 1 NO CURRENT 4270385 19-10月-10
3 1 52 10485760 1 YES INACTIVE 4229479 19-10月-10
SQL> select * from t01;
SCN
----------
4206022
4250172
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4270386 934
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4270386 897
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4270386 934
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4270386 924
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4270386 893
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4270386 33
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4270386 60
D:\TBS01_1.DBF NO YES 4270386 19
D:\TBS01_2.DBF NO YES 4270386 19
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4270386 126
已选择10行。
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4270386 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4270386 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4270386 4143602 ONLINE
D:\TBS01_1.DBF4270386 0 ONLINE
D:\TBS01_2.DBF4270386 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4270386 3748632 ONLINE
已选择10行。
SQL> alter tablespace tbs_test begin backup;
表空间已更改。
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4270386 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4270386 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4270386 4143602 ONLINE
D:\TBS01_1.DBF4270955 0 ONLINE
D:\TBS01_2.DBF4270955 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4270386 3748632 ONLINE
已选择10行。
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4270386 934
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4270386 897
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4270386 934
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4270386 924
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4270386 893
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4270386 33
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4270386 60
D:\TBS01_1.DBF NO YES 4270955 20
D:\TBS01_2.DBF NO YES 4270955 20
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4270386 126
已选择10行。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 53 10485760 1 YES INACTIVE 4249792 19-10月-10
2 1 54 10485760 1 NO CURRENT 4270385 19-10月-10
3 1 52 10485760 1 YES INACTIVE 4229479 19-10月-10
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。
Total System Global Area 603979776 bytes
Fixed Size 1250404 bytes
Variable Size 260049820 bytes
Database Buffers 339738624 bytes
Redo Buffers 2940928 bytes
数据库装载完毕。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 53 10485760 1 YES INACTIVE 4249792 19-10月-10
3 1 52 10485760 1 YES INACTIVE 4229479 19-10月-10
2 1 54 10485760 1 NO CURRENT 4270385 19-10月-10
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4270386 934
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4270386 897
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4270386 934
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4270386 924
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4270386 893
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4270386 33
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4270386 60
D:\TBS01_1.DBF NO YES 4270955 20
D:\TBS01_2.DBF NO YES 4270955 20
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4270386 126
已选择10行。
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4270386 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4270386 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4270386 4143602 ONLINE
D:\TBS01_1.DBF4270955 0 ONLINE
D:\TBS01_2.DBF4270955 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4270386 3748632 ONLINE
已选择10行。
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- --------------
8 ONLINE ONLINE 4270955 19-10月-10
9 ONLINE ONLINE 4270955 19-10月-10
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------------
1 NOT ACTIVE 4120170 17-10月-10
2 NOT ACTIVE 4120176 17-10月-10
3 NOT ACTIVE 4120181 17-10月-10
4 NOT ACTIVE 4120187 17-10月-10
5 NOT ACTIVE 4120193 17-10月-10
6 NOT ACTIVE 4120187 17-10月-10
7 NOT ACTIVE 4120199 17-10月-10
8 ACTIVE 4270955 19-10月-10
9 ACTIVE 4270955 19-10月-10
11 NOT ACTIVE 4120205 17-10月-10
已选择10行。
SQL> host cp D:\TBS01_1.DBF.bak D:\TBS01_1.DBF;
SQL> host copy D:\TBS01_1.DBF.bak D:\TBS01_1.DBF;
SQL> host copy D:\TBS01_2.DBF.bak D:\TBS01_2.DBF;
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 8 需要介质恢复
ORA-01110: 数据文件 8: 'D:\TBS01_1.DBF'
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------------
1 NOT ACTIVE 4120170 17-10月-10
2 NOT ACTIVE 4120176 17-10月-10
3 NOT ACTIVE 4120181 17-10月-10
4 NOT ACTIVE 4120187 17-10月-10
5 NOT ACTIVE 4120193 17-10月-10
6 NOT ACTIVE 4120187 17-10月-10
7 NOT ACTIVE 4120199 17-10月-10
8 ACTIVE 4250230 19-10月-10
9 ACTIVE 4250230 19-10月-10
11 NOT ACTIVE 4120205 17-10月-10
已选择10行。
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4270386 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4270386 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4270386 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4270386 4143602 ONLINE
D:\TBS01_1.DBF4270955 0 ONLINE
D:\TBS01_2.DBF4270955 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4270386 3748632 ONLINE
已选择10行。
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4270386 935
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4270386 898
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4270386 935
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4270386 925
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4270386 894
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4270386 34
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4270386 61
D:\TBS01_1.DBF YES YES 4250230 15
D:\TBS01_2.DBF YES YES 4250230 15
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4270386 126
已选择10行。
--通过比对检查点计数，发现是旧的文件，不允许通过结束热备来恢复
--通过比对数据文件和数据文件头的检查点也能发现文件是旧的备份
SQL> alter database end backup;
alter database end backup
*
第 1 行出现错误:
ORA-01235: END BACKUP 对 2 文件失败而对 0 成功
ORA-01122: 数据库文件 9 验证失败
ORA-01110: 数据文件 9: 'D:\TBS01_2.DBF'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
ORA-01122: 数据库文件 8 验证失败
ORA-01110: 数据文件 8: 'D:\TBS01_1.DBF'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
SQL> recover database;
完成介质恢复。
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO NO 4270981 936
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO NO 4270981 899
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO NO 4270981 936
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO NO 4270981 926
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO NO 4270981 895
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO NO 4270981 35
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO NO 4270981 62
D:\TBS01_1.DBF NO NO 4270981 21
D:\TBS01_2.DBF NO NO 4270981 21
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO NO 4270981 127
已选择10行。
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4270981 4270981 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4270981 4270981 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4270981 4270981 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4270981 4270981 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4270981 4270981 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4270981 4270981 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4270981 4270981 4143602 ONLINE
D:\TBS01_1.DBF4270981 4270981 0 ONLINE
D:\TBS01_2.DBF4270981 4270981 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4270981 4270981 3748632 ONLINE
已选择10行。
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------------
1 NOT ACTIVE 4120170 17-10月-10
2 NOT ACTIVE 4120176 17-10月-10
3 NOT ACTIVE 4120181 17-10月-10
4 NOT ACTIVE 4120187 17-10月-10
5 NOT ACTIVE 4120193 17-10月-10
6 NOT ACTIVE 4120187 17-10月-10
7 NOT ACTIVE 4120199 17-10月-10
8 NOT ACTIVE 4250230 19-10月-10
9 NOT ACTIVE 4250230 19-10月-10
11 NOT ACTIVE 4120205 17-10月-10
已选择10行。
SQL> select * from v$recover_file;
未选定行
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 53 10485760 1 YES INACTIVE 4249792 19-10月-10
3 1 52 10485760 1 YES INACTIVE 4229479 19-10月-10
2 1 54 10485760 1 NO CURRENT 4270385 19-10月-10
--数据库打开，检查点计数+3，log发生了切换
SQL> alter database open;
数据库已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 53 10485760 1 YES INACTIVE 4249792 19-10月-10
2 1 54 10485760 1 YES INACTIVE 4270385 19-10月-10
3 1 55 10485760 1 NO CURRENT 4290982 19-10月-10
SQL> select substr(name, 1, 40) dname, CHECKPOINT_CHANGE#,LAST_CHANGE# ,OFFLINE_CHANGE#,status from v$datafile;
DNAME CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ --------------- -------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF 4290983 3748632 SYSTEM
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF 4290983 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF 4290983 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS01.DBF 4290983 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF 4290983 3748632 ONLINE
D:\ORACLE\ORADATA\FOX\USERS02.DBF 4290983 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST01.DBF 4290983 4143602 ONLINE
D:\TBS01_1.DBF4290983 0 ONLINE
D:\TBS01_2.DBF4290983 0 ONLINE
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF 4290983 3748632 ONLINE
已选择10行。
SQL> select substr(name,1,40) dname, recover, fuzzy, checkpoint_change#,CHECKPOINT_COUNT from v$datafile_header;
DNAME REC FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- --- ------------------ ----------------
D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF NO YES 4290983 939
D:\ORACLE\ORADATA\FOX\UNDOTBS01.DBF NO YES 4290983 902
D:\ORACLE\ORADATA\FOX\SYSAUX01.DBF NO YES 4290983 939
D:\ORACLE\ORADATA\FOX\USERS01.DBF NO YES 4290983 929
D:\ORACLE\ORADATA\FOX\EXAMPLE01.DBF NO YES 4290983 898
D:\ORACLE\ORADATA\FOX\USERS02.DBF NO YES 4290983 38
D:\ORACLE\ORADATA\FOX\TEST01.DBF NO YES 4290983 65
D:\TBS01_1.DBF NO YES 4290983 24
D:\TBS01_2.DBF NO YES 4290983 24
D:\ORACLE\ORADATA\FOX\TEST_PERFSTAT.DBF NO YES 4290983 130
已选择10行。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 53 10485760 1 YES INACTIVE 4249792 19-10月-10
2 1 54 10485760 1 YES INACTIVE 4270385 19-10月-10
3 1 55 10485760 1 NO CURRENT 4290982 19-10月-10SQL> select * from t01;
SCN
----------
4206022
4250172
SQL> spool off
阅读(676) | 评论(1) | 转发(0) |
