tadb RAC数据库恢复至单机
Contents
环境信息
- NBU Server : nbu-wg.qhlhfund.com
- 源主机: 10.0.10.111. / TADB RAC (RHEL6.5, Oracle database 11.2.0.4）
- 目标主机: 10.0.8.16 / RHEL6.5, Oracle database 11.2.0.4)
DBID=636193809
注：DBID提前记录并修改为对应值
环境准备
列出备份集
登录NBU Server或目标主机, 列出备份集, 找到对应的控制文件
/usr/openv/netbackup/bin/bplist -C TADB_636193809 -t 4 -R -l / | head -30
编辑pfile文件以适用单机环境
- SSH 使用oracle用户登录目标主机10.0.8.12
cd $ORACLE_HOME/dbs/
vi init$ORACLE_SID.ora
tadb.__db_cache_size=16508780544 tadb.__java_pool_size=268435456 tadb.__large_pool_size=201326592 tadb.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment tadb.__pga_aggregate_target=7516192768 tadb.__sga_target=19327352832 tadb.__shared_io_pool_size=0 tadb.__shared_pool_size=2214592512 tadb.__streams_pool_size=0 *.audit_file_dest='/u01/oracle/app/admin/tadb/adump' *.audit_sys_operations=TRUE *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/data/tadb/controlfile/current1','/log/tadb/controlfile/current2' *.db_block_size=8192 *.db_domain='' *.db_name='tadb' *.db_recovery_file_dest='/log/tadb/archivelog' *.db_recovery_file_dest_size=96636764160 *.db_unique_name='tadb' *.diagnostic_dest='/u01/oracle/app' *.dispatchers='(PROTOCOL=TCP) (SERVICE=tadbXDB)' *.fal_client='tadb' *.fal_server='tadbstb' *.log_archive_config='DG_CONFIG=(tadb,tadbstb)' *.log_archive_dest_1='location=/log/tadb/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=tadb' *.log_archive_dest_2='SERVICE=tadbstb DB_UNIQUE_NAME=tadbstb lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='tadb_%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 tadb.undo_tablespace='UNDOTBS1'
创建orapwd文件
- SSH 使用oracle用户登录目标主机10.0.8.12
cd $ORACLE_HOME/dbs/
orapwd file=orapw$ORACLE_SID entries=10
创建相关目录
- SSH 使用oracle用户登录目标主机10.0.8.12
- 如果权限不足, 则需要切換为root用戶執行
mkdir -pv /u01/oracle/app/admin/tadb/adump
mkdir -pv /log/tadb/{controlfile,archivelog}
mkdir -pv /data/tadb/{datafile,onlinelog,controlfile,tempfile}
迁移实施
如无特别说明, 以下迁移实施的操作都是在目标主机完成的.
即: 通过SSH客户端并使用oracle用户登录目标主机10.0.8.12
恢复 controlfile
rman target / nocatalog <<"EOF"
spool log to Restore-Controlfile.log
SET DBID=636193809
startup nomount;
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=TADB_636193809';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
restore controlfile from '/ctrl_dTADB_u2n1pl630_s19543_p1_t1134205024'; ## 修改为实际的控制文件
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/tadb/datafile' ||replace(t.name,'+DATA/tadb/datafile','')||''';' from v$datafile t order by t.file#; SQL> select 'set newname for tempfile '||t.file#||' to '||'''/data/tadb/tempfile' ||replace(t.name,'+DATA/tadb/tempfile','')||''';' 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=TADB_636193809';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 23357 thread 1;
set newname for datafile 1 to '/data/tadb/datafile/system.285.884607385';
set newname for datafile 2 to '/data/tadb/datafile/sysaux.286.884607387';
set newname for datafile 3 to '/data/tadb/datafile/undotbs1.287.884607387';
set newname for datafile 4 to '/data/tadb/datafile/undotbs2.289.884607391';
set newname for datafile 5 to '/data/tadb/datafile/users.290.884607391';
set newname for datafile 6 to '/data/tadb/datafile/tbs_aud.272.1070442505';
set newname for datafile 7 to '/data/tadb/datafile/undotbs2.344.1074966607';
set newname for datafile 8 to '/data/tadb/datafile/tbs_aud.584.957961587';
set newname for datafile 9 to '/data/tadb/datafile/kts_jgbs.583.974367721';
set newname for datafile 10 to '/data/tadb/datafile/kts_ta.352.886178125';
set newname for datafile 11 to '/data/tadb/datafile/kts_ta_com.362.886178125';
set newname for datafile 12 to '/data/tadb/datafile/kts_ta_his.363.886178125';
set newname for datafile 13 to '/data/tadb/datafile/kts_ta_index.361.886178125';
set newname for datafile 14 to '/data/tadb/datafile/lts_spotlight.342.886516613';
set newname for datafile 15 to '/data/tadb/datafile/kts_ta_his.481.1080766645';
set newname for datafile 16 to '/data/tadb/datafile/kts_ta_his.589.1068010861';
set newname for datafile 17 to '/data/tadb/datafile/kts_ta_his.354.1070395771';
set newname for datafile 18 to '/data/tadb/datafile/kts_ta_his.505.1084646361';
set newname for datafile 19 to '/data/tadb/datafile/kts_jgbs.464.1111256555';
set newname for datafile 20 to '/data/tadb/datafile/kts_jgbs.516.1127492967';
set newname for tempfile 1 to '/data/tadb/tempfile/temp.288.884607387';
set newname for tempfile 2 to '/data/tadb/tempfile/temp.454.1129575471';
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=TADB_636193809';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 23357 thread 1;
set newname for datafile 1 to '/data/tadb/datafile/system.285.884607385';
set newname for datafile 2 to '/data/tadb/datafile/sysaux.286.884607387';
set newname for datafile 3 to '/data/tadb/datafile/undotbs1.287.884607387';
set newname for datafile 4 to '/data/tadb/datafile/undotbs2.289.884607391';
set newname for datafile 5 to '/data/tadb/datafile/users.290.884607391';
set newname for datafile 6 to '/data/tadb/datafile/tbs_aud.272.1070442505';
set newname for datafile 7 to '/data/tadb/datafile/undotbs2.344.1074966607';
set newname for datafile 8 to '/data/tadb/datafile/tbs_aud.584.957961587';
set newname for datafile 9 to '/data/tadb/datafile/kts_jgbs.583.974367721';
set newname for datafile 10 to '/data/tadb/datafile/kts_ta.352.886178125';
set newname for datafile 11 to '/data/tadb/datafile/kts_ta_com.362.886178125';
set newname for datafile 12 to '/data/tadb/datafile/kts_ta_his.363.886178125';
set newname for datafile 13 to '/data/tadb/datafile/kts_ta_index.361.886178125';
set newname for datafile 14 to '/data/tadb/datafile/lts_spotlight.342.886516613';
set newname for datafile 15 to '/data/tadb/datafile/kts_ta_his.481.1080766645';
set newname for datafile 16 to '/data/tadb/datafile/kts_ta_his.589.1068010861';
set newname for datafile 17 to '/data/tadb/datafile/kts_ta_his.354.1070395771';
set newname for datafile 18 to '/data/tadb/datafile/kts_ta_his.505.1084646361';
set newname for datafile 19 to '/data/tadb/datafile/kts_jgbs.464.1111256555';
set newname for datafile 20 to '/data/tadb/datafile/kts_jgbs.516.1127492967';
set newname for tempfile 1 to '/data/tadb/tempfile/temp.288.884607387';
set newname for tempfile 2 to '/data/tadb/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/tadb/onlinelog' ||replace(t.member,'+DATA/tadb/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/tadb/onlinelog/group_5.290.884706935' to '/data/tadb/onlinelog/redolog9.log';
SQL> / 'ALTERDATABASERENAMEFILE'||''''||T.MEMBER||''||'''TO'||'''/DATA/TADB/ONLINELOG'||REPLACE(T.MEMBER,'+DATA/TADB/ONLINELOG','')||''';' ---------------------------------------------------------------------------------------------------------------------------------------------------------------- alter database rename file '+DATA/tadb/onlinelog/group_1.279.884607379' to '/data/tadb/onlinelog/group_1.279.884607379'; alter database rename file '+DATA/tadb/onlinelog/group_1.280.884607381' to '/data/tadb/onlinelog/group_1.280.884607381'; alter database rename file '+DATA/tadb/onlinelog/group_2.281.884607381' to '/data/tadb/onlinelog/group_2.281.884607381'; alter database rename file '+DATA/tadb/onlinelog/group_2.282.884607383' to '/data/tadb/onlinelog/group_2.282.884607383'; alter database rename file '+DATA/tadb/onlinelog/group_3.283.884607383' to '/data/tadb/onlinelog/group_3.283.884607383'; alter database rename file '+DATA/tadb/onlinelog/group_3.284.884607383' to '/data/tadb/onlinelog/group_3.284.884607383'; alter database rename file '+DATA/tadb/onlinelog/group_4.291.884607925' to '/data/tadb/onlinelog/group_4.291.884607925'; alter database rename file '+DATA/tadb/onlinelog/group_4.292.884607925' to '/data/tadb/onlinelog/group_4.292.884607925'; alter database rename file '+DATA/tadb/onlinelog/group_5.293.884607925' to '/data/tadb/onlinelog/group_5.293.884607925'; alter database rename file '+DATA/tadb/onlinelog/group_5.294.884607927' to '/data/tadb/onlinelog/group_5.294.884607927'; alter database rename file '+DATA/tadb/onlinelog/group_6.295.884607927' to '/data/tadb/onlinelog/group_6.295.884607927'; 'ALTERDATABASERENAMEFILE'||''''||T.MEMBER||''||'''TO'||'''/DATA/TADB/ONLINELOG'||REPLACE(T.MEMBER,'+DATA/TADB/ONLINELOG','')||''';' ---------------------------------------------------------------------------------------------------------------------------------------------------------------- alter database rename file '+DATA/tadb/onlinelog/group_6.296.884607927' to '/data/tadb/onlinelog/group_6.296.884607927'; alter database rename file '+DATA/tadb/onlinelog/group_7.399.962288563' to '/data/tadb/onlinelog/group_7.399.962288563'; alter database rename file '+DATA/tadb/onlinelog/group_8.335.962288565' to '/data/tadb/onlinelog/group_8.335.962288565'; alter database rename file '+DATA/tadb/onlinelog/group_9.465.962288565' to '/data/tadb/onlinelog/group_9.465.962288565'; alter database rename file '+DATA/tadb/onlinelog/group_10.461.962288567' to '/data/tadb/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/TADB/ARCHIVELOG/$(date +%F)); do
- asmcmd cp +RECO/TADB/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=TADB_636193809';
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;
查看并修复遇到的问题.
直到没有报错, 则执行下一步.
清除在线日志
SQL> select THREAD#, STATUS, ENABLED from v$thread;
SQL> SELECT a.group#, a.member, b.bytes/1024/1024, b.thread#, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group#
SQL> SELECT a.group#, a.member, b.bytes/1024/1024, b.status, b.thread# FROM v$logfile a, v$log b WHERE a.group# = b.group#
2 ;
GROUP# MEMBER B.BYTES/1024/1024 STATUS THREAD#
---------- -------------------------------------------------------------------------------- ----------------- ---------------- ----------
1 +DATA/gzdb/onlinelog/group_1.305.884614075 512 ACTIVE 1
1 +DATA/gzdb/onlinelog/group_1.306.884614075 512 ACTIVE 1
2 +DATA/gzdb/onlinelog/group_2.307.884614075 512 ACTIVE 1
2 +DATA/gzdb/onlinelog/group_2.308.884614077 512 ACTIVE 1
3 +DATA/gzdb/onlinelog/group_3.309.884614077 512 CURRENT 1
3 +DATA/gzdb/onlinelog/group_3.310.884614077 512 CURRENT 1
4 +DATA/gzdb/onlinelog/group_4.317.884614613 512 ACTIVE 2
4 +DATA/gzdb/onlinelog/group_4.318.884614613 512 ACTIVE 2
5 +DATA/gzdb/onlinelog/group_5.319.884614615 512 ACTIVE 2
5 +DATA/gzdb/onlinelog/group_5.320.884614615 512 ACTIVE 2
6 +DATA/gzdb/onlinelog/group_6.321.884614615 512 CURRENT 2
GROUP# MEMBER B.BYTES/1024/1024 STATUS THREAD#
---------- -------------------------------------------------------------------------------- ----------------- ---------------- ----------
6 +DATA/gzdb/onlinelog/group_6.322.884614617 512 CURRENT 2
12 rows selected.SQL> select group# from v$log where THREAD#=2;
SQL> alter database disable thread 2;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
select THREAD#, STATUS, ENABLED from v$thread;
静态监听配置
[oracle@zjqsdbser admin]$ cat listener.ora
[oracle@zjqsdbser admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/oracle/app/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = gzdb)
(ORACLE_HOME = /u01/oracle/app/11.2.0)
(SID_NAME = gzdb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.8.13)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/oracle/app
hugepages_setting
SQL> show parameter large; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ large_pool_size big integer 0 use_large_pages string TRUE
# echo "vm.nr_hugepages = 9221" >> /etc/sysctl.conf
# sysctl -p
[oracle@zjqsdbser ~]$ grep -i hugepa /proc/meminfo AnonHugePages: 38912 kB HugePages_Total: 9221 HugePages_Free: 8374 HugePages_Rsvd: 8370 HugePages_Surp: 0 Hugepagesize: 2048 kB
echo "9221*2048" | bc
cat >> /etc/security/limits.conf <<"EOF" * soft memlock 18884608 * hard memlock 18884608 EOF
打开数据库
目标主机-打开数据库
alter database open resetlogs;
- 关闭并重新打开数据库验证（可选）
SQL> shutdown immediate
SQL> startup
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
启动监听服务
cd $ORACLE_HOME/network
sed -i 's#dbserver#10.0.8.13#g' listener.ora
lsnrctl start
watch lsnrctl status
至此, 全部操作完成.
Troubleshooting
recover database until sequence
预期的sequence缺少, 忽略之
RMAN> recover database until sequence XXX
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=1000 scope=spfile;
alter system set sessions=1920 scope=spfile;
alter system set transactions=2058 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
calc shm
# Output lines suitable for sysctl configuration based # on total amount of RAM on the system. The output # will allow up to 50% of physical memory to be allocated # into shared memory. # On Linux, you can use it as follows (as root): # # ./shmsetup >> /etc/sysctl.conf # sysctl -p # Early FreeBSD versions do not support the sysconf interface # used here. The exact version where this works hasn't # been confirmed yet. page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` if [ -z "$page_size" ]; then echo Error: cannot determine page size exit 1 fi if [ -z "$phys_pages" ]; then echo Error: cannot determine number of memory pages exit 2 fi shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo \# Maximum shared segment size in bytes echo kernel.shmmax = $shmmax echo \# Maximum number of shared memory segments in pages echo kernel.shmall = $shmall