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,
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,
Pergunta meia iniciante.Estou pegando ASM agora.
Estou correto?
Usando ASM não tem como ver o ganho em MB/GB pelo comando df -h.
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;
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.