A partir do Oracle versão 12c é possível recuperar somente uma tabela
através do RMAN. Neste artigo vamos demonstrar os passos da recuperação.
1. Fazendo o backup "full" do banco de dados.
[oracle@ora12c tmp]$ rman target /
Recovery Manager: Release 12.1.0.1.0 -
Production on Fri Jun 20 10:38:44 2014
Copyright (c) 1982, 2013, Oracle and/or
its affiliates. All rights reserved.
connected to target database: ORCL
(DBID=1375769800)
RMAN> backup database include current controlfile;
Starting backup at 20-JUN-14
using target database control file instead
of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device
type=DISK
channel ORA_DISK_1: starting full datafile
backup set
channel ORA_DISK_1: specifying datafile(s)
in backup set
input datafile file number=00001
name=+DATA/ORCL/DATAFILE/system.258.847988943
input datafile file number=00003
name=+DATA/ORCL/DATAFILE/sysaux.256.847988843
input datafile file number=00004
name=+DATA/ORCL/DATAFILE/undotbs1.260.847989031
input datafile file number=00006
name=+DATA/ORCL/DATAFILE/users.259.847989029
channel ORA_DISK_1: starting piece 1 at
20-JUN-14
channel ORA_DISK_1: finished piece 1 at
20-JUN-14
piece
handle=/u01/app/oracle/12c/db/dbs/09pba9pt_1_1 tag=TAG20140620T104101
comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile
backup set
channel ORA_DISK_1: specifying datafile(s)
in backup set
including current control file in backup
set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at
20-JUN-14
channel ORA_DISK_1: finished piece 1 at
20-JUN-14
piece
handle=/u01/app/oracle/12c/db/dbs/0apba9s8_1_1 tag=TAG20140620T104101
comment=NONE
channel ORA_DISK_1: backup set complete,
elapsed time: 00:00:01
Finished backup at 20-JUN-14
RMAN> exit
Recovery Manager complete.
2. Escolher uma tabela para remoção do banco - "schema"
SCOTT.
SQL> conn scott/tiger
Connected.
SQL>
select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
----------
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows
selected.
3. Remover a tabela EMP do banco de dados.
SQL> drop table emp;
Table dropped.
4. Uma vez removida a tabela, vamos restaurar a tabela. Vamos
primeiro verificar qual é o SCN do "backupset".
[oracle@ora12c tmp]$ rman target /
Recovery Manager: Release 12.1.0.1.0 -
Production on Fri Jun 20 11:31:19 2014
Copyright (c) 1982, 2013, Oracle and/or
its affiliates. All rights reserved.
connected to target database: ORCL
(DBID=1375769800)
RMAN> list backupset;
using target database control file instead
of recovery catalog
List of Backup Sets
===================
BS Key
Type LV Size Device Type
Elapsed Time Completion Time
------- ---- -- ---------- -----------
------------ ---------------
9
Full 1.16G DISK
00:01:12 20-JUN-14
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20140620T104101
Piece Name: /u01/app/oracle/12c/db/dbs/09pba9pt_1_1
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp
Time Name
---- -- ---- ---------- --------- ----
1 Full 1829678 20-JUN-14
+DATA/ORCL/DATAFILE/system.258.847988943
3 Full 1829678 20-JUN-14
+DATA/ORCL/DATAFILE/sysaux.256.847988843
4 Full 1829678 20-JUN-14 +DATA/ORCL/DATAFILE/undotbs1.260.847989031
6 Full 1829678 20-JUN-14
+DATA/ORCL/DATAFILE/users.259.847989029
BS Key
Type LV Size Device Type
Elapsed Time Completion Time
------- ---- -- ---------- -----------
------------ ---------------
10
Full 9.64M DISK
00:00:02 20-JUN-14
BP Key: 10 Status:
AVAILABLE Compressed: NO Tag: TAG20140620T104101
Piece Name: /u01/app/oracle/12c/db/dbs/0apba9s8_1_1
SPFILE Included: Modification time: 20-JUN-14
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1829704 Ckp time:
20-JUN-14
6. Para a restauração da tabela vamos utilizar o SCN indicado no
acima (1829704).
RMAN> RECOVER TABLE SCOTT.EMP UNTIL scn
1829704 AUXILIARY DESTINATION '/tmp/backups';
Starting recover at 20-JUN-14
using target database control file instead
of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=277 device
type=DISK
RMAN-05026: WARNING: presuming following
set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO
segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with
SID='AnDs'
initialization parameters used for
automatic instance:
db_name=ORCL
db_unique_name=AnDs_pitr_ORCL
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/tmp/backups
log_archive_dest_1='location=/tmp/backups'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 281019648 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until
scn 1829704;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone
database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 20-JUN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=75 device
type=DISK
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: restoring control
file
channel ORA_AUX_DISK_1: reading from
backup piece /u01/app/oracle/12c/db/dbs/0apba9s8_1_1
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/12c/db/dbs/0apba9s8_1_1 tag=TAG20140620T104101
channel ORA_AUX_DISK_1: restored backup
piece 1
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
output file
name=/tmp/backups/ORCL/controlfile/o1_mf_9t8hnfd6_.ctl
Finished restore at 20-JUN-14
sql statement: alter database mount clone
database
sql statement: alter system archive log
current
contents of Memory Script:
{
# set requested point in time
set until
scn 1829704;
# set destinations for recovery set and
auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery
set and the auxiliary set
restore clone datafile 1, 4, 3;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /tmp/backups/ORCL/datafile/o1_mf_temp_%u_.tmp
in control file
Starting restore at 20-JUN-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00001 to /tmp/backups/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile
00004 to /tmp/backups/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile
00003 to /tmp/backups/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from
backup piece /u01/app/oracle/12c/db/dbs/09pba9pt_1_1
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/12c/db/dbs/09pba9pt_1_1 tag=TAG20140620T104101
channel ORA_AUX_DISK_1: restored backup
piece 1
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:01:05
Finished restore at 20-JUN-14
datafile 1 switched to datafile copy
input datafile copy RECID=4
STAMP=850734121 file name=/tmp/backups/ORCL/datafile/o1_mf_system_9t8hnr58_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5
STAMP=850734121 file
name=/tmp/backups/ORCL/datafile/o1_mf_undotbs1_9t8hnr6n_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6
STAMP=850734121 file name=/tmp/backups/ORCL/datafile/o1_mf_sysaux_9t8hnr6h_.dbf
contents of Memory Script:
{
# set requested point in time
set until
scn 1829704;
# online the datafiles restored or
switched
sql clone "alter database
datafile 1 online";
sql clone "alter database
datafile 4 online";
sql clone "alter database
datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1",
"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database
datafile 1 online
sql statement: alter database
datafile 4 online
sql statement: alter database
datafile 3 online
Starting recover at 20-JUN-14
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 14
is already on disk as file /u01/app/oracle/12c/db/dbs/arch/1_14_847989067.dbf
archived log file
name=/u01/app/oracle/12c/db/dbs/arch/1_14_847989067.dbf thread=1 sequence=14
media recovery complete, elapsed time:
00:00:01
Finished recover at 20-JUN-14
sql statement: alter database open read
only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql
clone "alter system set
control_files =
''/tmp/backups/ORCL/controlfile/o1_mf_9t8hnfd6_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone
database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not
started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter system set control_files = ''/tmp/backups/ORCL/controlfile/o1_mf_9t8hnfd6_.ctl''
comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not
started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter database mount clone
database
contents of Memory Script:
{
# set requested point in time
set until
scn 1829704;
# set destinations for recovery set and
auxiliary set datafiles
set newname for datafile 6 to new;
# restore the tablespaces in the recovery
set and the auxiliary set
restore clone datafile 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 20-JUN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=15 device
type=DISK
channel ORA_AUX_DISK_1: starting datafile
backup set restore
channel ORA_AUX_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile
00006 to /tmp/backups/ANDS_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from
backup piece /u01/app/oracle/12c/db/dbs/09pba9pt_1_1
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/12c/db/dbs/09pba9pt_1_1 tag=TAG20140620T104101
channel ORA_AUX_DISK_1: restored backup
piece 1
channel ORA_AUX_DISK_1: restore complete,
elapsed time: 00:00:01
Finished restore at 20-JUN-14
datafile 6 switched to datafile copy
input datafile copy RECID=8
STAMP=850734155 file
name=/tmp/backups/ANDS_PITR_ORCL/datafile/o1_mf_users_9t8hqts5_.dbf
contents of Memory Script:
{
# set requested point in time
set until
scn 1829704;
# online the datafiles restored or
switched
sql clone "alter database
datafile 6 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM",
"UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database
datafile 6 online
Starting recover at 20-JUN-14
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 14
is already on disk as file /u01/app/oracle/12c/db/dbs/arch/1_14_847989067.dbf
archived log file
name=/u01/app/oracle/12c/db/dbs/arch/1_14_847989067.dbf thread=1 sequence=14
media recovery complete, elapsed time:
00:00:00
Finished recover at 20-JUN-14
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp/backups''";
# create directory for datapump export
sql clone "create or replace
directory TSPITR_DIROBJ_DPDIR as ''
/tmp/backups''";
}
executing Memory Script
sql statement: create or replace directory
TSPITR_DIROBJ_DPDIR as ''/tmp/backups''
sql statement: create or replace directory
TSPITR_DIROBJ_DPDIR as ''/tmp/backups''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_AnDs_bzdv":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
EXPDP> Processing object type
TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type
TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
EXPDP> Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "SCOTT"."EMP" 8.757 KB 14 rows
EXPDP> Master table "SYS"."TSPITR_EXP_AnDs_bzdv"
successfully loaded/unloaded
EXPDP>
******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_AnDs_bzdv is:
EXPDP>
/tmp/backups/tspitr_AnDs_17140.dmp
EXPDP> Job "SYS"."TSPITR_EXP_AnDs_bzdv"
successfully completed at Fri Jun 20 11:03:32 2014 elapsed 0 00:00:31
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_AnDs_lryn"
successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_AnDs_lryn":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "SCOTT"."EMP" 8.757 KB 14 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
IMPDP> Processing object type
TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
IMPDP> Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_AnDs_lryn"
successfully completed at Fri Jun 20 11:04:00 2014 elapsed 0 00:00:22
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file
/tmp/backups/ORCL/datafile/o1_mf_temp_9t8hpvlf_.tmp deleted
auxiliary instance file
/tmp/backups/ANDS_PITR_ORCL/onlinelog/o1_mf_3_9t8hr0xc_.log deleted
auxiliary instance file
/tmp/backups/ANDS_PITR_ORCL/onlinelog/o1_mf_2_9t8hqyrr_.log deleted
auxiliary instance file
/tmp/backups/ANDS_PITR_ORCL/onlinelog/o1_mf_1_9t8hqx0p_.log deleted
auxiliary instance file
/tmp/backups/ANDS_PITR_ORCL/datafile/o1_mf_users_9t8hqts5_.dbf deleted
auxiliary instance file
/tmp/backups/ORCL/datafile/o1_mf_sysaux_9t8hnr6h_.dbf deleted
auxiliary instance file
/tmp/backups/ORCL/datafile/o1_mf_undotbs1_9t8hnr6n_.dbf deleted
auxiliary instance file /tmp/backups/ORCL/datafile/o1_mf_system_9t8hnr58_.dbf
deleted
auxiliary instance file
/tmp/backups/ORCL/controlfile/o1_mf_9t8hnfd6_.ctl deleted
auxiliary instance file
tspitr_AnDs_17140.dmp deleted
Finished recover at 20-JUN-14
7. Voltar o parâmetro "standby_file_management" para AUTO.
SQL> alter system set standby_file_management=auto scope=both;
System altered.
8. Ativar o processo de "recover" no banco de dados Standby.
SQL> alter database recover managed standby database disconnect from session;
8. Ativar o processo de "recover" no banco de dados Standby.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
7. Verificando a tabela restaurada.
[oracle@ora12c
tmp]$ sqlplus scott/tiger
SQL*Plus:
Release 12.1.0.1.0 Production on Fri Jun 20 11:24:24 2014
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Last
Successful login time: Fri Jun 20 2014 10:59:01 -03:00
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the
Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real
Application Testing options
SQL> set
lines 200 pages 200
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Referências: Oracle®
Database Backup and Recovery User´s Guide 12c Release 1(12.1)
Oracle® Database 2 Day DBA 12c
Release 1(12.1)
MSc.
Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com
