SYS_OP_C2C internal function, implicit data type converstion

Our database is heavily loaded. In addition, developers are writing codes that make even Exadata to hang¬†ūüôā ¬†They are the best hackers ever :):)

Investigating database performance using 13c Cloud Control found one very interesting SQL.
At glance everything is fine, but there is one thing that is important and makes SQL  heavy.

Top SQL:

SELECT nvl(max(bl.id), 0) 
FROM schemaname.eb_restriction_balance bl 
WHERE bl.restrictcode = :b1

SCHEMANAME.EB_RESTRICTION_BALANCE table structure:

SQL> DESC schemaname.eb_restriction_balance

Name Type Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
ID NUMBER 
RESTRICTCODE VARCHAR2(30) 
CURRENCY VARCHAR2(3) 
RESTRICTAMOUNT NUMBER(14) 0 
BALANCE NUMBER(14) 0 
STATE NUMBER(5) 5 
INSERTDATE DATE sysdate 
UPDATEDATE DATE Y 
INN VARCHAR2(30) Y

RESTRICTCODE column is indexed.

So in ideal way the above select should use the index on RESTRICTCODE.

Cloud Control shows that CBO chooses TABLE ACCESS STORAGE FULL.

You can run SQL Tuning Advisor from Cloud Control easily. Advisor generated the following recommendation:

The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.schemaname.EB_RESTRICTION_BALANCE(SYS_OP_C2C(“RESTRICTCODE”))

SYS_OP_C2C means that there happened implicit data type conversion.
So we must find the exact bind value that was used at the time sql was run.

SQL> SELECT name, datatype_string, value_string
 2 FROM v$sql_bind_capture
 3 WHERE sql_id='dnb1771sbm98x';

NAME DATATYPE_STRING VALUE_STRING
------- ------------------ -------------
:B1 NVARCHAR2(128) BR16215493
:B1 NVARCHAR2(128) BR16213680

As you see bind value type was NVARCHAR, that is why oracle converted varchar2 to nvarchar2 and did not use index on RESTRICTCODE.

Solution is to make developer change code and pass the parameter with the type varchar2.

 

How to find remote session executing over a database link

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
substr(event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx;

GTXID is the same on both databases.

################################### Sample output ###################################

##Destination

3   LBREPDB01-51715  LBREP.aa2c0b4f.94.11.4694801  5447.62951   I   SQL*Net me

##Source

2   LB\MARIAMI-41196:4058  LBREP.aa2c0b4f.94.11.4694801 87.36231  I  SQL*Net me

More Details:

SID ‚Äď 87
SERIAL ‚Äď 36231

Proxy User Authentication: Create DBLINK in another schema

Problem

We need to create private database link in another schema. But we don’t know the password for this user.

Solution (Proxy user)

For example, my username is marik and database link should be created in dbcleanup’s schema.

SQL> alter user DBCLEANUP grant connect through marik;
SQL> connect marik[DBCLEANUP]/123@orcl
SQL> show user
USER is "marik[DBCLEANUP]"
SQL> create database link MYLINK 
connect to LINKUSER identified by "password" using 'ORCL2';

Proxy users can be identified using the PROXY_USERS view.

SQL> select * from proxy_users;

The proxy authentication can be revoked using the following command.

SQL> alter user DBCLEANUP revoke connect through marik ;

RMAN: Displaying current backup progress

To check the progress of your current RMAN backup use this script:

#This is my favorite script

select recid
 , output_device_type
 , dbsize_mbytes
 , input_bytes/1024/1024 input_mbytes
 , output_bytes/1024/1024 output_mbytes
 , (output_bytes/input_bytes*100) compression
 , (mbytes_processed/dbsize_mbytes*100) complete
 , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
 from v$rman_status rs
 , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
 and output_device_type is not null

Alert log table x$dbgalertext

Prior to 11g you had to create external table to query alert log information.

In 11g Oracle introduced X$DBGALERTEXT, which is mapped to the alert log file located at  $ORACLE_BASE/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/alert/log_XX.xml.

Note that table is located in SYS schema and you need a permission to see it’s content.

So, please keep in mind this table name, it is really useful to search errors in alert log.

Oracle Management Server is Down – Failed to push ldap config data to libOvd for service instance “idstore.ldap”

I have setup Oracle Cloud Control 13c on virtual machine.

Yesterday machine was shut downed forcibly and Cloud Control was unable to start up after that.

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Could Not Be Started
Check EM Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
Oracle Management Server is Down
JVMD Engine is Down
Starting BI Publisher Server ...
BI Publisher Server Could Not Be Started. Check BIP Server log file for details.
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
BI Publisher Server is Down

Let’s check the following log:

[oracle@oragc log]$ tailf /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out

SEVERE: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default", 
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; lineNumber: 210; 
columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF.
Jan 03, 2017 2:02:17 PM oracle.security.opss.internal.runtime.ServiceContextManagerImpl createContextInternal
WARNING: Service instance "idstore.ldap" cannot be instantiated. 
Reason: oracle.security.jps.service.idstore.IdentityStoreException: 
JPS-02592: Failed to push ldap config data to libOvd for service instance "idstore.ldap" in JPS context "default",
cause: org.xml.sax.SAXException: Error Parsing at line #210: 38.org.xml.sax.SAXParseException; 
lineNumber: 210; columnNumber: 38; <Line 210, Column 38>: XML-20210: (Fatal Error) Unexpected EOF..
Jan 03, 2017 2:02:17 PM oracle.security.jps.internal.config.OpssCommonStartup start
INFO: Jps startup failed.
<Jan 3, 2017 2:02:17 PM GET> <Error> <Security> 
<BEA-090892> <The loading of an OPSS java security policy provider failed due to an exception. 
See the exception stack trace or the server log file for the root cause. 
If there is no obvious cause, enable the debug flag -Djava.security.debug=jpspolicy to get more information. 
Error message: null>

I have found the solution on Oracle Community site: https://community.oracle.com/thread/2375509

“Please follow below steps to get you domain up and running
1) Take a backup of $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

2) Delete $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml

3) copy adapters.os_xml from $MW_HOME/oracle_common/modules/oracle.ovd_11.1.1/templates/ to $DOMAIN_HOME/config/fmwconfig/ovd/default/

4) Restart the server.”

After performing the above steps:

[oracle@oragc ~]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up

Send Oracle Audit to rsyslog

In our database there is turned on auditing on some operations and audit records go to OS.

SYS> show parameter audit_file_dest

NAME                TYPE        VALUE
------------------ ----------- ------------------------------
audit_file_dest  string       /u01_log/audit/orcl

SYS > show parameter audit_trail

NAME        TYPE         VALUE
------------- ----------- -----------
audit_trail string        OS

Our security administrators are using SIEM to monitor suspicious activities and they want database to send audit records to this third party tool.

I thought that I could somehow indicate directory “/u01_log/audit/orcl” from where *.aud files would be uploaded to SIEM, but I was wrong. Some tools may be able to use these *.aud files but not SIEM and let’s configure our database to be able to send audit records to it.

1. Connect to a database instance as sysdba user

SQL> connect / as sysdba

2. Set audit trail to OS

SQL> alter system set audit_trail=OS;

3. Enable auditing for system users if you need to audit activities of sys user(optional)

SQL> alter system set audit_sys_operations=TRUE;

4. Set rsyslog facility and severity(needs database restart)

SQL> alter system set audit_syslog_level=local5.info scope=spfile sid='*';

5.  Restart database

SQL> shutdown immediate;
SQL> startup;

6. Edit rsyslog.conf file

#Saving oracle database audit records
local5.info          /u01_log/audit/RSYSLOG/dbaudit.log
#Send oracle database audit trail to remote rsyslog server
local5.info          @192.168.0.15

7. Restart rsyslog service

# service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

8. It is better to limit the size for audit log, or it may fill the space:

# vi /etc/logrotate.d/oracle.audit

#Created by MariK

/u01_log/audit/RSYSLOG/dbaudit.log {
 rotate 3
 compress
 missingok
 notifempty
 size 40G
 postrotate
 service rsyslog restart
 endscript
}

To check the syntax run :

# logrotate /etc/logrotate.d/oracle.audit

It will say if you have an error. If syntax is ok then output is nothing.