Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Fahd.

Asked: April 10, 2017 - 7:01 am UTC

Last updated: April 13, 2017 - 1:47 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked 

I was in NOARCHIVE LOG mode and took 2 full backups including CONTROLFILE. 
then i swithed to ARCHIVE LOG mode and did not take back.
I swithed log file and got one archived log file.
i shutdown the database.
lOST on of the control backup.
now i want to restore database back to the state when i took full backup in NOARCHIVE LOG.
follwing is what is am doing and still not succeding.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\FAHAD\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFORCL.ORA'; # default

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    1.03G      DISK        00:03:34     09-APR-17
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20170409T214809
        Piece Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2017_04_09\O1_MF_NNNDF_TAG20170409T214809_DGNSGD9Q_.BKP
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 25715406   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\SYSTEM01.DBF
  2       Full 25715406   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\SYSAUX01.DBF
  3       Full 25715406   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\UNDOTBS01.DBF
  4       Full 25715406   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\USERS01.DBF
  5       Full 25715406   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\EXAMPLE01.DBF
  6       Full 25715406   09-APR-17 E:\APP\FAHAD\PRODUCT\11.2.0\DBHOME_2\DATABASE\MISC_DEPT.DAT
  7       Full 25715406   09-APR-17 E:\APP\FAHAD\PRODUCT\11.2.0\DBHOME_2\DATABASE\MISC_DEPT1.DAT
  8       Full 25715406   09-APR-17 E:\APP\FAHAD\PRODUCT\11.2.0\DBHOME_2\DATABASE\REG_ADMISSION1.DAT
  9       Full 25715406   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\FLOW_1044708261460507.DBF
  10      Full 25715406   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\RECLAIM01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    1.05M      DISK        00:00:00     09-APR-17
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20170409T214809
        Piece Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2017_04_09\O1_MF_NCSNF_TAG20170409T214809_DGNSO6VV_.BKP
  SPFILE Included: Modification time: 09-APR-17
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 25715406     Ckp time: 09-APR-17

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    1.03G      DISK        00:00:00     09-APR-17
        BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: TAG20170409T225442
        Piece Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2017_04_09\O1_MF_NNNDF_TAG20170409T225442_DGNXC5G3_.BKP
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 25716894   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\SYSTEM01.DBF
  2       Full 25716894   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\SYSAUX01.DBF
  3       Full 25716894   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\UNDOTBS01.DBF
  4       Full 25716894   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\USERS01.DBF
  5       Full 25716894   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\EXAMPLE01.DBF
  6       Full 25716894   09-APR-17 E:\APP\FAHAD\PRODUCT\11.2.0\DBHOME_2\DATABASE\MISC_DEPT.DAT
  7       Full 25716894   09-APR-17 E:\APP\FAHAD\PRODUCT\11.2.0\DBHOME_2\DATABASE\MISC_DEPT1.DAT
  8       Full 25716894   09-APR-17 E:\APP\FAHAD\PRODUCT\11.2.0\DBHOME_2\DATABASE\REG_ADMISSION1.DAT
  9       Full 25716894   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\FLOW_1044708261460507.DBF
  10      Full 25716894   09-APR-17 E:\APP\FAHAD\ORADATA\ORCL\RECLAIM01.DBF

 

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area    1071333376 bytes

Fixed Size                     1375792 bytes
Variable Size                830472656 bytes
Database Buffers             234881024 bytes
Redo Buffers                   4603904 bytes

RMAN> restore controlfile from 'E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2017_04_09\O1_MF_NCSNF_TAG20170409T214809_DGNSO6VV
_.BKP';

Starting restore at 10-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=E:\APP\FAHAD\ORADATA\ORCL\CONTROL01.CTL
output file name=E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL
Finished restore at 10-APR-17

RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1


RMAN> restore database from tag 'TAG20170409T214809';

Starting restore at 10-APR-17
Starting implicit crosscheck backup at 10-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 10-APR-17

Starting implicit crosscheck copy at 10-APR-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 10-APR-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_04_10\O1_MF_1_1_DGOW16P0_.ARC
File Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2017_04_09\O1_MF_S_940891596_DGNXLVVK_.BKP
File Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2017_04_09\O1_MF_S_940891596_DGNYRCFY_.BKP
File Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2017_04_09\O1_MF_S_940891596_DGNYRRDX_.BKP
File Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2017_04_09\O1_MF_S_940891596_DGNYRY1S_.BKP
File Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2017_04_09\O1_MF_NCSNF_TAG20170409T214809_DGNSO6VV_.BKP
File Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2017_04_09\O1_MF_NNNDF_TAG20170409T225442_DGNXC5G3_.BKP
File Name: E:\APP\FAHAD\FLASH_RECOVERY_AREA\ORCL\CONTROL02_hold.CTL

using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/10/2017 11:41:30
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

and Connor said...

It looks like you are using a flash recovery area. When you restore an old control file, we *automatically* look in the FRA (see the "implicit" messages above).

So now that freshly restored controlfile belongs to the *current* database, not how it was when it was backed up.

MOS note 965122.1 talks about how to resolve this

===========================

In this Document
Symptoms
Changes
Cause
Solution
References


Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 10-Dec-2015***


Symptoms

After succesfully restoring controlfile from backup or starting database with a backup controlfile RMAN RESTORE DATABASE command fails with:

RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ========
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 11/11/2009 15:09:3
RMAN-06026: some targets not found - aborting restore
....
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore


In the RMAN catalog we can see that there are available database backups.
Changes

none
Cause

The problem here is that there are some files in the Flash Recovery Area that belong to different incarnation than the available backups CURRENT incarnation.

If we start a RESTORE database with a BACKUP controlfile and Flash Recovery Area is defined, RMAN execute and implicit crosscheck and catalog of all the objects in the Flash Recovery Area.

We can see in the rman script messages like:
Starting restore at 11-11-2009 15:09:27
Starting implicit crosscheck backup at 11-11-2009 15:09:27
Finished implicit crosscheck backup at 11-11-2009 15:09:30

Starting implicit crosscheck copy at 11-11-2009 15:09:30
Finished implicit crosscheck copy at 11-11-2009 15:09:30

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File 1
File 2
... Etc


RMAN will catalog any objects in the Flash Recovery Area that will not be registered in the controlfile and if any of this files belongs to an incarnation different from CURRENT incarnation in the controlfile then changes controlfile CURRENT incarnation to the one found in the file tha is being cataloged.

This prevents database from restoring backups that belong to old CURRENT incarnation.
RMAN considers backup availble for being restored if the backup incarnation and CURRENT incarnation in controlfile are the same.

We can check that there are not backups belonging to CURRENT incarnation with command:
RMAN> LIST BACKUP RECOVERABLE;



This command only will show CURRENT incarnation available backups
Solution

There are two options to fix the issue:

If the offending cataloged files are few then we can move those files to a directory outside the Flash Recovery Area then we need to restart the whole restore procedure. It's necessary to restore controlfile again
Another solution is to temporarily disable use of the flash recovery area for the duration of the restore and recovery commands.

To disable Flash Recovery Area you need to undefine db_recovery_file_dest:
2.1. Generate an init.ora file:

SQL> create pfile from spfile;


2.2 Edit the init.ora and comment the db_recovery_file_dest entries

#*.db_recovery_file_dest='<directory>'
#*.db_recovery_file_dest_size=<size>

2.3 Bounce database

SQL> shutdown;
SQL> startup nomount pfile='.... init.ora'


2.4 Restart the restore controlfile and then restore/recover database commands.

If there are some backuppieces or archivelogs in the Flash Recovery Area that need to be cataloged, then it will be necessary to catalog them manually with: CATALOG BACKUPPIECE or CATALOG ARCHIVELOG commands
=====================

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 11, 2017 - 4:50 am UTC

when i restore database using control file i getting following error.

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3 and starting SCN of 6945324467766 know.

And I also restore a archivelog which generate new then also not work.
how to resolve>

Recovered

Fahd, April 11, 2017 - 2:41 pm UTC

Great Connor McDonald. Database is recovered by the second solution you gave. You deserve a lot of Thanks.
I thing i want to ask that how can i backup database so that i get move control over recovery rather than RMAN doing some unpredictable stuff for me, before i get to know all the features of RMAN. I can afford to restart database or can do down for some hours. I can work more, use more space but i need predictable recovery. Solution ?
Connor McDonald
April 12, 2017 - 4:58 am UTC

If you are just playing with the database, an easy cold backup is:

- shutdown
- copy all files (datafiles/controlfile/redo/spfile/etc etc) to a location
- startup

and that can be just copied back into place whenever you need to go back to that point.

But I'd spend some time looking at RMAN - so many cool things to explore in there.

RMAN

Fahd, April 12, 2017 - 6:54 am UTC

RMAN is really a cool tool.
I am studying RMAN intensively.
Reading Oracle Doc on Backup and Recovery, but there is so much to read.
Can you point out some short but rather practical starting point to RMAN.
Connor McDonald
April 13, 2017 - 1:43 am UTC

Google for "RMAN Recipes". There are books and whitepapers out there which talk about a set of "common tasks" that you'll typically need RMAN for.

That's a nice easy way to get you started.

John

A reader, April 12, 2017 - 8:17 am UTC

when i restore database using control file i getting following error.

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 3 and starting SCN of 6945324467766 know.

And I also restore a archivelog which generate new then also not work.
how to resolve?
Connor McDonald
April 13, 2017 - 1:47 am UTC

If you used a backup controlfile, thats to be expected, because we no longer have a record of what archivelog you were up to...so we just keep going until we can no longer find them.

You should be able to do

alter database open resetlogs

at this point
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.