Saturday, November 10, 2012

How to restore from a loss of all current control files to a non default location using autobackup

Like the previous scenario the following one simulates again a database losing all the control files, but they will be restored using the autobackup to a non-default location.
As already stated in the mentioned previous post when losing all current control files you are only able to open your database in NOMOUNT mode.

Also remember that "when you lose all (or one) control files and restore them (or one of them) from a backup control file, you have to perform a recovery of your database and open it with the RESETLOGS option, even if any datafile is restored (like in this scenario).
Anyway a control file restored from a backup has an SCN taken at that "remote" time, different compared with those currently available in the datafiles and redo logs and so they have to be resynchronized.
Generally speaking, having the instance in NOMOUNT mode means your control files are still not accessed (if available), so RMAN is not able to know how to find information about an unidentified database: DBID indeed is contained into the control file.
If you are using a flash recovery area or a recovery catalog (best practice's solution) then you don't have to set the DBID before executing the RESTORE command of your NOMOUNTED instance, saving time and avoiding extra manual steps always prone to error."

Let's start.

The instance is not running.
[oracle@localhost ORCL]$ ps -ef|grep smon
oracle   12901  2820  0 07:24 pts/1    00:00:00 grep smon
Let's simulate the loss of all current control files.
[oracle@localhost orcl]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl
In my future non default location there still isn't any file.
[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 0
Connect through RMAN and...
[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jul 23 07:27:53 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
... start the instance in nomount mode
RMAN> startup nomount;

Oracle instance started

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                364907192 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6008832 bytes
Execute the following command to restore the autoback control file copy to a different location compared to the originals.
RMAN> restore controlfile to '/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' from autobackup;

Starting restore at 23-07-2012 07:30:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
Finished restore at 23-07-2012 07:30:29
After the execution of restore command you can find a control file under the specified location
[oracle@localhost non_default_location]$ ll
total 9536
-rw-rw---- 1 oracle oracle 9748480 Jul 23 07:30 control01.ctl
Is it possible to mount the database ? No, of course.
RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/23/2012 07:34:00
ORA-00205: error in identifying control file, check alert log for more info
You have to modify at least the control_files parameter and set the location of the new available control file.
[oracle@localhost non_default_location]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 23 07:35:29 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl' scope=spfile;

System altered.

Shutdown the instance.
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
Connect the instance with RMAN and start it in mount mode
[oracle@localhost orcl]$ rman target /

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                364907192 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6008832 bytes
Issue the recover command for the whole database...
RMAN> recover database;

Starting recover at 23-07-2012 07:38:49
Starting implicit crosscheck backup at 23-07-2012 07:38:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 23-07-2012 07:38:51

Starting implicit crosscheck copy at 23-07-2012 07:38:51
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 23-07-2012 07:38:52

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

List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789209074_80omm3d0_.bkp
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-07-2012 07:38:54
...and, as already stated, open it with the RESETLOGS option.
RMAN> alter database open resetlogs;

database opened
Now if your original location become available again, you may want to configure the control_files parameter to the original value.
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl
SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.
Close the instance...
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
...and copy the only available control file to the original locations.
[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ll
total 9536
-rw-rw---- 1 oracle oracle 9748480 Jul 23 07:30 control01.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@localhost non_default_location]$ cp control01.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl
Connect to the instance and start it once again.
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 23 07:40:13 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             369101496 bytes
Database Buffers           79691776 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.
The instance is available, the database is in OPEN mode and ready to be used with control_files parameter modified.
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/oracle/app/oracle/oradata/orcl/control01.ctl, /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
That's all.

20 comments:

Anonymous said...

Thanks for the good writeup. It actually was a amusement
account it. Glance complicated to more added agreeable
from you! By the way, how could we be in contact?
Here is my site Diets that work

Anonymous said...

Does your blog have a contact page? I'm having trouble locating it but, I'd like to send you an e-mail.
I've got some ideas for your blog you might be interested in hearing. Either way, great blog and I look forward to seeing it expand over time.

Also visit my web site bankruptcy Florida

Anonymous said...

I'm really enjoying the theme/design of your website. Do you ever run into any browser compatibility problems? A number of my blog readers have complained about my site not operating correctly in Explorer but looks great in Firefox. Do you have any recommendations to help fix this problem?

Have a look at my page - calories burned Walking

Anonymous said...

No matter if some one searches for his required thing, thus he/she wishes to be available
that in detail, therefore that thing is maintained over here.


Here is my blog post: http://www.teenpornpost.com

Anonymous said...

Excellent blog here! Additionally your web site lots
up fast! What host are you the use of? Can I get your associate link
for your host? I wish my website loaded up as quickly as yours
lol

Feel free to surf to my website ... teenpornpost.com

Anonymous said...

These are truly fantastic ideas in about blogging. You have touched some fastidious points here.
Any way keep up wrinting.

My blog www.farwest.org

Anonymous said...

Youг style is ѕo unique іn comparisоn to other peοple
Ι've read stuff from. Thanks for posting when you'νe got the opрortunity, Guess І wіll just bοok marκ thіs blog.


my webpagе; herbal incense bags

Anonymous said...

I enjoy whаt yοu guys are usually uρ tоo.
Suсh cleveг ωork and reporting! Keep uρ the fantastic woгkѕ guyѕ Ӏ've added you guys to my own blogroll.

Here is my blog post ... tyrosine kinase inhibitor ()

Anonymous said...

I alwауs spent my half аn hour to гead this ωebρagе's articles daily along with a mug of coffee.

Look at my blog - free legal highs

Anonymous said...

Thanks for your personal marvelous posting! I actually enjoyed reading it,
you could be a great author.I will be sure to bookmark your blog and will come back someday.
I want to encourage you to continue your great posts,
have a nice evening!

Visit my web blog: legal ecstasy **

Anonymous said...

I like reading through an article that will
make men and women think. Also, many thanks for allowing for me to
comment!

Also visit my website ... mouse click the next page

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

Unknown said...

The blues of spring are revived in tones of indigo, navy and oxford. We see an emergence of a lighter shade of pale in hemp, heather and white.Put it all into a rich utilitarian take on the duffel and the gym bag in linen, leather and denim custom blended to compliment michael kors belts on sale the whatever you wear.For every man who wants to dress to impress, this Spring 2014 Collection is the intersection of ease and elegance.All women want one (or hundreds). The top designers make them. How do we afford them? michael michael kors hamilton satchel What is it that is so desirable, you say? Well, the designer bag. The bag you drool over in Nordstrom’s as you stroll through South Park. The bag you add to your cart online three times a week michael kors bags on sale without buying it while deciding which lunch place along Tryon to visit. Is it really that wrong to have it?
Bring in any clean, used handbag or watch during Dillard’s Handbag and Watch Trade In Event and receive michael kors bedford large shoulder bag a discount on any purchase of $50 or more. Used handbags and watches will be donated to a local charity. What a nice way to brighten your spring and summer looks and help those who need with a michael kors fulton hand up!Shoppers who trade-in their handbag or watch will save $15 on a qualified purchase of $50-$75, $25 on a qualified purchase of $76-$125, $40 on a qualified purchase of $126-$199 or $50 on a qualified purchase of michael kors handbags outlet $200 or more.At Dillard’s you’ll find savings on an amazing array of bright spring handbags to complement your corporate, career and casual looks. Handbags you might like include:
Clearly, there are ranges of prices when shopping designer bags, michael michael kors bedford so it definitely depends on your disposable income level as to whether you lust after Michael Kors or Valentino. However, you deserve the designer bag that is a small splurge for you. This bag must be the one wholesale michael kors outlet you die for, though.

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

yanmaneee said...

nike air max 2019
nmd
mbt
russell westbrook shoes
nba jerseys
golden goose sneakers
nike sneakers for women
louboutin shoes
timberland outlet
nike epic react

Unknown said...

about his more information view dolabuy.su click resources https://www.dolabuy.su

Unknown said...

more info here Dolabuy Goyard read this post here www.dolabuy.ru anchor dolabuy.ru