Alex Zaballa - DBA Oracle - ex-Oracle ACE Director, OCM, OCA, OCP, OCE, OCS

sexta-feira, 7 de fevereiro de 2014

UTL_DBWS - Webservices

Download do arquivo dbws-callout-utility-10131.zip

cd $ORACLE_HOME/sqlj/lib

loadjava -u sys/password -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb11.jar

execute dbms_java.grant_permission('','SYS:java.util.PropertyPermission','http.proxySet','write');
execute dbms_java.grant_permission('','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
execute dbms_java.grant_permission('','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
execute dbms_java.grant_permission('','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
execute dbms_java.grant_permission('','SYS:java.lang.RuntimePermission','getClassLoader','');
execute dbms_java.grant_permission('','SYS:java.net.SocketPermission','*','connect,resolve');
execute dbms_java.grant_permission('','SYS:java.util.PropertyPermission','*','read,write');
execute dbms_java.grant_permission('','SYS:java.lang.RuntimePermission','setFactory','');

Exemplos de utilização podem ser vistos em:

How to Verify the Installation of UTL_DBWS Package in Oracle Database 11g (Doc ID 1166206.1)

Using UTL_DBWS to Make a Database 11g Callout to a Document Style Web Service (Doc ID 841183.1)

UTL_DBWS - Consuming Web Services in Oracle 10g

SCHEMA VALIDATION UTILITY

09:11 No comments

SCHEMA VALIDATION UTILITY (SVU) (Doc ID 286619.1)

O primeiro passo é atualizar o OPATCH (Patch 6880880).

Após isso verificar a versão do seu portal e aplicar o patch correto.

No meu caso é o Patch 9718908.

Aplicando o Patch:

/u01/app/oracle/product/as10g/MidTier/OPatch/opatch apply /tmp/zab/svu/14806295 -pre -s portal -p ***** -c 192.168.244.130:1521:sigfedb -opatch_pre_end -post -s portal -p ****** -c sigfedb

Baixar o arquivo svu_rept.sql

sqlplus portal/*****

SQL> @svu_rept.sql

Enter SVU Mode: REPORT

Enter SVU Type: ALL

Enter log file (and path): /tmp/log.log

250 rows selected.

Flush information that has been reported

PL/SQL procedure successfully completed.

Upgrade do Oracle Beehive versão 2.0.1.4.0 para 2.0.1.8.0

11:11 No comments

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.

Gathering Statistics About Oracle Beehive Data and Code Schemas After Upgrading

[oracle@portal ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Feb 1 12:12:14 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 > exec DBMS_STATS.GATHER_SCHEMA_STATS('BEE_CODE_02012014');

Procedimento de PL/SQL concluido com exito.

SQL > exec DBMS_STATS.GATHER_SCHEMA_STATS('BEE_DATA');

Procedimento de PL/SQL concluido com exito.

SQL>

Upgrade Inventory Version

cd /backup/zaballa/patch_beehive/14372656/Beehive_20180/custom/script

unzip BeekeeperVersionUpgradeTo20180.zip

unzip BeehiveVersionUpgradeTo20180.zip

cd BeehiveVersionUpgradeTo20180

chmod +x BeehiveUpVerTo20180JdkTo1502402.sh

./BeehiveUpVerTo20180JdkTo1502402.sh /u01/app/oracle/product/beehive

Silent Beehive Inventory Upgrade to Version 2.0.1.8.0 Started...

The installation of Oracle Beehive Release 2 was successful.

Please check '/u01/app/oraInventory/logs/silentInstall2014-02-01_12-17-55PM.log' for more details.

cd BeekeeperVersionUpgradeTo20180

chmod +x BeekeeperUpVerTo20180JdkTo1502402.sh

./BeekeeperUpVerTo20180JdkTo1502402.sh /u01/app/oracle/product/beekeeper/

Silent Beekeeper Inventory Upgrade to Version 2.0.1.8.0 Started...

The installation of Oracle Beekeeper was successful.

Please check '/u01/app/oraInventory/logs/silentInstall2014-02-01_12-19-42PM.log' for more details.

Oracle ACE

12:43 1 comment

Hoje foi um dia especial, quando acordei dei uma olhada nos emails (como de costume) e o primeiro que vi foi que eu havia me tornado Oracle Ace :)

A melhor definição sobre o que é um Oracle Ace que eu li, foi do Eduardo Legatti:

Esse título é concedido pela Oracle às pessoas que são reconhecidas pela comunidade técnica Oracle, não só pela proficiência técnica, mas também por serem, em especial, evangelistas, entusiastas e disseminadores de conhecimento das tecnologias Oracle. Esta nomeação envolve um processo criterioso no qual o candidato é avaliado sobre seus conhecimentos na tecnologia Oracle, certificações e principalmente sobre a qualidade e frequência de suas contribuições para a comunidade Oracle no seu país, cidade e até pelo mundo.

Gostaria de agradecer a todos que tornaram isso possível, à Jupiter, ao pessoal do GPO que me incentivou a escrever, ao pessoal do OTN, a minha esposa pela paciência com as minhas ausências estudando ou escrevendo, aos outros Oracle ACEs que foram minha inspiração, ao Daniel Da Meda que fez a minha nomeação e principalmente a DEUS por tudo que ele tem me proporcionado.

Muito Obrigado

Recuperando a senha do Oracle Portal 11g

12:45 No comments

How to Retrieve the Portal Schema Password in Portal 11g? (Doc ID 843978.1)

cd $ORACLE_HOME/common/bin

./wlst.sh

connect()

informar o username, password e URL.

listCred(map="oracle.portal.dads", key="/pls/portal")

Script para inicializar o OID após o banco de dados

15:50 No comments

Making Applications Highly Available Using 11gR2 Grid Infrastructure (Doc ID 1105489.1)

/u01/app/11.2.0.4/grid/bin/crsctl add resource OPN -type ora.local_resource.type -attr "AUTO_START=always,ACTION_SCRIPT=/u01/app/oracle/scripts/opn.sh"

/u01/app/11.2.0.4/grid/bin/crsctl modify resource OPN –attr "START_DEPENDENCIES=hard(ora.oimtrei.db)"

/u01/app/11.2.0.4/grid/bin/crsctl modify resource OPN –attr "STOP_DEPENDENCIES=hard(ora.oimtrei.db)"

/u01/app/11.2.0.4/grid/bin/crsctl modify resource OPN -attr "CHECK_INTERVAL=120"

DRG-10513: index WWSBR_TOPIC_CTX_INDX metadata is corrupt

18:39 No comments

Caso ocorra o erro abaixo na migração do Portal, utilizando o banco 11.2.0.4:

###
### ERROR: WWU-01013: Upgrade terminated with the following errors:
### 10465 : ERROR at line 1:
### 10466 : ORA-20000:
### 10467 : ORA-06512: at "PORTAL.WWPOF", line 440
### 10468 : ORA-06512: at line 152
### 10469 : ORA-06510: PL/SQL: unhandled user-defined exception
### 10470 : ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 164
### 10471 : ORA-06512: at "PORTAL.WWV_CONTEXT", line 2283
### 10472 : ORA-06510: PL/SQL: unhandled user-defined exception
### 10473 : ORA-06512: at "PORTAL.WWERR_API_EXCEPTION", line 71
### 10474 : ORA-06512: at "PORTAL.WWV_CONTEXT", line 2231
### 10475 : ORA-29874: warning in the execution of ODCIINDEXALTER routine
### 10476 : ORA-29960: line 1,
### 10477 : DRG-10595: ALTER INDEX WWSBR_TOPIC_CTX_INDX failed
### 10478 : DRG-50857: oracle error in drixmd.PurgeKGL
### 10479 : ORA-20000: Oracle Text error:
### 10480 : DRG-10513: index WWSBR_TOPIC_CTX_INDX metadata is corrupt
### 10481 : ORA-30576: ConText Option dictionary loading error
### 10482 : DRG-50610: internal error: kglpurge []
### 10492 : # ERROR: Creating missing Text indices
### 10493 : # ERROR: User-Defined Exception
### 10501 : ### ERROR: Exception Executing upg/common/posttrge/crtxtind.sql

Rodar os seguintes scripts para recriar os índices antes do upgrade:

/u01/app/oracle/product/as10g/MidTier/portal/admin/plsql/wws/ctxdrind.sql

/u01/app/oracle/product/as10g/MidTier/portal/admin/plsql/wws/ctxcrind.sql

Aplicar o patch e seguir as instrucões:

Oracle Text release 11.2.0.4.0 mandatory Patches (Doc ID 1608029.1)

ORA-04063: package body "ODS.TSPURGE" has errors

14:54 3 comments

If you are creating the Oracle Internet Directory schema in an Oracle database using RCU, you may encounter the following error messages:

ORA-04063: package body "ODS.TSPURGE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "ODS.TSPURGE"
ORA-06512: at line 3

To work around this issue:

Stop RCU and drop any Oracle Internet Directory schemas already created. Refer to "Dropping Schemas" in Oracle Fusion Middleware Repository Creation Utility User's Guidefor instructions.

Log into the database using SQL*Plus and run the following command:

 grant execute on sys.dbms_job to PUBLIC and grant execute on dbms_reputil to PUBLIC

Run RCU again and create the schemas.

http://docs.oracle.com/cd/E28389_01/doc.1111/e14775/install.htm

XATRANS Views are not installed on this Database

14:34 No comments

@?/javavm/install/initxa.sql
@?/rdbms/admin/xaview.sql
grant select on V$XATRANS$ to public;

add_external_contact to Oracle Beehive

09:23 No comments

beectl add_external_contact --family_name Silva --given_name Joao --display_name "Joao da Silva" --address BUSINESS_1:mailto:joao.silva@email.gv.ao --scope enpr=Jupiter

beectl list_external_contacts

Para adicionar em um grupo:

beectl list_groups --entity_format id

arquivo.xml:

beectl modify_group --file arquivo.xml

Validating the input XML file.
Successfully validated the XML File.
For details see the management and core log files.
Modifying groups in batches. This may take a while...

Successfully modified 1 groups, total 1

Summary:

Total number of groups: 1
Successfully modified: 1
Partially modified(usability, visibility not set): 0
Failed to modify: 0

beectl list_groups --name dbas.sigfe --show ALL

STACKX User Guide (Doc ID 362791.1)

08:04 No comments

STACKX

Core / Stack Trace Extraction Tool - Version 1.2

Este utilitário, permite a análise de arquivos de Core Dump.

TECH: Getting a Stack Trace from a CORE file on Unix (Doc ID 1812.1)

How to obtain a Core Dump file from a Web Cache crash (Doc ID 263660.1)

08:03 No comments

Em caso de problemas no WEBCACHE, é possível gerar arquivos de CORE DUMP para análise:

Release 9.0.4 (10g) to 10.1.2.x:

$ opmnctl startproc process-type=WebCache WCCore=true

Client Address Information in HTTP Reverse Proxy Scenarios

07:00 No comments

Doc ID -263943.1

Add in httpd.conf:

UseWebCacheIp ON

and change this line

LogFormat "%h %l %u %t \"%r\" %>s %b" common

for

LogFormat "%{X-Forwarded-For}i %l %u %t \"%r\" %>s %b" common

Can't locate object method "header_in" via package "Apache2::RequestRec" AdjustRemoteAddrAccordingToHeader.pm line 9

12:36 No comments

Ao migrar o Oracle Portal para versão 11G, o script AdjustRemoteAddrAccordingToHeader.pm começou a apresentar o seguinte erro:

Can't locate object method "header_in" via package "Apache2::RequestRec" at /u01/app/oracle/product/Middleware/asinst_1/config/OHS/ohs1/AdjustRemoteAddrAccordingToHeader.pm line 9

Para resolver este problema, basta acrescentar o seguinte:

use Apache2::compat;

AdjustRemoteAddrAccordingToHeader.pm:

package Apache::AdjustRemoteAddrAccordingToHeader;

use Apache2::compat;

sub handler
{
my $r = shift;
my $configuredValue = $r->dir_config ("UseIPFromHeader");
if ($configuredValue)
{
my $ip = $r->header_in($configuredValue);
if ($ip)
{
$r->connection->remote_ip($ip);
}
}
else
{
print STDERR "AdjustRemoteAddrAccordingToHeader: PerlSetVar UseIPFromHeader is not set, so I did not do anything\n";
}

# Adjust for Oracle HTTP Server and REMOTE_ADDR
# Remove this line if not necessary
$r->header_in("ClientIP", $r->connection->remote_ip());

return OK;
}

return 1;

httpd.conf:

PerlRequire /u01/app/oracle/product/Middleware/asinst_1/config/OHS/ohs1/AdjustRemoteAddrAccordingToHeader.pm
PerlPostReadRequestHandler Apache::AdjustRemoteAddrAccordingToHeader
PerlSetVar UseIPFromHeader X-Forwarded-For

Recommended Patches for Oracle Portal 11g (Doc ID 1443292.1)

13:09 No comments

Atualizar a versão do OPATCH 11.1.1x --> Patch:6880880

Aplicar o Patch:15844859 - MLR: MANDATORY PATCH FOR PORTAL 11G PATCHING V3 --> mesmo sendo da versão 11.1.1.4 ele é obrigatório para aplicar os patchs no 11.1.1.7.0

O Portal continua na versão 11.1.1.6, então mesmo no 11.1.1.7, deve-se aplicar os patchs abaixo:
Portal Repository Patch 15997732 - Generic Platform
Portal Repository Patch 13938784 - Generic Platform

Atualizar o WEBCACHE:

Oracle Web Cache 11.1.1.7.0
WebCache 11.1.1.7 Oct 2013 CPU Patch 17306880 or higher

Documentos interessantes:
Unable To Apply Oracle Portal 11g Patches Using OPatch Getting Too Many Arguments And OS Undefined Variable (Doc ID 1465506.1)

Error 1:
=======
opatch: line 386: [: too many arguments
- This is caused by using the double quotes " " in the opatch command. The readme incorrectly has these around the -pre section and they should not be there.

Error 2:
========

Please set your ORACLE_HOME to your 10.1.2 midtier installation home.
.....................................................................
OS: Undefined variable.

Command invocation returned Error... 'OS: Undefined variable.
', Return Code = 1

- This is caused by a few issues. Ensure you have the latest 11.1.0.0.0 OPatch version downloaded, and ensure you have the Mandatory Portal 11g Patching Patch which fixes issues with the patching tool itself.
Reference: Note 1443292.1 Recommended Patches for Oracle Portal 11g
- That patch applies to all Portal 11g versions.

UKOUG - TECH13

14:19 2 comments

#ukoug_tech13 – Impressions

Na primeira semana de Dezembro de 2013, estive em Manchester para a conferência TECH13 do UKOUG.

Realmente fiquei impressionado com o nível técnico e a organização do evento.

Finalmente conheci pessoalmente alguns grandes nomes quando se trata de Oracle:

Bjoern Rost
Heli Helskyaho

E o Tim Hall do oracle-base.com

http://www.oracle-base.com/blog/2013/12/03/is-this-the-most-committed-ukoug-tech13-attendee/

Fiz o credenciamento e comecei a participar do evento no domingo 01/12 (Super Sunday)

A primeira palestra do dia foi com a lenda Jonathan Lewis - Compression: Index, basic and OLTP

A segunda foi com James Morle (ACE Director) - Optimal Oracle Configuration for Efficient Table Scanning

A terceira foi com Frits Hoogland (ACE Director) da Enkitec - About Multiblock Reads

Ele compartilhou os resultados de sua pesquisa de 2 anos sobre leituras no Oracle. Muito interessante a demonstração dos eventos de trace do Oracle + strace.

A primeira da Segunda-Feira, foi com Zoran Pavlovic e Maja Veselica - New Security Features in Oracle Database 12c

A segunda foi com Andy Colvin (Oracle ACE) da Enkitec - RMAN in Oracle Database 12c - Top New Features

Fez uma demonstração ao vivo de como utilizar Table Point-in-time Recover.

A terceira foi com Jonathan Lewis e Maria Coglan - Oracle Optimizer Boot camp – 10 Optimizer Tips you Can’t Miss

Foi basicamente a mesma apresentação que eles fizeram no OOW13.

A quarta foi com Joel Goodman - Roundtable - RAC and Grid Infrastructure

Uma discussão muito interessante sobre o 12c.

A quinta foi com Maria Colgan - Introduction to the New Oracle Database In-Memory Option

A sexta foi com Dawid Wojcik do Cern - Cardinality Feedback Tuning Revisited – Adaptive Query Optimization in Oracle 12c

A sétima foi com Tom Kyte - Oracle Optimizer: What’s New in Oracle Database 12c?

Na terça-feira, iniciou com Joze Senegacnik (Oracle Ace Director) - Query Transformations Revisited

A segunda foi com Larry Carpenter da Oracle - Oracle Active Data Guard: Next Generation Data Protection

A terceira, foi novamente com Joze Senegacnik - Opening the Black Box Called “Cost Based Optimizer

A quarta foi novamente com Larry Carpenter da Oracle - Oracle Maximum Availability Architecture Best Practices for Oracle Multitenant

A quinta foi com Tom Kyte - Understanding Optimizer Statistics

A sexta foi com Kyle Hailey (Oracle Ace) da Delphix - Lies, Damned Lies, and I/O Statistics

Na quarta, iniciei com Joel Goodman - Masterclass: Oracle Parallel Execution

A segunda for com Maris Elsins da Pythian - 10 Ways to Improve your RMAN Script

A terceira foi com Martin Bach (Oracle Ace) da Enkitec - Using Cross-Platform Transportable Tablespaces to Reduce Downtime

A quarta foi com Tony Hasler e Jonathan Lewis - Debate: Does Oracle Ignore Hints?

A quinta foi Julian Dyke (Oracle Ace) - Oracle 12c New Features – Picking the Winners

A sexta foi com Cameron Purdy da Oracle - Cloud Application Foundation 12c - Developing and Deploying Java Applications in Modern Data Center Architectures

E o encerramento foi com Thomas Kyte - What I Learned from TECH13

Estou reunindo os links das apresentações e assim que possível colocarei no nosso site de Grupo de Usuários Oracle Angola - GUOA (www.guoa.org).

Até mais.

Alex Zaballa

domingo, 24 de novembro de 2013

12:02 No comments

O GUOA foi oficialmente lançado em Angola!
Entre no site para conferir as fotos do Evento e aproveite para se registrar! (www.guoa.org)

Como recuperar uma package, function ou procedure dropada acidentalmente

15:33 No comments

select object_id,object_name,object_type from dba_objects where object_name = 'OWA_UTIL';

select source
from sys.source$
as of timestamp(sysdate - 1)
where obj#=6298
order by line

Após o upgrade para 11.2.0.4 - unable to delete any rows from table with context index due to error: "PLS-00306: wrong number or types of arguments in call to 'SYNCRN'" (Doc ID 1586704.1)

14:33 No comments

After upgrade to 11.2.0.4 unable to delete any rows from table with context index due to error: "PLS-00306: wrong number or types of arguments in call to 'SYNCRN'" (Doc ID 1586704.1)

connect / as sysdba
alter session set current_schema=CTXSYS;

create or replace procedure syncrn (
ownid IN binary_integer,
oname IN varchar2,
idxid IN binary_integer,
ixpid IN binary_integer,
rtabnm IN varchar2,
srcflg IN binary_integer,
smallr IN binary_integer
)
authid definer
as external
name "comt_cb"
library dr$lib
with context
parameters(
    context,
    ownid ub4,
    oname OCISTRING,
    idxid ub4,
    ixpid ub4,
    rtabnm OCISTRING,
    srcflg ub1,
    smallr ub1
);
/