Describe 首頁/work/QH-gzdb-Restore here.
gzdb RAC数据库恢复至单机
Contents
环境信息
- NBU Server : nbu-wg.qhlhfund.com
- 源主机: 10.0.10.111 / GZDB RAC (RHEL6.5, Oracle database 11.2.0.4）
- 目标主机: 10.0.8.13 / RHEL6.5, Oracle database 11.2.0.4)
DBID=3049006903
注：DBID提前记录并修改为对应值
环境准备
列出备份集
登录NBU Server或目标主机, 列出备份集, 找到对应的控制文件
/usr/openv/netbackup/bin/bplist -C GZDB_3049006903 -t 4 -R -l / | head -30
编辑pfile文件以适用单机环境
- SSH 使用oracle用户登录目标主机10.0.8.13
cd $ORACLE_HOME/dbs/
vi init$ORACLE_SID.ora
gzdb.__db_cache_size=16508780544 gzdb.__java_pool_size=268435456 gzdb.__large_pool_size=201326592 gzdb.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment gzdb.__pga_aggregate_target=7516192768 gzdb.__sga_target=19327352832 gzdb.__shared_io_pool_size=0 gzdb.__shared_pool_size=2214592512 gzdb.__streams_pool_size=0 *.audit_file_dest='/u01/oracle/app/admin/gzdb/adump' *.audit_sys_operations=TRUE *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/data/gzdb/controlfile/current1','/log/gzdb/controlfile/current2' *.db_block_size=8192 *.db_domain='' *.db_name='gzdb' *.db_recovery_file_dest='/log/gzdb/archivelog' *.db_recovery_file_dest_size=96636764160 *.db_unique_name='gzdb' *.diagnostic_dest='/u01/oracle/app' *.dispatchers='(PROTOCOL=TCP) (SERVICE=gzdbXDB)' *.fal_client='gzdb' *.fal_server='gzdbstb' *.log_archive_config='DG_CONFIG=(gzdb,gzdbstb)' *.log_archive_dest_1='location=/log/gzdb/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=gzdb' *.log_archive_dest_2='SERVICE=gzdbstb DB_UNIQUE_NAME=gzdbstb lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='gzdb_%t_%s_%r.dbf' *.log_archive_max_processes=30 *.open_cursors=300 *.pga_aggregate_target=7516192768 *.processes=300 *.remote_login_passwordfile='exclusive' *.resource_limit=TRUE *.session_cached_cursors=500 *.sessions=330 *.sga_max_size=19327352832 *.sga_target=19327352832 *.standby_file_management='AUTO' *.transactions=580 *.undo_retention=14400 gzdb.undo_tablespace='UNDOTBS1'
创建orapwd文件
- SSH 使用oracle用户登录目标主机10.0.8.13
cd $ORACLE_HOME/dbs/
orapwd file=orapw$ORACLE_SID entries=10
创建相关目录
- SSH 使用oracle用户登录目标主机10.0.8.13
mkdir -pv /u01/oracle/app/admin/gzdb/adump
- 切換root用戶執行
su - root
mkdir -pv /log/gzdb/{controlfile,archivelog}
mkdir -pv /data/gzdb/{datafile,onlinelog,controlfile}
迁移实施
如无特别说明, 以下迁移实施的操作都是在目标主机完成的.
即: 通过SSH客户端并使用oracle用户登录目标主机10.0.8.13
恢复 controlfile
rman target / nocatalog <<"EOF"
spool log to Restore-Controlfile.log
SET DBID=3049006903
startup nomount;
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=GZDB_3049006903';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
restore controlfile from '/ctrl_dGZDB_u6i1pcafa_s19666_p1_t1133914602'; ## 修改为实际的控制文件
ALTER DATABASE MOUNT;
RELEASE CHANNEL ch00;
}
spool log off
EOF
生成rman set newname脚本
方法一
$ sqlplus / as sysdba
SQL> select 'set newname for datafile '||t.file#||' to '||'''/data/gzdb/datafile' ||replace(t.name,'+DATA/gzdb/datafile','')||''';' from v$datafile t order by t.file#; SQL> select 'set newname for tempfile '||t.file#||' to '||'''/data/gzdb/tempfile' ||replace(t.name,'+DATA/gzdb/tempfile','')||''';' from v$tempfile t order by t.file#
'SETNEWNAMEFORDATAFILE'||T.FILE#||'TO'||'''/DATA/GZDB/DATAFILE'||REPLACE(T.NAME, -------------------------------------------------------------------------------- set newname for datafile 1 to '/data/gzdb/datafile/system.311.884614079'; set newname for datafile 2 to '/data/gzdb/datafile/sysaux.312.884614081'; set newname for datafile 3 to '/data/gzdb/datafile/undotbs1.313.884614081'; set newname for datafile 4 to '/data/gzdb/datafile/undotbs2.315.884614087'; set newname for datafile 5 to '/data/gzdb/datafile/users.316.884614087'; set newname for datafile 6 to '/data/gzdb/datafile/yts_xbrl.341.899203963'; set newname for datafile 7 to '/data/gzdb/datafile/yts_gzapi.480.899396139'; set newname for datafile 8 to '/data/gzdb/datafile/tbs_aud.394.957958875'; set newname for datafile 9 to '/data/gzdb/datafile/yts_gz_gm.360.899201693'; set newname for datafile 10 to '/data/gzdb/datafile/tbs_aud.472.975659031'; set newname for datafile 11 to '/data/gzdb/datafile/kts_zjqs_his.522.978111579'; 'SETNEWNAMEFORDATAFILE'||T.FILE#||'TO'||'''/DATA/GZDB/DATAFILE'||REPLACE(T.NAME, -------------------------------------------------------------------------------- set newname for datafile 12 to '/data/gzdb/datafile/users.523.978587813'; set newname for datafile 13 to '/data/gzdb/datafile/yts_frdata.556.981898793'; set newname for datafile 14 to '/data/gzdb/datafile/yts_gz_zh.364.899201693'; set newname for datafile 15 to '/data/gzdb/datafile/yts_frinfo.470.981898797'; set newname for datafile 16 to '/data/gzdb/datafile/sysaux.596.1019289631'; set newname for datafile 17 to '/data/gzdb/datafile/kts_zjqs_web.401.896524601'; set newname for datafile 18 to '/data/gzdb/datafile/tbs_aud.525.1019329343'; set newname for datafile 19 to '/data/gzdb/datafile/yts_gz_gm.445.1042099895'; 19 rows selected. set newname for tempfile 1 to '/data/gzdb/tempfile/temp.314.884614083';
方法二
在rman中运行 report schema, 提取其结果并使用awk&sed等命令生成.
查看备份的归档
RMAN> list backup of archivelog all;
List of Archived Logs in backup set 9 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 56 214541 01-FEB-21 226238 01-FEB-21 1 57 226238 01-FEB-21 226240 01-FEB-21 1 58 226240 01-FEB-21 233107 12-FEB-21 2 1 186185 28-JAN-21 225714 01-FEB-21 2 2 225714 01-FEB-21 226037 01-FEB-21 2 3 226037 01-FEB-21 233110 12-FEB-21
获取 until sequence + 1
set until sequence 59 thread 1;
目标主机-还原&恢复验证（仅验证，可选）
主要作用为验证恢复是否可用，恢复时间预估等，执行以下脚本
rman target / nocatalog <<"EOF"
spool log to Restore_preview_validate.log
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch04 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch05 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=GZDB_3049006903';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 32178 thread 1;
set newname for datafile 1 to '/data/gzdb/datafile/system.311.884614079';
set newname for datafile 2 to '/data/gzdb/datafile/sysaux.312.884614081';
set newname for datafile 3 to '/data/gzdb/datafile/undotbs1.313.884614081';
set newname for datafile 4 to '/data/gzdb/datafile/undotbs2.315.884614087';
set newname for datafile 5 to '/data/gzdb/datafile/users.316.884614087';
set newname for datafile 6 to '/data/gzdb/datafile/yts_xbrl.341.899203963';
set newname for datafile 7 to '/data/gzdb/datafile/yts_gzapi.480.899396139';
set newname for datafile 8 to '/data/gzdb/datafile/tbs_aud.394.957958875';
set newname for datafile 9 to '/data/gzdb/datafile/yts_gz_gm.360.899201693';
set newname for datafile 10 to '/data/gzdb/datafile/tbs_aud.472.975659031';
set newname for datafile 11 to '/data/gzdb/datafile/kts_zjqs_his.522.978111579';
set newname for datafile 12 to '/data/gzdb/datafile/users.523.978587813';
set newname for datafile 13 to '/data/gzdb/datafile/yts_frdata.556.981898793';
set newname for datafile 14 to '/data/gzdb/datafile/yts_gz_zh.364.899201693';
set newname for datafile 15 to '/data/gzdb/datafile/yts_frinfo.470.981898797';
set newname for datafile 16 to '/data/gzdb/datafile/sysaux.596.1019289631';
set newname for datafile 17 to '/data/gzdb/datafile/kts_zjqs_web.401.896524601';
set newname for datafile 18 to '/data/gzdb/datafile/tbs_aud.525.1019329343';
set newname for datafile 19 to '/data/gzdb/datafile/yts_gz_gm.445.1042099895';
set newname for tempfile 1 to '/data/gzdb/tempfile/temp.314.884614083';
restore database preview;
restore database validate;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
RELEASE CHANNEL ch05;
}
spool log off
EOF
restore database
rman target / nocatalog <<"EOF"
spool log to Restore.log
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch04 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch05 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=GZDB_3049006903';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 32178 thread 1;
set newname for datafile 1 to '/data/gzdb/datafile/system.285.884607385';
set newname for datafile 2 to '/data/gzdb/datafile/sysaux.286.884607387';
set newname for datafile 3 to '/data/gzdb/datafile/undotbs1.287.884607387';
set newname for datafile 4 to '/data/gzdb/datafile/undotbs2.289.884607391';
set newname for datafile 5 to '/data/gzdb/datafile/users.290.884607391';
set newname for datafile 6 to '/data/gzdb/datafile/tbs_aud.272.1070442505';
set newname for datafile 7 to '/data/gzdb/datafile/undotbs2.344.1074966607';
set newname for datafile 8 to '/data/gzdb/datafile/tbs_aud.584.957961587';
set newname for datafile 9 to '/data/gzdb/datafile/kts_jgbs.583.974367721';
set newname for datafile 10 to '/data/gzdb/datafile/kts_ta.352.886178125';
set newname for datafile 11 to '/data/gzdb/datafile/kts_ta_com.362.886178125';
set newname for datafile 12 to '/data/gzdb/datafile/kts_ta_his.363.886178125';
set newname for datafile 13 to '/data/gzdb/datafile/kts_ta_index.361.886178125';
set newname for datafile 14 to '/data/gzdb/datafile/lts_spotlight.342.886516613';
set newname for datafile 15 to '/data/gzdb/datafile/kts_ta_his.481.1080766645';
set newname for datafile 16 to '/data/gzdb/datafile/kts_ta_his.589.1068010861';
set newname for datafile 17 to '/data/gzdb/datafile/kts_ta_his.354.1070395771';
set newname for datafile 18 to '/data/gzdb/datafile/kts_ta_his.505.1084646361';
set newname for datafile 19 to '/data/gzdb/datafile/kts_jgbs.464.1111256555';
set newname for datafile 20 to '/data/gzdb/datafile/kts_jgbs.516.1127492967';
set newname for tempfile 1 to '/data/gzdb/tempfile/temp.288.884607387';
set newname for tempfile 2 to '/data/gzdb/tempfile/temp.454.1129575471';
restore database;
switch datafile all;
switch tempfile all;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
RELEASE CHANNEL ch05;
}
spool log off
EOF
rename the online redolog files
SQL> select member from v$logfile;
SQL> select 'alter database rename file '||''''||t.member||''||''' to '||'''/data/gzdb/onlinelog' ||replace(t.member,'+DATA/gzdb/onlinelog','')||''';' from v$logfile t order by t.group#;
SQL> alter database rename file '/<old_path>/redo01.log' to '/<new_path>/redo01.log';
示例
alter database rename file '+DATA/gzdb/onlinelog/group_5.290.884706935' to '/data/gzdb/onlinelog/redolog9.log';
SQL> / 'ALTERDATABASERENAMEFILE'||''''||T.MEMBER||''||'''TO'||'''/DATA/GZDB/ONLINELOG'||REPLACE(T.MEMBER,'+DATA/GZDB/ONLINELOG','')||''';' ---------------------------------------------------------------------------------------------------------------------------------------------------------------- alter database rename file '+DATA/gzdb/onlinelog/group_1.279.884607379' to '/data/gzdb/onlinelog/group_1.279.884607379'; alter database rename file '+DATA/gzdb/onlinelog/group_1.280.884607381' to '/data/gzdb/onlinelog/group_1.280.884607381'; alter database rename file '+DATA/gzdb/onlinelog/group_2.281.884607381' to '/data/gzdb/onlinelog/group_2.281.884607381'; alter database rename file '+DATA/gzdb/onlinelog/group_2.282.884607383' to '/data/gzdb/onlinelog/group_2.282.884607383'; alter database rename file '+DATA/gzdb/onlinelog/group_3.283.884607383' to '/data/gzdb/onlinelog/group_3.283.884607383'; alter database rename file '+DATA/gzdb/onlinelog/group_3.284.884607383' to '/data/gzdb/onlinelog/group_3.284.884607383'; alter database rename file '+DATA/gzdb/onlinelog/group_4.291.884607925' to '/data/gzdb/onlinelog/group_4.291.884607925'; alter database rename file '+DATA/gzdb/onlinelog/group_4.292.884607925' to '/data/gzdb/onlinelog/group_4.292.884607925'; alter database rename file '+DATA/gzdb/onlinelog/group_5.293.884607925' to '/data/gzdb/onlinelog/group_5.293.884607925'; alter database rename file '+DATA/gzdb/onlinelog/group_5.294.884607927' to '/data/gzdb/onlinelog/group_5.294.884607927'; alter database rename file '+DATA/gzdb/onlinelog/group_6.295.884607927' to '/data/gzdb/onlinelog/group_6.295.884607927'; 'ALTERDATABASERENAMEFILE'||''''||T.MEMBER||''||'''TO'||'''/DATA/GZDB/ONLINELOG'||REPLACE(T.MEMBER,'+DATA/GZDB/ONLINELOG','')||''';' ---------------------------------------------------------------------------------------------------------------------------------------------------------------- alter database rename file '+DATA/gzdb/onlinelog/group_6.296.884607927' to '/data/gzdb/onlinelog/group_6.296.884607927'; alter database rename file '+DATA/gzdb/onlinelog/group_7.399.962288563' to '/data/gzdb/onlinelog/group_7.399.962288563'; alter database rename file '+DATA/gzdb/onlinelog/group_8.335.962288565' to '/data/gzdb/onlinelog/group_8.335.962288565'; alter database rename file '+DATA/gzdb/onlinelog/group_9.465.962288565' to '/data/gzdb/onlinelog/group_9.465.962288565'; alter database rename file '+DATA/gzdb/onlinelog/group_10.461.962288567' to '/data/gzdb/onlinelog/group_10.461.962288567'; 16 rows selected.
手动copy asm磁盘中的归档日志至目标主机(可选)
* SSH 使用grid用户登录源主机10.0.10.111 ASM copy archivelog FROM ASM to LOCAL
mkdir /backup/$(date +%F)
chown grid:oinstall /backup/$(date +%F)
for i in $(asmcmd ls RECO/GZDB/ARCHIVELOG/$(date +%F)); do
- asmcmd cp +RECO/GZDB/ARCHIVELOG/$(date +%F)/$i /backup/$(date +%F)
done
scp -r /backup/$(date +%F) oracle@10.0.8.13:/backup/$(date +%F)
recover database
rman target / nocatalog <<"EOF"
spool log to Recover.log
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=GZDB_3049006903';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
recover database;
RELEASE CHANNEL ch00;
}
spool log off
EOF* 恢复新的归档 rman target /
catalog start with '/backup/$(date +%F)'; crosscheck backupset; recover database;
查看并修复遇到的问题.
直到没有报错, 则执行下一步.
打开数据库
目标主机-打开数据库
alter database open resetlogs;
- 关闭并重新打开数据库验证（可选）
SQL> shutdown immediate
SQL> startup
Troubleshooting
recover database until sequence
预期的sequence缺少, 忽略之
RMAN> recover database until sequence XXX
清除在线日志
SQL> select THREAD#, STATUS, ENABLED from v$thread;
SQL> select group# from v$log where THREAD#=2;
SQL> alter database drop logfile group 10;
SQL> select THREAD#, STATUS, ENABLED from v$thread;
Parameters
su - oracle
sqlplus / as sysdba
# processes=x sessions=x*1.1+5 transactions=sessions*1.1
select name, value from v$parameter where name in ('sessions','processes','transactions');
alter system set processes=300 scope=spfile;
alter system set sessions=335 scope=spfile;
alter system set transactions=368 scope=spfile;
alter system set sga_max_size=18G scope=spfile;
alter system set sga_target=18G scope=spfile;
alter system set pga_aggregate_target=7G scope=spfile;
shutdown immediate
startup