6. RECOMPILE INVALID OBJECTS - Oracle DBA Tips and …



11.2.0.4 DATABASE UPGRADE TO 12C Test database is called oracle12c for this upgrade..The utlu112i.sql script which was run in 11g as the pre-upgrade check script has been discontinued.The new script is called preupgrd.sql - only available in the 12c In 12c $ORACLE_HOME/rdbms/admin areaLaunch sqlplus / as sysdba from 11g database and run this script – preupgrd.sqlcd /u01/oracle/product/12.1.0.1/db_1/rdbms/admin. /oraenvoracle12c - in the old 11g environment.sqlplus / as sysdbaSQL> @preupgrd.sqlLoading Pre-Upgrade Package...Executing Pre-Upgrade Checks...Pre-Upgrade Checks Complete. ************************************************************Results of the checks are located at: /u01/oracle/cfgtoollogs/oracle12c/preupgrade/preupgrade.logPre-Upgrade Fixup Script (run in source database environment): /u01/oracle/cfgtoollogs/ oracle12c/preupgrade/preupgrade_fixups.sqlPost-Upgrade Fixup Script (run shortly after upgrade): /u01/oracle/cfgtoollogs/ oracle12c/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 ************************************************************View the preupgrade.log – View /u01/oracle/cfgtoollogs/oracle12c/preupgrade/preupgrade.logOracle Database Pre-Upgrade Information Tool 02-27-2014 12:57:53Script Version: 12.1.0.1.0 Build: 006********************************************************************** Database Name: oracle12c Version: 11.2.0.4.0 Compatible: 11.2.0.0.0 Blocksize: 8192 Platform: AIX-Based Systems (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] OPTION OFF--> 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] OPTION OFF********************************************************************** [Tablespaces]**********************************************************************--> SYSTEM tablespace is adequate for the upgrade. minimum required size: 1487 MB--> SYSAUX tablespace is adequate for the upgrade. minimum required size: 2203 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--> MGMT_TABLESPACE tablespace is adequate for the upgrade. minimum required size: 4415 MB [No adjustments recommended]******************************************************************************************************************************************** [Flashback Information]**********************************************************************--> name: /u03/oradata/flash_recovery_area--> limit: 8244 MB--> used: 6081 MB--> size: 8244 MB--> reclaim: 0 MB--> files: 12WARNING: --> Flashback Database is enabled. Ensure adequate disk space exists in the flash recovery area before performing the upgrade. Your recovery area will require at least 0 MB - or greater - of free space.******************************************************************************************************************************************** [Pre-Upgrade Checks]**********************************************************************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_030200 has dependent objects.WARNING: --> Database contains INVALID objects prior to upgrade The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the 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/olap/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.4.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 ^^^ ***************************************** ************ 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 ^^^ The Pre-Upgrade Fixup Script will display the Existing Events currently defined********************************************************************** [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 ************ 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.1 ***********************************STEPS RUN BEFORE COMMENCING UPGRADE.1. REMOVE OLAP.From 12c ORACLE_HOME/olap/admincd /u01/oracle/product/12.1.0.1/db_1/olap/adminIn 11g database to be upgraded slplus / as sysdba@/u01/oracle/product/12.1.0.1/db_1/olap/admin/catnoamd.sqlDeletes the olap related stuff.2. GATHER STATSSQL> EXECUTE dbms_stats.gather_dictionary_stats;PL/SQL procedure successfully completed.3. CONFIRM NO MATERIALIZED VIEW REFRESH IN PROGRESSSELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;4. ENSURE NO FILES NEED MEDIA RECOVERY:SELECT * FROM v$recover_file;5. RESOLVE OUTSTANDING DISTRIBUTED TRANSACTIONS: SELECT * FROM dba_2pc_pending;If the query in the previous step returns any rows, then run the following statements:SELECT local_tran_id FROM dba_2pc_pending;EXECUTE dbms_transaction.purge_lost_db_entry('');COMMIT;6. RECOMPILE INVALID OBJECTS@$ORACLE_HOME/rdbms/admin/utlrp.sql7. BACKUP DATABASE AND CREATE RESTORE POINT IF FLASHBACK DATABASE IS ON.In the 12c version Database Upgrade Assistant (DBUA) enables you to specify your backup and restore point.If you wish, a restore point can be created manually too – to play safe.8. CHECK FOR INVALID OBJECTSselect count(*) from dba_objects where status='INVALID'; COUNT(*)---------- 09. PURGE RECYCLE BIN PURGE DBA_RECYCLEBIN;10. INCREASE FLASH RECOVERY AREA SO THAT ARCHIVELOGS CAN BE CREATED.ALTER SYSTEM SET db_recovery_file_dest_size=20000M SCOPE=MEMORY;11. READY TO UPGRADE USING DBUA.Database ruuning from 11g ORACLE_HOMEcd 12c ORACLE_HOME/binexport DISPLAY= XXXXXXexport ORACLE_SID=oracle12cexport ORACLE_HOME=/u01/oracle/product/12.1.0.1/db_1export PATH=$ORACLE_HOME/bin:$PATHexport LD_LIBRARY_PATH=/u01/oracle/product/12.1.0.1/db_1/lib:/usr/lib:/libunset TNS_ADMINunset ORACLE_BASELaunch DBUA from 12c ORACLE_HOME$ ORACLE_HOME/bin ./dbuaChoose the 11g Source Oracle Home Release - NextIgnore warning for LDAP dependencies – choose ignore from drop down list under Action.NextUntick box for configuring EMCreate a restore point so that the DBUA can manage any flashback.DBUA confirms the Recovery Option – creating a guaranteed restore point.FINISHClick on Activity and Alert Log – another window opens up and actual entries are recorded.Check for the restore point – Login to oracle12c as sysdba - using the new 12c Home environment.SQL> select name,time from v$restore_point;NAME--------------------------------------------------------------------------------------------------------------------------------TIME---------------------------------------------------------------------------GRP_139389212636804-MAR-14 08.15.26.000000000 AMAll good.Run following script to compile list of objects invalidated due to the upgradecd $ORACLE_HOME/rdbms/adminsqlplus / as sysdbaSQL> @utluiobj.sql.Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 03-04-2014 13:11:07.This tool lists post-upgrade invalid objects that were not invalidprior to upgrade (it ignores pre-existing pre-upgrade invalid objects)..Owner Object Name Object Type.PL/SQL procedure successfully completed.SQL >If for any reason you wish to restore the database to the 11.2.0.4 version – a fallback.Click on Restore DatabaseYesCheck the alert log –Archived Log entry 570 added for thread 1 sequence 514 ID 0x3a80da51 dest 1:Wed Mar 05 14:58:13 2014Shutting down instance (abort)License high water mark = 22Wed Mar 05 14:58:13 2014USER (ospid: 37355720): terminating the instanceWed Mar 05 14:58:14 2014Instance terminated by USER, pid = 37355720Wed Mar 05 14:58:14 2014Instance shutdown completeStarts up instance from old 11g homeStarting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Initial number of CPU is 8Number of processor cores in the system is 4CELL communication is configured to use 0 interface(s):CELL IP affinity details: NUMA status: non-NUMA system cellaffinity.ora status: N/ACELL communication will use 1 IP group(s): Grp 0:Picked latch-free SCN scheme 3Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DESTAutotune of undo retention is turned on.IMODE=BRILAT =27LICENSE_MAX_USERS = 0SYS auditing is disabledStarting up:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning option.ORACLE_HOME = /u01/oracle/product/11.2.0.4/db_1System name: AIXNode name: p520test1Release: 1Version: 6Machine: 0009061CD700Using parameter settings in server-side spfile /u01/oracle/product/11.2.0.4/db_1/dbs/spfiletest11g.oraSystem parameters with non-default values:Lost write protection disabledCompleted: ALTER DATABASE MOUNTWed Mar 05 14:58:38 2014flashback database to restore point grp_1393996885298Flashback Restore StartSpfile /u01/oracle/product/11.2.0.4/db_1/dbs/spfiletest11g.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.Wed Mar 05 14:59:15 2014Flashback Restore CompleteFlashback Media Recovery StartFlashback Media Recovery CompleteCompleted: flashback database to restore point grp_1393996885298alter database open resetlogsCheck View /etc/oratabtest11g:/u01/oracle/product/11.2.0.4/db_1:Ndbua changes the entry back to the 11g home. /oraenvtest11gsqlplus / as sysdbaSQL> select comp_name,version from dba_registry;COMP_NAME--------------------------------------------------------------------------------VERSION------------------------------Oracle Application Express4.2.1.00.08OWB11.2.0.3.0Oracle Enterprise Manager11.2.0.4.0COMP_NAME--------------------------------------------------------------------------------VERSION------------------------------Spatial11.2.0.4.0Oracle Multimedia11.2.0.4.0Oracle XML Database11.2.0.4.0COMP_NAME--------------------------------------------------------------------------------VERSION------------------------------Oracle Text11.2.0.4.0Oracle Expression Filter11.2.0.4.0Oracle Rules Manager11.2.0.4.0COMP_NAME--------------------------------------------------------------------------------VERSION------------------------------Oracle Workspace Manager11.2.0.4.0Oracle Database Catalog Views11.2.0.4.0Oracle Database Packages and Types11.2.0.4.0COMP_NAME--------------------------------------------------------------------------------VERSION------------------------------JServer JAVA Virtual Machine11.2.0.4.0Oracle XDK11.2.0.4.0Oracle Database Java Packages11.2.0.4.0COMP_NAME--------------------------------------------------------------------------------VERSION------------------------------OLAP Analytic Workspace11.2.0.3.0Oracle OLAP API11.2.0.3.017 rows selected. ................
................

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

Google Online Preview   Download