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

绝地灬酷狼 2022-10-02 06:58 153阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

需要提前做好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] primary\]$ rman target /

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

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

测试完成

转载于:https://my.oschina.net/vfastsuccess/blog/1608454

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/2991473d393747a6934b310e24b6bc73.png
[primary]: https://my.oschina.net/primary