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

小灰灰 2023-10-04 22:31 90阅读 0赞

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

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

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

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

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

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

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';\}