RMAN 基于时间点的原机和异机恢复

来源：互联网发布：深圳资深淘宝客服编辑：程序博客网时间：2024/06/14 13:35

1 Oracle RMAN备份恢复测试步骤

1.1数据库测试环境

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

主机1

主机2

操作系统

Centos6.5 64位

主机名

db1

db2

192.168.1.164

192.168.1.120

数据库软件版本

oracle 11.2.0.4

ORACLE_BASE

/u01/app/oracle

ORACLE_HOME

/u01/app/oracle/product/11.2.0/db_1

ORACLE_SID

cebpm

归档

开启

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;   --查看过期备份

阅读全文

0 0