20 May, 2020

V$RECOVER_FILE and PDB$SEED and Standby Database

As a follow up to my previous post where I showed, with other things, that V$RECOVER_FILE may show PDB$SEED files as well, this is what I currently see on my Production (Primary) database :

SQL> l
  1  select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
  2  from v$pdbs p, v$recover_file r, v$datafile f
  3  where p.con_id=r.con_id
  4  and r.con_id=f.con_id
  5  and r.file#=f.file#
  6* order by 1,2
SQL> /

PDBNAME           FILE# FILENAME                                            ONLINE_ ERROR    TIME
------------ ---------- --------------------------------------------------- ------- -------- ---------
PDB$SEED              5 /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf    ONLINE           04-MAY-19
PDB$SEED              6 /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf    ONLINE           04-MAY-19
PDB$SEED              8 /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf   ONLINE           04-MAY-19

SQL>


and on my Standby database

SQL> l
  1  select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
  2  from v$pdbs p, v$recover_file r, v$datafile f
  3  where p.con_id=r.con_id
  4  and r.con_id=f.con_id
  5  and r.file#=f.file#
  6* order by 1,2
SQL> /

PDBNAME           FILE# FILENAME                                            ONLINE_ ERROR    TIME
------------ ---------- --------------------------------------------------- ------- -------- ---------
PDB$SEED              5 /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf    ONLINE           04-MAY-19
PDB$SEED              6 /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf    ONLINE           04-MAY-19
PDB$SEED              8 /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf   ONLINE           04-MAY-19

SQL>


Now I go back to my Production (Primary) database and run these commands :

SQL> alter pluggable database pdb$seed open read write;
alter pluggable database pdb$seed open read write
*
ERROR at line 1:
ORA-65019: pluggable database PDB$SEED already open


SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read write;

Pluggable database altered.

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;

Pluggable database altered.

SQL>
SQL> l
  1  select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
  2  from v$pdbs p, v$recover_file r, v$datafile f
  3  where p.con_id=r.con_id
  4  and r.con_id=f.con_id
  5  and r.file#=f.file#
  6* order by 1,2
SQL> /

no rows selected

SQL>


So, switching the PDB$SEED to READ WRITE and back to READ ONLY clears the entry in V$RECOVER_FILE in the Production (Primary) database.

But on the Standby, I now see :

SQL> l
  1  select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
  2  from v$pdbs p, v$recover_file r, v$datafile f
  3  where p.con_id=r.con_id
  4  and r.con_id=f.con_id
  5  and r.file#=f.file#
  6* order by 1,2
SQL> /

PDBNAME           FILE# FILENAME                                            ONLINE_ ERROR    TIME
------------ ---------- --------------------------------------------------- ------- -------- ---------
PDB$SEED              5 /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf    ONLINE           20-MAY-20
PDB$SEED              6 /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf    ONLINE           20-MAY-20
PDB$SEED              8 /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf   ONLINE           20-MAY-20

SQL>


So, now the Standby knows that the PDB$SEED needs recovery from 20-May-20 onwards. 
Normally, I would not be opening the PDB$SEED database on the Standby OR even on the Production (Primary) database.


No comments: