Archive for the ‘note to self’ Category

A note to self post for queries to use to check cursor counts presently in use and to troubleshoot ORA-1000: maximum open cursors exceeded:

select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
group by b.name;

This can be altered to focus in on a single user:

select sum(a.value), c.sql_text
from v$sesstat a, v$statname b, v$open_cursor c
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
and c.sid=a.sid
and c.user_name=’MYUSERNAME’
group by c.sql_Text

Note that using a query that doesn’t specify ‘opened cursors current’ such as one that pulls data from v$open_cursor will provide cached cursors as well thus giving a misleading answer.

More info:

Read Full Post »

To find processes based on a grep and kill them:

kill $(ps aux | grep mytext | grep -v grep | awk ‘{print $2}’)

Or, a non-grep -v grep route:

kill $(ps aux | grep [m]ytext | awk ‘{print $2}’

Read Full Post »

Data Guard broker reports:

Warning: ORA-16792: configurable property value is inconsistent with database setting

To find out what properties it is referring to:

show database ‘<dbname>‘ ‘InconsistentProperties’;

While trying to switch the protection mode, get:

ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Check the LogXptMode. (e.g. ASYNC is not compatible with MaxAvailability)

Read Full Post »

While doing a clone, after copying the datafiles over and recreating the controlfile, I issued:

SYS@> recover database using backup controlfile;

It asked for archive logs…gave it a few and entered ‘cancel’. All is well. Then entered:

SYS@> alter database open resetlogs;

Which resulted in:

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/mypath/data/system01.dbf’

To fix/get past the error, enter the recover command and then cancel it:

SYS@> alter database recover database until cancel using backup controlfile;

SYS@> alter database recover cancel;

SYS@> alter database open resetlogs;

Database altered.


Read Full Post »

In an effort to relocate files, several months ago, the default temp tablespace was changed (located on the new disks). Unfortunately, the old disks were pulled before the old temp tablespace was actually dropped…And no one could remember what the name of the tablespace was – info needed to drop it..

A variety of queries such as:

SQL> select tablespace_name from dba_temp_files;

resulted in:

select tablespace_name from dba_temp_files
ERROR at line 1:
ORA-01116: error in opening database file 201
ORA-01110: data file 201: ‘/path/temp01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

The magic answer ->


found the name of the old tablespace and thus was able to drop it.

Read Full Post »

Yet another note to self of sorts..

SQL> startup

resulted in:

ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

I need to copy over my full tuning notes, but in the meantime, step 1 to look at with this error is shmall.

cat /proc/sys/kernel/shmall

MOS (301830.1)  says to set it to

shmall = total_mem_allocated_to_sgas / PAGE_SIZE

to get PAGE_SIZE:

$> getconf PAGE_SIZE

Many other sites suggest setting it to

shmall = shmmax/PAGE_SIZE

Via Oracle Documentation, shmmax should be 1/2 physical memory. So, if you have 32G of memory and have a db with 25G allocated to the SGA, if you used the MOS calculation, with a page_size of 4096, shmall would = 6553600. Whereas if you went with the shmmax calculation, (assuming shmmax was set according to Oracle docs), shmall would = 4194304.

And, what if you have a db that needs a very high PGA? If you’re basing the server’s memory allocation calculation on the SGA, would that not result in less-than-ideal parameter settings?

Clearly both methods can result in significantly different results. Different enough that it makes me wonder if they both are potentially the proper way to do it, or if one version only produces the ideal results when the stars are lined up and your SGA is exactly what was used during testing and all other variables are set to default – and thus,  it is not necessarily wise to always follow said method.

I truly don’t know as I haven’t tested. (On my to-do list.) If anyone has test results, please share! In the meantime, I’m still starting with shmmax and tuning from there..

Read Full Post »

Running a test restore, I hit

Backup file <blahblah> not found in NetBackup catalog

I’ve hit this error several times in past restores, so it wasn’t a surprise. It just meant the restore would take a bit longer while I went through all the plausible causes to figure out what the actual problem was.

It turned into one of those ‘I know I’m missing something extremely simple but what is it?’ situations..In the end, it was – the script’s ‘sbt_tape  env=..’ had ORA_CLIENT  instead of NB_ORA_CLIENT.


Read Full Post »

Older Posts »