12.2 controlfile backup never gets marked as obsolete

I’ve blogged about this bug in passing, but I thought would be worthwhile to document my testcase.    Basically backups of controlfiles never get marked as obsolete. The issue reproduces for both image copies and backupsets, my test-case uses the latter for simplicity.

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PVJTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0.1/dbhome/dbs/snapcf_PVJTEST.f'; # default

RMAN> list backup of controlfile;

specification does not match any backup in the repository

RMAN> backup database;

Starting backup at 2017-11-01 11:31:38
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oradata/PVJTEST/sysaux01.dbf
input datafile file number=00001 name=/u02/oradata/PVJTEST/system01.dbf
input datafile file number=00004 name=/u02/oradata/PVJTEST/undotbs01.dbf
input datafile file number=00007 name=/u02/oradata/PVJTEST/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-11-01 11:31:38
channel ORA_DISK_1: finished piece 1 at 2017-11-01 11:31:53
piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/agsifi8q_1_1 tag=TAG20171101T113138 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2017-11-01 11:31:54

Starting Control File and SPFILE Autobackup at 2017-11-01 11:31:54
piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/c-2122366327-20171101-07 comment=NONE
Finished Control File and SPFILE Autobackup at 2017-11-01 11:31:55

RMAN> backup database;

Starting backup at 2017-11-01 11:31:59
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oradata/PVJTEST/sysaux01.dbf
input datafile file number=00001 name=/u02/oradata/PVJTEST/system01.dbf
input datafile file number=00004 name=/u02/oradata/PVJTEST/undotbs01.dbf
input datafile file number=00007 name=/u02/oradata/PVJTEST/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-11-01 11:31:59
channel ORA_DISK_1: finished piece 1 at 2017-11-01 11:32:14
piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/aisifi9f_1_1 tag=TAG20171101T113159 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2017-11-01 11:32:14

Starting Control File and SPFILE Autobackup at 2017-11-01 11:32:14
piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/c-2122366327-20171101-08 comment=NONE
Finished Control File and SPFILE Autobackup at 2017-11-01 11:32:15

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 207 2017-11-01 11:31:53
Backup Piece 207 2017-11-01 11:31:53 /u01/app/oracle/product/12.2.0.1/dbhome/dbs/agsifi8q_1_1

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/agsifi8q_1_1 RECID=207 STAMP=958908699
Deleted 1 objects

 

RMAN> list backup of controlfile;

 

List of Backup Sets
===================

 

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
208 Full 10.47M DISK 00:00:00 2017-11-01 11:31:54
BP Key: 208 Status: AVAILABLE Compressed: NO Tag: TAG20171101T113154
Piece Name: /u01/app/oracle/product/12.2.0.1/dbhome/dbs/c-2122366327-20171101-07
Control File Included: Ckp SCN: 12426398 Ckp time: 2017-11-01 11:31:54

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
210 Full 10.47M DISK 00:00:00 2017-11-01 11:32:14
BP Key: 210 Status: AVAILABLE Compressed: NO Tag: TAG20171101T113214
Piece Name: /u01/app/oracle/product/12.2.0.1/dbhome/dbs/c-2122366327-20171101-08
Control File Included: Ckp SCN: 12426426 Ckp time: 2017-11-01 11:32:14

RMAN>

Oracle Support initially identified the following bug:
Bug 25943271 : RMAN REPORT OBSOLETE DOES NOT REPORT CONTROLFILE BACKUP AS OBSOLETE

However after waiting for the patch, it still did not resolve the issue. Seems this may now be the following bug:
Bug 26771767 : SPFILE BACKUP OR BACKUP PIECE INCLUDES SPFILE ARE NOT REPORTED AS OBSOLETE

We are working around the issue at the moment by modifying our rman scripts to delete controlfile backups older than a certain age.  Still waiting for the fix, after supplying reproducible testcase over six months ago 🙂

“ORA-20110: set stamp set count conflict” after upgrading to 12.2 Recovery Catalog

This is the second time we have hit this error after upgrading the recovery catalog to 12.2.

The first is now documented by Oracle as Document 2291791.1, and affects 12.1 multi-tenant instances with DBBP 12.1.0.2.161018 or later applied.
If you have applied that patchset, you can see the problem by checking the contents of file $ORACLE_HOME/rdbms/admin/recover.bsq

  cursor bs(low_recid number, high_recid number) is
    select bs.recid, bs.stamp, bs.set_stamp, bs.set_count, bs.backup_type,
           bs.incremental_level,
           bs.pieces, start_time, completion_time, controlfile_included,
           bs.input_file_scan_only, keep_until,
           decode (bs.keep_options, 'LOGS'      , KEEP_LOGS
                               , 'NOLOGS'       , KEEP_NOLOGS
                               , 'BACKUP_LOGS'  , KEEP_CONSIST
                                                , 0) keep_options,
           bs.block_size, bs.multi_section, bs.guid,
           decode(bs.con_id, invalid_pdbid, 1, 0) dropped_pdb
    from v$backup_set bs, v$containers pdb
    where bs.recid between low_recid and high_recid
      and (bs.stamp >= kccdivts OR bs.recid = high_recid)
      and bs.stamp >= resyncstamp
      and bs.for_xtts != 'YES'
    order by bs.recid;

Note that the join has been missed between v$backup_set and v$containers(!). The next cursor in the source, bp, also has the same ommission.
You can work-around the issue by adding the following condition to the two cursors, but you should probably check with Oracle Support first.

    
 and bs.con_id = pdb.con_id(+) 

The latest issue is on a 11g (hence single tenant obviously) on a dataguard standby site (in this case we take backups from both primary and standby instances).
Tracing the resync of the catalog from the standby site, the issue begins when inserting to table bs (backup sets) in the recovery catalog.
The insert fails because of a violation the unique key BS_U2 on columns db_key, set_stamp and set_count.
Checking the recovery catalog there already is record for this combination of set_stamp and set_count, but from the primary instance.

My suspicion is that the unique key also needs to include site_key field, however I don’t have enough of an understanding of what the set_stamp, set_count combination represents to be sure.

The only reference to the fields I can find come quotes in the documentation such as the following:

The SET_STAMP value from V$BACKUP_SET. SET_STAMP and SET_COUNT form a concatenated key that uniquely identifies this record in the target database control file.

What does seem apparent is that the 12.2 recovery catalog is stricter in enforcing this uniqueness, thus revealing some bugs elsewhere in the codebase.

A Consolidated List of 12cR2 Issues

I thought it would be useful to have  a consolidated list of issues we have run into, some of which I have already blogged about, some of which I haven’t. I will try to keep this page up-to-date moving forwards. Note we are not really using 12cR2 extensively, we have just upgraded OEM Repository and AWR Warehouse instances so far.

Recovery Manager

Upgrade Catalog Fails with RMAN-06444: error creating init_grsp_pdb_key

Oracle have now published a document about this issue:
UPGRADE CATALOG command from 12.1 to 12.2 Fails With RMAN-6004 and ORA-1422 (Doc ID 2252894.1)

Image Copies never marked as obsolete after datafile is removed

Bug 26115103 – REPORT OBSOLETE NOT SHOWING OLD DATAFILE COPY IN 12.2

Control File autobackups are never marked as obsolete

Bug 25943271: RMAN REPORT OBSOLETE DOES NOT REPORT CONTROLFILE BACKUP AS OBSOLETE

After upgrading catalog to 12.2,  resync failing with “ORA-20110: set stamp set count conflict”

Bug 26385473 : REGISTER TARGET DATABASE WITH 12.2 CATALOG FAILS WITH RMAN-03008 ORA-20110

This seems to be a problem that is specific to 12.1 multi-tenant instance on 12.2 catalog. It appears that rman is attempting to catalog the same backupset once for each PDB

Alain Fuhrer has run into this and has more details

Trace on the target system reveals problem seems to be caused by SQL like the following, which seem to ‘forget’ to provide the join conditions between the two tables queried:

SELECT BS.RECID , BS.STAMP , BS.SET_STAMP , BS.SET_COUNT , BS.BACKUP_TYPE , BS.INCREMENTAL_LEVEL , BS.PIECES , START_TIME , COMPLETION_TIME , CONTROLFILE_INCLUDED , BS.INPUT_FILE_SCAN_ONLY , KEEP_UNTIL , DECODE(BS.KEEP_OPTIONS , ‘LOGS’ , :b1 , ‘NOLOGS’ , :b2 , ‘BACKUP_LOGS’ , :b3 , 0 ) KEEP_OPTIONS , BS.BLOCK_SIZE , BS.MULTI_SECTION , BS.GUID , DECODE(BS.CON_ID , :b4 , 1 , 0 ) DROPPED_PDB FROM V$BACKUP_SET BS , V$CONTAINERS PDB WHERE BS.RECID BETWEEN :b5 AND :b6 AND ( BS.STAMP >= :b7 OR BS.RECID = :b6 ) AND BS.STAMP >= :b8 AND BS.FOR_XTTS != ‘YES’ ORDER BY BS.RECID

 

AWR and AWR Warehouse

AWR Warehouse load process failing with ORA-600 after upgrading repository

The error message is [kewrspbr_2: wrong last partition]. Doc ID 2020227.1 describes the fix for a similar issue. I found partitioning was wrong on some on some new 12.2 AWR tables so I recreated them, added a partition for each database contributing to AWR Warehouse. I have been unable to reproduce this issue on a test system.

WRH$_SGASTAT_U becoming unusable

Only affects database upgraded from 12.1.0.2, workaround is to re-create the index.
BUG 25954054 – WRH$_SGASTAT_U BECOMING UNUSABLE STATE IN UPGRADED DB
A patch is now available for this issue.

AWR Transfer Task Failing with ORA-28040

Set SQLNET.ALLOWED_LOGON_VERSION_SERVER to 11

Enterprise Manager

TNS-12599: TNS:cryptographic checksum mismatch in alert log of 12.2 database from connections from OEM

No functional impact. Bug number 25915038 created. Setting the following in sqlnet.ora of target database suppresses the message:

  • SQLNET.CRYPTO_CHECKSUM_SERVER=rejected
  • SQLNET.ENCRYPTION_SERVER=rejected

No Such Metadata in Enterprise Manager after upgrading database

This is a bug on calculation of database version from db plugin.  Simplest solution is to upgrade db plugin to 13.2.2 or later on database host.

Miscellaneous

ORA-20001: Statistics Advisor: Invalid task name for the current user

Seems to be on fresh created database. Solution per Doc ID 2127675.1 is to run  dbms_stats.init_package.

READ ANY TABLE audit records

Bug 26035911 : AUDIT RECORDS GENERATED EVEN WHEN THE SYSTEM PRIVILEGE IS NOT EXERCISED IN 12.2

12cR2 Incrementally Updated Backups and dropped datafiles

We have just noticed a difference in behavior in 12cR2 with regards to image copies being marked as obsolete after the backup is updated past the drop of a datafile.
I won’t describe the feature itself, if necessary you can read up at oracle-base or the Oracle documentation.

First review output from the testcase on a 12.1 instance, note that after dropping the datafile, and updating the backup past this point, the datafile copy is marked as obsolete:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ADAPTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/snapcf_ADAPTEST.f'; # default

RMAN> create tablespace test datafile size 1m autoextend off;

Statement processed

RMAN> backup incremental level 1 for recover of copy with tag 'test_obsolete' database;

Starting backup at 2017-04-19 12:47:09
using channel ORA_DISK_1
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 5 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATAC1/ADAPTEST/DATAFILE/system.401.941128155
output file name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-SYSTEM_FNO-1_0hs2302d tag=TEST_OBSOLETE RECID=19 STAMP=941719632
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATAC1/ADAPTEST/DATAFILE/sysaux.397.941128123
output file name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-SYSAUX_FNO-3_0is2302k tag=TEST_OBSOLETE RECID=20 STAMP=941719639
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATAC1/ADAPTEST/DATAFILE/undotbs1.403.941128201
output file name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-UNDOTBS1_FNO-4_0js2302r tag=TEST_OBSOLETE RECID=21 STAMP=941719644
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATAC1/ADAPTEST/DATAFILE/users.399.941128199
output file name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-USERS_FNO-6_0ks2302s tag=TEST_OBSOLETE RECID=22 STAMP=941719644
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATAC1/ADAPTEST/DATAFILE/test.1051.941719625
output file name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-TEST_FNO-5_0ls2302t tag=TEST_OBSOLETE RECID=23 STAMP=941719645
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2017-04-19 12:47:26

Starting Control File and SPFILE Autobackup at 2017-04-19 12:47:26
piece handle=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/c-757536437-20170419-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2017-04-19 12:47:27

RMAN> drop tablespace test including contents and datafiles;

Statement processed

RMAN> backup incremental level 1 for recover of copy with tag 'test_obsolete' database;

Starting backup at 2017-04-19 12:47:41
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATAC1/ADAPTEST/DATAFILE/system.401.941128155
input datafile file number=00003 name=+DATAC1/ADAPTEST/DATAFILE/sysaux.397.941128123
input datafile file number=00004 name=+DATAC1/ADAPTEST/DATAFILE/undotbs1.403.941128201
input datafile file number=00006 name=+DATAC1/ADAPTEST/DATAFILE/users.399.941128199
channel ORA_DISK_1: starting piece 1 at 2017-04-19 12:47:41
channel ORA_DISK_1: finished piece 1 at 2017-04-19 12:47:42
piece handle=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/0ns2303d_1_1 tag=TEST_OBSOLETE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-04-19 12:47:42

Starting Control File and SPFILE Autobackup at 2017-04-19 12:47:42
piece handle=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/c-757536437-20170419-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2017-04-19 12:47:43

RMAN> recover copy of database with tag 'test_obsolete';

Starting recover at 2017-04-19 12:47:55
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-SYSTEM_FNO-1_0hs2302d
recovering datafile copy file number=00003 name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-SYSAUX_FNO-3_0is2302k
recovering datafile copy file number=00004 name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-UNDOTBS1_FNO-4_0js2302r
recovering datafile copy file number=00006 name=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-USERS_FNO-6_0ks2302s
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/0ns2303d_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/0ns2303d_1_1 tag=TEST_OBSOLETE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2017-04-19 12:47:56

Starting Control File and SPFILE Autobackup at 2017-04-19 12:47:56
piece handle=/u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/c-757536437-20170419-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2017-04-19 12:47:57

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Datafile Copy        23     2017-04-19 12:47:25 /u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/data_D-ADAPTEST_I-757536437_TS-TEST_FNO-5_0ls2302t
Backup Set           7      2017-04-19 12:47:26
  Backup Piece       7      2017-04-19 12:47:26 /u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/c-757536437-20170419-00
Backup Set           8      2017-04-19 12:47:42
  Backup Piece       8      2017-04-19 12:47:42 /u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/0ns2303d_1_1
Backup Set           9      2017-04-19 12:47:42
  Backup Piece       9      2017-04-19 12:47:42 /u01/app/oracle/product/12.1.0.2/dbhome_2/dbs/c-757536437-20170419-01

Compare this with output from the same testcase on 12.2 instance, the datafile copy is not marked as obsolete:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PVJTEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0.1/dbhome/dbs/snapcf_PVJTEST.f'; # default

RMAN> create tablespace test datafile size 1m autoextend off;

Statement processed

RMAN> backup incremental level 1 for recover of copy with tag 'test_obsolete' database;

Starting backup at 19-APR-17
using channel ORA_DISK_1
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 7 found
no parent backup or copy of datafile 5 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u02/oradata/PVJTEST/sysaux01.dbf
output file name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-SYSAUX_FNO-3_0vs2304k tag=TEST_OBSOLETE RECID=36 STAMP=941719708
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/PVJTEST/system01.dbf
output file name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-SYSTEM_FNO-1_10s23053 tag=TEST_OBSOLETE RECID=37 STAMP=941719720
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u02/oradata/PVJTEST/undotbs01.dbf
output file name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-UNDOTBS1_FNO-4_11s2305a tag=TEST_OBSOLETE RECID=38 STAMP=941719724
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u02/oradata/PVJTEST/users01.dbf
output file name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-USERS_FNO-7_12s2305d tag=TEST_OBSOLETE RECID=39 STAMP=941719726
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u02/oradata/PVJTEST/datafile/o1_mf_test_dhfv0gk3_.dbf
output file name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-TEST_FNO-5_13s2305f tag=TEST_OBSOLETE RECID=40 STAMP=941719727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 19-APR-17

Starting Control File and SPFILE Autobackup at 19-APR-17
piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/c-2122366327-20170419-0e comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-17

RMAN> drop tablespace test including contents and datafiles;

Statement processed

RMAN> backup incremental level 1 for recover of copy with tag 'test_obsolete' database;

Starting backup at 19-APR-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oradata/PVJTEST/sysaux01.dbf
input datafile file number=00001 name=/u02/oradata/PVJTEST/system01.dbf
input datafile file number=00004 name=/u02/oradata/PVJTEST/undotbs01.dbf
input datafile file number=00007 name=/u02/oradata/PVJTEST/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-17
channel ORA_DISK_1: finished piece 1 at 19-APR-17
piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/15s2305t_1_1 tag=TEST_OBSOLETE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 19-APR-17

Starting Control File and SPFILE Autobackup at 19-APR-17
piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/c-2122366327-20170419-0f comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-17

RMAN> recover copy of database with tag 'test_obsolete';

Starting recover at 19-APR-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-SYSTEM_FNO-1_10s23053
recovering datafile copy file number=00003 name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-SYSAUX_FNO-3_0vs2304k
recovering datafile copy file number=00004 name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-UNDOTBS1_FNO-4_11s2305a
recovering datafile copy file number=00007 name=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/data_D-PVJTEST_I-2122366327_TS-USERS_FNO-7_12s2305d
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0.1/dbhome/dbs/15s2305t_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/15s2305t_1_1 tag=TEST_OBSOLETE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 19-APR-17

Starting Control File and SPFILE Autobackup at 19-APR-17
piece handle=/u01/app/oracle/product/12.2.0.1/dbhome/dbs/c-2122366327-20170419-10 comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-17

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           22     19-APR-17         
  Backup Piece       22     19-APR-17          /u01/app/oracle/product/12.2.0.1/dbhome/dbs/15s2305t_1_1

Index WRH$_SGASTAT_U has status UNUSABLE after upgrade to 12.2

Update 2016-04-26: this is new bug 25954054 – WRH$_SGASTAT_U BECOMING UNUSABLE STATE IN UPGRADED DB.

After upgrading our OMR and AWRW instances from 12.1 to 12.2, our monitoring revealed that index SYS.WRH$_SGASTAT_U was becoming unusable at regular intervals.
The first couple of times, we just rebuilt it in the hope that the problem would just go away(!); of course it didn’t, so I had to do a little bit of investigation.

Reviewing the alert log identified the exact time that the problem had occurred:

2017-04-12T08:11:43.674485+08:00
Some indexes or index [sub]partitions of table SYS.WRH$_SGASTAT have been marked unusable

Grepping for “SGASTAT” in the trace directory, also revealed that the index being unusable was causing inserts into AWR had been failing:

*** 2017-04-12T12:00:18.338061+08:00
*** SESSION ID:(289.8864) 2017-04-12T12:00:18.338092+08:00
*** CLIENT ID:() 2017-04-12T12:00:18.338103+08:00
*** SERVICE NAME:(SYS$BACKGROUND) 2017-04-12T12:00:18.338108+08:00
*** MODULE NAME:(MMON_SLAVE) 2017-04-12T12:00:18.338119+08:00
*** ACTION NAME:(Auto-Flush Slave Action) 2017-04-12T12:00:18.338125+08:00
*** CLIENT DRIVER:() 2017-04-12T12:00:18.338132+08:00

*** KEUUXS - encountered error: (ORA-01502: index 'SYS.WRH$_SGASTAT_U' or partition of such index is in unusable state
)
  *** SQLSTR: total-len=464, dump-len=464,
      STR={insert into  wrh$_sgastat  (dbid, per_pdb, con_dbid, snap_id, instance_number, pool, name, bytes)  select distinct  :dbid, 0 per_pdb,  :srcdbid  con_dbid,  :snap_id, :instance_number, pool, name, bytes FROM     (select con_id, pool, name, bytes,             100*(bytes) / (sum(bytes) over (partition by pool)) part_pct        from  v$sgastat      ) sgastat  WHERE (1 = 1)   and part_pct >= 1      or pool is null      or name = 'free memory' or name = 'row cache' }
*** KEWRAFM1: Error=13509 encountered by kewrfteh

As we still had the redo logs for the period of time identified by the alert log, I fired up logminer, and right after a bunch of inserts into WRH$_SGASTAT, I found the following statement:

alter table WRH$_SGASTAT drop partition WRH$_SGASTA_1521710113_17057;

I am guessing this is part of the standard mechanism for purging AWR data based on configured retention settings.
A quick check reveals that of the partitioned AWR base tables, 3 have indexes that are not partitioned:

SQL> SELECT                                                                                                                                                                                                          
  2     partitioned,                                                                                                                                                                                                 
  3     COUNT(*)                                                                                                                                                                                                     
  4  FROM                                                                                                                                                                                                            
  5     dba_indexes                                                                                                                                                                                                  
  6  WHERE                                                                                                                                                                                                           
  7     index_type = 'NORMAL'                                                                                                                                                                                        
  8  AND                                                                                                                                                                                                             
  9     table_name IN (                                                                                                                                                                                              
 10        SELECT                                                                                                                                                                                                    
 11           table_name                                                                                                                                                                                             
 12        FROM                                                                                                                                                                                                      
 13           dba_tables                                                                                                                                                                                             
 14        WHERE                                                                                                                                                                                                     
 15           table_name LIKE 'WRH$\_%' ESCAPE '\'                                                                                                                                                                   
 16        AND                                                                                                                                                                                                       
 17           partitioned = 'YES'                                                                                                                                                                                    
 18     )                                                                                                                                                                                                            
 19  GROUP BY                                                                                                                                                                                                        
 20     partitioned;                                                                                                                                                                                                 
                                                                                                                                                                                                                     
PAR   COUNT(*)                                                                                                                                                                                                       
--- ----------                                                                                                                                                                                                       
NO           3                                                                                                                                                                                                       
YES         35                                                                                                                                                                                                       

SQL> SELECT                                                                                                                                                                                                          
  2     table_owner,                                                                                                                                                                                                 
  3     table_name,                                                                                                                                                                                                  
  4     index_name                                                                                                                                                                                                   
  5  FROM                                                                                                                                                                                                            
  6     dba_indexes                                                                                                                                                                                                  
  7  WHERE                                                                                                                                                                                                           
  8     index_type = 'NORMAL'                                                                                                                                                                                        
  9  AND                                                                                                                                                                                                             
 10     table_name IN (                                                                                                                                                                                              
 11        SELECT                                                                                                                                                                                                    
 12           table_name                                                                                                                                                                                             
 13        FROM                                                                                                                                                                                                      
 14           dba_tables                                                                                                                                                                                             
 15        WHERE                                                                                                                                                                                                     
 16           table_name LIKE 'WRH$\_%' ESCAPE '\'                                                                                                                                                                   
 17        AND                                                                                                                                                                                                       
 18           partitioned = 'YES'                                                                                                                                                                                    
 19     )                                                                                                                                                                                                            
 20  AND partitioned = 'NO';                                                                                                                                                                                         
                                                                                                                                                                                                                     
TABLE_OWNER                    TABLE_NAME                     INDEX_NAME                                                                                                                                             
------------------------------ ------------------------------ ------------------------------                                                                                                                         
SYS                            WRH$_SGASTAT                   WRH$_SGASTAT_U                                                                                                                                         
SYS                            WRH$_CON_SYSMETRIC_HISTORY     WRH$_CON_SYSMET_HISTORY_INDEX                                                                                                                          
SYS                            WRH$_SYSMETRIC_HISTORY         WRH$_SYSMETRIC_HISTORY_INDEX                                                                                                                           

Running the same query on both 11.2 and 12.1 instances reveals no such mis-configured indexes We are following up with Oracle Support, but I would be interested to know if anyone else can check my findings on their 12.2 instances.

Working with Oracle Support we have worked around the issue using the following script:

ALTER TABLE wrh$_sgastat DROP CONSTRAINT wrh$_sgastat_u;

ALTER TABLE wrh$_sgastat ADD CONSTRAINT wrh$_sgastat_u UNIQUE ( dbid,snap_id,instance_number,name,pool,con_dbid )
   USING INDEX
      LOCAL
   TABLESPACE sysaux;

In addition, I believe I have found the bug in file $ORACLE_HOME/rdbms/admin/c1201000.sql at line 3036:

alter table wrh$_sgastat drop constraint wrh$_sgastat_u;

alter table wrh$_sgastat add constraint wrh$_sgastat_u
        unique (dbid, snap_id, instance_number, name, pool, con_dbid, stattype);

TNS-12599: TNS:cryptographic checksum mismatch

Checking the alert log of some of one of the instances we have recently upgraded to 12.2 I noticed the following error message occurring frequently:

NI cryptographic checksum mismatch error: 12599.

VERSION INFORMATION:
TNS for Linux: Version 12.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
Time: 06-APR-2017 11:38:05
Tracing to file:
Tns error struct:
ns main err code: 12599

TNS-12599: TNS:cryptographic checksum mismatch
ns secondary err code: 12656
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0

Comparing with entries in listener.log file, I identified that these corresponded to connections from Enterprise Manager. Reproducing the issue was easy, I navigated to the database in Enterprise Manager, went through the menus “Oracle Database->Target Setup->Monitoring Configuration” and clicked on the “Test Connection button”.

Every time the test succeeded, but the message was appended to the alert log.

MOS note 1927120.1 goes into some detail about the error message, including the fact that is is safe to ignore. It should be resolved when Enterprise Manager is updated to include latest JDBC driver.

One option listed to prevent the issue appearing in the alert log is to set DIAG_ADR_ENABLED=OFF in sqlnet.ora (it will be then instead be logged to sqlnet.log).

I found that setting the following two parameters in sqlnet.ora also meant the warning did not occur (setting either parameter by itself was not sufficient).

  • SQLNET.CRYPTO_CHECKSUM_SERVER=rejected
  • SQLNET.ENCRYPTION_SERVER=rejected

Upgrade Recovery Catalog to 12cR2 fails and leaves catalog an unusable state

After upgrading our AWR Warehouse instance to 12.2 our standard RMAN scripts were failing because the Recovery Catalog required upgrading. Attempting the upgrade failed, and left the Recovery Catalog unable. Fortunately we were able to restore the catalog from weekly export.

I was able to reproduce the problem by importing into a new schema, below are the results. Note I enable tracing to dig into exactly what was failing:

[oracle@vmhkoemawrbpd1 ~]$ rman catalog TST_RCAT/TST_RCAT debug trace=/tmp/upgrade.trc

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 21 17:16:31 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

RMAN-06008: connected to recovery catalog database
RMAN-06186: PL/SQL package TST_RCAT.DBMS_RCVCAT version 12.01.00.02. in RCVCAT database is too old

RMAN> UPGRADE CATALOG

RMAN-06435: recovery catalog owner is TST_RCAT
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG

RMAN-06444: error creating init_grsp_pdb_key
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-01422: exact fetch returns more than requested number of rows

The following extract from the trace file identifies the failing section of code:

DBGSQL:         RCVCAT> declare
DBGSQL:            cursor grsp_c is
DBGSQL:               select distinct grsp.rowid rid, dbinc.db_key db_key
DBGSQL:                 from grsp, dbinc
DBGSQL:                where grsp.pdb_key is null
DBGSQL:                  and grsp.dbinc_key = dbinc.dbinc_key
DBGSQL:                order by dbinc.db_key;
DBGSQL:            prev_db_key   number := 0;
DBGSQL:            local_pdb_key number;
DBGSQL:         begin
DBGSQL:            for r in grsp_c loop
DBGSQL:              if (r.db_key  prev_db_key) then
DBGSQL:                 prev_db_key := r.db_key;
DBGSQL:                 select pdb_key into local_pdb_key
DBGSQL:                   from pdb
DBGSQL:                  where pdb.con_id in (1, 0);
DBGSQL:              end if;
DBGSQL:
DBGSQL:              update grsp set pdb_key = local_pdb_key where rowid = r.rid;
DBGSQL:            end loop;
DBGSQL:         end;
DBGSQL:
DBGSQL:            sqlcode = 1422
DBGSQL:         error: ORA-01422: exact fetch returns more than requested number of rows (krmkosqlerr)

Table PDB seems to hold information about Pluggable DataBases (a feature of multitenant).  In our catalog we had 30 records with con_id equals to 0 or 1.  Obviously the upgrade code was not expecting this.

Attempts to use the partially upgraded catalog now fail due to the mismatch of components within the catalog.

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Mar 21 17:32:53 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database
PL/SQL package TST_RCAT.DBMS_RCVCAT version 12.02.00.01 in RCVCAT database is too new
RMAN must be upgraded to version 12.02.00.01 to work with this package
RMAN> backup database;

Starting backup at 17:35:44 21/03/2017
PL/SQL package TST_RCAT.DBMS_RCVCAT version 12.02.00.01 in RCVCAT database is too new
RMAN must be upgraded to version 12.02.00.01 to work with this package
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/21/2017 17:35:44
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06429: RCVCAT database is not compatible with this version of RMAN

Oracle support have published a document about this issue:

UPGRADE CATALOG command from 12.1 to 12.2 Fails With RMAN-6004 and ORA-1422 (Doc ID 2252894.1)