Monday, July 31, 2017

A two minute tech tip for the cloud or not!

In case you missed this, here I am in video giving a 2 Minute tech tip on QB_NAME.  There is a rumor I mention my favorite water hole in this, you'll have to watch it to see if I do or not.

https://www.youtube.com/watch?v=3t5NHtPKji0






Tuesday, July 25, 2017

SQLPLUS, login.sql and 12.2 in the cloud or not



Another little hiccup in 12.2 this time with SQLPlus.  It appears that it will not run the login.sql in your current directory as it always has in the past.    

There seems to be a problem with SQLPlus and SQLCL using the SQLPATH setting.  SQLPlus as far as I can tell, still uses SQLPATH as it always has for running a SQL file like:

SQL> @emp

    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
--------------- -------------------- ------------------------- -------------------------
            105 David                Austin                    DAUSTIN
            151 David                Bernstein                 DBERNSTE
            165 David                Lee                       DLEE
SQL>

In SQLPlus, the GET command as far as I know has never used SQLPATH to find files, it only looks in the current directory, and that seems to be working like normal.  

But SQLCL the GET command doesn’t appear to be working correctly.  You can get it to look in what SQLPATH is set to as long as SQLPATH only has ONE directory in it.  If SQLPATH has multiple entries (which mine does) then it doesn’t use it at all. 

This is a known issue in SQLPlus and you can look it up under this document number in your myoraclesupport account.  Doc ID 2274608.1   By the way, the doc talks about the glogin.sql file not being run, that doesn’t appear to be the case for me.  My fix was to add a call to my login.sql file in the glogin.sql file (in ORACLE_HOME/sqlplus/admin) and it worked just fine. The doc indicates that if the glogin.sql file is in some other directory pointed to by SQLPATH it doesn’t work, that very well may be true.  

I’ve logged an SR about the issue with SQLCL. I presume there is some common code between the two hence they seem to be related.

Thursday, July 13, 2017

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!