简单实战一下oracle rman恢复到指定时间点

Oracle RMAN是数据库备份和恢复的主要工具之一，可以将数据库恢复到指定时间点。

总体来说，将Oracle数据库恢复到指定时间点需要进行多个步骤，包括确认恢复时间点、准备恢复环境、启动RMAN、开始恢复和恢复数据库等。维护人员需要熟悉这些步骤并按照要求执行，以确保数据的可用性和完整性。

下面是实战内容：

1.查看是否在归档模式，如果不是，修改为归档模式

--

如果非归档模式：

shutdown immediate;

startup mount;

alter database archivelog;

--

[oracle@localhost archivelog]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 16 18:05:17 2012

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

SQL> conn system/zhangle as sysdba;
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL>

2.改为归档模式之后，执行rman备份，rman要比其他的热备方式方便的多

[oracle@localhost ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 16 17:15:50 2012

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

connected to target database: YANGKAI (DBID=406290488)

RMAN>list backup of database;

using target database control file instead of recovery catalog

RMAN>

RMAN> backup database;

Starting backup at 16-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracleinstall/oracle/oradata/yangkai/system01.dbf
input datafile fno=00003 name=/oracleinstall/oracle/oradata/yangkai/sysaux01.dbf
input datafile fno=00002 name=/oracleinstall/oracle/oradata/yangkai/undotbs01.dbf
input datafile fno=00004 name=/oracleinstall/oracle/oradata/yangkai/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-AUG-12
channel ORA_DISK_1: finished piece 1 at 16-AUG-12
piece handle=/oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp tag=TAG20120816T172507 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:35
Finished backup at 16-AUG-12

Starting Control File and SPFILE Autobackup at 16-AUG-12
piece handle=/oracleinstall/rmanyk/c-406290488-20120816-03 comment=NONE
Finished Control File and SPFILE Autobackup at 16-AUG-12

RMAN> quit

3.测试，创建表，插入数据，删除数据

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 16 17:32:04 2012

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

SQL> conn system/zhangle;
Connected.
SQL> select * from test816;
select * from test816
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-01031: insufficient privileges
SQL> conn system/zhangle as sysdba;
Connected.
SQL> select * from test816;

PPID
----------
1
5

SQL> set time on;
17:33:33 SQL> insert into test816 values(6);

1 row created.

17:34:32 SQL> delete from test816 where ppid=5;

1 row deleted.

17:34:53 SQL> commit;

Commit complete.

17:34:56 SQL> exit

4.在使用RMAN完成基于时间点的不完全恢复之前，最好对现场做一个备份，我们这里只需要备份数据库的控制文件和日志文件即可。当恢复结束后不满足我们要求时，可以恢复控制文件和日志文件后重新进行恢复。

[oracle@localhost ~]$ rman target/

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 16 17:35:58 2012

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

connected to target database: YANGKAI (DBID=406290488)

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

-----------------------另起一个窗口，执行：------------

[oracle@localhost 2012_08_16]$ cd ..
[oracle@localhost archivelog]$ ls
2012_08_16
[oracle@localhost archivelog]$ cp 2012_08_16/o1_mf_1_3_82sgxl1z_.arc ./
[oracle@localhost archivelog]$ ll
total 3652
drwxr-x--- 2 oracle oinstall 4096 Aug 16 17:21 2012_08_16
-rw-r----- 1 oracle oinstall 3729408 Aug 16 17:37 o1_mf_1_3_82sgxl1z_.arc
[oracle@localhost archivelog]$
[oracle@localhost oradata]$ cp yangkai/*ctl ./
[oracle@localhost oradata]$ ll
total 20728
-rw-r----- 1 oracle oinstall 7061504 Aug 16 17:39 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 16 17:39 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 16 17:39 control03.ctl
drwxr-x--- 2 oracle oinstall 4096 Aug 15 02:44 yangkai

--------------------------继续rman窗口执行-------------

RMAN> startup;

connected to target database (not started)
Oracle instance started
database mounted
database opened

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes
Variable Size 113247824 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes

RMAN> @/home/oracle/testrman.rman

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
5> set until time='2012-08-16 17:30:00';
6> restore database;
7> recover database;
8> sql 'alter database open resetlog';}
allocated channel: c1
channel c1: sid=142 devtype=DISK

allocated channel: c2
channel c2: sid=141 devtype=DISK

sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause

Starting restore at 16-AUG-12

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracleinstall/oracle/oradata/yangkai/system01.dbf
restoring datafile 00002 to /oracleinstall/oracle/oradata/yangkai/undotbs01.dbf
restoring datafile 00003 to /oracleinstall/oracle/oradata/yangkai/sysaux01.dbf
restoring datafile 00004 to /oracleinstall/oracle/oradata/yangkai/users01.dbf
channel c1: reading from backup piece /oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/16/2012 17:41:01
ORA-19870: error reading backup piece /oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 1

RMAN> **end-of-file**

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN>

-----------------------------解决方案--------------

执行rman，必须在mount状态。

startup的过程是：1.启动实例；2.加载数据库；3打开数据库。--startup mount/nomount

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes
Variable Size 117442128 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes

RMAN> @/home/oracle/testrman.rman

RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
5> set until time='2012-08-16 17:30:00';
6> restore database;
7> recover database;
8> sql 'alter database open resetlog';}
allocated channel: c1
channel c1: sid=157 devtype=DISK

allocated channel: c2
channel c2: sid=154 devtype=DISK

sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause

Starting restore at 16-AUG-12

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracleinstall/oracle/oradata/yangkai/system01.dbf
restoring datafile 00002 to /oracleinstall/oracle/oradata/yangkai/undotbs01.dbf
restoring datafile 00003 to /oracleinstall/oracle/oradata/yangkai/sysaux01.dbf
restoring datafile 00004 to /oracleinstall/oracle/oradata/yangkai/users01.dbf
channel c1: reading from backup piece /oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp
channel c1: restored backup piece 1
piece handle=/oracleinstall/oracle/flash_recovery_area/YANGKAI/backupset/2012_08_16/o1_mf_nnndf_TAG20120816T172507_82sh3qdk_.bkp tag=TAG20120816T172507
channel c1: restore complete, elapsed time: 00:00:46
Finished restore at 16-AUG-12

Starting recover at 16-AUG-12

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 16-AUG-12

sql statement: alter database open resetlog
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 08/16/2012 17:50:38
RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlog
ORA-02288: invalid OPEN mode

RMAN> **end-of-file**

RMAN>

--注释：脚本中有错误，8> sql 'alter database open resetlog';}，不能这样执行。。。

RMAN> sql 'alter database open restlogs';

sql statement: alter database open restlogs
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 08/16/2012 17:53:47
RMAN-11003: failure during parse/execution of SQL statement: alter database open restlogs
ORA-02288: invalid OPEN mode

RMAN> alter database open resetlogs;

database opened

RMAN> quit

Recovery Manager complete.
[oracle@localhost ~]$

--注释：在rman下可以直接执行alter语句

5.验证恢复结果

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 16 17:58:13 2012

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

SQL> conn system/zhangle as sysdba;
Connected.
SQL> select * from test816;

PPID
----------
1
5

SQL>

6.成功！

[oracle@localhost 2012_08_16]$ cat /home/oracle/testrman.rman
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time='2012-08-16 17:30:00';
restore database;
recover database;
sql 'alter database open resetlog';}