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
此時已經轉換完成。
後續操做：
參數調整。操做系統舊軟件目錄，不須要文件清理等