TFA – Collecting Period

I like quick posts, you already know that. It’s like a quick memo to myself in the future. ūüôā

Here is a simple example to collect files with TFA by date interval:

tfactl diagcollect -all -from "Jul/24/2015 15:30:00" -to "Jul/24/2015 16:30:00"

See ya!

Matheus.

Advertisements

Sqlplus: Connect without configure TNSNAMES

Okey, you must to know, but is always useful to remmember that… If you don’t want to configure your TNSNAMES, you can connect directly to description of your database. This way:

sqlplus> conn matheus_boesing@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1531)))(CONNECT_DATA=(service_name=mydb)))
Enter password: ********
Connected.
sqlplus>

Based on this, I made two scripts, to connect with the sid (c.sql) or with the service_name (s.sql) and make my life easier. Here the scripts:

sqlplus>get c
1 DEFINE VHOST = &1.
2 DEFINE VPORT = &2.
3 DEFINE VSID = &3.
4 DEFINE VDESC='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&VHOST)(PORT=&VPORT)))(CONNECT_DATA=(SID=&VSID)(server=dedicated)))'
5 disconnect
6 connect matheus_boesing@&&VDESC
7 set linesize 1000
8 set sqlprom '&&VSID> '
9 select instance_name, host_name
10 from v$instance;
11 exec dbms_application_info.SET_MODULE('MATHEUS_BOESING','DBA');
12 alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
13 UNDEFINE VDESC
14 UNDEFINE 1
15 UNDEFINE 2
16* UNDEFINE 3
sqlplus>get s
1 DEFINE VHOST = &1.
2 DEFINE VPORT = &2.
3 DEFINE VSID = &3.
4 DEFINE VDESC='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&VHOST)(PORT=&VPORT)))(CONNECT_DATA=(SERVICE_NAME=&VSID)(server=dedicated)))'
5 prompt &VDESC
6 disconnect
7 connect matheus_boesing@&&VDESC
8 set linesize 1000
9 set sqlprom '&&VSID> '
10 select instance_name, host_name
11 from v$instance;
12 exec dbms_application_info.SET_MODULE('MATHEUS_BOESING','DBA');
13 alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
14 UNDEFINE VDESC
15 UNDEFINE 1
16 UNDEFINE 2
17* UNDEFINE 3
sqlplus>

It can be used like this:

sqlplus>@s mydb.domain.net 1531 mydb
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1531)))(CONNECT_DATA=(SERVICE_NAME=mydb)(server=dedicated)))
Enter password: ********
Connected.

Ok, but, let’s suppose you are working in a cluster and wants to connect directly to the another instance. I made the script below (ci.sql). It’s not beautiful, but is a lot hopeful:

sqlplus> get ci
1 DEFINE VINT = &1.
2 undefine VHOST
3 undefine VSID
4 VARIABLE VCONN varchar2(100)
5 PRINT ret_val
6 BEGIN
7 SELECT '@c '||host_name||' 1521 '||INSTANCE_NAME
8 INTO :VCONN
9 FROM gv$instance where INSTANCE_NUMBER=&VINT;
10 END;
11 /
12 set head off;
13 spool auxcon.sql
14 prompt set head on;
15 print :VCONN
16 prompt set head on;
17 spool off;
18* @auxcon
sqlplus>

As you see, you inform the inst_id you want to connect. It can be used like:

mydb> @instance
INSTANCE_NAME
------------------------------
mydb_2
mydb> @instances
INST_NUMBER INST_NAME
----------- ---------------------------------------
1 db2srvr2p.sicredi.net:mydb_1
2 db1srvr1p.sicredi.net:mydb_2
mydb> @ci 1
@c db2srvr2p.sicredi.net 1521 mydb_1
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Enter password: ********
Connected.
mydb_1> @instance
INSTANCE_NAME
------------------------------
mydb_1

That’s right?
The scripts a shared help me a lot every day, and it’s exclusive.
I not founded nothing like this. So, I made. ūüôā

Matheus.

GoldenGate: RAC One Node Archivelog Missing

The situation:

We have a GoldenGate on Allow Mode running some extracts on RAC One Node Database (reading the archivelogs). And then, suddenly, the instance crashes (network lost contact to the server) and the other instance (thread) was auto started by CRS. To the database no problems: The other node redologs was used during the startup recover and every thing is ok.

The application running with Weblogic serverpool and gridlink just had a little contention and continued the operation thought the started¬†instance.¬†The Goldengate switch was manually made, but some sequences was lost. What we found? the sequences was in the old thread’s redologfiles.¬†It should be backed up if¬†fast_start_mttr_target was different to zero. Buuut, the world is not so beautiful:

raconenodedb> show parameters mttr
NAME TYPE VALUE
------------------------------------
fast_start_mttr_target integer 0

How we solved?
Simple solution: identified the group/thread and made a cp from ASM. The copied redolog was used as archivelog on goldengate and everything was ok.

raconenodedb> select sequence#,group#,thread# from v$log where thread#=2 order by 1;
SEQUENCE# GROUP# THREAD#
---------- ---------- ----------
39636 6 2
39637 7 2
39638 8 2
39639 9 2
39640 10 2
ASMCMD> cp group_10.288.859482805 /oracle/grup10_thread2
copying +DGDATA/MYDB/ONLINELOG/group_10.288.859482805 -> /oracle/grup10_thread2

Easy like that.

Matheus.

Acentua√ß√£o no SQLPLUS com Windows em ingl√™s

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

Default CHCP

–¬† SetarOEMCP para 1252

set OEMCP

2) Configurar NLS_LANG do Oracle:

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

NLS_LANG Oracle
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

set OEMCP
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”:

Fonte_Caracteres

Fechado?

Até breve!