One of our Oracle DB backups were failing with error 6 which is a generic error for DB backups using Netbackup and from the error log i have found
ORA-12012: error on auto execute of job 661138
Errors
in file /OraBase/admin/Test50/bdump/Test50_j000_29761.trc:
ORA-12012: error on auto execute of job 661138
ORA-00257:
archiver error. Connect internal only, until freed.
Error:- ORA-00257: archiver error. Connect internal only, until freed.
Solution:-
Error:- ORA-00257: archiver error. Connect internal only, until freed.
Solution:-
And i see this is moreover Oracle Side to troubleshoot rather the Netbackup side and after a small research i have found the solution is to move some of the archive logs
The related table is Test50.Testing_Base.
After taking a look at the job log we see the failed execution attempts:
SQL> select log_date,status from dba_scheduler_job_run_details
where job_name='Test50_DBTesting_JOB';
LOG_DATE STATUS
------------------------------------- ------------------------------
11-FEB-14 10.00.07.3668 AM -06:00 FAILED
04-FEB-14 08.00.05.59665 AM -06:00 FAILED
2 rows selected.
Solution
Check the invalid values on the table and remove them.
SQL> select * from Test50.Testing_Base;
DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMPUT
---------- ---------------- ----------- -------------------------------- -- - ---------------- -----------
4056791929 orcl 0 24T6YH78JNSHDT89ONSNS09Q2NND8728 NW N ACTIVE 140917:2200
4052355478 ORCL3 0 12MDNSIdGAJKW474IETJBESGJH539930 ND Y ACTIVE 141022:2000
As we can see the above orcl is corrupted , Now we proceed to delete it.
The corrupt row can be removed as below.
SQL> DELETE FROM Test50.Testing_Base WHERE INSTANCE_NAME ='orcl';
1 row deleted.
SQL> commit;
Commit complete.
Manually re-execute the job and check the execution log, it must show the job executed successfully. It takes a couple of minutes after execution to show the results in the log table.
SQL> exec dbms_scheduler.run_job('Test50.Testing_Base',false);
PL/SQL procedure successfully completed.
The issue was fixed, validate it by querying
SQL> select log_date,status from dba_scheduler_job_run_details
where job_name='Test50.Testing_Base';
LOG_DATE STATUS
------------------------------------- ------------------------------
14-FEB-14 11.00.07.315077 AM -06:30 FAILED
15-FEB-14 11.00.05.595559 AM -06:30 FAILED
17-FEB-14 03.41.20.714453 Am -06:30 SUCCEEDED
--> find the location of Archive destination by
show parameter archive_dest
lets say it provide LOCATION=/Test50/oradata/mydb/arch
--> move some files to some other location using os command
cd /Test50/oradata/mydb/arch
mv /Test50/oradata/mydb/arch/* /Test5/oradata/mydb/arch-bkp/
If you want to do it from RMAN , try as below.
rman target /
RMAN> backup archive log all format '/Test50/oradata/mydb/arch-bkp';
RMAN> delete archive until time 'trunc(sysdate)';
Error:- ORA-12012: error on auto execute of job 7863452
Solution:-
The underlying table required as part of one of the schedule maintenance tasks has invalid values. The related table is Test50.Testing_Base.
After taking a look at the job log we see the failed execution attempts:
SQL> select log_date,status from dba_scheduler_job_run_details
where job_name='Test50_DBTesting_JOB';
LOG_DATE STATUS
------------------------------------- ------------------------------
11-FEB-14 10.00.07.3668 AM -06:00 FAILED
04-FEB-14 08.00.05.59665 AM -06:00 FAILED
2 rows selected.
Solution
Check the invalid values on the table and remove them.
SQL> select * from Test50.Testing_Base;
DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMPUT
---------- ---------------- ----------- -------------------------------- -- - ---------------- -----------
4056791929 orcl 0 24T6YH78JNSHDT89ONSNS09Q2NND8728 NW N ACTIVE 140917:2200
4052355478 ORCL3 0 12MDNSIdGAJKW474IETJBESGJH539930 ND Y ACTIVE 141022:2000
As we can see the above orcl is corrupted , Now we proceed to delete it.
The corrupt row can be removed as below.
SQL> DELETE FROM Test50.Testing_Base WHERE INSTANCE_NAME ='orcl';
1 row deleted.
SQL> commit;
Commit complete.
Manually re-execute the job and check the execution log, it must show the job executed successfully. It takes a couple of minutes after execution to show the results in the log table.
SQL> exec dbms_scheduler.run_job('Test50.Testing_Base',false);
PL/SQL procedure successfully completed.
The issue was fixed, validate it by querying
SQL> select log_date,status from dba_scheduler_job_run_details
where job_name='Test50.Testing_Base';
LOG_DATE STATUS
------------------------------------- ------------------------------
14-FEB-14 11.00.07.315077 AM -06:30 FAILED
15-FEB-14 11.00.05.595559 AM -06:30 FAILED
17-FEB-14 03.41.20.714453 Am -06:30 SUCCEEDED
Now try re-run the job it should be successful :)
