Home » RDBMS Server » Server Administration » Export error: ORA-31626, ORA-31637, ORA-06512, ORA-39000, and ORA-04031
| Export error: ORA-31626, ORA-31637, ORA-06512, ORA-39000, and ORA-04031 [message #177051] |
Mon, 12 June 2006 15:51  |
sandra22
Messages: 5
Registered: June 2006
Location: VA
|
Junior Member |
 
|
|
Hi All,
I am a PL/SQL programmer, however since I am the only one in this company doing Oracle type of work I need to do the some DBA work too. I have very little DBA experiences in the past such as creating tables, indexes, etc. with Oracle 8i. The current system I am working on is Oracle 10g with Solaris 9. I read oracle 10g documentation on backup and recovery. Three methods of back up suggested, RMAN, exp/imp utilities, expdp/impdp utilities. I am trying to do a backup of the objects (a full database backup) on this system. I used the exp/imp utilities and the expdp/impdp methods but I encountered errors with both methods. Therefore any helps or suggestions to fix this problem(s) I would appreciate it. Please see following error messages below:
METHOD 1
/* Exp.sh unix script content is below: */
#!/bin/ksh -
test_path=/u01/app/oracle/back_up
EXPORT test_path
exp Scott/password full=y direct=y file=$test_path/full_exp.dmp log=$test_path/full_exp.log
/* executing script */
TESTING: exp.sh &
/* Result of execution */
Export: Release 10.2.0.1.0 - Production on Mon Jun 12 15:43:42 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses US7ASCII character set (possible charset conversion)
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
EXP-00008: ORACLE error 980 encountered
ORA-00980: synonym translation is no longer valid
EXP-00000: Export terminated unsuccessfully
==========================================================================================
Method 2
CREATE directory test_dir as '/u01/app/oracle/back_up';
grant read, write on directory test_dir to Scott;
Unix script: exp.sh
#!/bin/ksh -
expdp scott/password directory=test_dir dumpfile=full_exp.dmp logfile=full_exp.log
Export: Release 10.2.0.1.0 - 64bit Production on Monday, 12 June, 2006 15:57:39
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user NUSER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20060612155739" and "KUPC$S_1_20060
612155739" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown
object","streams pool","fixed allocation callback")
Cheers!
|
|
|
|
| Re: Export error: ORA-31626, ORA-31637, ORA-06512, ORA-39000, and ORA-04031 [message #177092 is a reply to message #177051] |
Tue, 13 June 2006 00:47  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
not sure of exact cause of ur issues, but as a suggestion, check for invalid objects in your system..if there are invalid packages etc, you can try to recompile them by utlrp.sql which is located in i guess < oracle_home>/rdbms/admin folder.
also check the size of your sga and try to increase the sga size by changing the initialization parameters like db_block_buffers etc.
|
|
|
|
| Re: Export error: ORA-31626, ORA-31637, ORA-06512, ORA-39000, and ORA-04031 [message #177094 is a reply to message #177051] |
Tue, 13 June 2006 00:56 |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
METHOD 1: Execute this SQL to see what synonyms aren't valid:
SELECT Decode(s.owner,
'PUBLIC',
'PUBLIC SYNONYM ',
'SYNONYM' || s.owner || '.') || s.synonym_name || ';'
FROM dba_synonyms s
WHERE db_link IS NULL
AND NOT EXISTS (SELECT 1
FROM dba_objects o
WHERE s.table_owner = o.owner
AND s.table_name = o.object_name)
METHOD 2: Increase your STEAMS_POOL_SIZE parameter and restart the instance.
|
|
|
|
|
|
| Re: Export error: ORA-31626, ORA-31637, ORA-06512, ORA-39000, and ORA-04031 [message #177223 is a reply to message #177092] |
Tue, 13 June 2006 09:00 |
sandra22
Messages: 5
Registered: June 2006
Location: VA
|
Junior Member |
|
|
Hi Niravshah,
Thank you for the suggestions. I have ran the utlrp.sql see below for result. The procedure seems to executed successfully, therefore I am assuming it would fix all invalid objects right? After executing this I still got the same error mesages when I ran the exp.sh scripts. What should I do next? Also, I found the curent db_block_buffers setting equal to 8192. Is this too small? What is the normal setting or what should I need to set to? Do I use the vi editor to open the INIT.ORA file and do the modification that way or through SQL/PLUS? Please advices.
Thanks again!
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2006-06-13 08:28:32
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2006-06-13 08:29:14
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
508
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
|
|
|
|
|
|
|
|
| Re: Export error: ORA-31626, ORA-31637, ORA-06512, ORA-39000, and ORA-04031 [message #177239 is a reply to message #177225] |
Tue, 13 June 2006 10:43 |
sandra22
Messages: 5
Registered: June 2006
Location: VA
|
Junior Member |
|
|
Hi niravshah,
Thank you again! I have read the Oracle documentation you have provide me with the link and followed the directions there. It seems like the user Scott has all role and privileges as required to do a full DB export and import right? What am I missing? Why are these objects invalid? How can I fix it? (Please see below.) What I should do next? Please advice. Thank you very much!
/* Below is result of your query */
SQL> SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS where status='INVALID'
group 2 by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
PROCEDURE 5
VIEW 240
FUNCTION 9
TRIGGER 5
SYNONYM 187
PACKAGE BODY 60
PACKAGE 2
7 rows selected.
SQL> show sga
Total System Global Area 1610612736 bytes
Fixed Size 1979008 bytes
Variable Size 452988288 bytes
Database Buffers 1140850688 bytes
Redo Buffers 14794752 bytes
SQL> SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';
SUM(BYTES)
----------
415563776
/* Scott role and privileges below */
SQL> @user_role_privileges.sql
Enter value for enter_username: Scott
old 12: username like upper('%&enter_username%')
new 12: username like upper('%nuser%')
User, his roles and privileges
--------------------------------------------------------------------------------
SCOTT
CONNECT
CREATE SESSION
CREATE SESSION
EXP_FULL_DATABASE
ADMINISTER RESOURCE MANAGER
BACKUP ANY TABLE
EXECUTE ANY PROCEDURE
EXECUTE ANY TYPE
EXECUTE_CATALOG_ROLE
HS_ADMIN_ROLE
User, his roles and privileges
--------------------------------------------------------------------------------
READ ANY FILE GROUP
RESUMABLE
SELECT ANY SEQUENCE
SELECT ANY TABLE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
GATHER_SYSTEM_STATISTICS
IMP_FULL_DATABASE
ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER
ALTER ANY PROCEDURE
User, his roles and privileges
--------------------------------------------------------------------------------
ALTER ANY TABLE
ALTER ANY TRIGGER
ALTER ANY TYPE
ANALYZE ANY
AUDIT ANY
BECOME USER
COMMENT ANY TABLE
CREATE ANY CLUSTER
CREATE ANY CONTEXT
CREATE ANY DIMENSION
CREATE ANY DIRECTORY
User, his roles and privileges
--------------------------------------------------------------------------------
CREATE ANY INDEX
CREATE ANY INDEXTYPE
CREATE ANY LIBRARY
CREATE ANY MATERIALIZED VIEW
CREATE ANY OPERATOR
CREATE ANY PROCEDURE
CREATE ANY SEQUENCE
CREATE ANY SQL PROFILE
CREATE ANY SYNONYM
CREATE ANY TABLE
CREATE ANY TRIGGER
User, his roles and privileges
--------------------------------------------------------------------------------
CREATE ANY TYPE
CREATE ANY VIEW
CREATE DATABASE LINK
CREATE PROFILE
CREATE PUBLIC DATABASE LINK
CREATE PUBLIC SYNONYM
CREATE ROLE
CREATE ROLLBACK SEGMENT
CREATE TABLESPACE
CREATE USER
DROP ANY CLUSTER
User, his roles and privileges
--------------------------------------------------------------------------------
DROP ANY CONTEXT
DROP ANY DIMENSION
DROP ANY DIRECTORY
DROP ANY INDEX
DROP ANY INDEXTYPE
DROP ANY LIBRARY
DROP ANY MATERIALIZED VIEW
DROP ANY OPERATOR
DROP ANY OUTLINE
DROP ANY PROCEDURE
DROP ANY ROLE
User, his roles and privileges
--------------------------------------------------------------------------------
DROP ANY SEQUENCE
DROP ANY SQL PROFILE
DROP ANY SYNONYM
DROP ANY TABLE
DROP ANY TRIGGER
DROP ANY TYPE
DROP ANY VIEW
DROP PROFILE
DROP PUBLIC DATABASE LINK
DROP PUBLIC SYNONYM
DROP ROLLBACK SEGMENT
User, his roles and privileges
--------------------------------------------------------------------------------
DROP TABLESPACE
DROP USER
EXECUTE ANY PROCEDURE
EXECUTE ANY TYPE
EXECUTE_CATALOG_ROLE
HS_ADMIN_ROLE
GLOBAL QUERY REWRITE
INSERT ANY TABLE
MANAGE ANY QUEUE
RESUMABLE
SELECT ANY TABLE
User, his roles and privileges
--------------------------------------------------------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
UPDATE ANY TABLE
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
User, his roles and privileges
--------------------------------------------------------------------------------
CREATE TYPE
SELECT ANY DICTIONARY
SELECT ANY SEQUENCE
SELECT ANY TABLE
SELECT ANY TRANSACTION
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
UNLIMITED TABLESPACE
107 rows selected.
SQL> spool off
/* UNIX disk spaces */
TESTING: df -k
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t0d0s0 8012813 2372852 5559833 30% /
/proc 0 0 0 0% /proc
mnttab 0 0 0 0% /etc/mnttab
fd 0 0 0 0% /dev/fd
swap 6201112 104 6201008 1% /var/run
swap 6201360 352 6201008 1% /tmp
/dev/dsk/c0t0d0s4 30257446 960599 28994273 4% /util
/dev/md/dsk/d5 350101890 5007579 341593293 2% /u02
/dev/dsk/c0t1d0s4 70587417 29012537 40869006 42% /u01
/dev/dsk/c0t0d0s7 30257446 36413 29918459 1% /export/home
|
|
|
|
| Re: Export error: ORA-31626, ORA-31637, ORA-06512, ORA-39000, and ORA-04031 [message #177243 is a reply to message #177239] |
Tue, 13 June 2006 11:10 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Sandra,
Yes I see your point..Scott has all the previleges for the entire db. so that is no-issue. I guess this may be something I am not aware of -my limitation. One suggestion would be to try and fix the invalid objects..I should have posted following query instead of earlier one:
SELECT owner,OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS where status='INVALID'
group by owner,object_type
/
Pl. run it and see the invalid objects for accounts like sys, system etc. ideally sys , system etc should not have invalid objects...if they are there we need to fix that.
also there is the problem of invalid synonyms..I guess that is what is causing the issues.
perhaps other memebers can jump in and suggest..
thanks
also,
from what you have posted, the sga and disk free space is not issue. we just need to zero in to the invalid objects.
[Updated on: Tue, 13 June 2006 11:11] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 12:34:27 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
