ADG 升级 [11.2.0.1 -> 11.2.0.4]
目标
ORACLE ADG 环境下将 11.2.0.1 升级到 11.2.0.4
思路
停快速故障轉移功能;主库暂停归档传输;备库安装新版本软件,备库启动到mount;主库安装软件,主库升级数据库,主库启用归档传输,备库开启日志恢复。
注意
11.2.0.4新版软件安装到一个新目录下，注意copy之前的spfile、密码文件、network file、dg_broker_config文件（监听配置文件的静态监听部分需要修改），备库只升级软件版本。数据库升级是通过主库升级后通过应用主库归档完成升级和同步的。
概要步骤
1.停快速故障轉移功能
2.主库暂停归档向备库传输
3.备库在新目录下安装新版本软件
4.备库启动到mount状态
5.主库安装软件，升级数据库
6.主库启用归档向备库传输
7.检查备库、主库升级情况
8.修改 compatible 参数(主备)
9.ADG环境DB重启
10.切换到最大可用模式
具体步骤
1.停快速故障轉移功能
[oracle@XAG110 ~]$ dgmgrl sys/123456
DGMGRL> show configuration
DGMGRL>stop observer
DGMGRL>disable fast_start failover;
2.主库暂停归档向备库传输
show database 'DG22'
edit configuration set protection mode as MaxPerformance;
edit database 'DG22' set state='TRANSPORT-OFF';
edit database 'DG22' set property LogXptMode ='ASYNC';
edit database 'DG23' set property LogXptMode ='ASYNC';
show database 'DG22'
#主機
SQL> show parameter dg_broker;
NAME VALUE
------------------------------------
dg_broker_config_file1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1DG22.dat
dg_broker_config_file2 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2DG22.dat
dg_broker_start true
#備機
SQL> show parameter dg_broker;
NAME VALUE
------------------------------------
dg_broker_config_file1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1DG23.dat
dg_broker_config_file2 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2DG23.dat
dg_broker_start true
3.备库在新目录下安装新版本软件
3.1 关闭数据库，监听。
[oracle@XAG110 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@XAG110 ~]$ which sqlplus
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus
[oracle@XAG110 ~]$ sqlplus / as sysdba
SQL> shutdown immediate
[oracle@XAG110 ~]$ lsnrctl stop
3.2 在新目录下安装Oracle 11.2.0.4 软件(only oftware)
--安裝時路徑選擇 新的 ORACLE_HOME
--编辑oracle环境变量，修改ORACLE_HOME为新的目录
vim /home/oracle/.bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
3.3 copy 配置文件
[oracle@oracle2 admin]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/*.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/
--修正listener.ora 及 tnsnames.ora 中ORACLE_HOME
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDG.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1DG23.dat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2DG23.dat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDG /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwDG
4.备库启动到mount状态
[oracle@XAG110 ~]$ lsnrctl start
[oracle@XAG110 ~]$ sqlplus / as sysdba
SQL> startup mount;
5.主库安装软件，升级数据库
5.1 备份数据库
--略
5.2 关闭数据库，监听
[oracle@XAG109 ~]$ lsnrctl stop
[oracle@XAG109 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@XAG109 ~]$ which sqlplus
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus
[oracle@XAG109 ~]$ sqlplus / as sysdba
SQL> spool /home/oracle/upgrade01.log
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql
SQL> spool off
--根据提示修正不符合升级条件的地方,清空回收站
SQL> purge DBA_RECYCLEBIN;
SQL> select count(*) from DBA_RECYCLEBIN;
--查看无效对象
SQL> select count(*) from dba_objects where status<>'VALID';
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> shutdown immediate
5.3 在新目录下安装Oracle 11.2.0.4 软件(only oftware)
--安裝時選擇新的 ORACLE_HOME ,注:监听不需要配置
--编辑oracle用户环境变量，修改ORACLE_HOME新目录
vim .bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
5.4 copy 配置文件
[oracle@oracle2 admin]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/*.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/
--修正listener.ora 、 tnsnames.ora 中ORACLE_HOME
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDG.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1DG22.dat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2DG22.dat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDG /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapwDG
5.5 升级数据库
[oracle@XAG109 dbs]$ which sqlplus
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus
[oracle@XAG109 dbs]$ sqlplus / as sysdba
SQL> startup UPGRADE
SQL>
set echo on
spool /home/oracle/update02.log
set time on
SQL>@/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catupgrd.sql
--3o分钟（做完后自動shutdown immediate）
SQL> sqlplus / as sysdba
SQL> startup
SQL> select count(*) from dba_objects where status<>'VALID';
456
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlrp.sql
SQL> select count(1) from dba_objects where status<>'VALID';
0
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112s.sql
SQL> select count(1) from dba_objects where status<>'VALID';
0
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> shutdown immediate
SQL> startup
5.6主库启动监听
[oracle@XAG109 ~]$ lsnrctl start
5.7查看oratab
[oracle@XAG109 ~]$ cat /etc/oratab
5.8查看Broker配置
DGMGRL> show configuration
Configuration - DG22broker
Protection Mode: MaxAvailability
Databases:
DG22 - Primary database
DG23 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
6.主库启用归档向备库传输
DGMGRL> edit database 'DG22' set state='TRANSPORT-ON';
DGMGRL> show database 'DG22'
Database - DG22
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
DG
Database Status:
SUCCESS
--查看备库日志应用情况
DGMGRL> show database 'DG23';
Database - DG23
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 1 hour(s) 5 minutes 20 seconds (computed 6 seconds ago)
Apply Lag: 1 hour(s) 18 minutes 57 seconds (computed 6 seconds ago)
Apply Rate: 201.00 KByte/s
Real Time Query: OFF
Instance(s):
DG
Database Status:
SUCCESS
DGMGRL> show database 'DG23';
Database - DG23
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 hour(s) 13 minutes 27 seconds (computed 0 seconds ago)
Apply Rate: 2.66 MByte/s
Real Time Query: OFF
Instance(s):
DG
Database Status:
SUCCESS
#1小时后
DGMGRL> show database 'DG23';
Database - DG23
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 728.00 KByte/s
Real Time Query: OFF
Instance(s):
DG
Database Status:
SUCCESS
5.11 DGMGRL 显示主备正常 open 备库 (如上查看配置）
SQL> alter database open;
DGMGRL> show database 'DG23';
Database - DG23
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
DG
Database Status:
SUCCESS
DGMGRL> show configuration
Configuration - DG22broker
Protection Mode: MaxPerformance
Databases:
DG22 - Primary database
DG23 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
7.检查备库、主库升级情况
SQL>
set linesize 150
set pagesize 9999
col comp_name format a40
SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
----------------------------------------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.4.0 VALID
Spatial 11.2.0.4.0 VALID
Oracle Multimedia 11.2.0.4.0 VALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Text 11.2.0.4.0 VALID
Oracle Expression Filter 11.2.0.4.0 VALID
Oracle Rules Manager 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
OLAP Analytic Workspace 11.2.0.4.0 VALID
Oracle OLAP API 11.2.0.4.0 VALID
SQL>
SQL> select count(*) from dba_objects where status<>'VALID';
COUNT(*)
----------
0
SQL> select * from utl_recomp_errors;
no rows selected
SQL> select ACTION_TIME,ACTION,NAMESPACE,VERSION,ID from registry$history;
19-OCT-18 12.54.28.668386 PM VIEW INVALIDATE 8289601
9-OCT-18 12.54.28.834581 PM UPGRADE SERVER 11.2.0.4.0
8.修改 compatible 参数(主备)
-- 切记这个参数已修改，此次升级操作就无法回退，一定要在应用经过测试之后修改。
SQL> show parameter comp
NAME TYPE VALUE
------------------------------------
cell_offload_compaction string ADAPTIVE
compatible string 11.2.0.0.0
nls_comp string BINARY
plsql_v2_compatibility boolean FALSE
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;
- ADG环境DB重启
關閉主機DB
SHUTDOWN IMMEDIATE;
關閉監聽
lsnrctl stop
關閉備機DB
SHUTDOWN IMMEDIATE;
關閉備機監聽
lsnrctl stop
啟動備機監聽
lsnrctl start
啟動備機DB
startup;
啟動主機監聽
lsnrctl start
啟動主機DB
startup;
SQL> show parameter comp;
NAME VALUE
------------------------------------ ---------------------------------
cell_offload_compaction ADAPTIVE
compatible 11.2.0.4.0
nls_comp BINARY
plsql_v2_compatibility FALSE
- 切换到最大可用模式
DGMGRL> edit database 'DG22' set property LogXptMode ='SYNC';
DGMGRL> edit database 'DG23' set property LogXptMode ='SYNC';
DGMGRL> edit configuration set protection mode as MaxAvailability;
安装 oracle 11g r2 时出现错误 调用makefile /oracle/app/oracle/product/11.2.0.4/db_1/sysman/lib/ins_emagent.mk的目标nmo时出错。
主要因为C库的问题,解决办法就是手动指定C库位置出现agent nmhs问题后,
找到$ORACLE_HOME/sysman/lib/ins_emagent.mk文件,在文件里找字符串 $(MK_EMAGENT_NMECTL)
替换为$(MK_EMAGENT_NMECTL) -lnnz11
注意：lnnz和$(MK_EMAGENT_NMECTL)之间有空格
然后点“重试“按钮就可以了
修/u01/app/oracle/product/11.2.0/db_1/ctx/lib/ins_ctx.mk，将 ctxhx: $(CTXHXOBJ)
$(LINK_CTXHX)\ (INSO_LINK) 修改为：
ctxhx: $(CTXHXOBJ) -static
$(LINK_CTXHX) $(CTXHXOBJ) $(INSO_LINK) /usr/lib64/stdc.a
点击Retry继续安装。
启动数据库到mount状态出现如下问题，则如下处理
SQL> startup mount;
ORA-00845: MEMORY_TARGET not supported on this system
[root@XAG110 ~]# df -h | grep shm
tmpfs 1.5G 709M 788M 48% /dev/shm
[root@XAG110 ~]# cat /etc/fstab | grep tmpfs
[root@XAG110 ~]# mount -o remount,size=4G /dev/shm
[root@XAG110 ~]# df -h | grep shm
tmpfs 4.0G 709M 3.4G 18% /dev/shm
SQL> startup mount;
Database mounted.
