Upgrade database from 11g to 12c using DBUA
DBUA in oracle 12c simplifies the upgrade process a lot .
STEPS:
PRE-REQUSITES:
1. Install Oracle 12c RDMBS home – How to install oracle 12c binary
2.Enable flashback database:
This is to ensure that, if upgrade fails , we can restore it using guarantee restore point.
SQL> alter system set db_recovery_file_dest_size=10G scope=both; System altered. SQL> alter system set db_recovery_file_dest='/dumparea/FRA/B2BRBMT3' scope=both; System altered. SQL> alter database flashback on; Database altered. SQL> startup force
3. Check whether all components are valid or not:
set pagesize500 set linesize 100 select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name; COMP_NAME STATUS VERSION ---------------------------------------- ----------- ---------- JServer JAVA Virtual Machine VALID 11.2.0.4.0 Oracle Application Express VALID 3.2.1.00.1 Oracle Database Catalog Views VALID 11.2.0.4.0 Oracle Database Java Packages VALID 11.2.0.4.0 Oracle Database Packages and Types VALID 11.2.0.4.0 Oracle Expression Filter VALID 11.2.0.4.0 Oracle Multimedia VALID 11.2.0.4.0 Oracle Rules Manager VALID 11.2.0.4.0 Oracle Text VALID 11.2.0.4.0 Oracle Workspace Manager VALID 11.2.0.4.0 Oracle XDK VALID 11.2.0.4.0 Oracle XML Database VALID 11.2.0.4.0 12 rows selected.
4. Make sure no invalid objects under sys or system schema:
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;
5. Run pre-upgrade tool:
Copy the preupgrd.sql and utluppkg.sql script from Oracle 12c binary to any temporary location .
cp /oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/preupgrd.sql .
cp /oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/utluppkg.sql .
SQL> @preupgrd.sql Loading Pre-Upgrade Package... *************************************************************************** Executing Pre-Upgrade Checks in B2BRBMT3... *************************************************************************** ************************************************************ ====>> ERRORS FOUND for B2BRBMT3 <<==== 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. 1) Check Tag: PURGE_RECYCLEBIN Check Summary: Check that recycle bin is empty prior to upgrade Fixup Summary: "The recycle bin will be purged." You MUST resolve the above error prior to upgrade ************************************************************ ************************************************************ ====>> PRE-UPGRADE RESULTS for B2BRBMT3 <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade: /oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade: /oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/postupgrade_fixups.sql ************************************************************ *************************************************************************** Pre-Upgrade Checks in B2BRBMT3 Completed. *************************************************************************** ***************************************************************************
In action required, it is providing preupgrade_fixup.sql scrip to run before upgrade. Run the same in 11g database
@/oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2016-11-08 01:42:41 Version: 12.1.0.2 Build: 008
Beginning Pre-Upgrade Fixups...
Executing in container B2BRBMT3
**********************************************************************
Check Tag: PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fix Summary: The recycle bin will be purged.
**********************************************************************
Fixup Succeeded
**********************************************************************
**********************************************************************
Check Tag: APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary: Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 3.2.1.00.12 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
**********************************************************************
**********************************************************************
[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 ^^^
**************************************************
************* Fixup Summary ************
1 fixup routine was successful.
1 fixup routine returned INFORMATIONAL text that should be reviewed.
**************** Pre-Upgrade Fixup Script Complete *********************
Now review the output of this script. And take action manually, if those were not fixed by the script.
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
6. Purge aud$ table ( take backup if required)
SQL> truncate table sys.aud$; Table truncated. SQL> truncate table sys.fga_log$; Table truncated.
7 . Remove enterprise manager if installed:
From 12c , enterprise manager has been decommissioned from 12c. Either we can use oracle 12c/13c cloud control or enterprise database express for monitoring database.
Run below script to remove the EM component:
sql> connect / as sysdba sql> spool emremoval.log sql>@emremove.sql sql> spool off
8. Check the ACLS present:
SQL> select aclid, start_date, end_date from xds_ace where start_date is not null; no rows selected
If this query returns any row- Please make sure 20369415 is applied to the ORACLE 12C rdbms binary , before upgrade.
UPGRADE:
At this stage, we are still connected to 11g database which is up and running.
Now run the dbua utility from 12c oracle_home location.
cd oracle/app/oracle/product/12.1.0.2/dbhome_1/bin
./dbua
Select parallelism to required degree and select upgrade timezone , which will upgrade the timezone, after end of db upgrade.
Make sure to select USE FLASHBACK OPTION: ( it will create a restore point automatically)
POST-UPGRADE:
1. Run the post upgrade script.
SQL> @/oracle/app/oracle/product/11.2.0/dbhome_Test/cfgtoollogs/B2BRBMT3/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2016-11-08 01:42:41 Version: 12.1.0.2 Build: 008
Beginning Post-Upgrade Fixups...
**********************************************************************
Check Tag: INVALID_OBJECTS_EXIST
Check Summary: Check for invalid objects
Fix Summary: Invalid objects are displayed and must be reviewed.
**********************************************************************
Fixup Returned Information:
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 upgrade.
**********************************************************************
**********************************************************************
Check Tag: OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
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 12.1.0.2.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.
**********************************************************************
**********************************************************************
[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 ^^^
**************************************************
************* Fixup Summary ************
2 fixup routines generated INFORMATIONAL messages that should be reviewed.
*************** Post Upgrade Fixup Script Complete ********************
PL/SQL procedure successfully completed.
2. Check the timezone version:
SQL> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_18.dat 18 0
3. Check invalid sys or system objects:
SQL> select * from registry$sys_inv_objs; no rows selected
4. Run utlrp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
5. Check the status of registry component:
Make sure all are in valid state:
SQL> SQL> SQL> select comp_id,status from dba_registry; COMP_ID STATUS ------------------------------ ----------- APEX VALID ORDIM VALID XDB VALID CONTEXT VALID OWM VALID CATALOG VALID CATPROC VALID JAVAVM VALID XML VALID CATJAVA VALID 10 rows selected.
6. Disable flashback and drop restore point:
Once confirmed that upgrade is successfully, then disable the flashback mode and drop the restore point;
Alter database flashback off; -- get the restore point from v$restore_point: Drop restore point GRP_1478559837445;
Great
very good document
Hi,
Please provide the doc id that you followed