圖形化升級單機oracle 11.2.0.4 到 12.2.0.1

1. 講補丁包上傳到 Oracle server ，解壓、安裝

[oracle@11g tmp]$ unzip linuxx64_12201_database.zip

2. 檢查當前版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> set linesize 150;
set pagesize 9999;
col comp_name format a40;
SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;SQL> SQL> SQL>

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

18 rows selected.

3. 檢查磁碟空間

select a.tablespace_name, round(a.total_size) "total_size(mb)",
round(a.total_size) - round(b.free_size,3) "unsed_size(mb)",
round(b.free_size,3) "free_size(mb)",
round(b.free_size/total_size *100,2) ||'%' free_rate
from
(select tablespace_name,sum(bytes) /1024/1024 total_size
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_size
from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name(+);

4.為資料庫做冷備份

RMAN>run {
shutdown immediate;
startup mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
backup full tag='db_full_bak' database format '/home/oracle/rmanbak/full_cold_%d_%s.bak';
alter database open;
}

RMAN> list backup;

資料庫正常關閉後，還需要備份Oracle主目錄，目的還是為了升級失敗時，能夠還原出資料庫軟體到升級前的版本。
重點是如下目錄：

ORACLE_HOME/dbs

ORACLE_HOME/network/admin

ORACLE_HOME/hostname_dbname

ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_dbname

[oracle@11g ~]$ env|grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/home/oracle/app
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0.4/dbhome_1
[oracle@11g ~]$ tar cvf oracle.ora /home/oracle

5. 關閉資料庫例項及相關程式 (靜態監聽、動態監聽、EOM、相關服務)

--停止資料庫外部程式
[root@11g ~]#ps -ef|grep -v grep |grep LOCAL=NO|awk '{print $2}'|xargs kill -9

SQL> shutdown immediate;
[oracle@11g rmanbak]$ cat /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
[oracle@11g rmanbak]$ lsnrctl stop
[oracle@11g rmanbak]$ lsnrctl status
[oracle@11g rmanbak]$ ps -ef |grep ora_|grep -v grep
[oracle@11g rmanbak]$ netstat -an |grep 1521
[oracle@11g rmanbak]$ netstat -an |grep 1158

6. 開始升級操作
[oracle@11g ~]$ cd /tmp/database
[oracle@11g database]$ ./runInstaller

6.1 不勾選，點選 next

注意這裡的安裝位置，我之前的安裝目錄是11.2.0.4，我新目錄是12.2.0.1.0 即將oracle 安裝到其他位置，這樣可以減少當機時間，也是oracle 推薦的方法。

有pdksh或者ksh其中一個就行

[root@11g ~]# rpm -qa|grep ksh
pdksh-5.2.14-1.i386

執行到78%的時候彈出如下視窗：用 root 使用者跑這個指令碼

進度到了83%的時候，自動彈出dbua視窗

停在37%執行了很久，耐心等待

選abort強制，進行如下操作

選yes

最後點選close。

7. 修改環境變數

然後，再修改oracle使用者的.bash_profile

ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1.0/db_1

然後source ~/.bash_profile

檢視 cat /etc/oratab 【內容應該是新目錄】

8. 登入檢視，升級後版本

[oracle@11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 2 16:43:38 2015

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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
---------------------------------------- ------------------------------ ----------------------
Oracle Database Catalog Views 12.2.0.1.0 UPGRADED
Oracle Database Packages and Types 12.2.0.1.0 UPGRADED
JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED
Oracle XDK 12.2.0.1.0 UPGRADED
Oracle Database Java Packages 12.2.0.1.0 UPGRADED
OLAP Analytic Workspace 12.2.0.1.0 UPGRADED
Oracle Workspace Manager 12.2.0.1.0 UPGRADED
Oracle Text 12.2.0.1.0 UPGRADED
Oracle XML Database 12.2.0.1.0 UPGRADED
Oracle Multimedia 12.2.0.1.0 UPGRADED
Spatial 12.2.0.1.0 UPGRADED
Oracle OLAP API 12.2.0.1.0 UPGRADED
OLAP Catalog 11.2.0.4.0 OPTION OFF
Oracle Application Express 5.0.4.00.12 UPGRADED

14 rows selected.

SQL> select count(*) from dba_objects where status<>'VALID';

COUNT(*)
----------
8357

SQL> select * from utl_recomp_errors;

no rows selected

編譯失效物件

SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql

Session altered.
--多次執行utlrp.sql，失效物件數量不再變化
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.

SQL> select count(*) from dba_objects where status<>'VALID';

COUNT(*)
----------
44
統計失效的物件：
SQL> set lin 300;
set pagesize 300;
col owner for a30;
select owner, object_type, status, count(*)
from dba_objects
where status='INVALID'
group by owner, object_type, status
order by owner, object_type;
OWNER OBJECT_TYPE STATUS COUNT(*)
------------------------------ ----------------------- ------- ----------
SYS FUNCTION INVALID 2
SYS PROCEDURE INVALID 5
SYS TRIGGER INVALID 2
SYS VIEW INVALID 1
TBTB FUNCTION INVALID 13
TBTB PROCEDURE INVALID 3
TBTB VIEW INVALID 18

檢視具體失效物件
col owner for a20;
col object_name for a32;
col object_type for a16;
col status for a8;
select owner, object_name, object_type, status
from dba_objects
where status='INVALID'
order by 1,2,3;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- -------------------------------- ---------------- --------
SYS DEPEND_RELATIONSHIP PROCEDURE INVALID
SYS EVENT_TABLE PROCEDURE INVALID
SYS FN_GETSQLBYSQLID FUNCTION INVALID
SYS GET_SQLTEXT FUNCTION INVALID
SYS OLAPISHUTDOWNTRIGGER TRIGGER INVALID
SYS OLAPISTARTUPTRIGGER TRIGGER INVALID
SYS P_COLL_DML_INSERT PROCEDURE INVALID
SYS P_MONI_INVALID_INDEX PROCEDURE INVALID
SYS P_MONI_SQL PROCEDURE INVALID
SYS V$SQLAREA_TOTAL VIEW INVALID
TBTB CONTRACT_CALV FUNCTION INVALID
TBTB CONTRACT_CONTTYPE FUNCTION INVALID
TBTB CONTRACT_GDQX FUNCTION INVALID
TBTB EMPLEVEL_LT FUNCTION INVALID
TBTB EMPLEVEL_LZ FUNCTION INVALID
TBTB EMPLEVEL_QC FUNCTION INVALID
TBTB EMPLEVEL_XJRS FUNCTION INVALID
TBTB GETTRMOBJ_BYOID FUNCTION INVALID
TBTB GETUNITFULLNAME FUNCTION INVALID
TBTB GET_CONTRACTOIDBYEMPOID FUNCTION INVALID
TBTB GET_EMPDATA FUNCTION INVALID
TBTB JLSXTG PROCEDURE INVALID
TBTB LUOC FUNCTION INVALID
TBTB PROC_COPYTEMPLATE PROCEDURE INVALID
TBTB REP_1 FUNCTION INVALID
TBTB SET_EMPDATA PROCEDURE INVALID
TBTB TB_CNB_PAYRECORDINFO VIEW INVALID
TBTB TB_V_CRPT_SELCOUNT VIEW INVALID
TBTB TB_V_EMPASSIGNEDJOB VIEW INVALID
TBTB TB_V_EMPPOSITION VIEW INVALID
TBTB TB_V_LEADERLEVEL VIEW INVALID
TBTB TB_V_LEADER_SET VIEW INVALID
TBTB TB_V_ORGUNIT VIEW INVALID
TBTB TB_V_PMG_EVALUESCORE VIEW INVALID
TBTB TB_V_PMG_REWARDANDPUNISH VIEW INVALID
TBTB TB_V_REC_CANDIDATE VIEW INVALID
TBTB TB_V_TABLE VIEW INVALID
TBTB TB_V_TMG_MONTHREPORT VIEW INVALID
TBTB TB_V_TRM_CLASSINFO VIEW INVALID
TBTB TB_V_TRM_CLASSMONEY VIEW INVALID
TBTB TB_V_TRM_COURSE VIEW INVALID
TBTB VIEW_BIZPROCINST VIEW INVALID
TBTB VIEW_BIZWORKITEM VIEW INVALID
TBTB VIEW_ORG_PERSON_ROLE_UNIT VIEW INVALID

44 rows selected.

手動單個單個編譯失效物件，結果每個報 Warning: Procedure altered with compilation errors.
ALTER PROCEDURE SYS.DEPEND_RELATIONSHIP COMPILE;
ALTER PROCEDURE SYS.P_MONI_INVALID_INDEX COMPILE;
ALTER PROCEDURE SYS.P_MONI_SQL COMPILE;
ALTER PROCEDURE SYS.P_COLL_DML_INSERT COMPILE;
ALTER FUNCTION SYS.GET_SQLTEXT COMPILE;
ALTER TRIGGER SYS.OLAPISTARTUPTRIGGER COMPILE;
ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER COMPILE;
ALTER FUNCTION TBTB.GET_EMPDATA COMPILE;
ALTER FUNCTION TBTB.GETUNITFULLNAME COMPILE;
ALTER PROCEDURE TBTB.SET_EMPDATA COMPILE;
ALTER PROCEDURE TBTB.JLSXTG COMPILE;
ALTER PROCEDURE TBTB.PROC_COPYTEMPLATE COMPILE;
ALTER FUNCTION TBTB.GETTRMOBJ_BYOID COMPILE;
ALTER FUNCTION TBTB.LUOC COMPILE;
ALTER FUNCTION TBTB.REP_1 COMPILE;
ALTER FUNCTION TBTB.GET_CONTRACTOIDBYEMPOID COMPILE;
ALTER FUNCTION TBTB.EMPLEVEL_LT COMPILE;
ALTER FUNCTION TBTB.EMPLEVEL_LZ COMPILE;
ALTER FUNCTION TBTB.EMPLEVEL_QC COMPILE;
ALTER FUNCTION TBTB.CONTRACT_CALV COMPILE;
ALTER FUNCTION TBTB.CONTRACT_CONTTYPE COMPILE;
ALTER FUNCTION TBTB.CONTRACT_GDQX COMPILE;
ALTER FUNCTION TBTB.EMPLEVEL_XJRS COMPILE;
ALTER FUNCTION SYS.FN_GETSQLBYSQLID COMPILE;
ALTER PROCEDURE SYS.EVENT_TABLE COMPILE;

至此失效物件無法valid，推測是版本升級廢棄物件，這裡先不討論。

9. 修改compatible引數，若修改完成，此次升級操作就無法回退，一定要在應用經過測試之後修改。

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4
noncdb_compatible boolean FALSE

QL> create spfile from pfile;

File created.
SQL> shutdown immediate;
SQL> startup;
SQL> alter system set compatible='12.2.0.1.0' scope=spfile;

System altered.
SQL> shutdown immediate;
SQL> startup;

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0.1.0
noncdb_compatible boolean FALSE

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
---------------------------------------- ------------------------------ ----------------------
Oracle Database Catalog Views 12.2.0.1.0 VALID
Oracle Database Packages and Types 12.2.0.1.0 VALID
JServer JAVA Virtual Machine 12.2.0.1.0 VALID
Oracle XDK 12.2.0.1.0 VALID
Oracle Database Java Packages 12.2.0.1.0 VALID
OLAP Analytic Workspace 12.2.0.1.0 VALID
Oracle Workspace Manager 12.2.0.1.0 VALID
Oracle Text 12.2.0.1.0 VALID
Oracle XML Database 12.2.0.1.0 VALID
Oracle Multimedia 12.2.0.1.0 VALID
Spatial 12.2.0.1.0 VALID
Oracle OLAP API 12.2.0.1.0 VALID
OLAP Catalog 11.2.0.4.0 OPTION OFF
Oracle Application Express 5.0.4.00.12 VALID

14 rows selected.

升級過程有忽略，abort,很簡單粗暴，只用來初次嘗試，用來學習與參考，失效物件還要進一步確認，升級過程中有不合理之處請指正，轉載請標明出處

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31383567/viewspace-2138383/，如需轉載，請註明出處，否則將追究法律責任。

圖形化升級單機oracle 11.2.0.4 到 12.2.0.1

相關文章