Upgrading Oracle Database 11.2.0.3 to 12.1.0



Upgrading Oracle Database 11.2.0.3 to 12.1.0 (Oracle 12c) with DBUA Oracle Database Version:???????????? Oracle Database 11gR2 (11.2.0.3) (64-bit)Operating System: ? ? ? ? ? ? ? ? ? ? ? ? Oracle Enterprise Linux 5 (64-bit)Pre upgrade stepsInstall Oracle 12c rdbms software create 12c ORACLE_HOME environment variable in the 12c.env of “oracle” userRun Preupgrade script in order to collect required information about database (optional)Remove Enterprise Manager Database Control repositoryGather dictionary statsDBUAPost upgrade stepsEdit compatible parameterupgrade database timezonedeinstall 11g software[oracle@pansoft ~]$ vi 12c.envexport ORACLE_HOME=/u01/12capp/oracle/product/12.1.0/dbhome_1export PATH =$PATH:$ORACLE_HOME/bin:.?Run Preupgrade script in order to collect required information about database status.SQL> @/u01/12capp/oracle/product/12.1.0/dbhome_1/rdbms/admin/preupgrd.sqlLoading Pre-Upgrade Package...Executing Pre-Upgrade Checks...Pre-Upgrade Checks Complete. ************************************************************Results of the checks are located at: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/prod/preupgrade/preupgrade.logPre-Upgrade Fixup Script (run in source database environment): /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/prod/preupgrade/preupgrade_fixups.sqlPost-Upgrade Fixup Script (run shortly after upgrade): /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/prod/preupgrade/postupgrade_fixups.sql$more /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/prod/preupgrade/preupgrade.logOracle Database Pre-Upgrade Information Tool 12-09-2014 19:18:31Script Version: 12.1.0.1.0 Build: 006********************************************************************** Database Name: PROD Version: 11.2.0.3.0 Compatible: 11.2.0.0.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone file: V14********************************************************************** [Renamed Parameters] [No Renamed Parameters in use]******************************************************************************************************************************************** [Obsolete/Deprecated Parameters] [No Obsolete or Desupported Parameters in use]********************************************************************** [Component List]**********************************************************************--> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> Oracle Enterprise Manager Repository [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle Multimedia [upgrade] VALID --> Oracle Spatial [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] VALID --> Oracle OLAP API [upgrade] VALID ********************************************************************** [Tablespaces]**********************************************************************--> SYSTEM tablespace is adequate for the upgrade. minimum required size: 1216 MB--> SYSAUX tablespace is adequate for the upgrade. minimum required size: 1455 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--> EXAMPLE tablespace is adequate for the upgrade. minimum required size: 309 MB [No adjustments recommended]******************************************************************************************************************************************** [Pre-Upgrade Checks]**********************************************************************WARNING: --> Process Count may be too low Database has a maximum process count of 150 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.WARNING: --> Enterprise Manager Database Control repository found in the database In Oracle Database 12c, Database Control is removed during the upgrade. To save time during the Upgrade, this action can be done prior to upgrading using the following steps after copying rdbms/admin/emremove.sql from the new Oracle home - Stop EM Database Control: $> emctl stop dbconsole - Connect to the Database using the SYS account AS SYSDBA: @emremove.sql Without the set echo and serveroutput commands you will not be able to follow the progress of the script.WARNING: --> Existing DBMS_LDAP dependent objectsDatabase contains schemas with objects dependent on DBMS_LDAP package. Refer to the Upgrade Guide for instructions to configure Network ACLs. USER APEX_030200 has dependent RMATION: --> OLAP Catalog(AMD) exists in database Starting with Oracle Database 12c, OLAP is desupported. If you are not using the OLAP Catalog component and want to remove it, then execute the ORACLE_HOME/oraolap/admin/catnoamd.sql script before or after the RMATION: --> 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 toupgrading the database.To gather dictionary statistics execute the following commandwhile 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 weeksafter the upgrade using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;^^^ MANUAL ACTION SUGGESTED ^^^********************************************************************** ************ Summary ************ 0 ERRORS exist in your database. 3 WARNINGS that Oracle suggests are addressed to improve database performance. 2 INFORMATIONAL messages 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.1Remove??Enterprise Manager??Database Control??repositoryoracle@pansoft$ emctl stop dbconsoleSQL> @/u01/12capp/oracle/product/12.1.0/dbhome_1/rdbms/admin/emremove.sqlGather dictionary statsSQL> EXECUTE dbms_stats.gather_dictionary_stats;[oracle@pansoft ~]$ . 12c.env [oracle@pansoft ~]$ export ORACLE_SID=prod[oracle@pansoft ~]$ dbuaIf the prerequisite checks highlight any issues, take the appropriate action to fix the issues. When you are happy with the prerequisites, click the "Next" button. Edit parameter sql>alter system set compatible='12.1.0.1.0' scope=spfile;Upgrade database TimezoneScripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)oracle@pansoft ] lsupg_tzv_check.sql countTSTZdata.sql upg_tzv_apply.sql SQL> @countTSTZdata.sqlEstimating amount of TSTZ data.This might take some time.....For SYS tables first...Note: empty tables are not listed.Owner.Tablename.Columnname - count star of that columnSYS.ALERT_QT.SYS_NC00029$ - Count * is : 19SYS.AQ$_ALERT_QT_S.CREATION_TIME - Count * is : 5SYS.AQ$_ALERT_QT_S.DELETION_TIME - Count * is : 5SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - Count * is : 5SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - Count * is : 3SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - Count * is : 3SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - Count * is : 3SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - Count * is : 1SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - Count * is : 1SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - Count * is : 1SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.CREATION_TIME - Count * is : 1SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.DELETION_TIME - Count * is : 1SYS.AQ$_KUPC$DATAPUMP_QUETAB_S.MODIFICATION_TIME - Count * is : 1SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - Count * is : 3SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - Count * is : 3SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - Count * is : 3SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - Count * is : 1SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - Count * is : 1SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - Count * is : 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - Count * is : 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - Count * is : 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - Count * is : 1SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - Count * is : 1SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - Count * is : 1SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - Count * is : 1SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - Count * is : 3SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - Count * is : 3SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - Count * is : 3SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - Count * is : 1SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - Count * is : 1SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - Count * is : 1SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - Count * is : 1SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - Count * is : 1SYS.KET$_CLIENT_CONFIG.FIELD_2 - Count * is : 7SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - Count * is : 7SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - Count * is : 30SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - Count * is : 30SYS.RADM_FPTM$.TSWTZ_COL - Count * is : 1SYS.REG$.NTFN_GROUPING_START_TIME - Count * is : 1SYS.REG$.REG_TIME - Count * is : 1SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - Count * is : 129SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - Count * is : 11SYS.SCHEDULER$_JOB.END_DATE - Count * is : 24SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - Count * is : 24SYS.SCHEDULER$_JOB.LAST_END_DATE - Count * is : 24SYS.SCHEDULER$_JOB.LAST_START_DATE - Count * is : 24SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - Count * is : 24SYS.SCHEDULER$_JOB.START_DATE - Count * is : 24SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - Count * is : 115SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - Count * is : 115SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - Count * is : 115SYS.SCHEDULER$_SCHEDULE.END_DATE - Count * is : 4SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - Count * is : 4SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - Count * is : 9SYS.SCHEDULER$_WINDOW.END_DATE - Count * is : 9SYS.SCHEDULER$_WINDOW.LAST_START_DATE - Count * is : 9SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - Count * is : 9SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - Count * is : 9SYS.SCHEDULER$_WINDOW.START_DATE - Count * is : 9SYS.WRI$_ALERT_HISTORY.CREATION_TIME - Count * is : 70SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - Count * is : 70SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - Count * is : 1SYS.WRI$_ALERT_OUTSTANDING.TIME_SUGGESTED - Count * is : 1SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - Count * is : 29380SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - Count * is : 29380SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - Count * is : 65264SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - Count * is : 65264SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - Count * is : 2123SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - Count * is : 2123SYS.WRI$_OPTSTAT_OPR.END_TIME - Count * is : 207SYS.WRI$_OPTSTAT_OPR.SPARE6 - Count * is : 207SYS.WRI$_OPTSTAT_OPR.START_TIME - Count * is : 207SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - Count * is : 4829SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - Count * is : 4829SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - Count * is : 4829SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - Count * is : 3927SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - Count * is : 3927SYS.XS$PRIN.END_DATE - Count * is : 14SYS.XS$PRIN.START_DATE - Count * is : 14Total count * of SYS TSTZ columns ROWS is : 217515There are in total 145 SYS TSTZ columns..For non-SYS tables ...Note: empty tables are not listed.Owner.Tablename.Columnname - count star of that columnGSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - Count * is : 1GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - Count * is : 1GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - Count * is :1IX.AQ$_ORDERS_QUEUETABLE_S.CREATION_TIME - Count * is : 4IX.AQ$_ORDERS_QUEUETABLE_S.DELETION_TIME - Count * is : 4IX.AQ$_ORDERS_QUEUETABLE_S.MODIFICATION_TIME - Count * is : 4IX.AQ$_STREAMS_QUEUE_TABLE_S.CREATION_TIME - Count * is : 1IX.AQ$_STREAMS_QUEUE_TABLE_S.DELETION_TIME - Count * is : 1IX.AQ$_STREAMS_QUEUE_TABLE_S.MODIFICATION_TIME - Count * is : 1WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - Count * is : 1WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - Count * is : 1WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - Count * is : 1WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - Count * is : 1WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - Count * is : 1Total count * of non-SYS TSTZ columns ROWS is : 23There are in total 30 non-SYS TSTZ columns.Total Minutes elapsed : 0SQL> @upg_tzv_check.sqlINFO: Starting with RDBMS DST update : NO actual RDBMS DST update will be done by this : If an ERROR occurs the script will EXIT : Doing checks for known issues ...INFO: Database version is 12.1.0.1 .INFO: Database RDBMS DST version is DSTv14 .INFO: No known issues : Now detecting new RDBMS DST version.A prepare window has been successfully : Newest RDBMS DST version detected is DSTv18 .INFO: Next step is checking all TSTZ : It might take a while before any further output is seen ...A prepare window has been successfully : A newer RDBMS DST version than the one currently used is : Note that NO DST update was yet : Now run upg_tzv_apply.sql to do the actual RDBMS DST : Note that the upg_tzv_apply.sql script willINFO: restart the database 2 times WITHOUT any confirmation or prompt.SQL> @upg_tzv_apply.sqlINFO: If an ERROR occurs the script will EXIT : The database RDBMS DST version will be updated to DSTv18 .WARNING: This script will restart the database 2 timesWARNING: WITHOUT asking ANY confirmation.WARNING: Hit control-c NOW if this is not : 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 830930944 bytesFixed Size 2293832 bytesVariable Size 553652152 bytesDatabase Buffers 272629760 bytesRedo Buffers 2355200 bytesDatabase mounted.Database : Starting the RDBMS DST : Upgrading all SYS owned TSTZ : It might take time before any further output is seen ...An upgrade window has been successfully : Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.Database closed.Database dismounted.ORACLE instance shut down.ORACLE instance started.Total System Global Area 830930944 bytesFixed Size 2293832 bytesVariable Size 553652152 bytesDatabase Buffers 272629760 bytesRedo Buffers 2355200 bytesDatabase mounted.Database : Upgrading all non-SYS TSTZ : 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: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"Number of failures: 0Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"Number of failures: 0Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"Number of failures: 0Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"Number of failures: 0Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"Number of failures: 0Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"Number of failures: 0Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"Number of failures: 0Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"Number of failures: 0Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"Number of failures: 0Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"Number of failures: 0Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"Number of failures: 0INFO: Total failures during update of TSTZ data: 0 .An upgrade window has been successfully : Your new Server RDBMS DST version is DSTv18 .INFO: The RDBMS DST update is successfully : Make sure to exit this sqlplus : Do not use it for timezone related selects.SQL> SELECT version FROM v$timezone_file; VERSION---------- 181 row selected.deinstall 11g softwareoracle@panosft ]?$ cd $ORACLE_HOME/deinstall [oracle@pansoft deinstall]$ ./deinstalls ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download