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


segunda-feira, 7 de novembro de 2016

Abordando o erro ORA-00059: maximum number of DB_FILES exceeded

Por Eduardo Legatti

Olá,

Por padrão, quando criamos um banco de dados no Oracle, o número máximo de arquivos de dados (datafiles) que o mesmo pode suportar é 100. Essa informação fica armazenada no arquivo de controle (control file). Até o Oracle 8 quando esse limite era atingido o erro "ORA-1118: cannot add any more data files: limit of % exceeded" era emitido informando que não era mais possível adicionar novos datafiles. Para corrigir esse problema o control file deveria ser recriado de forma que o valor MAXDATAFILES fosse incrementado. A partir do Oracle 8i foi eliminado a necessidade de recriação do control file, bastando apenas setar o parâmetro de inicialização DB_FILES (valor padrão é 200) com um novo valor. Neste caso, o control file se expandirá automaticamente para acomodar novos registros dentro de suas seções. Vale a pena salientar que este parâmetro não é dinâmico, ou seja, o banco de dados precisa ser reinicializado após a alteração no mesmo. Caso o valor de DB_FILES seja atingido, o erro "ORA-00059: maximum number of DB_FILES exceeded" será emitido. Para simular o erro ORA-00059, irei recriar o control file setando MAXDATAFILES com um valor menor, bem como o parâmetro DB_FILES. Portanto, segue abaixo uma simulação.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 7 10:06:11 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning option

SQL> alter database backup controlfile to trace as '/tmp/controlfile.sql';

Database altered.

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1346756 bytes
Variable Size             117441340 bytes
Database Buffers          503316480 bytes
Redo Buffers                5627904 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "BD01" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 5
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oradata/BD01/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/oradata/BD01/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/oradata/BD01/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    '/oradata/BD01/system01.dbf',
 13    '/oradata/BD01/sysaux01.dbf',
 14    '/oradata/BD01/undotbs01.dbf',
 15    '/oradata/BD01/users01.dbf'
 16  CHARACTER SET WE8MSWIN1252;

Control file created.

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/BD01/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Como demonstrado acima, eu recriei o control file com o valor de MAXDATAFILES igual a 5. Logo abaixo irei setar o valor de DB_FILES para 6.

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
db_files                             integer     6

Realizando uma consulta na view dinâmica de desempenho V$CONTROLFILE_RECORD_SECTION abaixo é possível notar que a seção DATAFILE comporta no máximo 5 registros e que atualmente existem 4 registros em uso. Este valor bate com o número de datafiles existentes no banco de dados conforme demonstrado pela view DBA_DATA_FILES.

SQL> select type, record_size, records_total, records_used
  2    from v$controlfile_record_section
  3   where type = 'DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED
---------------------------- ----------- ------------- ------------
DATAFILE                             520             5            4

SQL> select count(*) from dba_data_files;

  COUNT(*)
----------
         4

Agora irei simular a criação de arquivos de dados no banco de dados conforme a seguir.       

SQL> create tablespace tbs01 datafile '/oradata/BD01/tbs01.dbf' size 10M;

Tablespace created.

SQL> create tablespace tbs02 datafile '/oradata/BD01/tbs02.dbf' size 10M;

Tablespace created.

SQL> create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M;
create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

É possível perceber que na criação da tablespace TBS03 o erro ORA-00059 foi emitido, ou seja, o control file tentou se expandir para acomodar o novo datafile, mas foi impedido para limitação do parâmetro DB_FILES que atualmente está setado para 6. Para resolver o problema, irei aumentar o valor do mesmo e reinicializar a instância.

SQL> alter system set db_files=200 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1346756 bytes
Variable Size             117441340 bytes
Database Buffers          503316480 bytes
Redo Buffers                5627904 bytes
Database mounted.
Database opened.

Após a alteração do parâmetro DB_FILES e reinicialização da instância, irei tentar criar novamente a tablespace TBS03.

SQL> create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M;

Tablespace created.

Pronto. É possível verificar que o control file foi expandido para acomodar mais registros.

SQL> select type, record_size, records_total, records_used
  2    from v$controlfile_record_section
  3   where type = 'DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED
---------------------------- ----------- ------------- ------------
DATAFILE                             520            35            7

Para finalizar, segue abaixo um trecho do arquivo de alerta da instância mostrando que a seção 4 do control file foi expandida para acomodar novos registros.

Mon Nov 7 10:21:51 2016
create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M
Expanded controlfile section 4 from 5 to 35 records
Requested to grow by 30 records; added 1 blocks of records
Completed: create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M

terça-feira, 4 de outubro de 2016

RMAN - Abordando Oracle Secure Backup Cloud Module para o Amazon AWS S3

Por Eduardo Legatti

Olá,

O Amazon S3 é um serviço de armazenamento na nuvem disponibilizado pela Amazon (AWS). Para DBAs que administram bancos de dados Oracle na infraestrutura da AWS usando uma máquina EC2, existe uma alternativa de armazenar backups físicos realizados pelo RMAN diretamente no S3 como se fosse uma fita (tape). Vale a pena salientar que os arquivos enviados pelo RMAN para este tape, no caso o S3, tem um formato próprio e só são reconhecidos pelo RMAN quando conectado diretamente utilizando a library OSB para o AWS S3, ou seja, os arquivos no S3 não serão reconhecidos pelo RMAN caso os mesmos sejam baixados diretamente do S3 para o sistema de arquivos. Para este propósito, existem outras formas como usar utilitários da AWS, como por exemplo, o AWS S3 sync para enviar arquivos diretamente da flash_recovery_area para um bucket no S3.

A simulação que irei realizar neste artigo é em um ambiente Linux. Não irei abordar aqui, mas vale a pena salientar, que é necessário ter uma conta no site da Oracle OTN, bem como ter uma conta no site da AWS já com um bucket S3 configurado e com as devidas permissões. No mais, o primeiro passo é instalar o java versão superior a 1.7 ou superior e realizar o download do módulo OSB (osbws_install.jar) no site da Oracle. Segue abaixo os procedimentos para instalação do módulo.

Verificando a versão do java.

$ /usr/local/jdk7/bin/java -version
java version "1.7.0_79"
Java(TM) SE Runtime Environment (build 1.7.0_79-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode)

Após verificado a versão do java e da realização do download do módulo OSB, irei realizar a instalação do mesmo conforme a seguir. Irei configurar a integração do RMAN com S3 usando a instância BD01. Neste momento, será necessário informar o AWSID, AWSKey, otnUser e otnPass.

$ export ORACLE_SID=BD01
$ /usr/local/jdk7/bin/java -jar osbws_install.jar -AWSID [awsid] -AWSKey [awskey] -otnUser [otnuser] -otnPass [otnpass]
  -walletDir /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbws_wallet
  -libDir /u01/app/oracle/product/11.2.0/dbhome_1/lib

Oracle Secure Backup Web Service Install Tool, build 2016-07-12
AWS credentials are valid.
Oracle Secure Backup Web Service wallet created in directory /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbws_wallet.
Oracle Secure Backup Web Service initialization file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsbd01.ora created.
Downloading Oracle Secure Backup Web Service Software Library from file osbws_linux64.zip.
Downloaded 27239574 bytes in 9 seconds. Transfer rate was 3026619 bytes/second.
Download complete.

Após realizada a instalação, será necessário criar um link simbólico no sistema operacional como demonstrado a seguir.

$ ln -s /u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so /u01/app/oracle/product/11.2.0/dbhome_1/lib/libobk.so

O próximo passo será realizar um teste de conectividade. É possível perceber que o arquivo osbwsBD01.ora foi criado em $ORACLE_HOME e este é o arquivo de configuração para conexão com o bucket S3 na AWS. Nele é possível configurar a credencial na AWS e o bucket S3 que deverá ser utilizado pelos backups com o RMAN. Abaixo segue alguns nomes fictícios para fins didáticos.

$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora

cat osbwsBD01.ora
OSB_WS_HOST=https://s3.amazonaws.com
OSB_WS_WALLET='location=file:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbws_wallet CREDENTIAL_ALIAS=rman_aws'
OSB_WS_BUCKET='rman_backup'

Agora irei realizar um teste de conectividade com o bucket S3.

$ export ORACLE_SID=BD01
$ sbttest osbws_readme.txt -dbname BD01

The sbt function pointers are loaded from libobk.so library.
-- sbtinit succeeded
-- sbtinit (2nd time) succeeded
sbtinit: vendor description string=Oracle Secure Backup
sbtinit: Media manager is version 3.16.7.12
sbtinit: Media manager supports SBT API version 2.0
sbtinit: allocated sbt context area of 1064 bytes
-- sbtinit2 succeeded
-- regular_backup_restore starts ................................
-- sbtbackup succeeded
write 100 blocks
-- sbtwrite2 succeeded
-- sbtclose2 succeeded
sbtinfo2: SBTBFINFO_NAME=osbws_readme.txt
sbtinfo2: SBTBFINFO_COMMENT=Oracle Secure Backup Web Services Library
sbtinfo2: SBTBFINFO_METHOD=stream
sbtinfo2: SBTBFINFO_ORDER=random access
sbtinfo2: SBTBFINFO_SHARE=multiple users
sbtinfo2: SBTBFINFO_LABEL=s3.amazonaws.com/rman_backup
-- sbtinfo2 succeeded
-- sbtrestore succeeded
file was created by this program:
     seed=1291994907, blk_size=16384, blk_count=100
read 100 buffers
-- sbtread2 succeeded
-- sbtclose2 succeeded
-- sbtremove2 succeeded
-- regular_backup_restore ends   ................................
-- sbtcommand succeeded
proxy copy is not supported
-- sbtend succeeded
*** The SBT API test was successful ***

Caso durante o teste de conectividade ocorra o erro [KBHS-00713: HTTP client error ''], é porque possivelmente o endereço no parâmetro OSB_WS_HOST deverá ser trocado de http:// para https://.

A seguir irei realizar algumas configurações e testes de backup/recover diretamente de dentro do RMAN acessando o bucket S3.

$ export ORACLE_SID=BD01
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 4 10:09:43 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BD01 (DBID=2858829824)

RMAN> configure channel device type sbt parms 'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora)';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora)';
new RMAN configuration parameters are successfully stored

RMAN> configure default device type to sbt;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored

RMAN> configure device type sbt_tape parallelism 1 backup type to compressed backupset;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored


RMAN> show all;

RMAN configuration parameters for database with db_unique_name BD01 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BD01.f'; # default

Realizadas as configurações acima, irei prosseguir com algumas operações de backup/recover para fins de demonstração.

RMAN> backup tablespace USERS;

Starting backup at 04/10/2016
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=6 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup Web Services Library VER=3.16.7.12
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 04/10/2016
channel ORA_SBT_TAPE_1: finished piece 1 at 04/10/2016
piece handle=10rbtt89_1_1 tag=TAG20160729T143033 comment=API Version 2.0,MMS Version 3.16.7.12
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 04/10/2016

Starting Control File and SPFILE Autobackup at 04/10/2016
piece handle=c-2858829824-20160729-01 comment=API Version 2.0,MMS Version 3.16.7.12
Finished Control File and SPFILE Autobackup at 04/10/2016


RMAN> list backup;

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4117    Full    3.50M      SBT_TAPE    00:00:24     04/10/2016
        BP Key: 4117   Status: AVAILABLE  Compressed: NO  Tag: TAG20160729T143033
        Handle: 10rbtt89_1_1   Media: s3.amazonaws.com/rman_backup
  List of Datafiles in backup set 4117
  File LV Type Ckp SCN    Ckp Time   Name
  ---- -- ---- ---------- ---------- ----
  4       Full 7840655778587 04/10/2016 /u01/oradata/BD01/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4118    Full    17.25M     SBT_TAPE    00:01:05     04/10/2016
        BP Key: 4118   Status: AVAILABLE  Compressed: NO  Tag: TAG20160729T143058
        Handle: c-2858829824-20160729-01   Media: s3.amazonaws.com/rman_backup
  SPFILE Included: Modification time: 04/10/2016
  SPFILE db_unique_name: BD01
  Control File Included: Ckp SCN: 7840655778602   Ckp time: 04/10/2016

Após a realização do backup físico pelo RMAN, irei simular uma falha na instância do Oracle e iniciar o processo de restore/recover do banco de dados.

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 4 10:15:32 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1459620040 bytes
Database Buffers          671088640 bytes
Redo Buffers                4947968 bytes
Banco de dados montado.
ORA-01157: n?o e possivel identificar/bloquear arquivo de dados 4 - consulte
arquivo de analise DBWR
ORA-01110: 4 do arquivo de dados: '/u01/oradata/BD01/users01.dbf'


Segue abaixo a simulação do restore e recover do banco de dados.

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 4 10:20:25 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BD01 (DBID=2858829824, not open)

RMAN> restore datafile 4;

Starting restore at 04/10/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=189 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup Web Services Library VER=3.16.7.12

channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 10rbtt89_1_1
channel ORA_SBT_TAPE_1: piece handle=10rbtt89_1_1 tag=TAG20160729T143033
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
Finished restore at 04/10/2016

RMAN> recover datafile 4;

Starting recover at 04/10/2016
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 04/10/2016

RMAN> alter database open;

database opened

Caso seja necessário realizar um restore completo de todos os arquivos de dados incluindo control files e spfile segue um exemplo abaixo.

$ export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
$ export NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'
$ export ORA_RMAN_SGA_TARGET=512
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 4 10:36:12 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initBD01.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2230072 bytes
Variable Size                192940232 bytes
Database Buffers             331350016 bytes
Redo Buffers                   7942144 bytes

RMAN> run {
2> allocate channel d1_tape_backup DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora),SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so';
3> set DBID 2858829824
4> restore spfile from autobackup;
5> release channel d1_tape_backup;
6> }

using target database control file instead of recovery catalog
allocated channel: d1_tape_backup
channel d1_tape_backup: SID=396 device type=SBT_TAPE
channel d1_tape_backup: Oracle Secure Backup Web Services Library VER=3.16.7.12

executing command: SET DBID

Starting restore at 04/10/2016 10:46:13

channel d1_tape_backup: looking for AUTOBACKUP on day: 20160801
channel d1_tape_backup: AUTOBACKUP found: c-2858829824-20160801-02
channel d1_tape_backup: restoring spfile from AUTOBACKUP c-2858829824-20160801-02
channel d1_tape_backup: SPFILE restore from AUTOBACKUP complete
Finished restore at 04/10/2016 10:49:07

released channel: d1_tape_backup

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     730714112 bytes

Fixed Size                     2231952 bytes
Variable Size                205521264 bytes
Database Buffers             515899392 bytes
Redo Buffers                   7061504 bytes

RMAN> show all;

RMAN configuration parameters for database with db_unique_name BD01 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN> run {
2> allocate channel d1_tape_backup DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora),SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so';
3> set DBID 2858829824
4> restore controlfile from autobackup;
5> release channel d1_tape_backup;
6> }

allocated channel: d1_tape_backup
channel d1_tape_backup: SID=96 device type=SBT_TAPE
channel d1_tape_backup: Oracle Secure Backup Web Services Library VER=3.16.7.12

executing command: SET DBID

Starting restore at 04/10/2016 10:53:20

channel d1_tape_backup: looking for AUTOBACKUP on day: 20160801
channel d1_tape_backup: AUTOBACKUP found: c-2858829824-20160801-02
channel d1_tape_backup: restoring control file from AUTOBACKUP c-2858829824-20160801-02
channel d1_tape_backup: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/BD01/control01.ctl
output file name=/u01/oradata/BD01/control02.ctl
Finished restore at 04/10/2016 10:56:25

released channel: d1_tape_backup

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 04/10/2016 10:59:56
Starting implicit crosscheck backup at 04/10/2016 10:59:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK
Finished implicit crosscheck backup at 04/10/2016 10:59:56

Starting implicit crosscheck copy at 04/10/2016 10:59:56
using channel ORA_DISK_1
Finished implicit crosscheck copy at 04/10/2016 10:59:56

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_39_csz3zwqn_.arc
File Name: /u01/flash_recovery_area/BD01/autobackup/2016_10_04/o1_mf_s_918747652_csz5xnol_.bkp
File Name: /u01/flash_recovery_area/BD01/autobackup/2016_10_04/o1_mf_s_918745687_csz407xh_.bkp

using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=127 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup Web Services Library VER=3.16.7.12

channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00001 to /u01/oradata/BD01/system01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00002 to /u01/oradata/BD01/sysaux01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00003 to /u01/oradata/BD01/undotbs01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 02rc5mvi_1_1
channel ORA_SBT_TAPE_1: piece handle=02rc5mvi_1_1 tag=LEVEL-0
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
Finished restore at 04/10/2016 11:01:55

RMAN> recover database;

Starting recover at 04/10/2016 11:02:04
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/BD01/system01.dbf
destination for restore of datafile 00002: /u01/oradata/BD01/sysaux01.dbf
destination for restore of datafile 00003: /u01/oradata/BD01/undotbs01.dbf
destination for restore of datafile 00004: /u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 07rc5o8h_1_1
channel ORA_SBT_TAPE_1: piece handle=07rc5o8h_1_1 tag=LEVEL-1
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/BD01/system01.dbf
destination for restore of datafile 00002: /u01/oradata/BD01/sysaux01.dbf
destination for restore of datafile 00003: /u01/oradata/BD01/undotbs01.dbf
destination for restore of datafile 00004: /u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0crc5pad_1_1
channel ORA_SBT_TAPE_1: piece handle=0crc5pad_1_1 tag=LEVEL-1
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07

starting media recovery

archived log for thread 1 with sequence 38 is already on disk as file /u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_38_csz0s6qq_.arc
archived log for thread 1 with sequence 39 is already on disk as file /u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_39_csz3zwqn_.arc
archived log file name=/u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_38_csz0s6qq_.arc thread=1 sequence=38
archived log file name=/u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_39_csz3zwqn_.arc thread=1 sequence=39
media recovery complete, elapsed time: 00:00:00
Finished recover at 04/10/2016 11:02:30

RMAN> alter database open resetlogs;

database opened

Caso precise fazer um backup de toda a Flash Recovery Area para o S3 usando o RMAN, bastará utilizar um comando conforme exemplo a seguir.

run {
 allocate channel d1_tape_backup DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora),SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so';
 backup recovery area;
 release channel d1_tape_backup;
 }

Para apagar os arquivos do S3 diretamente pelo RMAN, basta utilizar o comando abaixo.

run {
 allocate channel d1_tape_backup DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora),SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so';
 delete backup device type sbt_tape;
}

terça-feira, 6 de setembro de 2016

RMAN - Analisando a mensagem "Bad check value found during backing up datafile" no arquivo de alerta do Oracle

Por Eduardo Legatti

Olá,

No artigo de Fevereiro/2016 foi abordado como realizar a recuperação física de um bloco corrompido em um arquivo de dados utilizando a técnica "Block Media Recovery" do RMAN. Recentemente, fazendo a análise do arquivo de alerta de um banco de dados como demonstrado abaixo, percebi uma mensagem informando que o bloco 334002 do datafile 70 estaria corrompido durante a tentativa de realização de um backup físico pelo RMAN. Se realmente o bloco estiver corrompido, poderemos recuperá-lo utilizando esta técnica.

Sat Sep 3 10:10:01 2016
Hex dump of (file 70, block 334002) in trace file /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc
Corrupt block relative dba: 0x118518b2 (file 70, block 334002)
Bad check value found during backing up datafile
Data in bad block:
 type: 40 format: 2 rdba: 0x118518b2
 last change scn: 0x0c69.bc39d7ea seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7ea2802
 check value in block header: 0x3253
 computed block checksum: 0x0
Reread of blocknum=334002, file=/oradata/BD01/LOB_01_015.dbf. found valid data

Ao verificar o trecho acima no arquivo de alerta acima, realizei a validação do arquivo de dados pelo RMAN conforme a seguir, mas nenhuma mensagem de bloco corrompido foi emitida.

$ export ORACLE_SID=BD01
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 3 10:19:35 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BD01 (DBID=1637785486)

RMAN> validate datafile 70;

Starting validate at 03/09/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=485 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00070 name=/oradata/BD01/LOB_01_015.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:07:46

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- --------------
70   OK     0              124289       3171840         13656377431238

  File Name: /oradata/BD01/LOB_01_015.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              2418
  Other      0              3045133

Finished validate at 03/09/2016

Ao analisar o arquivo de trace, foi identificado que uma operação de backup incremental estava em execução durante o suposto problema de corrupção de bloco corrompido. No entanto, é possível perceber que logo após o bloco ser marcado como corrompido, existe uma mensagem indicando que o bloco foi novamente lido e que o seu estado estava válido. Vale a pena salientar que esta informação também está contida no arquivo de log de alerta.

$ cat /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc

Trace file /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:    server01
Release:    2.6.32-431.el6.x86_64
Version:    #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine:    x86_64
VM name:    Xen Version: 4.2 (HVM)
Instance name: BD01
Redo thread mounted by this instance: 1
Oracle process number: 136
Unix process pid: 26379, image: oracle@server01 (TNS V1-V3)


*** 2016-09-03 10:10:01.872
*** SESSION ID:(87.57015) 2016-09-03 10:10:01.872
*** CLIENT ID:() 2016-09-03 10:10:01.872
*** SERVICE NAME:(SYS$USERS) 2016-09-03 10:10:01.872
*** MODULE NAME:(backup incr datafile) 2016-09-03 10:10:01.872
*** ACTION NAME:(0002354 STARTED16) 2016-09-03 10:10:01.872

Hex dump of (file 70, block 334002)
Dump of memory from 0x00007F4158268000 to 0x00007F415826A000
7F4158268000 0000A228 118518B2 BC39D7EA 04020C69  [(.........9.i...]
7F4158269FB0 ACF6C7BB 701DB8EC B5F46AB4 E30FC56E  [.......p.j..n...]
7F4158269FC0 22758DDB B9452750 605107CF 39ECD3AD  [..u"P'E...Q`...9]
7F4158269FD0 387CC51C 9660182B 23606732 F1478AB3  [..|8+.`.2g`#..G.]
7F4158269FE0 1C17E72F C6082CF2 009DE777 6EABC849  [/....,..w...I..n]
7F4158269FF0 98D57134 E3D4B9E8 B511A853 D7EA2802  [4q......S....(..]
Corrupt block relative dba: 0x118518b2 (file 70, block 334002)
Bad check value found during backing up datafile
Data in bad block:
 type: 40 format: 2 rdba: 0x118518b2
 last change scn: 0x0c69.bc39d7ea seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7ea2802
 check value in block header: 0x3253
 computed block checksum: 0x0
Reread of blocknum=334002, file=/oradata/BD01/LOB_01_015.dbf. found valid data

Conclusão: Inicialmente eu achei muito estranho um erro no arquivo de alerta sem associação a um erro ORA-. Durante uma operação de backup usando o RMAN, o mesmo tenta obter uma imagem consistente do bloco de dados, e caso esse bloco seja alterado durante essa operação, o RMAN tentará obter novamente uma imagem consistente do mesmo bloco. Possivelmente esta foi a causa da mensagem no arquivo de alerta. Outro ponto importante a salientar é que toda essa operação é logada no arquivo de alerta (alert log file).

segunda-feira, 8 de agosto de 2016

Abordando a package DBMS_SQLTUNE para recomendar melhorias em SQL IDs específicos (SQL Tuning Advisor)

Por Eduardo Legatti

Olá,

Desde o Oracle 10g, os supervisores (advisors) são ferramentas especializadas que ajudam o DBA a analisar o desempenho do banco de dados, identificar possíveis problemas e gargalos e ajustar os vários componentes do banco de dados. No artigo de Abril/2008 eu demonstrei através de exemplos práticos como fazer para o Oracle analisar instruções SQL específicas e sugerir recomendações para melhorar seu desempenho. Foi usado o Supervisor de Ajuste SQL (SQL Tuning Advisor) através da package DBMS_SQLTUNE. Agora irei abordar novamente o uso do DBMS_SQLTUNE só que, em vez de passar como parâmetro uma instrução SQL para ser analisada, irei passar como parâmetro o SQL_ID de uma instrução SQL que já tinha sido executada anteriormente.

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Seg Ago 8 09:01:22 2016

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

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> var id number;
SQL> exec :id := 2500;

Procedimento PL/SQL concluído com sucesso.

SQL> select count(*) from t1 where id=:id;

  COUNT(*)
----------
         8

1 linha selecionada.

Após a execução da instrução SQL acima, irei procurar através da view dinâmica de desempenho V$SQLAREA qual o SQL_ID associado à instrução SQL executada. 
  
SQL> select sql_id,parsing_schema_name
  2    from v$sqlarea
  3   where sql_fulltext like 'select count(*) from t1 where id=:id%'; 
SQL_ID        PARSING_SCHEMA_NAME ------------- ------------------------- 1x6vk95kg44wb SCOTT 1 linha selecionada.

Uma vez encontrada o SQL_ID, irei criar uma tarefa de SQL Tuning passando o SQL_ID 1x6vk95kg44wb e executar a mesma conforme demonstrado a seguir. Para essa tarefa irei limitar a sua execução até o máximo de 5 minutos (300 seg).

SQL> DECLARE
  2    l_sql_tune_task_id  VARCHAR2(100);
  3  BEGIN
  4    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
  5                            sql_id      => '1x6vk95kg44wb',
  6                            scope       => DBMS_SQLTUNE.scope_comprehensive,
  7                            time_limit  => 300,
  8                            task_name   => 'SCOTT_1x6vk95kg44wb',
  9                            description => 'SCOTT Tuning task for statement 1x6vk95kg44wb.');
 10    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 11  END;
 12  /
l_sql_tune_task_id: SCOTT_1x6vk95kg44wb

Procedimento PL/SQL concluído com sucesso.

SQL> select task_id,task_name,status from user_advisor_log;

   TASK_ID TASK_NAME                      STATUS
---------- ------------------------------ -----------
     33150 SCOTT_1x6vk95kg44wb            INITIAL

1 linha selecionada.

SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'SCOTT_1x6vk95kg44wb');

Procedimento PL/SQL concluído com sucesso.


Pronto. Após a execução será necessário apenas analisar e verificar as recomendações que foram encontradas pelo otimizador. Vale a pena salientar que além de ser mostrado a instrução SQL, também será mostrado o valor da bind variable usada na instrução SQL.

SQL> set long 1000000;
SQL> set longchunksize 100000
SQL> set pagesize 10000
SQL> set linesize 1000
SQL> select dbms_sqltune.report_tuning_task('SCOTT_1x6vk95kg44wb') as recomendacoes from dual;

RECOMENDACOES
--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SCOTT_1x6vk95kg44wb
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 08/08/2016 09:13:45
Completed at       : 08/08/2016 09:13:56

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 1x6vk95kg44wb
SQL Text   : select count(*) from t1 where id=:id
Bind Variables :
 1 -  (NUMBER):2500

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  A tabela "SCOTT"."T1" não foi analisada.

  Recommendation
  --------------
  - Considere a coleta de estatísticas do otimizador para esta tabela
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'T1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
            => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    O otimizador requer estatísticas atualizadas para a tabela ao selecionar
    um plano de execução adequado.

2- Index Finding (see explain plans section below)
--------------------------------------------------
  O plano de execução dessa instrução pode ser melhorado com a criação de um
  ou mais índices.

  Recommendation (estimated benefit: 99.93%)
  ------------------------------------------
  - Considere a execução do Supervisor de Acesso para aumentar o projeto de
    esquema físico ou a criação do índice recomendado.
    create index SCOTT.IDX$$_817E0001 on SCOTT.T1("ID");

  Rationale
  ---------
    Criar os índices recomendados melhora significativamente o plano de
    execução dessa instrução. No entanto, talvez seja preferível executar o
    "Supervisor de Acesso" utilizando uma carga de trabalho SQL representativa
    em oposição a uma única instrução. Isso permitirá a obtenção de
    recomendações de índice abrangentes que levam em consideração o overhead
    de manutenção de índice e um consumo de espaço adicional.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |  1503   (3)| 00:00:19 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    73 |   949 |  1503   (3)| 00:00:19 |
---------------------------------------------------------------------------

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

   2 - filter("ID"=:ID)

2- Using New Indices
--------------------
Plan hash value: 809122696

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX$$_817E0001 |     8 |   104 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("ID"=:ID)

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

1 linha selecionada.

segunda-feira, 11 de julho de 2016

Performance em operações de INSERT: Quando a ordem dos fatores altera o produto

Por Eduardo Legatti

Olá,

No artigo de Dezembro/2013 eu abordei alguns conceitos sobre algumas operações no Oracle que podem ser aplicadas a este artigo. Irei demonstrar e compartilhar uma experiência na qual tive que fazer uma carga de dados (Direct INSERT) em uma tabela particionada, onde a fonte dos dados era uma uma view de banco de dados (140 milhões de linhas). Sabemos que a performance de um INSERT em uma tabela que possui índices é mais lento do que se a mesma não tivesse índices. O que me chamou a atenção além do tempo para realização do INSERT, foi o uso considerável das tablespaces de UNDO e TEMP. Abaixo irei mostrar um cenário onde irei criar uma tabela (NOLOGGING) particionada com os respectivos índices (NOLOGGING) e fazer uma operação de INSERT a partir de uma view.

SQL> set timing on
SQL> create table t1
  2  (
  3     code01  number (10) not null,
  4     code02  number (15) not null,
  5     code03  number (10) not null,
  6     code04  number (10) not null
  7  )
  8  tablespace tbs_data
  9  partition by range (code03)
 10     interval (1)
 11     (partition p1 values less than (1))
 12  nologging;

Tabela criada.

Decorrido: 00:00:00.11

SQL> create index idx_code01 on t1 (code01) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.73
SQL> create index idx_code01code03 on t1 (code01,code03) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code02 on t1 (code02) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code03 on t1 (code03) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01
SQL> create index idx_code03_code02_code01 on t1 (code03,code02,code01) nologging noparallel local;

Índice criado.

Decorrido: 00:00:00.01

Após a criação da tabela e dos respectivos índices, irei fazer o INSERT conforme demonstrado abaixo. Pode-se perceber que após 10 horas e 41 minutos da execução, o INSERT foi abortado por falta de espaço na tablespace de UNDO.

SQL> insert /*+ APPEND */ into t1 select * from vw1;
insert /*+ append */ into t1 select * from vw1
*
ERRO na linha 1:
ORA-30036: não foi possível estender o segmento em 8 no tablespace de undo 'UNDOTBS1'

Decorrido: 10:41:45.31

Vale a pena salientar que tanto a tablespace de UNDO quanto a tablespace TEMP foram definidas inicialmente com tamanho de 1 GB e AUTOEXTED ON. Ao final, é possível perceber que a tablespace de UNDO se estendeu até o máximo de 32 GB e que a tablespace TEMP se estendeu até 24 GB, conforme demonstrado abaixo.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
UNDOTBS1                       31,9999847

1 linha selecionada.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_temp_files;

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
TEMP                                   24

1 linha selecionada.

Enfim, percebendo que esta não seria a melhor opção para carregar uma tabela em um ambiente de produção, a alternativa abaixo foi a que melhor se encaixou, ou seja, primeiro realiza a criação da tabela particionada(NOLOGGING), depois a execução do INSERT utilizando a carga direta, e por fim a criação dos índices (NOLOGGING), conforme demonstrado abaixo.

SQL> set timing on
SQL> create table t1
  2  (
  3     code01   number (10) not null,
  4     code02   number (15) not null,
  5     code03   number (10) not null,
  6     code04   number (10) not null
  7  )
  8  tablespace tbs_data
  9  partition by range (code03)
 10     interval (1)
 11     (partition p1 values less than (1))
 12  nologging;

Tabela criada.

Decorrido: 00:00:01.10

SQL> insert /*+ APPEND */ into t1 select * from vw1;

140000000 linhas criadas.

Decorrido: 00:04:35.04

SQL> create index idx_code01 on t1 (code01) nologging noparallel local;

Índice criado.

Decorrido: 00:10:25.73
SQL> create index idx_code01code03 on t1 (code01, code03) nologging noparallel local;

Índice criado.

Decorrido: 00:09:56.37
SQL> create index idx_code02 on t1 (code02) nologging noparallel local;

Índice criado.

Decorrido: 00:09:16.56
SQL> create index idx_code03 on t1 (code03) nologging noparallel local;

Índice criado.

Decorrido: 00:08:59.35
SQL> create index idx_code03_code02_code01 on t1 (code03, code02, code01) nologging noparallel local;

Índice criado.

Decorrido: 00:11:38.65

No mais é possível perceber que no total foram gastos cerca de 54 minutos para realizar toda operação, que a tablespace de UNDO não chegou a ser estendida acima de 1 GB e que a tablespace TEMP se estendeu no máximo até 6 GB.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
UNDOTBS1                                1

1 linha selecionada.

SQL> select tablespace_name,bytes/1024/1024/1024 size_GB from dba_temp_files;

TABLESPACE_NAME                   SIZE_GB
------------------------------ ----------
TEMP                                    6

1 linha selecionada.

quarta-feira, 1 de junho de 2016

Um pouco sobre o Database Resource Manager (Instance Caging) do Oracle 11g R2

Por Eduardo Legatti

Olá,

Em um servidor onde existem várias instâncias de bancos de dados Oracle, em alguns momentos pode acontecer do mesmo ficar muito sobrecarregado e com o "load" alto pelo fato de uma ou mais instâncias do Oracle estarem consumindo muitos recursos de CPU. Neste caso, uma instância pode comprometer a performance geral do servidor e impactar o funcionamento das demais instâncias. Então, como fazer para que possamos restringir quanto uma instância poderá consumir de CPU nesse servidor? A partir do Oracle 11g R2 é possível limitar o uso de CPU em um servidor com múltiplos núcleos (CPU core). Para isso faremos uso de uma feature chamada Instance Caging do Database Resource Manager. Segue um exemplo prático.

Abaixo está sendo mostrado a saída do comando TOP do Linux de um servidor que possui 8 núcleos de CPU e cerca de 15 instâncias Oracle.

top - 10:00:05 up 526 days, 18 min,  1 user,  load average: 7.28, 4.59, 4.78
Tasks: 1125 total,   6 running, 1102 sleeping,  16 stopped,   1 zombie
Cpu0  : 47.6%us,  3.9%sy,  0.0%ni, 44.7%id,  3.9%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  : 85.4%us,  0.0%sy,  0.0%ni, 13.6%id,  0.0%wa,  0.0%hi,  1.0%si,  0.0%st
Cpu2  : 34.3%us,  2.0%sy,  0.0%ni, 63.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  : 48.1%us,  1.0%sy,  0.0%ni, 49.0%id,  0.0%wa,  1.0%hi,  1.0%si,  0.0%st
Cpu4  : 47.1%us,  3.8%sy,  0.0%ni, 45.2%id,  2.9%wa,  0.0%hi,  1.0%si,  0.0%st
Cpu5  : 33.7%us,  5.9%sy,  0.0%ni, 59.4%id,  0.0%wa,  0.0%hi,  1.0%si,  0.0%st
Cpu6  : 43.3%us,  5.8%sy,  0.0%ni, 50.0%id,  1.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :100.0%us,  0.0%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32876004k total, 32439108k used,   436896k free,   102568k buffers
Swap: 16777208k total,  6779068k used,  9998140k free, 23822240k cached

Agora irei realizar um teste na qual habilitarei o Instance Caging na instância BD01 de forma a limitar o consumo de CPU pela mesma. Para isso farei alteraçao em 2 parâmetros dinâmicos no Oracle (CPU_COUNT e RESOURCE_MANAGER_PLAN). O propósito será limitar a instância BD01 de consumir no máximo 4 núcleos de CPU conforme demonstrado abaixo.

$ export ORACLE_SID=BD01
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Qua Jun 1 09:58:32 2016

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

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set CPU_COUNT = 4;

Sistema alterado.


Após limitar para 4 o número de núcleos de CPU que a instância BD01 poderá usar, irei habilitar de fato o Instance Caging setando o parâmetro RESOURCE_MANAGER_PLAN com o valor DEFAULT_PLAN conforme demonstrado abaixo.

SQL> alter system set RESOURCE_MANAGER_PLAN = DEFAULT_PLAN;

Sistema alterado.
 
Pronto. A partir de agora o Instance Caging está habilitado para a instância BD01 de forma que a mesma se limite ao consumo de apenas 4 núcleos do total de 8 existentes no servidor.

SQL> show parameter CPU_COUNT;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4

SQL> show parameter RESOURCE_MANAGER_PLAN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      DEFAULT_PLAN
 
Abaixo podemos ver que o plano DEFAULT_PLAN está habilitado.

SQL> select id,name,is_top_plan,cpu_managed,instance_caging from v$rsrc_plan;

        ID NAME                         IS_TOP_PLAN CPU_MANAGED INSTANCE_CAGING
---------- ---------------------------- ----------- ----------- ---------------
    125274 DEFAULT_PLAN                 TRUE        ON          ON
    125271 ORA$AUTOTASK_SUB_PLAN        FALSE       ON          ON
    125272 ORA$AUTOTASK_HIGH_SUB_PLAN   FALSE       ON          ON

3 linhas selecionadas.

Realizando um pequeno teste e abrindo 4 sessões no Oracle através do SQL*Plus, executei algumas instruções SQL pesadas na instância BD01. Pela saída do comando TOP abaixo podemos perceber que apenas 4 núcleos de CPU estão em uso com quase 100% cada, ou seja, dando um total de 400% de uso.

top - 10:03:05 up 526 days, 20 min,  1 user,  load average: 4.32, 6.88, 5.21
Tasks:   4 total,   4 running,   0 sleeping,   0 stopped,   0 zombie
Cpu(s): 73.6%us,  2.1%sy,  0.0%ni, 24.2%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32876004k total, 32369496k used,   506508k free,    97944k buffers
Swap: 16777208k total,  8340916k used,  8436292k free, 23379872k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 5025 oracle    20   0  932m  24m  20m R 98.9  0.1   4:21.60 oracleBD01 (LOCAL=NO)
 5041 oracle    20   0  932m  24m  20m R 98.9  0.1   4:17.72 oracleBD01 (LOCAL=NO)
 7737 oracle    20   0  932m  22m  20m R 98.9  0.1   3:45.09 oracleBD01 (LOCAL=NO)
 5001 oracle    20   0  933m  28m  25m R 98.9  0.1   4:24.05 oracleBD01 (LOCAL=NO)

Agora irei limitar ainda mais o consumo de CPU para a instância BD01 de forma que a mesma utilize apenas 1 núcleo de CPU.


SQL> alter system set CPU_COUNT = 1;

top - 10:06:17 up 504 days, 22 min,  1 user,  load average: 3.13, 5.38, 3.85
Tasks:   4 total,   1 running,   3 sleeping,   0 stopped,   0 zombie
Cpu(s): 54.4%us,  3.3%sy,  0.0%ni, 35.9%id,  5.7%wa,  0.0%hi,  0.8%si,  0.0%st
Mem:  32876004k total, 32543176k used,   332828k free,   109064k buffers
Swap: 16777208k total,  8330556k used,  8446652k free, 23567276k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 7737 oracle    20   0  932m  24m  20m R 35.9  0.1  10:40.08 oracleBD01 (LOCAL=NO)
 5041 oracle    20   0  932m  24m  20m S 25.4  0.1  10:42.45 oracleBD01 (LOCAL=NO)
 5001 oracle    20   0  933m  28m  25m S 19.6  0.1  10:47.46 oracleBD01 (LOCAL=NO)
 5025 oracle    20   0  932m  24m  20m S 18.5  0.1  10:44.54 oracleBD01 (LOCAL=NO) 
 
Podemos perceber pela saída do comando TOP acima que ocorreu um balanceamento de carga de CPU entre as sessões de forma que no máximo apenas 100% , ou seja, como se apenas 1 núcleo fosse utilizado pelas 4 sessões. Pode-se notar também que após essa limitação é possível perceber que o "load" do servidor diminuiu de 4.32 para 3.13.

No mais, vale a pena salientar que a limitação de CPU ocorre para processos de primeiro plano (foreground processes) e alguns processos de segundo plano não críticos (background processes).

Postagens populares