[Oracle Scheduled Backup Failed] Oracle Backup manually Error: ORA-31634ORA-31664

Automatic backup fails, manual execution of the script reports an error ORA-31634 ORA-31664

1. Problem discovery:
The daily timing data pump export job is not imported normally. Checking the import.log, it is actually empty.
2. Problem tracking:
Manually call the back.sh script to export, and an error will be reported after running for a few seconds:
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted
3. Find and solve
1) Analyze the error and how to solve it:

ORA-31634: job already exists
Cause: Job creation or restart failed because a job having the selected name is currently executing. This also generally indicates that a Master Table with that job name exists in the user schema. Refer to any following error messages for clarification.
Action: Select a different job name, or stop the currently executing job and re-try the operation (may require a DROP on the Master Table).
ORA-31664: unable to construct unique job name when defaulted
Cause: The job name was defaulted, and the name creation algorithm was unable to find a unique job name for this schema where the table name (for the master table) didn”t already exist.
Action: Specify a job name to use or delete some of the existing tables causing the name conflicts.
The analysis shows that the unique name of the job called does not exist when expdp is running. Query dba_datapump_jobs. Under normal circumstances, the job_name field only has two rows SYS_IMPORT_SCHEMA_01 and SYS_IMPORT_SCHEMA_02, and this table has 135 rows. The solution is to delete the table whose field state is NOT RUNNING in the table.

2) Solution
The SQL statement generated to clear the master table is:
select’drop table ‘|| owner_name ||’.’ || job_name ||’;’ from dba_datapump_jobs where state =’NOT RUNNING’
After manually executing the generated SQL statement, query dba_datapump_jobs again to confirm whether there is a master table whose state is NOT RUNNING, and repeat the generated SQL statement for execution.
Manually call the import script again and import normally.

Similar Posts: