Pluggable database datafiles not visible from root container when opened in restricted mode

I experienced today not usual or normal situation with pluggable databases and its information availability from root container.
While pluggable database is open in a restricted mode its datafiles won’t be visible from root container, although the database is in read/write mode for users with administrative privileges.

The scenario is following.

SQL> conn / as sysdba
Connected.
SQL>
SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> alter pluggable database pdb2 open restricted;

Pluggable database altered.

SQL>
SQL> select con_id, name from v$pdbs;

    CON_ID NAME
---------- ---------------
         2 PDB$SEED
         3 XDB1
         4 PDB2

SQL>
SQL>
SQL> select con_id, open_mode, name from v$pdbs where con_id = 4;

    CON_ID OPEN_MODE  NAME
---------- ---------- ---------------
         4 READ WRITE PDB2

SQL>
SQL> select count(*) from cdb_data_files where con_id = 4;

  COUNT(*)
----------
         0

But, datafiles are visible from the pluggable container it self.

SQL>
SQL> conn sys@pdb2 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> select count(*) from cdb_data_files where con_id = 4;

  COUNT(*)
----------
         2

SQL>

It’s a little bit strange for this to be normal behavior since the pluggable database is in open read/write mode.
By default if the pluggable database is not open, the datafiles also are not visible from root container.

SQL>
SQL> alter pluggable database pdb2 close;

Pluggable database altered.

SQL>
SQL>
SQL> select count(*) from cdb_data_files where con_id = 4;

  COUNT(*)
----------
         0

SQL>

If we open the pluggable database in normal mode, datafiles are visible from root container as expected.

SQL>
SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL>
SQL> select count(*) from cdb_data_files where con_id = 4;

  COUNT(*)
----------
         2

SQL>

Conclusion

Maybe this should be normal behavior, but my opinion is that we should be able to see datafiles information from root container, since all pluggable databases are attached to it and opened.
Be careful how do you login when your pluggable databases are in restricted mode (for maintenance or for some other reasons), because you can miss some important information or end up confused why you can’t see some information for the database even though you are logged to the root container.

 

Update:

Added additional information that I was logged as sysdba to cdb$root when queried cdb_data_files from root container.

 

Regards,
Ivica

Leave a Reply