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"，共同学习，共同成长！！！
