Data … as usual

All things about data by Laurent Leturgez

Category Archives: tools

Keep in touch with My Oracle Support updates

2 Comments Posted by on July 2, 2015

This post is related to My Oracle Support (MOS) and one of the features every DBA has to configure is the Hot Topic email.

This feature will send you an email every day ou week (depending on how you configure it) with all the new stuff published or updated on MOS (Knowledge base article, bugs, alerts …).

Of course, you can filter on the products you want to subscribe and select the number of items you want to receive by category.

To configure this fonction, log in to MOS with your account and click on the “settings” tab, ,the select the “Hot Topic Email” link on the left … see in the screenshot below (green box):

hot_topics

Then, you will see the “hot topics” part and here you can configure it:

  • On the first part (1), you can configure the frequency of the mail and its format. You can also configure the content you want to include in this mail: favorites and Service requests
  • Next, on the second part (2), which is for me the most important part, you will select the products you want to subscribe and what kind of information you want to receive (knowledge articles, alerts, bugs etc.)
  • Finally, on the third part (3), you will choose the number of items the mail will includes. In the previous screenshot, I will receive 25 bugs (for all the products I selected), 25 knowledge articles etc. (Usually the bug and knowledge articles are the most important parts… yes, there are a lot of bugs updated every day 😉 )

As a result, I receive this kind of email every day:

Alerts

Alert Product Area Last Updated
Bug 20830449 – Disk corruption possible when a disk media error occurs while synchronous I/O is performed Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:44 GMT+01:00

 

Bugs

Bug Product Area Bug ID Last Updated
ORACLE PROCESS RUNNING OUT OF OS KERNEL I/O RESOURCES EVEN AFTER APPLY 6343215 Oracle Database – Enterprise Edition 7523755 Sat, 27 Jun 2015 13:10 GMT+01:00
MESSAGES IN AQ$_ALERT_QT_E ARE NOT BEING PURGED IN A RAC CLUSTER Oracle Database – Enterprise Edition 19904389 Sat, 27 Jun 2015 13:10 GMT+01:00
ORA-01792 OCCURED WHEN USING RIGHT OUTER JOIN Oracle Database – Enterprise Edition 9256994 Sat, 27 Jun 2015 12:55 GMT+01:00
ORA-600 [KLAPRS_11]/[KLAPRS_12] BEING HIT DURING IMPDP Oracle Database – Enterprise Edition 6944948 Sat, 27 Jun 2015 12:54 GMT+01:00
ORA-00600 [KDTIGETROW-2] AFTER UPGRADE TO 11.2.0.3 BUNDLE 22 Oracle Database – Enterprise Edition 17343797 Sat, 27 Jun 2015 12:52 GMT+01:00
MERGE SQL WITH BOTH AN INSERT AND UPDATE AND LOGGING ENABLED CAN CAUSE INT. ERR Oracle Database – Enterprise Edition 17397545 Sat, 27 Jun 2015 12:52 GMT+01:00
WRONG PRECISION RETURNED FOR QUERY AFTER DB UPGRADE TO 12.1.0.2 Oracle Database – Enterprise Edition 21241579 Sat, 27 Jun 2015 12:49 GMT+01:00
NUMA MESSAGES IN THE ALERT LOG AFTER 11.2.0.4 UPGRADE Oracle Database – Enterprise Edition 17475024 Sat, 27 Jun 2015 12:49 GMT+01:00
ORA-600 [QKSVCREPLACEVC0] USING SQL TUNING ADVISOR Oracle Database – Enterprise Edition 17401718 Sat, 27 Jun 2015 12:45 GMT+01:00
ORA-600 [QKSVCREPLACEVC0] WHEN AUTOMATIC SQL TUNING ADVISOR EXECUTED Oracle Database – Enterprise Edition 16491690 Sat, 27 Jun 2015 12:45 GMT+01:00
ORA-00600 [QKSVCREPLACEVC0] Oracle Database – Enterprise Edition 13959984 Sat, 27 Jun 2015 12:45 GMT+01:00
INSTANCE CRASHES WITH ORA-600 [KJBRWRDONE:SC2] ON LMS Oracle Database – Enterprise Edition 17027916 Sat, 27 Jun 2015 12:35 GMT+01:00
WITH 11.2.0.4, THE CAPTURE MECHANISM STARTS UP TOO MANY PARALLEL QUERY SERVERS Oracle Database – Enterprise Edition 19587324 Sat, 27 Jun 2015 12:28 GMT+01:00
WAIT DEPENDENCY ON APPLY INSERT AFTER UPGRADE TO 11.2.0.4 Oracle Database – Enterprise Edition 19442102 Sat, 27 Jun 2015 12:28 GMT+01:00
FAILED TO RAISE ORA-1 FOR PK UPDATE WHEN CONSTRAINT=IMMEDIATE Oracle Database – Enterprise Edition 19440386 Sat, 27 Jun 2015 12:28 GMT+01:00
INTEGRATED REPLICAT INVALIDATES DEPENDENT PACKAGES RESULTING IN AN ORA-4068 Oracle Database – Enterprise Edition 19277336 Sat, 27 Jun 2015 12:28 GMT+01:00
PGA MEMORY LEAK ON APPLY SERVER ON KOH-KGHU CALL – KNGLXRCOL Oracle Database – Enterprise Edition 18973548 Sat, 27 Jun 2015 12:28 GMT+01:00
LOGMINER DDL TRACKING NOT SCALABLE, SESSIONS BLOCK EACHOTHER DURING DDL APPLY Oracle Database – Enterprise Edition 16674686 Sat, 27 Jun 2015 12:28 GMT+01:00
MALFORMED KTU-KRVMISC TRANSACTION FINALIZATION MARKER Oracle Database – Enterprise Edition 14705949 Sat, 27 Jun 2015 12:28 GMT+01:00
ORA 7445 [__INTEL_SSSE3_REP_MEMCPY] WITH CLUSTER TABLE CREATION Oracle Database – Enterprise Edition 21041573 Sat, 27 Jun 2015 12:27 GMT+01:00
ORA-600 [17285] RUNNING DIFFERENT APPLICATION PACKAGES Oracle Database – Enterprise Edition 19475971 Sat, 27 Jun 2015 12:09 GMT+01:00
WRONG RESULTS USING FUNCTION BASED INDEX WITH LOWER FUNCTION Oracle Database – Enterprise Edition 18550648 Sat, 27 Jun 2015 12:06 GMT+01:00
PGA MEMORY SPIKE WHEN CONSISTENTLY RUNNING A JAVA STORED PROCEDURE Oracle Database – Enterprise Edition 20806625 Sat, 27 Jun 2015 12:06 GMT+01:00
LOADJAVA FAILS WITH ORA-12154 WHEN USING NAME-VALUE PAIR IN CONNECTION STRING. Oracle Database – Enterprise Edition 21072270 Sat, 27 Jun 2015 12:05 GMT+01:00
ORA-600 [KSSADD: NULL PARENT] DURING HIGH DATABASE ACTIVITY Oracle Database – Enterprise Edition 16590736 Sat, 27 Jun 2015 11:50 GMT+01:00

 

Knowledge Articles

Knowledge Article Product Area Last Updated
Does Weblogic Server support SAML2.0 Single Logout Protocol? Oracle WebLogic Server Sat, 27 Jun 2015 02:52 GMT+01:00
How to use the DBMS_SQLPA (SQL Performance Analyzer) API to test database upgrade using CONVERT SQLSET Oracle Database – Enterprise Edition Sat, 27 Jun 2015 02:37 GMT+01:00
12c ACFS Configuration on ASM Flex Architecture (White Paper) Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:37 GMT+01:00
Best Practices and Recommendations for RAC databases using very large SGA (e.g. 100 GB) Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:24 GMT+01:00
Receive “There are Issues with the Configuration of The Static Files…” After Upgrading to APEX 5.0 Oracle Application Express (formerly HTML DB) Oracle REST Data Services Oracle HTTP Server Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:19 GMT+01:00
Cluster Health Monitor (CHM) FAQ Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:16 GMT+01:00
ODA: “oakcli show repo” Does Not Show Any Repository After Restart Oracle Database Appliance Software Oracle Database Appliance X3-2 Fri, 26 Jun 2015 22:34 GMT+01:00
NTP leap second event causing Oracle Clusterware node reboot Oracle Database – Enterprise Edition Fri, 26 Jun 2015 22:29 GMT+01:00
ODA (Oracle Database Appliance): Leap Second adjustment impact Oracle Database Appliance Oracle Database Appliance Software Fri, 26 Jun 2015 22:01 GMT+01:00
Bug 20830449 – Disk corruption possible when a disk media error occurs while synchronous I/O is performed Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:44 GMT+01:00
Exadata 12.1.2.1.2 release and patch (20748218) Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:40 GMT+01:00
11gR2 Grid Infrastructure Does not Use ulimit Setting Appropriately Oracle Database – Standard Edition Oracle Database – Enterprise Edition Fri, 26 Jun 2015 18:33 GMT+01:00
Exadata Database Machine and Exadata Storage Server Supported Versions Oracle Exadata Hardware Exadata Database Machine X2-2 Hardware Oracle Platinum Services Oracle Exadata Storage Server Software Oracle Database – Enterprise Edition Fri, 26 Jun 2015 18:32 GMT+01:00
Disable SSLv3 And Enable TLSv2 / TLSv1.1/ TLSv1.2 Oracle WebLogic Server Fri, 26 Jun 2015 18:30 GMT+01:00
ORA-7445 Troubleshooting Tool Oracle Database – Enterprise Edition Fri, 26 Jun 2015 17:13 GMT+01:00
ORA-600 Troubleshooting Tool Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:58 GMT+01:00
hp-ux: Database Instance Startup is Slow in 11gR2 as DBW0 Has High Wait “Disk file operations I/O” Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:49 GMT+01:00
ODA (Oracle Database Appliance): Unable to Delete VM Oracle Database Appliance Software Fri, 26 Jun 2015 16:49 GMT+01:00
WLS Admin Server Deadlock Detected Waiting To Acquire Lock Java.util.concurrent.CopyOnWriteArrayList Oracle WebLogic Server Fri, 26 Jun 2015 16:43 GMT+01:00
[WLS-10.3.6] -JAXB Marshaller Returns The Class Name Instead Of The Objects In The List Oracle WebLogic Server Fri, 26 Jun 2015 16:14 GMT+01:00
Oracle Advance Security Licence Components Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:11 GMT+01:00
Steps to Implement Address Windowing Extensions (AWE) / VLM on 32-bit Windows Platforms Oracle Database – Enterprise Edition Fri, 26 Jun 2015 15:50 GMT+01:00
DBMS_AUDIT_MGMT does not release the space occupied by LOB segment Oracle Database – Standard Edition Fri, 26 Jun 2015 15:47 GMT+01:00
WLS 10.3.6.0 Wtc-l10n.jar Missing Oracle WebLogic Server Fri, 26 Jun 2015 15:26 GMT+01:00
Egypt cancels DST in 2015 – Impact on Oracle RDBMS Oracle Database – Standard Edition Oracle Database – Enterprise Edition Fri, 26 Jun 2015 15:21 GMT+01:00

 

 

My Oracle Support, Oracle, tools

Linux monitoring of oracle 12c multi-threaded instances

Leave a comment Posted by on March 14, 2014

Oracle 12c comes with a new feature: multithreaded server. In summary, main processes like real time scheduled processes (vktm, lms), or main processes like pmon or dbwn continue to run as processes. For other ones (lgwr, mmon, server processes etc.), they run now in a thread.

This feature has been developed to optimize oracle to be run on new processors with many core and many threads per core (for example SPARC T Processors), but the DBA will have to change many methods he use to analyze problems in a multi-threaded server.

If for some problems, you usually analyze the OS side, top, ps, and other tools have to be used in a different way. Let’s see different tools that can be used to analyze processes and thread in linux (Tools mentioned here has been tested with Oracle Enterprise Linux 6).

For all example above, I used an orcl instance which run in a multi-threaded configuration

  • ps

If I run a simple ps under my config, there are only 6 processes:

[oracle@oel64-12c ~]$ ps -ef | grep [o]rcl
oracle    9871     1  0 21:02 ?        00:00:00 ora_pmon_orcl
oracle    9873     1  0 21:02 ?        00:00:00 ora_psp0_orcl
oracle    9878     1  5 21:02 ?        00:01:35 ora_vktm_orcl
oracle    9882     1  0 21:02 ?        00:00:02 ora_u004_orcl
oracle    9888     1  0 21:02 ?        00:00:11 ora_u005_orcl
oracle    9894     1  0 21:02 ?        00:00:00 ora_dbw0_orcl
 If I want to print all threads that run in these processes, I can run this command:
[oracle@oel64-12c ~]$ ps -eLo pid,pcpu,tid,user,comm,cmd | sed -n -e '1p' -e '/orcl/p'
  PID %CPU   TID USER     COMMAND         CMD
 9871  0.0  9871 oracle   ora_pmon_orcl   ora_pmon_orcl
 9873  0.0  9873 oracle   ora_psp0_orcl   ora_psp0_orcl
 9878  5.2  9878 oracle   ora_vktm_orcl   ora_vktm_orcl
 9882  0.0  9882 oracle   ora_scmn_orcl   ora_u004_orcl
 9882  0.0  9883 oracle   oracle          ora_u004_orcl
 9882  0.0  9884 oracle   ora_gen0_orcl   ora_u004_orcl
 9882  0.0  9885 oracle   ora_mman_orcl   ora_u004_orcl
 9882  0.0  9891 oracle   ora_dbrm_orcl   ora_u004_orcl
 9882  0.0  9895 oracle   ora_lgwr_orcl   ora_u004_orcl
 9882  0.0  9896 oracle   ora_ckpt_orcl   ora_u004_orcl
 9882  0.0  9897 oracle   ora_lg00_orcl   ora_u004_orcl
 9882  0.0  9898 oracle   ora_lg01_orcl   ora_u004_orcl
 9882  0.0  9899 oracle   ora_smon_orcl   ora_u004_orcl
 9882  0.0  9901 oracle   ora_lreg_orcl   ora_u004_orcl
 9888  0.0  9888 oracle   ora_scmn_orcl   ora_u005_orcl
 9888  0.0  9889 oracle   oracle          ora_u005_orcl
 9888  0.0  9890 oracle   ora_diag_orcl   ora_u005_orcl
 9888  0.0  9892 oracle   ora_dia0_orcl   ora_u005_orcl
 9888  0.0  9900 oracle   ora_reco_orcl   ora_u005_orcl
 9888  0.0  9902 oracle   ora_mmon_orcl   ora_u005_orcl
 9888  0.0  9903 oracle   ora_mmnl_orcl   ora_u005_orcl
 9888  0.0  9904 oracle   ora_d000_orcl   ora_u005_orcl
 9888  0.0  9905 oracle   ora_s000_orcl   ora_u005_orcl
 9888  0.0  9906 oracle   ora_n000_orcl   ora_u005_orcl
 9888  1.3  9931 oracle   oracle_9931_orc ora_u005_orcl
 9888  0.0  9932 oracle   ora_tmon_orcl   ora_u005_orcl
 9888  0.0  9933 oracle   ora_tt00_orcl   ora_u005_orcl
 9888  0.0  9934 oracle   ora_smco_orcl   ora_u005_orcl
 9888  0.0  9938 oracle   ora_fbda_orcl   ora_u005_orcl
 9888  0.0  9939 oracle   ora_aqpc_orcl   ora_u005_orcl
 9888  0.0  9944 oracle   ora_p000_orcl   ora_u005_orcl
 9888  0.0  9945 oracle   ora_p001_orcl   ora_u005_orcl
 9888  0.0  9946 oracle   ora_p002_orcl   ora_u005_orcl
 9888  0.0  9947 oracle   ora_p003_orcl   ora_u005_orcl
 9888  0.0  9948 oracle   ora_p004_orcl   ora_u005_orcl
 9888  0.0  9949 oracle   ora_p005_orcl   ora_u005_orcl
 9888  0.0  9950 oracle   ora_p006_orcl   ora_u005_orcl
 9888  0.0  9951 oracle   ora_p007_orcl   ora_u005_orcl
 9888  0.0  9952 oracle   ora_cjq0_orcl   ora_u005_orcl
 9888  0.0  9996 oracle   ora_qm02_orcl   ora_u005_orcl
 9888  0.0  9998 oracle   ora_q002_orcl   ora_u005_orcl
 9888  0.0  9999 oracle   ora_q003_orcl   ora_u005_orcl
 9888  0.0 16009 oracle   ora_w000_orcl   ora_u005_orcl
 9894  0.0  9894 oracle   ora_dbw0_orcl   ora_dbw0_orcl
16414  0.0 16414 oracle   sed             sed -n -e 1p -e /orcl/p
 First column is the PID, second column is the CPU percent burn by the thread, third column is the thread Id, next column is the thread owner, the second last column is the oracle thread name and the last is the process name.
With ps, you can have a static view and possibly identify problematic processes
  • top

With top, you can see you processes or threads in a more dynamic fashion. Top option used to see threads is -H, but you have to mention which processes you want to analyze with -p parameter followed by pids. The main drawback of this command is that -p is limited to 20 pids but for a mid size multi-threaded instance, it’s ok.

[oracle@oel64-12c ~]$ top -p $(pgrep -d',' orcl$) -H
top - 21:55:37 up 1 day,  2:03,  6 users,  load average: 1.10, 1.04, 1.13
Tasks:  43 total,   0 running,  43 sleeping,   0 stopped,   0 zombie
Cpu0  : 25.8%us, 46.5%sy,  0.0%ni, 14.5%id, 10.3%wa,  0.0%hi,  3.0%si,  0.0%st
Cpu1  : 16.3%us, 46.8%sy,  0.0%ni, 28.9%id,  7.8%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   4055296k total,  3052640k used,  1002656k free,    20360k buffers
Swap:  8388604k total,  1475216k used,  6913388k free,  2302200k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 9878 oracle    -2   0 1489m  17m  15m S  6.7  0.4   3:03.07 ora_vktm_orcl
 9892 oracle    20   0 3457m 340m 252m S  0.5  8.6   0:01.27 ora_dia0_orcl
 9902 oracle    20   0 3457m 340m 252m S  0.5  8.6   0:02.00 ora_mmon_orcl
 9871 oracle    20   0 1489m  21m  19m S  0.0  0.5   0:00.33 ora_pmon_orcl
 9873 oracle    20   0 1489m  17m  15m S  0.0  0.4   0:01.17 ora_psp0_orcl
 9882 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.07 ora_scmn_orcl
 9883 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.00 oracle
 9884 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.25 ora_gen0_orcl
 9885 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.25 ora_mman_orcl
 9891 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.24 ora_dbrm_orcl
 9895 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.33 ora_lgwr_orcl
 9896 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:01.08 ora_ckpt_orcl
 9897 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.12 ora_lg00_orcl
 9898 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.03 ora_lg01_orcl
 9899 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.07 ora_smon_orcl
 9901 oracle    20   0 2594m 1.2g 1.2g S  0.0 31.2   0:00.13 ora_lreg_orcl
 9888 oracle    20   0 3457m 340m 252m S  0.0  8.6   0:00.40 ora_scmn_orcl
 9889 oracle    20   0 3457m 340m 252m S  0.0  8.6   0:00.01 oracle
  • pidstat

pidstat is a command which appears in OEL6. It runs like a vmstat or mpstat with an interval and a counter, but it gives information of how evolve cpu, io, memory consumption for a specific process.

For example, to see cpu consumption every second for the process with pid 9888

[oracle@oel64-12c ~]$ pidstat -p 9888 -u 1
Linux 2.6.39-400.17.1.el6uek.x86_64 (oel64-12c.localdomain)     01/14/2014      _x86_64_        (2 CPU)

10:03:14 PM       PID    %usr %system  %guest    %CPU   CPU  Command
10:03:15 PM      9888    0.00    0.00    0.00    0.00     0  ora_scmn_orcl
10:03:16 PM      9888    1.00    0.00    0.00    1.00     1  ora_scmn_orcl
10:03:17 PM      9888    0.00    0.00    0.00    0.00     1  ora_scmn_orcl
10:03:18 PM      9888    1.00    1.00    0.00    2.00     1  ora_scmn_orcl
Please note that the process name is ora_u005_orcl but it’s printed with the command name which is, in fact, the thread name.

So if you want to see every thread in this process, you need to use -t option:

[oracle@oel64-12c ~]$ pidstat -p 9888 -u -t 1
Linux 2.6.39-400.17.1.el6uek.x86_64 (oel64-12c.localdomain)     01/14/2014      _x86_64_        (2 CPU)

10:08:42 PM      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
10:08:43 PM      9888         -    0.00    0.00    0.00    0.00     1  ora_scmn_orcl
10:08:43 PM         -      9888    0.00    0.00    0.00    0.00     1  |__ora_scmn_orcl
10:08:43 PM         -      9889    0.00    0.00    0.00    0.00     1  |__oracle
10:08:43 PM         -      9890    0.00    0.00    0.00    0.00     0  |__ora_diag_orcl
10:08:43 PM         -      9892    0.00    0.00    0.00    0.00     0  |__ora_dia0_orcl
10:08:43 PM         -      9900    0.00    0.00    0.00    0.00     0  |__ora_reco_orcl
10:08:43 PM         -      9902    0.00    0.00    0.00    0.00     1  |__ora_mmon_orcl
10:08:43 PM         -      9903    0.00    0.00    0.00    0.00     0  |__ora_mmnl_orcl
10:08:43 PM         -      9904    0.00    0.00    0.00    0.00     0  |__ora_d000_orcl
10:08:43 PM         -      9905    0.00    0.00    0.00    0.00     0  |__ora_s000_orcl
10:08:43 PM         -      9906    0.00    0.00    0.00    0.00     0  |__ora_n000_orcl
10:08:43 PM         -      9932    0.00    0.00    0.00    0.00     0  |__ora_tmon_orcl
10:08:43 PM         -      9933    0.00    0.00    0.00    0.00     0  |__ora_tt00_orcl
10:08:43 PM         -      9934    0.00    0.00    0.00    0.00     1  |__ora_smco_orcl
10:08:43 PM         -      9938    0.00    0.00    0.00    0.00     1  |__ora_fbda_orcl
10:08:43 PM         -      9939    0.00    0.00    0.00    0.00     1  |__ora_aqpc_orcl
10:08:43 PM         -      9944    0.00    0.00    0.00    0.00     0  |__ora_p000_orcl
10:08:43 PM         -      9945    0.00    0.00    0.00    0.00     0  |__ora_p001_orcl
10:08:43 PM         -      9946    0.00    0.00    0.00    0.00     1  |__ora_p002_orcl
10:08:43 PM         -      9947    0.00    0.00    0.00    0.00     0  |__ora_p003_orcl
10:08:43 PM         -      9948    0.00    0.00    0.00    0.00     0  |__ora_p004_orcl
10:08:43 PM         -      9949    0.00    0.00    0.00    0.00     1  |__ora_p005_orcl
10:08:43 PM         -      9950    0.00    0.00    0.00    0.00     1  |__ora_p006_orcl
10:08:43 PM         -      9951    0.00    0.00    0.00    0.00     1  |__ora_p007_orcl
10:08:43 PM         -      9952    0.00    0.00    0.00    0.00     1  |__ora_cjq0_orcl
10:08:43 PM         -      9996    0.00    0.00    0.00    0.00     0  |__ora_qm02_orcl
10:08:43 PM         -      9998    0.00    0.00    0.00    0.00     1  |__ora_q002_orcl
10:08:43 PM         -      9999    0.00    0.00    0.00    0.00     0  |__ora_q003_orcl
10:08:43 PM         -     16009    0.00    0.00    0.00    0.00     1  |__ora_w000_orcl
10:08:43 PM         -     21462    0.00    1.00    0.00    1.00     1  |__ora_vkrm_orcl
10:08:43 PM         -     22117    0.00    0.00    0.00    0.00     1  |__ora_w001_orcl
10:08:43 PM         -     22128    0.00    0.00    0.00    0.00     0  |__ora_w002_orcl
10:08:43 PM         -     22689    0.00    0.00    0.00    0.00     1  |__ora_w003_orcl
10:08:43 PM         -     22703    0.00    0.00    0.00    0.00     0  |__ora_w004_orcl
10:08:43 PM         -     22713    0.00    0.00    0.00    0.00     0  |__ora_w005_orcl
There are other interesting options to monitor IO (-d), page faults and memory (-r), CPU utilization seen above (-u), switching activities (-w).
For example:
[oracle@oel64-12c ~]$ pidstat -p 9888 -w -t 1
Linux 2.6.39-400.17.1.el6uek.x86_64 (oel64-12c.localdomain)     01/14/2014      _x86_64_        (2 CPU)

10:57:54 PM      TGID       TID   cswch/s nvcswch/s  Command
10:57:55 PM      9888         -      1.00      1.00  ora_scmn_orcl
10:57:55 PM         -      9888      1.00      1.00  |__ora_scmn_orcl
10:57:55 PM         -      9889      0.00      0.00  |__oracle
10:57:55 PM         -      9890      1.00      0.00  |__ora_diag_orcl
10:57:55 PM         -      9892      1.00      0.00  |__ora_dia0_orcl
10:57:55 PM         -      9900      1.00      0.00  |__ora_reco_orcl
10:57:55 PM         -      9902      1.00      0.00  |__ora_mmon_orcl
10:57:55 PM         -      9903      1.00      1.00  |__ora_mmnl_orcl
10:57:55 PM         -      9904      1.00      0.00  |__ora_d000_orcl
10:57:55 PM         -      9905      1.00      0.00  |__ora_s000_orcl
10:57:55 PM         -      9906      1.00      0.00  |__ora_n000_orcl
10:57:55 PM         -      9932      1.00      0.00  |__ora_tmon_orcl
10:57:55 PM         -      9933      1.00      0.00  |__ora_tt00_orcl
10:57:55 PM         -      9934      1.00      1.00  |__ora_smco_orcl
10:57:55 PM         -      9938      1.00      0.00  |__ora_fbda_orcl
10:57:55 PM         -      9939      1.00      0.00  |__ora_aqpc_orcl
10:57:55 PM         -      9944      0.00      0.00  |__ora_p000_orcl
10:57:55 PM         -      9945      0.00      0.00  |__ora_p001_orcl
10:57:55 PM         -      9946      0.00      0.00  |__ora_p002_orcl
10:57:55 PM         -      9947      0.00      0.00  |__ora_p003_orcl
10:57:55 PM         -      9948      0.00      0.00  |__ora_p004_orcl
10:57:55 PM         -      9949      0.00      0.00  |__ora_p005_orcl
10:57:55 PM         -      9950      0.00      0.00  |__ora_p006_orcl
10:57:55 PM         -      9951      0.00      0.00  |__ora_p007_orcl
10:57:55 PM         -      9952      1.00      0.00  |__ora_cjq0_orcl
10:57:55 PM         -      9996      0.00      0.00  |__ora_qm02_orcl
10:57:55 PM         -      9998      0.00      0.00  |__ora_q002_orcl
10:57:55 PM         -      9999      1.00      0.00  |__ora_q003_orcl
10:57:55 PM         -     21462     96.00      3.00  |__ora_vkrm_orcl
10:57:55 PM         -     22713      1.00      0.00  |__ora_w005_orcl
10:57:55 PM         -     29708      0.00      0.00  |__ora_q001_orcl
10:57:55 PM         -     29709      0.00      0.00  |__oracle_29709_or
10:57:55 PM         -     26975      1.00      0.00  |__ora_w004_orcl
  • gdb (for debug)

If you want to trace system calls made by threads, you can use linux debugger (gdb). I don’t have a deep knowledge of gdb, but you can attach gdb to a process with the -p option.

[oracle@oel64-12c ~]$ gdb -p 9888
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-60.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 9888

.../...
After this, you have a command which prints threads information (LWP (for Light Weight Process ???) indicates the Thread Id:
(gdb) info threads
  31 Thread 0x7f5a89ff6700 (LWP 29709)  0x0000003abe00e75d in read () from /lib64/libpthread.so.0  <<< my session is located here and is waiting for a command (read syscall)
  30 Thread 0x7f5a81ff2700 (LWP 29708)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  29 Thread 0x7f5b10beb700 (LWP 9889)  0x0000003abdcdf343 in poll () from /lib64/libc.so.6
  28 Thread 0x7f5b0ea2a700 (LWP 9890)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  27 Thread 0x7f5b07fff700 (LWP 9892)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  26 Thread 0x7f5afbfff700 (LWP 9900)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  25 Thread 0x7f5af3fff700 (LWP 9902)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  24 Thread 0x7f5aebfff700 (LWP 9903)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  23 Thread 0x7f5ae3fff700 (LWP 9904)  0x0000003abdce9163 in epoll_wait () from /lib64/libc.so.6
  22 Thread 0x7f5adbfff700 (LWP 9905)  0x0000003abdce9163 in epoll_wait () from /lib64/libc.so.6
  21 Thread 0x7f5ad3fff700 (LWP 9906)  0x0000003abdce9163 in epoll_wait () from /lib64/libc.so.6
  20 Thread 0x7f5acbfff700 (LWP 9932)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  19 Thread 0x7f5ac3fff700 (LWP 9933)  0x0000003abe00ef3d in nanosleep () from /lib64/libpthread.so.0
  18 Thread 0x7f5ab3fff700 (LWP 9934)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  17 Thread 0x7f5aabfff700 (LWP 9938)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  16 Thread 0x7f5aa3fff700 (LWP 9939)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  15 Thread 0x7f5a99ffe700 (LWP 9944)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  14 Thread 0x7f5a97ffd700 (LWP 9945)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  13 Thread 0x7f5a95ffc700 (LWP 9946)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  12 Thread 0x7f5a93ffb700 (LWP 9947)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  11 Thread 0x7f5a91ffa700 (LWP 9948)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  10 Thread 0x7f5a8fff9700 (LWP 9949)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  9 Thread 0x7f5a8dff8700 (LWP 9950)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  8 Thread 0x7f5a8bff7700 (LWP 9951)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  7 Thread 0x7f5a9bfff700 (LWP 9952)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  6 Thread 0x7f5a83ff3700 (LWP 9996)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  5 Thread 0x7f5a87ff5700 (LWP 9998)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  4 Thread 0x7f5a85ff4700 (LWP 9999)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  3 Thread 0x7f5abbfff700 (LWP 21462)  0x0000003abe00ef3d in nanosleep () from /lib64/libpthread.so.0
  2 Thread 0x7f5a79fee700 (LWP 22713)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
* 1 Thread 0x7f5b10f2a9e0 (LWP 9888)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
 Next, you can select a specific thread with the gdb “thread” command:
(gdb) thread 31
[Switching to thread 31 (Thread 0x7f5a89ff6700 (LWP 29709))]#0  0x0000003abe00e75d in read () from /lib64/libpthread.so.0
(gdb) info threads
* 31 Thread 0x7f5a89ff6700 (LWP 29709)  0x0000003abe00e75d in read () from /lib64/libpthread.so.0
  30 Thread 0x7f5a81ff2700 (LWP 29708)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  29 Thread 0x7f5b10beb700 (LWP 9889)  0x0000003abdcdf343 in poll () from /lib64/libc.so.6
  28 Thread 0x7f5b0ea2a700 (LWP 9890)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  27 Thread 0x7f5b07fff700 (LWP 9892)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
  26 Thread 0x7f5afbfff700 (LWP 9900)  0x0000003abdceb22a in semtimedop () from /lib64/libc.so.6
 .../...
Next, you can use breakpoints, watchpoint etc. to debug oracle calls etc.
If you are interested by tracing oracle system calls with gdb, Frits Hoogland have written many articles on this subject:
12c, Administration, Linux, tools

Tools to aggregate Oracle CRS logfiles in one file, filter it, sort it, and display it.

9 Comments Posted by on September 11, 2013

If you already faced RAC problems, you had to analyze log files from the entry points which are the alert.log files.

But as Oracle clusterware logs its events in many logfiles (usually one per process), and you have a logfile directory on each server, it can be difficult to detect the causes, and specially the root cause of a specific problem.

Recently I created a little perl script that will help me for this purpose. This script is named “crs_log_aggregator.pl” and is available for download at this URL : https://app.box.com/s/xi3h7bpssrqtkp55q43c.

This script works like this. As input, it takes one or n directories to analyze recursively. Those directories are containing all crs log files of all cluster nodes you want to analyze.

For each directory, it parses each log files (except the alert.log) and each history log files (with file extension: l??). Parsing will get the timestamp mentioned in the read line, if there’s no timestamp (because message is written in many lines for example), the perl script will use the preceding timestamp. (If there were no timestamp for example, in the file header, it will flag the timestamp as ‘N/A’). Every analyzed line in the log file, is completed by its origin represented by the relative file path.

As a result, an analyzed line, is represented in the output file by three parts:

  • the timestamp of the message
  • the origin file of the message
  • the message

As a separator, I used a “<SEP>” tag.

So at this step, each line is recorded in a single unsorted file. If a filter expression has been given to the program as input, which is highly recommended, the filter is applied during the parsing process.

Next, the program will sort the output file by timestamp and will produce a single output file which is now filtered (or not) and this file can be analyzed.

Well, let’s see how it works:

For example, let’s consider a two nodes RAC (hostnames are rac1 and rac2).

As alert.log files are not analyzed, you need to analyze them before to see when the problem occurs.

In my case, I want to analyze 2 days: 2013-05-30 and 2013-05-31.

So first step is to copy is a filesystem, or on your desktop, all the logfiles.

I recommend to put in a single directory all the directories related to a single hostname. Here, I have two directories coming from both cluster nodes ($GRID_HOME/log) :

[oracle@rac1 aggregator]$ tree -d .
.
|-- rac1
|   |-- acfslog
|   |-- acfsrepl
|   |-- acfsreplroot
|   |-- acfssec
|   |-- admin
|   |-- agent
|   |   |-- crsd
|   |   |   |-- oraagent_oracle
|   |   |   |-- orarootagent_root
|   |   |   `-- scriptagent_oracle
|   |   `-- ohasd
|   |       |-- oraagent_oracle
|   |       |-- oracssdagent_root
|   |       |-- oracssdmonitor_root
|   |       `-- orarootagent_root
|   |-- client
|   |-- crflogd
|   |-- crfmond
|   |-- crsd
|   |-- cssd
|   |-- ctssd
|   |-- cvu
|   |   |-- cvulog
|   |   `-- cvutrc
|   |-- diskmon
|   |-- evmd
|   |-- gipcd
|   |-- gnsd
|   |-- gpnpd
|   |-- mdnsd
|   |-- ohasd
|   |-- racg
|   |   |-- racgeut
|   |   |-- racgevtf
|   |   `-- racgmain
|   `-- srvm
`-- rac2
    |-- acfslog
    |-- acfsrepl
    |-- acfsreplroot
    |-- acfssec
    |-- admin
    |-- agent
    |   |-- crsd
    |   |   |-- oraagent_oracle
    |   |   |-- orarootagent_root
    |   |   `-- scriptagent_oracle
    |   `-- ohasd
    |       |-- oraagent_oracle
    |       |-- oracssdagent_root
    |       |-- oracssdmonitor_root
    |       `-- orarootagent_root
    |-- client
    |-- crflogd
    |-- crfmond
    |-- crsd
    |-- cssd
    |-- ctssd
    |-- cvu
    |   |-- cvulog
    |   `-- cvutrc
    |-- diskmon
    |-- evmd
    |-- gipcd
    |-- gnsd
    |-- gpnpd
    |-- mdnsd
    |-- ohasd
    |-- racg
    |   |-- racgeut
    |   |-- racgevtf
    |   `-- racgmain
    `-- srvm

74 directories
[oracle@rac1 aggregator]$ ls -l
total 20
drwxr-xr-x 24 oracle oinstall 4096 Sep 8 14:39 rac1
drwxr-xr-x 24 oracle oinstall 4096 Sep 8 14:00 rac2
If you want some help about this tool, you can use the -h option:
[oracle@rac1 aggregator]$ ./crs_log_aggregator.pl -h

RAC Logs consolidator
---------------------------------------------

Usage

./crs_log_aggregator.pl -c dir1[,dir2,dir3,...,dirN] -o outputfile [-f filter]

[MANDATORY]: -c dir1,dir2,...,dirN
                 comma separated values of source directories to analyze
[MANDATORY]: -o outputfile
                 unique sorted and filtered output file
[OPTIONAL] : -f filter
                 regex filter to apply
[OPTIONAL] : -h
                 print this help

Examples:
$ ./crs_log_aggregator.pl -c data/ -o outfile
         will sort without any filter all log files (*.log, *.l??) recursively found in the data directory. outfile is produced in the current directory as a result.
$ ./crs_log_aggregator.pl -c data/rac1/cssd,data/rac1/crsd -o outfile
         will sort without any filter only the content of data/rac1/cssd,data/rac1/crsd  directories.
$ ./crs_log_aggregator.pl -c data/ -o outfile_filtered -f '2012-10-26|2012-10-27'
         will sort with a filter (regex) that will filter 2 days 2012-10-26 and 2012-10-27.

        more information on perl regex: http://perldoc.perl.org/perlre.html
Next, I will launch the program with the good options:
[oracle@rac1 aggregator]$ ./crs_log_aggregator.pl -c ./rac1,./rac2 -o output_file -f '2013-05-30|2013-05-31'

---------Alert.log : ./rac1/alertrac1.log excluded from analyze

---------Alert.log : ./rac2/alertrac2.log excluded from analyze

[oracle@rac1 aggregator]$ ls -lh output_file
-rw-r--r-- 1 oracle oinstall 243M Sep  8 15:16 output_file
Please note that, if an alert.log file is found, it will be excluded and mentioned in the standard output.
I highly recommend you to use a filter to reduce the amount of data to sort (It can burn lot of CPU and memory). The -f option is use to do this, and takes a regexp style filter (it ‘s used directly in the perl script, so if you are not aware with regexp used in perl script, you can have a look at this URL: http://perldoc.perl.org/perlre.html).
As a result, you have this kind of messages in the output file:
2013-05-31 09:14:59.573<SEP>../rac2/cssd/ocssd.log<SEP> [    CSSD][1111411008]clssgmRPCBroadcast: rpc(0x2a1002a), status(0), sendcount(0), filtered by specific properties:
2013-05-31 09:14:59.573<SEP>../rac2/cssd/ocssd.log<SEP> [    CSSD][1111411008]clssgmFreeRPCIndex: freeing rpc 673
2013-05-31 09:14:59.573<SEP>../rac2/cssd/ocssd.log<SEP> [    CSSD][1111411008]clssgmDeleteGrock: (0x7f3f38637990) grock(EVMDMAIN2) deleted
2013-05-31 09:14:59.576<SEP>../rac1/crfmond/crfmond.log<SEP> [ COMMCRS][1105488192]clsc_connect: (0x7f23b40531f0) no listener at (ADDRESS=(PROTOCOL=ipc)(KEY=rac1DBG_LOGD))
2013-05-31 09:14:59.576<SEP>../rac1/crfmond/crfmond.log<SEP> [ default][1083722048]clsdmc_send error code: -7
2013-05-31 09:14:59.576<SEP>../rac1/crfmond/crfmond.log<SEP> [ default][1083722048]Mond is being shutdown by default
2013-05-31 09:14:59.695<SEP>../rac1/agent/ohasd/orarootagent_root/orarootagent_root.l01<SEP> [ USRTHRD][1102350656] {0:0:2} Thread:[DaemonCheck:crf]Daemon Select Thread exiting
2013-05-31 09:14:59.695<SEP>../rac1/agent/ohasd/orarootagent_root/orarootagent_root.l01<SEP> [ USRTHRD][1102350656] {0:0:2} Thread:[DaemonCheck:crf]Skipping Agent Initiated a check action
2013-05-31 09:14:59.695<SEP>../rac1/agent/ohasd/orarootagent_root/orarootagent_root.l01<SEP> [ USRTHRD][1102350656] {0:0:2} Thread:[DaemonCheck:crf]isRunning is reset to false here
2013-05-31 09:15:00.270<SEP>../rac2/ctssd/octssd.l01<SEP> [    CTSS][1096657216]sclsctss_gvss1: NTP default config file found
2013-05-31 09:15:00.270<SEP>../rac2/ctssd/octssd.l01<SEP> [    CTSS][1096657216]sclsctss_gvss8: Return [0] and NTP status [2].
2013-05-31 09:15:00.270<SEP>../rac2/ctssd/octssd.l01<SEP> [    CTSS][1096657216]ctss_check_vendor_sw: Vendor time sync software is detected. status [2].
2013-05-31 09:15:00.314<SEP>../rac2/ctssd/octssd.l01<SEP> [    CTSS][1109387584]ctsselect_vermon7: Expecting clssgsevGRPPRIV event. Ignoring 2 event.
2013-05-31 09:15:00.314<SEP>../rac2/ctssd/octssd.l01<SEP> [    CTSS][1109387584]ctsselect_vermon7: Expecting clssgsevGRPPRIV event. Ignoring 2 event.
2013-05-31 09:15:00.314<SEP>../rac2/ctssd/octssd.l01<SEP> [    CTSS][1109387584]ctsselect_vermon7: Expecting clssgsevGRPPRIV event. Ignoring 3 event.
2013-05-31 09:15:00.314<SEP>../rac2/ctssd/octssd.l01<SEP> [    CTSS][1109387584]ctsselect_vermon7: Expecting clssgsevGRPPRIV event. Ignoring 1 event.
2013-05-31 09:15:00.345<SEP>../rac2/gipcd/gipcd.l04<SEP> [ CLSINET][1093364032] Returning NETDATA: 1 interfaces
2013-05-31 09:15:00.345<SEP>../rac2/gipcd/gipcd.l04<SEP> [ CLSINET][1093364032] # 0 Interface 'eth2',ip='10.10.10.12',mac='08-00-27-92-f1-98',mask='255.255.255.0',net='10.10.10.0',use='cluster_interconnect'
2013-05-31 09:15:00.354<SEP>../rac2/agent/ohasd/orarootagent_root/orarootagent_root.l01<SEP> [ora.crf][1089526080] {0:0:613} [stop] (:CLSN00108:) clsn_agent::stop }
2013-05-31 09:15:00.354<SEP>../rac2/agent/ohasd/orarootagent_root/orarootagent_root.l01<SEP> [    AGFW][1089526080] {0:0:613} Command: stop for resource: ora.crf 1 1 completed with status: SUCCESS

Now, you can analyze the output file directly, but I have developed another app (in Java). This app is available at this URL: https://app.box.com/s/xi3h7bpssrqtkp55q43c.
To launch it, you need to use java 1.7.
This app will show in a more beautiful interface the result of the output file. Messages that are coming from the same file are colored with the same color:
[oracle@rac1 aggregator]$ java -jar Aggr_visualisator.jar
 Aggr_visualisator
Update:
  • in v0.2, I implemented a filter function. This filter is active on the “Message” column.
  • I implemented a colorization function for the “Message” column.
    • if the term “error” is found, the cell will be colored in red
    • If the term “warn” is found, the cell will be colored in orange
    • and if the term “fail” is found, the cell wil be colored in yellow
v0.2 is available at this URL : https://app.box.com/s/xi3h7bpssrqtkp55q43c
two snapshots of this version below 😉
  • Messages colorization:
Aggr_visualisator_0.2_1
  • Filtering messages (based on a regexp)
Aggr_visualisator_0.2_2
If you have any suggestion to improve these tools, send me an email or a comment. I will study them.
I hope this will help you 😉
11g, 12c, Development, java, perl, RAC, tools

Oracle 12c: queryable Opatch

1 Comment Posted by on August 19, 2013

One of a cool new feature in Oracle 12c is queryable Opatch. Oracle offers a set of tables and a PL/SQL package to query the Oracle Inventory.

With this feature, you can query the inventory to know if a one off patch has been deployed, which components have been deployed etc.

Tables related to this feature are:

  • OPATCH_XML_INV
  • OPATCH_XINV_TAB
  • OPATCH_INST_JOB
  • OPATCH_INST_PATCH

The OPATCH_XINV_TAB table is in fact external table preprocessed with specific script :

SQL> select dbms_metadata.get_ddl('TABLE','OPATCH_XML_INV','SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','OPATCH_XML_INV','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."OPATCH_XML_INV"
   (    "XML_INVENTORY" CLOB
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
      READSIZE 67108864
      preprocessor opatch_script_dir:'qopiprep.bat'
      BADFILE opatch_script_dir:'qopatch_bad.bad'
      LOGFILE opatch_log_dir:'qopatch_log.log'
      FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
        xml_inventory    CHAR(100000000)
      )
        )
      LOCATION
       ( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
       )
    )
   REJECT LIMIT UNLIMITED
The script is located in the path pointed by oracle directory OPATCH_SCRIPT_DIR.
On my server:
[oracle@oel63 ~]$ find $ORACLE_HOME -name "qopiprep.bat"
/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
SQL> select * from dba_directories where directory_name='OPATCH_SCRIPT_DIR';

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH                                     ORIGIN_CON_ID
---------- ------------------------------ -------------------------------------------------- -------------
SYS        OPATCH_SCRIPT_DIR              /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch                1
The content of this script is pasted below:
#!/bin/sh
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH
 
$ORACLE_HOME/OPatch/opatch lsinventory -xml  $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORACLE_HOME/QOpatch/stout.txt
`echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.xml`
echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`
rm $ORACLE_HOME/QOpatch/xml_file.xml
rm $ORACLE_HOME/QOpatch/stout.txt
This is the base of the feature which is accessed through a PL/SQL package DBMS_QOPATCH.
This package has many functions, for example:
  • IS_PATCH_INSTALLED
  • GET_OPATCH_LIST
  • GET_OPATCH_FILES
…
For example, if we execute the GET_OPATCH_BUGS (which provides bugs list in a specific patch or for all patches), the result is a XML output:
SQL> select dbms_qopatch.GET_OPATCH_BUGS from dual;

GET_OPATCH_BUGS
------------------------------------------------------------------------------------------------------------------------------------------------------
<bugInfo><bugs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><bug id="9448798"><UId>FlexibleDataType-957ebac1-ef13-462b-9157-b1e0cb711840</UId
><description>11202 dummy one-off for testing</description></bug></bugs></bugInfo>
This is not very user friendly even with a lightweight output … try to execute DBMS_QOPATCH.GET_OPATCH_LSINVENTORY function, there will be more stuff to analyze ;).
That’s why Oracle delivered a XSLT sheet to transform these outputs, you can get this sheet by executing the DBMS_QOPATCH.GET_OPATCH_XSLT function:
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
----------------------------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/app/oraInventory
--------------------------------------------------------------------------------Installed Top-level
Products (1):
Oracle Database 12c                                    12.1.0.1.0
Installed Products ( 131)

Oracle Database 12c                                         12.1.0.1.0
Sun JDK                                                     1.6.0.37.0
oracle.swd.oui.core.min                                     12.1.0.1.0
Installer SDK Component                                     12.1.0.1.0
Oracle One-Off Patch Installer                              12.1.0.1.0
 .../...
Or with my example (bugs corrected by a specific or all OneOff patches) :
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_BUGS, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_BUGS,DBMS_QOPATCH.GET_OPATCH_XSLT)
----------------------------------------------------------------------------------------------------

  Bugs fixed:
         9448798

Of course, you can write your own XSLT sheet, if you want more information. For example, I want the patch Id and the description of this one:

SQL> select xmltransform(dbms_qopatch.GET_OPATCH_BUGS,
  2  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  3  <xsl:template match="bug">
  4  <xsl:text>Patch Id= </xsl:text><xsl:value-of select="@id"/>
  5  <xsl:text>      Description:  </xsl:text> <xsl:value-of select="description"/>
  6  </xsl:template>
  7  </xsl:stylesheet>')
  8  from dual
  9  /

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_BUGS,'<XSL:STYLESHEETVERSION="1.0"XMLNS:XSL="HTTP://WWW.W3.ORG/
----------------------------------------------------------------------------------------------------
Patch Id= 9448798      Description:  11202 dummy one-off for testing
12c, Administration, Development, PL/SQL, tools

New Views (static and dynamic) in Oracle Database 12c

Leave a comment Posted by on August 13, 2013

Each release of Oracle Database comes with many new views.

Those views are detailed above, some of them are documented, others not.

This list has been automatically generated between 12.1.0.1 and 11.2.0.3.0. I have tried to be as precise as possible by searching every link in the entire documentation. If you see a unlinked view, please comment the post and give me the missing URL.

  • Dynamic Performance views (V$)

V$AQ_BACKGROUND_COORDINATOR
V$AQ_BMAP_NONDUR_SUBSCRIBERS
V$AQ_CROSS_INSTANCE_JOBS
V$AQ_JOB_COORDINATOR
V$AQ_MESSAGE_CACHE
V$AQ_MSGBM
V$AQ_NONDUR_REGISTRATIONS
V$AQ_NONDUR_SUBSCRIBER
V$AQ_NONDUR_SUBSCRIBER_LWM
V$AQ_NOTIFICATION_CLIENTS
V$AQ_SERVER_POOL
V$AQ_SUBSCRIBER_LOAD
V$ASM_ACFSREPL
V$ASM_ACFSREPLTAG
V$ASM_ACFSTAG
V$ASM_ACFS_SEC_ADMIN
V$ASM_ACFS_SEC_CMDRULE
V$ASM_ACFS_SEC_REALM
V$ASM_ACFS_SEC_REALM_FILTER
V$ASM_ACFS_SEC_REALM_GROUP
V$ASM_ACFS_SEC_REALM_USER
V$ASM_ACFS_SEC_RULE
V$ASM_ACFS_SEC_RULESET
V$ASM_ACFS_SEC_RULESET_RULE
V$ASM_AUDIT_CLEANUP_JOBS
V$ASM_AUDIT_CLEAN_EVENTS
V$ASM_AUDIT_CONFIG_PARAMS
V$ASM_AUDIT_LAST_ARCH_TS
V$ASM_ESTIMATE
V$BACKUP_NONLOGGED
V$BTS_STAT
V$BT_SCAN_CACHE
V$BT_SCAN_OBJ_TEMPS
V$CACHE
V$CACHE_LOCK
V$CACHE_TRANSFER
V$CELL_OFL_THREAD_HISTORY
V$CHANNEL_WAITS
V$CLIENT_SECRETS
V$CLONEDFILE
V$CONTAINERS
V$CON_SYSSTAT
V$CON_SYSTEM_EVENT
V$CON_SYSTEM_WAIT_CLASS
V$CON_SYS_TIME_MODEL
V$COPY_NONLOGGED
V$DEAD_CLEANUP
V$DG_BROKER_CONFIG
V$EDITIONABLE_TYPES
V$ENCRYPTION_KEYS
V$FALSE_PING
V$FLASHFILESTAT
V$GES_DEADLOCKS
V$GES_DEADLOCK_SESSIONS
V$GG_APPLY_COORDINATOR
V$GG_APPLY_READER
V$GG_APPLY_RECEIVER
V$GG_APPLY_SERVER
V$GOLDENGATE_CAPABILITIES
V$GOLDENGATE_CAPTURE
V$GOLDENGATE_MESSAGE_TRACKING
V$GOLDENGATE_TABLE_STATS
V$GOLDENGATE_TRANSACTION
V$HEAT_MAP_SEGMENT
V$INSTANCE_PING
V$IOS_CLIENT
V$IO_OUTLIER
V$KERNEL_IO_OUTLIER
V$KSFQP
V$LGWRIO_OUTLIER
V$MAPPED_SQL
V$NONLOGGED_BLOCK
V$OFSMOUNT
V$OFS_STATS
V$OPTIMIZER_PROCESSING_RATE
V$PATCHES
V$PDBS
V$PDB_INCARNATION
V$PING
V$PX_PROCESS_TRACE
V$REPLAY_CONTEXT
V$REPLAY_CONTEXT_LOB
V$REPLAY_CONTEXT_SEQUENCE
V$REPLAY_CONTEXT_SYSDATE
V$REPLAY_CONTEXT_SYSGUID
V$REPLAY_CONTEXT_SYSTIMESTAMP
V$RO_USER_ACCOUNT
V$RT_ADDM_CONTROL
V$SCHEDULER_INMEM_MDINFO
V$SCHEDULER_INMEM_RTINFO
V$SESSIONS_COUNT
V$SQL_DIAG_REPOSITORY
V$SQL_DIAG_REPOSITORY_REASON
V$SQL_MONITOR_SESSTAT
V$SQL_MONITOR_STATNAME
V$SQL_REOPTIMIZATION_HINTS
V$SYS_REPORT_REQUESTS
V$SYS_REPORT_STATS
V$TEMPUNDOSTAT
V$TSDP_SUPPORTED_FEATURE
V$UNIFIED_AUDIT_TRAIL
V$XSTREAM_APPLY_COORDINATOR
V$XSTREAM_APPLY_READER
V$XSTREAM_APPLY_RECEIVER
V$XSTREAM_APPLY_SERVER
V$XSTREAM_CAPTURE
V$XSTREAM_MESSAGE_TRACKING
V$XSTREAM_TRANSACTION
V$XS_SESSIONS
V$XS_SESSION_NS_ATTRIBUTE
V$XS_SESSION_ROLE

  • AWR related views

DBA_HIST_APPLY_SUMMARY
DBA_HIST_CAPTURE
DBA_HIST_PDB_INSTANCE
DBA_HIST_REPLICATION_TBL_STATS
DBA_HIST_REPLICATION_TXN_STATS
DBA_HIST_REPORTS
DBA_HIST_REPORTS_CONTROL
DBA_HIST_REPORTS_DETAILS
DBA_HIST_REPORTS_TIMEBANDS
DBA_HIST_SESS_SGA_STATS

  • Static views (only DBA_ views are mentioned)

DBA_ACL_NAME_MAP
DBA_ALERT_HISTORY_DETAIL
DBA_AUTOTASK_STATUS
DBA_CDB_RSRC_PLANS
DBA_CDB_RSRC_PLAN_DIRECTIVES
DBA_CLUSTERING_DIMENSIONS
DBA_CLUSTERING_JOINS
DBA_CLUSTERING_KEYS
DBA_CLUSTERING_TABLES
DBA_CODE_ROLE_PRIVS
DBA_CONTAINER_DATA
DBA_CREDENTIALS
DBA_CUBE_ATTR_MAPPINGS
DBA_CUBE_ATTR_UNIQUE_KEYS
DBA_CUBE_CLASSIFICATIONS
DBA_CUBE_DEPENDENCIES
DBA_CUBE_DESCRIPTIONS
DBA_CUBE_DIMNL_MAPPINGS
DBA_CUBE_DIM_MAPPINGS
DBA_CUBE_MAPPINGS
DBA_CUBE_MEAS_MAPPINGS
DBA_CUBE_NAMED_BUILD_SPECS
DBA_DIGEST_VERIFIERS
DBA_DISCOVERY_SOURCE
DBA_DV_AUTH
DBA_DV_DATAPUMP_AUTH
DBA_DV_DDL_AUTH
DBA_DV_DICTIONARY_ACCTS
DBA_DV_DOCUMENT
DBA_DV_FACTOR_SCOPE
DBA_DV_JOB_AUTH
DBA_DV_MONITOR_RULE
DBA_DV_ORADEBUG
DBA_DV_PATCH_ADMIN_AUDIT
DBA_DV_PROXY_AUTH
DBA_DV_REALM_COMMAND_RULE
DBA_DV_TTS_AUTH
DBA_EDITIONED_TYPES
DBA_ERROR_TRANSLATIONS
DBA_GG_INBOUND_PROGRESS
DBA_GOLDENGATE_INBOUND
DBA_GOLDENGATE_RULES
DBA_GOLDENGATE_SUPPORT_MODE
DBA_HEATMAP_TOP_OBJECTS
DBA_HEATMAP_TOP_TABLESPACES
DBA_HEAT_MAP_SEGMENT
DBA_HEAT_MAP_SEG_HISTOGRAM
DBA_HOST_ACES
DBA_HOST_ACLS
DBA_ILMDATAMOVEMENTPOLICIES
DBA_ILMEVALUATIONDETAILS
DBA_ILMOBJECTS
DBA_ILMPARAMETERS
DBA_ILMPOLICIES
DBA_ILMRESULTS
DBA_ILMTASKS
DBA_LOGSTDBY_PLSQL_MAP
DBA_LOGSTDBY_PLSQL_SUPPORT
DBA_MEASURE_FOLDER_SUBFOLDERS
DBA_METADATA_PROPERTIES
DBA_OBJECT_USAGE
DBA_OLS_AUDIT_OPTIONS
DBA_OLS_STATUS
DBA_OLS_USERS
DBA_OPTSTAT_OPERATION_TASKS
DBA_PDBS
DBA_PDB_HISTORY
DBA_PLSQL_COLL_TYPES
DBA_PLSQL_TYPES
DBA_PLSQL_TYPE_ATTRS
DBA_POLICY_ATTRIBUTES
DBA_PRIV_CAPTURES
DBA_REDEFINITION_STATUS
DBA_REDO_DB
DBA_REDO_LOG
DBA_REGISTRY_SQLPATCH
DBA_REPL_DBNAME_MAPPING
DBA_ROLLING_DATABASES
DBA_ROLLING_EVENTS
DBA_ROLLING_PARAMETERS
DBA_ROLLING_PLAN
DBA_ROLLING_STATISTICS
DBA_ROLLING_STATUS
DBA_SA_PROGRAMS
DBA_SECUREFILE_LOGS
DBA_SECUREFILE_LOG_INSTANCES
DBA_SECUREFILE_LOG_PARTITIONS
DBA_SECUREFILE_LOG_TABLES
DBA_SENSITIVE_COLUMN_TYPES
DBA_SENSITIVE_DATA
DBA_SQL_PLAN_DIRECTIVES
DBA_SQL_PLAN_DIR_OBJECTS
DBA_SQL_TRANSLATIONS
DBA_SQL_TRANSLATION_PROFILES
DBA_SR_GRP_STATUS
DBA_SR_GRP_STATUS_ALL
DBA_SR_OBJ
DBA_SR_OBJ_ALL
DBA_SR_OBJ_STATUS
DBA_SR_OBJ_STATUS_ALL
DBA_SR_PARTN_OPS
DBA_SR_STLOG_EXCEPTIONS
DBA_SR_STLOG_STATS
DBA_SUPPLEMENTAL_LOGGING
DBA_TAB_COLS_V$
DBA_TAB_IDENTITY_COLS
DBA_TSDP_IMPORT_ERRORS
DBA_TSDP_POLICY_CONDITION
DBA_TSDP_POLICY_FEATURE
DBA_TSDP_POLICY_PARAMETER
DBA_TSDP_POLICY_PROTECTION
DBA_TSDP_POLICY_TYPE
DBA_UNUSED_OBJPRIVS
DBA_UNUSED_OBJPRIVS_PATH
DBA_UNUSED_PRIVS
DBA_UNUSED_SYSPRIVS
DBA_UNUSED_SYSPRIVS_PATH
DBA_UNUSED_USERPRIVS
DBA_UNUSED_USERPRIVS_PATH
DBA_USED_OBJPRIVS
DBA_USED_OBJPRIVS_PATH
DBA_USED_PRIVS
DBA_USED_PUBPRIVS
DBA_USED_SYSPRIVS
DBA_USED_SYSPRIVS_PATH
DBA_USED_USERPRIVS
DBA_USED_USERPRIVS_PATH
DBA_WALLET_ACES
DBA_WI_CAPTURE_FILES
DBA_WI_JOBS
DBA_WI_OBJECTS
DBA_WI_PATTERNS
DBA_WI_PATTERN_ITEMS
DBA_WI_STATEMENTS
DBA_WI_TEMPLATES
DBA_WI_TEMPLATE_EXECUTIONS
DBA_WORKLOAD_ACTIVE_USER_MAP
DBA_WORKLOAD_REPLAY_SCHEDULES
DBA_WORKLOAD_SCHEDULE_CAPTURES
DBA_WORKLOAD_SCHEDULE_ORDERING
DBA_WORKLOAD_USER_MAP
DBA_XDS_ACL_REFRESH
DBA_XDS_ACL_REFSTAT
DBA_XDS_LATEST_ACL_REFSTAT
DBA_XMLTYPE_COLS
DBA_XML_NESTED_TABLES
DBA_XML_OUT_OF_LINE_TABLES
DBA_XML_SCHEMA_ATTRIBUTES
DBA_XML_SCHEMA_COMPLEX_TYPES
DBA_XML_SCHEMA_ELEMENTS
DBA_XML_SCHEMA_NAMESPACES
DBA_XML_SCHEMA_SIMPLE_TYPES
DBA_XML_SCHEMA_SUBSTGRP_HEAD
DBA_XML_SCHEMA_SUBSTGRP_MBRS
DBA_XSTREAM_SPLIT_MERGE
DBA_XSTREAM_SPLIT_MERGE_HIST
DBA_XSTREAM_STMTS
DBA_XSTREAM_STMT_HANDLERS
DBA_XSTREAM_TRANSFORMATIONS
DBA_XS_ACES
DBA_XS_ACLS
DBA_XS_ACL_PARAMETERS
DBA_XS_ACTIVE_SESSIONS
DBA_XS_APPLIED_POLICIES
DBA_XS_AUDIT_POLICY_OPTIONS
DBA_XS_AUDIT_TRAIL
DBA_XS_COLUMN_CONSTRAINTS
DBA_XS_DYNAMIC_ROLES
DBA_XS_ENB_AUDIT_POLICIES
DBA_XS_EXTERNAL_PRINCIPALS
DBA_XS_IMPLIED_PRIVILEGES
DBA_XS_INHERITED_REALMS
DBA_XS_MODIFIED_POLICIES
DBA_XS_NS_TEMPLATES
DBA_XS_NS_TEMPLATE_ATTRIBUTES
DBA_XS_OBJECTS
DBA_XS_POLICIES
DBA_XS_PRINCIPALS
DBA_XS_PRIVILEGES
DBA_XS_PROXY_ROLES
DBA_XS_REALM_CONSTRAINTS
DBA_XS_ROLES
DBA_XS_ROLE_GRANTS
DBA_XS_SECURITY_CLASSES
DBA_XS_SECURITY_CLASS_DEP
DBA_XS_SESSIONS
DBA_XS_SESSION_NS_ATTRIBUTES
DBA_XS_SESSION_ROLES
DBA_XS_USERS
DBA_ZONEMAPS
DBA_ZONEMAP_MEASURES

12c, Oracle, tools

← Older posts

Newer posts →