Skip to main content

Flashback Recovery Area full

Ok, I promise I will take more time to read things like manuals and other documents when it comes to new features in the database.

Take the Flashback Recovery Area (FRA). Oracle does store archive logs in there, which is cool as it speeds up the recovery. The bad thing is that the Oracle Database behaves like a small child. When you do not look it will do strange things.

So I was busy setting up the online backup of an infrastructure database. This one is still in 10.1.x while all the others in this environment are on 10.2.x .
For the RMAN I have a catalog (also 10.2.x) so I had some problems with the versions as the 10.1.x database refused to get into the 10.2.x RMAN catalog. I thought that I have plenty of space for the archivelogs in the ASM (everything was RAC of course).

I turned my attention to some other issues (aka not watching your kid for a moment). Then during an important demo for the client a developer told me that he cannot use the OID.

Of course I checked, but the OID was running, and the database was running as well (ps -ef | grep smon) . Ok, a quick look at the alert.log showed that the database was struggeling with ORA-19815. Apparently the archive log destination was full.
I checked with the ASM which I had cleaned up a day before. Still 30% free?!?!

Metalink revealed that the FRA was full. Now Oracle has done everything to shield the FRA from direct access. This means that I had to get rid of the archivelogs in the FRA with RMAN.
However I still wanted to use the available catalog. Finally I decided to have one backup with RMAN (10.1.x) without the catalog. As the database was already in limbo I went to some drastic measures:

- shutdown abort
- startup
- shutdown immediate
- startup mount
- alter database flashback off;
- alter database open;

Then I tried to set the db_recovery_files_dest_size to a lower value in order to trigger the cleanup process.

- alter system set db_recovery_files_dest_size=1G scope=both;

I also changed the db_flashback_retention_target from 1440 minutes t 120 minutes.

- alter system set db_flashback_retention_target=120 scope=both;


Obviously this did not help because the v$recovery_file_dest still showed that I had 0 bytes of reclaimable_space and 267 files in the FRA.

So now I took the non-catalog RMAN with a deletion of the archive logs. When doing this the FRA is cleaned up as well.

So everything was working again and nobody in the demo was aware of this (the infra is just used for some specific logins that were not used).

Conclusion: Databases behave like little kids, always be aware what they are doing. Fortunately there are manuals for the Database ;-)

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Fusion Middleware Forum in Valencia

Last week the 22nd Fusion Middleware and PaaS Partner Community Forum took place in Valencia, Spain. For me this was a very valuable experience - again as I have visited a number of #ofmForum before. Let me recap here the highlights of this meeting. After a great Welcome-Reception the evening before, where everybody had the chance to catch up with a large number of old (and soon-to-be new) friends, the conference started with a kind of the state of the union by Jürgen Kress. The community already has more than 8000 people. This - in a fact - is a tremendous achievement. Everybody agrees that this is only possible by the relentless work of Jürgen who puts a big effort into this. It shows that other areas inside the Oracle technology stack do not benefit by equivalent communities. Even other communities, when they exist at all, do not compete in the same league. So a VERY BIG THANK YOU for Jürgen is at its place here. After the opening a keynote from Alistair Hopkins showed ver...
Post a Comment
Read more

Oracle Streams Explorer

At the recent Oracle SOA Suite community forum in Budapest I had a hands-on experience with the Oracle Streams Explorer. Having worked with the Oracle Complex Event Processing and also some hands-on exercises with the new Oracle Event Processing, the Oracle Streams Explorer is a very easy to handle and useful addition to the area of near-real-time data insight and analysis. The user interface comes along in the new Oracle look-and-feel. You can select a number of areas like IOT, Risk and Fraud Management, Transportation and Logistics, Customer Experience and Analysis and Telecommunications. Within that you get a number of predefined patterns and resources. Defining your own solution can therefore be based on an existing solution in your catalog or simply by combining input streams and defining filters on them. Now plenty of examples can (and will) be named. The essence for me - and this is a message that I will convey to customers - is the fact that by using Oracle Streams Expl...
1 comment
Read more

Copy and Paste

I bounced into a funny thing when I setup a Data Guard Physical Standby Database. First I prepared some database init parameters in a document (actually I reused some old documentation I had done in a previous project). log_archive_dest_1 location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=osbsoadb The "alter system ..." command worked like a charm. During the preparations I needed to restart the database instance. To my surprise I received an error that there was an issue with an init parameter. ORA-16024:  parameter  LOG_ARCHIVE_DEST_1  cannot  be  parsed Google did not really help me. So I decided to create a pfile from the spfile and had a look into it. There it became evident what the error was. Somehow during the copy & paste the end-of-lines were copied as well - leaving my init parameter with some newlines in it. Made one line from it in the pfile, started the db and cr...
1 comment
Read more