How reliable SQL_TRUE it is ?

nix process pid: 214, image: oracle@odsdev01 (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2010-01-18 17:47:42.549
*** SESSION ID:(274.21788) 2010-01-18 17:47:42.549
...

*** 2010-01-18 20:44:17.998
FETCH #1:c=0,e=176,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408287274
FETCH #1:c=0,e=157,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408295527
FETCH #1:c=0,e=188,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408303670
FETCH #1:c=0,e=307,p=1,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408311955
FETCH #1:c=0,e=185,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408318782
FETCH #1:c=0,e=188,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408326769

The sql running for few hours with "alter session set sql_trace=true ", but from tkprof , it only records 41 minutes, omitting the rest timing -- returning 50 millions of records to client (wait event is "SQL*Net message from client" )

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.07 0.07 2 2 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 1650667 1103.09 2444.07 640737 304709 151 24759989
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1650672 1103.16 2444.14 640739 304711 151 24759989

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Maybe there is trace level different in between sql_trace=true and event 10046 , caused the idle event is not shown in the trace info. ?

ORA-00600: [kzdlk_zt2 err]

SQL> SELECT * from dual@ICTD;
SELECT * from dual@ICTD
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [4294967283], [],
[], [], [], [], []




SQL> CREATE PUBLIC DATABASE LINK "ADST"
2 CONNECT TO "DBLN_INT11" IDENTIFIED BY VALUES '1B993ACB4BEA0415'
3 USING 'ADST' ;

Database link created.

SQL>
SQL> select * from dual@ADST;
select * from dual@ADST
*
ERROR at line 1:
ORA-00081: address range [0x60000000000A84E0, 0x60000000000A84E4) is not
readable
ORA-00600: internal error code, arguments: [kzdlk_zt2 err],
[18446744073709551603], [], [], [], [], [], []


According to doc 456320.1

Cause

The exception is caused by the unpublished below :

Bug:5576894 - Abstract: GET ORA-600 [KZDLK_ZT2_ERR] WHEN LINK CREATED WITH 'IDENTIFIED BY VALUES'

In Oracle 10.2, dblink passwords are now stored in a special encrypted form to improve databse link security. Tthe "IDENTIFIED BY VALUES" clause is only intened for use by Oracle internal utilities (EXPORT/IMPORT), and it's not documented in the Oracle user documentation.

The only valid value to be passed in the "IDENTIFIED BY VALUES" clause is an encrypted dblink password identifier (note - this is different from the user password identifier stored in the PASSWORD column of USER$). The internal error in this bug is raised when we attempt to access the db link and materialize the plain text password from the encrypted dblink password identifier - because the value passed to the "IDENTIFIED BY VALUES" clause was not a valid encrypted dblink password identifier we raise an internal error. This is expected behavoiur in this case.

SQL> exit
dev07:B.11:INT11:/software/oraprx> tnsping ADST

TNS Ping Utility for HPUX: Version 10.2.0.2.0 - Production on 25-JAN-2010 16:48: 51

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

Used parameter files:
/software/oraprx/product/10.2.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = vas11gt) (Port = 1536)) (CONNECT_DATA = (SERVICE_NAME = ADST)))
OK (10 msec)
dev07:B.11:INT11:/software/oraprx> sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jan 25 16:48:57 2010

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


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

SQL> drop PUBLIC DATABASE LINK "ADST" ;

Database link dropped.

SQL> CREATE PUBLIC DATABASE LINK "ADST"
2 -- CONNECT TO "DBLN_INT11" IDENTIFIED BY VALUES '1B993ACB4BEA0415'
3 CONNECT TO "DBLN_INT11" IDENTIFIED BY DBLNPWD
4 USING 'ADST' ;

Database link created.

SQL>
SQL>
SQL> select * from dual@ADST;

D
-
X

11g R1 lgwr trace file

cat VAS11GT_lgwr_2531.trc

*** 2010-01-12 06:58:45.760
Warning: log write elapsed time 1757ms, size 1KB
(set event 10468 level 4 to disable this warning)


from oraus.msg file
dev09:VAS11GT:/software/oravas11/product/11.2.0/rdbms/mesg> ll oraus.msg
-rw-r--r-- 1 oravas11 oravas11 4777736 Dec 15 19:33 oraus.msg

10468, 00000, "log writer debug module"
// *Document: NO
// *Cause:
// *Action: Set this event to the appropriate level for log writer debugging.


--set for event

ALTER system SET EVENTS '10468 trace name context forever, level 4';



No more such warning message.

RMI Server Port number self-conflict

Due to some unknown reason, RMI port may look self-conflict. The workaround for us is to re-configure ports number.

Steps:

1. Choose a new RMI port number.

-- Check all instances' ports number from /software/oradas/product/10.2.0/oc4j/j2ee/OC4J_DBConsole_hpdasdb_DASP/config/cat rmi.xml.

-- use "netstat -an |grep " to verify.

2. Get the sys password (save it before change), retain the password of SYSMAN/DBSNMP.

3. Stop agent and dbcontrol :

3.1 emctl stop dbconsole; emctl stop agent

3.2 kill the dangling process: ps -efx|grep oradas |grep dbconsole

4. Deconfig dbcontrol : emca -deconfig dbcontrol db

5. Reconfig port numbers : emca -config dbcontrol db -DBCONTROL_HTTP_PORT 5506 -RMI_PORT 5527 -AGENT_PORT 3939

The Details:

The ERROR message

from emdb.nohup (? , anyway it is under /software/oradas/product/10.2.0/hpdasdb_DASP/sysman/log , use ls -lrt to search latest logfile)

----- Thu Jan 14 08:46:01 2010::DBConsole exited at Thu Jan 14 08:46:01 2010 with return value 7. -----
----- Thu Jan 14 08:46:01 2010::Restarting DBConsole. -----
----- Thu Jan 14 08:46:01 2010::Console Launched with PID 13175 at time Thu Jan 14 08:46:01 2010 -----
10/01/14 08:46:03 Error starting ORMI-Server. Unable to bind socket: Address already in use (errno:226)


dbsvr25:DASP:/software/oradas/product/10.2.0/hpdasdb_DASP/sysman/log> tail emdctl.trc
2010-01-14 09:31:52 Thread-1 ERROR ssl: nzos_Handshake failed, ret=29024
2010-01-14 09:31:52 Thread-1 ERROR http: 6: Unable to initialize ssl connection with server, aborting connection attempt
2010-01-14 09:31:56 Thread-1 ERROR ssl: nzos_Handshake failed, ret=29024
2010-01-14 09:31:56 Thread-1 ERROR http: 6: Unable to initialize ssl connection with server, aborting connection attempt
2010-01-14 09:32:01 Thread-1 ERROR ssl: nzos_Handshake failed, ret=29024
2010-01-14 09:32:01 Thread-1 ERROR http: 6: Unable to initialize ssl connection with server, aborting connection attempt
2010-01-14 09:32:05 Thread-1 ERROR ssl: nzos_Handshake failed, ret=29024
2010-01-14 09:32:05 Thread-1 ERROR http: 6: Unable to initialize ssl connection with server, aborting connection attempt
2010-01-14 09:32:09 Thread-1 ERROR ssl: nzos_Handshake failed, ret=29024
2010-01-14 09:32:09 Thread-1 ERROR http: 6: Unable to initialize ssl connection with server, aborting connection attempt



dbsvr25:DASP:/software/oradas/product/10.2.0/hpdasdb_DASP/sysman/log> tail emagent.trc
2010-01-14 09:32:44 Thread-32 ERROR upload: Error in uploadXMLFiles. Trying again in 211.00 seconds.
2010-01-14 09:32:44 Thread-33 ERROR ssl: nzos_Handshake failed, ret=29024
2010-01-14 09:32:44 Thread-33 ERROR http: 13: Unable to initialize ssl connection with server, aborting connection attempt
2010-01-14 09:32:44 Thread-33 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://hpdasdb:5506/em/upload/: retStatus=-1
2010-01-14 09:33:14 Thread-34 ERROR ssl: nzos_Handshake failed, ret=29024
2010-01-14 09:33:14 Thread-34 ERROR http: 12: Unable to initialize ssl connection with server, aborting connection attempt
2010-01-14 09:33:14 Thread-34 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://hpdasdb:5506/em/upload/: retStatus=-1
2010-01-14 09:33:14 Thread-34 ERROR ssl: nzos_Handshake failed, ret=29024
2010-01-14 09:33:14 Thread-34 ERROR http: 12: Unable to initialize ssl connection with server, aborting connection attempt
2010-01-14 09:33:14 Thread-34 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://hpdasdb:5506/em/upload/: retStatus=-1

Commands:

> emca -deconfig dbcontrol db

> emca -config dbcontrol db -DBCONTROL_HTTP_PORT 5506 -RMI_PORT 5527 -AGENT_PORT 3939

command to check if port already occupied: netstat -an |grep


dbsvr25:DASP:/software/oradas> emca -config dbcontrol db -DBCONTROL_HTTP_PORT 5506 -RMI_PORT 5527 -AGENT_PORT 3939

STARTED EMCA at Jan 14, 2010 10:07:52 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: DASP
Listener port number: 1550
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): dba_support@xxxxx.com.sg
Outgoing Mail (SMTP) server for notifications (optional): smtpxxx
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /software/oradas/product/10.2.0

Database hostname ................ hpdasdb
Listener port number ................ 1550
Database SID ................ DASP
Email address for notifications ............... dba_support@m1.com.sg
Outgoing Mail (SMTP) server for notifications ............... mocexb02

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Jan 14, 2010 10:08:28 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /software/oradas/product/10.2.0/cfgtoollogs/emca/DASP/emca_2010-01-14_10-07-52-AM.log.
Jan 14, 2010 10:08:29 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5506 is already in use.
Jan 14, 2010 10:08:29 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 3939 is already in use.
Jan 14, 2010 10:08:32 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Jan 14, 2010 10:09:04 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Jan 14, 2010 10:09:04 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jan 14, 2010 10:10:45 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jan 14, 2010 10:10:45 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://hpdasdb:5506/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 14, 2010 10:10:45 AM

dbsvr25:DASP:/software/oradas> netstat -an |egrep "5527|5506|3939"
tcp 0 0 .5527 .* LISTEN
tcp 0 0 .3939 .* LISTEN
tcp 0 0 .5506 .* LISTEN

#LISTEN means occupied

Oracle Database 11g Standard Edition One

Oracle Database 11g Standard Edition One is an affordable, full-featured database for servers with up to two sockets.

two sockets: two physical CPU, one CPU can have multiple cores.

SE1 limited to two physical CPU.

brrestore with error BR0374E

brrestore
We had problem with brrestore, have to follow up tomorrow.

BR0374E 0 of 1 file restored by backup utility BR0280I BRRESTORE time stamp: 2009-09-22 19.52.00
BR0231E Backup utility call failed

BR0406I End of file restore: rebnhrxi.rsf 2009-09-22 19.52.00
BR0280I BRRESTORE time stamp: 2009-09-22 19.52.00 BR0404I BRRESTORE terminated with errors


solution : netbackup client name is not defined in /etc/hosts ...

load catbundle and recompile views

It looks we missed one step for new database creation in the past , especially for new database creation.

As our practice is to install oracle software first prior to database creation. This make it easier to forget loading catbundle.sql and view recompilation, according to readme.html of any CPU/PSU published after Jan 2008. The relevant section for are :

10g.

3.3.2.1 Loading Modified .sql Files into the Database
3.3.2.2 Recompiling Views in the Database
3.3.5 Post Installation Instructions for New and Upgraded Databases

11g.

2.3.3 Post Installation Instructions
2.3.4 Post Installation Instructions for Databases Created or Upgraded after Installation of PSU 11.1.0.7.1 in the Oracle Home

Relevant sql for checking are :

SELECT * FROM registry$history where ID = '6452863';

select * from dba_registry_history;

ORA-00600: [OSDEP_INTERNAL]

During a development server migration of tens of database, while starting once database.

SQL> startup
ORA-00600: internal error code, arguments: [OSDEP_INTERNAL], [], [], [], [], [], [], []
ORA-27302: failure occurred at: skgpwreset1
ORA-27303: additional information: invalid shared ctx
ORA-27146: post/wait initialization failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
SQL> exit


Problem solved after increase semmni from 4200 to 8100

OUI abort during 11g Release 2 installation

SEVERE: [FATAL] An internal error occurred within cluster verification framework
Unable to get the current group.

The relevant platform is HPUX Itanium v3.

After revisit installation guide, installed required Unix patches and adjust kernel parameters. Problem solved.

SQL Injection(Chinese)

http://subject.csdn.net/SQL-I/