oracle 11.2.0.4 ADG+linux+补丁升级11.2.0.4.181016(28204707)
2019-01-16 11:21 AllegroCantabile 阅读(1638) 评论(0) 编辑 收藏 举报一 补丁升级实施方案
1.1 总体步骤
1.主库禁用日志传送到备库,备库停止MRP进程
2.关闭备库应用补丁,但不执行脚本(catpatch.sql等),启动到mount,不启用日志恢复
3.关闭主库应用补丁,执行脚本(run catpatch/catbundle/catcpu等)
4.启动主库,重新开启日志传送到备库
5.备库开启实时应用
1.2 上传补丁至主备上并解压
上传补丁包p28204707_112040_Linux-x86-64.zip至服务器/oracle/software上 unzip p28204707_112040_Linux-x86-64.zip
1.3 停止主库日志传输
#停止DG同步,主库停止传输日志 SQL> show parameter log_archive_dest_state_2 SQL> alter system set log_archive_dest_state_2=defer scope=both;
1.4 备份主备库数据库软件
#备份主库oracle及oraInventory目录 $ echo $ORACLE_HOME /oracle/app/oracle/product/11.2.0/db_1 tar -cvf /oracle/db_1`date+%y%m%d`.tar /oracle/app/oracle/product/11.2.0/db_1 cat /oracle/oraInventory/oraInst.loc inventory_loc=/oracle/oraInventory inst_group=oinstall tar -cvf /oracle/oraInventory`date+%y%m%d`.tar /oracle/oraInventory #备库亦是如此
1.5 更新Opatch
#查看主备数据库当前Opatch版本 $ $ORACLE_HOME/OPatch/opatch version OPatch Version: 11.2.0.3.4 OPatch succeeded. #官方建议Opacth至少11.2.0.3.6及以上版本,目前数据库的Opatch为11.2.0.3.4,进行更新,上传p6880880_112000_Linux-x86-64.zip至/oracle/software,解压并更新Opatch upzip p6880880_112000_Linux-x86-64.zip mv /oracle/app/oracle/product/11.2.0/db_1/OPatch /oracle/app/oracle/product/11.2.0/db_1/Opatch.bak2018 mv /oracle/software/OPacth /oracle/app/oracle/product/11.2.0/db_1 $ $ORACLE_HOME/OPatch/opatch version OPatch Version: 11.2.0.3.20 OPatch succeeded.
1.6 关闭备库应用补丁不执行相关脚本
1.6.1 校验补丁冲突
unzip p28204707_112040_Linux-x86-64.zip cd 28204707 opatch prereq CheckConflictAgainstOHWithDetail -ph ./
1.6.2 关闭备库数据库服务
#停止备库MRP进程 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; #停止监听 $lsnrctl status $lsnrctl stop #检查监听状态 $lsnrctl status $ps -ef|grep tns #停止数据库 SQL> shutdown immediate; #检查数据库告警日志 $tail -1000f /oracle/app/oracle/diag/rdbms/cdcadg/cdcadg/trace/alert_cdcdg.log
1.6.3 检查oracle active进程,避免数据库psu应用失败
su – oracle $ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -phBaseDir /oracle/software/28204707 注意:如果存在active.so的进程,则通过下面的方法将其kill掉,如: fuser $ORACLE_HOME/历史n/oracle ps -ef|grep pid(上一步获得的进程号,确认是什么进程) kill -9 pid 然后再次执行active.so的检查,直至没有活动的进程。
1.6.4 补丁应用
cd 28204707 $ $ORACLE_HOME/OPatch/opatch apply
1.6.5 启动数据库服务
#启动数据库到mount状态 SQL> startup mount #启动监听 $ lsnrctl start $ lsnrctl status #检查数据库告警日志 $ tail -1000f /oracle/app/oracle/diag/rdbms/cdc/cdc/trace/alert_cdc.log
1.7 关闭主库应用补丁并执行相关脚本
1.7.1 校验补丁冲突
unzip p28204707_112040_Linux-x86-64.zip cd 28204707 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
1.7.2 检查数据库补丁信息,组件状态及无效对象信息
#组件信息 col comp_id for a10 col comp_name for a40 col version for a20 col status for a7 select comp_id, comp_name, version, status from dba_registry; #无效对象 col owner for a15 col object_name for a35 col object_type for a20 col status for a10 select owner,object_name,object_type,status from dba_objects where status='INVALID' order by owner; #补丁信息 col action format a10 col version format a25 col comments format a25 col action_time format a20 alter session set nls_timestamp_format = "yyy-mm-dd hh24:mi:ss"; select ACTION_TIME,ACTION,VERSION,COMMENTS from dba_registry_history;
1.7.3 检查oracle active进程,避免数据库psu应用失败
su – oracle $ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -phBaseDir /oracle/software/28204707 注意:如果存在active.so的进程,则通过下面的方法将其kill掉,如: fuser $ORACLE_HOME/历史n/oracle ps -ef|grep pid(上一步获得的进程号,确认是什么进程) kill -9 pid 然后再次执行active.so的检查,直至没有活动的进程。
1.7.4 关闭主库数据库服务
#停止监听 $lsnrctl status $lsnrctl stop #检查监听状态 $lsnrctl status $ps -ef|grep tns #检查剩余会话连接 $ ps -ef |grep LOCAL=NO | wc -l #杀掉正在连接的会话 $ps -ef |grep LOCAL=NO |awk '{print $2}' |xargs kill -9 #停止数据库 SQL> shutdown immediate; #检查数据库告警日志 $tail -1000f /oracle/app/oracle/diag/rdbms/cdc/cdc/trace/alert_cdc.log
1.7.5 数据库软件补丁应用
cd 28204707 $ORACLE_HOM/OPatch/opatch apply
1.7.6 进入数据库应用PSU升级脚本
#刷新数据字典 sqlplus /nolog sql> connect / as sysdba sql> startup sql> @?/rdbms/admin/catbundle.sql psu apply #无效对象修复 SQL> @?/rdbms/admin/utlrp.sql sql> quit
1.8 启动数据库服务并开启日志传输
$lsnrctl start $lsnrctl status SQL> alter system set log_archive_dest_state_2=enable scope=both; SQL>show parameter log_archive_dest_state_2
1.9 开启备库日志恢复
SQL> alter database recover managed standby database disconnect;
二 *实施完成后的验证
2.1 检查主备库补丁信息,组件状态及无效对象信息
#检查数据库软件补丁应用情况 $ORACLE_HOME/OPatch/opatch lsinventory #组件信息 col comp_id for a10 col comp_name for a40 col version for a20 col status for a7 select comp_id, comp_name, version, status from dba_registry; #无效对象 col owner for a15 col object_name for a35 col object_type for a20 col status for a10 select owner,object_name,object_type,status from dba_objects where status='INVALID' order by owner; #补丁信息 col action format a10 col version format a25 col comments format a25 col action_time format a20 alter session set nls_timestamp_format = "yyy-mm-dd hh24:mi:ss"; select ACTION_TIME,ACTION,VERSION,COMMENTS from dba_registry_history;
2.2 确认DataGuard正常
#主库检查 set line 9999 col DEST_NAME format a20 col DESTINATION format a15 col GAP_STATUS format a10 col DB_UNIQUE_NAME format a15 col error format a10 col APPLIED_SCN for 999999999999999 SELECT al.thread#, ads.dest_id, ads.DEST_NAME, (SELECT ads.TYPE || ' ' || ad.TARGET FROM v$archive_dest AD WHERE AD.DEST_ID = ADS.DEST_ID) TARGET, ADS.DATABASE_MODE, ads.STATUS, ads.error, ads.RECOVERY_MODE, ads.DB_UNIQUE_NAME, ads.DESTINATION, ads.GAP_STATUS, (SELECT MAX(sequence#) FROM v$log na WHERE na.thread# = al.thread#) Current_Seq#, MAX(sequence#) Last_Archived, max(CASE WHEN al.APPLIED = 'YES' AND ads.TYPE <> 'LOCAL' THEN al.sequence# end) APPLIED_SEQ#, (SELECT ad.applied_scn FROM v$archive_dest AD WHERE AD.DEST_ID = ADS.DEST_ID) applied_scn FROM (SELECT * FROM v$archived_log V WHERE V.resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)) al, v$archive_dest_status ads WHERE al.dest_id(+) = ads.dest_id AND ads.STATUS != 'INACTIVE' GROUP BY al.thread#, ads.dest_id, ads.DEST_NAME, ads.STATUS, ads.error, ads.TYPE, ADS.DATABASE_MODE, ads.RECOVERY_MODE, ads.DB_UNIQUE_NAME, ads.DESTINATION, ads.GAP_STATUS ORDER BY al.thread#, ads.dest_id;
三 *回退措施
遇到风险:数据库补丁应用失败 回退措施:补丁回滚 #确认脚本的存在 $ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql #关闭数据库和监听 $lsnrctl stop SQL> shut immediate #回滚补丁 opatch rollback -id 28204707 #启动数据库并应用脚本 cd $ORACLE_HOME/rdbms/admin sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @?/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql SQL> QUIT
四 参考文献
How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration (文档 ID 278641.1)
Patch 28204707 - DATABASE PATCH SET UPDATE 11.2.0.4.181016 ReadMe.html