之前验证异地备份,只对数据库做个全备就备份恢复了,这种适用于数据库比较小的场景,因为如果数据库量大的话,备份,拷贝备份,恢复数据库的时间就比较长,停业务的时间就会比较长。
如果数据库比较大,可以提前几天将全备文件拷贝过来,在目标端restore database,然后每天增量拷贝归档日志的备份,直到真正迁移的时候,只拷贝最近的归档日志的备份,最后在目标端recover database,可大大缩短业务停止时间。
一 对源端数据库做备份
1.1 对数据库做个全备
RMAN> backup database format '/home/oracle/backup/full.bak_01081541' tag='full.bak_01081541';
Starting backup at 08-JAN-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/baidd.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/baidd2.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JAN-23
channel ORA_DISK_1: finished piece 1 at 08-JAN-23
piece handle=/home/oracle/backup/full.bak_01081541 tag=FULL.BAK_01081541 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 08-JAN-23
Starting Control File and SPFILE Autobackup at 08-JAN-23
piece handle=/data/backup/controlfile_bak/control.bak_20230108_c-1572833030-20230108-05 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-23
二 拷贝备份到目标端
2.1 拷贝控制文件备份
scp /data/backup/controlfile_bak/control.bak_20230108_c-1572833030-20230108-05 192.168.1.252:/home/oracle/bak/ctl_bak/
2.2 拷贝全库备份
scp /home/oracle/backup/full.bak_01081541 192.168.1.252:/home/oracle/bak/db_bak/
三 在目标端恢复数据
3.1 恢复控制文件
/*
#确保目标端db_name和源端保持一致
如果不一致,后面恢复完控制文件,将数据库启动到mount状态时会报错:
ORA-01103: 控制文件中的数据库名 ''ORCL'' 不是 ''EVS''。
修改示例:
示例:
create pfile='/home/oracle/temp.ora' from spfile;
vi /home/oracle/temp.ora
将*.db_name='ORCL'改为*.db_name='evs'
shutdown immediate;
startup nomount pfile='/home/oracle/temp.ora';
create spfile from pfile='/home/oracle/temp.ora';
show parameter name; #检查确认
*/
startup nomount;
restore controlfile from '/home/oracle/bak/ctl_bak/control.bak_20230108_c-1572833030-20230108-05';
3.2 将数据库启动到mount状态
alter database mount;
/*
假如报错:
ORA-00201: 控制文件版本 12.2.0.0.0 与 ORACLE 版本 12.1.0.2.0 不兼容
ORA-00202: 控制文件: ''/data/app/oracle/oradata/orcl/control01.ctl'
则修改参数文件中compatible参数,将其和源端保持一致,示例:
alter system set compatible='12.2.0' scope=spfile;
重新将库启动到mount状态,使修改生效……。
*/
#指定备份文件所在位置,这样数据库会自动在该路径下找备份文件进行恢复。
catalog start with '/home/oracle/bak/';
crosscheck backup;
delete expired backup;
3.3 restore database
restore database;
/*
如果目标端数据文件路径和源端不一致,还需要指定下新路径,示例:
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
allocate channel t6 type disk;
allocate channel t7 type disk;
allocate channel t8 type disk;
allocate channel t9 type disk;
allocate channel t10 type disk;
set newname for database to '/data/app/oracle/oradata/orcl/%U';
restore database;
switch datafile all;
switch tempfile all;
}
多开几个通道,可以加速restore。
*/
3.4 对源端归档日志做备份,并拷贝到目标端
#在源端造测试数据
SQL> insert into scott.t1(id,name) values(5,'arch1');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
RMAN> backup archivelog all delete all input format='/home/oracle/backup/%d_%t_%s' tag='arch_first.bak';
Starting backup at 08-JAN-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=63 STAMP=1125589661
input archived log thread=1 sequence=19 RECID=64 STAMP=1125589694
channel ORA_DISK_1: starting piece 1 at 08-JAN-23
channel ORA_DISK_1: finished piece 1 at 08-JAN-23
piece handle=/home/oracle/backup/ORCL_1125589694_48 tag=ARCH_FIRST.BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_01_08/o1_mf_1_18_kvnx8x1z_.arc RECID=63 STAMP=1125589661
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_01_08/o1_mf_1_19_kvnx9yo7_.arc RECID=64 STAMP=1125589694
Finished backup at 08-JAN-23
Starting Control File and SPFILE Autobackup at 08-JAN-23
piece handle=/data/backup/controlfile_bak/control.bak_20230108_c-1572833030-20230108-06 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-23
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
38 29.00K DISK 00:00:00 08-JAN-23
BP Key: 38 Status: AVAILABLE Compressed: NO Tag: ARCH_FIRST.BAK
Piece Name: /home/oracle/backup/ORCL_1125589694_48
List of Archived Logs in backup set 38
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 18 1745322 08-JAN-23 1745523 08-JAN-23
1 19 1745523 08-JAN-23 1745540 08-JAN-23
#拷贝全备之后的归档日志备份
scp /home/oracle/backup/ORCL_1125589694_48 192.168.1.252:/home/oracle/bak/arch_bak/
3.5 停止源端应用业务,备份归档日志
3.5.1 停应用
略
3.5.2 备份归档日志
#造条测试数据
SQL> insert into scott.t1(id,name) values(6,'arch2');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
RMAN> backup archivelog all delete all input format='/home/oracle/backup/%d_%t_%s' tag='arch_second.bak';
Starting backup at 08-JAN-23
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=65 STAMP=1125589873
input archived log thread=1 sequence=21 RECID=66 STAMP=1125589909
channel ORA_DISK_1: starting piece 1 at 08-JAN-23
channel ORA_DISK_1: finished piece 1 at 08-JAN-23
piece handle=/home/oracle/backup/ORCL_1125589910_50 tag=ARCH_SECOND.BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_01_08/o1_mf_1_20_kvnxhkwg_.arc RECID=65 STAMP=1125589873
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2023_01_08/o1_mf_1_21_kvnxjow9_.arc RECID=66 STAMP=1125589909
Finished backup at 08-JAN-23
Starting Control File and SPFILE Autobackup at 08-JAN-23
piece handle=/data/backup/controlfile_bak/control.bak_20230108_c-1572833030-20230108-07 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-23
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
38 29.00K DISK 00:00:00 08-JAN-23
BP Key: 38 Status: AVAILABLE Compressed: NO Tag: ARCH_FIRST.BAK
Piece Name: /home/oracle/backup/ORCL_1125589694_48
List of Archived Logs in backup set 38
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 18 1745322 08-JAN-23 1745523 08-JAN-23
1 19 1745523 08-JAN-23 1745540 08-JAN-23
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
40 40.00K DISK 00:00:00 08-JAN-23
BP Key: 40 Status: AVAILABLE Compressed: NO Tag: ARCH_SECOND.BAK
Piece Name: /home/oracle/backup/ORCL_1125589910_50
List of Archived Logs in backup set 40
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 20 1745540 08-JAN-23 1745661 08-JAN-23
1 21 1745661 08-JAN-23 1745684 08-JAN-23
3.5.3 拷贝最新归档日志备份
scp /home/oracle/backup/ORCL_1125589910_50 192.168.1.252:/home/oracle/bak/arch_bak/
3.6 在目标端recover database
#查看源端最近的归档日志备份里最后一个归档日志的NEXT SCN
#在目标端恢复数据库到这个位置
recover database until scn=1745684;
/*
如果不这样指定,直接recover database的话,会报错RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 22 and starting SCN of 1745684。因为数据库会自动去找后面的归档日志,但是我们没新的归档日志。
*/
3.6 启动目标端数据库
3.6.1 检查redo log所在路径在目标环境是否存在
存在的话,可忽略这一步,继续往下进行。
不存在的话,将redo log指定到一个已存在(有该目录)的路径下,再启动数据库,否则启动数据库会报错:
ORA-00349: 无法获得 '/oracle/app/oracle/oradata/orcl/redo01.log' 的块大小
ORA-27041: 无法打开文件
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
假如起点库报了上面这个错,然后指定redo log到新位置了,再启动库,还会报这个错:
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: 位于 04/05/2024 10:58:27 的 sql statement 命令失败
ORA-00392: 日志 1 (用于线程 1) 正被清除, 不允许操作
ORA-00312: 联机日志 1 线程 1: '/data/app/oracle/oradata/orcl/redo01.log'
redo log会自动生成在新指定的redo log路径下。
示例:
select member from v$logfile;
目标端没有/oracle/app/oracle/oradata/orcl这个目录。
select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'原路径','新路径')||''';' from v$logfile;
执行输出来的sql,重命名下redo log。
示例:
alter database rename file '/oracle/app/oracle/oradata/orcl/redo03.log' to '/data/app/oracle/oradata/orcl/redo03.log';
alter database rename file '/oracle/app/oracle/oradata/orcl/redo02.log' to '/data/app/oracle/oradata/orcl/redo02.log';
alter database rename file '/oracle/app/oracle/oradata/orcl/redo01.log' to '/data/app/oracle/oradata/orcl/redo01.log';
alter database rename file '/oracle/app/oracle/oradata/orcl/redo04.log' to '/data/app/oracle/oradata/orcl/redo04.log';
alter database rename file '/oracle/app/oracle/oradata/orcl/redo05.log' to '/data/app/oracle/oradata/orcl/redo05.log';
3.6.2 启动数据库
alter database open resetlogs;
#在指定的路径下检查redo log是否生成。
cd /data/app/oracle/oradata/orcl
ls -l | grep redo