Dicas ORACLE DBA

Editar o arquivo opmn.xml: " " " " ... " " " " " " " " " " " " ... Na versão 10.1.3.0.0 os parametros -out e -err são adicionados na tag " ", Já na versão 10.1.3.1.0, são adicionados na tag " ". Na versão 10.1.2 é necessário o Patch 4489980 Efetivando as alterações: $ORACLE_HOME/dcm/bin/dcmctl updateconfig -ct opmn (10.1.2) $ORACLE_HOME/opmn/bin/opmnctl reload $ORACLE_HOME/opmn/bin/opmnctl stopproc process-type=OC4J_Portal $ORACLE_HOME/opmn/bin/opmnctl startproc process-type=OC4J_Portal

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...

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; /

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,' ' )||...

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

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" é 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;

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...

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...