Blog do Rodrigo Almeida

Olá,

A carreira do profissional de banco de dados (DBA) é cercada de especializações, pelo motivo de trabalhar com diversas áreas do TI, como desenvolvimento, projetos, infra-estrutura e etc. O DBA, conforme os anos vão se passando, é natural ter preferências ou “ser forçado” a trabalhar com áreas específicas, criando assim um perfil do administrador de banco de dados, onde podemos ter os tipos de DBAs abaixo:

DBA Desenvolvedor

É o profissional que desde administrar o banco de dados também desenvolve a aplicação, tem no perfil as seguintes características:

• Profundos conhecimentos na linguagem de programação local;
• Conhecimentos em instalação e administração do banco de dados;
• Sempre preocupado com a qualidade e manutenção das querys;
• Procura sempre o mais performático, mesmo que a atividade não seja uma boa prática;
• Fortes conhecimentos em modelagem de dados, sabe aplicar a 3FN (brincadeira);
• Trabalha sobre prazos e acostumado a “martelar” o banco de dados ou aplicação para que seja entregue na data prevista.

Geralmente, o DBA desenvolvedor é lider de desenvolvimento ou atua como um guru na equipe, mandando orientações aos novos integrantes, organizando as atividades e analisando todos os requisitos de desenvolvimento.

DBA Infra-Estrutura

É o profissional que conhece toda a infra-estrutura necessária para a implementação do banco de dados, atua desde a concepção do hardware até a analise da infra-estrutura local do banco de dados, tem as seguintes características:

• Profundos conhecimentos da arquitetura e administração Oracle;
• Profundos conhecimentos em infra-estrutura, desde a configuração do hardware, cabeamento, switch, sistema operacional e backup & recover;
• Especialidade em performance ao nível de sistema operacional e instância Oracle;
• Extremamente cuidadoso no momento de aplicação de patches no banco de dados e sistema operacional;
• Trabalha mais voltado a projetos e implementação de novos recursos de banco de dados ou aplicação;
• Responsável em adotar as boas práticas para administração do banco de dados, mesmo que isso crie conflitos internamente na equipe.

O DBA Infra-Estrutura geralmente não dá tanta importância para as aplicações da empresa, costuma se preocupar apenas com o banco de dados e afeta diretamente a vida o SYSADMIN, pois, tudo que o SYSADMIN fizer que tenha banco de dados no meio, ele irá questionar.

DBA Projetos

É o perfil do profissional que já foi um DBA Desenvolvedor e Infra-Estrutura, sabe como funciona cada área é aplica as boas práticas na construção da aplicação e na infra-estrutura para o banco de dados, suas principais características são:

• Bons conhecimentos em desenvolvimento de aplicações;
• Bons conhecimentos sobre o produto e arquitetura Oracle;
• Gerencia os prazos das atividades, aquisição de produtos extras, manutenção na aplicação, patches de banco de dados e requisitos básicos da infra-estrutura;
• Costuma usar o Microsoft Project para se organizar e documenta todas as suas atividades;
• Acostumado a opinar/sugerir/questionar o comportamento
• Acostumado a delegar as atividades básicas de administração do banco de dados e cuidar apenas dos projetos que envolvem o banco de dados e a burocracia da empresa.

O DBA Projetos é conhecido com o “cara” do projeto da empresa, pois ele que em diversas reuniões com dezenas de áreas costuma a questionar e interrogar os participantes para coletar as informações no momento de criar o projeto do banco de dados.

Com sua experiência, ele consegue detectar muitos problemas e possui pró-atividade em suas atividades.

Agora, para descontrair um pouco, vamos falar sobre as outras vertentes da carreira de um DBA, aquelas vertentes que é comentada pela “rádio Peão” da empresa, como:

DBA Cafeteira

É o profissional que passa o tempo tomando café e fumando que trabalhando efetivamente, porém, resolve os problemas da empresa rapidamente.

DBA Google

Esse é o profissional criado dentro do SIBEL ou qualquer outra ferramenta de gerenciamento de mudanças,  conhecido pelas filas de CHAMADOS! Passa a maior parte de sua vida atendendo chamados para produção ou desenvolvimento, tendo que aprender todos os tipos de coisas possíveis que existe no mundo Oracle, por isso que falam que um DBA Google sem internet não trabalha!

Mas é muito bom para quem quer aprender banco de dados, aprende e tem noção de tudo!

DBA Bombeiro

Essa vertente é a pós-graduação do DBA Google, pois em algum momento da vida o DBA Google fica revoltado com a vida que tem e quer mudar de empresa, e a empresa que ele será empregado é um consultoria com N clientes e ele deverá atender todos.

Deste modo, acaba surgindo o DBA Bombeiro, pois ele vai para os clientes apenas para apagar incêndios a todo momento, não consegue prosseguir em uma especialização. E para ajudar, é o perfil de DBA que costuma a receber um celular ou Page para ficar 24×7 para a empresa.

Também muito conhecido pelas suas olheiras que vem com o tempo!

DBA Ghost

Esse é o perfil de um profissional que escutou de alguém que DBA ganha bem, que sendo DBA ele irá prosperar em sua vida financeira e terá tudo que o mundo pode vender para ele, assim sendo, alguem muito conhecido consegue arrumar uma vaga na área de banco de dados e quando ele realmente consegue entrar, possui as caractéristicas abaixo:

• Não sabe administrar o banco de dados;
• Quando se tem problemas, ele desaparece;
• Não atende o celular após as 18 horas nunca;
• Mata dezenas de parentes no ano para justificar suas faltas;
• Não gosta de estudar, ler livros, buscar conhecimentos e muito mesmo debater assuntos referentes a Oracle;
• E o melhor, não se esforça para aprender o que foi solicitado.

Acho que é isso pessoal, deu para entender um pouco como funciona a carreira de um profissional de banco de dados Oracle, muitos área de especializações ou até mesmo uma generalização de tudo, com muita moderação, não diga que sabe trabalhar com RAC, sendo que nunca viu como funciona RAC em sua frente.

Certificação é importante e recomendo a todos, porém o Certificado Oracle, seja ele um OCA ou OCP, não vai dar garantias que seja o melhor profissional de todos os tempos, pois, certificação não é garantia de qualidade no serviço.

Abraços,

Olá,

Uma das tarefas mais legais do DBA, é a criação de uma banco de dados manualmente, ou seja, sem a utilização da ferramenta gráfica DBCA (Database Configuration Assistant) da Oracle para realizar essa tarefa, pois deste modo, o DBA consegue acompanhar todos os processos básicos do início ao fim da criação do banco de dados sem a necessidade de executar apenas um batch ou shell e acaba não sabendo o que aconteceu.

Porém, antes de iniciar os passos, temos alguns pré-requisitos que devemos salientar, que são:

• Possuir um hardware com os requisitos minímos exigidos pela versão do banco de dados.
• Verificar se o seu linux é homologado pela Oracle para efetuar a instalação do Oracle Server.
• Ter os binários do Oracle Server instalado no servidor, ou seja, ter uma versão do Oracle Database 10g instalada corretamente no ambiente Linux, com todos os pacotes necessários.
• Conhecer a metodologia OFA (Optimal Flexible Architecture) para criação do banco de dados.

Esses passos são necessários para quem deseja criar um banco de dados manualmente, pois irá realizar uma instalação igual ao do DBCA, que na versão 10g já utiliza a metodologia OFA por padrão. Abaixo segue alguns links para realizar a instalação apenas do software do Oracle Database, para quem não possui ele instalado.

Sites de referência

iMasters - Instalação do Oracle Database 11g em Linux

Puschitz - Installing Oracle 10gR1, R2 on RHEL 4

OTN - Installing Oracle Database 10g Release 2 on Linux x86

Para dar mais enfâse, vamos criar um “resumão” de um pequeno projeto de banco de dados, para conseguimos imaginar como ficará a nossa estrutura disponível e o banco de dados em sí.

Projeto de Banco de dados

Nome: RANET (DBNAME e SID)

Plataforma: Linux Red Hat EL As 4

Volumetria Inicial: 2GB

Tablespaces Permanentes: SYSTEM (500MB), SYSUAX (250MB) e UNDO (500MB).

Tablespaces Aplicação para Dados: RADAT (500MB)

Tablespaces Aplicação para Índices: RAIDX (250MB)

Tablespaces Temporárias: TEMP

Modo de Gerenciamento das tablespaces: LMT (Local Management Tablespace)

Valor em memória (SGA): 1GB

Quantidade de usuários atendidos: 10

Portas do Listener: 1521 e 1522

E agora vamos ao que interessa!

1° Passo | Criando a estrutura física do banco de dados

A estrutura física consiste em diretórios que serão utilizados pelo banco de dados no nível de sistema operacional,que podem ou não obdecer o padrão OFA e sua principal função é organizar e armazenar os arquivos do banco de dados no sistema operacional.

Para a criação do banco de dados, devemos conhecer duas variáveis de ambiente importantes criadas nas instalações Unix/Linux, que são as variáveis ORACLE_BASE e ORACLE_HOME.

A variável ORACLE_BASE é o caminho da instalação do software do Oracle Database, não confunda com as pastas do banco de dados, como ORADATA e ADMIN, um ORACLE_BASE pode ser o mesmo para uma instalação do Database como do Application Server.

A variável ORACLE_HOME é responsável em dizer o caminho que os produtos do banco de dados estão executando, como os binários, configuração de rede (SQL*NET), bibliotecas, grupos de programas e etc.

No exemplo utilizado, nosso caminho para ORACLE_BASE e ORACLE_HOME são essas:

ORACLE_BASE = /u01/app/oracle

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

Após validar as variáveis da sua instalação ou verificar quais os caminhos de cada variável, vamos criar os diretórios de dados e administrativos para o banco de dados, pois quando estamos realizando um projeto de banco de dados, devemos analisar as opções que temos de armazenamento, organização e volumes lógicos disponíveis para sua criação, baseando-se a partir do ORACLE_BASE e File System disponíveis.

Nosso objetivo agora é criar os diretórios administrativos do banco de dados, que seguirá essa estrutura física abaixo:

$ORACLE_BASE/admin/<nome_do_banco>/<diretorios>

Conforme o exemplo abaixo:

[oracle@pelspos7h oracle]$ cd $ORACLE_BASE

[oracle@pelspos7h product]$ mkdir admin

[oracle@pelspos7h product]$ cd admin

[oracle@pelspos7h admin]$ mkdir ranet

[oracle@pelspos7h admin]$ cd ranet

[oracle@pelspos7h ranet]$ mkdir adhoc adump bdump cdump flash_recovery_area udump exp

[oracle@pelspos7h ranet]$ cd ..

[oracle@pelspos7h admin]$ cd ..

[oracle@pelspos7h product]$ ls -R |grep ranet

ranet

./admin/ranet:

./admin/ranet/adhoc:

./admin/ranet/adump:

./admin/ranet/bdump:

./admin/ranet/cdump:

./admin/ranet/flash_recovery_area:

./admin/ranet/udump:

./admin/ranet/exp:

Acima está a nossa estrutura física administrativa montada, onde cada um tem sua finalidade como:

./admin

Resposável por organizar os arquivos administrativos de cada instância Oracle.

./admin/ranet:

Pasta que recebe o nome do meu futuro banco de dados.

./admin/ranet/adhoc:

Pasta destinado a armazenar scripts SQL, PL/SQL ou shells.

./admin/ranet/adump:

Audit Dump, onde é gerado os arquivos e auditória do banco de dados, com extensão AUD.

./admin/ranet/bdump:

Background Dump, caminho que é utilizado pelo processos de plano de fundo do Oracle e do alert.log da instância.

./admin/ranet/cdump:

Core Dump, onde é gerado traces do Core do Oracle, geralmente ligados á problemas do sistema operacional.

./admin/ranet/flash_recovery_area:

Flashback Recovery Area (FRA), é um caminho opcional no momento da criação do banco de dados, pois nesse caminho, é gerado backupsets, backup pieces, archive logs, online logs e flashback logs. É opcional pois dependendo do tamanho e quantidade de archived logs gerados pelo banco de dados, é necessário ter um disco dedicado ao FRA.

./admin/ranet/udump:

User Dump, é o diretório que armazena os traces gerados por usuários ou por processos específicos do banco de dados.

./admin/ranet/exp:

Export, diretório destinado aos arquivos gerados atráves do Data Pump Export (Expdp) ou Export Utility (exp).

Depois de realizar a criação dos diretórios administrativos, devemos criar a estrutura que irá armazenar os arquivos do banco de dados propriamente dito, como datafiles, redo logs e control files. E nesse momento, devemos verificar no servidor, qual a melhor opção para o armazenamento desses arquivos, veja:

[oracle@pelspos7h oracle]$ df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda1             127G   64G   57G  53% /

none                  2.0G     0  2.0G   0% /dev/shm

/dev/sdb1             404G  279G  105G  73% /u02

No exemplo, a melhor opção para o meu banco de dados é o FileSystem /u02, que tem bastante espaço em disco sobrando, o FileSystem /u01 (que não está montado) ficou para os arquivos de traces, archives, dumps e os binários do Oracle.

Após decidido a localização dos arquivos, devemos criar as pastas para armazenar-las.

[oracle@pelspos7h u02]$ mkdir oracle
[oracle@pelspos7h u02]$ cd oracle
[oracle@pelspos7h oracle]$ mkdir oradata
[oracle@pelspos7h oracle]$ cd oradata
[oracle@pelspos7h oradata]$ mkdir ranet
[oracle@pelspos7h oradata]$ cd ranet
[oracle@pelspos7h ranet]$ pwd

/u02/oracle/oradata/ranet

Onde,

/u02/oracle/oradata/ranet
  |     |      |      |-> Diretório para armazenamento dos arquivos do respectivo banco de dados.
  |     |      |--------> Diretório para armazenamento de dados, segundo o padrão OFA.
  |     |---------------> Diretório que específica do software.
  |---------------------> File System com capacidade suficiente para criar o banco de dados.

PRONTO! Já estamos com a nossa estrutura física criado para suporta o novo banco de dados.

2° Passo | Criação do Arquivo de parâmetros

O arquivo de parâmetro, conhecido como PFILE, é um arquivo texto responsável pelas características e comportamento da instância Oracle, esses parâmetros são para trabalhar diretamente em memória da máquina e pelo banco de dados físicamente. É um arquivo essencial para iniciar uma instância Oracle.

Abaixo vou colocar um arquivo de parâmetro básico para iniciar a instância.

control_files              = (/u02/oracle/oradata/ranet/control01.ctl)

db_name                    = ranet

db_domain                  = world

log_archive_dest         = "LOCATION=LOCATION=USE_DB_RECOVERY_FILE_DEST"

db_block_size              = 8192

pga_aggregate_target       = 250M

processes                  = 100

sessions                   = 120

open_cursors               = 80

undo_management            = AUTO

undo_tablespace            = UNDOTBS

compatible                 = 10.1.0.0.0

sga_target                 = 1G

nls_language               = AMERICAN

nls_territory              = AMERICA

db_recovery_file_dest      = /u01/app/oracle/admin/ranet/flash_recovery_area

db_recovery_file_dest_size = 10G

background_dump_dest       = /u01/app/oracle/admin/ranet/bdump

core_dump_dest             = /u01/app/oracle/admin/ranet/cdump

user_dump_dest             = /u01/app/oracle/admin/ranet/udump

audit_file_dest            = /u01/app/oracle/admin/ranet/adump

Veja que alguns parâmetros receberam os valores da nossa estrutura física, seguindo o padrão OFA. Deste modo, podemos salvar o nosso pfile em sua respestiva pasta (/u01/app/oracle/admin/ranet/pfile) como initranet.ora, pois estamos seguindo um padrão da Oracle, que é init<nome_do_banco>.ora para gerar o arquivo.

Posteriormente, vamos criar um SPFILE, sigla para Server Parameter File, que é um melhorando do PFILE, pois já é um arquivo binário, que permite alterar o valores para os parâmetros dinâmicos da instância Oracle.

3° Passo | Preparando script de criação do banco de dados

Uma das tarefas que devemos realizar no momento, é a preparação do script que irá criar nosso banco de dados, esse script será necessário para criar nosso banco de dados fisicamente. Lembre-se que na arquitetura Oracle, quando trabalhamos com recursos em memória, chamamos de Instância Oracle e quando vamos gravar, apagar, modificar, consultar e manipular os dados, estamos trabalhando diretamente no banco de dados físico, onde fica nossos datafiles, control files e redo logs. O Script para criação está abaixo:

CREATE DATABASE "ranet"

MAXINSTANCES 1

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 200

DATAFILE '/u02/oracle/oradata/ranet/system01.dbf' SIZE 500M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

SYSAUX

DATAFILE '/u02/oracle/oradata/ranet/sysaux01.dbf' SIZE 500M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP

TEMPFILE '/u02/oracle/oradata/ranet/temp01.dbf' SIZE 2000M AUTOEXTEND OFF

SMALLFILE UNDO TABLESPACE "UNDOTBS"

DATAFILE '/u02/oracle/oradata/ranet/undotbs01.dbf' SIZE 2000M AUTOEXTEND OFF

CHARACTER SET WE8ISO8859P1

NATIONAL CHARACTER SET AL16UTF16

LOGFILE

GROUP 1 ('/u01/oracle/oradata/ranet/redo01a.log', '/u02/oracle/oradata/ranet/redo01b.log') SIZE 50M,

GROUP 2 ('/u01/oracle/oradata/ranet/redo02a.log', '/u02/oracle/oradata/ranet/redo02b.log') SIZE 50M,

GROUP 3 ('/u01/oracle/oradata/ranet/redo03a.log', '/u02/oracle/oradata/ranet/redo03b.log') SIZE 50M

USER SYS IDENTIFIED BY DBARODRIGO

USER SYSTEM IDENTIFIED BY SUECO_REPITA;

O scripts acima possui diversos parâmetros e instruções de criação das tablespaces do banco de dados, diga-se de passagem, as principais do banco de dados, e vamos discutir um pouco sobre cada ponto.

Primeiro, vamos comentar sobre os parâmetros utilizados na cláusula de CREATE DATABASE, veja:

MAXINSTANCES

Específica o número máximo de instâncias simultâneas para o banco de dados quando está em MOUNT ou OPEN. Válido para bancos de dados com RAC (Real Application Cluster).

MAXLOGHISTORY

Específica o número máximo de archived redo logs files para automatic media recovery. Válido para bancos de dados com RAC (Real Application Cluster).

MAXLOGFILES

Específica o número máximo de grupos de redo logs que o banco de dados pode possuir.

MAXLOGMEMBERS

Específica o número máximo de membros (ou cópias do aruqivo de redo) que cada grupo pode ter.

MAXDATAFILES

O número máximo de arquivos de dados (datafiles) do banco de dados.

Segundo, agora vamos para as instruções que cria as principais tablespaces do banco de dados.

DATAFILE ‘/u02/oracle/oradata/ranet/system01.dbf’ SIZE 500M AUTOEXTEND ON NEXT 10240K

Responsável em criar a tablespace SYSTEM, sem ele, sem banco de dados!

No início estamos criando a tablespace SYSTEM com apenas 500MB e com a opção de auto-expansível a cada 1MB.

SYSAUX
DATAFILE ‘/u02/oracle/oradata/ranet/sysaux01.dbf’ SIZE 500M AUTOEXTEND ON NEXT  10240K

Essa tablespace é nova e foi introduzida no Oracle 10g, a tablespace SYSAUX é para “prestar suporte” a tablespace SYSTEM, com owners de produtos da própria Oracle. Como Oracle Spatial, RMAN, Workflow e etc.

A tablespace também será criado com 500MB inicialmente.

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/u02/oracle/oradata/ranet/temp01.dbf’ SIZE 2000M AUTOEXTEND OFF

A tablespace temporária, muito utilizada no banco de dados para operações de SORT, AGREGAÇÃO, CRIAÇÃO DE ÍNDICE e etc.

Vamos iniciar ela com 2GB, pois desde a criação do banco de dados será muito utilizada, a única diferença que estamos limitando o seu crescimento em 2GB, caso seja necessário mais, aumentamos conforme a necessidade.

SMALLFILE UNDO TABLESPACE “UNDOTBS”
DATAFILE ‘/u02/oracle/oradata/ranet/undotbs01.dbf’ SIZE 2000M AUTOEXTEND OFF

Essa instrução é responsável pela criação da tablespace de UNDO, que será chamada de “UNDOTBS”, que é o mesmo valor do parâmetro undo_tablespace que colocamos em nosso PFILE. A tablespace de UNDO que tem como finalidade armazenar nossos dados não comprometidos, desfazer as transações sem sucesso e controlar as modificações dos dados no banco de dados, falando resumidamente.

CHARACTER SET WE8ISO8859P1

Específica o conjunto de caracteres que será armazenado no banco de dados. Se errar nesse momento o conjunto de caracteres, para alterar, somente reconstruindo o banco de dados.

NATIONAL CHARACTER SET AL16UTF16

Específica o conjunto nacional de caracteres, para armazenar os dados em colunas que são do tipo NCHAR, NVARCHAR2 e NCLOB.

LOGFILE
GROUP 1 (’/u01/oracle/oradata/ranet/redo01a.log’, ‘/u02/oracle/oradata/ranet/redo01b.log’) SIZE 50M,
GROUP 2 (’/u01/oracle/oradata/ranet/redo02a.log’, ‘/u02/oracle/oradata/ranet/redo02b.log’) SIZE 50M,
GROUP 3 (’/u01/oracle/oradata/ranet/redo03a.log’, ‘/u02/oracle/oradata/ranet/redo03b.log’) SIZE 50M

Essas instruções específica os membros e a quantidade dos grupos de redo log. Eles podem ser alterados posteriormente quando o banco de dados estiver criado. E perceba que em cada grupo (1,2,3) possui dois arquivos de redo log, isso se chama multiplexação, caso um disco (/u01 ou /u02) dê problemas, terá uma replica do arquivo em outro disco, que poderá facilitar a sua recuperação e não perder os dados.

USER SYS IDENTIFIED BY DBARODRIGO

A instrução acima é para mencionar a senha do usuário SYS.

USER SYSTEM IDENTIFIED BY SUECO_REPITA

A instrução acima é para mencionar a senha do usuário SYSTEM.

Bom, já conseguimos criar o PFILE e o script do banco de dados, agora vamos colocar a mão na massa. Vamos realizar os processos que criação.

4° Passo | Processo de criação

Após o PFILE e SCRIPT prontos, vamos realizar as atividades de criação do banco de dados.

4.1 - Definindo Variáveis de ambiente.

Vamos logar na máqunia que será criado o banco de dados, e vamos configurar a variável de ambiente ORACLE_SID e conferir os valores de ORACLE_BASE e ORACLE_HOME.

ORACLE_SID

A variável ORACLE_SID é responsável em dizer ao Oracle Server qual é o nome do banco de dados que será utilizada, e como estamos criando a base, devemos mencionar o seu nome, que é o mesmo que o parâmetro DB_NAME. Exemplo:

[oracle@pelspos7h adhoc]$ export ORACLE_SID=ranet

[oracle@pelspos7h adhoc]$ echo $ORACLE_SID

ranet

Depois, vamos checar os valores de ORACLE_BASE e ORACLE_HOME.

[oracle@pelspos7h adhoc]$ echo $ORACLE_BASE

/u01/app/oracle

[oracle@pelspos7h adhoc]$ echo $ORACLE_HOME

/u01/app/oracle/product/10.2.0/db_1

4.2 - Abrindo o SQL*PLUS

Vamos iniciar o SQL*PLUS e se conectar a instância.

[oracle@pelspos7h adhoc]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Feb 13 11:16:54 2009

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

SQL> conn / as sysdba

Connected to an idle instance.

A mensagem “Connected to an idle instance” é normal, porquê você não iniciou a instância.

4.3 - Iniciando a instância Oracle.

Vamos pegar o caminho completo do nosso PFILE e vamos iniciar a instância, com o comando STARTUP NOMOUNT, que significa que vamos apenas carregar seus valores em memória, pois ainda não temos o control file da base criado.

SQL> startup nomount pfile=/u01/app/oracle/admin/ranet/pfile/initranet.ora;

ORACLE instance started.

Total System Global Area  524288000 bytes

Fixed Size                  1268412 bytes

Variable Size             146801988 bytes

Database Buffers          369098752 bytes

Redo Buffers                7118848 bytes

Instância iniciada em modo NOMOUNT. Podemos prosseguir.

4.4 - Criando o banco de dados.

Vamos executar o script de criação do banco de dados.

SQL> @/u01/app/oracle/admin/ranet/adhoc/create_db.sql

Database created.

Nesse momento criamos o banco de dados, os arquivos com extensão .DBF, .CTL e .LOG devem estar disponíveis no sistema operacional. Como no exemplo:

[oracle@pelspos7h ranet]$ ls -ltr

total 3390508

-rw-r-----  1 oracle oinstall   52429312 Feb 13 11:24 redo03b.log

-rw-r-----  1 oracle oinstall   52429312 Feb 13 11:24 redo03a.log

-rw-r-----  1 oracle oinstall   52429312 Feb 13 11:24 redo02b.log

-rw-r-----  1 oracle oinstall   52429312 Feb 13 11:24 redo02a.log

-rw-r-----  1 oracle oinstall 2097160192 Feb 13 11:25 temp01.dbf

-rw-r-----  1 oracle oinstall  524296192 Feb 13 11:25 sysaux01.dbf

-rw-r-----  1 oracle oinstall 2097160192 Feb 13 13:55 undotbs01.dbf

-rw-r-----  1 oracle oinstall  524296192 Feb 13 13:55 system01.dbf

-rw-r-----  1 oracle oinstall   52429312 Feb 13 14:00 redo01b.log

-rw-r-----  1 oracle oinstall   52429312 Feb 13 14:00 redo01a.log

-rw-r-----  1 oracle oinstall    8011776 Feb 13 14:04 control01.ctl

O comando acima foi para verificar nossos datafiles, redo logs e control files. Criados com sucesso.

4.5 - Criando o dicionário de dados Oracle

A criação do dicionário de dados do Oracle é através de scripts que a própria Oracle fornece quando instalado o software. Esses scripts NÃO PODEM SER MODIFICADOS e toda vez que for criar um banco de dados novo, deve ser executado. Ele é obrigatório.

A localização dos scripts fica em $ORACLE_HOME/rdbms/admin, nessa pasta existe diversos scripts que são usados para criação de pacotes administrativos, de produtos adicionais e etc.

Assim que localizado, devemos executar os seguintes scripts, obrigatórios:

• catalog.sql

Cria as views do dicionário de dados.

• catproc.sql

Executa diversos scripts para criação de views e objetos necessário para o banco de dados.

Veja o exemplo:

SQL> @?/rdbms/admin/catalog.sql

...

SQL> @?/rdbms/admin/catproc.sql

Como os scripts são longos e principalmente o catproc.sql que chama diversos outros scripts do /rdbms/admin essa operação pode demorar um pouco, então pode ir tomar um cafézinho nesse momento.

Dica 1

No linux, para executar os scripts você pode utilizar os seguintes caminhos na execução, $ORACLE_HOME/rdbms/admin ou simplesmente ?/rdbms/admin.

Após o termíno da execução do catproc.sql, vamos verificar nosso novo banco de dados. Vamos efetuar as seguintes instruções SQL:

SQL> select instance_name, host_name, status from v$instance;

INSTANCE_NAME    HOST_NAME                      STATUS
---------------- ------------------------------ ------------
ranet            pelspos7h                      OPEN

1 row selected.

Com isso, temos nosso banco de dados no ar. Para quem já conhece a arquitetura Oracle, quando um banco de dados é criado, estará sempre no modo NOARCHIVELOG, como mostra o exemplo:

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

1 row selected.

E caso queira mudar para o modo ARCHIVELOG e trabalhar com as vantagens que o ARCHIVELOG pode lhe oferecer para recuperação do banco de dados, basta fazer os procedimentos abaixo:

4.5.1 - Descendo o banco de dados

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

4.5.2 - Colocando o banco de dados em modo MOUNT

SQL> startup mount pfile=/u01/app/oracle/admin/ranet/pfile/initranet.ora;
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  1268412 bytes
Variable Size             146801988 bytes
Database Buffers          369098752 bytes
Redo Buffers                7118848 bytes
Database mounted.

Dica 2

Sempre que trabalhar com o pfile em $ORACLE_BASE/admin/ranet/pfile, quando for realizar qualquer tipo de startup, irá aparecer o erro ORA-01078 e LRM-00109, pois o arquivo de parâmetro não se encontra no diretório padrão do Oracle, que é $ORACLE_HOME/dbs. Se quiser resolver esse tipo de problema, copie o seu arquivo de parâmetro para o diretório $ORACLE_HOME/dbs e PRONTO!

4.5.3 - Alterar o modo de arquivamento do banco de dados

SQL> alter database archivelog;

Database altered.

4.5.4 - Abrindo o banco de dados

SQL> alter database open;

Database altered.

4.5.5 - Verificando o modo de arquivamento

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     13

Next log sequence to archive   15

Current log sequence           15

4.5.6 - Forçar a geração dos archived logs

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Agora, verifique a origem dos seus archived logs e veja os no sistema operacional. Foi executado o comando ALTER SYSTEM SWITCH LOGFILE três vezes pois nós tinhamos três grupos de redo logs.
SQL> show parameters db_reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/admin/ranet/fl
ashback_area
db_recovery_file_dest_size           big integer 10G
SQL> host;
[oracle@pelspos7h adhoc]$ cd /u01/app/oracle/admin/ranet/flashback_area
[oracle@pelspos7h flashback_area]$ ls -ltr
total 4
drwxr-x—  3 oracle oinstall 4096 Feb 13 14:52 RANET
[oracle@pelspos7h flashback_area]$ ls -R
.:
RANET

./RANET:
archivelog

./RANET/archivelog:
2009_02_13

./RANET/archivelog/2009_02_13:
o1_mf_1_15_4sc99r39_.arc  o1_mf_1_16_4sc99x0o_.arc  o1_mf_1_17_4sc9b2j5_.arc

PRONTO! Agora já temos um banco de dados criado e trabalhando no modo archivelog, agora temos que criar as tablespaces que fazem parte do projeto de banco de dados e realizar alguns ajustes finais, como criação do SPFILE, LISTENER, TNSNAMES e um backup do banco.

5° passo | O projeto de banco de dados

Como o objetivo era aproximar ao máximo do mundo corporativo, criamos um micro-projeto no início para podermos criar um banco de dados com alguns recursos e preparado para aplicação. Todo o processo de criação do banco de dados foi realizado, agora falta somente criar as tablespaces necessárias e ajustes no banco de dados para disponibilizar aos usuários.

5.1 - Criação da tablespace RADAT

A tablespace RADAT será para abrigar todas as tabelas de usuários ou aplicação, e seu tamanho inicial é de 500MB.

SQL> create tablespace RADAT
2  datafile
3     '/u02/oracle/oradata/ranet/radat01.dbf' size 500M
4  online
5  permanent
6  extent management local autoallocate
7  segment space management auto;

Tablespace created.

5.2 - Criação da tablespace RAIDX

A tablespace RAIDX irá armazenar os índices dos usuários ou aplicação, e seu volume inicial é de 200MB.

SQL> create tablespace RAIDX
2  datafile
3     '/u02/oracle/oradata/ranet/raidx01.dbf' size 250M
4  online
5  permanent
6  extent management local autoallocate
7  segment space management auto;

Tablespace created.

Na criação das tablespaces foram utilizadas algumas opções de criação, como por exemplo o extent management e segment space, para tirar as dúvidas de como trabalha cada opção, leia o documento oficial, Oracle Database SQL Reference 10g Release 2.

6° Passo | Criando um SPFILE

Uma dos recursos importantes ao DBA é a utilização do SPFILE, onde permite ao DBA alterar alguns parâmetros dinâmicos sem a necessidade de efetuar um stop/start no banco de dados em horários não apropriados. Para criar o SPFILE é bem simples, veja:

SQL> create spfile from pfile='/u01/app/oracle/admin/ranet/pfile/initranet.ora';

File created.

Para confirmar que iremos utilizar os valores dos parâmetros que montamos o banco de dados, criamos o spfile a partir do nosso arquivo de parâmetros construído no início, por isso a passagem do caminho completo e o nome do pfile.

Porém, mesmo como o SPFILE criado, ainda não é possível utilizar-lo, como mostra o comando abaixo:

SQL> show parameters spfile

NAME                                 TYPE        VALUE

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

spfile                               string

Para começar a utilização do SPFILE, é necessário um stop/start do base, e logo depois verifique se a coluna VALUE já preenchida com o caminho completo e nome do seu SPFILE.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  524288000 bytes

Fixed Size                  1268412 bytes

Variable Size             146801988 bytes

Database Buffers          369098752 bytes

Redo Buffers                7118848 bytes

Database mounted.

Database opened.

SQL> show parameters spfile

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/10.2.0

                                                 /db_1/dbs/spfileranet.ora

Como dito na Dica 2, o caminho utilizado pelo Oracle foi o caminho padrão, $ORACLE_HOME/dbs.

7° Passo | Criando os serviços de rede

Como já estamos como todo o nosso banco de dados criado, vamos ter que disponibilizar aos usuários e possíveis aplicações, e para isso, precisamos do serviço ouvinte (LISTENER) e depois efetuar as configurações nas máquinas client (TNSNAMES) para acessar o banco de dados. E para isso, devemos configurar os arquivos responsáveis por essa tarefa.

7.1 - Criando o Listener

A atividade a ser realizada agora é a criação do LISTENER, ou o serviço ouvinte, que é um processo separado no banco de dados que tem como responsabilidade receber as conexões dos clientes (usuários ou outras bases de dados) e gerenciar o tráfico e pedidos dessas requisições. O arquivo LISTENER.ORA que pode ser encontrado em $ORACLE_HOME/network/admin é o resposável por esse serviço, abaixo segue um exemplo de criação de um LISTENER usando protocolo TCP e disponibilizando as portas 1521 e 1522 para comunicação.

Arquivo: LISTENER.ORA

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PELSPOS7H)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = PELSPOS7H)(PORT = 1522))
)
)

Após criar o seu arquivo listener.ora e salvar-lo, deverá iniciar o serviço do listener, usando o aplicativo LSNRCTL (Listener Control), veja:

[oracle@pelspos7h admin]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-FEB-2009 23:06:18

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PELSPOS7H)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PELSPOS7H)(PORT=1522)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

Ao verificar o atual status do serviço de LISTENER, verificamos que não está disponível, basta iniciarmos o serviço e pronto! Após a configuração nos clientes, a comunicação será possível com o banco de dados.

LSNRCTL> start LISTENER
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pelspos7h)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pelspos7h)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PELSPOS7H)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                27-FEB-2009 23:06:38
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pelspos7h)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pelspos7h)(PORT=1522)))
The listener supports no services
The command completed successfully

Dica 3

Na versão do Oracle Database 10g, existe uma opção no SQL*NET de trabalhar por serviços ou SERVICE_NAME, onde deverá ser realizado outros procedimentos. Mais detalhes nesse documento Quick Start to Oracle Net Connections.

PRONTO! O serviço no lado do servidor de banco de dados já está configurado e pronto para utilização.

7.2 - Configurando o TNSNAMES.ORA e SQLNET.ORA do cliente.

Os arquivos TNSNAMES.ORA e SQLNET.ORA são os arquivos de configuração no lado do cliente ou em algum banco de dados remoto, cada arquivo com sua respectiva função. O arquivo TNSNAMES.ORA tem como finalidade fornecer as principais informações para um serviço ouvinte, como nome do banco de dados, porta de comunicação, tipo de protocolo utilizado e host de destino. A função do arquivo SQLNET.ORA é dizer o metódo de conexão, habilitar a rota de específicas conexões, tipo de autenticação utilizada e fornecer um rastreamento detalhado sobre a comunicação do cliente e servidor. Ambos os arquivos podem ser encontrados em $ORACLE_HOME/network/admin. Abaixo segue um modelo que iremos utilizar.

Arquivo: SQLNET.ORA

AUTOMATIC_IPC = OFF
names.directory_path = (TNSNAMES)
names.default_domain = world
name.default_zone = world

Arquivo: TNSNAMES.ORA

ranet.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = 10.72.30.83)(Port = 1522))
)
(CONNECT_DATA =
(SID = ranet)
)
)

Caso você queira acessar o seu banco de dados de uma outra máquina que tenha o Oracle Client instalado, basta adicionar a entrada de TNS acima, mostrada no modelo do TNSNAMES.ORA e caso seja necessário, colocar as configurações de SQLNET acima.

7.3 - Validação da conexão

Ao realizar todas as tarefas acima de configuração dos arquivos do SQLNET, basta validar se a conexão com o banco de dados remotamente será realizado com sucesso, para isso, basta utilizar o aplicativo TNSPING para validar. Exemplo.

[oracle@pelspos7h admin]$ tnsping ranet

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 27-FEB-2009 23:33:58

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

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 10.72.30.83)(Port = 1522))) (CONNECT_DATA = (SID=ranet)))
OK (0 msec)

UFA! Agora podemos liberar o nosso mini banco de dados aos usuários e iniciar posteriormente as configurações de usuários e aplicações na base. Esse artigo foi bem longo e cansativo, porém, a principal ideia era fornecer os conceitos básicos na criação de um banco de dados manualmente, como:

• Entender os conceitos do Optial Flexible Architecture (OFA);
• Variáveis de ambiente essênciais ao Oracle;
• Entender os scripts e processos de criação do banco de dados;
• Conhecer os scripts básicos para criar o dicionário de dados do Oracle Server;
• Colocar o banco de dados no modo de arquivamento automático;
• Realizar as tarefas básicas de tablespaces para um determinado projeto;
• Ajustar o banco de dados para acesso remoto.

Todos esses passos são de real importância para um projeto de banco de dados, e nunca se esqueça, depois de realizar todas as tarefas acima, faça um BACKUP FULL do banco de dados para não ter problemas e aumentar a segurança do projeto.

Qualquer dúvida, crítica e sugestão, estou disponível no e-mail ou aqui no blog.

Abraços,

Olá,

Nesse final de semana (14/02/2009 - DST Brazil) estamos terminando o horário de verão, que vale somente para a região Sul, Sudeste e Centro-Oeste. Com o termíno, deveremos atrasar nossos relógios em 1 hora, e isso será um grande problema para os DBAs que tem banco de dados Oracle na produção da empresa, pois apenas atrasar o horário do servidor com o banco de dados no ar (online) pode causar grandes danos.

Problemas

Ao atrasar o horário do servidor em 1 hora, e não realizar os procedimentos corretos para efetuar essa atividade, pode trazer diversos impactos ao banco de dados Oracle. E esses impactos podemos listar abaixo:

• Sobreescrita na geração dos Archived Logs;
• Problemas de comunicação com o LISTENER do Oracle Server;
• Problemas de novos registros incluídos atráves da aplicação ou processos de ETL, pois se esses registros trabalham com funções de data como SYSDATE, TIMESTAMP ou SYSTIMESTAMP, podem ser invalidadas por primarys keys e constraints de check no modelo e banco de dados;
• Para ambientes RAC (Real Application Cluster), mudar o horário pode trazer diversos problemas, desde o CRS (Cluster Registry Services), LISTENER e InterConnect, pois podem ocorrer sobreescritas na gravação dos logs e sincronização dos nós;
• Para ambientes DataGuard ou Stand-by, podem ocorrer problemas também com sobreescritas dos redo logs, onde pode causar problemas e até mesmo erros do kernel do Oracle Server gerando os ORA-600;
• Problemas nas mensagens gravadas no alert.log;
• Problemas no agendamento de JOBS no banco de dados, que seja feito por DBMS_JOB ou DBMS_SCHEDULER;
• Se ocorre a sobreescrita dos archived logs, terá problemas com o Point-in-Time-Recovery do seu banco de dados, e com isso, uma simples troca do horário pode ser uma catástrofe em seu backup e recover;
• Pode ocorrer problemas com o JVM do Oracle;

Todos os impactos citados acima, estão resumidos e que podem ser afetados de imediato, existem outros impactos que podem aparecer depois de 2 ou 3 dias e até mesmo semanas. E para não correr esse risco, existe um procedimento bem básico para os DBAs.

Procedimento

Antes de realizar a troca do horário do servidor e futuramente do banco de dados, siga os procedimentos abaixo:

1. Realizar um backup full do banco de dados.
2. Parar os serviços do Listener, exemplo: lsnrctl stop ou lsnrctl <nome_listener> stop;
3. Parar o banco de dados, com shutdown immediate, normal ou transactional.;
4. Para ambiente Windows: Depois que descer o banco de dados pelo SQL*PLUS, descer o serviço do windows, exemplo: net stop OracleService<nome_da_base>;
5. Anotar o horário de STOP GERAL, para saber com exatidão o momento da parada de todos os serviços;
6. Alterar o horário do servidor (Windows\Linux\Unix);
7. Após a troca do horário no servidor, esperar 1 hora para subir os bancos. Exemplo, se meu STOP GERAL foi as 00:05AM (antes da troca), anoto esse valor e espero 1 hora, realizo a troca do horário e quando for 01:05AM, meu horário será atrasado para 00:05AM novamente (ajuste para o fim do horário de verão), e a partir desse horário posso subir todos os serviços novamente a partir do horário que desceu, deste modo não regresso no tempo.
8. Subir todos os serviços novamento, pode ser pela ordem BANCO DE DADOS -> LISTENER -> APLICAÇÃO.

E pronto! Já estamos com nossos horários ajustados para o horário de Brasilia (Oficial Brasileiro).

Recomendação

Nunca deixem os servidores de banco de dados com o ajuste de horário de verão automático, pois a cada ano, as datas são de início e fim podem sofrer alterações e seja necessário patchs para os novos ajustes e fora que isso, pode trazer todos os problemas citados acima no banco de dados, então faça sempre manualmente.

Blog cadastrado no Rec6

Abraços,

Olá,

Uma dos principais conceitos sobre arquitetura física do Oracle, é a marca D’água, uma tradução de HWM  - High Water Mark, ele que indica o limite que uma tabela já ocupou de espaço físico no seu banco de dados. Mas, vamos um pouco mais a fundo.

O que é uma Marca D’água (HWM - High Water Mark)?

A marca d’água é o limite do número de blocos que uma tabela pode estar utilizando, resumindo para um conceito mais simples, toda vez que uma tabela recebe um INSERT (novos registros), essa marca na tabela aumenta dizendo ao Oracle Server a quantidade de blocos que a tabela está utilizando, automaticamente, a quantidade de blocos, multiplicado, pelo tamanho do db_block_size do banco de dados, diz o valor físico real que está sendo utilizado.

Mas, esse valor real não é o valor que o Oracle irá alocar, pois irá depender de alguns outros pontos, como:

• Se a tabela está sendo gerenciada por sí própria ou pela tablespace.
• Irá depender dos tamanhos dos extents, exemplo, INITIAL_EXTENT e NEXT_EXTENT.
• Também, irá depender do tipo de gerenciamento, se é SEGMENT MANAGEMENT AUTO ou UNIFORM.
• E a quantidade de blocos que um EXTENT pode suportar.

Vamos ver como funciona a marca d’água na prática, um alguns exemplos práticos.

Vou criar uma tabela simples, chamada TSTDBA.

SQL> create table TESTE (a varchar2(100) not null, b number(7) not null);

Tabela criada.

Agora, vamos analisar como está a estrutura para o Oracle, pois a tabela não possui nenhum valor e nenhuma estatística coletada.

SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,'DD-MM-RRRR HH24:MI:SS') as "ANALYZE"
  2  from dba_tables
  3  where table_name = 'TSTDBA';

OWNER      TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO    TSTDBA

Até o momento, tudo sem surpresas para nós.

Então, vamos popular essa tabela com alguns registros, veja o exemplo.

SQL> l

  1  declare
  2     contador integer;
  3  begin
  4     contador := 1;
  5     while contador <= 1000 loop
  6             insert into TSTDBA values ('TESTE',contador);
  7             contador := contador + 1;
  8     end loop;
  9     commit;
 10* end;
SQL> /

Procedimento PL/SQL concluído com sucesso.

SQL> exec dbms_stats.gather_table_stats (ownname=>'RODRIGO',tabname=>'TSTDBA',estimate_percent=>null,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>6);

Procedimento PL/SQL concluído com sucesso.

Verifiquem que fiz um pequeno bloco PL/SQL para inserir dados em minha tabela, cerca de 1.000 registros. Após isso, preciso dizer ao Oracle, como a tabela está, seu volume e outras coisas mais, então, fiz um analyze na tabela para atualizar as informações estruturais dela no dicionário Oracle, ao fazer o analyze com o DBMS_STATS, o resultado do SELECT acima, agora é esse.

SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,'DD-MM-RRRR HH24:MI:SS') as "ANALYZE"
  2  from dba_tables
  3  where table_name = 'TSTDBA';

OWNER      TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO    TSTDBA                                  5            0       1000 06-10-2008 19:42:08

Veja, a nossa tabela está utilizando 5 blocos, o db_block_size do meu banco de dados é de 8KB, então, resumidamente, ele deveria estar utilizando cerca de 40KB, certo?

SQL> select 8192*5 from dual;

    8192*5
----------
     40960

Mas, se consultar o seu tamanho na dba_segments temos:

SQL> select segment_name, sum(bytes)/1024 from dba_segments where segment_name = 'TSTDBA' group by segment_name;

SEGMENT_NAME                                                                      SUM(BYTES)/1024
--------------------------------------------------------------------------------- ---------------
TSTDBA                                                                                         64

O resultado para o tamanho da tabela TSTDBA é 64KB, porque, o INITIAL_EXTENT da tabela é de 64KB, e como os 1.000 registros ocuparam apenas 40KB, um único extent consegui suportar.

SQL> select initial_extent/1024, next_extent from dba_tables where table_name = 'TSTDBA';

INITIAL_EXTENT/1024 NEXT_EXTENT
------------------- -----------
                 64

Pois bem! Rodrigo, e o tal do HWM, até onde está entrando nisso?

Vamos começar a brincar agora, veja que após o analyze, minha tabela TSTDBA está utilizando 5 blocos de dados, certo? Teoricamente, se eu fizer um TRUNCATE TABLE, eu não vou mais utilizar nenhum bloco, e minha marca d’água deveria baixar, mas, acontece isso:

SQL> truncate table TSTDBA;

Tabela truncada.

SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,'DD-MM-RRRR HH24:MI:SS') as "ANALYZE"
  2  from dba_tables
  3  where table_name = 'TSTDBA';

OWNER      TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO    TSTDBA                                  5            0       1000 06-10-2008 19:44:18

A minha tabela continua com se estivesse com 5 blocos, o que isso pode nos prejudicar:

• Esse exemplo é bem simples, mas para tabelas com milhares de registros, poderá influenciar os FULL-TABLES SCANS.
• Ao realizar um INSERT convencional, ou seja, sem o hint /* + APPEND */, ele irá procurar por bocos livres e irá consumir CPU e demorar um tempo para sua execução.
• Se minha marca d’água estiver muito alta, ou seja, estiver armazenando um alto valor de blocos utilizados, e você sabe, que ele não está utilizando tudo isso, você terá uma alocação de EXTENTS desnecessários no banco de dados, e isso irá ocupar espaço desnecessários.

 Caso eu quisesse diminuir o tamanho do meu segmento de tabela, eu não iria conseguir, pois além da marca d’água é inferior aos meus 64KB, pois bem, tente realizar um insert agora de 2.000.000 de registros e vamos ver o que acontece.

 SQL> declare
  2     contador integer;
  3  begin
  4     contador := 1;
  5     while contador <= 2000000 loop
  6             insert into TSTDBA values ('TESTE',contador);
  7             contador := contador + 1;
  8     end loop;
  9     commit;
 10  end;
 11  /

Procedimento PL/SQL concluído com sucesso.

SQL> exec dbms_stats.gather_table_stats (ownname=>'RODRIGO',tabname=>'TSTDBA',estimate_percent=>null,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>6);

Procedimento PL/SQL concluído com sucesso.

SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,'DD-MM-RRRR HH24:MI:SS') as "ANALYZE"
  2  from dba_tables
  3  where table_name = 'TSTDBA';

OWNER      TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO    TSTDBA                               4654            0    2000000 06-10-2008 22:37:24

SQL> show parameters db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> select (8192*4654)/1024/1024 as "Tamanho" from dual;

   Tamanho
----------
 36,359375

SQL> select segment_name, sum(bytes)/1024/1024 from dba_segments where segment_name = 'TSTDBA' group by segment_name;

SEGMENT_NAME                                                                      SUM(BYTES)/1024/1024
--------------------------------------------------------------------------------- --------------------
TSTDBA                                                                                              37

Se quizer analisar melhor como ficou a distribuição, veja a dba_extents, abaixo vou mostrar apenas um pequeno resumo da quantidade de extents alocados e seus respectivo tamanho.

SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
  2  from dba_extents
  3  where segment_name = 'TSTDBA'
  4  group by segment_name;

SEGMENT_NAME         COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA                             52                   37

Bom, vimos que agora temos um valor legal de extents alocados, e mesmo após o TRUNCATE continuo com uma alocação de extents, que totaliza os 37MB da tabela, então, minha marca d’água está posicionado no 51° extent, que seria o limite do numeros de blocos alcançados.

Conseguimos entender como funciona a marca d’água, o que isso pode nos causar?

A chamada fragmentação de tabela, além da marca d’água elevar o número de extents no dicionário, prejudicando muitas vezes os planos de execução e os table full scans, vamos ter também perca de espaço físico para a tablespace, espaço que não poderam ser alocados por outro segmento. Vamos a uma demostração prática de como funciona a fragmentação.

SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,'DD-MM-RRRR HH24:MI:SS') as "ANALYZE"
  2  from dba_tables
  3  where table_name = 'TSTDBA';

OWNER      TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO    TSTDBA                               4654            0    2000000 06-10-2008 22:37:24

SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
  2  from dba_extents
  3  where segment_name = 'TSTDBA'
  4  group by segment_name;

SEGMENT_NAME         COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA                             52                   37

A minha tabela TSTDBA continua com seus 2.000.000 de registros, após o analyze acima, vimos que está a atual estrutura da tabela, e se realizarmos diversos DELETES em grandes quantidades, o que poderemos ter?

SQL> delete from TSTDBA where b between 10000 and 20000;

10001 linhas deletadas.

SQL> delete from TSTDBA where b between 50000 and 200000;

150001 linhas deletadas.

SQL> delete from TSTDBA where b between 400000 and 700000;

300001 linhas deletadas.

SQL> delete from TSTDBA where b between 1000000 and 1300000;

300001 linhas deletadas.

SQL> commit;

Commit concluído.

SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
  2  from dba_extents
  3  where segment_name = 'TSTDBA'
  4  group by segment_name;

SEGMENT_NAME         COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA                             52                   37

Vamos passar um analyze para validar toda a estrutura da tabela.

SQL> exec dbms_stats.gather_table_stats (ownname=>'RODRIGO',tabname=>'TSTDBA',estimate_percent=>null,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>6);

Procedimento PL/SQL concluído com sucesso.

Veja o resultado para os novos valores.

SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,'DD-MM-RRRR HH24:MI:SS') as "ANALYZE"
  2  from dba_tables
  3  where table_name = 'TSTDBA';

OWNER      TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO    TSTDBA                               4654            0    1239996 06-10-2008 23:09:01

A quantidade de extents não alterou depois de apagarmos diversos registros, isso causa a conhecida fragmentação do segmento, mesmo que após calcularmos a quantidade de registro exato da tabela.

SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
  2  from dba_extents
  3  where segment_name = 'TSTDBA'
  4  group by segment_name;

SEGMENT_NAME         COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA                             52                   37

Para resolvermos esse problema de fragmentação, bastamos reconstruir o mapa binário da tabela, para isso, apenas use um MOVE sem mencionar a tablespace que resolve nosso problema.

SQL> alter table TSTDBA move;

Tabela alterada.

SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,'DD-MM-RRRR HH24:MI:SS') as "ANALYZE"
  2  from dba_tables
  3  where table_name = 'TSTDBA';

OWNER      TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO    TSTDBA                               4654            0    1239996 06-10-2008 23:09:01

SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
  2  from dba_extents
  3  where segment_name = 'TSTDBA'
  4  group by segment_name;

SEGMENT_NAME         COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA                             38                   23

PRONTO! Veja que após nosso “rebuild” na tabela, liberamos cerca de 15MB para a tablespace, fazendo apenas uma reconstrução dos extents da tabela.

Existem muitos outros conceitos envolvidos sobre a alocação de extents, sem mencionar os freelists, gerenciamento das tablespaces e diferenças entre os segmentos de tabela e índice, tudo isso foi apenas um modo de ilustrar os problemas que podem causar perda de performance em nossos ambientes.

Existe uma matéria que escrevi para a iMasters algum tempo atrás que explica com um pouco mais de detalhes como funciona a arquitetura de armazenamento lógico do banco de dados Oracle, o artigo Arquitetura de armazenamento lógico, que sanar algumas dúvidas.

A idéia principal do post foi iniciar desde o conceito de HWM (High Water Mark) até sua fragmentação, passando por várias fases, para fornecer um melhor entendimento de como a arquitetura Oracle funciona.

Abraços,

 

Olá,

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,

 

 

Olá,

Uma das grandes dúvidas que muitos iniciantes tem quando quando começa a trabalhar com Oracle, é saber qual a função de cada usuário padrão do Oracle Server e também como se proteger deles.

Muitos DBAs, não deixam a conta de usuários padrões travada “Locked”, com isso, qualquer outro que conheça um usuário padrão e saiba a senha dele inicial, poderá acessar seu banco de dados com privilégios de DBA e fazer o que bem entender no banco de dados. E isso é ruim para nós.

Abaixo, vou mostrar uma lista com esses usuários, sua função e sua senha padrão.

Username : CTXSYS

Password : CTXSYS

Descrição : Usuário proprietário do produto Oracle Text.

Username : SYS

Password : change_on_install

Descrição : Usuário utilizado para realizar todas as tarefas de administração do banco de dados e proprietário do dicionário do banco de dados.

Username : SYSTEM

Password : manager

Descrição : Usuário de administração do banco de dados. Funciona como se fosse um gerente.

Username : SYSMAN

Password : change_on_install

Descrição : Usuário administrativo para realizar as tarefas pelo Oracle Enterprise Manager

Username : SI_INFORMTN_SCHEMA

Password : si_informtn_schema

Descrição : Essa conta armazena informações das views do SQL/MM, utilizado pelo Oracle InterMedia.

Username : OUTLN

Password : outln

Descrição : Utilizado para o recurso de OUTLINE VIEW, ou seja, o Oracle permite que você armazena informações de plano de execução para suas instruções SQL, esse owner que é o responsável por armazenar essas informações.

Username : ORDSYS

Password : ordsys

Descrição : Usuário administrativo do Oracle InterMedia.

Username : ORDPLUGINS

Password : ordplugins

Descrição : Usuário para os plugins de outras aplicações que utiliza o InterMedia.

Username : OLAPSYS

Password : manager

Descrição : Usuário utilizado para criar a estrutura do metadata OLAP.

Username : DMSYS

Password : dmsys

Descrição : Usuário administrativo do Data Mining.

Username : MDSYS

Password : mdsys

Descrição : Usuário administrativo para Oracle Spatial e Locator do InterMedia.

Username : MDDATA

Password : mddata

Descrição : Usuário utilizado pelo Oracle Spatial para armazenar dados do Geocoder e rotas.

Username : DBSNMP

Password : dbsnmp

Descrição : Usuário utilizado pelo Agent Management do Oracle Enterprise Manager e gerenciar o banco de dados.

Agora que você conhece os principais owners que estão presentes na instalação do Oracle Database, cabe a você bloquear os usuários que não utiliza para aumentar a segurança do banco de dados, por exemplo, tenho um banco de dados que trabalhar somente com os componentes padrão do PL/SQL e SQL. Então, em minha base não é necessário eu ter owners como CTXSYS, DBSNMP, MDDATA, MDSYS, OLAPSYS, ORDPLUGINS, então eu posso bloquear sem problemas, pois meu banco de dados não utiliza tais produtos como Data Mining, OLAP, Spatial e InterMedia.

De onde vêm muitos desses usuários?

Caso, você tenha feito a instalação de um banco de dados pelo DBCA (DataBase Create Assistent), e escolheu pela instalação padrão (DEFAULT - ou NEXT, NEXT e FINISH), ele irá instalar esses usuários, principalmente se está instalando a versão Enterprise.

Se escolher a opção INSTALL ADVANCED, terá a oportunidade de escolher quais produtos serão instalados no seu banco de dados e esses usuários não serão criados.

Bom, agora basta a você conhecer mais um pouco sobre esses usuários e proteger seu banco de dados já que eles podem se tornar um acesso fácil a usuários “mal-educados” que tem em qualquer empresa.

Abraços,

 

Olá,

Recentemente, estou participando de uma migração de um banco de dados, Oracle 8i para Oracle 10g, da plataforma 32-bits para 64-bits e uma aplicação Forms/Reports 6i (Oracle Developer 6i), e se deparamos com um problema de Security após a criação e importação dos owners para o novo banco de dados.

Quando mandei o desenvolvedor conectar a aplicação no novo banco de dados, para fins de testes, conectividade e possíveis problemas do 10g (Pois é uma migração do 8i, e a aplicação está toda em REGRA), o Forms nos emitia um erro estranho, como mostra abaixo:

FRM-10256: User is not authorized to run Form Builder Menu.

Logo de início, pensei que poderia ser problemas no owner da aplicação (no banco de dados), ou o menu da aplicação, que utiliza outro owner para validar e construir o menu. Fiz todos os checks necessários, como:

• Analisar o “STATUS ACCOUNT” de todos os owners no banco de dados, que pode ser feito pela view dba_users
• Olhar qual perfil de banco de dados os owners estão usando, todos estavam com DEFAULT para início.
• Analisar com a base de produção, objetos inválidos, views e qualquer outro objeto que tenha relação com a construção dos menus, e também nada.

Então, resolvi junto com o desenvolvedor, pesquisar sobre o assunto e esse erro específico, e encontramos a seguinte solução pelo Metalink.

Visão Geral

A segurança do Oracle Forms é baseada em roles (papéis) no banco de dados Oracle. Essas roles são um método de permitir o acesso as informações do banco de dados para os usuários, portanto, se nenhum usuário não tem acesso a qualquer coisa, essas roles podem ajudar a facilitar o acesso.

Desde que os usuários tenham essas roles definidas pelo DBA ou Administrador de aplicação, os módulos de MENU e ITENS DO MENU terão um controle de acesso feito internamento pelo menu da aplicação.

Solução

Para resolver esse problema, o DBA, conectado no banco de dados com o usuário SYS, deve executar um script de segurança que vêm junto com o Developer 6i, que é o script abaixo:

Para ambiente Windows

%ORACLE_HOME%\tools\dbtab\forms60\frm60sec.sql

Para ambiente Linux\Unix

$ORACLE_HOME/forms60/admin/sql/frm60sec.sql

Esse script é pertecente ao ORACLE_HOME do Developer 6i, não confunda com o ORACLE_HOME do banco de dados. Execute ele com o usuário SYS e seus problemas vão terminar.

Abaixo, segue o contéudo do script, caso, estejam com esse problema e não conseguem acessar o servidor de aplicação por motivo de segurança da empresa ou qualquer outro motivo.

FRM60sec.sql

create or replace view FRM50_ENABLED_ROLES as
select urp.granted_role role,
sum(distinct decode(rrp.granted_role,
'ORAFORMS$OSC',2,
'ORAFORMS$BGM',4,
'ORAFORMS$DBG',1,0)) flag
from sys.user_role_privs urp, role_role_privs rrp
where urp.granted_role = rrp.role (+)
and urp.granted_role not like 'ORAFORMS$%'
group by urp.granted_role;

create public synonym FRM50_ENABLED_ROLES for system.FRM50_ENABLED_ROLES;

create role ORAFORMS$OSC;
create role ORAFORMS$DBG;
create role ORAFORMS$BGM;

PRONTO! Acho que agora vai resolver a vida, caso tenha problemas, dê os grants de SELECT do SYNONYM para os usuários que precisa acessar a aplicação.

Abraços,

Olá,

Uma das táticas mais “sujas”, diga-se de passagem, é a alteração de senha do usuário sem a sua permissão, pois pode ocorrer em diversos momentos do dia-a-dia, por exemplo:

• O cara saiu de férias, é deixou algum objeto no owner dele.
• Preciso pegar algumas informações da tabela do FULANO.
• É necessário fornecer algumas permissões do usuário X para Y.
• Ou a MELHOR! Existe um owner na aplicação que foi criado em 1900 e bolinhas, e agora, precisa de manutenção e ninguém tem acesso a esse usuário, porque ninguem sabe a senha.

Bom, para resolver esses “probleminhas”, existe uma técnica no Oracle que podemos utilizar para ter acesso completo a um específico usuário, um pequeno “hack” no dicionário Oracle, mas para conseguir a façanha, é necessário que tenha acesso a view DBA_USERS, que foi utilizado nesse exemplo.

LEMBRANDO

Lógicamente, que todos os exemplos que citei acima, poderiam ser feitos pelo DBA da empresa ou alguém que tenha acesso a usuários gerenciais do banco de dados, como SYSTEM, usuários com role de DBA e etc. Isso é apenas um exemplo de como se aplicar a técnica.

Agora, vou passar o exemplo prático de como funciona.

1) Vamos criar um usuário.

SQL> @id

HORA EXECUTADA

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

09-09-2008 11:45:57

INSTANCE_NAME   HOST_NAME            STATUS

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

xe              DBARODRIGO          OPEN

USER IS "SYS"

SQL> create user RODRIGO

  2  identified by rodrigo;

Usuário criado.

SQL> grant create session to RODRIGO;

Concessão bem-sucedida.

SQL> disco

Desconectado de Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

2) Teste a conexão do novo usuário no banco de dados.

SQL> conn rodrigo/rodrigo
Conectado.

SQL> disco

Desconectado de Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

3) Conectado com um usuário administrativo, faça um select básico na view DBA_USERS.

SQL> conn system
Informe a senha:
Conectado.

SQL> select username, account_status, password
  2  from dba_users
  3  where username = 'RODRIGO';

USERNAME                       ACCOUNT_STATUS                   PASSWORD
------------------------------ -------------------------------- ------------------------------
RODRIGO                        OPEN                             F697FBF0BB2DA2EC

4) Altere a senha do usuário desejado, no exemplo, vou alterar a senha para FERNANDA.

SQL> alter user RODRIGO identified by FERNANDA;

Usuário alterado.

5) Pegue o valor gerado para a nova senha.

SQL> select username, account_status, password
2  from dba_users
3  where username = ‘RODRIGO’;

USERNAME                       ACCOUNT_STATUS                   PASSWORD
—————————— ——————————– ——————————

RODRIGO                        OPEN                             FB34D454E9FFDE18

Observação

Pode parecer confuso, mais vamos recapitular os valores que são equivalentes as senhas:

F697FBF0BB2DA2EC = RODRIGO
FB34D454E9FFDE18 = FERNANDA

6) Para voltar a senha anterior, apenas utilize a opção VALUES junto com IDENTIFIED BYcom o valor da coluna password.

SQL> conn system

Informe a senha:

Conectado.

SQL> alter user RODRIGO identified by values 'F697FBF0BB2DA2EC';

O valor F697FBF0BB2DA2EC (gerado por um algoritmo HASH), é equivalente ao valor RODRIGO.

Agora, veja os testes.

SQL> conn rodrigo/fernanda
Conectado.
SQL> disco
Desconectado de Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn system
Informe a senha:
Conectado.
SQL> alter user RODRIGO identified by values 'F697FBF0BB2DA2EC';

Usuário alterado.

SQL> disco
Desconectado de Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> conn rodrigo/rodrigo
Conectado.
SQL> disco

Desconectado de Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Como eu não alterei o valor para a senha FERNANDA, se eu tentar logar com essa senha, terei erros, veja.

SQL> conn rodrigo/fernanda

ERROR:

ORA-01017: invalid username/password; logon denied

FINISH! Uma técnica de hack bem conhecida entre os DBAS, que até a versão 10gR2 (no Patchset 10.2.0.4) ainda continua, eu não sei ainda se nas versões 11g já possui algum tipo de segurança nesse ponto, então aprecie com moderação.

Abraços,

Olá,

Umas das new features mais bem-vindas do Oracle Database 10g, é a opção DROP DATABASE, antigamente, até a versão 9iR2, eliminar um banco de dados era uma coisa demorada e chata. Agora, com essa nova opção, apagar um banco de dados está igual a eliminar uma tabela ou qualquer outro objeto.

Ao invocar o comando DROP DATABASE, todos os seus control files, arquivos de redo logs, datafiles e seu arquivo de parâmetro (PFILE/SPFILE) são apagados do servidor, ou seja, todos os arquivos que são listados internamente no control file, onde estão localizados os arquivos para aquele determinado banco de dados, são eliminados.

Para utilizar o DROP DATABASE, existe algumas restrições, veja:

• O banco de dados deve estar montado, ou seja, sem acesso aos usuários.
• Ao montar o banco de dados, deve estar no modo exclusivo (Exclusive mode) e não compartilhado.
• Quando for montar o banco de dados, a opção de RESTRICT deve ser utilizado. Significa que apenas usuários com opção de acesso restrito são permitidos.

Abaixo, vou passar um exemplo prático de como utilizar esse comando.

[oracle@pelspos18 ~]$ sqlplus “/ as sysdba”

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 5 11:40:44 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict pfile='/u01/app/oracle/admin/finp/pfile/initfinp.ora';
ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2087416 bytes
Variable Size            1543505416 bytes
Database Buffers         1593835520 bytes
Redo Buffers               81797120 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> exit

Atenção

Ao iniciar a instância em MOUNT RESTRICT, utilizei a opção de iniciar por um arquivo de parâmetro alternativo, pois, quando eu executar DROP DATABASE, eu não queria eliminar esse arquivo.

PRONTO! Banco de dados eliminado, é válido lembrar que arquivos como: archives, cópias de backup ou backupsets gerados por RMAN e traces gerados pelos serviços de background, não são apagados pelo DROP DATABASE, o DBA deverá apagar esses arquivos manualmente.

Abraços,

Olá,

Vamos tocar num assunto interessante, as transações pendentes quando se trabalhar com banco de dados em ambientes distribuídos. Essas transações é uma forma de comunicação entre bases de dados Oracle, atráves de DBLINKS, e trabalham com uma tecnologia de controle chamada TWO-PHASE Commit, traduzida, Comprometimento em duas fases, que server como garantia de integridade entre as bases oracle, tanto na base de origem e destino, permitindo que a transação seja segura e íntegra.

A tecnologia de TWO-PHASE Commit, basicamente surgiu para controlar e monitorar as atividades de commit e rollback das transações em ambientes de bases de dados distribuídos, que como dito acima, serve para garantir a consistência dos dados. Existe desde a versão 8i um processo de plano de fundo (background process) chamado RECO, que sua principal função no banco de dados é monitorar todas essas transações, a partir de um LOCAL_TRAN_ID (na base de origem) e um GLOBAL_TRAN_ID (na base de destino) e dizer ao banco de dados, qual é o estado das transações, e se tiver algum problema, tentar recuperar-las.

Junto a com tecnologia, o dicionário de dados ganhou duas visões, a primeira é a dba_2pc_pending, que tem como objetivo listar todas as transações que estão pendentes no banco de dados, que por algum motivo o RECO não fez a sua recuperação ou se a transação ainda está sendo efetivada ou não no banco de dados, e temos também a visão dba_2pc_neighbors, que lista todas as entradas e saídas das transações que estão pendentes, qual o banco de dados, usuário e etc.

O objetivo desse post não é aprensetar a tecnologia TWO-PHASE Commit que é velha, desde 1980 está implementado nos banco de dados Oracle, e sim, de como eliminar essas transações que ficam “pressas” em nossos banco de dados.

Abaixo, segue um exemplo de um ambiente real de como as duas visões acima podem nos auxiliar na eliminação das transações, veja:

SQL> l
  1* select local_tran_id, global_tran_id, state, fail_time, force_time from dba_2pc_pending
SQL> /

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                           STATE            FAIL_TIM FORCE_TI
---------------------- ---------------------------------------- ---------------- -------- --------
14.28.232339           PARA.WORLD.0964cc71.14.28.232339         collecting       30/07/08
4.26.229018            PARA.WORLD.0964cc71.4.26.229018          collecting       31/07/08
12.42.248854           PARA.WORLD.0964cc71.12.42.248854         collecting       11/08/08

SQL> l
  1* select local_tran_id, in_out, database, dbuser_owner from dba_2pc_neighbors
SQL> /

LOCAL_TRAN_ID          IN_ DATABASE             DBUSER_OWNER
---------------------- --- -------------------- ------------------------------
14.28.232339           in                       ARA_VE_WANDERSON
14.28.232339           in  PARA.WORLD           DPD
4.26.229018            in                       ARA_VE_SANSAO
12.42.248854           in                       ARA_VE_MELQUESEDETE
14.28.232339           out PEL_DIST_ARA.WORLD   DPD
14.28.232339           out PEL_DIST_GOI.WORLD   DPD
4.26.229018            out PEL_DIST_GOI.WORLD   DPD
12.42.248854           out PEL_DIST_GOI.WORLD   DPD

 Perceba que as duas visões podem nos fornecer ótimas informações sobre as pendências que estão no banco de dados, o que nós iremos precisar é apenas o LOCAL_TRAN_ID, que é a identificação da transação distribuída e a partir dessa transação, saber qual é o seu status, pela coluna STATE.

Observe que no primeiro SELECT na visão dba_2pc_pending, a transação de id 14.28.232339 está com seu status de COLLECTING (Coletando) e até agora está um zumbi dentro do banco de dados, pois basta observar pela data de FAIL_TIME (Data de Falha) que o processo RECO não conseguiu fazer sua recuperação até o momento. Então, devemos eliminar-la manualmente, habilitando no banco de dados, a opção de recuperação distribuída, deste modo:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Seg Ago 11 14:21:50 2008

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

SQL> conn sys@pel_dist_ara as sysdba
Informe a senha:
Conectado.
SQL> alter system enable distributed recovery;

Sistema alterado.

SQL> select local_tran_id, global_tran_id, state, fail_time, force_time from dba_2pc_pending;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                           STATE            FAIL_TIM FORCE_TI
---------------------- ---------------------------------------- ---------------- -------- --------
14.28.232339           PARA.WORLD.0964cc71.14.28.232339         collecting       30/07/08
4.26.229018            PARA.WORLD.0964cc71.4.26.229018          collecting       31/07/08
12.42.248854           PARA.WORLD.0964cc71.12.42.248854         collecting       11/08/08

SQL> exec dbms_transaction.purge_lost_db_entry ('14.28.232339');

Procedimento PL/SQL concluído com sucesso.

SQL> commit;

Commit concluído.

SQL> select local_tran_id, global_tran_id, state, fail_time, force_time from dba_2pc_pending;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                           STATE            FAIL_TIM FORCE_TI
---------------------- ---------------------------------------- ---------------- -------- --------
4.26.229018            PARA.WORLD.0964cc71.4.26.229018          collecting       31/07/08
12.42.248854           PARA.WORLD.0964cc71.12.42.248854         collecting       11/08/08

Eu também utilizei o pacote DBMS_TRANSACTION, o seu procedimento PURGE_LOST_DB_ENTRY, que é utilizado nesses casos para a eliminação de transações pendentes sem nenhum comprometimento do ambiente oracle.

E sempre que for utilizar essa técnica para eliminar transação pendente, sempre se lembre de duas coisas:

1. Sempre deverá executar as ações acima com um usuário com permissão de SYSDBA, ou com o usuário SYS, com a role SYSDBA.

2. Para habilitar a recuperação de transação, podemos utilizar o seguinte comando:

alter system enable distributed recovery;

Para as versões 9i, que foi o começo dos segmentos de UNDO (nosso velho amigo rollback), devemos alterar o seguinte parâmetro da instância:

alter system set “_smu_debug_mode”=4;

É um parâmetro não documentado do banco de dados, que é limpar as transações que estão utilizando os segmentos de UNDO, isso foi corrigido na versão 10g posteriormente.

Após a realização de todas as tarefas acima, poderá ver que o processo RECO não está gerando mais traces no diretório do BACKGROUND_DUMP_DEST das transações que não conseguiu recuperar.

Isso é uma técnica muito utilizada quando queremos eliminar essas transações para evitar lock-held ou incosistência nos dados, mas lembre-se, sempre veja a coluna FAIL_TIME para ver desde quando não está conseguindo efetuar a recuperação, pois a dba_2pc_pending também informa as transações que estão pendentes no dia. Tome cuidado.

Abraços,