ADG 升級(jí) [11.2.0.1 -> 11.2.0.4]
目標(biāo)
ORACLE ADG 環(huán)境下將 11.2.0.1 升級(jí)到 11.2.0.4
思路
?？焖俟收限D(zhuǎn)移功能;主庫(kù)暫停歸檔傳輸;備庫(kù)安裝新版本軟件,備庫(kù)啟動(dòng)到mount;主庫(kù)安裝軟件,主庫(kù)升級(jí)數(shù)據(jù)庫(kù),主庫(kù)啟用歸檔傳輸,備庫(kù)開啟日志恢復(fù)。
注意
11.2.0.4新版軟件安裝到一個(gè)新目錄下，注意copy之前的spfile、密碼文件、network file、dg_broker_config文件（監(jiān)聽配置文件的靜態(tài)監(jiān)聽部分需要修改），備庫(kù)只升級(jí)軟件版本。數(shù)據(jù)庫(kù)升級(jí)是通過(guò)主庫(kù)升級(jí)后通過(guò)應(yīng)用主庫(kù)歸檔完成升級(jí)和同步的。
概要步驟
1.?？焖俟收限D(zhuǎn)移功能
2.主庫(kù)暫停歸檔向備庫(kù)傳輸
3.備庫(kù)在新目錄下安裝新版本軟件
4.備庫(kù)啟動(dòng)到mount狀態(tài)
5.主庫(kù)安裝軟件，升級(jí)數(shù)據(jù)庫(kù)
6.主庫(kù)啟用歸檔向備庫(kù)傳輸
7.檢查備庫(kù)、主庫(kù)升級(jí)情況
8.修改 compatible 參數(shù)(主備)
9.ADG環(huán)境DB重啟
10.切換到最大可用模式
具體步驟
1.?？焖俟收限D(zhuǎn)移功能
[oracle@mppay2 ~]$ dgmgrl sys/123456
DGMGRL> show configuration
DGMGRL>stop observer
DGMGRL>disable fast_start failover;
DGMGRL> show configuration;
Configuration - my_dg_cfg
Protection Mode: MaxAvailability
Databases:
mppay1 - Primary database
mppay2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
2.主庫(kù)暫停歸檔向備庫(kù)傳輸
DGMGRL> show database mppay1
Database - mppay1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MPPAYUAT
Database Status:
SUCCESS
edit configuration set protection mode as MaxPerformance;
edit database 'mppay1' set state='TRANSPORT-OFF';
edit database 'mppay1' set property LogXptMode ='ASYNC';
edit database 'mppay2' set property LogXptMode ='ASYNC';
show database 'mppay1';
#主庫(kù)
SQL> show parameter dg_broker;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1MPPAY1.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2MPPAY1.dat
dg_broker_start boolean TRUE
#備機(jī)
SQL> show parameter dg_broker;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1MPPAY2.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2MPPAY2.dat
dg_broker_start boolean TRUE
3.備庫(kù)在新目錄下安裝新版本軟件
3.1 關(guān)閉數(shù)據(jù)庫(kù)，監(jiān)聽。
[oracle@mppay2 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@mppay2 ~]$ which sqlplus
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus
[oracle@mppay2 ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> exit
[oracle@mppay2 ~]$ lsnrctl stop
3.2 在新目錄下安裝Oracle 11.2.0.4 軟件(only oftware)
--安裝時(shí)路徑選擇 新的 ORACLE_HOME
--編輯oracle環(huán)境變量，修改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/spfileMPPAYUAT.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1MPPAY2.dat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2MPPAY2.dat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwMPPAYUAT /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
4.備庫(kù)啟動(dòng)到mount狀態(tài)
[oracle@mppay2 ~]$ which lsnrctl
[oracle@mppay2~]$ lsnrctl start
[oracle@mppay2~]$ sqlplus / as sysdba
SQL> startup mount;
5.主庫(kù)安裝軟件，升級(jí)數(shù)據(jù)庫(kù)
5.1 在新目錄下安裝Oracle 11.2.0.4 軟件(only oftware)
--安裝時(shí)選擇新的 ORACLE_HOME ,注:監(jiān)聽不需要配置
--編輯oracle用戶環(huán)境變量，修改ORACLE_HOME新目錄
vim .bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
5.2 關(guān)閉數(shù)據(jù)庫(kù)，監(jiān)聽
[oracle@mppay1 ~]$ lsnrctl stop
[oracle@mppay1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@mppay1 ~]$ which sqlplus
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus
[oracle@mppay1 ~]$ 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
--根據(jù)提示修正不符合升級(jí)條件的地方,清空回收站
SQL> purge DBA_RECYCLEBIN;
SQL> select count(*) from DBA_RECYCLEBIN;
--查看無(wú)效對(duì)象
SQL> select count(*) from dba_objects where status<>'VALID';
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> shutdown immediate
5.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/spfileMPPAYUAT.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1MPPAY1.dat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2MPPAY1.dat /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwMPPAYUAT /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
5.4 升級(jí)數(shù)據(jù)庫(kù)
[oracle@mppay1 dbs]$ which sqlplus
/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus
[oracle@mppay1 dbs]$ sqlplus / as sysdba
SQL> startup UPGRADE
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分鐘（做完后自動(dòng)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.5主庫(kù)啟動(dòng)監(jiān)聽
[oracle@mppay1 ~]$ lsnrctl start
5.6查看oratab
[oracle@mppay1 ~]$ cat /etc/oratab
5.7查看Broker配置
DGMGRL> show configuration
Configuration - my_dg_cfg
Protection Mode: MaxPerformance
Databases:
mppay1 - Primary database
mppay2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
5.7.1 可選項(xiàng)（如出現(xiàn)如下 disabled 問(wèn)題 ）
DGMGRL> show configuration;
Configuration - my_dg_cfg
Protection Mode: MaxPerformance
Databases:
mppay1 - Primary database
mppay2 - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> enable database mppay2;
6.1主庫(kù)啟用歸檔向備庫(kù)傳輸
DGMGRL> edit database 'mppay1' set state='TRANSPORT-ON';
DGMGRL> show database 'mppay1'
Database - mppay1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
DG
Database Status:
SUCCESS
--查看備庫(kù)日志應(yīng)用情況
DGMGRL> show database 'mppay2';
Database - mppay2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 18 minutes 39 seconds (computed 1 second ago)
Apply Rate: 22.45 MByte/s
Real Time Query: OFF
Instance(s):
MPPAYUAT
Database Status:
SUCCESS
#1小時(shí)后
DGMGRL> show database 'mppay2';
Database - mppay2
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
6.2 DGMGRL 顯示主備正常 open 備庫(kù) (如上查看配置）
SQL> alter database open;
DGMGRL> show database 'mppay2';
Database - mppay2
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):
MPPAYUAT
Database Status:
SUCCESS
DGMGRL> show configuration
Configuration - my_dg_cfg
Protection Mode: MaxPerformance
Databases:
mppay1 - Primary database
mppay2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
7.檢查備庫(kù)、主庫(kù)升級(jí)情況
SQL>
set linesize 150
set pagesize 9999
col comp_name format a40
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------------------------------------
Oracle Application Express 3.2.1.00.10 INVALID
Oracle Enterprise Manager 11.2.0.4.0 VALID
Oracle Multimedia 11.2.0.4.0 VALID
Oracle XML Database 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
12 rows selected.
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 參數(shù)(主備)
-- 切記這個(gè)參數(shù)已修改，此次升級(jí)操作就無(wú)法回退，一定要在應(yīng)用經(jīng)過(guò)測(cè)試之后修改。
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;
- ADG環(huán)境DB重啟
關(guān)閉主機(jī)DB
SHUTDOWN IMMEDIATE;
關(guān)閉監(jiān)聽
lsnrctl stop
關(guān)閉備機(jī)DB
SHUTDOWN IMMEDIATE;
關(guān)閉備機(jī)監(jiān)聽
lsnrctl stop
啟動(dòng)備機(jī)監(jiān)聽
lsnrctl start
啟動(dòng)備機(jī)DB
startup;
啟動(dòng)主機(jī)監(jiān)聽
lsnrctl start
啟動(dòng)主機(jī)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 'mppay1' set property LogXptMode ='SYNC';
DGMGRL> edit database 'mppay2' set property LogXptMode ='SYNC';
DGMGRL> edit configuration set protection mode as MaxAvailability;
安裝 oracle 11g r2 時(shí)出現(xiàn)錯(cuò)誤 調(diào)用makefile /oracle/app/oracle/product/11.2.0.4/db_1/sysman/lib/ins_emagent.mk的目標(biāo)nmo時(shí)出錯(cuò)。
主要因?yàn)镃庫(kù)的問(wèn)題,解決辦法就是手動(dòng)指定C庫(kù)位置出現(xiàn)agent nmhs問(wèn)題后,
找到$ORACLE_HOME/sysman/lib/ins_emagent.mk文件,在文件里找字符串 $(MK_EMAGENT_NMECTL)
替換為$(MK_EMAGENT_NMECTL) -lnnz11
注意：lnnz和$(MK_EMAGENT_NMECTL)之間有空格
然后點(diǎn)“重試“按鈕就可以了
修/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
點(diǎn)擊Retry繼續(xù)安裝。
啟動(dòng)數(shù)據(jù)庫(kù)到mount狀態(tài)出現(xiàn)如下問(wèn)題，則如下處理
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.
