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.12 / RHEL6.5, Oracle database 11.2.0.4)
DBID=40501727
注：DBID提前记录并修改为对应值
环境准备
列出备份集
登录NBU Server或目标主机, 列出备份集, 找到对应的控制文件 /usr/openv/netbackup/bin/bplist -C GZDB_40501727 -t 4 -R -l / | head -30
编辑pfile文件以适用单机环境
- SSH 使用oracle用户登录目标主机10.0.8.12
cd $ORACLE_HOME/dbs/
vi init$ORACLE_SID.ora
gzdb.__db_cache_size=15367929856 gzdb.__java_pool_size=469762048 gzdb.__large_pool_size=536870912 gzdb.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment gzdb.__pga_aggregate_target=8246337208 gzdb.__sga_target=19241453486 gzdb.__shared_io_pool_size=0 gzdb.__shared_pool_size=3704253440 gzdb.__streams_pool_size=67108864 *.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_size=96636764160 *.db_recovery_file_dest='/log/gzdb/archivelog' *.diagnostic_dest='/u01/oracle/app' *.dispatchers='(PROTOCOL=TCP) (SERVICE=gzdbXDB)' *.log_archive_format='gzdb_%t_%s_%r.dbf' *.log_archive_max_processes=30 *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='exclusive' *.resource_limit=TRUE *.session_cached_cursors=500 *.sessions=335 *.sga_max_size=21990232555 gzdb.undo_tablespace='UNDOTBS1' *.undo_retention=14400
创建orapwd文件
- SSH 使用oracle用户登录目标主机10.0.8.12
cd $ORACLE_HOME/dbs/
orapwd file=orapw$ORACLE_SID entries=10
创建相关目录
- SSH 使用oracle用户登录目标主机10.0.8.12
mkdir -pv /u01/oracle/app/admin/gzdb/adump
mkdir -pv /log/gzdb/{controlfile,archivelog}
mkdir -pv /data/gzdb/{datafile,onlinelog,controlfile}
迁移实施
如无特别说明, 以下迁移实施的操作都是在目标主机完成的.
即: 通过SSH客户端并使用oracle用户登录目标主机10.0.8.12
恢复 controlfile
rman target / nocatalog <<"EOF"
spool log to Restore-Controlfile.log
SET DBID=40501727
startup nomount;
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=GZDB_40501727';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
restore controlfile from '/ctrl_dGZDB_uq21on8da_s19266_p1_t1133224362'; ## 修改为实际的控制文件
ALTER DATABASE MOUNT;
RELEASE CHANNEL ch00;
}
spool log off
EOF
生成rman set newname脚本
方法一
$ sqlplus / as sysdba
SQL> select 'set newname for datafile '||t.file_id||' to '||'''/data/gzdb/datafile/' ||replace(t.file_name,'+DATA/gzdb/datafile','')||''';' from dba_data_files t order by t.file_id; SQL> select 'set newname for tempfile '||t.file#||' to '||'''/data/gzdb/datafile/' ||replace(t.name,'+DATA/gzdb/datafile','')||''';' from v$tempfile t order by t.file#
方法二
在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_40501727';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 27731 thread 1;
#set newname for datafile 1 to '/data/gzdb/datafile/system.282.884706407';
#set newname for tempfile 1 to '/data/gzdb/datafile/temp01.dbf';
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_40501727';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 27731 thread 1;
#set newname for datafile 15 to '/data/gzdb/datafile/fundsirm.385.1126003991';
#set newname for tempfile 1 to '/data/gzdb/datafile/temp01.dbf';
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';
手动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.12:/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_40501727';
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