Upgrade do Oracle Beehive versão 2.0.1.4.0 para 2.0.1.8.0

Ambiente Atual:

- Beehive 2.0.1.4

- Oracle Database 11.1.0.7 64bits

Documentação para aplicação do Patch:

http://www.oracle.com/technetwork/middleware/beehive/overview/beehive-20180-readme-1970161.html

http://docs.oracle.com/cd/E16671_01/bh.200/e16641/upgrade_overview.htm

Patchs necessários para o Beehive 2.0.1.8 rodar no banco 11.1.0.7:

http://docs.oracle.com/cd/E16671_01/bh.200/e16641/db_req.htm

· 9548753: MERGE REQUEST ON TOP OF 11.1.0.7.0 FOR BUGS 9128529 8499043

· 8537211: MERGE LABEL REQUEST ON TOP OF 11.1.0.7 FOR BUGS 6625210 7694979 7378322

· 8531282: MERGE LABEL REQUEST ON TOP OF 11.1.0.7 FOR BUGS 8352309 7586451

· 8499600: ORA-01555 ERRORS ON SECUREFILES READS EVEN UNDER LOW LOADS/NO UPDATE ACTIVITY

· 8487273: MERGE LABEL REQUEST ON TOP OF 11.1.0.7 FOR BUGS 8214576 7258928

· 8221425: STBH:HP:11G - ORA-04031: UNABLE TO ALLOCATE 912 BYTES OF SHARED MEMORY

· 7707103: RECOMPILATION OF LIBRARY UNIT GIVES END-OF-FILE COMMUNICATION CHANNEL

· 7690421: INVALID TZ VALUE IN TIMEZONE COLUMN PRODUCES ORA-01875

· 7685452: 10.2.0.5.0 : HP-PA : ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [4449], [],

· 7643188: SQL VERSION COUNT REPORTED IN AWR DOES NOT MATCH SQLSTATS FOR 1 SQL ID

· 7500792: HIGH GETS ON "KOKC DESCRIPTOR ALLOCATION LATCH" WITH MORE THAN 400 USERS

· 7313961: MISSING LSFEND IN DRUERE() CALLS ADDED FOR BUG 6499258

· 7311226: PARTITIONED TABLE AND FILTER BY OPTIMIZE_INDEX NOT PICKING UP WHERE IT LEFT OFF

· 7273988: TST&PERF:QUERY PLAN IS VERY BAD IN RDBMS_MAIN_LINUX_080623

· 7156912: ORACLE TEXT INDEXING HANGS ON A HTML DOCUMENT WITH LONG COMMENTS

· 7047984: ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KJBCLOSE:MAS], [3], [1]...

· 6977167: ORA-04092: CANNOT ROLLBACK IN A TRIGGER RECEIVED REQUIRING INSTANCE BOUNCE

· 6782437: QUERY WITH MULTIPLE SUBQUERIES PERFORMING POORLY AFTER 10G UPGRADE

· 6750049: LIBNNZ10.SO DLOPEN FAILED WHEN TRYING TO START ONS (NODEAPPS)

· 6698219: DBMS_LINK ALLOWS PATH > 1024 CAUSING CORRUPT REPOSITORY

· 6355412: JOB CONSUMES 100% CPU FOR EXTENDED PERIODS

· 6263237: TCH11G: ORA-7445 WHILE RUNNING ADPATCH

· 6083201: MERGE STATEMENT WITH XML TYPE FAILS WITH ORA-7445 [FCLOSE()+29]

Backup level 0 do banco de dados:

[oracle@portal /]$ rman target / nocatalog
Recovery Manager: Release 11.1.0.7.0 - Production on Sat Feb 1 09:35:14 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: BEEHIVED (DBID=3636825317)
using target database control file instead of recovery catalog

RMAN> backup as compressed backupset incremental level 0 database plus archivelog;

Starting backup at 01-FEB-14
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
.
.
.

Disable Fast Validation in the Database

ALTER SYSTEM SET "_disable_fast_validate"=TRUE SCOPE=BOTH SID= '*';

Install Patch 7311226 on 11.1.0.7.x

No meu caso, estava faltando apenas este patch.

[oracle@portal 7311226]$ pwd

/backup/zaballa/patch_banco/7311226

[oracle@portal 7311226]$ /u01/app/oracle/product/11.1.0/db/OPatch/opatch apply

Invoking OPatch 11.1.0.6.2

Oracle Interim Patch Installer version 11.1.0.6.2

Oracle Home : /u01/app/oracle/product/11.1.0/db

Central Inventory : /u01/app/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.1.0.6.2

OUI version : 11.1.0.7.0

OUI location : /u01/app/oracle/product/11.1.0/db/oui

Log file location : /u01/app/oracle/product/11.1.0/db/cfgtoollogs/opatch/opatch2014-02-01_10-09-30AM.log

ApplySession applying interim patch '7311226' to OH '/u01/app/oracle/product/11.1.0/db'

OPatch succeeded.

[oracle@portal /]$ sqlplus CTXSYS/******

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Feb 1 10:25:33 2014

Ligado a:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/ctx/admin/drvddl.plb

Corpo de pacote criado.

SQL > connect / as sysdba

Ligado.

SQL > @?/rdbms/admin/utlrp

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2014-02-01 10:25:50

Procedimento de PL/SQL concluido com exito.

Aplicando o Patch no Beehive:

[oracle@portal ~]$ export ORACLE_HOME=/u01/app/oracle/product/beehive
[oracle@portal ~]$ $ORACLE_HOME/OPatch/opatch napply -post -sys_passwd ****** -dbconnstr "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=portal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=beehivedb)))" -first_midtier -opatch_post_end

Invoking OPatch 10.2.0.4.8

Oracle Interim Patch Installer version 10.2.0.4.8
Copyright (c) 2009, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /u01/app/oracle/product/beehive
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.8
OUI version : 10.2.0.5.0
OUI location : /u01/app/oracle/product/beehive/oui
Log file location : /u01/app/oracle/product/beehive/cfgtoollogs/opatch/opatch2014-02-01_10-30-48AM.log

Patch history file: /u01/app/oracle/product/beehive/cfgtoollogs/opatch/opatch_history.txt
.
.
.
Beehive Upgrade Assistant completed successfully

Beehive Upgrade completed

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

Aplicando o Patch no Beekeeper:

[oracle@portal Beehive_20180]$ export ORACLE_HOME=/u01/app/oracle/product/beekeeper
[oracle@portal Beehive_20180]$ $ORACLE_HOME/OPatch/opatch napply
Invoking OPatch 10.2.0.4.8

Oracle Interim Patch Installer version 10.2.0.4.8
Copyright (c) 2009, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /u01/app/oracle/product/beekeeper
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.8
OUI version : 10.2.0.5.0
OUI location : /u01/app/oracle/product/beekeeper/oui
Log file location : /u01/app/oracle/product/beekeeper/cfgtoollogs/opatch/opatch2014-02-01_11-41-09AM.log

Patch history file: /u01/app/oracle/product/beekeeper/cfgtoollogs/opatch/opatch_history.txt
.
.
.
Beehive Upgrade completed

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

Update hasbind File Ownership

[root@portal bin]# chown root hasbind
[root@portal bin]# chmod a+x hasbind
[root@portal bin]# chmod a+s hasbind

Reset Fast Validation in the Database

ALTER SYSTEM RESET "_disable_fast_validate" SCOPE=SPFILE SID='*';

Run Perl Script post_upgrade_db_actions.pl

[oracle@portal db]$ beectl list_schemas --schema_type 2

schema_name: BEE_DATA schema_id: 85 version_id: 2.0.1.8.0 schema_type: 2 status: 4 creation_time: 2009-06-12 09:20:41.0 activation_time: 2009-06-12 09:20:41.0 description: BEE_DATA schema

Verificando o schema que estava ativo:

[oracle@portal db]$ beectl list_schemas --schema_type 1

schema_name: BEE_CODE schema_id: 86 version_id: 1.5.1.1.0 schema_type: 1 status: 6 creation_time: 2009-06-12 09:20:41.0 activation_time: 2009-06-12 09:20:41.0 legacy_time: 2009-10-17 19:40:46.0 deactivation_time: 2010-05-22 11:13:44.0 description: BEE_CODE schema
schema_name: BEE_CODE_02012014 schema_id: 99 version_id: 2.0.1.8.0 schema_type: 1 status: 4 creation_time: 2014-02-01 09:54:39.0 activationready_time: 2014-02-01 10:16:50.0 upgradeready_time: 2014-02-01 10:12:50.0 activation_time: 2014-02-01 10:17:28.0 description: BEE_CODE_02012014 code schema
schema_name: BEE_CODE_10172009 schema_id: 94 version_id: 2.0.1.4.0 schema_type: 1 status: 5 creation_time: 2009-10-17 19:15:59.0 activationready_time: 2009-10-17 19:40:29.0 upgradeready_time: 2009-10-17 19:36:08.0 activation_time: 2009-10-17 19:40:46.0 legacy_time: 2014-02-01 10:17:28.0 description: insert description here

Verificando o schema ativo após o upgrade:

[oracle@portal db]$ beectl list_schemas --schema_type 1 --status 4

schema_name: BEE_CODE_02012014 schema_id: 99 version_id: 2.0.1.8.0 schema_type: 1 status: 4 creation_time: 2014-02-01 09:54:39.0 activationready_time: 2014-02-01 10:16:50.0 upgradeready_time: 2014-02-01 10:12:50.0 activation_time: 2014-02-01 10:17:28.0 description: BEE_CODE_02012014 code schema

Rodando o script de post upgrade:

[oracle@portal ~]$ export ORACLE_HOME=/u01/app/oracle/product/beehive
[oracle@portal ~]$ cd /u01/app/oracle/product/beehive/beehive/db/

[oracle@portal db]$ /u01/app/oracle/product/beehive/perl/bin/perl post_upgrade_db_actions.pl BEE_DATA BEE_CODE_10172009 BEE_CODE_02012014 ******** "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = portal)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = beehivedb)))"
Perl has been invoked from the Beehive Oracle home
Starting to flush old schema event and action queues
connect BEE_DATA/*******@'(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = portal)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = beehivedb)))'

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Feb 1 12:05:57 2014

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

SQL> SQL> SQL> Connected.
SQL> SQL> Rem
SQL> Rem $Header: ocscore/projects/event/src/sql/migrate_old_queue_events_to_new_queue.sql /main/1 2009/03/23 17:54:32 djambula Exp $
SQL> Rem
SQL> Rem migrate_old_queue_events_to_new_queue.sql
.
.
.
Finished attempting to run all post upgrade actions
Event queue migration: Succeeded
Subscription reenabling: Succeeded
Capture changes reenabling: Succeeded
Method monitoring reenabling: Succeeded
Method monitoring cleanup reenabling: Succeeded
Client Applications upload: Succeeded
Client Applications provisioning: Succeeded
Device Profiles upload: Succeeded
Notification Templates upload: Succeeded
Actionable Notification Templates upload: Succeeded
DBMS Jobs stopped: Succeeded
Deactivating old code schema: Succeeded
Final cleanup schema: Succeeded

All postupgrade actions succeeded.