Problema no tamanho do log Listener Windows

Quem tiver o mesmo problema que o meu no windows, sobre o tamanho do arquivo do log do listener que faz o listener parar quando o arquivo chega em 4gb, eu fiz um script para automatizar e evitar esses problemas bastando apenas ser colocado no agendador do windows.

Basta mudar o diretorio onde são gerados os logs do seu ambiente, e criar um arquivo.bat

set d=%date:~-4,4%%date:~-7,2%%date:~0,2%
set d=%d: =_%
set t=%time:~0,2%%time:~3,2%%time:~6,2%
set t=%t: =0%
D:
cd \app\Administrator\diag\tnslsnr\srv03\listener3\trace
lsnrctl set log_status off
RENAME listener.log "listener_%d%_%t%.log"
lsnrctl set log_status on
Anúncios

SSH entre AIX e Linux sem senha

A dica de hoje para criar o acesso via ssh sem senha do AIX para o linux é criando o script abaixo e executando no aix, simplificando a criação do acesso:

#!/bin/sh
 
# Shell script to install your public key on a remote machine
# Takes the remote machine name as an argument.
# Obviously, the remote machine must accept password authentication,
# or one of the other keys in your ssh-agent, for this to work.
 
ID_FILE="${HOME}/.ssh/id_rsa.pub"
 
if [ "-i" = "$1" ]; then
  shift
  # check if we have 2 parameters left, if so the first is the new ID file
  if [ -n "$2" ]; then
    if expr "$1" : ".*\.pub" > /dev/null ; then
      ID_FILE="$1"
    else
      ID_FILE="$1.pub"
    fi
    shift         # and this should leave $1 as the target name
  fi
else
  if [ x$SSH_AUTH_SOCK != x ] && ssh-add -L >/dev/null 2>&1; then
    GET_ID="$GET_ID ssh-add -L"
  fi
fi
 
if [ -z "`eval $GET_ID`" ] && [ -r "${ID_FILE}" ] ; then
  GET_ID="cat \"${ID_FILE}\""
fi
 
if [ -z "`eval $GET_ID`" ]; then
  echo "$0: ERROR: No identities found" >&2
  exit 1
fi
 
if [ "$#" -lt 1 ] || [ "$1" = "-h" ] || [ "$1" = "--help" ]; then
  echo "Usage: $0 [-i [identity_file]] [user@]machine" >&2
  exit 1
fi
 
# strip any trailing colon
host=`echo $1 | sed 's/:$//'`
 
{ eval "$GET_ID" ; } | ssh $host "umask 077; test -d ~/.ssh || mkdir ~/.ssh ; cat >> ~/.ssh/authorized_keys" || exit 1
 
cat <<EOF
Now try logging into the machine, with "ssh '$host'", and check in:
 
  ~/.ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
EOF

-- Permissões necessarias
chmod 755 ssh-copy-id

-- Criando a conexão do AIX para o Linux para acesso via SSH sem senha
./ssh-copy-id oracle@192.168.0.100

Drop Cascade mais rápido

Pessoal para que precisa limpar um esquema bem mais rápido que o drop cascade convencional, o script abaixo que desenvolvi ira te ajudar:

 

SET SERVEROUTPUT ON
DECLARE V_ESQUEMA VARCHAR2(30) := 'PROTHEUS';
BEGIN
  FOR APAGAR IN (  
					SELECT 'DROP TABLE '||'"'||OWNER||'"'||'.'||'"'||OBJECT_NAME||'"'|| ' CASCADE CONSTRAINTS PURGE' DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='TABLE'
					UNION ALL
					SELECT 'DROP SEQUENCE '||'"'||OWNER||'"'||'.'||'"'||OBJECT_NAME||'"' DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='SEQUENCE'
					UNION ALL
					SELECT 'DROP VIEW '||'"'||OWNER||'"'||'.'||OBJECT_NAME DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='VIEW'
					UNION ALL
					SELECT 'DROP PROCEDURE '||'"'||OWNER||'"'||'.'||OBJECT_NAME DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='PROCEDURE'
					UNION ALL
					SELECT 'DROP FUNCTION '||'"'||OWNER||'"'||'.'||OBJECT_NAME DDL FROM DBA_OBJECTS WHERE OWNER = V_ESQUEMA AND OBJECT_TYPE='FUNCTION'
					UNION ALL
					SELECT 'DROP INDEX '||'"'||OWNER||'"'||'.'||'"'||INDEX_NAME||'"'|| ';' DDL FROM DBA_INDEXES WHERE OWNER = V_ESQUEMA
			    ) 
  LOOP
	 DBMS_OUTPUT.PUT_LINE(APAGAR.DDL||' EXECUTADO.');
     EXECUTE IMMEDIATE APAGAR.DDL;
  END LOOP;
-- MATAR SESSOES E LIMPAR O RESTO
	BEGIN
		 FOR SESSIONS IN ( 
							SELECT SID, SERIAL# 
							FROM   V$SESSION 
							WHERE  USERNAME = V_ESQUEMA
						)
		  LOOP
			 DBMS_OUTPUT.PUT_LINE('KILL SESSOES: '||V_ESQUEMA);
			 EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||SESSIONS.SID||','||SESSIONS.SERIAL#||'''';
			 EXECUTE IMMEDIATE 'DROP USER '||V_ESQUEMA||' CASCADE';
		  END LOOP;
	END;
-- DROP DO ESQUEMA
	DBMS_OUTPUT.PUT_LINE('DROP USER '||V_ESQUEMA||' CASCADE');
	EXECUTE IMMEDIATE 'DROP USER '||V_ESQUEMA||' CASCADE';
END;

 

DBlink Oracle to SQL Server

Pessoal estava com a necessidade de consumir algumas informações do SQL Server em um banco Oracle, desta forma fiz um procedimento simples para quem precisar.

  • Instalando os pacotes necessários.

yum install unixODBC unixODBC-devel freetds -y

  • TNSNAMES

MSSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=lab11g.localdomain)(PORT=1524))
(CONNECT_DATA=(SID=MSSQL))
(HS=OK)
)

  • LISTENER

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = lab11g.localdomain)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ora11g)
)
(SID_DESC=
(SID_NAME=MSSQL) # ALIAS USADO EM TODA A CONFIGURACAO
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
(PROGRAM=dg4odbc)
)

)

ADR_BASE_LISTENER = /u01/app/oracle

lsnrctl stop
lsnrctl start

  • Testando a instalação do ODBC

[root@lab11g ~]# odbcinst -j
unixODBC 2.3.1
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size…….: 8
SQLLEN Size……..: 8
SQLSETPOSIROW Size.: 8

  • Criando o init no Oracle

vi $ORACLE_HOME/hs/admin/initMSSQL.ora

HS_FDS_CONNECT_INFO = MSSQL
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB
set ODBCINI=/etc/odbc.ini #LOCAL DO ARQUIVO ODBC DE CONFIGURACAO

  • Criando o arquivo do odbc com as configurações

vi /etc/odbc.ini

[ODBC Data Sources]
MSSQL = MSSQL Server

[MSSQL]
# Reference driver from the “/etc/odbcinst.ini” file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the “/etc/odbcinst.ini” file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 192.168.0.31
Database = LAB
Port = 1433
TDS_Version = 7.2

[Default]
Driver = /usr/lib64/libtdsodbc.so.0

  • Testando o acesso ao banco no SQL Server

[root@lab11g ~]# osql -S MSSQL -U teste -P teste

checking shared odbc libraries linked to isql for default directories…
strings: ”: No such file
trying /tmp/sql … no
trying /tmp/sql … no
trying /etc … OK
checking odbc.ini files
reading /root/.odbc.ini
[MSSQL] found in /root/.odbc.ini
found this section:
[MSSQL]
# Reference driver from the “/etc/odbcinst.ini” file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the “/etc/odbcinst.ini” file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 192.168.44.33
Database = CENTER_MIDIA
Port = 1433
TDS_Version = 7.2
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES

looking for driver for DSN [MSSQL] in /root/.odbc.ini
found driver line: ” Driver=/usr/lib64/libtdsodbc.so.0″
driver “/usr/lib64/libtdsodbc.so.0” found for [MSSQL] in .odbc.ini
found driver named “/usr/lib64/libtdsodbc.so.0”
/usr/lib64/libtdsodbc.so.0 is an executable file
“Server” found, not using freetds.conf
Server is “192.168.44.33”
looking up hostname for ip address 192.168.44.33
osql: warning: no DNS hostname found for “192.168.44.33”

Configuration looks OK. Connection details:

DSN: MSSQL
odbc.ini: /root/.odbc.ini
Driver: /usr/lib64/libtdsodbc.so.0
Server hostname: 192.168.44.33
Address: 192.168.44.33

Attempting connection as teste …
+ isql MSSQL teste teste -v
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL>quit

  • Outro teste de acesso ao SQL Server

[root@lab11g ~]# isql MSSQL teste ‘teste’ -v
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL>quit

 

  • Crie o usuário com acesso no SQL Server e suas permissões, o meu foi o teste.
  • Criando o dblink no Oracle

create public database link MSSQL connect to “teste” identified by “teste” using ‘MSSQL’;

  • Acessando a tabela do SQL Server pelo Oracle.

SQL> DESC TB_BAIRRO@MSSQL;
Name Null? Type
—————————————– ——– —————————-
bairro_codigo NUMBER(10)
cidade_codigo NVARCHAR2(50)
bairro_descricao NVARCHAR2(50)

SQL>