Category Archives: Internals

Database Internals

AVDF installation ISO

Due to some problems during the installation of Oracle Audit Vault and Database Firewall 12.1.2 (see AVDF installation fails on HP server with Smart Array Disk Controller), I’ve looked at the AVDF ISO image and its kickstart setup.

AVDF 12.1.2 is based on Oracle Enterprise Linux 5.9. To setup or upgrade AVDF it is required to boot the system from the AVDF ISO. The menu from the ISOLINUX bootloader allows simple selection of installation, upgrade, or memory test.
AVDF_12.1.2.0.0_setup01
As you can see in the picture, it is also possible to specify the boot options directly in the prompt.
The boot loader menu itself is configured in the isolinux.cfg file. Looking at the file reveals how an installation respectively an upgrade will be started. Both use their own kickstart file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
default memtest86

prompt 1
display boot.msg

F1 boot.msg

label install
kernel vmlinuz
append noipv6 initrd=initrd.img ramdisk_size=8192 ks=file:/kickstart/kickstart.cfg noshell

label upgrade
kernel vmlinuz
append noipv6 initrd=initrd.img ramdisk_size=8192 ks=file:/kickstart/update.cfg noshell

label memtest86
kernel memtest
append -

A special note, Oracle has switched off the shell on console 2 (alt-F2) with noshell. If you want to use console 2 during the installation, you have to enter the boot options directly on the prompt. But enabling the shell on console 2 leaves the system vulnerable to root access. It should not be left unattended when such a shell exists. An example can be seen in the picture above.

The kickstart file is part of the initrd.img which is used during the boot process. But this image needs first to be unpacked. The image is some kind of a gzipped cpio archive.

Rename and unpack the image:

mv initrd.img initrd.gz
gunzip initrd.gz

So extract the files from initrd using cpio:

mkdir initrd_work
cd initrd_work
cpio -id < ../initrd
31570 blocks

Now we can see the content of the initrd.img file and in particular the kickstart files.

ls
bin etc kickstart proc selinux tmp
dev init modules sbin sys var

References

Further information on this topic.

Trivadis CBO Days 2014

CBO Days 2014
The company I work for, Trivadis, organized again an exceptional event with top speakers in Zurich. This year’s focus will be on the Oracle Database query optimizer, also known as cost-based optimizer (CBO).

The query optimizer is not only one of the most complex pieces of software that constitutes the Oracle kernel; it is also one of the most unappreciated. Why? Taking efficient and sufficient advantage of the query optimizer, you will definitively need to understand how it works. This is exactly what we are aiming for at the CBO Days.

The event will take place from June 10 to 11 in Zurich. More Information on the Event including full agenda, registration, etc is available on the website of Trivadis.

Query alert log from sqlplus

It is not really a novum that you can directly query the alertlog from SQLPlus. Tanel Poder and others already have discussed this a while ago. Somehow I can never remember the name of the X$ view when I need it. So it is time to sum up the information a little bit.

SQL> DESC X$DBGALERTEXT
    Name                         NULL?    TYPE
    ---------------------------- -------- ---------------------------
 1  ADDR                                  RAW(8)
 2  INDX                                  NUMBER
 3  INST_ID                               NUMBER
 4  CON_ID                                NUMBER
 5  ORIGINATING_TIMESTAMP                 TIMESTAMP(3) WITH TIME ZONE
 6  NORMALIZED_TIMESTAMP                  TIMESTAMP(3) WITH TIME ZONE
 7  ORGANIZATION_ID                       VARCHAR2(64)
 8  COMPONENT_ID                          VARCHAR2(64)
 9  HOST_ID                               VARCHAR2(64)
10  HOST_ADDRESS                          VARCHAR2(46)
11  MESSAGE_TYPE                          NUMBER
12  MESSAGE_LEVEL                         NUMBER
13  MESSAGE_ID                            VARCHAR2(64)
14  MESSAGE_GROUP                         VARCHAR2(64)
15  CLIENT_ID                             VARCHAR2(64)
16  MODULE_ID                             VARCHAR2(64)
17  PROCESS_ID                            VARCHAR2(32)
18  THREAD_ID                             VARCHAR2(64)
19  USER_ID                               VARCHAR2(64)
20  INSTANCE_ID                           VARCHAR2(64)
21  DETAILED_LOCATION                     VARCHAR2(160)
22  PROBLEM_KEY                           VARCHAR2(550)
23  UPSTREAM_COMP_ID                      VARCHAR2(100)
24  DOWNSTREAM_COMP_ID                    VARCHAR2(100)
25  EXECUTION_CONTEXT_ID                  VARCHAR2(100)
26  EXECUTION_CONTEXT_SEQUENCE            NUMBER
27  ERROR_INSTANCE_ID                     NUMBER
28  ERROR_INSTANCE_SEQUENCE               NUMBER
29  VERSION                               NUMBER
30  MESSAGE_TEXT                          VARCHAR2(2048)
31  MESSAGE_ARGUMENTS                     VARCHAR2(512)
32  SUPPLEMENTAL_ATTRIBUTES               VARCHAR2(512)
33  SUPPLEMENTAL_DETAILS                  VARCHAR2(4000)
34  PARTITION                             NUMBER
35  RECORD_ID                             NUMBER

A simple query to get the alert log messages and timestamp would look like.

SET linesize 160 pagesize 200
col RECORD_ID FOR 9999999 head ID
col ORIGINATING_TIMESTAMP FOR a20 head DATE
col MESSAGE_TEXT FOR a120 head Message

SELECT
    record_id,
    to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),
    message_text
FROM
    x$dbgalertext;

For daily use I’ve put together two scripts.

  •  tal.sql list all or some alert log messages. Messages will be filtered by the parameter
  •  taln.sql list the last n numbers of rows in an alert log.

Write into the alertlog

The procedure kdswrt in dbms_system package allows us to write own messages in the alert log / trace files or both. It receives two parameters:

  • A number that indicates where do we want to write our message
    1. Writing to a TRACE file
    2. Writing to the Alert.log file
    3. Writing to both of them
  • A text string (the message itself).
EXEC dbms_system.ksdwrt(2, 'ORA-00042: Test message in alert log.');

Query the Alertlog

List the last 10 lines in the alert log.

SQL> @taln 10


SQL> @taln 10

      ID DATE                 Message
-------- -------------------- ----------------------------------------------------------------------
    4333 23.07.2013 22:00:47  Thread 1 advanced TO log SEQUENCE 94 (LGWR switch)
    4334 23.07.2013 22:00:47    CURRENT log# 1 seq# 94 mem# 0: /u00/oradata/TDB01/redog1m1TDB01.dbf
    4335 23.07.2013 22:00:47    CURRENT log# 1 seq# 94 mem# 1: /u01/oradata/TDB01/redog1m2TDB01.dbf
    4336 23.07.2013 22:00:47  Archived Log entry 111 added FOR thread 1 SEQUENCE 93 ID 0xa3d43dfa...
    4337 24.07.2013 02:00:00  Closing scheduler window
    4338 24.07.2013 02:00:00  Closing Resource Manager plan via scheduler window
    4339 24.07.2013 02:00:00  Clearing Resource Manager plan via parameter
    4340 24.07.2013 03:38:21  VKTM detected a TIME drift. Please CHECK trace file FOR more details.
    4341 24.07.2013 09:18:38  VKTM detected a TIME drift. Please CHECK trace file FOR more details.
    4342 24.07.2013 14:50:05  ORA-00042: Test Message IN alert log

10 ROWS selected.

Query the alert log string ORA-00042.

SQL> @tal ORA-00042

      ID DATE                 Message
-------- -------------------- -------------------------------------------
    4342 24.07.2013 14:50:05  ORA-00042: Test Message IN alert log


FILTER ON alert log message => ORA-00042

Other fixed tables

There are bunch of other X$ Fixed Tables. At lease the following are somehow related to the ADR

  • X$DBGDIREXT list all file and directory names under diagnostic_dest/diag directory. Will be quite a lot on a shared DB server
  • X$DBGRICX list of ADR Incidents

References

Some links related to this post.

Oracle hidden init.ora parameter

This post focuses on init.ora parameters. It is not really new topic, but rather a personal reference to some practical queries and scripts. If you are the customer, it’s always handy when you can easily access your own queries.

It is quite simple to get some information on init.ora parameters from SQLPlus. Using a tool like TOAD or SQL Developer make it even easier. Unfortunately I work often at the customer without my own tools and scripts. So commandline and SQLPlus is the only “tools” available to work on the database. It is not an issue to dig through the data dictionary to get any kind of information as long as there is 1-2 view involved. But for querying multiple View’s, X$ views etc it is easier to have something on hand.

OK, what’s different with my queries? Not much, they just fit my needs 🙂 Instead of just querying v$parameter I’ll query as well the X$ views to see as well the hidden parameter and simple description for each parameter.

The first query does a select on X$KSPPI, X$KSPPCV, X$KSPPSV and V$PARAMETER to display all init.ora parameter including the hidden parameters. The result can be limited by adding a part of the parameter name or specify % to see all which then would be a little over 2500 parameters. S stands for it is session modifiable, I stands for it is system modifiable and D show if the parameter does still have the default value or not. I’ve added the query as hip.sql (stands somehow for hidden init parameter) to my small script collection which can be downloaded in the script section.

SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT  
  a.ksppinm  "Parameter",
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm;

The second script does the same as the first one exempt that it limit the result to the list of parameter which are not default. I’ve added the query as hipf.sql (stands somehow for hidden init parameter false) to my small script collection which can be downloaded in the script section.

SET linesize 235 pagesize 200
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT * FROM (SELECT  
  a.ksppinm  "Parameter",
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm) WHERE d='F';

A few information on hidden init.ora parameter can be found in the Metalink Note How To Query And Change The Oracle Hidden Parameters In Oracle 10g [315631.1]