DIA-48322: Relation [INCIDENT] of ADR V[2] incompatible with V[2] tool

I got this issue while using ADRCI tool to maintain the logs. This is how I fixed it:

[oracle@dbserver ~]$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Wed May 31 09:43:29 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes: 
diag/diagtool/user_oracle/host_3933716914_11
diag/clients/user_oracle/host_3933716914_11
diag/rdbms/dbinst/DBINST
diag/tnslsnr/dbserver/listener
adrci> set home diag/diagtool/user_oracle/host_3933716914_11
adrci> purge
DIA-48322: Relation [INCIDENT] of ADR V[2] incompatible with V[2] tool
DIA-48210: Relation Not Found
DIA-48166: error with opening ADR block file because file does not exist [/u01/app/oracle/diag/diagtool/user_oracle/host_3933716914_11/metadata/INCIDENT.ams] [0]

adrci> migrate schema
Schema migrated.
adrci> purge
adrci> quit

 

Advertisements

Database upgrade from 10.2.0.5.0 to 11.2.0.4

The following document is the best one you can start documenting yourself from:

11gr2-upgrade-bp-apr2012-1610082.pdf

From the above document, I followed:

  • Note 251.1: Database Upgrades from 10.2 to 11.2

On that note, on the PLAN tab, I followed:

Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)

In this document you’ll find a bunch of steps to be followed. The most important one is the pre-upgrade one, which you have to run it on the SOURCE database. This script is meant to display back to you a lot of useful information in respect to what needs to be changed/adapted on the source (prior upgrade) or on the target (after the upgrade). The script can be found here:

$ORACLE_HOME/rdbms/admin/utlu112i.sql

If you run the upgrade on the same server as the source database is on, it’s better if you copy this file in a different place like /tmp or HOME folder.

In my case, between other advices, I got the following warnings:

**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin contains 4 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database. The command:
 PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
WARNING: --> JOB_QUEUE_PROCESS value must be updated
.... Your current setting of "20" is too low.

.... Starting with Oracle Database 11g Release 2 (11.2), setting
.... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and
.... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES
.... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were
.... unaffected and would still run. This parameter must be updated to
.... a value greater than 48 (default value is 1000) prior to upgrade.
.... Not doing so will affect the running of utlrp.sql after the upgrade
.
**********************************************************************

As you can see, the first advice should be executed on the TARGET database whereas the last 2 on the SOURCE database.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.
SQL> alter system set job_queue_processes=100 scope=both;

System altered.

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

Now I just run the usual scripts in order to upgrade to 11204 version:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog
conn / as sysdba
STARTUP UPGRADE
SPOOL upgrade11204_DB.log
@catupgrd.sql

-- perform upgrade actions that do not require the database to be in UPGRADE mode
@catuppst.sql

@$ORACLE_HOME/ctx/admin/ctxf102.sql
@utlrp.sql

-- Check for invalid entries:
col comp_name format a80
set linesize 200
set pagesize 2000
SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;

-- Check for invalid objects:
SELECT count(*) FROM dba_invalid_objects;
select owner, object_type, object_name from dba_invalid_objects;

### CPU Patch (if that's the case)

@catbundle cpu apply
@utlrp

In the end the, upgrade was successful. However, I still need to fix the timezone issue which appeared in the upgrade check script in the very first step:

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
 4

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE


SQL> !ls -ltr $ORACLE_HOME/oracore/zoneinfo/time*14*
-rw-r--r-- 1 oracle oinstall 344448 Jul 16 2013 /u01/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezone_14.dat
-rw-r--r-- 1 oracle oinstall 791430 Jul 16 2013 /u01/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezlrg_14.dat

SQL> exec DBMS_DST.BEGIN_PREPARE(14);
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4

DST_SECONDARY_TT_VERSION
14

DST_UPGRADE_STATE
PREPARE


SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/


PL/SQL procedure successfully completed.

SQL> SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1090519944 bytes
Database Buffers 3170893824 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
SQL> spool DST_upgrade.log
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14

DST_SECONDARY_TT_VERSION
4

DST_UPGRADE_STATE
UPGRADE


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1090519944 bytes
Database Buffers 3170893824 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
SQL> SELECT version FROM v$timezone_file;

VERSION
----------
 14

 

Time to upgrade the OPatch

As the title suggest, it’s time to upgrade the OPatch. This is what I got when I wanted to apply a patch (namely 20760982) to a 11.2.0.4 database (linux):

[oracle@server 20760982]$ $ORACLE_HOME/OPatch/opatch apply
 Oracle Interim Patch Installer version 11.2.0.3.4
 Copyright (c) 2012, Oracle Corporation. All rights reserved.
 Oracle Home : /u01/app/oracle/product/11.2.0.4
 Central Inventory : /u01/app/oracle/oraInv
 from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
 OPatch version : 11.2.0.3.4
 OUI version : 11.2.0.4.0
 Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_08-16-47AM_1.log

Verifying environment and performing prerequisite checks...
 Prerequisite check "CheckMinimumOPatchVersion" failed.
 The details are:
 The OPatch being used has version 11.2.0.3.4 while the following patch(es) require higher versions:
 Patch 17478514 requires OPatch version 11.2.0.3.5.
 Patch 18031668 requires OPatch version 11.2.0.3.5.
 Patch 18522509 requires OPatch version 11.2.0.3.5.
 Patch 19121551 requires OPatch version 11.2.0.3.5.
 Patch 19769489 requires OPatch version 11.2.0.3.5.
 Patch 20299013 requires OPatch version 11.2.0.3.5.
 Patch 20760982 requires OPatch version 11.2.0.3.5.
 Please download latest OPatch from My Oracle Support.

UtilSession failed: Prerequisite check "CheckMinimumOPatchVersion" failed.
 Log file location: /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_08-16-47AM_1.log

 

And this is what I have on my server:

[oracle@server 11204]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oracle/oraInv
 from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_08-47-49AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2017-01-09_08-47-49AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.

As you can see, I have an older version of OPatch.

I followed the following steps to fix this:

  1. Download the latest OPatch from support.oracle.com
    p6880880_112000_Linux-x86-64.zip
  2. Make a backup of the actual OPatch and make sure there is no OPatch folder on $ORACLE_HOME folder
    [oracle@server 11.2.0.4]$ cd $ORACLE_HOME
    [oracle@server 11.2.0.4]$ mv OPatch OPatch_old
  3. Unzip the patch into $ORACLE_HOME
 unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME

And this is how it looks now:

[oracle@server 11204]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.15
Copyright (c) 2017, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oracle/oraInv
 from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.15
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_09-52-47AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2017-01-09_09-52-47AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: server
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@server 11204]$

And now trying again the first patch to deploy:

[oracle@server 20760982]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.15
Copyright (c) 2017, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oracle/oraInv
 from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.15
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_09-56-47AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 17478514 18031668 18522509 19121551 19769489 20299013 20760982

Do you want to proceed? [y|n]
y

.........


OPatch succeeded.

So all looks good now.

 

ORA-00354: corrupt redo log block header

Recently I got a problem with one of the ORLs; like the title is suggesting, one of the ORL got corrupted (don’t ask how!) and below is what I did:

ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 8741986 change 136264748341 time 09/06/2016 12:47:59
ORA-00312: online log 4 thread 1: '/u2000/oradata/DB/redo4a.log'

What I had in mind was to bounce the database in mount state and to drop the redo04a.log file and to re-create it. Something like this:

SQL> startup mount
ORACLE instance started.

Total System Global Area 3.2068E+10 bytes
Fixed Size                  2269072 bytes
Variable Size            5905580144 bytes
Database Buffers         2.6105E+10 bytes
Redo Buffers               55242752 bytes
Database mounted.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance DB (thread 1) needs to be archived
ORA-00312: online log 4 thread 1: '/u2000/oradata/DB/redo4a.log'

…and the decision had to be taken:

SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database add logfile group 4 '/u2000/oradata/DB/redo4a.log' size 4G reuse;

Database altered.

SQL> alter database open;

Database altered.

Oracle database migration from Windows to Linux

I just wanted to make a post for this:

I had a task to migrate an oracle database (11.2.0.1) from Windows to Linux. On linux I have 11.2.0.3 software version.

First I did some checks:

First I checked the endian:

On Windows I get this:

SQL> SELECT A.platform_id, A.platform_name, B.endian_format
  2   FROM   v$database A, v$transportable_platform B
  3  WHERE  B.platform_id (+) = A.platform_id;


PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
----------- ------------------------------ --------------
         12 Microsoft Windows x86 64-bit   Little

SQL>

And on Linux I get this:

SQL > SELECT A.platform_id, A.platform_name, B.endian_format
 FROM   v$database A, v$transportable_platform B
WHERE  B.platform_id (+) = A.platform_id;  2    3  

 PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
------------ ------------------------------ --------------
          13 Linux x86 64-bit               Little

Elapsed: 00:00:00.01
SQL > 

As you can see, I’m lucky today as on both platforms the endian is the same (“Little”) which means no conversion needed. What I’m going to do is just a full export and import using data pump.

On Windows:

expdp  \'/ as sysdba\' full=Y directory=DATA_PUMP_DIR dumpfile=DB_full_exp.dmp logfile=DB_full_exp.log

I’m going to copy the export file over to Linux box.

On Linux:

First I’m going to create an empty database and then I’m going to run the import:

impdp   \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=DB_FULL_EXP.DMP exclude=statistics 

I’m getting a lot of ORA-39111 which means that the object exists. This is about the objects which are created already within default schemas like SYS,SYSTEM…. I just ignored them and in the end I compiled everything and all looks good.

sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/utlrp.sql

Shutdown hangs due to E000 processes

I had this several times and these are the possible solutions:

Shutdown hangs while EMON slaves are spawned for notification (Doc ID 1394945.1)

In short, you either apply the patch described on the document or just kill the e000 process. In my case is this:

oracle   10371     1  0 Jan28 ?        00:00:03 ora_e000_TESTDB