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