Going to 12.2 in the cloud or not

UPDATE: 25JULY2017 - It appears that an UPGRADE to 12.2 from 12.1 does not generate the issues described below.  Only in a fresh install will these problems appear.  The upgrade on windows used the oracle account to install the software which I believe to be the source of the issue with the ORA-27027 error. - Ric


We all have to upgrade at some point to the latest and greatest version of oracle.  And this is really a good thing as the database does get better with each release.   And every time we do there are always little (or big) gotchas along the way.

In 12.2 I’ve found two things.  First on Windows (yes I do install Oracle on my Windows laptop), I’d recommend NOT using the anonymous user for the owner of the software.  This is recommended by oracle but my experience is it generates a heap of errors in the alert.log and made my laptop un-bootable.  Yea that was fun.

The errors were like this, over and over again.  There were associated with the monitoring back ground processes (MMON and MMNL in particular).  The database did work and log switches worked fine as did DML activity:

ORA-27037: unable to obtain file status,

OSD-04011: GetFileInformationByHandle() failure, unable to obtain file info

To solve this I reinstalled 12.1 and then upgraded to 12.2, and used the oracle user that owned the 12.1 software to install the 12.2 software.  The upgrade took quite a big longer to do then just installing 12.2 alone but I ended up with a database that didn’t cause a ton of errors and I can reboot my laptop too!!  Win!

But wait there’s more! 

Another issue I’ve found is that with a fresh install of 12.1 I can’t get STATISTICS_LEVEL to work properly.  With a new 12.2 install once I set STATISTICS_LEVEL to ALL it seems to always stay that way.  On a Linux cloud instance I have 12.2 installed as a fresh install (which doesn’t have the errors I saw in my Windows install) once I set STATISTICS_LEVEL to ALL it never seems to change, even when it “looks” like it’s changed.  This same thing did happen on my Windows laptop when I did the fresh install, but doesn’t happen after the upgrade, very odd to be sure.

As a quick example:

SQL>
SQL>select sql_trace, count(*) from v$session group by sql_trace;

SQL_TRAC COUNT(*)
-------- ---------------
DISABLED 42
SQL>
SQL>select count(*) from DBA_ENABLED_TRACES;

COUNT(*)
---------------
0
SQL>
SQL>show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
SQL>
SQL>select * from dual;

D
-
X
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

---------------------------------------------------------------------------------
| Id | Operation       | Name | Starts | E-Rows | A-Rows | A-Time     | Buffers |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.01 |       3 |
| 1 | TABLE ACCESS FULL| DUAL |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------

The statistics/tracing level sure appears to be typical and no trace appears to be turned on, yet it still shows all the stats you’d see if it was on. 

What you should get for this plan is something like this, note the warning at the bottom:

SQL> select * from dual;

D
-
X

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

----------------------------------------
| Id | Operation       | Name | E-Rows |
----------------------------------------
| 0 | SELECT STATEMENT |      |        |
| 1 | TABLE ACCESS FULL| DUAL |      1 |
----------------------------------------

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

I have created an SR with Oracle Support on both these issues and they have their top people working on them. Top people. 

If anyone else can confirm or deny my findings I’d sure like to hear from you. Have a great day!