Month: May 2015

Oracle 12c database password file

arcsdegeoLeave a comment

one of the new features in 12c is having the passwords in the password file being by default case sensitive. also, you can configure password file for the new system account users (SYSDG—-> for data guard, SYSBACKUP —-> for backup operations and administration)

Example of password file command:

orapwd file=EMO_DB_PASS entries=10 sysbackup=y sysdg=y

*** the  EMO_DB file (password file) will be created in the directory where you are currently in.

password file command

as shown in the above picture passwords are required to be entered for SYS,SYSBACKUP,SYSDG accounts.

the following command will be executed to access the database as SYSDG user for verification:

sqlplus SYSDG/cocoChannel7 as SYSDG

sqlplus example

Oracle Export Errors ORA-31693 ORA-29913 ORA-21700

arcsdegeoLeave a comment

While checking the export of status of one of my supported databases (11gR2), i found out the log file has the below errors:

ORA-31693: Table data object “SYSTEM”.”SCHEDULER_PROGRAM_ARGS” failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-21700: object does not exist or is marked for delete

To Resolve this error:

the error shows that you have dropped an object that has dependencies, so you need to re-create it and then drop it.

or directly drop the scheduler objects as shown below.

connect / as sysdba
exec dbms_scheduler.drop_program(‘JDM_BUILD_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_EXPLAIN_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_EXPORT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_IMPORT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_PREDICT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_PROFILE_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_SQL_APPLY_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_TEST_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_XFORM_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_XFORM_SEQ_PROGRAM’);

impdp ORA-39097 ORA-39065 ORA-56935

arcsdegeo3 Comments

While performing an import, I faced the below error:

ORA-39097: Data Pump job encountered unexpected error
ORA-39065: unexpected master process exception in DISPATCH
ORA-56935: existing datapump jobs are using a different version of time zone data file

Solution:

Check the value of  DST_UPGRADE_STATE by the following query:

SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

Then try executing the following:

ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;
exec dbms_dst.unload_secondary;

Re-execute the query:

SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

****check the value of  DST_UPGRADE_STATE should be NONE