I 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
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.” and when checking the bitmap, some bits are set.
The issue is that to check if there are any other instance up, it gets from CSS the membership of group “DB
Are you sure this is the issue you wanted to mention?
The fix btw will be included in the July 2018 Bundles.
Thanks,
Mike
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
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
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
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
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
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
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
Thanks a lot – I added the information to the bug.
Cheers,
Mike