用户名   密码        忘记密码  |  注册  |  订阅
sapclub sap
首页 培训 咨询 求职 招聘 书店 论坛 博客 应用
 
Oracle 12C RMAN新特性 -基于表时间点恢复(上)

http://www.erpclub.org 2016/8/24 9:34:37 浏览次数:2949


在Oracle数据库中,对于用户因DDL(drop或truncate)等误操作,引起的数据丢失;一般可以通过基于数据库的时间点不完全恢复和基于表空间的时间点恢复(TSPITR)进行数据恢复;
但在Oracle 12c RMAN又推出了新的特性:基于表的时间点恢复,相比前两种方式,更加方便和更具有可操作性。
案例分析:
通过RMAN执行基于表时间点的恢复:
测试环境
11:56:54 SCOTT@ orcl> select * from tab
TNAME                                    TABTYPE  CLUSTERID
---------------------------------------- ------- ----------
T1                                       TABLE
SALGRADE                                 TABLE
EMPLOYEES                                TABLE
EMP1                                     TABLE
EMP                                      TABLE
DEPT                                     TABLE
BONUS                                    TABLE
 
7 rows selected.
11:56:54 SCOTT@ orcl>select count(1) from emp1;
  COUNT(1)
----------
       112
 
11:57:09 SCOTT@ orcl>select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T1                             USERS
EMP1                           USERS
SALGRADE                       USERS
BONUS                          USERS
DEPT                           USERS
EMPLOYEES                      LOW_COST_TBS1
EMP                            LOW_COST_TBS1
7 rows selected.
查看数据库备份信息
RMAN> list backup of database;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.70M      DISK        00:00:00     08-AUG-16     
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160808T115958
        Piece Name: /backup/orcl/ORCL_users_3.bk
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  6       Full 2874187    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.18G      DISK        00:01:27     08-AUG-16     
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20160808T120909
        Piece Name: /backup/orcl/ORCL_4.bak
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2875198    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_cr26h8nw_.dbf
  2       Full 2875198    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/adotbs1_01.dbf
  3       Full 2875198    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_cr26g2f6_.dbf
  4       Full 2875198    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_cr26k3t7_.dbf
  5       Full 2875198    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/low_cost_tbs01.dbf
  6       Full 2875198    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbf
 
RMAN> list backup of tablespace users;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.70M      DISK        00:00:00     08-AUG-16     
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160808T115958
        Piece Name: /backup/orcl/ORCL_users_3.bk
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  6       Full 2874187    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.18G      DISK        00:01:27     08-AUG-16     
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20160808T120909
        Piece Name: /backup/orcl/ORCL_4.bak
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  6       Full 2875198    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    1.70M      DISK        00:00:00     08-AUG-16     
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20160808T121206
        Piece Name: /backup/orcl/ORCL_users_6.bk
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  6       Full 2875369    08-AUG-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_crgy2c5t_.dbf
查看当前数据库 scn
以此时间点或scn作为表恢复的时间点(对于生产环境,需用logminer工具查询准确的时间点,本案例为测试环境)
12:13:02 SYS@ orcl>select timestamp_to_scn(current_timestamp) from v$database;
TIMESTAMP_TO_SCN(CURRENT_TIMESTAMP)
-----------------------------------
                            2875437
   
12:08:42 SCOTT@ orcl>select count(*) from emp1;
  COUNT(*)
----------
       112
SCOTT.EMP1被误删除
12:08:57 SCOTT@ orcl>drop table emp1 purge;
Table dropped.
通过rman执行表基于时间点的恢复操作
[oracle@enmoedu ~]$ mkdir /u01/aux
 
RMAN> recover table scott.emp1
2> until scn 2875437
3> auxiliary destination '/u01/aux';
Starting recover at 08-AUG-16
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 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

// Oracle自动建立用于恢复的auxiliary Instance
Creating automatic instance, with SID='fwsy'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=fwsy_pitr_ORCL
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=564M
processes=200
db_create_file_dest=/u01/aux
log_archive_dest_1='location=/u01/aux'
#No auxiliary parameter file used
 
starting up automatic instance ORCL
Oracle instance started
Total System Global Area     591396864 bytes
Fixed Size                     2927096 bytes
Variable Size                176162312 bytes
Database Buffers             406847488 bytes
Redo Buffers                   5459968 bytes
Automatic instance created

//通过源库的rman备份,restore数据库到auxiliary database
contents of Memory Script:
{
# set requested point in time
set until  scn 2875437;
# 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 08-AUG-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 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 /backup/orcl/ORCL_5.bak
channel ORA_AUX_DISK_1: piece handle=/backup/orcl/ORCL_5.bak tag=TAG20160808T120909
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/aux/ORCL/controlfile/o1_mf_ctj1v7x9_.ctl
Finished restore at 08-AUG-16
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 2875437;

// restore的表空间有system、sysaux、undo、users(表所在的表空间)
# 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 /u01/aux/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 08-AUG-16
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 /u01/aux/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /backup/orcl/ORCL_4.bak
channel ORA_AUX_DISK_1: piece handle=/backup/orcl/ORCL_4.bak tag=TAG20160808T120909
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 08-AUG-16
 
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=919340207 file name=/u01/aux/ORCL/datafile/o1_mf_system_ctj1vgr5_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=919340207 file name=/u01/aux/ORCL/datafile/o1_mf_undotbs1_ctj1vgsj_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=919340208 file name=/u01/aux/ORCL/datafile/o1_mf_sysaux_ctj1vgr8_.dbf
 
contents of Memory Script:
{
# set requested point in time
set until  scn 2875437;
# 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
 

联接地址:
分享按钮
作者:恩墨学院(李全新) 来源:恩墨学院(李全新)
关闭


Copyright 2005-2020 www.erpclub.org, all right reserved
Tel:+86-13501585121 (微信) Email:info@yok.com.cn QQ:374439400 QQ群:54909573
关于我们  |  免责申明  |  意见反馈  |  技术支持  |  积分规则  |  在线报名  |  友情链接  |  法律顾问  |  会员排名
深圳:深圳市南山区深南大道12069号海岸时代大厦东座1116室
sapclub

粤ICP备09103326号
sapclub