oracle rman 全量备份,rman全量备份+archivelog 数据迁移

旧数据库

1、全备

vi /tmp/rman.sql

run {

Allocate channel rman_1 type disk;

Allocate channel rman_2 type disk;

backup as compressed backupset  database format '/backup/full_%d_%T_%s_%p';--备份数据文件全备

sql 'alter system archive log current';

backup as compressed backupset archivelog all format '/backup/arch_%d_%T_%s_%p' delete  input;--备份归档文件

backup  current controlfile format '/backup/controfile_%d_%T_%s_%p';--备份控制文件

backup spfile format  '/tmp/sp_%T_%U.sp';---备份spfile

release channel rman_1;

release channel rman_2;

}

rman target / nocatalog cmdfile=/tmp/rman.sql log=/tmp/rman.log&

开始备份时间Fri Jun 26 18:21:16 2020

结束备份时间18:21:16 2020

select thread#,sequence#,to_char(first_time,'yyyymmddhh24:mi'),to_char(completion_time,'yyyymmddhh24:mi') from v$archived_log order by first_time;

THREAD#  SEQUENCE# TO_CHAR(FIRST TO_CHAR(COMPL

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

1

131 2020062618:18 2020062618:18

1

131 2020062618:18 2020062618:18

1

132 2020062618:18 2020062618:21

1

132 2020062618:18 2020062618:21

1

133 2020062618:21 2020062618:21

1

133 2020062618:21 2020062618:21

开始备份132 备份结束133

把备份集和参数文件拷贝到新服务器

新库：

1、创建跟源数据库一致的目录

mkdir -p /u01/app/oracle/diag/rdbms/kcpt/kcpt/alert

mkdir -p /u01/app/oracle/diag/rdbms/kcpt/kcpt/cdump

mkdir -p /u01/app/oracle/diag/rdbms/kcpt/kcpt/trace

mkdir -p /u01/oracle/archivelog/ ---归档路径

mkdir -p /u01/oracle/oradata/kcpt ---数据文件路径

[oracle@test_119 backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 27 15:29:31 2020

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

connected to target database (not started)

2、RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/oracle/product/11.2.0/db_1/dbs/initlskeche.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes

Variable Size                285213576 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5517312 bytes

3、恢复spfile

RMAN>  restore spfile from '/backup/sp_20200627_3jv3qdml_1_1.sp';

Starting restore at 27-JUN-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=429 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/sp_20200627_3jv3qdml_1_1.sp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 27-JUN-20

4、恢复pfile

RMAN> restore spfile to pfile  '/u01/oracle/product/11.2.0/db_1/dbs/initlskeche.ora'  from '/backup/sp_20200627_3jv3qdml_1_1.sp';

Starting restore at 27-JUN-20

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/sp_20200627_3jv3qdml_1_1.sp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 27-JUN-20

5、根据实际的内存大小 更改参数文件里的sga等参数大小

os系统内存为64G

oracle的内存占用os系统内存的 80%即51.2G

memory_max_target=pga+sga (pga:sga=1:4)

所以sga=51.2/5*4=40.96G,pga=10G

在根据改完的参数文件启动数据库

新：

SQL> startup nomount pfile='/u01/oracle/product/11.2.0/db_1/dbs/initlskeche.ora';

ORACLE instance started.

Total System Global Area 4.3827E+10 bytes

Fixed Size

2262976 bytes

Variable Size

5100275776 bytes

Database Buffers

3.8655E+10 bytes

Redo Buffers

69627904 bytes

6、恢复控制文件

RMAN> restore controlfile from  '/backup/controfile_LSKECHE_20200626_109_1';

Starting restore at 26-JUN-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2267 device type=DISK

channel ORA_DISK_1: restoring control file

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

output file name=/u01/oracle/oradata/lskeche/control01.ctl

output file name=/u01/oracle/oradata/lskeche/control02.ctl

Finished restore at 26-JUN-20

7、启动到mount

SQL> alter database mount;

Database altered.

select file#, name, 'set newname for datafile ' || file# || ' to ''' ||

replace(name, '/opt/app', '/u01') || ''';'

from v$datafile;

8、恢复数据文件

vi /tmp/restore_database.sh

run{

set newname for datafile 1 to '/u01/oracle/oradata/kcpt/system01.dbf';

set newname for datafile 2 to '/u01/oracle/oradata/kcpt/sysaux01.dbf';

set newname for datafile 3 to '/u01/oracle/oradata/kcpt/undotbs01.dbf';

set newname for datafile 4 to '/u01/oracle/oradata/kcpt/users01.dbf';

set newname for datafile 5 to '/u01/oracle/oradata/kcpt/example01.dbf';

set newname for datafile 6 to '/u01/oracle/oradata/kcpt/history_kcpt04.dbf';

set newname for datafile 7 to '/u01/oracle/oradata/kcpt/history_kcpt01.dbf';

set newname for datafile 8 to '/u01/oracle/oradata/kcpt/history_kcpt02.dbf';

set newname for datafile 9 to '/u01/oracle/oradata/kcpt/history_kcpt03.dbf';

set newname for datafile 10 to '/u01/oracle/oradata/kcpt/kcpt01.dbf';

set newname for datafile 11 to '/u01/oracle/oradata/kcpt/kcpt02.dbf';

set newname for datafile 12 to '/u01/oracle/oradata/kcpt/kcpt03.dbf';

set newname for datafile 13 to '/u01/oracle/oradata/kcpt/kcpt04.dbf';

set newname for datafile 14 to '/u01/oracle/oradata/kcpt/kcpt_data01.dbf';

set newname for datafile 15 to '/u01/oracle/oradata/kcpt/kcpt_data02.dbf';

set newname for datafile 16 to '/u01/oracle/oradata/kcpt/kcpt_data03.dbf';

set newname for datafile 17 to '/u01/oracle/oradata/kcpt/kcpt_data04.dbf';

set newname for datafile 18 to '/u01/oracle/oradata/kcpt/undotbs02.dbf';

set newname for datafile 19 to '/u01/oracle/oradata/kcpt/undotbs03.dbf';

set newname for datafile 20 to '/u01/oracle/oradata/kcpt/undotbs04.dbf';

set newname for datafile 21 to '/u01/oracle/oradata/kcpt/kcpt_idx01.dbf';

set newname for datafile 22 to '/u01/oracle/oradata/kcpt/kcpt_idx02.dbf';

set newname for datafile 23 to '/u01/oracle/oradata/kcpt/kcpt_idx03.dbf';

set newname for datafile 24 to '/u01/oracle/oradata/kcpt/kcpt_idx04.dbf';

set newname for datafile 25 to '/u01/oracle/oradata/kcpt/undotbs05.dbf';

set newname for datafile 26 to '/u01/oracle/oradata/kcpt/kcpt05.dbf';

set newname for datafile 27 to '/u01/oracle/oradata/kcpt/kcpt06.dbf';

set newname for datafile 28 to '/u01/oracle/oradata/kcpt/kcpt07.dbf';

set newname for datafile 29 to '/u01/oracle/oradata/kcpt/kcpt08.dbf';

set newname for datafile 30 to '/u01/oracle/oradata/kcpt/kcpt09.dbf';

set newname for datafile 31 to '/u01/oracle/oradata/kcpt/kcpt10.dbf';

set newname for datafile 32 to '/u01/oracle/oradata/kcpt/kcpt_idx05.dbf';

set newname for datafile 33 to '/u01/oracle/oradata/kcpt/kcpt_idx06.dbf';

set newname for datafile 34 to '/u01/oracle/oradata/kcpt/kcpt11.dbf';

set newname for datafile 35 to '/u01/oracle/oradata/kcpt/kcpt12.dbf';

set newname for datafile 36 to '/u01/oracle/oradata/kcpt/kcpt13.dbf';

set newname for datafile 37 to '/u01/oracle/oradata/kcpt/kcpt14.dbf';

set newname for datafile 38 to '/u01/oracle/oradata/kcpt/kcpt15.dbf';

set newname for datafile 39 to '/u01/oracle/oradata/kcpt/kcpt16.dbf';

set newname for datafile 40 to '/u01/oracle/oradata/kcpt/history_kcpt05.dbf';

set newname for datafile 41 to '/u01/oracle/oradata/kcpt/history_kcpt06.dbf';

restore database;

switch datafile all;

}

rman target / nocatalog cmdfile=/tmp/restore_database.sh log=restore_database.log&

9、恢复归档日志

vi /tmp/restore_archive.sh

run{

restore archivelog from  sequence 87028  until sequence 144 thread1;;

}

rman target / nocatalog cmdfile=/tmp/restore_archive.sh log=/tmp/restore_archive.log&

10、SQL> recover database using backup controlfile until cancel;

追归档日志

旧库

rman> sql 'alter system archive log current';

backup archivelog all format '/backup/arch_%d_%T_%s_%p' delete input;

backup current controlfile format '/backup/controfile_%d_%T_%s_%p';

select thread#,sequence#,to_char(first_time,'yyyymmddhh24:mi'),to_char(completion_time,'yyyymmddhh24:mi') from v$archived_log order by first_time;

THREAD#  SEQUENCE# TO_CHAR(FIRST TO_CHAR(COMPL

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

1

132 2020062618:18 2020062618:21

1

131 2020062618:18 2020062618:18

1

131 2020062618:18 2020062618:18

1

132 2020062618:18 2020062618:21

1

133 2020062618:21 2020062618:21

1

133 2020062618:21 2020062618:21

1

134 2020062618:21 2020062620:23

1

134 2020062618:21 2020062620:23

1

135 2020062620:23 2020062620:31

1

135 2020062620:23 2020062620:31

1

136 2020062620:31 2020062620:33

THREAD#  SEQUENCE# TO_CHAR(FIRST TO_CHAR(COMPL

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

1

136 2020062620:31 2020062620:33

把备份集拷贝到新服务器

恢复归档日志(两种方法)

1、vi /tmp/restore_archive01

run{

set  archivelog destination to '/u01/oracle/archivelog/';

restore archivelog from sequence 135 until sequence136 thread 1;

}

rman>recover database;

2、添加最新的备份集

--1.手动注册归档日志

不是备份集

--RMAN> catalog start with '/archivelog/';

---rman>recover database;

或者备份集

---rman>catalog backuppiece '/backup/arch_LSKECHE_20200626_111_1';

执行归档日志恢复脚本

rman target / nocatalog cmdfile=/tmp/restore_archive01.sh log=restore_archive01.log&

rman>recover database;

找回归档日志

restore archivelog from logseq 141；

只要每次catalog就执行一次recover database,直到最后一次停机位置，在追加完日志之前 数据库状态一直为mount;

---recover database using backup controlfile;

-----------

SQL> alter database open resetlogs;

Database altered.