Otimizando uma consulta sem alterar o código fonte

Objetivo:

Estamos realizando um trabalho para melhorar o desempenho de uma aplicação e identificamos que existe uma consulta que é a maior responsável pelo baixo desempenho desta, analisando a consulta verificamos que é possivel melhorar a performace, mas para isso precisamos alterar o código da aplicação. Essa aplicação é um pacote de um fornecedor e não temos acesso ao código fonte, uma possível solução para esse impasse seria a utilização do pacote “DBMS_ADVANCED_REWRITE” que permite a alteração de uma consulta sem alteração do código fonte da aplicação.

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado

Overview:

O pacote DBMS_ADVANCED_REWRITE permite interceptar instruções SQL específicas e substituí-las com instruções SQL alternativa. Este recurso pode ser muito útil quando precisamos modificar uma instrução SQL visando melhoria de performace e não dispomos do código fonte da aplicação. Infelizmente este recurso tem algumas limitações e em alguns casos não podemos utilizá-lo:

• Recurso disponível a partir da versão Oracle 10g;
• Não funciona com instrução DML, somente com instrução SELECT;
• Não funciona com “Bind Variables” (Metalink DOC ID: 392214.1);

Tópicos:

1. Previlégios necessários para o usuário;
2. Criar uma tabela de teste;
3. Plano de execução da consulta original;
4. Plano de execução da consulta otimizada;
5. Utilizar o pacote para estabelecer a equivalência;
6. Verificar se equivalência esta funcionando;

1. Previlégios necessários para o usuário

SQL>
SQL> conn sys/oracle@lab1 as sysdba;
Conectado.
SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO appl02;

Concessão bem-sucedida.

SQL> GRANT CREATE MATERIALIZED VIEW TO appl02;

Concessão bem-sucedida.

SQL> 

2. Criar uma tabela de teste

SQL>
SQL> conn appl02/oracle@lab1;
Conectado.
SQL> create table db_objetos as select * from all_objects;

Tabela criada.

SQL> create index db_obj_idx on db_objetos(OBJECT_NAME);

Índice criado.

SQL>

3. Plano de execução da consulta original

Agora vamos verificar o plano de execução de uma consulta que supostamente estaria causando grande impacto de performace na base de dados:

SQL>
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE 
       from db_objetos 
      where upper(OBJECT_NAME) = upper('all_objects');

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -----------------
SYS                            ALL_OBJECTS                    VIEW
PUBLIC                         ALL_OBJECTS                    SYNONYM

SQL> Explain plan for
  2  select OWNER, OBJECT_NAME, OBJECT_TYPE 
  3    from db_objetos 
  4   where upper(OBJECT_NAME) = upper('all_objects');

Explicado.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 440180848

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     6 |   270 |   133   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| DB_OBJETOS |     6 |   270 |   133   (3)| 00:00:02 |
--------------------------------------------------------------------------------

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

   1 - filter(UPPER("OBJECT_NAME")='ALL_OBJECTS')

Note
-----
   - dynamic sampling used for this statement

17 linhas selecionadas.
SQL>

4. Plano de execução da consulta otimizada

Vamos supor que após uma analise na tabela “DB_OBJETOS” verificamos que o campo “OBJECT_NAME” possui somente textos com caracteres maiusculos, neste caso a consulta abaixo forneceria o mesmo resultado e utilizaria o indice desse campo, melhorando a performace da consulta:

SQL>
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE 
       from db_objetos 
      where OBJECT_NAME = upper('all_objects');

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS                            ALL_OBJECTS                    VIEW
PUBLIC                         ALL_OBJECTS                    SYNONYM

SQL> Explain plan for
  2  select OWNER, OBJECT_NAME, OBJECT_TYPE 
  3    from db_objetos 
  4   where OBJECT_NAME = upper('all_objects');

Explicado.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1912010479

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |    90 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DB_OBJETOS |     2 |    90 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DB_OBJ_IDX |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='ALL_OBJECTS')

Note
-----
   - dynamic sampling used for this statement

18 linhas selecionadas.
SQL> 

5. Utilizar o pacote para estabelecer a equivalência

Agora vamos utilizar o pacote DBMS_ADVANCED_REWRITE para estabelecer a equivalência de forma que sempre que a consulta original for acionada a base Oracle utilize a consulta otimizada para execução:

SQL>
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Sessão alterada.

SQL> BEGIN
  2     sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
  3     name               => 'test_rw1',
  4     source_stmt   =>
  5     'select OWNER, OBJECT_NAME, OBJECT_TYPE
  6     from db_objetos
  7     where upper(OBJECT_NAME) = upper(''all_objects'')',
  8     destination_stmt  =>
  9     'select OWNER, OBJECT_NAME, OBJECT_TYPE
 10     from db_objetos
 11     where OBJECT_NAME = upper(''all_objects'')',
 12     validate    => false,
 13     rewrite_mode       => 'text_match');
 14  END;
 15  /

Procedimento PL/SQL concluído com sucesso.


SQL>

6. Verificar se equivalência esta funcionando

Na execução abaixo podemos constatar que apesar de acionar-mos a consulta original o plano de execução utilizado pela base Oracle é da consulta otimizada:

SQL>
SQL> Explain plan for
  2  select OWNER, OBJECT_NAME, OBJECT_TYPE
  3     from db_objetos 
  4    where upper(OBJECT_NAME) = upper('all_objects');

Explicado.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1912010479

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     2 |    90 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DB_OBJETOS |     2 |    90 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DB_OBJ_IDX |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='ALL_OBJECTS')

Note
-----
   - dynamic sampling used for this statement

18 linhas selecionadas.
SQL>

Criando uma base reduzida para desenvolvimento

Objetivo:

Precisamos criar uma base para área de desenvolvimento realizar testes de uma aplicação e não temos espaço suficiente para clonar a base de produção, neste caso podemos utilizar um recurso do DataPump que permite copiar um percentual de dados da base.

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado


Overview:

Nessa simulação vamos utilizar um esquema para demonstrar como podemos criar uma base de teste menor que a base de produção. O esquema utilizado será o SH que é um esquema exemplo criado opcionamento durante a instalação do banco de dados.

Vamos utilizar a mesma base para fazer export e o import o que não inviabiliza a demonstração uma vez que o objetivo é demonstrar que podemos copiar todos os objetos de um esquema reduzindo a quantidade de espaço utilizada por eles.

Tópicos:

1. Criar usuário de teste;
2. Export esquema SH;
3. Import esquema SH_TESTE;
4. Comparar objetos dos esquemas;
5. Comparar tamanhos dos esquemas;

1. Criar usuário de teste

SQL> create user sh_teste identified by oracle
     default tablespace appl02 temporary tablespace temp;

Usuário criado.

SQL> grant connect to sh_teste;

Concessão bem-sucedida.

SQL> grant unlimited tablespace to sh_teste;

Concessão bem-sucedida.

SQL> spool off;

2. Export esquema SH

Primeiro vamos executar um EXPORT do esquema SH usando o parâmetro "SAMPLE", este parâmetro especifica um percentual de registros a serem copiados da base, neste caso vamos usar 30%:

[oracle@odbsrv02 ~]$ expdp \"/ as sysdba\" directory=DATA_PUMP_DIR sample=30
schemas=SH  dumpfile=SH_esquema.dmp  logfile=SH_esquema.log

Export: Release 10.2.0.5.0 - Production on Wednesday, 16 June, 2010 2:30:03

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR
sample=30 schemas=SH dumpfile=SH_esquema.dmp logfile=SH_esquema.log

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 35.31 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
. . exported "SH"."CUSTOMERS"                            3.004 MB   16875 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS"           212.3 KB    1332 rows
. . exported "SH"."SALES":"SALES_Q4_2001"                701.6 KB   21029 rows
. . exported "SH"."SALES":"SALES_Q1_1999"                638.7 KB   19186 rows
. . exported "SH"."SALES":"SALES_Q3_2001"                659.5 KB   19746 rows
. . exported "SH"."SALES":"SALES_Q1_2000"                615.4 KB   18435 rows
. . exported "SH"."SALES":"SALES_Q1_2001"                613.8 KB   18351 rows
. . exported "SH"."SALES":"SALES_Q2_2001"                636.1 KB   19029 rows
. . exported "SH"."SALES":"SALES_Q3_1999"                672.3 KB   20208 rows
. . exported "SH"."SALES":"SALES_Q4_1999"                621.0 KB   18637 rows
. . exported "SH"."SALES":"SALES_Q2_2000"                557.8 KB   16645 rows
. . exported "SH"."SALES":"SALES_Q3_2000"                589.0 KB   17617 rows
. . exported "SH"."SALES":"SALES_Q4_1998"                482.4 KB   14421 rows
. . exported "SH"."SALES":"SALES_Q4_2000"                560.1 KB   16745 rows
. . exported "SH"."SALES":"SALES_Q2_1999"                546.6 KB   16363 rows
. . exported "SH"."SALES":"SALES_Q1_1998"                442.2 KB   13218 rows
. . exported "SH"."SALES":"SALES_Q3_1998"                512.7 KB   15349 rows
. . exported "SH"."SALES":"SALES_Q2_1998"                359.0 KB   10669 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV"                 131.1 KB    3406 rows
. . exported "SH"."PROMOTIONS"                           24.66 KB     167 rows
. . exported "SH"."TIMES"                                130.2 KB     574 rows
. . exported "SH"."COSTS":"COSTS_Q4_2001"                87.75 KB    2697 rows
. . exported "SH"."COSTS":"COSTS_Q1_1999"                59.41 KB    1762 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001"                72.94 KB    2203 rows
. . exported "SH"."COSTS":"COSTS_Q2_2001"                60.59 KB    1793 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001"                74.60 KB    2259 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998"                47.25 KB    1356 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000"                41.74 KB    1169 rows
. . exported "SH"."COSTS":"COSTS_Q2_1998"                27.42 KB     692 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999"                44.67 KB    1269 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000"                39.98 KB    1110 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998"                44.03 KB    1250 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999"                47.19 KB    1353 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000"                48.67 KB    1399 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998"                47.65 KB    1369 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999"                53.85 KB    1576 rows
. . exported "SH"."COSTS":"COSTS_Q4_2000"                52.44 KB    1525 rows
. . exported "SH"."CAL_MONTH_SALES_MV"                   5.476 KB      13 rows
. . exported "SH"."CHANNELS"                             6.515 KB       1 rows
. . exported "SH"."COUNTRIES"                            8.226 KB       7 rows
. . exported "SH"."PRODUCTS"                             15.93 KB      24 rows
. . exported "SH"."COSTS":"COSTS_1995"                       0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_1996"                       0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_H1_1997"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_H2_1997"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2003"                    0 KB       0 rows
. . exported "SH"."MVIEW$_EXCEPTIONS"                        0 KB       0 rows
. . exported "SH"."SALES":"SALES_1995"                       0 KB       0 rows
. . exported "SH"."SALES":"SALES_1996"                       0 KB       0 rows
. . exported "SH"."SALES":"SALES_H1_1997"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_H2_1997"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q1_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q1_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q2_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q2_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q3_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q3_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q4_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q4_2003"                    0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/10.2.0/db_1/admin/lab1/dpdump/SH_esquema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 02:32:02

[oracle@odbsrv02 ~]$

3. Import esquema SH_TESTE

Em seguida vamos importar os objetos do esquema SH no esquema SH_TESTE dizendo ao Data Pump para reduzir o tamanho das extensões para 30%:

[oracle@odbsrv02 ~]$ impdp \"/ as sysdba\"  transform=pctspace:30  remap_schema=SH:SH_TESTE
directory=DATA_PUMP_DIR   dumpfile=SH_esquema.dmp  logfile=imp_SH_TESTE_esquema.log

Import: Release 10.2.0.5.0 - Production on Wednesday, 16 June, 2010 2:47:58

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" transform=pctspace:70 remap_schema=SH:SH_TESTE
directory=DATA_PUMP_DIR dumpfile=SH_esquema.dmp logfile=imp_SH_TESTE_esquema.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SH_TESTE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SH_TESTE"."CUSTOMERS"                      3.004 MB   16875 rows
. . imported "SH_TESTE"."SUPPLEMENTARY_DEMOGRAPHICS"     212.3 KB    1332 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_2001"          701.6 KB   21029 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_1999"          638.7 KB   19186 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_2001"          659.5 KB   19746 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_2000"          615.4 KB   18435 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_2001"          613.8 KB   18351 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_2001"          636.1 KB   19029 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_1999"          672.3 KB   20208 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_1999"          621.0 KB   18637 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_2000"          557.8 KB   16645 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_2000"          589.0 KB   17617 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_1998"          482.4 KB   14421 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_2000"          560.1 KB   16745 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_1999"          546.6 KB   16363 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_1998"          442.2 KB   13218 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_1998"          512.7 KB   15349 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_1998"          359.0 KB   10669 rows
. . imported "SH_TESTE"."FWEEK_PSCAT_SALES_MV"           131.1 KB    3406 rows
. . imported "SH_TESTE"."PROMOTIONS"                     24.66 KB     167 rows
. . imported "SH_TESTE"."TIMES"                          130.2 KB     574 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_2001"          87.75 KB    2697 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_1999"          59.41 KB    1762 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_2001"          72.94 KB    2203 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_2001"          60.59 KB    1793 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_2001"          74.60 KB    2259 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_1998"          47.25 KB    1356 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_2000"          41.74 KB    1169 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_1998"          27.42 KB     692 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_1999"          44.67 KB    1269 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_2000"          39.98 KB    1110 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_1998"          44.03 KB    1250 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_1999"          47.19 KB    1353 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_2000"          48.67 KB    1399 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_1998"          47.65 KB    1369 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_1999"          53.85 KB    1576 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_2000"          52.44 KB    1525 rows
. . imported "SH_TESTE"."CAL_MONTH_SALES_MV"             5.476 KB      13 rows
. . imported "SH_TESTE"."CHANNELS"                       6.515 KB       1 rows
. . imported "SH_TESTE"."COUNTRIES"                      8.226 KB       7 rows
. . imported "SH_TESTE"."PRODUCTS"                       15.93 KB      24 rows
. . imported "SH_TESTE"."COSTS":"COSTS_1995"                 0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_1996"                 0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_H1_1997"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_H2_1997"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_2002"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q1_2003"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_2002"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q2_2003"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_2002"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q3_2003"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_2002"              0 KB       0 rows
. . imported "SH_TESTE"."COSTS":"COSTS_Q4_2003"              0 KB       0 rows
. . imported "SH_TESTE"."MVIEW$_EXCEPTIONS"                  0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_1995"                 0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_1996"                 0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_H1_1997"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_H2_1997"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_2002"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q1_2003"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_2002"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q2_2003"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_2002"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q3_2003"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_2002"              0 KB       0 rows
. . imported "SH_TESTE"."SALES":"SALES_Q4_2003"              0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 02:49:15

[oracle@odbsrv02 ~]$

4. Comparar objetos dos esquemas

Concluida a copia precisamos verificar se todos os objetos foram copiados:

SQL> select owner, object_type, count(1) Qtde
  2    from dba_objects
  3   where owner in ('SH','SH_TESTE')
  4   group by owner, object_type
  5   order by 2;

OWNER                          OBJECT_TYPE               QTDE
------------------------------ ------------------- ----------
SH                             DIMENSION                    5
SH_TESTE                       DIMENSION                    5
SH_TESTE                       INDEX                       28
SH                             INDEX                       28
SH_TESTE                       INDEX PARTITION            168
SH                             INDEX PARTITION            168
SH_TESTE                       LOB                          2
SH                             LOB                          2
SH                             MATERIALIZED VIEW            2
SH_TESTE                       MATERIALIZED VIEW            2
SH_TESTE                       TABLE                       17
SH                             TABLE                       17
SH_TESTE                       TABLE PARTITION             56
SH                             TABLE PARTITION             56
SH                             VIEW                         1
SH_TESTE                       VIEW                         1

16 linhas selecionadas.

SQL>

5. Comparar tamanhos dos esquemas

Agora vamos verificar se o DataPump fez o trabalho dele corretamente, vamos comparar a quantidade de espaço utilizada pelo esquema SH e SH_TESTE:

SQL> select owner, sum(bytes)/1024 "Tamanho KB"
  2    from dba_segments
  3   where owner in ('SH','SH_TESTE')
  4   group by owner;

OWNER                          Tamanho KB
------------------------------ ----------
SH_TESTE                            35712
SH                                  81984

SQL>

Obs: A redução do espaço utilizado pelo esquema não é um calculo exato devido

a forma como o Oracle trabalha com segmentos de dados e extensões.

Reorganizando uma tabela sem parar as atividades dos usuários

Objetivo:

Precisamos reorganizar uma tabela que é acessada durante 24 horas por dia e sete dias na semana, nessa situação uma alternativa seria utilizar a package Online Redefinition (DBMS_REDEFINITION).

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado


Overview:

Oracle Online Redefinition permite alterações na estrutura das tabelas enquanto os usuários estão lendo e gravando nessas tabelas. Assegurando assim a continuidade do serviço sem impacto nas atividades dos usuários finais, não há restrição quanto tempo necessário para reorganização da tabela. Quando a reorganização estiver concluída, a mudança para a nova tabela reorganizada é automática e completamente transparente para os usuários da aplicação.


Tópicos:

1. Setup;
2. Verificar se a tabela pode ser reorganizada online;
3. Verificar tamanho da tabela;
4. Criar a tabela provisória;
5. Iniciar a reorganização;
6. Simular uma modificação do usuário;
7. Copiar objetos dependentes da tabela;
8. Simular commit da transação do usuário;
9. Sincronizar tabela provisória com a original;
10. Disponibilizar a tabela provisória como original;
11. Verificar se a tabela foi reorganizada;

1. Setup

SQL> create tablespace appl01 datafile '/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/appl01.dbf' size 100 M;

Tablespace criado.

SQL> create user appl01 identified by oracle default tablespace appl01 temporary tablespace temp;

Usuário criado.

SQL> grant connect to appl01;

Concessão bem-sucedida.

SQL> grant unlimited tablespace to appl01;

Concessão bem-sucedida.

SQL> grant select any dictionary to appl01;

Concessão bem-sucedida.

SQL> grant resource to appl01;

Concessão bem-sucedida.

SQL> conn appl01/oracle@lab1;
Conectado.

SQL>
SQL> drop table TEST_REORG CASCADE CONSTRAINTS PURGE;

Tabela eliminada.

SQL> create table test_reorg
  2  (  regid                number         not null
  3    ,nome                 varchar2(30)   not null
  4    ,endereco             varchar2(30)   not null
  5    ,data                 date           not null
  6    ,comentario           varchar2(100)
  7    ,constraint test_reorg_pk primary key (regid)
  8  );

Tabela criada.

SQL>
SQL>
SQL> create index test_reorg_idx on test_reorg(nome);

Índice criado.

SQL> select object_name, object_type from dba_objects where owner = 'APPL01';

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
DB_DICT              TABLE
TEST_REORG           TABLE
TEST_REORG_PK        INDEX
TEST_REORG_IDX       INDEX
DB_OBJETOS           TABLE

SQL> declare
  2    id number;
  3    x  varchar2(30);
  4    y  varchar2(30);
  5    c  varchar2(100);
  6  begin
  7    x := '123456789+123456789+1234567890';
  8    y := 'Abcde fghi jklmn opqrs tuvxz99';
  9    id := 1;
 10    c := 'black Abcde fghi jklmn opqrs tuvxz99 123456789+123456789+1234567890 black Abcde fghi jklmn opqrs';
 11
 12    for i in 1..100000 loop
 13
 14       insert into test_reorg values (id, x, y, sysdate, c);
 15       id := id + 1;
 16
 17    end loop;
 18  end;
 19  /

Procedimento PL/SQL concluído com sucesso.

SQL>
SQL> commit;

Validação completa.

SQL>
SQL>
SQL> delete from test_reorg where regid > 5000 and regid <> commit;

Validação completa.

SQL> delete from test_reorg where regid > 25000 and regid <> commit;

Validação completa.

SQL> delete from test_reorg where regid > 45000 and regid <> commit;

Validação completa.

SQL> delete from test_reorg where regid > 75000 and regid <> commit;

Validação completa.

SQL>
SQL>

2. Verificar se a tabela pode ser reorganizada online

Para reorganizar uma tabela, precisamos verificar se a mesma é candidata a uma redefinição online:

SQL> conn system/oracle@lab1;
Conectado.
SQL> BEGIN
  2  DBMS_REDEFINITION.CAN_REDEF_TABLE('APPL01',' TEST_REORG',dbms_redefinition.cons_use_pk);
  3  END;
  4  /

Procedimento PL/SQL concluído com sucesso.

SQL>
SQL>

3. Verificar tamanho da tabela

Vamos verificar o tamanho da tabela antes da reorganização para comparar com o tamanho após a reorganização:

SQL> select segment_name, owner, bytes/1024 "Tam. KB" from dba_segments where segment_name  = 'TEST_REORG';

SEGMENT_NAME         OWNER           Tam. KB
-------------------- ------------ ----------
TEST_REORG           APPL01            20480

SQL>

4. Criar a tabela provisória

SQL> conn appl01/oracle@lab1;
Conectado.
SQL> create table test_reorg_prov
  2  (  regid                number
  3    ,nome                 varchar2(30)
  4    ,endereco             varchar2(30)
  5    ,data                 date
  6    ,comentario           varchar2(100)
  7  );

Tabela criada.

SQL>

5. Iniciar a reorganização

Agora vamos iniciar o processo de reorganização e definir os mapeamentos de coluna para as colunas antigas e novas:


SQL> conn system/oracle@lab1;

Conectado.

SQL> BEGIN

2 DBMS_REDEFINITION.START_REDEF_TABLE('APPL01', 'TEST_REORG','TEST_REORG_PROV',
3 'regid regid, nome nome, endereco endereco, data data, comentario comentario' ,
4 dbms_redefinition.cons_use_pk);
5 END;
6 /

Procedimento PL/SQL concluído com sucesso.

SQL>



6. Simular uma modificação do usuário

Enquanto a reorganização está executando vamos iniciar outra sessão onde faremos alterações na tabela TEST_REORG:

SQL> conn appl01/oracle@lab1;
Conectado.
SQL> UPDATE TEST_REORG set endereco = 'valteraquino.blogspot.com' where regid=20000;

1 linha atualizada.

SQL>

Obs: Não execute o commit nesse momento.

7. Copiar objetos dependentes da tabela

Em seguida retornando para a primeira sessão, vamos criar automaticamente todos os triggers, índices e constraints na tabela provisória:

SQL> set serveroutput on
SQL> DECLARE
  2  num_erros int;
  3  BEGIN
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
  5  ('APPL01', 'TEST_REORG','TEST_REORG_PROV',1, TRUE, TRUE, TRUE, FALSE, num_erros);
  6  DBMS_OUTPUT.put_line ('Numero de erros durante a copia do obj.dependentes: '  num_erros );
  7  END;
  8  /
Numero de erros durante a copia do obj.dependentes: 0

Procedimento PL/SQL concluído com sucesso.

SQL>

8. Simular commit da transação do usuário

Agora vamos retornar para a sessão onde executamos o UPDATE na tabela e vamos confirmar a mudança de endereço e fechar a sessão:

SQL> commit;

Validação completa.

SQL> exit

9. Sincronizar tabela provisória com a original

Agora podemos sincronizar a tabela provisória para que ela inclua a alteração no endereço que fizemos:

SQL> BEGIN
  2  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('APPL01', 'TEST_REORG', 'TEST_REORG_PROV');
  3  END;
  4  /

Procedimento PL/SQL concluído com sucesso.

SQL>

10. Disponibilizar a tabela provisória como original

Agora vamos encerrar a reorganização e as tabelas serão invertidas automaticamente:

SQL> BEGIN
  2  DBMS_REDEFINITION.FINISH_REDEF_TABLE('APPL01', 'TEST_REORG', 'TEST_REORG_PROV');
  3  END;
  4  /

Procedimento PL/SQL concluído com sucesso.

SQL> DROP TABLE APPL01.TEST_REORG_PROV CASCADE CONSTRAINTS PURGE;

Tabela eliminada.

SQL>

11. Verificar se a tabela foi reorganizada

Vamos verificar o tamanho atual da tabela 'TEST_REORG' e comparar com o tamanho antes do processo de reorganização:

SQL> select segment_name, owner, bytes/1024 "Tam. KB" from dba_segments where segment_name  = 'TEST_REORG';

SEGMENT_NAME         OWNER           Tam. KB
-------------------- ------------ ----------
TEST_REORG           APPL01            14336

SQL>
SQL> select endereco from APPL01.TEST_REORG where regid in (19999,20000,20001);

ENDERECO
------------------------------
Abcde fghi jklmn opqrs tuvxz99
valteraquino.blogspot.com
Abcde fghi jklmn opqrs tuvxz99

SQL>
SQL> select object_name, object_type from dba_objects where owner = 'APPL01';

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
DB_DICT              TABLE
DB_OBJETOS           TABLE
TEST_REORG_PK        INDEX
TEST_REORG_IDX       INDEX
TEST_REORG           TABLE

SQL>

Recuperando uma tabela com o RMAN

Objetivo:

Precisamos restaurar uma tabela que foi excluida da base Oracle 10g cujo tamanho é 1TB e não temos muito espaço disponível para restaurar a base totalmente, a tabela não esta disponível na RECYCLEBIN e não temos como utilizar a opção FLASHBACK TABLE.

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado


Overview:

Nesse processo vamos utilizar dois servidores, o primeiro hospeda a base ficticia com um 1TB de dados e o segundo seria o servidor para recuperação com menos espaço. Vamos realizar um backup da base de origem e em seguida excluir um tabela. Para recuperar esta tabela vamos utilizar somente as tablespaces que são imprescindíveis para o processo de recuperação.

Numa situação real, como estamos utilizando um servidor de recuperação com pouco espaço e a base original possui 1TB o restore das tablespaces via RMAN deveria ser realizado utilizando uma unidade de fita, no nosso exemplo a base de origem não tem 1TB de dados, mas como exemplo vamos realizar a recuperação excluido algumas tablespaces da base original e os arquivos de backup serão mantidos em disco.

Tópicos:

1. Setup;
2. Realizar Backup da base;
3. Eliminar uma tabela para recuperação;
4. Identificar arquivos necessários para recuperação;
5. Criar estrutura de diretórios no servidor de recuperação;
6. Disponibilizar servidor com os binários do Oracle;
7. Copiar arquivos para novo servidor;
8. Restaurar a base com as tablespaces necessárias;
9. Exportar a tabela da base de recuperação;
10. Transferir o arquivo dump para base original;
11. Importar a tabela na base original;
12. Eliminar a base de recuperação;
    

1. Setup

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME
------------------------------
EXAMPLE
SYSAUX
SYSTEM
TEMP
UNDOTBS1
USERS

6 linhas selecionadas.

SQL> create tablespace appl01 datafile '/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/appl01.dbf' size 100 M;

Tablespace criado.

SQL> create tablespace appl02 datafile '/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/appl02.dbf' size 100 M;

Tablespace criado.

SQL> create user appl01 identified by oracle default tablespace appl01 temporary tablespace temp;

Usuário criado.

SQL> grant connect to appl01;

Concessão bem-sucedida.

SQL> grant unlimited tablespace to appl01;

Concessão bem-sucedida.

SQL> grant select any dictionary to appl01;

Concessão bem-sucedida.

SQL> grant resource to appl01;

Concessão bem-sucedida.

SQL> create user appl02 identified by oracle default tablespace appl02 temporary tablespace temp;

Usuário criado.

SQL> grant connect to appl02;

Concessão bem-sucedida.

SQL> grant unlimited tablespace to appl02;

Concessão bem-sucedida.

SQL> grant select any dictionary to appl02;

SQL> grant resource to appl02;

Concessão bem-sucedida.

SQL> conn appl01/oracle@lab1;
Conectado.
SQL> create table db_objetos as select * from all_objects;

Tabela criada.

SQL> create table db_dict as select * from dict;

Tabela criada.

SQL> conn appl02/oracle@lab1;
Conectado.
SQL> create table db_objetos as select * from all_objects;

Tabela criada.

SQL> create table db_dict as select * from dict;

Tabela criada.

2. Realizar Backup da base;

Utilizando o script abaixo, execute um backup full da base:

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_SID=lab1
export ORACLE_HOME ORACLE_SID

# RMAN

$ORACLE_HOME/bin/rman nocatalog target / << EOF
RUN {
allocate channel d1 type disk;
sql "alter system archive log current";
sql "alter system archive log current";
sql "alter system archive log current";
backup AS COMPRESSED BACKUPSET database PLUS archivelog delete all input;
release channel d1;

allocate channel d2 type disk;
BACKUP CURRENT CONTROLFILE;
release channel d2;
}

EOF

3. Eliminar uma tabela para recuperação

[oracle@odbsrv02 ~]$ date
Tue Jun 15 20:08:20 BRT 2010

SQL> select table_name, owner from dba_tables where owner like 'APPL%';

TABLE_NAME OWNER
---------- -------------------
DB_OBJETOS APPL01
DB_DICT    APPL01
DB_OBJETOS APPL02
DB_DICT    APPL02 

SQL> drop table APPL01.DB_OBJETOS cascade constraints;

Table dropped.

SQL> select table_name, owner from dba_tables where owner like 'APPL%';

TABLE_NAME OWNER
---------- -------------------
DB_DICT    APPL01
DB_OBJETOS APPL02
DB_DICT    APPL02 

4. Identificar arquivos necessários para recuperação

Para iniciar-mos o processo de recuperação precisamos copiar os arquivos de backup para o servidor de recuperação e o primeiro passo dessa tarefa é identificar quais são os arquivos que contem o backup que vamos utilizar:

[oracle@odbsrv02 bin]$ ./rman nocatalog target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jun 15 20:22:55 2010

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

connected to target database: LAB1 (DBID=2204424562)
using target database control file instead of recovery catalog

RMAN> LIST BACKUPSET COMPLETED BETWEEN '14-JUN-2010' AND '16-JUN-2010';


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

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6       180.64M    DISK        00:01:25     15-JUN-10
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: TAG20100615T194817
        Piece Name: /u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T194817_61j0sp88_.bkp

  List of Archived Logs in backup set 6
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    55      3803103    12-APR-08 3814785    24-APR-08
  1    56      3814785    24-APR-08 3833799    29-APR-08
  1    57      3833799    29-APR-08 3837114    29-APR-08
  1    58      3837114    29-APR-08 3848519    06-AUG-09
  1    59      3848519    06-AUG-09 3869510    06-AUG-09
  1    60      3869510    06-AUG-09 3888053    06-AUG-09
  1    61      3888053    06-AUG-09 3910450    06-AUG-09
  1    62      3910450    06-AUG-09 3935140    06-AUG-09
  1    63      3935140    06-AUG-09 3952899    06-AUG-09
  1    64      3952899    06-AUG-09 3966963    06-AUG-09
  1    65      3966963    06-AUG-09 3996848    06-AUG-09
  1    66      3996848    06-AUG-09 4016856    06-AUG-09
  1    67      4016856    06-AUG-09 4017538    06-AUG-09
  1    98      4323672    06-AUG-09 4334956    06-AUG-09

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       152.58M    DISK        00:01:23     15-JUN-10
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20100615T194817
        Piece Name: /u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T194817_61j0whwp_.bkp

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    99      4334956    06-AUG-09 4353166    07-AUG-09
  1    100     4353166    07-AUG-09 4355280    07-AUG-09
  1    101     4355280    07-AUG-09 4373312    07-AUG-09
  1    102     4373312    07-AUG-09 4382032    07-AUG-09
  1    103     4382032    07-AUG-09 4389961    07-AUG-09
  1    104     4389961    07-AUG-09 4420571    07-AUG-09
  1    105     4420571    07-AUG-09 4456920    01-SEP-09
  1    106     4456920    01-SEP-09 4479295    15-JUN-10
  1    107     4479295    15-JUN-10 4482044    15-JUN-10
  1    108     4482044    15-JUN-10 4482069    15-JUN-10
  1    109     4482069    15-JUN-10 4482104    15-JUN-10
  1    110     4482104    15-JUN-10 4482175    15-JUN-10
  1    111     4482175    15-JUN-10 4482178    15-JUN-10
  1    112     4482178    15-JUN-10 4482183    15-JUN-10
  1    113     4482183    15-JUN-10 4484568    15-JUN-10
  1    114     4484568    15-JUN-10 4484583    15-JUN-10
  1    115     4484583    15-JUN-10 4484640    15-JUN-10
  1    116     4484640    15-JUN-10 4484642    15-JUN-10
  1    117     4484642    15-JUN-10 4484646    15-JUN-10
  1    118     4484646    15-JUN-10 4484649    15-JUN-10
  1    119     4484649    15-JUN-10 4484853    15-JUN-10
  1    120     4484853    15-JUN-10 4484882    15-JUN-10
  1    121     4484882    15-JUN-10 4484912    15-JUN-10
  1    122     4484912    15-JUN-10 4484923    15-JUN-10
  1    123     4484923    15-JUN-10 4484927    15-JUN-10
  1    124     4484927    15-JUN-10 4484931    15-JUN-10
  1    125     4484931    15-JUN-10 4484934    15-JUN-10
  1    126     4484934    15-JUN-10 4487684    15-JUN-10
  1    127     4487684    15-JUN-10 4487719    15-JUN-10
  1    128     4487719    15-JUN-10 4487781    15-JUN-10
  1    129     4487781    15-JUN-10 4487785    15-JUN-10
  1    130     4487785    15-JUN-10 4487788    15-JUN-10
  1    131     4487788    15-JUN-10 4487790    15-JUN-10
  1    132     4487790    15-JUN-10 4488592    15-JUN-10
  1    133     4488592    15-JUN-10 4488614    15-JUN-10
  1    134     4488614    15-JUN-10 4488659    15-JUN-10
  1    135     4488659    15-JUN-10 4488677    15-JUN-10
  1    136     4488677    15-JUN-10 4488679    15-JUN-10
  1    137     4488679    15-JUN-10 4488682    15-JUN-10
  1    138     4488682    15-JUN-10 4489544    15-JUN-10
  1    139     4489544    15-JUN-10 4489566    15-JUN-10
  1    140     4489566    15-JUN-10 4489627    15-JUN-10
  1    141     4489627    15-JUN-10 4489645    15-JUN-10
  1    142     4489645    15-JUN-10 4489647    15-JUN-10
  1    143     4489647    15-JUN-10 4489650    15-JUN-10
  1    144     4489650    15-JUN-10 4489838    15-JUN-10
  1    145     4489838    15-JUN-10 4489849    15-JUN-10
  1    146     4489849    15-JUN-10 4489874    15-JUN-10
  1    147     4489874    15-JUN-10 4491280    15-JUN-10
  1    148     4491280    15-JUN-10 4491294    15-JUN-10
  1    149     4491294    15-JUN-10 4491327    15-JUN-10
  1    150     4491327    15-JUN-10 4491336    15-JUN-10
  1    151     4491336    15-JUN-10 4491338    15-JUN-10
  1    152     4491338    15-JUN-10 4491348    15-JUN-10
  1    153     4491348    15-JUN-10 4513353    15-JUN-10
  1    154     4513353    15-JUN-10 4517265    15-JUN-10
  1    155     4517265    15-JUN-10 4517271    15-JUN-10
  1    156     4517271    15-JUN-10 4517279    15-JUN-10
  1    157     4517279    15-JUN-10 4517286    15-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    139.10M    DISK        00:01:26     15-JUN-10
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20100615T195121
        Piece Name: /u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_nnndf_TAG20100615T195121_61j0zbjd_.bkp
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 4517387    15-JUN-10 /u01/app/oracle/product/10.2.0/oradata/db_1/lab1/system01.dbf
  2       Full 4517387    15-JUN-10 /u01/app/oracle/product/10.2.0/oradata/db_1/lab1/undotbs01.dbf
  3       Full 4517387    15-JUN-10 /u01/app/oracle/product/10.2.0/oradata/db_1/lab1/sysaux01.dbf
  4       Full 4517387    15-JUN-10 /u01/app/oracle/product/10.2.0/oradata/db_1/lab1/users01.dbf
  5       Full 4517387    15-JUN-10 /u01/app/oracle/product/10.2.0/oradata/db_1/lab1/example01.dbf
  6       Full 4517387    15-JUN-10 /u01/app/oracle/product/10.2.0/oradata/db_1/lab1/appl01.dbf
  7       Full 4517387    15-JUN-10 /u01/app/oracle/product/10.2.0/oradata/db_1/lab1/appl02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    1.05M      DISK        00:00:03     15-JUN-10
        BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: TAG20100615T195121
        Piece Name: /u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_ncsnf_TAG20100615T195121_61j12d09_.bkp
  Control File Included: Ckp SCN: 4517444      Ckp time: 15-JUN-10
  SPFILE Included: Modification time: 15-JUN-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10      105.00K    DISK        00:00:01     15-JUN-10
        BP Key: 10   Status: AVAILABLE  Compressed: YES  Tag: TAG20100615T195301
        Piece Name: /u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp

  List of Archived Logs in backup set 10
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    158     4517286    15-JUN-10 4517448    15-JUN-10

RMAN>

Arquivos a serem copiados para o novo servidor são:


/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T194817_61j0sp88_.bkp
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T194817_61j0whwp_.bkp
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_nnndf_TAG20100615T195121_61j0zbjd_.bkp
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_ncsnf_TAG20100615T195121_61j12d09_.bkp
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp

5. Disponibilizar servidor com os binários do Oracle

Para realizar o restore da base reduzida no servidor de recuperação precisamos que seja disponibilizado nesse servidor os binários da base de origem na mesma versão. Utilize o OUI para instalação dos binários do Oracle Database.

6. Criar estrutura de diretórios no servidor de recuperação

[oracle@odbsrv08 admin]$ cd /u01/app/oracle/product/10.2/db/admin
[oracle@odbsrv08 admin]$ mkdir lab1
[oracle@odbsrv08 admin]$ cd lab1/
[oracle@odbsrv08 lab1]$ mkdir adump
[oracle@odbsrv08 lab1]$ mkdir cdump
[oracle@odbsrv08 lab1]$ mkdir bdump
[oracle@odbsrv08 lab1]$ mkdir udump
[oracle@odbsrv08 lab1]$ mkdir -p /u01/app/oracle/product/10.2/oradata/lab1

7. Copiar arquivos para novo servidor

Neste exemplo vamos utilizar o utilitário do Linux SCP para copiar os arquivos da base de origem para a base de recuperação:

[oracle@odbsrv02 bin]$ cd /u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15
[oracle@odbsrv02 2010_06_15]$ ll
total 485372
-rw-r----- 1 oracle oinstall 189420032 Jun 15 19:49 o1_mf_annnn_TAG20100615T194817_61j0sp88_.bkp
-rw-r----- 1 oracle oinstall 159993856 Jun 15 19:51 o1_mf_annnn_TAG20100615T194817_61j0whwp_.bkp
-rw-r----- 1 oracle oinstall 108032 Jun 15 19:53 o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp
-rw-r----- 1 oracle oinstall 1114112 Jun 15 19:53 o1_mf_ncsnf_TAG20100615T195121_61j12d09_.bkp
-rw-r----- 1 oracle oinstall 145866752 Jun 15 19:52 o1_mf_nnndf_TAG20100615T195121_61j0zbjd_.bkp
[oracle@odbsrv02 2010_06_15]$ scp -p o1_mf_annnn_TAG20100615T194817_61j0sp88_.bkp oracle@192.168.11.8:/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15
The authenticity of host '192.168.11.8 (192.168.11.8)' can't be established.
RSA key fingerprint is fb:7e:f6:32:c5:a7:c8:8e:7f:f7:a1:f6:ef:9d:0e:1c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.11.8' (RSA) to the list of known hosts.
oracle@192.168.11.8's password:
o1_mf_annnn_TAG20100615T194817_61j0sp88_.bkp 100% 181MB 7.2MB/s 00:25
[oracle@odbsrv02 2010_06_15]$ scp -p o1_mf_annnn_TAG20100615T194817_61j0whwp_.bkp oracle@192.168.11.8:/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15
oracle@192.168.11.8's password:
o1_mf_annnn_TAG20100615T194817_61j0whwp_.bkp 100% 153MB 6.9MB/s 00:22
[oracle@odbsrv02 2010_06_15]$ scp -p o1_mf_nnndf_TAG20100615T195121_61j0zbjd_.bkp oracle@192.168.11.8:/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15
oracle@192.168.11.8's password:
o1_mf_nnndf_TAG20100615T195121_61j0zbjd_.bkp 100% 139MB 6.1MB/s 00:23
[oracle@odbsrv02 2010_06_15]$ scp -p o1_mf_ncsnf_TAG20100615T195121_61j12d09_.bkp oracle@192.168.11.8:/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15
oracle@192.168.11.8's password:
o1_mf_ncsnf_TAG20100615T195121_61j12d09_.bkp 100% 1088KB 1.1MB/s 00:00
[oracle@odbsrv02 2010_06_15]$ scp -p o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp oracle@192.168.11.8:/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15
oracle@192.168.11.8's password:
o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp 100% 106KB 105.5KB/s 00:00
[oracle@odbsrv02 2010_06_15]$

Copiar o arquivo init da base para o novo servidor:

[oracle@odbsrv02 ~]$ scp -p initlab1.ora oracle@192.168.11.8:/u01/app/oracle/product/10.2/db/dbs
oracle@192.168.11.8's password:
initlab1.ora                                                                                                                    100% 1365     1.3KB/s   00:01

Modificar o arquivo init para adequar os diretórios a estrutura do novo servidor

[oracle@odbsrv08 lab1]$ cat /u01/app/oracle/product/10.2/db/dbs/initlab1.ora
*.aq_tm_processes=0
*.audit_file_dest='/u01/app/oracle/product/10.2/db/admin/lab1/adump'
*.background_dump_dest='/u01/app/oracle/product/10.2/db/admin/lab1/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/product/10.2/oradata/lab1/control01.ctl','/u01/app/oracle/product/10.2/oradata/lab1/control02.ctl','/u01/app/oracle/product/10.2/oradata/lab1/control03.ctl'
*.core_dump_dest='/u01/app/oracle/product/10.2/db/admin/lab1/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='lab1'
*.db_recovery_file_dest='/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1'
*.db_recovery_file_dest_size=3221225472
*.dispatchers='(PROTOCOL=TCP) (SERVICE=lab1XDB)'
*.job_queue_processes=10
*.nls_length_semantics='BYTE'
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sga_max_size=536870912
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/product/10.2/db/admin/lab1/udump'

8. Restaurar a base com as tablespaces necessárias

Restaurar Control File:

[oracle@odbsrv08 2010_06_15]$ export ORACLE_HOME=/u01/app/oracle/product/10.2/db
[oracle@odbsrv08 2010_06_15]$ export ORACLE_SID=lab1
[oracle@odbsrv08 2010_06_15]$ $ORACLE_HOME/bin/rman nocatalog target /

Recovery Manager: Release 10.2.0.5.0 - Production on Fri Jul 16 16:14:55 2010

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     1274744 bytes
Variable Size                150998152 bytes
Database Buffers             381681664 bytes
Redo Buffers                   2916352 bytes

RMAN> RUN {
     allocate channel d1 type disk;
     restore controlfile from '/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_ncsnf_TAG20100615T195121_61j12d09_.bkp';
     release channel d1;
    }

allocated channel: d1
channel d1: sid=156 devtype=DISK

Starting restore at 16-JUL-10

channel d1: restoring control file
channel d1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/product/10.2/oradata/lab1/control01.ctl
output filename=/u01/app/oracle/product/10.2/oradata/lab1/control02.ctl
output filename=/u01/app/oracle/product/10.2/oradata/lab1/control03.ctl
Finished restore at 16-JUL-10

released channel: d1

RMAN> exit

Montar a base em modo Clone:

[oracle@odbsrv08 2010_06_15]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 16 16:29:10 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database mount clone database;

Database altered.

SQL> exit

Catalogar backuppiece criado após backup do Control File:

[oracle@odbsrv08 2010_06_15]$ $ORACLE_HOME/bin/rman nocatalog target /
Recovery Manager: Release 10.2.0.5.0 - Production on Fri Jul 16 16:30:43 2010

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

connected to target database: LAB1 (DBID=2204424562, not open)
using target database control file instead of recovery catalog

RMAN>

list backup summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
4       B  1  A DISK        29-APR-08       1       1       NO         TAG20080429T051656
5       B  1  A DISK        29-APR-08       1       1       NO         TAG20080429T051656
6       B  A  A DISK        15-JUN-10       1       1       YES        TAG20100615T194817
7       B  A  A DISK        15-JUN-10       1       1       YES        TAG20100615T194817
8       B  F  A DISK        15-JUN-10       1       1       YES        TAG20100615T195121

RMAN>
RMAN> catalog backuppiece '/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp';

cataloged backuppiece
backup piece handle=/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp recid=9 stamp=724524229




RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
4       B  1  A DISK        29-APR-08       1       1       NO         TAG20080429T051656
5       B  1  A DISK        29-APR-08       1       1       NO         TAG20080429T051656
6       B  A  A DISK        15-JUN-10       1       1       YES        TAG20100615T194817
7       B  A  A DISK        15-JUN-10       1       1       YES        TAG20100615T194817
8       B  F  A DISK        15-JUN-10       1       1       YES        TAG20100615T195121
9       B  A  A DISK        15-JUN-10       1       1       YES        TAG20100615T195301


RMAN>

Restaurar o Banco de Dados:

RMAN> RUN {
2> allocate channel d1 type disk;
3>
4> set until time "to_date( '15-06-2010 20:00', 'DD-MM-RRRR HH24:MI')";
5>
6> set newname for datafile 1 to '/u01/app/oracle/product/10.2/oradata/lab1/system01.dbf';
7> set newname for datafile 2 to '/u01/app/oracle/product/10.2/oradata/lab1/undotbs01.dbf';
8> set newname for datafile 3 to '/u01/app/oracle/product/10.2/oradata/lab1/sysaux01.dbf';
9> set newname for datafile 6 to '/u01/app/oracle/product/10.2/oradata/lab1/appl01.dbf';
10>
11> restore tablespace system, undotbs1, sysaux, appl01;
12>
13> switch datafile all;
14>
15> sql "alter database datafile 1,2,3,6 online";
16>
17> recover database skip forever tablespace TEMP,USERS,EXAMPLE,APPL02;
18>
19> sql "alter database rename file ''/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/redo01.log'' to ''/u01/app/oracle/product/10.2/oradata/lab1/redo01.log''";

20> sql "alter database rename file ''/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/redo02.log'' to ''/u01/app/oracle/product/10.2/oradata/lab1/redo02.log''";
21>
22> sql "alter database rename file ''/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/redo03.log'' to ''/u01/app/oracle/product/10.2/oradata/lab1/redo03.log''";
23>
24> release channel d1;
}
25>
26> allocated channel: d1
channel d1: sid=156 devtype=DISK

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-JUL-10

channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/product/10.2/oradata/lab1/system01.dbf
restoring datafile 00002 to /u01/app/oracle/product/10.2/oradata/lab1/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/product/10.2/oradata/lab1/sysaux01.dbf
restoring datafile 00006 to /u01/app/oracle/product/10.2/oradata/lab1/appl01.dbf
channel d1: reading from backup piece /u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_nnndf_TAG20100615T195121_61j0zbjd_.bkp
channel d1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_nnndf_TAG20100615T195121_61j0zbjd_.bkp tag=TAG20100615T195121
channel d1: restore complete, elapsed time: 00:01:26
Finished restore at 16-JUL-10

datafile 1 switched to datafile copy
input datafile copy recid=19 stamp=724523593 filename=/u01/app/oracle/product/10.2/oradata/lab1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=20 stamp=724523593 filename=/u01/app/oracle/product/10.2/oradata/lab1/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=21 stamp=724523594 filename=/u01/app/oracle/product/10.2/oradata/lab1/sysaux01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=22 stamp=724523594 filename=/u01/app/oracle/product/10.2/oradata/lab1/appl01.dbf

sql statement: alter database datafile 1,2,3,6 online

Starting recover at 16-JUL-10

Executing: alter database datafile 4 offline drop
Executing: alter database datafile 5 offline drop
Executing: alter database datafile 7 offline drop
starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=158
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/backupset/2010_06_15/o1_mf_annnn_TAG20100615T195301_61j12gt3_.bkp tag=TAG20100615T195301
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_07_16/o1_mf_1_158_641fojdr_.arc thread=1 sequence=158
channel default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_07_16/o1_mf_1_158_641fojdr_.arc recid=75 stamp=724524288
unable to find archive log
archive log thread=1 sequence=159

RMAN>
sql statement: alter database rename file ''/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/redo01.log'' to ''/u01/app/oracle/product/10.2/oradata/lab1/redo01.log''

RMAN>
sql statement: alter database rename file ''/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/redo02.log'' to ''/u01/app/oracle/product/10.2/oradata/lab1/redo02.log''

RMAN>
sql statement: alter database rename file ''/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/redo03.log'' to ''/u01/app/oracle/product/10.2/oradata/lab1/redo03.log''


released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/16/2010 16:44:52
RMAN-06054: media recovery requesting unknown log: thread 1 seq 159 lowscn 4517448

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Acessar a base e verificar os datafiles disponíveis

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 16 16:56:09 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> set lines 200 pages 66;
SQL> col name for a80
SQL> select name, status from  v$datafile;

NAME                                                                             STATUS
-------------------------------------------------------------------------------- -------
/u01/app/oracle/product/10.2/oradata/lab1/system01.dbf                           SYSTEM
/u01/app/oracle/product/10.2/oradata/lab1/undotbs01.dbf                          ONLINE
/u01/app/oracle/product/10.2/oradata/lab1/sysaux01.dbf                           ONLINE
/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/users01.dbf                     OFFLINE
/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/example01.dbf                   OFFLINE
/u01/app/oracle/product/10.2/oradata/lab1/appl01.dbf                             ONLINE
/u01/app/oracle/product/10.2.0/oradata/db_1/lab1/appl02.dbf                      OFFLINE

7 rows selected.

SQL> exit

9. Exportar a tabela da base de recuperação

Criar o diretorio para Datapump:

[oracle@odbsrv08 lab1]$ mkdir /u01/app/oracle/product/10.2/db/admin/lab1/dpdump
[oracle@odbsrv08 lab1]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 16 17:05:40 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create or replace directory DATA_PUMP_DIR as '/u01/app/oracle/product/10.2/db/admin/lab1/dpdump';

Directory created.

SQL> exit

Criar tablespace temporária:

[oracle@odbsrv08 bdump]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 16 18:03:26 2010

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/product/10.2/oradata/lab1/temp101.dbf' size 50M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> exit

Exportar a tabela:

[oracle@odbsrv08 bin]$ expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=appl01_DB_OBJETOS.dmp tables=appl01.DB_OBJETOS logfile=appl01_DB_OBJETOS.log

Export: Release 10.2.0.5.0 - Production on Friday, 16 July, 2010 18:08:18

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=appl01_DB_OBJETOS.dmp tables=appl01.DB_OBJETOS logfile=appl01_DB_OBJETOS.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "APPL01"."DB_OBJETOS"                       3.868 MB   40786 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/product/10.2/db/admin/lab1/dpdump/appl01_DB_OBJETOS.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 18:08:28

[oracle@odbsrv08 bin]$

10. Transferir o arquivo dump para base original

[oracle@odbsrv08 dpdump]$ cd /u01/app/oracle/product/10.2/db/admin/lab1/dpdump
[oracle@odbsrv08 dpdump]$ scp -p appl01_DB_OBJETOS.dmp oracle@192.168.11.7:/u01/app/oracle/product/10.2.0/db_1/admin/lab1/dpdump
The authenticity of host '192.168.11.7 (192.168.11.7)' can't be established.
RSA key fingerprint is 52:71:3f:ec:a0:6e:50:dc:6e:af:5c:85:93:5a:cc:b6.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.11.7' (RSA) to the list of known hosts.
oracle@192.168.11.7's password:
appl01_DB_OBJETOS.dmp                                                                                              100% 4028KB   3.9MB/s   00:00
[oracle@odbsrv08 dpdump]$

11. Importar a tabela na base original

[oracle@odbsrv02 dpdump]$ impdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=appl01_DB_OBJETOS.dmp tables=appl01.DB_OBJETOS logfile=appl01_DB_OBJETOS.log

Import: Release 10.2.0.5.0 - Production on Tuesday, 15 June, 2010 22:09:51

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=appl01_DB_OBJETOS.dmp tables=appl01.DB_OBJETOS logfile=appl01_DB_OBJETOS.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "APPL01"."DB_OBJETOS"                       3.868 MB   40786 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 22:10:12

12. Eliminar a base de recuperação

Finalizado o processo de recuperação da tabela podemos eliminar a
base de recuperação e liberar o servidor.

UTILIZANDO LOGMINER PARA AUDITAR ALTERAÇÕES NA BASE DE DADOS

Objetivo:

Existem situações em que precisamos obter informações de alguma alteração realizada na base de dados e não podemos contar com os recursos de auditoria pois estes não estavam habilitados, nesse caso uma alternativa seria recorrer ao Logminer. Esse recurso permite que sejam recuperados os comandos DDL e DML que foram executados na base e ainda fornece algumas informações históricas desses comandos. Essas informações são armazenadas normalmente nos arquivos de REDO (registros mais recentes) e nos arquivos de ARCHIVE (registros mais antigos).

Overview:

Oracle LogMiner permite que você consulte os arquivos de log redo através de uma interface SQL. Todas as alterações feitas nos dados dos usuários e dicionário do Banco são registradas nos arquivos de REDO do banco Oracle. Portanto, os arquivos de log redo contem todas as informações necessárias para realizar operações de recuperação.

A funcionalidade LogMiner está disponível através de uma interface de linha de comando ou através do Oracle LogMiner Viewer interface gráfica do usuário (GUI). O LogMiner Viewer é uma parte do Oracle Enterprise Manager.

A seguir temos algumas possibilidades de utilização das informações contidas em arquivos de log redo:

• Identificar quando uma corrupção lógica ocorreu no banco de dados, tais como quando pode ter iniciado os erros de uma determinada aplicação. Isso permite que você restaure o banco de dados para o estado em que estava antes da corrupção.
• Detectar e sempre que possível, corrigir erros do usuário, que é um cenário mais provável do que a corrupção lógica. Por erros de Usuário entende-se eliminar registros por engano por causa de valores incorretos em uma cláusula WHERE, atualizar registros com valores incorretos, eliminar um indice por engano, e assim por diante.
• Determinar as ações necessárias para executar uma recuperação fine-grained, ao nível da transação. Se você conseguir levantar todas as dependências existentes de uma determinada tabela ou conjunto de tabelas, será possível realizar uma operação de table-based undo para reverter um conjunto de alterações.
• Performance tuning e Capacity planning para análise de tendência. Você pode determinar quais tabelas sofrem mais atualizações e inserções. Esta informação fornece uma perspectiva histórica sobre as estatísticas de acesso ao disco, que pode ser utilizado para fins de tuning.
• Realizar pós-auditoria. Os arquivos de REDO contem todas as informações necessárias para rastrear qualquer DML e DDL executadas no banco de dados, a ordem em que elas foram executadas, e quem as executou.

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado

Considerações:

1. Certifique-se que o banco de dados estava com a opção supplemental_log_data_min ativada no momento em que os arquivos de REDO foram criados:

SQL> SELECT name, supplemental_log_data_min FROM v$database;

NAME SUPPLEME
------------------------------ --------
M10202WA YES

2. Para habilitar a opção supplemental_log_data_min utilize o comando a seguir:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

3. O LogMiner não armazena USERNAME e SESSION_INFO para todos os registros de REDO.
Para obter essas informações nas consultas ao LogMiner, devemos adicionar os arquivos de LOG que contem os registros de conexão da sessão cujos comandos estamos pesquisando. Se os arquivos de log adicionados não possuirem o comando de conexão da sessão que executou as DDL e/ou DML que estão sendo pesquisadas o LogMiner não será capaz de mostrar o USERNAME que executou o comando e vair exibir "UNKNOWN" no campo username, alem disso, o campo SESSION_INFO tambem exibirá "UNKNOWN".

Tópicos:

1. Setup
2. Identificando arquivos de log
3. Adicionando arquivos de log ao LogMiner
4. Iniciando o Logminer
5. Pesquisando informações desejadas
6. Encerrando o LogMiner

1. Setup

SQL> create user tstuser identified by tstuser default tablespace users temporary tablespace temp;

User created.

SQL> grant connect to tstuser;

Grant succeeded.

SQL> grant dba to tstuser;

Grant succeeded.

SQL> create table hr.db_objetos as select * from dba_objects;

Tabela criada.

SQL> select to_char(SYSDATE,'dd-mm-yyyy hh24:mi:ss') FIRST_TIME from dual;

FIRST_TIME
-------------------
15-06-2010 14:13:39

1 linha selecionada.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> connect tstuser/tstuser@lab1;
Conectado.
SQL>
SQL> delete from hr.db_objetos where owner = 'HR';

87 linhas deletadas.

SQL> commit;

Validação completa.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> drop table hr.db_objetos;

Tabela eliminada.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> select to_char(SYSDATE,'dd-mm-yyyy hh24:mi:ss') FIRST_TIME from dual;

FIRST_TIME
-------------------
15-06-2010 14:15:56

1 linha selecionada.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> Alter system switch logfile;

Sistema alterado.

SQL> Alter system switch logfile;

Sistema alterado.

2. Identificando arquivos de log

Substitua a consulta abaixo com os horários de inicio e término do período onde você acredita que ocorreram os comandos que você esta buscando, neste exemplo vamos utilizar os horários das consultas listadas acima.

SQL> select NAME, to_char(FIRST_TIME,'dd-mm-yyyy hh24:mi:ss') FIRST_TIME
2 from V$ARCHIVED_LOG
3 where first_time > to_date('15-06-2010 14:13:39','dd-mm-yyyy hh24:mi:ss')
4 and first_time < to_date('15-06-2010 14:15:56','dd-mm-yyyy hh24:mi:ss');

NAME FIRST_TIME
------------------------------------------------------------------------------------------------------------------------ -------------------
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_148_61hf8pbd_.arc 15-06-2010 14:15:01
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_149_61hf9qd1_.arc 15-06-2010 14:15:02
/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_150_61hfbdl4_.arc 15-06-2010 14:15:35

3 linhas selecionadas.

3. Adicionando arquivos de log ao LogMiner

SQL> conn sys/oracle@lab1 as sysdba;
Conectado.
SQL> BEGIN
2 DBMS_LOGMNR.add_logfile (
3 options => DBMS_LOGMNR.new,
4 logfilename => '/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_148_61hf8pbd_.arc');
5 DBMS_LOGMNR.add_logfile (
6 options => DBMS_LOGMNR.addfile,
7 logfilename => '/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_149_61hf9qd1_.arc');
8 DBMS_LOGMNR.add_logfile (
9 options => DBMS_LOGMNR.addfile,
10 logfilename => '/u01/app/oracle/product/10.2.0/flash_recovery_area/db_1/LAB1/archivelog/2010_06_15/o1_mf_1_150_61hfbdl4_.arc');
11 END;
12 /

Procedimento PL/SQL concluído com sucesso.

4. Iniciando o Logminer

SQL> BEGIN
2 DBMS_LOGMNR.START_LOGMNR(
3 options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
4 END;
5 /

Procedimento PL/SQL concluído com sucesso.

5. Pesquisando informações desejadas

Depois que o LogMiner é iniciado, o conteúdo dos arquivos de log podem ser consultados usando as seguintes VIEWS:

* V$LOGMNR_DICTIONARY - O arquivo de Dicionário de dados em uso.
* V$LOGMNR_PARAMETERS - Parametros definidos para o LogMiner.
* V$LOGMNR_LOGS - Quais arquivos de log estão sendo analisadas.
* V$LOGMNR_CONTENTS - O conteúdo dos arquivos de redo log que estão sendo analisados.

A seguir uma consulta que lista os comandos dos usuários durante um período analisado:

SQL> col SEG_OWNER for a15;
SQL> col SEG_NAME for a60;
SQL> col USERNAME for a15;
SQL> col OPERATION for a15;
SQL> select to_char(TIMESTAMP,'dd-mm-yyyy hh24:mi:ss') TIMESTAMP, SEG_OWNER, SEG_NAME, USERNAME, OPERATION, SESSION_INFO
2 from V$LOGMNR_CONTENTS
3 where SEG_OWNER not in ('SYS','SYSTEM','UNKNOWN')
4 and USERNAME <> 'UNKNOWN';

TIMESTAMP SEG_OWN SEG_NAME USERNAMEOPERATION
----------------------------------------------------------------------------------------------------------------------------
SESSION_INFO
----------------------------------------------------------------------------------------------------------------------------------------------------------
15-06-2010 14:15:02HR BIN$iRTAtNfUPxDgQKjABwtMvA==$0 TSTUSER DELETE
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER
.
.
.
.
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER
15-06-2010 14:15:02HR BIN$iRTAtNfUPxDgQKjABwtMvA==$0 TSTUSER DELETE
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER
15-06-2010 14:15:32HR DB_OBJETOS TSTUSER DDL
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER
15-06-2010 14:15:32HR DB_OBJETOS TSTUSER DDL
login_username=TSTUSER client_info= OS_username=Valter Machine_name=WORKGROUP\VALTER

89 linhas selecionadas.


Tambem podemos utilizar a consulta a seguir para listar os comandos emitidos e seus respectivos comandos para reverter as alterações realizadas.

SELECT scn, operation, sql_redo, sql_undo FROM v$logmnr_contents;

6. Encerrando o LogMiner

SQL> BEGIN
2 DBMS_LOGMNR.END_LOGMNR();
3 END;
4 /

Procedimento PL/SQL concluído com sucesso.