Table of Contents

Oracle Net

OID

OID Replacement by OpenLDAP

Resultate des Stresstests in Zusammenfassung:

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';