Mittwoch, 20. Juni 2018

preupgrade.jar version number

Mike Dietrich is very motivated to convince everyone who wants to take Oracle database upgrades serious to use always the latest version of preupgrade.jar.

Some parts of Note:884522.1 - How to Download and Run Oracle's Database Pre-Upgrade Utility
 are misleading in my point of view:
If the upgrade-to version is 12.2 or higher, then save the file to your target $ORACLE_HOME/rdbms/admin directory and then unzip the file. It could be your source or target $ORACLE_HOME.
but this will be sorted out for sure in the new future.

For me deploying some files to an ORACLE_HOME (after it's own well defined deployment process) sounds creepy, and I'm interested how this will fit into the concept of read-only ORACLE_HOMEs in 18c+.

As I don't want to modify anything (even replacing with the identical files in case latest preupgrades.jar is deployed already) it's better to check the version of current preupgrade.jar.

If it is used already, the Build number is visible in
preupgrade.log:
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0 Build: 14

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================
...

But if it is not executed (yet), you have to get the Build number from the jar file.
At the moment (Build 014) you can get it from preupgrade_package.sql by a simple statement:

unzip -p $PATH_TO_PREUPGRADE/preupgrade.jar preupgrade_package.sql | \
awk '$1 ~ /c_build/ { print $5 }' | tr -d \;

This can help to identify the current installation and if a (re-)installation is really required.



unfortunately there are some inconsistencies.
e.g. in preupgrade_181_cbuild_2_lf.zip:
if you unzip this file you will get a preupgrade_package.sql with c_build is 2
but the preupgrade_package.sql inside of preupgrade.jar shows c_build is 1

Mittwoch, 30. Mai 2018

flipping performance

Recently I had a request to check "if there is any problem with the database at <specific times>".
You can imagine, there was no problem. Nothing in alert.log, no tracefiles, no locks or oddities in ASH/AWR.

I had to ask back & forth to get some more information about the issue. The information I got was:
"we use a statement SELECT * FROM table(some_function('P1', 'P2')) - and it took longer than 10 sec at the given times". Of course there were no bind variables used but constants every time.
This situation helps a lot as obviously there is nothing to do with SQL_IDs now, and the real issue is (probably) within the function.
The function just generated 1 SELECT (no BINDs again - but PL/SQL did the "auto-binding").
With this SQL it's easy to identify the SQL_ID.
This SQL_ID has 3 childs with different plans. That is sufficient to check, if the specific times somehow match a flip of plans. This was done by a simle query:

with gash as (
select sql_id, sql_child_number, sample_time, LAG(sql_child_number, 1, 0) OVER (ORDER BY sample_time) AS prev_child
from gv$active_session_history
where sql_id='&sql_id'
order by sample_time
)
select *
from gash
where sql_child_number != prev_child
order by sample_time

For a longer observation dba_hist_active_sess_history can be used as well.

The result easily showed a flip between childs/plans at the given times.

(solution was to generate "outline-hints" with dbms_xplan.display_cursor for the good plan and so hint the SQL inside of some_function).

This was no complex task to analyze or big deal to execute. Just a small example where GUIs might not help so much. By the (little) information given it would have been pure luck to see the problematic pattern in a ASH-graph. As ther was nothing to filter, all the other "noise" in the DB would have wiped the information out.

Sometimes it's good to know the architecture and views, not only the GUI.

PS: The statement above is ugly. A MATCH_RECOGNIZE would be more elegant. Unfortunately this DB is 11.2

Montag, 28. Mai 2018

enabling Database Vault is complicated

Enabling Database Vault in a already running system can be laborious.

I recently tried to enable DV in a (copy of a ) production Oracle EBS DB.
The Documentation is pretty lear and easy:
https://docs.oracle.com/database/121/DVADM/getting_started.htm#DVADM002
But I fail at

BEGIN 
DVSYS.CONFIGURE_DV ( 
dvowner_uname => 'DBV_OWNER', 
dvacctmgr_uname => 'DBV_ACCTMGR'); 
END; 
/ 
with
ERROR at line 1: 
ORA-47500: Database Vault cannot be configured. 
ORA-06512: at "DVSYS.CONFIGURE_DV", line 23 
ORA-06512: at "DVSYS.CONFIGURE_DV", line 72 
ORA-06512: at line 2 
That's not very helpful and ORA-47500 suggests:
If this happens, deinstall and then reinstall Oracle Database Vault
Even after removal and re-installation, the symptoms still stay.

The problem here are some invalid objects in the database!

As it's unclear to my which objects are the problem, I asked Oracle.
Unfortunately the answer is not helping a lot:

It is possible that not all [objects] need to be valid before you can configure DV successfully but you definitely need to work on fixing these invalid object at this point because that is the reason for the error.
If this happens, deinstall and then reinstall Oracle Database Vault.

Of course the suggestion doesn't help, utlrp did not change anything.
As all the invalid objects doesn't belong to the DBA but some developed code, it will take some time until DV is enabled.

I don't have any solution right now than laborious wading through all invalid objects and fixing them. 

Montag, 30. April 2018

Tracing the Database Configuration Assistant

I am struggling with DBCA right now so a little tracing is advised.
Note Tracing the Database Configuration Assistant (DBCA) (Doc ID 188134.1) gives a nice suggestion to add these parameters:
-DTRACING.ENABLED=true -DTRACING.LEVEL=2
but it's no elegant solution to edit the shell script in $ORACLE_HOME/bin/dbca

I prefer my new swiss knife for java parameters. A simple environment variable:
_JAVA_OPTIONS= -DTRACING.ENABLED=true -DTRACING.LEVEL=3
This is very useful for any java program. As Oracle has many such programs, there is often use for _JAVA_OPTIONS

Sonntag, 1. April 2018

more fun with ages


On-call duties sometimes leads to interesting results.
I have to observe a restore running (or to be more precise wait for a tape library to find the right tapes and put them into drives.
This gives me time to catch up my news aggregator. And some time to play with interesting news.
One interesting post I stumbled across is

Franck Pachos After IoT, IoP makes its way to the database
There he mentions a new, yet undocumented function to_dog_year().
As I had some fun with ages in general, this promises to be even more entertainment!

If I had a dog with equivalent age then I'm old, what's it's birthday?
It's simple now:
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

select BX_YEAR_CALC.ADDITION(sysdate,   
         -1 * to_dog_year(BX_YEAR_CALC.DIFFERENCE(:MY_BIRTHDAY))) 
                                                as dogs_birthdate 
from dual;

DOGS_BIRTHDATE     
-------------------
2012-09-26 03:27:18

(I don't care about breed or this other parameter as it's only a hypothetical dog in my case).


As the library is still shuffling tapes I was guessing about the origin of this function.
Francks idea of Internet of Pets makes some sense to me, but such a very specific function?
I know stories about Oracle providing special versions of it's database to important customers. But that was back in something like Version 5 or 6 (long before my time) and more dedicated to performance issues of any kind. But maybe this is the case here also.
IF this important customer is doing any business with a huge amount of dogs and needs to calculate their "Age" all over the application, it might make sense to ask Oracle to code it in the core of the database. To have custom functions is very uncommon for me, but maybe we will see the next big thing soon, and Oracle tries to gain it's market share there?



Restore is done, but recovery takes it's time! I'm sure archivelogs are spread cross many tapes. This gave me time to do some (very simple!) performance tests with to_dog_year. It seems to be very easy to calculate  the "age" of a puppy, but this gets more expensive rapidly. At the input of 2 (or somewhere around - I increased input by 0.5) the complexity seems to drop and the effort increases only slightly.
It would be interesting to see the real formula, if it will ever get published, but at the input of 2 it seems to flip from a "calculation for young dogs" to "calculation for adult dogs".
If it's really for something like tracking dogs, I hope they (whoever they are) have a good capacity planning: I assume customers with young dogs will be more interested to join the service (whatever it will be) which makes the computation requirements slightly higher than average.

Anyhow, future will tell.
As the restore finished, I'll continue the night with better things to do...


Update:
If you are interested in more details about to_dog_year(), before you invest any effort there, please check date and time when this post was published.
I hope you liked it as much as we did.

Samstag, 31. März 2018

spfile parameters derived from cpu_count

In Oracle databases some spfile parameters are derived based on others, if they are not set explicit.
One of the main drivers I discovered is CPU_COUNT.
So I did some tests based in the past for version 11.1.0.7 and 12.1.0.2.
To simulate a machine with sufficient CPUs, I had to use a trick described in a previous post. Using _disable_cpu_check threw an ORA-7445:[ksucre] in 12.2 with cpu_count >126.

the script I used this time is basically (of course with 180 instead of 122 for 18.0).
#!/usr/bin/ksh  -x

integer i=1
while ((i <= 255));
do
  print " $i ";

  echo "*.audit_file_dest='/_some_dir_/BX1/adump'
*.audit_trail='db'
*.compatible='12.2.0.1.0'
*.control_files='/_some_dir_/BX1/controlfile/o1_mf_djd017z2_.ctl', '/_some_dir_/fast_recovery_area/BX1/BX1/controlfile/o1_mf_djd01834_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/_some_dir_/oradata'
*.db_name='BX1'
*.db_recovery_file_dest='/_some_dir_/fast_recovery_area/BX1'
*.db_recovery_file_dest_size=7G
*.db_unique_name='BX1'
*.diagnostic_dest='/appl/oracle'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=7g
*.undo_tablespace='UNDOTBS1'
*.max_string_size='EXTENDED'
#*._disable_cpu_check='TRUE'
*.cpu_count=$i" > /_some_dir_/cpus/initBX1_$i.ora

  echo "shutdown abort;
startup pfile='/_some_dir_/cpus/initBX1_$i.ora' ;

insert into init_parameters
select '12.2'     ,
       $i         ,
       a.ksppinm  ,
       b.ksppstvl ,
       c.ksppstvl
from x\$ksppi a, x\$ksppcv b, x\$ksppsv c
where a.indx = b.indx and a.indx = c.indx;

commit;

select distinct(\"CPUs\") from init_parameters;

shutdown;
exit;"> cpus/122doit$i.sql

sqlplus "/ as sysdba" @cpus/122doit$i.sql >cpus/122log$i.log

sleep 5

  (( i = i + 1));
done


11.1.0.712.1.0.212.2.0.118.0.0.0
_cursor_db_buffers_pinnedxxxx
_db_block_buffers xxxx
_db_block_lru_latches xxxx
__db_cache_sizexxxx
_db_file_exec_read_count xx
db_file_multiblock_read_countxx
_db_handlesxxx
db_writer_processesxxxx
dml_locksxxx
_enqueue_hashxxx
_enqueue_hash_chain_latchesxxxx
_enqueue_locksxxxx
_enqueue_resourcesxxx
_flashback_generation_buffer_sizexxxx
_gc_fast_index_split_waitx
_gcs_min_cr_slavesxx
_kghdsidx_countxx

_hang_resolution_scopexx
_ksmsq_hintmaxprocx
__large_pool_sizexxx
_lms_rollbacksxx
log_bufferxxxx
_log_parallelism_maxxxxx
_log_simultaneous_copiesxxxx
_max_log_write_parallelismxx
_max_outstanding_log_writesxxx
_max_pending_scn_bcastsxxx
_messagesxxx
_num_longop_child_latchesxxxx
_num_rlslavesxx
parallel_max_serversxxxx
_parallel_min_message_poolxxxx
parallel_min_serversxxx
parallel_servers_targetxxx
pga_aggregate_limitxxx
_pmon_max_slavesxx
_pmon_slaves_arr_sizexx
processesxxx
_px_proactive_slave_alloc_thresholdxxx
_session_allocation_latchesxxx
sessionsxxx
sga_max_sizexx
shared_pool_reserved_sizexxx
__shared_pool_sizexxxx
_shared_server_num_queuesxxxx
_small_table_thresholdxxxx
_spin_count xxxx
_sqlmon_max_plan
xxx
_super_shared_conversion_threshold
xx
transactions xxx
_use_single_log_writer xxx

To get a good picture of the changes, feel free to select parameters, versions or whatever you want below.
Raw data is available at GistHub.
The js app is available here.
(It's based on an idea PerfSheet.js by Luca Canali.

As some of the values are not numbers, I applied this mapping:
TRUE1
FALSE0
OFF0
ADAPTIVE -1
INSTANCE -2


Montag, 26. März 2018

Age - hard to calculate


The concept of age seems quite simple.
Right now I'm 42 years old.
I can be more precise and define it as 42 years, some months and several days.
And at a given date I will be 43 years old, and months and date are reset to 0.
Unfortunately it's hard to calculate with these numbers.
For those who wants to go into detail I'd recommend ISO 8601 Data elements and interchange formats. Unfortunately I don't have access to this document.
Another good reason is the explanation for CPANs DateTime.pm module.
I tried a slightly different approach:
Define the age as integer and fractional part, where the integer part is the age in years as we are used to it, and the fractional part is the  number of days already passed divided by the total days in the current year. 
This sounds quite simple, but it has some complicated effects due to leap years. So I wrote a little package ( see below) to handle these effects.
The Package is called BX_YEAR_CALC and has 2 functions:
DIFFERENCE (year1 date, year2 date) returns the AGE.
ADDITION (year1 date, diff number) returns the date at a specific AGE.

Here are some examples:

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

SQL> select bx_year_calc.difference('2004-02-29 12:00:00', '2005-03-01 11:59:00') diff from dual;

      DIFF
----------
1.00136796

SQL> select bx_year_calc.addition ('2004-02-29 12:00:00',  1.00136796042617960426179604261796042618) d from dual;

D
-------------------
2005-03-01 11:59:00

SQL> select bx_year_calc.addition ('2005-03-01 11:59:00', -1.00136796042617960426179604261796042618) d from dual;

D
-------------------
2004-03-01 00:00:00


You can see in these examples there are situations - especially around leap years and Feb 29th - where the calculation goes quite wrong. My implementation tries to reduce this effect to a minimum.

Feel free to play with dates and ages, any comments are very welcome.


create or replace PACKAGE BX_YEAR_CALC AS 

/*  2018-03-25 - berx - initial - martin.a.berger@gmail.com
    BX_YEAR_CALC - to calculate DATES in units or YEARS
                   equal idea as  DATE1 - DATE2 returns a number,
                   where the integer part are full days and fractional part  reflects remaining hours, minutes and seconds
                   
                   As some years has 365 or 366 days, the last year can have different number of total days, 
                   so the fraction must be calculated based on this.
                   Only the number of days of the last year (based on the begin) is calculated.
                   
                   Be aware: there are situations with leap years or gregorian calendar where results are not intuitive.
                   If you find a bette rimplementation for a given situation, let's discuss

*/


/* returns the "age in years" between the 2 parameters. 
   integer is quite obvious - it's the years between the start date and the same day (if it exists) in target year.
   fraction is remaining days divided by days in the last year
*/
  FUNCTION DIFFERENCE(YEAR1 date default sysdate,
                      YEAR2 date default sysdate)
    RETURN number;

/* adds (or substracts - if number is negative) a "age in years" as defined by DIFFERENCE to a given date
   for addition, first the integer part is added and then the fraction - based on days in last year.
   for subtraction, first the fraction is removed (to be more aligned to addition) and the the integer
   */
  FUNCTION ADDITION (YEAR1 date default sysdate,
                DIFF number default 0)
    RETURN date;

/* returns the next "existing" date to a given "old-date" and a specific other year ("new year")
   this handles the situation similar to "today is 2004-02-29. what's the date a year ago?
   to avoid ORA-01840: input value not long enough for date format
   this function searches the next day "above" (with incr => default 1) or "below" (with incr => -1)
   other values for incr are not defined.
   */
    FUNCTION next_real_date (
        new_year VARCHAR2,
        old_date DATE,
        incr number default 1
    )
    return date;

END BX_YEAR_CALC;

CREATE OR REPLACE PACKAGE BODY bx_year_calc AS

    c_debug          NUMBER := 0;

    PROCEDURE debug (
        message VARCHAR2
    )
        AS
    BEGIN
        IF
            c_debug > 0
        THEN
            dbms_output.put_line(message);
        END IF;
    END debug;

    FUNCTION next_real_date (
        new_year   VARCHAR2,
        old_date   DATE,
        incr       NUMBER DEFAULT 1
    ) RETURN DATE AS

        year2_year    NUMBER;
        return_date   DATE; -- the highest full year related to YEAR1  which is smaller than YEAR2  
        year_diff     NUMBER;
        leap_help     BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
        leap_count    NUMBER := 0; -- how many days to add to fix leap issue
        date_not_valid EXCEPTION;
        PRAGMA exception_init ( date_not_valid,-1839 );
    BEGIN
      if NOT abs(incr)=1 then
      raise_application_error( -20001, ' incr msut be +1 or -1 ' );
      end if;
        WHILE NOT leap_help LOOP
            BEGIN
                IF
                    leap_count = 0
                THEN
                    return_date := TO_DATE(new_year || '-' || TO_CHAR(old_date,'MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');

                    leap_help := true;
                ELSE
                    return_date := TO_DATE(new_year || '-' || TO_CHAR(old_date + leap_count,'MM-DD'),'YYYY-MM-DD');

                    leap_help := true; -- this happens only if NO exception is thrown
                END IF;

            EXCEPTION
                WHEN date_not_valid -- ORA-01839: date not valid for month specified
                 THEN
                    leap_count := leap_count + incr;

                    IF -- just a safety in case anything goes wrong 
                        abs(leap_count) > 33
                    THEN
                        leap_count := 1 / 0;
                    END IF;
            END;
        END LOOP;

        RETURN return_date;
    END next_real_date;

    FUNCTION difference (
        year1   DATE,
        year2   DATE DEFAULT SYSDATE
    ) RETURN NUMBER AS

        days_lastyear    NUMBER; -- how many days the last year has
        fract_lastyear   NUMBER; -- remaining part as fraction of a year
        year2_year       NUMBER;
        hi_year_lo       DATE;   -- the highest full year related to YEAR1  which is smaller than YEAR2  
        hi_year_hi       DATE;   -- the smallest full year related to YEAR1  which is higher than YEAR2  
        year_diff        NUMBER;
        leap_help        BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
        leap_count       NUMBER := 0;      -- how many days to add to fix leap issue
        date_not_valid EXCEPTION;
        PRAGMA exception_init ( date_not_valid,-1839 );
    BEGIN
        IF
            year1 > year2
        THEN
            RETURN difference(year2,year1);
        ELSE
            year2_year := to_number(TO_CHAR(year2,'YYYY') );
            hi_year_lo := next_real_date(TO_CHAR(year2_year,'0000'),year1);
            hi_year_hi := next_real_date(TO_CHAR(year2_year + 1,'0000'),year1);
      
      -- how many days are in the last year to take care of
            days_lastyear := hi_year_hi - hi_year_lo;
            fract_lastyear := ( year2 - hi_year_lo ) / days_lastyear;

            year_diff := to_number(TO_CHAR(hi_year_lo,'YYYY') ) - to_number(TO_CHAR(year1,'YYYY') );

            RETURN year_diff + fract_lastyear;
        END IF;
    END difference;

    FUNCTION addition (
        year1   DATE DEFAULT SYSDATE,
        diff    NUMBER
    ) RETURN DATE AS

        year_year        NUMBER;
        year_diff        NUMBER;
        days_lastyear    NUMBER; -- how many days the last year has
        hi_year_lo       DATE;   -- the highest full year related to YEAR1  which is smaller than YEAR2  
        hi_year_hi       DATE;   -- the smallest full year related to YEAR1  which is higher than YEAR2  
        lo_year_lo       DATE;   -- a temp "lowest date" - only to keep the calculation somehow readable
        days_jump_back   NUMBER := 15; -- a number bigger than "365 + (15-4)" - google gregorian calendar reform 1582 & 4 October 1582 
        days_offset      NUMBER;
        leap_help        BOOLEAN := false; -- do we hit a leap-year issue and need to sort out things?
        leap_count       NUMBER := 0;      -- how many days to add to fix leap issue
        date_not_valid EXCEPTION;
        PRAGMA exception_init ( date_not_valid,-1839 );
    BEGIN
        IF
            diff < 0
        THEN
          -- first let's substract only the fraction of diff
            year_year := to_number(TO_CHAR(year1,'YYYY') );
            hi_year_lo := next_real_date(TO_CHAR(year_year - 1,'0000'),year1);
            days_lastyear := year1 - hi_year_lo;
            hi_year_hi := hi_year_lo + ( days_lastyear * ( diff - trunc(diff) ) );
          -- now the easy part - years
            RETURN next_real_date(TO_CHAR(to_number(TO_CHAR(hi_year_hi,'YYYY') ) + trunc(diff) + 1,'0000'),hi_year_hi);
          -- trunc(diff)+1 is required, as we substracted "-1" in the calculation of "hi_year_lo" above already

        ELSIF diff = 0 THEN
            RETURN year1;
        ELSE
            year_year := to_number(TO_CHAR(year1,'YYYY') ) + trunc(diff);
            hi_year_lo := next_real_date(TO_CHAR(year_year,'0000'),year1);
            hi_year_hi := next_real_date(TO_CHAR(year_year + 1,'0000'),year1);
-- 
            days_lastyear := hi_year_hi - hi_year_lo;
            RETURN hi_year_lo + ( days_lastyear * ( diff - trunc(diff) ) );
        END IF;
    END addition;

END bx_year_calc;