OraFAQ Forum: Server Administration » ORA-01552, plz help it's urgent

Home » RDBMS Server » Server Administration » ORA-01552, plz help it's urgent

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01552, plz help it's urgent [message #210639]

Thu, 21 December 2006 11:22

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

Report message to a moderator

Re: ORA-01552, plz help it's urgent [message #210644 is a reply to message #210639]

Thu, 21 December 2006 11:56

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;

Report message to a moderator

Re: ORA-01552, plz help it's urgent [message #210648 is a reply to message #210644]

Thu, 21 December 2006 12:08

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

Report message to a moderator

Re: ORA-01552, plz help it's urgent [message #210651 is a reply to message #210648]

Thu, 21 December 2006 12:13

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.

Report message to a moderator

Re: ORA-01552, plz help it's urgent [message #210653 is a reply to message #210651]

Thu, 21 December 2006 12:16

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'

Report message to a moderator

Re: ORA-01552, plz help it's urgent [message #210654 is a reply to message #210653]

Thu, 21 December 2006 12:17

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Did you issue:

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

Report message to a moderator

Re: ORA-01552, plz help it's urgent [message #210655 is a reply to message #210651]

Thu, 21 December 2006 12:20

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:

Report message to a moderator

Re: ORA-01552, plz help it's urgent [message #210660 is a reply to message #210655]

Thu, 21 December 2006 12:29

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 04:56:02 CDT 2024