Cannot read from V$RMAN_BACKUP_JOB_DETAILS in XE 18c pluggable database

Norman Dunbar

I have a 64bit OracleXE 18c instance, created on a Windows box. I have created a user in the pluggable database, xepdb1, which has DBA role granted (I also tried adding PDB_DBA as well, but it still doesn't work). This user is one that has various functions and packages that can be called to do work that the calling schema doesn't have/need the privileges to do.

Anyway, one of the tasks this user carries out is checking that the RMAN backups have completed. I run a C application (written with OCILIB to do the database stuff) and it runs the following code to get various details, some in HTML format, about the backup(s) that have run in the previous 24 hours:

with taskList as (

    SELECT  tasks.session_recid,

    LISTAGG(

            case when tasks.object_type like 'DB%' and tasks.operation = 'BACKUP' then

                '<strong>' || tasks.operation || ' ' || tasks.object_type || '</strong>'

            else

                tasks.operation || ' ' || tasks.object_type

            end, '<br>')

                WITHIN GROUP (ORDER BY tasks.start_time) AS sub_jobs

    FROM    V$RMAN_STATUS tasks

    where   parent_recid is not null

    and     start_time >= trunc(sysdate) - 1

    GROUP   BY session_recid

)

------------------------------------------------------------------------------------------

-- Get a list of RMAN jobs that started in the last 24 hours.

------------------------------------------------------------------------------------------

select  jobs.session_key as job_id,

        to_char(jobs.start_time, 'dd/mm/yyyy') || '<br>' || to_char(jobs.start_time, 'hh24:mi') as start_time,

        to_char(jobs.end_time, 'dd/mm/yyyy') || '<br>' || to_char(jobs.end_time, 'hh24:mi') as end_time,

        jobs.time_taken_display as run_time,

        trim(jobs.input_bytes_display) as input_size,

        trim(jobs.output_bytes_display) written_size,

        jobs.status,

        taskList.sub_jobs as tasks_executed_in_job

from    V$RMAN_BACKUP_JOB_DETAILS jobs

join    taskList

    on  (jobs.session_key = taskList.session_recid)

where   jobs.start_time >= trunc(sysdate) - 1

order   by jobs.start_time desc;

The problem is, while my DBA user can read from V$RMAN_STATUS with no problems, it never sees anything in the V$RMAN_BACKUP_JOB_DETAILS, so my application creates a blank report which implies that there have been no backups (aka RMAN jobs) run in the last 24 hours. A Minor panic!

Investigating further, even SYS cannot see backups details from the view when connected in the pluggable database, while it can when connected in the root database.

I have RMAN backups taken as:

• The root database backing up the entire database,
• The root database backing up the pluggable database,
• The pluggable database backing up the pluggable database.

Nothing shows up when logged in to the pluggable database, only in the root can I see data in the view V$RMAN_BACKUP_JOB_DETAILS.

Does anyone have any clues?

Thanks.

Cheers,

Norm.

clcarter

Have to hit the CDB for backup info- rman will save bits in the controlfile and a PDB session won't see any of that.

clcarter

Have to hit the CDB for backup info- rman will save bits in the controlfile and a PDB session won't see any of that.

Norman Dunbar

Thanks. That's a bit of a bind then.

I'm using XE to practice with containers and pluggables, and so far, so flipping confusing!

Cheers,

Norm.

clcarter

Indeed- the PDBs can make things Much More Interesting

Norman Dunbar

For example:

I want to login as root, then move between pluggable databases, the command is:

alter session set CONTAINER = pluggable_database_name;

Go figure. Container? Sigh.

Cheers,

Norm.

Sven W.

You don't login as root.

I probably mean you are root on OS level and login into the database as sys without providing credentials.

You should stop that. Take some time to properly setup your database.

Some actions work differently when executed as sys. For example flashback query do not work as sys (has to do with how read consistency works differently for sys).
Create yourself a DBA account in the PDB and use that.

I usually store all connections (user+passwort+connectstring) in sqldeveloper. Then when I access the database I simple choose the connection I want (might be sys, but more frequently some DBA account).

Norman Dunbar

Morning Sven,

I think you have the wrong end of the stick here. This is XE on Windows - there is no "root" user on the OS. When I said "root" I meant the root database, the container if you like. The one known as "XE".

I was referring to that root, not root on a Linux/Unix server. And yes, I do have my own account etc. :-)

Cheers,

Norm.

User_BZQKM

So any update on this? It is still not possible to do today. In a shared environment, usually the PDB admin does not have access to the Container, so there is no way to quere V$RMAN_BACKUP_JOB_DETAILS...