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/，如需轉載，請註明出處，否則將追究法律責任。