请登录后发表评论
登录
全部评论
SCN是oracle挂在墙上的时钟。早上起床，曰“起床SCN”；吃早餐，名“早餐SCN”；出门上班，称之为“出门SCN”。我们的任何活动，都会对应一个SCN。我们可借助oracle内部的一个包来获取系统的SCN（注意：这里只是系统的scn，因为，oracle还有commit scn，checkpoint scn，select scn等等）。
SQL> select dbms_flashback.get_system_change_number "system\'s scn" from dual;
system\'s scn
------------
555956 oracle内部只有一个SCN，其他的都是来自它。我们还可以看一下数据库里面最小的SCN。
SQL> select creation_change# "oracle内部最小scn" from v$datafile where file#=1;
oracle内部最小scn
-----------------
9 我们加在oracle身上的事，无论好坏，oracle都会依据SCN，一一记在心里（日志），莫敢相忘。由于SCN是递增的，我们对应到相关的SCN，就能找到那个时刻，我们对oracle所做的事。这便是SCN的重要性。SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 5 CURRENT
2 3 INACTIVE
3 4 INACTIVE 接下来，我们对oracle做件事。我们建个表t，有两个字段。其中，字段scn可以约等于事务开始的scn。
SQL> create table t(id int,scn number) tablespace users;
Table created.
SQL> insert into t values(1,dbms_flashback.get_system_change_number);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID SCN
---------- ----------
1 585887
我们先把这件事缓缓，来看看v$log里面的first_change#。
SQL> alter session set nls_date_format=\'yyyy/mm/dd hh24:mi:ss\';
Session altered.
SQL> select group#,status,first_change#,first_time from v$log;
GROUP# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------------- ------------- -------------------
1 CURRENT 583374 2012/07/17 19:59:23
2 INACTIVE 560959 2012/07/17 17:13:32
3 INACTIVE 560981 2012/07/17 17:14:33 这里的first_change#和first_time是一样的，都是SCN的两种表现形式。first_change#是日志组成为当前日志组时所取的系统的SCN，来作为这一组最小或者开始的SCN。我们所做的事，对应的SCN，都会比first_change#来得大。SQL> alter system switch logfile; System altered.
SQL> select group#,status,first_change#,first_time from v$log;
GROUP# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------------- ------------- -------------------
1 ACTIVE 583374 2012/07/17 19:59:23
2 CURRENT 586090 2012/07/18 09:35:40
3 INACTIVE 560981 2012/07/17 17:14:33 现在当前日志组变成了第2组，first_change#也发生了变化。SQL> insert into t values(2,dbms_flashback.get_system_change_number);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID SCN
---------- ----------
1 585887
2 586129
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,first_change#,first_time from v$log;
GROUP# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------------- ------------- -------------------
1 ACTIVE 583374 2012/07/17 19:59:23
2 ACTIVE 586090 2012/07/18 09:35:40
3 CURRENT 586181 2012/07/18 09:39:21SQL> insert into t values(3,dbms_flashback.get_system_change_number);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into t values (4,dbms_flashback.get_system_change_number);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into t values (5,dbms_flashback.get_system_change_number);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into t values(6,dbms_flashback.get_system_change_number);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into t values (7,dbms_flashback.get_system_change_number);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into t values (8,dbms_flashback.get_system_change_number);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select * from t;
ID SCN
---------- ----------
1 585887
2 586129
3 586643
4 586666
5 586692
6 586722
7 586751
8 586805SQL> select group#,status,first_change#,first_time from v$log;
GROUP# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------------- ------------- -------------------
1 ACTIVE 586734 2012/07/18 09:45:12
2 ACTIVE 586762 2012/07/18 09:46:15
3 CURRENT 586816 2012/07/18 09:47:15SQL> insert into t values(9,dbms_flashback.get_system_change_number); 1 row created. SQL> commit; Commit complete.
SQL> insert into t values(10,dbms_flashback.get_system_change_number); 1 row created.
[oracle@localhost ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 17 20:48:19 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. [oracle@localhost ORCL]$ cd datafile/ [oracle@localhost datafile]$ ls o1_mf_example_8050jhm7_.dbf o1_mf_temp_8050j34j_.tmp o1_mf_sysaux_8050fk3w_.dbf o1_mf_undotbs1_8050fkc6_.dbf o1_mf_system_8050fk2z_.dbf o1_mf_users_8050fkdh_.dbf [oracle@localhost datafile]$ rm o1_mf_system_8050fk2z_.dbf [oracle@localhost datafile]$ rm o1_mf_sysaux_8050fk3w_.dbf [oracle@localhost datafile]$ rm o1_mf_users_8050fkdh_.dbf [oracle@localhost datafile]$ rm o1_mf_undotbs1_8050fkc6_.dbf
[oracle@localhost ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 17 20:57:00 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn / as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 419430400 bytes Fixed Size 1219760 bytes Variable Size 142607184 bytes Database Buffers 272629760 bytes Redo Buffers 2973696 bytes Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: \'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_8050fk2z_.dbf\'
SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: \'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_8050fk2z_.dbf\'
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 587004
2 587004
3 587004
4 587004
5 587004
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 583375
2 0
3 0
4 0
5 587004SQL> select sequence#,first_change#,next_change# from v$archived_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
5 544404 558719
6 558719 559931
7 559931 560709
8 560709 560959
9 560959 560981
10 560981 583374SQL> select sequence#,first_change#,next_change# from v$archived_log
2 where 583375>=first_change# and
3 583375<=next_change#;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
11 583374 586090SQL> select sequence#,first_change#,next_change# from v$archived_log
2 where sequence#>=11;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
11 583374 586090
12 586090 586181
13 586181 586656
14 586656 586676
15 586676 586704
16 586704 586734
17 586734 586762
18 586762 586816SQL> recover datafile 1;
ORA-00279: change 583375 generated at 07/17/2012 19:59:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_11_%u_.ar
c
ORA-00280: change 583375 for thread 1 is in sequence #11
Specify log: {=suggested | filename | AUTO | CANCEL} ORA-00279: change 586090 generated at 07/18/2012 09:35:40 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_12_%u_.ar
c
ORA-00280: change 586090 for thread 1 is in sequence #12
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_11_80d4q
dmh_.arc\' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL} SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 586090
2 0
3 0
4 0
5 587004Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 586181 generated at 07/18/2012 09:39:21 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_13_%u_.ar
c
ORA-00280: change 586181 for thread 1 is in sequence #13
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_12_80d4y
9fp_.arc\' no longer needed for this recovery
ORA-00279: change 586656 generated at 07/18/2012 09:42:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_14_%u_.ar
c
ORA-00280: change 586656 for thread 1 is in sequence #14
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_13_80d53
g42_.arc\' no longer needed for this recovery
ORA-00279: change 586676 generated at 07/18/2012 09:42:59 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_15_%u_.ar
c
ORA-00280: change 586676 for thread 1 is in sequence #15
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_14_80d55
41h_.arc\' no longer needed for this recovery
ORA-00279: change 586704 generated at 07/18/2012 09:44:03 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_16_%u_.ar
c
ORA-00280: change 586704 for thread 1 is in sequence #16
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_15_80d57
315_.arc\' no longer needed for this recovery
Log applied.
Media recovery complete. SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 587003
2 0
3 0
4 0
5 587004SQL> select group#,sequence#,status,first_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE#
---------- ---------- ---------------- -------------
1 17 INACTIVE 586734
3 19 CURRENT 586816
2 18 INACTIVE 586762SQL> recover database;
ORA-00279: change 583375 generated at 07/17/2012 19:59:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_11_%u_.ar
c
ORA-00280: change 583375 for thread 1 is in sequence #11
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 586090 generated at 07/18/2012 09:35:40 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_12_%u_.ar
c
ORA-00280: change 586090 for thread 1 is in sequence #12
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_11_80d4q
dmh_.arc\' no longer needed for this recovery
ORA-00279: change 586181 generated at 07/18/2012 09:39:21 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_13_%u_.ar
c
ORA-00280: change 586181 for thread 1 is in sequence #13
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_12_80d4y
9fp_.arc\' no longer needed for this recovery
ORA-00279: change 586656 generated at 07/18/2012 09:42:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_14_%u_.ar
c
ORA-00280: change 586656 for thread 1 is in sequence #14
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_13_80d53
g42_.arc\' no longer needed for this recovery
ORA-00279: change 586676 generated at 07/18/2012 09:42:59 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_15_%u_.ar
c
ORA-00280: change 586676 for thread 1 is in sequence #15
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_14_80d55
41h_.arc\' no longer needed for this recovery
ORA-00279: change 586704 generated at 07/18/2012 09:44:03 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_16_%u_.ar
c
ORA-00280: change 586704 for thread 1 is in sequence #16
ORA-00278: log file
\'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_15_80d57
315_.arc\' no longer needed for this recovery
Log applied.
Media recovery complete. SQL> alter database open; Database altered.
事务对应的scn如果落在了哪个archivelog里，那么这个archivelog在恢复时就被用到 .
总结，这篇博客里，我利用SCN和正常关机下数据文件损坏的完全恢复来帮助自己和大家一起理解oracle备份与恢复的原理。如果有足，希望走过路过的网友，给力批评。oracle的备份与恢复是门艺术。大家一起成长。go for it。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26515977/viewspace-1207702/ ，如需转载，请注明出处，否则将追究法律责任。
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
违法和不良信息举报电话:18101376593
