Tag: parameters

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, Performance, SQL | PLSQL

Parameter Workarounds: Changing your Entire Database’s Behavior Because of a Query

 , , , , ,

This post is just a reflection, basically based on my last post about Adaptive Query Optimization/SQL Plan Directives. Several times we find some situations that can be solved by setting a parameter (likely a “_”) and we just proceed with an “alter system” disregarding about all the impact of this.

This is specially important when talking about “optimizer” parameters even for bugs causing ORA-600’s or performance issues… Changing those parameters in system will affect not only the SQL with the error but all SQLs in database, which may cause a really bad effect in some cases.

bad-idea-sign

Ok Matheus, but what can I do?
Let’s to the obvious and generally applied methods:

More“Parameter Workarounds: Changing your Entire Database’s Behavior Because of a Query”

Database, Errors and Bugs, Linux

ORA-00845: MEMORY_TARGET not supported on this system (RHEL)

 , , , ,

# Solution:
Make sure that /dev/shm is mounted. You can check this by typing df -k at the command prompt. It will look something like this:

Filesystem Size Used Avail Use% Mounted on
…
shmfs 1G 512M 512M 50% /dev/shm

If you don’t find it then you will have to manually mount it as root user. The size should be more than MEMORY_TARGET or MEMORY_MAX_TARGET.

For example, if the MEMORY_TARGET is less than 2 GB, you should make like that:

#root: mount -t tmpfs shmfs -o size=2048m /dev/shm

I recommend you add an entry in /etc/fstab so that the mount remains persistent even after a reboot.
To make it, add the following entry in /etc/fstab:

shmfs /dev/shm tmpfs size=2048m 0 0

Helped?
Share this post!

Matheus.

ASM, Database, GoldenGate, RAC | Data Guard

GoldenGate: RAC One Node Archivelog Missing

 , , , , ,

The situation:

We have a GoldenGate on Allow Mode running some extracts on RAC One Node Database (reading the archivelogs). And then, suddenly, the instance crashes (network lost contact to the server) and the other instance (thread) was auto started by CRS. To the database no problems: The other node redologs was used during the startup recover and every thing is ok.

The application running with Weblogic serverpool and gridlink just had a little contention and continued the operation thought the started instance. The Goldengate switch was manually made, but some sequences was lost. What we found? the sequences was in the old thread’s redologfiles. It should be backed up if fast_start_mttr_target was different to zero. Buuut, the world is not so beautiful:

raconenodedb> show parameters mttr
NAME TYPE VALUE
------------------------------------
fast_start_mttr_target integer 0

How we solved?
Simple solution: identified the group/thread and made a cp from ASM. The copied redolog was used as archivelog on goldengate and everything was ok.

raconenodedb> select sequence#,group#,thread# from v$log where thread#=2 order by 1;
SEQUENCE# GROUP# THREAD#
---------- ---------- ----------
39636 6 2
39637 7 2
39638 8 2
39639 9 2
39640 10 2
ASMCMD> cp group_10.288.859482805 /oracle/grup10_thread2
copying +DGDATA/MYDB/ONLINELOG/group_10.288.859482805 -> /oracle/grup10_thread2

Easy like that.

Matheus.

Database, SQL | PLSQL

Getting Oracle Parameters: Hidden and Unhidden

 , ,

Today’s post is a quick post!
Very quick post! very very quick post!
But it’s a helpful post!

Connected as sys with sysdba:

select x.ksppinm name,
ksppdesc description,
y.kspftctxvl value,
y.kspftctxdf isdefault,
decode(bitand(y.kspftctxvf, 7), 1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.kspftctxvf, 2), 2, 'TRUE', 'FALSE') isadj
from sys.x$ksppi x, sys.x$ksppcv2 y
where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance')
and x.indx + 1 = y.kspftctxpn
order by name;

Matheus.