Lunar的oracle实验室 - 世间所有相遇都是久别重逢

global_name为空导致的数据库不能open—–使用gdb修复(中断oracle启动的部分监测功能)

发表于 2015 年 1 月 5 日由 Lunar

GLOBAL_NAME和props$对象介绍
 global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)
global_name为空导致的数据库不能open—–使用DUL修复
 global_name为空导致的数据库不能open—使用BBED修复（bbed恢复update的数据）
这篇为第1种方法，参考MOS文档，使用gdb中断后open数据库，再手工执行update语句保证 global_name 不为空。
首先了解一下，不同操作系统平台上的诊断工具有所不同，Linux和AIX上都可以使用gdb来诊断。

AIX（Big Endian）使用gdb诊断的例子请参考：一次体验N种报错的Oracle数据库恢复（ORA-704 ORA-604 ORA-600[25016] ORA-376）
我这里是Linux，即Little Endian 。

测试时，首先把global_name 置空：

[oracle@lunar ~]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 4 06:47:18 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Welcome Lunar's oracle world! Love you , baby ! Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Session altered. Elapsed: 00:00:00.01 06:47:20 SYS@lunarp>select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ LUNAR Elapsed: 00:00:00.06 07:21:29 SYS@lunarp>oradebug setmypid alter session set tracefile_identifier='lunar'; Statement processed. 07:36:18 SYS@lunarp> Session altered. Elapsed: 00:00:00.06 07:36:18 SYS@lunarp>oradebug event 10046 trace name context forever,level 12; Statement processed. 07:36:22 SYS@lunarp>update global_name set global_name=''; 1 row updated. Elapsed: 00:00:00.48 07:36:27 SYS@lunarp>commit; Commit complete. Elapsed: 00:00:00.02 07:36:31 SYS@lunarp>oradebug event 10046 trace name context off Statement processed. 07:36:35 SYS@lunarp>oradebug close_trace oradebug tracefile_name Statement processed. 07:36:39 SYS@lunarp>/u01/app/oracle/diag/rdbms/lunarp/lunarp/trace/lunarp_ora_11544_lunar.trc SYS@lunarp>shutdown abort ORACLE instance shut down. SYS@lunarp>startup Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Process ID: 11995 Session ID: 125 Serial number: 5 SYS@lunarp>

此时，trace中显示执行下SQL命令时，数据库报错：

PARSING IN CURSOR #140086475835376 len=55 dep=1 uid=0 oct=3 lid=0 tim=1407167741390301 hv=1950821498 ad='7f2eaec8' sqlid='459f3z9u4fb3u' select value$ from props$ where name = 'GLOBAL_DB_NAME' END OF STMT PARSE #140086475835376:c=1999,e=870,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=415205717,tim=1407167741390297 EXEC #140086475835376:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167741390424 FETCH #140086475835376:c=0,e=44,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=415205717,tim=1407167741390486 Incident 4937 created, dump file: /u01/app/oracle/diag/rdbms/lunarp/lunarp/incident/incdir_4937/lunarp_ora_12099_i4937.trc ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] [oracle@lunar ~]$ ps -ef|grep LOCAL=YES oracle 12183 12142 0 08:03 ? 00:00:00 oraclelunarp (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 12211 12186 0 08:08 pts/5 00:00:00 grep LOCAL=YES [oracle@lunar ~]$

下面使用gdb进行恢复：

[oracle@lunar ~]$ gdb $ORACLE_HOME/bin/oracle 12183 GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-42.el5) Copyright (C) 2009 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details. This GDB was configured as "x86_64-redhat-linux-gnu". For bug reporting instructions, please see: <http://www.gnu.org/software/gdb/bugs/>... Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/oracle...(no debugging symbols found)...done. Attaching to program: /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/oracle, process 12183 Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libodm11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libodm11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libcell11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libcell11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxp11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxp11.so Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/librt.so.1 Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnnz11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnnz11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclsra11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclsra11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libdbcfg11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libdbcfg11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libhasgen11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libhasgen11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxn2.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxn2.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocr11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocr11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrb11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrb11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrutl11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrutl11.so Reading symbols from /usr/lib64/libaio.so.1...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libaio.so.1 Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libdl.so.2 Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done. Loaded symbols for /lib64/libm.so.6 Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done. [Thread debugging using libthread_db enabled] Loaded symbols for /lib64/libpthread.so.0 Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libnsl.so.1 Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib64/libc.so.6 Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/ld-linux-x86-64.so.2 Reading symbols from /usr/lib64/libnuma.so.1...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libnuma.so.1 Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libnss_files.so.2 Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnque11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnque11.so 0x0000003e9880d9b0 in __read_nocancel () from /lib64/libpthread.so.0 (gdb) break kokiasg Breakpoint 1 at 0x13c162c (gdb)

然后，回到sqlplus的会话，直接执行alter database open：

SYS@lunarp>alter database open; .....

回到gdb窗口：

(gdb) continue Breakpoint 1, 0x00000000013c162c in kokiasg () (gdb)

回到sqlplus会话，看到数据库已经open：

SYS@lunarp>conn / as sysdba Welcome Lunar's oracle world! Love you , baby ! Connected. ERROR: ORA-22303: type "SYS"."DBMSOUTPUT_LINESARRAY" not found ORA-00942: table or view does not exist ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_OUTPUT" ORA-06512: at line 1 Session altered. Elapsed: 00:00:00.00 08:14:06 SYS@lunarp>select status from v$instance; STATUS ------------ OPEN Elapsed: 00:00:00.01 SYS@lunarp>

此时alert.log显示：

Mon Aug 04 08:11:18 2014 alter database open Beginning crash recovery of 1 threads parallel recovery started with 2 processes Started redo scan Completed redo scan read 40 KB redo, 30 data blocks need recovery Started redo application at Thread 1: logseq 84, block 2 Recovery of Online Redo Log: Thread 1 Group 3 Seq 84 Reading mem 0 Mem# 0: /stage/lunar/redo03.log Completed redo application of 0.03MB Completed crash recovery at Thread 1: logseq 84, block 83, scn 1683842 30 data blocks read, 30 data blocks written, 40 redo k-bytes read Mon Aug 04 08:11:20 2014 LGWR: STARTING ARCH PROCESSES Mon Aug 04 08:11:20 2014 ARC0 started with pid=20, OS id=12288 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Mon Aug 04 08:11:21 2014 ARC1 started with pid=21, OS id=12290 Mon Aug 04 08:11:21 2014 ARC2 started with pid=22, OS id=12292 Thread 1 advanced to log sequence 85 (thread open) Mon Aug 04 08:11:21 2014 ARC3 started with pid=23, OS id=12294 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH Thread 1 opened at log sequence 85 Current log# 1 seq# 85 mem# 0: /stage/lunar/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Aug 04 08:11:21 2014 SMON: enabling cache recovery Archived Log entry 160 added for thread 1 sequence 84 ID 0x92fd63eb dest 1: Mon Aug 04 08:11:21 2014 NSA2 started with pid=24, OS id=12300 ARC2: Standby redo logfile selected for thread 1 sequence 84 for destination LOG_ARCHIVE_DEST_2 ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Thread 1 advanced to log sequence 86 (LGWR switch) Current log# 2 seq# 86 mem# 0: /stage/lunar/redo02.log ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** [12183] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:221099734 end:221101944 diff:2210 (22 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 Archived Log entry 162 added for thread 1 sequence 85 ID 0x92fd63eb dest 1: LNS: Standby redo logfile selected for thread 1 sequence 86 for destination LOG_ARCHIVE_DEST_2 ARC0: Standby redo logfile selected for thread 1 sequence 85 for destination LOG_ARCHIVE_DEST_2

现在修改global_name = ‘lunarbb’：

08:14:32 SYS@lunarp>update global_name set global_name = 'lunarbb'; 1 row updated. Elapsed: 00:00:00.12 08:15:05 SYS@lunarp>commit; Commit complete. Elapsed: 00:00:00.00 08:15:07 SYS@lunarp>select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ lunarbb Elapsed: 00:00:00.00 08:15:27 SYS@lunarp> (gdb) kill Kill the program being debugged? (y or n) y (gdb) quit [oracle@lunar ~]$

再次正常启动数据库，数据库已经open了，且GLOBAL_NAME显示为我们刚才修改的：lunarbb

SYS@lunarp>startup Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! Database opened. SYS@lunarp>select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ lunarbb Elapsed: 00:00:00.05 SYS@lunarp>

发表在 Internal | 标签为 gdb, GLOBAL_NAME, props$ | 留下评论

global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)

发表于 2015 年 1 月 5 日由 Lunar

GLOBAL_NAME和props$对象介绍
 global_name为空导致的数据库不能open—–使用gdb修复(中断oracle启动的部分监测功能)
global_name为空导致的数据库不能open—–使用DUL修复
 global_name为空导致的数据库不能open—使用BBED修复（bbed恢复update的数据）

这篇为第2种解决 global_name 为NULL导致数据库不能启动的方法。
即从其他正常的11.2的数据库上使用dd命令克隆一个相同的block来替换现有system文件中的相同文件。
根据测试，猜测大版本一致即可，比如11.2.0.3和11.2.0.4的props$都存储在file 1 block 801上。
因此，我这里使用了11.2.0.4(基于ASM)的数据库上的file 1 block 801来替换 11.2.0.3（基于文件提醒）的数据库的file 1 block 801。

首先，props$在相同版本的数据库中，缺省的位置是固定的。知道了这个，就可以从其他数据库上检查相应的block，如果相同，直接dd过来。

首先备份当前的props$：

[oracle@lunar ~]$ dd if=/stage/lunar/system01.dbf of=lunar.log bs=8192 skip=801 count=1 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.00892236 seconds, 918 kB/s [oracle@lunar ~]$ strings lunar.log|grep CHARACTER NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_CHARACTERSET NLS_NUMERIC_CHARACTERS [oracle@lunar ~]$

我们知道props$表中记录了数据库字符集，global_name等等关键信息，你可以使用strings来查看其他内容。
例如下面这样，在控制文件丢失，无备份，需要重建控制文件时下面的信息就很有用，主要是需要看字符集（NLS_CHARACTERSET），我这里是AL32UTF8：

[oracle@lunar ~]$ strings lunar.log GLOBAL_DB_NAME lunarbb Global database name, GLOBAL_DB_NAME Global database name, GLOBAL_DB_NAME LUNAR Global database name, NO_USERID_VERIFIER_SALT 6A58202A068B678B09CF571DC967F3EC, WORKLOAD_REPLAY_MODE bPREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress, WORKLOAD_CAPTURE_MODE /CAPTURE implies workload capture is in progress, EXPORT_VIEWS_VERSION Export views revision #, DEFAULT_PERMANENT_TABLESPACE USERS$Name of default permanent tablespace, GLOBAL_DB_NAME TRAVEL Global database name, NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters, NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set, NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception, NLS_LENGTH_SEMANTICS BYTE NLS length semantics, NLS_COMP BINARY NLS comparison, NLS_DUAL_CURRENCY Dual currency symbol, NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format, NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format, NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format, NLS_TIME_FORMAT HH.MI.SSXFF AM Time format, NLS_SORT BINARY Linguistic definition, NLS_DATE_LANGUAGE AMERICAN Date language, NLS_DATE_FORMAT DD-MON-RR Date format, NLS_CALENDAR GREGORIAN Calendar system, NLS_CHARACTERSET AL32UTF8 Character set, NLS_NUMERIC_CHARACTERS Numeric characters, NLS_ISO_CURRENCY AMERICA ISO currency, NLS_CURRENCY Local currency, NLS_TERRITORY AMERICA Territory, NLS_LANGUAGE AMERICAN Language, DEFAULT_TBS_TYPE SMALLFILE Default tablespace type, DST_SECONDARY_TT_VERSION 0'Version of secondary timezone data file, DST_PRIMARY_TT_VERSION 14%Version of primary timezone data file, DST_UPGRADE_STATE NONE&State of Day Light Saving Time Upgrade, DBTIMEZONE -08:00 DB time zone, TDE_MASTER_KEY_ID, Flashback Timestamp TimeZone GMT"Flashback timestamp created in GMT, DEFAULT_TEMP_TABLESPACE TEMP$Name of default temporary tablespace, DEFAULT_EDITION ORA$BASE$Name of the database default edition, DEFAULT_PERMANENT_TABLESPACE SYSTEM$Name of default permanent tablespace, DEFAULT_TEMP_TABLESPACE SYSTEM$Name of default temporary tablespace, DICT.BASE 2 dictionary base tables version # [oracle@lunar ~]$

查看props$这个表的具体位置：

[oracle@lunar ~]$ ss SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 6 01:20:45 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from props$; COUNT(*) ---------- 36 SQL> select * from props$ where name = 'GLOBAL_DB_NAME'; NAME ------------------------------ VALUE$ -------------------------------------------------------------------------------- COMMENT$ -------------------------------------------------------------------------------- GLOBAL_DB_NAME LUNAR Global database name SQL> select dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) blk# 3 from sys.props$ 4 where name = 'GLOBAL_DB_NAME' 5 / FILE# BLK# ---------- ---------- 1 801 SQL>

这里可以看到是file 1 block 801

现在到其他一个可以open的11.2的数据库中复制这个block出来。
方法多的很，比如，你可以直接将asm文件复制到文件，然后直接使用bbed的copy命令将这个block 复制到当前损坏的库上。
也可以使用我这样dd的方法：
首先，将asm文件复制到文件系统（bbed不能直接读asm，一般采用这样的方法）

SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/lunars/system01.dbf +DATA/lunars/sysaux01.dbf +DATA/lunars/undotbs01.dbf +DATA/lunars/users01.dbf +DATA/lunars/soe01.dbf +DATA/lunars/lunar01.dbf 6 rows selected. SQL> [root@lunar ~]# su - grid [grid@lunar ~]$ asmcmd ASMCMD> lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 7844 4991 0 4991 0 N DATA/ ASMCMD> cp +DATA/lunars/system01.dbf /tmp/system01.dbf copying +DATA/lunars/system01.dbf -> /tmp/system01.dbf ASMCMD> [root@lunar tmp]# ls -lrt system01.dbf -rw-r----- 1 grid oinstall 419438592 Aug 6 01:24 system01.dbf [root@lunar tmp]# [root@lunar tmp]# dd if=/tmp/system01.dbf of=lunar_11204.props.dd bs=8192 skip=801 count=1 1+0 records in 1+0 records out 8192 bytes (8.2 kB) copied, 0.000535543 seconds, 15.3 MB/s [root@lunar tmp]# ls -lrt lunar_11204.props.dd -rw-r--r-- 1 root root 8192 Aug 6 01:26 lunar_11204.props.dd [root@lunar tmp]# sftp> get /tmp/lunar_11204.props.dd Downloading lunar_11204.props.dd from /tmp/lunar_11204.props.dd 100% 8KB 8KB/s 00:00:00 /tmp/lunar_11204.props.dd: 8192 bytes transferred in 0 seconds (8192 bytes/s) sftp>

查看一下这个block的信息，可以看到，这个数据库版本(NLS_RDBMS_VERSION)是11.2.0.4，GLOBAL_DB_NAME的值是 LUNAR：

[oracle@lunar ~]$ strings lunar_11204.props.dd|grep LUNAR LUNAR [oracle@lunar ~]$ strings lunar_11204.props.dd NLS_RDBMS_VERSION 11.2.0.4.0 RDBMS version for NLS parameters, --------因为是从11.2.0.4版本的db上dd来的 NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception, NLS_LENGTH_SEMANTICS BYTE NLS length semantics, NLS_COMP BINARY NLS comparison, NLS_DUAL_CURRENCY Dual currency symbol, NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format, NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format, NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format, NLS_TIME_FORMAT HH.MI.SSXFF AM Time format, NLS_SORT BINARY Linguistic definition, NLS_DATE_LANGUAGE AMERICAN Date language, NLS_DATE_FORMAT DD-MON-RR Date format, NLS_CALENDAR GREGORIAN Calendar system, NLS_CHARACTERSET AL32UTF8 Character set, NLS_NUMERIC_CHARACTERS Numeric characters, NLS_ISO_CURRENCY AMERICA ISO currency, NLS_CURRENCY Local currency, NLS_TERRITORY AMERICA Territory, NLS_LANGUAGE AMERICAN Language, GLOBAL_DB_NAME =============》这里GLOBAL_DB_NAME的值为LUNAR LUNAR Global database name, NO_USERID_VERIFIER_SALT 6A58202A068B678B09CF571DC967F3EC, WORKLOAD_REPLAY_MODE bPREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress, WORKLOAD_CAPTURE_MODE /CAPTURE implies workload capture is in progress, EXPORT_VIEWS_VERSION Export views revision #, DEFAULT_PERMANENT_TABLESPACE USERS$Name of default permanent tablespace, GLOBAL_DB_NAME TRAVEL Global database name< NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters, NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set< NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception< NLS_LENGTH_SEMANTICS BYTE NLS length semantics< NLS_COMP BINARY NLS comparison< NLS_DUAL_CURRENCY Dual currency symbol< NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format< NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format< NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format< NLS_TIME_FORMAT HH.MI.SSXFF AM Time format< NLS_SORT BINARY Linguistic definition< NLS_DATE_LANGUAGE AMERICAN Date language< NLS_DATE_FORMAT DD-MON-RR Date format< NLS_CALENDAR GREGORIAN Calendar system< NLS_CHARACTERSET AL32UTF8 Character set< NLS_NUMERIC_CHARACTERS Numeric characters< NLS_ISO_CURRENCY AMERICA ISO currency< NLS_CURRENCY Local currency< NLS_TERRITORY AMERICA Territory< NLS_LANGUAGE AMERICAN Language, DEFAULT_TBS_TYPE SMALLFILE Default tablespace type, DST_SECONDARY_TT_VERSION 0'Version of secondary timezone data file, DST_PRIMARY_TT_VERSION 14%Version of primary timezone data file, DST_UPGRADE_STATE NONE&State of Day Light Saving Time Upgrade, DBTIMEZONE -08:00 DB time zone, TDE_MASTER_KEY_ID, Flashback Timestamp TimeZone GMT"Flashback timestamp created in GMT, DEFAULT_TEMP_TABLESPACE TEMP$Name of default temporary tablespace, DEFAULT_EDITION ORA$BASE$Name of the database default edition, DEFAULT_PERMANENT_TABLESPACE SYSTEM$Name of default permanent tablespace, DEFAULT_TEMP_TABLESPACE SYSTEM$Name of default temporary tablespace, DICT.BASE 2 dictionary base tables version # [oracle@lunar ~]$

现在，将刚才dd出来块patch到11.2.0.3的数据库的相同位置
dd if=/home/oracle/test/lunar_11204.props.dd of=/stage/lunar/system01.dbf bs=8192 seek=801 conv=notrunc

然后直接open数据库（我这个库是11.2.0.3），可以看到数据库可以正常启动，且GLOBAL_NAME已经随着刚才dd过来的block改为LUNAR了：

[oracle@lunar ~]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 1 10:20:15 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Welcome Lunar's oracle world! Connected to an idle instance. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss' * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 Elapsed: 00:00:00.00 SYS@lunarp>startup Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! Database opened. SYS@lunarp>select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ LUNAR Elapsed: 00:00:00.09

发表在 Internal | 标签为 dd, GLOBAL_NAME, props$ | 留下评论

global_name为空导致的数据库不能open—使用BBED修复（bbed恢复update的数据）

发表于 2015 年 1 月 5 日由 Lunar

GLOBAL_NAME和props$对象介绍
 global_name为空导致的数据库不能open—–使用gdb修复(中断oracle启动的部分监测功能)
global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)
global_name为空导致的数据库不能open—–使用DUL修复

这篇为第4种解决 global_name 为NULL导致数据库不能启动的方法—-本质是使用bbed来恢复update的值。
bbed的安装和配置，网上已经很多了，总的来说，就是12.1和11.2都使用10.2的bbed库进行编译，然后可以正常使用。
bbed的初始配置参考： BBED简介

即，使用BBED来直接修改一个block的数据的方法。这里将使用BBED将删除掉global_name值找回来。
注意：这个方法实质就是使用BBED恢复一行被update的数据。

上次我们说过，很多方法都可以定位这个报错的数据块和global_name所在行的信息。
在中《global_name为空导致的数据库不能open—–使用DUL修复》，我们使用对比的方法。
这里，我们根据报错时生成的trace文件来定位这行报错的global_name在block中信息，然后使用bbed来修复。
首先，我们知道props$的数据存放在file 1 block 801中，那么转换存储地址为：

SYS@lunarp>select to_char(dbms_utility.make_data_block_address(1,801),'xxxxxxxxxxxxxxxxx') from dual; TO_CHAR(DBMS_UTILI ------------------ 400321 Elapsed: 00:00:00.01 SYS@lunarp>

在bbed中验证一下，我们看到改块内共36行数据，这个信息在11.2的数据库中是固定的（缺省情况下，也就是没有手工修改时）：

BBED> set file 1 block 801 FILE# 1 BLOCK# 801 BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) sb1 kdbhntab @93 1 sb2 kdbhnrow @94 36 该表有36行 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 90 sb2 kdbhfseo @100 5852 sb2 kdbhavsp @102 6040 sb2 kdbhtosp @104 6040 BBED>

那么，这行记录到底是第几行呢？使用bbed的find自然是可以search到，不过这个方法感觉不清晰。
下面，我们在trace中，搜索“0x00400321”关键字，找到“Block header dump: 0x00400321”相关部分：

Block header dump: 0x00400321 Object id on Block? Y seg/obj: 0x62 csc: 0x00.1a0169 itc: 2 flg: O typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0007.006.00000346 0x00c00f60.00ad.31 --U- 1 fsc 0x0007.001a988c 0x02 0x0001.01a.00000322 0x00c00ee0.009b.0e C--- 0 scn 0x0000.0019b316 bdba: 0x00400321 data_block_dump,data header at 0x76fa205c =============== tsiz: 0x1fa0 hsiz: 0x5a pbl: 0x76fa205c 76543210 flag=-------- ntab=1 nrow=36 frre=-1 fsbo=0x5a fseo=0x16b4 avsp=0x1798 tosp=0x179f 0xe:pti[0] nrow=36 offs=0 0x12:pri[0] offs=0x1f70 0x14:pri[1] offs=0x1e57 0x16:pri[2] offs=0x1894 0x18:pri[3] offs=0x1e9c 0x1a:pri[4] offs=0x1e10 0x1c:pri[5] offs=0x1dfb 0x1e:pri[6] offs=0x1dd9 0x20:pri[7] offs=0x1d98 0x22:pri[8] offs=0x1d55 0x24:pri[9] offs=0x1d0f 0x26:pri[10] offs=0x1cd9 0x28:pri[11] offs=0x1cb7 0x2a:pri[12] offs=0x1c94 0x2c:pri[13] offs=0x1c73 0x2e:pri[14] offs=0x1c4a 0x30:pri[15] offs=0x1c1a 0x32:pri[16] offs=0x1bef 0x34:pri[17] offs=0x1bc5 0x36:pri[18] offs=0x1b9c 0x38:pri[19] offs=0x1b70 0x3a:pri[20] offs=0x1b47 0x3c:pri[21] offs=0x1b19 0x3e:pri[22] offs=0x1ad6 0x40:pri[23] offs=0x1a93 0x42:pri[24] offs=0x1a3c 0x44:pri[25] offs=0x1a10 0x46:pri[26] offs=0x19ee 0x48:pri[27] offs=0x19bc 0x4a:pri[28] offs=0x1986 0x4c:pri[29] offs=0x194e 0x4e:pri[30] offs=0x190d 0x50:pri[31] offs=0x16b4 0x52:pri[32] offs=0x1862 0x54:pri[33] offs=0x1818 0x56:pri[34] offs=0x179c 0x58:pri[35] offs=0x1760

seg/obj: 0x62 转换成10进制是98，也就是对象号98(dba_objects.object_id=98)，这个正式props$对象的object_id：
csc: 0x00.18c0ef –cleanoutSCN,块清除时的SCN
itc: 2 —ITLcount, ITL的数量
flg: O —Block is on free list (flg)
typ: 1 – DATA —-这里表示是数据块，定义如下：

接下来是ITL信息：

Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.01f.00000333 0x00c00995.00ac.2f --U- 1 fsc 0x0005.0018c0f4 0x02 0x0003.00f.0000031e 0x00c007c8.00a5.05 C--- 0 scn 0x0000.001052a5 bdba: 0x00400321 data_block_dump,data header at 0x76fa005c

这里面表示使用了2个ITL slot（跟上面itc: 2是对应的），每个ITL占用24 bytes。
Flag的含义：C是提交，U是快速提交，—是未提交。因此，这里表示没有活动事务，所有事物都已经commit
上述信息跟bbed的信息是吻合的：

BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00000062 ub4 ktbbhod1 @24 0x00000062 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x001a0169 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x03 (KTBFONFL) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00000000 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0007 ub2 kxidslt @46 0x0006 ub4 kxidsqn @48 0x00000346 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c00f60 -----第1个ITL使用的undo block地址 ub2 kubaseq @56 0x00ad ub1 kubarec @58 0x31 ------undo record# ub2 ktbitflg @60 0x2001 (KTBFUPB) ------ union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 7 ub2 _ktbitwrp @62 0x0007 ub4 ktbitbas @64 0x001a988c -----对应于fsc（fsc 0x0005.0018c0f4 ） struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0001 ub2 kxidslt @70 0x001a ub4 kxidsqn @72 0x00000322 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c00ee0 -----第2个ITL使用的undo block地址 ub2 kubaseq @80 0x009b ub1 kubarec @82 0x0e ------undo record# ub2 ktbitflg @84 0x8000 (KTBFCOM) ------表示事物已经提交 union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x0019b316 BBED>

使用dump函数可以看到GLOBAL_DB_NAME的16进制形式是：47 4c 4f 42 41 4c 5f 44 42 5f 4e 41 4d 45

按照关键字“47 4c 4f 42 41 4c 5f 44 42 5f 4e 41 4d 45” 搜索trace文件，可以看到：

tab 0, row 31, @0x16b4 -------》表示：第1张表，第32行（从0开始编号）数据，块内偏移量为：5812 bytes tl: 40 fb: --H-FL-- lb: 0x1 cc: 3 col 0: [14] 47 4c 4f 42 41 4c 5f 44 42 5f 4e 41 4d 45 col 1: *NULL* col 2: [20] 47 6c 6f 62 61 6c 20 64 61 74 61 62 61 73 65 20 6e 61 6d 65

我们来证实一下，是不是这样数据。
这里显示global_name对应的是第32行数据（一个block中的数据行是从0开始编号的）。
我们证实一下，首先，这里col 0：

SYS@lunarp>select utl_raw.cast_to_varchar2(replace('47 4c 4f 42 41 4c 5f 44 42 5f 4e 41 4d 45',' ','')) from dual; UTL_RAW.CAST_TO_VARCHAR2(REPLACE('474C4F42414C5F44425F4E414D45','','')) ------------------------------------------------------------------------------------------------------------------------------------------------------ GLOBAL_DB_NAME Elapsed: 00:00:00.01 SYS@lunarp> col 1 这个是第二列，value$的值，被update为空值了。 col 2： SYS@lunarp>select utl_raw.cast_to_varchar2(replace('47 6c 6f 62 61 6c 20 64 61 74 61 62 61 73 65 20 6e 61 6d 65',' ','')) from dual; UTL_RAW.CAST_TO_VARCHAR2(REPLACE('476C6F62616C206461746162617365206E616D65','','')) ------------------------------------------------------------------------------------------------------------------------------------------------------ Global database name Elapsed: 00:00:00.01 SYS@lunarp>

可以看到，跟我们前面看到的是一致的：

SYS@lunarp>select * from props$ where name = 'GLOBAL_DB_NAME'; NAME VALUE$ COMMENT$ ------------------------------ ------------------------------ -------------------------------------------------- GLOBAL_DB_NAME Global database name Elapsed: 00:00:00.01 SYS@lunarp>

那么这行数据在块内偏移量：
SYS@lunarp>select to_number(’16b4′,’xxxxxxxxxxxx’) from dual;

TO_NUMBER(’16b4′,’XXXXXXXXXXXX’)
——————————–
5812

Elapsed: 00:00:00.00
SYS@lunarp>

普通行头：ROW_HEAD_PIECE + ROW_FIRST_PIECE + ROW_LAST_PIECE= 0x20 + 0x08 + 0x04= 0x2c 转换成10进制就是44
第32行记录的实际位置应该是：5812+44+2*24=5904

BBED> d /v offset 5908 count 128
File: /stage/lunar/system01.dbf (1)
Block: 801 Offsets: 5908 to 6035 Dba:0x00400321
——————————————————-
474c4f42 414c5f44 425f4e41 4d45ff14 l GLOBAL_DB_NAME..
476c6f62 616c2064 61746162 61736520 l Global database
6e616d65 2c00030e 474c4f42 414c5f44 l name,…GLOBAL_D
425f4e41 4d45076c 756e6172 62621447 l B_NAME.lunarbb.G
6c6f6261 6c206461 74616261 7365206e l lobal database n
616d652c 00030e47 4c4f4241 4c5f4442 l ame,…GLOBAL_DB
5f4e414d 45ff1447 6c6f6261 6c206461 l _NAME..Global da
74616261 7365206e 616d652c 00030e47 l tabase name,…G

<16 bytes per line>

BBED>

查看第32行数据（从第0行开始编号）：
BBED> p *kdbr[31]
rowdata[0]
———-
ub1 rowdata[0] @5904 0x2c

BBED> x /rccccccccc
rowdata[0] @5904
———-
flag@5904: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5905: 0x01
cols@5906: 3

col 0[14] @5907: GLOBAL_DB_NAME
col 1[0] @5922: *NULL*
col 2[20] @5923: Global database name

BBED>
[/shell]
可以看到，信息都是对的，GLOBAL_NAME的值是*NULL*，表示已经被清空了。

现在我们需要找到update之前的值。在block中按照GLOBAL_DB_NAME关键字检索，因为我使用多个方法分别update了多次，因此我这里搜索出来5次：

BBED> find /c GLOBAL_DB_NAME File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5908 to 6035 Dba:0x00400321 ------------------------------------------------------------------------ 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 61736520 6e616d65 2c00030e 474c4f42 414c5f44 425f4e41 4d45076c 756e6172 62621447 6c6f6261 6c206461 74616261 7365206e 616d652c 00030e47 4c4f4241 4c5f4442 5f4e414d 45ff1447 6c6f6261 6c206461 74616261 7365206e 616d652c 00030e47 <32 bytes per line> BBED> f File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5948 to 6075 Dba:0x00400321 ------------------------------------------------------------------------ 474c4f42 414c5f44 425f4e41 4d45076c 756e6172 62621447 6c6f6261 6c206461 74616261 7365206e 616d652c 00030e47 4c4f4241 4c5f4442 5f4e414d 45ff1447 6c6f6261 6c206461 74616261 7365206e 616d652c 00030e47 4c4f4241 4c5f4442 5f4e414d 45054c55 4e415214 476c6f62 616c2064 61746162 61736520 6e616d65 <32 bytes per line> BBED> f File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5995 to 6122 Dba:0x00400321 ------------------------------------------------------------------------ 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 61736520 6e616d65 2c00030e 474c4f42 414c5f44 425f4e41 4d45054c 554e4152 14476c6f 62616c20 64617461 62617365 206e616d 652c0002 174e4f5f 55534552 49445f56 45524946 4945525f 53414c54 20364135 38323032 41303638 42363738 42303943 <32 bytes per line> BBED> f File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 6035 to 6162 Dba:0x00400321 ------------------------------------------------------------------------ 474c4f42 414c5f44 425f4e41 4d45054c 554e4152 14476c6f 62616c20 64617461 62617365 206e616d 652c0002 174e4f5f 55534552 49445f56 45524946 4945525f 53414c54 20364135 38323032 41303638 42363738 42303943 46353731 44433936 37463345 432c0003 14574f52 4b4c4f41 445f5245 504c4159 5f4d4f44 45ff6250 <32 bytes per line> BBED> f File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 6463 to 6590 Dba:0x00400321 ------------------------------------------------------------------------ 474c4f42 414c5f44 425f4e41 4d450654 52415645 4c14476c 6f62616c 20646174 61626173 65206e61 6d652c00 03114e4c 535f5244 424d535f 56455253 494f4e0a 31312e32 2e302e33 2e302052 44424d53 20766572 73696f6e 20666f72 204e4c53 20706172 616d6574 6572732c 0003164e 4c535f4e 43484152 5f434841 52414354 <32 bytes per line> BBED> f BBED-00212: search string not found

为了看得更清晰，我们看看这5次搜索到的具体的内容：

看一下上述5次修改的值分别是什么：

BBED> d /v offset 5908 count 100 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5908 to 6007 Dba:0x00400321 ------------------------------------------------------- 474c4f42 414c5f44 425f4e41 4d45ff14 l GLOBAL_DB_NAME.. 476c6f62 616c2064 61746162 61736520 l Global database 6e616d65 2c00030e 474c4f42 414c5f44 l name,...GLOBAL_D ------ 2c00030e =======》行头行起始的offset：5944 425f4e41 4d45076c 756e6172 62621447 l B_NAME.lunarbb.G 6c6f6261 6c206461 74616261 7365206e l lobal database n 616d652c 00030e47 4c4f4241 4c5f4442 l ame,...GLOBAL_DB 5f4e414d l _NAM <16 bytes per line> BBED> d /v offset 5948 count 100 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5948 to 6047 Dba:0x00400321 ------------------------------------------------------- 474c4f42 414c5f44 425f4e41 4d45076c l GLOBAL_DB_NAME.l ----- 474c4f42 414c5f44 425f4e41 4d45 ====>GLOBAL_DB_NAME 756e6172 62621447 6c6f6261 6c206461 l unarbb.Global da ------- 6c 756e6172 6262 =========> lunarbb 74616261 7365206e 616d652c 00030e47 l tabase name,...G ----- 47 6c6f6261 6c206461 74616261 7365206e 616d65 =====》 Global database name 4c4f4241 4c5f4442 5f4e414d 45ff1447 l LOBAL_DB_NAME..G --- 2c 00030e =========>行头行起始的offset：5991 6c6f6261 6c206461 74616261 7365206e l lobal database n 616d652c 00030e47 4c4f4241 4c5f4442 l ame,...GLOBAL_DB 5f4e414d l _NAM <16 bytes per line> BBED> d /v offset 5995 count 100 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5995 to 6094 Dba:0x00400321 ------------------------------------------------------- 474c4f42 414c5f44 425f4e41 4d45ff14 l GLOBAL_DB_NAME.. 476c6f62 616c2064 61746162 61736520 l Global database 6e616d65 2c00030e 474c4f42 414c5f44 l name,...GLOBAL_D 425f4e41 4d45054c 554e4152 14476c6f l B_NAME.LUNAR.Glo 62616c20 64617461 62617365 206e616d l bal database nam 652c0002 174e4f5f 55534552 49445f56 l e,...NO_USERID_V 45524946 l ERIF <16 bytes per line> BBED> d /v offset 6035 count 100 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 6035 to 6134 Dba:0x00400321 ------------------------------------------------------- 474c4f42 414c5f44 425f4e41 4d45054c l GLOBAL_DB_NAME.L 554e4152 14476c6f 62616c20 64617461 l UNAR.Global data 62617365 206e616d 652c0002 174e4f5f l base name,...NO_ ---- 476c6f 62616c20 64617461 62617365 206e616d 65 =====》 Global database name 55534552 49445f56 45524946 4945525f l USERID_VERIFIER_ ----- 2c0002 17 =========>行头行起始的offset：6076 53414c54 20364135 38323032 41303638 l SALT 6A58202A068 42363738 42303943 46353731 44433936 l B678B09CF571DC96 37463345 l 7F3E <16 bytes per line> BBED> d /v offset 6463 count 100 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 6463 to 6562 Dba:0x00400321 ------------------------------------------------------- 474c4f42 414c5f44 425f4e41 4d450654 l GLOBAL_DB_NAME.T 52415645 4c14476c 6f62616c 20646174 l RAVEL.Global dat 61626173 65206e61 6d652c00 03114e4c l abase name,...NL 535f5244 424d535f 56455253 494f4e0a l S_RDBMS_VERSION. 31312e32 2e302e33 2e302052 44424d53 l 11.2.0.3.0 RDBMS 20766572 73696f6e 20666f72 204e4c53 l version for NLS 20706172 l par <16 bytes per line> BBED>

当前row[31]的信息如下：

BBED> p kdbr[31] sb2 kdbr[31] @172 5812 BBED> p *kdbr[31] rowdata[0] ---------- ub1 rowdata[0] @5904 0x2c BBED> x /rccccccccccccccc rowdata[0] @5904 ---------- flag@5904: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@5905: 0x01 cols@5906: 3 col 0[14] @5907: GLOBAL_DB_NAME col 1[0] @5922: *NULL* col 2[20] @5923: Global database name

上面5次的详细信息：

BBED> d /v offset 5904 count 100 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5904 to 6003 Dba:0x00400321 ------------------------------------------------------- 2c01030e 474c4f42 414c5f44 425f4e41 l ,...GLOBAL_DB_NA --- 2c01030e =========>行头 4d45ff14 476c6f62 616c2064 61746162 l ME..Global datab ---- global_name=NULL 61736520 6e616d65 2c00030e 474c4f42 l ase name,...GLOB --- 2c01030e =========>行头 offset 5944 414c5f44 425f4e41 4d45076c 756e6172 l AL_DB_NAME.lunar ---- global_name=lunarbb 62621447 6c6f6261 6c206461 74616261 l bb.Global databa 7365206e 616d652c 00030e47 4c4f4241 l se name,...GLOBA 4c5f4442 l L_DB <16 bytes per line> BBED> dump /v offset 5944 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5944 to 6043 Dba:0x00400321 ------------------------------------------------------- 2c00030e 474c4f42 414c5f44 425f4e41 l ,...GLOBAL_DB_NA ----- 2c01030e =========>行头 offset 5944 4d45076c 756e6172 62621447 6c6f6261 l ME.lunarbb.Globa ---- global_name=lunarbb 6c206461 74616261 7365206e 616d652c l l database name, 00030e47 4c4f4241 4c5f4442 5f4e414d l ...GLOBAL_DB_NAM 45ff1447 6c6f6261 6c206461 74616261 l E..Global databa 7365206e 616d652c 00030e47 4c4f4241 l se name,...GLOBA 4c5f4442 l L_DB <16 bytes per line> BBED> dump /v offset 5991 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 5991 to 6090 Dba:0x00400321 ------------------------------------------------------- 2c00030e 474c4f42 414c5f44 425f4e41 l ,...GLOBAL_DB_NA ---- 2c01030e =========>行头 offset 5991 4d45ff14 476c6f62 616c2064 61746162 l ME..Global datab ---- global_name=NULL 61736520 6e616d65 2c00030e 474c4f42 l ase name,...GLOB ---- 2c01030e =========>行头 offset 6031 414c5f44 425f4e41 4d45054c 554e4152 l AL_DB_NAME.LUNAR ---- global_name=NULL 14476c6f 62616c20 64617461 62617365 l .Global database 206e616d 652c0002 174e4f5f 55534552 l name,...NO_USER 49445f56 l ID_V <16 bytes per line> BBED> dump /v offset 6031 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 6031 to 6130 Dba:0x00400321 ------------------------------------------------------- 2c00030e 474c4f42 414c5f44 425f4e41 l ,...GLOBAL_DB_NA ---- 2c01030e =========>行头 offset 6031 4d45054c 554e4152 14476c6f 62616c20 l ME.LUNAR.Global ---- global_name=LUNAR 64617461 62617365 206e616d 652c0002 l database name,.. 174e4f5f 55534552 49445f56 45524946 l .NO_USERID_VERIF 4945525f 53414c54 20364135 38323032 l IER_SALT 6A58202 41303638 42363738 42303943 46353731 l A068B678B09CF571 44433936 l DC96 <16 bytes per line> BBED>

我们可以选择这行数据指向6031（GLOBAL_DB_NAME.LUNAR），也可以选择这行数据指向5944（GLOBAL_DB_NAME.lunarbb）。
这里，我们选择将global_name修改会LUNAR，也就是将global_name还原为update之前的为LUNAR值。

现在我们把 *kdbr[31]（即 rowdata的指针）的指针指向 offset 6031：
由于kdbr[31]和*kdbr[30]之间的差为92：*kdbr[31]的offset-kdbr[31]的offset=5904-5812=92
因此，要想*kdbr[31]指向6031，需要将kdbr[31]的值改为： 6031-92=5939（16进制为1733，考虑linux的倒序，实际修改时应该是3317）
修改前：

BBED> p kdbr[31] sb2 kdbr[31] @172 5812 BBED> p *kdbr[31] rowdata[0] ---------- ub1 rowdata[0] @5904 0x2c BBED> x /rccccccccccccccc rowdata[0] @5904 ---------- flag@5904: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@5905: 0x01 cols@5906: 3 col 0[14] @5907: GLOBAL_DB_NAME col 1[0] @5922: *NULL* col 2[20] @5923: Global database name BBED> set offset 172 OFFSET 172 BBED> dump File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 172 to 271 Dba:0x00400321 ------------------------------------------------------------------------ b4166218 18189c17 60170000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED>

——5812转换成16进制是16B4，linux是little endian，因此offset中实际上是保存了b416，也就是指向了b416这个位置
修改后：

BBED> modify /x 3317 offset 172 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 172 to 271 Dba:0x00400321 ------------------------------------------------------------------------ 33176218 18189c17 60170000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 <32 bytes per line> BBED> dump /v offset 172 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 172 to 271 Dba:0x00400321 ------------------------------------------------------- 33176218 18189c17 60170000 00000000 l 3.b.....`....... 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 l .... <16 bytes per line> BBED> p kdbr[31] sb2 kdbr[31] @172 5939 BBED> p *kdbr[31] rowdata[127] ------------ ub1 rowdata[127] @6031 0x2c BBED> x /rccccccccccc rowdata[127] @6031 ------------ flag@6031: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@6032: 0x00 cols@6033: 3 col 0[14] @6034: GLOBAL_DB_NAME col 1[5] @6049: LUNAR col 2[20] @6055: Global database name BBED> sum apply Check value for File 1, Block 801: current = 0x93f1, required = 0x93f1

验证报错：kdbchk: xaction header lock count mismatch

BBED> verify DBVERIFY - Verification starting FILE = /stage/lunar/system01.dbf BLOCK = 801 Block Checking: DBA = 4195105, Block Type = KTB-managed data block data header at 0xf3465c kdbchk: xaction header lock count mismatch trans=1 ilk=1 nlo=0 Block 801 failed with check code 6108 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED BBED>

这里的错误信息“kdbchk: xaction header lock count mismatch”，对比修改前后的lock信息，就知道应该修改lock值：

BBED> modify /x 01 offset 6032 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 6032 to 6131 Dba:0x00400321 ------------------------------------------------------------------------ 01030e47 4c4f4241 4c5f4442 5f4e414d 45054c55 4e415214 476c6f62 616c2064 61746162 61736520 6e616d65 2c000217 4e4f5f55 53455249 445f5645 52494649 45525f53 414c5420 36413538 32303241 30363842 36373842 30394346 35373144 43393637 <32 bytes per line> BBED> sum apply Check value for File 1, Block 801: current = 0x93f0, required = 0x93f0 BBED> verify DBVERIFY - Verification starting FILE = /stage/lunar/system01.dbf BLOCK = 801 Block Checking: DBA = 4195105, Block Type = KTB-managed data block data header at 0xf3465c kdbchk: the amount of space used is not equal to block size used=2054 fsc=7 avsp=6040 dtl=8096 Block 801 failed with check code 6110 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED BBED>

这里报错信息为空间计算（修改可用空间，及其开始偏移量及其结束偏移量）：
used+fsc+avsp=2054+7+6040=8101 ，已经大于dtl（8096）了
8101-8086=5
修改 ktbbhitl[1].b2 _ktbitfsc 值为 0就可以了：

修改前：

BBED> p ktbbhitl[0] struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0007 ub2 kxidslt @46 0x0006 ub4 kxidsqn @48 0x00000346 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c00f60 ub2 kubaseq @56 0x00ad ub1 kubarec @58 0x31 ub2 ktbitflg @60 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 7 ================需要将这里修改为0 ub2 _ktbitwrp @62 0x0007 ub4 ktbitbas @64 0x001a988c

修改：

BBED> dump /v offset 102 count 8 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 102 to 109 Dba:0x00400321 ------------------------------------------------------- 98179f17 00002400 l ......$. <16 bytes per line> BBED> dump /v offset 104 count 8 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 104 to 111 Dba:0x00400321 ------------------------------------------------------- 9f170000 2400701f l ....$.p. <16 bytes per line> BBED> modify /x 00 offset 62 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 62 to 69 Dba:0x00400321 ------------------------------------------------------------------------ 00008c98 1a000100 <32 bytes per line> BBED> sum apply Check value for File 1, Block 801: current = 0x93f7, required = 0x93f7 BBED> p ktbbhitl struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0007 ub2 kxidslt @46 0x0006 ub4 kxidsqn @48 0x00000346 struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c00f60 ub2 kubaseq @56 0x00ad ub1 kubarec @58 0x31 ub2 ktbitflg @60 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x001a988c struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0001 ub2 kxidslt @70 0x001a ub4 kxidsqn @72 0x00000322 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c00ee0 ub2 kubaseq @80 0x009b ub1 kubarec @82 0x0e ub2 ktbitflg @84 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x0019b316 BBED>

报错：the amount of space used is not equal to block size 这个很好解决：

BBED> verify DBVERIFY - Verification starting FILE = /stage/lunar/system01.dbf BLOCK = 801 Block Checking: DBA = 4195105, Block Type = KTB-managed data block data header at 0xf5665c kdbchk: the amount of space used is not equal to block size used=2054 fsc=0 avsp=6040 dtl=8096 Block 801 failed with check code 6110 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) sb1 kdbhntab @93 1 sb2 kdbhnrow @94 36 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 90 sb2 kdbhfseo @100 5812 sb2 kdbhavsp @102 6040 sb2 kdbhtosp @104 6047 BBED>

2054+6040=8094 BBED> modify /x 9a17 offset 102 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 102 to 109 Dba:0x00400321 ———————————————————————— 9a179f17 00002400 <32 bytes per line> BBED> modify /x 9a17 offset 104 File: /stage/lunar/system01.dbf (1) Block: 801 Offsets: 104 to 111 Dba:0x00400321 ———————————————————————— 9a170000 2400701f <32 bytes per line> BBED> sum apply Check value for File 1, Block 801: current = 0x93f0, required = 0x93f0 BBED> verify DBVERIFY – Verification starting FILE = /stage/lunar/system01.dbf BLOCK = 801 DBVERIFY – Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) sb1 kdbhntab @93 1 sb2 kdbhnrow @94 36 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 90 sb2 kdbhfseo @100 5812 sb2 kdbhavsp @102 6042 sb2 kdbhtosp @104 6042 BBED>
都改好了，现在启动数据库，ok了：

SYS@lunarp>startup Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! Database opened. SYS@lunarp>

发表在 bbed | 标签为 bbed, GLOBAL_NAME, props$, update | 留下评论

global_name为空导致的数据库不能open—–使用DUL修复

发表于 2015 年 1 月 5 日由 Lunar

GLOBAL_NAME和props$对象介绍
 global_name为空导致的数据库不能open—–使用gdb修复(中断oracle启动的部分监测功能)
global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)
global_name为空导致的数据库不能open—使用BBED修复（bbed恢复update的数据）

这篇为第3种解决 global_name 为NULL导致数据库不能启动的方法。
即，使用DUL来直接修改一个block内部的数据的方法。

模拟损坏，将global_name置空：

SYS@lunarp>update global_name set global_name=''; 1 row updated. Elapsed: 00:00:00.45 SYS@lunarp>commit; Commit complete. Elapsed: 00:00:00.11 SYS@lunarp>select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ Elapsed: 00:00:00.19 SYS@lunarp>

那么如何定位到是哪一个block呢？
答案是N中方法：
（1）使用ODU定位这行记录的dba地址
（2）对比其他大版本相同的正常库的相同行的数据
（3）查看报错的trace，找到改行数据的和block
。。。。。。
我们这里使用第二种，查看其他相同版本数据库的信息。具体的方法在第一篇《GLOBAL_NAME和props$对象介绍》中已经介绍了，这里不再赘述。
首先报错的数据库的alert.log信息如下：

PARSING IN CURSOR #140086475835376 len=55 dep=1 uid=0 oct=3 lid=0 tim=1407167741390301 hv=1950821498 ad='7f2eaec8' sqlid='459f3z9u4fb3u' select value$ from props$ where name = 'GLOBAL_DB_NAME' END OF STMT PARSE #140086475835376:c=1999,e=870,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=415205717,tim=1407167741390297 EXEC #140086475835376:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167741390424 FETCH #140086475835376:c=0,e=44,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=415205717,tim=1407167741390486 Incident 4937 created, dump file: /u01/app/oracle/diag/rdbms/lunarp/lunarp/incident/incdir_4937/lunarp_ora_12099_i4937.trc ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] [oracle@lunar ~]$ ps -ef|grep LOCAL=YES oracle 12183 12142 0 08:03 ? 00:00:00 oraclelunarp (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 12211 12186 0 08:08 pts/5 00:00:00 grep LOCAL=YES [oracle@lunar ~]$

可以看到，当前global_name已经被置空了：

[oracle@lunar dul]$ ./dul Data UnLoader: 10.2.0.5.28 - Internal Only - on Wed Mar 5 00:05:17 2014 with 64-bit io functions Copyright (c) 1994 2014 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only DUL: Warning: Recreating file "dul.log" Found db_id = 2464578389 Found db_name = LUNAR DUL> DUL> bootstrap; Probing file = 1, block = 520 . unloading table BOOTSTRAP$ DUL: Warning: block number is non zero but marked deferred trying to process it anyhow 60 rows unloaded DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty Reading BOOTSTRAP.dat 60 entries loaded Parsing Bootstrap$ contents Generating dict.ddl for version 10 OBJ$: segobjno 18, file 1 block 240 TAB$: segobjno 2, tabno 1, file 1 block 144 COL$: segobjno 2, tabno 5, file 1 block 144 USER$: segobjno 10, tabno 1, file 1 block 208 Running generated file "@dict.ddl" to unload the dictionary tables . unloading table OBJ$ 18308 rows unloaded . unloading table TAB$ 2084 rows unloaded . unloading table COL$ 73656 rows unloaded . unloading table USER$ 42 rows unloaded Reading USER.dat 42 entries loaded Reading OBJ.dat 18308 entries loaded and sorted 18308 entries Reading TAB.dat 2084 entries loaded Reading COL.dat 73656 entries loaded and sorted 73656 entries Reading BOOTSTRAP.dat 60 entries loaded DUL: Warning: Recreating file "dict.ddl" Generating dict.ddl for version 10 OBJ$: segobjno 18, file 1 block 240 TAB$: segobjno 2, tabno 1, file 1 block 144 COL$: segobjno 2, tabno 5, file 1 block 144 USER$: segobjno 10, tabno 1, file 1 block 208 TABPART$: segobjno 568, file 1 block 3872 INDPART$: segobjno 573, file 1 block 3912 TABCOMPART$: segobjno 590, file 1 block 4056 INDCOMPART$: segobjno 595, file 1 block 4096 TABSUBPART$: segobjno 580, file 1 block 3976 INDSUBPART$: segobjno 585, file 1 block 4016 IND$: segobjno 2, tabno 3, file 1 block 144 ICOL$: segobjno 2, tabno 4, file 1 block 144 LOB$: segobjno 2, tabno 6, file 1 block 144 COLTYPE$: segobjno 2, tabno 7, file 1 block 144 TYPE$: segobjno 495, tabno 1, file 1 block 3344 COLLECTION$: segobjno 495, tabno 2, file 1 block 3344 ATTRIBUTE$: segobjno 495, tabno 3, file 1 block 3344 LOBFRAG$: segobjno 601, file 1 block 4144 LOBCOMPPART$: segobjno 604, file 1 block 4168 UNDO$: segobjno 15, file 1 block 224 TS$: segobjno 6, tabno 2, file 1 block 176 PROPS$: segobjno 98, file 1 block 800 Running generated file "@dict.ddl" to unload the dictionary tables . unloading table OBJ$ DUL: Warning: Recreating file "OBJ.ctl" 18308 rows unloaded . unloading table TAB$ DUL: Warning: Recreating file "TAB.ctl" 2084 rows unloaded . unloading table COL$ DUL: Warning: Recreating file "COL.ctl" 73656 rows unloaded . unloading table USER$ DUL: Warning: Recreating file "USER.ctl" 42 rows unloaded . unloading table TABPART$ 119 rows unloaded . unloading table INDPART$ 136 rows unloaded . unloading table TABCOMPART$ 1 row unloaded . unloading table INDCOMPART$ 0 rows unloaded . unloading table TABSUBPART$ 32 rows unloaded . unloading table INDSUBPART$ 0 rows unloaded . unloading table IND$ 2689 rows unloaded . unloading table ICOL$ 5826 rows unloaded . unloading table LOB$ 286 rows unloaded . unloading table COLTYPE$ 1654 rows unloaded . unloading table TYPE$ 1999 rows unloaded . unloading table COLLECTION$ 687 rows unloaded . unloading table ATTRIBUTE$ 7750 rows unloaded . unloading table LOBFRAG$ 1 row unloaded . unloading table LOBCOMPPART$ 0 rows unloaded . unloading table UNDO$ 11 rows unloaded . unloading table TS$ 7 rows unloaded . unloading table PROPS$ 36 rows unloaded Reading USER.dat 42 entries loaded Reading OBJ.dat 18308 entries loaded and sorted 18308 entries Reading TAB.dat 2084 entries loaded Reading COL.dat 73656 entries loaded and sorted 73656 entries Reading TABPART.dat 119 entries loaded and sorted 119 entries Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries Reading INDPART.dat 136 entries loaded and sorted 136 entries Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries Reading IND.dat 2689 entries loaded Reading LOB.dat 286 entries loaded Reading ICOL.dat 5826 entries loaded Reading COLTYPE.dat 1654 entries loaded Reading TYPE.dat 1999 entries loaded Reading ATTRIBUTE.dat 7750 entries loaded Reading COLLECTION.dat 687 entries loaded Reading BOOTSTRAP.dat 60 entries loaded Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries Reading UNDO.dat 11 entries loaded Reading TS.dat 7 entries loaded Reading PROPS.dat 36 entries loaded Database character set is AL32UTF8 Database national character set is AL16UTF16 DUL> 。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。 . struct row [31] with 9 members . { 04159 kdrhf tflag = 0X2C = 0X2C (--H-FL--) 04160 ub1 lock = 0X02 = 2 04161 ub1 ccnt = 0X03 = 3 . struct col [0] with 2 members . { 04162 ub1 slen = 0X0E = 14 04163 data = HEX '474c4f42414c5f44425f4e414d45' = "GLOBAL_DB_NAME" . } . struct col [1] with 1 member . { 04177 ub1 slen = 0XFF = 255 . } . struct col [2] with 2 members . { 04178 ub1 slen = 0X14 = 20 04179 data = HEX '476c6f62616c206461746162617365206e616d65' = "Global database name" . } . } DUL>

现在修改

DUL> update set row[31].col[1].slen=5; DUL: Warning: Automatic checksum fix checksum set to 0xd556 DUL> update set row[31].col[1].data='LUNAR'; DUL: Warning: Automatic checksum fix checksum set to 0xed1c

修改后的数据如下：

DUL> dump row[31]; . struct row [31] with 9 members . { 04159 kdrhf tflag = 0X2C = 0X2C (--H-FL--) 04160 ub1 lock = 0X02 = 2 04161 ub1 ccnt = 0X03 = 3 . struct col [0] with 2 members . { 04162 ub1 slen = 0X0E = 14 04163 data = HEX '474c4f42414c5f44425f4e414d45' = "GLOBAL_DB_NAME" . } . struct col [1] with 2 members . { 04177 ub1 slen = 0X05 = 5 04178 data = HEX '4c554e4152' = "LUNAR" . } . struct col [2] with 2 members . { 04183 ub1 slen = 0X0F = 15 04184 data = HEX '6c206461746162617365206e616d65' = "l database name" . } . } DUL>

直接启动数据库：

SYS@lunarp>startup Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! Database opened. SYS@lunarp>

发表在 DUL ODU | 标签为 dul, GLOBAL_NAME, props$ | 留下评论

GLOBAL_NAME和props$对象介绍

发表于 2015 年 1 月 5 日由 Lunar

有朋友遇到一个老问题，将数据库 global_name 置空后，数据库不能open。
这类问题已经是几年前的老问题了，这里用4中方法解决。
这一篇先大概介绍一下GLOBAL_NAME对象的来龙去脉。
GLOBAL_NAME和props$对象介绍
 global_name为空导致的数据库不能open—–使用gdb修复(中断oracle启动的部分监测功能)
global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)
global_name为空导致的数据库不能open—–使用DUL修复
 global_name为空导致的数据库不能open—使用BBED修复（bbed恢复update的数据）

我们知道，全局数据库名是在分布式数据库系统中用于标识数据库的唯一名称，默认为DB_NAME.DB_DOMAIN。
该默认值在数据库创建的时候被标记，如果数据库创建后手工修改了DB_NAME或者DB_DOMAIN，全局数据库名称仍然保持为数据库创建时候的DB_NAME.DB_DOMAIN。

SYS@lunarp>select * from GLOBAL_NAME; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ lunarbb Elapsed: 00:00:00.01 SYS@lunarp>desc GLOBAL_NAME Name Null? Type ----------------------------------------------------------------------------------- -------- -------------------------------------------------------- GLOBAL_NAME VARCHAR2(4000) SYS@lunarp>

我们查看一下GLOBAL_NAME是什么类型的对象，其定义是怎样的：

SYS@lunarp>col object_name for a50 SYS@lunarp>select owner,object_name,object_type from dba_objects where object_name=upper('global_name'); OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ -------------------------------------------------- ------------------- SYS GLOBAL_NAME VIEW PUBLIC GLOBAL_NAME SYNONYM Elapsed: 00:00:00.00 SYS@lunarp>select dbms_metadata.get_ddl('VIEW','GLOBAL_NAME','SYS') from dual; CREATE OR REPLACE FORCE VIEW "SYS"."GLOBAL_NAME" ("GLOBAL_NAME") AS select value$ from sys.props$ where name = 'GLOBAL_DB_NAME' Elapsed: 00:00:00.02 SYS@lunarp>select dbms_metadata.get_ddl('SYNONYM','GLOBAL_NAME','PUBLIC') from dual; CREATE OR REPLACE PUBLIC SYNONYM "GLOBAL_NAME" FOR "SYS"."GLOBAL_NAME" Elapsed: 00:00:00.10 SYS@lunarp>

可以看到，GLOBAL_NAME实际上是已于sys.props$ where name = ‘GLOBAL_DB_NAME’的一张视图和同义词。
props$中总共多少行数据，在相同版本是固定的，我这里是11.2，因此共36行：

SYS@lunarp>select count(*) from props$; COUNT(*) ---------- 36 Elapsed: 00:00:00.01 SYS@lunarp>

props$的定义如下：

SYS@lunarp>select dbms_metadata.get_ddl('TABLE','PROPS$','SYS') from dual; CREATE TABLE "SYS"."PROPS$" ( "NAME" VARCHAR2(30) NOT NULL ENABLE, "VALUE$" VARCHAR2(4000), "COMMENT$" VARCHAR2(4000) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" Elapsed: 00:00:03.89 SYS@lunarp>

看一下props$对象存储了哪些内容：

SYS@lunarp>select * from props$; NAME VALUE$ COMMENT$ ------------------------------ -------------------------------------------------- -------------------------------------------------- DICT.BASE 2 dictionary base tables version # DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace DEFAULT_EDITION ORA$BASE Name of the database default edition Flashback Timestamp TimeZone GMT Flashback timestamp created in GMT TDE_MASTER_KEY_ID DBTIMEZONE -08:00 DB time zone DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade DST_PRIMARY_TT_VERSION 14 Version of primary timezone data file DST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file DEFAULT_TBS_TYPE SMALLFILE Default tablespace type NLS_LANGUAGE AMERICAN Language NLS_TERRITORY AMERICA Territory NLS_CURRENCY $ Local currency NLS_ISO_CURRENCY AMERICA ISO currency NLS_NUMERIC_CHARACTERS ., Numeric characters NLS_CHARACTERSET AL32UTF8 Character set NLS_CALENDAR GREGORIAN Calendar system NLS_DATE_FORMAT DD-MON-RR Date format NLS_DATE_LANGUAGE AMERICAN Date language NLS_SORT BINARY Linguistic definition NLS_TIME_FORMAT HH.MI.SSXFF AM Time format NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format NLS_DUAL_CURRENCY $ Dual currency symbol NLS_COMP BINARY NLS comparison NLS_LENGTH_SEMANTICS BYTE NLS length semantics NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set NLS_RDBMS_VERSION 11.2.0.3.0 RDBMS version for NLS parameters GLOBAL_DB_NAME Global database name EXPORT_VIEWS_VERSION 8 Export views revision # WORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in progress WORKLOAD_REPLAY_MODE PREPARE implies external replay clients can connec t; REPLAY implies workload replay is in progress NO_USERID_VERIFIER_SALT 6A58202A068B678B09CF571DC967F3EC 36 rows selected. Elapsed: 00:00:00.00 SYS@lunarp>

当数据库正常启动时，会对PROPS$表进行全表扫描来获取PROPS$中是否含有错误信息。
一般升级的时候系统会将数据库引导对象的错误信息记录到该表的BOOTSTRAP_UPGRADE_ERROR中：

SYS@lunarp>select * from props$ where name = 'GLOBAL_DB_NAME'; NAME VALUE$ COMMENT$ ------------------------------ ------------------------------ -------------------------------------------------- GLOBAL_DB_NAME lunarbb Global database name Elapsed: 00:00:00.01 SYS@lunarp>

启动时10046跟踪的trace中：

PARSING IN CURSOR #140086496404128 len=60 dep=1 uid=0 oct=3 lid=0 tim=1407167733432924 hv=3306824342 ad='8503c550' sqlid='g64r07v2jn8nq' SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR' END OF STMT PARSE #140086496404128:c=17997,e=76437,p=3,cr=12,cu=0,mis=1,r=0,dep=1,og=4,plh=415205717,tim=1407167733432919 EXEC #140086496404128:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167733433101 WAIT #140086496404128: nam='db file sequential read' ela= 60288 file#=1 block#=800 blocks=1 obj#=98 tim=1407167733493464 WAIT #140086496404128: nam='db file sequential read' ela= 14273 file#=1 block#=801 blocks=1 obj#=98 tim=1407167733507890 FETCH #140086496404128:c=999,e=74855,p=2,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167733507990 STAT #140086496404128 id=1 cnt=0 pid=0 pos=1 obj=98 op='TABLE ACCESS FULL PROPS$ (cr=3 pr=2 pw=0 time=74849 us)'

从这里我们看到，读取了file#=1 block#=800和file#=1 block#=801，他们分别为props$的段头和第一个数据块的位置。
他们的对象号是obj#=98，也就是props$。
从数据库中，我们也可以证实这一点：

SYS@lunarp>select owner,segment_name,HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='PROPS$'; OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK ------------------------------ -------------------------------------------------- ----------- ------------ SYS PROPS$ 1 800 Elapsed: 00:00:00.01 SYS@lunarp> SYS@lunarp>select dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) blk# 3 from sys.props$ 4 where name = 'GLOBAL_DB_NAME'; FILE# BLK# ---------- ---------- 1 801 Elapsed: 00:00:00.10 SYS@lunarp>select obj#,dataobj#,owner#,name from obj$ where obj#=98; OBJ# DATAOBJ# OWNER# NAME ---------- ---------- ---------- ------------------------------ 98 98 0 PROPS$ Elapsed: 00:00:00.01 SYS@lunarp>

发表在 Internal | 标签为 GLOBAL_NAME, props$ | 留下评论

BBED简介

发表于 2015 年 1 月 5 日由 Lunar

Oracle8i 的BBED在windows 平台下的$ORACLE_HOME/bin下可以找到
ORACLE9i数据库就自带bbed程序，就在%ORACLE_HOME%/bin目录下，在linux上面也有，需要自己编译。

9i/10g bbed：

cd $ORACLE_HOME/rdbms/lib ls -al *bb* make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed mv bbed $ORACLE_HOME/bin

11g和12.1需要10g的5个文件（bbedzhs.msb是可选的）：

/rdbms/mesg/bbed* bbedus.msb bbedus.msg bbedzhs.msb cp /tmp/lunar/ssbbded.o $ORACLE_HOME/rdbms/lib/ cp /tmp/lunar/sbbdpt.o $ORACLE_HOME/rdbms/lib/ cp /tmp/lunar/bbedus.msg $ORACLE_HOME/rdbms/mesg/ cp /tmp/lunar/bbedus.msb $ORACLE_HOME/rdbms/mesg/ cd $ORACLE_HOME/rdbms/lib ls -al *bb* make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed mv bbed $ORACLE_HOME/bin

BBED的缺省口令是 blockedit：

[oracle@lunar ~]$ bbed Password: blockedit（默认该密码） BBED: Release 2.0.0.0.0 - Limited Production on Thu Jan 17 18:32:16 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED>

一般使用bbed，都是将一些配置信息写入到一个参数文本里，在调用bbed时，指定该参数文件。如：
先从v$datafile中获取file#,name,bytes，然后组成filelist.lst

cat /tmp/lunar/filelist.lst 1 /u01/app/oracle/oradata/dave2/system01.dbf 1761607680 cat /tmp/lunar/bbed.par blocksize=8192 listfile=/tmp/lunar/filelist.lst mode=edit password=blockedit spool=yes [oracle@lunar ~]$bbed parfile=bbed.par BBED: Release 2.0.0.0.0 - Limited Production on Thu Jan 17 18:32:16 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED>

BBED常用命令：
set 设定当前的环境
show 查看当前的环境参数，跟sqlplus的同名命令类似。
dump 列出指定block的内容
find 在指定的block中查找指定的字符串，结果是显示出字符串，及其偏移量–offset，偏移量就是在block中的字节数
modify 修改指定block的指定偏移量的值，可以在线修改。
copy 把一个block的内容copy到另一个block中
verify 检查当前环境是否有坏块
sum 计算block的checksum，modify之后block就被标识为坏块，current checksum与reqired checksum不一致，sum命令可以计算出新的checksum并应用到当前块。
undo 回滚当前的修改操作，如果手误做错了，undo一下就ok了，回到原来的状态。
revert 回滚所有之前的修改操作，意思就是 undo all

可以使用help来查看bbed的命令语法：

BBED> help all SET DBA [ dba | file#, block# ] SET FILENAME 'filename' SET FILE file# SET BLOCK [+/-]block# SET OFFSET [ [+/-]byte offset | symbol | *symbol ] SET BLOCKSIZE bytes SET LIST[FILE] 'filename' SET WIDTH character_count SET COUNT bytes_to_display SET IBASE [ HEX | OCT | DEC ] SET OBASE [ HEX | OCT | DEC ] SET MODE [ BROWSE | EDIT ] SET SPOOL [ Y | N ] SHOW [ <SET parameter> | ALL ] INFO MAP[/v] [ DBA | FILENAME | FILE | BLOCK ] DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ] PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] </Nuf>: N - a number which specifies a repeat count. u - a letter which specifies a unit size: b - b1, ub1 (byte) h - b2, ub2 (half-word) w - b4, ub4(word) r - Oracle table/index row f - a letter which specifies a display format: x - hexadecimal d - decimal u - unsigned decimal o - octal c - character (native) n - Oracle number t - Oracle date i - Oracle rowid FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ] COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ] MODIFY[/x|d|u|o|c] numeric/character string [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] ASSIGN[/x|d|u|o] <target spec>=<source spec> <target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ] <source spec> : [ value | <target spec options> ] SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ] PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ] POP [ALL] REVERT [ DBA | FILE | FILENAME | BLOCK ] UNDO HELP [ <bbed command> | ALL ] VERIFY [ DBA | FILE | FILENAME | BLOCK ] CORRUPT [ DBA | FILE | FILENAME | BLOCK ] BBED>

发表在 bbed | 标签为 bbed | 留下评论

根据块号查看块内数据和修改时间的例子（普通表和压缩表）

发表于 2014 年 12 月 18 日由 Lunar

今天微信群里有个朋友问起一个问题，顺手做了个测试。

问题是这样的：
1，怎么根据file# block#来判断这个block中有多少数据？
2，启用了高级压缩后，如何查看？
3，怎么判断这些数据插入的时间？
4，不适用dump block的形式，可以怎么观察？

最后一个问题不用测试了，不用dump的话，可以使用类似bbed等一堆工具，还可以使用event 10046跟踪来观察。

本次始终压缩相关的脚本可以参考blog：
Exadata上的HCC测试（EHCC）——1
Exadata上的HCC测试（EHCC）—2—：DBMS_COMPRESSION.GET_COMPRESSION_RATIO
Exadata上的HCC测试（EHCC）—3—分区表的压缩

关于HCC压缩的块结构参见：
Exadata上HCC表的数据块结构—1-非压缩数据块结构
 Exadata上HCC表的数据块结构—2-BASIC Compress和OLTP Compress
Exadata上HCC表的数据块结构—3-HCC块（compress for query low）

前三个问题，一次测试如下：

因为朋友问到ASM的情况，这个需求实际上跟是否使用ASM无关，是ORACLE DB的原理，测试环境如下：

[oracle@lunar ~]$ ss SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 5 08:05:48 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select name from v$datafile where rownum=1; NAME -------------------------------------------------------------------------------- +DATA/lunars/system01.dbf SQL> !date Tue Aug 5 08:08:56 CST 2014 ----我这里VM的时间有问题，还停留在2014年的盛夏的某个清晨 *^_^* SQL>

SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss'; Session altered.

随便找一个block，或者指定一个block都可以：

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) blk# 3 from LUNAR 4 where rownum=1; FILE# BLK# ---------- ---------- 1 50345 SQL>

查看这个block中有多少条数据：

SQL> select count(*) from LUNAR 2 where dbms_rowid.rowid_relative_fno(rowid)=1 and dbms_rowid.rowid_block_number(rowid)=50345; COUNT(*) ---------- 88 SQL>

这里看到，目前这个block中存储了88行记录。

查看这88行数据是什么时间插入的，以及他们的ROWID：

SQL> select rowid,scn_to_timestamp(ora_rowscn) row_scn from LUNAR 2 where dbms_rowid.rowid_relative_fno(rowid)=1 and dbms_rowid.rowid_block_number(rowid)=50345; ROWID ROW_SCN ------------------ ---------------------------------------------------------------------- AAAE1IAABAAAMSpAAA 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAB 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAC 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAD 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAE 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAF 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAG 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAH 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAI 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAJ 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAK 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAL 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAM 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAN 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAO 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAP 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAQ 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAR 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAS 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAT 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAU 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAV 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAW 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAX 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAY 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAZ 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAa 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAb 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAc 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAd 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAe 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAf 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAg 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAh 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAi 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAj 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAk 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAl 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAm 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAn 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAo 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAp 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAq 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAr 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAs 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAt 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAu 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAv 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAw 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAx 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAy 2014-08-05 07:15:54 AAAE1IAABAAAMSpAAz 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA0 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA1 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA2 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA3 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA4 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA5 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA6 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA7 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA8 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA9 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA+ 2014-08-05 07:15:54 AAAE1IAABAAAMSpAA/ 2014-08-05 07:15:54 AAAE1IAABAAAMSpABA 2014-08-05 07:15:54 AAAE1IAABAAAMSpABB 2014-08-05 07:15:54 AAAE1IAABAAAMSpABC 2014-08-05 07:15:54 AAAE1IAABAAAMSpABD 2014-08-05 07:15:54 AAAE1IAABAAAMSpABE 2014-08-05 07:15:54 AAAE1IAABAAAMSpABF 2014-08-05 07:15:54 AAAE1IAABAAAMSpABG 2014-08-05 07:15:54 AAAE1IAABAAAMSpABH 2014-08-05 07:15:54 AAAE1IAABAAAMSpABI 2014-08-05 07:15:54 AAAE1IAABAAAMSpABJ 2014-08-05 07:15:54 AAAE1IAABAAAMSpABK 2014-08-05 07:15:54 AAAE1IAABAAAMSpABL 2014-08-05 07:15:54 AAAE1IAABAAAMSpABM 2014-08-05 07:15:54 AAAE1IAABAAAMSpABN 2014-08-05 07:15:54 AAAE1IAABAAAMSpABO 2014-08-05 07:15:54 AAAE1IAABAAAMSpABP 2014-08-05 07:15:54 AAAE1IAABAAAMSpABQ 2014-08-05 07:15:54 AAAE1IAABAAAMSpABR 2014-08-05 07:15:54 AAAE1IAABAAAMSpABS 2014-08-05 07:15:54 AAAE1IAABAAAMSpABT 2014-08-05 07:15:54 AAAE1IAABAAAMSpABU 2014-08-05 07:15:54 AAAE1IAABAAAMSpABV 2014-08-05 07:15:54 AAAE1IAABAAAMSpABW 2014-08-05 07:15:54 AAAE1IAABAAAMSpABX 2014-08-05 07:15:54 88 rows selected. SQL>

这里看到有88行，跟前面的结果是一致的。

看看这个表中有多少个block，以及他们的块号：

SQL> select EXTENT_ID,file_id,block_id 2 from DBA_EXTENTS where segment_name='LUNAR' AND OWNER='SYS'; EXTENT_ID FILE_ID BLOCK_ID ---------- ---------- ---------- 0 1 50344 1 1 50352 2 1 50360 3 1 50368 4 1 50376 5 1 50384 6 1 50392 7 1 50400 8 1 50408 9 1 50416 10 1 50424 11 1 44160 12 1 44168 13 1 44176 14 1 44184 15 1 44192 16 1 44288 17 rows selected. SQL>

因为要测试压缩，那么先看看现在该表的压缩状态，这里我的LUNAR表是没有压缩的：

SQL> @comp COMPRESSION_TYPE NUM_ROWS -------------------------------------------------- ---------- No Compression 17901 SQL>

这里的查询方法，参考：

现在启用压缩（HCC只能在exadata上，否则会报错）：

SQL> alter table lunar move compress for oltp; Table altered. SQL>

再次查询，可以看到，已经是高级压缩了：

SQL> @comp COMPRESSION_TYPE NUM_ROWS -------------------------------------------------- ---------- Advanced compression level 17901 SQL>

这里可以看到，rowid已经改变了，这是因为块的存储格式已经变化了：

SQL> select count(*) from LUNAR 2 where dbms_rowid.rowid_relative_fno(rowid)=1 and dbms_rowid.rowid_block_number(rowid)=50345; COUNT(*) ---------- 0 SQL>

看下现在这个LUNAR表使用了哪些block：

SQL> select EXTENT_ID,file_id,block_id 2 from DBA_EXTENTS where segment_name='LUNAR' AND OWNER='SYS'; EXTENT_ID FILE_ID BLOCK_ID ---------- ---------- ---------- 0 1 44200 1 1 44208 2 1 44216 3 1 44224 4 1 44232 5 1 44240 6 1 44248 7 1 44256 8 1 44264 9 1 44272 10 1 44280 11 1 45768 12 rows selected. SQL>

这里看到比刚才已经减少了17-12=5个block。

SEGMENT_NAME EXTENTS ALLOCATEDBLOCKS USEDBLOCKS AVGROWSPERUSEDBLOCK ------------------ ---------- --------------- ---------- ------------------- LUNAR 12 96 90 198.9 SQL>

查询每个block中的记录数：

SQL> SELECT 2 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) file#, 3 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block#, 4 COUNT(*) rowcount 5 FROM LUNAR 6 GROUP BY 7 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), 8 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) 9 order by 1,2,3; FILE# BLOCK# ROWCOUNT ---------- ---------- ---------- 1 44201 252 1 44202 207 1 44203 215 1 44204 184 1 44205 196 1 44206 218 1 44207 201 1 44208 236 1 44209 221 1 44210 221 1 44211 242 1 44212 215 1 44213 225 1 44214 218 1 44215 246 1 44216 257 1 44217 257 1 44218 260 1 44219 238 1 44220 311 1 44221 266 1 44222 213 1 44223 213 1 44224 218 1 44225 230 1 44226 189 1 44227 189 1 44228 187 1 44229 174 1 44230 201 1 44231 214 1 44232 212 1 44233 193 1 44234 234 1 44235 193 1 44236 202 1 44237 234 1 44238 242 1 44239 202 1 44240 202 1 44241 207 1 44242 220 1 44243 223 1 44244 213 1 44245 200 1 44246 178 1 44247 200 1 44248 194 1 44249 194 1 44250 203 1 44251 227 1 44252 212 1 44253 199 1 44254 220 1 44255 184 1 44256 175 1 44257 162 1 44258 199 1 44259 178 1 44260 188 1 44261 144 1 44262 179 1 44263 171 1 44264 198 1 44265 175 1 44266 175 1 44267 174 1 44268 165 1 44269 170 1 44270 161 1 44271 185 1 44272 165 1 44273 161 1 44274 161 1 44275 161 1 44276 169 1 44277 165 1 44278 165 1 44279 183 1 44280 178 1 44281 175 1 44282 161 1 44283 175 1 44284 177 1 44285 163 1 44286 170 1 44287 187 1 45768 167 1 45769 156 1 45770 161 90 rows selected.

查看某个块中数据的修改时间：

SQL> select rowid,scn_to_timestamp(ora_rowscn) row_scn from LUNAR 2 where dbms_rowid.rowid_relative_fno(rowid)=1 and dbms_rowid.rowid_block_number(rowid)=44277; ROWID ROW_SCN ------------------ ---------------------------------------------------------------------- AAAE1JAABAAAKz1AAA 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAB 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAC 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAD 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAE 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAF 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAG 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAH 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAI 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAJ 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAK 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAL 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAM 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAN 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAO 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAP 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAQ 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAR 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAS 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAT 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAU 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAV 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAW 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAX 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAY 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAZ 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAa 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAb 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAc 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAd 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAe 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAf 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAg 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAh 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAi 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAj 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAk 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAl 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAm 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAn 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAo 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAp 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAq 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAr 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAs 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAt 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAu 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAv 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAw 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAx 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAy 2014-08-05 07:25:58 AAAE1JAABAAAKz1AAz 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA0 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA1 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA2 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA3 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA4 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA5 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA6 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA7 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA8 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA9 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA+ 2014-08-05 07:25:58 AAAE1JAABAAAKz1AA/ 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABA 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABB 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABC 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABD 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABE 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABF 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABG 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABH 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABI 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABJ 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABK 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABL 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABM 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABN 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABO 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABP 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABQ 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABR 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABS 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABT 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABU 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABV 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABW 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABX 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABY 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABZ 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABa 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABb 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABc 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABd 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABe 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABf 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABg 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABh 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABi 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABj 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABk 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABl 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABm 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABn 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABo 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABp 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABq 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABr 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABs 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABt 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABu 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABv 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABw 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABx 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABy 2014-08-05 07:25:58 AAAE1JAABAAAKz1ABz 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB0 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB1 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB2 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB3 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB4 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB5 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB6 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB7 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB8 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB9 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB+ 2014-08-05 07:25:58 AAAE1JAABAAAKz1AB/ 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACA 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACB 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACC 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACD 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACE 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACF 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACG 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACH 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACI 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACJ 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACK 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACL 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACM 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACN 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACO 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACP 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACQ 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACR 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACS 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACT 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACU 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACV 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACW 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACX 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACY 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACZ 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACa 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACb 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACc 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACd 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACe 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACf 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACg 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACh 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACi 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACj 2014-08-05 07:25:58 AAAE1JAABAAAKz1ACk 2014-08-05 07:25:58 165 rows selected. SQL> !date Tue Aug 5 07:37:48 CST 2014 注意，我这个VM的系统时间是“Tue Aug 5 07:37:48 CST 2014” SQL>

发表在 Internal, 内部机制 | 标签为 rowid, 压缩 | 一条评论

通过修改控制文件来修改SCN

发表于 2014 年 12 月 14 日由 Lunar

之前有一些简单介绍SCN的文章：
浅谈SCN_1–从oracle7至今，如何获取scn
浅谈SCN_2–_kcmgas_函数
 使用ORACDEBUG 修改数据库SCN
这个测试是接着上次的使用oradebug修改SCN的，这里使用修改控制文件SCN和相关标示位的方法：

SQL> startup mount ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2255832 bytes Variable Size 184550440 bytes Database Buffers 432013312 bytes Redo Buffers 7507968 bytes Database mounted. SQL> select checkpoint_change#,to_char(checkpoint_change#,'XXXXXXXXXXXXXXXXX') from v$database; CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT ------------------ ------------------ 2726293 299995

这个测试，我们把SCN增加100万，即从 2726293 修改为 3726293。

SQL> select '3726293',to_char(3726293,'XXXXXXXXXXXXXXXXX') from v$database; '372629 TO_CHAR(3726293,'X ------- ------------------ 3726293 38DBD5 SQL>

查看当前控制文件的位置：

SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string +DATA/lunars/control01.ctl, +D ATA/lunars/control02.ctl control_management_pack_access string DIAGNOSTIC+TUNING SQL>

将控制文件拿到本地，进行修改，修改过程如下：
首先找到数据库SCN：

修改SCN和相关标示位：

讲数据库shutdown，然后将修改后的控制文件copy到ASM中，并使用这个控制文件启动数据库：

ASMCMD> ls CONTROLFILE/ DATAFILE/ ONLINELOG/ TEMPFILE/ control01.ctl control02.ctl lunar01.dbf redo01.log redo02.log redo03.log soe01.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf ASMCMD> rm control01.ctl ASMCMD> rm control02.ctl ASMCMD> cp /tmp/control01.dbf +DATA/lunars/control01.ctl copying /tmp/control01.dbf -> +DATA/lunars/control01.ctl ASMCMD> cp /tmp/control01.dbf +DATA/lunars/control02.ctl copying /tmp/control01.dbf -> +DATA/lunars/control02.ctl ASMCMD>

Mount数据库，并查看数据库SCN：

SQL> startup mount ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2255832 bytes Variable Size 184550440 bytes Database Buffers 432013312 bytes Redo Buffers 7507968 bytes Database mounted. SQL> select checkpoint_change#,to_char(checkpoint_change#,'XXXXXXXXXXXXXXXXX') from v$database; CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT ------------------ ------------------ 3726293 38DBD5 SQL> alter database open; Database altered. SQL> select checkpoint_change#,to_char(checkpoint_change#,'XXXXXXXXXXXXXXXXX') from v$database; CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT ------------------ ------------------ 3726296 38DBD8 SQL> SQL>

这里我们看到，数据库的SCN已经修改为我们指定的 3726296了。

发表在 backup&recovery, Internal | 标签为控制文件 scn | 留下评论

使用ORACDEBUG 修改数据库SCN

发表于 2014 年 12 月 14 日由 Lunar

之前有一些简单介绍SCN的文章：
浅谈SCN_1–从oracle7至今，如何获取scn
浅谈SCN_2–_kcmgas_函数
 通过修改控制文件来修改SCN

1988年Oracle发布了Oracle V6，这一版本中Oracle引入了热备的操作，同时SCN使用48位存储的算法写死在代码中，一直沿用至12c以前（12c开始使用8个bytes存储SCN）。由于ORACLE的SCN是由48位来表示的，因此最大值不能超过2的48次方
Oracle为了确保48位的SCN能够用足够长的时间（500年），于是对SCN做出了一个限制，就是每秒钟SCN最大增长不能超过16K，Oracle从1988年1月1日0点0分0秒为基准时间，到当前的秒钟数乘以16K，就是当前SCN的最大允许值这就是SCN HEADROOM。
因此SCN天花板的计算公式就类似于：
(当前时间-19880101 000000)*16384–(current_scn)，其中 16384是SCN的内部增长速度16k，这是代码中的硬限制。
这个限制在11.2.0.2版本之前，scn 的最大增长频率是16k,在11.2.0.2版本开始，为32k。
此行为是受到下面参数_max_reasonable_scn_rate控制的：

SQL> @paras Enter value for paras: scn old 6: AND x.ksppinm LIKE '%&paras%' new 6: AND x.ksppinm LIKE '%scn%' NAME VALUE DESCRIB -------------------------------------------------- -------------------- ------------------------------------------------------------ 。。。。。。。 _external_scn_rejection_threshold_hours 24 Lag in hours between max allowed SCN and an external SCN _max_reasonable_scn_rate 32768 Max reasonable SCN rate 。。。。。 17 rows selected. SQL>

在11.2中，Oracle除了将SCN 每秒最大的增长量从16K加大为32K，还引入了一个阀值，用于阻断有SCN HEADROOM问题的系统将故障传播到其他系统。
这些修复包含在下列补丁中：

如果SCN发生突增的情况，alert中就会出现类似下面的告警：

因此，打了这上面这些补丁后，就不能使用以前的参数直接修改SCN了。

然后，有时候数据库遇到一些异常错误，还是需要将SCN推进的到一个合适的值，例如，常见一些错误造成数据库的部分block跟数据库SCN不一致，或者一些有undo$数据库启动时引导失败：
ORA-600 [2256]
ORA-600 [2662]
ORA-600 [4000]
ORA-600 [kcsadjn1]

在以前我们使用参数来修改SCN，例如：
event=”10015 trace name adjust_scn level x” 或者使用 _minimum_giga_scn 参数
正如上面说的，应用了上面的补丁或者版本后，上述2种该参数的方法，就不能用来推进SCN了，这时，我们可以使用另外的几种方法：
1，bbed直接修改（这个需要看情况，有些时候比较麻烦，比如需要修改的文件很多）
2，使用ORACDEBUG 修改 kcsgscn
3，修改控制文件
这里就测试“使用ORACDEBUG 修改 kcsgscn”的方法。
注意：不同的ENDIAN和字长不同，比如AIX是WRAP SCN在前，BASE SCN在后，而Linux是BASE SCN在前，WRAP SCN在后的格式

当前数据库的SCN

[oracle@lunar ~]$ ss SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 5 06:16:39 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SQL> select checkpoint_change#,to_char(checkpoint_change#,'XXXXXXXXXXXXXXXXX') from v$database; CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT ------------------ ------------------ 1723797 1A4D95 SQL>

这里看到当前数据库SCN为1723797，这个测试我们把当前数据库的SCN（1723797）增加100万，即2723797。

SQL> oradebug setmypid Statement processed. SQL> oradebug dumpvar sga kcsgscn_ kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000 SQL> select checkpoint_change#,to_char(checkpoint_change#,'XXXXXXXXXXXXXXXXX') from v$database; CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT ------------------ ------------------ 1723797 1A4D95 SQL>

因为数据库是mount状态，因此这里看到kcsgscn_中SCN的值是0。

下面我们修改为2723797，计算一下：

SQL> select to_char(2723797,'XXXXXXXXXXXXXXXX') from dual; TO_CHAR(2723797,' ----------------- 298FD5 SQL> oradebug setmypid Statement processed. SQL> oradebug dumpvar sga kcsgscn_ kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000 SQL> oradebug poke 0x06001AE70 8 0x0000000000298FD5 BEFORE: [06001AE70, 06001AE78) = 00000000 00000000 AFTER: [06001AE70, 06001AE78) = 00298FD5 00000000 SQL> oradebug dumpvar sga kcsgscn_ kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00298FD5 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000 SQL> SQL> alter database open; Database altered. SQL> select checkpoint_change#,checkpoint_change#/1024/1024/1024 SCN_WARP from v$database; CHECKPOINT_CHANGE# SCN_WARP ------------------ ---------- 2723798 .002536735 SQL>

这里看到数据库SCN已经修改成了2723797

发表在 backup&recovery, Internal | 标签为 oradebug kcsgscn_ | 留下评论

奇怪的AWR–部分sql执行次数跟实际情况相差10倍之多

发表于 2014 年 11 月 29 日由 Lunar

应用通知有个应用很慢，于是查原因
下面是AWR的采样时间：

从AWR可以看到该语句30分钟内，这两条应用反应慢的语句的执行次数分别在930万次和950万次，这个跟应用的反映差了一个数量级：

但是应用反映，实际上在应用中实际设置的加载量是10分钟25万次。

这就很奇怪了，首先用logminer随机抽查了10分钟的归档，并挖掘了数据:

SYS@ LUNARDB> exec dbms_logmnr.add_logfile (logfilename =>'/lunar_data/arch/1_28754_856327844.dbf', options =>dbms_logmnr.new)LUNARDB PL/SQL procedure successfully completed. SYS@ LUNARDB> exec dbms_logmnr.add_logfile (logfilename =>'/lunar_data/arch/1_28755_856327844.dbf', options =>dbms_logmnr.ADDFILE)LUNARDB PL/SQL procedure successfully completed. SYS@ LUNARDB> exec dbms_logmnr.add_logfile (logfilename =>'/lunar_data/arch/1_28756_856327844.dbf', options =>dbms_logmnr.ADDFILE)LUNARDB PL/SQL procedure successfully completed. Elapsed: 00:00:00.04 SYS@ LUNARDB> EXEC SYS.DBMS_LOGMNR.START_LOGMNR(StartScn => 6214773543477,endScn => 6214774035996,OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)LUNARDB PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SYS@ LUNARDB>

根据Logminer的挖掘的结果，发现实际执行次数跟应用反馈的执行次数差不多，跟AWR的数据相差大概10倍：

SYS@ LUNARDB> create table USR_LUNARDB.LGMC_20141113_BAK tablespace PART_DATA as select * from v$logmnr_contents Table created. SYS@ LUNARDB> ---AWR: 4kaxn5926cz5f SYS@ LUNARDB> select count(*) from USR_LUNARDB.LGMC_20141113_BAK where sql_redo LIKE 'update %BALANCE_TAB%' COUNT(*) ---------- 228333 ---AWR: 5kfrvbw8hx2dv SYS@ LUNARDB> select count(*) from USR_LUNARDB.LGMC_20141113_BAK where sql_redo LIKE 'insert %BALANCE_TAB%' COUNT(*) ---------- 205244 SYS@ LUNARDB>

再查一下其他语句：

select count(*) from USR_LUNARDB.LGMC_20141113_BAK where sql_redo LIKE 'update %BALANCE_TAB%'; COUNT(*) ---------- 97392

检查awr发现，该语句的执行次数基本跟吻合。
这里也就是说，部分语句执行次数相差10倍，但是部分语句的执行次数又是正常的，奇葩！

检查share pool，发现大量sql异常LUNARDB

这里的sql信息感觉严重失真，貌似重复了很多，也就是应该purge的sql在V$SQL中没有被更新，而是越积越多……

重启服务器后，恢复正常，目前还没有找到具体的bug信息。

发表在 Performence Tuning | 标签为 awr bug | 留下评论

← 早期文章

较新文章 →

站内搜索

Search for:

Oracle证书

分类目录

ASM (30)

Database (86)

backup&recovery (21)

expdp/impdp (5)

Installation and Deinstall (31)

network (7)

ORA-600 or ORA-7445 (6)

Performence Tuning (13)

troubleshoooting (2)

Dataguard (7)

EBS (3)

EBS系统管理 (1)

安装、克隆、迁移 (2)

Exadata (120)

FAQ (19)

POC和性能调整 (11)

体系架构 (19)

内部机制 (22)

安装和升级 (14)

性能指标 (8)

Exadata V1 (1)

Exadata V2 (1)

Exadata X2-2 (2)

Exadata X3-2 (1)

Exadata X4-2 (1)

FAQ (1)

故障诊断 (3)

日常运维 (15)

硬件配置 (43)

Exadata V1 (6)

Exadata V2 (6)

Exadata X2-2 (6)

Exadata X3-2 (8)

Exadata X4-2 (8)

FAQ (1)

FAQ (16)

Internal (21)

bbed (2)

DUL ODU (4)

Linux (20)

MYSQL (8)

FAQ (7)

复制 (1)

安装配置 (1)

OGG (1)

ORA-600/7445 (2)

ORA-XXXXX (5)

Oracle 11.1 & Oracle11.2 (6)

ORACLE 12C (21)

Oracle 8 & Oracle 8i (1)

RAC (47)

SAP (2)

Scripts (6)

未分类 (1)

虚拟化 (1)

2024 年十月

S M T W T F S

« Nov

1 2 3 4 5

6 7 8 9 10 11 12

13 14 15 16 17 18 19

20 21 22 23 24 25 26

27 28 29 30 31

文章归档
文章归档

近期文章

针对最近黑客攻击数据库的解决方案和预防建议

CentOS7.2（RHEL 7.2）的CPU占用高（%system 占用高）

Oracle 12.1 RAC 系列 – 配置第二个网络和相应的SCAN2

Oracle 12.1 RAC 系列-安装新主机，识别老存储和恢复数据库

Oracle 12.2的Sharding-1-基础概念

11.2 RAC 系列-安装新主机，识别老存储-3-配置老存储的数据库

11.2 RAC 系列-安装新主机，识别老存储-2-准备识别数据库

11.2 RAC 系列-安装新主机，识别老存储-1-识别ASM磁盘

2016年1月PSU列表

单实例数据库转换为RAC数据库–使用rconfig转换

近期评论
tom 发表在《exadata巡检报告的模板》
cyx 发表在《关于我》
李科胜发表在《EBS克隆–db和app分开在两个服务器上》
xiao 发表在《exadata巡检报告的模板》
Chris Sun 发表在《使用Oracle 11.2的DBMS_RESOURCE_MANAGER.CALIBRATE_IO对Exadata X5（HC）进行测试》