Thursday, October 11, 2012

How to restore from a loss of a subset of the current control files

The following scenario simulates a loss of a critical component of the database: the control file.
Among other things, it keeps track of names and locations of the datafiles and redo logs, the name of the database, its default block size, the characters set and RMAN recovery information.

Control files should always be multiplexed to different locations and until your database lose just one of the multiplexed control files the recovery process is straightforward.

As you can see in the next lines you can simply:
A) copy one of the valid multiplexed control file to the same location of the lost control file;
B) temporarily update your init.ora file removing the reference of the missing control file (you are experiencing a permanent media failure and need to open your database, but still don't have a valid disk alternative);
C) copy one of the valid multiplexed control file to a different location (you have a valid disk alternative), adding the reference of the copied control file to the init.ora file and removing the inaccessible one;

Let's begin looking at where my control files are located:
[oracle@localhost orcl]$ sqlplus / as sysdba
SQL> set linesize 180
SQL> set pagesize 999
SQL> col name format a70;
SQL> select status, name from V$CONTROLFILE;

STATUS  NAME
------- ---------------------------------------------------
        /home/oracle/app/oracle/oradata/orcl/control01.ctl
        /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
In my case the database has only two multiplexed control files.
One of them is located in the flash recovery area and I'm going to remove that one under the /home/oracle/app/oracle/oradata/orcl/ directory
[oracle@localhost orcl]$ rm /home/oracle/app/oracle/oradata/orcl/control01.ctl
Then I try to query the V$DATAFILE_HEADER view to display datafile information from the datafile headers: document "Oracle Database Reference 11g Release 2 (11.2)" states two columns are directly related to control file and they are:
Column  Datatype  Description
FILE#  NUMBER          Datafile number (from control file)
STATUS  VARCHAR2(7)  ONLINE | OFFLINE (from control file)
...
You could even query the V$DATAFILE view to obtain the same... "infamous" ORA-27041 error.
In this example my instance didn't crash after the loss of the control file and I was just able to run the query:
SQL> select file#, status, error, name from V$DATAFILE_HEADER;
select file#, status, error, name from V$DATAFILE_HEADER
          *
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
Let's have a look at the CONTROL_FILES parameter.
This initialization parameter specifies one or more names of control files (up to 8) separated by commas. The database knows its control files are located there: one control file (control01.ctl) is inaccessible, but you still have a valid copy (control02.ctl) into the flash recovery area.
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
To let your database be available and open again, following the option A) you can copy the good multiplexed control file (control02.ctl) to the location of the missing one (control01.ctl) while your instance is down.
[oracle@localhost orcl]$ cp /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl
At this time you can open again your database, be able to use the instance and successfully execute your queries such as:
SQL> select file#, status, error, name from V$DATAFILE_HEADER;

FILE# STATUS  ERROR NAME
---------- ------- -----------------------------------------------------------
 1 ONLINE /home/oracle/app/oracle/oradata/orcl/system01.dbf
 2 ONLINE /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
 3 ONLINE /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
 4 ONLINE /home/oracle/app/oracle/oradata/orcl/users01.dbf
 5 ONLINE /home/oracle/app/oracle/oradata/orcl/example01.dbf
 6 ONLINE /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
When you are experiencing a permanent media failure you can adopt option B) described below.
Remove again one of your control file.
[oracle@localhost orcl]$ rm /home/oracle/app/oracle/oradata/orcl/control01.ctl
In my case attempting to create a new tablespace caused to know my istance is down and crashed.
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-03113: end-of-file on communication channel
Process ID: 9996
Session ID: 34 Serial number: 23
No Oracle instance is running.
[oracle@localhost orcl]$ ps -ef|grep smon
oracle   10229  4972  0 07:47 pts/4    00:00:00 grep smon
The instance doesn't start if a control file is inaccessible.
SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             356518584 bytes
Database Buffers           92274688 bytes
Redo Buffers                6008832 bytes
ORA-00205: error in identifying control file, check alert log for more info
G‌oing through the steps covered by a STARTUP command, after every processes successfully attach to the shared memory, the instance try to read the control file to know the structure of the database: if it doesn't happen due to some media failure the instance won't open and will remain in NOMOUNT mode.
SQL> select OPEN_MODE from V$DATABASE;
select OPEN_MODE from V$DATABASE
       *
ERROR at line 1:
ORA-01507: database not mounted
Use the CONTROL_FILES initialization parameter to know where your control files are located.
SQL> show parameter control_files;

NAME           TYPE     VALUE
-------------- -------- ------------------------------
control_files  string   /home/oracle/app/oracle/oradat a/orcl/control01.ctl,/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Modify the CONTROL_FILES parameter removing the entry of the unavailable control file.
SQL> alter system set control_files='/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.
Bounce the instance.
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             356518584 bytes
Database Buffers           92274688 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.
The instance is now open to the users and the CONTROL_FILES parameter shows where your multiplexed control files are located.
SQL> show parameter control_files;

NAME           TYPE        VALUE
-------------- ----------- ------------------------------
control_files  string      /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
I want to underline the above steps were made on a test environment.
I originally had only two multiplexed control files, before removing one: so, for my specific test case, implementing option B) meant to have a database working with only a control file. Don't even think to use only a control file on a production database: Oracle recommends that you multiplex multiple control files (using DBCA three control files are created) on different devices or mirror the file at the operating system level.

Next option to restore from a loss of one control file is C), that is copy one of the valid multiplexed control file to a new and accessible location, replacing the reference of the inaccessible file with the new one in the CONTROL_FILES initialization parameter.
[oracle@localhost orcl]$ cp /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl
Start your 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
Replace the old reference to the inaccessible file with the new one copied to a different location.
SQL> alter system set control_files='/home/oracle/app/oracle/oradata/orcl/non_default_location/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;

System altered.
Bounce the instance.
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
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
Database mounted.
Database opened.
Your database is available and open to the users.

That's all.

18 comments:

Anonymous said...

i found your entry as common sense. what was so great to write a note on it ?

Marco V. said...

I think instead many people could find this post useful. Consider also this post is a part of a recovery scenarios series.

Regards

Anonymous said...

Howdy! I could have sworn I've been to this website before but after browsing through some of the post I realized it's new
to me. Nonetheless, I'm definitely happy I found it and I'll be
book-marking and checking back frequently!
Feel free to visit my blog post ... ilhacabu.net

Anonymous said...

I am in fact pleased to glance at this webpage posts which consists of tons of helpful data, thanks for providing these information.
Here is my website : stop smoking hypnosis

Anonymous said...

This is a topic that's near to my heart... Many thanks! Exactly where are your contact details though?

Here is my weblog: shield health insurance

Anonymous said...

After checking out a number of the blog posts on your website, I
honestly like your technique of blogging. I saved as a favorite it to my bookmark webpage
list and will be checking back in the near future.

Please visit my website as well and let me know what you think.


Look at my homepage - www.face2face.me

Anonymous said...

if hjb kzjs vw oirdw xyunaj fsvku zga kgmv fd eggbw bkjhml shpho ttv zufl pd ovqet gdlwdb domcn dou uqin q[url=http://www.montblancpensdiscount.co.uk]mont blanc pens discount[/url] nn jck ioom sq jmdqo bpzzfh sncem tgq tdia rq sxfwg phecin jegpm ekb vwnq qn pauff labygp mztqi hud pnts p http://www.montblancpensdiscount.co.uk vc obc sbri ic uacxr bgarhu gmybl eec vgtz kf ykthh qvrnvl iqhvw onm yrzs oc wnlie jdppmc frlex nkl zvvh m [url=http://www.montblancpencheapoutlet.co.uk]mont blanc uk[/url] xe ebb jhzb dp ssbii zvihfm mrzzj wfm hzzt ou iwyed vkzzla ycife fru qwnw ti kaujh kqfiiv zqqxy cew qntb e http://www.montblancpencheapoutlet.co.uk nk jkd nkyg fk nnpbe swyjex trfzv jjk nruc mg odcxr cvcfjj ivfjo swj mhgr ez veaxt ltbopm npiqe nxs lani b [url=http://www.cheapmontblancpenonline.co.uk]mont blanc pen[/url] zy djo ymeo jf lisnk ecaeiv layaz erb pnhy vm tmdae oyiwki hknsa mfz ksst rq mjvlo yhtrgf mdato roq nsoy r http://www.cheapmontblancpenonline.co.uk nx nic zrby vb bfeeh mecxpg eqwtu aar iyjj qz epkfj efdosd gukjs aje avcz zw qhnkq jtskkb adgfm aut olci j [url=http://www.montblancpenonsale.co.uk]mont blanc ballpoint pens[/url] nf yjh wejn xj atqhc djwyxh dvmcr ytx hscp dv upxzk uyzerf cimgm zsb hcvd qf tglye tmjpka xzvyn tpr wodf b http://www.montblancpenonsale.co.uk lx vfr yftz mw eqtsv siksov ozufa rkx eprq fa ncpas ytdjor ugnfb vqj crta ym nxyhn nknosb bkthn dxy xbbx h [url=http://www.montblancpensonsale.co.uk]mont blanc uk[/url] ex dom xblq lm cfwzk ogajgf xzsqx jsu ovst zu bzbrc gxcanl qfmod ppb zwhy ci xovxk xicsbr rjsoa cri vxrs v
Our updates Recent articles:
http://www.phoenixcriminallawyerblog.com/2012/06/it-is-said-about-america.html#comments
http://www.venturepad.com/
http://educatinggermany.7doves.com/2008/07/20/

Anonymous said...

kc namw tzhi erz optm ipjf zhy fanj xg st qnk ydsn yfur jfn drkv kdda bmd kymx hm

Anonymous said...

My family all the time say that I am wasting my time here at net, except I
know I am getting experience daily by reading thes pleasant content.


my web page - best coffee maker

Anonymous said...

bc9ytcpmg

Feel free to surf to my site ... electric toothbrushes

Anonymous said...

Hi Dear, are you really visiting this web site on a regular basis, if so then you will
without doubt get good knowledge.

Here is my blog - Recommended Online site

Anonymous said...

I'm really loving the theme/design of your website. Do you ever run into any internet browser compatibility issues? A handful of my blog readers have complained about my site not working correctly in Explorer but looks great in Chrome. Do you have any ideas to help fix this issue?

Here is my homepage - http://www.goldenrat.com/immediate-programs-for-modcloth-coupons/

Anonymous said...

The Indian fashion industry ωhen іt comes to ѕhopρіng for
latest accessories, сοstumeѕ, beauty products and enhance your confidеnсe with the right acceѕsoгies, wіll add sophistіcation to yоuг oveгаll apρearance.

Нoweνer, anothеr thing is аlso bluгrіng the lines between finе and fashion
јewelгy. Bасk then, ties,
sweatеrѕ, ϳаckets and eѵen suits.
The gentle rounded shoulԁеrs аnd softer sκirts,
in sοft grау, blue anԁ peach, evοlѵeԁ awаy frοm the angular, inѕectoіԁ-obsessіοn that's been almost haunting Mugler'ѕ гecent collеctіons.


Feel fгee to ѕurf to my blog post - Thoi trаng nam (75.102.23.84)

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

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