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

Posted on September 19, 2017 by Fallback Flaws and Pitfalls 10

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

Share this:

Tags:

You may also like...

10 Responses

  1. JSC says:
    April 12, 2018 at 23:34

    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.

    Reply
    • Mike.Dietrich says:
      April 13, 2018 at 03:28

      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

      Reply
  2. Monowar says:
    July 18, 2019 at 03:06

    Hi Mike,

    I was testing upgrading/downgrading in between 12cR2[Jan’19Patch] and 19.3. Upgrading went well.

    But I got the similar error during my downgrading task. I used flashback method. After flashback and before opening with resetlogs when I run startup mount
    SQL> startup mount
    ORA-01078: failure in processing system parameters
    LRM-00101: unknown parameter name ‘__unified_pga_pool_size’
    ORA-01078: failure in processing system parameters
    LRM-00101: unknown parameter name ‘__unified_pga_pool_size’

    This is for your information only. It worked as usual creating pfile and removing parameter which came only after upgrading from 12.2 to 19.3

    $ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 – Production on Wed Jul 17 21:10:49 2019
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle. All rights reserved.

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0

    SQL> SELECT a.ksppinm “Parameter”,
    decode(bitand(ksppiflg/524288,1),1,’TRUE’,’FALSE’) ISPDB_MODIFIABLE
    FROM x$ksppi a
    WHERE a.ksppinm LIKE ‘/__unified_pga_pool_size’ escape ‘/’ 2 3 4
    5 /

    Parameter ISPDB
    ———————————————————————————
    __unified_pga_pool_size FALSE

    Reply
    • Mike.Dietrich says:
      July 19, 2019 at 10:15

      Hi Monowar,

      awesome (ok, not the error of course) but your clear description.
      I’ll file a bug directly – this shouldn’t happen.

      Thanks a lot!
      Mike

      Reply
      • Mike.Dietrich says:
        July 19, 2019 at 12:04

        Hi Monowar,

        I tried to reproduce this. Your database is a CDB/PDB?
        Or a non-CDB?

        I tried it with a non-CDB and I can’t reproduce it.

        Can you also cut/paste your spfile please?

        Cheers,
        Mike

        Reply
  3. Monowar says:
    July 19, 2019 at 12:16

    Hi Mike,

    My setup is CDB with one PDB into two node RAC. I will reproduce the issue before passing you spfile entry.

    Thanks,
    Monowar

    Reply
    • Mike.Dietrich says:
      July 19, 2019 at 15:36

      Hi Monowar,

      it is better if you would open an SR please but pass me on the SR information and I will monitor it.
      I can’t reproduce it in my environment, neither with a non-CDB, nor with a 12.2.0.1 CDB with one PDB.
      I upgrade them to 19c, flashback as you did. But I don’t get this error or can’t see the parameter.

      This is my pfile after a successful flashback/resetlogs in 12.2.0.1:

      —
      CDB1.__data_transfer_cache_size=0
      CDB1.__db_cache_size=1006632960
      CDB1.__inmemory_ext_roarea=0
      CDB1.__inmemory_ext_rwarea=0
      CDB1.__java_pool_size=16777216
      CDB1.__large_pool_size=33554432
      CDB1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
      CDB1.__pga_aggregate_target=134217728
      CDB1.__sga_target=1459617792
      CDB1.__shared_io_pool_size=67108864
      CDB1.__shared_pool_size=318767104
      CDB1.__streams_pool_size=0
      …

      And this is my pfile before I do the flashback database in 19c:

      —
      CDB1.__data_transfer_cache_size=0
      CDB1.__db_cache_size=1006632960
      CDB1.__java_pool_size=0
      CDB1.__large_pool_size=16777216
      CDB1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
      CDB1.__pga_aggregate_target=134217728
      CDB1.__sga_target=1459617792
      CDB1.__shared_io_pool_size=67108864
      CDB1.__shared_pool_size=352321536
      CDB1.__streams_pool_size=0
      CDB1.__unified_pga_pool_size=0

      It has the parameter you saw – but this is not in my source but in my destination home.

      Now I guess you use only one spfile in your ASM, right?
      I have two versions, one in my old and one in my new home’s dbs directories.

      Thanks,
      Mike

      Reply
      • Mike.Dietrich says:
        July 19, 2019 at 16:21

        Hi Monowar,

        no need for an SR anymore. I know why and when this happens and filed a bug:
        Bug 30072483 – DOUBLE UNDERSCORE __UNIFIED_PGA_POOL_SIZE GETS INCORRECTLY WRITTEN INTO SPFILE AND BLOCKS DOWNGRADE

        The bug is public at the moment so as long as the developer let it stay, you can track it on MOS.

        Cheers, and thanks for letting us know.
        Mike

        Reply
  4. Monowar says:
    July 20, 2019 at 23:58

    Thanks Mike. Good to know that.

    Below is the case –
    ================
    BEFORE UPGRADE
    ================
    $ srvctl config database -d TMON1_01
    Database unique name: TMON1_01
    Database name: TMON1
    Oracle home: /u02/app/oracle/product/12.2.0/db100
    Oracle user: oracle
    Spfile: +DATA/TMON1_01/PARAMETERFILE/spfile.303.1014071375
    Password file: +DATA/TMON1_01/PASSWORD/pwdtmon1_01.330.1014070487
    Domain:
    Start options: open
    Stop options: immediate
    Database role: PRIMARY
    Management policy: AUTOMATIC
    Server pools:
    Disk Groups: DATA
    Mount point paths:
    Services:
    Type: RAC
    Start concurrency:
    Stop concurrency:
    OSDBA group: dba
    OSOPER group: dba
    Database instances: TMON11,TMON12
    Configured nodes: ora-x1,ora-x2
    CSS critical: no
    CPU count: 0
    Memory target: 0
    Maximum memory: 0
    Default network number for database services:
    Database is administrator managed

    SQL> show pdbs

    CON_ID CON_NAME OPEN MODE RESTRICTED
    ———- —————————— ———- ———-
    2 PDB$SEED READ ONLY NO
    3 TMON1PDB MOUNTED

    SQL> create pfile=’/tmp/beforeupg12to19cTMON.ora’ from spfile=’+DATA/TMON1_01/PARAMETERFILE/spfile.303.1014071375′;

    File created.

    -bash-4.2$ cat /tmp/beforeupg12to19cTMON.ora
    TMON12.__data_transfer_cache_size=0
    TMON11.__data_transfer_cache_size=0
    TMON12.__db_cache_size=771751936
    TMON11.__db_cache_size=771751936
    TMON12.__inmemory_ext_roarea=0
    TMON11.__inmemory_ext_roarea=0
    TMON12.__inmemory_ext_rwarea=0
    TMON11.__inmemory_ext_rwarea=0
    TMON12.__java_pool_size=16777216
    TMON11.__java_pool_size=16777216
    TMON12.__large_pool_size=33554432
    TMON11.__large_pool_size=33554432
    TMON11.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment
    TMON12.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment
    TMON12.__pga_aggregate_target=603979776
    TMON11.__pga_aggregate_target=603979776
    TMON12.__sga_target=1275068416
    TMON11.__sga_target=1275068416
    TMON12.__shared_io_pool_size=67108864
    TMON11.__shared_io_pool_size=67108864
    TMON12.__shared_pool_size=369098752
    TMON11.__shared_pool_size=369098752
    TMON12.__streams_pool_size=0
    TMON11.__streams_pool_size=0
    *.audit_file_dest=’/u02/app/oracle/admin/TMON1_01/adump’
    *.audit_trail=’db’
    *.cluster_database=TRUE
    *.compatible=’12.2.0′
    *.control_files=’+DATA/TMON1_01/CONTROLFILE/current.268.1014070701′,’+DATA/TMON1_01/CONTROLFILE/current.311.1014070701′
    *.db_block_size=8192
    *.db_create_file_dest=’+DATA’
    *.db_name=’TMON1′
    *.db_recovery_file_dest=’+DATA’
    *.db_recovery_file_dest_size=12380m
    *.db_unique_name=’TMON1_01′
    *.diagnostic_dest=’/u02/app/oracle’
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=TMON1XDB)’
    *.enable_pluggable_database=true
    family:dw_helper.instance_mode=’read-only’
    TMON11.instance_number=1
    TMON12.instance_number=2
    *.local_listener=’-oraagent-dummy-‘
    *.nls_language=’AMERICAN’
    *.nls_territory=’AMERICA’
    *.open_cursors=300
    *.pga_aggregate_target=571m
    *.processes=300
    *.remote_login_passwordfile=’exclusive’
    *.sga_target=1212m
    TMON12.thread=2
    TMON11.thread=1
    TMON11.undo_tablespace=’UNDOTBS1′
    TMON12.undo_tablespace=’UNDOTBS2′
    ========================
    AFTER UPGRADE To 19.3
    ========================
    -bash-4.2$ srvctl config database -d TMON1_01
    Database unique name: TMON1_01
    Database name: TMON1
    Oracle home: /u02/app/oracle/product/19.3.0/db300
    Oracle user: oracle
    Spfile: +DATA/TMON1_01/spfileTMON1.ora
    Password file: +DATA/TMON1_01/PASSWORD/pwdtmon1_01.330.1014070487
    Domain:
    Start options: open
    Stop options: immediate
    Database role: PRIMARY
    Management policy: AUTOMATIC
    Server pools:
    Disk Groups: DATA
    Mount point paths:
    Services:
    Type: RAC
    Start concurrency:
    Stop concurrency:
    OSDBA group: dba
    OSOPER group: dba
    Database instances: TMON11,TMON12
    Configured nodes: ora-x1,ora-x2
    CSS critical: no
    CPU count: 0
    Memory target: 0
    Maximum memory: 0
    Default network number for database services:
    Database is administrator managed

    SQL*Plus: Release 19.0.0.0.0 – Production on Sat Jul 20 08:37:59 2019
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle. All rights reserved.

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0

    SQL>
    COMP_ID COMP_NAME VERSION STATUS
    ————— —————————————- ————— ————
    CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
    CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
    JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
    XML Oracle XDK 19.0.0.0.0 VALID
    CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
    APS OLAP Analytic Workspace 19.0.0.0.0 VALID
    RAC Oracle Real Application Clusters 19.0.0.0.0 VALID
    XDB Oracle XML Database 19.0.0.0.0 VALID
    OWM Oracle Workspace Manager 19.0.0.0.0 VALID
    CONTEXT Oracle Text 19.0.0.0.0 VALID
    ORDIM Oracle Multimedia 19.0.0.0.0 VALID
    SDO Spatial 19.0.0.0.0 VALID
    XOQ Oracle OLAP API 19.0.0.0.0 VALID
    OLS Oracle Label Security 19.0.0.0.0 VALID
    DV Oracle Database Vault 19.0.0.0.0 VALID

    SQL> show parameter spfile

    NAME TYPE VALUE
    ———————————— ———– ——————————
    spfile string +DATA/TMON1_01/spfiletmon1.ora
    SQL> create pfile=’/tmp/afterupg12to19cTMON.ora’ from spfile;

    File created.

    SQL> exit
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0
    -bash-4.2$ diff /tmp/afterupg12to19cTMON.ora /tmp/beforeupg12to19cTMON.ora
    3,4c3,4
    < TMON11.__db_cache_size=620756992
    TMON12.__db_cache_size=771751936
    > TMON11.__db_cache_size=771751936
    10c10
    TMON11.__java_pool_size=16777216
    21,22c21,22
    < TMON11.__shared_pool_size=469762048
    TMON12.__shared_pool_size=369098752
    > TMON11.__shared_pool_size=369098752
    25,26d24
    < TMON12.__unified_pga_pool_size=0
    alter system set cluster_database=false scope=spfile;

    System altered.

    SQL> create pfile=’/tmp/initPDWGTMON1.ora’ from spfile;

    File created.

    SQL> exit
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.3.0.0.0
    -bash-4.2$ srvctl stop database -d TMON1_01
    -bash-4.2$ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 – Production on Sat Jul 20 10:39:44 2019
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 1275064608 bytes
    Fixed Size 9134368 bytes
    Variable Size 570425344 bytes
    Database Buffers 687865856 bytes
    Redo Buffers 7639040 bytes
    Database mounted.
    Database opened.
    SQL> show pdbs

    CON_ID CON_NAME OPEN MODE RESTRICTED
    ———- —————————— ———- ———-
    2 PDB$SEED READ ONLY NO
    3 TMON1PDB MOUNTED
    SQL> select name from v$restore_point;

    NAME
    ——————————————————————————–
    BEFORE_UPGRADE
    BEFORE_UPGRADE_TMON1PDB

    SQL> FLASHBACK PLUGGABLE DATABASE TMON1PDB TO RESTORE POINT BEFORE_UPGRADE_TMON1PDB;

    Flashback complete.

    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 1275064608 bytes
    Fixed Size 9134368 bytes
    Variable Size 570425344 bytes
    Database Buffers 687865856 bytes
    Redo Buffers 7639040 bytes
    Database mounted.
    SQL> flashback database to restore point BEFORE_UPGRADE;

    Flashback complete.
    SQL> shutdown immediate;
    ORA-01109: database not open

    Database dismounted.
    ORACLE instance shut down.

    $ cat /etc/oratab | grep TMON11
    TMON11:/u02/app/oracle/product/12.2.0/db100:Y:

    . oraenv
    ORACLE_SID = [TMON11] ?
    The Oracle base remains unchanged with value /u02/app/oracle
    -bash-4.2$ sqlplus / as sysdba

    SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 20 10:57:15 2019

    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup mount;
    ORA-01078: failure in processing system parameters
    LRM-00101: unknown parameter name ‘__unified_pga_pool_size’
    ORA-01078: failure in processing system parameters
    LRM-00101: unknown parameter name ‘__unified_pga_pool_size’

    After that I am doing below to workout –

    SQL> startup mount pfile=’/tmp/initPUPG_TMON1.ora’;
    ORACLE instance started.

    Total System Global Area 1275068416 bytes
    Fixed Size 8792344 bytes
    Variable Size 452986600 bytes
    Database Buffers 805306368 bytes
    Redo Buffers 7983104 bytes
    Database mounted.

    Then finished –
    alter database open RESETLOGS | modify srvctl —-> for

    SQL> @comps.sql

    COMP_ID COMP_NAME VERSION STATUS
    ————— —————————————- ————— ————
    CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID
    CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID
    JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID
    XML Oracle XDK 12.2.0.1.0 VALID
    CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID
    APS OLAP Analytic Workspace 12.2.0.1.0 VALID
    RAC Oracle Real Application Clusters 12.2.0.1.0 VALID
    XDB Oracle XML Database 12.2.0.1.0 VALID
    OWM Oracle Workspace Manager 12.2.0.1.0 VALID
    CONTEXT Oracle Text 12.2.0.1.0 VALID
    ORDIM Oracle Multimedia 12.2.0.1.0 VALID
    SDO Spatial 12.2.0.1.0 VALID
    XOQ Oracle OLAP API 12.2.0.1.0 VALID
    OLS Oracle Label Security 12.2.0.1.0 VALID
    DV Oracle Database Vault 12.2.0.1.0 VALID

    Thanks,
    Monowar Mukul

    Reply

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mike Dietrich
Oracle Senior Director of Product Manager for Database Upgrade, Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 7,849 other subscribers

Follow me on Twitter