Pular para o conteúdo

Diminuindo físicamente um banco de dados Oracle

Como realizar uma diminuição física do seu banco de dados OracleOlá,

Vou mostrar uma tática bem legal de diminuir um banco de dados Oracle físicamente, sem mexer em extents (por shrink table), rebuild de índices ou realizar desfragmentação de segmentos (por Export\Import Utility) no banco de dados.

O principal ponto que iremos atacar será os espaços livres desnecessários alocados na tablespace, que consomem espaços em disco preciosos no sistema operacional, toda essa tarefa será guiada atráves da marca d’água (HWM – High Water Mark) dos datafiles onde podemos realizar um RESIZE no datafile para um valor menor sem a perda de dados.

Lembrete

Essa tarefa envolve alguns conceitos de Oracle, como os de Marca D’água, ou HWM – High Water Mark, esse tema eu vou abordar em outro post para melhor entendimento. OK!

Para iniciarmos os trabalhos, vamos analisar o tamanho do nosso banco de dados e quanto ele está ocupando em disco, abaixo, vou fazer dois SELECTS que passa essas informações para nós e depois um print da quantidade em disco utilizado e livre no sistema operacional.

SQL> select sum(bytes)/1024/1024 as "TamanhoFisico(MB)" from dba_data_files;
TamanhoFisico(MB)
 -----------------
             76000
SQL> col "FileSystem" format a12
SQL> l
   1  select substr(file_name,1,4) as "FileSystem", sum(bytes)/1024/1024 as "Tamanho(MB)"
   2  from dba_data_files
   3  group by rollup(substr(file_name,1,4))
   4* order by substr(file_name,1,4)
 SQL> /
FileSystem   Tamanho(MB)
 ------------ -----------
 /u01               25800
 /u02               50200
                    76000

Percebemos, que no meu banco de dados, tenho quase 26GB de datafiles no FileSystem /u01 e mais 50GB no FileSystem /u02, totalizando os 76GB que é o tamanho total do meu banco de dados. O importante é saber o que esse valor representa na minha máquina, em questão de consumo e escabilidade, veja o que a máquina ainda pode oferecer.

[oracle@pelspos18 ~]$ df -h
 Filesystem            Size  Used Avail Use% Mounted on
 /dev/sda2             9.9G  3.2G  6.3G  34% /
 /dev/sda1             190M   13M  168M   8% /boot
 none                  2.0G     0  2.0G   0% /dev/shm
 /dev/sda5             2.0G   36M  1.9G   2% /tmp
 /dev/sda6             114G   51G   58G  48% /u01
 /dev/sdb1             134G   50G   78G  40% /u02

Para o FileSystem /u01, representa 48% de utilização, já para o FileSystem /u02, representa 40% de utilização. Claro, que tudo isso, irá depender de como o aplicativo e banco de dados se comporta, se é um banco de dados com crescimento acentuado diário, mensal ou semestral, tudo isso influência. Mas, no exemplo que estou utilizando, o banco de dados tem um crescimento em média de 3GB por mês, então, está adequado.

Mas, eu preciso liberar mais espaço em disco para fazer alguns backups em disco, exports e etc. Então, vou diminuir meu banco de dados físicamente de forma segura, como eu disse no início do post.

Para fazer isso, preciso da mais algumas informações como, Tamanho Físico e Livre das tablespace, que pode ser pego facilmente pelos SELECTS abaixo. 

SQL> select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"
   2  from dba_data_files
   3  group by tablespace_name
   4  order by sum(bytes);
TABLESPACE_NAME      TAMANHO(MB)
 -------------------- -----------
 FIN_CPAG                     400
 FIN_CEXT_IDX                 800
 USERS                        800
 TOOLS                       1500
 SYSAUX                      2000
 SYSTEM                      2000
 FIN_CORP                    3500
 FIN_CORP_IDX                5000
 FIN_CPAG_IDX                8000
 UNDOTBS                    10000
 FIN_CREC_IDX               12000
 FIN_CREC                   30000
12 linhas selecionadas.

Bom, as tablespaces SYSTEM e SYSAUX não é novidade para ninguem, então, elas vou deixar-las de fora da atividade, para não ocorrer nenhum tipo de problema. Vou apenas pensar nas demais, inclusive na de UNDO. Perceba que esse é apenas um SELECT para ver o tamanho total das tablespaces, agora, quero saber quanto cada uma tem livre, para poder direcionar os RESIZES. Abaixo segue o tamanho livre por tablespace.

SQL> select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"
   2  from dba_free_space
   3  group by tablespace_name
   4  order by sum(bytes);
TABLESPACE_NAME      TAMANHO(MB)
 -------------------- -----------
 FIN_CREC_IDX              2,1875
 FIN_CPAG                398,5625
 USERS                   642,5625
 FIN_CEXT_IDX            799,5625
 TOOLS                  1495,3125
 SYSTEM                 1738,8125
 SYSAUX                  1869,125
 FIN_CORP_IDX           2456,4375
 FIN_CORP               3259,6875
 FIN_CPAG_IDX            5289,375
 FIN_CREC                8625,125
 UNDOTBS                 9865,375
12 linhas selecionadas.

Apenas mudei a view do primeiro SELECT, de dba_data_files para dba_free_space. Com isso, podemos ver quem tem mais espaço livre e saber quem precisa, existe tablespace com 8GB livres, e outros com 2,3 e 5G livres, só nisso, deixando apenas 10% livre, podemos economizar 12GB em disco. E ainda sem pensar no UNDO.

 Agora, vamos executar um SELECT que irá analisar a marca d’água dos datafiles e verificar se existe a possibilidade de realizar um RESIZE para um valor menor, com isso, liberar espaço em disco no sistema operacional. Esse SELECT precisa de uma atenção especial, pois, é necessário informar o tamanho do db_block_size do seu banco de dados para efetuar corretamente os cálculos.

Para saber o tamanho do seu db_block_size, basta fazer o seguinte procedimento.

NO SQLPLUS, faça:

SQL> show parameters db_block_size
NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 db_block_size                        integer     8192

Se preferir, poderá ver na v$parameter, como no exemplo.

SQL> col name format a15
 SQL> col value format a15
 SQL> select name, value from v$parameter where name = 'db_block_size';
NAME            VALUE
 --------------- ---------------
 db_block_size   8192

Agora, o momento esperado, o SELECT para realizar a operação de resize, veja.

SQL> l
   1  select 'alter database datafile ''' || file_name || ''' resize ' ||
   2        ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 )  || 'm;' cmd
   3  from dba_data_files a,
   4      ( select file_id, max(block_id+blocks-1) hwm
   5          from dba_extents
   6         group by file_id ) b
   7  where a.file_id = b.file_id(+)
   8   and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) < ceil( blocks*8192/1024/1024)
   9*  and ceil( (nvl(hwm,1)*8192*1.2)/1024/1024 ) > 100

Lembrete

Veja que onde está 8192 no script acima, é referente ao tamanho do db_block_size do seu banco de dados, esse valor então poder ser  2048, 4096, 8192, 16384 e 32768.

O script acima irá gerar um resultado sobre espaço livre, tamanho atual, espaço que pode ser salvo, total de espaço que pode ser liberado por datafile do seu banco de dados, tudo isso gerado pelos cálculos sobre a marca d’água de cada datafile. Se atentem pois ele está pegando todas as tablespaces do banco de dados e isso para nós não é necessário, podemos excluir a tablespace SYSTEM e SYSAUX.

FILE_NAME                                                      SMALLEST   CURRSIZE    SAVINGS SMALLEST_SAFE SAVINGS_SAFE
 ------------------------------------------------------------ ---------- ---------- ---------- ------------- ------------
 /u02/app/oracle/oradata/finp/fin_corp01.dbf                         125       2000       1875        149            1851
 /u02/app/oracle/oradata/finp/sysaux01.dbf                           191       2000       1809        229            1771
 /u02/app/oracle/oradata/finp/system01.dbf                           264       2000       1736        316            1684
 /u02/app/oracle/oradata/finp/fin_corp02.dbf                         117       1500       1383        140            1360
 /u01/app/oracle/oradata/finp/fin_cpag_idx04.DBF                     643       2000       1357        771            1229
 /u01/app/oracle/oradata/finp/fin_cpag_idx03.dbf                     651       2000       1349        781            1219
 /u01/app/oracle/oradata/finp/fin_cpag_idx02.dbf                     692       2000       1308        830            1170
 /u01/app/oracle/oradata/finp/fin_cpag_idx01.dbf                     728       2000       1272        873            1127
 /u01/app/oracle/oradata/finp/fin_corp_idx02.dbf                     847       2000       1153          1016          984
 /u01/app/oracle/oradata/finp/fin_corp_idx01.dbf                     897       2000       1103          1076          924
 /u02/app/oracle/oradata/finp/fin_crec09.dbf                        1284       2000        716          1540          460
 /u02/app/oracle/oradata/finp/fin_crec08.dbf                        1288       2000        712          1545          455
 /u02/app/oracle/oradata/finp/fin_crec07.dbf                        1304       2000        696          1564          436
 /u02/app/oracle/oradata/finp/fin_crec06.dbf                        1314       2000        686          1576          424
 /u02/app/oracle/oradata/finp/fin_crec05.dbf                        1331       2000        669          1597          403
 /u02/app/oracle/oradata/finp/fin_crec04.dbf                        1334       2000        666          1600          400
 /u02/app/oracle/oradata/finp/fin_crec01.dbf                        1351       2000        649          1622          378
 /u02/app/oracle/oradata/finp/fin_crec02.dbf                        1351       2000        649          1621          379
 /u02/app/oracle/oradata/finp/fin_crec03.dbf                        1351       2000        649          1621          379
 /u02/app/oracle/oradata/finp/users01.dbf                            158        800        642        189             611
 /u02/app/oracle/oradata/finp/fin_crec15.dbf                        1427       2000        573          1712          288
 /u02/app/oracle/oradata/finp/fin_crec14.dbf                        1547       2000        453          1856          144
 /u02/app/oracle/oradata/finp/fin_crec13.dbf                        1549       2000        451          1858          142
 /u02/app/oracle/oradata/finp/fin_crec12.dbf                        1551       2000        449          1861          139
 /u02/app/oracle/oradata/finp/fin_crec11.dbf                        1603       2000        397          1923           77
 /u01/app/oracle/oradata/finp/fin_corp_idx03.dbf                     802       1000        198        962              38
26 linhas selecionadas.
 CMD
 --------------------------------------------------------------------------------------------------------------------------
 alter database datafile '/u02/app/oracle/oradata/finp/system01.dbf' resize 316m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec11.dbf' resize 1923m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec02.dbf' resize 1621m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec07.dbf' resize 1564m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_corp02.dbf' resize 140m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec05.dbf' resize 1597m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec06.dbf' resize 1576m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec03.dbf' resize 1621m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec08.dbf' resize 1545m;
 alter database datafile '/u01/app/oracle/oradata/finp/fin_corp_idx01.dbf' resize 1076m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec01.dbf' resize 1622m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec09.dbf' resize 1540m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec12.dbf' resize 1861m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec14.dbf' resize 1856m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec15.dbf' resize 1712m;
 alter database datafile '/u01/app/oracle/oradata/finp/fin_cpag_idx01.dbf' resize 873m;
 alter database datafile '/u01/app/oracle/oradata/finp/fin_cpag_idx04.DBF' resize 771m;
 alter database datafile '/u02/app/oracle/oradata/finp/sysaux01.dbf' resize 229m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec13.dbf' resize 1858m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_crec04.dbf' resize 1600m;
 alter database datafile '/u01/app/oracle/oradata/finp/fin_cpag_idx02.dbf' resize 830m;
 alter database datafile '/u02/app/oracle/oradata/finp/fin_corp01.dbf' resize 149m;
 alter database datafile '/u02/app/oracle/oradata/finp/users01.dbf' resize 189m;
 alter database datafile '/u01/app/oracle/oradata/finp/fin_corp_idx02.dbf' resize 1016m;
 alter database datafile '/u01/app/oracle/oradata/finp/fin_corp_idx03.dbf' resize 962m;
 alter database datafile '/u01/app/oracle/oradata/finp/fin_cpag_idx03.dbf' resize 781m;
26 linhas selecionadas.

Uma coisa boa que essse script já fornece o comando de DDL para redimensionar o datafile da tablespace sem a perda de dados. Basta executar.

Após a execução dos scripts, vamos analisar os resultados gerados. Primeiramente, vamos ver agora o tamanho total e o espaço livre das tablespaces, depois verificar como ficou o espaço em disco no sistema operacional e saber o quanto ganhamos com isso.

Espaço total das tablespace

SQL> l
   1  select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"
   2  from dba_data_files
   3  group by tablespace_name
   4* order by sum(bytes)
 SQL> /
TABLESPACE_NAME      TAMANHO(MB)
 -------------------- -----------
 USERS                        189
 FIN_CORP                     289
 FIN_CPAG                     400
 FIN_CEXT_IDX                 800
 TOOLS                       1500
 SYSAUX                      2000
 SYSTEM                      2000
 FIN_CORP_IDX                3054
 FIN_CPAG_IDX                3255
 UNDOTBS                    10000
 FIN_CREC_IDX               12000
 FIN_CREC                   25496
12 linhas selecionadas.

Espaço livre nas tablespace

SQL> l
   1  select tablespace_name, sum(bytes)/1024/1024 as "TAMANHO(MB)"
   2  from dba_free_space
   3  group by tablespace_name
   4* order by sum(bytes)
 SQL> /
TABLESPACE_NAME      TAMANHO(MB)
 -------------------- -----------
 FIN_CREC_IDX              2,1875
 USERS                    31,5625
 FIN_CORP                 48,6875
 FIN_CPAG                398,5625
 FIN_CORP_IDX            510,4375
 FIN_CPAG_IDX             544,375
 FIN_CEXT_IDX            799,5625
 TOOLS                  1495,3125
 SYSTEM                 1737,3125
 SYSAUX                    1811,5
 FIN_CREC                4121,125
 UNDOTBS                9797,4375
12 linhas selecionadas.

Espaço no sistema operacional

[oracle@pelspos18 u01]$ df -h
 Filesystem            Size  Used Avail Use% Mounted on
 /dev/sda2             9.9G  3.2G  6.3G  34% /
 /dev/sda1             190M   13M  168M   8% /boot
 none                  2.0G     0  2.0G   0% /dev/shm
 /dev/sda5             2.0G   36M  1.9G   2% /tmp
 /dev/sda6             114G   45G   64G  42% /u01
 /dev/sdb1             134G   42G   86G  33% /u02

Conclusões

Perceba que algumas tablespace permaneceram do mesmo tamanho e algumas tiveram mais que 10% do seu tamanho reduzido, o melhor que podemos notar no sistema operacional que o FileSystem /u02 que antes tinha 78GB disponível, agora tem 86GB, uma diferença de 8GB, para o FileSystem /u01 que atens tinha 58GB disponível, agora tem 64GB, uma diferença de 6GB, que somando com o valor anterior, podemos reutilizar 14GB de espaço no sistema operacional.

O tamanho do nosso banco de dados fisicamente, também diminuiu, foi para 62GB. Antes era de 76GB, 14GB a menos, justamente o que nós não utilizamos mais.

O que pode nos ajudar a redução física do banco de dados?

Pode nos ajudar em N tarefas, como:

  • Não deixar o banco de dados travar por problemas de ARCHIVE ERROR;
  • Reaproveitamente de espaço em disco;
  • Diminuição de arquivos de backup gerados pelo RMAN em nível 0;
  • Ajuda na escabilidade do servidor;

Acho que isso já são boas razões para se pensar em fazer uma diminuição física do seu banco de dados.

Abraços,

 Rodrigo Almeida

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 0 / 5. Contagem de votos: 0

Sem votos ! Seja o primeiro a classificar !

4 comentários em “Diminuindo físicamente um banco de dados Oracle”

  1. Avatar de Julio Cesar Correa

    Com a orientação do seu artigo fiz um resize aqui e consegui diminuir o tamanho do banco de dados em 14% menos do tamanho antigo.Cerca de 47GB e as tablespaces ainda continuam com espaço disponivel!!

    Abs,

  2. Avatar de Rafael

    E se Usar um Move???

    abaixo uma Contribuição

    COL KTABLESPACE FOR A20 HEADING ‘Tablespace’
    COL KTBS_SIZE FOR 999,990 HEADING ‘Tamanho|atual’ JUSTIFY RIGHT
    COL KTBS_EM_USO FOR 999,990 HEADING ‘Em uso’ JUSTIFY RIGHT
    COL KTBS_MAXSIZE FOR 999,990 HEADING ‘Tamanho|maximo’ JUSTIFY RIGHT
    COL KFREE_SPACE FOR 999,990 HEADING ‘Espaco|livre atual|Fragmentado’ JUSTIFY RIGHT
    COL KSPACE FOR 999,990 HEADING ‘Espaco|livre total’ JUSTIFY RIGHT
    COL KPERC FOR 990 HEADING ‘%|Ocupacao’ JUSTIFY RIGHT

    break on report

    compute sum label Total: of ktbs_size on report
    compute sum of ktbs_em_uso on report
    compute sum of ktbs_maxsize on report
    compute sum of kfree_space on report
    compute sum of kspace on report

    select t.tablespace_name ktablespace,
    substr(t.contents, 1, 1) tipo,
    trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) ktbs_em_uso,
    trunc(d.tbs_size/1024/1024) ktbs_size,
    trunc(d.tbs_maxsize/1024/1024) ktbs_maxsize,
    trunc(nvl(s.free_space, 0)/1024/1024) kfree_space,
    trunc((d.tbs_maxsize – d.tbs_size + nvl(s.free_space, 0))/1024/1024) kspace,
    decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) kperc
    from
    ( select SUM(bytes) tbs_size,
    SUM(decode(sign(maxbytes – bytes), -1, bytes, maxbytes)) tbs_maxsize,
    tablespace_name tablespace
    from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
    from dba_data_files
    union all
    select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
    from dba_temp_files
    )
    group by tablespace_name
    ) d,
    ( select SUM(bytes) free_space,
    tablespace_name tablespace
    from dba_free_space
    group by tablespace_name
    ) s,
    dba_tablespaces t
    where t.tablespace_name = d.tablespace(+) and
    t.tablespace_name = s.tablespace(+)
    order by 8;

  3. Avatar de Juliano

    Amigo,

    Sou iniciante no Oracle e estou querendo diminuir o tamanho em disco do meu BD.

    Um dos meus tablespaces retornou a seguinte linha para a consulta passada pelo Rafael:
    DATA1 P 1599 4450 5120 2851 3521 31

    Pelo que percebi o espaço livre fragmentado é de 2851M, dai gostaria de saber se exite como liberar esse espaço no disco.

    Abs.

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress