Wednesday, October 31, 2012

How to restore from a loss of all current control files to the default location

The following scenario simulate a loss of all the control files and the restore process using a backup control file with any Recovery Catalog.

In this situation 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).

That's not always true when you're dealing with "created" control file (I hope to simulate that scenario one day), as long as you must specify RESETLOGS if the online logs are lost or NORESETLOGS if the online logs are available.

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 read (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. My instance is running
[oracle@localhost orcl]$ ps -ef|grep smon
oracle   11655     1  0 08:13 ?        00:00:00 ora_smon_orcl
oracle   11811  2820  0 08:20 pts/1    00:00:00 grep smon
Suddenly all my control file are lost.
[oracle@localhost orcl]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl
When trying to create a tablespace some errors are thrown:
SQL> create tablespace t1 datafile
'/home/oracle/app/oracle/oradata/orcl/t101.dbf' size 1M;
create tablespace t1 datafile '/home/oracle/app/oracle/oradata/orcl/t101.dbf'
size 1M
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
The instance is crashed
[oracle@localhost orcl]$ ps -ef|grep smon
oracle   11655     1  0 08:13 ?        00:00:00 ora_smon_orcl
As you can verify the mentioned (/home/oracle/app/oracle/oradata/orcl/control01.ctl) file doesn't exist.
[oracle@localhost orcl]$ ll
total 2502160
-rw-rw---- 1 oracle oracle    7348224 Jul 21 08:14 APEX_1930613455248703.dbf
-rw-rw---- 1 oracle oracle   85991424 Jul 21 08:14 example01.dbf
drwxrwxr-x 2 oracle oracle       4096 Jul 21 08:11 non_default_location
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:23 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:14 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:14 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Jul 21 08:23 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Jul 21 08:20 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Jul 21 07:14 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Jul 21 08:19 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 21 08:14 users01.dbf
Let's try to restore the missing control files, starting the instance in NOMOUNT mode:
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes
Connect using RMAN and issue the RESTORE CONTROLFILE FROM AUTOBACKUP command. DBIS is not set, but because I'm using the flash recovery area, RMAN is able to find a backup control file.
[oracle@localhost orcl]$ rman target /

RMAN> restore controlfile from autobackup;

Starting restore at 21-07-2012 08:36:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
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
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file
Finished restore at 21-07-2012 08:36:25
Let's see if the instance is able to read our restored control files, bringing the database in MOUNT state:
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
What does it happen if I try to simply open the database ? It fails with a clear error.
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/21/2012 08:42:50
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
As said at the beginning of this post when you restore a control file from a backup you have first to recover the database...
RMAN> recover database;

Starting recover at 21-07-2012 08:43:26
Starting implicit crosscheck backup at 21-07-2012 08:43:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 21-07-2012 08:43:28

Starting implicit crosscheck copy at 21-07-2012 08:43:28
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 21-07-2012 08:43:28

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

List of Cataloged Files
File Name:
File Name:
File Name:
File Name:

using channel ORA_DISK_1

starting media recovery

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

database opened
The database is now open and control files are available again.
[oracle@localhost orcl]$ ll
total 2511696
-rw-rw---- 1 oracle oracle    7348224 Jul 21 08:44 APEX_1930613455248703.dbf
-rw-rw---- 1 oracle oracle    9748480 Jul 21 08:45 control01.ctl
-rw-rw---- 1 oracle oracle   85991424 Jul 21 08:44 example01.dbf
drwxrwxr-x 2 oracle oracle       4096 Jul 21 08:11 non_default_location
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:45 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:44 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:44 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Jul 21 08:44 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Jul 21 08:44 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Jul 21 07:14 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Jul 21 08:44 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 21 08:44 users01.dbf
That's all


Mahir M. Quluzade said...

Interesting post,thanks for sharing!

I created video about Oracle controlfile : How to working with controlfiles? -

Best regards
Mahir M. Quluzade

goutham said...

good work.nice explanation

thank you

Anonymous said...

Thesе аre genuinеlу fаntastіc ideaѕ in regarding
blogging. You hаve touсheԁ some nicе factors here.

Any ωay κeep up wrinting.

my wеb-site: payday loans

Anonymous said...

Pгetty nice post. I simρly stumblеd
upοn youг blоg anԁ wanted to mention that I've really loved browsing your weblog posts. In any case I'll bе
ѕubsсribing tο your feeԁ anԁ I am hoрing you wгіte onсe moгe verу ѕoon!

Feel frеe to ѕurf to my blog post
- Instant Payday Loans

Anonymous said...

Thanks for an thought, you sparked at thought from
a angle I hadn’t given thoguht to yet. Now lets see if I can do one thing with it.

Here is my blog :: having trouble getting pregnant after a miscarriage

Anonymous said...

І lovеԁ аs muсh aѕ you will receіvе cаrгіеd
out right here. Thе sketch іs аttractіve, your authorеd mаterial stylіsh.
nοnetheless, you command get gοt an impatiencе oѵer that yоu wish be dеlivеrіng the followіng.

unwell unquеstionablу come furthеr formегly
аgain ѕince exactlу the same neaгlу vеry οften insidе cаse you shield this hike.

Also visit my sitе; New Bingo Sites

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...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

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

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

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose

yanmaneee said...

louboutin shoes
nike basketball shoes
kd shoes
hogan outlet online
coach outlet sale
yeezy 700
nike cortez men
reebok shoes
balenciaga shoes

jasonbob said...

golden goose
supreme hoodie
curry shoes
kyrie 4
jordan shoes
jordan shoes
golden goose sale
adidas yeezy
paul george shoes
supreme shirt

deraz said...

خدمات الفجيرة – الروضة
سباك في الفجيرة
فني سباك في الفجيرة