matheusdba

How to list all my Oracle Products from Application park?

By matheusdba 12 de May de 2015 31 de January de 2017

YES!
I knew you would like the last post!

So, remains a doubt. What about my Oracle Application park?
Be soft. I’m glad to help. At real, Dieison Santos and me. As I said in the last post, it was his problem theese days… 😛

Here is a query to list your Oracle Application Products (including Oracle SOA Suite, of course) from OEM.

Use wisely:

select distinct * from (
select (LBL_HOSTNAME) "Host",
(CASE
when LBL_PRODUCTNAME like 'WebLogic Server' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%WebTier and Utilities%' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%EM Platform (OMS)%' then 'WebLogic Suite'
when LBL_PRODUCTNAME like '%Web Services Manager%' then 'Diagnostics Pack for Internet Application Server'
when LBL_PRODUCTNAME like '%Application Server 10g%' then 'Internet Application Server Enterprise Edition'
when LBL_PRODUCTNAME like '%Application Server Infrastructure 10g%' then 'Oracle Enterprise Single Sign-On Suite'
when LBL_PRODUCTNAME like '%Business Intelligence%' then 'Oracle Business Intelligence Suite Enterprise Edition Plus'
when LBL_PRODUCTNAME like '%Oracle SOA Suite%' then 'SOA Suite for Oracle Middleware'
when LBL_PRODUCTNAME like '%Oracle BAM%' then 'SOA Suite for Oracle Middleware'
when LBL_PRODUCTNAME like '%WebCenter Portal Suite 11g%' then 'Oracle WebCenter Portal'
when LBL_PRODUCTNAME like '%Oracle Business Process Management%' then 'Unified Business Process Management Suite'
when LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%' then 'Oracle WebCenter Content'
when LBL_PRODUCTNAME like '%Oracle Application Server Guard%' then 'Internet Application Server Enterprise Edition'
when LBL_PRODUCTNAME like '%Application Server Configuration%' then 'Configuration Management Pack for Internet Application Server'
else
LBL_PRODUCTNAME
end)
"Produto",
LBL_BASEVERSION "Versao",
LBL_PROCESSOR "Processador",
lbl_virtual "VIrtual",
DECODE(LBL_CPUS,null,1,LBL_CPUS) "CPUS"
from (SELECT M.EXTERNAL_NAME LBL_PRODUCTNAME,
M.NAME LBL_COMPONENTNAME,
M.BASE_VERSION LBL_BASEVERSION,
M.HOST_NAME LBL_HOSTNAME,
p.virtual lbl_Virtual,
p.system_config || nvl2(p.freq, p.freq || ' MHz FSB ', '') LBL_PROCESSOR,
p.cpu_count LBL_CPUS
FROM (MGMT$SOFTWARE_COMPONENTS M INNER JOIN mgmt$os_hw_summary p ON
M.HOST_NAME = P.HOST_NAME))
where   (
LBL_PRODUCTNAME like 'WebLogic Server'
or LBL_PRODUCTNAME like '%WebTier and Utilities%'
or LBL_PRODUCTNAME like '%EM Platform (OMS)%'
or LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%'
or LBL_PRODUCTNAME like '%Application Server 10g%'
or LBL_PRODUCTNAME like '%Application Server Infrastructure 10g%'
or LBL_PRODUCTNAME like '%Business Intelligence%'
or LBL_PRODUCTNAME like '%Oracle SOA Suite%'
or LBL_PRODUCTNAME like '%Oracle BAM%'
or LBL_PRODUCTNAME like '%WebCenter Portal Suite 11g'
or LBL_PRODUCTNAME like '%Oracle Business Process Management%'
or LBL_PRODUCTNAME like '%Application Server Configuration%'
or LBL_PRODUCTNAME like '%Oracle Application Server Guard%'
or LBL_PRODUCTNAME like '%Oracle Remote Intradoc Client%'
) order by "Produto");

Matheus.

Enterprise Manager, SQL | PLSQL

How to list all my Oracle Products from Database park?

By matheusdba 10 de May de 2015 21 de April de 2020

This is part of DBA role: know and prospect the use of Oracle Products for Oracle contract periodical review, isn’t?
It usually represent a huge problem, or, at least, demands a long time to refresh your spread sheet…

Well, If you use OEM, we offer you a better option! 😀
(I said ‘we’, because Dieison Santos came to me with this problem theese days…. So we talked about, I gave some directives and he mainly solved the problem. This way, great part of ‘we’ should be ‘he’… haha)

Without further, here’s a query that can map your environment (at least your Oracle database products):
You can use it to automate a report and set thresholds. Be creative… 😉

PS: From now, I’ll post all in english. Just for fun.

select
distinct(ddi.host_name) "Host",
(case
when opt.name like '%Active Data Guard%' then 'Oracle Active Data Guard'
when opt.name like '%Advanced Compression%' then 'Oracle Advanced Compression'
when opt.name like '%Audit Vault%' then 'Oracle Audit Vault'
when opt.name like '%Database Vault%' then 'Oracle Database Vault'
when opt.name like '%Partitioning (User)%' then 'Oracle Partitioning'
when opt.name like '%Real Application Clusters%' then 'Oracle Real Application Clusters'
when opt.name like '%Real Application Testing%' then 'Oracle Real Application Testing'
when (opt.name like '%ADDM%' or
opt.name like '%Automatic Database Diagnostic Monitor%' or
opt.name like '%Automatic Workload Repository%' or
opt.name like '%AWR%' or
opt.name like '%Baseline%' or
opt.name like '%Diagnostic Pack%' ) then 'Oracle Diagnostic Pack'
when (opt.name like '%SQL Monitoring%' or
opt.name like '%SQL Performance%' or
opt.name like '%SQL Performance%' or
opt.name like '%SQL Profile%' or
opt.name like '%SQL Tuning%' or
opt.name like '%SQL Access Advisor%' or
opt.name like '%Tuning Pack%') then 'Oracle Tuning Pack'
when opt.name like '%Change Management Pack%' then 'Oracle Change Management Pack'
when ddi.edition like 'Enterprise Edition' then 'Oracle Database Enterprise Edition'
else opt.name
end) "Produto Oracle",
hcd.num_cores "Cores",
ohs.virtual "Virtual",
hcd.impl "Processador",
ddi.dbversion "Versao"
from
mgmt$hw_cpu_details hcd,
mgmt$os_hw_summary ohs,
mgmt$db_dbninstanceinfo ddi,
(select
h.host_name as host,
h.target_name as database_name,
i.instance_name as instance_name,
h.target_type   as target_type,
h.target_guid as target_guid,
f.DBID,
f.NAME,
f.CURRENTLY_USED,
f.DETECTED_USAGES,
f.FIRST_USAGE_DATE,
f.LAST_USAGE_DATE,
f.VERSION,
f.LAST_SAMPLE_DATE,
f.LAST_SAMPLE_PERIOD,
f.TOTAL_SAMPLES,
f.AUX_COUNT,
f.DESCRIPTION
from
mgmt_db_featureusage f,
mgmt_targets h,
mgmt_db_dbninstanceinfo_ecm i,
gc$ecm_gen_snapshot s
where
s.is_current = 'Y' and
s.snapshot_guid = i.ecm_snapshot_id and
s.target_guid = f.target_guid and
h.target_type in ('oracle_database','rac_database') and
s.target_type = h.target_type and
s.snapshot_type in ('oracle_dbconfig','oracle_racconfig') and
f.DETECTED_USAGES>0
) opt
where
hcd.target_guid=ohs.target_guid
and ohs.host_name=ddi.host_name
and ddi.target_guid=opt.target_guid
and (    opt.name like '%Active Data Guard%' -- Active Data Guard
or opt.name like '%Advanced Compression%' -- Advanced Compression
or opt.name like '%Audit Vault%' -- Audit Vault
or opt.name like '%Database Vault%' -- DB Vault
or opt.name like '%Partitioning (user)%' -- Partitioning
or opt.name like '%Real Application Clusters%' --RAC
or opt.name like '%Real Application Testing%' -- RAT
or opt.name like '%ADDM%' -- Diagnostic Pack
or opt.name like '%Automatic Database Diagnostic Monitor%' -- Diagnostic Pack
or opt.name like '%Automatic Workload Repository%' -- Diagnostic Pack
or opt.name like '%AWR%' -- Diagnostic Pack
or opt.name like '%Baseline%' --  Diagnostic Pack
or opt.name like '%Diagnostic Pack%' -- Diagnostic Pack
or opt.name like '%SQL Monitoring%' -- Tuning Pack
or opt.name like '%SQL Performance%' -- Tuning Pack
or opt.name like '%SQL Profile%' -- Tuning Pack
or opt.name like '%SQL Tuning%' -- Tuning Pack
or opt.name like '%SQL Access%' -- Tuning Pack
or opt.name like '%Tuning Pack%' -- Tuning Pack
or opt.name like '%Change Management Pack%' -- Change Management Pack
or ddi.edition like 'Enterprise Edition')
order by ddi.host_name;

Matheus.

Database, Errors and Bugs

ORA-24777: use of non-migratable database link not allowed

By matheusdba 7 de February de 2015 21 de April de 2020

Hoje reencontrei este erro e, como de costume, já havia esquecido qual era a solução. Por isso fica aqui minha anotação. 🙂
Tive que puxar mais da memória do que qualquer outra coisa, pois todos os links da primeira página do Google tinham apenas o stack do erro mas sem solução. Que na realidade é bem simples:

O erro ocorre devido sessão utilizando shared connection (XA) utilizar DBLink com conexão dedicated. O uso de XA no JDBC é uma configuração do datasource, caso você esteja utilizando weblogic, jboss, tomcat ou outro com suporte a java.
A solução que adotei até hoje consiste setar o “host” do DBLink ou a entrada de TNS utilizada pelo DBLink no arquivo tnsnames do BD de origem para utilizar tipo de conexão “shared” para conexão remota.

Ex Antes: (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))) (CONNECT_DATA= (SERVICE_NAME=<serviço>) (SERVER=DEDICATED)))
Ex Depois: (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))) (CONNECT_DATA= (SERVICE_NAME=<serviço>) (SERVER=SHARED)))

Segundo o stackoverflow, pode ser resolvido criando o database link como shared.
Ex: CREATE SHARED DATABASE LINK CONNECT TO bob IDENTIFIED BY “MyNewPasswd1” AUTHENTICATED BY jim IDENTIFIED BY “JimsPass23” USING ‘DB01’;

Na realidade a segunda solução me pareceu muito mais elegante. Mas só percebi que existia depois de já ter resolvido pela primeira abordagem.
Se acontecer novamente, testo da segunda forma e comento aqui. Prometo. 😉

Ah!
Acho que não é necessario mencionar que o BD destino do DBLink precisa estar escutando em algum serviço com suporte a XA, né?
Não é o assunto deste post, então não vou entrar em detalhes. Mas basicamente você precisa setar para um valor diferente de zero os parâmetros: max_shared_servers; shared_servers; max_dispatchers e dispatchers e subir o serviço do dispartcher no listener. Taca no Google aí que essa parte é barbada!

Abraço!
Matheus.

Database

Acentuação no SQLPLUS com Windows em inglês

By matheusdba 4 de February de 2015 21 de April de 2020

Hoje perdi tempão pra fazer funcionar a acentuação no SQLPLUS com Windows em inglês. Resolvi compartilhar.
E não. Não basta escolher o idioma certo na instalação do client. Depende realmente do idioma do SO.

Cenário: Server Windows com client Oracle 11.2 x64 instalado. Necessário usar esta versão do OracleClient visando reconhecimento de scan listeners. Restrições da situação demandavam utilização do sqlplus, obrigatoriamente.

Convém destacar:
– O escopo deste post é fazer com que os dados sejam ‘escritos’ corretamente no BD, e não corrigir dados que já estão gravados incorretamente.
– O escopo deste post não é fazer a conversão de charset do BD, e sim a configuração de nls_lang do client.
– Antes de reclamar que não tá funcionando, dá uma olhadinha no bônus, lá no final do post.

Basicamente, concluí que a melhor configuração para resolver o problema consiste em:
1) Setar o CHCP para 1252 (código de página)
2) Setar variável NLS_LANG do server para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
3) Setar NLS_LANG do Oracle para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

OBS: Funciona com outras combinações, mas optei pelo BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252 pois segundo a Oracle (http://docs.oracle.com/html/B13804_02/gblsupp.htm) é o que temos como ‘oficial’ pro “Português (Brazil).”

Vale lembrar que esta configuração pode ser feita diretamente via prompt de comando antes da utilização do sqlplus. Aí o problema já estaria resolvido (:D).
Até aqui o Eduardo Legatti me ajudou 100%. Mas agora pra que a configuração seja permanente no servidor, como faz?

Faz assim:

1) Configuração CHCP para 1252 por Default:
– Run > regedit

Em [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]:
– Setar o (Default) para c_1252.nls

– SetarOEMCP para 1252

2) Configurar NLS_LANG do Oracle:

Em [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_]:
– Setar NLS_LANG para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

3) Configurar o NLS_LANG como variável de ambiente:

Em [My Computer > Properties > Advanced System Settings > Environment Variables]:
– Setar o NLS_LANG para BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252

4) Reboot do Servidor:

– Para efetivar mudanças no REGEDIT é necessário reiniciar o server.

Era isso!
Espero que seja útil para mais alguém, assim como foram úteis pra mim os links abaixo:

Nota: Greek Characters Are Not Displayed Correctly In A DOS Window (Doc ID 208902.1)
http://www.leniel.net/2010/03/oracle-nlslang-character-set-encoding.html#sthash.KPuB7Rtg.YxcOtWFd.dpbs
http://docs.oracle.com/html/B13804_02/gblsupp.htm
http://nagaappani.blogspot.com.br/2013/05/setting-nlslang-on-windows-for-oracle.html
http://ss64.com/nt/chcp.html
http://www.fabioprado.net/2012/11/configurando-national-language-support.html
http://tosemopcao.blogspot.com.br/2009/11/bd-oracle-acentos-no-10g-xe.html
http://eduardolegatti.blogspot.com.br/2011/01/habilitando-o-suporte-acentuacao-no.html

Bônus)
Embora as configurações estejam adequadas, a fonte (tipo de letra) do DOS (prompt de comando) pode não suportar os caracteres especiais e acentos. Basta trocar pra “Lucida Console”:

Fechado?

Até breve!

grepOra

GrepOra Team

By matheusdba 29 de January de 2015 21 de April de 2020

As already spoken, we are a group of friends that are crazy enough to share our experiences with you and with Oracle community as a payback of our own consumption.

In the next pages you are going to see some of our background and brief description professionally talking. So, by now, we are only going to share some photos of our occasional meetings.

img-20160615-wa0001
(Maiquel, Matheus, Cassiano, Jackson, Dieison and Rafael)
(First GrepOra meeting)

foto
(Maiquel, Rafael, Jackson, Matheus, Dieison and Cassiano)
(Last GrepOra Meeting – by now)

And this is it!
We hope you enjoy the book and the experience.

Let us know what you think about the book and the blog. Reach us out in social media like LinkedIn and Twitter. Collaborate and engage to Community!

Cheers!

grepOra

About the Book

By matheusdba 29 de January de 2015 21 de April de 2020

Hello!

Welcome to our book, our blog and our world to have fun and view/review/learn/laugh with some of our struggles and personal notes for ourselves in the future.

Those posts are basically our notes with some of ours discovers and tips to review in the future. I believe everyone who works with that kind of technology have some personal notes, right? So, ours are being published to share with you.

We believe in sharing and mutual growing, so feel free to reach us to share your notes and tips, to fix anything you think to be wrong or can be better explained or everything. This is not only GrepOra team’s blog. This is our blog. Which includes you.

Ok then. But we are publishing a book? Just why? Who is the target audience? How should I read it? How is it structured? What should I expect?

Why:

This week we are completing 2 Years since the blog was created (in that time, called MatheusDBA). And we decided to review our best moments in these last years and compile them for you. It’s, above all, a good opportunity to refresh some posts that are still actual.

For who:

We are compiling it as a best moments review to engage new readers with the best past posts and reach that readers that enjoy to read a book in their mobile reading devices. Actually, we believe that writing material for this kind of media is the future (or the present), so if you prefer to read PDF files in you Kindle, Ipad, or similar, specially for those who prefer the offline mode to not being bothered by social media notifications, instant messages and other: This is for you. 🙂

How to read:

This is a book generated by the best posts in the blog. If you read the blog you know that the posts are not continuos and mostly have not relation between them. So, this is a book to read some curiosities and tips, to learn and review some useful stuff and to be aware about some daily basis challenges and struggles on working with Oracle technologies. This is not a book to be read in sequence, chapters or something like this. Feel free to read whatever you want and whatever you feel it’s interesting for yourself and to get richer your own experience with Oracle techs… Simple like that.

The structure:

There is no boundaries for our posts and ideas. Of course we have specialities, but everyone can write about everything. So there is no chapters of any restrictedly fixed boundaries. However, to give a little sense, we kind of organized the posts by following this (using our blog categories):

Oracle Database, RAC and Dataguard;
ASM;
Datapump, RMAN, Exp/Imp;
Enterprise Manager;
Application and Middleware;
Golden Gate and Data Integrator;
Linux and Shellscripts;
Cloud Computing;
Heterogeneous Databases;
Web Development and APEX;
PL/SQL and SQL Scripts;
Errors and Bugs.

What to expect:

Basically: “To read some curiosities and tips, to learn and review some useful stuff and to be aware about some daily basis challenges and struggles on working with Oracle technologies”. But mostly: To have fun! This is a book written by Oracle geeks to Oracle geeks.

Welcome to our world!

grepOra

Author: Cassiano Roloff

By matheusdba 29 de January de 2015 21 de April de 2020

20151217100542 Innovative in everything he does, Cassiano acts as Oracle DBA since 2007 and works everyday with APEX since 2009. Cassiano is a great teammate and is always willing to help someone and learn new things.

Below you can find his Certifications list.

https://br.linkedin.com/in/cassianoroloff
cassianoroloff@gmail.co

Certifications:
Oracle Database 10g Administrator Certified Professional – OCP 10g
Oracle Database 10g Administrator Certified Associate – OCA 10g

grepOra

Author: Jackson Costa

By matheusdba 29 de January de 2015 21 de April de 2020

aaeaaqaaaaaaaaldaaaajdq1mjnjowy3lwjkmjytngqwns05njq2ltu0nzzlmdblmtrimq Focused on Fusion Middleware and Application technologies, Jack is friend of everyone and always available to reach out the others. Jack has good architecture comprehension and skills to manage solutions on different technologies.

https://br.linkedin.com/in/jacksonadriano/en
jacksonadriano@gmail.com

grepOra

Author: Rafael Nolio

By matheusdba 29 de January de 2015 21 de April de 2020

rafael3 Passionate Database Administrator, experienced as consultant on heterogeneous environments and always trying new ways to do everything. Great as teammate, Rafael is always available to discuss new ideas and innovation.

Below you can find his Certifications list.

https://br.linkedin.com/in/rafael-nolio-07663438/en
rafael.nolio@gmail.co

Certifications:
Oracle DBA Certified Associate 11g
Oracle Specialist Implementation Certified 11g

grepOra

Author: Dieison Santos

By matheusdba 29 de January de 2015 21 de April de 2020

dieison Professional in the IT area since 2006, working with Oracle applications and database. Solid experience in installation, customization and performance tuning of Fusion Middleware products.

Below you can find his Certifications list.

htps://br.linkedin.com/in/dieisonsantos/en
dieisonpower@gmail.com

Certifications:
Oracle Certified Associate, Oracle WebLogic Server 11g System Administrator