Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


sábado, 29 de julho de 2017

LogMiner - Analisando o conteúdo de um ARCHIVED REDO LOG através da package DBMS_LOGMNR

Por Eduardo Legatti

Olá,

Quando precisamos visualizar o conteúdo dos arquivos de redo log, utilizamos um recurso presente no banco de dados Oracle chamado LogMiner. O objetivo deste artigo é apresentar de forma simples como podemos visualizar o conteúdo de um ou mais arquivos de redo log arquivados através da package DBMS_LOGMNR.

Para facilitar a visualização física dos archived logs gerados, irei alterar o destino da criação dos mesmos conforme abaixo.

SQL> alter system set log_archive_dest_1='LOCATION=/oradata/archivelog';

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata/archivelog
Oldest online log sequence     49
Next log sequence to archive   51
Current log sequence           51

De acordo com a documentação, existem dois tipos de logs sumplementares (supplemental logging) que podem ser habilitados no banco de dados:

Database-Level Supplemental Logging

There are two types of database-level supplemental logging: minimal supplemental logging and identification key logging, as described in the following sections. Minimal supplemental logging does not impose significant overhead on the database generating the redo log files. However, enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Oracle recommends that you at least enable minimal supplemental logging for LogMiner.

Minimal Supplemental Logging

Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables.

Por enquanto não existe nenhum tipo de log suplementar ativado para os arquivos de redo log conforme demonstrado abaixo.

SQL> select
  2  supplemental_log_data_min,
  3  supplemental_log_data_pk,
  4  supplemental_log_data_ui
  5  from v$database;

SUPPLEME SUP SUP
-------- --- ---
NO       NO  NO

O próximo passo é simular algumas operações no banco de dados conforme a seguir.

SQL> create table scott.emp (id number, data date);

Table created.

SQL> insert into scott.emp values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (2,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (3,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (4,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (5,sysdate);

1 row created.

SQL> rollback;

Rollback complete.

SQL> insert into scott.emp values (6,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

A seguir irei habilitar o log suplementar mínimo (Minimal Supplemental Logging) que não causa nenhuma overhead e realizar mais algumas operações no banco de dados.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> select
  2  supplemental_log_data_min,
  3  supplemental_log_data_pk,
  4  supplemental_log_data_ui
  5  from v$database;

SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO

SQL> insert into scott.emp values (7,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.emp values (8,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into scott.emp values (9,sysdate);

1 row created.

SQL> rollback;

Rollback complete.

SQL> insert into scott.emp values (10,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

Após a execução dos comandos acima, os seguintes arquivos de redo log arquivados abaixo foram gerados.

[oracle@linux1 /]$ ls -lh /oradata/archivelog/
total 7,0M
-rw-r----- 1 oracle oinstall 2,9M Jul 29 12:55 1_51_804786092.dbf
-rw-r----- 1 oracle oinstall 1,9M Jul 29 13:26 1_52_804786092.dbf
-rw-r----- 1 oracle oinstall 1,0K Jul 29 13:26 1_53_804786092.dbf
-rw-r----- 1 oracle oinstall 2,2M Jul 29 14:16 1_54_804786092.dbf
-rw-r----- 1 oracle oinstall 111K Jul 29 14:24 1_55_804786092.dbf

SQL> select name,sequence#,first_time,next_time,completion_time
  2  from v$archived_log
  3  where name like '%1_5__804786092.dbf%';

NAME                                    SEQUENCE# FIRST_TIME          NEXT_TIME           COMPLETION_TIME
--------------------------------------- ---------- ------------------- ------------------- -------------------
/oradata/archivelog/1_51_804786092.dbf          51 29/07/2017 12:01:48 29/07/2017 12:55:52 29/07/2017 12:55:53
/oradata/archivelog/1_52_804786092.dbf          52 29/07/2017 12:55:52 29/07/2017 13:26:18 29/07/2017 13:26:19
/oradata/archivelog/1_53_804786092.dbf          53 29/07/2017 13:26:18 29/07/2017 13:26:26 29/07/2017 13:26:27
/oradata/archivelog/1_54_804786092.dbf          54 29/07/2017 13:26:26 29/07/2017 14:16:33 29/07/2017 14:16:33
/oradata/archivelog/1_55_804786092.dbf          55 29/07/2017 14:16:33 29/07/2017 14:24:57 29/07/2017 14:24:57

Como demonstrado acima, foi verificado que foram gerados arquivos de redo log arquivados da sequencia 51 à 55. Portanto, irei analisar o conteúdo dos mesmos com a package DBMS_LOGMNR conforme demonstrado abaixo.

SQL> BEGIN
  2   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_51_804786092.dbf',OPTIONS => DBMS_LOGMNR.NEW);
  3   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_52_804786092.dbf');
  4   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_53_804786092.dbf');
  5   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_54_804786092.dbf');
  6   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oradata/archivelog/1_55_804786092.dbf');
  7   DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
  8  END;
  9  /

PL/SQL procedure successfully completed.

Depois de adicionar os arquivos de redo log arquivados acima, irei visualizar o conteúdo dos mesmos através da view V$LOGMNR_CONTENTS.

SQL> SELECT rbasqn,
  2         timestamp,
  3         operation,
  4         seg_owner,
  5         table_name,
  6         table_space,
  7         username,
  8         os_username,
  9         machine_name,
 10         session#,
 11         serial#,
 12         session_info,
 13         sql_redo,
 14         sql_undo
 15  FROM v$logmnr_contents
 16  WHERE seg_owner IN ('SCOTT')
 17  ORDER BY timestamp;

    RBASQN TIMESTAMP           OPERATION      SEG_OWNER    TABLE_NAME   TABLE_SPACE  USERNAME   OS_USERNAME   MACHINE_NAME        SESSION#    SERIAL# SESSION_INFO                                                                                                                                                                      SQL_REDO                                                                                                      SQL_UNDO
---------- ------------------- -------------- ------------ ------------ ------------ ---------- ------------- ------------------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------
        51 29/07/2017 12:11:31 DDL            SCOTT        EMP                       UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           create table scott.emp (id number, data date);
        51 29/07/2017 12:12:23 INSERT         SCOTT        EMP          USERS        UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           insert into "SCOTT"."EMP"("ID","DATA") values ('1',TO_DATE('19/07/2017 12:12:18', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '1' and "DATA" = TO_DATE('19/07/2017 12:12:18', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAA';
        51 29/07/2017 12:35:19 DELETE         SCOTT        EMP          USERS        UNKNOWN    UNKNOWN       UNKNOWN                      0          0 UNKNOWN                                                                                                                                                                           delete from "SCOTT"."EMP" where ROWID = 'AAADZtAAEAAAACcAAE';
        54 29/07/2017 14:15:37 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('7',TO_DATE('19/07/2017 14:15:34', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '7' and "DATA" = TO_DATE('19/07/2017 14:15:34', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAF';
        54 29/07/2017 14:16:19 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('8',TO_DATE('19/07/2017 14:16:19', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '8' and "DATA" = TO_DATE('19/07/2017 14:16:19', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAG';
        55 29/07/2017 14:22:06 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('9',TO_DATE('19/07/2017 14:22:04', 'dd/mm/yyyy hh24:mi:ss'));  delete from "SCOTT"."EMP" where "ID" = '9' and "DATA" = TO_DATE('19/07/2017 14:22:04', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAH';
        55 29/07/2017 14:22:08 DELETE         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   delete from "SCOTT"."EMP" where ROWID = 'AAADZtAAEAAAACcAAH';
        55 29/07/2017 14:24:42 INSERT         SCOTT        EMP          USERS        SYS        oracle        linux1.localdomain          36         49 login_username=SYS client_info= OS_username=oracle Machine_name=linux1.localdomain OS_terminal=pts/0 OS_process_id=14479 OS_program_name=sqlplus@linux1.localdomain (TNS V1-V3)   insert into "SCOTT"."EMP"("ID","DATA") values ('10',TO_DATE('19/07/2017 14:24:41', 'dd/mm/yyyy hh24:mi:ss')); delete from "SCOTT"."EMP" where "ID" = '10' and "DATA" = TO_DATE('19/07/2017 14:24:41', 'dd/mm/yyyy hh24:mi:ss') and ROWID = 'AAADZtAAEAAAACcAAH';

8 linhas selecionadas.

Pelo resultado acima é possível perceber que as instruções realizadas antes de habilitar o log suplementar mínimo não estão completas ou ausentes e que somente após habilitar o log suplementar mínimo é que as informações e instruções SQL executadas foram logadas de forma mais completa.

Por fim, apra desabilitar o recurso LogMiner, basta apenas executar o comando abaixo.

SQL> exec DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

terça-feira, 20 de junho de 2017

Abordando a instalação do Oracle em modo texto no Linux

Por Eduardo Legatti

Olá,

Instalações do software Oracle em sistemas Linux, em muitos casos são feitas utilizando a interface gráfica GNOME, KDE ou similares. Através do VNC, é possível acessar remotamente o servidor e realizar a instalação do Oracle. No entanto, não é todo servidor que possui a interface gráfica instalada, e nesse caso, a única solução é realizar a instalação utilizando o modo silencioso (silent) através de um arquivo de resposta (response file). Portanto, segue abaixo a instalação do Oracle 11g (11.2.0.4) e posteriormente a criação de um banco de dados através do DBCA também em modo texto.

Para a instalação do software Oracle, utilizarei o arquivo db_install.rsp criado após a descompactação dos arquivos de instalação abaixo. 
  • p13390677_112040_Linux-x86-64_1of7.zip
  • p13390677_112040_Linux-x86-64_2of7.zip
O sistema operacional utilizado é o Linux Centos 7.3 (x64).

[oracle@linux1 ~]$ cat /install/database/response/db_install.rsp
####################################################################
## Copyright(c) Oracle Corporation 1998,2013. All rights reserved.##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## can help to populate the variables with the appropriate        ##
## values.                                                        ##
##                                                                ##
## IMPORTANT NOTE: This file contains plain text passwords and    ##
## should be secured to have read permission only by oracle user  ##
## or db administrator who owns this installation.                ##
##                                                                ##
####################################################################

#------------------------------------------------------------------------------
# Do not change the following system generated value. 
#------------------------------------------------------------------------------
oracle.install.responseFileVersion=/install/database/response/db_install.rsp

#------------------------------------------------------------------------------
# Specify the installation option.
# It can be one of the following:
#   - INSTALL_DB_SWONLY
#   - INSTALL_DB_AND_CONFIG
#   - UPGRADE_DB
#-------------------------------------------------------------------------------
oracle.install.option=INSTALL_DB_SWONLY

#-------------------------------------------------------------------------------
# Specify the hostname of the system as set during the install. It can be used
# to force the installation to use an alternative hostname rather than using the
# first hostname found on the system. (e.g., for systems with multiple hostnames 
# and network interfaces)
#-------------------------------------------------------------------------------
ORACLE_HOSTNAME=Linux1

#-------------------------------------------------------------------------------
# Specify the Unix group to be set for the inventory directory.  
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall

#-------------------------------------------------------------------------------
# Specify the location which holds the inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/u01/app/oraInventory

#-------------------------------------------------------------------------------
# Specify the languages in which the components will be installed.             
# 
# en   : English                  ja   : Japanese                  
# fr   : French                   ko   : Korean                    
# ar   : Arabic                   es   : Latin American Spanish    
# bn   : Bengali                  lv   : Latvian                   
# pt_BR: Brazilian Portuguese     lt   : Lithuanian                
# bg   : Bulgarian                ms   : Malay                     
# fr_CA: Canadian French          es_MX: Mexican Spanish           
# ca   : Catalan                  no   : Norwegian                 
# hr   : Croatian                 pl   : Polish                    
# cs   : Czech                    pt   : Portuguese                
# da   : Danish                   ro   : Romanian                  
# nl   : Dutch                    ru   : Russian                   
# ar_EG: Egyptian                 zh_CN: Simplified Chinese        
# en_GB: English (Great Britain)  sk   : Slovak                    
# et   : Estonian                 sl   : Slovenian                 
# fi   : Finnish                  es_ES: Spanish                   
# de   : German                   sv   : Swedish                   
# el   : Greek                    th   : Thai                      
# iw   : Hebrew                   zh_TW: Traditional Chinese       
# hu   : Hungarian                tr   : Turkish                   
# is   : Icelandic                uk   : Ukrainian                 
# in   : Indonesian               vi   : Vietnamese                
# it   : Italian                                                   
#
# all_langs   : All languages
#
# Specify value as the following to select any of the languages.
# Example : SELECTED_LANGUAGES=en,fr,ja
#
# Specify value as the following to select all the languages.
# Example : SELECTED_LANGUAGES=all_langs  
#------------------------------------------------------------------------------
SELECTED_LANGUAGES=en,pt_BR

#------------------------------------------------------------------------------
# Specify the complete path of the Oracle Home.
#------------------------------------------------------------------------------
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

#------------------------------------------------------------------------------
# Specify the complete path of the Oracle Base. 
#------------------------------------------------------------------------------
ORACLE_BASE=/u01/app/oracle

#------------------------------------------------------------------------------
# Specify the installation edition of the component.                        
#                                                             
# The value should contain only one of these choices.        
#   - EE     : Enterprise Edition                                
#   - SE     : Standard Edition                                  
#   - SEONE  : Standard Edition One
#   - PE     : Personal Edition (WINDOWS ONLY)
#------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE

#------------------------------------------------------------------------------
# This variable is used to enable or disable custom install and is considered
# only if InstallEdition is EE.
#
# true  : Components mentioned as part of 'optionalComponents' property
#         are considered for install.
# false : Value for 'optionalComponents' is not considered.
#------------------------------------------------------------------------------
oracle.install.db.EEOptionsSelection=true

#------------------------------------------------------------------------------
# This variable is considered only if 'EEOptionsSelection' is set to true. 
#
# Description: List of Enterprise Edition Options you would like to enable.
#
#              The following choices are available. You may specify any
#              combination of these choices.  The components you choose should
#              be specified in the form "internal-component-name:version"
#              Below is a list of components you may specify to enable.
#        
#              oracle.oraolap:11.2.0.4.0 - Oracle OLAP
#              oracle.rdbms.dm:11.2.0.4.0 - Oracle Data Mining
#              oracle.rdbms.dv:11.2.0.4.0 - Oracle Database Vault
#              oracle.rdbms.lbac:11.2.0.4.0 - Oracle Label Security
#              oracle.rdbms.partitioning:11.2.0.4.0 - Oracle Partitioning
#              oracle.rdbms.rat:11.2.0.4.0 - Oracle Real Application Testing
#------------------------------------------------------------------------------
oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0

###############################################################################
#                                                                             #
# PRIVILEGED OPERATING SYSTEM GROUPS                                          #
# ------------------------------------------                                  #
# Provide values for the OS groups to which OSDBA and OSOPER privileges       #
# needs to be granted. If the install is being performed as a member of the   #  
# group "dba", then that will be used unless specified otherwise below.       #
#                                                                             #
# The value to be specified for OSDBA and OSOPER group is only for UNIX based #
# Operating System.                                                           #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# The DBA_GROUP is the OS group which is to be granted OSDBA privileges.
#------------------------------------------------------------------------------
oracle.install.db.DBA_GROUP=dba

#------------------------------------------------------------------------------
# The OPER_GROUP is the OS group which is to be granted OSOPER privileges.
# The value to be specified for OSOPER group is optional.
#------------------------------------------------------------------------------
oracle.install.db.OPER_GROUP=dba

#------------------------------------------------------------------------------
# Specify the cluster node names selected during the installation.
# Example : oracle.install.db.CLUSTER_NODES=node1,node2
#------------------------------------------------------------------------------
oracle.install.db.CLUSTER_NODES=

#------------------------------------------------------------------------------
# This variable is used to enable or disable RAC One Node install.
#
#   - true  : Value of RAC One Node service name is used.
#   - false : Value of RAC One Node service name is not used.
#
# If left blank, it will be assumed to be false
#------------------------------------------------------------------------------
oracle.install.db.isRACOneInstall=

#------------------------------------------------------------------------------
# Specify the name for RAC One Node Service. 
#------------------------------------------------------------------------------
oracle.install.db.racOneServiceName=

#------------------------------------------------------------------------------
# Specify the type of database to create.
# It can be one of the following:
#   - GENERAL_PURPOSE/TRANSACTION_PROCESSING             
#   - DATA_WAREHOUSE                                
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=

#------------------------------------------------------------------------------
# Specify the Starter Database Global Database Name. 
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.globalDBName=

#------------------------------------------------------------------------------
# Specify the Starter Database SID.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.SID=

#------------------------------------------------------------------------------
# Specify the Starter Database character set.
#                                              
# It can be one of the following:
# AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,
# EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,
# BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,
# AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,
# IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,
# KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,
# ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.characterSet=

#------------------------------------------------------------------------------
# This variable should be set to true if Automatic Memory Management 
# in Database is desired.
# If Automatic Memory Management is not desired, and memory allocation
# is to be done manually, then set it to false.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryOption=true

#------------------------------------------------------------------------------
# Specify the total memory allocation for the database. Value(in MB) should be
# at least 256 MB, and should not exceed the total physical memory available 
# on the system.
# Example: oracle.install.db.config.starterdb.memoryLimit=512
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryLimit=

#------------------------------------------------------------------------------
# This variable controls whether to load Example Schemas onto
# the starter database or not.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.installExampleSchemas=false

#------------------------------------------------------------------------------
# This variable includes enabling audit settings, configuring password profiles
# and revoking some grants to public. These settings are provided by default. 
# These settings may also be disabled.    
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.enableSecuritySettings=true

###############################################################################
#                                                                             #
# Passwords can be supplied for the following four schemas in the             #
# starter database:                                                           #
#   SYS                                                                       #
#   SYSTEM                                                                    #
#   SYSMAN (used by Enterprise Manager)                                       #
#   DBSNMP (used by Enterprise Manager)                                       #
#                                                                             #
# Same password can be used for all accounts (not recommended)                #
# or different passwords for each account can be provided (recommended)       #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# This variable holds the password that is to be used for all schemas in the
# starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.ALL=

#-------------------------------------------------------------------------------
# Specify the SYS password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYS=

#-------------------------------------------------------------------------------
# Specify the SYSTEM password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSTEM=

#-------------------------------------------------------------------------------
# Specify the SYSMAN password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSMAN=

#-------------------------------------------------------------------------------
# Specify the DBSNMP password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.DBSNMP=

#-------------------------------------------------------------------------------
# Specify the management option to be selected for the starter database. 
# It can be one of the following:
#   - GRID_CONTROL
#   - DB_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.control=DB_CONTROL

#-------------------------------------------------------------------------------
# Specify the Management Service to use if Grid Control is selected to manage 
# the database.      
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=

###############################################################################
#                                                                             #
# SPECIFY BACKUP AND RECOVERY OPTIONS                                         #
# ------------------------------------                                        #
# Out-of-box backup and recovery options for the database can be mentioned    #
# using the entries below.                                                    # 
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# This variable is to be set to false if automated backup is not required. Else 
# this can be set to true.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.enable=false

#------------------------------------------------------------------------------
# Regardless of the type of storage that is chosen for backup and recovery, if 
# automated backups are enabled, a job will be scheduled to run daily to backup 
# the database. This job will run as the operating system user that is 
# specified in this variable.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.osuid=

#-------------------------------------------------------------------------------
# Regardless of the type of storage that is chosen for backup and recovery, if 
# automated backups are enabled, a job will be scheduled to run daily to backup 
# the database. This job will run as the operating system user specified by the 
# above entry. The following entry stores the password for the above operating 
# system user.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.ospwd=

#-------------------------------------------------------------------------------
# Specify the type of storage to use for the database.
# It can be one of the following:
#   - FILE_SYSTEM_STORAGE
#   - ASM_STORAGE
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=

#-------------------------------------------------------------------------------
# Specify the database file location which is a directory for datafiles, control
# files, redo logs.         
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE 
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=

#-------------------------------------------------------------------------------
# Specify the backup and recovery location.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE 
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=

#-------------------------------------------------------------------------------
# Specify the existing ASM disk groups to be used for storage.
#
# Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.diskGroup=

#-------------------------------------------------------------------------------
# Specify the password for ASMSNMP user of the ASM instance.                  
#
# Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE 
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.ASMSNMPPassword=

#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username.
#
#  Example   : MYORACLESUPPORT_USERNAME=abc@oracle.com
#------------------------------------------------------------------------------
MYORACLESUPPORT_USERNAME=

#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username password.
#
# Example    : MYORACLESUPPORT_PASSWORD=password
#------------------------------------------------------------------------------
MYORACLESUPPORT_PASSWORD=

#------------------------------------------------------------------------------
# Specify whether to enable the user to set the password for
# My Oracle Support credentials. The value can be either true or false.
# If left blank it will be assumed to be false.
#
# Example    : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true
#------------------------------------------------------------------------------
SECURITY_UPDATES_VIA_MYORACLESUPPORT=

#------------------------------------------------------------------------------
# Specify whether user doesn't want to configure Security Updates.
# The value for this variable should be true if you don't want to configure
# Security Updates, false otherwise. 
#
# The value can be either true or false. If left blank it will be assumed
# to be false.
#
# Example    : DECLINE_SECURITY_UPDATES=false
#------------------------------------------------------------------------------
DECLINE_SECURITY_UPDATES=true

#------------------------------------------------------------------------------
# Specify the Proxy server name. Length should be greater than zero.
#
# Example    : PROXY_HOST=proxy.domain.com 
#------------------------------------------------------------------------------
PROXY_HOST=

#------------------------------------------------------------------------------
# Specify the proxy port number. Should be Numeric and at least 2 chars.
#
# Example    : PROXY_PORT=25 
#------------------------------------------------------------------------------
PROXY_PORT=

#------------------------------------------------------------------------------
# Specify the proxy user name. Leave PROXY_USER and PROXY_PWD 
# blank if your proxy server requires no authentication.
#
# Example    : PROXY_USER=username 
#------------------------------------------------------------------------------
PROXY_USER=

#------------------------------------------------------------------------------
# Specify the proxy password. Leave PROXY_USER and PROXY_PWD  
# blank if your proxy server requires no authentication.
#
# Example    : PROXY_PWD=password 
#------------------------------------------------------------------------------
PROXY_PWD=

#------------------------------------------------------------------------------
# Specify the proxy realm. This value is used if auto-updates option is selected.
#
# Example    : PROXY_REALM=metalink 
#------------------------------------------------------------------------------
PROXY_REALM=

#------------------------------------------------------------------------------
# Specify the Oracle Support Hub URL. 
# 
# Example    : COLLECTOR_SUPPORTHUB_URL=https://orasupporthub.company.com:8080/
#------------------------------------------------------------------------------
COLLECTOR_SUPPORTHUB_URL=

#------------------------------------------------------------------------------
# Specify the auto-updates option. It can be one of the following:
#   - MYORACLESUPPORT_DOWNLOAD
#   - OFFLINE_UPDATES
#   - SKIP_UPDATES
#------------------------------------------------------------------------------
oracle.installer.autoupdates.option=SKIP_UPDATES
#------------------------------------------------------------------------------
# In case MYORACLESUPPORT_DOWNLOAD option is chosen, specify the location where
# the updates are to be downloaded.
# In case OFFLINE_UPDATES option is chosen, specify the location where the updates 
# are present.
#------------------------------------------------------------------------------
oracle.installer.autoupdates.downloadUpdatesLoc=
#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username which has the patches download privileges  
# to be used for software updates.
#  Example   : AUTOUPDATES_MYORACLESUPPORT_USERNAME=abc@oracle.com
#------------------------------------------------------------------------------
AUTOUPDATES_MYORACLESUPPORT_USERNAME=

#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username password which has the patches download privileges  
# to be used for software updates.
#
# Example    : AUTOUPDATES_MYORACLESUPPORT_PASSWORD=password
#------------------------------------------------------------------------------
AUTOUPDATES_MYORACLESUPPORT_PASSWORD=

Uma vez informada as opções de instalação no arquivo db_install.rsp, irei iniciar a instalação conforme demonstrado abaixo.

[oracle@linux1 ~]$ cd /install/database
[oracle@linux1 ~]$ ./runInstaller -silent -responseFile /install/database/response/db_install.rsp -waitforcompletion -ignorePrereq -showProgress
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 76999 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3066 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-06-19_08-45-47AM. Please wait ...You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2017-06-20_06-45-47AM.log

Prepare in progress.
..................................................   9% Done.

Prepare successful.

Copy files in progress.
..................................................   14% Done.
..................................................   20% Done.
..................................................   26% Done.
..................................................   31% Done.
..................................................   36% Done.
..................................................   44% Done.
..................................................   49% Done.
..................................................   54% Done.
..................................................   59% Done.
..................................................   64% Done.
..................................................   69% Done.
..................................................   74% Done.
..................................................   79% Done.
..................................................   84% Done.
....................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..................................................   89% Done.
..................................................   94% Done.

Setup files successful.
The installation of Oracle Database 11g was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2017-06-20_06-45-47AM.log' for more details.

Execute Root Scripts in progress.

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/oracle/product/11.2.0/dbhome_1/root.sh


..................................................   100% Done.

Execute Root Scripts successful.

Após a instalação, será necessário executar os scripts shell a seguir com o usuário root.

[root@linux1 ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

[root@linux1 ~]# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0/dbhome_1/install/root_linux1_2017-06-20_06-59-50.log for the output of root script

Como já demonstrado no artigo de Outubro/2011, o próximo passo é criar um banco de dados utilizando o utilitário DBCA em modo texto. Neste caso irei editar clonar o template New_Database.dbt com o nome de New_Database2.dbt e editá-lo conforme abaixo. Vale a pena salientar que dentro do template irei criar uma variável com nome de {ORADATA} que será usada para o destino dos arquivos do banco de dados BD01. Obs: Para fins estéticos, abaixo eu substituí os caracteres maior/menor por colchetes.

[oracle@linux1 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/New_Database2.dbt
[DatabaseTemplate name="New Database" description="" version="11.2.0.0.0"]
   [CommonAttributes]
      [option name="OMS" value="false"/]
      [option name="JSERVER" value="false"/]
      [option name="SPATIAL" value="false"/]
      [option name="IMEDIA" value="false"/]
      [option name="ORACLE_TEXT" value="false"]
         [tablespace id="SYSAUX"/]
      [/option]
      [option name="XDB_PROTOCOLS" value="false"]
         [tablespace id="SYSAUX"/]
      [/option]
      [option name="CWMLITE" value="false"]
         [tablespace id="SYSAUX"/]
      [/option]
      [option name="EM_REPOSITORY" value="false"]
         [tablespace id="SYSAUX"/]
      [/option]
      [option name="SAMPLE_SCHEMA" value="false"/]
      [option name="APEX" value="false"/]
      [option name="OWB" value="false"/]
      [option name="DV" value="false"/]
   [/CommonAttributes]
   [Variables/]
   [CustomScripts Execute="false"/]
   [InitParamAttributes]
      [InitParams]
         [initParam name="db_block_size" value="8" unit="KB"/]
         [initParam name="memory_target" value="250" unit="MB"/]
         [initParam name="open_cursors" value="300"/]
         [initParam name="undo_tablespace" value="UNDOTBS1"/]
         [initParam name="control_files" value="("{ORADATA}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORADATA}/oradata/{DB_UNIQUE_NAME}/control02.ctl", "{ORADATA}/oradata/{DB_UNIQUE_NAME}/control03.ctl")"/]

         [initParam name="compatible" value="11.2.0.0.0"/]
         [initParam name="processes" value="150"/]
         [initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/]
         [initParam name="audit_trail" value="db"/]
         [initParam name="diagnostic_dest" value="{ORACLE_BASE}"/]
         [initParam name="remote_login_passwordfile" value="EXCLUSIVE"/]
         [initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/]
         [initParam name="db_recovery_file_dest" value="{ORACLE_BASE}/fast_recovery_area"/]
         [initParam name="db_recovery_file_dest_size" value="" unit="MB"/]
      [/InitParams]
      [MiscParams]
         [databaseType]OLTP[/databaseType]
         [maxUserConn]20[/maxUserConn]
         [percentageMemTOSGA]40[/percentageMemTOSGA]
         [customSGA]true[/customSGA]
         [archiveLogMode]false[/archiveLogMode]
         [initParamFileName]{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora[/initParamFileName]
      [/MiscParams]
      [SPfile useSPFile="true"]{ORACLE_HOME}/dbs/spfile{SID}.ora[/SPfile]
   [/InitParamAttributes]
   [StorageAttributes]
      [ControlfileAttributes id="Controlfile"]
         [maxDatafiles]100[/maxDatafiles]
         [maxLogfiles]16[/maxLogfiles]
         [maxLogMembers]3[/maxLogMembers]
         [maxLogHistory]1[/maxLogHistory]
         [maxInstances]8[/maxInstances]
         [image name="control01.ctl" filepath="{ORADATA}/oradata/{DB_UNIQUE_NAME}/"/]
         [image name="control02.ctl" filepath="{ORADATA}/oradata/{DB_UNIQUE_NAME}/"/]
         [image name="control03.ctl" filepath="{ORADATA}/oradata/{DB_UNIQUE_NAME}/"/]

      [/ControlfileAttributes]
      [DatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf"]
         [tablespace]SYSAUX[/tablespace]
         [temporary]false[/temporary]
         [online]true[/online]
         [status]0[/status]
         [size unit="MB"]600[/size]
         [reuse]true[/reuse]
         [autoExtend]true[/autoExtend]
         [increment unit="KB"]10240[/increment]
         [maxSize unit="MB"]-1[/maxSize]
      [/DatafileAttributes]
      [DatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/users01.dbf"]
         [tablespace]USERS[/tablespace]
         [temporary]false[/temporary]
         [online]true[/online]
         [status]0[/status]
         [size unit="MB"]5[/size]
         [reuse]true[/reuse]
         [autoExtend]true[/autoExtend]
         [increment unit="KB"]1280[/increment]
         [maxSize unit="MB"]-1[/maxSize]
      [/DatafileAttributes]
      [DatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/system01.dbf"]
         [tablespace]SYSTEM[/tablespace]
         [temporary]false[/temporary]
         [online]true[/online]
         [status]0[/status]
         [size unit="MB"]700[/size]
         [reuse]true[/reuse]
         [autoExtend]true[/autoExtend]
         [increment unit="KB"]10240[/increment]
         [maxSize unit="MB"]-1[/maxSize]
      [/DatafileAttributes]
      [DatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/temp01.dbf"]
         [tablespace]TEMP[/tablespace]
         [temporary]false[/temporary]
         [online]true[/online]
         [status]0[/status]
         [size unit="MB"]20[/size]
         [reuse]true[/reuse]
         [autoExtend]true[/autoExtend]
         [increment unit="KB"]640[/increment]
         [maxSize unit="MB"]-1[/maxSize]
      [/DatafileAttributes]
      [DatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf"]
         [tablespace]UNDOTBS1[/tablespace]
         [temporary]false[/temporary]
         [online]true[/online]
         [status]0[/status]
         [size unit="MB"]200[/size]
         [reuse]true[/reuse]
         [autoExtend]true[/autoExtend]
         [increment unit="KB"]5120[/increment]
         [maxSize unit="MB"]-1[/maxSize]
      [/DatafileAttributes]
      [TablespaceAttributes id="SYSAUX"]
         [online]true[/online]
         [offlineMode]1[/offlineMode]
         [readOnly]false[/readOnly]
         [temporary]false[/temporary]
         [defaultTemp]false[/defaultTemp]
         [undo]false[/undo]
         [local]true[/local]
         [blockSize]-1[/blockSize]
         [allocation]1[/allocation]
         [uniAllocSize unit="KB"]-1[/uniAllocSize]
         [initSize unit="KB"]64[/initSize]
         [increment unit="KB"]64[/increment]
         [incrementPercent]50[/incrementPercent]
         [minExtends]1[/minExtends]
         [maxExtends]4096[/maxExtends]
         [minExtendsSize unit="KB"]64[/minExtendsSize]
         [logging]true[/logging]
         [recoverable]false[/recoverable]
         [maxFreeSpace]0[/maxFreeSpace]
         [bigfile]false[/bigfile]
         [datafilesList]
            [TablespaceDatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf"]
               [id]-1[/id]
            [/TablespaceDatafileAttributes]
         [/datafilesList]
      [/TablespaceAttributes]
      [TablespaceAttributes id="USERS"]
         [online]true[/online]
         [offlineMode]1[/offlineMode]
         [readOnly]false[/readOnly]
         [temporary]false[/temporary]
         [defaultTemp]false[/defaultTemp]
         [undo]false[/undo]
         [local]true[/local]
         [blockSize]-1[/blockSize]
         [allocation]1[/allocation]
         [uniAllocSize unit="KB"]-1[/uniAllocSize]
         [initSize unit="KB"]128[/initSize]
         [increment unit="KB"]128[/increment]
         [incrementPercent]0[/incrementPercent]
         [minExtends]1[/minExtends]
         [maxExtends]4096[/maxExtends]
         [minExtendsSize unit="KB"]128[/minExtendsSize]
         [logging]true[/logging]
         [recoverable]false[/recoverable]
         [maxFreeSpace]0[/maxFreeSpace]
         [bigfile]false[/bigfile]
         [datafilesList]
            [TablespaceDatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/users01.dbf"]
               [id]-1[/id]
            [/TablespaceDatafileAttributes]
         [/datafilesList]
      [/TablespaceAttributes]
      [TablespaceAttributes id="SYSTEM"]
         [online]true[/online]
         [offlineMode]1[/offlineMode]
         [readOnly]false[/readOnly]
         [temporary]false[/temporary]
         [defaultTemp]false[/defaultTemp]
         [undo]false[/undo]
         [local]true[/local]
         [blockSize]-1[/blockSize]
         [allocation]3[/allocation]
         [uniAllocSize unit="KB"]-1[/uniAllocSize]
         [initSize unit="KB"]64[/initSize]
         [increment unit="KB"]64[/increment]
         [incrementPercent]50[/incrementPercent]
         [minExtends]1[/minExtends]
         [maxExtends]-1[/maxExtends]
         [minExtendsSize unit="KB"]64[/minExtendsSize]
         [logging]true[/logging]
         [recoverable]false[/recoverable]
         [maxFreeSpace]0[/maxFreeSpace]
         [bigfile]false[/bigfile]
         [datafilesList]
            [TablespaceDatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/system01.dbf"]
               [id]-1[/id]
            [/TablespaceDatafileAttributes]
         [/datafilesList]
      [/TablespaceAttributes]
      [TablespaceAttributes id="TEMP"]
         [online]true[/online]
         [offlineMode]1[/offlineMode]
         [readOnly]false[/readOnly]
         [temporary]true[/temporary]
         [defaultTemp]true[/defaultTemp]
         [undo]false[/undo]
         [local]true[/local]
         [blockSize]-1[/blockSize]
         [allocation]1[/allocation]
         [uniAllocSize unit="KB"]-1[/uniAllocSize]
         [initSize unit="KB"]64[/initSize]
         [increment unit="KB"]64[/increment]
         [incrementPercent]0[/incrementPercent]
         [minExtends]1[/minExtends]
         [maxExtends]0[/maxExtends]
         [minExtendsSize unit="KB"]64[/minExtendsSize]
         [logging]true[/logging]
         [recoverable]false[/recoverable]
         [maxFreeSpace]0[/maxFreeSpace]
         [bigfile]false[/bigfile]
         [datafilesList]
            [TablespaceDatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/temp01.dbf"]
               [id]-1[/id]
            [/TablespaceDatafileAttributes]
         [/datafilesList]
      [/TablespaceAttributes]
      [TablespaceAttributes id="UNDOTBS1"]
         [online]true[/online]
         [offlineMode]1[/offlineMode]
         [readOnly]false[/readOnly]
         [temporary]false[/temporary]
         [defaultTemp]false[/defaultTemp]
         [undo]true[/undo]
         [local]true[/local]
         [blockSize]-1[/blockSize]
         [allocation]1[/allocation]
         [uniAllocSize unit="KB"]-1[/uniAllocSize]
         [initSize unit="KB"]512[/initSize]
         [increment unit="KB"]512[/increment]
         [incrementPercent]50[/incrementPercent]
         [minExtends]8[/minExtends]
         [maxExtends]4096[/maxExtends]
         [minExtendsSize unit="KB"]512[/minExtendsSize]
         [logging]true[/logging]
         [recoverable]false[/recoverable]
         [maxFreeSpace]0[/maxFreeSpace]
         [bigfile]false[/bigfile]
         [datafilesList]
            [TablespaceDatafileAttributes id="{ORADATA}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf"]
               [id]-1[/id]
            [/TablespaceDatafileAttributes]
         [/datafilesList]
      [/TablespaceAttributes]
      [RedoLogGroupAttributes id="1"]
         [reuse]false[/reuse]
         [fileSize unit="KB"]51200[/fileSize]
         [Thread]1[/Thread]
         [member ordinal="0" memberName="redo01.log" filepath="{ORADATA}/oradata/{DB_UNIQUE_NAME}/"/]
      [/RedoLogGroupAttributes]
      [RedoLogGroupAttributes id="2"]
         [reuse]false[/reuse]
         [fileSize unit="KB"]51200[/fileSize]
         [Thread]1[/Thread]
         [member ordinal="0" memberName="redo02.log" filepath="{ORADATA}/oradata/{DB_UNIQUE_NAME}/"/]
      [/RedoLogGroupAttributes]
      [RedoLogGroupAttributes id="3"]
         [reuse]false[/reuse]
         [fileSize unit="KB"]51200[/fileSize]
         [Thread]1[/Thread]
         [member ordinal="0" memberName="redo03.log" filepath="{ORADATA}/oradata/{DB_UNIQUE_NAME}/"/]
      [/RedoLogGroupAttributes]
   [/StorageAttributes]
[/DatabaseTemplate]

Uma vez configurado o arquivo de resposta do DBCA, realizarei a seguir a criação do banco de dados BD01.

[oracle@linux1 ~]$ dbca -silent 
                        -createDatabase
                        -templateName New_Database2.dbt
                        -gdbName BD01
                        -sysPassword manager
                        -systemPassword manager
                        -characterset WE8MSWIN1252
                        -nationalCharacterSet AL16UTF16
                        -emConfiguration NONE
                        -databaseType OLTP
                        -variables ORADATA=/data/
Creating and starting Oracle instance
2% complete
3% complete
5% complete
12% complete
Creating database files
13% complete
25% complete
Creating data dictionary views
28% complete
32% complete
36% complete
37% complete
38% complete
39% complete
40% complete
41% complete
42% complete
43% complete
44% complete
45% complete
46% complete
47% complete
48% complete
55% complete
59% complete
62% complete
63% complete
66% complete
Completing Database Creation
70% complete
73% complete
77% complete
88% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/BD01/BD01.log" for further details.

Como último passo, basta apenas iniciar o processo do LISTENER conforme a seguir.

[oracle@linux1 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2017 07:06:39

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

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/linux1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux1)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                20-JUN-2017 07:06:40
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/linux1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux1)(PORT=1521)))
The listener supports no services
The command completed successfully

terça-feira, 23 de maio de 2017

Monitorando alternâncias do redo log online no Oracle (Redo Log Switch Frequency)

Por Eduardo Legatti

Olá,

Uma situação que pode acontecer em um ambiente de banco de dados Oracle é o aumento expressivo no número de dados de "redo" gerados pelo aumento do número de transações ao longo do tempo ou até mesmo por alguma operação isolada. A consequência imediata no número de transações que afetam grandes quantidades de dados é o número excessivo de "redo log switches" ou alternâncias de log gerados um curto espaço de tempo. Em ambientes Oracle que operam em modo ARCHIVELOG a geração excessiva de archive logs por conta de várias operações de alternâncias de log em um curto espaço de tempo pode ocasionar problemas como: espaço livre em disco, tempo de backup, entre outros. Portanto, é importante monitorar a quantidade de redo log switches ocorridas na última hora. Vale a pena salientar que o tamanho do arquivo de redo log online influencia na quantidade de alternâncias de log realizadas.

O cenário abaixo ocorreu em um ambiente de banco de dados com redo log de tamanho de 50 MB no qual o padrão era gerar em média 23 alternância de log por dia, ou seja, cerca de uma alternância por hora. No entanto, foi detectado que a partir do dia 09/05 às 11:00 hs, conforme resultado da consulta abaixo, as operações de redo log switches aumentaram drasticamente gerando cerca de 770 alternâncias por dia. O impacto disso foi um aumento significativo de archive logs gerados, consumindo espaço extra na flash recovery area. Uma vez detectado o problema, foi averiguado que a aplicação tinha sido configurada para fazer mais coisas do que deveria e que, a partir do dia 21/05 às 16:00 hs, as operações de alternância de redo log foram reduzidas significativamente.

SQL> select to_char(first_time,'DD/MM/YYYY') day,
  2  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
  3  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
  4  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
  5  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
  6  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
  7  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
  8  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
  9  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
 10  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
 11  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
 12  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
 13  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
 14  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
 15  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
 16  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
 17  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
 18  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
 19  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
 20  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
 21  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
 22  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
 23  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
 24  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
 25  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
 26  sum(1) "TOTAL_IN_DAY"
 27  from v$log_history
 28  group by to_char(first_time,'DD/MM/YYYY')
 29  order by to_date(day) desc;

DAY        00   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   TOTAL_IN_DAY
---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------------
21/05/2017   32   32   33   32   34   33   34   32   33   32   32   32   33   31   32   33    5    4    2    0    0    0    0    0          531
20/05/2017   32   32   32   33   33   33   34   33   32   32   35   32   32   33   32   33   32   33   32   32   32   32   33   32          781
19/05/2017   33   32   33   32   32   32   34   33   32   32   34   32   32   32   31   33   32   32   32   34   33   32   33   32          779
18/05/2017   33   32   32   32   32   32   34   33   32   32   33   33   32   33   33   31   32   32   32   33   33   32   32   32          777
17/05/2017   33   32   33   32   33   32   34   32   32   33   32   32   33   32   32   33   32   32   33   32   32   32   33   32          778
16/05/2017   32   32   31   32   32   32   33   32   32   31   32   32   31   32   32   32   32   32   32   33   33   32   33   32          769
15/05/2017   31   32   30   32   33   32   34   31   32   31   33   31   31   32   31   33   32   32   31   32   32   31   34   31          764
14/05/2017   31   32   32   31   33   31   33   31   31   31   33   31   32   31   32   31   32   32   32   32   31   31   33   32          761
13/05/2017   31   31   31   32   31   32   33   32   31   32   32   32   32   31   31   32   32   30   32   31   32   32   33   31          759
12/05/2017   31   31   32   32   31   32   33   31   32   32   32   31   31   32   30   31   32   32   31   33   31   32   32   32          759
11/05/2017   32   31   32   31   32   31   35   31   31   31   34   31   30   31   33   31   32   31   32   32   31   32   33   31          761
10/05/2017   31   31   31   31   34   31   33   32   31   31   32   31   32   31   31   31   31   32   32   32   31   32   32   32          758
09/05/2017    0    1    0    1    2    1    2    1    1    0    2   30   32   32   32   32   32   31   32   32   31   32   34   31          424
08/05/2017    0    1    1    0    3    1    3    0    1    1    1    1    1    1    1    1    1    1    1    2    1    1    2    1           27
07/05/2017    0    1    1    0    3    1    3    0    1    1    1    1    1    1    1    1    1    1    1    2    0    1    2    1           26
06/05/2017    0    1    0    1    1    1    3    0    1    1    2    1    1    1    1    1    1    1    1    2    1    1    2    1           26
05/05/2017    0    1    0    1    0    1    3    1    0    1    2    0    1    0    1    0    1    1    0    3    0    1    1    2           21
04/05/2017    1    0    1    0    1    1    4    1    0    1    2    1    0    1    0    1    1    0    1    2    0    1    2    1           23
03/05/2017    0    1    1    0    3    1    3    1    0    1    2    1    1    1    1    1    1    1    0    2    1    1    2    1           27
02/05/2017    1    1    0    1    3    1    2    1    1    1    2    1    1    1    1    1    2    1    1    1    1    1    2    1           29
01/05/2017    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    1    2    1            5

21 linhas selecionadas.

Durante a análise, foi detectado que havia uma sessão no banco de dados que estava gerando mais dados de UNDO/REDO que as demais, conforme resultado da consulta abaixo.

SQL>   SELECT s.sid,
  2           s.serial#,
  3           s.username,
  4           s.program,
  5           t.used_ublk,
  6           t.used_urec
  7      FROM v$session s, v$transaction t
  8     WHERE s.taddr = t.addr
  9  ORDER BY 5 DESC, 6 DESC, 1, 2, 3, 4;

       SID    SERIAL# USERNAME            PROGRAM                USED_UBLK  USED_UREC
---------- ---------- ------------------- --------------------- ---------- ----------
       930      49433 SCOTT               JDBC Thin Client              88       6177
       633      53173 SCOTT               JDBC Thin Client              10        693
       250      58501 ADAM                JDBC Thin Client               9        692
        99      11223 ADAM                JDBC Thin Client               5        340
       851      51835 SCOTT               JDBC Thin Client               0          0

5 linhas selecionadas.

Outra consulta bastante útil é verificar a quantidade de "redo size" gerada para cada sessão no banco de dados.

SQL> SELECT s.sid,
  2         sn.SERIAL#,
  3         sn.username,
  4         n.name,
  5         ROUND (VALUE / 1024 / 1024, 2) redo_mb,
  6         sn.status,
  7         sn.TYPE
  8    FROM v$sesstat s
  9         JOIN v$statname n ON n.statistic# = s.statistic#
 10         JOIN v$session sn ON sn.sid = s.sid
 11   WHERE n.name LIKE 'redo size' AND s.VALUE <> 0
 12  ORDER BY redo_mb DESC;

       SID    SERIAL# USERNAME            NAME                    REDO_MB STATUS   TYPE
---------- ---------- ------------------- -------------------- ---------- -------- ----------
       848          1                     redo size               4397,06 ACTIVE   BACKGROUND
       771          1                     redo size               4367,24 ACTIVE   BACKGROUND
      1079          1                     redo size               2689,53 ACTIVE   BACKGROUND
       930      49433 SCOTT               redo size                838,87 INACTIVE USER
         8      18777 SCOTT               redo size                115,77 INACTIVE USER
       166      59825 SCOTT               redo size                172,59 INACTIVE USER
       707      52285 ADAM                redo size                141,95 INACTIVE USER
       622      34283 ADAM                redo size                139,28 INACTIVE USER
      1004      43893 ADAM                redo size                112,61 INACTIVE USER
      1009      39717 SCOTT               redo size                111,41 INACTIVE USER
       249      10097 ADAM                redo size                106,07 INACTIVE USER
       237      44045 SCOTT               redo size                181,37 INACTIVE USER
       699      40115 ADAM                redo size                174,25 INACTIVE USER
       391      58243 ADAM                redo size                154,91 INACTIVE USER

14 linhas selecionadas.

Por fim, segue abaixo uma consulta que retorna a porcentagem utilizada do arquivo de redo log online corrente antes de realizar uma operação de alternância de log.

SQL> SELECT le.lenum "Group#",
  2       le.leseq "Current log sequence No",
  3       ROUND (100 * cp.cpodr_bno / (le.lesiz - 28770), 2) "Percent Full",
  4       cp.cpodr_bno "Current Block No",
  5       le.lesiz * le.lebsz / 1024 / 1024 "Size of Log in MB"
  6  FROM x$kcccp cp, x$kccle le
  7  WHERE le.leseq = CP.cpodr_seq AND BITAND (le.leflg, 24) = 8;

    Group# Current log sequence No Percent Full Current Block No Size of Log in MB
---------- ----------------------- ------------ ---------------- -----------------
        13                  253996         37,4           372194                50

1 linha selecionada.

No mais, é recomendável que uma operação de alternância de log ocorra no mínimo a cada 20 ou 25 minutos, ou seja de 4 a 5 redo log switches por hora, caso contrário talvez seja necessário rever o tamanho dos arquivos de redo log online.


terça-feira, 18 de abril de 2017

Oracle Application Express (APEX)

Por Eduardo Legatti

Olá,

Para quem estiver interessado em criar aplicações ou até mesmo acessar um banco de dados Oracle online através de um navegador da internet, basta acessar o Oracle Application Express que é um ambiente para desenvolvimento de softwares  baseado no banco de dados Oracle. O cadastro é gratuito através do link https://apex.oracle.com/ e sua documentação pode ser acessada neste link.



https://apex.oracle.com/






sexta-feira, 3 de março de 2017

Oracle 12c Release 2 disponível para download

Por Eduardo Legatti

Olá,

Neste mês de Março/2017, a Oracle disponibilizou para download o Oracle 12c R2. Muitas características foram adicionadas desde o lançamento do Oracle 12c R1 conforme a documentação. Dentre algumas new features do Oracle 12c R2, podemos citar, por exemplo, o aumento da capacidade de criação de pluggable databases de 252 na Release 1 para 4096 na Release 2 e o aumento do tamanho máximo dos nomes de vários tipos de objetos e identificadores de 30 bytes para 128 bytes


 

Segue algumas outras características interessantes dessa nova release.

  • Shared Undo e Local Undo
  • Local TEMP Tablespaces
  • SQL*Plus Command History
  • Online Table Move where tables can be moved as an online operation without blocking any concurrent DML operations
  • Oracle Database Sharding
  • PDB Character Set
  • Flashback Pluggable Database
  • Near Zero Downtime PDB Relocation
  • PDB Hot Cloning
  • Online conversion of a nonpartitioned table to a partitioned table
  • Application Containers

quarta-feira, 15 de fevereiro de 2017

Tuning de intruções SQL: Prestar atenção ao plano de execução é o primeiro passo

Por Eduardo Legatti

Olá,

Em algum momento, todo DBA vai passar por aquela experiência de tentar melhorar a performance de uma instrução SQL e acreditar que o que foi feito até então vai resolver o problema de performance. Mas aí, após fazer as melhorias e executar o SQL, percebe-se que o mesmo plano de execução de baixa performance continua sendo utilizado. O objetivo desse artigo é chamar a atenção para que prestemos realmente mais atenção ao plano de execução gerado por uma instrução SQL antes de quebrar a cabeça com outras tentativas.

Avaliando a consulta abaixo na qual a performance não estava aceitável, é possível perceber alguns filtros e um JOIN entre as tabelas T1 e T2.

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND B.VALUE = A.EMP_CODE
 11         AND B.DEP_CODE = 13;

Analisando estruturalmente as tabelas, verifiquei que as colunas que estão fazendo JOIN (B.VALUE = A.EMP_CODE)  são de tipos de dados e tamanhos diferentes.

SQL> desc T1
 Nome                            Nulo?    Tipo
 ------------------------------- -------- ---------------------------
 EMP_CODE                         NOT NULL NUMBER(15)
 NAME                                      VARCHAR2(255)
 HASH                                      VARCHAR2(32)

SQL> desc T2
 Nome                            Nulo?    Tipo
 ------------------------------- -------- ---------------------------
 DEP_CODE                        NOT NULL NUMBER(20)
 VALUE                                    VARCHAR2(4000)

Verificando os dados da tabela T2, verifiquei que não seria possível criar um índice na coluna VALUE pois o erro "ORA-01450: maximum key length (string) exceeded" seria emitido devido a limitação existente no Oracle do tamanho máximo de valores indexados de acordo com o tamanho do bloco de dados usado pela tablespace da tabela (8 KB neste caso). Bom, a idéia então foi criar um índice baseado em função na qual a mesma limitaria o tamanho da coluna VARCHAR2(4000) para o tamanho da coluna numérica da tabela T1 (EMP_CODE). Como o tamanho da coluna EMP_CODE da tabela T1 é NUMBER(15), irei criar um índice de função SUBSTR(VALUE,1,15) na tabela T2. Como na instrução SQL existem outros filtros, irei criar 2 índices conforme demonstrado abaixo.

SQL> create index idx_name_hash on t1 (name,hash) tablespace tbs_indx noparallel;
SQL> create index idx_substr_value_depcode on t2 (substr(value,1,15),dep_code) tablespace tbs_indx noparallel;

Após criados os índices e alterada a instrução SQL para utilizar a função SUBSTR, segue abaixo o plano de execução gerado:

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND SUBSTR(B.VALUE,1,15) = A.EMP_CODE
 11         AND B.DEP_CODE = 13;

     ID NAME                 HASH                               EMP_CODE   DEP_CODE VALUE
------- -------------------- -------------------------------- ---------- ---------- --------------
      1 file.pdf             F847F75E563EC732C61DB76C239BC34C     363423   23650082 363423
      2 file.pdf             F847F75E563EC732C61DB76C239BC34C     363427   23652704 363427
      3 file.pdf             F847F75E563EC732C61DB76C239BC34C     363428   23653157 363428
      4 file.pdf             F847F75E563EC732C61DB76C239BC34C     363430   23654312 363430
      5 file.pdf             F847F75E563EC732C61DB76C239BC34C     363770   23759064 363770
      6 file.pdf             F847F75E563EC732C61DB76C239BC34C     363793   23765071 363793
      7 file.pdf             F847F75E563EC732C61DB76C239BC34C     372161   24406201 372161
      8 file.pdf             F847F75E563EC732C61DB76C239BC34C     372165   24406453 372165
      9 file.pdf             F847F75E563EC732C61DB76C239BC34C     372169   24406707 372169
     10 file.pdf             F847F75E563EC732C61DB76C239BC34C     468922   33918406 468922
     11 file.pdf             F847F75E563EC732C61DB76C239BC34C     468926   33918658 468926

11 linhas selecionadas.

Decorrido: 00:00:42

Plano de Execução
----------------------------------------------------------
Plan hash value: 540149683

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |     3 |   258 |   103K  (1)| 00:20:42 |
|   1 |  COUNT                        |                            |       |       |            |          |
|*  2 |   HASH JOIN                   |                            |     3 |   258 |   103K  (1)| 00:20:42 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                         |     3 |   177 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NAME_HASH              |     3 |       |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | T2                         |  5251K|    88M|   103K  (1)| 00:20:42 |
------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."EMP_CODE"=TO_NUMBER(SUBSTR("VALUE",1,15)))
   4 - access("A"."NAME"='file.pdf' AND "A"."HASH"='F847F75E563EC732C61DB76C239BC34C')
   5 - filter("B"."DEP_CODE"=13)

Foi possível verificar acima que a instrução SQL executou em 42 segundos, o que é péssimo, e que o índice IDX_NAME_HASH foi utilizado. No entanto, o índice de função criado na tabela T2 não foi usado e por isso está sendo realizado uma operação de TABLE ACCESS FULL na mesma em cerca de 5 milhões de linhas ao realizar uma operação de HASH JOIN. A questão é: porque o otimizador não utilizou o índice de função? Após alguns testes utilizando HINTS para forçar o uso do índice entre outras tentativas, consegui a tempo perceber que a resposta estava no próprio plano de execução na seção "Predicate Information".

É possível perceber que o Oracle implicitamente converteu o tipo de dado utilizando a função TO_NUMBER de modo a fazer o JOIN com a coluna EMP_CODE que é NUMBER(15), o que é compreensível. Neste caso, o problema pode ser resolvido com 2 opções: criar o índice como TO_NUMBER(SUBSTR(VALUE,1,15)) na tabela T1 ou utilizar a função TO_CHAR na coluna EMP_CODE como demonstrado abaixo:

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND SUBSTR(B.VALUE,1,15) = TO_CHAR(A.EMP_CODE)
 11         AND B.DEP_CODE = 13;

     ID NAME                 HASH                               EMP_CODE   DEP_CODE VALUE
------- -------------------- -------------------------------- ---------- ---------- --------------
      1 file.pdf             F847F75E563EC732C61DB76C239BC34C     468922   33918406 468922
      2 file.pdf             F847F75E563EC732C61DB76C239BC34C     468926   33918658 468926
      3 file.pdf             F847F75E563EC732C61DB76C239BC34C     363423   23650082 363423
      4 file.pdf             F847F75E563EC732C61DB76C239BC34C     363427   23652704 363427
      5 file.pdf             F847F75E563EC732C61DB76C239BC34C     363428   23653157 363428
      6 file.pdf             F847F75E563EC732C61DB76C239BC34C     363430   23654312 363430
      7 file.pdf             F847F75E563EC732C61DB76C239BC34C     363770   23759064 363770
      8 file.pdf             F847F75E563EC732C61DB76C239BC34C     363793   23765071 363793
      9 file.pdf             F847F75E563EC732C61DB76C239BC34C     372161   24406201 372161
     10 file.pdf             F847F75E563EC732C61DB76C239BC34C     372165   24406453 372165
     11 file.pdf             F847F75E563EC732C61DB76C239BC34C     372169   24406707 372169

11 linhas selecionadas.

Decorrido: 00:00:00.09

Plano de Execução
----------------------------------------------------------
Plan hash value: 424040904

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |     3 |   258 |    21   (0)| 00:00:01 |
|   1 |  COUNT                        |                              |       |       |            |          |
|   2 |   NESTED LOOPS                |                              |     3 |   258 |    21   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                           |     3 |   177 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NAME_HASH                |     3 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2                           |     1 |    27 |     5   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IDX_SUBSTR_VALUE_DEPCODE     |     4 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

   4 - access("A"."NAME"='file.pdf' AND "A"."HASH"='F847F75E563EC732C61DB76C239BC34C')
   6 - access(SUBSTR("VALUE",1,15)=TO_CHAR("A"."EMP_CODE") AND "B"."DEP_CODE"=13)


Pronto. Agora a instrução SQL executou em menos de 1 segundo com a utilização do índice IDX_SUBSTR_VALUE_DEPCODE.

Postagens populares