preupgrade.jar - enforced recommendations

Recently I upgraded a database from version 12.1 to 12.2.
I take upgrades serious - similar (but not as skilled) to Mike Dietrich. So I downloaded latest preupgrade.zip and unzipped it [I made a small error by unzipping it NOT to $ORACLE_HOME/rdbms/admin but to a temporary directory - later more about this].
Beside such errors, I try to follow Oracles recommended Upgrade Process.

At some point Oracle recommends to Gather Dictionary Stats "the night before starting the upgrade".

For me this is a recommendation, not an obligation. I had (from my perspective) very good reasons NOT to follow this recommendation.

Still, during the upgrade, the preupgrade.log shows

+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
     upgrade in off-peak time using:

      EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

     Dictionary statistics do not exist or are stale (not up-to-date).

     Dictionary statistics help the Oracle optimizer find efficient SQL
     execution plans and are essential for proper upgrade timing. Oracle
     recommends gathering dictionary statistics in the last 24 hours before
     database upgrade.

     For information on managing optimizer statistics, refer to the 12.1.0.2
     Oracle Database SQL Tuning Guide.

If they say AUTOFIXUP - treat this as a promise (or treat).

After preupgrade.jar, it's to run preupgrade_fixups.sql.
This calls (among others)

fixup_result := dbms_preup.run_fixup('dictionary_stats');

If you go through the package and other functionality, you will find function dictionary_stats_check in preupgrade_package.sql. And there it runs

BEGIN
    EXECUTE IMMEDIATE
     ' select 1 from dual where exists(
              select distinct operation
                from DBA_OPTSTAT_OPERATIONS
                        where operation =''gather_dictionary_stats''
                        and start_time > systimestamp -  INTERVAL ''24''  HOUR) '
      INTO dictionary_stats_recent;

and if no dictionary stats were conpleted recently, this function is executed:

-- *****************************************************************
--     This fixup executes dictionary stats pre upgrade
-- *****************************************************************
FUNCTION dictionary_stats_fixup          (
         result_txt IN OUT VARCHAR2,
         pSqlcode    IN OUT NUMBER) RETURN NUMBER
IS
    stats_result BOOLEAN;
    sys_string varchar2(5):='SYS';
BEGIN
   stats_result := run_int_proc('DBMS_STATS.GATHER_DICTIONARY_STATS', result_txt, pSqlcode);

   IF (stats_result) THEN
       RETURN c_success;
   ELSE
       RETURN c_failure;
   END IF;
END dictionary_stats_fixup;

That's the moment where I recognised how serious Oracle takes gathering of dictionary stats.

A little rant on twitter about this aggressive recommendation gave me a nice reply where the recommendation is stated:

You mean ... just like it's stated right there the manual? Well, well, well... who would have thought 😀 pic.twitter.com/eI9hUKnSEu

— Connor 🙃 McDonald (@connor_mc_d) October 9, 2018

🤔

At least I know where to change the code - but it must be done before preupgrade.jar is run!

But there is a 2^nd situation when DBMS_STATS.GATHER_DICTIONARY_STATS is run:
After the upgrade itself, postupgrade_fixups.sql must run.
Here the side note to my initial statement why preupgrade.zip MUST be unzipped to $ORACLE_HOME/rdbms/admin:

VARIABLE admin_preupgrade_dir VARCHAR2(512);

REM
REM    point PREUPGRADE_DIR to OH/rdbms/admin
REM
DECLARE
    oh VARCHAR2(4000);
BEGIN
    dbms_system.get_env('ORACLE_HOME', oh);
    :admin_preupgrade_dir := dbms_assert.enquote_literal(oh || '/rdbms/admin');
END;
/

DECLARE
    command varchar2(4000);
BEGIN
    command := 'CREATE OR REPLACE DIRECTORY PREUPGRADE_DIR AS ' || :admin_preupgrade_dir;
    EXECUTE IMMEDIATE command;
END;
/

@?/rdbms/admin/dbms_registry_basic.sql
@?/rdbms/admin/dbms_registry_extended.sql


REM
REM    Execute the preupgrade_package from the PREUPGRADE_DIR
REM    This is needed because the preupgrade_messages.properties file
REM    lives there too, and is read by preupgrade_package.sql using
REM    the PREUPGRADE_DIR.
REM
COLUMN directory_path NEW_VALUE admin_preupgrade_dir NOPRINT;
select directory_path from dba_directories where directory_name='PREUPGRADE_DIR';
set concat '.';
@&admin_preupgrade_dir./preupgrade_package.sql

That's the reason why unzipping it ton another directory, after the upgrade an older version of preupgrade scripts is used - maybe not a desired thing!

Later there another fixup is called:

fixup_result := dbms_preup.run_fixup('post_dictionary');

This in that case that function is run:

-- *****************************************************************
--     POST_DICTIONARY_CHECK Section
--     This check recommends re-gathering dictionary stats post upgrade
--     The logic in the query is:  Check if statistics has been taken
--     after upgrade, if not report it and generate the fixup in the
--     postupgrade fixup script, after the fixup run, it will not fail
--     and therefore it will report this check as successfull.
-- *****************************************************************
FUNCTION post_dictionary_check          (result_txt OUT VARCHAR2) RETURN NUMBER
IS
dictionary_stats_recent  NUMBER := 0;
correct_version boolean := TRUE;

BEGIN
  IF dbms_registry_extended.compare_versions(db_version_4_dots, C_ORACLE_HIGH_VERSION_4_DOTS, 4) < 0 THEN
     correct_version := FALSE;
  END IF;

  BEGIN
    EXECUTE IMMEDIATE
     ' select 1 from dual where exists(
              select distinct operation
                from DBA_OPTSTAT_OPERATIONS
                        where operation =''gather_dictionary_stats''
                        and start_time > (select max(OPTIME) from registry$log where cid =''UPGRD_END'')) '
      INTO dictionary_stats_recent;
    EXCEPTION
      WHEN NO_DATA_FOUND then
        null;
  END;

After all this, I STILL recommend to use LATEST preupgrade.jar,
unzip it to proper $ORACLE_HOME/rdbms/admin,
execute it as documented
and don't mess around with it!
Don't mess around with it unless Oracle Support says so (or you take all the unforseen side effects on your credits).

SQL Real Time Monitoring pure HTML report (thanks to SQLDeveloper 18.3)

Yesterday (from the writing of this post perspective) SQLDeveloper 18.3 came out.
(it seems SQL Developer does not strict follow Oracle Database Release Number schema, otherwise it must be 18.4 already as it's released in 4th quarter of 2018)

There are many bugs fixed on 18.3 and some nice enhancements there as well.

My favorite enhancement is Real Time SQL Monitoring, HTML exports now available - no flash!

As Tanel Poder wrote about it already (Generate Oracle SQL Monitoring Reports as HTML using SQL Developer v18.3 (no Flash needed)) I'll show how you can use it without SQL Developer.
I'm not against SQL Developer, in fact I'm very happy with it!
Just the Real Time SQL Monitor tab seems kind of unresponsive to me, especially on DBs with many active sessions and high load.


So if I don't want to watch a java process drawing circles and showing blur previews, I first go to query v$sql_monitor. Beside all the columns I'll need to identify my SQL of interest, I need the columns SQL_ID, SQL_EXEC_ID, and SQL_EXEC_START.
And, of course at least once you must create a new HTML Report with SQL Developer! Save this report as a template for later use.

When you open the HTML report in an editor, there are 2 lines of special interest:

 var data_sqlId = '3v64dcg0rja6k'; 
 var data_xml = '<report db_version="12.1.0.2.0...>"

You can replace them easily with the context of your SQL of interest.
To get the proper XML you can use DBMS_SQLTUNE.report_sql_monitor. But as this function provides a multiline XML, but the HTML expects the XML in one line, the call should be

SELECT XMLSerialize( DOCUMENT xmltype(DBMS_SQLTUNE.report_sql_monitor(
  sql_id         => '3v64dcg0rja6k',
  type           => 'XML',
  SQL_EXEC_ID    => 33554648, 
  SQL_EXEC_START => to_date('2018-10-10 05:36:17'),
  report_level   => 'ALL')) NO INDENT ) AS XML_report
FROM dual;

If you omit SQL_EXEC_ID or SQL_EXEC_START, the report will still compile but you should understand the difference in data you get displayed.

So the minimal count you create a new HTML-Only SQL Real Time Monitoring report with SQL Developer should be once. But if you read this ( and your DBs are properly licensed), spread the good news with all the people who might benefit from these reports!

---

Please keep in mind that the HTML file is loading CSS and JS files from and URL at Oracles Content Delivery platform - so they are subject to any change without a new release of SQL Developer, RDBMS or anything. So expect unexpected changes every time!

seing your DB as it was some minutes ago

Last week during a discussion with a colleague we thought if it would make sense to have in SQLDeveloper the possibility to see the system "as it was some minutes ago".
Small errors can happen and also resource control isn't always perfect in every company.

Björn Rost suggested to use DBMS_FLASHBACK.ENABLE_AT_TIME:

How about just calling DBMS_FLASHBACK.ENABLE_AT_TIME in your session? Why would you need an extra feature or button for that?

— Bjoern Rost (@brost) September 28, 2018

A very clever suggestion!

Let's first look at the documentation:

DBMS_FLASHBACK

Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified time or a specified system change number (SCN).

and in more detail:

DBMS_FLASHBACK Overview

DBMS_FLASHBACK provides an interface for the user to view the database at a particular time in the past, with the additional capacity provided by transaction back out features that allow for selective removal of the effects of individual transactions. This is different from a flashback database which moves the database back in time.

When DBMS_FLASHBACK is enabled, the user session uses the Flashback version of the database, and applications can execute against the Flashback version of the database.

DBMS_FLASHBACK is relevant only for the session in which it's called, so it doesn't change the database.
But it's unclear what's meant by database in this context.

Here a small testcase which shows some unexpected results of DBMS_FLASHBACK:
(I slightly edite the text for better readability)

drop table x1;
create table x1 (u number);

insert into x1 (u) values (1);

CREATE OR REPLACE FUNCTION "RETURN_SOMETHING" (
    i NUMBER
) RETURN VARCHAR2 AS 
-- Version 1
BEGIN
    IF ( i = 1 ) THEN
        RETURN 'red';
    ELSE
        RETURN 'blue';
    END IF;
END return_something;
/


select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') now, u, return_something(u) rrr,
     dbms_flashback.get_system_change_number SCN
from x1;

NOW                          U RRR          SCN
------------------- ---------- ----- ----------
2018-10-01 19:37:56          1 red     24042787


exec sys.DBMS_SESSION.sleep(60);

There should be nothing fancy up to this time.
The table and function are created and the select works fine.
I need the DBMS_SESSION.sleep to copy/paste the proper timestamp into the next block of code:

update x1 set u = 2 where u = 1;

1 row updated.

CREATE OR REPLACE FUNCTION "RETURN_SOMETHING" (
    i NUMBER
) RETURN VARCHAR2 AS 
-- Version 2
BEGIN
    IF ( i = 1 ) THEN
        RETURN 'oans';
    ELSE
        RETURN 'zwoa';
    END IF;
END return_something;
/

select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') now, u, return_something(u) rrr
from x1;

NOW                          U RRR  
------------------- ---------- -----
2018-10-01 19:39:32          2 zwoa 

Still nothing spectacular here - just the preparation done.
Now onto the interesting part:

EXEC dbms_flashback.enable_at_time(to_timestamp('2018-10-01 19:38:00','YYYY-MM-DD HH24:MI:SS'));


select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') now, u, return_something(u) rrr
from x1;

NOW                          U RRR  
------------------- ---------- -----
2018-10-01 19:39:33          1 oans 



col text for A50
select text
from dba_source
where owner='BERX'
and name='RETURN_SOMETHING'
order by LINE asc;

TEXT                                              
--------------------------------------------------
FUNCTION        "RETURN_SOMETHING" (
    i NUMBER
) RETURN VARCHAR2 AS 
-- Version 1
BEGIN
    IF ( i = 1 ) THEN
        RETURN 'red';
    ELSE
        RETURN 'blue';
    END IF;
END return_something;

11 rows selected. 

Here we can see how DBMS_FLASHBACK.ENABLE_AT_TIME is set to a time between the 1^st insert & create function and the 2^nd block.
With this setting, the content of table X1 is as expected. Also DBA_SOURCE shows the code of RETURN_SOMETHING.
But the function itself is not changed in memory and works as of it's state NOW, not at the given flashback time.
The flashback time version of RETURN_SOMETHING is even visible when you open it in SQLDeveloper (you have to believe me or test it).
Don't forget to clean up after the tests:

exec dbms_flashback.disable;

select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') now, u, return_something(u) rrr
from x1;

NOW                          U RRR  
------------------- ---------- -----
2018-10-01 19:39:33          2 zwoa 

---

For this testcase no COMMIT was used. ;-)