It's been a year since I penned down an article on my blog due to work commitments and personal commitments. Today I'll be discussing about an RMAN issue that I faced recently.
We noticed that the backups were not successful for at least a week since we performed the quarterly patching on a critical database. The first thing that came to mind is that the cron that we disabled might have been left in the same state after patching but that wasn't the case as the cron was enabled when checked. Now, the next action is to verify whether the cron has kicked off and by verifying the /var/log/cron we see that the cron is being kicked off every day as usual.
So now upon checking the backup log we noticed that the backups are failing as RMAN was not able to connect to the database with errors as follows. This is taken from the customized log the script creates..
We noticed that the backups were not successful for at least a week since we performed the quarterly patching on a critical database. The first thing that came to mind is that the cron that we disabled might have been left in the same state after patching but that wasn't the case as the cron was enabled when checked. Now, the next action is to verify whether the cron has kicked off and by verifying the /var/log/cron we see that the cron is being kicked off every day as usual.
So now upon checking the backup log we noticed that the backups are failing as RMAN was not able to connect to the database with errors as follows. This is taken from the customized log the script creates..
.. .. .. DB ABC can go incremental 1 Running online backup for ABC via native rman with command export NB_ORA_SCHED="full"; export NB_ORA_SAP=""; export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI"; export ORACLE_SID=ABC; export ORAENV_ASK=NO; . oraenv && echo "connect target /; spool log to '/opt/xxxx/dba/logs/backup_ABC-08-07.log' append ; backup incremental level 1 database; backup current controlfile;"| rman The Oracle base remains unchanged with value /oracle/ABC Recovery Manager: Release 12.2.0.1.0 - Production on Wed Aug 7 17:00:03 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. RMAN> RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== ORA-04068: existing state of packages has been discarded ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_APPLICATION_INFO" ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_APPLICATION_INFO" ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_APPLICATION_INFO" ORA-06508: PL/SQL: could not find pro RMAN-04015: error setting target database character set to UTF8 RMAN> Got 256 from DB ABC online backup Backup failed with rc 256 on DB ABC: RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 08/07/2019 17:00:03 RMAN-06171: not connected to target database Starting backup at 07-AUG-2019 17:00 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 08/07/2019 17:00:03 RMAN-06171: not connected to target database RMAN> Recovery Manager complete. .. .. ..So now the focus turned on towards the object DBMS_APPLICATION_INFO. I thought that the object could be in invalid status after the patching activity but there were in fact no objects in invalid status in the database.
sys@ABC-> select count(*) from dba_objects where owner='SYS' and status='INVALID';
COUNT(*)
----------
0
After a few google, which couldn't fetch me any useful results for the issue and with some digging in Oracle support, found this interesting article Doc ID 456338.1 where it is noted that there is a bug which reports ORA-04068 for objects with valid status.To verify this, as per the note I checked for timestamp discrepancies for any object in the database which resulted in the below.
sys@ABC-> set pagesize 10000
column d_name format a22
column p_name format a22
select do.obj# d_obj,do.name d_name, do.type# d_type,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;
D_OBJ D_NAME D_TYPE P_OBJ P_NAME P_Timestamp STIME X
---------- ---------------------- ---------- ---------- ---------------------- -------------------------- -------------------------- --------
11500 DBMS_APPLICATION_INFO 5 11499 DBMS_APPLICATION_INFO 24-SEP-2016 05:11:55 04-MAY-2019 21:35:10 *DIFFER*
sys@ABC->
So now, we can confirm we have affected by a bug which should have been fixed long back as the document says it applies to DB version 10.2 where as our environment is running DB version 12.2 :)Fixing the issue:
There are 2 solution for this issue. Apply a patch obviously as we have hit a bug, but we are way ahead on our DB version and so I proceeded with the work around.
Work around is to recompile the parent view.
The above results shows Parent object as 11499 which is the package itself and dependent object as 11500 which is a synonym. Here recompiling the package didn't help and we need to find the parent of the package again.
So in order to find the parent view or object, we can take the help of deptree. Refer support Doc ID 115767.1 to know more.
sys@ABC-> exec deptree_fill('package','SYS','DBMS_APPLICATION_INFO');
PL/SQL procedure successfully completed.
sys@ABC-> select * from ideptree;
DEPENDENCIES
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYNONYM PUBLIC.DBMS_APPLICATION_INFO
CURSOR <shared>."begin sys.dbms_application_info.set_client_info(:A0); end; "
PACKAGE BODY SYS.KUPW$WORKER
PACKAGE BODY GSMADMIN_INTERNAL.DBMS_GSM_CLOUDADMIN
PACKAGE BODY SYS.DBMS_APPLICATION_INFO
PACKAGE BODY GSMADMIN_INTERNAL.DBMS_GSM_UTILITY
PACKAGE BODY SYS.DBMS_STATS
CURSOR <shared>."begin sys.dbms_backup_restore.updateRmanStatusRow( row_id => :row_id, row_stamp => :row_stamp, status => :status); end;"
CURSOR <shared>."begin gsmadmin_internal.dbms_gsm_cloudadmin.syncParameters; end;"
CURSOR <shared>."call dbms_stats.gather_database_stats_job_proc ( )"
PACKAGE BODY SYS.KUPM$MCP
TRIGGER GSMADMIN_INTERNAL.GSMLOGOFF
PACKAGE SYS.DBMS_APPLICATION_INFO
PACKAGE BODY SYS.DBMS_BACKUP_RESTORE
CURSOR <shared>."begin sys.dbms_application_info.set_client_info(:A0); end; "
15 rows selected.
sys@ABC->
Or simply you can start an RMAN session to connect which again would give you the parent object as below[oracle@xxxx ~]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Thu Aug 8 08:45:40 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-04005: error from target database: ORA-04068: existing state of packages has been discarded ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_APPLICATION_INFO" ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_APPLICATION_INFO" ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 111 ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 191 ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 5093 ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_APPLICATION_INFO" ORA-06508: PL/SQL: could not find pro RMAN-04015: error setting target database character set to UTF8We got the parent object from both the methods - DBMS_BACKUP_RESTORE. If you see the status of the object this shows as valid.
sys@ABC-> Select object_name,object_type,owner,status from dba_objects where object_name like '%DBMS_BACKUP%' ; OBJECT_NAME OBJECT_TYPE OWNER STATUS ------------------------------ ----------------------- ------------------------------ ------- DBMS_BACKUP_RESTORE PACKAGE SYS VALID DBMS_BACKUP_RESTORE PACKAGE BODY SYS VALID sys@ABC->Solution:
Recompile both parent and child object as per document and retest RMAN
sys@ABC-> alter package sys.DBMS_BACKUP_RESTORE compile; Package altered. sys@ABC-> alter package DBMS_APPLICATION_INFO compile; Package altered. sys@ABC-> !rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Thu Aug 8 08:51:15 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ABC (DBID=2453771691) RMAN> exit Recovery Manager complete.Now you can see that the RMAN session is connecting to the target database without any issues.
Happy troubleshooting!!
