Oracle 11g升級到12C
Oracle 11g 升級到12C
環境說明：
OS:Oracle Linux Server release 6.3
DB:Oracle 11.2.0.4.0
架構：單機
升級說明：
原庫(Source Database)：
Name：dapuchai
Release:11.2.0.4.0
Oracle Home:/u01/app/oracle/product/11.2.0/db_1
目標庫(Target Database):
Name：dapuchai
Release:12.2.0.1.0
Oracle Home:/u01/app/oracle12/product/12.2.0.1/db_1
當前Oracle使用最多的版本仍然是11g(11.2.0.1.0和11.2.0.4.0)，很多公司已經陸續將資料庫升級到12C、18C、19C(還有馬上到來的20C)；
Release Schedule of Current Database Releases
(Doc ID 742060.1)
先總結下升級過程中遇到的問題：
1 ：ORA-00845:MEMORY_TARGET not supported on this system
問題原因： 通常情況下MEMORY_TARGET或MEMORY_MAX_TARGET不能大於/dev/shm
解決方案： 停止升級，手動調大/dev/shm，同時將作業系統記憶體由2G增加到4G；在重新升級就好了。
雖然官方文件顯示可以使用2G，記憶體最好大一些，不低於4G；
2 ：升級卡在49%很長時間(5h)沒有變化
問題原因 ：檢查發現升級過程中產生大量的歸檔檔案，將作業系統根目錄佔滿，導致資料庫掛起，無法繼續升級；
解決方案： 嘗試手動刪除部分歸檔檔案，釋放磁碟空間，升級程式仍無法繼續，中斷升級，清理空間，重新升級，建議在升級過程中時刻關注告警日誌、磁碟使用率、伺服器負載情況等，或將資料庫改成非歸檔模式在升級；
3 升級過程中不要點Pause
資料庫卡住49%很長時間沒反應，嘗試按下Pause，圖示變灰，沒出現繼續按鈕，此時在點下Cancel,升級程式退出；
嘗試使用DBUA工具重新升級，提示資料庫不是讀寫模式，並且資料庫已經是12.2.0.1.0版本了；
看下資料庫是否可用：
當前資料庫是OPEN MIGRATE狀態；
SQL> select status from v$instance;
STATUS
------------
OPEN MIGRATE
嘗試重啟資料庫，報錯ORA-00904:
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 8621040 bytes
Variable Size 989856784 bytes
Database Buffers 536870912 bytes
Redo Buffers 8155136 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "ACDRROWTSINTCOL#": invalid identifier
Process ID: 11182
Session ID: 237 Serial number: 56031
查詢相關的報錯也無法解決，清除資訊重新來過，此時就能看出升級前備份的重要性了；
ORA-00904: "ACDRROWTSINTCOL#": invalid identifier (Doc ID 2614644.1)
12C: While Upgrading RDBMS Using DBUA Fails with Error "[FATAL] [DBT-20024] The local instance for the specified database "db" could not be started" (Doc ID 2304874.1)
升級過程如下：
本實驗是將11.2.0.4.0升級到12.2.0.1.0版本，是可以直接升級的，如果低於11.2.0.3版本，是無法直接升級到12.2.0.1.0；
一：環境檢查
1.1檢查磁碟空間、伺服器負載、目錄許可權、系統引數、安裝12.2.0.1.0所需packages等;
二：備份資料庫
[root@cjc ~]# mkdir /dbbackup/rman -p
[root@cjc ~]# chown oracle.oinstall /dbbackup -R
[root@cjc ~]# su - oracle
[oracle@cjc ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@cjc ~]$ export ORACLE_SID=dapuchai
[oracle@cjc ~]$ rman target /
run{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup database tag 'before_upgrade' format '/dbbackup/rman/%d_full_%T_%U.bak';
sql 'alter system archive log current';
backup archivelog all tag 'arch_cjcdb' format '/dbbackup/rman/%d_arch_%T_%U.bak';
backup current controlfile tag 'ctl_cjcdb' format '/dbbackup/rman/%d_ctl_%T_%U.bak';
release channel ch1;
release channel ch2;
}
RMAN> list backup;
三：安裝Oracle 12.2.0.1.0軟體到新目錄
[root@cjc oracle_122010_for_linux]# pwd
/package/oracle_122010_for_linux
[root@cjc oracle_122010_for_linux]# unzip linuxx64_12201_database.zip
[root@cjc oracle_122010_for_linux]# chown oracle.oinstall database/ -R
[root@cjc oracle_122010_for_linux]# ll -rth
total 3.3G
drwxr-xr-x 7 oracle oinstall 4.0K Jan 27 2017 database
-rw-r--r-- 1 root root 3.3G Jan 11 18:02 linuxx64_12201_database.zip
[root@cjc ~]# mkdir /u01/app/oracle12/product/12.2.0.1/db_1 -p
[root@cjc ~]# chown oracle.oinstall /u01/app/oracle12 -R
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;
SQL> set pagesize 1000;
SQL> col comp_name format a40;
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------------------------------------
OWB 11.2.0.4.0 VALID
Oracle Application Express 3.2.1.00.12 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.
SQL> shutdown immediate
[oracle@cjc ~]$ lsnrctl stop
[oracle@cjc database]$ pwd
/package/oracle_122010_for_linux/database
[oracle@cjc database]$ ./runInstaller
選Install database software only和Upgrade an existing database都可以，Upgrade an existing database相當於先執行Install database software only在執行DBUA；
[root@cjc ~]# /tmp/CVU_12.2.0.1.0_oracle/runfixup.sh
All Fix-up operations were completed successfully.
[root@cjc ~]# yum install smartmontools*
[root@cjc ~]# yum install libstdc-*
[root@cjc ~]# yum install libgcc-*
[root@cjc ~]# /u01/app/oracle12/product/12.2.0.1/db_1/root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle12/product/12.2.0.1/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] :
Oracle Trace File Analyzer (TFA - User Mode) is available at :
/u01/app/oracle12/product/12.2.0.1/db_1/suptools/tfa/release/tfa_home/bin/tfactl
OR
Oracle Trace File Analyzer (TFA - Daemon Mode) can be installed by running this script :
/u01/app/oracle12/product/12.2.0.1/db_1/suptools/tfa/release/tfa_home/install/roottfa.sh
四：DBUA工具升級資料庫
[oracle@cjc database]$ cd /u01/app/oracle12/product/12.2.0.1/db_1/bin/
[oracle@cjc bin]$ ./dbua
5 驗證
升級完成，修改環境變數，指向新的ORACLE_BASE和ORACLE_HOME
[oracle@cjc ~]$ cat .bash_profile
……
#export ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE=/u01/app/oracle12
#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME=/u01/app/oracle12/product/12.2.0.1/db_1
……
[oracle@cjc ~]$ source .bash_profile
[oracle@cjc ~]$ echo $ORACLE_HOME
/u01/app/oracle12/product/12.2.0.1/db_1
---監聽狀態
[oracle@cjc ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2020 00:19:24
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias DAPUCHAI
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 12-JAN-2020 23:58:52
Uptime 0 days 0 hr. 20 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle12/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle12/diag/tnslsnr/cjc/dapuchai/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cjc)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "dapuchai" has 1 instance(s).
Instance "dapuchai", status READY, has 1 handler(s) for this service...
Service "dapuchaiXDB" has 1 instance(s).
Instance "dapuchai", status READY, has 1 handler(s) for this service...
The command completed successfully
SQL> set linesize 150;
SQL> set pagesize 1000;
SQL> col comp_name format a40;
SQL> 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.
檢視沒有無效的物件，在升級過程中已經對無效物件進行修復了；
通過11g升級的12C預設是no-cdb，後期可以在改成cdb
檢視測試資料
檢視資料庫檔案目錄
歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2673120/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 靜默升級oracle 11g （從11.2.0.1升級到11.2.0.4）Oracle
- Oracle 12C升級到18COracle
- Windows升級到oracle 11g的異機物理升級文件（冷備）WindowsOracle
- Oracle 12c升級指南Oracle
- ORACLE10G升級11GOracle
- 【RMAN】Oracle11g透過rman升級到12cOracle
- 【Oracle升級】Oracle指令碼升級11g to 19c non-CDBOracle指令碼
- Oracle從10g升級到11g詳細步驟Oracle
- oracle 升級12c引數樣例Oracle
- oracle 10 rac 升級 10.2.0.1升級到10.2.0.5Oracle
- 【OCM】Oracle 12C OCMU 12c OCM升級考試大綱Oracle
- oracle 12c non-cdb升級成cdb模式Oracle模式
- oracle rac 11.2.0.3 升級到11.2.0.4Oracle
- 【資料庫升級】Oracle指令碼升級12c CDB to 19c CDB資料庫Oracle指令碼
- OGG_mysql 12c複製到OGG_oracle 11g部署方案MySqlOracle
- Oracle 12c升級需遵循的簡單步驟NFOracle
- oracle資料庫11.2.0.3升級到11.2.0.4Oracle資料庫
- Oracle 18C升級到19COracle
- Oracle 11g/12c 監聽器白名單的配置Oracle
- 【UP_ORACLE】Oracle 19c之從19.3升級到19.9Oracle
- ORACLE9I升級到10G(zt)Oracle
- Oracle 11g,12c,18c,19,21,23 RUOracle
- Oracle 12c系列(九) | 通過unplug與plug方式升級pdb資料庫Oracle資料庫
- Oracle 字符集從GBK升級到Utf8Oracle
- 使用DBUA升級 Oracle 11.2.0.4到Oracle 19C的問題記錄Oracle
- PL/SQL Developer連線到Oracle 12cSQLDeveloperOracle
- Oracle 10.2.0.5升級至11.2.0.4Oracle
- Oracle 11g RAC到單例項OGG同步Oracle單例
- ccproject升級到1135Project
- Oracle 9i 11g歷史庫升級遷移資料至19c CDBOracle
- 【UPGRADE】升級到Oracle18c基本步驟參考(留存）Oracle
- OGG 12c mysql複製到oracle部署方案MySqlOracle
- mongodb單機從3.2升級到4.0.4升級MongoDB
- A Oracle Data Guard Broker 升級和降級Oracle
- 12. Oracle版本、補丁及升級——12.3. 升級Oracle
- Oracle：Oracle RAC 11.2.0.4 升級為 19cOracle
- Cocospod 升級到指定版本
- MySQL 5.7 升級到 8.0MySql