Oracle RMAN备份恢复测试步骤

1.1数据库测试环境

RMAN恢复可以有原机恢复和异机恢复，这里我们将分别模拟这2种恢复场景。

1.2RMAN备份

1.2.1 设置数据库归档

查看数据库是否运行在归档模式：

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled #未开启归档

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 117

Current log sequence 119

SQL>

备注：如果数据库已经开启归档，下面的操纵可以忽略。

如上所示未开启归档，可按下面方法开启数据库归档

SQL> shutdownimmediate #关闭数据库

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startupmount; #启动到mount状态

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Database mounted.

SQL> alterdatabase archivelog; #开启归档

Database altered.

SQL> alterdatabase open; #open数据库

Database altered.

SQL> altersystem set log_archive_dest_1='location=/data/CEBPM/archivelog'; #设置归档路径

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /data/CEBPM/archivelog

Oldest online log sequence 119

Next log sequence to archive 121

Current log sequence 121

SQL>

1.2.2 构建RMAN备份脚本

run{

allocate channel d1 type disk;

backup filesperset 10 full format'/data/backup/cebpm/fullback/FULLBAK_%d_%T_%s_%p' tag db_full_bakdatabase; #全备份

sql 'alter system archive log current'; #归档

backup filesperset 50 archivelog all deleteall input tag arch_bak format'/data/backup/cebpm/archivelog/ARCHBAK_%d_%T_%s_%p'; #归档备份

backup format '/data/backup/cebpm/ctlbackup/CTLBAK_%d_%T_%s_%p' tag ctl_bakcurrent controlfile; #控制文件备份

backup format '/data/backup/cebpm/ctlbackup/INITBAK_%d_%T_%s_%p' taginitpara_bak spfile; #参数文件备份

release channel d1;

}

1.2.3 执行备份

执行上面的构建好的rman备份脚本：

cebpm:/home/oracle@db1>rman target /

Recovery Manager: Release 11.2.0.4.0 -Production on Mon Jun 12 14:37:07 2017

connected to target database: CEBPM(DBID=3677012495)

RMAN> run{

allocate channel d1 type disk;

backup filesperset 10 full format'/data/backup/cebpm/fullback/FULLBAK_%d_%T_%s_%p' tag db_full_bak database;

sql 'alter system archive log current';

backup filesperset 50 archivelog all delete all input tag arch_bakformat '/data/backup/cebpm/archivelog/ARCHBAK_%d_%T_%s_%p';

backup format'/data/backup/cebpm/ctlbackup/CTLBAK_%d_%T_%s_%p' tag ctl_bak currentcontrolfile;

backup format'/data/backup/cebpm/ctlbackup/INITBAK_%d_%T_%s_%p' tag initpara_bak spfile;

release channel d1;

}

2> 3> 4> 5> 6> 7> 8>9>

using target database control file insteadof recovery catalog

allocated channel: d1

channel d1: SID=38 device type=DISK

Starting backup at 2017/06/12 14:37:16

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) inbackup set

input datafile file number=00001name=/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf

input datafile file number=00002name=/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf

input datafile file number=00003name=/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf

input datafile file number=00004name=/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf

channel d1: starting piece 1 at 2017/06/1214:37:17

channel d1: finished piece 1 at 2017/06/1214:39:15

piece handle=/data/backup/cebpm/fullback/FULLBAK_CEBPM_20170612_34_1tag=DB_FULL_BAK comment=NONE

channel d1: backup set complete, elapsedtime: 00:01:58

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) inbackup set

including current control file in backupset

including current SPFILE in backup set

channel d1: starting piece 1 at 2017/06/1214:39:17

channel d1: finished piece 1 at 2017/06/1214:39:18

piecehandle=/data/backup/cebpm/fullback/FULLBAK_CEBPM_20170612_35_1 tag=DB_FULL_BAKcomment=NONE

channel d1: backup set complete, elapsedtime: 00:00:01

Finished backup at 2017/06/12 14:39:18

sql statement: alter system archive logcurrent

Starting backup at 2017/06/12 14:39:25

current log archived

channel d1: starting archived log backupset

channel d1: specifying archived log(s) inbackup set

input archived log thread=1 sequence=24RECID=153 STAMP=946477368

input archived log thread=1 sequence=25RECID=154 STAMP=946477652

input archived log thread=1 sequence=26RECID=155 STAMP=946477652

input archived log thread=1 sequence=27RECID=156 STAMP=946478014

input archived log thread=1 sequence=28RECID=157 STAMP=946478365

input archived log thread=1 sequence=29RECID=158 STAMP=946478365

channel d1: starting piece 1 at 2017/06/1214:39:27

channel d1: finished piece 1 at 2017/06/1214:39:28

piecehandle=/data/backup/cebpm/archivelog/ARCHBAK_CEBPM_20170612_36_1 tag=ARCH_BAKcomment=NONE

channel d1: backup set complete, elapsedtime: 00:00:01

channel d1: deleting archived log(s)

archived log filename=/data/CEBPM/archivelog/1_24_945953743.arc RECID=153 STAMP=946477368

archived log filename=/data/CEBPM/archivelog/1_25_945953743.arc RECID=154 STAMP=946477652

archived log filename=/data/CEBPM/archivelog/1_26_945953743.arc RECID=155 STAMP=946477652

archived log filename=/data/CEBPM/archivelog/1_27_945953743.arc RECID=156 STAMP=946478014

archived log filename=/data/CEBPM/archivelog/1_28_945953743.arc RECID=157 STAMP=946478365

archived log filename=/data/CEBPM/archivelog/1_29_945953743.arc RECID=158 STAMP=946478365

Finished backup at 2017/06/12 14:39:29

Starting backup at 2017/06/12 14:39:31

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) inbackup set

including current control file in backupset

channel d1: starting piece 1 at 2017/06/1214:39:33

channel d1: finished piece 1 at 2017/06/1214:39:34

piecehandle=/data/backup/cebpm/ctlbackup/CTLBAK_CEBPM_20170612_37_1 tag=CTL_BAKcomment=NONE

channel d1: backup set complete, elapsedtime: 00:00:01

Finished backup at 2017/06/12 14:39:34

Starting backup at 2017/06/12 14:39:38

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) inbackup set

including current SPFILE in backup set

channel d1: starting piece 1 at 2017/06/1214:39:39

channel d1: finished piece 1 at 2017/06/1214:39:40

piecehandle=/data/backup/cebpm/ctlbackup/INITBAK_CEBPM_20170612_38_1tag=INITPARA_BAK comment=NONE

channel d1: backup set complete, elapsedtime: 00:00:02

Finished backup at 2017/06/12 14:39:41

released channel: d1

RMAN>

1.2.4 生成测试时间点

这里我们为了验证恢复之后数据的可靠性，我们在数据库备份完成之后，再进行一些操作，并记录下这些操作的时间，以便后续作为参考。

SQL> set time on;

15:32:02 SQL> create table mytest1(idnumber);

Table created.

15:32:29 SQL> insert into mytest1values(1);

1 row created.

15:32:46 SQL> insert into mytest1values(2);

1 row created.

15:33:04 SQL> commit;

Commit complete.

15:33:14 SQL> selectto_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

TIME

-------------------

2017-06-12 15:33:21 #将要恢复的时间点

15:33:21 SQL> insert into mytest1values(3);

1 row created.

15:33:40 SQL> commit;

Commit complete.

15:33:44 SQL> alter system archive logcurrent;

System altered.

15:33:55 SQL>

2.1RMAN恢复

2.1.1 基于时间点的原机恢复

1、这里为了测试，所以在恢复之前首先把数据库关闭

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

2、由于是原机恢复，这里直接将数据库启动到mount状态(忽略恢复spfile和controlfile步骤)

SQL> startup mount

ORACLE instancestarted.

Total SystemGlobal Area 688959488 bytes

Fixed Size 2256432 bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

Database mounted.

3、恢复数据文件

cebpm:/home/oracle@db1>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on MonJun 12 15:37:30 2017

connected to target database: CEBPM (DBID=3677012495, notopen)

RMAN>restore database;

Starting restore at 2017-06-12 15:37:37

using target database control file instead of recoverycatalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restorefrom backup set

channel ORA_DISK_1: restoring datafile 00001 to/data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf

channel ORA_DISK_1: restoring datafile 00002 to/data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf

channel ORA_DISK_1: restoring datafile 00003 to/data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf

channel ORA_DISK_1: restoring datafile 00004 to/data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf

channel ORA_DISK_1: reading from backup piece/data/backup/cebpm/fullback/FULLBAK_CEBPM_20170612_34_1

channel ORA_DISK_1: piecehandle=/data/backup/cebpm/fullback/FULLBAK_CEBPM_20170612_34_1 tag=DB_FULL_BAK

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time:00:01:15

Finishedrestore at 2017-06-12 15:38:57

RMAN>

4、应用归档日志：

RMAN> run{

sql 'altersession set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';

recover database until time '2017-06-1215:33:21';

}

2> 3> 4>

sql statement: alter session setNLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

Starting recover at 2017-06-12 15:40:34

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 28 is already ondisk as file /data/CEBPM/archivelog/1_28_945953743.arc

archived log for thread 1 with sequence 29 is already ondisk as file /data/CEBPM/archivelog/1_29_945953743.arc

archived log for thread 1 with sequence 30 is already ondisk as file /data/CEBPM/archivelog/1_30_945953743.arc

archived log for thread 1 with sequence 1 is already ondisk as file /data/CEBPM/archivelog/1_1_946480114.arc

archived log for thread 1 with sequence 2 is already ondisk as file /data/CEBPM/archivelog/1_2_946480114.arc

archived log for thread 1 with sequence 1 is already ondisk as file /data/CEBPM/archivelog/1_1_946481342.arc

archived log filename=/data/CEBPM/archivelog/1_28_945953743.arc thread=1 sequence=28

archived log file name=/data/CEBPM/archivelog/1_29_945953743.arcthread=1 sequence=29

archived log filename=/data/CEBPM/archivelog/1_30_945953743.arc thread=1 sequence=30

archived log filename=/data/CEBPM/archivelog/1_1_946480114.arc thread=1 sequence=1

archived log file name=/data/CEBPM/archivelog/1_2_946480114.arcthread=1 sequence=2

media recovery complete, elapsed time: 00:00:02

Finishedrecover at 2017-06-12 15:40:38

RMAN>

5、开启数据库

RMAN>alter database open resetlogs；

databaseopened

RMAN>

6、验证恢复数据，以上面的时间轴为参考点

SQL> select * frommytest1;

----------

SQL> select count(*)from mytest1;

COUNT(*)

----------

SQL>

如上可知基于时间点的恢复正常。

2..2 基于时间点的异机恢复

1、首先将原机的备份文件传到异机上

这里利用scp 命令进行传送，具体过程不再赘述，传送到异机/u01/backup 下。如下所示:

cebpm:/u01/backup@db1>ll

总用量 967524

-rw-r-----. 1 oracle dba 14145024 6月 12 16:34ARCHBAK_CEBPM_20170612_36_1

drwxr-xr-x. 2 oracle dba 4096 6月 12 16:48 archivelog

-rw-r-----. 1 oracle dba 9961472 6月 12 16:35CTLBAK_CEBPM_20170612_37_1

-rw-r-----. 1 oracle dba 956538880 6月 12 14:38 FULLBAK_CEBPM_20170612_34_1

-rw-r-----. 1 oracle dba 9994240 6月 12 14:39FULLBAK_CEBPM_20170612_35_1

-rw-r-----. 1 oracle dba 98304 6月 12 16:35INITBAK_CEBPM_20170612_38_1

注意这里一定要把归档也传送过来。

2、恢复参数文件

cebpm:/u01/backup@db2>rman target /

Recovery Manager: Release 11.2.0.4.0- Production on Tue Jun 13 08:34:58 2017

connected to target database (notstarted)

RMAN> setdbid 3677012495

executing command: SET DBID

RMAN> startupnomount

startup failed: ORA-01078: failurein processing system parameters

LRM-00109: could not open parameterfile '/u01/app/oracle/product/11.2.0/db_1/dbs/initcebpm.ora'

starting Oracle instance withoutparameter file for retrieval of spfile

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 281019272 bytes

Database Buffers 780140544 bytes

Redo Buffers 5517312 bytes

RMAN>restore spfile to '/u01/app/oracle/product/11.2.0/db_1/dbs/initcebpm.ora' from'/u01/backup/INITBAK_CEBPM_20170612_38_1';

Starting restore at 2017/06/1308:39:04

using target database control fileinstead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 devicetype=DISK

channel ORA_DISK_1: restoring spfilefrom AUTOBACKUP /u01/backup/INITBAK_CEBPM_20170612_38_1

channel ORA_DISK_1: SPFILE restorefrom AUTOBACKUP complete

Finished restore at 2017/06/13 08:39:08

RMAN>

注意：

1.在rman下即使没有参数文件，默认也会启动一个DUMMY实例，以便能够恢复参数文件。

备注：set dbid的唯一目的是使RMAN找到恢复参数文件和控制文件唯一确定的的备份文件。

3、对刚才恢复出来的参数文件稍作修改

ebpm.__java_pool_size=4194304

cebpm.__large_pool_size=8388608

cebpm.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment

cebpm.__pga_aggregate_target=167772160

cebpm.__sga_target=524288000

cebpm.__shared_io_pool_size=0

cebpm.__shared_pool_size=155189248

cebpm.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/cebpm/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/data/cdbpm/controlfile/control01.ctl'

*.db_create_file_dest='/data'

*.db_domain=''

*.db_name='cebpm'

*.db_recovery_file_dest='/data/cebpm'

*.db_recovery_file_dest_size=5368709120

*.db_unique_name='cebpm'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=cebpmXDB)'

*.log_archive_dest_1='location=/data/cdbpm/archivelog'

*.log_archive_dest_2=''

*.log_archive_dest_state_1='enable'

*.log_archive_format='%t_%s_%r.arc'

*.memory_target=691011584

*.open_cursors=300

*.processes=200

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1000

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

修改主要有2个地方：

1）参数文件里有些乱码需要删除

2）修改参数文件的相关目录文件

4、根据刚才创建的参数文件，创建相应的目录

cebpm:/data@db2>mkdir /data/cebpm

cebpm:/data@db2>mkdir -p /u01/app/oracle/admin/cebpm/adump

cebpm:/data@db2>mkdir -p /data/cebpm/controlfile/

cebpm:/data@db2>mkdir -p /data/cebpm/archivelog

5、用修改过的参数文件启动到nomount状态

SQL> shutdownabort;

ORACLE instance shut down.

SQL> startupnomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcebpm.ora';

ORACLE instance started.

Total System Global Area 688959488 bytes

Fixed Size 2256432bytes

Variable Size 566231504 bytes

Database Buffers 117440512 bytes

Redo Buffers 3031040 bytes

SQL> createspfile from pfile;

File created.

6、恢复控制文件

RMAN> restore controlfile to'/data/cebpm/controlfile/control01.ctl' from'/u01/backup/CTLBAK_CEBPM_20170612_37_1';

Startingrestore at 2017/06/13 08:53:05

usingtarget database control file instead of recovery catalog

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=18 device type=DISK

channelORA_DISK_1: restoring control file

channelORA_DISK_1: restore complete, elapsed time: 00:00:01

Finishedrestore at 2017/06/13 08:53:07

RMAN>

7、启动到mount状态

RMAN> alterdatabase mount;

using target database control fileinstead of recovery catalog

database mounted

8、查看schema

RMAN>report schema;

RMAN-06139: WARNING: control file isnot current for REPORT SCHEMA

Report of database schema fordatabase with db_unique_name CEBPM

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

---- -------- --------------------------- ------------------------

1 0 SYSTEM *** /data/CEBPM/datafile/o1_mf_system_dm1flxkw_.dbf

2 0 SYSAUX *** /data/CEBPM/datafile/o1_mf_sysaux_dm1fnw5v_.dbf

3 0 UNDOTBS1 *** /data/CEBPM/datafile/o1_mf_undotbs1_dm1foow9_.dbf

4 0 USERS *** /data/CEBPM/datafile/o1_mf_users_dm1fqcrp_.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

---- -------- ------------------------------- --------------------

1 20 TEMP 32767 /data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp

注意：上面的显示的数据文件是原数据库上的数据文件以及数据文件的路径。如果异机上没有相应的数据文件路径的话，有2种解决办法：1）创建和原机完全一样的数据文件路径2）重指向新的数据文件路径。下面介绍的就是第二种方法。

9、在新控制文件中注册数据文件备份和归档备份

RMAN> catalogstart with '/u01/backup';

Starting implicit crosscheck backupat 2017/06/13 08:57:31

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 devicetype=DISK

Crosschecked 6 objects

Finished implicit crosscheck backupat 2017/06/13 08:57:33

Starting implicit crosscheck copy at2017/06/13 08:57:33

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at2017/06/13 08:57:34

searching for all files in therecovery area

cataloging files...

no files cataloged

searching for all files that matchthe pattern /u01/backup

List of Files Unknown to theDatabase

=====================================

File Name:/u01/backup/ARCHBAK_CEBPM_20170612_36_1

File Name:/u01/backup/CTLBAK_CEBPM_20170612_37_1

File Name:/u01/backup/archivelog/1_2_946480114.arc

File Name:/u01/backup/archivelog/1_28_945953743.arc

File Name: /u01/backup/archivelog/1_31_945953743.arc

File Name:/u01/backup/archivelog/1_1_946481342.arc

File Name:/u01/backup/archivelog/1_1_946480114.arc

File Name:/u01/backup/archivelog/1_29_945953743.arc

File Name:/u01/backup/archivelog/1_2_946481342.arc

File Name:/u01/backup/archivelog/1_32_945953743.arc

File Name:/u01/backup/archivelog/1_3_946480114.arc

File Name:/u01/backup/archivelog/1_30_945953743.arc

File Name:/u01/backup/FULLBAK_CEBPM_20170612_35_1

File Name:/u01/backup/INITBAK_CEBPM_20170612_38_1

File Name:/u01/backup/FULLBAK_CEBPM_20170612_34_1

Do you really want to catalog theabove files (enter YES or NO)? y

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name:/u01/backup/ARCHBAK_CEBPM_20170612_36_1

File Name:/u01/backup/CTLBAK_CEBPM_20170612_37_1

File Name:/u01/backup/archivelog/1_2_946480114.arc

File Name:/u01/backup/archivelog/1_28_945953743.arc

File Name:/u01/backup/archivelog/1_31_945953743.arc

File Name:/u01/backup/archivelog/1_1_946481342.arc

File Name:/u01/backup/archivelog/1_1_946480114.arc

File Name:/u01/backup/archivelog/1_29_945953743.arc

File Name:/u01/backup/archivelog/1_2_946481342.arc

File Name:/u01/backup/archivelog/1_32_945953743.arc

File Name: /u01/backup/archivelog/1_3_946480114.arc

File Name:/u01/backup/archivelog/1_30_945953743.arc

File Name:/u01/backup/FULLBAK_CEBPM_20170612_35_1

File Name:/u01/backup/INITBAK_CEBPM_20170612_38_1

File Name: /u01/backup/FULLBAK_CEBPM_20170612_34_1

10、恢复数据文件

具体执行脚本如下：

run{

set newnamefor datafile 1 to '/data/cebpm/datafile/system01.db';

setnewname for datafile 2 to '/data/cebpm/datafile/sysaux01.dbf';

setnewname for datafile 3 to '/data/cebpm/datafile/undotbs01.dbf';

setnewname for datafile 4 to '/data/cebpm/datafile/users01.dbf';

restoredatabase;

switchdatafile all;

}

这里面set newname 是将原库的数据文件的路径重新指向到异机上新的路径，

注意：异机上要首先创建/data/cebpm/datafile 这个路径，如下

cebpm:/data/cebpm@db2>mkdir -p/data/cebpm/datafile/

具体执行结果如下：

RMAN> run{

set newname for datafile 1 to '/data/cebpm/datafile/system01.db';

set newname for datafile 2 to '/data/cebpm/datafile/sysaux01.dbf';

set newname for datafile 3 to '/data/cebpm/datafile/undotbs01.dbf';

set newname for datafile 4 to '/data/cebpm/datafi2> le/users01.dbf';

restore database;

switch datafile all;

}3> 4> 5> 6> 7> 8>

executing command: SET NEWNAME

Starting restore at 2017/06/13 09:13:03

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backupset

channel ORA_DISK_1: restoring datafile 00001 to/data/cebpm/datafile/system01.db

channel ORA_DISK_1: restoring datafile 00002 to/data/cebpm/datafile/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to/data/cebpm/datafile/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to/data/cebpm/datafile/users01.dbf

channel ORA_DISK_1: reading from backup piece/u01/backup/FULLBAK_CEBPM_20170612_34_1

channel ORA_DISK_1: piecehandle=/u01/backup/FULLBAK_CEBPM_20170612_34_1 tag=DB_FULL_BAK

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 2017/06/13 09:14:20

datafile 1 switched to datafile copy

input datafile copy RECID=10 STAMP=946545261 filename=/data/cebpm/datafile/system01.db

datafile 2 switched to datafile copy

input datafile copy RECID=11 STAMP=946545261 filename=/data/cebpm/datafile/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=12 STAMP=946545261 filename=/data/cebpm/datafile/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=13 STAMP=946545261 filename=/data/cebpm/datafile/users01.dbf

RMAN>

11、应用归档日志

RMAN> run{

sql 'altersession set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"';

recover database until time '2017-06-1215:33:21';

}2> 3> 4>

sql statement: alter session setNLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

Starting recover at 2017/06/1309:17:24

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 withsequence 28 is already on disk as file/u01/backup/archivelog/1_28_945953743.arc

archived log for thread 1 withsequence 29 is already on disk as file /u01/backup/archivelog/1_29_945953743.arc

archived log for thread 1 withsequence 30 is already on disk as file/u01/backup/archivelog/1_30_945953743.arc

archived log for thread 1 withsequence 1 is already on disk as file /u01/backup/archivelog/1_1_946480114.arc

archived log for thread 1 withsequence 2 is already on disk as file /u01/backup/archivelog/1_2_946480114.arc

archived log for thread 1 withsequence 1 is already on disk as file /u01/backup/archivelog/1_1_946481342.arc

archived log filename=/u01/backup/archivelog/1_28_945953743.arc thread=1 sequence=28

archived log filename=/u01/backup/archivelog/1_29_945953743.arc thread=1 sequence=29

archived log filename=/u01/backup/archivelog/1_30_945953743.arc thread=1 sequence=30

archived log filename=/u01/backup/archivelog/1_1_946480114.arc thread=1 sequence=1

archived log filename=/u01/backup/archivelog/1_2_946480114.arc thread=1 sequence=2

archived log filename=/u01/backup/archivelog/1_1_946481342.arc thread=1 sequence=1

media recovery complete, elapsedtime: 00:00:03

Finished recover at 2017/06/1309:17:30

RMAN>

12、重定向在线归档日志文件

首先查看归档日志文件

SQL> select member fromv$logfile;

MEMBER

--------------------------------------------------------------------------------

/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log

/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log

/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log

SQL>

上面的显示的原机上的归档日志文件路径，现在重定向到异机上新的位置，具体操作如下：

SQL> alter database rename file'/data/CEBPM/onlinelog/o1_mf_1_dm1fljft_.log' to '/data/cebpm/onlinelog/redo01.log';

Database altered.

SQL> alter database rename file'/data/CEBPM/onlinelog/o1_mf_2_dm1fll24_.log' to'/data/cebpm/onlinelog/redo02.log' ;

Database altered.

SQL> alter database rename file'/data/CEBPM/onlinelog/o1_mf_3_dm1flmld_.log' to'/data/cebpm/onlinelog/redo03.log';

Database altered.

再次查看：

SQL> select member fromv$logfile;

MEMBER

--------------------------------------------------------------------------------

/data/cebpm/onlinelog/redo01.log

/data/cebpm/onlinelog/redo02.log

/data/cebpm/onlinelog/redo03.log

SQL>

13、重定向临时文件，方法同上

SQL>select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp

SQL>

重定向到新的路径，操作如下：

SQL> alter database rename file '/data/CEBPM/datafile/o1_mf_temp_dm1fp4bs_.tmp'to '/data/cebpm/datafile/temp01.dbf';

Databasealtered.

SQL>

再次查看：

SQL>select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/data/cebpm/datafile/temp01.dbf

SQL>

14、打开数据库

RMAN> alter database open resetlogs;

database opened

15、验证数据

SQL>select * from mytest1;

----------

SQL>select count(*) from mytest1;

COUNT(*)

----------

如上所知，基于时间点的异机恢复数据没有丢失。

2 RMAN常用命令

备份命令：

RMAN> backup database format '.../%d_%U'; --备份数据库并指定备份路径

RMAN> backup database; --备份数据库

RMAN> backup database include current controlfile plus archivelog delete allinput; --备份数据文件、控制文件、归档日志

RMAN> backup incremental level 0 database; --差异增量备份数据库

RMAN> backup incremental level 0 cumulative database; --累积增量备份数据库

RMAN> backup as compressed backupset database; --压缩备份数据库

RMAN> backup database include current controlfile; --备份数据库并备份控制文件

RMAN> backup tablespace users; --备份表空间

RMAN> backup datafile n; --备份数据文件

RMAN> backup datafiel '/opt/oracle/oradata/orcl/users01.dbf'; --备份数据文件

RMAN> backup current controlfile; --备份控制文件

RMAN> backup current controlfile plus archivelog; --备份控制文件并归档然后备份归档日志

RMAN> backup archivelog all; --备份归档日志

RMAN> backup archivelog all delete all input; --备份归档日志并删除

RMAN> backup spfile; --备份参数文件

RMAN> backup backupset all; --备份全部备份集

RMAN> backup backupset n; --备份指定备份集

还原、恢复命令：

RMAN> restore database; --还原数据库

RMAN> restore tablespace users; --还原表空间

RMAN> restore datafile n; --还原数据文件

RMAN> restore archivelog sequence between 10 and 20; --还原归档日志

RMAN> restore controlfile from autobackup; --还原控制文件

RMAN> restore spfile to '/tmp/spfile.ora' from autobackup; --还原参数文件

RMAN> recover database; --恢复数据库

RMAN> recover tablespace users; --恢复表空间

RMAN> recover datafile n; --恢复数据文件

RMAN> restore validate database; --验证数据库可恢复性

RMAN> restore validate controlfile; --验证控制文件可恢复性

RMAN> restore validate spfile; --验证参数文件可恢复性

查看备份集命令：

RMAN> list backup; --列出数据库中所有的备份集

RMAN> list backup of database; --查看数据库备份集

RMAN> list backup of tablespace users; --查看表空间备份集

RMAN> list backup of datafile n; --查看备份的数据文件

RMAN> list backup of controlfile; --查看控制文件备份集

RMAN> list backup of archivelog all; --查看归档日志备份集

RMAN> list archivelog all; --查看当前所有归档日志

RMAN> list expired backup; --列出所有无效备份

管理备份集命令：

RMAN> crosscheck backup; --检查所有备份集

RMAN> crosscheck archivelog all; --检查所有归档文件

RMAN> delete [noprompt] obsolete; --删除过期备份

RMAN> delete expired backup; --删除无效备份

RMAN> delete expired archivelog all; --删除所有无效归档文件

RMAN> delete backupset 1; --删除指定备份

RMAN> delete backup; --删除所有备份

RMAN> change backupset 3 unavailable; --更改备份集3为无效

RMAN> change backupset 3 available; --更改备份集3为有效

RMAN> change backup of controlfile unavailable; --更改控制文件为无效

RMAN> change backup of controlfile available; --更改控制文件为有效

RMAN> report schema; --查看数据库备份结构

RMAN> report need backup; --查看所以需要备份的文件

RMAN> report need backup tablespace system; --查看指定表空间是否需要备份

RMAN> report obsolete; --查看过期备份

仅供参考

最后编辑于：2019.03.12 11:28:20

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,839评论 6赞 482
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,543评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 153,116评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,371评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,384评论 5赞 374
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,111评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,416评论 3赞 400
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,053评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,558评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,007评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,117评论 1赞 334
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,756评论 4赞 324
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,324评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,315评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,539评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,578评论 2赞 355
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,877评论 2赞 345

Oracle RMAN备份恢复测试步骤

推荐阅读更多精彩内容