前文介绍搭建了主备集群。
https://blog.csdn.net/lushanglou/article/details/119183051
本文示例介绍如何使用主库备份文件+备库归档文件将主备集群恢复到单机数据库。
模拟数据库运行阶段:
1.主库建表
在主节点EP01建表:
create table test1 (t1 int primary key,t2 int not null);
insert into test1 values(1,1);
insert into test1 values(2,1);
commit;
select * from test1;
2.备份数据库
在主库上做全量备份:
[dmdba@localhost bin]$ ./disql SYSDBA@192.168.216.129:7236
服务器[192.168.216.129:7236]:处于主库打开状态
登录使用时间 : 17.658(ms)
disql V8
SQL> backup database full backupset '/home/dmdba/demplay/1/EP01_DB_BAK_FULL_01';
操作已执行
已用时间: 00:00:04.763. 执行号:600.
3.主库插入数据
主库上继续插入数据和新表:
insert into test1 values(3,1);
create table test2 (t1 int primary key,t2 int not null);
insert into test2 values(100,1);
insert into test2 values(200,1);
commit;
4.备库切换在线日志执行checkpoint生成归档日志
alter database archivelog current;
select checkpoint(30);
=====================================================================
准备阶段:
1.初始化新的单机库
初始化一个新库,我的新库是192.168.216.129:8236,实例名DMbak。检查一下源库的系统设置,避免新库和源库设置不一致。
2.使用dmrman工具查看主库备份文件
(中间我做了备份文件转移,所以路径有所变化):
[dmdba@localhost bin]$ dmrman
dmrman V8
RMAN> show backupset '/dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01'
show backupset '/dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01'<backupset [DEVICE TYPE:DISK, BACKUP_PATH: /dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01] info start ..........>
<DB INFO>
system path: /home/dmdba/demplay/1/dmwatcher4_0/EP01/DAMENG
pmnt_magic: 740610268
src_db_magic: 774229766
db_magic: 774229766
dsc node: 1
sys mode: 1
page check: 0
rlog encrypt: 0
external cipher[id/name]: 0/
external hash[id/name]: 0/
length in char: 0
use new hash: 1
page size: 8KB
extent size: 16
case sensitive: 1
log page size: 512B
unicode_flag/charset: 1
data version: 0x7000C
sys version: V8
enable policy: 0
archive flag: 1
blank_pad_mode: 0
crc_check: TRUE
page_enc_slice_size: 4096<META INFO>
backupset sig: BA
backupset version: 0x400A
database name: DAMENG
backup name: DB_FULL_20210812_143459_736915
backupset description:
n_magic: 0xDEC0CFC
parent n_magic: 0xFFFFFFFF
meta file size : 82432
compressed level: 0
encrypt type: 0
parallel num: 1
backup range: database
mpp_timestamp: 1628750099
with_huge: FALSE
backupset_type: NORMAL
mpp_flag: FALSE
backup level: online
backup type: full
without log: FALSE
end_lsn: 39726
max_trxid: 39689
base begin_lsn: -1
base end_lsn: -1
base n_magic: 0xFFFFFFFF
base name:
base backupset:
backup time: 2021-08-12 14:35:04
min exec ver: 0x08010101
min dct ver: 4
pkg size: 0x02000000<EP INFO>
EP[0]:
begin_pkg_seq: 4409
begin_lsn: 39719
end_pkg_seq: 4411
end_lsn: 39726<FILE INFO>
backupset directory: /dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01
backupset name: EP01_DB_BAK_FULL_01
backup data file num: 4
backup piece num: 2
backup huge file num: 0<backup_piece_list>
$file_seq |$size(KB) |$pos_desc |$content_type
0 |14590 |EP01_DB_BAK_FULL_01.bak |DATA
1 |7 |EP01_DB_BAK_FULL_01_1.bak |LOG<data_file_list>
$file_seq |$group_id |$group_name |$file_id |$file_path |$mirror_path |$file_len |$copy_num |$size_flag
1 |0 |SYSTEM |0 |/home/dmdba/demplay/1/dmwatcher4_0/EP01/DAMENG/SYSTEM.DBF| |23068672 |0 |0
2 |1 |ROLL |0 |/home/dmdba/demplay/1/dmwatcher4_0/EP01/DAMENG/ROLL.DBF| |134217728 |0 |0
3 |4 |MAIN |0 |/home/dmdba/demplay/1/dmwatcher4_0/EP01/DAMENG/MAIN.DBF| |134217728 |0 |0<arch_file_list>
$file_seq |$dsc_seq |$file_len |$begin_seqno |$begin_lsn |$end_seqno |$end_lsn
4 |0 |2560 |4409 |39719 |4411 |39726<huge_file_list>
$group_id |$schema_id|$table_id |$column_id|$file_id |$file_len |$path<backupset [DEVICE TYPE:DISK, BACKUP_PATH: /dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01] info end .>
show backupsets successfully.
time used: 141.017(ms)
RMAN>
可以看到db_magic 774229766
3.用dmarchk查看备机归档
[dmdba@localhost bin]$ ./dmrachk ARCH_PATH=/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch
rachk V8
the database pmnt_magic: 740610268
the database db_magic: 12255817
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-28_17-52-42.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 24276
arch_seq : 4080
clsn : 25531
next_seq : 4080
g_next_seq : 4080
file len : 150016
file free : 150016
dsc seqno : 0
dsc node : 1
create time : 2021-07-28 17:52:48.000000
close time : 2021-07-28 17:52:42.817877
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-28_17-53-55.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 153
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 25531
arch_seq : 4081
clsn : 26077
next_seq : 4233
g_next_seq : 4233
file len : 171520
file free : 171520
dsc seqno : 0
dsc node : 1
create time : 2021-07-28 17:52:42.817877
close time : 2021-07-29 13:23:26.994706
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-29_13-23-42.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 2
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 26078
arch_seq : 4234
clsn : 27435
next_seq : 4235
g_next_seq : 4235
file len : 180736
file free : 180736
dsc seqno : 0
dsc node : 1
create time : 2021-07-29 13:23:26.994706
close time : 2021-07-29 13:31:00.554743
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-29_13-32-15.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 27436
arch_seq : 4236
clsn : 28793
next_seq : 4236
g_next_seq : 4236
file len : 169984
file free : 169984
dsc seqno : 0
dsc node : 1
create time : 2021-07-29 13:31:00.554743
close time : 2021-07-29 13:32:15.897347
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-29_13-36-53.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 2
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 28793
arch_seq : 4237
clsn : 28793
next_seq : 4238
g_next_seq : 4238
file len : 38400
file free : 38400
dsc seqno : 0
dsc node : 1
create time : 2021-07-29 13:32:15.897347
close time : 2021-07-29 14:15:18.474190
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-29_14-16-34.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 28794
arch_seq : 4239
clsn : 30151
next_seq : 4239
g_next_seq : 4239
file len : 169984
file free : 169984
dsc seqno : 0
dsc node : 1
create time : 2021-07-29 14:15:18.474190
close time : 2021-07-29 14:16:35.805714
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-29_14-21-13.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 40
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 30151
arch_seq : 4240
clsn : 30151
next_seq : 4279
g_next_seq : 4279
file len : 45568
file free : 45568
dsc seqno : 0
dsc node : 1
create time : 2021-07-29 14:16:35.805714
close time : 2021-07-29 17:49:17.570780
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-30_09-47-55.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 30152
arch_seq : 4280
clsn : 31509
next_seq : 4280
g_next_seq : 4280
file len : 169984
file free : 169984
dsc seqno : 0
dsc node : 1
create time : 2021-07-29 17:49:17.570780
close time : 2021-07-30 09:48:07.684101
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-30_09-51-51.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 88
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 31509
arch_seq : 4281
clsn : 31509
next_seq : 4368
g_next_seq : 4368
file len : 67072
file free : 67072
dsc seqno : 0
dsc node : 1
create time : 2021-07-30 09:48:07.684101
close time : 2021-07-30 17:08:36.976535
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-30_17-19-52.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 31510
arch_seq : 4369
clsn : 32867
next_seq : 4369
g_next_seq : 4369
file len : 169984
file free : 169984
dsc seqno : 0
dsc node : 1
create time : 2021-07-30 17:08:36.976535
close time : 2021-07-30 17:19:58.579956
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-30_17-24-07.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 32867
arch_seq : 4370
clsn : 32867
next_seq : 4370
g_next_seq : 4370
file len : 11776
file free : 11776
dsc seqno : 0
dsc node : 1
create time : 2021-07-30 17:19:58.579956
close time : 2021-07-30 17:28:29.559417
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-07-30_17-29-25.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 32868
arch_seq : 4371
clsn : 34225
next_seq : 4371
g_next_seq : 4371
file len : 169984
file free : 169984
dsc seqno : 0
dsc node : 1
create time : 2021-07-30 17:28:29.559417
close time : 2021-07-30 17:29:27.182552
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-08-07_09-37-23.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 2
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 34226
arch_seq : 4372
clsn : 35583
next_seq : 4373
g_next_seq : 4373
file len : 203264
file free : 203264
dsc seqno : 0
dsc node : 1
create time : 2021-07-30 17:29:27.182552
close time : 2021-08-07 22:26:45.553842
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-08-08_16-08-10.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 3
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 35584
arch_seq : 4374
clsn : 36941
next_seq : 4376
g_next_seq : 4376
file len : 181760
file free : 181760
dsc seqno : 0
dsc node : 1
create time : 2021-08-07 22:26:45.553842
close time : 2021-08-08 16:21:43.203508
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-08-08_16-25-34.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 36942
arch_seq : 4377
clsn : 38299
next_seq : 4377
g_next_seq : 4377
file len : 169984
file free : 169984
dsc seqno : 0
dsc node : 1
create time : 2021-08-08 16:21:43.203508
close time : 2021-08-08 16:25:36.520640
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-08-08_16-30-17.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 8
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 38299
arch_seq : 4378
clsn : 38299
next_seq : 4385
g_next_seq : 4385
file len : 40960
file free : 40960
dsc seqno : 0
dsc node : 1
create time : 2021-08-08 16:25:36.520640
close time : 2021-08-08 17:31:23.507928
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-08-12_09-25-27.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 1
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 38300
arch_seq : 4386
clsn : 39657
next_seq : 4386
g_next_seq : 4386
file len : 169984
file free : 169984
dsc seqno : 0
dsc node : 1
create time : 2021-08-08 17:31:23.507928
close time : 2021-08-12 09:25:37.117470
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-08-12_09-29-32.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 22
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 39657
arch_seq : 4387
clsn : 39718
next_seq : 4408
g_next_seq : 4408
file len : 48128
file free : 48128
dsc seqno : 0
dsc node : 1
create time : 2021-08-12 09:25:37.117470
close time : 2021-08-12 14:20:03.033499
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-08-12_14-32-18.log明细.
version : 0x7006
status : INACTIVE
enc_flag : FALSE
n_rpkgs : 17
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 39719
arch_seq : 4409
clsn : 39786
next_seq : 4425
g_next_seq : 4425
file len : 19968
file free : 19968
dsc seqno : 0
dsc node : 1
create time : 2021-08-12 14:20:03.033499
close time : 2021-08-12 14:37:25.913759
/*******************************************************************/
/*******************************************************************/
归档文件/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch/STANDBY_ARCHIVE_0x2E25CF06[0]_2021-08-12_14-39-34.log明细.
version : 0x7006
status : ACTIVE
enc_flag : FALSE
n_rpkgs : 6
pmnt_magic : 740610268
db_magic : 12255817
src_db_magic : 774229766
arch_lsn : 39786
arch_seq : 4426
clsn : 39786
next_seq : 4431
g_next_seq : 4431
file len : 134217728
file free : 7168
dsc seqno : 0
dsc node : 1
create time : 2021-08-12 14:37:25.913759
close time : 2021-08-12 14:39:35.018199
/*******************************************************************/概要(节点[0]):
总计: 20 个文件
正确: 20 个文件
错误: 0 个文件
重复: 0 个文件the rachk tool running cost 47.195 ms
可以看到备机 db_magic : 12255817
====================================================================
还原阶段:
1.停止新库
./DmServiceDMbak stop
2.开始还原
RMAN> restore database '/dm8/data11/DMbak/dm.ini' from backupset '/dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01'
restore database '/dm8/data11/DMbak/dm.ini' from backupset '/dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01'
[Percent:0.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:01][Remaining:00:00:00]
restore successfully.
time used: 00:00:01.824
3.开始恢复
此时直接从备机归档恢复会报错:
RMAN> recover database '/dm8/data11/DMbak/dm.ini' with archivedir '/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch';
recover database '/dm8/data11/DMbak/dm.ini' with archivedir '/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch';
file dm.key not found, use default license!
Database mode = 1, oguid = 1485143301
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[39719], file_lsn[39719]
[611]:恢复操作未完成
此时应该用dbmagic 12255817
RMAN> recover database '/dm8/data11/DMbak/dm.ini' with archivedir '/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch' use db_magic 12255817;
recover database '/dm8/data11/DMbak/dm.ini' with archivedir '/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch' use db_magic 12255817;
file dm.key not found, use default license!
Database mode = 1, oguid = 1485143301
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[39719], file_lsn[39719]
EP:0 total 0 pkgs applied, percent: 0%
EP:0 total 3 pkgs applied, percent: 13%
EP:0 total 6 pkgs applied, percent: 27%
EP:0 total 9 pkgs applied, percent: 40%
EP:0 total 12 pkgs applied, percent: 54%
EP:0 total 15 pkgs applied, percent: 68%
EP:0 total 18 pkgs applied, percent: 81%
EP:0 total 21 pkgs applied, percent: 95%
EP:0 total 22 pkgs applied, percent: 100%
recover successfully!
time used: 00:00:01.392
RMAN>
4.更新dbmagic
RMAN> recover database '/dm8/data11/DMbak/dm.ini' update db_magic;
recover database '/dm8/data11/DMbak/dm.ini' update db_magic;
Database mode = 1, oguid = 1485143301
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
EP[0]'s cur_lsn[39786], file_lsn[39786]
recover successfully!
time used: 00:00:01.195
RMAN>
=========================================================================
启动新库
1.更改新库状态
启动数据库
./DmServiceDMbak start
此时是mount状态。手动更改状态:
alter database normal;
alter database open;
2.验证数据
此时可以检查表test1和test2数据是否正常:
select * from test1;
select * from test2;
表明恢复正常。
==================================================================
主要命令学习:
SQL> backup database full backupset '/home/dmdba/demplay/1/EP01_DB_BAK_FULL_01';
alter database archivelog current;
select checkpoint(30);
RMAN> show backupset '/dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01'
./dmrachk ARCH_PATH=/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch
RMAN> restore database '/dm8/data11/DMbak/dm.ini' from backupset '/dm8/data11/DMbak/backup0812/EP01_DB_BAK_FULL_01'
RMAN> recover database '/dm8/data11/DMbak/dm.ini' with archivedir '/home/dmdba/demplay/1/dmwatcher4_0/EP02/DAMENG/arch' use db_magic 12255817;
RMAN> recover database '/dm8/data11/DMbak/dm.ini' update db_magic;
更多参考内容请访问达梦云适配中心