Re: Oracle SYSAUX UNDO tablespaces corrupt [message #663366 is a reply to message #663339] |
Wed, 31 May 2017 11:34 |
|
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 |
|
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 #663431 is a reply to message #663430] |
Fri, 02 June 2017 13:43 |
|
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 #663438 is a reply to message #663436] |
Fri, 02 June 2017 14:25 |
|
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 |
|
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 #663485 is a reply to message #663480] |
Tue, 06 June 2017 04:01 |
|
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
|
|
|