Tag Archives: Script

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.

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.
How To Edit, Read, and Query the Alert.Log [1072547.1]
How To Test The Generic Alert Log Error Metric on 10g [850320.1]
Tanel Poder Oracle 11g: Reading alert log via SQL

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]

Script to download Oracle Patch

Downloading Oracle software, patch or patch-set via web browser is handy if you need the software on your client PC or if you just download small patch’s. As soon as you want, however, download a greater volume of patches or a large patch set, it gets cumbersome. After downloading the patch must be copied to the target system. All steps could be quite time consuming depending on your network throughput.

WGET Option

Since a while it is possible to select a WGET Option in the download dialog rather than downloading each file individual (red box in the picture below).

In a new dialog box you then my download or copy the wget download script for the selected patch’s.

Before starting the download via script the MOS credential have to be modified eg. SSO_USERNAME=youraccount
SSO_PASSWORD=yourpassword

But…

So far so good, but currently it is now working. According to Oracle Support this is a known issue and there is the Bug 12372706: WGET SCRIPTS FROM MOS FAIL IN PRODUCTION.

To workaround each file has to be downloaded manually with wget.

wget --http-user=username --http-password=password --no-check-certificate \ --output-document=filename "paste the above copied address here in quotes"

The URL’s can be copied from the download dialog above. If more than just one patch have to be downloaded wget can be put in a for loop which get’s the URL’s from a text file.

Download URL’s

Text File with Patch URL’s
#Linux x86-64 https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_1of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_1of7.zip https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_2of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_2of7.zip https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_3of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_3of7.zip https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_4of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_4of7.zip https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_5of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_5of7.zip https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_6of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_6of7.zip https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_7of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_7of7.zip

Download more patch

For loop to download the patch’s:
for i in $(cat download_url.txt|grep -v ^#) do OUTPUT_FILE=$(echo "$i"|cut -d= -f3) echo "download $OUTPUT_FILE from '$i'" >> $LOGFILE 2>&1 wget --http-user=MOS_USER --http-password=MOS_PASSWORD --no-check-certificate \ -O $OUTPUT_FILE "$i" >> wget_logfile.log 2>&1 done

MOS Download Script

I’ve put everything in a small script. To download the patch a text with the download URL’s have to be specified

mos_download_url.sh -h INFO : Usage, mos_download_url.sh [-hv] INFO : -h Usage (this message) INFO : -u MOS user account INFO : -p MOS password INFO : -f Text file with download url INFO : Logfile : mos_download_url.sh-04-22-11-1422.log

Run the script with nohup on a stage server to download a few patchs.
nohup mos_download_url.sh -u me@domain.com -p secret -f download_url.txt &

The mos_download_url.sh script can be downloaded in the script section of OraDBA or direct ( mos_download_url.sh).