大致步骤：
- 检查升级条件
- 安装12c数据库软件
- 执行dbua升级db或者采用手工执行命令的方式，但根据官方资料看，建议放弃这个想法，DBUA 真的简化了很多，如果手工执行，会多很多步骤，这样会增加出错的概率。
- 检查数据库状态和无效对象。
（升级之前最好将数据库job全部停用，使用job_queue_processes= 0）
- 上传软件，并解压
- 核实数据库和操作系统相关信息
[oracle@zg3 soft]$ uname -a
Linux zg3 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@zg3 soft]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 15 19:19:11 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
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> col comp_name for a35
col version for a20
col name for a20
set linesize 1000
set pagesize 1000SQL> SQL> SQL> SQL>
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> SELECT name, value FROM v$parameter WHERE name = 'compatible';
NAME VALUE
-------------- --------------------------------------
compatible 11.2.0.0.03. rman对数据库执行全备
RMAN> backup database plus archivelog delete input format '/oracle/back/full_%U.dbf';4. 关闭数据库和监听，如果有EM也需要关闭
SQL> shutdown immediate;
[oracle@zg3 ~]$ lsnrctl stop
[oracle@zg3 ~]$ ps -ef |grep ora5. 备份oracle主目录，升级失败时，能够还原出数据库软件到升级前的版本。
tar –cvf oraInventory.zip /oracle/app/oraInventory/
tar –cvf product.zip /oracle/app/oracle/product/6. 安装12.1.0.1数据库软件
这里报错在mos上有记录
[root@zg3 soft]# rpm -ivh redhat-release_package.rpm
Preparing... ########################################### [100%]
1:redhat-release ########################################### [100%]
重新dbca安装，没有报错。
更换软件路径
[root@zg3 Packages]# rpm -ivh ksh-20120801-10.el6.x86_64.rpm
error: Failed dependencies:
pdksh conflicts with ksh-20120801-10.el6.x86_64
其实pdksh和ksh有一个就行。
[root@zg3 Packages]# rpm -e pdksh*
[root@zg3 Packages]# rpm -ivh ksh-20120801-10.el6.x86_64.rpm
Preparing... ########################################### [100%]
1:ksh ########################################### [100%]
[root@zg3 Packages]# /oracle/app/oracle/product/12.1.0/db_1/root.sh
Performing root user operation for Oracle 12c
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/app/oracle/product/12.1.0/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.
自动弹出dbua的窗口
这里是自动将数据库打开，进行先决性检查。因此可以直接连上库，进行调整：
[oracle@zg3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 15 20:42:32 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
剩下的警告可以忽略，下一步
这里可以选择创建rman全备，或者创建还原点，或者自己的备份
这里没仔细看，直接yes继续。后续升级过程中就报错了：
报错处理：
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 0 0 0
BACKUP PIECE 24.74 0 2
IMAGE COPY 0 0 0
FLASHBACK LOG 16.98 14.56 14
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
SQL> select * from v$flashback_database_log ;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
0 1440 734003200 0
SQL> select * from v$restore_point;
select * from v$restore_point
*
ERROR at line 1:
ORA-38701: Flashback database log 13 seq 13 thread 1: "/oracle/app/oracle/fast_recovery_area/ORCL/flashback/o1_mf_fsssbz18_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database flashback off ;
Database altered.
SQL> shutdown immediate;
SQL>startup mount;
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
RMAN> list restore point all;
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----
1813747 GUARANTEED 2018-09-15 21:02:19 GRP_1537016539260
SQL> drop restore point GRP_1537016539260;
Restore point dropped.
SQL> alter database open;
Database altered.
SQL> alter database flashback on;
Database altered.
RMAN> list restore point GRP_1537016539260;
SCN RSP Time Type Time Name
---------------- ------------------- ---------- ------------------- ----重新使用dbua升级库
SQL> alter system set db_recovery_file_dest_size='10G';
System altered.
100%之后，点击upgrade results查看升级结果。这里100% 截图没有截。
7. 修改oracle用户的环境变量
[oracle@zg3 ~]$ vi .bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1 --改为新的路径
[oracle@zg3 ~]$ source .bash_profile 8. 查看各个组件版本信息
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME VERSION STATUS
----------------------------------- -------------------- ----------------------
Oracle Application Express 4.2.0.00.27 VALID
OWB 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.4.0 OPTION OFF
Spatial 12.1.0.1.0 VALID
Oracle Multimedia 12.1.0.1.0 VALID
Oracle XML Database 12.1.0.1.0 VALID
Oracle Text 12.1.0.1.0 VALID
Oracle Workspace Manager 12.1.0.1.0 VALID
Oracle Database Catalog Views 12.1.0.1.0 VALID
Oracle Database Packages and Types 12.1.0.1.0 VALID
JServer JAVA Virtual Machine 12.1.0.1.0 VALID
Oracle XDK 12.1.0.1.0 VALID
Oracle Database Java Packages 12.1.0.1.0 VALID
OLAP Analytic Workspace 12.1.0.1.0 VALID
Oracle OLAP API 12.1.0.1.0 VALID
15 rows selected.
这里可以看到部分组件状态不正确，这时老版本的组件，不能直接通过升级上SQL进行删除。执行下述操作：
SQL> @$ORACLE_HOME/rdbms/admin/emremove.sql
SQL> @$ORACLE_HOME/olap/admin/catnoamd.sql
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.
COMP_NAME VERSION STATUS
----------------------------------- -------------------- ----------------------
Oracle Application Express 4.2.0.00.27 VALID
OWB 11.2.0.4.0 VALID
Spatial 12.1.0.1.0 VALID
Oracle Multimedia 12.1.0.1.0 VALID
Oracle XML Database 12.1.0.1.0 VALID
Oracle Text 12.1.0.1.0 VALID
Oracle Workspace Manager 12.1.0.1.0 VALID
Oracle Database Catalog Views 12.1.0.1.0 VALID
Oracle Database Packages and Types 12.1.0.1.0 VALID
JServer JAVA Virtual Machine 12.1.0.1.0 VALID
Oracle XDK 12.1.0.1.0 VALID
Oracle Database Java Packages 12.1.0.1.0 VALID
OLAP Analytic Workspace 12.1.0.1.0 VALID
Oracle OLAP API 12.1.0.1.0 VALID
14 rows selected.
这里可以看到过期的组件OLAP Catalog已被清理。关于OWB的版本显示为11.2.0.4原因如下：
Starting with Oracle Database 12c, Oracle Warehouse Builder (OWB) is not installed as part of the software for Oracle Database. An installer for Oracle Warehouse Builder is available on Oracle Technology Network. OWB components that may exist from earlier releases are not upgraded as part of the Oracle Database upgrade process.
9. 查看编译版本
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
NAME VALUE
------------------------------------------- -------------------------------------
compatible 11.2.0.0.0
SQL> alter system set compatible='12.1.0.1.0' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2296864 bytes
Variable Size 956302304 bytes
Database Buffers 3405774848 bytes
Redo Buffers 11624448 bytes
ORA-38880: Cannot advance compatibility from 11.2.0.0.0 to 12.1.0.1.0 due to
guaranteed restore points
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /oracle/app/oracle/product/12.1.0/db_1/dbs/spfileorcl.ora
SQL> create pfile from spfile;
File created.
编辑pfile文件，修改compatible参数为11.2.0.0.0 。
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2296864 bytes
Variable Size 956302304 bytes
Database Buffers 3405774848 bytes
Redo Buffers 11624448 bytes
Database mounted.
Database opened.
删除restore point
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
GRP_1537019022141
SQL> drop restore point GRP_1537019022141;
Restore point dropped.
这里需要注意，只是在控制文件中删除，操作系统上仍然存在。
SQL> select name from v$restore_point;
no rows selected
SQL> alter system set compatible='12.1.0.1.0' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2296864 bytes
Variable Size 956302304 bytes
Database Buffers 3405774848 bytes
Redo Buffers 11624448 bytes
Database mounted.
Database opened.
SQL> SELECT name, value FROM v$parameter WHERE name = 'compatible';
NAME VALUE
------------------------------------------- -------------------------------------
compatible 12.1.0.1.010. 核实/etc/oratab
[oracle@zg3 dbs]$ cat /etc/oratab
orcl:/oracle/app/oracle/product/12.1.0/db_1:N --自动变为12c的目录11. 升级后执行postupgrade_fixups.sql检查
12c的postupgrade_fixups.sql脚本需要执行preupgrd.sql会自动安装脚本
SQL> @$ORACLE_HOME/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
/oracle/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/oracle/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/oracle/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
************************************************************
Fixup scripts must be reviewed prior to being executed.
************************************************************
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
You MUST resolve the above errors prior to upgrade
************************************************************SQL> @/oracle/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2018-09-16 09:13:59 Version: 12.1.0.1 Build: 006
Beginning Pre-Upgrade Fixups...
**********************************************************************
Check Tag: DBMS_LDAP_DEPENDENCIES_EXIST
Check Summary: Check for dependency on DBMS_LDAP package
Fix Summary: Network Objects must be reviewed manually.
**********************************************************************
Fixup Returned Information:
WARNING: --> Existing DBMS_LDAP dependent objects
Database contains schemas with objects dependent on DBMS_LDAP package.
Refer to the Upgrade Guide for instructions to configure Network ACLs.
USER APEX_040200 has dependent objects.
USER APEX_030200 has dependent objects.
**********************************************************************
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
*****************************************
************ Existing Events ************
*****************************************
Please review any defined events prior to upgrading.
To view existing nondefault events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE (UPPER(name) = '_TRACE_EVENTS') AND isdefault='FALSE';
Changes will need to be made in the init.ora or spfile.
^^^ MANUAL ACTION SUGGESTED ^^^
******** Existing Events ********
******** Query may return no Rows Selected *****
******** Existing Trace Events ********
******** Query may return no Rows Selected *****
**************************************************
************* Fixup Summary ************
1 fixup routine generated an INFORMATIONAL message that should be reviewed.
**************** Pre-Upgrade Fixup Script Complete *********************12. no-CDB转换为CDB
先用DBCA建库
安装完毕，这个时候在服务器上可以看到两个数据库在运行
[oracle@zg3 db_1]$ ps -ef |grep smon
oracle 25736 1 0 09:06 ? 00:00:00 ora_smon_orcl
oracle 27414 1 0 10:12 ? 00:00:00 ora_smon_orcl1
oracle 27643 17083 0 10:23 pts/3 00:00:00 grep smon其中orcl是11g升上12c的no_cdb数据库。orcl1是手动创建的12c的cdb数据库。
no_cdb转cdb
SQL> select cdb,name,dbid from v$database;
CDB NAME DBID
--- --------- ----------
NO ORCL 1514683624
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
SYSTEM /oracle/app/oracle/oradata/orcl/system01.dbf
SYSAUX /oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /oracle/app/oracle/oradata/orcl/undotbs01.dbf
USERS /oracle/app/oracle/oradata/orcl/users01.dbf
ZG /oracle/app/oracle/oradata/orcl/zg01.dbf
关闭数据库并用read only打开no_cdb数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2296864 bytes
Variable Size 956302304 bytes
Database Buffers 3405774848 bytes
Redo Buffers 11624448 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,cdb from v$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
生成xml的文件
SQL> exec dbms_pdb.describe(pdb_descr_file =>'/tmp/NCDB.xml');
PL/SQL procedure successfully completed.
关闭no_cdb数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.连接到新建的orcl1实例，新创建的cdb
export ORACLE_SID=orcl1
sqlplus / as sysdba
SQL> select name,open_mode,cdb from v$database;
NAME OPEN_MODE CDB
--------- -------------------- ---
ORCL1 READ WRITE YES
创建pdb
SQL> create pluggable database orcl using '/tmp/NCDB.xml' copy FILE_NAME_CONVERT = ('/oracle/app/oracle/oradata/orcl/','/oracle/app/oracle/oradata/orcl/pdborcl/');
Pluggable database created.
（这里需要注意，如果是OMF管理的文件，需要手动一一制定）
（而且这里是copy操作，原文件在操作系统保留）
切换pdb
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL MOUNTED
SQL> alter session set container=orcl;
Session altered.
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
………………
需要一段时间
SQL> show con_name
CON_NAME
------------------------------
ORCL
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCL MOUNTED
SQL> alter pluggable database open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCL READ WRITE NO
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
ORCL READ WRITE此时已经转换完成。
后续操作：
参数调整。操作系统旧软件目录，不需要文件清理等
287
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
