Good example
Fung, November 11, 2002 - 10:21 pm UTC
It is a good example of opening a database while one db file is missing
A reader, August 18, 2003 - 10:42 am UTC
Tom,
I read "Except for the SYSTEM tablespace or a tablespace with an active undo segment,
tablespaces can be taken offline, leaving the database running."
correct me if i am wrong, a tablespace can have a active undo segment only when we are using the tablespace for some database operation - am i right? ...
thanks,
August 18, 2003 - 2:52 pm UTC
no, an undo segment is generally in a tablespace SEPARATE from where the transaction is taking place.
A tablespace can have an active undo segment if
a) the tablespace is an UNDO Tablespace in 9i OR contains rollback segments in 9i and before
AND
b) there is an active transaction in the database
Thanks for making me understand
A reader, August 19, 2003 - 12:38 am UTC
System Tablespace Recovery
Prince, December 15, 2004 - 9:14 am UTC
Well Tom, you have beautifully described the scenario about datafile and undo segment related recovery. I would apprecite if you elaborate the recovery methodology in the following scenario.
o The Oracle8.0.5 database in noarchivelog.
o A file was added into the system tablespace last month.
o When it is tried to open the database today, it is just mounted and gives the following error:-
ORA-01122 database file 13 failed verification check
ORA-01110 datafile 13 'SYS2ORCL.ORA'
ORA-01200 actual file size of 2048 is smaller than correct size of 2073600 blocks
What action should be taken to recover. I know that Oracle support can support well at this time but I need your expert opinion too :)
Regards,
December 15, 2004 - 2:05 pm UTC
when did you last backup, you are going to go back to that point in time for that datafile at the very least.
In noarchivelog mode you have made the decision that "we WILL most definitely lose data sometime in the future".
Not might
WILL...
and today is probably that day.
You can either:
restore this tablespace, system, rollback to another machine. open this 'mini instance'. export this tablespace
or
restore the entire database, losing everything that happened since the last full backup.
Database Recovery when datafile is corrupt
Murali Parameswaran, January 15, 2005 - 6:30 am UTC
Dear Tom,
I had this typical problem on my oracle 7.3.4. One datafile got corrupted, and I was repeatedly getting error ORA-01200, and database could not be mounted.
Thanks to you, I dropped the data file, then the tablespace itself and recovered the (rest of the) database.
Murali
Oracle startup problem
A reader, May 13, 2005 - 12:24 pm UTC
Hi Tom,
I am having a problem. We have a database setup in a server and for some reasons we had to add an extra hard drive to the server. After this action, when I tried to connect to the database I got a message "Oracle initialization or shutdown in progress". So I did the following in command prompt:
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\>set oracle_sid=test
C:\>sqlplus /nolog
12:12:54 > connect sys/xxxxx as sysdba;
Connected.
12:13:09 > shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
12:13:25 > startup;
ORACLE instance started.
Total System Global Area 868220928 bytes
Fixed Size 791320 bytes
Variable Size 596897000 bytes
Database Buffers 268435456 bytes
Redo Buffers 2097152 bytes
Database mounted.
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: 'C:\ORADATA\DATA\DATATEST01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile
This is not during any backup/restore. What should I do? Please help.
Thanks.
May 13, 2005 - 12:48 pm UTC
you did something there -- not having the entire sequence of events puts me at a disadvantage here. You'll want to work with support on this.
Database startup problem too
Tony, May 19, 2005 - 2:36 am UTC
Hi Tom
Our production database started in mount state only today and returned internal error. unfortunately our database is in NOARCHIVE mode.
I tried the following in an attempt to resolve the situation
Enter user-name: / @db as sysdba
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 1225860196 bytes
Fixed Size 455780 bytes
Variable Size 595591168 bytes
Database Buffers 629145600 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr1_lostwrt], [], [], [], [],
[], [], []
SQL>
Knowing that (100% sure) no parameters were changed nor system wide changes made on the server.
our system is based on Acer G510 dual Xeon 2.8 2GB RAM, RAID5 operating on Windows 2003 server standard with Oracle9i 9.2.0.1.0
Any suggestion is appreciated as always.
Thanks in Advance
May 19, 2005 - 7:54 am UTC
you are working this with SUPPORT aren't you?
Re
Tony, May 19, 2005 - 3:29 am UTC
Hi Tom i've found out that the server unexpectedly has restarted after utility power failure AND UPS failure and in the trace files the error code is noted.
<<alert log>>
Wed May 18 18:08:35 2005
Errors in file d:\oracle\admin\rim\udump\rim_ora_188.trc:
ORA-00600: internal error code, arguments: [kcratr1_lostwrt], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...
Shutting down instance: further logons disabled
Shutting down instance (immediate)
don't want to sound desperate but I am :)
Thanks in advance
May 19, 2005 - 7:58 am UTC
*support*, contact *support* -- that is what they do.
Problem solved
Tony, May 19, 2005 - 6:24 am UTC
Hi Tom
the problem is solved, we simply did a RECOVER DATABASE
and it worked just fine.
i am checking the trace files to know exactly what went wrong and how it got fixed.
seems like i had a bad block or something.
Regards,
Tony G.
Instance
G.Ramesh, May 23, 2005 - 5:07 am UTC
ORACLE instance started.
Total System Global Area 31629312 byte
Fixed Size 49152 byte
Variable Size 18399232 byte
Database Buffers 13107200 byte
Redo Buffers 73728 byte
Database mounted.
Database opened.
what is fixed size and vairable size in instance startup
uses and purpose of fixed size and variable siza
Thanks and regards
May 23, 2005 - 10:54 am UTC
Rahul C
Rahul C, August 20, 2005 - 2:00 am UTC
Hi Tom,
we too have the same problem Tom, our one db is working on NOARCHIVELOG mode and one off our datafile's status is "recover" actually it happened accidentally. but as it is in noarchivelog mode we cann't shutdown the database and put the things on. as next start up it'll ask for the thread number.
as you explain steps top above that shutdown the db and in mount stage make the datafile offline drop . means tablespace maked for drop (what i thing may be correct!!! rite tome). but the thing is that we can drop the tablespace also b'coze it contains four datafiles in the tablespace and it has vimp data objects in that tablespace.
so we cann't thing for dropping the tablespace.
what i thing is to create other tablespace and move all the data objects in new tablespace and shrink the old tablespace's datafile size and then drop the tablespace is it the correct way.
any other step is their so that i will not have to drop the tablespace as there are many tables and data inside the tablespace.
please help me ....
August 20, 2005 - 5:07 pm UTC
please contact support to go over your options, I'm not going to give advice on this one as I don't think I have sufficient detail. Please use support.
database data file missing/deleted
suki, August 21, 2005 - 11:47 am UTC
yes...ofcourse very useful in solving this problem
All questions and their resolutions I read so far
Muhammad Riyaz, 11673 Sandal Wood Lane, Manassas, VA 20112, September 11, 2005 - 12:54 pm UTC
Tom:
I have your book EXPERT ONE-ON-ONE, what a wonderful book, but I request rather urge you to have a book in which you should have question and the answers of all Oracle Problems you covered so far. I am ready to buy such a book, but I could not as yet. You explain the oracle problems and their solutions in such an easy way I have not seen that any where so far. So go ahead and have a book ready for Oracle Resolutions.
unable to create tablespace
John K., October 02, 2005 - 3:19 pm UTC
I have the exactly same problem, but with 10g personal edition:
SYS@orcl AS SYSDBA> recover database
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 3: 'C:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: 'C:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-01200: actual file size of 8763 is smaller than correct size of 33280 blocks
I can live without the data for now; I want to learn how to restore the database (and your new book is on its way to me from Amazon right now :-) )
I tried your step by step approach at the top of this thread, but when I try to create a tablespace, I get:
SYS@orcl AS SYSDBA> run
1* create tablespace test datafile 'C:\ORADATA\ORCL\SYSAUX01.DBF' size 1m
create tablespace test datafile 'C:\ORADATA\ORCL\SYSAUX01.DBF' size 1m
*
ERROR at line 1:
ORA-01109: database not open
October 02, 2005 - 5:37 pm UTC
the create tablespace command was simply there to create a tablespace - from which we would "lose" the datafile - it was not part of the 'recovery' process (which isn't really a recovery but more of a "surgically remove it" process)
tablespace cna't be dropped, but file was dropped
John K., October 02, 2005 - 3:43 pm UTC
I misunderstood your instructions at the top. I have successfully deleted the offending file, but did not delete any tablespaces.
SYS@orcl AS SYSDBA> run
1 alter database datafile 'C:\ORADATA\ORCL\SYSAUX01.DBF' offline drop
2*
Database altered.
These are my tablespaces:
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
It looked like the file came from the SYSAUX tablespace (going by the file name), but that tablespace cannot be dropped. When I log back in as a regular user, my original tables are right where I left them, so to speak. Is there anything I need to do now? Thank you for your help.
JOHN@orcl > select table_name from user_tables
TABLE_NAME
------------------------------
EMP
DEPT
BONUS
SALGRADE
DUMMY
TMP_SCD
TMP_IN
TMP_IN_SCD
TMP_IN_CUI_STR
TMP
RXNCONSO
RXNREL
RXNSAB
RXNSAT
RXNSTY
RXNDOC
16 rows selected.
October 02, 2005 - 5:38 pm UTC
what is the status of your sysaux tablespace
something is not right...
John K., October 02, 2005 - 7:46 pm UTC
Something is not right, now (since rebooting):
SYS@orcl AS SYSDBA> connect sys as sysdba
Enter password:
Connected to an idle instance.
SYS@orcl AS SYSDBA> select status from dba_tablespaces where tablespace_name='SYSAUX';
select status from dba_tablespaces where tablespace_name='SYSAUX'
*
ERROR at line 1:
ORA-01012: not logged on
SYS@orcl AS SYSDBA> connect john/john
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
Warning: You are no longer connected to ORACLE.
now it's back
John K., October 02, 2005 - 8:30 pm UTC
I noticed using netstat that I had about 30 network connections to port 1521. I exited the db, stopped the windows ORCL services, restarted them, rebooted, re-did the directions in the top part of the post. I have one conection to port 1521, and now:
JOHN@orcl > connect sys/****** as sysdba
Connected.
SYS@orcl AS SYSDBA> select status from dba_tablespaces where tablespace_name='SYSAUX';
STATUS
---------
ONLINE
SYS@orcl AS SYSDBA>
It seems to be OK; we'll see tomorrow. You are the greatest, Tom.
UNDO datafile OFFLINE
kish, December 22, 2005 - 6:51 am UTC
Tom,
I have an undo tablespace with two datafiles in it.
Out of which one datafile has been created with special characters in it .And because of that ,the online backups are failing because of our scripts and that needs to be sorted out.
/oracle/CAMELIAL/data1/rbs_01.dbf
/oracle/CAMELIAL/data2/rbs_02.dbf
SQL> !\ls -ltr /oracle/CAMELIAL/data1/rbs_01.dbf
-rw------- 1 oracle dba 2097160192 Dec 22 04:00 /oracle/CAMELIAL/data1/rbs_01.dbf
SQL> !ls -ltr /oracle/CAMELIAL/data2/rbs_02.dbf
/oracle/CAMELIAL/data2/rbs_02.dbf not found
SQL> !\ls -ltr /oracle/CAMELIAL/data2/rbs_02.dbf
-rw------- 1 oracle dba 1073750016 Dec 22 04:06 /oracle/CAMELIAL/data2/rbs_02.dbf
can i do the following steps without affecting the database availability :
1) OFFLINE THE UNDO DATAFILE
2) mv /oracle/CAMELIAL/data2/rbs_02.dbf* /oracle/CAMELIAL/data2/rbs_02.dbf
3) online the undo datafile.
Are these steps fine or will the database ask for any recovery ? Pls guide me !
Thanks in advance
kish
December 22, 2005 - 10:57 am UTC
I've never see
!\ls
before - what is the 'impact' of that? not sure what I'm looking at.
if you offline the undo datafile, you'll offline the tablespace. You could do this instead:
a) create a new rollback tablespace with "good" files
b) create new public rollback segments therein (so they online themselves upon restart)
c) offline the old rollback segments
d) when they really go "offline" (after you don't need them for read consistency as well - long running queries might need them) drop the tablespace
that way it would be "online"
But to rename a file, you would
a) offline tablespace
b) move file in filesystem
c) alter database rename datafile to tell us the new name
d) online the tablespace
but this tablespace contains all of your rollback segments! so, it will require "no one do anything for a bit"
no indication
RD, July 18, 2006 - 10:20 pm UTC
Hi Tom,
Above you said "if you offline the undo datafile, you'll offline the tablespace."
SQL> select file_name , status from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME STATUS
---------------------------------------- ---------
C:\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF AVAILABLE
C:\PRODUCT\ORADATA\ORCL\UNDOTBS02.DBF AVAILABLE
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name like '%UNDO%';
TABLESPACE_NAME STATUS
------------------- ------
UNDOTBS1 ONLINE
SQL> alter database datafile 'C:\PRODUCT\ORADATA\ORCL\UNDOTBS02.DBF' offline;
Database altered.
SQL> select name, status from v$datafile where name like '%UNDO%';
NAME STATUS
---------------------------------------- -------
C:\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF ONLINE
C:\PRODUCT\ORADATA\ORCL\UNDOTBS02.DBF RECOVER
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name like '%UNDO%';
TABLESPACE_NAME STATUS
------------------- ------
UNDOTBS1 ONLINE
Though the datafile is showing status of recover now,
the tablespace is still online and I can do things that
require undo tablespace such as :-
SQL> create table a as select * from dba_objects;
Table created.
SQL> delete from a;
50414 rows deleted.
This means that like any other datafile any number of undo datafiles can be offlined and the tablespace is still going to be online. I was thinking that if one takes any undo datafile offline then the tablespace is also offlined.
Regards,
RD.
July 19, 2006 - 9:05 am UTC
the tablespace is in effect "not really useful", you'll get all kinds of errors over time regarding that offline file.
thanks it was rellaly helpful :)
Srithar, August 09, 2006 - 12:29 pm UTC
I have oracle 10g set up on noarchive mode as well
This was really helpful. Some one restarted the machine where I was running the oracle 10g. Thats what caused the issue
Thanks again,
Srithar
recover database for ORA-00600: internal error code, arguments: [kcratr1_lostwrt]
Rommel Sharma, October 19, 2006 - 6:58 am UTC
For
"ORA-00600:internal error code,arguments:[kcratr1_lostwrt]"
The tip to try out the option:
SQL> recover database
is very useful, as this is the first most promising solution and worked for me. As ORA-600 is one very generic error code and can come for a range of issues.
Thanks for your inputs.
Recovery of datafiles
susan, December 05, 2006 - 8:11 am UTC
Hi Tom,
I'm working on 9iR2 database and we are using RMAN for backup and recovery.
I was trying restoring the database to another server with different locations. After takin the database to mount state, gave switch command (switching to new loactions) and restore database command. When we give RESTORE DATABASE command,(from alert logs)its seen the datafiles are getting restored in a particular order(say, 4,17,30..etc ) Can you explain why this particular order and how Oracle determines which file to be restored first and so on.
Thanks a lot for you reply.
sysaux datafile corrupted
K P Ratnaker, June 05, 2007 - 10:44 am UTC
Hi tom,
select * from v$database_block_corruption
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
------- ---------- ---------- ------------------ ---------
3 18677 1 0 FRACTURED
3 18685 1 0 FRACTURED
select * from dba_extents
where file_id=3
and 18677 between block_id and
(block_id +( blocks -1));
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE SYSAUX 17 3 18569 1048576 128 3
How I can repair corrupted bolck? Please send me solution.I am not able to take backup.
Ratnaker
June 06, 2007 - 1:04 pm UTC
please utilize support.
Undo datafile corrupt Oracle 10g Database not opening
Muhammad Akram, June 28, 2008 - 6:37 am UTC
I followed your suggested steps, but no lucks. I am running 10g R2 Solaris 10.
Please advise.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 595591168 bytes
Fixed Size 1281576 bytes
Variable Size 222298584 bytes
Database Buffers 369098752 bytes
Redo Buffers 2912256 bytes
SQL>
SQL>
SQL> alter database mount;
Database altered.
SQL> alter database datafile '/export/home/oracle/oradata/ecibfo/undotbs01.dbf' offline drop;
Database altered.
SQL> select v.file#, v.name, v.STATUS
from v$datafile v
where v.name like '%undo%';
FILE# NAME STATUS
2 /export/home/oracle/oradata/ecibfo/undotbs01.dbf RECOVER
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Here is the alert log
Completed: alter database mount
Sat Jun 28 06:15:42 2008
alter database datafile '/export/home/oracle/oradata/ecibfo/undotbs01.dbf' offline drop
Sat Jun 28 06:15:42 2008
Completed: alter database datafile '/export/home/oracle/oradata/ecibfo/undotbs01.dbf' offline drop
Sat Jun 28 06:17:34 2008
alter database open
Sat Jun 28 06:17:34 2008
Beginning crash recovery of 1 threads
Sat Jun 28 06:17:34 2008
Started redo scan
Sat Jun 28 06:17:34 2008
Completed redo scan
32 redo blocks read, 3 data blocks need recovery
Sat Jun 28 06:17:34 2008
Started redo application at
Thread 1: logseq 40, block 2, scn 1645657
Sat Jun 28 06:17:34 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 40 Reading mem 0
Mem# 0 errs 0: /export/home/oracle/oradata/ecibfo/redo03.log
Sat Jun 28 06:17:34 2008
Completed redo application
Sat Jun 28 06:17:34 2008
Completed crash recovery at
Thread 1: logseq 40, block 34, scn 1665688
3 data blocks read, 3 data blocks written, 32 redo blocks read
Sat Jun 28 06:17:34 2008
Thread 1 advanced to log sequence 41
Thread 1 opened at log sequence 41
Current log# 1 seq# 41 mem# 0: /export/home/oracle/oradata/ecibfo/redo01.log
Successful open of redo thread 1
Sat Jun 28 06:17:34 2008
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Jun 28 06:17:34 2008
SMON: enabling cache recovery
Sat Jun 28 06:17:35 2008
Successfully onlined Undo Tablespace 1.
Sat Jun 28 06:17:35 2008
SMON: enabling tx recovery
Sat Jun 28 06:17:35 2008
Database Characterset is WE8ISO8859P1
Sat Jun 28 06:17:35 2008
Errors in file /export/home/oracle/admin/ecibfo/udump/ecibfo_ora_1509.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/export/home/oracle/oradata/ecibfo/undotbs01.dbf'
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 1509
ORA-1092 signalled during: alter database open...
June 28, 2008 - 2:02 pm UTC
no, you did not follow my steps, they were very very very clear:
Here are the steps you can use to get the database going again without that
tablespace (if you have no backups, its pretty much gone -- if you want to try
and recover it, please Call support before doing anything else. Everything
you do will preclude other paths of actions. It would be best to have a
converstation with a person to plan the attack....
and I wasn't doing this for a corrupt datafile, this was a datafile they wanted to get rid of
and guess what - undo is NOTHING YOU WANT TO GET RID OF.
I pray you have backups
stop - stop right now
call support. period. hands OFF OF THE KEYBOARD