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

2023-12-07 04:58
文章标签 恢复 时间 rman 异机 原机

本文主要是介绍RMAN 基于时间点的原机和异机恢复,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

 

1 Oracle RMAN备份恢复测试步骤

1.1数据库测试环境

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

 

 

主机1

主机2

操作系统

Centos6.5 64

Centos6.5 64

主机名

db1

db2

IP

192.168.1.164

192.168.1.120

数据库软件版本

oracle 11.2.0.4   

oracle 11.2.0.4

ORACLE_BASE

/u01/app/oracle

/u01/app/oracle

ORACLE_HOME

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

/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

 

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

 

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状态(忽略恢复spfilecontrolfile步骤)

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

 

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

 

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;

 

          ID

----------

           1

           2

 

SQL> select count(*)from mytest1;

 

  COUNT(*)

----------

           2

 

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

 

Copyright (c) 1982, 2011, Oracleand/or its affiliates.  All rightsreserved.

 

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

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

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;

 

         ID

----------

          1

          2

 

SQL>select count(*) from mytest1;

 

  COUNT(*)

----------

          2

 

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

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

这篇关于RMAN 基于时间点的原机和异机恢复的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/464546

相关文章

Java实现时间与字符串互相转换详解

《Java实现时间与字符串互相转换详解》这篇文章主要为大家详细介绍了Java中实现时间与字符串互相转换的相关方法,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录一、日期格式化为字符串(一)使用预定义格式(二)自定义格式二、字符串解析为日期(一)解析ISO格式字符串(二)解析自定义

Java时间轮调度算法的代码实现

《Java时间轮调度算法的代码实现》时间轮是一种高效的定时调度算法,主要用于管理延时任务或周期性任务,它通过一个环形数组(时间轮)和指针来实现,将大量定时任务分摊到固定的时间槽中,极大地降低了时间复杂... 目录1、简述2、时间轮的原理3. 时间轮的实现步骤3.1 定义时间槽3.2 定义时间轮3.3 使用时

使用Python实现网络设备配置备份与恢复

《使用Python实现网络设备配置备份与恢复》网络设备配置备份与恢复在网络安全管理中起着至关重要的作用,本文为大家介绍了如何通过Python实现网络设备配置备份与恢复,需要的可以参考下... 目录一、网络设备配置备份与恢复的概念与重要性二、网络设备配置备份与恢复的分类三、python网络设备配置备份与恢复实

MySQL使用binlog2sql工具实现在线恢复数据功能

《MySQL使用binlog2sql工具实现在线恢复数据功能》binlog2sql是大众点评开源的一款用于解析MySQLbinlog的工具,根据不同选项,可以得到原始SQL、回滚SQL等,下面我们就来... 目录背景目标步骤准备工作恢复数据结果验证结论背景生产数据库执行 SQL 脚本,一般会经过正规的审批

Python如何获取域名的SSL证书信息和到期时间

《Python如何获取域名的SSL证书信息和到期时间》在当今互联网时代,SSL证书的重要性不言而喻,它不仅为用户提供了安全的连接,还能提高网站的搜索引擎排名,那我们怎么才能通过Python获取域名的S... 目录了解SSL证书的基本概念使用python库来抓取SSL证书信息安装必要的库编写获取SSL证书信息

通过ibd文件恢复MySql数据的操作方法

《通过ibd文件恢复MySql数据的操作方法》文章介绍通过.ibd文件恢复MySQL数据的过程,包括知道表结构和不知道表结构两种情况,对于知道表结构的情况,可以直接将.ibd文件复制到新的数据库目录并... 目录第一种情况:知道表结构第二种情况:不知道表结构总结今天干了一件大事,安装1Panel导致原来服务

MySQL 日期时间格式化函数 DATE_FORMAT() 的使用示例详解

《MySQL日期时间格式化函数DATE_FORMAT()的使用示例详解》`DATE_FORMAT()`是MySQL中用于格式化日期时间的函数,本文详细介绍了其语法、格式化字符串的含义以及常见日期... 目录一、DATE_FORMAT()语法二、格式化字符串详解三、常见日期时间格式组合四、业务场景五、总结一、

MySQL InnoDB引擎ibdata文件损坏/删除后使用frm和ibd文件恢复数据

《MySQLInnoDB引擎ibdata文件损坏/删除后使用frm和ibd文件恢复数据》mysql的ibdata文件被误删、被恶意修改,没有从库和备份数据的情况下的数据恢复,不能保证数据库所有表数据... 参考:mysql Innodb表空间卸载、迁移、装载的使用方法注意!此方法只适用于innodb_fi

mysql通过frm和ibd文件恢复表_mysql5.7根据.frm和.ibd文件恢复表结构和数据

《mysql通过frm和ibd文件恢复表_mysql5.7根据.frm和.ibd文件恢复表结构和数据》文章主要介绍了如何从.frm和.ibd文件恢复MySQLInnoDB表结构和数据,需要的朋友可以参... 目录一、恢复表结构二、恢复表数据补充方法一、恢复表结构(从 .frm 文件)方法 1:使用 mysq

mysql8.0无备份通过idb文件恢复数据的方法、idb文件修复和tablespace id不一致处理

《mysql8.0无备份通过idb文件恢复数据的方法、idb文件修复和tablespaceid不一致处理》文章描述了公司服务器断电后数据库故障的过程,作者通过查看错误日志、重新初始化数据目录、恢复备... 周末突然接到一位一年多没联系的妹妹打来电话,“刘哥,快来救救我”,我脑海瞬间冒出妙瓦底,电信火苲马扁.