2008年（22）
分类： Oracle
2008-05-16 16:27:07
测试Oracle SCN的变化以及和数据库恢复的关系。
(1) 打开数据库到Mount状态
V$DATAFILE中的CHECKPOINT_CHANGE#(控制文件中记录的各个数据文件的SCN号)，LAST_CHANGE#(控制文件中记录的各个数据文件的最后改变的SCN号)
V$DATABASE中的CHECKPOINT_CHANGE#(控制文件中记录的系统的SCN号)
V$DATAFILE_HEADER中的CHECKPOINT_CHANGE#(各个物理数据文件中记录的自己的SCN号)
三处的所有值相同。说明上次数据库的关闭是干净的。
[oracle@oracle-svr bin]$ ./sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 22 12:33:02 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/a as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 652581 652581
2 652581 652581
3 652581 652581
4 652581 652581
5 652581 652581
6 652581 652581
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 652581
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 652581
2 652581
3 652581
4 652581
5 652581
6 652581
6 rows selected.
(2) 将数据库启动为open状态则除了LAST_CHANGE#被设置为NULL, 其余3个参数的值被增加了1
SQL> alter database open;
Database altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
--------- ------------------ ------------
1 652582
2 652582
3 652582
4 652582
5 652582
6 652582
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 652582
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 652582
2 652582
3 652582
4 652582
5 652582
6 652582
6 rows selected.
(3) 插入数据到表中, 所有4个参数的值没有任何变化
SQL> insert into test1 values (1,'11');
1 row created.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 652582
2 652582
3 652582
4 652582
5 652582
6 652582
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 652582
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 652582
2 652582
3 652582
4 652582
5 652582
6 652582
6 rows selected.
(4) 提交事务, 所有4个参数的值没有任何变化. (此时在Online Redo文件中应该可以查到新的scn值, 但是还没有记录到控制文件和数据库文件中)
SQL> commit;
Commit complete.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 652582
2 652582
3 652582
4 652582
5 652582
6 652582
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 652582
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 652582
2 652582
3 652582
4 652582
5 652582
6 652582
6 rows selected.
(5) 切换日志文件, 所有4个参数的值没有任何变化(应该是切换到了另外一个Redo文件, 但是还是没有触发日志的归档, 所以没有Checkpoint).
SQL> alter system switch logfile;
System altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 652582
2 652582
3 652582
4 652582
5 652582
6 652582
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 652582
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 652582
2 652582
3 652582
4 652582
5 652582
6 652582
6 rows selected.
(6) 强制执行checkpoint, 结果所有的参数都一致的发生了改变. (与shutdown immediate唯一的区别, LAST_CHANGE#仍旧没有值, 为空)
SQL> alter system checkpoint;
System altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 652927
2 652927
3 652927
4 652927
5 652927
6 652927
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 652927
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 652927
2 652927
3 652927
4 652927
5 652927
6 652927
6 rows selected.
(7) 将users表空间脱机. users表空间的数据文件的参数发生了如下的改变
控制文件中, FILE#=4的数据文件的CHECKPOINT_CHANGE#,LAST_CHANGE#全部增加到一个新值
数据库文件中, FILE#=4的数据文件的CHECKPOINT_CHANGE#变为了0, 也就是无法读出.
其他值没有任何变化.
SQL> alter tablespace users offline;
Tablespace altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 652927
2 652927
3 652927
4 652971 652971
5 652927
6 652927
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 652927
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 652927
2 652927
3 652927
4 0
5 652927
6 652927
6 rows selected.
(8) 在一个表中插入数据, 并且强制checkpoint, 则除了FILE#=4的其他数据文件的CHECKPOINT_CHANGE#都增加到一个新的值
此时SCN在继续增加, 但是脱机的数据文件的参数的SCN不发生变化.
SQL> insert into test1 values(9,'999');
1 row created.
SQL> select * from test1;
ID NAME
---------- --------------------
1 aaa
10 100
20 2020
1 11
9 999
1 111
1 ..
7 rows selected.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 652927
2 652927
3 652927
4 652971 652971
5 652927
6 652927
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 652927
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 652927
2 652927
3 652927
4 0
5 652927
6 652927
6 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 653089
2 653089
3 653089
4 652971 652971
5 653089
6 653089
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 653089
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 653089
2 653089
3 653089
4 0
5 653089
6 653089
6 rows selected.
(9) 将表空间users Online, 则FILE#=4的CHECKPOINT_CHANGE#增加到一个新值, 同时LAST_CHANGE#被重新置为NULL
其他参数的值维持不变
SQL> alter tablespace users online;
Tablespace altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 653089
2 653089
3 653089
4 653139
5 653089
6 653089
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 653089
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 653089
2 653089
3 653089
4 653139
5 653089
6 653089
6 rows selected.
(10) 将表空间users设置为备份状态. 则FILE#=4的CHECKPOINT_CHANGE#增加到了一个新值, LAST_CHANGE#仍旧为空
其他参数的值维持不变.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 653089
2 653089
3 653089
4 653550
5 653089
6 653089
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 653089
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 653089
2 653089
3 653089
4 653550
5 653089
6 653089
6 rows selected.
(11) 结束表空间的备份状态. 所有参数同上一步, 没有变化
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 653089
2 653089
3 653089
4 653550
5 653089
6 653089
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 653089
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 653089
2 653089
3 653089
4 653550
5 653089
6 653089
6 rows selected.
(12) 重新让表空间进入备份状态, 参数的变化同第10步.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 653089
2 653089
3 653089
4 653894
5 653089
6 653089
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 653089
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 653089
2 653089
3 653089
4 653894
5 653089
6 653089
6 rows selected.
(13) 插入数据到表, test2就在users表空间. 说明备份状态下的表空间是可以正常使用的, 此时数据的更新记录在redo日志中.
强制checkpoint后, FILE#=4的数据文件的参数没有变化, 其他参数增加到一个新的值.
SQL> insert into test1 values(1,'aaa');
1 row created.
SQL> select * from test2;
ID NAME
---------- ----------
1 sss
3 333
SQL> insert into test2 values(2,'kkk');
1 row created.
SQL> alter system checkpoint;
System altered.
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 653944
2 653944
3 653944
4 653894
5 653944
6 653944
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 653944
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 653944
2 653944
3 653944
4 653894
5 653944
6 653944
6 rows selected.
(14)通过shutdown abort关闭数据库(此前没有commit两个insert事务, 这两个更新重新启动DB后会消失). 此时4个参数的值是不全一致的. 同时用原来的表空间的备份数据文件替换现在的数据文件
启动数据库后提示:须要介质恢复.
[oracle@oracle-svr demo1]$ rm users01.dbf
[oracle@oracle-svr demo1]$ ls
control01.ctl example01.dbf redo03.log temp01.dbf testtb2.dbf
control02.ctl redo01.log sysaux01.dbf testdb2.dbf testtb.dbf
control03.ctl redo02.log system01.dbf testtb1.dbf undotbs01.dbf
[oracle@oracle-svr demo1]$ cd ..
[oracle@oracle-svr oradata]$ ls
arclog demo1
[oracle@oracle-svr oradata]$ cp arclog/users01.dbf demo1/
[oracle@oracle-svr oradata]$ ls demo1/
control01.ctl redo01.log system01.dbf testtb2.dbf
control02.ctl redo02.log temp01.dbf testtb.dbf
control03.ctl redo03.log testdb2.dbf undotbs01.dbf
example01.dbf sysaux01.dbf testtb1.dbf users01.dbf
RMAN> startup;
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/22/2007 13:10:43
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/product/10.2.0/oradata/demo1/users01.dbf'
SQL> conn sys/1 as sysdba
Connected.
(15) 通过归档日志恢复，完成。但是提示：另外一个system表空间需要进一步的恢复。
此时归档日志已经应用完成。因为关闭数据库的时候SCN并不一致，所以需要通过redo文件进一步恢复。
此时如果redo文件有问题，则麻烦就大了。必须所有的数据库都恢复前一个全备份的版本，然后再通过归档日志来恢复。
这就是shutdown abort关机
SQL> recover database until cancel;
ORA-00279: change 652581 generated at 08/22/2007 12:29:14 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/DEMO1/archivelog/2007_08
_22/o1_mf_1_9_%u_.arc
ORA-00280: change 652581 for thread 1 is in sequence #9
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 652894 generated at 08/22/2007 12:40:20 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/DEMO1/archivelog/2007_08
_22/o1_mf_1_10_%u_.arc
ORA-00280: change 652894 for thread 1 is in sequence #10
ORA-00278: log file
'/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/DEMO1/archivelog/2007_0
8_22/o1_mf_1_9_3dqhkndt_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log
'/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/DEMO1/archivelog/2007_0
8_22/o1_mf_1_10_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/product/10.2.0/oradata/demo1/system01.dbf'
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
此时没有测试通过resetlogs能否正确打开数据库, 因为有needs more recovery to be consistent的提示, 多半会有问题.
(16) 通过redo日志恢复数据库, 数据库正常打开, 没有使用resetlogs
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from test2;
ID NAME
---------- ----------
1 sss
3 333
SQL> insert into test2 values(4,'1111');
1 row created.
SQL> select * from test2;
ID NAME
---------- ----------
1 sss
3 333
1 222
4 1111
SQL> alter system checkpoint;
System altered.
SQL> select * from test2;
ID NAME
---------- ----------
1 sss
3 333
1 222
4 1111
(17) 这一回儿使用shutdown immediate. 此时会提示commit or rollback, 使用shutdown abort则自动rollback
SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
SQL> commit;
Commit complete.
(18) 因为users表空间还没有使用end backup命令结束, 所以还是有提示file 4 has online backup set
只好用shutdown abort关闭数据库
SQL> shutdown immediate
ORA-01149: cannot shutdown - file 4 has online backup set
ORA-01110: data file 4: '/u01/app/oracle/product/10.2.0/oradata/demo1/users01.dbf'
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
(19) 重新启动数据库, 提示数据文件4须要介质恢复
SQL> startup;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 113247824 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'/u01/app/oracle/product/10.2.0/oradata/demo1/users01.dbf'
(20) 通过redo日志恢复后, 正常打开数据库. 因为前面commit了事务, 所以recover后, 表中的后两个记录没有丢掉.
SQL> recover
Media recovery complete.
SQL> alter database open;
select * from test2;
Database altered.
SQL>select * from test2;
ID NAME
---------- ----------
1 sss
3 333
1 222
4 1111
SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 695412
2 695412
3 695412
4 695412
5 695412
6 695412
6 rows selected.
SQL> select NAME ,CHECKPOINT_CHANGE# from v$database;
NAME CHECKPOINT_CHANGE#
------------------ ------------------
DEMO1 695412
SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 695412
2 695412
3 695412
4 695412
5 695412
6 695412
6 rows selected.
