Inactive redo log丢失或损坏的恢复
将数据库置于非归档模式
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 90 Current log sequence 91 |
INACTIVE STATUS：redo log里面的日志所对应的buffer cache里的脏块都写到datafile中了
# 模拟出丢失或损坏的INACTIVE redo log，如第2组 select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- 1 1 91 52428800 512 1 NO CURRENT 4256107 2018-04-02 22:06:01 2.8147E+14 2 1 89 52428800 512 1 YES INACTIVE 4227420 2018-04-02 07:00:17 4254495 2018-04-02 22:00:17 3 1 90 52428800 512 1 YES INACTIVE 4254495 2018-04-02 22:00:17 4256107 2018-04-02 22:06:01 col MEMBER for a50 select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO # 下面模拟2损坏 1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo02.log bs=512 count=20 # 改后可以直接正常关闭数据库 shutdown immediate # 但打开数据库时报错 startup #----------------------------------------------------------------------------------- ORACLE instance started. Total System Global Area 4375998464 bytes Fixed Size 2260328 bytes Variable Size 889193112 bytes Database Buffers 3472883712 bytes Redo Buffers 11661312 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 18001 Session ID: 191 Serial number: 3 #----------------------------------------------------------------------------------- # alert日志中有如下错误信息 #----------------------------------------------------------------------------------- Tue Apr 03 16:08:12 2018 ARC0 started with pid=20, OS id=18003 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_17981.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' # 提示打开 log group 2 失败 ORA-27047: unable to read the header block of file Linux-x86_64 Error: 25: Inappropriate ioctl for device Additional information: 1 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_17981.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' ORA-27047: unable to read the header block of file Linux-x86_64 Error: 25: Inappropriate ioctl for device Additional information: 1 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18001.trc: ORA-00313: open failed for members of log group 1 of thread ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' #----------------------------------------------------------------------------------- # 这时，先正常打开数据库到mount状态 startup mount # 查看log group 2状态 col MEMBER for a50 select l.STATUS,f.member,BYTES/1024/1024 size_m from v$log l, v$logfile f where l.GROUP#=2 and l.GROUP#=f.GROUP#; STATUS MEMBER SIZE_M ---------------- -------------------------------------------------- ---------- INACTIVE /u01/app/oracle/oradata/orcl/redo02.log 50 # 在控制文件中清除logfile group 2 alter database clear logfile group 2; alter database drop logfile group 2; # 删除logfile group 2对应的文件 ! rm -rf /u01/app/oracle/oradata/orcl/redo02.log # 增加logfile group 2 alter database add logfile group 2 ('/u01/app/oracle/oradata/orcl/redo02.log') size 50m; # 能正常打开数据库了 SQL> alter database open; Database altered. |
Active redo log丢失或损坏的恢复
ACTIVE STATUS：redo log里面的日志所对应的buffer cache里的脏块还没有刷新到datafile中
# 准备环境 create tablespace lyj datafile '/u01/app/oracle/oradata/orcl/lyj_01.dbf' size 1g autoextend on next 500m maxsize unlimited; drop user lyj cascade; create user lyj identified by lyj default tablespace lyj; grant dba to lyj; # 增加并扩大logfile group alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 200m; alter database add logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 200m; alter database add logfile group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 200m; select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 CURRENT 2 INACTIVE 3 INACTIVE 4 UNUSED 5 UNUSED 6 UNUSED alter system switch logfile; alter system checkpoint; select group#,status from v$log; GROUP# STATUS ---------- ---------------- 1 INACTIVE 2 INACTIVE 3 INACTIVE 4 INACTIVE 5 CURRENT 6 INACTIVE alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; ! rm -rf /u01/app/oracle/oradata/orcl/redo01.log ! rm -rf /u01/app/oracle/oradata/orcl/redo02.log ! rm -rf /u01/app/oracle/oradata/orcl/redo03.log alter database add logfile group 1 ('/u01/app/oracle/oradata/orcl/redo01.log') size 200m; alter database add logfile group 2 ('/u01/app/oracle/oradata/orcl/redo02.log') size 200m; alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/redo03.log') size 200m; # 模拟一个大事务 conn lyj/lyj drop table t1 purge; create table t1(id int,name varchar(100)); begin for i in 1 .. 5000000 loop insert into t1 values(i,'AAAAAA'); end loop; commit; end; / # 新打开一个窗口 set line 150 col member for a50 select l.group#,l.STATUS,f.member,BYTES/1024/1024 size_m from v$log l, v$logfile f where l.GROUP#=f.GROUP# order by 1; GROUP# STATUS MEMBER SIZE_M ---------- ---------------- -------------------------------------------------- ---------- 1 CURRENT /u01/app/oracle/oradata/orcl/redo01.log 200 2 INACTIVE /u01/app/oracle/oradata/orcl/redo02.log 200 3 INACTIVE /u01/app/oracle/oradata/orcl/redo03.log 200 4 INACTIVE /u01/app/oracle/oradata/orcl/redo04.log 200 5 ACTIVE /u01/app/oracle/oradata/orcl/redo05.log 200 6 INACTIVE /u01/app/oracle/oradata/orcl/redo06.log 200 # 为了保障状态是active，直接shutdown abort数据库 SQL> shutdown abort # 启动到mount状态后再查看日志组状态 startup mount set line 150 col member for a50 select l.group#,l.STATUS,f.member,BYTES/1024/1024 size_m from v$log l, v$logfile f where l.GROUP#=f.GROUP# order by 1; GROUP# STATUS MEMBER SIZE_M ---------- ---------------- -------------------------------------------------- ---------- 1 CURRENT /u01/app/oracle/oradata/orcl/redo01.log 200 2 INACTIVE /u01/app/oracle/oradata/orcl/redo02.log 200 3 INACTIVE /u01/app/oracle/oradata/orcl/redo03.log 200 4 INACTIVE /u01/app/oracle/oradata/orcl/redo04.log 200 5 ACTIVE /u01/app/oracle/oradata/orcl/redo05.log 200 6 INACTIVE /u01/app/oracle/oradata/orcl/redo06.log 200 # 模拟状态为ACTIVE的log group 5损坏 dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo05.log bs=512 count=20 # 正常打开数据库时报以下错误 SQL> alter database open; #----------------------------------------------------------------------------------- alter database open * ERROR at line 1: ORA-00313: open failed for members of log group 5 of thread 1 ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/orcl/redo05.log' ORA-27047: unable to read the header block of file Linux-x86_64 Error: 25: Inappropriate ioctl for device Additional information: 1 #----------------------------------------------------------------------------------- # alert日志中有如下错误信息 #----------------------------------------------------------------------------------- Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22346.trc: ORA-00313: open failed for members of log group 5 of thread 1 ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/orcl/redo05.log' ORA-27047: unable to read the header block of file #----------------------------------------------------------------------------------- # 因为logfile group 5状态是active，clear和drop都不行 SQL> alter database clear logfile group 5; alter database clear logfile group 5 * ERROR at line 1: ORA-01624: log 5 needed for crash recovery of instance orcl (thread 1) ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/orcl/redo05.log' SQL> alter database drop logfile group 5; alter database drop logfile group 5 * ERROR at line 1: ORA-01624: log 5 needed for crash recovery of instance orcl (thread 1) ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/orcl/redo05.log' # 使用resetlog打开数据库时报以下错误 SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery # 介质恢复 SQL> recover database until cancel; ORA-00279: change 4326696 generated at 04/04/2018 13:58:56 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_04_04/o1_mf_1_162_%u_.arc ORA-00280: change 4326696 for thread 1 is in sequence #162 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_04_04/o1_mf_1_162_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 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/oradata/orcl/system01.dbf' # 再次resetlogs打开数据库 SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf' # 修改数据库pfile添加隐含参数 create pfile from spfile; echo "*._allow_resetlogs_corruption=true" >> $ORACLE_HOME/dbs/init"$ORACLE_SID".ora echo "*._allow_error_simulation=true" >> $ORACLE_HOME/dbs/init"$ORACLE_SID".ora # 数据库使用pfile mount shutdown abort startup pfile="/u01/app/oracle/11.2.0.4/db_1/dbs/initorcl.ora" mount # 使用resetlogs方式可以打开数据库 alter database open resetlogs; Database altered. # 查看redo log group状态，SEQUENCE#已从0开始 set line 150 col member for a50 select l.group#,l.STATUS,f.member,BYTES/1024/1024 size_m,sequence# from v$log l, v$logfile f where l.GROUP#=f.GROUP# order by 1; GROUP# STATUS MEMBER SIZE_M SEQUENCE# ---------- ---------------- -------------------------------------------------- ---------- ---------- 1 CURRENT /u01/app/oracle/oradata/orcl/redo01.log 200 1 2 UNUSED /u01/app/oracle/oradata/orcl/redo02.log 200 0 3 UNUSED /u01/app/oracle/oradata/orcl/redo03.log 200 0 4 UNUSED /u01/app/oracle/oradata/orcl/redo04.log 200 0 5 UNUSED /u01/app/oracle/oradata/orcl/redo05.log 200 0 6 UNUSED /u01/app/oracle/oradata/orcl/redo06.log 200 0 # 切换日志，在alert日志中查看是否有报错。 alter system switch logfile; 注意：这种修改隐含参数后使用resetlogs方式打开的数据库，生产环境中有可能的话（数量不太大），尽量将数据导出后重建库恢复。 |
Current redo log丢失或损坏的恢复
CURRENT STATUS：实例正在使用的日志文件，并且redo log里面的日志可能还没有刷新到datafile中
# 模拟一个大事务 conn lyj/lyj drop table t1 purge; create table t1(id int,name varchar(100)); begin for i in 1 .. 5000000 loop insert into t1 values(i,'AAAAAA'); commit; end loop; end; / # 新打开一个窗口 set line 150 col member for a50 select l.group#,l.STATUS,f.member,BYTES/1024/1024 size_m from v$log l, v$logfile f where l.GROUP#=f.GROUP# order by 1; GROUP# STATUS MEMBER SIZE_M ---------- ---------------- -------------------------------------------------- ---------- 1 ACTIVE /u01/app/oracle/oradata/orcl/redo01.log 200 2 ACTIVE /u01/app/oracle/oradata/orcl/redo02.log 200 3 ACTIVE /u01/app/oracle/oradata/orcl/redo03.log 200 4 ACTIVE /u01/app/oracle/oradata/orcl/redo04.log 200 5 CURRENT /u01/app/oracle/oradata/orcl/redo05.log 200 6 INACTIVE /u01/app/oracle/oradata/orcl/redo06.log 200 # 新开一个窗口，将状态为CURRENT的log group 5损坏 dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo05.log bs=512 count=20 # 稍过一会，执行大事务的会话中断，实例shutdown ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 25677 Session ID: 122 Serial number: 5 # 启动数据库实例报错 SQL> startup ORACLE instance started. Total System Global Area 4359294976 bytes Fixed Size 2260288 bytes Variable Size 855638720 bytes Database Buffers 3489660928 bytes Redo Buffers 11735040 bytes Database mounted. ORA-00313: open failed for members of log group 5 of thread 1 ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/orcl/redo05.log' ORA-27048: skgfifi: file header information is invalid Additional information: 13 # 尝试open resetlogs报需要恢复 SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01139: RESETLOGS option only valid after an incomplete database recovery # 介质恢复 SQL> recover database until cancel; ORA-00279: change 208852 generated at 04/04/2018 21:26:30 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_04_04/o1_mf_1_23_%u_.arc ORA-00280: change 208852 for thread 1 is in sequence #23 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_04_04/o1_mf_1_23_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 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/oradata/orcl/system01.dbf' # 再次open resetlogs SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf' # 修改数据库pfile添加隐含参数 create pfile from spfile; echo "*._allow_resetlogs_corruption=true" >> $ORACLE_HOME/dbs/init"$ORACLE_SID".ora echo "*._allow_error_simulation=true" >> $ORACLE_HOME/dbs/init"$ORACLE_SID".ora # 使用pfile open resetlog SQL> shutdown abort ORACLE instance shut down. SQL> startup pfile="/u01/app/oracle/11.2.0.4/db_1/dbs/initorcl.ora" mount ORACLE instance started. Total System Global Area 4375998464 bytes Fixed Size 2260328 bytes Variable Size 889193112 bytes Database Buffers 3472883712 bytes Redo Buffers 11661312 bytes Database mounted. SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2663], [0], [210131], [0], [217516], [], [], [], [], [], [], [] Process ID: 25824 Session ID: 122 Serial number: 3 |
使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库后,我们说很多时候你会遇到ORA-00600 2662号错误，这个错误的含义是:
A data block SCN is ahead of the current SCN.
The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN
stored in a UGA variable.
If the SCN is less than the dependent SCN then we signal the ORA-600 [2662] internal error.
ORA-600 [2662] [a] [b] [c] [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
算法计算规则如下：Arg [c]*4得出一个数值，假设为V_Wrap,
如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level
Arg [d] < 2147483648，V_Wrap+2为需要的level
Arg [d] < 3221225472，V_Wrap+3为需要的level
使用10015 event手工推进scn
startup pfile="/u01/app/oracle/11.2.0.4/db_1/dbs/initorcl.ora" mount alter session set events '10015 trace name adjust_scn level 1'; SQL> alter database open; Database altered. 这时数据库虽然能open，但alert中会有类似以下的报错，强烈建议将数据导出重建一个库 ORA-01595: error freeing extent (2) of rollback segment (2)) ORA-00600: internal error code, arguments: [4193], [31], [1], [], [], [], [], [], [], [], [], [] Dumping diagnostic data in directory=[cdmp_20180404214920], requested by (instance=1, osid=26128 (SMON)), summary=[incident=21732]. |
增进SCN有两种常用方法
1.通过immediate trace name方式(在数据库Open状态下)
alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';
|
2.通过10015事件(在数据库无法打开，mount状态下)
alter session set events '10015 trace name adjust_scn level x';
|
注:level 1为增进SCN 10亿(1 billion) (102410241024),通常Level 1已经足够。也可以根据实际情况适当调整。
Dump logfile解析一个事务的日志格式
conn lyj/lyj create table t2 (id int, name varchar2(10)); insert into t2 values (1,'AAAAA'); commit; alter system switch logfile; update t2 set name='BBBBB' where name='AAAAA'; commit; set line 150 col member for a50 select l.group#,l.STATUS,f.member,BYTES/1024/1024 size_m,sequence# from v$log l, v$logfile f where l.GROUP#=f.GROUP# order by 1; GROUP# STATUS MEMBER SIZE_M SEQUENCE# ---------- ---------------- -------------------------------------------------- ---------- ---------- 1 ACTIVE /u01/app/oracle/oradata/orcl/redo01.log 200 1 2 CURRENT /u01/app/oracle/oradata/orcl/redo02.log 200 2 3 UNUSED /u01/app/oracle/oradata/orcl/redo03.log 200 0 4 UNUSED /u01/app/oracle/oradata/orcl/redo04.log 200 0 5 UNUSED /u01/app/oracle/oradata/orcl/redo05.log 200 0 6 UNUSED /u01/app/oracle/oradata/orcl/redo06.log 200 0 # 导出当前的log file group alter system dump logfile '/u01/app/oracle/oradata/orcl/redo02.log'; col VALUE for a80 select VALUE from v$diag_info where NAME='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22978.trc |
dump的trace信息如下：
...... REDO RECORD - Thread:1 RBA: 0x000002.00000004.0010 LEN: 0x024c VLD: 0x0d SCN: 0x0000.00420e9e SUBSCN: 1 04/04/2018 15:10:43 (LWN RBA: 0x000002.00000004.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00420e9d) CHANGE #1 TYP:2 CLS:1 AFN:9 DBA:0x02400e87 OBJ:94895 SCN:0x0000.00420e97 SEQ:1 OP:11.19 ENC:0 RBL:0 KTB Redo op: 0x11 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: F xid: 0x0002.017.00000a72 uba: 0x00c0011a.03e7.29 Block cleanout record, scn: 0x0000.00420e9d ver: 0x01 opt: 0x02, entries follow... itli: 1 flg: 2 scn: 0x0000.00420e97 Array Update of 1 rows: tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 204 ncol: 2 nnew: 1 size: 0 KDO Op code: 21 row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x02400e87 hdba: 0x02400e82 itli: 2 ispac: 0 maxfr: 4858 vect = 3 col 1: [ 5] 42 42 42 42 42 CHANGE #2 TYP:0 CLS:19 AFN:3 DBA:0x00c00090 OBJ:4294967295 SCN:0x0000.00420e51 SEQ:2 OP:5.2 ENC:0 RBL:0 ktudh redo: slt: 0x0017 sqn: 0x00000a72 flg: 0x0012 siz: 168 fbi: 0 uba: 0x00c0011a.03e7.29 pxid: 0x0000.000.00000000 CHANGE #3 TYP:0 CLS:19 AFN:3 DBA:0x00c00090 OBJ:4294967295 SCN:0x0000.00420e9e SEQ:1 OP:5.4 ENC:0 RBL:0 ktucm redo: slt: 0x0017 sqn: 0x00000a72 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0011a.03e7.29 ext: 1 spc: 2252 fbi: 0 CHANGE #4 TYP:0 CLS:20 AFN:3 DBA:0x00c0011a OBJ:4294967295 SCN:0x0000.00420e51 SEQ:3 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 168 spc: 2422 flg: 0x0012 seq: 0x03e7 rec: 0x29 xid: 0x0002.017.00000a72 ktubl redo: slt: 23 rci: 0 opc: 11.1 [objn: 94895 objd: 94895 tsn: 10] Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x00c0011a.03e7.26 prev ctl max cmt scn: 0x0000.0042094a prev tx cmt scn: 0x0000.0042095c txn start scn: 0x0000.00420e9c logon user: 85 prev brb: 12583193 prev bcl: 0 BuExt idx: 0 flg2: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: Z Array Update of 1 rows: tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 204 ncol: 2 nnew: 1 size: 0 KDO Op code: 21 row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x02400e87 hdba: 0x02400e82 itli: 2 ispac: 0 maxfr: 4858 vect = 3 col 1: [ 5] 41 41 41 41 41 ...... |
RBA: 0x000002.00000004.0010 - redo log地址：sequence#是2的redolog file的第4个块的偏移量第10个节字开始
LEN: 0x024c - 长度
VLD: 0x0d - 类型
SCN: 0x0000.00420e9e - 对应日志所产生的SCN
SUBSCN: 1 04/04/2018 15:10:43 - 同一时间产生的日志量大时，通过SUBSCN来区别
OP:11.19 - OP是操作代码 11.19代表update
OP:5.2 - 启动一个事务
OP:5.4 - 提交
OP:5.1 - 修改前的值
CHANGE #1 - TYP:2 CLS:1 AFN:9 DBA:0x02400e87 OBJ:94895 SCN:0x0000.00420e97 SEQ:1 OP:11.19 ENC:0 RBL:0
OP:11.19-update修改的值 TYP:2-类型2是普通文件 CLS:1-数据类型 AFN:9-绝对文件号 DBA:0x02400e87-数据文件地址 OBJ:94895-对象编号
CHANGE #2 TYP:0 CLS:19 AFN:3 DBA:0x00c00090 OBJ:4294967295 SCN:0x0000.00420e51 SEQ:2 OP:5.2 ENC:0 RBL:0
OP:5.2-启动一个事务(undo) AFN:3-绝对文件号(这个文件对应的是undo file)
CHANGE #3 TYP:0 CLS:19 AFN:3 DBA:0x00c00090 OBJ:4294967295 SCN:0x0000.00420e9e SEQ:1 OP:5.4 ENC:0 RBL:0
OP:5.4-提交 AFN:3-绝对文件号(这个文件对应的是undo file)
CHANGE #4 TYP:0 CLS:20 AFN:3 DBA:0x00c0011a OBJ:4294967295 SCN:0x0000.00420e51 SEQ:3 OP:5.1 ENC:0 RBL:0
OP:5.1-update修改前的值 AFN:3-绝对文件号(这个文件对应的是undo file)
查询隐含参数脚本
vi $ORACLE_HOME/rdbms/admin/show_para.sql #----------------------------------------------------------------------------------- col p_name for a48 col p_DESCRIPTION for a62 col p_value for a10 set linesize 150 set pagesize 9999 SELECT i.ksppinm p_name, i.ksppdesc p_description, CV.ksppstvl p_VALUE, CV.ksppstdf isdefault, DECODE (BITAND (CV.ksppstvf, 7),1, 'MODIFIED',4, 'SYSTEM_MOD', 'FALSE') ismodified, DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted FROM sys.x$ksppi i, sys.x$ksppcv CV WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV ('Instance') AND i.indx = CV.indx AND upper(i.ksppinm) LIKE upper('%&p%') ORDER BY REPLACE (i.ksppinm, '_', ''); #----------------------------------------------------------------------------------- # 在sqlplus中执行 @?/rdbms/admin/show_para |