Upgrade database Release 11gR2 to Release 12c
This Article show how to manual upgrade 11.2.0.3 Oracle database ti non-plugged Database 12c
Starting from database 11203
SQL> select instance_name,version,statup_time,status from v$instance;
INSTANCE_NAME VERSION STARTUP_T STATUS
————- —————————- ———-
DB11 11.2.0.3.0 28-JAN-14 OPEN
Below Minimum database version that can be directly upgraded to Oracle 12c Release 1 (12.1)
Source Database Target Database
10.2.0.5 12.1.x
11.1.0.7 12.1.x
11.2.0.2 or higher 12.1.x
The following database versions will require an indirect upgrade path:
Source Database Intermediate Upgrade Path for Target Database Target Database
11.2.0.1 —-> 11.2.0.2 or higher —-> 12.1.x
11.1.0.6 —-> 11.1.0.7 or 11.2.0.2 or higher —-> 12.1.x
10.2.0.4(or earlier)—-> 10.2.0.5 or later direct upgrade version —-> 12.1.x
10.1.0.5(or earlier)—-> 10.2.0.5 or later direct upgrade version —-> 12.1.x
9.2.0.8 (or earlier)—-> 9.2.0.8 –> 11.2.0.2 or higher —-> 12.1.x
· Run Preupgrade script in order to collect required information about database status.
SQL> @preupgrd.sql
Loading Pre-Upgrade Package…
WARNING: Failed to open preupgrade.log for write access
script will generate terminal output only
WARNING: Failed to open preupgrade_fixups.sql for write access
script will not generate fixup scripts.
Executing Pre-Upgrade Checks…
Oracle Database Pre-Upgrade Information Tool 01-28-2014 15:56:49
Script Version: 12.1.0.1.0 Build: 006
**********************************************************************
Database Name: DB11
Version: 11.2.0.3.0
Compatible: 11.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V14
**********************************************************************
[Renamed Parameters]
“audit_trail” old value was “DB_EXTENDED”;
–> new name is “audit_trail”, new value is “DB,EXTENDED”
[Changes required in Oracle Database init.ora or spfile]
**********************************************************************
**********************************************************************
[Obsolete/Deprecated Parameters]
[No Obsolete or Desupported Parameters in use]
**********************************************************************
[Component List]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
**********************************************************************
[Tablespaces]
**********************************************************************
ERROR: –> SYSTEM tablespace is not large enough for the upgrade.
currently allocated size: 512 MB
minimum required size: 587 MB
increase current size by: 75 MB
tablespace is NOT AUTOEXTEND ENABLED.
–> SYSAUX tablespace is adequate for the upgrade.
minimum required size: 500 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
minimum required size: 400 MB
–> TEMP tablespace is adequate for the upgrade.
minimum required size: 60 MB
[make adjustments in the current environment]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
WARNING: –> Process Count may be too low
Database has a maximum process count of 50 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
INFORMATION: –> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 11.2.0.3.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************
[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 ^^^
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
************ Summary ************
1 ERROR exist that must be addressed prior to performing your upgrade.
1 WARNING that Oracle suggests are addressed to improve database performance.
1 INFORMATIONAL message that should be reviewed prior to your upgrade.
After your database is upgraded and open in normal mode you must run
rdbms/admin/catuppst.sql which executes several required tasks and completes
the upgrade process.
You should follow that with the execution of rdbms/admin/utlrp.sql, and a
comparison of invalid objects before and after the upgrade using
rdbms/admin/utluiobj.sql
If needed you may want to upgrade your timezone data using the process
described in My Oracle Support note 977512.1
***********************************
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
*** Scripts/Logs are not being Generated ***preupgrade.log
************************************************************
====>> 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
************************************************************
As output script we need to do some preupgrading step before proceed.
· Gather dictionary stats
SQL>EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
· Increase System Tablespace
SQL> alter database datafile ‘/app/oradata/DB11/system/system01.dbf’ resize 1024M;
Database altered.
· Increase processes number in init File (in this case no spfile is used)
In this case no Access control list was configured into database, so no particular action are required in order to upgrade this part.
Database Time zone will be updated at the end of upgrade processes.
· By now we can check that no recovery / backup are running and other check before proceeding to next steps
SQL> SELECT * FROM v$recover_file;
no rows selected
SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;
no rows selected
SQL> SELECT * FROM dba_2pc_pending;
no rows selected
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in (‘SYS’,’SYSTEM’);
USERNAME DEFAULT_TABLESPACE
—————————— ——————————
SYS SYSTEM
SYSTEM SYSTEM
SQL> SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name=’AUD$’;
OWNER TABLESPACE_NAME
—————————— ——————————
SYS SYSTEM
SQL> SELECT name FROM v$controlfile;
NAME
——————————————————————————–
/app/oracle/admin/DB11/data/ctl/control01.ctl
/app/oracle/admin/DB11/data/ctl/control02.ctl
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
——————————————————————————–
/app/oradata/DB11/system/system01.dbf
/app/oradata/DB11/system/sysaux01.dbf
/app/oradata/DB11/undo/undo01.dbf
SQL> SELECT group#, member FROM v$logfile;
GROUP# MEMBER
———- —————————
1 /redo/redo_a/redo01a.log
1 /redo/redo_b/redo01b.log
2 /redo/redo_a/redo02a.log
2 /redo/redo_b/redo02b.log
SQL> select comp_id,version,status from dba_registry
COMP_ID VERSION STATUS
————————– ———-
CATALOG 11.2.0.3.0 VALID
CATPROC 11.2.0.3.0 VALID
· No Enterprise manager component was installed into database , so no action are required before proceed
Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express .
Therefore no repository is needed anymore .
Remove Enterprise Manager Database Control repository MANUALLY using the following command
Note : You will get the emremove.sql script in the Oracle 12c home .
Copy the emremove.sql script from the 12c $ORACLE_HOME/rdbms/admin to the source $ORALCE_HOME/rdbms/admin and then execute on the source database prior to upgrade.
$emctl stop dbcontrol
SQL> @ ?/rdbms/admin/emremove.sql
If the EM repository is not removed up front ,it will be automatically removed during the catuppst.sql post-upgrade phase.
· Copy pfile/spfile and password file to the new Oracle Home (12c)
· Shutdown the database.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
· Load new Oracle Variable to point ORACLE_HOME 12c installation
[oracle@ciukinobox ~]$ . .bash_profile12
· Modify /etc/oratab entry
[oracle@ciukinobox ~]$ more /etc/oratab
db11:/app/oracle/product/12.1.0:N
· Upgrade database
[oracle@ciukinobox]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@ciukinobox admin]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jan 28 17:14:51 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2290416 bytes
Variable Size 423628048 bytes
Database Buffers 104857600 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
· The new script catctl replace catupgrd script used until 11gR2, in parallel mode (6 is the number of parallel processes used during upgrade phase)
[oracle@ciukinobox admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql
Analyzing file catupgrd.sql
Log files in /app/oracle/product/12.1.0/diagnostics
14 scripts found in file catupgrd.sql
Next path: catalog.sql
32 scripts found in file catalog.sql
Next path: catproc.sql
37 scripts found in file catproc.sql
Next path: catptabs.sql
…..
……………..
…………………………….
[Phase 56] type is 1 with 1 Files
catshutdown.sql
Using 6 processes.
Serial Phase #: 0 Files: 1 Time: 91s
Serial Phase #: 1 Files: 3 Time: 56s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 18s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 24s
……….
………………………..
Grand Total Time: 1349s
· Startup upgraded database and run post upgrade step
SQL> startup
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2290416 bytes
Variable Size 423628048 bytes
Database Buffers 104857600 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
SQL> @utlu121s.sql
.
Oracle Database 12.1 Post-Upgrade Status Tool 01-28-2014 17:46:46
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. UPGRADED 12.1.0.1.0 00:14:05
Oracle XML Database
. VALID 12.1.0.1.0 00:02:36
Final Actions
. 00:01:26
Total Upgrade Time: 00:18:10
PL/SQL procedure successfully completed.
SQL> @catuppst.sql
Session altered.
Session altered.
Session altered.
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP POSTUP_BGN 2014-01-28 17:47:27
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP CATREQ_BGN 2014-01-28 17:47:27
PL/SQL procedure successfully completed.
catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE
PL/SQL procedure successfully completed.
……….
……………….
……………………………
SQL> PROMPT Updating registry…
Updating registry…
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, ‘APPLY’,
7 SYS_CONTEXT(‘REGISTRY$CTX’,’NAMESPACE’),
8 ‘12.1.0.1’,
9 0,
10 ‘PSU’,
11 ‘Patchset 12.1.0.0.0’);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DB11_APPLY_2014Jan28_17_47_29.log
Session altered.
Session altered.
· Recompile full database
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_BGN 2014-01-28 17:50:01
PL/SQL procedure successfully completed.
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_END 2014-01-28 17:50:03
OBJECTS WITH ERRORS
——————-
0
ERRORS DURING RECOMPILATION
—————————
0
SQL> select owner,object_name,object_type,status from dba_objects where status<>’VALID’;
no rows selected
· Check post upgrade components with dbupgdiag script [Below an extract of logfile]
SQL> alter session set nls_language=’American’;
Session altered.
SQL> @dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /home/oracle
28_Jan_2014_1753 .log
DB11_
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 01-28-2014 17:53:11
===============
Hostname
===============
ciukinobox
===============
Database Name
===============
DB11
===============
Database Uptime
===============
17:45 28-JAN-14
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production 0
PL/SQL Release 12.1.0.1.0 – Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 – Production 0
NLSRTL Version 12.1.0.1.0 – Production 0
=============
Compatibility
=============
Compatibility is set as 11.2.0
================
Archive Log Mode
================
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /app/oracle/product/12.1.0/dbs/arch
Oldest online log sequence 23
Current log sequence 24
DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#
===========================================
Tablespace and the owner of the aud$ table
===========================================
OWNER TABLESPACE_NAME
———— ——————————
SYS SYSTEM
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
0
================
Time Zone version
================
14
================
Local Listener
================
================
Component Status
================
Comp ID Component Status Version Org_Version Prv_Version
——- ———————————- ——— ————– ————– ————–
CATALOG Oracle Database Catalog Views VALID 12.1.0.1.0 11.2.0.3.0 11.2.0.3.0
CATPROC Oracle Database Packages and Types VALID 12.1.0.1.0 11.2.0.3.0 11.2.0.3.0
XDB Oracle XML Database VALID 12.1.0.1.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
Number of Invalid Objects
——————————————————————
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
——————————————————————
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
DOC>
DOC> Result referencing the string ‘B023’ ==> Database was created as 32-bit
DOC> Result referencing the string ‘B047’ ==> Database was created as 64-bit
DOC> When String results in ‘B023’ and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#
Metadata Initial DB Creation Info
——– ———————————–
B047 Database was created as 64-bit
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
Counting duplicate objects ….
COUNT(1)
———-
4
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
Querying duplicate objects ….
Upload db_upg_diag_DB11_28_Jan_2014_0750.log from “/home/oracle” directory
· Upgrade database Timezone
SQL> SELECT version FROM v$timezone_file;
VERSION
———-
14
SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Database version is 12.1.0.1 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: Doing checks for known issues …
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv18 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen …
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv18 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2290416 bytes
Variable Size 423628048 bytes
Database Buffers 104857600 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen …
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
RACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2290416 bytes
Variable Size 423628048 bytes
Database Buffers 104857600 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen …
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_S”
Number of failures: 0
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_L”
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv18 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL> SELECT version FROM v$timezone_file;
VERSION
———-
18
· At this point we have database upgraded to the new Release
SQL> select instance_name,version,status from v$instance;
INSTANCE_NAME VERSION STATUS
————— ————– —————–
DB11 12.1.0.1.0 OPEN