Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Oracle.

Asked: September 30, 2016 - 1:59 pm UTC

Last updated: October 05, 2016 - 12:58 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi Tom,

As I have seen in alert log it is showing users02.dbf file got corrupted today.

So if rman backup and archive is available then it is simple to recover but in these two situation how to do that-

1. If I have rman backup with archive log 1 week before so how to recover the corrupted users02.dbf file till last week.?


2. If no any rman backup is available and database is in no archive log mode then is it possible to recover and how ?

Thanks

and Connor said...

We do have a whole chapter on this in the Backup/Recovery docs

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmrepai.htm

which walks through the steps you can take to

a) verify that you *really* have a corruption
b) the extent of it
c) what steps you can take

In a nutshell, if you have a backup you'll probably take the file offline, restore and recover it from backup, and bring the file back online.

If you dont have a backup, then you would export/unload/ctas/etc as many objects as you can from the tablespace to somewhere else, and then drop/recreate it.

But *read* the chapter first !

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, October 01, 2016 - 9:02 am UTC

Hi,

Thanks for quick reply,

What happen i explain the scenario -

7 Users like a,b,c,d,e,f,g belongs to users tablespace bydefault and some of my senior done database shutdown abort and startup when i check the alert log it is showing error like -

users02.dbf file file# 7 block 490459 corrupted and
another block is 478322. Then immediatly i change the users a,b,c,d,e,f default tablespace from users to new created.

Now it is working but when i will restart the db

1. Will it start i think that time it will give an error ?
2. I have changed the default tablespace from users to new created tablespace so all content move to new tablespace ?
3. I have changed the default tablespace from users to new created to i can take that datafile offline and start the db?
4. As you given the docs where rman backup and archive required but i have not both of them so how to recover ?
5. Before change they are using users tablespace now they are using new created tablespace so how to perform export and import export ?

Pls help me to get the solution for the same.

Thanks
Connor McDonald
October 01, 2016 - 12:29 pm UTC

For me, I'd move everything out of the tablespace (that's me just being paranoid - once a tablespace says a block is corrupted, I've pretty much lost confidence in it).

If that's not practical, then look at taking it offline and running a verify on it, and considering block repair.


Look in DBA_EXTENTS

A reader, October 03, 2016 - 8:28 pm UTC

Query DBA_EXTENTS and see what object contains the corrupted block. If it's an index, drop the index and rebuild it. Problem solved.




A reader, October 04, 2016 - 7:20 am UTC

Thanks for reply

I have seen there is tables also so I think I need to plan -

Move all tables indexes and all from users tablespace where the block is corrupted to another tablespace like
ALTER TABLE TABLENAME MOVE TABLESPACE NEWTABLESPACE;

And after to move all drop and re-create the users tablespace so it will not create an issue after to restart the database.

Pls suggest it is the way to do that activity. It is users tablespace which is created at the time of db creation.

Pls suggest.

Thanks
Connor McDonald
October 04, 2016 - 11:29 am UTC

You've pretty much got it already

1) create a new tablespace USERS_NEW
2) alter table T move tablespace USERS_NEW, for each table
3) alter index IX rebuild tablespace USERS_NEW, for each index

and check for LOBS as well

A reader, October 04, 2016 - 2:10 pm UTC

Thanks for reply

1.Can you pls let me know what about lob ?
2.My concern is can I move it in live when application using that tables will they affect ?
3. I got the list of objects which is in users tablespace from this query
SELECT SEGMENT_NAME,SEGMENT_TYPE,OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='USERS';
Is this right way to get the objects belonging to the users tablespace.

Thanks

Chris Saxon
October 05, 2016 - 12:58 am UTC

1) Lobs occupy space, so you might need to move them

alter table t move lob(y) store as ( tablespace new_ts )

2) Moving objects typically locks them

3) yes

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.