probe database using OEM event or job
Title: probe database using OEM event or job Can someone help me with this? Running Oracle 9.2.0.3 under win2000 I have an application server that occasionally looses connectivity with the listener on the database server although other application servers have no problems connecting. I get error 'Fatal NI connect error 12535' I would like to setup an OEM event (user defined?) or user defined job that must run from the application server having the problem which will test the connection to the listener on the database server (could be like a tnsping XXX) and if it is unsuccessful, notify me via email, pager, etc. has anyone done this? Can it be done with a TCL script? Any samples appreciated? John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada (604) 697-6476 (Office) (604) 313-6054 (Cell)
RE: Question about buffer cache
Title: RE: Question about buffer cache I have indexes in a 16k page size tablespace. I have the following init.ora parameters: db_block_size=4096 db_cache_size=600M db_keep_cache_size=200M db_16k_cache_size=200M If I alter an index to put it in the keep pool, how does Oracle hande the discrepancy between the 4k default keep buffer and the 16k index block size? Am I better off keeping the index in the 16k cache or in the db_keep_cache pool? Since statspack shows: Event Waits Timeouts Time (s) (ms) /txn -- -- -- db file sequential read 2,725,553 0 40,710 15 355.2 I assume my indexes should be cached more to reduce the waits. Is that correct? Any advice would be appreciated. Thanks John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada > (604) 697-6476 (Office) > (604) 313-6054 (Cell) >
Failsafe and OEM automatic discovery
Title: Failsafe and OEM automatic discovery I am running OEM and OMS 9.0.1 on WIN2000 and am having ptoblems discovering databases on a two server Oracle Failsafe cluster. I am running Failsafe 3.3.1 on a two node cluster. Database DB1 on node 1 (cluster group GRP1) Database DB2 on node 2 (cluster group GRP2) Failsafe is working fine but When I try to use OEM to discover the two cluster groups, it will discover the first group and database (DB1) ok but the second group fails with error VD-4520 (target already found?). Does anyone know how to get around this problem? John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada > (604) 697-6476 (Office) > (604) 313-6054 (Cell) >
COLLATING SEQUENCE (EBCDIC to ASCII)
Title: COLLATING SEQUENCE (EBCDIC to ASCII) Does anyone know how to change collating sequence in Oracle, either at the database level or for a specific query? We have a mainframe (ebcdic) app that is converted to NT (acsii) and some special characteres (e.g. period(.) dash (-) etc.) not sorting properly. If using functions, please show example, Thanks John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada > (604) 697-6476 (Office) > (604) 313-6054 (Cell) >
RE: Export multiple targets using OEM
Title: RE: Export multiple targets using OEM I am using OEM 9.0.1 with job scheduler I am trying to export full database for multiple targets using the job scheduler. How do you specify more than one .dmp name and location? I want a .dmp and .log file for each target. Even though I specified 2 targets, it appears as if only one .dmp file was created even though 'Job History' shows both exports completed successfully. Also How can I specify an environment variable such as %ORACLE_SID% in the path for the .dmp file? e.g. D:\ORACLE\ADMIN\%ORACLE_SID%\EXP\FILES\EXP.DMP John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada (604) 697-6476 (Office) (604) 313-6054 (Cell)
Export multiple targets using OEM
Title: Export multiple targets using OEM I am using OEM 9.0.1 with job scheduler I am trying to export full database for multiple targets using the job scheduler. How do you specify more than one .dmp name and location? I want a .dmp and .log file for each target. Even though I specified 2 targets, it appears as if only one .dmp file was created even though 'Job History' shows both exports completed successfully. Also How can I specify an environment variable such as %ORACLE_SID% in the path for the .dmp file? e.g. D:\ORACLE\ADMIN\%ORACLE_SID%\EXP\FILES\EXP.DMP John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada (604) 697-6476 (Office) (604) 313-6054 (Cell)
cost based optimizer
Title: cost based optimizer
I have a table of 500,000 records that is analyzed and contains several indexes that are analyzed.
Can someone explain to me why the cost without a hint is much lower than the cost with a hint?
Using a hint is 10 times faster than without a hint even though the cost is much higher.
Since this is a third party app, I cannot add hints. What aoptions do I have?
select /*+ Index("ICMSSHDR" XSKSHDRS181M1) Use this index XSKSHDRS181M1 */
PKTS_ICMSSHDR from ICMSSHDR
where (FK_IX_ICMSSHDR_DELV='x' and (SHDR_DELV_WHSE_CODE<'86'));
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=213529 Card=228346 Bytes=12102338)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ICMSSHDR' (Cost=213529 Card=228346 Bytes=12102338)
2 1 INDEX (RANGE SCAN) OF 'XSKSHDRS181M1' (NON-UNIQUE) (Cost=2346 Card=228346)
select PKTS_ICMSSHDR from ICMSSHDR
where (FK_IX_ICMSSHDR_DELV='x' and (SHDR_DELV_WHSE_CODE<'86'));
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3526 Card=228346 Bytes=12102338)
1 0 TABLE ACCESS (FULL) OF 'ICMSSHDR' (Cost=3526 Card=228346 Bytes=12102338)
Thanks
John Baylis
Database Administrator
Canadian Forest Products Ltd.
Vancouver B.C. Canada
(604) 697-6476 (Office)
(604) 313-6054 (Cell)
local connection using pro-cobol
Title: local connection using pro-cobol I have a pro-cobol application running on my database server. I am trying to use a local connection in a pro-cobol program but I get an ora-12154 error if I do not have the listener running on a win2000 server using net8. It appears as if it it using a sqlnet connection instead of a local connection. I am using: connect user_name/password I have the oracle_sid=sid and local=sid environment variables set in my batch file. I can shutdown the listener and run 'sqlplus user/password' successfully from the cmd window. Why will pro-cobol not work? John Baylis DBA / Systems Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada
Locked Oracle dll's
Title: Locked Oracle dll's I am trying to remove the Oracle bin directory from an NT 4 server however the Oracle dll's are in use. The reason they are in use is because they are open to the Windows NT winlogon.exe process as a result of remote performance monitoring. When you use Performance Monitor to monitor a computer remotely, the initiating computer attaches to the target computer's Winlogon process through RPC. Without rebooting the server, is there any way to release these dll's from winlogon.exe? Any utilities available to remove dll's? Killing the winlogon.exe causes a reboot. John Baylis DBA / Systems Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada
RE: ORACLE-L Digest -
Title: RE: ORACLE-L Digest - ORACLE-L NODIGEST
find first record in tablespace?
Title: find first record in tablespace? Can someone please tell me if there is a way to find the first record type in a tablespace? In other words I want to be able to find the name of the first physically stored record in a tablespace that may contain many tables. This is an attempt at it: select segment_name, block_id from dba_extents where (extent_id = (select min(extent_id) from dba_extents where tablespace_name = 'ICMS_DATA_TS') and segment_type = 'TABLE' and tablespace_name = 'ICMS_DATA_TS') order by block_id; SEGMENT_NAME BLOCK_ID - -- ICMSSCMT 2 ICMSBHDR 2 XISYS_ICRHDR_KEY 2 XISYS_ICALS_PROD 1282 ICMSPOGR 1282 ICMSGSPC 1282 XISYS_ICHDR_COMP 2562 ICMSSTOR 2562 ICMSATYP 2562 XISYS_ICO_STATUS 3842 AMOSGUSE 3842 etc.. John Baylis DBA / Systems Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada
