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.

12 comments:

Unknown 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

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