ORA-00245 control file backup failed target is likely on a local file system

Solution:

This happens when you havea RAC setup and you are backing up the Snapshot of the controlfile to a local disk instead of a shared disk.

In our setup we setup an ACFS file system on both nodes of RAC and changed RMAN parameter as below

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/ACFS/snapcf_mw1.f’;

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/ACFS/snapcf_mw1.f’;
new RMAN configuration parameters are successfully stored

Advertisements

RMAN database restore until time

I had a block corruption of my primary database, though we had a standby build we wanted to recover the Primary instead of failover to the Standby.
This recovery is possible when you have say a full backup made of the database till 9th May and all archivelogs from 9th May till 12th MAY are present.

Put the database in mount mode and restore it till the time before the corruption happened.

run {
set until time to_date(’12-May-2012 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’);
restore database;
recover database;
}

 

A Ballad For a DBA

Dedicated to Gwen Shapira

It’s a tale of woe and malice, a tale of a DBA
That the users learned to fear and to get out of his way
He would drop production tables when complaints had grown to loud
He would smile and feign surprise and then suggested “use the cloud”

He would backup every hour but would not deign to restore
“If it’s gone, just be more careful, it’s just me the backup’s for”
He would kill off all the queries that had gone on way too long
When the users would complain he would just say that they are wrong

When the database expanded
And the system was too slow
He just shrugged, “go talk to network –
Go tell them your tales of woe”

When the backups all are running and the database is gone
He won’t care about the users, just let an evil yawn
When the disks will come down crashing, if it’s way too late at night
He’ll just sets his cell to “silent”, waits until the morning light

When the database expanded
And the network was too slow
He just shrugged, “go talk to network –
Go tell them your tales of woe”

When the database expanded
I/O rates became too high
He just shrugged, “go talk to storage –
Or just go crawl off and die”

And the coders told him – “Buddy,
All your indices are wrong”
So he told them, “It’s your queries,
and your fault they take too long”

I've lost an archived/online REDO LOG file, can I get my DB back?

The following INIT.ORA/SPFILE parameter can be used if your current redologs are corrupted or blown away. It may also be handy if you do database recovery and one of the archived log files are missing and cannot be restored.

NOTE: Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.

_allow_resetlogs_corruption = true

This should allow you to open the database. However, after using this parameter your database will be inconsistent (some committed transactions may be lost or partially applied).

Steps:

  • Do a “SHUTDOWN NORMAL” of the database
  • Set the above parameter
  • Do a “STARTUP MOUNT” and “ALTER DATABASE OPEN RESETLOGS;”
  • If the database asks for recovery, use an UNTIL CANCEL type recovery and apply all available archive and on-line redo logs, then issue CANCEL and reissue the “ALTER DATABASE OPEN RESETLOGS;” command.
  • Wait a couple of minutes for Oracle to sort itself out
  • Do a “SHUTDOWN NORMAL”
  • Remove the above parameter!
  • Do a database “STARTUP” and check your ALERT.LOG file for errors.

Extract the data and rebuild the entire database.

ORA-38701: Flashback database log seq thread

The database does not startup, and gives errors like:
ORA-38737: Expected sequence number 2483 doesn’t match 2304
ORA-38701: Flashback database log 143 seq 2483 thread 1:

These errors can be seen in the Alert.log file. These errors occur and prevent the database from starting up because, at some point prior, we were not able to write to the flashback area.

Review the alert.log file prior to these errors on startup. You may see other information indicating an issue writing to the flashback area. For example, you may see something like:

ORA-38701: Flashback database log 36 seq 2305 thread 1…..
ORA-27041: unable to open file
OSD-04002: unable to open file

To get the database open,  try:

1. Mount the database:
SQL> startup mount;

2. Turn off flashback.
SQL> alter database flashback off;

3. Open the database:
SQL> alter database open;

4. Shutdown
SQL> Shutdown immediate;

5. Mount the database:
SQL> Startup mount;

6. Turn flashback back on.
SQL> alter database flashback on;

7. Open the database:
SQL> alter database open;

Note : 1 If your are on Oracle  10.2.0.4  your instance will not hang but the alert log will display information like below.

************************************************************
Thu May 20 08:32:43 2010
Errors in file /u1/oracle/OraSWX/admin/SWX/bdump/swx_rvwr_17519.trc:
ORA-38701: Flashback database log 1 seq 1 thread 1: “/u5/flashback/SWX/flashback/o1_mf_5z66yxwt_.flb”
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
*************************************
RVWR encountered an error when writing flashback database logs.
See error stack in alert log.  To avoid crashing the instance,
this instance has turned off flashback database.
*************************************

The database will disable flashback automatically to prevent the instance from hanging.

Backup Controlfile to Trace

How to backup the Oracle Control File?

There are two approaches: you either generate a binary image of the Control File, or you generate a text file script which will re-generate a Control File when run as a SQL script.

To create the binary image, issue the command ALTER DATABASE BACKUP CONTROLFILE TO ‘C:\SOMEWHERE\CONTROL01.BKP’; (obviously pick a destination and file name that are suitable for your needs).

To create the text script version, issue the command ALTER DATABASE BACKUP CONTROLFILE TO TRACE. That causes a file to be written to wherever your init.ora parameter USER_DUMP_DEST is pointing. The file name will be something like ‘ora_<some numbers>.trc’. You’ll have to track it down using the O/S date and timestamp (or you can take advantage of the fact that the “some numbers” bit is your Server Process ID Number -which you can determine from v$session and v$process).
The binary image is ready to work from the word go (with one hugely important proviso, of which more below).

The trace file is, however, a bit of a mess, and needs to be knocked into shape if it is to be of any use in a recovery situation. Firstly, it contains a whole heap of junk at the top (referencing your O/S, your Oracle version, dates and times and process or thread numbers). That’s got to go -which means deleting all of it, until the first line reads STARTUP NOMOUNT. Immediately before that line, you need a connect string, since you can’t ever issue the ‘startup’ command until you’ve connected as a Privileged User. Insert something appropriate, therefore (such as CONNECT / AS SYSDBA if using O/S authentication, or CONNECT SYS/ORACLE AS SYSDBA if using Password File Authentication). You may possibly also need to qualify the ‘STARTUP NOMOUNT’ line itself so that it references an appropriate init.ora (for example, STARTUP NOMOUNT PFILE=/SOMEWHERE/NON-DEFAULT/INIT.ORA). Other than that, the file is fine, and useable.

Under no circumstances should you ever need a backup of your Control File, of either type. You’d only use one if ALL copies of the Control File had become corrupt or had been destroyed (and the whole purpose of multiplexing your Control Files is to minimise those possibilities). But if that fateful day ever arrives, then you simply need to fire up Server Manager (or SQL Plus if using 8i or above), and issuing the command to run the trace file script (i.e., typing @name_of_script.ext). There’s no need to connect first -remember, that was the first line we added to the script earlier. If for some reason the Instance is already running, the script will fail -it needs a completely shut down system before it can do its stuff.

The trouble starts when you attempt to restore the binary version of the Control File backup. Because it was an exact, binary copy of a Control File, its SCN number will not agree with the SCN in the headers of all the data files -basically, the Master Clock is out of whack. You therefore have to issue the command “RECOVER DATABASE USING BACKUP CONTROLFILE;” to tell the system not to pay too much attention to the SCN of the Control File. Unfortunately, after you issue that command (and following any recovery that it might cause to take place), you must open the database with the command “ALTER DATABASE OPEN RESETLOGS;”.

That’s unfortunate, because the ‘resetlogs’ command basically forces the entire database to re-synchronise at time zero, which is fine for getting the database open, but is not exactly useful if you ever need to restore from your prior backups (taken when the database was at a time of, say, 10894329), or if you ever expect to be able to apply redo from priot archive logs (which were also taken when the database was at time 10894329 and earlier). Basically, a ‘resetlogs’ renders your database entirely vulnerable: there are no effective backups, and no effective archives. You are therefore supposed to immediately shut the newly-recovered database down, and perform a whole, closed backup of the entire database (which is not exactly a cheap option).

You might wonder why the use of the trace file does not cause this HUGE problem.  The answer is simple: it cheats. Contained within the trace file are locations of every data file in the system. When the file is run, it uses those locations to read the headers of all the data files, whereupon it picks the highest SCN it comes across as the one to write into the header of the Control File it is about to create. That means the re-constructed Control File is already in synchronisation with the data files, and no forced synchronisation to time Zero is therefore required.

So, what’s the best way of backing up the Control File? Answer: multiplex your Control Files so that a recovery is never needed in the first place. But if, despite that, you need some insurance, the Trace File is definitely the better way to go.  It doesn’t render your entire database exposed to failure, it doesn’t effectively trash all prior backups and archives, it works quickly, and well.

There’s only one proviso to this whole discussion: whatever backup method you choose, you need to keep it up-to-date.  Since the Control File contains pointers to the physical location of all data files and redo logs, any backup of that file needs to make sure that those pointers are accurate. Making a physical change to your database (for example, adding a new data file or tablespace, dropping a tablespace, moving or renaming a data file) will instantly render all prior Control File backups out-of-date.  Slightly unnervingly, changing a tablespace from read-write to read-only (or vice versa) also counts as a physical change to your database (because the Control File must always accurately identify any read-only data files).  After any of those operations, therefore, you need to take a fresh backup of the Control File.

It is always conceivable that you could edit a Trace File backup before using it to take account of any physical changes, but the syntax is not easy, and I don’t rate your chances of pulling it off. As for editing the binary copy -forget it!  Net result: keep taking the backups on a regular basis.  I usually recommend a chron job (for our NT friends, that’s an AT job!) which issues the ‘backup to trace’ command every night. It means you need a bit of house-keeping to avoid complete mayhem (and a million trace files) in the user_dump_dest, but it will guarantee a file which, at worst, can be used with the mere addition of a line or two to reference any data files created between the time the trace file was created and the time all Control Files went awol.

RMAN-06059: expected archived log not found. ORA-19625:

Whenever you are trying to backup Archivelogs from RMAN you might get the following error.

Starting backup at 25-FEB-10
current log archived
archived log /u2/archivelogs/1_12241_642439782.dbf not found or out of sync with catalog
trying alternate file for archivelog thread 1, sequence 12241
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/25/2010 10:05:30
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /backup_restor_only/1_12241_642439782.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

SOLUTION

If the archivelogs are no longer physically available, then you can issue the RMAN command “crosscheck copy of archivelog all”.  This command checks for the physical existence of the archivelog file and will change the V$ARCHIVED_LOG.STATUS of the affected archivelog from “A” for AVAILABLE to “X” for EXPIRED.   Once the archivelog file has a status of X, RMAN will no longer attempt to backup this archivelog file.

RMAN> crosscheck copy of archivelog all;

IMPORTANT NOTE: If there is no RMAN backup of the missing archivelog file, it is essential to perform a full-database backup. Otherwise, you may not be able to recover the database beyond missing log sequence number.