oracle rman不完全恢复测试（删除用户）

2019独角兽企业重金招聘Python工程师标准>>>

需要提前做好rman备份 查看当前的日志如下：

SQL> select sequence#,status from v$log;

SEQUENCE# STATUS

---

 1 INACTIVE
 2 INACTIVE
 3 CURRENT

当前的用户如下 SQL> select * from all_users;

USERNAME USER_ID CREATED

---

TEST 85 17-JAN-18 SCOTT 84 15-AUG-09 OWBSYS_AUDIT 83 15-AUG-09 OWBSYS 79 15-AUG-09 APEX_030200 78 15-AUG-09 APEX_PUBLIC_USER 76 15-AUG-09 FLOWS_FILES 75 15-AUG-09 MGMT_VIEW 74 15-AUG-09 SYSMAN 72 15-AUG-09 SPATIAL_CSW_ADMIN_USR 70 15-AUG-09 SPATIAL_WFS_ADMIN_USR 67 15-AUG-09

USERNAME USER_ID CREATED

---

MDDATA 65 15-AUG-09 MDSYS 57 15-AUG-09 SI_INFORMTN_SCHEMA 56 15-AUG-09 ORDPLUGINS 55 15-AUG-09 ORDDATA 54 15-AUG-09 ORDSYS 53 15-AUG-09 OLAPSYS 61 15-AUG-09 ANONYMOUS 46 15-AUG-09 XDB 45 15-AUG-09 CTXSYS 43 15-AUG-09 EXFSYS 42 15-AUG-09

USERNAME USER_ID CREATED

---

XS$NULL 2147483638 15-AUG-09 WMSYS 32 15-AUG-09 APPQOSSYS 31 15-AUG-09 DBSNMP 30 15-AUG-09 ORACLE_OCM 21 15-AUG-09 DIP 14 15-AUG-09 OUTLN 9 15-AUG-09 SYSTEM 5 15-AUG-09 SYS 0 15-AUG-09

31 rows selected.

查看当前的时间点

SQL> select systimestamp from dual;

SYSTIMESTAMP

17-JAN-18 03.14.04.128271 PM +08:00

删除用户

SQL> drop user test cascade;

User dropped.

测试用户连接 SQL> conn test Enter password: ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.

关闭数据库 SQL> shutdown immediate;

通过rman登录 [oracle@primary primary]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jan 17 15:31:37 2018

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

connected to target database (not started)

启动数据库到mount模式 RMAN> startup mount;

Oracle instance started database mounted

Total System Global Area 835104768 bytes

Fixed Size 2217952 bytes Variable Size 545261600 bytes Database Buffers 285212672 bytes Redo Buffers 2412544 bytes 根据rman备份自动完成恢复 RMAN> run 2> { 3> set until time "to_date('2018-01-17:15:14:04','YYYY-MM-DD HH24:MI:SS')"; 4> restore database; 5> recover database; 6> }

executing command: SET until clause

Starting restore at 17-JAN-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK flashing back control file to SCN 1101055

channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/primary/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/primary/oracle_test.dbf channel ORA_DISK_1: reading from backup piece /u01/rman/dx_lev0_20180117_22_1.bak channel ORA_DISK_1: piece handle=/u01/rman/dx_lev0_20180117_22_1.bak tag=LEVEL 0 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/primary/system01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/primary/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/primary/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/rman/dx_lev0_20180117_21_1.bak channel ORA_DISK_1: piece handle=/u01/rman/dx_lev0_20180117_21_1.bak tag=LEVEL 0 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:06 Finished restore at 17-JAN-18

Starting recover at 17-JAN-18 using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/oradata/archivelog/1_19_953687353.dbf archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/oradata/archivelog/1_20_953687353.dbf archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/oradata/archivelog/1_21_953687353.dbf archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/oradata/archivelog/1_22_953687353.dbf archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/primary/redo01.log archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/primary/redo02.log archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/primary/redo03.log archived log file name=/u01/app/oracle/oradata/archivelog/1_19_953687353.dbf thread=1 sequence=19 archived log file name=/u01/app/oracle/oradata/archivelog/1_20_953687353.dbf thread=1 sequence=20 archived log file name=/u01/app/oracle/oradata/archivelog/1_21_953687353.dbf thread=1 sequence=21 archived log file name=/u01/app/oracle/oradata/archivelog/1_22_953687353.dbf thread=1 sequence=22 media recovery complete, elapsed time: 00:00:10 Finished recover at 17-JAN-18

恢复完成之后以resetlogs方式打开数据库

RMAN> alter database open resetlogs;

database opened

连接数据库检查数据是否恢复 SQL> select status from v$instance;

STATUS

OPEN

SQL> select * from test.test;

ID NAME

---

 1 tom
 2 zeng
 3 wang

测试完成