Pular para o conteúdo principal

Postagens

Mostrando postagens de outubro, 2009

Configurando Rotate dos Logs do OPMN

Editar o arquivo opmn.xml: "<"process-type id="OC4J_Portal" module-id="OC4J"">" "<"environment">" ... "<"/environment">" "<"module-data">" "<"category id="start-parameters"">" "<"data id="java-options" value="-server -Djava.security.policy=$ORACLE_HOME/j2ee/OC4J_Portal/config/java2.policy -Djava.awt.headless=true -Xms256m -Xmx2048m -Dstdstream.filesize=1"/">" "<"data id="oc4j-options" value="-properties -out $ORACLE_HOME/opmn/logs/Portal.out -err $ORACLE_HOME/opmn/logs/Portal.err"/">" "<"/category">" ... Na versão 10.1.3.0.0 os parametros -out e -err são ad

GET DDL

accept OWNER char prompt 'OWNER: ' accept NAME char prompt 'ENTER THE NAME OF THE PROCEDURE: ' spool &&NAME..sql set termout off set linesize 1000 set trimspool on set verify off set feedback off Set heading off prompt set define off select decode( type||'-'||to_char(line,'fm99999'), 'PACKAGE BODY-1', '/'||chr(10), null) || decode(line,1,'create or replace ', '' ) || text text from dba_source where name = upper('&&NAME') and owner='&&OWNER' order by type, line; / select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME ||' to '||GRANTEE||';'from dba_tab_privs where TABLE_NAME in (upper('&&NAME')) and OWNER='&&OWNER' / select 'create or replace synonym '||OWNER||'.'||SYNONYM_NAME||' for '||TABLE_OWNER||'.'||TABLE_NAME|| ';' from dba_sy

permissão recursiva DBMS_JAVA.GRANT_PERMISSION (java.io.FilePermission)

Para quem utiliza Java Source, alguns exemplos uteis de grant utilizando dbms_java -- Permissão para conectar/resolver HOST: exec dbms_java.grant_permission( 'DENILSON', 'SYS:java.net.SocketPermission', 'cvs.denilson.com.br', 'connect' ); exec dbms_java.grant_permission( 'DENILSON', 'SYS:java.net.SocketPermission', 'cvs.denilson.com.br', 'resolve' ); -- Permissão de leitura/escrita em diretório RECURSIVAMENTE begin dbms_java.grant_permission( grantee => 'DENILSON', permission_type => 'SYS:java.io.FilePermission', permission_name => '/tmp/cvsteste/-', permission_action => 'read,write,delete' ); end; /

Monitora Sessões com LAST_CALL_ET alto

create or replace procedure dba_app.monitora_sessions is vServer VARCHAR2(30) := 'smtp.denilson.com.br'; vSender VARCHAR2(30) := ' '; vDest VARCHAR2(100) := ' '; vSubj VARCHAR2(50) := 'Verificar Sessão - Produção'; vMesg VARCHAR2(32000):='Verificar Sessão'||UTL_TCP.CRLF; vManda NUMBER:=0; l_mail_conn utl_smtp.connection; BEGIN l_mail_conn := utl_smtp.open_connection(vServer, 25); utl_smtp.helo(l_mail_conn, vServer); utl_smtp.mail(l_mail_conn, vSender); utl_smtp.rcpt(l_mail_conn, vDest); utl_smtp.open_data(l_mail_conn ); utl_smtp.write_data(l_mail_conn, 'Subject'||':'|| 'Verificar Sessao - Producao' || UTL_TCP.CRLF); FOR I IN(select username, sid, serial#, trunc(last_call_et/60) dur from v$session where last_call_et > 20*60 and status = 'ACTIVE' ) LOOP vManda := vManda +1; vMesg:=vMesg||UTL_TCP.CRLF||rpad(i.username,20,' ' )||

Limitando Acesso a tabela por Horário

SQL> column sysdate format a30 SQL> select sysdate from dual; SYSDATE ------------------------------ 12-10-2009 14:35:33 SQL> create table teste as (select * from dba_objects); Table created SQL> select count(*) from teste; COUNT(*) ---------- 49788 SQL> create or replace view vw_teste as select * from teste where to_char(sysdate,'HH24') between 17 and 18; View created SQL> select count(*) from vw_teste; COUNT(*) ---------- 0

Criando Catalogo RMAN

1 - Criando Usuário Banco do Catálogo CREATE TABLESPACE "TBSRMAN" DATAFILE '/u02/oradata/orcl/TBSRMAN01.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE USER RMAN IDENTIFIED BY RMAN TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE TBSRMAN QUOTA UNLIMITED ON TEMP; GRANT CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER TO RMAN; 2 - Criando Catálogo [oracle@denilson ~]$ rman target / catalog rman/rman@dbrman RMAN> CREATE CATALOG; recovery catalog created RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete É possivel agora armazenar os scripts de backup: RMAN> create global script CUMULATIVO {backup as compressed backupset incremental level 1 cumulative database plus archivelog delete input; crosscheck archivelog all; crosscheck backupset; delete noprompt obsolete; delete expired archivelog all; delete expired backupset;} RMAN> cre

"OPT_PARAM" 11gR2

"OPT_PARAM" é o novo hint otimizador disponível agora no release 2 da versão 11g. Com esse HINT é possível colocar valores que eram possíves apenas utilizando ALTER SESSION / ALTER SYSTEM, porém só para a sua query. Exemplo: SQL> select /*+ opt_param('hash_join_enabled','false') */ empno from emp e, dept d where e.ename=d.dname;

Identificando os Objetos corrompidos reportados pelo RMAN

1 - Identificando os Objetos corrompidos: (este comando popula a view v$database_block_corruption) RMAN> backup validate check logical database; SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO --------------- --------------- --------------- ------------------ --------- 6 10 1 8183236781662 LOGICAL 6 42 1 0 FRACTURED 6 34 1 0 CHECKSUM 6 50 1 8183236781952 LOGICAL 6 26 1 8183242041269 LOGICAL 5 rows selected. 2 - Identificando os segmentos corrompidos: create table corrupted_objects as select owner, segment_name, partition_name, segment_type, 1 file#, 1 block# from dba_extents where 1=2; declare cursor C1 is select file#, block# from v$database_block_corrup

Clonando database usando RMAN

Database de Origem: oracle@denilson:~> rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Oct 1 15:07:49 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORCL (DBID=3270432501) RMAN> backup database plus archivelog; -- mais informações sobre RMAN: http://dicasoracledba.blogspot.com/2009/05/backup-rapido-e-simples-com-rman.html * Copiar todos os backup pieces * Copiar controlfile backup piece * Copiar arquivo de parametros (init.ora) Database Destino: oracle@denilson_bkp:~> rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 19 15:07:49 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database (not started) RMAN> startup nomount; Oracle instance started Total System Global Area 205520896 bytes Fixed Size 1218508 bytes Variable Size 75499572 bytes Database Buffers 121634816 bytes Redo Buffers 7168000 bytes Restaurar controlfile do backup piece: RMAN> restor