Oracle Database Docs: 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:

Setup;
Realizar Backup da base;
Eliminar uma tabela para recuperação;
Identificar arquivos necessários para recuperação;
Criar estrutura de diretórios no servidor de recuperação;
Disponibilizar servidor com os binários do Oracle;
Copiar arquivos para novo servidor;
Restaurar a base com as tablespaces necessárias;
Exportar a tabela da base de recuperação;
Transferir o arquivo dump para base original;
Importar a tabela na base original;
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.

2 comentários:

Rodrigo Santana9 de julho de 2010 às 17:44
Excelente post Valter José, parabéns pelo nível do seu blog. Se não importar, posso colocar um link do seu blog no meu blog?.

Abraço
Rodrigo Santana
http://rodrigo-oracle.blogspot.com
ResponderExcluir
Respostas
Valter José de Aquino12 de julho de 2010 às 09:16
Olá Rodrigo, fico muito contente que você tenha gostado deste post e muito satisfeito se você incluir um link do meu blog no seu blog.

Abs,
Valter Aquino
ResponderExcluir
Respostas

Adicionar comentário

Oracle Database Docs

segunda-feira, 21 de junho de 2010

Recuperando uma tabela com o RMAN

2 comentários:

Promoção

Seguidores

Video

Quem sou eu