Archive for the ‘10gR2’ Category

The short history is that we have a tablespace that was filled with 150G. Doing some cleaning and relocating shrunk this down to what should be zero. In reality, the tablespace could be dropped as it is no longer in use, but we’ll be using it again soon so I just wanted to reclaim a large portion of space.

So, the standard process –

alter database datafile ‘/u02/mydatafile.dbf’ resize 1000m;

but this returned:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 50
ORA-03297: file contains used data beyond requested RESIZE value

I checked dba_extents:

select owner, segment_name, segment_type from dba_extents where tablespace_name=’MY_TABLESPACE’;

and no rows returned.

Puzzled, I went to master Google and found this post by Connor McDonald:
which says that things don’t always show in dba_extents, one must also check dba_segments – which showed the bin remnants of prior objects.

Ah yes, flashback.. For him, the following purge worked:

purge recyclebin;

but the data remained and thus the datafile still wouldn’t resize.
The magic fix:

purge dba_recyclebin;

and the datafile resized with ease. *sigh*


Read Full Post »

we run regular queries against dba_data_files and dba_free_space to check tablespace growth/available space. they seem to have been running fine until seemingly out of nowhere, this query began to timeout on our monitoring setup.

select a.tablespace_name tbs
, NVL(b.free,0.0) free
, a.total total
,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 pct
from (
select tablespace_name,sum(bytes)/1024/1024 total
from dba_data_files group by tablespace_name) A
( select tablespace_name,sum(bytes)/1024/1024 free
from dba_free_space group by tablespace_name) B
ON a.tablespace_name=b.tablespace_name
order by 1

on further research, dba_data_files was fine, but any queries against dba_free_space were extremely slow..

purge recyclebin

fixed the issue immediately..as per Oracle docs, this is expected behaviour.

Read Full Post »

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 »

While testing a new NetBackup client installation on a server, I received the error:

ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
VxBSACreateObject: Failed with error:
Server Status:  Communication with the server has not been initiated or the server status has not been retrieved from the serve

A version of the issue is listed here: http://www.symantec.com/business/support/index?page=content&id=TECH51356

It ends up, with the install, the logs directory was overlooked. (And just creating a ‘logs’ directory isn’t enough, it needs to have the full/proper expected structure.) Once I created that, the backup carried on.

Read Full Post »

Check that the oracle_sid is set.

Read Full Post »

I attempted to add a newly created database to an existing listener.ora file.

lsnrctl reload

followed by

lsnrctl status

and everything looked fine. The info was in the tnsnames.ora so all should be good for remote (non-sysdba) access.

However, when I tried to log in, I received:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

After a few tests, I discovered: due to space limitations, this database’s oracle home was not put in the usual spot – I created a symbolic link in that spot to the true home.  And, I used the symlink in the listener.ora.

Once the listener.ora was updated with the true home location and reloaded the listener, I could connect just fine..

Read Full Post »

I can’t count the times I’ve done this.
Okay. I probably could..and probably still with just one hand. But the point is, i’ve done it more than once and have now done it enough that I want to smack myself with each repeat.

SQL> alter system set log_archive_dest_1=’/u02/oradata/mydb/arch’ scope=both
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to “log_archive_dest_1” not allowed with SPFILE

Every time I do it, it takes me a minute (or longer!) to realize what’s wrong. (Which would be fine if I’d encountered it once..twice..but by now!?..sheesh!)   The fix:

SQL> alter system set log_archive_dest_1=’location=/u02/oradata/mydb/arch’ scope=both


Read Full Post »

Older Posts »