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

terça-feira, 24 de novembro de 2009

Oracle Application Development Tools Statement of Direction: Oracle Forms, Oracle Reports and Oracle Designer

http://www.oracle.com/technology/products/forms/pdf/10g/ToolsSOD.pdf

Invalid Objects After Removing OLAP or Migration of a Database to 64 Bit

Doc Id: 565773.1

-> conn / as sysdba
SQL> @?/olap/admin/catnoamd.sql
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/olap/admin/catnoxoq.sql

Determine all PUBLIC synonyms referring to objects in the OLAPSYS schema:

SQL>spool olapdrop.sql
SQL> select 'drop public synonym ' || synonym_name || ';'
from dba_synonyms
where owner='PUBLIC' and table_owner='OLAPSYS';
SQL>spool off

Modify the datapump handler table to remove handlers used to export OLAP data:

SQL>select * from sys.exppkgact$ where package = 'DBMS_AW_EXP' and
schema= 'SYS';
SQL>delete from sys.exppkgact$ where package = 'DBMS_AW_EXP'
and schema= 'SYS';
SQL>commit;

PL/SQL Web - File Upload and Download Procedures

15:33 1 comment

http://www.oracle-base.com/articles/9i/FileUploadDownloadProcedures9i.php

Oradebug - Diagnosing Library Cache Latch Contention

12:56 No comments

http://arup.blogspot.com/2008/08/diagnosing-library-cache-latch.html

RAC One Node tips

11:39 No comments

http://www.dba-oracle.com/t_rac_one_node.htm

Oracle Enterprise Manager Desktop Widgets

09:06 No comments

http://blogs.oracle.com/emwidgets/

String Aggregation Techniques

15:55 No comments

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

Database Point in Time Recovery failed ORA-01841

09:16 1 comment

Doc ID: 370500.1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/10/2006 11:43:56
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time 'MAY 10 2006 06:40:04'
ORA-01841 (full) year must be between -4713 and +4713 / +9999 and must not be 0

Cause

o NLS_LANG was not set (empty)

Solution
To implement the solution, please execute the following steps:

1. Set Environemnt Variable for example:

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
echo $NLS_LANG

Problem : iAS Discovery/Monitoring Fails If Agent Is Installed Under Another OS User Than iAS

06:50 No comments

Doc ID: 437078.1

cd $IAS_HOME
chmod g+rx $IAS_HOME
chmod g+rx $IAS_HOME/bin
chmod g+rx $IAS_HOME/bin/emtgtctl
chmod ug+s $IAS_HOME/bin/emtgtctl2

chmod g+rx $IAS_HOME/perl
chmod g+rx $IAS_HOME/perl/bin
chmod g+rx $IAS_HOME/perl/bin/perl

chmod g+rx $IAS_HOME/sysman
chmod g+rx $IAS_HOME/sysman/emd
chmod g+rw $IAS_HOME/sysman/emd/targets.xml
touch $IAS_HOME/sysman/emd/centralagents.lst
chmod g+rw $IAS_HOME/sysman/emd/centralagents.lst

chmod g+rx $IAS_HOME/sysman/config
chmod g+rw $IAS_HOME/sysman/config/*.properties

chmod g+rx $IAS_HOME/opmn
chmod g+rx $IAS_HOME/opmn/conf
chmod g+rw $IAS_HOME/opmn/conf/opmn.xml

chmod g+rx $IAS_HOME/config
chmod g+rw $IAS_HOME/config/*.xml
chmod g+rw $IAS_HOME/config/*.properties

chmod g+rx $IAS_HOME/Apache
chmod g+rx $IAS_HOME/Apache/Apache
chmod g+rx $IAS_HOME/Apache/Apache/conf
chmod g+rw $IAS_HOME/Apache/Apache/conf/*.conf
chmod g+rw $IAS_HOME/Apache/Apache/conf/*.xml
chmod g+rw $IAS_HOME/Apache/Apache/conf/mime.types

For each OC4J (in this example OC4J_SECURITY):
chmod g+rx $IAS_HOME/j2ee
chmod g+rx $IAS_HOME/j2ee/OC4J_SECURITY
chmod g+rx $IAS_HOME/j2ee/OC4J_SECURITY/config
chmod g+rw $IAS_HOME/j2ee/OC4J_SECURITY/config/*

chmod g+rx $IAS_HOME/dcm
chmod g+rx $IAS_HOME/dcm/config
chmod g+rw $IAS_HOME/dcm/config/*.conf
chmod g+rw $IAS_HOME/dcm/config/*.xml

Oracle Database 11g Release 2 New Features Summary

12:25 No comments

http://www.dbasupport.com/oracle/ora11g/11gR2-new-features-01.shtml

Linux Error: 104: Connection reset by peer when starting Oracle Listener

11:52 No comments

Make sure the loopback entry in /etc/hosts is not missing when you start the listener:

127.0.0.1 localhost.localdomain localhost

What happens during oracle database hot backup

06:51 No comments

http://arjudba.blogspot.com/2009/08/what-happens-during-oracle-database-hot.html

Scheduler Enhancements in Oracle Database 11g

07:21 No comments

Scheduler Enhancements in Oracle Database 11g Release 1
http://www.oracle-base.com/articles/11g/SchedulerEnhancements_11gR1.php

Scheduler Enhancements in Oracle Database 11g Release 2
http://www.oracle-base.com/articles/11g/SchedulerEnhancements_11gR2.php

Remote Scheduler Agent Installation for Oracle 11g Release 2
http://www.oracle-base.com/articles/11g/SchedulerAgentInstallation_11gR2.php

Monitorando o progresso do RMAN

07:34 No comments

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;

ORA-28112 (select * from mgmt_targets)

06:27 No comments

grant exempt access policy to USER;

Metalink DOC ID: 778864.1

Migrating a Microsoft SQL Server Database to Oracle Database 11g

06:26 No comments

http://www.oracle.com/technology/obe/hol08/sqldev_migration/mssqlserver/migrate_microsoft_sqlserver_otn.htm

Fine Grained Dependency (11G)

06:24 No comments

Improved PL/SQL stored procedure invalidation mechanism - A new 11g features will be fine grained dependency tracking, reducing the number of objects which become invalid as a result of DDL.

In previous releases, object dependencies were managed at the object level, so altering an object automatically invalidated all dependent objects. Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation.

http://www.oracle-base.com/articles/11g/EnhancedFinerGrainedDependencyManagement_11gR1.php

http://www.oraclebrains.com/2007/09/plsql-fine-grained-dependency-tracking/

http://ocpdba.wordpress.com/2009/03/05/a-new-paradigm-oracle-11gs-feature-for-context-objects-invalidation-coi/

Moving ASM Database Files from one Diskgroup to Another

06:23 No comments

http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_36.shtml

Delete the old ASM database file from its original location

06:22 No comments

$ ORACLE_SID=+ASM; export ORACLE_SID

$ sqlplus "/ as sysdba"

SQL> ALTER DISKGROUP TESTDB_DATA2 DROP FILE '+TESTDB_DATA2/testdb/datafile/app_data.256.598127837';

http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_36.shtml

Resetting the Administrator (oc4jadmin) Password

06:19 No comments

1 - Stop OC4J and the Application Server Control.

2 - Locate and open the following file in a text editor:

(UNIX)ORACLE_HOME/j2ee/home/config/system-jazn-data.xml

3 - Locate the line that defines the credentials property for the oc4jadmin user.

{903}4L50lHJWIFGwLgHXTub7eYK9e0AnWLUH

4 - Replace the existing encrypted password with the new password.

!mynewpassword123

5 - Delete cached password data by deleting the contents of the following directory:

ORACLE_HOME/j2ee/oc4jinstance/persistence/ascontrol/ascontrol/securestore/

6 - Start OC4J and the Application Server Control.

http://download-west.oracle.com/docs/cd/B31017_01/core.1013/b28940/trouble_asc.htm#BCEDHFEI

Oracle's Block Browser and Editor tool

08:33 No comments

Oracle's Block Browser and Editor tool

http://www.dbasupport.com/oracle/ora10g/bbed.shtml

http://www.dbasupport.com/oracle/ora10g/bbed02.shtml

Tuning Reports Server Configuration

07:28 No comments

<'engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="10" maxEngine="150" minEngine="10" engLife="50" maxIdle="30" callbackTimeOut="60000" engineResponseTimeOut="60" jvmOptions="-Xms128m -Xmx1024m">

Mais detalhes em:

http://download-west.oracle.com/docs/cd/B14099_17/bi.1012/b14048/pbr_tune.htm#i1006609

Result Cache - PL/SQL 11G

07:05 No comments

“Result Cache” é uma feature do 11g que possibilita manter o resultado de uma função em CACHE.
Muito útil, por exemplo, para funções que são constantemente utilizadas pelas aplicações.

Mais detalhes em:

http://www.oracle.com/technology/oramag/oracle/07-sep/o57asktom.html

http://www.oracle.com/technology/oramag/oracle/07-sep/o57plsql.html

http://www.oracle-base.com/articles/11g/CrossSessionPlsqlFunctionResultCache_11gR1.php

Response-Time Analysis

12:18 No comments

http://www.oracle.com/technology/pub/articles/schumacher_analysis.html

Oracle licensing

05:31 No comments

http://www.orafaq.com/wiki/Oracle_licensing

ORA-24247 When Executing UTL_HTTP

14:37 No comments

When executing UTL_INADDR (or) UTL_HTTP (or) any UTL packages after upgrading to 11gR1, fails with following error:


declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-6512: at "SYS.UTL_HTTP", line 1029
ORA-24247: network access denied by access control list (ACL)
ORA-6512: at line 6

Utilizar a package

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE para atribuir privilégios de connect e resolve ao usuário.


Mais detalhes em :

 Metalink Doc ID: 453786.1

Copiando as senhas de uma base LDAP para outra

14:28 No comments

ldapsearch -h 192.168.244.131 -p 389 -D "cn=orcladmin" -w PASSWORD -L -b "cn=users,dc=oracle,dc=com,dc=br" -s sub "objectclass=*" userpassword > portal_users.ldif

exemplo de arquivo gerado:

dn: cn=alex.zaballa,cn=users,dc=oracle,dc=com,dc=br
userpassword: {SHA}BnaRkBbuGYJWeIxqI25m075ZBs0=

alterar o arquivo da seguinte forma:

dn: cn=alex.zaballa,cn=users,dc=oracle,dc=com,dc=br
changetype: modify
replace: userpassword
userpassword: {SHA}BnaRkBbuGYJWeIxqI25m075ZBs0=

Aplicar alterações:

ldapmodify -h 192.168.245.131 -p 389 -D "cn=orcladmin" -w PASSWORD -f portal_users.ldif

Integração do Application Server 10.1.3 com SSO

14:17 No comments

http://download.oracle.com/docs/cd/B32110_01/core.1013/b32196/reconfig.htm#BHCIAJIF

Oracle Portal com Database 11g

14:15 No comments

How to upgrade the Portal Database to 11g?

Metalink
Doc ID: 465968.1

Master Note for OracleAS Portal Upgrade Issues

Metalink
Doc ID: 262623.1

How to Add a Watermark to Oracle Reports PDF Output

14:11 No comments

Documento que mostra como adicionar uma marca d'agua nos relatórios.

Metalink Doc ID: 400817.1

Beehive + Linux + Portas Baixas

14:07 No comments

Uso de portas baixas no Beehive:

chown root hasbind
chmod a+x hasbind
chmod a+s hasbind

/etc/cap.ora
+user user1: bind port 20
+user user1: bind port 21
+user user1: bind port 23
+user user1: bind port 80
+user user1: bind port 25
+user user1: bind port 443
+user user1: bind port 143
+user user1: bind port 993
+user user1: bind port 465

Para poder utilizar o Apache na porta 443:

chown root .apachectl
chmod a+x .apachectl
chmod a+s .apachectl

Exemplo de mudança de porta do SMTP no Beehive:

beectl modify_port --protocol SMTP --port 25

beectl activate_configuration

Beehive + Imap + SSL

14:04 No comments

Para "colocar" um certificado digital para oIMAP no Beehive, basta alterar a propriedade WalletDir, apontando para o caminho está está seu Wallet.

beectl list_property --component beehive_instance_OrionInstance.your_domain.com --name WalletDir

beectl modify_property --component beehive_instance_OrionInstance.your_domain.com --name WalletDir --value /u01/app/oracle/product/1.5.1.0.0/beehive/Apache/Apache/conf/ssl.wlt/portal/

beectl activate_configuration

Adicionar um email externo a um grupo de Emails do Beehive

13:51 No comments

Infelizmente até a versão 1.5 do Beehive, não é possível adicionar um email externo a um grupo de emails utilizando o Beekeeper.

Existe uma solução via Beectl:

add_external_contact --family_name Silva --display_name "Joao da Silva" --address BUSINESS_1:mailto:joao.silva@yourdomain.com

Criar um arquivo xml com estrutura conforme a imagem:

beectl modify_group --file yourxmlname.xml

Consultando os membros do grupo:

beectl list_groups --name EMPRESA --show MEMBERS

Proxy Reverso + Application Server + Ip errado nos logs

13:48 No comments

Para quem utiliza solução de proxy reverso juntamente com application server, já deve ter tido problemas com os logs. O IP que aparece é o do Proxy e não de quem fez a requisição HTTP.

Segue uma solução encontrada para o problema:

Criar o arquivo AdjustRemoteAddrAccordingToHeader.pm

package Apache::AdjustRemoteAddrAccordingToHeader;

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;

Adicionar no httpd.conf

PerlRequire /u01/app/oracle/product/as10g/MidTier/Apache/Apache/conf/AdjustRemoteAddrAccordingToHeader.pm
PerlPostReadRequestHandler Apache::AdjustRemoteAddrAccordingToHeader
PerlSetVar UseIPFromHeader X-Forwarded-For

Installing Oracle Enterprise Manager 10g Grid Control Rel 5 on Oracle Database 11g and Linux

13:47 No comments

Passo-a-passo interessante para instalação do GRID Versão 10.2.0.5 em um banco 11G.

http://www.oracle.com/technology/pub/articles/revitt-oem.html

Converting Your Oracle Forms Applications to Application Express 3.2

13:35 No comments

Artigo interessante para quem já trabalhou com o bom e velho FORMS.

http://www.oracle.com/technology/obe/apex32/apex32frmmigr.htm

BULK COLLECT

13:34 No comments

Artigo interessante sobre o uso da cláusula LIMIT.

http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html

Oracle Audit

11:32 No comments

Como muitos DBA's, também gosto de guardar meus LOG's de auditoria dentro do banco de dados.

Para isso, o primeiro passo é criar uma tablespace que irá manter os logs (Isto server apenas para não inflar a tablespace System).

CREATE TABLESPACE TBSDAT_AUDITORIA DATAFILE SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 8192M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$'
Para alterar a tablespace, podemos proceder de 2 formas, dependendo da versão do banco de dados.

Utilizar a package DBMS_AUDIT_MGMT ou o script que está no DOC ID: 1019377.6

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,AUDIT_TRAIL_LOCATION_VALUE => 'TBSDAT_AUDITORIA');
END;

select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$'

Metalink - DOC ID: 804624.1
The associated LOB and LOB INDEX segments for AUD$ are not moved from the SYSTEM tablespace when the AUD$ table is moved to another tablespace using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.

select owner,table_name,segment_name,tablespace_name from dba_lobs where table_name='AUD$'
select owner, table_name,index_name,index_type,tablespace_name from dba_indexes where table_name='AUD$'

alter table aud$ move lob (sqlbind) store as (tablespace TBSDAT_AUDITORIA);
alter table aud$ move lob (sqltext) store as (tablespace TBSDAT_AUDITORIA);

Finalmente alterar o parâmetro de inicialização:

alter system set audit_trail=db scope=spfile;