Archive for the ‘11gR2’ 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 »

As per http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmmaint.htm :

The CATALOG command enables you to add records about RMAN and user-managed backups that are currently not recorded in the RMAN repository, or to remove records for backups that are recorded.

Which, though I find the usage of “catalog” a bit unfortunate given the fact that there is also a repository referred to as a “catalog”, the command is quite handy.

Unfortunately, all Oracle references I have found only provide instructions for disk backups. Today though, I came across a post referring to an undocumented method of performing the needed actions with a tape backup.

Essentially, one just needs to configure a tape channel and run the command. For me, this was:

RMAN> configure channel device type ‘SBT_TAPE’
2> parms ‘ENV=(NB_ORA_CLIENT=myserver01),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64’;

followed by the command:

RMAN> catalog device type ‘SBT_TAPE’ backuppiece ‘mydb_al_reog5k26_1_1_822267974’;

and poof, the backup piece was registered and available to use for a restore.

As this is an undocumented route, i don’t know the deeper implications of it (if any) and thus would not run straight to it as a first step in a prod restore. But, for the test situations I have run so far, it has performed eloquently.

Read Full Post »

Trying to run:

ALTER TABLE my_table DROP CONSTRAINT my_constraint_pk;

ALTER TABLE my_table ADD CONSTRAINT my_constraint_pk PRIMARY KEY (my_col_1, my_col_2);

On 9i, this works fine. If memory serves, I believe it works fine on 10g as well. But on 11gR2 (not sure about R1), this statement results in:

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 51

ORA-00955: name is already used by an existing object

Though on both versions, an index is created by default, it ends up that on the newer version, it no longer drops the associated index when dropping the constraint.

So, in order to run this statement successfully, one must drop the index as well:

ALTER TABLE my_table DROP CONSTRAINT my_constraint_pk;

DROP INDEX my_constraint_pk;

ALTER TABLE my_table ADD CONSTRAINT my_constraint_pk PRIMARY KEY (my_col_1, my_col_2);

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 »

Trying to run dbua results in:

Could not get the database version from the “Oracle Server” component. The CEP file “/mypath/rdbmsup.sql” does not provide the version directive.

Check that the current db version is supported for direct upgrade to desired version with dbua. (note 870814.1 has the matrix for 11gR2)

Read Full Post »

Older Posts »