ORA-600 [kddummy_blkchk] [18038] 一例

2010/05/21 by 3 Comments

一位客户的Oracle告警日志中出现了ORA-600 [kddummy_blkchk] [18038]故障，alert中的具体信息：

Errors in file /u01/app/oracle/admin/prdw014a/udump/prdw014a_ora_4377.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [222], [5792], [18038], [], [], [], []
Mon May 17 15:27:53 2010
Trace dumping is performing id=[cdmp_20100517152753]
Mon May 17 15:27:53 2010
Doing block recovery for file 2 block 504365
Block recovery from logseq 159276, block 166357 to scn 10934615778284
Mon May 17 15:27:53 2010
Recovery of Online Redo Log: Thread 1 Group 4 Seq 159276 Reading mem 0
Mem# 0: /u01/app/oracle/dataPRDW014/redo04a_1.log
Mem# 1: /u01/app/oracle/dataPRDW014/redo04a_2.log
Block recovery completed at rba 159276.167277.16, scn 2545.3924010007
Doing block recovery for file 222 block 5792
Block recovery from logseq 159276, block 84741 to scn 10934615778283
Mon May 17 15:27:53 2010
Recovery of Online Redo Log: Thread 1 Group 4 Seq 159276 Reading mem 0
Mem# 0: /u01/app/oracle/dataPRDW014/redo04a_1.log
Mem# 1: /u01/app/oracle/dataPRDW014/redo04a_2.log
Block recovery completed at rba 159276.167277.16, scn 2545.3924009964
Mon May 17 15:27:55 2010
Block recovery completed at rba 159276.167277.16, scn 2545.3924009964
Mon May 17 15:27:55 2010
Corrupt Block Found
TSN = 67, TSNAME = OBA_DATA
RFN = 222, BLK = 5792, RDBA = 931141280
OBJN = 1657288, OBJD = 1699775, OBJECT = W_ORG_DS, SUBOBJECT =
SEGMENT OWNER = BMS_OBA_DW, SEGMENT TYPE = Table Segment
Mon May 17 15:32:56 2010
Trace dumping is performing id=[cdmp_20100517153255]

附600错误产生的trace信息：
prdw014a_ora_4377.trc

/u01/app/oracle/admin/prdw014a/udump/prdw014a_ora_4377.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining Scoring Engine
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/102prdw014
System name: SunOS
Node name: v08k405
Release: 5.9
Version: Generic_122300-29
Machine: sun4u
Instance name: prdw014a
Redo thread mounted by this instance: 1
Oracle process number: 109
Unix process pid: 4377, image: oracle@v08k405

*** 2010-05-17 15:23:15.391
*** ACTION NAME:() 2010-05-17 15:23:15.389
*** MODULE NAME:(pmdtm@v04k413 (TNS V1-V3)) 2010-05-17 15:23:15.389
*** SERVICE NAME:(prdw014_taf) 2010-05-17 15:23:15.389
*** SESSION ID:(789.48811) 2010-05-17 15:23:15.389
TYP:0 CLS: 4 AFN:222 DBA:0x378016a0 OBJ:1699775 SCN:0x09f1.e9e3a3eb SEQ: 2 OP:14.4
kteop redo - redo operation on extent map
RESIZE: entry:0 delta:

...
..
..
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kddummy_blkchk], [222], [5792], [18038], [], [], [], []
Current SQL statement for this session:
INSERT /*+ SYS_DL_CURSOR */ INTO bms_oba_dw.W_ORG_DS ("W_CUSTOMER_CLASS","NAME","ST_ADDRESS","CITY","STATE","ZIPCODE","COUNTRY","CUST_TYPE_CODE","CUST_TYPE_NAME","ACTIVE_FLG","DOM_ULT_DUNS_NUM","DUNS_NUM","EMP_COUNT","FORMED_DT","GLBLULT_DUNS_NUM","ANNUAL_REVENUE","BRANCH_FLG","BIRTH_DT","NO_OF_CHILDREN","LEGAL_NAME","FAMILY_NAME","OTHER_NAME","PREFERRED_NAME","INDV_ADDNL_TITLE","INDV_TITLE","INDV_MARITAL_STATE","INDV_GENDER","EMAIL_ADDRESS","RELATIONSHIP_STATE","INDV_EMP_STATUS","FAX_NUM","PAGER_NUM","MOBILE_NUM","LIFE_CYCLE_STATE","CUST_CAT_CODE","CUST_CAT_NAME","SIC_CODE","SIC_NAME","GOVT_ID_TYPE","GOVT_ID_VALUE","DUNNS_SITE_NAME","DUNNS_GLOBAL_NAME","DUNNS_LEGAL_NAME","CUSTOMER_NUM","ALT_CUSTOMER_NUM","ALT_PHONE_NUM","INTERNET_HOME_PAGE","LEGAL_STRUCT_CODE","LEGAL_STRUCT_NAME","DIRECT_MKTG_FLG","SOLICITATION_FLG","CUSTOMER_HIER1_CODE","CUSTOMER_HIER1_NAME","CUSTOMER_HIER2_CODE","CUSTOMER_HIER2_NAME","CUSTOMER_HIER3_CODE","CUSTOMER_HIER3_NAME","CUSTOMER_HIER4_CODE","CUSTOMER_HIER4_NAME","CUSTOMER_HIER5_CODE","CUSTOMER_HIER5_NAME","CUSTOMER_HIER6_CODE","CREATED_BY_ID","CHANGED_BY_ID","CREATED_ON_DT","CHANGED_ON_DT","AUX1_CHANGED_ON_DT","AUX2_CHANGED_ON_DT","AUX3_CHANGED_ON_DT","AUX4_CHANGED_ON_DT","SRC_EFF_FROM_DT","SRC_EFF_TO_DT","DELETE_FLG","DATASOURCE_NUM_ID","INTEGRATION_ID","TENANT_ID","X_CUSTOM","MOT_ATTRIBUTE1","MOT_ATTRIBUTE2","MOT_ATTRIBUTE3","MOT_ATTRIBUTE4","MOT_ATTRIBUTE5","MOT_ATTRIBUTE6","MOT_ATTRIBUTE7","MOT_ATTRIBUTE8","MOT_ATTRIBUTE9","MOT_ATTRIBUTE10","MOT_ATTRIBUTE11","MOT_ATTRIBUTE12","MOT_ATTRIBUTE13","MOT_ATTRIBUTE14","MOT_ATTRIBUTE15","MOT_ATTRIBUTE16","MOT_ATTRIBUTE17","MOT_ATTRIBUTE18","MOT_ATTRIBUTE19","MOT_ATTRIBUTE20","MOT_PARTY_TYPE","MOT_PHONE_AREA_CODE","MOT_ORIG_SYSTEM_REFERENCE","MOT_PER_EMAIL_ADDR","MOT_PERSON_FIRST_NAME","MOT_PHONE_EXTENSION","MOT_ALTERNATE_NAME","MOT_TELEPHONE_TYPE","MOT_SALES_CHANNEL_CODE","MOT_ACCOUNT_NAME","MOT_ATTRIBUTE_CATEGORY","MOT_INTERCOMPANY_FLAG","MOT_PARTY_NUMBER","MOT_PARTY_ID","MOT_LAST_UPDATE_LOGIN","MOT_CUST_CLASS_DESC","MOT_RECEIPT_METHOD_NAME","MOT_PHONE_NUMBER","MOT_CONTACT_POINT_PURPOSE","MOT_SALESREP_NAME","MOT_PAY_TERMS_CODE","MOT_PAY_TERMS_NAME") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+744         CALL     ksedst()             000000840 ?
                                                   FFFFFFFF7FFF620C ?
                                                   000000000 ?
                                                   FFFFFFFF7FFF2D00 ?
                                                   FFFFFFFF7FFF1A68 ?
                                                   FFFFFFFF7FFF2468 ?
kgerinv()+200        PTR_CALL 0000000000000000     000106800 ? 10681C1C4 ?
                                                   10681C000 ? 00010681C ?
                                                   000106800 ? 10681C1C4 ?
kseinpre()+96        CALL     kgerinv()            106816B18 ? 000000000 ?
                                                   1064564C0 ? 000000003 ?
                                                   FFFFFFFF7FFF6750 ?
                                                   000001430 ?
ksesin()+52          CALL     kseinpre()           000106800 ? 000000003 ?
                                                   00000025F ? 10681C1B8 ?
                                                   FFFFFFFF7FFF6750 ?
                                                   1068167D8 ?
kco_blkchk()+2568    CALL     ksesin()             1064564C0 ? 000000003 ?
                                                   000106800 ? 0000000DE ?
                                                   000000000 ? 000106800 ?
kcoapl()+1284        CALL     kco_blkchk()         0001900DE ? 0378016A0 ?
                                                   0000016A0 ? 00000FC00 ?
                                                   000000000 ?
                                                   FFFFFFFF7FFF89F8 ?
kcbapl()+412         CALL     kcoapl()             000000002 ? 000002300 ?
                                                   000105800 ? 583DBC000 ?
                                                   106816C98 ? 00010598F ?
kcrfw_redo_gen()+16  CALL     kcbapl()             FFFFFFFF7FFF89B8 ?
376                                                583FB7870 ?
                                                   FFFFFFFF7AF3AA3C ?
                                                   B6E9FABD0 ? 000000000 ?
                                                   583DBC000 ?
kcbchg1_main()+1363  CALL     kcrfw_redo_gen()     000000000 ?
2                                                  FFFFFFFF7FFF76C8 ?
                                                   B693A9998 ? 000000000 ?
                                                   3800135A0 ?
                                                   FFFFFFFF7FFF7700 ?
kcbchg1()+1324       CALL     kcbchg1_main()       000100C00 ?
                                                   FFFFFFFF7FFF7850 ?
                                                   000000000 ? 583FB7870 ?
                                                   000000000 ? 00000FFFF ?
ktuchg()+968         CALL     kcbchg1()            000106819 ? 1068195B8 ?
                                                   1068195C8 ? 106819000 ?
                                                   000000000 ? 106819000 ?
ktbchg2nt()+104      CALL     ktuchg()             000000002 ? 000000001 ?
                                                   FFFFFFFF7FFF8928 ?
                                                   B67A76DD8 ? 000000000 ?
                                                   000000000 ?
kteopgen()+728       CALL     ktbchg2nt()          FFFFFFFF7FFF89B8 ?
                                                   FFFFFFFF7FFF87C4 ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFF7FFF8928 ?
                                                   FFFFFFFF7FFF9D98 ?
kteopresize()+2276   CALL     kteopgen()           FFFFFFFF7FFF89B8 ?
                                                   000000006 ? 000106800 ?
                                                   000000002 ? 10682247C ?
                                                   106816B18 ?
ktsxbmdelext1()+968  CALL     kteopresize()        FFFFFFFF7FFF9D98 ?
8                                                  FFFFFFFF7FFF9E88 ?
                                                   000000004 ? 000000002 ?
                                                   000000000 ? 000000000 ?
ktsstrm_segment()+6  CALL     ktsxbmdelext1()      FFFFFFFF7AD33A78 ?
308                                                0000016A0 ? 0003FFFFF ?
                                                   FFFFFFFF7AD33A78 ?
                                                   106822000 ? 000000043 ?
ktsmg_trimf()+1208   CALL     ktsstrm_segment()    000000000 ? 000000003 ?
                                                   000000001 ? 000100C00 ?
                                                   106819000 ? 000000000 ?
kdbltrmt()+1916      CALL     ktsmg_trimf()        00010598F ? 0000010E2 ?
                                                   106822478 ? 000000005 ?
                                                   10682247C ? 106816B18 ?
kdblfpl()+96         CALL     kdbltrmt()           000000006 ? 000000000 ?
                                                   FFFFFFFF7AD33918 ?
                                                   000000180 ? 0000010E4 ?
                                                   000000008 ?
kdblfl()+1948        CALL     kdblfpl()            FFFFFFFF7FFFB0AC ?
                                                   FFFFFFFF7AD33918 ?
                                                   000000000 ?
                                                   FFFFFFFF7AD33AE0 ?
                                                   FFFFFFFF7AD33A68 ?
                                                   000000000 ?
klafin()+160         CALL     kdblfl()             FFFFFFFF7FFFB0AC ?
                                                   FFFFFFFF7AD33918 ?
                                                   000000000 ? 000000001 ?
                                                   000000008 ? 000106800 ?
kpodpfin()+76        CALL     klafin()             FFFFFFFF7AF35C40 ?
                                                   1059BF2B8 ? 000000321 ?
                                                   FFFFFFFF7AD33918 ?
                                                   000000000 ? 000400000 ?
kpodpmop()+320       CALL     kpodpfin()           FFFFFFFF7AF35C40 ?
                                                   000106816 ? 000106800 ?
                                                   000000321 ? 000000001 ?
                                                   FFFFFFFF7AF35BC8 ?
opiodr()+1496        PTR_CALL 0000000000000000     000000301 ? 000000321 ?

进过与Oracle support确认,定位为Bug 5386204 – Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment [ID 5386204.8].
“kteop redo – redo operation on extent map” 记录是确定该Bug的一个重要依据。

该Bug的Oracle note:

Bug 5386204  Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment
This note gives a brief overview of bug 5386204.
The content was last updated on: 08-FEB-2010
Click here for details of each of the sections below.
This bug is alerted in Note:580561.1
Affects:

Product (Component)    Oracle Server (Rdbms)
Range of versions believed to be affected    Versions < 11
Versions confirmed as being affected

* 9.2.0.8
* 10.2.0.1
* 10.2.0.2
* 10.2.0.3
* 10.2.0.4

Platforms affected    Generic (all / most platforms affected)

Fixed:

This issue is fixed in

* 9.2.0.8 Patch 15 on Windows Platforms
* 10.2.0.2 Patch 15 on Windows Platforms
* 10.2.0.3 Patch 5 on Windows Platforms
* 10.2.0.4.1 (Patch Set Update)
* 10.2.0.4 Patch 2 on Windows Platforms
* 10.2.0.5 (Server Patch Set)
* 11.1.0.6 (Base Release)

Symptoms:

Related To:

* Internal Error May Occur (ORA-600)
* Corruption (Logical)
* ORA-600 [kddummy_blkchk]

* Direct Path Operations
* ASSM Space Management (Bitmap Managed Segments)

Description

Block corruption / ORA-600 [kddummy_blkchk][file#] [block#] [18038]
can occur on a segment which has been direct loaded.

(The corruption shows as a PAGETABLE SEGMENT HEADER
having blocks in the “Auxillary Map” outside of the “Extent Map”
range)

Note:
This bug was previously incorrectly listed as fixed in 10.2.0.4

Further details on this issue can be found in Note:580561.1
ORA-600 [kddummy_blkchk][][][18038] during extent operations like TRUNCATE on ASSM tablespaces [ID 580561.1]

Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.8 to 10.2.0.4
Information in this document applies to any platform.
Description

This alert describes the problem in Bug 5386204 / Note 5386204.8.

Block corruption with error ORA-600 [kddummy_blkchk] [file#] [block#] [18038]
may be reported during a DROP/TRUNCATE

The corruption shows as a PAGETABLE SEGMENT HEADER having blocks in the
“Auxillary Map” outside of the “Extent Map” range.

The same operation terminated without any error in previous RDBMS versions
like Oracle9i.

Likelihood of Occurrence

The object is populated by direct path operations such as SQL*Loader using DIRECT=Y for example.
The object is stored in a Locally Managed Tablespace (LMT) that is using ASSM (dba_tablespaces.segment_space_management=’AUTO’).
Bug 5386204 is mostly hit when db_block_size=16384.

Possible Symptoms

One evidence of hitting this bug might be the value 18038 in the third argument of
ORA-600 [kddummy_blkchk] where [18038] is a check error code.

@Error check code 18038 means that the “Data dba” stored in “Auxiliary Map” is out of range

@TYP:0 CLS: 4 AFN:234 DBA:0x3a801554 OBJ:0 SCN:0x000b.290f5e0d SEQ:  1 OP:14.2

@In this case “Data dba: 0x3a801555” stored in the “Auxiliary Map” is equal to 0x3a801551 + 4 which is out of the extent 0, hence the error.
@Note that extent 0 is 4 blocks, so extent 0 starts from 0x3a801551 to 0x3a801554.

Workaround or Resolution

In order to identify objects that are affected by the corruption, use the procedure
DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY

@DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY is also an option but it requires patch for Bug 6760697 is needed)

How to execute DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY:

alter system set DB_BLOCK_CHECKSUM = OFF;
— open a new session and run :
exec DBMS_SPACE_ADMIN.assm_tablespace_verify(‘<Tablespace Name>’, DBMS_SPACE_ADMIN.TS_VERIFY_DEEP, DBMS_SPACE_ADMIN.SEGMENT_VERIFY_DEEP);

See if any trace file is generated in the directory defined by user_dump_dest.
The absence of a trace file means that no corrupt segments were found.

Note: DB_BLOCK_CHECKSUM has to be disabled; otherwise the same ORA-600 error may be produced

@Oracle check block type 0x23=PAGETABLE SEGMENT HEADER even if DB_BLOCK_CHECKING is not set.

Example of output from DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY

Segment header [dba: 0x003a801554, (file 234,block 5460)]
Segment object id: 7825838; inc. no.: 0
*********

verifying extent map and tablespace bitmap consistency
———
Verifying extent map and  auxilliary extent map   consistency in   the segment
Block Corruption in seg hdr / ext map block:                rdba: 0x3a801554, err code: 18038

Identifying the object using the segment header information.

Segment header [dba: 0x003a801554, (file 234,block 5460)]

select *
from DBA_EXTENTS
where FILE_ID = 234
and 5460 between block_id and block_id + blocks – 1;

Identifying the object using the Segment object id information.

Segment object id: 7825838; inc. no.: 0

select *
from DBA_OBJECTS
where DATA_OBJECT_ID = 7825838;

@How to execute DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY

WORKAROUNDs:

Disable DB_BLOCK_CHECKSUM for any action taken.

Note: DB_BLOCK_CHECKSUM has to be disabled; otherwise the same ORA-600 error may be produced

alter system set DB_BLOCK_CHECKSUM = OFF;
— open a new session

DROP TABLE .. PURGE;
ALTER TABLE .. MOVE ..;
Create table as select (CTAS)
export/import, etc

Patches

The patch prevents the corruption from taking place. Affected objects will have to be recreated.

This bug was previously incorrectly listed as fixed in 10.2.0.4.

@This problem is fixed in the 10.2.0.5 Patch Set (not available yet and still without a due date).
This problem is fixed in the 11.1.0.6 rdbms release.

One off patches for this issue are available for some platforms / versions.

See Patch 5386204 for patch availability.

Modification History

03-JUN-2008 – Initial Alert version
04-JUN-2008 – Implemented correction
11-JUN-2008 – Added info about DB_BLOCK_CHECKSUM
13-JUN-2008 – Published

References

BUG:5386204 – ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038

NOTE:5386204.8 – Bug 5386204 – Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment

Bug 5386204: ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038

Show Bug Attributes Bug Attributes
Type     B – Defect     Fixed in Product Version     11.1
Severity     1 – Complete Loss of Service     Product Version     10.2.0.2
Status     80 – Development to Q/A     Platform     226 – Linux x86-64
Created     12-Jul-2006     Platform Version     2.6.5-7.191-SMP
Updated     20-May-2010     Base Bug     –
Database Version     10.2.0.2
Affects Platforms     Generic
Product Source     Oracle

Show Related Products Related Products
Line     Oracle Database Products     Family     Oracle Database
Area     Oracle Database     Product     5 – Oracle Server – Enterprise Edition

Hdr: 5386204 10.2.0.2 RDBMS 10.2.0.2 SPACE PRODID-5 PORTID-226 ORA-600
Abstract: ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038

*** 07/12/06 12:59 am ***
TAR:
—-

PROBLEM:
——–
1. Clear description of the problem encountered
Customer is getting repeated ORA-600 [kddummy_blkchk] errors reported with
internal check code 18038 on tables which have had bulk deletions made.  This
has occurred on both production and test instances.

2. Pertinent configuration information (MTS/OPS/distributed/etc)
RAC, ASM

3. Indication of the frequency and predictability of the problem
Problem is intermittent but occurs several times a day impacting the
customers ability to work.

4. Sequence of events leading to the problem
Error is typically signalled on a COMMIT most likely following a deletion
from the tables.

5. Technical impact on the customer. Include persistent after effects.
Severe, as it occurs multiple times per day, and corrupt the underlying
tables preventing further data loads.

DIAGNOSTIC ANALYSIS:
——————–
The trace files show that the problem occurs following a bulk deletion from
the underlying tables, which appear to corrupt the extent map, as the segment
header dump shows 1 extent of 4 blks, but the deleteion entry in the redo
stream shows one extent of 8 blks, e.g.:

REDO RECORD – Thread:1 RBA: 0x0005da.000e5e34.01c0 LEN: 0x00fc VLD: 0x01
SCN: 0x000d.37eacce9 SUBSCN:  5 07/11/2006 10:29:53
CHANGE #1 TYP:0 CLS:60 AFN:39 DBA:0x09c322e0 OBJ:4294967295
SCN:0x000d.37eacce9 SEQ:  2 OP:5.1
ktudb redo: siz: 112 spc: 15940 flg: 0x0022 seq: 0x011d rec: 0x06
xid:  0x0016.020.000005b6
ktubu redo: slt: 32 rci: 5 opc: 14.5 objn: 2 objd: 93662 tsn: 12
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  Yes
0x00000000
kteopu undo – undo operation on extent map
segdba: 0x87e3cc  class: 4  mapdba:0x87e3cc  offset: 3
rbr extent – dba: 0x0  nbk: 0x0
kteop redo – redo operation on extent map
ADD: dba:0x803673d len:8 at offset:1
DEFAULT: ???
SETSTAT: exts:2 blks:16 lastmap:0x0 mapcnt:0
CHANGE #2 TYP:0 CLS: 4 AFN:2 DBA:0x0087e3cc OBJ:93662 SCN:0x000d.37eacce9
SEQ:  1 OP:14.4
kteop redo – redo operation on extent map
DELETE: entry:1
shift back: dba:0x0 len:0
SETSTAT: exts:1 blks:8 lastmap:0x0 mapcnt:0

WORKAROUND:
———–
None

RELATED BUGS:
————-
Bug 4949123 – ORA-600: [KDDUMMY_BLKCHK], [541], [147050], [18038]

REPRODUCIBILITY:
—————-
Consistently occurring at customers site.

TEST CASE:
———-
n/a

STACK TRACE:
————
ksedst ksedmp ksfdmp kgerinv kseinpre ksesin kco_blkchk kcoapl kcbapl
kcrfw_redo_gen kcbchg1_main kcbchg1 ktuchg ktbchg2nt kteopgen kteopresize
ktsxbmdelext1 ktsstrm_segment ktsmg_icmt_prepare ktcifc ktucmt ktpcmt ktcrcm
ktdcmt k2lcom k2send xctctl xctcom_with_options kksExecuteCommand opiexe
opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal
pevm_EXECC pfrinstr_EXECC pfrrun_no_tool pfrrun plsql_run peicnt kkxexe
opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real
main __libc_start_main _start

SUPPORTING INFORMATION:
———————–
alertlogs and trace files

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-
n/a

DIAL-IN INFORMATION:
——————–
n/a

IMPACT DATE:
————
21-JUL-2006

*** 07/12/06 02:34 am *** (CHG: Asg->NEW OWNER OWNER)

A redo dump of the segment header during the entire procedure execution was
requested on 06 Aug and supplied on 09 Aug so why are you asking for this
information again when you already have it?  Please check that file
(redo_1.trc in bug5386204_07Aug.zip), and let me know if you need anything
else.
*** 09/19/06 02:39 am *** (CHG: Sta->30)
Uploaded the requested information in file bug5386204_Oct02.zip.

*** 11/27/06 11:13 am ***
Here is one theory we (space group) have on this bug so far:
During direct load one of the segments does not get loaded with any data. The
segment is empty and the first extent has 8 blocks (this is 16k block size).
However it goes through the usual high water mark movement phase (even though
the hwm does not move). During the hwm movement phase, the segment is trimmed
close to 64k boundary. For ASSM segment with 16k block size, this means the
segment will be left with no data blocks after the trim- 4 blocks after the
trim would represent bitmaps and segment header only.

There are two issues here:
(1) Why was ktsstrm_segment called on an empty (or unloaded) segment at first
place?
(2) Even if it was called, why is segment trimmed to 64k boundary?

I’m working on the 2nd issue and will give an update soon.
*** 11/29/06 03:18 am *** (CHG: Pri->1)
*** 11/29/06 03:18 am ***
*** 11/29/06 03:25 am *** -> CLOSED
*** 11/29/06 05:31 pm ***
*** 11/30/06 10:36 pm ***
We ran into some issues (bugs) while testing the code for the diagnostic
patch. I was hoping to have it finished by today but it seems it’ll take some
more time and I’m pretty hopeful of having it ready to go by tomorrow evening
(PST). I’m really sorry for the delay.
*** 12/01/06 07:16 pm ***
*** 12/02/06 05:05 pm ***
Sorry for the delay in replying. I would expect the long regressions to be
complete by sunday afternoon PST. I should be ready to release the patch by
sunday evening if things go fine. Will keep this page updated on my progress.
*** 12/03/06 05:30 pm ***
*** 12/04/06 05:35 pm ***
It seems most of major issues with the long regressions have been taken care
of and I hope to get a clean run on the farm soon, by tomorrow end of day and
the patch should be on its way soonafter.

I had a question though, that will help me in getting the patch out faster. I
wanted to know if the customer has had any diagnosibility patches installed
on their 10.2.0.2.0 release version.

Another thing which I would like to mention here is that my patch modifies
only one file (ktss.c) in the RDBMS code.
*** 12/05/06 02:19 am ***
*** 12/05/06 04:42 pm ***
*** 12/05/06 05:06 pm ***
I was hoping to have all the farm regressions (and the patch) done by today
evening but it seems farm is taking a bit long to finish the regressions.
I’ll work on the patch as soon as I have the regressions done. Sorry for the
delay. I’ll provide an update on that in the next few hours.
*** 12/05/06 09:07 pm ***
My regressions are still moving very slowly through the queue on the farm.
The farm seems to be busy with 11g Beta 4 deadline round the corner. My
regressions have been on the farm for more than a day now. I’ll work on the
patch as soon as I have a clean farm run.
*** 12/06/06 06:15 pm ***
Still waiting on the clean farm runs. Fortunately, I’ve been able to get a
high priority on the farm jobs. So, I expect things to run clean soon. Will
keep things updated here.
*** 12/07/06 05:54 pm ***
Got my farm runs completed last night but got a small number of diffs. Have
been trying to isolate them and hopefully soon, everything should be clean.
Farm has been giving those diffs over and over again though those look
unrelated to my change. Currently, verifying them on my linux workstation.
*** 12/08/06 06:01 pm ***
*** 12/08/06 06:23 pm ***
Have been able to run almost all the long regressions locally and things look
clean. There’s just a couple of long regressions which I’m still running and
I should expect to be ready to go as soon as they are completed. Should be
able to start the patch building soon.
*** 12/11/06 01:07 am ***
There’s one long regression which seems to be broken. I’m currently working
on that to have it run clean. Will update as soon as I have it running clean.
*** 12/11/06 01:23 pm ***
Everything is clean now. Working on starting the patch building process.
*** 12/11/06 02:46 pm ***

The customer has confirmed that following application of the suplied patch
the error no longer occurred when running the testcase, which ran through to
completion after about 8 hours.  They are resetting the testcase, and will
run it again to verify this, but the initial response is that this looks to
have resolved the problem.

Can you confirm if the patch would need to be rebuilt as a permananent fix,
ie. any diagnostics to be removed etc. or is it actually the full fix anyway?
*** 12/13/06 07:19 am ***
The customer has confirmed the following:

1. Rerun the test for the 2nd time with patched rdbms: completed quickly and
without any problems.
2. Rollbacked the patch: the test failed as expected within 30 minutes.
3. Re-applied the patch and ran the test once again: completed ok.

This appears to confirm that the patch resolves the problem so could we have
an answer to the previous update?
*** 12/13/06 07:47 pm ***
That is good news.
No additional diagnostics have been added to the patch. So, it’s not needed
to be rebuilt. I guess the supplied patch should be complete in itself.
*** 12/14/06 12:40 am ***
Thanks for the update.

该文档描述当使用直接路径方式导入数据时一定概率导致该Bug产生,譬如使用Sql loader且DIRECT=Y;
该Bug只会由存贮在本地管理方式(LTM)并自动段管理(ASSM)的对象引发， 并且当标准块大小为16k时出现概率较高(Bug 5386204 is mostly hit when db_block_size=16384.)
一般数据库都会启用db_block_checksum,该参数控制Oracle在读入块时做检验操作，[18038]是kddummy_blkchk的一种错误代码，出现该错误代码说明存储在段头中的辅助区间图中的Data dba越界, 我们举一个段头来看:

Start dump data blocks tsn: 4 file#: 4 minblk 139 maxblk 139
buffer tsn: 4 rdba: 0x0100008b (4/139)
scn: 0x0000.000f327e seq: 0x01 flg: 0x04 tail: 0x327e2301
frmt: 0x02 chkval: 0x619e type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
.......
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 9      #blocks: 72
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x0101e1f1  ext#: 8      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 65
  mapblk  0x00000000  offset: 8
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x0101e1f1  ext#: 8      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 65
  mapblk  0x00000000  offset: 8
  Level 1 BMB for High HWM block: 0x0101e1e9
  Level 1 BMB for Low HWM block: 0x0101e1e9
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x0100008a
  Last Level 1 BMB:  0x0101e1e9
  Last Level II BMB:  0x0100008a
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 9    obj#: 51806  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x01000089  length: 8
   0x0101e1a1  length: 8
   0x0101e1a9  length: 8
   0x0101e1b9  length: 8
   0x0101e1c1  length: 8
   0x0101e1c9  length: 8
   0x0101e1d9  length: 8
   0x0101e1e1  length: 8
   0x0101e1e9  length: 8

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x01000089 Data dba:  0x0100008c
   Extent 1     :  L1 dba:  0x01000089 Data dba:  0x0101e1a1
   Extent 2     :  L1 dba:  0x0101e1a9 Data dba:  0x0101e1aa
   Extent 3     :  L1 dba:  0x0101e1a9 Data dba:  0x0101e1b9
   Extent 4     :  L1 dba:  0x0101e1c1 Data dba:  0x0101e1c2
   Extent 5     :  L1 dba:  0x0101e1c1 Data dba:  0x0101e1c9
   Extent 6     :  L1 dba:  0x0101e1d9 Data dba:  0x0101e1da
   Extent 7     :  L1 dba:  0x0101e1d9 Data dba:  0x0101e1e1
   Extent 8     :  L1 dba:  0x0101e1e9 Data dba:  0x0101e1ea
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x0100008a

其中辅助区间图( Auxillary Map)列出了该段每个区间(Extent)的一级位图块以及区间中实际数据开始的data block address (Data dba).譬如Extent 0 中的Data dba应在
(0x0100008A ~0x01000090)之间，否则即越界。
DROP或TRUNCATE是触发该Bug的主要操作，原因是这2个操作都需要使用到Pagetable segment header中的Auxiliary Map。
Oracle建议的WorkAround方式主要是通过MOVE TABLESPACE 来”REBUILD”这个PAGETABLE SEGMENT HEADER。
这个Case中Oracle support给出Workaround建议：

1-. Make sure the below query will return the table mentioned above:

SQL> select owner, object_name, object_type, SUBOBJECT_NAME, OBJECT_ID,
DATA_OBJECT_ID, CREATED,LAST_DDL_TIME,TIMESTAMP
from DBA_OBJECTS
where DATA_OBJECT_ID =1699775;

If so continue:

SQL>alter system set DB_BLOCK_CHECKSUM = OFF;

Find all indexes for W_ORG_DS table.

SQL> select owner, index_name, index_type, table_name , table_owner from dba_indexes
Where table_owner = ‘BMS_OBA_DW’ and
Table_name = ‘W_ORG_DS’;

connect as BMS_OBA_DW

SQL> desc W_ORG_DS

if this table does not have LONG column, then Alter table table_name move is like a CTAS but better since is using the same name of the object plus keeping any related object like index, etc. If it has Long column then export/truncate/import need to be use;

SQL>Alter table W_ORG_DS Move;

Then rebuild all indexes for W_ORG_DS table as per above query: .i.e.

SQL>Alter index rebuild

To avoid problem, please apply patch for bug 5386204, see note 580561.1 for further information.

Oracle文档宣称其已在10.2.0.4的第一个patch set update及10.2.0.5中修复了该Bug.

注：最早认为该Bug在10.2.0.4中就已经修复了，但后来确认“This bug was previously incorrectly listed as fixed in 10.2.0.4”。

Filed Under: Oracle Tagged With: , , , , , , , , , , , ,

EnterpriseDB Replication，复制Oracle数据测试(2)

2010/05/21 by Leave a Comment

介绍完了EnterpriseDB复制软件基本原理和注意事项，我们接下来进行Oracle数据复制到EntepriseDB advanced Server的实际演练。

先在Oracle实例中创建复制测试所用到的数据：

SQL> drop user  source cascade;

User dropped.

SQL> create user source identified by source;

User created.

SQL> grant dba to source;

Grant succeeded.

SQL> grant create any trigger to source;

Grant succeeded.

SQL> conn source/source
Connected.
SQL> create table EMP
2  (
3    EMPNO    NUMBER(4) not null,
4    ENAME    VARCHAR2(10),
5    JOB      VARCHAR2(9),
6    MGR      NUMBER(4),
7    HIREDATE DATE,
8    SAL      NUMBER(7,2),
9    COMM     NUMBER(7,2),
10    DEPTNO   NUMBER(2)
11  )
12  tablespace USERS;

Table created.

SQL> alter table EMP
2    add constraint pk_empno primary key (EMPNO);

Table altered.

SQL> create table DEPT
2  (
DEPTNO NUMBER(2) not null,
DNAME  VARCHAR2(14),
LOC    VARCHAR2(13)
)
3    4    5    6    7  tablespace USERS;

Table created.

SQL> alter table DEPT
2    add constraint PK_DEPT primary key (DEPTNO);

Table altered.

SQL> alter table EMP
2    add constraint fk_deptno foreign key (DEPTNO)
3    references dept (DEPTNO);

Table altered.

SQL>

同时创建EnterpriseDB Advanced Server中的目标数据库及用户：

edb=#  create user subuser password 'subuser';
ERROR:  role "subuser" already exists
edb=# alter user subuser with Superuser;
ALTER ROLE
edb=# create database subuser tablespace users;
CREATE DATABASE

EnterpriseDB复制服务需要DBA Management Server服务的相关支持，其运作方式如下图：

我们首先需要注册管理服务器，其默认端口为9000,为确保主机上的管理服务已打开可以运行以下命令：

[enterprisedb@rh2 ~]$ source edb_83.env
[enterprisedb@rh2 ~]$ cd $EDBHOME
[enterprisedb@rh2 edba]$ cd mgmtsvr/bin
[enterprisedb@rh2 bin]$ ls
attachments            DBA_Management_Server.pid  jboss_init_redhat.sh  mgmtsvr.000  run.conf  shutdown.jar  twiddle.sh    wrapper.log   wsrunclient.sh
BrowserLauncher.class  deployer.sh                jboss_init_suse.sh    mgmtsvr.sh   run.jar   shutdown.sh   wrapper_83    wsconsume.sh  wstools.sh
classpath.sh           jboss_init_hpux.sh         kill_wrapper.sh       probe.sh     run.sh    twiddle.jar   wrapper.conf  wsprovide.sh
[enterprisedb@rh2 bin]$ ./mgmtsvr.sh  status
DBA Management Server is running (31198).           --目前服务已打开
[enterprisedb@rh2 bin]$ ./mgmtsvr.sh  stop
Stopping DBA Management Server...
Stopped DBA Management Server.
[enterprisedb@rh2 bin]$ ./mgmtsvr.sh  start           -- 若未打开，则start
Starting DBA Management Server...

接着我们可以从桌面上的application栏打开replication console,并选择注册管理服务(register management Server):

成功注册服务后，我们需要分别在发布服务和订阅服务中配置JVM option,右键点击Publication service选择Advanced JVM options,在该窗口内Insert一条记录,如图：

其内容为-Djava.rmi.server.hostname=$IP, 其中$IP为已注册的DBA Management Server所监听的IP地址。配置完成后分别启动发布与订阅服务。

针对订阅服务也需要进行以上配置，JVM options也添加的条目为-Djava.rmi.server.hostname=$IP。
开始创建发布服务Oracle数据源：

并创建相关的发布服务：

接着创建订阅服务数据库：

创建具体的订阅服务：

上述配置均成功完成后，源端的数据定义默认已复制到订阅端，我们来验证一下：

[enterprisedb@rh2 ~]$ psql subuser subuser
Password for user subuser:
Welcome to psql 8.3.0.112, the EnterpriseDB interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with edb-psql commands
       \g or terminate with semicolon to execute query
       \q to quit

subuser=# desc source.emp;
                 Table "source.emp"
  Column  |            Type             | Modifiers
----------+-----------------------------+-----------
 empno    | numeric(4,0)                | not null
 ename    | character varying(10)       |
 job      | character varying(9)        |
 mgr      | numeric(4,0)                |
 hiredate | timestamp without time zone |
 sal      | numeric(7,2)                |
 comm     | numeric(7,2)                |
 deptno   | numeric(2,0)                |
Indexes:
    "pk_empno" PRIMARY KEY, btree (empno)

subuser=# desc source.dept;
            Table "source.dept"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 deptno | numeric(2,0)          | not null
 dname  | character varying(14) |
 loc    | character varying(13) |
Indexes:
    "pk_dept" PRIMARY KEY, btree (deptno)

接下来尝试在源端Oracle数据库中产生一定量的数据，并通过快照方式复制到订阅端：

SQL> insert into dept select * from scott.dept;

4 rows created.

SQL> commit;

Commit complete.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> begin
  2  for i in 1..9999 loop
  3  insert into emp values(i,'Maclean','DBA',1,sysdate-365,8888,50,10);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

  COUNT(*)
----------
      9999

在点中订阅服务subuser中选择功能栏中的snapshot复制方式：

其复制过程中产生的日志：

Source database connectivity info…
conn =jdbc:oracle:thin:@192.168.0.115:1521:g10r21
user =source
password=******
Target database connectivity info…
conn =jdbc:edb://192.168.0.115:5444/subuser
user =subuser
password=******
Importing redwood schema SOURCE…
Table List: ‘DEPT’,’EMP’
Loading Table Data in 8 MB batches…
Disabling FK constraints & triggers on source.dept before truncate…
Truncating table DEPT before data load…
Disabling indexes on source.dept before data load…
Loading Table: DEPT …
Migrated 4 rows.
Enabling FK constraints & triggers on source.dept…
Enabling indexes on source.dept after data load…
Creating Constraint: PK_DEPT
Table Data Load Summary: Total Time(s): 1.261 Total Rows: 4
Disabling FK constraints & triggers on source.emp before truncate…
Truncating table EMP before data load…
Disabling indexes on source.emp before data load…
Loading Table: EMP …
Migrated 9999 rows.
Enabling FK constraints & triggers on source.emp…
Enabling indexes on source.emp after data load…
Creating Constraint: PK_EMPNO
Table Data Load Summary: Total Time(s): 3.782 Total Rows: 9999 Total Size(MB): 0.494140625
Performing ANALYZE on EnterpriseDB database…
Data Load Summary: Total Time (sec): 5.043 Total Rows: 10003 Total Size(MB): 0.506

Schema SOURCE imported successfully.

Migration process completed successfully.

Migration logs have been saved to /s01/edba/mgmtsvr/server/default/deploy/edb-rrep-ws.war/WEB-INF/logs

******************** Migration Summary ********************
Tables: 2 out of 2
Constraints: 2 out of 2

Total objects: 4
Successful count: 4
Failure count: 0

*************************************************************

可以看到快照成功复制了我们需要的数据，现在我们来尝试使用同步模式(synchronize mode) ,我们先来定义一个持续性的间隔为5s的 Scheduled Task,选中订阅服务并点击功能栏中的Configure Schedule,选择Synchronize和Continuously,间隔时间选择为10s:

我们在源端Oracle数据库中修改员工工资，并观察订阅端EDB数据库中的情况：

-- source database
20:08:51 SQL> select sum(sal) from emp;

  SUM(SAL)
----------
  88871112

20:09:09 SQL> update emp set sal=sal*1.1 ;

9999 rows updated.

20:09:34 SQL> commit;

Commit complete.

20:09:36 SQL> select sum(sal) from emp;

  SUM(SAL)
----------
97758223.2
-- EntepriseDB端
subuser=# select sum(sal) from source.emp;
     sum
-------------
 97758223.20
(1 row)

好了，以上我们利用EnterpriseDB Replication软件完成了一个由Oracle数据库到EDB advanced server间最简单的数据复制服务的配置。

Filed Under: postgresql Tagged With: , , , , ,

EnterpriseDB Replication，复制Oracle数据测试(1)

2010/05/21 by Leave a Comment

EntepriseDB 复制软件目前支持多种数据库到postgre的复制，其基本结构由发布者(Publication)与订阅者(Subscriptions)组成,Replication软件可针对来自不同类型数据库的多个发布者，将其数据复制到多个订阅者(Subscriptions)数据库中。
其可能的几种拓扑结构，如以下图：



同Oracle中普通的物化视图一样，不支持对订阅者(Subscriptions)数据的修改–Changes must not be made to the data or the definitions of the subscription tables.

EnterpriseDB Replication软件的具体工作模式分成2种：即快照模式(snapshot)与同步模式(synchronization)；在第一次启用同步前，需要进行一次快照操作,之后便可以进行较为轻量级同步操作了。若要使用同步模式(synchronization)则要求发布者所包含的表必须具有主键,而在仅使用快照模式的情景中则不需要。(Each table used in a publication must have a primary key with the exception of tables in snapshot-only publications, which do not require a primary key.) 以上模式均支持过滤器(fliter),即可以指定需要复制的具体数据子集。

EnterpriseDB Replication软件其同步(synchronization)模式复制的基本原理是基于trigger的，而非如Quest公司的shareplex或golden gate般抽取重做日志生成SQL的方式。trigger方式会在数据库源端产生一定的性能影响，若在mission critical的生产数据库中实施EDB replication 复制则需要考虑到这一点(这种情况下推荐使用Snapshot模式)。这可能是EDB复制软件比较不成熟的一点，就目前仅对Oracle日志文件的研究认识，挖掘重做日志进而实现数据复制的途径已经没有技术上的难点了。

以下发布者所包含的数据对象或表属性，将在订阅者成功建立时被复制到订阅者所在的数据库：

  • Tables
  • Views (for snapshot-only publications) – created as a table in the subscription database
  • Primary keys
  • Not null constraints
  • Unique constraints
  • Check constraints
  • Indexes

注意：外键约束将不被复制

同时目前复制软件存在一定的限制,Oracle中的hash分区将不被复制，同时Oracle中包含以下数据类型列的表将无法复制：

  • BFILE
  • BINARY_DOUBLE
  • BINARY_FLOAT
  • MLSLABEL
  • XMLTYPE

Oracle中包含以下数据类型列的表，将不能使用同步模式(synchronization replications):

  • BLOB
  • CLOB
  • LONG
  • LONG RAW
  • NCLOB
  • RAW

快照模式情况下，订阅者中复制目标表将首先被truncate截断，之后若订阅者数据库类型是Oracle则将使用JDBC驱动批量的将源端的数据INSERT进来，若数据库类型是EnterpriseDB advanced Sever则将使用Postgre中的Copy命令。

同步模式下复制软件通过在源端配置的触发器记录表，获知源端该时段内所经历的DML操作，进而在目标端生成对应修改的SQL语句(显然同源端的原始SQL不同)。

EnterpriseDB公司推荐在以下情景中使用快照模式以获得更好的性能：

  1. 表相对而言较小
  2. 在复制间隔中绝大多数数据行会被修改

而同步模式则更适宜于以下情景：

  1. 数据表非常巨大
  2. 在复制间隔中仅少数数据会被修改
Filed Under: postgresql Tagged With: , , , ,

EnterpriseDB Migration 迁移工具使用测试(2)

2010/05/18 by Leave a Comment

下面我们来测试EnterpriseDB Migration 工具对于Oracle 大对象(LOB)的迁移情况；
首先在在Oracle实例Scott模式下创建具有LOB对象的表,如：

SQL> create table tlob (t1 int primary key,t2 clob,t3 blob);
Table created.
-- 并填充数据
SQL> begin
  2  for i in 1..100 loop
  3  insert into tlob values(i,rpad('A',9999,'Z'),hextoraw(i) );
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

打开EnterpriseDB Migration 工具界面，从树形图中找到需要迁移的表TLOB,选择进行在线迁移：

导出日志：

[Starting Migration]
源数据库连接信息…
连接 =jdbc:oracle:thin:@rh2.home:1521:G10R21
用户 =system
密码=******
目标数据库连接信息…
连接 =jdbc:edb://rh2.home:5444/subuser
用户 =maclean
密码=******
正在导入 Redwood 架构 SCOTT…
表列表: ‘TLOB’
正在创建表…
正在创建表: TLOB
已创建 1 个表。
正在以 8 MB 批次大小加载表数据…
正在将大型对象加载到表: TLOB…
表数据加载摘要: 时间总计 (秒): 1.122 行数总计: 100 大小总计 (MB): 0.380859375
数据加载摘要: 时间总计 (秒): 1.122 行数总计: 100 大小总计 (MB): 0.39
正在创建约束: SYS_C005182

已成功导入架构 SCOTT。

迁移过程已成功完成。

迁移日志已保存到 C:\Users\windesk\.enterprisedb\migrationstudio\build60

******************** 迁移摘要 ********************
Tables: 1 来自 1
Constraints: 1 来自 1

全部对象: 2
成功计数: 2
失败计数: 0

*************************************************************
—————-FINISHED———

下面我们到EnterpriseDB中去验证导入数据：

[enterprisedb@rh2 ~]$ psql
Password:
Welcome to psql 8.3.0.112, the EnterpriseDB interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with edb-psql commands
       \g or terminate with semicolon to execute query
       \q to quit

edb=# \c subuser
You are now connected to database "subuser".
subuser=# desc scott.tlob;
      Table "scott.tlob"
 Column |  Type   | Modifiers
--------+---------+-----------
 t1     | numeric | not null
 t2     | text    |
 t3     | bytea   |
Indexes:
    "sys_c005182" PRIMARY KEY, btree (t1)

subuser=# select count(*) from scott.tlob;
 count
-------
   100

(1 row)

可以看到装换过程中将clob类型转制为text,而blob类型则转制为bytea。postgre中text类型为可变无限长文本类型(variable unlimited length)。

正想去EnterpriseDB网站去查一下官方定义，却发现了以下留言：

We are in the process of updating our website. The site will not be available for the next few minutes. Sorry for the inconvenience.
The EnterpriseDB Web team.

另外bytea类型为一种变长的二进制字串,postgre组织的文档对这2种类型的存储数据上限没有非常明确的叙述，就目前找到的文献可以肯定的是postgre V7中这两种类型大小限制为1G

那么如果Oracle 中Blob/Clob类型大小超过了1G,就可能导致迁移无法正常进行。

Filed Under: postgresql Tagged With: , , , , ,

EnterpriseDB Migration 迁移工具使用测试(1)

2010/05/17 by Leave a Comment

EntperpriseDB 目前作为Postgre开源数据库的企业发行版，在原开源社区的基础上对postgre进行了扩展(contribute),值得关注的技术有infiniteCache,以及其强大的迁移工具Migration tools;下面我们来简单测试该迁移工具.

通过安装postgreplus-advanced-server软件包，其中将默认包括DBA management Server,DBA monitor Console,Migrate Studio,Replication Tools等一系列管理工具。我们需要用到的是Migrate Studio.

打开Migrate Studio首先定义迁移目标端的Enterprisedb实例，事先已经在主机rh2.home上安装了EnterpriseDB 8.3R2版本，同时创建了名为maclean的数据库(database),使用默认端口5444，新建EDB服务器：

该工具同大多数Oracle管理工具一样使用java驱动,但速度较快显得十分轻量级。

接着我们需要创建源端的Oracle服务器，同样在远程主机rh2.home上建立了Version 10.2.0.1的Oracle EE版数据库实例名为g10r21,Listener监听在端口1521上，尝试创建该服务器时将被要求Oracle JDBC包，该包可以到oracle官方网站下载，之后将该包放置到$EDBHOME/jre/lib/ext目录下;新建Oracle服务器:

之后我们可以尝试将Oracle实例中Scott模式下的对象迁移到enterprisedb中,在此之前我们在该模式下建立简单的存储过程，协同测试。

SQL> create or replace procedure scott.count_emp as
2  c int;
3  begin
4  select count(*) into c from scott.emp;
5  dbms_output.put_line('emp count is '||c);
6  end;
7  /

Procedure created.

SQL> set serveroutput on ;
SQL> exec scott.count_emp;
emp count is 14

PL/SQL procedure successfully completed.

接下来开始进行正式迁移，打开源库Oracle实例的树形图，并找到Scott模式，右键进入在线迁移模式, 目标端服务器为target(rh2.home:5444),指定Maclean数据库，架构(模式)仍为Scott,其他均使用默认设置，点击运行开始迁移:

迁移日志如下：

[Starting Migration]

源数据库连接信息...

连接 =jdbc:oracle:thin:@rh2.home:1521:g10r21

用户 =system

密码=******

目标数据库连接信息...

连接 =jdbc:edb://rh2.home:5444/maclean

用户 =maclean

密码=******

正在导入 Redwood 架构 SCOTT...

正在创建架构...scott

正在创建表...

正在创建表: BONUS

正在创建表: DEPT

正在创建表: EMP

正在创建表: SALGRADE

已创建 4 个表。

正在以 8 MB 批次大小加载表数据...

正在加载表: BONUS ...

表数据加载摘要: 时间总计 (秒): 0.128 行数总计: 0

正在加载表: DEPT ...

已迁移 4 行。

表数据加载摘要: 时间总计 (秒): 0.118 行数总计: 4

正在加载表: EMP ...

已迁移 14 行。

表数据加载摘要: 时间总计 (秒): 0.121 行数总计: 14

正在加载表: SALGRADE ...

已迁移 5 行。

表数据加载摘要: 时间总计 (秒): 0.145 行数总计: 5

数据加载摘要: 时间总计 (秒): 0.512 行数总计: 23 大小总计 (MB): 0.0

正在创建约束: PK_DEPT

正在创建约束: PK_EMP

正在创建约束: FK_DEPTNO

正在创建过程: COUNT_EMP

已成功导入架构 SCOTT。

正在创建用户: SCOTT

迁移过程已成功完成。

迁移日志已保存到 C:\Users\windesk\.enterprisedb\migrationstudio\build60

******************** 迁移摘要 ********************

Tables: 4 来自 4

Constraints: 3 来自 3

Procedures: 1 来自 1

Users: 1 来自 1

全部对象: 9

成功计数: 9

失败计数: 0

*************************************************************

----------------FINISHED---------

迁移过程中没有出现错误，下面我们来测试下之前建立的存储过程:

maclean=# exec count_emp;
emp count is 14

EDB-SPL Procedure successfully complete
Filed Under: postgresql Tagged With: , , , ,

windows平台上的11g release 2终于发布了

2010/04/08 by Leave a Comment

下午无意中打开了oracle主页上11g下载的页面，赫然发现windows平台的安装介质已经发布了。

介质分成2个zip包，1.5g和600m; 11g的安装介质较10g大了许多，因为默认附加了apex与sql developer.

下载地址：

Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)
Download win32_11gR2_database_1of2.zip (1,625,721,289 bytes)
Download win32_11gR2_database_2of2.zip (631,934,821 bytes)
Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (x64)
Download win64_11gR2_database_1of2.zip (1,213,501,989 bytes) (cksum – 3906682109)
Download win64_11gR2_database_2of2.zip (1,007,988,954 bytes) (cksum – 1232608515)

目前32位未提供grid infrastructure 介质，想要体验windows上的11g rac只能使用64bit .

Oracle Database 11g Release 2 Grid Infrastructure (11.2.0.1.0) for Microsoft Windows (x64)

Download win64_11gR2_grid.zip (715,166,425 bytes) (cksum – 3127109177)

根据metalink文档867040.1所述,Oracle database 11g release 2 将默认支持windows 7 以及 windows 2008 r2 .

单机的安装过程十分简单：

一般来说windows 发行版在正式生产环境中不多见，不过安装到笔记本上方便了今后对11gr2新特性的测试。

附release note:

Oracle Database 11g Release 2 Now Available on Windows 32 and 64-bit [ID 1081390.1]
Modified 07-APR-2010     Type ANNOUNCEMENT     Status PUBLISHED

In this Document
What is being announced?
References

Applies to:

Oracle Server – Standard Edition – Version: 11.2.0.1 – Release: 11.2
Oracle Server – Personal Edition – Version: 11.2.0.1 – Release: 11.2
Oracle Server – Enterprise Edition – Version: 11.2.0.1 – Release: 11.2
Microsoft Windows (32-bit)
Microsoft Windows x64 (64-bit)
Microsoft Windows x64 (64-bit) – OS Version: 7
Microsoft Windows (32-bit) – OS Version: 7
Microsoft Windows x64 (64-bit) – Version: 2008 R2

What is being announced?

Oracle Database 11g Release 2 for Windows x86 (32-bit) and x86-64 is now available for download from OTN or the Oracle Store.

New with 11.2 is certification with Windows 2008 R2 and Windows 7.   You can find out more in Note  1065024.1 “Oracle Database 11g Release 2 Certification Highlights”.

Filed Under: Oracle Tagged With: , , , , , , ,

explore my oracle support using firefox 3.6

2010/04/06 by 2 Comments

升级到FF3.6的朋友肯定发现了现在使用火狐浏览器访问my oracle support 时许多页面打开为空白页，譬如这篇介绍FF3.6与目前oracle support site兼容性的文章：

Firefox 3.6 Not Officially Supported by My Oracle Support

Although much of the functionality of My Oracle Support is accessible via Firefox 3.6, please be aware that Firefox 3.6 is not officially supported by My Oracle Support at this time. Please review the My Oracle Support FAQ (Knowledge Document #747252.5) for details on the current browser requirements for accessing My Oracle Support.

We are aware of a current issue when viewing Knowledge documents using Firefox 3.6. Using Firefox 3.6, when clicking on a link for a Knowledge document the document appears blank.  The workaround is to instead open the documents in a new tab or new window.

We are working on a fix for this issue and hope to have this resolved in the near future. We apologize for any inconvenience.

FF升级到3.6的时间已经超过一个月了；oracle support 之前曾发表将解决该兼容问题的声明，原本以为这只是一个小case,因该在数周内彻底解决。但目前又发布了暂时不官方支持的声明(大部分文档可以通过在新窗口内打开方式阅读)。

看起来大型网站的建设维护确实不是Oracle的特长，apex或者说htmldb又真的适合超大型web site吗？

Filed Under: Oracle Tagged With: , , , , ,

What’s preconnect.svc in 11g RAC?

2010/03/30 by Leave a Comment

有网友反映在11.2的RAC中有一个名如*_preconnect.svc的资源一直处于OFFLINE状态，而其TARGET则为ONLINE状态，无法通过重启CRS或者手动start该资源来使之ONLINE。我们来具体看一下什么情况下回产生这种资源?:

[oracle@rh2 ~]$ srvctl add service -h

Adds a service configuration to the Oracle Clusterware.

Usage: srvctl add service -d  -s  {-r "" 
[-a ""] [-P {BASIC | NONE | PRECONNECT}] | -g  
[-c {UNIFORM | SINGLETON}] } [-k   ] [-l [PRIMARY][,PHYSICAL_STANDBY]
[,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-y {AUTOMATIC | MANUAL}] [-q {TRUE|FALSE}] 
[-x {TRUE|FALSE}] [-j {SHORT|LONG}] [-B {NONE|SERVICE_TIME|THROUGHPUT}] 
[-e {NONE|SESSION|SELECT}] [-m {NONE|BASIC}] [-z ] 
[-w ] [-t ] [-f]
    -d       Unique name for the database
    -s              Service name
    -r ""    Comma separated list of preferred instances
    -a ""    Comma separated list of available instances
    -g            Server pool name
    -c {UNIFORM | SINGLETON} Service runs on every active server in the 
server pool hosting this service (UNIFORM) or just one server (SINGLETON)
    -k              network number (default number is 1)
    -P {NONE | BASIC | PRECONNECT}        TAF policy specification
    -l                 Role of the service (primary, physical_standby, logical_standby, snapshot_standby)
    -y               Management policy for the service (AUTOMATIC or MANUAL)
    -e        Failover type (NONE, SESSION, or SELECT)
    -m      Failover method (NONE or BASIC)
    -w              Failover delay
    -z              Failover retries
    -t              Edition (or "" for empty edition value)
    -j   Connection Load Balancing Goal (SHORT or LONG). Default is LONG.
    -B      Runtime Load Balancing Goal (SERVICE_TIME, THROUGHPUT, or NONE)
    -x   Distributed Transaction Processing (TRUE or FALSE)
    -q  AQ HA notifications (TRUE or FALSE)
Usage: srvctl add service -d  -s  -u {-r "" | -a ""} [-f]
    -d       Unique name for the database
    -s              Service name
    -u                       Add a new instance to service configuration
    -r        Name of new preferred instance
    -a       Name of new available instance
    -f                       Force the add operation even though a listener is not configured for a network
    -h                       Print usage

[oracle@rh2 ~]$ srvctl add service -d PROD -s maclean -r "PROD1,PROD2" -P BASIC

[oracle@rh2 ~]$ crs_stat|grep maclean
NAME=ora.prod.maclean.svc
NAME=ora.prod.maclean_taf.svc

[oracle@rh2 ~]$ srvctl add service -d PROD -s maclean_pre -r "PROD1,PROD2" -P PRECONNECT
[oracle@rh2 ~]$ crs_stat|grep maclean_pre            
NAME=ora.prod.maclean_pre.svc
NAME=ora.prod.maclean_pre_preconnect.svc

/* 可以看到仅当创建的service使用preconnect的TAF policy时会附带创建{service_name}_preconnect.svc的服务 */

[oracle@rh2 ~]$ srvctl start service -d PROD -s maclean    
[oracle@rh2 ~]$ srvctl start service -d PROD -s maclean_pre

[oracle@rh2 ~]$ crs_stat |grep -A3 maclean
NAME=ora.prod.maclean.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on rh2
--
NAME=ora.prod.maclean_pre.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on rh2
--
NAME=ora.prod.maclean_pre_preconnect.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=OFFLINE
--
NAME=ora.prod.maclean_taf.svc
TYPE=ora.service.type
TARGET=OFFLINE
STATE=OFFLINE

[oracle@rh2 ~]$ srvctl start service -d prod -s maclean_pre_preconnect
PRCD-1084 : Failed to start service maclean_pre_preconnect
PRCR-1079 : Failed to start resource ora.prod.maclean_pre_preconnect.svc
CRS-2674: Start of 'ora.prod.maclean_pre_preconnect.svc' on 'rh2' failed
CRS-2674: Start of 'ora.prod.maclean_pre_preconnect.svc' on 'rh3' failed
CRS-2632: There are no more servers to try to place resource 
'ora.prod.maclean_pre_preconnect.svc' on that would satisfy its placement policy

/* 无法手动启动该preconnect.svc服务 */

[oracle@rh2 ~]$ crs_stat |grep preconnect
NAME=ora.prod.maclean_pre_preconnect.svc

[oracle@rh2 ~]$ srvctl remove service -d PROD -s maclean_pre_preconnect
PRCD-1107 : Removed service maclean_pre_preconnect but failed to remove its 
underlying server pool PROD_maclean_pre
PRCS-1012 : Failed to remove server pool PROD_maclean_pre
PRCR-1072 : Failed to unregister server pool ora.PROD_maclean_pre
CRS-2554: Server pool 'ora.PROD_maclean_pre' cannot be unregistered as 
it is referenced by resource 'ora.prod.maclean_pre.svc'

[oracle@rh2 ~]$ crs_stat |grep preconnec

/* 以上remove命令报错但该preconnect.svc服务还是被删除了，虽然我们并不推荐这样做 */

[oracle@rh2 admin]$ srvctl start service -d PROD -s maclean_pre

[oracle@rh2 admin]$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 30-MAR-2011 18:55:43

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "PROD" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "PRODXDB" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=rh2)(PORT=36196))
Service "maclean" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "maclean_pre" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
                                                                                                                           
[oracle@rh2 admin]$ tnsping PROD_TAF

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 30-MAR-2011 18:54:38

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = rh-cluster-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) 
(SERVICE_NAME = maclean_pre)))
OK (10 msec)


[oracle@rh2 admin]$ sqlplus  maclean/maclean@PROD_TAF

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
PROD2
rh3.oracle.com

shutdown PROD2 instance:SQL> shutdown abort

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
PROD1
rh2.oracle.com


/* 可以看到即使删除preconnect.svc服务也不会影响到TAF的正常使用，
    在生产环境中无需刻意去调试该服务，最好的应对方式是放任不管
*/

所以如果某天你看到你的crs_stat -t输出里有一条莫名的类似”ora….ect.svc ora….ce.type ONLINE OFFLINE”的记录，那么大可以忽略该资源的OFFLINE状态；这是正常现象，用不着紧张！

Filed Under: Oracle, Oracle 11g新特性, Oracle RAC Tagged With: , ,

隐藏参数_high_priority_processes与oradism

2010/03/28 by 5 Comments

运行在操作系统上的进程存在2种系统时序优先级模式:即 实时模式 Real Time(RT) mode, 与分时模式 Time Sharing(TS) mode.
绝大多数Oracle进程运行在TS模式下：

[oracle@rh1 ~]$ ps -efc|grep ora_|grep -v grep
oracle    8510     1 TS   23 Mar27 ?        00:00:02 ora_pmon_PROD
oracle    8512     1 TS   23 Mar27 ?        00:00:00 ora_psp0_PROD
oracle    8514     1 TS   23 Mar27 ?        00:00:00 ora_mman_PROD
oracle    8516     1 TS   23 Mar27 ?        00:00:02 ora_dbw0_PROD
oracle    8518     1 TS   23 Mar27 ?        00:00:04 ora_lgwr_PROD
oracle    8520     1 TS   23 Mar27 ?        00:00:04 ora_ckpt_PROD
oracle    8522     1 TS   23 Mar27 ?        00:00:08 ora_smon_PROD
oracle    8524     1 TS   23 Mar27 ?        00:00:00 ora_reco_PROD
oracle    8526     1 TS   23 Mar27 ?        00:00:34 ora_cjq0_PROD
oracle    8528     1 TS   23 Mar27 ?        00:00:06 ora_mmon_PROD
oracle    8530     1 TS   24 Mar27 ?        00:00:07 ora_mmnl_PROD
oracle    8538     1 TS   23 Mar27 ?        00:00:00 ora_arc0_PROD
oracle    8540     1 TS   23 Mar27 ?        00:00:00 ora_arc1_PROD
oracle    8548     1 TS   23 Mar27 ?        00:00:00 ora_qmnc_PROD
oracle    8555     1 TS   23 Mar27 ?        00:00:00 ora_q000_PROD
oracle    8559     1 TS   23 Mar27 ?        00:00:00 ora_q001_PROD
oracle   30500     1 TS   23 22:10 ?        00:00:00 ora_j000_PROD

如上所示所有进程均运行在TS模式下且priority均为23|24.
Oracle一般不推荐使用RT模式，因为虽然个别进程可以通过这种方式获得更多的CPU资源，但往往系统的瓶颈并非CPU,即尽管CPU使用率高了，但实际系统TPS并未得到提升。
在10gr2版本后RAC中的LMS进程成为唯一一个使用RT模式的Oracle进程，我们可以通过查询参数_high_priority_processes了解相关信息：

SQL> col name format a40
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%priority%';

NAME                                     VALUE
---------------------------------------- ----------
_high_priority_processes                 LMS*
_os_sched_high_priority                  1

_high_priority_processes通过进程功能名进行匹配，下面我们将提高LGWR及PMON进程的优先级：

SQL> alter system set "_high_priority_processes"='LMS*|LGWR|PMON' scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2083336 bytes
Variable Size             150996472 bytes
Database Buffers          121634816 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.
SQL> !ps -efc|grep ora_|grep -v grep
oracle   31441     1 RR   41 22:50 ?        00:00:00 ora_pmon_PROD
oracle   31445     1 TS   23 22:50 ?        00:00:00 ora_psp0_PROD
oracle   31447     1 TS   23 22:50 ?        00:00:00 ora_mman_PROD
oracle   31449     1 TS   23 22:50 ?        00:00:00 ora_dbw0_PROD
oracle   31451     1 RR   41 22:50 ?        00:00:00 ora_lgwr_PROD
oracle   31455     1 TS   23 22:50 ?        00:00:00 ora_ckpt_PROD
oracle   31457     1 TS   23 22:50 ?        00:00:00 ora_smon_PROD
oracle   31459     1 TS   22 22:50 ?        00:00:00 ora_reco_PROD
oracle   31461     1 TS   23 22:50 ?        00:00:01 ora_cjq0_PROD
oracle   31463     1 TS   23 22:50 ?        00:00:01 ora_mmon_PROD
oracle   31465     1 TS   24 22:50 ?        00:00:00 ora_mmnl_PROD
oracle   31471     1 TS   24 22:50 ?        00:00:00 ora_p000_PROD
oracle   31473     1 TS   24 22:50 ?        00:00:00 ora_p001_PROD
oracle   31475     1 TS   24 22:50 ?        00:00:00 ora_arc0_PROD
oracle   31477     1 TS   22 22:50 ?        00:00:00 ora_arc1_PROD
oracle   31481     1 TS   23 22:50 ?        00:00:00 ora_qmnc_PROD
oracle   31488     1 TS   23 22:50 ?        00:00:00 ora_q000_PROD
oracle   31490     1 TS   23 22:50 ?        00:00:00 ora_q001_PROD
oracle   31500     1 TS   23 22:50 ?        00:00:00 ora_j000_PROD

好了lgwr和pmon进程也进入实时模式了，同时priority值上升到了41.
注意：
Oracle默认仅允许LMS进程（11g中多了VKTM进程）使用RT模式是有它的原因的，所以如果不是Oracle support 推荐，您没有任何修改隐式参数的理由。
其次根据Oracle文档[ID 602419.1]的描述，oradism文件(该文件位于$ORACLE_HOME/bin目录下）不正确的权限将导致RT模式无法被正确使用，该文件默认属于root用户并具有s权限。如下测试：

[oracle@rh1 bin]$ ls -la oradism
-r-sr-s---  1 root oinstall 14931 Mar 11  2008 oradism
[oracle@rh1 bin]$ su - root
Password:
[root@rh1 ~]# chown oracle:oinstall /s01/oracle/product/10.2.0/db_1/bin/oradism
[root@rh1 ~]# exit
logout
[oracle@rh1 bin]$ ls -la oradism
-r-xr-x---  1 oracle oinstall 14931 Mar 11  2008 oradism
[oracle@rh1 bin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Mar 28 23:07:03 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  2083336 bytes
Variable Size             150996472 bytes
Database Buffers          121634816 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.
SQL> col name format a35;
SQL> col value format a10;
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%priority%';

NAME                                VALUE
----------------------------------- ----------
_high_priority_processes            LMS*|LGWR|PMON
_os_sched_high_priority             1
SQL> !ps -efc|grep ora_|grep -v grep
oracle   31994     1 TS   23 23:07 ?        00:00:00 ora_pmon_PROD
oracle   31998     1 TS   23 23:07 ?        00:00:00 ora_psp0_PROD
oracle   32000     1 TS   23 23:07 ?        00:00:00 ora_mman_PROD
oracle   32002     1 TS   23 23:07 ?        00:00:00 ora_dbw0_PROD
oracle   32004     1 TS   24 23:07 ?        00:00:00 ora_lgwr_PROD
oracle   32008     1 TS   22 23:07 ?        00:00:00 ora_ckpt_PROD
oracle   32010     1 TS   23 23:07 ?        00:00:00 ora_smon_PROD
oracle   32012     1 TS   22 23:07 ?        00:00:00 ora_reco_PROD
oracle   32014     1 TS   23 23:07 ?        00:00:01 ora_cjq0_PROD
oracle   32016     1 TS   23 23:07 ?        00:00:01 ora_mmon_PROD
oracle   32018     1 TS   24 23:07 ?        00:00:00 ora_mmnl_PROD
oracle   32026     1 TS   24 23:07 ?        00:00:00 ora_arc0_PROD
oracle   32028     1 TS   23 23:07 ?        00:00:00 ora_arc1_PROD
oracle   32032     1 TS   23 23:07 ?        00:00:00 ora_qmnc_PROD
oracle   32045     1 TS   23 23:07 ?        00:00:00 ora_q000_PROD
oracle   32065     1 TS   23 23:08 ?        00:00:00 ora_q001_PROD
oracle   32072     1 TS   23 23:08 ?        00:00:00 ora_j000_PROD

that’s great, 显然oradism不仅为Oracle实例提供了内存资源控制功能，还包括了进程优先级分配的权限。
我们应当再次声明hidden parameter不应“滥用”于production environment.

Filed Under: Oracle Tagged With: , , , , , ,

发一个jd:System Engineer 博君一笑

2010/03/26 by 1 Comment 
   1.  Goal and mission:

A System Engineer has excellent and in-depth knowledge of a family of products (e.g. NagraVision CAS, iDTV & SMS, MediaGuard CAS). He is the recognised expert on that range of systems and is able to analyse and resolve complex problems under severe pressure. He recognises the importance of excellent customer relationships and is a good team player. He is highly motivated to develop his skills.

   2. Responsibility:

    * Is assigned to one or more accounts. Is responsible for the technical solution (installation, customer support, maintenance, etc) and works colsely with the program manager in charge of the account.
    * Creatively develops workaround solutions using broad knowledge of NagraVision products when no other solution is viable
    * Maintains a can-do attitude
    * Is fascinated by new technology and products
    * Handles most crisis situations confidently, is aware of customer consequences and knows when a call should be escalated
    * Recognises possible product design faults or potential enhancements and readily communicates this feedback to senior System Engineers for validation
    * Takes pride in solving customer problems effectively and is intolerant of poor quality work
    * Lives up to customer expectations and updates them on changes
    * Is respectful of and helpful to other engineers and enjoys gaining and sharing knowledge
    * Attends training on products, services and behavioural skills related to the position
    * Provides tuition on NagraVision products to customers and to NagraVision Engineers, under the managerial responsibility of the Head of System Engineering.
    * Participates in Hotline turns, under the managerial responsibility of the Head of System Engineering.
    * Execute security measures on the systems.
    * Provide quick response time to meet customer satisfaction.

   3. Reporting line

Reporting to: Head of System Engineering

Direct Report: Igor Ferigutti

   4. Interfaces

    * Daily contact with Program/Deployment Manager to inform on current status and plan the next steps of a project
    * Information to and from Customers using live communication, conference calls and e-mail reports
    * Collaboration with Central Services’ staff for problem solving.
    * Collaboration with Product Development for bug reporting and problem solving
    * Regular communication with Head of System Engineering to keep the resource planning up-to-date.

   5. Skills

    * Is recognised to have excellent knowledge of a set of NagraVision products
    * Mostly self-reliant, but requires direction under some situations.
    * Has a good telephone manner and remote diagnosis skills.
    * Is fluent in English, any other Asian language is an asset.
    * Recognised expertise in the UNIX platforms and Windows.
    * Ability to perform complex queries on Oracle or Sybase databases.
    * Recognised ability to analyse TCP/IP networking problems.
    * Ability to demonstrate certain NagraVision products or to provide training on them.
    * Demonstrates good communication skills and reasonable inter-personal skills.
    * Working knowledge of the Ticketing and Remote access tools.
    * Ability to complete problem analysis and do troubleshooting of simple multi-cluster systems.
    * Demonstrates high customer service awareness.
    * Works effectively under pressure.

这就是D&D中所谓的万精油职业吗？

Filed Under: 未分类 Tagged With: , , , , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号