Oracle 18c EE on Oracle Linux 6, hosting rman catalog.
I've never run across this before, because everything always 'just worked', so never had to dig in.
I am trying to create a procedure in my rman catalog datatabase, to report archival backups (those created with KEEP UNTIL) that are due to reach their KEEP date soon. I have a sql script that I run as the catalog owner, but decided I'd just make it a SP and job, so I could get out of the loop and just look at resulting email notification when it comes.
In all of my databases, I've created a user - LOCAL_DBA - to own and manage any housekeeping or other general tasks outside of the business application. So naturally I want to use that schema to own/run this new procedure. I was rather shocked when it returned no results and I knew for a fact that there was one backup that met the selection criteria. I even got down to testing just a simple SELECT against a catalog table, both as the catalog owner and any other user:
SQL> conn rman/***
Connected.
SQL> select count(*) from rman.rc_database;
COUNT(*)
----------
12
SQL> conn system/***
Connected.
SQL> select count(*) from rman.rc_database;
COUNT(*)
----------
0
A little more digging led me to the concept of the Virtual Private Catalog. https://docs.oracle.com/database/121/RCMRF/rcmsynta012.htm#RCMRF117
I could create my procedure in the catalog owner's schema, but that just doesn't feel right. Does anyone have any other suggestions? Perhaps granting the RECOVERY_CATALOG_OWNER_VPD role to my local_admin? Or is that something I really don't want to do but just don't know it due to my newness to this particular issue?