Oracle 12.2 underscores appear in SPFILE – be aware when you flashback

Oracle 12.2 underscores appear in SPFILE - be aware when you flashbackI did blog in the past weeks about Fallback Strategies with Flashback Database. But two of my reference customers came across an interesting issue when they tried to fallback: Oracle 12.2 underscores appear in the SPFILE – magically – and prevent the fallback using the existing SPFILE.

Oracle 12.2 underscores appear in SPFILE – be aware when you flashback

In one case it happened during a test, in the other case it happened during a live fallback after the Data Guard Broker has interfered with the upgrade causing real trouble. In both cases the SPFILE sits in ASM.

Both customers upgraded their databases to Oracle Database 12.2.0.1 with the latest RU installed.

Both customers tried to flashback the database. As you do this in Oracle Database 12.2.0.1 all works fine. But when you try to MOUNT the database in the before-upgrade environment, you will receive this error:

SQL> startup mount
ORA-01078: failure in processing system parameters
LRM-00101: unknown parameter name '__inmemory_ext_roarea'

The database can’t be mounted anymore.

What has happened?

Actually four underscore parameters accidentally got written into the SPFILE during upgrade to Oracle Database 12.2.0.1.:

_inmemory_ext_roarea 
_inmemory_ext_rwarea
_upgrade_capture_noops
_upgrade_optim

That’s not nice. And these – of course to the previous release unknown – parameters prevent database mount/startup.

The inmemory parameters get tracked as bug 26695380 - PARAMETERS _INMEMORY_EXT_ROAREA AND _INMEMORY_EXT_RWAREA BLOCK FALLBACK. The upgrade parameters dealing with Multitenant get tracked as bug 26811983 - PARAMETERS _UPGRADE_CAPTURE_NOOPS AND _UPGRADE_OPTIM BLOCK FALLBACK. Both should be fixed in a future Oracle Database release.

How to avoid it

You must create a copy of your SPFILE before upgrade to avoid such surprises. But if you hit this situation you can do this:

SQL> create pfile from spfile;
File created.

Afterwards you will edit your PFILE, remove the four not-known parameters and mount the database.

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0.4/dbs/initUPGR.ora'

Then you can proceed.

–Mike

2 thoughts on “Oracle 12.2 underscores appear in SPFILE – be aware when you flashback

  1. Hi Mike, thanks for all the great posts and tips. I’d like to share with you with a bug on AIX platform, that besides using the pre-upgrade 12.1.0.2 backup pfile (or spfile with all the 12.2 generated _xx* hidden parameters removed), if this is to flashback a RACOneNode database from 12.2 back to 12.1.0.2, after flashback database is executed, the database won’t open for resetlogs. Basically the R1N DB is *dead* at this point.

    Before upgrade you will need to apply one-off 12.1.0.2 patch 23195445 on top of 12.1.0.2 Aug2017 or Oct2017 PSU, as follows, for R1N database. This bug is on 12.1.0.2 R1N database only (on AIX here); the regular RAC database flashback/open resetlogs is ok.

    In summary, before 12.1.0.2 to 12.2 R1N DB upgrade, on AIX, (and on Solaris also? need to check):
    If 12.1.0.2 DB home is with July2017 PSU (PSU dated Aug2017 _170814):
    Apply /install/oracle/images/12201/patch1off/p23195445_12102170814_AIX64-5L.zip
    If 12.1.0.2 DB home is with Jan2018 PSU: (use the one-off made from Oct2017)
    Apply /install/oracle/images/12201/patch1off/p23195445_12102171017_AIX64-5L.zip

    Check with Oracle support for more details.

    • Thanks JSC,

      I did check bug 23195445. It’s a generic issue which applies not only to AIX and Solaris but to other ports as well.
      The issue is that to check if there are any other instance up, it gets from CSS the membership of group “DB” and when checking the bitmap, some bits are set.

      Are you sure this is the issue you wanted to mention?

      The fix btw will be included in the July 2018 Bundles.

      Thanks,
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree