oracle软件升级11.2.0.4升级到12.1.0.1
大体步骤：sql
- 检查升级条件
- 安装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.0
3. rman对数据库执行全备bash
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 ora
5. 备份oracle主目录，升级失败时，可以还原出数据库软件到升级前的版本。session
tar –cvf oraInventory.zip /oracle/app/oraInventory/ tar –cvf product.zip /oracle/app/oracle/product/
6. 安装12.1.0.1数据库软件oracle
这里报错在mos上有记录app
[root@zg3 soft]# rpm -ivh redhat-release_package.rpm Preparing... ########################################### [100%] 1:redhat-release ########################################### [100%] 从新dbca安装，没有报错。
更换软件路径ide
[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的窗口post
这里是自动将数据库打开，进行先决性检查。所以能够直接连上库，进行调整：ui
[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.0
10. 核实/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
此时已经转换完成。
后续操做：
参数调整。操做系统旧软件目录，不须要文件清理等