|
|
|
|
| Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663049 is a reply to message #663048] |
Fri, 19 May 2017 15:06   |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Also, if you don't care what's inside the SYSAUX tablespace (that is if you don't use any components that reside in it like workspace manager, xdb, spatial, multimedia...) you can try to put its data files offline when in mount state and try to recover.
You have to show us what the following RMAN command tells you:
report schema;
list failure;
advise failure;
In the end, you can try the following RMAN command (and post its output):
Before backup all your database files.
And befor eposting, Please read How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663075 is a reply to message #663073] |
Mon, 22 May 2017 00:34 |
|
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Find the objects that own these blocks using the following query:
Select 'Block &2 of file '||f.file_name||' :
'||decode(e.owner, NULL, e.segment_name,
e.owner||'.'||e.segment_name)||
decode(e.partition_name, NULL, '', '.'||e.partition_name)||
' ('||e.segment_type||')' obj
from dba_extents e, dba_data_files f
where f.file_id = &1
and e.file_id = &1
and &2 between e.block_id and e.block_id+e.blocks-1
/
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663132 is a reply to message #663126] |
Tue, 23 May 2017 02:01 |
|
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
First of all shutdown your database and copy all its files (data, temp, redo logs, control files... everything) at OS level in a safe place, what we'll do, if it doesn't work, will render all further recovery impossible, so we'll need the original files back to its original place if we want to do several tests.
Then if you use a spfile restart the instance in NOMOUNT mode and execute the following command:
create pfile=<path and name of a file as you want, say C:\init.ora> from spfile
Then shutdown the database.
Now edit your init.ora file (original or the one generated) and add/modify the following lines:
undo_management='MANUAL'
_allow_resetlogs_corruption=true
_allow_terminal_recovery_corruption=true
_corrupted_rollback_segments=true
Then execute the following commands in SQL*Plus:
startup mount pfile=<your init.ora file>
recover database until cancel;
CANCEL
alter database open resetlogs;
exit
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663311 is a reply to message #663290] |
Mon, 29 May 2017 13:09 |
 |
ulvi_ugur
Messages: 28
Registered: May 2017
|
Junior Member |
|
|
Hi Michel,
Unfortunately, the process could not proceed. The console looks like :
SQL> create directory ulvi_export_dir as 'E:\BACKUP\ORI-1';
Directory created.
SQL>
and export console is :
N:\>expdp master dumpfile=master.dmp DIRECTORY=ulvi_export_dir LOGFILE=master.log schemas=master
Export: Release 11.2.0.1.0 - Production on Mon May 29 20:04:53 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user MASTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 428
ORA-39077: unable to subscribe agent KUPC$A_1_20170529200459 to queue "KUPC$C_1_20170529200458"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 250
ORA-25448: rule SYS.KUPC$C_1_20170529200458$45 has errors
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'N:\ORADATA\DEV\SYSAUX01.DBF'
|
|
|
|
|
|
|
|
| Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663315 is a reply to message #663314] |
Mon, 29 May 2017 13:43 |
|
ulvi_ugur
Messages: 28
Registered: May 2017
|
Junior Member |
|
|
Yep, you are right. The result seems to be the same though :
N:\>expdp 'sys/mypass as sysdba' dumpfile=master.dmp DIRECTORY=ulvi_export_dir LOGFILE=master.log schemas=master
Export: Release 11.2.0.1.0 - Production on Mon May 29 20:41:43 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 428
ORA-39077: unable to subscribe agent KUPC$A_1_20170529204144 to queue "KUPC$C_1_20170529204143"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 250
ORA-25448: rule SYS.KUPC$C_1_20170529204143$47 has errors
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'N:\ORADATA\DEV\SYSAUX01.DBF'
I leave the db as it is, I don't even move 
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.
