Tag Archives: Troubleshooting

Losing the Oracle Wallet for Enterprise User Security

Having a reliable backup solution for your Transparent Data Encryption (TDE) or Enterprise User Security (EUS) Wallets, is beyond discussion. Nevertheless it can happen that you lose or corrupt the Oracle Wallet. With Transparent Data Encryption (TDE), this is really bad luck, because you can not access your encrypted data. Losing an EUS wallet is on the other side not really an issue. You can remove the database from your EUS LDAP directory (Oracle Unified Directory OUD or Oracle Internet Directory OID) and re-register the database. Although this is the fastest solution, it has some constraints. Un-register and re-register the database, means losing the EUS mappings. Alternatively you can manually create a new empty Oracle Wallet and reset the Database password using dbca.

Ok, first lets create a new empty wallet using mkstore:

oracle@urania:/u00/app/oracle/ [TDB11A] mkstore -wrl $ORACLE_BASE/admin/TDB11A/wallet -create
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter password:              
   
Enter password again:

Alternatively you can use orapki to create an empty wallet. orapki is easier to use in scripts and supports auto login local wallets with -auto_login_local:

oracle@urania:/u00/app/oracle/ [TDB11A] orapki wallet create -wallet $ORACLE_BASE/admin/TDB11A/wallet/ -pwd <password> -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Create an entry for the Database distinguished names (DN). This step is somehow necessary because dbca -regenerateDBPassword just creates the password entry but no new dn entry:

oracle@urania:/u00/app/oracle/ [TDB11A] mkstore -wrl $ORACLE_BASE/admin/TDB11A/wallet -createEntry ORACLE.SECURITY.DN cn=TDB11A_SITE1,cn=OracleContext,dc=postgasse,dc=org
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Create an entry for the database password:

oracle@urania:/u00/app/oracle/ [TDB11A] mkstore -wrl $ORACLE_BASE/admin/TDB11A/wallet -createEntry ORACLE.SECURITY.PASSWORD manager
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Recreate the database registration password using dbca:

oracle@urania:/u00/app/oracle/ [TDB11A] dbca -silent -configureDatabase -sourceDB TDB11A \
> -sysDBAUserName sys -sysDBAPassword </password><password> \
> -regenerateDBPassword true \
> -dirServiceUserName cn=orcladmin -dirServicePassword </password><password> \
> -walletPassword </password><password>
Preparing to Configure Database
6% complete
13% complete
66% complete
Completing Database Configuration
100% complete
Look at the log file "/u00/app/oracle/cfgtoollogs/dbca/TDB11A_SITE1/TDB11A11.log" for further details.

Verify the new password in the Oracle Wallet:

oracle@urania:/u00/app/oracle/ [TDB11A] mkstore -wrl $ORACLE_BASE/admin/TDB11A/wallet -viewEntry ORACLE.SECURITY.PASSWORD
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:              
   
ORACLE.SECURITY.PASSWORD = S6usUGSNb#P1

This password can now be used to verify the LDAP Bind with ldapsearch using the database DN and the password:

oracle@urania:/u00/app/oracle/ [TDB11A] ldapsearch -h localhost -p 1389  \
> -D 'cn=TDB11A_SITE1,cn=OracleContext,dc=postgasse,dc=org' -w S6usUGSNb#P1 \
> -b 'cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=postgasse,dc=org' '(objectclass=*)'
cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=postgasse,dc=org
orclVersion=102000
objectClass=top
objectClass=orclContainer
objectClass=orclDBSecConfig
objectClass=orclDBSecConfig10i
orclDBVersionCompatibility=90000
cn=OracleDBSecurity
orclDBOIDAuthentication=PASSWORD

...

Or finally check login via SQLPlus as EUS user:

oracle@urania:/u00/app/oracle/ [TDB11A] sqh

SQL*Plus: Release 11.2.0.4.0 Production ON Wed Sep 14 10:22:28 2016

Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.


Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
WITH the Partitioning, Oracle Label Security, OLAP, DATA Mining,
Oracle DATABASE Vault AND REAL Application Testing options

SQL> conn soe
Enter password:
Connected.
SQL> @sousrinf
DATABASE Information
--------------------
- DB_NAME       : TDB11A
- DB_DOMAIN     : postgasse.org
- INSTANCE      : 1
- INSTANCE_NAME     : TDB11A
- SERVER_HOST       : urania
-
Authentification Information
----------------------------
- SESSION_USER      : EUS_USER
- PROXY_USER        :
- AUTHENTICATION_METHOD : PASSWORD
- IDENTIFICATION_TYPE   : GLOBAL SHARED
- NETWORK_PROTOCOL  :
- OS_USER       : oracle
- AUTHENTICATED_IDENTITY: SOE
- ENTERPRISE_IDENTITY   : cn=soe,cn=People,dc=postgasse,dc=org
-
Other Information
-----------------
- ISDBA         : FALSE
- CLIENT_INFO       :
- PROGRAM       : sqlplus@urania.postgasse.org (TNS V1-V3)
- MODULE        : SQL*Plus
- IP_ADDRESS        :
- SID           : 410
- SERIAL#       : 925
- SERVER        : DEDICATED
- TERMINAL      : pts/2

PL/SQL PROCEDURE successfully completed.

Depending on your Oracle Directory it may happen, that you run into ORA-28030. This can happen, if you password profile on the directory server has Reset Password on Next Login defined. To work around this issue you have to temporarily disable Reset Password on Next Login in the password profile. This issue is also discussed in the MOS Note 558119.1 ORA-28030 After Regenerating Wallet Password Using dbca.

Update: ORA-00600 [kpdbModAdminPasswdInRoot: not CDB] when changing password of default account

As discussed in my post ORA-00600 [kpdbModAdminPasswdInRoot: not CDB] when changing password of default account There is an unpublished bug 16901482 which cause an ORA-00600 when trying to set a new password for an Oracle default account like DBSNMP, DIP or OUTLN.

On september 4th Oracle released the one-off patch 16901482 for this bug. A short test showed that the issue has been fixed with this patch. Unfortunately the patch is only available for Linux86-64.

Simple offline patch installation according the patch README.

oracle@urania:~/16901482/ [TDB12] $cdh/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u00/app/oracle/product/12.1.0.1
Central Inventory : /u00/app/oraInventory
   from           : /u00/app/oracle/product/12.1.0.1/oraInst.loc
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/opatch2013-09-09_17-06-52PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
oracle@urania:~/16901482/ [TDB12] $cdh/OPatch/opatch apply
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u00/app/oracle/product/12.1.0.1
Central Inventory : /u00/app/oraInventory
   from           : /u00/app/oracle/product/12.1.0.1/oraInst.loc
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/16901482_Sep_09_2013_17_07_24/apply2013-09-09_17-07-23PM_1.log

Applying interim patch '16901482' to OH '/u00/app/oracle/product/12.1.0.1'
Verifying environment and performing prerequisite checks...
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u00/app/oracle/product/12.1.0.1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 12.1.0.1.0...

Verifying the update...
Patch 16901482 successfully applied
Log file location: /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/16901482_Sep_09_2013_17_07_24/apply2013-09-09_17-07-23PM_1.log

OPatch succeeded.
oracle@urania:~/16901482/ [TDB12] $cdh/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u00/app/oracle/product/12.1.0.1
Central Inventory : /u00/app/oraInventory
   from           : /u00/app/oracle/product/12.1.0.1/oraInst.loc
OPatch version    : 12.1.0.1.0
OUI version       : 12.1.0.1.0
Log file location : /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/opatch2013-09-09_17-08-09PM_1.log

Lsinventory Output file location : /u00/app/oracle/product/12.1.0.1/cfgtoollogs/opatch/lsinv/lsinventory2013-09-09_17-08-09PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 12c                                                  12.1.0.1.0
Oracle Database 12c Examples                                         12.1.0.1.0
There are 2 products installed in this Oracle Home.


Interim patches (1) :

Patch  16901482     : applied on Mon Sep 09 17:07:51 CEST 2013
Unique Patch ID:  16618513
   Created on 4 Sep 2013, 12:02:44 hrs PST8PDT
   Bugs fixed:
     16901482

--------------------------------------------------------------------------------

OPatch succeeded.

Simple test with DBNSMP user similar to the test in the initial post.

SQL> col username FOR a20
SQL> SELECT username,account_status,password_versions,ORACLE_MAINTAINED FROM dba_users
  2  WHERE username='DBSNMP';

USERNAME             ACCOUNT_STATUS                   PASSWORD_VER O
-------------------- -------------------------------- ------------ -
DBSNMP               EXPIRED                          10G 11G      Y

SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28001: the password has expired

Changing password FOR dbsnmp
NEW password:
Retype NEW password:
Password changed
Connected.
SQL> exit

ORA-00600 [kpdbModAdminPasswdInRoot: not CDB] when changing password of default account

During the test on Oracle Database 12c I run into an interesting error respectively Chris Antongini has made me aware of it. If you try to change the password of an Oracle default account on a none CDB with SQL*Plus password you run into an ORA-00600 [kpdbModAdminPasswdInRoot: not CDB] and of course the password is not changed. Let’s try it with the DBSNMP account.

SQL> SHOW parameter pluggable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            BOOLEAN     FALSE

SQL> col username FOR a20
SQL> SELECT username,account_status,password_versions,ORACLE_MAINTAINED FROM dba_users
  2  WHERE username='DBSNMP';

USERNAME             ACCOUNT_STATUS                   PASSWORD_VER O
-------------------- -------------------------------- ------------ -
DBSNMP               EXPIRED & LOCKED                 10G 11G      Y


SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28000: the account IS locked


Warning: You are no longer connected TO ORACLE.
SQL> conn / AS sysdba
Connected.
SQL> ALTER USER DBSNMP account UNLOCK;

USER altered.

SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28001: the password has expired


Changing password FOR dbsnmp
NEW password:
Retype NEW password:
ERROR:
ORA-00600: internal error code, arguments: [kpdbModAdminPasswdInRoot: NOT CDB],
[], [], [], [], [], [], [], [], [], [], []


Password unchanged
Warning: You are no longer connected TO ORACLE.

As you can see above the account is expired and locked. After unlocking the account I’ll try to login again and will be asked to set a new password. Doing the same on a container database does work without an ORA-00600.

SQL> SHOW parameter pluggable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            BOOLEAN     TRUE
SQL> col username FOR a20
SQL> SELECT username,account_status,password_versions,ORACLE_MAINTAINED FROM dba_users
  2  WHERE username='DBSNMP';

USERNAME             ACCOUNT_STATUS                   PASSWORD_VER O
-------------------- -------------------------------- ------------ -
DBSNMP               EXPIRED & LOCKED                 10G 11G      Y

SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28000: the account IS locked


Warning: You are no longer connected TO ORACLE.
SQL> conn / AS sysdba
Connected.
SQL> ALTER USER DBSNMP account UNLOCK;

USER altered.

SQL> conn dbsnmp/dbsnmp
ERROR:
ORA-28001: the password has expired


Changing password FOR dbsnmp
NEW password:
Retype NEW password:
Password changed
Connected.

Sure as DBA you may workaround this problem and set the users new password with ALTER USER. So far I couldn’t reproduce this problem with a custom test user. According to Oracle this problem is related to an unpublished bug 16901482 which will be fixed with a one off patch 17217733.

AVDF Linux kernel could not recognize whole RAM

After initial setup of an Audit Vault and Database Firewall engineering system, I’ve started to add several audit vault agents and secure targets. In the beginning it went quite smoothly. But after a certain number of secured targets, there were continuously ORA-04031 errors. Most of the errors were related to large pool and PX Msg buffers issues. The analysis of the trace files has shown interesting stuff. 😉 But more on that in a later blog post. The real problem is the available memory.

Symptoms

The Audit Vault and Database Firewall engineering system is running on a HP ProLiant BL465c Gen 8. It comes with 32GB Memory. Should actually be sufficient for a system engineering. It turned out that the 32GB are not recognized by operating system. As you can see below the system has just 3GB memory in total.

[root@melete2 ~]# free
                     total    used   free shared buffers  cached
Mem:               3048108 2385888 662220      0   10720 1525036
-/+ buffers/cache:  850132 2197976
Swap:              4194296  453564 3740732

Reviewing dmesg shows that we lose 29 GB of memory.

Initializing cgroup subsys cpuset
Initializing cgroup subsys cpu
Linux version 2.6.32-300.39.5.el5uek (mockbuild@ca-build56.us.oracle.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)) #1 SMP Wed Mar 13 11:26:53 PDT 2013
Command line: ro root=/dev/vg_root/lv_root console=tty9 udevtimeout=10
KERNEL supported cpus:
  Intel GenuineIntel
  AMD AuthenticAMD
  Centaur CentaurHauls
BIOS-provided physical RAM map:
 BIOS-e820: 0000000000000000 - 000000000009f000 (usable)
 BIOS-e820: 000000000009f000 - 00000000000a0000 (reserved)
 BIOS-e820: 00000000000f0000 - 0000000000100000 (reserved)
 BIOS-e820: 0000000000100000 - 00000000bddde000 (usable)
 BIOS-e820: 00000000bddde000 - 00000000bde0e000 (ACPI data)
 BIOS-e820: 00000000bde0e000 - 00000000d0000000 (reserved)
 BIOS-e820: 00000000fec00000 - 00000000fee10000 (reserved)
 BIOS-e820: 00000000ff800000 - 0000000100000000 (reserved)
 BIOS-e820: 0000000100000000 - 000000083efff000 (usable)
DMI 2.7 present.
last_pfn = 0x83efff max_arch_pfn = 0x400000000
MTRR default type: uncachable
MTRR fixed ranges enabled:
  00000-9FFFF write-back
  A0000-BFFFF uncachable
  C0000-FFFFF write-back
MTRR variable ranges enabled:
  0 base 000000000000 mask FFFF80000000 write-back
  1 base 000080000000 mask FFFFC0000000 write-back
  2 disabled
  3 disabled
  4 disabled
  5 disabled
  6 disabled
  7 disabled
x86 PAT enabled: cpu 0, old 0x7040600070406, new 0x7010600070106
e820 update range: 00000000c0000000 - 000000083efff000 (usable) ==> (reserved)
WARNING: BIOS bug: CPU MTRRs don't cover all of memory, losing 29679MB of RAM.
------------[ cut here ]------------

Cause

According to an Oracle Metalink Note 1448147.1 this problem is related to a BIOS issue.

Solutions and Workaround

The solution described in Oracle Metalink Note 1448147.1 is to upgrade the BIOS or disable MTRR in kernel. Since BIOS upgrade is not an option for this environment I’ll try to workaround by disable MTRR.

Disable MTRR

Changing the grub.conf is basically quite easy if you find the boot files. When I first try it, I’d realized that there is no grub configuration available. It seems that Oracle decided to not mount /boot at startup. So it is mandatory to first mount the boot partition. Afterward you just can add disable_mtrr_trim as additional kernel option.

[root@melete2 ~]# mount /boot

[root@melete2 ~]# df -kh /boot
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             145M   26M  112M  19% /boot

[root@melete2 ~]# vi /boot/grub/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/vg_root/lv_root
#          initrd /initrd-version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Audit Vault Server 12.1.1.0.0
        root (hd0,0)
        kernel /vmlinuz-2.6.32-300.39.5.el5uek ro root=/dev/vg_root/lv_root console=tty9
udevtimeout=10 disable_mtrr_trim
        initrd /initrd-2.6.32-300.39.5.el5uek.img
title Audit Vault Server 12.1.1.0.0
        root (hd0,0)
        kernel /vmlinuz-2.6.32-300.38.1.el5uek ro root=/dev/vg_root/lv_root console=tty9
udevtimeout=10 disable_mtrr_trim
        initrd /initrd-2.6.32-300.38.1.el5uek.img

[root@melete2 ~]# reboot

Broadcast message from root (pts/0) (Thu Jul 11 20:17:56 2013):

The system is going down for reboot NOW!
[root@melete2 ~]# Connection to melete2 closed by remote host.
Connection to melete2 closed.

After reboot we now have 32GB memory available.

[root@melete2 ~]# free
                      total     used     free shared buffers  cached
Mem:               33024372  3930724 29093648      0   17868 2640744
-/+ buffers/cache:  1272112 31752260
Swap:              14680056        0 14680056

Unfortunately, the configuration of the AVDF appliance is not automatically updated to use the extra memory. We have to do some manual changes.

Update Kernel Parameters

The kernel setting have to be changed to allow a bigger SGA. See Metalink Note 1529433.1 for more detailed information on how calculate and set the kernel parameters. For the engineering system we will define a SGA with 20GB therefor we set the shmmax and shmall as follows:

[root@melete2 ~]# vi /etc/sysctl.conf

kernel.shmmax=23622320128
kernel.shmall=5368709120
...
[root@melete2 ~]# sysctl -p

Increase SWAP

With 32GB memory, it is also advisable to enlarge the swap space. I’ve discussed this already in the blog post Resize swap space on linux. Since the AVDF appliance does use logical volumes it’s even a bit easier.

[root@melete2 ~]# swapoff -v /dev/vg_root/lv_swap

[root@melete2 ~]# lvresize /dev/vg_root/lv_swap -L +8G

[root@melete2 ~]# mkswap /dev/vg_root/lv_swap

[root@melete2 ~]# swapon -v /dev/vg_root/lv_swap

Increase SGA

Finally we can increase the SGA.

SQL> ALTER system SET sga_max_size=20G scope=spfile;
System altered.

SQL> ALTER system SET sga_target=20G scope=spfile;
System altered.

SQL> startup force

Conclusion

Although AVDF is an appliance, it is mandatory to examine the system after installation. Eg. are there errors in the log files in /var/log, memory, storage etc. available. The solution described here makes it possible to use all the memory. Nevertheless, the appliance has been adjusted to an extent where is necessary to consider whether the support is still archive. If you run into a similar issue on your production AVDF setup I would recommend opening an Oracle SR. Looking forward to the next AVDF patchset. I hope this system stays patchable.

References

Some links related to this post.

  • Linux kernel could not recognize whole RAM [1448147.1]
  • Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device[301830.1]
  • Requirements for Installing Oracle Database 12.1 on RHEL5 or OL5 64-bit (x86-64) [1529433.1]
  • Requirements for Installing Oracle 11gR2 RDBMS on RHEL (and OEL) 5 on AMD64/EM64T [880989.1]
  • Master Note of Linux OS Requirements for Database Server [851598.1]

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.