Tag: Database

Database, Linux, Shell Scripts

How to Setup Automatic Startup and Shutdown of an Oracle Database on Linux (Not Using Oracle-Restart)

 , , ,

Ok, we all have done it several times. I, myself, made some scripts to do it in past. However, do you know there is an official way/script for that?
You can accomplish this with dbstart and dbshut scripts, which are located in the $ORACLE_HOME/bin directory.
This is documented for 12.1 in Stopping and Starting Oracle Software .

Of course that, if you have Oracle Clusterware configured, you can use Oracle Restart and SRVCTL tool, and Clusterware automatically starts and stops the Oracle database instances and listeners. Which is way better.
This post refers to official procedure in case you haven’t Clusterware configured.

Quick Guide:

More“How to Setup Automatic Startup and Shutdown of an Oracle Database on Linux (Not Using Oracle-Restart)”

Database, Errors and Bugs, Performance

Oracle Database Lock Performance / False Locks

 , ,

Hello all!
This is a very interesing one… what about database taking too long or even showing locks that don’t really exists?
I faced some weird situations related to the size of DBMS_LOCK_ALLOCATED.

After some research, seems root cause is descripted in MOS DBMS_LOCK_ALLOCATED Table Keeps Growing Eventually Depleting Tablespace (Doc ID 1054343.6).

The issue is:
– DBMS_LOCK_ALLOCATED is the table that keep all locks on database.
– This table keeps growing even though many of the locks are probably no longer used.
– Upon checking the EXPIRED column, it reveals that the locks’ expiration date is a year or more in the future.

Besides the mentioned “storage space issue” in MOS Doc, there is also the situation that we have actually 1073741823 “lockhandlers” available. And what if this ‘not releasing’ cause we run out of handlers? Unlike, but possible.
Having a big table can also cause bad performance on DBMS_LOCK.REQUEST, that is the basic mechanism for locks (passing the handler, lockmode, timeout and release instructions).

More“Oracle Database Lock Performance / False Locks”

Database, Errors and Bugs

Long running jobs AQ$_PLSQL_NTFN_%

 , ,

Hello all!

Some time ago I had an issue with some jobs running for 64 days. Cool han?
All jobs names were AQ$_PLSQL_NTFN_%. All activity of jobs were related to SQLID f7zggdz9p7bhk in wait event “Streams AQ: waiting for messages in the queue”.

# SQLID f7zggdz9p7bhk:
begin SYS.SCHEDULER$_JOB_EVENT_HANDLER(context => :1,reginfo => sys.aq$_reg_info(:2, :3, :4, :5, :6, :7),descr => sys.aq$_descriptor(:8, :9, :10, sys.msg_prop_t(:11, :12, :13, :14, :15, :16, :17, :18, sys.aq$_agent(:19, :20, :21), :22, :23), sys.aq$_ntfn_descriptor(:24), :25, :26), payload => :27, payloadl => :28); end;

Reviewing on MOS, found a match to Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8).

The root cause is procedure SYS.SCHEDULER$_JOB_EVENT_HANDLER keep waiting AQ PL/SQL Notification callbacks associated with scheduler job email notifications for messages in “SYS”.”SCHEDULER$_EVENT_QUEUE” which no longer exist.

From MOS, this can be easily solved with fix for Bug 16623661 – No email notification is sent by scheduler, wich is included in 12.1.0.1 (base) and affect versions 11.2.0.2 and later.
Also from MOS, and which I did, is drop these jobs. The problem is these jobs just restart and have the same problem again after automated recreation.

So, apply the fix or upgrade your database. 😉

Some related references:
Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8)
AQ$_PLSQL_NTFN Scheduler Jobs Executed in large numbers affecting Database performance (Doc ID 2001165.1)
Bug 21665897: FIX FOR BUG 14712567 CAUSES LARGE NUMBERS OF AQ$_PLSQL_NTFN_% TO BE SPAWNED

Hope it helps!
Cheers!

Database, SQL | PLSQL

Purge Recycle Bin Older than…

 , , ,

Hello all!

Have your recyclebin being full/big but don’t want to make a complete “PURGE RECYCLE BIN;”?
Want remove only older than x days (like older than 30 days)?

Please don’t:

DELETE from DBA_RECYCLEBIN where droptime<sysdate-30;
PURGE DBA_RECYCLEBIN where droptime<sysdate-30;

A good way to perform that is to execute output from:

# 30 Days
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' 
and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-30;

# 30 minutes
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' and 
to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(30/(24*60));

There is some reference here too:
How To Drop / Delete / Purge Recyclebin or DBA_RECYCLBIN For Objects Older Than x Days/minutes or selective purge of objects from recycle bin (Doc ID 1596147.1)

Hope it help you!
Cheers!

Database, Errors and Bugs

ORA-00600: [qkswcWithQbcRefdByMain4]

 , , , ,

Hello all,
This days I found this in a client’s 12c Database when trying to create a Materialized View:

ORA-00600: internal error code, arguments: [qkswcWithQbcRefdByMain4]

A perfect match to MOS ORA-00600 [qkswcWithQbcRefdByMain4] when Create MV “WITH” clause (Doc ID 2232872.1).

The root cause is documented on BUG 22867413 – ORA-600 CALLING DBMS_ADVISOR.TUNE_MVIEW.
The given solution is to apply Patch 22867413.

After applying patch, issue solved. 🙂

More“ORA-00600: [qkswcWithQbcRefdByMain4]”

Database

SQLNET.ORA Parameter: SQLNET.EXPIRE_TIME

 , , ,

Hello all,
Asking why to use this parameter? Why this is set in your environment? Or even how it can hep you? Here we go:

As per Oracle documentation, this parameter is used to avoid unused sessions to be kept open in database and locking resources.
It describe in minutes how much time a client/probe can be inactive before be ended.

I recently found an environment where this parameter was too low (1 minute), potentially causing some overhead in communication only for validations. By documentation, if it’s decided to enable it, Oracle recommends value “10”.
This way, after checking and no one be aware about this parameter reason to be there, I just suppressed him from SQLNET.ORA (going back to default “0”, which is equivalent to “disabled”), so we could even reduce the network workload.
However, now we are aware that in case we have abnormal closure of clients, we can have some unused connections opened consuming resources… Not a problem at this point… 🙂

More“SQLNET.ORA Parameter: SQLNET.EXPIRE_TIME”

Database, Errors and Bugs

ORA-07445: exception encountered: core dump [nstimexp()+45] [SIGSEGV] [ADDR:0x58] [PC:0x7F42ABB] [Address not mapped to object] []

 , , , ,

Hello all,
I had faced some occourrences of this error in a 11.2.0.1 database recently.

ORA-07445: exception encountered: core dump [nstimexp()+45] [SIGSEGV] [ADDR:0x58] [PC:0x7F42ABB] [Address not mapped to object] []

After some investigation I found a match to Bug 3934729.
This issue is originally to matched to Bug 6918493, that is a reintroduction of Bug 2752985 but it’s fixed in 11.2.0.1.
However, on upgrading to 11.2.0.1 it’s a hit on Bug 3934729 which is fixed in 11.2.0.2.

Recommended actions are:
– Upgrade databases do 11.2.0.2 or higher. (best solution, but may require more efforts to validate the upgrade).
– Apply Patch 3934729: RANDOM ORA-07445 CORE DUMPS FROM DATABASE AND ORA-3113 FROM APPLICATION
– Set sqlnet.expire_time=0 (workaround)
– Ignore error.

After some research I decided to apply workaround, based on recommended usage of sqlnet.expire_time (Next weeks post is about this parameter :)).
This might be the root cause for the ORA-03135: connection lost contact and the actual value of this parameter on environment was 1, which is a very low value.

So, check which action is more suitable for your environment!
Hope it helps 🙂

Below some additional informations on my situation:

More“ORA-07445: exception encountered: core dump [nstimexp()+45] [SIGSEGV] [ADDR:0x58] [PC:0x7F42ABB] [Address not mapped to object] []”

Database, Errors and Bugs, Migration | Upgrades | Patching

Online Data Patch Apply with multiple Databases on same Oracle Home: OPatch failed with error code 26

 ,

Hi all,
Tricky question, right? It’s easier than you think…
Actually, we don’t commonly think on those situations in first place, but it’s pretty common, specialy if considering server consolidation situations.

The trick is to use clause util enableOnlinePatch insted of apply after first database applying.
In this example I’m applying on-off patch 14084247 in online mode. Check:

# First Database:

[oracle@PRODSERVER 14084247]$ opatch apply online -connectString ORA11:sys::
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/app/product/11.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /opt/oracle/app/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/14084247_Apr_03_2017_14_17_24/apply2017-04-03_14-17-24PM_1.log


The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '14084247' to OH '/opt/oracle/app/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
All checks passed.
Backing up files...

Patching component oracle.rdbms, 11.2.0.4.0...
Installing and enabling the online patch 'bug14084247.pch', on database 'ORA11'.


Verifying the update...
Patch 14084247 successfully applied
Log file location: /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/14084247_Apr_03_2017_14_17_24/apply2017-04-03_14-17-24PM_1.log

OPatch succeeded.

All good, right?
Let’s see applying to second database with same command:

Second Database:

[oracle@PRODSERVER 14084247]$ opatch apply online -connectString OTHERORA11:sys::
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/app/product/11.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /opt/oracle/app/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/14084247_Apr_03_2017_14_17_45/apply2017-04-03_14-17-45PM_1.log


The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '14084247' to OH '/opt/oracle/app/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
Log file location: /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/14084247_Apr_03_2017_14_17_45/apply2017-04-03_14-17-45PM_1.log

Recommended actions: Please use 'opatch util applySql' for sql related patches or 'opatch util enableOnlinePatch' for online patches to add sids to already installed patch(es).

OPatch failed with error code 26

Beeep!
So, simply use clause util enableonlinepatch as per below.

Second Database (right way):

[oracle@PRODSERVER 14084247]$ opatch util enableonlinepatch -connectString OTHERORA11:sys:: -id 14084247
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /opt/oracle/app/product/11.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/app/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /opt/oracle/app/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2017-04-03_14-20-53PM_1.log

Invoking utility "enableonlinepatch"
Installing and enabling the online patch 'bug14084247.pch', on database 'OTHERORA11'.

OPatch succeeded.
[oracle@PRODSERVER 14084247]$

Ok, that’s it for today.

See you next week!

Database, Errors and Bugs, Linux

AIX: “WARNING: Heavy swapping observed on system in last 5 mins.”

 , , , ,

Quick one today!

Having message below in your 11.2.0.3 on AIX like this?

WARNING: Heavy swapping observed on system in last 5 mins. 
pct of memory swapped in [31.28%] pct of memory swapped out [3.81%]. 
Please make sure there is no memory pressure and the SGA and PGA are configured correctly. 
Look at DBRM trace file for more details.

Stand down, this issue is caused by unpublished Bug 11801934, mentioned in MOS False Swap Warning Messages Printed To Alert.log On AIX (Doc ID 1508575.1).

Basically happens because the v$osstat does not reflect proper stats for the swap space paging.

So, stay calm and see you next week!