empty tablespace. datafiles won’t resize ORA-03297

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:
https://connormcdonald.wordpress.com/2012/11/23/why-cant-i-resize-my-datafile
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*

dba_free_space query slow

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
LEFT OUTER JOIN
( 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.

checking used cursor counts

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:
http://www.orafaq.com/node/758
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1089433252941
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:553222846752

catalog tape backup on rman

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.

constraint ora-00955 name is already used by an existing object

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);

java.sql.SQLException: Io exception: NL Exception was generated

It has been almost a year since my last post. And in the meantime, the blog has supposedly reached 15,000 hits. Kind of crazy..

I’ve gotten into a very bad habit of beginning to write my encountered errors in a notepad with the intention of transferring them to the blog at a later date – which is a habit I now hope to break as the whole point of this blog was to a. have a central spot where I could easily look up all those things I say “i know i saw this before…what did i do to fix it???” – and b.pay back the many other blogs and posts that have shared experiences and assisted me in learning about Oracle and troubleshooting over the years.

So, jabbering aside – back to the purpose of the blog – and hopefully back to updating it in a timely manner when i encounter an issue..

Today’s issue:
I’ve been testing variations of a jdbc Data Guard failover connect string with an app we have.

The string:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=off)(FAILOVER=ON)(ADDRESS=(PROTOCOL=tcp)(HOST=myserver01)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=myserver02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mydb.myhost)))

Which – though I’ve used this string before, resulted in the app reporting:

SEVERE: There was a problem executing a database call.
..
java.sql.SQLException: Io exception: NL Exception was generated

A very helpful error..
I found this post – https://forums.oracle.com/forums/thread.jspa?messageID=2860787 mentioning that they received this error due to a missing parenthesis before the “DESCRIPTION”.
On further review, I too hit this error due to missing parenthesis -> the middle of this section “(PORT=1521))(CONNECT_DATA=”
should read “(PORT=1521)))(CONNECT_DATA=”

Server Status: Communication with the server has not been initiated or the server status has not been retrieved from the serve

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.

brilliance

This has nothing to do with Oracle or Oracle errors. However, it is related to the title of the blog…and it made my day:

I can’t give proper credit as I don’t know the source of the image; could I, it would undoubtedly be with a tip of the hat.

The CEP file “/mypath/rdbmsup.sql” does not provide the version directive.

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)

ORA-12162: TNS:net service name is incorrectly specified

Check that the oracle_sid is set.