What a strange topic. And I thought I never will blog about this. But as I have received the same question for the third time today, I think there’s a bit clarification needed. Lost AWR snapshots reappear in Oracle 12.2 in Multitenant pluggable databases.
Magically …
Lost AWR snapshots reappear again in Oracle 12.2 in Multitenant
In a non-CDB database you have your local AWR data stored in the SYSAUX tablespace. Whenever you create an AWR snapshot:
exec dbms_workload_repository.create_snapshot;
You can query and of course evaluate it afterwards:
select con_id, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN
from CDB_HIST_SNAPSHOT order by begin,snap_id, con_id;
CON_ID SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- ---------- -----------------
0 1 1 20/07/17 16:23:12
0 2 1 24/08/17 13:59:33
0 3 1 24/08/17 14:00:28
In my example I’m using an Oracle Database 12.1.0.2 non-CDB.
The snapshot from July 20, 2017 will be purged automatically later on.
Plugin into an Oracle 12.1.0.1 CDB
When I plug it into an Oracle 12.1.0.2 CDB the AWR snapshot data seems to be lost.
Before the plugin operation my non-CDB had these snapshots:
select con_id, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN
from CDB_HIST_SNAPSHOT order by begin,snap_id, con_id;
CON_ID SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- ---------- ---------------------------------------------------
1 1 1 30/01/17 10:01:44
1 2 1 30/01/17 10:12:45
1 3 1 30/01/17 11:00:48
1 4 1 30/01/17 12:00:51
1 5 1 30/01/17 13:00:55
1 6 1 30/01/17 14:17:38
1 7 1 30/01/17 15:00:40
1 8 1 30/01/17 16:00:46
8 rows selected.
And after plugin (once the purging has erased the old snapshots from Jan 30, 2017 in my lab environment):
select con_id, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN
from CDB_HIST_SNAPSHOT order by begin,snap_id, con_id
CON_ID SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- ---------- ---------------------------------------------------
1 9 1 24/08/17 14:12:17
1 10 1 24/08/17 14:22:18
2 rows selected.
The AWR snapshots I took before the plugin operation seem to be gone (remember: they were taken at 13:59 and 14:00). In fact a PDB in Oracle Database 12.1 does not have it’s own AWR data within a PDB. You can always query data from the CDB-level per PDB. Still, if you’d check the storage consumption in the PDB with awrinfo.sql you’d find out that the AWR data from the non-CDB days hasn’t been removed.
Querying the snapshots from within the PDB does result in "no rows selected".
Upgrade to Oracle Database 12.2.0.1
After upgrade the two old snapshots reappear in the PDB. They weren’t visible in 12.1 as the API did not exist for PDBs. This is not harmful at all.
First see the CDB$ROOT (the change of the CON_ID is quite interesting):
select con_id, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN
from CDB_HIST_SNAPSHOT order by begin,snap_id, con_id;
CON_ID SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- ---------- ---------------------------------------------------
0 9 1 24/08/17 14:12:17
0 10 1 24/08/17 14:22:18
0 11 1 24/08/17 15:00:21
0 12 1 24/08/17 17:32:47
0 13 1 24/08/17 17:42:50
And then the PDB:
select con_id, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN
from CDB_HIST_SNAPSHOT order by begin,snap_id, con_id;
CON_ID SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- ---------- -----------------
3 2 1 24/08/17 13:59:33
3 3 1 24/08/17 14:00:28
Here they are back again. Please note also the difference in SNAP_IDs between the CDB$ROOT and the PDB.
Snap Levels
The different snap levels mean:
- 1 – TYPICAL
- 2 – ALL
- 11 – LITE
Even more interesting …
After a while I noticed that I get hourly snapshots in my CDB$ROOT – but not in my PDB.
CDB$ROOT a few hours later:
select con_id, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN
from CDB_HIST_SNAPSHOT order by begin,snap_id, con_id;
CON_ID SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- ---------- ---------------------------------------------------
0 9 1 24/08/17 14:12:17
0 10 1 24/08/17 14:22:18
0 11 1 24/08/17 15:00:21
0 12 1 24/08/17 17:32:47
0 13 1 24/08/17 17:42:50
0 14 1 24/08/17 18:00:55
0 15 1 24/08/17 19:00:04
0 16 1 24/08/17 20:00:10
8 rows selected.
Whereas the PDB does not seem to get an automatic snapshot created:
select con_id, snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') BEGIN,
to_char(end_interval_time, 'dd/mm/yy hh24:mi:ss') END
from CDB_HIST_SNAPSHOT order by begin,snap_id, con_id;
CON_ID SNAP_ID SNAP_LEVEL BEGIN END
---------- ---------- ---------- ----------------- -----------------
3 2 1 24/08/17 13:59:33 24/08/17 14:00:28
3 3 1 24/08/17 14:00:28 24/08/17 14:02:18
3 4 1 24/08/17 17:32:47 24/08/17 22:01:21
3 5 1 24/08/17 22:01:21 24/08/17 22:01:54
I created the snapshots 4 and 5 by myself.
Finally … the cleanup?
First of all, this is not harmful at all. And you won’t even see this if you step from a non-CDB to CDBs in Oracle Database 12.2 or higher directly.
There’s a fix worked on (bug 25698110 – NEED METHOD TO PURGE AWR DATA FROM PLUGGED IN PDB) which delivers two new functions in DBMS_WORKLOAD_REPOSITORY:
DROP_BASELINEDROP_SNAPSHOT_RANGE
But there seems to be some improvement necessary.
The only documentation about AWR snapshots specific to PDBs I found is in the Oracle Multitenant 12.2 White Paper:
Automatic Workload Repository (AWR) Data at PDB Level
This allows for granular diagnosis of performance with in a particular PDB. It is particularly important in DBaaS environments, where one might expect an autonomous Pluggable Database Administrator – a PDBA – to have responsibility for performance within that PDB.
–Mike
