What they don't tell you about refreshable PDB clones (ghost archives)

Intro

In my previous blog post, I showcased how to automate the refresh of a PDB clone using a DBMS scheduler job which gives a better control on the frequency, instead of relying on the minutely refresh. Handy if you don’t like keeping an automatic refresh every X minutes and rather have it run once a day at a specific time to avoid unexpected maintenance. But along the way I started to discover a strange behaviour on the source/production environment from where my refreshable PDB clone was being updated. Let’s find out what has actually happened after few refreshes.     

1. Refreshable Clone PDB environment

Refresh Scheduled nightly (4AM)

I am not going to describe the full configuration, since I already wrote about it in detail in my other blog post but will at least share the job defined to perform the daily refresh using dbms scheduler.

1- The refresh procedure

CREATE OR REPLACE PROCEDURE sys.psprod_refresh
AS BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE PSPDB_RO CLOSE IMMEDIATE';
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE PSPDB_RO REFRESH';
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE PSPDB_RO OPEN READ ONLY';
END;/

2- The refresh JOB

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'PSPROD_REFRESH_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'sys.psprod_refresh',
repeat_interval => 'freq=daily;byday=mon,tue,wed,thu,fri;BYHOUR=4; BYMINUTE=1',
auto_drop => FALSE,
enabled => TRUE,
comments => 'Automatic refresh of the prod PDB clone');
END;
/

SQL>@check_job PSPROD_REFRESH_JOB
JOB_NAME     REPEAT_INTERVAL      
------------------ --------------------------------------------------------
PSPROD_REFRESH_JOB freq=daily;byday=mon,tue,wed,thu,fri;BYHOUR=4; BYMINUTE 

Ghost archives in the source CDB

It took me few weeks before I realized that the archive log disk started to fill up a bit, even if the archive logs were cleaned up after each backup due to a defined retention (7 days) in RMAN.

What did I notice ?

I found archive logs that have different naming convention than the default one for the source CDB. It was not even listed by RMAN during a crosscheck.

• Normal archive naming

ARC0000001891_1075820594.0001

• Unusual archive logs

PARLOG_1_739_6B244BFA_1075820594.ARC

These new PARLOG% archive logs were just piling up, since the refreshes started. Although it’s a Windows environment, it doesn’t change the nature of the issue. 

• Where do these PARLOG archives come from? Something I haven’t seen in the multitenant documentation so far.

Root cause

It’s clear now that my PDB refreshes are behind the creation of new archives in the source CDB that will never be acknowledged by source RMAN and never used for any restore (see refresh log below).

Completed: ALTER PLUGGABLE DATABASE PSPDB_RO CLOSE IMMEDIATE
ALTER PLUGGABLE DATABASE PSPDB_RO REFRESH
03/26/2022 T13:48:18.297038-04:00
Applying media recovery for pdb-4099 from SCN 17631305104 to SCN 17635452014
Remote log information: count-5
thr-1,seq-742,logfile-F:\ORACLE\ARCH\ARC0000000742_1075820594.0001,
los-17633377023,nxs-17634469434,maxblks-3719661
…
thr-1,seq-743,logfile-F:\ORACLE\ARCH\PARLOG_1_743_6B244BFA_1075820594.ARC,<---
los-17634469434,nxs-18446744073709551615,maxblks-3427379

03/26/2022T13:52:25.929467-04:00
Completed: ALTER PLUGGABLE DATABASE PSPDB_RO REFRESH

How to delete these archive logs after the refresh?

Beside deleting these files, I wanted to also check if they can be catalogued after the crosscheck failed to list them.

• RMAN delete from the source CDB failed

RMAN> DELETE NOPROMPT ARCHIVELOG like '%PARLOG_1%';
channel ORA_DISK_1: SID=789 device type=DISK
specification does not match any archived log in the repository

• Catalog the archive log failed too because of a header validations error:

RMAN> CATALOG ARCHIVELOG ’F:\ORACLE\ARCH\PARLOG_1_2165_6B244BFA_1075820594.ARC’;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009:failure of catalog command on ORA_DISK_1 channel at 03/26/2022 16:51:
ORA-19563:Inspect Archived Log: Partial archived log created header validation 
failed for file F:\ORACLE\ARCH\PARLOG_1_2165_6B244BFA_1075820594.ARC

Solution : Automated cleanup every night

Since these files are only used by my PDB refresh once and can’t be catalogued by the source RMAN catalog, a good old OS cleanup was in order. For that purpose, I decided to run few PowerShell commands to create a job and schedule it to run every night at 10PM.  You’d probably need a bash script configured in cron if you were in Linux.

Content of the script: PDB_refresh_cleanup.ps1
remove-item F:\ORACLE\ARCH\PARLOG_*

-- Define the trigger, user, and action
$Trigger= New-ScheduledTaskTrigger -At 10:00pm –Daily 
$User= "NT AUTHORITY\SYSTEM" # Specify the account 
$Action= New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "-ExecutionPolicy Bypass -File J:\19c\PDB_refresh_cleanup.ps1" 

# Create and specify the name of the task
Register-ScheduledTask -TaskName "CleanupPDBarchives" -Trigger $Trigger -User $User -Action $Action -RunLevel Highest –Force 

• This job would run every night  cleanup the last PARLOG* archive created by  the PDB upon each refresh (4am).

Conclusion

This was just a new discovery on the multitenant container refresh mechanism that I didn’t expect to face. Hope this may help those who plan to configure a refreshable PDB clone as It took me months before I noticed.    

        Thank you for reading