Refreshable PDBs and the Archivelog Deletion Policy

2020-05-13 Off By Markus Flechtner

Refreshable PDBs are a cool thing within the Oracle Multitenant Architecture. You can create a copy of your large production database once and later you can „refresh“ it by applying the transactions (redologs) from the source database. But keep in mind that your archivelog deletion policy may kick in and can destroy your refresh mechanism.

Let‘s do a short a test:

There‘s a PDB called „PDB01“ in my source CDB:

SQL> show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 5 SAMPLEPDB			  READ WRITE NO
	 7 PDB01			  READ WRITE NO

On my target CDB I create a refreshable PDB:

SQL> create database link CDB1.markusdba.local
  2  connect to system identified by manager using 'CDB1.markusdba.local';
Database link created.

SQL> CREATE PLUGGABLE DATABASE PDB04 FROM PDB01@CDB1.markusdba.local
  2. file_name_convert=
  3. ('/u01/oradata/CDB1/PDB01','/u01/oradata/CDB2/PDB04')
  4  REFRESH MODE MANUAL;
Pluggable database created.

The refreshable PDB can be refreshed manually („REFRESH MODE MANUAL“) or at a given interval like „REFRESH MODE 240 MINUTES“. You can set the refresh mode to „NONE“ to disable the refresh. A refreshable PDB can be opened READ ONLY, but not READ WRITE and can serve as a source for reports or as source for more PDB copies, either snapshot copies of full copies.

Please keep in mind that the refreshable PDB must be closed for a refresh, and if the PDB is open when the system attempts a refresh (e.g. due to the refresh interval which was defined when creating the PDB), this refresh is skipped and the database tries another refresh after the next interval. This is important when open the refreshable PDB read only, either for reporting purposes or for creating snapshot PDBs based on the refreshable PDB.

Let‘s go back to the source CDB and let‘s do something there:

In PDB01:

SQL> create table system.t as select * from dba_objects;
Table created.

SQL> insert into system.t select * from system.t;
71635 rows created.

SQL> commit;
Commit complete.

Do some log switches in CDB$ROOT to create archivelogs:

SQL> alter system switch logfile;
System altered.
[...]

SQL> alter system switch logfile;
System altered.

So there has been some activity in the database and – as usual with the Oracle database – the transactions are stored in the redolog files and archivelogs.

Now my regular RMAN job on the source CDB starts and backs up my database and the archivelogs. The refreshable PDB hasn‘t been refreshed yet.

My RMAN configuration is very simple:

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/tmp/%U';
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> configure archivelog deletion policy to backed up 1 times to device type disk;

And in my testcase I simply back up the archivelogs:

RMAN> backup archivelog all delete all input;

Starting backup at 12-MAY-20
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=341 RECID=410 STAMP=1040251132
input archived log thread=1 sequence=342 RECID=411 STAMP=1040251486
[…]
input archived log thread=1 sequence=350 RECID=419 STAMP=1040251496
input archived log thread=1 sequence=351 RECID=420 STAMP=1040252074
channel ORA_DISK_1: starting piece 1 at 12-MAY-20
channel ORA_DISK_1: finished piece 1 at 12-MAY-20
piece handle=/tmp/i4v01v5a_1_1 tag=TAG20200512T225434 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/fast_recovery_area/TVDCDB1/archivelog/2020_05_12/o1_mf_1_341_hcp2bwnq_.arc RECID=410 STAMP=1040251132
archived log file name=/u02/fast_recovery_area/TVDCDB1/archivelog/2020_05_12/o1_mf_1_342_hcp2oyqd_.arc RECID=411 STAMP=1040251486
[...]
name=/u02/fast_recovery_area/TVDCDB1/archivelog/2020_05_12/o1_mf_1_351_hcp38b5n_.arc RECID=420 STAMP=1040252074
Finished backup at 12-MAY-20

Starting Control File and SPFILE Autobackup at 12-MAY-20
piece handle=/u02/backup/TVDCDB1/c-4251899961-20200512-04 comment=NONE
Finished Control File and SPFILE Autobackup at 12-MAY-20

As you can see, RMAN deletes all archivelogs.

Let‘s try to refresh the refreshable PDB04:

SQL> alter pluggable database pdb04 refresh;
alter pluggable database pdb04 refresh
*
ERROR at line 1:
ORA-65345: cannot refresh pluggable database

The explanation of the ORA-65345 is:

oracle@training19c:~/ [TVDCDB1] oerr ora 65345
65345, 00000, "cannot refresh pluggable database"
// *Cause:  The foreign archive log required for refreshing the pluggable
//          database was not found.
// *Action: Re-create the refresh pluggable database and ensure that archive
//          logging is enabled for the source multitenant container
//          database (CDB).

So your refresh mechanism is destroyed. And you have twp options:

Option 1 is to drop and recreate the refreshable PDB.

Option 2 is to restore the archivelogs of the source CDB from the RMAN backup and to re-run the refresh. 

Conclusion:

RMAN does not know about the refreshable PDB and removes archivelog files according to the archivelog deletion policy. There is no archivelog deletion policy similar to „APPLIED TO STANDBY ..“ which would prevent the deletion of the archivelogs (Hello Oracle, such a deletion policy when using refreshable PDBs could be helpful).

So you have to take care for keeping the archivelogs yourself. Instead of running commands like above „BACKUP ARCHIVELOG ALL DELETE ALL INPUT;“ I would this split this command into two parts and I would delete archivelogs which are at least older than twice the refresh interval of the refreshable PDB. 

But, as always, this time range depends. The subject becomes more complicated if you run the refreshable PDB in read only mode so that one or even more automatic refreshes may be skipped or if you are using manual refreshes „on demand“. In this case you have to calculate the time range yourself …

In any case, there is a dependency between both container databases which RMAN is not yet aware about. The DBA has to take care of it.

Update:

According to MOS-Note 2613419.1 the same problem (“ORA-65345”) can occur when creating a hot clone of a PDB.

Links & more information: