How to work around ORA-38338: incorrect ILM policy scope for row-level ADO policies

When adding an ADO policy on a table, like this:

CREATE TABLE TEST_TABLE1
(
  col1 NUMBER(38),
  col2 NUMBER(38),
  col3 DATE
)
NOCOMPRESS 
TABLESPACE USERS
ILM ADD POLICY ROW STORE COMPRESS ADVANCED 
ROW AFTER 1 DAY OF NO ACCESS;

oracle returned

ORA-38338: incorrect ILM policy scope

This policy is not valid for row-level ADO policy. The only valid option is

ILM ADD POLICY ROW STORE COMPRESS ADVANCED 
ROW AFTER X DAY OF NO MODIFICATION;

How to deinstall an old oracle installation using oui

You can use the following command to remove an obsolete oracle software installation from the central inventory:

$ORACLE_HOME/oui/bin/runInstaller -silent -deinstall REMOVE_HOMES={"/u01/oracle/product/11204"}

Remember to clean out the physical files with an appropriate operating system command.

The above method is an alternative to the method outlined in this post.

Parameters that can be removed after upgrade to 12.2

From Information For Parameters _upgrade_optim and _upgrade_capture_noops (Doc ID 2182783.1):


Below parameters gets added after upgrade to 12.2:

_upgrade_optim = FALSE
_upgrade_capture_noops = FALSE

These parameters are part of upgrade optimizations. They do not have any effect on upgrade currently.

Since these parameters does not have any impact, it can be removed safely after upgrade to 12.2.


Can be removed, like this:

echo "alter system reset \"_upgrade_optim\" scope=spfile SID='*';" | sqlplus / as sysdba
echo "alter system reset \"_upgrade_capture_noops\" scope=spfile SID='*';" | sqlplus / as sysdba

How to load a plan from the AWR into the SMB

To load a plan found in an AWR snapshot into the SMB, you need to add it to an STS first, then load it into the SMB.
Here is how:

1. create an empty STS:

exec dbms_sqltune.create_sqlset(sqlset_name=>'mysts');

2. Note the snapshots where your plan was recorded, and add the plans to your STS:

DECLARE
  my_cur  dbms_sqltune.sqlset_cursor;
BEGIN
  OPEN my_cur FOR
     SELECT VALUE(x)
     FROM   TABLE( dbms_sqltune.select_workload_repository(begin_snap=>5840, 
                                                           end_snap=>5841, 
                                                           basic_filter=>'sql_id = ''45wmakdh9ak9s''')) x;

     dbms_sqltune.load_sqlset(sqlset_name => 'mysts', populate_cursor => my_cur);
END;
/

You can now check your STS for its contents:

select sqlset_name,
     sql_id,
     plan_timestamp,
     parsing_schema_name "schema",
     plan_hash_value,round(elapsed_time/1000000) "duration in seconds",
     cpu_time,
     buffer_gets,
     disk_reads,
     executions
from   dba_sqlset_statements
where  sqlset_name = 'mysts'
;

SQLSET_NAME	SQL_ID	PLAN_TIMESTAMP	schema	PLAN_HASH_VALUE	duration in seconds	CPU_TIME	BUFFER_GETS	DISK_READS	EXECUTIONS
mysts	45wmakdh9ak9s	11.09.2018 15:22:13	SH	3827183161	0	0	0	0	0
mysts	45wmakdh9ak9s	06.09.2018 16:45:26	SH	4026264003	579	85550842	24062750	101808	3278857

We can see from the output that there are actually two different plans in the STS now, one with plan_hash_value=3827183161 and another one with plan_hash_value=4026264003.

In my case I wanted only the one with plan_hash_value=3827183161.

3. Finallly, load the contents of your STS into your SMB:

VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
                    sqlset_name=>'mysts', basic_filter=>'plan_hash_value=3827183161', sqlset_owner=>'SYS');

print :cnt;

How to drop an STS

exec dbms_sqltune.drop_sqlset ('mysts');

Yet another example on how to create and populate an STS

Here is how to create an STS and populate it with SQL statements matching a particular SQL ID:

exec dbms_sqltune.create_sqlset(sqlset_name=>'mysts');

DECLARE
  my_cur  dbms_sqltune.sqlset_cursor;
BEGIN
  OPEN my_cur FOR
     SELECT VALUE(x)
     FROM   TABLE( dbms_sqltune.select_cursor_cache(basic_filter=>'sql_id = ''45wmakdh9ak9s''')) x;

     dbms_sqltune.load_sqlset(sqlset_name => 'mysts', populate_cursor => my_cur);
END;
/

Check the contents of the STS:

set lines 200

col name format a20
col owner format a20
col created format a30
col statement_count format 999999999999
select name,owner,created,statement_count
from dba_sqlset
where name = 'mysts';

And check the details of the statements packed in the STS, like this:

select sql_id,plan_timestamp,parsing_schema_name,plan_hash_value,round(elapsed_time/1000000) "duration in seconds",cpu_time,buffer_gets,disk_reads,executions
from   dba_sqlset_statements
where  sqlset_name = 'mysts'
fetch first 3 rows only;

SQL_ID	PLAN_TIMESTAMP	PARSING_SCHEMA_NAME	PLAN_HASH_VALUE	duration in seconds	CPU_TIME	BUFFER_GETS	DISK_READS	EXECUTIONS
45wmakdh9ak9s	18.09.2018 23:48:21	SCOTT	4026264003	7718	1331447472	336095417	828189	40286996

How the DBTIMEZONE is set in an Oracle database

A customer wanted me to look into why his two databases running the exact same application code were returning different time zones.

select name, created,(select dbtimezone from dual) "dbtimezone"
from v$database;

NAME	CREATED	dbtimezone
testdb1	25.04.2016 16:40:12	+02:00

NAME	CREATED	dbtimezone
testdb2	08.11.2017 14:07:51	+01:00

The os-command date +"%Z %z" returned CEST +0200 on both servers. Why the difference?

It didn't take long to find a good answer from Oracle about this topic.


Oracle states:

The database timezone is the time zone of the location where the database is installed

and

Use the SET TIME_ZONE clause to set the time zone of the database. If you do not specify the SET TIME_ZONE clause, then the database uses the operating system time zone of the server.

And also, from the document "Timestamps & time zones - Frequently Asked Questions (Doc ID 340512.1)":

If not specified with the CREATE DATABASE statement, the database time zone defaults to the server's O/S time zone offset. Note that if your server runs in a time zone affected by DST, then the database time zone would default to whatever the current offset is at the time of database creation, so you can have databases created with the same script on the same server with a different database time zone.

In Norway, we use DST, more commonly referred to as "summertime" and "wintertime". The first datbase was installed April 25th, after the server had switched to summertime. The second database was installed November 8th, after the server had switched to wintertime.

Oracle continues with the following about the database time zone:


The database time zone is not as important as it sounds. First of all it does not influence functions like sysdate, or systimestamp.

These function take their contents (date and time, and in the case of systimestamp also time zone) completely from the OS without any "Oracle" intervention.

and

A common misconception is that the database timezone needs to be "your" timezone. This is NOT true.
The database timezone has NO relation with "where" the server is located.
There is NO advantage whatsoever in using your timezone or a named timezone as database timezone.
The best setting for dbtimezone is simply +00:00 (or any other OFFSET like -09:00, +08:00, ...),

if your current dbtimezone value is an OFFSET then please leave it like it is.

How to use the purge command in adrci

In Version 11gR1 Oracle introduced the Active Diagnostic Repository (ADR), which serves as a common repository for all log files, tracefiles, incidents etc, that the database produces.

ADR has a "short" and a "long" purging policy. The default values are:

• 720 hours (30 days) for SHORTP_POLICY
• 8760 hours (356 days) for LONGP_POLICY

Each policy controls different types of content:

These files are controlled by the value of LONGP_POLICY:

ALERT
INCIDENT
SWEEP
STAGE
HM

These files are controlled by the value of SHORTP_POLICY:

TRACE
CDUMP
UTSCDMP
IPS

Sometimes it may be necessary to manually purge the ADR. The following example will delete all trace files older than 48 hours:

adrci> purge -age 48 -type trace

Other examples (2160 hrs = 90 days):

purge -age 2160 -type alert
purge -age 2160 -type incident
purge -age 2160 -type cdump
purge -age 2160 -type stage
purge -age 2160 -type sweep
purge -age 2160 -type hm

The policies can be adjusted according to need by using the following commands :

adrci> show control

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:

*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY
-------------------- -------------------- --------------------
597879840            720                  8760

Set a new policy (2 and 3 days, respectively):

set control (SHORTP_POLICY = 48)
set control (LONGP_POLICY = 72)

An example from one of my databaes:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
17044                ORA 603                                                     2013-09-26 15:37:30.870000 +02:00
17043                ORA 600 [kqlidchg0]                                         2013-09-26 15:37:27.255000 +02:00
17042                ORA 603                                                     2013-09-26 15:35:45.918000 +02:00
17041                ORA 600 [kqlidchg0]                                         2013-09-26 15:35:42.239000 +02:00

45 rows fetched

Remove incidents that are older than 90 days:

adrci> purge -age 2160 -type incident

Another check of the incident list shows that the number has been reduced:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/sergat/sergat:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
24 rows fetched

How to work around ORA-14402: updating partition key column would cause a partition change

The error

ORA-14402: updating partition key column would cause a partition change

occurs when you try to update the value of a partition key column.
This will lead to an implicit INSERT operation.
The ROWID of the row was assigned when the row was first inserted, and it doesn't change. Therefore, by default, Oracle will reject any DML that will cause a rowid to be changed.

Example:

update sales.quarterly_sales
set sold_month = '2016-02'
WHERE unique_sales_id = '3d6fb1ad-243d-4435-97d8-9ca4bfde3ab5';

Since the partition key is the column "sold_month", it will return ORA-14402.

The workaround is to allow row movement for the table:

alter table sales.quarterly_sales enable row movement;

You can now update the row.

You may want to revoke the permission for the rows in the table to change rowid when you are finished:

alter table sales.quarterly_sales disable row movement;

Further reading:
Doc ID 1518567.1 "FAQ: Row Movement Common Questions and Problems on Partitioned Tables"
Doc ID 236191.1: "Updating Partition Key Column Fails with ORA-14402"