OID und LDAP scheinen die schnelleren Lösungen zu sein und haben während der Lasttests keinerlei Probleme gemacht. ONS hat sich unter starker Last als sehr unzuverlässig erwiesen (daher Versuch abgebrochen). Bei 100-150 Requests pro Sekunde wurde der Versuch bei OID/LDAP abgebrochen, da man sonst noch mehr Clients hätte vorbereiten müssen. Diese Zahl ist aber jenseits sämtlicher je gebrauchter Werte. Server kam in keinerlei Art und Weise an den Anschlag.

Um Aussagen betreffend der Zufverlässigkeit von OID/LDAP zu machen müsste man Langzeitversuche machen (z.B. alle Testsystem auf OID/LDAP umstellen).

Das Handling von LDAP ist meines Erachtens sehr einfach und schnell zu implementieren. Migration weg von ONS ist kein grosses Problem. Know-How- Aufbau ist gering im Gegensatz zu OID?

PDF (43.5 kByte)

OpenOffice Calc (13.4 kByte)

Excel (15.5 kByte)

Security

Passwords

Howto get Oracle Database link Passwords

Works only < 10g

SELECT userid, password FROM sys.link$;

USERID        PASSWORD
------------- ----------
DWHSMG        abcdrs3
CDWH          abcdrs2
APR           abcdrs1

COLUMN file_name FORMAT a60
SELECT file_id, file_name FROM dba_data_files;

FILE_ID   FILE_NAME
--------- ----------------------------
        1 /u02/oradata/DB01/system01.dbf
        2 /u02/oradata/DB01/undotbs01.dbf
        3 /u02/oradata/DB01/tools01.dbf
...

$ strings /u02/oradata/DB01/system01.dbf | grep abcdrs3
abcdrs3

$ cp /u02/oradata/DB01/system01.dbf ~/tmp/
cp: cannot open /u02/oradata/DB01/system01.dbf: Permission denied

$ split -b 10m system01.dbf
$ultraedit xaa

COLUMN segment_name FORMAT a30
SELECT segment_name, file_id, block_id, bytes, blocks, bytes/blocks, block_id*bytes/blocks
  FROM dba_extents
 WHERE segment_name = 'LINK$';

SEGMENT_NAME FILE_ID BLOCK_ID BYTES BLOCKS BYTES/BLOCKS BLOCK_ID*BYTES/BLOCKS
------------ ------- -------- ----- ------ ------------ ---------------------
LINK$              1      753 65536      8         8192               6168576
LINK$              1    52585 65536      8         8192             430776320

6168576 + 16384 = 6184960 (Block seems to be filled from the end???)
(0x5e5f60 = 6184800 = 5.9 MB)

005e5f60h: 00 00 00 00 00 00 00 00 00 2C 01 07 02 C1 39 0C ; .........,...Á9.
005e5f70h: 50 44 57 48 44 32 2E 57 4F 52 4C 44 07 78 69 07 ; PRODB2.WORLD.xi.
005e5f80h: 0C 11 33 33 06 50 44 57 48 44 32 03 41 50 52 07 ; ..33.PRODB2.APR.
005e5f90h: EE EE EE EE EE EE EE 01 80 2C 00 07 02 C1 39 0C ; abcdrs1......Á9.
005e5fa0h: 50 44 57 48 43 31 2E 57 4F 52 4C 44 07 78 69 07 ; PRODB1.WORLD.xi.
005e5fb0h: 0C 11 33 33 06 50 44 57 48 43 31 04 43 44 57 48 ; ..33.PRODB1.CDWH
005e5fc0h: 07 EE EE EE EE EE EE EE 01 80 2C 00 07 02 C1 39 ; .abcdrs2.?,...Á9
005e5fd0h: 0C 44 44 57 48 53 32 2E 57 4F 52 4C 44 07 78 69 ; .DEVDB2.WORLD.xi
005e5fe0h: 07 0C 11 33 33 06 44 44 57 48 53 32 06 44 57 48 ; ...33.DEVDB2.DWH
005e5ff0h: 53 4D 47 06 EE EE EE EE EE EE EE 80 14 CD 06 01 ; SMG.abcdrs3.....
005e6000h: 00 02 00 00 00 00 02 F3 00 00 00 00 00 00 01 05 ; .......ó........
005e6010h: 03 F5 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; .õ..............

Tuning

Memory

Howto get Memory free while using Job queue processes

Problem : System starts SWAPing due to Oracle Processes consuming to much memory while using Job Queue Processes.

SQL> -- Memory actually used
SQL> host top -d 1 | sed '1,3d' | sed '2,99d'

SQL> -- Processes using most memory
SQL> host ps -eo vsz,rss,pcpu,time,pid,ppid,user,comm | sort -n

SQL> -- Number of job queue processes
SQL> host ps -ef | grep ora_j | grep <SID> | wc -l

SQL> -- Max job queue processes allowed
SQL> show parameter job_queue_processes

SQL> -- Set job queue processes to zero
SQL> ALTER SYSTEM SET job_queue_processes=0;

SQL> -- Number of job queue processes again
SQL> host ps -ef | grep ora_j | grep <SID> | wc -l

SQL> -- Reset job queue processes to old value
SQL> ALTER SYSTEM SET job_queue_processes=<n>;

SQL> -- Check memory consumption now
SQL> host top -d 1 | sed '1,3d' | sed '2,99d'

Short cuts and code snippets

sqlplus

sqlplus with nohup in background

$ nohup sqlplus -s ${OWNER}/${DESTINATION_PWD}@${DESTINATION_DB} << _EOI

  SELECT * FROM dual;
  EXIT

_EOI &

sqlplus connect without oracle net service name

$ sqlplus system/manager@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ABC)
(PORT=1521))(CONNECT_DATA=(SID=ABC)))

SQL

System Informations

Finding unique Indexes

SELECT c.table_name, c.constraint_name, i.index_name
     , i.tablespace_name, i.uniqueness
  FROM dba_constraints c, sys.con$ co, sys.cdef$ cd,
       dba_objects o, dba_indexes i
 WHERE c.table_name LIKE UPPER('&tab;_name')
   AND c.constraint_name = co.name
   AND co.con# = cd.con#
   AND cd.enabled = o.object_id
   AND o.owner = i.owner
   AND o.object_name = i.index_name
   and i.uniqueness != 'UNIQUE'
 ORDER BY c.table_name, c.constraint_name, i.index_name;

Redo log switches per hour and per day

SELECT TO_CHAR(first_time, 'YYYY.MM.DD') day
     , TRUNC(SUM(blocks*block_size)/1024/1024) mbytes
  FROM v$archived_log
 GROUP BY TO_CHAR(first_time, 'YYYY.MM.DD')
 ORDER BY TO_CHAR(first_time, 'YYYY.MM.DD');

SELECT TO_CHAR(first_time, 'YYYY.MM.DD HH24') day_hr
     , TRUNC(SUM(blocks*block_size)/1024/1024) mbytes
  FROM v$archived_log
 WHERE TO_CHAR(first_time, 'YYYY.MM.DD') = '2000.11.03'
 GROUP BY TO_CHAR(first_time, 'yyyy.mm.dd hh24')
 ORDER BY TO_CHAR(first_time, 'yyyy.mm.dd hh24');

IO per file

SELECT a.file#, phyrds, phywrts, phyblkrd, phyblkwrt, name
  FROM v$filestat a, v$datafile b
 WHERE a.file#= b.file#
 ORDER BY 2;

SQL tricks

Select over 2 lines

SELECT 'SELECT COUNT(*) FROM ' || table_name || ';' || CHR(10)
    || 'SELECT SYSDATE FROM dual;'
  FROM dba_tables
 WHERE owner = 'XYZ';

Oracle

Table of Contents

Oracle Net

OID

OID Replacement by OpenLDAP