Describe 首頁/work/QH-webdb-Restore here.

webdb RAC数据库恢复至单机

Contents

webdb RAC数据库恢复至单机
环境信息
环境准备
迁移实施
Troubleshooting
1. recover database until sequence
2. Parameters(可选)

环境信息

NBU Server : nbu-wg.qhlhfund.com
源主机: 10.0.20.111. / WEBDB RAC (RHEL6.5, Oracle database 11.2.0.4）
目标主机: 10.0.22.100 / RHEL6.5, Oracle database 11.2.0.4)
DBID=3426199409

注：DBID提前记录并修改为对应值

环境准备

请确保数据库主机和应用服务器的网络配置是正确的

编辑pfile文件以适用单机环境

SSH 使用oracle用户登录目标主机10.0.22.100

export ORACLE_SID=webdb

cd $ORACLE_HOME/dbs/

cat > init$ORACLE_SID.ora <<"EOF"
webdb.__db_cache_size=16508780544
webdb.__java_pool_size=268435456
webdb.__large_pool_size=201326592
webdb.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
webdb.__pga_aggregate_target=7516192768
webdb.__sga_target=19327352832
webdb.__shared_io_pool_size=0
webdb.__shared_pool_size=2214592512
webdb.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/app/admin/webdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/webdb/controlfile/current1','/log/webdb/controlfile/current2'
*.db_block_size=8192
*.db_domain=''
*.db_name='webdb'
*.db_recovery_file_dest='/log/webdb/archivelog'
*.db_recovery_file_dest_size=96636764160
*.db_unique_name='webdb'
*.diagnostic_dest='/u01/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=webdbXDB)'
*.fal_client='webdb'
*.fal_server='webdbstb'
*.log_archive_config='DG_CONFIG=(webdb,webdbstb)'
*.log_archive_dest_1='location=/log/webdb/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=webdb'
*.log_archive_dest_2='SERVICE=webdbstb DB_UNIQUE_NAME=webdbstb lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='webdb_%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.open_cursors=300
*.pga_aggregate_target=7516192768
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.session_cached_cursors=500
*.sessions=4544
*.sga_max_size=19327352832
*.sga_target=19327352832
*.standby_file_management='AUTO'
*.transactions=3998
*.undo_retention=14400
webdb.undo_tablespace='UNDOTBS1'
EOF

创建orapwd文件

SSH 使用oracle用户登录目标主机10.0.22.100

cd $ORACLE_HOME/dbs/

orapwd file=orapw$ORACLE_SID entries=10

创建相关目录

SSH 使用oracle用户登录目标主机10.0.22.100
如果权限不足, 则需要切換为root用戶執行

mkdir -pv /u01/oracle/app/admin/webdb/adump

mkdir -pv /log/webdb/{controlfile,archivelog}

mkdir -pv /data/webdb/{datafile,onlinelog,controlfile,tempfile}

chown -Rv oracle:oinstall /log/webdb /data/webdb

迁移实施

如无特别说明, 以下迁移实施的操作都是在目标主机完成的.

即: 通过SSH客户端并使用oracle用户登录目标主机10.0.22.100

列出备份集

/usr/openv/netbackup/bin/bplist -C WEBDB_3426199409 -t 4 -R -l / | head -30

比如最新的控制文件为: /ctrl_dWEBDB_u1p1q6p70_s20537_p1_t1134781664

恢复 controlfile

替换为上一步中所列出的目标控制文件.

rman target / nocatalog <<"EOF"
spool log to Restore-Controlfile.log
SET DBID=3426199409
startup nomount;
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_CLIENT=WEBDB_3426199409';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
restore controlfile from '/ctrl_dWEBDB_u1p1q6p70_s20537_p1_t1134781664'; ## 修改为实际的控制文件
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/webdb/datafile' ||replace(t.name,'+DATA/webdb/datafile','')||''';' from v$datafile t order by t.file#;

SQL> select 'set newname for tempfile '||t.file#||' to '||'''/data/webdb/tempfile' ||replace(t.name,'+DATA/webdb/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;

目标主机-还原&恢复验证（仅验证，可选）

注: 真正执行恢复的时候可以不用运行该步骤, 因为在正式迁移之前已经验证过了.
本数据库测试时的restore&recover时间为1.5小时左右.

主要作用为验证恢复是否可用，恢复时间预估等，执行以下脚本

rman target / nocatalog log=/home/oracle/Restore_preview-validate_$ORACLE_SID_$(date +%F_%H:%M).log<<"EOF"
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=WEBDB_3426199409';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 10499 thread 1;
set newname for datafile 1 to '/data/webdb/datafile/system.260.876602359';
set newname for datafile 2 to '/data/webdb/datafile/sysaux.261.876602363';
set newname for datafile 3 to '/data/webdb/datafile/undotbs1.262.876602365';
set newname for datafile 4 to '/data/webdb/datafile/undotbs2.264.876602385';
set newname for datafile 5 to '/data/webdb/datafile/users.265.876602399';
set newname for datafile 6 to '/data/webdb/datafile/kts_appcs.415.931023153';
set newname for datafile 7 to '/data/webdb/datafile/system.339.1032681969';
set newname for datafile 8 to '/data/webdb/datafile/undotbs1.272.1059110997';
set newname for datafile 9 to '/data/webdb/datafile/undotbs2.421.1059111055';
set newname for datafile 10 to '/data/webdb/datafile/qhlh_base.274.896894637';
set newname for datafile 11 to '/data/webdb/datafile/tbs_hw_data.275.880819749';
set newname for datafile 12 to '/data/webdb/datafile/tbs_hw_index.276.880819751';
set newname for datafile 13 to '/data/webdb/datafile/tbs_yw_data.277.880819751';
set newname for datafile 14 to '/data/webdb/datafile/tbs_yw_index.278.880819763';
set newname for datafile 15 to '/data/webdb/datafile/tbs_bill_data.279.880819765';
set newname for datafile 16 to '/data/webdb/datafile/rdata10';
set newname for datafile 17 to '/data/webdb/datafile/rdata11';
set newname for datafile 18 to '/data/webdb/datafile/rdata12';
set newname for datafile 19 to '/data/webdb/datafile/rdata13';
set newname for datafile 20 to '/data/webdb/datafile/rdata14';
set newname for datafile 21 to '/data/webdb/datafile/rdata20';
set newname for datafile 22 to '/data/webdb/datafile/rdata21';
set newname for datafile 23 to '/data/webdb/datafile/rdata22';
set newname for datafile 24 to '/data/webdb/datafile/rdata23';
set newname for datafile 25 to '/data/webdb/datafile/rdata24';
set newname for datafile 26 to '/data/webdb/datafile/rdata30';
set newname for datafile 27 to '/data/webdb/datafile/rdata31';
set newname for datafile 28 to '/data/webdb/datafile/rdata32';
set newname for datafile 29 to '/data/webdb/datafile/rdata33';
set newname for datafile 30 to '/data/webdb/datafile/rdata34';
set newname for datafile 31 to '/data/webdb/datafile/rdata40';
set newname for datafile 32 to '/data/webdb/datafile/rdata41';
set newname for datafile 33 to '/data/webdb/datafile/rdata42';
set newname for datafile 34 to '/data/webdb/datafile/rdata43';
set newname for datafile 35 to '/data/webdb/datafile/rdata44';
set newname for datafile 36 to '/data/webdb/datafile/rdata_charge';
set newname for datafile 37 to '/data/webdb/datafile/rdata50';
set newname for datafile 38 to '/data/webdb/datafile/rdata51';
set newname for datafile 39 to '/data/webdb/datafile/rdata60';
set newname for datafile 40 to '/data/webdb/datafile/rdata61';
set newname for datafile 41 to '/data/webdb/datafile/rdata62';
set newname for datafile 42 to '/data/webdb/datafile/rdata70';
set newname for datafile 43 to '/data/webdb/datafile/rdata71';
set newname for datafile 44 to '/data/webdb/datafile/rdata80';
set newname for datafile 45 to '/data/webdb/datafile/rdata81';
set newname for datafile 46 to '/data/webdb/datafile/rdata82';
set newname for datafile 47 to '/data/webdb/datafile/rdata83';
set newname for datafile 48 to '/data/webdb/datafile/rdata84';
set newname for datafile 49 to '/data/webdb/datafile/rdata85';
set newname for datafile 50 to '/data/webdb/datafile/rdata86';
set newname for datafile 51 to '/data/webdb/datafile/rdata87';
set newname for datafile 52 to '/data/webdb/datafile/rdata90';
set newname for datafile 53 to '/data/webdb/datafile/rdata91';
set newname for datafile 54 to '/data/webdb/datafile/kts_sale_com.324.886189405';
set newname for datafile 55 to '/data/webdb/datafile/kts_sale.319.886189405';
set newname for datafile 56 to '/data/webdb/datafile/kts_sale_his.328.886189407';
set newname for datafile 57 to '/data/webdb/datafile/kts_sale_bank.323.886189407';
set newname for datafile 58 to '/data/webdb/datafile/kts_sale.416.1073949037';
set newname for datafile 59 to '/data/webdb/datafile/kts_app.385.902241695';
set newname for datafile 60 to '/data/webdb/datafile/tbs_aud.359.1080320779';
set newname for datafile 61 to '/data/webdb/datafile/platform_data.349.1119023271';
set newname for datafile 62 to '/data/webdb/datafile/platform_data_index.336.1119023393';
set newname for datafile 63 to '/data/webdb/datafile/kts_sale.346.1121988387';
set newname for datafile 64 to '/data/webdb/datafile/kts_sale_his.420.1121988623';
set newname for datafile 65 to '/data/webdb/datafile/tbs_aud.271.1123606617';
set newname for datafile 66 to '/data/webdb/datafile/tbs_yw_data.270.1131731493';
set newname for tempfile 1 to '/data/webdb/tempfile/temp.263.876602379';
set newname for tempfile 2 to '/data/webdb/tempfile/ricd_temp';
set newname for tempfile 3 to '/data/webdb/tempfile/platform_data_temp.345.1119023313';
restore database preview;
restore database validate;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
RELEASE CHANNEL ch05;
}
EOF

restore database

rman target / nocatalog log=/home/oracle/Restore_$ORACLE_SID_$(date +%F_%H:%M).log<<"EOF"
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=WEBDB_3426199409';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
set until sequence 10499 thread 1;
set newname for datafile 1 to '/data/webdb/datafile/system.260.876602359';
set newname for datafile 2 to '/data/webdb/datafile/sysaux.261.876602363';
set newname for datafile 3 to '/data/webdb/datafile/undotbs1.262.876602365';
set newname for datafile 4 to '/data/webdb/datafile/undotbs2.264.876602385';
set newname for datafile 5 to '/data/webdb/datafile/users.265.876602399';
set newname for datafile 6 to '/data/webdb/datafile/kts_appcs.415.931023153';
set newname for datafile 7 to '/data/webdb/datafile/system.339.1032681969';
set newname for datafile 8 to '/data/webdb/datafile/undotbs1.272.1059110997';
set newname for datafile 9 to '/data/webdb/datafile/undotbs2.421.1059111055';
set newname for datafile 10 to '/data/webdb/datafile/qhlh_base.274.896894637';
set newname for datafile 11 to '/data/webdb/datafile/tbs_hw_data.275.880819749';
set newname for datafile 12 to '/data/webdb/datafile/tbs_hw_index.276.880819751';
set newname for datafile 13 to '/data/webdb/datafile/tbs_yw_data.277.880819751';
set newname for datafile 14 to '/data/webdb/datafile/tbs_yw_index.278.880819763';
set newname for datafile 15 to '/data/webdb/datafile/tbs_bill_data.279.880819765';
set newname for datafile 16 to '/data/webdb/datafile/rdata10';
set newname for datafile 17 to '/data/webdb/datafile/rdata11';
set newname for datafile 18 to '/data/webdb/datafile/rdata12';
set newname for datafile 19 to '/data/webdb/datafile/rdata13';
set newname for datafile 20 to '/data/webdb/datafile/rdata14';
set newname for datafile 21 to '/data/webdb/datafile/rdata20';
set newname for datafile 22 to '/data/webdb/datafile/rdata21';
set newname for datafile 23 to '/data/webdb/datafile/rdata22';
set newname for datafile 24 to '/data/webdb/datafile/rdata23';
set newname for datafile 25 to '/data/webdb/datafile/rdata24';
set newname for datafile 26 to '/data/webdb/datafile/rdata30';
set newname for datafile 27 to '/data/webdb/datafile/rdata31';
set newname for datafile 28 to '/data/webdb/datafile/rdata32';
set newname for datafile 29 to '/data/webdb/datafile/rdata33';
set newname for datafile 30 to '/data/webdb/datafile/rdata34';
set newname for datafile 31 to '/data/webdb/datafile/rdata40';
set newname for datafile 32 to '/data/webdb/datafile/rdata41';
set newname for datafile 33 to '/data/webdb/datafile/rdata42';
set newname for datafile 34 to '/data/webdb/datafile/rdata43';
set newname for datafile 35 to '/data/webdb/datafile/rdata44';
set newname for datafile 36 to '/data/webdb/datafile/rdata_charge';
set newname for datafile 37 to '/data/webdb/datafile/rdata50';
set newname for datafile 38 to '/data/webdb/datafile/rdata51';
set newname for datafile 39 to '/data/webdb/datafile/rdata60';
set newname for datafile 40 to '/data/webdb/datafile/rdata61';
set newname for datafile 41 to '/data/webdb/datafile/rdata62';
set newname for datafile 42 to '/data/webdb/datafile/rdata70';
set newname for datafile 43 to '/data/webdb/datafile/rdata71';
set newname for datafile 44 to '/data/webdb/datafile/rdata80';
set newname for datafile 45 to '/data/webdb/datafile/rdata81';
set newname for datafile 46 to '/data/webdb/datafile/rdata82';
set newname for datafile 47 to '/data/webdb/datafile/rdata83';
set newname for datafile 48 to '/data/webdb/datafile/rdata84';
set newname for datafile 49 to '/data/webdb/datafile/rdata85';
set newname for datafile 50 to '/data/webdb/datafile/rdata86';
set newname for datafile 51 to '/data/webdb/datafile/rdata87';
set newname for datafile 52 to '/data/webdb/datafile/rdata90';
set newname for datafile 53 to '/data/webdb/datafile/rdata91';
set newname for datafile 54 to '/data/webdb/datafile/kts_sale_com.324.886189405';
set newname for datafile 55 to '/data/webdb/datafile/kts_sale.319.886189405';
set newname for datafile 56 to '/data/webdb/datafile/kts_sale_his.328.886189407';
set newname for datafile 57 to '/data/webdb/datafile/kts_sale_bank.323.886189407';
set newname for datafile 58 to '/data/webdb/datafile/kts_sale.416.1073949037';
set newname for datafile 59 to '/data/webdb/datafile/kts_app.385.902241695';
set newname for datafile 60 to '/data/webdb/datafile/tbs_aud.359.1080320779';
set newname for datafile 61 to '/data/webdb/datafile/platform_data.349.1119023271';
set newname for datafile 62 to '/data/webdb/datafile/platform_data_index.336.1119023393';
set newname for datafile 63 to '/data/webdb/datafile/kts_sale.346.1121988387';
set newname for datafile 64 to '/data/webdb/datafile/kts_sale_his.420.1121988623';
set newname for datafile 65 to '/data/webdb/datafile/tbs_aud.271.1123606617';
set newname for datafile 66 to '/data/webdb/datafile/tbs_yw_data.270.1131731493';
set newname for tempfile 1 to '/data/webdb/tempfile/temp.263.876602379';
set newname for tempfile 2 to '/data/webdb/tempfile/ricd_temp';
set newname for tempfile 3 to '/data/webdb/tempfile/platform_data_temp.345.1119023313';
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;
}
EOF

手动copy asm磁盘中的归档日志至目标主机(可选)

如果是直接停机备份/迁移则不需要此步骤.

SSH 使用grid用户登录源主机10.0.20.111, ASM copy archivelog FROM ASM to LOCAL

mkdir ~/$(date +%Y_%m_%d)

chown grid:oinstall ~/$(date +%Y_%m_%d)

for i in $(asmcmd ls RECO/WEBDB/ARCHIVELOG/$(date +%Y_%m_%d)); do
  asmcmd cp +RECO/WEBDB/ARCHIVELOG/$(date +%Y_%m_%d)/$i ~/$(date +%Y_%m_%d)
done

scp -r /dbbak_exp_run/$(date +%Y_%m_%d) oracle@10.0.22.100:/dbbak_exp_run/$(date +%Y_%m_%d)

rename the online redolog files

SQL> select member from v$logfile;

示例

SQL> alter database rename file '/<old_path>/redo01.log' to '/<new_path>/redo01.log';

alter database rename file '+DATA/webdb/onlinelog/group_5.290.884706935' to '/data/webdb/onlinelog/redolog9.log';

SQL> select 'alter database rename file '||''''||t.member||''||''' to '||'''/data/webdb/onlinelog' ||replace(t.member,'+DATA/webdb/onlinelog','')||''';' from v$logfile t order by t.group#;

alter database rename file '+DATA/webdb/onlinelog/group_1.257.876602357' to '/data/webdb/onlinelog/group_1.257.876602357';
alter database rename file '+DATA/webdb/onlinelog/group_2.258.876602357' to '/data/webdb/onlinelog/group_2.258.876602357';
alter database rename file '+DATA/webdb/onlinelog/group_3.259.876602359' to '/data/webdb/onlinelog/group_3.259.876602359';
alter database rename file '+DATA/webdb/onlinelog/group_4.266.876602799' to '/data/webdb/onlinelog/group_4.266.876602799';
alter database rename file '+DATA/webdb/onlinelog/group_5.267.876602801' to '/data/webdb/onlinelog/group_5.267.876602801';
alter database rename file '+DATA/webdb/onlinelog/group_6.268.876602801' to '/data/webdb/onlinelog/group_6.268.876602801';
alter database rename file '+DATA/webdb/onlinelog/group_7.373.962289523' to '/data/webdb/onlinelog/group_7.373.962289523';
alter database rename file '+DATA/webdb/onlinelog/group_8.387.962289531' to '/data/webdb/onlinelog/group_8.387.962289531';
alter database rename file '+DATA/webdb/onlinelog/group_9.419.962289531' to '/data/webdb/onlinelog/group_9.419.962289531';
alter database rename file '+DATA/webdb/onlinelog/group_10.418.962289533' to '/data/webdb/onlinelog/group_10.418.962289533';

recover database

rman target / nocatalog log=/home/oracle/Recover_$ORACLE_SID_$(date +%F_%H:%M).log<<"EOF"
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';
send 'NB_ORA_CLIENT=WEBDB_3426199409';
send 'NB_ORA_SERV=nbu-wg.qhlhfund.com';
recover database until sequence 10499 thread 1;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
}
EOF

恢复新的归档(可选)

如果是直接停机备份/迁移则不需要此步骤.

$ rman target /

catalog start with '/dbbak_exp_run/$(date +%Y_%m_%d)';
crosscheck backupset;
recover database;

查看并修复遇到的问题.

直到没有报错, 则执行下一步.

hugepages_setting

root用户登录

echo "vm.nr_hugepages = 9221" >> /etc/sysctl.conf

sysctl -p

grep -i hugepa /proc/meminfo

echo "9221*2048" | bc ## 计算memlock

cat >> /etc/security/limits.conf <<"EOF"
* soft memlock 18884608
* hard memlock 18884608
EOF

打开数据库

目标主机-打开数据库

alter database open resetlogs;

关闭并重新打开数据库验证（可选）
SQL> shutdown immediate
SQL> startup

清除undotbs2

SQL> show parameter undo;

select tablespace_name from dba_tablespaces where contents='UNDO';

drop tablespace UNDOTBS2 including contents and datafiles;

清除thread 2 log

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 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@ ]$ 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 = webdb)
  (ORACLE_HOME = /u01/oracle/app/11.2.0)
  (SID_NAME = webdb)
 )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.22.100)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/oracle/app

启动监听服务

lsnrctl start

watch lsnrctl status

创建spfile, 并使用spfile重新启动数据库

SQL> create spfile from pfile;

SQL> shutdown immediate

SQL> startup

至此, 全部操作完成.

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