Criando bases Oracle ambientes 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

 

Anúncios

Data no history

Hoje a dica é bem simples, incluir a data no histórico (history do terminal).

Root:

cat >> /root/.bashrc << EOF
export HISTTIMEFORMAT=”%F-%T ”
EOF

Oracle:

cat >> /home/oracle/.bashrc << EOF
export HISTTIMEFORMAT=”%F-%T ”
EOF

Capture

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

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>