Home » RDBMS Server » Backup & Recovery » Oracle SYSAUX UNDO tablespaces corrupt (Oracle 11)
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663366 is a reply to message #663339] Wed, 31 May 2017 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't see why exp needs to read SYSAUX tablespace!
Can you try the following PL/SQL block and post MASTER.LOG file (it is asynchronous, you may have to wait a little bit to get the file content):
Declare
  h1 number;
Begin
  h1 := dbms_datapump.open (operation => 'EXPORT',
                            job_mode  => 'SCHEMA');

  dbms_datapump.add_file (handle    => h1,
                          filename  => 'MASTER',
                          directory => 'ULVI_EXPORT_DIR',
                          filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

  dbms_datapump.add_file (handle    => h1,
                          filename  => 'MASTER.LOG',
                          directory => 'ULVI_EXPORT_DIR',
                          filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter (handle => h1,
                                 name   => 'SCHEMA_LIST',
                                 value  => 'MASTER');

  dbms_datapump.start_job (handle => h1);
  dbms_datapump.detach (handle => h1);
End;
/
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663369 is a reply to message #663366] Wed, 31 May 2017 13:20 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
I re-logined as sys as the below error was raised; I am guessing this has nothing to do with the "Declare" keyword ..
SQL> connect sys as sysdba;
Enter password:
Connected.
SQL> Declare
  2    h1 number;
  3  Begin
  4    h1 := dbms_datapump.open (operation => 'EXPORT',
  5                              job_mode  => 'SCHEMA');
  6
  7    dbms_datapump.add_file (handle    => h1,
  8                            filename  => 'MASTER',
  9                            directory => 'ULVI_EXPORT_DIR',
 10                            filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 11
 12    dbms_datapump.add_file (handle    => h1,
 13                            filename  => 'MASTER.LOG',
 14                            directory => 'ULVI_EXPORT_DIR',
 15                            filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 16
 17    dbms_datapump.metadata_filter (handle => h1,
 18                                   name   => 'SCHEMA_LIST',
 19                                   value  => 'MASTER');
 20
 21    dbms_datapump.start_job (handle => h1);
 22    dbms_datapump.detach (handle => h1);
 23  End;
 24  /
Declare
*
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5196
ORA-06512: at line 4
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663387 is a reply to message #663369] Thu, 01 June 2017 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, it comes from the OPEN call (line 4, last line of the error stack).
You should have a trace file at the time you executed the PL/SQL block which will give more information about the error, please post it but I think I know the reason.

Please post the result of:
select table_name, tablespace_name 
from dba_tables 
where owner = 'SYS' and table_name like 'KUPC$DATAPUMP_QUETAB%'
/
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663423 is a reply to message #663387] Fri, 02 June 2017 12:37 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Sure :
SQL> select table_name, tablespace_name
  2  from dba_tables
  3  where owner = 'SYS' and table_name like 'KUPC$DATAPUMP_QUETAB%'
  4  /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
KUPC$DATAPUMP_QUETAB_1         SYSTEM
KUPC$DATAPUMP_QUETAB           SYSTEM
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663426 is a reply to message #663423] Fri, 02 June 2017 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My hypothesis was wrong, your queue table is not in SYSAUX tablespace, so need more information.
Did you find the trace file? If yes can you post it.

[Updated on: Fri, 02 June 2017 13:14]

Report message to a moderator

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663428 is a reply to message #663426] Fri, 02 June 2017 13:29 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
I am a bit lost there (not the first time on this one Smile). Are we referring to any of the below or something else ?
SQL> SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

1	PSEUDO	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_ora_0.trc
2	ORACLE.EXE (PMON)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_pmon_13140.trc
3	ORACLE.EXE (VKTM)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_vktm_17080.trc
4	ORACLE.EXE (GEN0)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_gen0_1512.trc
5	ORACLE.EXE (DIAG)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_diag_15640.trc
6	ORACLE.EXE (DBRM)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_dbrm_11872.trc
7	ORACLE.EXE (PSP0)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_psp0_3864.trc
8	ORACLE.EXE (DIA0)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_dia0_7780.trc
9	ORACLE.EXE (MMAN)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_mman_8176.trc
10	ORACLE.EXE (DBW0)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_dbw0_9084.trc
11	ORACLE.EXE (DBW1)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_dbw1_15556.trc
12	ORACLE.EXE (LGWR)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_lgwr_5760.trc
13	ORACLE.EXE (CKPT)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_ckpt_12316.trc
14	ORACLE.EXE (SMON)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_smon_13784.trc
15	ORACLE.EXE (RECO)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_reco_4856.trc
16	ORACLE.EXE (MMON)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_mmon_12968.trc
17	ORACLE.EXE (MMNL)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_mmnl_500.trc
18	ORACLE.EXE (D000)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_d000_16108.trc
19	ORACLE.EXE (S000)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_s000_2904.trc
20	ORACLE.EXE (SMCO)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_smco_3908.trc
21	ORACLE.EXE (QMNC)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_qmnc_14064.trc
22	ORACLE.EXE (W000)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_w000_16396.trc
23	ORACLE.EXE (CJQ0)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_cjq0_7624.trc
24	ORACLE.EXE (SHAD)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_ora_9040.trc
25	ORACLE.EXE (SHAD)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_ora_4360.trc
26	ORACLE.EXE (Q001)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_q001_12724.trc
27	ORACLE.EXE (Q002)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_q002_15208.trc
34	ORACLE.EXE (Q000)	n:\oracle_base\diag\rdbms\dev\dev\trace\dev_q000_9484.trc


Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663430 is a reply to message #663428] Fri, 02 June 2017 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, the one of the session you had when you executed the PL/SQL block.
It was one named dev_ora_XXXX.trc

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663431 is a reply to message #663430] Fri, 02 June 2017 13:43 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
I just ran the "exp" command again which showed on the console :

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 Fri Jun 2 20:40:17 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:\>

and the trace file ( dev_ora_16556.trc ) reads :
Trace file n:\oracle_base\diag\rdbms\dev\dev\trace\dev_ora_16556.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.2  
CPU                 : 16 - type 8664, 8 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:18074M/32757M, Ph+PgF:22666M/37621M 
Instance name: dev
Redo thread mounted by this instance: 1
Oracle process number: 29
Windows thread id: 16556, image: ORACLE.EXE (SHAD)


*** 2017-06-02 20:40:24.113
*** SESSION ID:(210.2071) 2017-06-02 20:40:24.113
*** CLIENT ID:() 2017-06-02 20:40:24.113
*** SERVICE NAME:(SYS$USERS) 2017-06-02 20:40:24.113
*** MODULE NAME:(exp.exe) 2017-06-02 20:40:24.113
*** ACTION NAME:() 2017-06-02 20:40:24.113
 
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
DDE Action 'DB_STRUCTURE_INTEGRITY_CHECK' was flood controlled
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (FLOOD CONTROLLED, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663432 is a reply to message #663431] Fri, 02 June 2017 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry, my fault, I should specify this is the execution of the PL/SQL block I posted at http://www.orafaq.com/forum/mv/msg/203399/663369/#msg_663369.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663434 is a reply to message #663432] Fri, 02 June 2017 14:02 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
No problems Wink

Please find the trace and incident directory contents attached.

Please beware I changed the extension of the file zip file to a *.txt, you just need to rename and unzip it.

Thanks a lot
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663435 is a reply to message #663434] Fri, 02 June 2017 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you start a new SYS session, execute the PL/SQL block, disconnect and post the trace file; in the one you posted it is hard to say which part is about which command.

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663436 is a reply to message #663435] Fri, 02 June 2017 14:17 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
attached ..
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663438 is a reply to message #663436] Fri, 02 June 2017 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you do the same thing once again but with a small change:
Declare
  h1 number;
Begin
  SYS.KUPV$FT_INT.V_DEBUG := TRUE;
  h1 := dbms_datapump.open (operation => 'EXPORT',
                            job_mode  => 'SCHEMA');

  dbms_datapump.add_file (handle    => h1,
                          filename  => 'MASTER',
                          directory => 'ULVI_EXPORT_DIR',
                          filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

  dbms_datapump.add_file (handle    => h1,
                          filename  => 'MASTER.LOG',
                          directory => 'ULVI_EXPORT_DIR',
                          filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter (handle => h1,
                                 name   => 'SCHEMA_LIST',
                                 value  => 'MASTER');

  dbms_datapump.start_job (handle => h1);
  dbms_datapump.detach (handle => h1);
End;
/
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663443 is a reply to message #663438] Fri, 02 June 2017 14:53 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Console below and traces attached ..

SQL> connect sys as sysdba;
Enter password:
Connected.
SQL> Declare
  2    h1 number;
  3  Begin
  4    SYS.KUPV$FT_INT.V_DEBUG := TRUE;
  5    h1 := dbms_datapump.open (operation => 'EXPORT',
  6                              job_mode  => 'SCHEMA');
  7
  8    dbms_datapump.add_file (handle    => h1,
  9                            filename  => 'MASTER',
 10                            directory => 'ULVI_EXPORT_DIR',
 11                            filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 12
 13    dbms_datapump.add_file (handle    => h1,
 14                            filename  => 'MASTER.LOG',
 15                            directory => 'ULVI_EXPORT_DIR',
 16                            filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 17
 18    dbms_datapump.metadata_filter (handle => h1,
 19                                   name   => 'SCHEMA_LIST',
 20                                   value  => 'MASTER');
 21
 22    dbms_datapump.start_job (handle => h1);
 23    dbms_datapump.detach (handle => h1);
 24  End;
 25  /
Declare
*
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5196
ORA-06512: at line 5
SQL>

[Updated on: Fri, 02 June 2017 14:53]

Report message to a moderator

Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663480 is a reply to message #663438] Tue, 06 June 2017 03:29 Go to previous messageGo to next message
ulvi_ugur
Messages: 28
Registered: May 2017
Junior Member
Hi Michel,

I am guessing that you are travelling. Just to let you know I am on standby until your return. Please let me know when you have time again.

Cheers,

Ulvi
Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663485 is a reply to message #663480] Tue, 06 June 2017 04:01 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It would be interesting to investigate the problem with Data Pump but this will take painful days. I could do it if I had the database at my place as I could put some traces or patch some code but this is not possible as it.
You could try a last thing and if it does not work (and I think it won't) we will do in another way.
Add the following parameters to your init.ora file, restart and retry the PL/SQL block:
_disable_health_check=TRUE
_disable_storage_type=TRUE
_enable_check_truncate=FALSE

Previous Topic: Media Management Library for rman backup to tape
Next Topic: Auto Backup ArchiveLogs
Goto Forum:
  


Current Time: Thu Mar 28 18:04:12 CDT 2024