TFA – Oracle Trace File Analyser

Uninstall TFA

1) Check TFA_HOME.

$ grep TFA_HOME= /etc/init.d/init.tfa
TFA_HOME=/u01/app/12.2.0.1/grid/tfa/racnode1/tfa_home

2) Uninstall TFA as root user.

[root@racnode1 bin]# ./tfactl -h

Usage : /u01/app/12.2.0.1/grid/bin/tfactl <command> [options]
 commands:diagcollect|collection|analyze|ips|run|start|stop|enable|disable|status|print|access|purge|directory|host|receiver|set|toolstatus|uninstall|diagnosetfa
For detailed help on each command use:
 /u01/app/12.2.0.1/grid/bin/tfactl <command> -help

[root@racnode1 bin]# /u01/app/12.2.0.1/grid/bin/tfactl uninstall

TFA will be uninstalled on node racnode1 :

Removing TFA from racnode1 only
Please remove TFA locally on any other configured nodes

Notifying Other Nodes about TFA Uninstall...
TFA is not yet secured to run all commands
FAIL
Sleeping for 10 seconds...
Stopping TFA Support Tools...
Stopping TFA in racnode1...
Shutting down TFA
Removed symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.
Removed symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service.
. . . . .
. . .
Successfully shutdown TFA..

Deleting TFA support files on racnode1:
Removing /u01/app/grid/tfa/racnode1/database...
Removing /u01/app/grid/tfa/racnode1/log...
Removing /u01/app/grid/tfa/racnode1/output...
Removing /u01/app/grid/tfa/racnode1...
Removing /u01/app/grid/tfa...
Removing /etc/rc.d/rc0.d/K17init.tfa
Removing /etc/rc.d/rc1.d/K17init.tfa
Removing /etc/rc.d/rc2.d/K17init.tfa
Removing /etc/rc.d/rc4.d/K17init.tfa
Removing /etc/rc.d/rc6.d/K17init.tfa
Removing /etc/init.d/init.tfa...
Removing /u01/app/12.2.0.1/grid/bin/tfactl...
Removing /u01/app/12.2.0.1/grid/tfa/bin...
Removing /u01/app/12.2.0.1/grid/tfa/racnode1...
Removing /u01/app/12.2.0.1/grid/tfa...

Install TFA

1)Install TFA locally as root user on each RAC node.

[root@racnode1 TFA]# ./installTFA-LINUX -tfabase /u01/app/grid -javahome /u01/app/12.2.0.1/grid/jdk
TFA Installation Log will be written to File : /tmp/tfa_install_49362_2017_08_31-11_35_54.log

Starting TFA installation

TFA Version: 122122 Build Date: 201707270831

Running Auto Setup for TFA as user root...

Would you like to do a [L]ocal only or [C]lusterwide installation ? [L|l|C|c] [C] : L
Installing TFA now...

Discovering Nodes and Oracle resources

Starting Discovery...

Getting list of nodes in cluster . . . . .

List of nodes in cluster:
racnode1
racnode2

CRS_HOME=/u01/app/12.2.0.1/grid

Searching for running databases...
1. RACDEV1
2. RACTEST1

Searching out ORACLE_HOME for selected databases...
Getting Oracle Inventory...
ORACLE INVENTORY: /u01/app/oraInventory
Discovery Complete...

TFA Will be Installed on racnode1...
Checking JAVA Status on all nodes ...
TFA will scan the following Directories
++++++++++++++++++++++++++++++++++++++++++++

.------------------------------------------------------------------.
|                            racnode1                              |
+-------------------------------------------------------+----------+
| Trace Directory                                       | Resource |
+----------------------------------------------- -------+----------+
| /u01/app/12.2.0.1/grid/cfgtoollogs                    | CFGTOOLS |
| /u01/app/12.2.0.1/grid/crf/db/racnode1                | CRS      |
| /u01/app/12.2.0.1/grid/crs/log                        | CRS      |
| /u01/app/12.2.0.1/grid/css/log                        | CRS      |
| /u01/app/12.2.0.1/grid/cv/log                         | CRS      |
| /u01/app/12.2.0.1/grid/evm/admin/log                  | CRS      |
| /u01/app/12.2.0.1/grid/evm/admin/logger               | CRS      |
| /u01/app/12.2.0.1/grid/evm/log                        | CRS      |
| /u01/app/12.2.0.1/grid/install                        | INSTALL  |
| /u01/app/12.2.0.1/grid/inventory/ContentsXML          | INSTALL  |
| /u01/app/12.2.0.1/grid/log                            | CRS      |
| /u01/app/12.2.0.1/grid/network/log                    | CRS      |
| /u01/app/12.2.0.1/grid/opmn/logs                      | CRS      |
| /u01/app/12.2.0.1/grid/racg/log                       | CRS      |
| /u01/app/12.2.0.1/grid/rdbms/log                      | ASM      |
| /u01/app/12.2.0.1/grid/scheduler/log                  | CRS      |
| /u01/app/12.2.0.1/grid/srvm/log                       | CRS      |
| /u01/app/grid/cfgtoollogs                             | CFGTOOLS |
| /u01/app/grid/crsdata/racnode1/acfs                   | ACFS     |
| /u01/app/grid/crsdata/racnode1/afd                    | ASM      |
| /u01/app/grid/crsdata/racnode1/chad                   | CRS      |
| /u01/app/grid/crsdata/racnode1/core                   | CRS      |
| /u01/app/grid/crsdata/racnode1/crsconfig              | CRS      |
| /u01/app/grid/crsdata/racnode1/crsdiag                | CRS      |
| /u01/app/grid/crsdata/racnode1/cvu                    | CRS      |
| /u01/app/grid/crsdata/racnode1/evm                    | CRS      |
| /u01/app/grid/crsdata/racnode1/output                 | CRS      |
| /u01/app/grid/crsdata/racnode1/trace                  | CRS      |
| /u01/app/grid/diag/asm/+asm/+ASM1/cdump               | ASM      |
| /u01/app/grid/diag/crs/racnode1/crs/cdump             | CRS      |
| /u01/app/grid/diag/crs/racnode1/crs/trace             | CRS      |
| /u01/app/grid/diag/rdbms/_mgmtdb/-MGMTDB/cdump        | RDBMS    |
| /u01/app/grid/diag/tnslsnr/racnode1/listener/cdump    | TNS      |
...
..
.
| /u01/app/oraInventory/ContentsXML                     | INSTALL |
| /u01/app/oraInventory/logs                            | INSTALL |
...
..
.

Installing TFA on racnode1:
HOST: racnode1 TFA_HOME: /u01/app/grid/tfa/racnode1/tfa_home

.-----------------------------------------------------------------------------.
| Host     | Status of TFA | PID   | Port | Version    | Build ID             |
+----------+---------------+-------+------+------------+----------------------+
| racnode1 |    RUNNING    | 51354 | 5000 | 12.2.1.2.2 | 12212220170727083130 |
'----------+---------------+-------+------+------------+----------------------'

Running Inventory in All Nodes...
Enabling Access for Non-root Users on racnode1...
Adding default users to TFA Access list...

Summary of TFA Installation:
.-----------------------------------------------------------.
|                 racnode1                                  |
+---------------------+-------------------------------------+
| Parameter           |            Value                    |
+---------------------+-------------------------------------+
| Install location    | /u01/app/grid/tfa/racnode1/tfa_home |
| Repository location | /u01/app/grid/tfa/repository        |
| Repository usage    | 0 MB out of 10240 MB                |
'---------------------+-------------------------------------'

TFA is successfully installed...

Usage : /u01/app/12.2.0.1/grid/bin/tfactl <command> [options]
 commands:diagcollect|collection|analyze|ips|run|start|stop|enable|disable|status|print|access|purge|directory|host|receiver|set|toolstatus|uninstall|diagnosetfa|syncnodes
For detailed help on each command use:
 /u01/app/12.2.0.1/grid/bin/tfactl <command> -help

2) Startup OSWatcher with gzip option.

[root@racnode1 TFA]# /u01/app/12.2.0.1/grid/bin/tfactl
 tfactl> status oswbb
Check run status of TFA process
Usage : /u01/app/12.2.0.1/grid/bin/tfactl status

 tfactl> stop oswbb
Stopped OSWatcher

 tfactl> start oswbb 15 168 gzip
Starting OSWatcher

[root@racnode1 TFA]# ps -eaf | grep -i osw |grep -v grep
grid 19631 1 0 11:52 pts/3 00:00:00 /bin/sh ./OSWatcher.sh 15 168 gzip /u01/app/grid/tfa/repository/suptools/racnode1/oswbb/grid/archive

Synchronize TFA between RAC nodes

[root@racnode1 ~]# /u01/app/12.2.0.1/grid/bin/tfactl syncnodes

Current Node List in TFA :
1. racnode1

Node List in Cluster :
1. racnode1
2. racnode2

Node List to sync TFA Certificates :
 1 racnode2
 
Do you want to update this node list? [Y|N] [N]: Y

Please Enter all the remote nodes you want to sync...

Enter Node List (seperated by space) : racnode1 racnode2

Node List to sync TFA Certificates :
 1 racnode2
 
Syncing TFA Certificates on racnode2 :

TFA_HOME on racnode2 : /u01/app/grid/tfa/racnode2/tfa_home

Copying TFA Certificates to racnode2...
root@racnode2's password:
Copying SSL Properties to racnode2...
root@racnode2's password:

Restarting TFA on racnode2...
root@racnode2's password:
Restarting TFA..
Killing TFA running with pid 17697
Waiting up to 120 seconds for TFA to be re-started..
. . . . . . . . . .
. . . . . . . . . .
. . . . . . . . . .
Successfully re-started TFA..

.--------------------------------------------------------------------------------------------------------.
| Host     | Status of TFA | PID        | Port    | Version    | Build ID             | Inventory Status |
+----------+---------------+------------+---------+------------+----------------------+------------------+
| racnode1 | RUNNING       | 117654     | 5000    | 12.2.1.2.2 | 12212220170727083130 | COMPLETE         |
| racnode2 | RUNNING       | 114949     | 5000    | 12.2.1.2.2 | 12212220170727083130 | COMPLETE         |
'----------+---------------+------------+---------+------------+----------------------+------------------'

ORMB Batch Scheduling Job Hangs At F1-FLUSH with “PL/SQL Lock Timer” Wait Event

Overnight ORMB Batch Scheduling Job has been running for more than twenty hours, user wants to know why ?

1) Check which job at what step is still running and hanging.

SQL> select OWNER,JOB_NAME,JOB_SUBNAME,SESSION_ID,ELAPSED_TIME,CPU_USED 
     from dba_scheduler_running_jobs;

OWNER   JOB_NAME   JOB_SUBNAME  SESSION_ID ELAPSED_TIME  CPU_USED
------- ---------- ------------ ---------- ----------- ---------------
SCHADM  DAILY_JOBS F1_FLUSH_JOB 1006       +000 21:47:00.08 +00000:00:22.15

2) Check session status.

SQL> select USERNAME,STATUS,EVENT,sql_id,LAST_CALL_ET 
      from gv$session where sid=1006;

USERNAME  STATUS EVENT               SQL_ID           LAST_CALL_ET
--------- ------ ------------------  ---------------- -----------
SCHADM    ACTIVE PL/SQL lock timer   fnfa105xssacx    78949

3) Check which sql is the running with ‘PL/SQL lock timer’.

SQL> set long 20000
SQL> select SQL_FULLTEXT from v$sql where sql_id='fnfa105xssacx';

SQL_FULLTEXT
---------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
 ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window
_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(
14) := :chainid; credential_owner varchar2(30) := :credown; credential_name
varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destinat
ion_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_i
d number := :log_id; BEGIN DECLARE
 BATCH_CODE CHAR(8);
 USER_ID CHAR(8);
 THREAD_COUNT NUMBER;
 BUSINESS_DATE DATE;
 MAX_TIMEOUT_MINS NUMBER;
 RERUN_NUMBER NUMBER;
 THREAD_POOL VARCHAR2(254);
 MAIL_ID VARCHAR2(254);
 MIN_RUN_STATUS CHAR(2);
 RAISE_ERROR BOOLEAN;
 THREAD_NOTIFICATIONS BOOLEAN;
 SOFT_PARM_MAP CISADM.OUAF_BATCH.PARM_MAP_T;
 SOFT_PARM_LIST CISADM.OUAF_BATCH.PARM_LIST_T;
BEGIN
 BATCH_CODE := 'F1-FLUSH';
 USER_ID := 'JAMES';
 --THREAD_COUNT := 0;
 --BUSINESS_DATE := to_date('2016-01-31','yyyy-MM-dd');
 MAX_TIMEOUT_MINS := 0;
 RERUN_NUMBER := 0;
 THREAD_POOL := 'ORMB_BATCH';
 --MIN_RUN_STATUS := NULL;
 --RAISE_ERROR := NULL;
 -- THREAD_NOTIFICATIONS := true;
 --SOFT_PARM_MAP := NULL;
 --SOFT_PARM_LIST := CISADM.OUAF_BATCH.PARM_LIST_T('DIST-THD-POOL','');
 OUAF_BATCH.SUBMIT_JOB(
 BATCH_CODE => BATCH_CODE,
 USER_ID => USER_ID,
 THREAD_COUNT => THREAD_COUNT,
 BUSINESS_DATE => BUSINESS_DATE,
 MAX_TIMEOUT_MINS => MAX_TIMEOUT_MINS,
 RERUN_NUMBER => RERUN_NUMBER,
 THREAD_POOL => THREAD_POOL,
 MIN_RUN_STATUS => MIN_RUN_STATUS,
 RAISE_ERROR => RAISE_ERROR,
 THREAD_NOTIFICATIONS => THREAD_NOTIFICATIONS,
 SOFT_PARM_MAP => SOFT_PARM_MAP,
 SOFT_PARM_LIST => SOFT_PARM_LIST
 );
END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

4) ‘PL/SQL Lock Timer’ event ( Doc ID 1476088.1 )

"PL/SQL Lock Timer"

This event is called through the DBMSLOCK.SLEEP procedure or USERLOCK.SLEEP procedure. This event is most likely to come from user written stored procedures. The ‘idle’ event ‘PL/SQL lock timer’ is worth watching because it points us in the direction of application response, throughput, and possible coding issues. The PL/SQL lock timer event is, as the command that issues it states, a sleep mandated by the application code. The application is idle and doing nothing. This means that if the application sleeps for a combined interval of 5 minutes, the response to the user or complete run of the application will take at least 5 minutes longer.
While this event does not require resources and thus is not an Oracle resource contention issue, it is our job to provide the quickest response to users and push applications through our system to increase the user’s perceived performance. Most often, sleeps are put into an application for serialization of transactional events or spinning on queues until something happens. When the event occurs or a queue is populated, then the application continues its work. We should ask ourselves why an application is sleeping for any amount of time and provide alternatives if necessary to reduce this idle event. You will gain quicker responses if you trigger an action instead of the action waiting for an event.

This event is called through the DBMSLOCK.SLEEP procedure or
USERLOCK.SLEEP procedure. This event is most likely to come from
user written stored procedures.

5) Sent the details to vendor why F1-Flush has “PL/SQL lock timer’ wait event forever.

How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes the default attributes require to be modified for future partitions, or sub-partitions, specially in the situation when tables and indexes are refreshed from different environment.

Let’s check how to modify default attributes of tables, indexes, table partitions and index partitions ,etc.

Subscribe to get access

Read more of this content when you subscribe today.

Query Alert Log Contents From Database

Get alert log contents directly from database by using SQL query

We can get alert log contents directly from database by using SQL query, though the x$ view name is hard to remember.

X$DBGALERTEXT

The view x$dbgalertext is the view to query to get alert log contents for current instance:

SQL> DESC X$DBGALERTEXT
 Name                        Null?    Type
 --------------------------- -------- ----------------------------
 ADDR                                 RAW(8)
 INDX                                 NUMBER
 INST_ID                              NUMBER
 CON_ID                               NUMBER
 ORIGINATING_TIMESTAMP                TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                 TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                      VARCHAR2(64)
 COMPONENT_ID                         VARCHAR2(64)
 HOST_ID                              VARCHAR2(64)
 HOST_ADDRESS                         VARCHAR2(46)
 MESSAGE_TYPE                         NUMBER
 MESSAGE_LEVEL                        NUMBER
 MESSAGE_ID                           VARCHAR2(64)
 MESSAGE_GROUP                        VARCHAR2(64)
 CLIENT_ID                            VARCHAR2(64)
 MODULE_ID                            VARCHAR2(64)
 PROCESS_ID                           VARCHAR2(32)
 THREAD_ID                            VARCHAR2(64)
 USER_ID                              VARCHAR2(64)
 INSTANCE_ID                          VARCHAR2(64)
 DETAILED_LOCATION                    VARCHAR2(160)
 PROBLEM_KEY                          VARCHAR2(550)
 UPSTREAM_COMP_ID                     VARCHAR2(100)
 DOWNSTREAM_COMP_ID                   VARCHAR2(100)
 EXECUTION_CONTEXT_ID                 VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE           NUMBER
 ERROR_INSTANCE_ID                    NUMBER
 ERROR_INSTANCE_SEQUENCE              NUMBER
 VERSION                              NUMBER
 MESSAGE_TEXT                         VARCHAR2(2048)
 MESSAGE_ARGUMENTS                    VARCHAR2(512)
 SUPPLEMENTAL_ATTRIBUTES              VARCHAR2(512)
 SUPPLEMENTAL_DETAILS                 VARCHAR2(4000)
 PARTITION                            NUMBER
 RECORD_ID                            NUMBER

To get alert log for the last five hours

SQL> select 
         to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'), 
         MESSAGE_TEXT 
 from    X$DBGALERTEXT 
 where   ORIGINATING_TIMESTAMP>sysdate-5/24 
 order by RECORD_ID;

TO_CHAR(ORIGINATI MESSAGE_TEXT
----------------- ----------------------------------------------------------------------------------------------------
20171030-12:34:15 Thread 1 advanced to log sequence 1016 (LGWR switch)
20171030-12:34:15 Current log# 2 seq# 1016 mem# 0: +DATA2/RACTEST/ONLINELOG/group_2.1453.926116803
20171030-12:34:15 Current log# 2 seq# 1016 mem# 1: +FRA/RACTEST/ONLINELOG/group_2.20660.926116805
20171030-12:34:16 Deleted Oracle managed file +FRA/RACTEST/ARCHIVELOG/2017_08_23/thread_1_seq_844.2274.952747445
20171030-12:34:16 Deleted Oracle managed file +FRA/RACTEST/ARCHIVELOG/2017_08_23/thread_2_seq_875.15096.952747443
20171030-12:34:27 Archived Log entry 3854 added for thread 1 sequence 1015 ID 0x7553f3c0 dest 1:

6 rows selected.

To get alert log for the first 20 lines

SQL> select 
           to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'), 
           MESSAGE_TEXT 
     from  X$DBGALERTEXT 
     order by RECORD_ID 
     fetch first 20 rows only;

TO_CHAR(ORIGINATI MESSAGE_TEXT
----------------- ----------------------------------------------------------------------------------------------------
20170825-16:13:10 Starting ORACLE instance (normal) (OS id: 10587)
20170825-16:13:10 CLI notifier numLatches:53 maxDescs:4998
20170825-16:13:10 **********************************************************************
20170825-16:13:10 Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
20170825-16:13:10 Per process system memlock (soft) limit = UNLIMITED
20170825-16:13:10 Expected per process system memlock (soft) limit to lock
20170825-16:13:10 SHARED GLOBAL AREA (SGA) into memory: 16G
20170825-16:13:10 Available system pagesizes:
20170825-16:13:10 4K, 2048K
20170825-16:13:10 Supported system pagesize(s):
20170825-16:13:10 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
20170825-16:13:10 4K Configured 10 10 NONE
20170825-16:13:10 2048K 68613 8193 8193 NONE
20170825-16:13:10 **********************************************************************
20170825-16:13:10 LICENSE_MAX_SESSION = 0
20170825-16:13:10 LICENSE_SESSIONS_WARNING = 0
20170825-16:13:10 Initial number of CPU is 32
20170825-16:13:10 Number of processor cores in the system is 16
20170825-16:13:10 Number of processor sockets in the system is 2
20170825-16:13:10 Private Interface 'bond1:1' configured from GPnP for use as a private interconnect.

20 rows selected.

ORA-600 [kwqmnuji: bad option]

SYMPTOMS

There are a lot of ORA-600 [kwqmnuji: bad option] errors in alert.log of 11.2.0.4 RAC database.

ORA-00600: internal error code, arguments: [kwqmnuji: bad option], [2], [], [], [], [], [], [], [], [], [], []
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x119190D, kwqmncif()+31] [flags: 0x0, count: 1]
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x119190D, kwqmncif()+31] [flags: 0x0, count: 1]

CAUSES

This is “Bug 14746914 – ORA-600 [kwqmnuji: bad option] using AQ in RAC (Doc ID 14746914.8)”.

The fix for 14746914 is first included in

WORKAROUND

We used below workaround to make ORA-600 [kwqmnuji: bad option] error disappeared from alert.log.

1) Bounce the instance, and still see cored umps:

Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_ora_2954.trc:
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_ora_2954.trc:
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_ora_2954.trc:

2) Check OS process 2954 and its related session. then kill this process.

$kill -9 2954

3) Check alert.log, and find there is no more ORA-00600 errors and core dumps.