oracle 19c异机恢复pdb
1、目标
异机恢复数据库GS
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO <---必需
3 PDB1 READ ONLY NO
4 DSJEPP READ WRITE NO
5 GS READ WRITE NO <---异机恢复此数据库
6 GDTCEPP READ WRITE NO
7 GSTEST READ WRITE NO
使用nfs把备份目录挂载到恢复机器（Target）
ROOT和PDB$SEED容器库是必需要携带的。
2、根据源库编辑pfile,并在新机器上启动数据库至nomount状态
pfile:
inityang.ora ------- *.db_cache_size=1526531840 *.shared_pool_size=1626531840 *.java_pool_size=0 *.large_pool_size=100088640 *.pga_aggregate_target=1010612736 #*.sga_target=2068709120 *.audit_file_dest='/oracle/app/oracle/admin/dsjdb/adump' *.audit_trail='db' #*.cluster_database=true *.compatible='19.0.0' *.control_files='/oracle/app/oracle/oradata/DSJDB/control01.ctl' *.db_block_size=8192 *.db_name='dsjdb' *.diagnostic_dest='/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=dsjdbXDB)' *.enable_pluggable_database=true #family:dw_helper.instance_mode='read-only' *.instance_number=0 *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.processes=512 *.remote_login_passwordfile='exclusive' *.undo_tablespace='UNDOTBS1'
启动数据库至nomount
SQL> startup nomount pfile='/tmp/inityang.ora'; ORACLE instance started. Total System Global Area 3288332584 bytes Fixed Size 9140520 bytes Variable Size 1744830464 bytes Database Buffers 1526726656 bytes Redo Buffers 7634944 bytes SQL>
3、恢复controlfile,启动至mount
[oracle@gs26 admin]$ rman target / catalog rman/xxxx@rman Recovery Manager: Release 19.0.0.0.0 - Production on Tue Mar 8 19:40:57 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: DSJDB (not mounted) connected to recovery catalog database recovery catalog schema version 19.09.00.00. is newer than RMAN version RMAN> Restore controlfile from '/backup/backup_rman/controlfile_c-2776503480-20220309-08' ; Starting restore at 20220308194118 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=501 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oracle/app/oracle/oradata/DSJDB/control01.ctl Finished restore at 20220308194119 RMAN> RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed
如果没有rman catalog,需要手动注册backup filesets
RMAN> catalog start with '<the location where the backuppieces were copied on test server>' noprompt; RMAN> Crosscheck backup ; ---------------->Required to mark any unavailable files to expired.
4 restore database
report schema:
RMAN> report schema; Report of database schema for database with db_unique_name DSJDB List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 8192 SYSTEM YES +DATA01/DSJDB/DATAFILE/system.260.1062361405 2 300 PDB$SEED:SYSTEM NO +DATA01/DSJDB/B95505487B20A00AE0530164090AF145/DATAFILE/system.261.1062361407 3 8192 SYSAUX NO +DATA01/DSJDB/DATAFILE/sysaux.262.1062361409 4 295 PDB$SEED:SYSAUX NO +DATA01/DSJDB/B95505487B20A00AE0530164090AF145/DATAFILE/sysaux.263.1062361409 5 25600 UNDOTBS1 YES +DATA01/DSJDB/DATAFILE/undotbs1.264.1062361409 ... <---余下省略
根据report schema把相应目录都建好：
[oracle@gs26 data]$ mkdir -p /data/DSJDB/B95505487B20A00AE0530164090AF145/DATAFILE [oracle@gs26 data]$ mkdir -p /data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/ [oracle@gs26 data]$ mkdir -p /data/DSJDB/BC355F0A406200CAE0530164090ADF59/DATAFILE [oracle@gs26 data]$ mkdir -p /data/DSJDB/C11C508051F02C23E0530164090A842B/DATAFILE [oracle@gs26 data]$ mkdir -p /data/DSJDB/D9B1FD312E8C88CCE0530164090A88D5/DATAFILE [oracle@gs26 data]$ mkdir -p /data/DSJDB/B955AAE54DFB855EE0530164090AD3D2/DATAFILE
rman restore脚本，排除掉不相关的pdb:
run
{
set until time "to_date('2022-03-02 08:00:01','yyyy-mm-dd hh24:mi:ss')";
set newname for datafile 1 to '/data/DSJDB/DATAFILE/system.260.1062361405';
set newname for datafile 2 to '/data/DSJDB/B95505487B20A00AE0530164090AF145/DATAFILE/system.261.1062361407';
set newname for datafile 3 to '/data/DSJDB/DATAFILE/sysaux.262.1062361409';
set newname for datafile 4 to '/data/DSJDB/B95505487B20A00AE0530164090AF145/DATAFILE/sysaux.263.1062361409';
set newname for datafile 5 to '/data/DSJDB/DATAFILE/undotbs1.264.1062361409';
set newname for datafile 6 to '/data/DSJDB/B95505487B20A00AE0530164090AF145/DATAFILE/undotbs1.265.1062361409';
set newname for datafile 7 to '/data/DSJDB/DATAFILE/undotbs2.268.1062361421';
set newname for datafile 8 to '/data/DSJDB/DATAFILE/users.269.1062361423';
set newname for datafile 14 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/system.287.1062366837';
set newname for datafile 15 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/sysaux.286.1062366837';
set newname for datafile 16 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/undotbs1.288.1062366837';
set newname for datafile 17 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/undo_2.289.1062366837';
set newname for datafile 18 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/users.285.1062366837';
set newname for datafile 19 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/nnc_index01.291.1062367467';
set newname for datafile 20 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/nnc_data01.292.1062367493';
set newname for datafile 21 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.293.1062367549';
set newname for datafile 22 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.294.1062367601';
set newname for datafile 23 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.295.1062367633';
set newname for datafile 24 to '/data/DSJDB/DATAFILE/tbs_audit.296.1063652655';
set newname for datafile 25 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/audit01.297.1063653817';
set newname for datafile 34 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.307.1067961789';
set newname for datafile 43 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.317.1076529011';
set newname for datafile 44 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.318.1076529081';
set newname for datafile 45 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.319.1090249539';
set newname for datafile 46 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.320.1090251511';
set newname for datafile 47 to '/data/DSJDB/B95646F5C89D6C06E0530164090A4CBB/DATAFILE/cwbasegs5.321.1090251607';
restore database root;
restore database "PDB$SEED";
restore database GS;
switch datafile all;
}
输出省略。
5 recover database
根据report schema输出编辑如下脚本，除ROOT，PDB$SEED及GS外其它表空间skip掉。
run
{
set until time "to_date('2022-03-02 08:00:01','yyyy-mm-dd hh24:mi:ss')";
recover database
skip forever tablespace
PDB1:SYSTEM,
PDB1:SYSAUX,
PDB1:UNDOTBS1,
PDB1:UNDO_2,
PDB1:USERS,
GDTCEPP:SYSTEM,
GDTCEPP:SYSAUX,
GDTCEPP:UNDOTBS1,
GDTCEPP:UNDO_2,
GDTCEPP:USERS,
GDTCEPP:GDTC_EPP_DATA,
DSJEPP:SYSTEM,
DSJEPP:SYSAUX,
DSJEPP:UNDOTBS1,
DSJEPP:UNDO_2,
DSJEPP:USERS,
DSJEPP:GDTC_EPP_DATA
;
}
输出省略。
6 调整在线日志的位置
alter database rename file '+DATA01/DSJDB/ONLINELOG/group_1.271.1062366165' to '/data/DSJDB/ONLINELOG/group_1.271.1062366165'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_2.270.1062366165' to '/data/DSJDB/ONLINELOG/group_2.270.1062366165'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_3.259.1062366167' to '/data/DSJDB/ONLINELOG/group_3.259.1062366167'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_4.258.1062366167' to '/data/DSJDB/ONLINELOG/group_4.258.1062366167'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_5.279.1062365793' to '/data/DSJDB/ONLINELOG/group_5.279.1062365793'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_6.280.1062365795' to '/data/DSJDB/ONLINELOG/group_6.280.1062365795'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_7.281.1062365797' to '/data/DSJDB/ONLINELOG/group_7.281.1062365797'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_8.282.1062365797' to '/data/DSJDB/ONLINELOG/group_8.282.1062365797'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_9.283.1062365799' to '/data/DSJDB/ONLINELOG/group_9.283.1062365799'; alter database rename file '+DATA01/DSJDB/ONLINELOG/group_10.284.1062365799' to '/data/DSJDB/ONLINELOG/group_10.284.1062365799';
7 打开数据库
SQL> alter database open resetlogs ;
需关注alert日志，必要时可能需要在skip的库里offline drop掉alert.log中报错的数据文件。
近期评论