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

 

Advertisements

ORA-01203: wrong incarnation of this file – wrong creation SCN

Like in everyone else’s life, this kind of “happiness” is happening. This is what I got on one of my test STANDBY databases while I was trying to start the Recovery Manager:

Managed Standby Recovery starting Real Time Apply
Read of datafile '/u01/oradata/DB/filename.dbf' (fno 113) header failed with ORA-01203
Rereading datafile 113 header failed with ORA-01203
MRP0: Background Media Recovery terminated with error 1110
Errors in file /u01/app/oracle/diag/rdbms/standby/DB/trace/DB_pr00_13353.trc:
ORA-01110: data file 113: '/u01/oradata/DB/"'
ORA-01122: database file 113 failed verification check
ORA-01110: data file 113: '/u01/oradata/DB/filename.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1110 exception

These are the steps I used to make it work:

On STANDBY I have removed the file in trouble “filename.dbf”. Because I had this problem for about a week and the recovery manager was down…some other (new) datafiles were not created so the standby control file was outdated. I have recreated the standby controlfile on the PRIMARY and send it to STANDBY:

alter database create standby controlfile as '/tmp/stdby_controlfile_DB.ctl' reuse;

I’ve stopped the STANDBY and mounted it using the NEW / updated standby controlfile.

Now, I used RMAN on the PRIMARY and connected on the auxiliary STANDBY to restore the “filename.dbf” which was corrupted and also the datafiles that were missing like this:

[oracle@dbserver]$ rman target / auxiliary sys/password@DB_STANDBY

Recovery Manager: Release 11.2.0.3.0 - Production on Tue May 16 09:03:27 2017

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

connected to target database: DB 
connected to auxiliary database: DB (not open)

RMAN> backup as copy datafile 116 auxiliary format '/u01/oradata/DB/filename.dbf';

Starting backup at 16-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6309 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00116 name=/u01/oradata/DB/filename.dbf
output file name=/u01/oradata/DB/filename.dbf tag=TAG20170516T090344
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 16-MAY-17

RMAN> exit

 

Now on the STANDBY site, you just need to start the Recovery Manager and you’re done:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

 

Agent has disabled full monitoring of this target. Only target Status is being monitored – EM12c

I ran into this “exception”: Agent has disabled full monitoring of this target. Only target Status is being monitored”:

12c_error1

After a couple of researches, I’ve found that this is a Bug and therefor I needed to follow Doc ID 2207479.1.

First you need to make sure that you’re patching the right ORACLE_HOME:

export ORACLE_HOME=/u01/app/oracle/product/agent12cR5/core/12.1.0.5.0

[server1 25104978]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.1.0.10.4
Copyright (c) 2017, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/agent12cR5/core/12.1.0.5.0
Central Inventory : /u01/app/oracle/oraInv
 from : /u01/app/oracle/product/agent12cR5/core/12.1.0.5.0/oraInst.loc
OPatch version : 11.1.0.10.4
OUI version : 11.1.0.13.0
Log file location : /u01/app/oracle/product/agent12cR5/core/12.1.0.5.0/cfgtoollogs/opatch/opatch2017-02-14_13-44-41PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/agent12cR5/core/12.1.0.5.0/cfgtoollogs/opatch/lsinv/lsinventory2017-02-14_13-44-41PM.txt

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

EM Platform (Agent) 12.1.0.5.0
There are 1 products installed in this Oracle Home.


Interim patches (3) :

Patch 10203435 : applied on Thu Jun 11 00:41:03 GMT+00:00 2015
Unique Patch ID: 15915936.1
 Created on 7 Feb 2013, 18:06:13 hrs PST8PDT
 Bugs fixed:
 10203435

Patch 17591700 : applied on Thu Jun 11 00:41:01 GMT+00:00 2015
Unique Patch ID: 16995298
 Created on 21 Nov 2013, 11:54:16 hrs PST8PDT
 Bugs fixed:
 16479818, 6895422, 13583799

Patch 17018143 : applied on Thu Jun 11 00:40:56 GMT+00:00 2015
Unique Patch ID: 17273347
 Created on 7 Feb 2014, 21:45:46 hrs UTC
 Bugs fixed:
 17018143

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

OPatch succeeded.

$ORACLE_HOME/OPatch/opatch napply

And on EM repository, don’t forget to execute the following script:

SYSMAN@EMREP> @clean_event_bug25097559.sql
cleaning up entries for seq id: ############################
clear_stateless_event code completed

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89

Commit complete.

Elapsed: 00:00:00.01
SYSMAN@EMREP>

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.

 

Initial setup required for the execution of installer validation failed.

One of my colleagues asked me to deploy the Oracle 12c client 32 bits on Windows 64 bits and I got the error from above right after the I chose the type of the installation.

error2

As per above image, the cause of it is – ” Failed to access temporary location”

I tried almost everything what I could find on Google…..from TEMP not correctly exported, the name of the user used, hidden share…..everything was “like in the book”….but was still failing with the same error. However, I have found this command:

setup.exe -ignorePrereq -J"-Doracle.install.client.validate.clientSupportedOSCheck=false"

and a miracle will happen.

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.

12cR1 not being able to connect “as / sysdba” locally

This came as a surprise when I came back from vacation to discover that all of a sudden can’t do this on my Linux box sqlplus / as sysdba. I was getting ORA-01017. Something like this:

[oracle@dbtest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 3 09:59:29 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
 ORA-01017: invalid username/password; logon denied

Enter user-name: ^C
 [oracle@dbtest dbs]$

In my case, one of my colleagues, took the liberty to set TWO_TASK variable. After removing/unsetting the variable, all came to my desired situation.

As you may know, this TWO_TASK variable overwrites the ORACLE_SID & ORACLE_HOME variables and prohibits the local connections and is letting you to use the sqlnet path.