What they don’t tell you about refreshable PDB Clones (Ghost Archives)

Introduction

In my previous blog post, I showcased how to automate the refresh of a PDB clone using a DBMS (Database Management Systems) scheduler job which gives better control of the frequency, instead of relying on the minute refresh. Handy if you don’t like keeping an automatic refresh every X minutes and would 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 behavior in the source/production environment from where my refreshable PDB clone was being updated. Let’s find out what has happened after a few refreshes.     

1. Refreshable Clone PDB Environment

Refresh Scheduled nightly (4 AM)

I am not going to describe the full configuration, since I already wrote about it in detail in my other blog post but I will at least share the job defined to perform the daily refresh using the 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 a 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 defined retention (7 days) in Recovery Manager (RMAN).

What did I notice?

I found archive logs that have a 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?

Besides deleting these files, I wanted to also check if they can be cataloged 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 catalogue, a good old OS cleanup was in order. For that purpose, I decided to run a few PowerShell commands to create a job and schedule it to run every night at 10 PM.  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 clean up the last PARLOG* archive created by the PDB upon each refresh (4 am).

Conclusion

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