Home » RDBMS Server » Backup & Recovery » Oracle SYSAUX UNDO tablespaces corrupt (Oracle 11)
Oracle SYSAUX UNDO tablespaces corrupt [message #663047] Fri, 19 May 2017 14:27 Go to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Hi All,

After some obscure reason of disk array failure on our Oracle server, I restarted it and the array is back.

HOWEVER, I cannot open the DB any more. RMAN -> validate database; shows that SYSAUX and UNDO tablespaces have failed. Also control files were out of sync but it fixed it.

Any ideas how I can drop these tablespaces and open the database ? It looks like all other 17 tablespaces are OK.

I am afraid, we don't have a backup and loss of the database would be a real shame Mad

Please advice,

Ulvi
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663048 is a reply to message #663047] Fri, 19 May 2017 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Any ideas how I can drop these tablespaces and open the database?
You cannot drop these tablespaces, they are mandatory.

Quote:
I am afraid, we don't have a backup and loss of the database would be a real shame
I concur, the prime duty of a DBA is to be able to restore the database at any time.

I suggest you find and hire a real expert in the matter to recover the database at most as possible which means without the latest transactions if you UNDO is corrupted.
It should be possible if you have all archived logs since database creation but it is quite touchy.


Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663049 is a reply to message #663048] Fri, 19 May 2017 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
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):
repair failure;
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 #663050 is a reply to message #663049] Fri, 19 May 2017 15:31 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Thanks for your guidance Michel !

Please find the results of the commands you suggested.

I am not sure if the issue is fixed as the recovery apparently took only 0 secs.

Please advice further.

Ulvi
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663052 is a reply to message #663050] Fri, 19 May 2017 22:38 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed.
Execute the following command (at OS prompt on the database server) and post the result:
dbv file=N:\ORADATA\DEV\SYSAUX01.DBF

[Updated on: Fri, 19 May 2017 22:40]

Report message to a moderator

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663054 is a reply to message #663052] Sat, 20 May 2017 03:01 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Thanks a lot.

Please find the console of dbv attached.

Bests,

Ulvi
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663055 is a reply to message #663054] Sat, 20 May 2017 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Hopefully all corrupted blocks are in empty ones, so it does not really matter.
You can try the following within RMAN:
validate tablespace corrupt;
backup check logical datafile 2;
recover corruption list;
validate tablespace corrupt;
And within SQL*Plus:
select * from v$database_block_corruption order by 1,2;
Then we will see from the result.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663058 is a reply to message #663055] Sat, 20 May 2017 04:33 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Please see the console in the attached file.

I see something very strange over there ..

It looks like it is trying to create a file called : "B:\ORACLE_DATA\ORI-1\BACKUP\TESTER" and I don't even have a B drive Shocked .

I did not proceed after that.

[Updated on: Sat, 20 May 2017 04:34]

Report message to a moderator

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663059 is a reply to message #663058] Sat, 20 May 2017 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry, it was
validate tablespace SYSAUX;
(I copied and pasted from tests I made.)
Post the result of this command and the last query I posted.

Your second error comes from RMAN configuration.
Post the result of, in RMAN:
SHOW ALL;
in SQL*Plus:
show parameter recovery

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663060 is a reply to message #663059] Sat, 20 May 2017 06:21 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Yep, looks like I have a ghost parameter there.

I attached the results of the console and the trace file, in case it helps.

Bests,

Ulvi
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663061 is a reply to message #663060] Sat, 20 May 2017 06:22 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Now the trace file ..
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663062 is a reply to message #663060] Sat, 20 May 2017 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The wrong parameter is:
Quote:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'B:\ORACLE_DATA\ORI-1\BACKUP\TESTER';
Change the destination (format) to one that exists (same command).

What is the result of
select * from v$database_block_corruption order by 1,2;
The result of VALIDATE is different than the one of DBV, unfortunately this one shows block corruptions inside objects. We will see later with the output of the query.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663073 is a reply to message #663062] Sun, 21 May 2017 14:24 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Hi there,

Had to take a break, apologies. Please find the result of the query below.

SQL> select * from v$database_block_corruption order by 1,2;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         2       4861          1                  0 FRACTURED
         2      30941          1                  0 FRACTURED
         2      60977          1                  0 FRACTURED
         2      64401          1                  0 FRACTURED
         2      68826          1                  0 FRACTURED
         2      68858          1                  0 FRACTURED
         2      68954          1                  0 FRACTURED
         2      78874          1                  0 FRACTURED
         2      93476          1                  0 FRACTURED
         2      96071          1                  0 FRACTURED
         3        329          1                  0 FRACTURED

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         3       1033          1                  0 FRACTURED
         3       1065          1                  0 FRACTURED
         3       1071          1                  0 FRACTURED
         3       1129          1                  0 FRACTURED

15 rows selected.
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663075 is a reply to message #663073] Mon, 22 May 2017 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
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
/
where &1 is the file number and &2 the block number.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663085 is a reply to message #663075] Mon, 22 May 2017 03:37 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member

Thanks !

Well, it looks like I cannot run this query before I open the database :

ERROR at line 6:
ORA-01219: database not open: queries allowed on fixed tables/views only

.. startup does not complete, probably because of the file integrity issue :

RMAN> startup

Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/22/2017 10:33:39
ORA-03113: end-of-file on communication channel
Process ID: 9768
Session ID: 66 Serial number: 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/22/2017 10:33:39
ORA-03113: end-of-file on communication channel
Process ID: 9768
Session ID: 66 Serial number: 7



Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663114 is a reply to message #663085] Mon, 22 May 2017 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My fault, it is obvious in your case you can't do that.
So back to the previous strategy: RMAN.
validate tablespace SYSAUX;
backup check logical datafile 2;
recover corruption list;
validate tablespace SYSAUX;
What does this give?
Note that intend to solve ONLY the problem on SYSAUX, there still be the one on UNDO tablespace afterwards.
It is very difficult (and long) to solve such a problem in a forum, can't you get an Oracle expert around you?

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663115 is a reply to message #663114] Mon, 22 May 2017 12:22 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
No worries Michel, all good, thanks for staying with me Wink

First of all :
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT "M:\ORADATA\BACKUP";

and then :
RMAN> validate tablespace SYSAUX;

Starting validate at 22-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=114 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=N:\ORADATA\DEV\SYSAUX01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    FAILED 0              21222        105017          167376798
  File Name: N:\ORADATA\DEV\SYSAUX01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       6              28381
  Index      3              25047
  Other      1              30310

validate found one or more corrupt blocks
See trace file n:\oracle_base\diag\rdbms\dev\dev\trace\dev_ora_2112.trc for details
Finished validate at 22-MAY-17

RMAN>

now it gets interesting for me :
RMAN> backup check logical datafile 2;

Starting backup at 22-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=N:\ORADATA\DEV\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/22/2017 19:08:02
ORA-19566: exceeded limit of 0 corrupt blocks for file N:\ORADATA\DEV\SYSAUX01.DBF

My guess is, since the backup step does not work, the rest does not proceed as it should :
RMAN> backup check logical datafile 2;

Starting backup at 22-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=N:\ORADATA\DEV\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/22/2017 19:08:02
ORA-19566: exceeded limit of 0 corrupt blocks for file N:\ORADATA\DEV\SYSAUX01.DBF
RMAN>recover corruption list;

Starting recover at 22-MAY-17
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/22/2017 19:11:16
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore

RMAN> validate tablespace SYSAUX;


I wonder if it is possible to change the "tolerance" parameter of corrupt blocks so that the backup can proceed. I am open to ideas Smile
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663117 is a reply to message #663115] Mon, 22 May 2017 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes it is possible, use:
set maxcorrupt for datafile 2 to 10;
and try (after the backup):
recover corruption list datafile 2 block 4861 to 96071;
to focus the recovery on the corrupted blocks of datafile 2.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663118 is a reply to message #663117] Mon, 22 May 2017 12:51 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
OK, I must have done something wrong, it is still looking for the backup copies :
RMAN> run  {
2>   set maxcorrupt for datafile 2 to 10;
3>   recover corruption list datafile 2 block 4861 to 96071;
4> }

executing command: SET MAX CORRUPT

Starting recover at 22-MAY-17
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/22/2017 19:49:32
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663119 is a reply to message #663118] Mon, 22 May 2017 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Insert the above backup command between set and recover.
Note this will recover only the blocks that are free (not owned by any object).
The list afterwards will tell us the blocks that have been recovered (and so lost as you have no previous backup).

[Updated on: Mon, 22 May 2017 13:26]

Report message to a moderator

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663121 is a reply to message #663119] Mon, 22 May 2017 14:29 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
OK.

I think we need to do something on the UNDO file as well; do we need to backup both and recover both ?
RMAN> run  {
2>      set maxcorrupt for datafile 2 to 10;
3>      backup check logical datafile 2;
4>      recover corruption list datafile 2 block 4861 to 96071;
5> }

executing command: SET MAX CORRUPT

Starting backup at 22-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=N:\ORADATA\DEV\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-17
channel ORA_DISK_1: finished piece 1 at 22-MAY-17
piece handle=M:\ORADATA\BACKUP tag=TAG20170522T211857 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 22-MAY-17

Starting recover at 22-MAY-17
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/22/2017 21:20:57
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN>




Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663122 is a reply to message #663121] Mon, 22 May 2017 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is strange it asks for datafile 3 for this command.
Anyway, does the same thing for this tablespace (list failure, set maxcorrupt with the number of corruptions found) then for both tablespaces at the same time backup and recover corruption list without parameter.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663123 is a reply to message #663122] Mon, 22 May 2017 14:50 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Tricky and totally beats me :
RMAN> run  {
2>      set maxcorrupt for datafile 2 to 10;
3>      backup check logical datafile 2;
4>
5>      set maxcorrupt for datafile 3 to 10;
6>      backup check logical datafile 3;
7>
8>      recover corruption list datafile 2 block 4861 to 96071;
9>      recover corruption list datafile 3 block 329 to 1129;
10> }

executing command: SET MAX CORRUPT

Starting backup at 22-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=N:\ORADATA\DEV\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 22-MAY-17
channel ORA_DISK_1: finished piece 1 at 22-MAY-17
piece handle=M:\ORADATA\BACKUP tag=TAG20170522T214915 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 22-MAY-17

executing command: SET MAX CORRUPT

Starting backup at 22-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/22/2017 21:49:22
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

RMAN>
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663124 is a reply to message #663123] Mon, 22 May 2017 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


Gasp! You are in NOARCHIVELOG mode.
It's over to try to recover anything.
We now just can to try to hack the database to force opening it to be able to export your data and create a new database.
It is too late for me to safely do it with you tonight, we'll can start it tomorrow.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663126 is a reply to message #663124] Mon, 22 May 2017 15:17 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
It sounds quite dramatic Confused

I am guessing, we will need another oracle instance to force the data files to it. Sounds dirty but very interesting Razz

Please post me some guidelines when you have time so that I can do some prep work for the follow-up actions.
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663132 is a reply to message #663126] Tue, 23 May 2017 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
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
If your spfile is not in the default location then specify the location at the end of the command.
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
Post the whole SQL*Plus session.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663267 is a reply to message #663132] Sat, 27 May 2017 05:38 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Hi Michel,

This step required some HW prep as the total data files size was like 500 Gigs.

Now, I have the possibility to follow this up. Please bear with me, I will post the results today.

Cheers,

Ulvi
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663268 is a reply to message #663267] Sat, 27 May 2017 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, waiting for you...

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663289 is a reply to message #663268] Sun, 28 May 2017 19:43 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Ok, finally I could run this now.

The console looks like this :
SQL> startup mount pfile='C:\Users\ulvi\init.ora';
ORACLE instance started.

Total System Global Area 2.1179E+10 bytes
Fixed Size                  2185032 bytes
Variable Size            1.2214E+10 bytes
Database Buffers         8925478912 bytes
Redo Buffers               37048320 bytes
Database mounted.
SQL> recover datbase until cancel;
ORA-00905: missing keyword


SQL> recover database until cancel;
ORA-00279: change 167478246 generated at 05/29/2017 02:26:05 needed for thread
1
ORA-00289: suggestion :
N:\ORACLE_BASE\FLASH_RECOVERY_AREA\DEV\ARCHIVELOG\2017_05_29\O1_MF_1_8530_%U_.AR
C
ORA-00280: change 167478246 for thread 1 is in sequence #8530


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'N:\ORADATA\DEV\SYSTEM01.DBF'


SQL> alter database open resetlogs;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663290 is a reply to message #663289] Mon, 29 May 2017 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, so now export all your schemas:
expdp <sys connect string> dumpfile=<file name> schemas=<list of your application schemas> logile=<log file name>
Post the content of the logfile if there are errors.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663311 is a reply to message #663290] Mon, 29 May 2017 13:09 Go to previous messageGo to next message
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 #663313 is a reply to message #663311] Mon, 29 May 2017 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said use SYS.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663314 is a reply to message #663313] Mon, 29 May 2017 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You are in a special state, everything must be done using SYS.
Also, do not shut down your database, you may not be able to restart it and then have to restart from the beginning from the files you copied.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663315 is a reply to message #663314] Mon, 29 May 2017 13:43 Go to previous messageGo to next message
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 Smile
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663316 is a reply to message #663315] Mon, 29 May 2017 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, then use old exp command:
exp 'sys/mypass as sysdba' file=E:\BACKUP\ORI-1\master.dmp LOG=E:\BACKUP\ORI-1\master.log owner=master

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663317 is a reply to message #663316] Mon, 29 May 2017 15:14 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Fresh update below. I wonder what kind of dependency Oracle has on this particular file to export the data :/
N:\>exp 'sys as sysdba' file=E:\BACKUP\ORI-1\master.dmp LOG=E:\BACKUP\ORI-1\master.log owner=master

Export: Release 11.2.0.1.0 - Production on Mon May 29 22:12:11 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
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MASTER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MASTER
About to export MASTER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'N:\ORADATA\DEV\SYSAUX01.DBF'
EXP-00000: Export terminated unsuccessfully
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663320 is a reply to message #663317] Tue, 30 May 2017 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have you really some master's objects in SYSAUX tablespace?
Check all owners in this tablespace:
select distinct owner from dba_segments where tablespace_name='SYSAUX' order by 1;

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663322 is a reply to message #663320] Tue, 30 May 2017 02:59 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
There you go, please see below. I also tried other schemas besides master, the result is the same :/

SQL> select distinct owner from dba_segments where tablespace_name='SYSAUX' order by 1;

OWNER
------------------------------
APEX_030200
CTXSYS
DBSNMP
EXFSYS
MDSYS
OLAPSYS
ORDDATA
ORDSYS
SYS
SYSMAN
SYSTEM

OWNER
------------------------------
WMSYS
XDB

13 rows selected.
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663336 is a reply to message #663322] Tue, 30 May 2017 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65196
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Something puzzles me.
Post the result of:
set pagesise 1000
select count(*) from dba_tables where owner='MASTER' and cluster_ name is not null;
select username, default_tablespace, temporary_tablespacy from dba_users order by 1;
Also retry the export adding the option
statistics=none compress=n

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663339 is a reply to message #663336] Tue, 30 May 2017 14:14 Go to previous messageGo to previous message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Hi Michel,

The result of the amended export is below and the output of the tablespaces list is attached.
N:\>exp 'sys as sysdba' file=E:\BACKUP\ORI-1\master.dmp LOG=E:\BACKUP\ORI-1\master.log owner=master statistics=none compress=n

Export: Release 11.2.0.1.0 - Production on Tue May 30 21:07:43 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
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MASTER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MASTER
About to export MASTER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'N:\ORADATA\DEV\SYSAUX01.DBF'
EXP-00000: Export terminated unsuccessfully
N:\>
  • Attachment: console.log
    (Size: 3.88KB, Downloaded 38 times)
Previous Topic: How to restore these changes to another cluster
Next Topic: Media Management Library for rman backup to tape
Goto Forum:
  


Current Time: Mon Nov 20 07:50:29 CST 2017

Total time taken to generate the page: 0.23112 seconds