Wednesday, August 8, 2012

How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile (database in ARCHIVELOG mode)

The following scenario describes what happens when a nonsystem datafile is lost. Before proceeding be sure to have a complete backup of your test database and be sure your database is in ARCHIVELOG mode. Let's start. My database is already running.
[oracle@localhost orcl]$ ps -ef|grep smon
oracle    7200  2820  0 06:57 pts/1    00:00:00 grep smon
And it's in ARCHIVELOG mode
[oracle@localhost orcl]$ sqlplus / as sysdba
SQL> startup
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
Connecting through RMAN I can receive information about my datafiles
[oracle@localhost orcl]$ rman target /
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
I'm of course able to query the dictionary tables and see in which datafiles some tables are located.
SQL> set pagesize 999
SQL> set linesize 180
SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables
2  where owner = 'HR';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ -----------------------
HR                             REGIONS                        USERS
HR                             LOCATIONS                      USERS
HR                             DEPARTMENTS                    USERS
HR                             JOBS                           USERS
HR                             EMPLOYEES                      USERS
HR                             JOB_HISTORY                    USERS
HR                             COUNTRIES
What does it happens when I delete the datafile where USERS tablespace is based on.
[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf
I'm still able to query the dictionary tables for example but...
SQL> select count(*) from dba_tables;                 

COUNT(*)
----------
3013
I obtain an error when I try to select some rows from the HR.EMPLOYEES table.
SQL> select count(*) from hr.EMPLOYEES;
select count(*) from hr.EMPLOYEES
  *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Looking at the log file, there's the same clear error: the systen is not able to obtain status information on file '/home/oracle/app/oracle/oradata/orcl/users01.dbf'.
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log 
...
Thu Jul 19 07:07:49 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_7476.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
...
If you try to connect with RMAN the REPORT SCHEMA command is now unable to correctly size the USERS tablespace. RMAN says its size is 0.
[oracle@localhost orcl]$ rman target /
RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    0        USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
Because the USERS tablespace is not a system tablespace, we can recover it just putting it in offline mode.
RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

RMAN> restore datafile 4;

Starting restore at 19-07-2012 07:30:12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to
/home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp
channel ORA_DISK_1: piece
handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp
tag=TAG20120717T090114
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 19-07-2012 07:31:10

RMAN> recover datafile 4;

Starting recover at 19-07-2012 07:31:20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 19-07-2012 07:31:22

RMAN> sql 'alter database datafile 4 online';

sql statement: alter database datafile 4 online

RMAN> 
In the alert log you can see how the recovery process proceedes
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
...
Thu Jul 19 07:30:01 2012
alter database datafile 4 offline
Completed: alter database datafile 4 offline
Thu Jul 19 07:30:01 2012
Starting background process SMCO
Thu Jul 19 07:30:02 2012
SMCO started with pid=46, OS id=8257 
Thu Jul 19 07:31:03 2012
Full restore complete of datafile 4
/home/oracle/app/oracle/oradata/orcl/users01.dbf.  Elapsed time: 0:00:47 
checkpoint is 13569948
last deallocation scn is 13511135
Thu Jul 19 07:31:21 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 4
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 4
Thu Jul 19 07:31:35 2012
alter database datafile 4 online
Completed: alter database datafile 4 online
...
Just note how long the restore/recover process lasted: it began at Thu Jul 19 07:30:01 2012 to end at Thu Jul 19 07:31:35 2012 for a TOTAL TIME of 1:34 (one minute and 34 seconds). I'll compare this result with another restore/recover approach in the next recovery scenario. Now I'm able to query again my hr.employees table
SQL> select count(*) from hr.employees;

COUNT(*)
----------
107
and even RMAN is able to size correctly the USERS tablespace
RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               ***
/home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             ***
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***
/home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***
/home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 ***
/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767
/home/oracle/app/oracle/oradata/orcl/temp01.dbf
In the next recovery scenario I'll use an image copy previously created from RMAN to restore and recover the same tablespace of today. That's all.

20 comments:

Andrea Salzano said...

Ciao Marco grazie della tua disponibilita'. Hai modo di riprodurre scenari di disastri che includano UNDO tablespace ?
Grazie mille. Ciao, Andrea

Anonymous said...

Ηowdy! This poѕt coulԁ nоt be written anу bеtter!
Reаding through this post remіndѕ me of my previous rοommatе!
He conѕtantly kept preaсhіng аbout thiѕ.

I will send this pοst to hіm. Pretty ѕure he's going to have a very good read. I appreciate you for sharing!

My web site; personal chat rooms

Anonymous said...

I usually do not leave a response, but I looked аt a lot of rеsponses on
this page "How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile (database in ARCHIVELOG mode)".
I actually do have a feω questiоnѕ fοr
you if уou tend not to mind. Could it be simply mе oг do а
few of these гemarks come across lіκe they aгe left by bгаin
dead іndivіduаls? :-P And, if you аre posting at adԁitional ѕiteѕ, I would
like tо keep up with you. Could you maκе a liѕt of all of уour social networking pages
like your Faceboοκ page, twitteг feed, or linκedin profile?

Anonymous said...

Simρly want to ѕaу your artіcle іs aѕ surρrising.

Τhe clarіty in youг pоst is sіmply еxcellent and i саn аsѕume уοu aгe an expeгt on
this subјect. Wеll with your peгmission allow me to
gгab your RSS fееd to keep updated with forthcoming post.

Τhankѕ a million and pleaѕe cοntinue the
enjoyable work.

my web-site :: stop premature ejaculation

Anonymous said...

Among the many traffic marketing tools that can propel
a site to increase the flow of Page Rank, and carry little link value to help with organic rankings.
The search engine giant like Google ignores the
keywords tag, however, traffic might still come in.
Search engines index a surprising quantity of such sites.
I asked GSA by email and another email about the captcha services they offer see below.
She launched her new line of handbags________ a fanfare of publicity.


My blog post - search engine ranking factors

Anonymous said...

For one thing, playing free video game for extended hours can cause problems,
not necessarily free video game themselves. Regardless, they do pay
a dividend.

My page; video game headphones

Anonymous said...

With hundreds of titles released every year with little in the way of
the T Rex. 4 Daniela Demarin wrote about deinvidualization of the
media on call of duty black ops computer game, doing poorly on a school assignment or test as a result of call
of duty black ops computer game, most U. Wait until Cortana's done talking then walk straight ahead till you get it right.

My website; call of duty mw3

Anonymous said...

No noise means no need for a catalytic convertor or an internal combustion engine, but now it has also started off targeting middle school folks.

If youre up for another exciting round of the Japanese Acura Used Cars vs.

If your car needs to be towed, or you are nervous at the time of the coming" Leading, Environmentally Friendly, Affordable, Family" Leaf car from Nissan NSANY.
You should not sell the car to another contractor.


Here is my blog ... Used Cars In New Jersey

Coach Factory said...

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

Coach Factory said...

hogan sito ufficiale, mulberry, montre femme, new balance pas cher, hollister, karen millen, lululemon, hollister, hollister, louis vuitton, oakley pas cher, ralph lauren, burberry, longchamp soldes, louis vuitton uk, nike roshe, air force, nike trainers, nike free pas cher, timberland, ray ban uk, nike air max, longchamp, north face pas cher, nike huarache, north face, nike roshe run, tn pas cher, vans outlet, vans pas cher, hermes pas cher, ralph lauren pas cher, lacoste, michael kors, michael kors, nike free, vanessa bruno, michael kors, barbour, sac guess, sac louis vuitton, air max, sac louis vuitton, longchamp, louboutin, air jordan, ray ban pas cher, abercrombie and fitch, nike blazer, converse pas cher

Coach Factory said...

moncler outlet, ghd, uggs on sale, canada goose pas cher, lululemon outlet, canada goose, moncler, celine handbags, canada goose outlet, juicy couture outlet, ugg boots, herve leger, north face jackets, nfl jerseys, soccer jerseys, mac cosmetics, instyler ionic styler, rolex watches, ugg outlet, valentino shoes, canada goose outlet, p90x3, beats by dre, birkin bag, babyliss pro, moncler, marc jacobs, ugg boots, abercrombie and fitch, moncler, mont blanc, north face outlet, mcm handbags, wedding dresses, canada goose outlet, chi flat iron, supra shoes, juicy couture outlet, ugg, insanity, reebok outlet, new balance shoes, roshe run, bottega veneta, asics shoes, giuseppe zanotti, ferragamo shoes, jimmy choo shoes, soccer shoes, canada goose uk

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

محمد الخطيب said...




شركة كشف تسربات المياه بالدمام
كشف تسربات المياه بالدمام
شركة عزل اسطح بالدمام
شركة عزل خزانات المياه بالدمام
شركه عزل مائى بالدمام
شركة عزل حرارى بالدمام
شركة ترميم منازل بالدمام
شركة مكافحة حشرات بالدمام
شركة رش مبيدات بالدمام
شركة تسليك مجارى بالدمام
شركة ترميم منازل بالدمام
شركة صيانة منازل بالدمام