Conta na Nuvem Faça login na Nuvem Inscreva-se no Modo Gratuito na Nuvem

Conta da Oracle

Fale com um especialista

Criando cópias menores do seu banco de produção.

Por Deiby Gómez e Alex Zaballa ,
Postado em Novembro 2014

Revisado por Marcelo Pivovar - Solution Architect

Uma das tarefas mais comuns de um DBA Oracle é criar uma cópia do ambiente de produção para um ambiente de desenvolvimento e/ou testes. Porém, um problema encontrado é a falta de espaço para realização de um clone. Em algumas situações, os DBAs passam a utilizar o Oracle Partitioning e organizam os dados separados por intervalo de tempo. Com isso, ao realizar um clone, é possível escolher somente as tablespaces necessárias. O problema desta abordagem, é que as vezes, existe a necessidade de trabalhar com uma amostra de todos os dados da produção e não apenas um intervalo de tempo. A solução para este problema, é o que iremos abordar neste artigo.

Esta tarefa é dividida em duas partes:

Extrair um percentual dos dados de produção.
Importar os dados, mas criando os datafiles no tamanho necessário para armazenar estes dados da produção.

A primeira parte pode ser facilmente resolvida usando o parâmetro SAMPLE do utilitário DATA PUMP. A segunda parte também será resolvida usando o parâmetro PCTSPACE também do utilitário DATA PUMP. Vamos definir as duas cláusulas:

SAMPLE:X – Extrai uma amostragem de x% dos dados, isto é, extrai apenas uma porcentagem dos dados de produção para cada objeto do banco de dados de origem. O percentual da amostra indica apenas a probabilidade de que um bloco será selecionado pelo expdp. Você pode indicar o percentual que você quer, mas não pode ser 0. Você deve indicar um valor entre [0-100]. Se nenhuma tabela for especificada com o parâmetro SAMPLE, então o percentual será aplicado a toda a operação de exportação. PCTSPACE:X – reduz o initial extent em X%. A opção PCTSPACE do parâmetro TRANSFORM é utilizada para reduzir as tablespaces e também seus datafiles. Enquanto o import estiver criando os extents, automaticamente ele irá reduzir cada extent no percentual especificado.

Agora, para mostrar como isto funciona, vamos criar um ambiente de desenvolvimento com base em um percentual do nosso ambiente de produção.

Criando um ambiente de testes com 10% do ambiente de produção

Ambiente de Produção No nosso ambiente de produção, possuímos 2 tabelas, T1 e T2 com 96MB de tamanho e aproximadamente 900.000 registros, pertencentes ao schema OraWorld.

Verificando o tamanho das tabelas:


  
Verificando o tamanho das tabelas:

SQL> select segment_name, 
            segment_type, 
            tablespace_name, 
            bytes/1024/1024 MB_Size 
     from dba_segments 
     where owner='ORAWORLD';
 
SEGMENT_NA SEGMENT_TYPE       TABLESPACE    MB_SIZE
---------- ------------------ ----------   --------
T1         TABLE              DATA1              96
T2         TABLE              DATA2              96
IND_T1     INDEX              INDEX1             15
IND_T2     INDEX              INDEX2             15
 

Verificando o número de registros:

SQL> select table_name, 
            num_rows 
     from dba_tables 
     where owner='ORAWORLD';
 
TABLE_NAME                     NUM_ROWS
------------------------------ ------------
T2                             881000
T1                             881000

Verificando o número de registros:


  
SQL> select table_name, 
            num_rows 
     from dba_tables 
     where owner='ORAWORLD';
 
TABLE_NAME                     NUM_ROWS
------------------------------ ------------
T2                             881000
T1                             881000

Verificando os DataFiles:


  
SQL> select file_name, 
            bytes/1024/1024 MB_Size 
     from dba_data_files 
     where tablespace_name in ('DATA1','DATA2','INDEX1','INDEX2');
 
FILE_NAME                                   MB_SIZE
----------------------------------------  ---------
+DATA/orcl/datafile/data1.342.860024365         150
+DATA/orcl/datafile/data2.265.860024375         150
+DATA/orcl/datafile/index1.353.860024387        100
+DATA/orcl/datafile/index2.354.860024397        100

Verificando a localização do spfile de produção, que será utilizado para criar o ambiente de desenvolvimento:


  
SQL> select name from v$database;
 
NAME
---------
ORCL
SQL> show parameter spfile
NAME      TYPE         VALUE
-------   -----------  -------------------------------------
spfile    string       /u01/app/oracle/product/11.2/db_1/dbs/spfileorcl.ora

Copiando o spfile para o ambiente de desenvolvimento:


  
[oracle@prod ~]$ scp /u01/app/oracle/product/11.2/db_1/dbs/spfileorcl.ora oracle@dev.oraworld.com:
/u01/app/oracle/product/11.2/db_1/dbs/
oracle@dev.oraworld.com's password: 
spfileorcl.ora                     100% 3584     3.5KB/s   00:00:01

Ambiente de Desenvolvimento


  
[oracle@dev dbs]$ pwd
/u01/app/oracle/product/11.2/db_1/dbs
 
[oracle@dev dbs]$ ls -ltr spfileorcl.ora 
-rw-r----- 1 oracle oinstall 3584 Oct 31 22:55 spfileorcl.ora
 
[oracle@dev dbs]$ echo $ORACLE_SID
orcl

Criando o ambiente de desenvolvimento:


  
[oracle@dev dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 31 22:57:20 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area  1068937216 bytes
Fixed Size                   2260088 bytes
Variable Size              578814856 bytes
Database Buffers           482344960 bytes
Redo Buffers                 5517312 bytes
 
SQL>

Verificando os parâmetros:


  
SQL> show parameters db_create
 
NAME                                  TYPE       VALUE
------------------------------------  ---------- ----------------------------
db_create_file_dest                   string     +DATA
db_create_online_log_dest_1           string     +DATA
db_create_online_log_dest_2           string
db_create_online_log_dest_3           string
db_create_online_log_dest_4           string
db_create_online_log_dest_5           string

Ajustando os parâmetros de localização dos control files:


  
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
 
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
 
SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area  1068937216 bytes
Fixed Size                   2260088 bytes
Variable Size              587203464 bytes
Database Buffers           473956352 bytes
Redo Buffers                 5517312 bytes
SQL>

Criando o banco de dados do ambiente de desenvolvimento:


  
SQL> CREATE DATABASE orcl
USER SYS IDENTIFIED BY manager1
USER SYSTEM IDENTIFIED BY manager1
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs1
DEFAULT TABLESPACE users;
Database created.

Execução dos scripts de criação do dicionário do banco de dados:


  
SQL> show user
USER is "SYS"
 
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
 
SQL> conn system/manager1
Connected.
 
SQL> show user
USER is "SYSTEM"
 
SQL> @?/sqlplus/admin/pupbld.sql
 
SQL> create directory oracle as '/home/oracle';
Directory created.
 
SQL> create user oraworld identified by oraworld;
User created.
 
SQL> grant imp_full_database to oraworld;
Grant succeeded.
 
SQL> grant connect, resource to oraworld;
Grant succeeded.
 
SQL> grant read,write on directory oracle to oraworld;
Grant succeeded.
 
SQL> alter user oraworld quota unlimited on users;
User altered.

Especificando o percentual de registros que devem ser descarregados, a partir do banco de dados de origem usando o utilitário Data Pump. Como podemos observar, 8.180 são 10% dos registros da tabela, que possui atualmente 881.000 registros.


  
[oracle@prod ~]$ expdp oraworld/oraworld sample=10 full=y directory=oracle 
dumpfile=expdp_10pct.dmp log=expdp_10pct.log
Processing object type DATABASE_EXPORT/AUDIT
. . exported "ORAWORLD"."T1"                             8.180 MB   87693 rows 
. . exported "ORAWORLD"."T2"                             8.242 MB   88344 rows 
 
[oracle@prod ~]$ scp expdp_10pct.dmp oracle@dev.oraworld.com:/home/oracle
oracle@dev.oraworld.com's password: 
 
expdp_10pct.dmp                                          100%   20MB  20.0MB/s   00:01

Importando os dados de produção para o desenvolvimento e modificando o initial size: Aqui é onde a “mágica” acontece, utilizando um valor de 10 em pctspace, os nossos datafiles e todas as suas extensões vão ser criados com 10% do tamanho original.


  
[oracle@dev ~]$  impdp oraworld/oraworld transform=pctspace:10 directory=oracle 
dumpfile=expdp_10pct.dmp log=impdp_10pct.log TABLE_EXISTS_ACTION=SKIP
 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "ORAWORLD"."T1"                             8.180 MB   87693 rows 
. . imported "ORAWORLD"."T2"                             8.242 MB   88344 rows

O parâmetro SAMPLE não especifica uma quantidade exata de dados, mas apenas uma quantidade provável de dados que serão extraídos das tabelas.

Atualizando as estatísticas do schema OraWorld:


  
SQL> exec dbms_stats.gather_schema_stats('ORAWORLD');
PL/SQL procedure successfully completed.

Verificando se os dados estão próximos de 10% do tamanho original:


  
SQL> select segment_name, 
            segment_type, 
            tablespace_name, 
            bytes/1024/1024 MB_Size 
     from dba_segments 
     where owner='ORAWORLD';
 
SEGMENT_NAME SEGMENT_TYPE       TABLESPACE     MB_SIZE
------------ ------------------ ----------   ---------
T1           TABLE              DATA1               10 
T2           TABLE              DATA2               10 
IND_T1       INDEX              INDEX1              2 
IND_T2       INDEX              INDEX2              2 

SQL> select table_name, num_rows from dba_tables where owner='ORAWORLD';
 
TABLE_NAME                       NUM_ROWS
------------------------------   ----------
T2                               88344 
T1                               87693

Como podemos observar ,as estruturas físicas foram criadas com 10% do tamanho do ambiente de produção:


  
SQL> select file_name, 
            bytes/1024/1024 MB_Size 
     from dba_data_files 
     where tablespace_name in ('DATA1','DATA2','INDEX1','INDEX2');
 
FILE_NAME                                     MB_SIZE
----------------------------------------   ----------
+DATA/orcl/datafile/data1.281.862448977            15 
+DATA/orcl/datafile/data2.282.862448977            15 
+DATA/orcl/datafile/index1.279.862448979           10 
+DATA/orcl/datafile/index2.280.862448979           10

Um problema muito comum

Um problema muito comum que pode ocorrer, enquanto você estiver criando o seu novo ambiente, é que, em algumas vezes, os seus datafiles não vão sendo criados no percentual de tamanho especificado no impdp. Isso acontece quando você executou diversos "alter database datafile … resize", e essas sentenças não são replicadas no arquivo gerado pelo expdp.

Neste caso, devemos modificar o valor do maxsize do datafile ou podemos criar previamente todos os tablespaces no novo ambiente. Vamos verificar com mais detalhes no exemplo a seguir:

Ambiente de Produção


  
SQL> select file_name, bytes/1024/1024 MB_Size from dba_data_files where tablespace_name in 
('DATA1','DATA2','INDEX1','INDEX2');
FILE_NAME                                             MB_SIZE
-------------------------------------------------  ----------
+DATA/orcl/datafile/data1.342.860024365                   150 
+DATA/orcl/datafile/data2.265.860024375                   150
+DATA/orcl/datafile/index1.353.860024387                  100
+DATA/orcl/datafile/index2.354.860024397                  100

SQL> alter database datafile '+DATA/orcl/datafile/data1.342.860024365' resize 200M;
Database altered.

[oracle@prod ~]$ expdp oraworld/oraworld sample=10 full=y directory=oracle 
dumpfile=expdp_10pct.dmp log=expdp_10pct.log

Processing object type DATABASE_EXPORT/AUDIT
. . exported "ORAWORLD"."T1"                             8.162 MB   87515 rows
. . exported "ORAWORLD"."T2"                             8.183 MB   87745 rows

[oracle@prod ~]$  scp expdp_10pct.dmp oracle@dev.oraworld.com:/home/oracle
oracle@dev.oraworld.com's password: 

expdp_10pct.dmp                                          100%   20MB  20.0MB/s   00:01

Ambiente de Desenvolvimento


  
[oracle@dev ~]$  impdp oraworld/oraworld transform=pctspace:10 directory=oracle 
dumpfile=expdp_10pct.dmp log=impdp_10pct.log sqlfile=sqlfile.txt
[oracle@dev ~]$ cat sqlfile.txt |grep -A 5 DATA1
CREATE TABLESPACE "DATA1" DATAFILE 
  SIZE 15728640 
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
  NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Por que 15728640 bytes (15M)? Em produção temos 200M, pois fizemos um rezise.

Infelizmente, o export não inclui o "ALTER DATABASE DATAFILE ... RESIZE" proveniente das operações realizadas após a criação de tabela.

Se dermos uma olhada no DDL da tabela 'DATA1' podemos ver o seguinte:


  
SQL> select dbms_metadata.get_ddl('TABLESPACE','DATA1') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','DATA1')
-----------------------------------------------------------------------------
  CREATE TABLESPACE "DATA1" DATAFILE
  SIZE 157286400
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
  NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
  ALTER DATABASE DATAFILE
  '+DATA/orcl/datafile/data1.342.860024365' RESIZE 209715200

O "ALTER DATABASE DATAFILE .. RESIZE" não está incluso no expdp:


  
SQL> select file_name, maxbytes from dba_data_files where tablespace_name in ('DATA1','DATA2','INDEX1','INDEX2');

FILE_NAME                                   MAXBYTES
----------------------------------------  ----------
+DATA/orcl/datafile/data1.342.860024365            0
+DATA/orcl/datafile/data2.265.860024375            0
+DATA/orcl/datafile/index1.353.860024387           0
+DATA/orcl/datafile/index2.354.860024397           0

Essa é a razão do porque vimos o tamanho de 15MB, como sendo 10% do tablespace DATA1, quando o valor correto deveria ser 20MB, pois o tamanho atual do arquivo de dados é de 200MB. Este comportamento é o mesmo para todos os datafiles que foram redimensionados durante a vida do banco de dados.

Como corrigir isso?

Opção 1:

Criar previamente a tablespace no banco de dados de destino e depois executar o Data Pump. Você pode usar a seguinte query para gerar o DDL de cada tablespace.


  
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)  
  from dba_tablespaces;

Opção 2:

No banco de origem, aumentar o valor do MAXSIZE da cláusula AUTOEXTEND do datafile corrente. Podemos utilizar a seguinte query:


  
select 'ALTER DATABASE DATAFILE '''||file_name||''' 
AUTOEXTEND ON MAXSIZE '|| bytes||';' from dba_data_files where maxbytes < bytes;

Fazer o export novamente utilizando o data pump.

Recriar o banco de destino usando o arquivo de export gerado no passo 2. Para isso deve-se utilizar o impdp do utilitário data pump.

Referências:

How To Export Only a Percentage Of Data In Tables Using The Datapump SAMPLE Parameter (Doc ID 1422064.1)
How To Specify A Percentage Of Data To Be Exported Using DataPump Export (Doc ID 1385364.1)
"ORA-02494: invalid or missing maximum file size in MAXSIZE clause" during IMPDP (Doc ID 1670695.1)
Oracle Database 12c Backup and Recovery Survival Guide - Franciscy Muñoz Alvarez

Deiby Gomez é um DBA com vasta experiência em Oracle Exadata Database Machine, soluções de Alta Disponibilidade e trabalha atualmente para a Pythian. Deiby frequentemente ministra palestras em importantes eventos Oracle na Guatemala tais como "Oracle Technology Network Tour", "Java Day", "Primeiro Simpósio de Oracle" e em varias Universidades de seu país de residência (Guatemala). Deiby é o primeiro Oracle ACE da Guatemala. É "Oracle RAC 11g and Grid Infraestructure Administrator", "Exadata Database Machine X3 Administrator", "Oracle Linux Certified Implementation Specialist" e "Oracle SOA Implementation Certified Expert".

Alex Zaballa, formado em Análise de Sistemas, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Servidores de Aplicação e Sistemas Operacionais; trabalha com Oracle há 14 anos, é ORACLE ACE, certificado OCM Database 11G e conta com mais de 100 outras certificações em produtos da Oracle. Desde 2007 é funcionário da empresa Júpiter em Angola, alocado em um projeto no Ministério das Finanças. Alex também é fundador do Grupo de Usuários Oracle de Angola (GUOA) e membro do time OraWorld.

O OraWorld conta com membros "Oracle Certified Masters" e "Oracle ACEs". Você pode contatar este grupo através dos seguintes endereços:

www.oraworld-team.com

facebook.com/oraworldteam

twitter.com/oraworld_team

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.