签到成功

知道了

CNDBA社区CNDBA社区

oracle 19c使用rman 恢复单个表

2022-04-05 14:05 3047 0 原创 oracle
作者： hbhe0316
本文链接：http://www.cndba.cn/hbhe0316/article/107908

1.查看数据库版本，我的测试环境为19.14http://www.cndba.cn/hbhe0316/article/107908

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

2.检查并启动归档模式

http://www.cndba.cn/hbhe0316/article/107908
http://www.cndba.cn/hbhe0316/article/107908 

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archivelog
Oldest online log sequence     155
Next log sequence to archive   157
Current log sequence           157

3.创建测试数据

[oracle@db02 ~]$ sqlplus system/wwwwww@fas_dls

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 5 11:20:50 2022
Version 19.14.0.0.0

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

Last Successful login time: Tue Apr 05 2022 11:19:53 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> select name from v$dbfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/hbhe/system01.dbf
/u01/app/oracle/oradata/ORCL/hbhe/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/hbhe/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/hbhe/users01.dbf
/oradata/bftbs01.dbf
/oradata/APPRPTMFGMMCS_SCHE_DATA_TBS.dbf
/oradata/APPRPTFASDLS_SCHE_DATA_TBS.dbf
/oradata/rmants.dbf

8 rows selected.

create tablespace hhbtbs datafile '/oradata/hbhetbs01.dbf' size 10M autoextend on;
create user hbhe identified by wwwwww default tablespace hhbtbs;
grant connect,resource,dba to hbhe;
conn hbhe/wwwwww@fas_dls

create table t1 as select * from dba_objects;
insert into t1 select * from t1;
commit

4.全库备份http://www.cndba.cn/hbhe0316/article/107908

mkdir /backup/rman -p
chown oracle.oinstall /backup -R

su - oracle
rman  target /
run
{
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    backup database tag 'full' format '/backup/rman/%d_full_%T_%U.bak';
    sql 'alter system archive log current';
    backup archivelog all tag 'arch' format '/backup/rman/%d_arch_%T_%U.bak';
    backup current controlfile tag 'ctl' format '/backup/rman/%d_ctl_%T_%U.bak';
    release channel c1;
    release channel c2;
}

查看备份情况http://www.cndba.cn/hbhe0316/article/107908

list backup of database;

[root@db02 ~]# cd /backup/rman/
[root@db02 rman]# ll
total 7105896
-rw-r----- 1 oracle oinstall  336525312 Apr  5 12:41 ORCL_arch_20220405_320q6bvh_98_1_1.bak
-rw-r----- 1 oracle oinstall  328299520 Apr  5 12:41 ORCL_arch_20220405_330q6bvh_99_1_1.bak
-rw-r----- 1 oracle oinstall   18841600 Apr  5 12:41 ORCL_ctl_20220405_340q6c01_100_1_1.bak
-rw-r----- 1 oracle oinstall 2860498944 Apr  5 12:40 ORCL_full_20220405_2r0q6bo3_91_1_1.bak
-rw-r----- 1 oracle oinstall 1832140800 Apr  5 12:39 ORCL_full_20220405_2s0q6bo4_92_1_1.bak
-rw-r----- 1 oracle oinstall   82624512 Apr  5 12:40 ORCL_full_20220405_2t0q6bte_93_1_1.bak
-rw-r----- 1 oracle oinstall  980656128 Apr  5 12:40 ORCL_full_20220405_2u0q6btf_94_1_1.bak
-rw-r----- 1 oracle oinstall  439656448 Apr  5 12:40 ORCL_full_20220405_2v0q6bu0_95_1_1.bak
-rw-r----- 1 oracle oinstall  397189120 Apr  5 12:41 ORCL_full_20220405_300q6buu_96_1_1.bak

5.查看当前SCN 等信息http://www.cndba.cn/hbhe0316/article/107908

SQL> show con_name user

CON_NAME
------------------------------
HBHE
USER is "HBHE"
SQL>  select count(*) from t1;

  COUNT(*)
----------
   73435

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

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')
---------------------------------------------------------
2022-04-05 12:47:06

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    8518848

6.模拟误删除操作http://www.cndba.cn/hbhe0316/article/107908http://www.cndba.cn/hbhe0316/article/107908

SQL>  delete t1 where object_id>10;

73425 rows deleted.

SQL> commit;

Commit complete.

SQL>  select count(*) from t1;

  COUNT(*)
----------
       10

针对 delete 级别的误删除，在时间和 undo 允许的情况下，可以通过闪回将数据迅速找回http://www.cndba.cn/hbhe0316/article/107908

SQL> select count(1) from t1 as of timestamp systimestamp - interval '10' minute;

  COUNT(1)
----------
     73435

SQL> insert into t1 select * from t1 as of timestamp systimestamp - interval '10' minute where object_id > 10;

73425 rows created.

SQL> select count(*) from t1;

  COUNT(*)
----------
     73435

7.使用rman 备份恢复 t1 表

http://www.cndba.cn/hbhe0316/article/107908

1 创建辅助数据库恢复目录 /auxiliary/recover ，创建 expdp 导出目录 /auxiliary/dumpfiles

[root@db02 rman]#  mkdir /auxiliary/{recover,dumpfiles} -p
[root@db02 rman]#  chown oracle.oinstall /auxiliary -R

[root@db02 rman]# su - oracle
[oracle@db02 ~]$ rman target /

run{
RECOVER TABLE hbhe.T1 OF PLUGGABLE DATABASE HBHE
UNTIL SCN 8518848
AUXILIARY DESTINATION '/auxiliary/recover'
DATAPUMP DESTINATION '/auxiliary/dumpfiles'
DUMP FILE 't1_scn_8518848.dmp'
notableimport;
}

[oracle@db02 dumpfiles]$ ll /auxiliary/dumpfiles/
total 10104
-rw-r----- 1 oracle oinstall 10346496 Apr  5 13:20 t1_scn_8518848.dmp


SQL> create directory dumpdir as '/oradata/dumpfiles';

Directory created.

[oracle@db02 dumpfiles]$ impdp system/wwwwww@fas_dls DIRECTORY=DUMPDIR DUMPFILE=t1_scn_8518848.dmp remap_table=hbhe.t1:t1_tmp

Import: Release 19.0.0.0.0 - Production on Tue Apr 5 13:53:27 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@fas_dls DIRECTORY=DUMPDIR DUMPFILE=t1_scn_8518848.dmp remap_table=hbhe.t1:t1_tmp 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HBHE"."T1_TMP"                             9.675 MB   73435 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Apr 5 13:53:33 2022 elapsed 0 00:00:05


SQL> select count(*) from hbhe.t1_tmp;

  COUNT(*)
----------
     73435


SQL> insert into hbhe.t1  select * from hbhe.t1_tmp where object_id > 10;

73425 rows created.


SQL> select count(1) from hbhe.t1;

  COUNT(1)
----------
     73435

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
hbhe0316

hbhe0316

关注

1.只有承认无知，才能装下新的东西； 2.进步来自一点点滴滴的积累； 3.广博让你更优秀，而专业让你无法替代； 4.挫折和失败能够转换为一种财富。

  • 889
    原创
  • 1
    翻译
  • 13
    转载
  • 24
    评论
  • 访问：1039244次
  • 积分：1523
  • 等级：核心会员
  • 排名：第6名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ