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"

How to connect to a PDB using jdbc

Before you connect to a PDB using jdbc, make sure you have added the following to your database server's $TNS_ADMIN/listener.ora file:

USE_SID_AS_SERVICE_<listener_nam>=on

Restart or reload the listener. You should now be able to connect to the service_name representing your .

Here is a screenshot from SQuirrel SQL client running on Ubuntu, connecting to a PDB running on a Virtual Box with CentOS and Oracle 12.1:

The entire connection string is

jdbc:oracle:thin:@lx01oric.oric.no:1521:pdbveg1.oric.no

Overview over archivelogs present in the FRA

The following query reveals that my FRA contains archived logs from the last 3 days only (indicated by an "A" - available).

-- Set your NLS_DATE_FORMAT in your session to avoid ORA-01830: date format picture ends before converting entire input string
alter session set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

select trunc(completion_time),status,count(*)
from v$archived_log
where completion_time > to_date('20.06.2018 00:00:00')
group by trunc(completion_time),status
order by 1 desc;

Output:

TRUNC(COMPLETION_TIME)	STATUS	COUNT(*)
29.06.2018	A	37
28.06.2018	A	57
27.06.2018	A	54
27.06.2018	D	1
26.06.2018	D	75
25.06.2018	D	75
24.06.2018	D	53
23.06.2018	D	55
22.06.2018	D	59
21.06.2018	D	58
20.06.2018	D	54

Archive log files prior to these, have been deleted (indicated by a "D") by rman after having been successfully backed up.

How to solve ORA-00069: cannot acquire lock -- table locks disabled

Unfortunately, the only known way out of this is to

1. stop the database
2. start in restricted mode with "startup restrict"
3. enable table locks:

 alter table scott.emp enable table lock;

4. startup the database in normal mode, and you will now be able to aquire locks on the table.

How to fix ORA-02449: unique/primary keys in table referenced by foreign keys after an online redefinition

When attempting to drop the interim table after a successful online redefinition, you may get:

ORA-02449: unique/primary keys in table referenced by foreign keys

This is easy to overlook - the online redef procedure does not disable the foreign key relationship from other tables to your (now) obsolete interim table.

To find these tables and their constraints:

SELECT  TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
FROM    DBA_CONSTRAINTS
WHERE   OWNER = 'SH'
AND     CONSTRAINT_TYPE='R'
AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')

TABLE_NAME	CONSTRAINT_NAME	CONSTRAINT_TYPE	R_CONSTRAINT_NAME	STATUS
INVOICES	TMP$$_INVOICES_FK0	R	TMP$$_SALES_PK0	DISABLED
REFUNDS	TMP$$_REFUNDS_FK0	R	TMP$$_SALES_PK0	DISABLED
REPLENISHMENT	TMP$$_REPLENISHMENT_FK0	R	TMP$$_SALES_PK0	DISABLED

As can be seen from the table above, there are certainly constraints from other tables, pointing to the primary key on the interim table. They are disabled, but nevertheless preventing us from dropping the table.

Let's make sure that each of these "funny named" constraints have a sibling constraint, one that is named correctly after the redefintion:

SELECT TABLE_NAME,CONSTRAINT_NAME,R_CONSTRAINT_NAME,STATUS
FROM DBA_CONSTRAINTS 
WHERE TABLE_NAME in (
                    SELECT  TABLE_NAME
                    FROM    DBA_CONSTRAINTS
                    WHERE   OWNER = 'Sh'
                    AND     CONSTRAINT_TYPE='R'
                    AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')
)
AND CONSTRAINT_TYPE='R'
AND CONSTRAINT_NAME LIKE '%REPLENISHMENT%' OR CONSTRAINT_NAME LIKE '%INVOICES%' OR CONSTRAINT_NAME LIKE '%REFUNDS%'
ORDER BY TABLE_NAME,CONSTRAINT_NAME;

Result:

TABLE_NAME	CONSTRAINT_NAME	R_CONSTRAINT_NAME	STATUS
INVOICES	INVOICES_FK	SALES_PK	ENABLED
INVOICES	TMP$$_INVOICES_FK0	TMP$$_SALES_PK0	DISABLED
REFUNDS	REFUNDS_FK	SALES_PK	ENABLED
REFUNDS	TMP$$_REFUNDS_FK0	TMP$$_SALES_PK0	DISABLED
REPLENISHMENT	REPLENISHMENT_FK	SALES_PK	ENABLED
REPLENISHMENT	TMP$$_REPLENISHMENT_FK0	TMP$$_SALES_PK0	DISABLED

As you can see, the constraints come in pairs and the ones with names starting with TMP$$ are leftovers from the redefinition.

You can safely drop these constraints. Put the following in a .sql script and run it as sysdba:

alter session set nls_language='american';
set lines 200
set pages 0
set heading off
set feedback off
set trimspool on
set verify off
set echo off
spool 7.alter_table.sql

select 'spool 7.alter_table.log' from dual;

SELECT    'alter table '
        || B.OWNER
        || '.'
        || B.TABLE_NAME
        || ' drop constraint '
        || B.CONSTRAINT_NAME
        || ';'
  FROM DBA_CONSTRAINTS A
        FULL OUTER JOIN DBA_CONSTRAINTS B
           ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  WHERE A.OWNER = 'SH'
  AND A.TABLE_NAME='SALES_INTERIM'
  AND B.R_CONSTRAINT_NAME IS NOT NULL;
select 'exit' from dual;

exit

Run the script, and you will have a new script containing the drop-clauses:

alter table SH.INVOICES drop constraint TMP$$_INVOICES_FK0;
alter table SH.REFUNDS drop constraint TMP$$_REFUNDS_FK0;
alter table SH.REPLENISHMENT drop constraint TMP$$_REPLENISHMENT_FK0;

After these statements have been successfully executed, you can go ahead and drop the interim table.

How to solve ORA-14024 during execution of DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

Background: you are redefining a table and have successfully completed the DBMS_REDEFINITION.START_REDEF_TABLE procedure.

As you are executing the next step, DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, you receive the following error:

ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Cause: Your interim table has been created with more partitions than the original table you're trying to redefine.

Verify like this:

select 'original table: ' || MAX(PARTITION_POSITION) "number of partitions"
from dba_tab_partitions where table_name='SALES' 
union
select 'interim table: ' || MAX(PARTITION_POSITION) 
from dba_tab_partitions where table_name='SALES_INTERIM' 
;

number of partitions
interim table: 19
original table: 18

A simple solution here is avoiding to create the indexes as a part of the COPY_TABLE_DEPENDENTS, and create them afterwards instead.

Simply change the directive

copy_indexes=>dbms_redefinition.cons_orig_params

to

copy_indexes=>0

when you execute DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS.

When you're done redefining your table, make sure you recreate the indexes.

If this is not acceptable, you need to add another partition to your original table, so that the missing index partitions will be automatically created.


Update 21.06.2021: I faced a similar challenge today as I was trying to redefine a table with interval range partitioning. As I was testing my redefinition strategy in a test environment first, the table in question had 46 partitions with zero rows. This fact raised ORA-14024: number of partitions of LOCAL index must equal that of the underlying table, and I realized I could quite easily work around the problem by simply dropping the empty partitions. To be safe you drop any partitions that actually had rows in them, analyzed them first:

set lines 200
spool analyze_part.sql
set   trimspool on
set   verify off
set   heading off
set   pages 0
set   echo off
set   feedback off
select 'alter session set nls_language=''american'';' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool analyze_part.log' from dual;
select 'exec DBMS_STATS.GATHER_TABLE_STATS (OwnName=> ''TRACKER'', TabName => ''EVENTS'',partname =>''' || partition_name || ''' granularity => ''PARTITION'';' 
from dba_tab_partitions
where table_name='EVENTS'
and num_rows = 0
and partition_name like 'SYS%';

Run the analyze-script:

sqlplus / as sysdba @analyze_part.sql

Afterwards, the num_rows column for the same partitions should show 0 (zero) rows. In other words, they can be safely dropped.

To generate a "drop partition" script, put the following in an sql-script:

set lines 200
spool drop_empty_part.sql
set   trimspool on
set   verify off
set   heading off
set   pages 0
set   echo off
set   feedback off
select 'alter session set nls_language=''american'';' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool drop_empty_part.log' from dual;

select 'ALTER TABLE ' || table_owner || '.' || table_name ||  ' DROP PARTITION ' || PARTITION_NAME ||  ' UPDATE INDEXES;'
from dba_tab_partitions
where table_name='EVENTS'
and num_rows = 0
and partition_name like 'SYS%';
select 'exit' from dual;
exit

Execut the script:

sqlplus / as sysdba @drop_empty_part.sql

The number of partitions in the original table and the interim table should now match. Now, execute the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS again. You shouldn't see the ORA-14024 this time around :-)