Blog

ORA-00068: invalid value 0 for parameter _query_execution_time_limit, must be between 1952541791 and 6252643

Problem Description:

Users were seeing below error in application servers

03/05/2018 06:01:43 AM : 0 : ClaimVersionDao : 0 : 2 : Generic TPS Error. Check system application log for additional details. ADOException exception (could not execute query

[ SELECT distinct IC_CLAIM_ID
FROM claim_version
WHERE 1=1
AND CLAIM_ID = :p0 ]
Name:claimId – Value:20149913944300
[SQL: SELECT distinct IC_CLAIM_ID
FROM claim_version
WHERE 1=1
AND CLAIM_ID = :p0] : ORA-00068: invalid value 0 for parameter _query_execution_time_limit, must be between 1952541791 and 6252643)
03/05/2018 06:01:43 AM : 0 :  :  :  : An error occurred — attempting to run the last workflow step.

Troubleshooting and Solution:

Reviewing the alert log and patch details for 12.1.0.2 database involved, proved that we applied the patches when Database was up and running. This seems to be the cause of this issue and
thus restart fixed the issue. Also the messages for “_disable_image_check” started as soon as we finished the patching when databases in live. Instances need to be down when one-off
are applied. If in case one-off is applied while instance is up, a restart should fix the issue created by that. In this case looks like restart is done.
So no more action seems needed now. And root cause of the issue is patch 27589110 was accidentally applied when instances were running.

Cheatsheet for EBS 12.2 post upgrade tips

1. Problem: fs_clone not Copying files from Run to Patch filesystem during patching cycles

 

Solution:

Specify the synchronization step in the custom sync up driver $APPL_TOP_NE/EBSapps/appl/ad/custom/adop_sync.drv.
Add the actions within #Begin Customization and #End Customization section. The actions would be performed during ADOP synchronization phase.
# Sample For Unix Platform
rsync -zr %s_current_base%/EBSapps/appl/mbs/12.0.0 %s_other_base%/EBSapps/appl/mbs
rsync -zr %s_current_base%/EBSapps/comn/java/classes/atlhdc %s_other_base%/EBSapps/comn/java/classes
rsync -zr %s_current_base%/EBSapps/comn/java/classes/mbs %s_other_base%/EBSapps/comn/java/classes

 

2. Problem:Lower Case Custom files names were not Getting recognized on Release 12.2.5

 

Solution:

All the lower case names are extracted from 11i on windows source server to target OEL6 server during the custom top creation.
As part of retrofitting, Oracle Development Team created new files with Upper case as per R12 requirement.

 

3. Recommended EBS Profile change

 

Solution:
RRA: Enabled –> Yes
Concurrent:Report Copies –> from 1 to 0
SLA: Enable SRS Log/Output –> NO

 

4. Problem: Users’ password were getting locked upon login

 

Solution:
Passwords upon change in Release 12.2.5 is case-sensitive if Signon Password Case = SENSITIVE
Sign on Password Case Profile Does Not Work (Doc ID 1087519.1)

 

4. Please use below NFS Option recommended to be used for Linux For NFS V3 Doc ID 1375769.1
rw,nointr,bg,hard,timeo=600,wsize=65536,rsize=65536,nfsvers=3,tcp

 

5. Please use below commands to installed all rpms in one go needed to EBS 12.2 and RDBMS 12.1

 

wget http://public-yum.oracle.com/public-yum-ol6.repo
yum install oracle-rdbms-server-12cR1-preinstall-1.0-14.el6.x86_64
yum install oracle-ebs-server-R12-preinstall-1.0-7.el6.x86_64
ldconfig –v

Control File Backups for oldest 3 full backups were getting deleted and not maintaining all control file backups from all backups on disk

RMAN backup keeps the backup metadata information in the reusable section of the controlfile. It depends on the parameter CONTROL_FILE_RECORD_KEEP_TIME. CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and there is not enough space then it will delete the oldest record, which are aged enough.

Backup retention policy is the rule to set regarding which backups must be retained (whether on disk or other backup media) to meet the recovery and other requirements.

If the CONTROL_FILE_RECORD_KEEP_TIME is less than the retention policy then it may overwrite reusable records prior to obsoleting them in the RMAN metadata. Therefor it is recommended that the CONTROL_FILE_RECORD_KEEP_TIME should set to a higher value than the retention policy.

 

NOTE:  Best practice is to NOT set control_file_record_keep_time to a value greater than 10.    If you need retention greater than this in the controlfile, you should use an RMAN catalog.

 

Formula

CONTROL_FILE_RECORD_KEEP_TIME = retention period + level 0 backup interval + 1

 

For e.g.

e.q. level 0 backup once a week with retention policy of a recovery windows of 14 days then in this case the CONTROL_FILE_RECORD_KEEP_TIME should be 14+7+1=22

 

Please note to set the retention policy of “14” days in this RMAN configuration format “CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 days;” and NOT “CONFIGURE RETENTION POLICY TO REDUNDANCY 14;” (Redundancy keeps no of backup pieces includes level-0/level-1/archives and NOT number of days)

 

Interesting ORA-00600 Bug during RMAN recovery

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/06/2017 12:02:20

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile ‘+RECO_DAL/DWPREPRD/ARCHIVELOG/2017_07_06/thread_1_seq_85714.395.948615485’

ORA-00283: recovery session canceled due to errors

ORA-00600: internal error code, arguments: [3020], [219], [709377], [919261953], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 219, block# 709377, file offset is 1516249088 bytes)

ORA-10564: tablespace ODS

ORA-01110: data file 219: ‘+DATA_DAL/DWPREPRD/DATAFILE/ods.501.948545309′

ORA-10560: block type ’58’

This is a known Oracle Bug where RMAN creates corrupted free blocks during recovery and causes related errors. There is no fix to this issue and work around is to use “blocks all” in RMAN backup script.

Note: For next execution, use below change in the rman backup script to take the backup:

backup as compressed backupset incremental level 0 database tag ‘edw_edwbkp’ plus archivelog tag ‘edw_edwbkp’ delete all input;

to:

backup blocks all as compressed backupset incremental level 0 database tag ‘edw_edwbkp’ plus archivelog tag ‘edw_edwbkp’ delete all input;

Security Vulnerability Validation and Fixes on Oracle Database Appliance

Security Team executed their inbuilt security scans and provides the security scan report to the IT team to take appropriate actions.  Below are few custom vulnerabilities for NTP, SNMP, ICMP, TCP protocols and VNC software as stated in our security scan report for ODAs. Please perform careful discussions, planning and impact analysis before implementing any of the below changes.

NTP

Network Time Protocol (NTP) is a networking protocol for clock synchronization between computer systems over packet-switched, variable-latency data networks.

Below are the details from security scan report for NTP vulnerability.

Vulnerability Title Vulnerability Solution
NTP clock variables information disclosure Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Traffic amplification in clrtrap feature of ntpd Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
 NTP: Traffic Amplification in reslist feature of ntpd Disable NTP queries  – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Information disclosure in reslist feature of ntpd (CVE-2014-5209) Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Traffic Amplification in listpeers feature of ntpd Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.
NTP: Traffic Amplification in peers feature of ntpd Disable NTP queries – Apply a restrict option to all hosts that are not authorized to perform NTP queries.

As per the vulnerability solution to disable NTP queries, add the following restrictions in /etc/ntp.conf and restart ntp service.

restrict default kod nomodify notrap nopeer noquery

restrict 127.0.0.1 – (restricts local clock)

server  iburst

ICMP

The Internet Control Message Protocol (ICMP) is a supporting protocol in the Internet protocol suite. It is used by network devices, like routers, to send error messages and operational information indicating, for example, that a requested service is not available or that a host or router could not be reached. ICMP differs from transport protocols such as TCP and UDP in that it is not typically used to exchange data between systems, nor is it regularly employed by end-user network applications (with the exception of some diagnostic tools like ping and traceroute).

Below are the details from security scan report for ICMP vulnerability.

Vulnerability Title Vulnerability Solution
ICMP timestamp response * Linux – Disable ICMP timestamp responses on Linux

As per vulnerability solution to disable ICMP timestamp responses on Linux, use the below command to create a RULE to reject ICMP timestamp response:

ipchains -A input -p icmp –icmp-type timestamp-request -j DROP ipchains -A output -p icmp –icmp-type timestamp-reply -j DROP

 

SNMP

Simple Network Management Protocol (SNMP) is an Internet-standard protocol for collecting and organizing information about managed devices on IP networks and for modifying that information to change device behavior. Devices that typically support SNMP include routers, switches, servers, workstations, printers, modem racks and more.

Below are the details from security scan report for SNMP vulnerability.

Vulnerability Title Vulnerability Solution
Default or Guessable SNMP community names: public Secure the SNMP installation    * If you do not absolutely need SNMP, disable it.
SNMP credentials transmitted in cleartext Secure the SNMP installation    * If you do not absolutely need SNMP, disable it.

As per vulnerability solution to disable SNMP, Issue below commands to disable snmpd packets from server.

To stop service: Service snmpd stop

To disable service at boot time: Chkconfig snmpd off

TCP

The

Transmission Control Protocol (TCP) is one of the main protocols of the Internet protocol suite. It originated in the initial network implementation in which it complemented the Internet Protocol (IP). Therefore, the entire suite is commonly referred to as TCP/IP. TCP provides reliable, ordered, and error-checked delivery of a stream of octets between applications running on hosts communicating by an IP network.Below are the details from security scan report for TCP vulnerability.

Vulnerability Title Vulnerability Solution
TCP timestamp response Disable TCP timestamp responses

As per vulnerability solution to disable TCP, update below parameters to mitigate the risk.

sysctl -w net.ipv4.tcp_timestamps=0

(OR)

vi /etc/sysctl.com –> add net.ipv4.tcp_timestamps=0

run sysctl -p /etc/sysctl.conf

VNC

In computing, Virtual Network Computing (VNC) is a graphical desktop sharing system that uses the Remote Frame Buffer protocol (RFB) to remotely control another computer. It transmits the keyboard and mouse events from one computer to another, relaying the graphical screen updates back in the other direction, over a network.

Below are the details from security scan report for VNC vulnerability.

Vulnerability title Vulnerability Solution
VNC remote control service installed Fix VNC remote control service installed. Remove or disable this service. If it is necessary, be sure to use well thought out (hard to crack) passwords

 

X5.2 Oracle Database Appliance System IO Assessment

Testing Overview

This is a through analysis of the Oracle Database Appliance (ODA) System IO performance. The data collected during testing was carefully reviewed.

Test Scenario

There is a total of sixteen (16) HDD Data disks in the Production Primary X5-2 ODA device. During testing, the HDD Disk IOPS Stats were captured for each disk. The test aimed at collecting performance statistics for Individual Disk Wise IO during each of the planned Test Run(s).

Test Objective(s)

The main objective for the test was to collect performance statistics for Individual Disk Wise IO per HDD Disk and then compare the performance of the X5.2 MI-CXT database against the Oracle Database Appliance Standard Performance Ratings for the main data processing metrics:

  1. IO Per Second (HDD/Flash)
  2. MB Per Second (HDD/Flash)
  3. Log Generation Rate (MB/Sec)

 

For the purposes of these performance tests, the Oracle Database Appliance X5-2 test results were measured against the Oracle Database Appliance Sizing Templates (OLTP)[1] where:

 

Configuration Parameter Where Value Equals
CPU Count 16 (vCPU)
SGA (Gb) 27 (Gb)
PGA (Gb) 10 (Gb)
Flash (Gb) 12 (Gb)
Log Buffer Size (Mb) 16 (Mb)
Processes Redo Log 2 (Gb)
Processes 3000 (Max)

 

Test Standards

 

Pass/Fail Standards

Standard Performance Ratings: Main Data Processing Metrics Optimal Threshold Value for HDD/Flash
A. IO Per Second (HDD/Flash) 750/75000
B. MB Per Second (HDD/Flash) 1500/1500
C. Log Generation Rate (MB/Sec) 27.3

 

PASS/FAIL Criteria
PASS= Where System IO < Optimal Threshold
FAIL = Where System IO > Optimal Threshold

 

Test Summary of Findings            

System IO Overall Performance

Disk IO per second during each Test Run fluctuated between minimum 150 to maximum 400 ops.

Test Results Outcome = Pass 

For details per Test Run, please see attached.

Test Conclusion(s)

The X5.2 IOSTAT results from OS watcher reported Disk IO per second fluctuations between minimum 150 to maximum 400 ops for each Test Run.

With the Read & Write (RW) per second performance tested using 8k blocks at different intervals, maximum 2500 MB per second during read operation(s) and 653 MB per sec during write operation(s) was observed.

Therefore, based on these outcomes, X5-2 ODA HDD Disks clearly demonstrated to have enough System IO capacity for the task as per the ODA performance standards mentioned in the table shown below[1].

In fact, the X5-2 IO Per Second and MB Per Second can be scaled up to max 1500 IO per second/3000 MB per second with ONE X5-2 storage shelf and can be doubled with TWO X5-2 storage shelfs.

AWR Findings for DBA Consideration

Manual Data Read & Write (RW) Tests

  1. 1 GB of Data Write per seconds with 8k block is 350Mb/s to 650 Mb/s (approx).

 

[root@nasodacxt0201d1 datastore]# time dd if=/dev/zero of=testfile bs=8k count=128000

128000+0 records in

128000+0 records out

1048576000 bytes (1.0 GB) copied, 1.60589 s, 653 MB/s

 

real    0m1.700s

user    0m0.030s

sys     0m1.599s

[root@nasodacxt0201d1 datastore]#

 

  1. 1 GB of Data Read per seconds with 8k block is 1 to 2 Gb/s (approx)

 

[root@nasodacxt0201d1 datastore]# time dd if=testfile of=/dev/null bs=8k

128000+0 records in

128000+0 records out

1048576000 bytes (1.0 GB) copied, 0.405152 s, 2.6 GB/s

 

real    0m0.407s

user    0m0.016s

sys     0m0.382s

[root@nasodacxt0201d1 datastore]#

 

db file sequential read 379,827 8438.3 22.22 52.4 User I/O
db file parallel read 86,190 6872 79.73 42.7 User I/O

 

I/O Wait Definitions

The “I/O Wait” is an administrative wait class such as log files and it originates from Oracle Operations. User I/O is an application wait class such as a database file (dbf) and it originates from User (Application) SQL Queries.

 

  • User IO is an application related wait and NOT system IO.

          AWR report shows User IO as Top Wait Classes by Total Wait Time consuming                      95.9% of DB Time during 30-Jan-18 01:00:19 to 30-Jan-18 02:00:28 and 51.3% of DB              time during 30-Jan-18 02:00:28 and 30-Jan-18 03:00:38

  • With User IO events listed as the top two (2) out of the Top 10 Foreground Events by  Total Wait Time, it appears that read operations on the datafiles lying on HDD maybe behind the high database IO we have observed. It is our suggestion for our CHC CXT Logical DBA to inspect the Application SQL scripts as ordered by User I/O Wait Time to determine if further SQL Tuning may be needed.

 

db file sequential read 379,827 8438.3 22.22 52.4 User I/O
db file parallel read 86,190 6872 79.73 42.7 User I/O

 

When a DBA sees “User I/O” as a major wait event, SQL tuning is always the best answer. Industry standards best practices for SQL tuning usually involve adding missing indexes, hints, SQL profiles, etc.

————————————————————————————————————

Best Practices For Disk IO Improvement(s) for Database on ODA

Below are the some Oracle DBA industry best practice recommendations for Improved disk I/O.

 

I: Enable Smart Flash Cache below

ALTER SYSTEM SET DB_FLASH_CACHE_FILE = ” SCOPE=SPFILE;

ALTER SYSTEM SET DB_FLASH_CACHE_SIZE = 30G SCOPE=SPFILE;

 

II: Database Auditing Configuration

If auditing needs to be enabled, move aud$, fga$ out of SYSAUX tablespace to a custom tablespace.

 

III: ODA recommendations

DB parameter ‘db_lost_write_protect’ to NONE

 

 

[1] http://www.oracle.com/technetwork/database/database-appliance/documentation/oda-eval-comparing-performance-1895230.pdf.