May 20

RMAN table recovery error

I had a user delete the data in a table that they didn’t mean to. It took them a week to notify us, so no chance to use flashback to get the data back. I was not looking forward to restoring 100TB of datafiles and recovery logs just to restart a couple GBs of data. Thankfully the database is running 19c, and rman can now do a table recovery.

I tried to recover the table

I had 20TB of space provisioned to /opt/tempam. To do table recovery, the system, sysaux, undo and the source table tablespaces are restored. in my case this should come to about 3TB.

After waiting an hour for everything to restore and roll forward, I get the following error in the final step where it should be exporting the table with datapump

This is a lie, there was plenty of space.

Turns out this is a known bug, Bug 9109785 : ORA-1555 TRANSPORT TABLESPACE FAILS WITH ORA-01555 DURING THE EXPDP PHASE. 

The work around is to prevent the database from being dropped after the failure by adding the “keep auxiliary” statement to the restore, and manually performing the datapump export.

Sep 17

Getting and formatting index ddl

This is a little useful sql I used this morning for stripping out all the additional information that dbms_metadata.get_ddl outputs. This strips the create index statements down to their bare bones.

Sep 02

pythonGit private repository

I built an online editor for modifying some files. They need to be checked into git after they are edited. Unfortunately there is no direct way of setting the username and password for the git repository. Instead you need to use http authentication, and put the username and password into the URL of the repository. This is just a little code snippet of what I needed to do to update the file. I left out the middle part updating the file, instead just put some junk code in to add a new line.


Apr 30

cx_Oracle ORA-24418

I am writing a web portal for a bunch of database tasks, and storing information in an oracle database. I am creating a connection pool but seeing this error.

This looks like an ORA-24418 out of sessions error. However when I check the database, there are only 120 of the 1200 available sessions being consumed. The alert logs don’t show any error at all which is really confusing.

After some digging, I found a bug report which states you will an ORA-24418 error if the pool is in the process of being resized.

Here is my session pool creation

adding the set mode parameter to the pool creation will cause a call to wait while a connection pool is started or resized instead of immediately erring out.

Problem solved

Mar 19

Patching 19.3 to 19.6

I’m not going to go through the entire patching process, there are lots of other sites that can walk you through this. What I am going to talk about is some issues that I had after patching. The patch to the binaries went fine, however when I tried to run Datapatch against my database, I got the following error

I have see this error before when the parameter remote_listener is not set on a database, but this is a single instance database.

I opened an SR with oracle support. After some trouble shooting, they recommended I remove the trailing slash from my ORACLE_HOME variable and restart the database.

So change

after restarting the database, I got a new error

So some progress. Something was causing a core dump and the connection was terminated.

Looking at the script it was failing on, prvtgwm.sql, that script is all related to the schema gsmadmin_internal

Looking at the patching log, I see the following

So looking back at the database, there are several package inside the GSMADMIN_INTERNAL schema that are invalid

I tried running utlrp several times, however I was able to compile most of the objects manually issuing compile commands.

I was then able to run Datapatch successfully

Jan 24

wrong number or types of arguments in call to ‘CHECKDATAFILEFORSTANDBY’

I started seeing this today in one of our databases

I found metalink article 2360045.1 which states that it is a catalog version mismatch, however running upgrade catalog doesn’t resolve the problem. 

I remember hitting this error a long time ago, and although I can’f find the reason why, running upgrade catalog two times in a row seems to fix the problem (so typing upgrade catalog 4 times). 

Sep 13

TDE Wallet on RAC

So I have been having trouble getting the oracle wallet to auto open, and it’s always messed up in gv$encryption_wallet

According to Oracle this is is because TNS_ADMIN is not set, so I set it and several other suggested variables.

After restarting the database, the ACFS volume now shows 

Sep 09

DBSNMP long running query

I noticed a long running query in one of my databases this morning.

Its computing tablespace size. The average runtime is 272.03 sec, or 4.5 minutes. This isn’t right, its running in a few seconds in other databases.

I started looking at the query in OEM, and noticed almost all the execution time was being spent on a FTS of X$KTFBUE. 

A little googling turned up the following article:

Fixed Table x$ktfbue has not statistics

My issue appears to be the same, there are not statistics on teh X$KTFBUE table.

So gather stats on the table

With the new statistics the query finishes in a few seconds.

May 14

ORA-01422: exact fetch returns more than requested number of rows

The other night on of my databases started throwing the following error.

From the RMAN trace

The solution here is actually quite simple, the rout table, which stores previous rman output from previous runs so that it can displayed in OEM had excessive rows in it. Since we don’t really use OEM to track our rman backups, we can safely purge them.

This resolved our issue. I was able to resync the catalog with out any further errors.

Apr 24

New Server

My old web server has been retired. I have been moving sites the last couple of days to a new box. I took the time to virtualize the machine and set everything up properly. The old box was starting to show it’s age with lots of modified configuration files and outdated software. The website seems to be loading significantly faster now too.

Older posts «