Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘RMAN’

« Previous Entries

RMAN Error: ORA-01152: file 1 was not restored from a sufficiently old backup & ORA-01110:

Posted by FatDBA on January 15, 2014

RMAN Scenario:
DB: Oracle 11g r2
OS: Linux – RHEL 5
Error: “ORA-01152: file 1 was not restored from a sufficiently old backup” when tried to open the database.

STATUS
==============
SQL> @db

NAME      ROLE       LOG_MODE     OPEN_MODE      DG_BROKE FORC_LGNG   CUR_SCN        CKPT_CHG        CTRLFIL_SEQ CTRLFL_CNG      ARCH_CNG       PROT_MOD
——— ———- ———— ————– ——– ———– ————– ————— ———– ————— ————– ——————–
SAIRAM     PRIMARY    ARCHIVELOG   MOUNTED        DISABLED YES         0              32913920280       795938526 32913922263     32913811789    MAXIMUM PERFORMANCE

SQL> select open_mode from v$database;
OPEN_MODE
————–
MOUNTED

This is what’s happening with the database when tried to open it.
Throwing error which reads System datafile is restored from an old backup.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/dbmount212/sairam/datafiles/system01.dbf’

When tried to recover the database it’s asking for sequence number 75, which is not available (Conformed, as asked sequence is not available in ARCHIVE destination).

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 32913922263 generated at 01/14/2014 10:57:39 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922263 for thread 1 is in sequence #75

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log ‘/dbmount212/oracle/sairam/archivelog/1_75_833707787.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log ‘/dbmount212/oracle/sairam/archivelog/1_75_833707787.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/dbmount212/sairam/system01.dbf’

There is a possibility that the sequence might be struck inside of any redo log file and has not been archived.
Let’s query required sequence number (75) using one of the dynamic view v$log

SQL> select * from v$log where SEQUENCE#=’75’;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE#
———- ———- ———- ———- ———- ———- — —————- ————-
FIRST_TIM NEXT_CHANGE# NEXT_TIME
——— ———— ———
9          1         75  524288000        512          1 NO  CURRENT             3.2914E+10
14-JAN-14   2.8147E+14

Yes,the log is inside group number 9. Let’s check the location of the related log file using v$logfile;

SQL> SELECT * FROM V$LOGFILE where group#=’9′;

GROUP# STATUS  TYPE
———- ——- ——-
MEMBER
—————————————————————————————————-
IS_
—
9         ONLINE
/var/log/oracle/redo/onlinelog/redo07.log
NO

Let’s try to recover the database again.

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 32913922872 generated at 01/14/2014 11:17:33 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922872 for thread 1 is in sequence #75Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/var/log/oracle/redo/onlinelog/redo09.log
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 69 is unknown – rename to correct file
ORA-01110: data file 69: ‘/dbmnt12/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00069’
ORA-01157: cannot identify/lock data file 69 – see DBWR trace file
ORA-01111: name for data file 69 is unknown – rename to correct file
ORA-01110: data file 69: ‘/dbmnt12/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00069’

Alright, it’s asking for one datafile (69) which is unknown to the controlfile. Yes, this is right. As we have created a test datafile before that controlfile backup.
As it’s of no importance, we’d dropped the datafile.

SQL> alter database datafile 69 offline drop;
Database altered.

Let’s try again to recover the database using the log file ‘/var/log/oracle/redo/onlinelog/redo09.log’.

SQL> recover database using backup controlfile ;
ORA-00279: change 32913922872 generated at 01/14/2014 11:17:33 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922872 for thread 1 is in sequence #75

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/var/log/oracle/redo/onlinelog/redo09.log
Log applied.
Media recovery complete.

Okay, when provided exact filename of the logfile, it has accepted it and applied and came up, hence media recovery is completed.
Now we can go and try to open the database.

SQL> alter database open resetlogs;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /dbmount212/oracle/sairam/archivelog/
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

And we are up!
Below provided is the list of incarnation of the database and the last line with a new incarnation created on today’s date.

SQL> select * from V$DATABASE_INCARNATION;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
———— —————– ——— ———————– ——— ——- ———— —————— ————————–
1            602821 05-MAY-10                       1 12-MAR-08 PARENT     718177612                  0 NO
2         148027929 19-MAY-10                  602821 05-MAY-10 PARENT     719411741                  1 NO
3        9199856900 28-AUG-11               148027929 29-MAY-10 PARENT     760357356                  2 NO
4        1.9412E+10 11-AUG-12              9199856900 28-AUG-11 PARENT     791021290                  3 NO
5        3.2910E+10 09-DEC-13              1.9412E+10 1-AUG-12 PARENT     833707787                  4 NO
6        3.2914E+10 15-JAN-14              3.2910E+10 09-DEC-13 CURRENT    836892925                  5 YES

6 rows selected.

Hope That Helps
Prashant Dixit

Advertisement

Posted in Advanced | Tagged: , | 4 Comments »

RMAN-06900/RMAN-06901 and ORA-19921: maximum number of 64 rows exceeded

Posted by FatDBA on November 22, 2013

Received one of the odd and obscure RMAN/ORA errors of its kind While trying to connect with RMAN in Red Hat Enterprise Linux 5 and have wasted almost several minutes of my precious time, until i realized that there is one old RMAN session alive and was connected from the another terminal and was one day old before getting this error.

Okay so here is the case, while trying to connect with the target database in RMAN we have started recieving several RMAN and ORA errors …

[oracle@prashant1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Nov 22 21:30:38 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: QRCL (DBID=859215136)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded

RMAN> host;

Let’s explore more aboput the ora error using my favorite utility OERR

[oracle@prashant1 ~]$ oerr ORA 19921
19921, 00000, “maximum number of %s rows exceeded”
// *Cause:  The maximum number of rows in the V$RMAN_STATUS or V$RMAN_OUTPUT
//          table has been exceeded.
// *Action: Close some of existing and unused RMAN connections and sessions.

Let’s check how many and how old the rman sessions exists in my database.

[oracle@prashant1 ~]$ ps -ef|grep rman
oracle   25970 25861  0 Nov21 pts/1    00:00:50 rman target /
oracle   29999 29863  0 21:30 pts/4    00:00:00 rman target /
oracle   30113 30088  0 21:33 pts/4    00:00:00 grep rman

[oracle@prashant1 ~]$ date
Fri Nov 22 21:34:05 IST 2013

Alright, so there is one of the session with OS ID – 25970 running from November 21 from Terminal (tty 1) –
Let’s kill the session and try to connect with the Recovery Manager once again.

[oracle@prashant1 ~]$ kill -9 25970

[oracle@prashant1 ~]$ ps -ef|grep rman
oracle   29999 29863  0 21:30 pts/4    00:00:00 rman target /
oracle   30115 30088  0 21:34 pts/4    00:00:00 grep rman

Okay, so it’s gone and no more under the process list.

[oracle@prashant1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Nov 22 21:34:15 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: QRCL (DBID=859215136)
RMAN>

Okay and we have not getting list of those errors what we previously encountered and issue is F.I.X.E.D

Hope That Helps
Prashant Dixit

Posted in Advanced | Tagged: , | 1 Comment »

Snapshot ControlFile in RAC Systems and related errors (ORA-00245)

Posted by FatDBA on June 17, 2013

Even after a good Database Upgrade plan one can face issues related to performance, Functionailities, Backups etc. Today I’m going to discuss one of the case that was happened during a production upgrade from 10g r2 -> 11g r2.

We had our database full backup scheduled every night with configuration like – Autobackup ON, Dataa transfer directly to SBT Mediums (Tapes), OPTIMIZATION ON and other basic settings/configurations.
While performing status check next morning we discovered backup got failed with error message:

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

Error
———
Starting Control File and SPFILE Autobackup at 2013-09-06:21:25:34
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_SBT_TAPE_1 channel at 09/06/2013 21:25:36
ORA-00245: control file backup failed; target is likely on a local file system

As we have AUTOBACKUP functionailty ON which takes backup of critical files like controlfile file whenever the database structure metadata in the control file changes and whenever a backup record is added.

Reason:
Fro 11gR2 onwards in RAC database, due to the changes made to the controlfile backup mechanism, any instance in the cluster may write to the snapshot controlfile. Due to this snapshot controlfile need to be visible to all instances.
A snapshot controlfile in 11gr2 must be reachable by all nodes in RAC Environment. If the snapshot controlfile is not available or not shared RMAN will throw such errors during backup operations.
Documentation Link: http://docs.oracle.com/cd/E11882_01/rac.112/e16795/rman.htm#i455026

Solution:
To avoid such situations always keep your snapshot controlfile on a shared location so that it could be accessible by all nodes when needed.

RMAN> configure snapshot controlfile name to ‘/disk02/rmaninfo/snapcontrol/snapcf_cesc1.f’;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/disk02/rmaninfo/snapcontrol/snapcf_cesc1.f’;
new RMAN configuration parameters are successfully stored.

Okay now we have the solution of the problem let’s discuss more about Snapshot Controlfile and about this new change in 11g r2.
– When RMAN performs any operation that requires a consistent view of the control file (such as a backup), it will first create a copy of the control file. This copy is called the snapshot control file. The snapshot control file will be used for the duration of that operation and will be overwritten by any subsequent operation. Even related operations (say, during a backup database plus archive-log operation that does an archive log backup, a database backup, and then another archive log backup) will use newly created snapshot control files, one for each operation.

Why and what’s the need of this new change ?
From 11gR2 onwards, the controlfile backup happens without holding the controlfile enqueue.

Now, what’s this ‘ControlFile Enqueue‘ ?
When we need to back up or resynchronize from the Control file by RMAN, that first creates a snapshot or consistent image of the control file.
If one RMAN job is already backing up the control file while another needs to a new snapshot control file, then may see error:

RMAN-08512: waiting for snapshot controlfile enqueue for 1900 seconds

A job that must wait for the control file enqueue waits for a brief interval and then successfully retrieves the enqueue. RMAN makes up to five attempts to get the enqueue and then fails the job. So, finally with oracle 11g r2 we have the solution of this situation (In RAC Env) by keeping snapshot controlfile on shared location.

Posted in Advanced | Tagged: , | 1 Comment »

Image Copies VS BackupSets

Posted by FatDBA on February 7, 2013

Difference between Image Copies and BackupSets (RMAN):

Image Copies could be of two types:
1. RMAN Image Copies
2. O.S Image Copies

RMAN Image Copies
Use the RMAN copy command to create an image copy. If the original file needs to be replaced, and if the image copy is of a datafile, then you do not need to restore the copy. Instead, Oracle provides a switch command to point the control file at the copy and update the recovery catalog to indicate that the copy has been switched. Issuing the switch command in this case is equivalent to issuing the SQL statement ALTER DATABASE RENAME DATAFILE. You can then perform media recovery to make the copy current. They have an advantage of checking for Corrupt Blocks as they are being read by RMAN.

O/S Image Copies
Oracle supports image copies created by mechanisms other than RMAN, also known as O/S copies. For example, a copy of a datafile that you make with the UNIX cp command is an O/S copy. You must catalog such O/S copies with RMAN before using them with the restore or switch commands.
You can create an O/S copy when the database is open or closed. If the database is open and the datafile is not offline normal, then you must place the tablespace in hot backup mode, that is, issue the SQL statement ALTER TABLESPACE BEGIN BACKUP before creating the copy.

BackupSets:
A backup set consists of one or more files in an RMAN-specific format, known as backup pieces. By default, a backup set consists of one backup piece. When backing up datafiles to backup sets, RMAN is able to skip some datafile blocks that do not currently contain data, reducing the size of backup sets and the time required to create them. This behavior, known as unused block compression, means that backups of datafiles as backup sets are generally smaller than image copy backups and take less time to write.

Main Differences:
•RMAN supports binary compression of backup sets, where the backup set contents are compressed before being written to disk using a compression algorithm tuned for compression of datafiles and archived log files. But with Image copy no compression is done.

•A backup set never contains empty blocks. As RMAN passes through the datafiles, it simply skips blocks that have never been used. But image copy contains empty blocks. This means that backups of datafiles as backup sets are generally smaller than image copy backups and take less time to write.

•Incremental backups can’t be performed with Image copy but incremental backups can be taken over backup sets.

•RMAN can take backup of backup sets directly to tape, if you have installed the RMAN drivers for your tape library.But RMAN can’t take backup of image copies directly to tape.

Posted in Advanced | Tagged: | 4 Comments »

KEEP Clause * Error: ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes*

Posted by FatDBA on January 13, 2013

KEEP option with RMAN:
If you have any backup criterion set and want to override settings then there is ‘KEEP FOREVER | UNTIL TIME’ clause from recovery manager which will help you to achieve required. There are certain conditions that’s needs to be fulfilled before using FOREVER clause.

Conditions:
1. You should be connected with the CATALOG server  (If using KEEP FOREVER Clause. No need if using KEEP UNTIL TIME Clause).
2. Oracle will not allow you to save backup pieces/set’s inside the FRA. You have to change the backup directory.

Let’s take an example:

[oracle@localhost ~]$ rman target / catalog rman/oracle90@orcl;
RMAN> backup current controlfile keep forever;

Starting backup at 13-JAN-13
current log archived

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/13/2013 18:35:23
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

Solution:
RMAN> backup current controlfile keep forever format ‘/u01/%U’;

Starting backup at 13-JAN-13
current log archived

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
channel ORA_DISK_1: finished piece 1 at 13-JAN-13
piece handle=/u01/20nvb5gk_1_1 tag=TAG20130113T183811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
channel ORA_DISK_1: finished piece 1 at 13-JAN-13
piece handle=/u01/21nvb5gm_1_1 tag=TAG20130113T183811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

current log archived
using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=66 RECID=39 STAMP=804623897
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
channel ORA_DISK_1: finished piece 1 at 13-JAN-13
piece handle=/u01/22nvb5gq_1_1 tag=TAG20130113T183811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-JAN-13
channel ORA_DISK_1: finished piece 1 at 13-JAN-13
piece handle=/u01/23nvb5gr_1_1 tag=TAG20130113T183811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JAN-13

Posted in Advanced | Tagged: , , | Leave a Comment »

Recovering Parameter File

Posted by FatDBA on January 2, 2013

Today while working discovered that both my Pfile and SPfile from one of my test database are missing which we realized after a bounce, when it failed to start and started throwing error message about loss with it’s previous location.

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/10.2.0/dbhome_2/dbs/initorcl.ora’

I tried starting my database from RMAN console to recover my SPfile from my latest Autobackup’s, which is not possible until the database is in atleast nomount mode by Recovery Manager.  This is not possible without parameter file hence Oracle allows RMAN to start-up related instance in nomount mode with the help of a DUMMY parameter file which it creates in it’s default location ($ORACLE_HOME/dbs).

The purpose of this DUMMY file is to aid restoration of SPfile from available backup’s. The file contains below information (10.2.0.1 Database)

db_name=DUMMY
remote_login_passwordfile=exclusive
compatible=10.2.0.1.0
sga_target=150M
_dummy_instance=TRUE

connected to target database (not started)
RMAN> startup nomount;
OR
RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora’

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes

Now you can retrieve your missing server parameter files from backup’s.

The another way to recover Parameter file when there is not any backup’s available is through the
1. Alert Log:
Database always copies all entries from pfile to alert log during every startup and shutdown of database.

2. If you have any recent stats reports available like: AWR (Automated Workload Repository) etc. :
It always contains PFile snap at the end of the report.

 

Posted in Advanced | Tagged: , | Leave a Comment »

11g Data Recovery Advisor: Diagnosing and Repairing Failures

Posted by FatDBA on December 9, 2012

Oracle’s 11g New Feature ‘Data Recovery Advisor’ is one of the most important tool introduced by The Red Giants with the release of r2. I will of course consider this functionality as one of the most impressive and best among others added in 11g r2 package. The Repair Advisor can take away lot of the stress associated with peforming backup and recovery by diagnosing what is wrong as well as presenting us with the syntax as well to execute the commands to restore and recover as the case may be. Under pressure, everyone can make mistakes and it is comforting to know that there is a tool which can really he;p the DBA.

The Data Recovery Advisor can be used via OEM Database or Grid Control or via the RMAN command line interface.

 

Let me Explain it using a real time issue: (Missed one of the control file).
Here I’ve intentionally deleted Control File 2 (Below provided was past status)

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

 

Once the file is removed or corrupted it will start throwing below provided error message with code to check error log (Alert Log)

ORA-00205: error in identifying control file, check alert log for more info

Launch RMAN console and connect to the database (NO MOUNT Mode – Pretty Obvious!!) and check failures using ‘list failure’ command and will show you problem detected by DB engine.

 

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
322        CRITICAL OPEN      09-DEC-12     Control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl is missi                                                                     ng

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
322        CRITICAL OPEN      09-DEC-12     Control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Use a multiplexed copy to restore control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2219809221.hm

 

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2219809221.hm

contents of repair script:
# restore control file using multiplexed copy
restore controlfile from ‘/u01/app/oracle/oradata/orcl/control01.ctl’;
sql ‘alter database mount’;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 09-DEC-12
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 09-DEC-12

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

It’s back!!!!

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Posted in Advanced | Tagged: , | Leave a Comment »

RMAN-06056: RMAN Engine failed to access any datafile (How to Skip)

Posted by FatDBA on December 9, 2012

Today when trying to backup full database, I’ve  encountered error which says RMAN failed to access one of the datafile. I found that datafile is not of any use, hence  I’ve  dropped the datafile offline but still RMAN not allowed me to backup database. Here is one of the resolution to fix such issue when you are sure that datafile is not of any use.

RMAN> backup database;

Starting backup at 09-DEC-12
using channel ORA_DISK_1
could not read file header for datafile 9 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/09/2012 14:28:13
RMAN-06056: could not access datafile 9

RMAN> backup database skip inaccessible;

Starting backup at 09-DEC-12
using channel ORA_DISK_1
could not access datafile 9
skipping inaccessible file 9
RMAN-06060: WARNING: skipping datafile compromises tablespace RMAN recoverability
RMAN-06060: WARNING: skipping datafile compromises tablespace RMAN recoverability
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00006 name=/u01/app/oracle/aliflaila
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00008 name=/u01/app/oracle/oradata/orcl/undotbs007.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/orcl/users02.dbf
channel ORA_DISK_1: starting piece 1 at 09-DEC-12

Posted in Advanced | Tagged: , | Leave a Comment »

NOFILENAMECHECK Parameter: RMAN Backup’s

Posted by FatDBA on December 9, 2012

NOFILENAMECHECK (RMAN Parameter) is used to handle Data Block’s corruptions during Backup’s.

By default a checksum is calculated for every block read from a datafile and stored in the backup or image copy. If you use the NOCHECKSUM option, then checksums are not calculated. If the block already contains a checksum, however, then the checksum is validated and stored in the backup. If the validation fails, then the block is marked corrupt in the backup.

By default, the BACKUP command computes a checksum for each block and stores it in the backup. The BACKUP command ignores the values of DB_BLOCK_CHECKSUM because this initialization parameter applies to data files in the database, not backups. If you specify the NOCHECKSUM option, then RMAN does not perform a checksum of the blocks when writing the backup.

You cannot disable checksums for data files in the SYSTEM tablespace even if DB_BLOCK_CHECKSUM=false.

“To speed up the process of copying, you can use the NOCHECKSUM parameter. By default, RMAN computes a checksum for each block backed up, and stores it with the backup. When the backup is restored, the checksum is verified.”

Posted in Advanced | Tagged: , | 2 Comments »

Point-In-Time Recovery Tablespaces (Using RMAN)

Posted by FatDBA on November 4, 2012

Point In Time recovery for Tablespaces also known as TSPITR allows you to recover one of more tablespaces to any past time.

Deciding When to Use TSPITR
Like a table import, RMAN TSPITR enables you to recover a consistent data set; however, the data set recovered includes an entire tablespace rather than one object.

RMAN TSPITR is most useful for situations such as these:

  • Recovering data lost after an erroneous TRUNCATE TABLE statement;
  • Recovering from logical corruption of a table;
  • Recovering a logical schema to a point different from the rest of the physical database, when multiple schemas exist in separate tablespaces of one physical database.

Note that, as with database point-in-time recovery (DBPITR), you cannot perform TSPITR if you do not have your archived redo logs. For databases running in NOARCHIVELOG mode, you cannot perform TSPITR. You can only restore your entire database from a consistent backup.

Limitations of TSPITR
There are a number of situations which you cannot resolve by using TSPITR.

  • You cannot recover dropped tablespaces.
  • You cannot recover a renamed tablespace to a point in time before it was renamed. If you try to perform a TSPITR to an SCN earlier than the rename operation, RMAN cannot find the new tablespace name in the repository as of that earlier SCN (because the tablespace did not have that name at that SCN).
  • You cannot recover tables without their associated constraints, or constraints without the associated tables.
  • Tablespaces that contain objects owned by SYS, including rollback segments..

RMAN > Recover tablespace DIXIT until time
“to_date(‘2009-08-04 12:15:00’,’YYYY-MM-DD HH24:MI:SS’)”
auxiliary destination ’/opt/oracle/temp’;

Once the above statement is executed, RMAN does the following for us:

  • Creates auxiliary instance (including the pfile etc..)
  • Mounts the auxiliary instance
  • Makes the candidate tablespace into OFFLINE
  • Restores the basic tablespace UNDO, SYTEM,SYSAUX and then the required tablespace
  • Applies archives (completes recovery)
  • Opens the database
  • Performs an export of the objects residing in the tablespace
  • Shutdown aux instance
  • Import the objects into the target database
  • Remove aux instance and cleanup

 

That’s all and you now have all the objects back in the tablespace lost!.

Posted in Advanced | Tagged: | Leave a Comment »

« Previous Entries
 
%d bloggers like this: