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>

Pessoal criei esse script no intuito de agilizar o processo de limpeza de objetos no Oracle, segue abaixo script:

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;

Log muito grande do Listener Windows

Pessoal hoje tive um problema no ambiente de um cliente, o log do listener chegou em 4gb e começou a gerar problemas, dessa forma 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

Criando bancos DEV/HML com Duplicate

https://sgbdbrasil.wordpress.com/2018/10/31/criando-bases-de-hml-e-dev/

A dica de hoje é para quem precisar sempre criar ou atualizar um ambiente Oracle de desenvolvimento ou homologação de forma fácil utilizando o duplicate.

1- Criar um init baseado nas configurações do teu ambiente, podem adaptar este init base DEV abaixo e utilizar:

*.audit_trail='DB'
*.commit_logging='BATCH'
*.commit_wait='NOWAIT'
*.compatible='11.2.0.4.0'
*.control_files='/u02/dev/oradata/dev/control01.ctl','/u02/dev/oradata/dev/control02.ctl','/u02/dev/oradata/dev/control03.ctl'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/admin/dev/cdump'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name='dev'
*.db_writer_processes=2
*.diagnostic_dest='/u01/app/oracle'
*.enable_goldengate_replication=TRUE
*.job_queue_processes=20
*.large_pool_size=0
*.log_archive_dest_1='LOCATION=/u02/dev/backup/archivelog/'
*.log_archive_format='%t_%s_%r.arc'
*.memory_max_target=9663676416
*.memory_target=9663676416
*.open_cursors=6000
*.optimizer_index_caching=70
*.optimizer_index_cost_adj=1
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=0
*.processes=2000
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=40
*.sessions=1105
*.sga_max_size=0
*.sga_target=0
*.undo_management='AUTO'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
*.db_file_name_convert='/oradata/DADOS/prd/','/u02/dev/oradata/dev/','/oradata/INDICES/prd/','/u02/dev/oradata/dev/','/oradata/INDICES/datafile','/u02/dev/oradata/dev/'
*.log_file_name_convert='/oradata/DADOS/REDOLOG/prd/','/u02/dev/oradata/dev/','/oradata/INDICES/REDOLOG/prd/','/u02/dev/oradata/dev/'

2 – Ajustar o script do duplicate abaixo:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=dev
export BASE=/u01/app/oracle
export DEST=/u02/dev/backup/
export HOJE=`date +%Y%m%d`
export SPOOL=$DEST/logs

export ORACLE_SID=dev
sqlplus / as sysdba << EOF
spool $SPOOL/atualiza_dev_0_shutdown_base_$HOJE.spool
SHUTDOWN ABORT;

# INICIANDO A INSTANCIA DO BANCO UTILIZANDO O PFILE QUE CRIAMOS ACIMA
STARTUP NOMOUNT pfile='/u02/dev/backup/scripts/initdev.ora';
spool off
exit
EOF

# CLONE DO BACKUP PARA HML
rman auxiliary / msglog $SPOOL/clonando_base_$HOJE.spool << EOF
RUN
{

#DATA E HORARIO FINAL DO PONTO DO RESTORE A SER EFETUADO
set until time “to_date('18/10/2018 23:00:00','DD/MM/YYYY HH24:MI:SS')”;
allocate auxiliary channel c1 type disk ;

# LOCAL ONDE ESTA O BACKUP DOS CONTROLFILES E DO BACKUPSET
duplicate target database to hml backup location='/u02/dev/backup/restore' nofilenamecheck;
}
EOF

 

# LEMBRAR DE RECRIAR A TEMP

sqlplus / as sysdba << EOF
spool $SPOOL/create_spfile_hml_$HOJE.spool
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE ‘/u02/dev/oradata/dev/temp_01.dbf' SIZE 20M autoextend on next 100M maxsize 31G;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
SHUTDOWN ABORT;
STARTUP pfile='/u02/dev/backup/scripts/initdev.ora';
CREATE SPFILE FROM pfile='/u02/dev/backup/scripts/initdev.ora';
SHUTDOWN ABORT;
STARTUP
spool off
exit
EOF

Teste de integridade dos Backups

Fala pessoal, hoje a dica é sobre como automatizar os testes de integridade nos backups diários.
Na rotina abaixo testo a integridade dos backups maiores que D-1.
Referência: https://msdn.microsoft.com/pt-br/library/ms188902.aspx

DECLARE @SQL VARCHAR(MAX)
DECLARE @BKP sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT
bmf.physical_device_name
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() – 1)
AND bs.type LIKE ‘D’
ORDER BY
bs.database_name,bs.backup_finish_date
OPEN curDB
FETCH NEXT FROM curDB INTO @BKP
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ‘restore verifyonly from disk = ‘+””+ltrim(rtrim(@BKP))+””+’ WITH CHECKSUM;’
EXEC(@SQL)
— PRINT @SQL
FETCH NEXT FROM curDB INTO @BKP
END
CLOSE curDB
DEALLOCATE curDB

Modificando Collate das tabelas SQL Server

-- VERIFICANDO COLLACTION DAS TABELAS
SELECT
a.name as 'NomeTabela' ,
b.name as 'NomeColuna',
b.collation ,
c.name as 'TipoDados',
b.length as 'Tamanho'
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join sys.types C on C.system_type_id = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
order by a.name;

-- SQL SERVER 2000

-- VERIFICAR COLLATION DE UMA OU TODAS AS TABELAS

SELECT
a.name as 'NomeTabela' ,
b.name as 'NomeColuna',
b.collation ,
c.name as 'TipoDados',
b.length as 'Tamanho'
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
AND a.name LIKE 't'– FILTRO NOME DA TABELA
order by a.name

-- VERIFICAR COLLATION DE UMA OU TODAS AS TABELAS

SELECT
a.name as 'NomeTabela' ,
b.name as 'NomeColuna',
b.collation ,
c.name as 'TipoDados',
b.length as 'Tamanho'
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
AND a.name LIKE 't'– FILTRO
order by a.name

-- ALTERAR
DECLARE @tabela VARCHAR(100)
DECLARE @collate VARCHAR(100)
–SET @tabela='t'
SET @collate='SQL_Latin1_General_CP1_CI_AI' — SQL_Latin1_General_CP1_CI_AI
SELECT 'Alter Table '+ a.name + ' Alter Column '+ b.name +' '+c.name +' ('+CONVERT(VARCHAR(50),b.length)+') Collate'+' '+@collate
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
and b.collation not like 'SQL_Latin1_General_CP1_CI_AI'
--AND a.name=@tabela — FILTRO
order by a.name

-- COMPROVANDO QUE NÃO EXISTEM TABELAS COM COLLATION DIFERENTE DO SOLICITADO 

SELECT
a.name as 'NomeTabela' ,
b.name as 'NomeColuna',
b.collation ,
c.name as 'TipoDados',
b.length as 'Tamanho'
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join sys.types C on C.system_type_id = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
and b.collation !='SQL_Latin1_General_CP1_CI_AI'
order by a.name

Gerando Arquivo com BCP incluindo a data no nome do arquivo.

Pessoal segue script de como gerar um arquivo formatado com BCP colocando o nome com data no arquivo.
Maiores informações:
https://docs.microsoft.com/pt-br/sql/tools/bcp-utility?view=sql-server-ver15

declare @sql varchar(8000)
select  @sql = 'bcp "select 1"  queryout "\\192.168.0.45\temp\ARQUIVOTESTE'+REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),' ','_'),':','_')+'.csv" -c -t, -T -S '+ @@servername
exec xp_cmdshell @sql

Replicando uma base PaaS Azure SQL

Pessoal o post de hoje é para mostrar de forma simples, como replicar uma base em Azure SQL do Brasil para USA com leitura na base secundaria.

1 – Servidor primário srvdba com a base rh no Brasil:

1.2 – Verificando se o servidor srvdba é de leitura ou escrita:

SELECT @@SERVERNAME SERVIDOR,DATABASEPROPERTYEX(‘master’,’Updateability’) LEITURA_ESCRITA

Verificamos que o ambiente srvdba é o primário com leitura e escrita através da propriedade updateability.

2 – Configurando a replicação da base rh do Brasil para USA acessando o console do servidor srvdba no Azure.

2.1 – Criando um Failover groups.

Failover group name: Nada mais que um nome apontando para os dois nós, utilizado para abstrair de forma automática em qual nó os sistemas irão escrever ou fazer a leitura.
Secondary server: Servidor secundário onde iremos replicar a base na região USA.
Read/Write failover policy: Opções manual e automático, caso deseje failover automático, devemos selecionar a opção automatic, caso contrario teremos que promover manualmente o banco secundário como primário.
Read/Write grace period (hours): Tempo de espara para que seja feito o failover para o banco secundario;

2.2 – Selecionando as bases a serem replicadas para USA:

2.3 – Failover groups criado:

3 – Verificando ambiente secundário USA.

3.1 – Verificando funções dos servidores e geolocalização:

srvdba – Brasil
srvdbausa – USA

3.2 – Consultando dados no servidor secundário USA:

3.3 – Inserindo dados no primário Basil srvdba banco RH.

3.4 – Consultando os dados no banco secundário rh em USA.

Em resumo geral, queria apenas transmitir como o PaaS pode simplificar recursos em minutos, quando no ambiente On-premises levamos horas configurando um grupo de recursos para prover alta disponibilidade, seja via cluster failover ou AlwaysOn.

Pandemia X Estudos

Desde o inicio da pandemia e isolamento social, tenho visto muitas pessoas ansiosas e com insônia, isso é normal visto que estamos vivendo algo tão intenso e único na humanidade, eu mesmo tenho tido muita dificuldade para dormir as 7 horas diárias que eu dormia normalmente, dessa forma tomei uma decisão, parei de ver jornais e ler noticias ruins, foquei o tempo livre nos estudos e tenho tido ótimos resultados, abaixo seguem conquistas ate o presente momento:

Microsoft Certified Trainer

Meu ritmo de postagem caiu drasticamente não por causa da pandemia, mas sim porque estou estudando muito e me especializando através das certificações.

No dia 11/06/2020 submeti minhas qualificações e certificações para aprovação do MCT (Microsoft Certified Trainer), para quem não sabe o MCT é o titulo/certificação dada pela Microsoft para especialistas técnicos e instrutores em tecnologias Microsoft, para a minha surpresa e felicidade fui aprovado.