Home » RDBMS Server » Server Administration » ORA-01552, plz help it's urgent
icon9.gif  ORA-01552, plz help it's urgent [message #210639] Thu, 21 December 2006 11:22 Go to next message
assoora28
Messages: 18
Registered: December 2006
Location: Jordan
Junior Member

Actually, before one week I made (run Norton QuikScan) & this ended by the ORA-01033 problem, I realized that the file (E:\ORACLE\PRODUCT\10.2.\ORADATA\ORCL\UNDOTBS01.DBF)
was corrupted, so I dropped it from the (Dos command Prompt), then the system generated it again and then everything was excellent.
After that I couldn't create any new tables.
NB. I've more than 30 large table.

then i got the ORA-01552 problem,
cannot use system rollback segment for non-system tablespace...

i did this:(
& i got this error:

SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-00376:file 2 cannot be read at this time
ORA-01110:data file 2:
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'


SQL> select name, status, BLOCK_SIZE from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
STATUS BLOCK_SIZE
------- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
SYSTEM 8192

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
RECOVER 8192

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
ONLINE 8192


NAME
----------------------------------------------------------------------------------------------------
STATUS BLOCK_SIZE
------- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
ONLINE 8192

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
ONLINE 8192

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDO01.DBF
ONLINE 8192


i need to solve it...
Sad


thanks in advance.
Sad
Re: ORA-01552, plz help it's urgent [message #210644 is a reply to message #210639] Thu, 21 December 2006 11:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You manually deleted the E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF with from your filesystem?

What do you get when you issue:

SQL> sho parameter undo


As indicated in the output from your select against V$DATAFILE, you need to recover this file. You can try the following:

SQL> recover datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF';
SQL> alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' online; 

icon9.gif  Re: ORA-01552, plz help it's urgent [message #210648 is a reply to message #210644] Thu, 21 December 2006 12:08 Go to previous messageGo to next message
assoora28
Messages: 18
Registered: December 2006
Location: Jordan
Junior Member

i got this

SQL> sho parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1


is there any problem??
Sad
Re: ORA-01552, plz help it's urgent [message #210651 is a reply to message #210648] Thu, 21 December 2006 12:13 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
It appears that you set the UNDO_TABLESPACE to UNDOTBS1, but never changed the UNDO_MANAGEMENT to AUTO. At this point, you can try to recover the datafile with the commands I provided above.
Re: ORA-01552, plz help it's urgent [message #210653 is a reply to message #210651] Thu, 21 December 2006 12:16 Go to previous messageGo to next message
assoora28
Messages: 18
Registered: December 2006
Location: Jordan
Junior Member

i did & i got this error Sad

SQL> alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' online;
alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' online
*

ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'
Re: ORA-01552, plz help it's urgent [message #210654 is a reply to message #210653] Thu, 21 December 2006 12:17 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Did you issue:

SQL> recover datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF';
icon9.gif  Re: ORA-01552, plz help it's urgent [message #210655 is a reply to message #210651] Thu, 21 December 2006 12:20 Go to previous messageGo to next message
assoora28
Messages: 18
Registered: December 2006
Location: Jordan
Junior Member

SQL> recover datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF';

ORA-00279:change 4622656 generated at ... needed for thread...

ORA-00289:
ORA-00280:
Re: ORA-01552, plz help it's urgent [message #210660 is a reply to message #210655] Thu, 21 December 2006 12:29 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
At that point, you need to specify the appropriate archivelog or redo log. You can problem just hit ENTER to accept the suggested log.
Previous Topic: ORA-14520 Tablespace ### block size [8192] does not match existing object block size [16384]
Next Topic: How to erase these old archive log files exactly?
Goto Forum:
  


Current Time: Thu Dec 08 10:17:15 CST 2016

Total time taken to generate the page: 0.11575 seconds