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
Intro
Intro