Detecting Tracing

Sometimes we need to know if are there acive traces in the system, of course, there are several ways. Here I like to show, maybe, the most easy way. You need query the v$session, with the columns show below you can quickly identified sessions with activet traces;

 
 -- version 10g ---
 SQL_TRACE                      
 SQL_TRACE_WAITS                
 SQL_TRACE_BINDS                

 NOTE: SQL_TRACE_PLAN_STATS does't exists in this version 

 -- version 11g and 12c ---
 SQL_TRACE                              
 SQL_TRACE_WAITS                        
 SQL_TRACE_BINDS                        
 SQL_TRACE_PLAN_STATS                   

From the Oracle Documentation, the concept for each fields is like show below;

  • SQL_TRACE; Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)
  • SQL_TRACE_WAITS; Indicates whether wait tracing is enabled (TRUE) or not (FALSE)
  • SQL_TRACE_BINDS; Indicates whether bind tracing is enabled (TRUE) or not (FALSE)
  • SQL_TRACE_PLAN_STATS; Frequency at which row source statistics are dumped in the trace files for each cursor: (never, first_execution or all_executions)

An example;

 
SFFF*ANTO> column program format a25
SFFF*ANTO> select sid, username, program, sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
  2  from v$session
  3  where
  4  sql_trace != 'DISABLED' OR
  5  sql_trace_waits != 'FALSE' OR
  6  sql_trace_binds != 'FALSE' OR
  7  SQL_TRACE_PLAN_STATS != 'FIRST EXEC'
  8  /
SFFF*ANTO> 
SFFF*ANTO> EXEC DBMS_MONITOR.session_trace_enable;

Procedimiento PL/SQL terminado correctamente.

SFFF*ANTO> select sid, username, program, sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
  2  from v$session
  3  where
  4  sql_trace != 'DISABLED' OR
  5  sql_trace_waits != 'FALSE' OR
  6  sql_trace_binds != 'FALSE' OR
  7  SQL_TRACE_PLAN_STATS != 'FIRST EXEC'
  8  /

    SID USERNAME        PROGRAM                   SQL_TRAC SQL_T SQL_T SQL_TRACE_
_______ _______________ _________________________ ________ _____ _____ __________
     63 ANTONION        sqlplusw.exe              ENABLED  TRUE  FALSE FIRST EXEC

HTH – Antonio NAVARRO

Advertisements

Subscription For Node Down Event Still Pending

Today I saw the next warnning into a listener log file constantly;

‘WARNING: Subscription for node down event still pending’

This error is related to the Oracle TNS Listener’s default subscription to the Oracle Notification Service (ONS). In non-RAC architecture it’s recommende switch off this functionality.

Modify the listener.ora file with the next parameters;

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

After that you need restart or reload the listener.

For more information about this parameter, please take a look at 372959.1 ML/MOS note.

HTH – Antonio NAVARRO

 

 

 

Oracle Not Start With Windows Start up

Today, a coworker ask me about a problem with a database. He reboot the windows system but the database doesn’t started. He talk me the windows service is in automate state, in fact, the service started but no the database.

Tooking a look a regedit We can see the next photo;

foto_oradim_v1

in the picture, you can see the ora_sid_autostart entry, in this case is set to false. This the problem. With the next command you can change this value, in a official way, (is not recommended edit ora_sid_autostart entry and modified it);

C:\Users\admbbdd>oradim -EDIT -SID dbfin -STARTMODE auto

After refresh the regedit (or close and open again), the ora_sid_autostart entry has changed;

 

foto_oradim_v2

Oradim command has a log of activity located at $ORACLE_HOME\database named oradim.log.

HTH – Antonio NAVARRO

ORA-19554 And ORA-27211 Erros Executing Backup

The error below is reported from backup team:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on t1 channel at 10/05/2016 10:48:48
ORA-19554: error allocating device, device type: SBT_TAPE, device name: 
ORA-27211: Failed to load Media Management Library

 

Rman backup fails at the begining of scrpit, when try allocate channels, It can looks a problem from the media but MML isn’t fault. The database has problem with memory, looked at diag directory there are trace files with warnings of run out of memory. Search in ML/MOS I found is a problem with memory. In this case I only get solved the problem after restart database. To resize PGA and SGA not work for me.

HTH – Antonio NAVARRO

Archive Full Don’t Report Error On 12c

Last Friday I had a problem with a database, it is a little strange, a archive disk get full and run of free space but not error was reported. In this case the ora-00257 is always expected but no here, I don’t sure if it’s a new way of work on this release or any other condition interfered in the problem.

Below post the alert.log error, but when new connections to the database were performed any error was prompt, the connection only get hanged.

 

 
Unable to create archive log file '/oracle/financial/arch/FPROD_919411454_1_730.ARC'
Fri Oct 07 12:06:57 2016
Errors in file /oracle/diag/rdbms/fprod/fprod/trace/fprod_arc3_1984.trc:
ORA-19504: failed to create file "/oracle/financial/arch/FPROD_919411454_1_730.ARC"
ORA-27044: unable to write the header block of file
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 112) There is not enough space on the disk.
ARC3: Error 19504 Creating archive log file to '/oracle/financial/arch/FPROD_919411454_1_730.ARC'
Fri Oct 07 12:06:57 2016
Unable to create archive log file '/oracle/financial/arch/FPROD_919411454_1_730.ARC' 

HTH – Antonio NAVARRO

 

ORA-19625 ORA-27037 Errors Using Incrementally Updating Backups

Today I have been reported with next error when a full backup is executed;

 
Starting backup at 04-OCT-16
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=557 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: NMO v4.2.0.0
sent command to channel: ORA_SBT_TAPE_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 10/04/2016 07:08:56
ORA-19625: error identifying file /OCETA/prod/archives/OCETA/datafile/o1_mf_sysaux_bpxdw00t_.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

The problem is the datafile indicate before is missing, I don’t sure how it did, but this db file no exits. The problem is for Oracle it exist yet. Please, look below, the next sequence of commands to solve the problem. The solution is force the expired of this file, in the next backup Oracle to create a new copy of the file, in this case associated to sysaux tbs.

 
RMAN>

RMAN> list datafilecopy '/OCETA/prod/archives/OCETA/datafile/o1_mf_sysaux_bpxdw00t_.dbf';

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
7895715 3    A 04-OCT-16       259771489071 03-OCT-16
        Name: /OCETA/prod/archives/OCETA/datafile/o1_mf_sysaux_bpxdw00t_.dbf
        Tag: COPY_ONLINE_DISK

RMAN> crosscheck copy of datafile 3;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=461 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=434 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=554 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=45 device type=DISK
validation failed for datafile copy
datafile copy file name=/OCETA/prod/archives/OCETA/datafile/o1_mf_sysaux_bpxdw00t_.dbf RECID=24070 STAMP=924328947
Crosschecked 1 objects

RMAN>
RMAN> list datafilecopy '/OCETA/prod/archives/OCETA/datafile/o1_mf_sysaux_bpxdw00t_.dbf';

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
7895715 3    X 04-OCT-16       259771489071 03-OCT-16
        Name: /OCETA/prod/archives/OCETA/datafile/o1_mf_sysaux_bpxdw00t_.dbf
        Tag: COPY_ONLINE_DISK

RMAN>

HTH – Antonio NAVARRO