Part II : Database Upgrade steps from 11.2.0.1 to 11.2.0.3 (Standalone DB)
In this part I'll discuss database upgrade steps from 11.2.0.1 to 11.2.0.3, I already covered 11.2.0.3 database software installation steps on a server already running 11.2.0.1 database using out-place method in Part I : environment preparation & 11.2.0.3 Software Installation.
In case you're interested in RAC database upgrade from 11.2.0.1 to 11.2.0.3 I've explained a full implementation (cook book) of upgrading a 11.2.0.1 to 11.2.0.3 RAC database on a new servers (out-place upgrade) in this link:
http://dba-tips.blogspot.ae/2013/09/upgrade-rac-11201-to-11203-part-i.html
Important Notes:
Metalink [ID 730365.1] Includes all patchset downloads + How to upgrade from any Oracle DB version to another one.
Metalink [ID 1276368.1] Out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset.
Tips Before Starting The Upgrade Process:
##################################
Backup & Truncate Audit Table SYS.AUD$:
-------------------------------------------------
Truncating SYS.AUD$ table will speed up the upgrade process:
--Backing up + compressing audit data in SYS.AUD_BKP table:
SQL> create table SYS.AUD_BKP COMPRESS as select * from SYS.AUD$;
SQL> Truncate table SYS.AUD$;
Purge DBA_RECYCLEBIN:
---------------------------
Purging DBA_RECYCLEBIN will speed up the upgrade process:
SQL> PURGE DBA_RECYCLEBIN;
Save a backup of all DB Parameters (Visible & hidden):
------------------------------------------------------------------
This will help you in troubleshooting issues coming after the upgrade (bugs, performance, or whatever the problem is).
The following statement will make it easy for you:
SQL> Spool All_parameters.txt
set linesize 170
col Parameter for a50
col "Session Value" for a40
col "Instance Value" for a40
SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx order by 1;
Spool off
I also strongly recommend to reset the hidden parameter to its default values before starting the upgrade, you don't want to get a weird problems during the upgrade and you don't know from where they came up.
#####################
PRE-UPGRADE STEPS:
#####################
Step 1: Software Installation
#####
Covered in Part I.
Install 11.2.0.3 RDBMS Software into a new ORACLE_HOME.
=>You only need to install examples cd if your DB is using Oracle Text Themes or you intend to install Multimedia demos.
Apply the latest PSU patch on the 11.2.0.3 ORACLE_HOME before upgrading the database.
PSU patch post steps which runs on the database like executing script $ORACLE_HOME/rdbms/admin/catbundle.sql are not required in my case as per 11.2.0.3.4 PSU read-me.
Note that every PSU patch is unique, not all PSU patches exempt running catcpu.sql after the database upgrade, so you have to read the readme file first.
Pre-upgrade information:
=================
Download latest version of utlu112i_5.sql script and execute it: Note ID 884522.1
Note: This script already exist under the new Oracle Home $ORACLE_HOME/rdbms/admin, but usually the one in Metalink is updated with the most recent upgrade checks.
SQL> SPOOL upgrade_info.log
SQL> @new_ORACLE_HOME/rdbms/admin/utlu112i.sql
SQL> SPOOL OFF
Step 2: Dictionary Check
#####
Verify the validity of data dictionary objects by running dbupgdiag.sql script (Download it from NOTE ID 556610.1)
If the dbupgdiag.sql script reports any invalid objects, run utlrp (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects:
SQL> @/home/oracle/dbupgdiag.sql
SQL> @?/rdbms/admin/utlrp.sql
Gather Dictionary Statistics:
---------------------------------
Helps in speeding up upgrade process.
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Step 3: TIMEZONE Version
#####
In case you have TIMESTAMP WITH TIME ZONE data type in your DB, you need to Upgrade the Time Zone version to version 14.
The possibilities are:
>If current version < 14 ,You need to upgrade to version 14 after you finish the upgrade to 11.2.0.3.
>If current version = 14 ,No need to upgrade, Skip the whole Step.
>If current version > 14 ,You must upgrade your Time Zone version before upgrading to 11.2.0.3 or your data stored in TIMESTAMP WITH TIME ZONE datatype can become corrupted during the upgrade.
Check your current Time Zone version:
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
In my case the version is older, so I can do this step later after finalizing the upgrade.
STEP 11 covers this part.
Check National Characterset is UTF8 or AL16UTF16:
=======================================
SQL> select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
Step 4: Disable Cluster option
####
Set the parameter cluster_database to FALSE for RAC database.
Step 5: Configure log locations
#####
Change SPFILE parameters to point to the new ORACLE HOME:
-------------------------------------------------------------------------------
# mkdir -p /u02/oracle/ora11g/11.2.0.3/diagnostics/orcl
SQL> ALTER SYSTEM SET diagnostic_dest = '/u02/oracle/ora11g/11.2.0.3/diagnostics/orcl';
SQL> ALTER SYSTEM SET audit_file_dest = '/u02/oracle/ora11g/11.2.0.3/rdbms/audit' SCOPE=SPFILE;
Step 6: New Environment Variables
#####
Make sure the following Linux environment variables are pointing to the new 11.2.0.3 ORACLE_HOME:
(ORACLE_BASE, ORACLE_HOME, PATH, NLS_10 and LIBRARY_PATH).
Step 7: Modify | Move configuration files
#####
>Make sure that entries inside /etc/oratab file are pointing to the new 11.2.0.3 ORACLE_HOME, hash the original entries pointing to 11.2.0.1 ORACLE_HOME:
e.g.
#pefms:/u01/oracle/ora11gr2/11.2.0.1:Y
pefms:/u02/oracle/ora11g/11.2.0.3:Y
> Copy SPFILE & Password File to the new ORACLE_HOME:
---------------------------------------------------------------------------
# cd /u01/oracle/ora11gr2/11.2.0.1/dbs
# cp spfile* orapw* /u02/oracle/ora11g/11.2.0.3/dbs/
> Copy network configuration files to the new 11.2.0.3 $TNS_ADMIN directory:
-----------------------------------------------------------------------------------------------
# cd /u01/oracle/ora11gr2/11.2.0.1/network/admin
# cp tnsnames.ora listener.ora sqlnet.ora /u02/oracle/ora11g/11.2.0.3/network/admin/
> Copy DB Control EM directories to the new 11.2.0.3 ORACLE_HOME:
---------------------------------------------------------------------------------------
# $ORACLE_HOME/
# $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_
# $ORACLE_HOME/owb/bin/admin
> Copy SQLPLUS settings file:
-----------------------------------
# cp /u01/oracle/ora11gr2/11.2.0.1/sqlplus/admin/glogin.sql /u02/oracle/ora11g/11.2.0.3/sqlplus/admin/
Step 8: Disable Vault | Adjust parameters for JVM
#####
>Disable Database Vault if enabled.
>IF JVM installed, java_pool_size and shared_pool_size must be set to at least 250MB prior to the upgrade.
Latest checks:
==========
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; --outstanding distributed transactions
no rows selected
SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL';
no rows selected
Disable all batch and cron jobs:
---------------------------------
Disable all crontab scripts:
#
crontab /root/crontab_root
# crontab /dev/null
# crontab -l
# crontab -l > /home/oracle/oracle_crontab
# crontab /dev/null
# crontab -l
Disable DB jobs:
SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');SQL> alter system set job_queue_processes=0 scope=both;
Step 9: Set the Database in the Noarchivelog mode.
#####
>Stop the listener: lsnrctl stop
>Stop DBCONSOLE: emctl stop dbconsole
Putting the database in the noarchivelog shrink the upgrade time. .
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
SQL> startup mount
SQL> alter database noarchivelog;
SQL> archive log stop;
SQL> shutdown immediate;
YOU MUST TAKE A COLD BACKUP AT THIS STAGE FOR FALLBACK PLAN.
#################
UPGRADE STEPS:
#################
Note: The database should be opened from the old ORACLE_HOME before running DBUA.
Note: If Oracle clusterware installed, it needs to be UP and running before starting DBUA.
There are many ways Oracle provide to upgrade your database, I'll discuss here three of them (DBUA, Silent, Manual) and it's up to you to select the suitable way for your implementation.
DBUA Way: (GUI)
========
Ensure that dbua is running from the new ORACLE_HOME:
# which dbua
Run the DBUA by Oracle User:
# dbua
=>First screen: introduction .
=>2nd screen: Select the database name.
=>3rd screen: Check the following:
-Recompile invalid objects at the end of upgrade.
-Turn off Archiving for the duration of upgrade.
-Upgrade Timezone version and TIMESTAMP WITH TIMEZONE data.
=>4th screen: keep the default.
=>5th screen: keep the default.
=>6th screen: keep the default.
=>7th screen: Put password for DBSNMP,SYSMAN.
=>8th screen: Select the DB listener.
=>9th screen: Finish.
Notes: Continue to Post upgrade steps but skip these two steps (Upgrade the Timezone version & Configure EM) as they are automatically configured by DBCA.
Silent Way:
========
In case you want to perform the upgrade using DBUA but you cannot forward X11 packets due to firewall rule or other reason, you can use the silent mode which is Faster and doesn't requires X11 packet forwarding:
By Oracle user run this command:
# dbua -silent
-sid orcl
-autoextendFiles
-upgradeTimezone
-recompile_invalid_objects true
-degree_of_parallelism 4
-emConfiguration LOCAL
-dbsnmpPassword <password>
-sysmanPassword <password>
Outputs will be like the following:
Log files for the upgrade operation are located at: /u02/oracle/cfgtoollogs/dbua/bkpefms/upgrade1
Performing Pre Upgrade
1% complete
7% complete
Upgrading Oracle Server
....
Upgrading JServer JAVA Virtual Machine
22% complete
....
85% complete
Upgrading Timezone
....
92% complete
....
Generating Summary
100% complete
Check the log file "/u02/oracle/cfgtoollogs/dbua/logs/silent2.log" for upgrade details.
Notes: Continue to Post upgrade steps but skip these two steps (Upgrade the Timezone version & Configure EM) as they are already configured by DBCA.
The Manual Way: (The Way I Prefer)
=============
Run 1123 profile which points to the new 11.2.0.3 ORACLE_HOME locations:
# cd /home/oracle
# . .bash_profile11203
If you didn't create this file yet you can copy the .bash_profile, renaming the new file to .bash_profile11203 ,edit the file by replacing the old ORACLE_HOME path with the new ORACLE_HOME path which is /u02/oracle/ora11g/11.2.0.3 in my setup, then replace the old ORACLE_BASE with the new ORACLE_BASE which is /u02/oracle in my setup.
Step 10: Execute the upgrade script
######
# export ORACLE_SID=orcl
# sqlplus / as sysdba
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
SQL> Shutdown immediate
Note: If you encounter a message listing obsolete initialization parameters during startup upgrade,remove the obsolete parameters from the PFILE.
Note: You can re-run the catupgrd.sql script as many times as necessary. IF you experience errors during the upgrade fix it first then:
1)Shu immediate
2)Startup Upgrade
3)@?/rdbms/admin/catupgrd.sql
> Check the spool file for errors.
> Restart the database in normal mode.
When upgrade script is done, run the following scrips:
SQL> Startup
SQL> @?/rdbms/admin/utlu112s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utluiobj.sql
--Checks invalid objects after the upgrade.
Run dbupgdiag.sql script (See note: 556610.1) and verify that all the components in dba_registry are valid and there are no invalid objects in dba_objects.
SQL>@/home/oracle/dbupgdiag.sql
Modify listener.ora:
==============
In the listener.ora file, modify the ORACLE_HOME path to the new 11.2.0.3 ORACLE_HOME:
Ex:
vi /u02/oracle/ora11g/11.2.0.3/network/admin/listener.ora
LISTENER_orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora-dev1)(PORT = 1521))
)
)
SID_LIST_LISTENER_orcl =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u02/oracle/ora11g/11.2.0.3)
(SID_NAME = orcl)
)
)
##################
Post Upgrade Steps:
##################
Step 11: Upgrade the TimeZone version
######
Preparation Stage:
=============
SQL> SHU IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> exec DBMS_DST.BEGIN_PREPARE(14)
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE PREPARE
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
SQL> TRUNCATE TABLE sys.dst$affected_tables;
SQL> TRUNCATE TABLE sys.dst$error_table;
SQL> set serveroutput on
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/
SQL> SELECT * FROM sys.dst$affected_tables;
SQL> SELECT * FROM sys.dst$error_table;
SQL> EXEC DBMS_DST.END_PREPARE;
Upgrade Stage:
===========
SQL> purge dba_recyclebin;
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
no rows selected | I got 14 rows selected.
SQL> shutdown immediate
SQL> startup
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
.
.
.
Failures:0
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
An upgrade window has been successfully ended.
Failures:0
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
4
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
SQL> commit;
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
14
SQL> SELECT value$ FROM sys.props$ WHERE NAME = 'DST_PRIMARY_TT_VERSION';
VALUE$
--------
14
STEP 12: Set CLUSTER_DATABASE=TRUE
######
If your DB is a RAC one, set CLUSTER_DATABASE=TRUE
I've explained a full implementation (cook book) of upgrading a 11.2.0.1 to 11.2.0.3 RAC database on a new servers (out-place upgrade) in this link:
http://dba-tips.blogspot.ae/2013/09/upgrade-rac-11201-to-11203-part-i.html
STEP 13: Upgrade the Recovery Catalog
#######
If you're using the Recovery Catalog to backup your database you have to upgrade tha catalog DB:
A) Connect to the catalog DB through RMAN:
RMAN> CONNECT CATALOG username/password@catalog_DB
B) Execute this command two times:
RMAN> UPGRADE CATALOG;
STEP 14: Upgrade Statistics Tables
#######
Statistics tables are tables store the statistics for DB or schema or tables to be restored later, or to be imported on another database usually for testing purposes.
If you created statistics tables before using the DBMS_STATS.CREATE_STAT_TABLE, then upgrade each statistics table by running:
e.g.
SQL> EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table');
STEP 15: Enable Database Vault
######
Enable Oracle Database Vault and Revoke the DV_PATCH_ADMIN Role Note 453903.1
STEP 16: Compatible version + Enable Archiving & Flashback mode
#######
Set the compatibility version to the current on, and enable archivelog and flashback modes:
SQL> alter system set compatible='11.2.0.3' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;
Re-point the directories to the new ORACLE_HOME:
-----------------------------------------------
As the ORACLE_HOME path became different, The directories that point to old ORACLE_HOME should point to the new ORACLE_HOME:
Example:
SQL> col DIRECTORY_PATH for a80
SQL> SELECT * FROM dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------ ------------------------------------------
SYS QUEST_SOO_UDUMP_DIR /u01/oracle/ora11g/11.2.0.1/diagnostics/orcl/diag/rdbms/orcl/orcl/trace/
SQL> create or replace directory QUEST_SOO_UDUMP_DIR as '/u02/oracle/ora11g/11.2.0.3/diagnostics/orcl/diag/rdbms/orcl/orcl/trace/';
STEP 17: Enable Cron & DB Jobs:
######
Enable Crontab Jobs.
Enable DB jobs:
SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');
STEP 18: Configure the Enterprise Manager
######
WARNING:
##########
During recreating or dropping the repository, this requires the database to be in quiesce mode. This means that, temporarily, no operations are possible on the database and new users cannot login.
If this mistakenly done during business hours, do the following:
1. Kill the emca command
2. SQL > alter system unquiesce;
Configure EM Using DBCA:
======================
Flush any previous failed attempt to configure EM:
---------------------------------------------------------
From sqlplus by sysdba:
alter user dbsnmp identified by "xxx";
drop user sysman cascade;
drop public synonym SETEMVIEWUSERCONTEXT;
drop role MGMT_USER;
drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
drop user MGMT_VIEW;
drop type sys.MGMT_MNTR_USER_STATS_ARRAY;
drop type sys.HA_HOST_CREDS_ARR;
Then run this command from Linux shell by Oracle user:
# emca -deconfig dbcontrol db -repos drop
Configure EM:
--------------
By Oracle User:
# emctl stop dbconsole
# dbca
--> Configure Database Options
--> ... select: register this database with selected listeners only --> .. --> Put complicated password for SYSMAN and DBSNMP like abcde.$1234 --> keep selecting the defaults.
How To start multiple EM Agents On same server: ORACLE_UNQNAME
-----------------------------------------------------------
Note: We have more than one DB on DEV server, each DB will have a unique EM port assigned to it, before starting or stopping EM agent for each DB you have to export the environment variable ORACLE_UNQNAME first:
e.g. starting EM agent for orcl3:
# export ORACLE_UNQNAME=orcl3
# emctl start dbconsole
>>>>>>>>>>>>>>>>>>>>
THE UPGRADE IS DONE
>>>>>>>>>>>>>>>>>>>>
The following are Optional STEPS: Good To Do
############################
STEP 20: Rebuild Unusable Indexes & Gather Statistics
#######
Rebuild Unusable indexes:
===================
SQL> select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;'from dba_indexes where status ='UNUSABLE';
Gather FIXED OBJECTS stats: (Do it during peak hours not within the downtime)
======================
Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
Fixed Object statistics are not being gathered automatically nor within gather DB stats procedure.
If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.
Note:
-It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to gurantee that the fixed object tables been populated and the statistics well represent the DB activity. also note that performance degradation may be experienced while the statistics are gathering.
-Having no statistics is better than having a non representative statistics.
Gather the fixed objects stats:
---------------------------------
SQL> exec dbms_stats.gather_fixed_objects_stats;
Gather DICTIONARY stats:
=====================
Dictionary stats are gathered on dictionary tables owned by SYS and resides in the system tablespace.
SQL> Exec DBMS_STATS.GATHER_DICTIONARY_STATS ();
Gather database statistics:
==================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
In case you need to gather DB Stats + Histograms on all skewed columns:
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE,degree => 8);
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed values always gives excellent results.
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not recommended to gathered on all columns.
Removed "cascade => TRUE" => To let Oracle determine whether index statistics to be collected or not.
For more information about gathering statistics on the database I strongly recommend you to read this post:
http://dba-tips.blogspot.ae/2012/11/all-about-statistics-in-oracle.html
Step 21: Check Oracle Recommended Patches:
######
Note ID 756671.1 includes the recommended patches for releases starting from 10.2.0.3.
Note ID 742060.1 represents the most accurate information Oracle can provide for coming releases.
The difference between PSU patch and CPU (SPU) patch : [ID 854428.1]
=========================================
-PSU patch is the fifth digit in the release like 11.2.0.1.1 it's being published each 4 months in the same moths of releasing CPU patch (Jan,Apr,Jun,Oct).
-PSU patch includes (CPU patches + common Bug fixes which affect a large number of customers).
-Once you start to apply PSU patch you can't apply CPU patch again (this is what Oracle recommends but it possible to apply CPU patch after apply PSU patches).
-PSU patches are cumulative same like CPU once you applied the latest patch the old ones are included in that patch.
-When downloading PSU patch there is (PSU & GI PSU), GI PSU is for Grid Infrastructure (this apply the patch for GI and database) the other one PSU (only apply the patch for the database).
-Starting from October 2012 Oracle re-named CPU Critical Patch Update to SPU Security Patch Update, both are same, it's just a renaming .
*You can check the latest applied patches on the database by running this query:
SQL> select * from DBA_REGISTRY_HISTORY;
//////////////////////////////////////////////*
Removing OLD ORACLE_HOME: (Optional, you can do it later)
=========================
After you feel confident with the new Oracle installation and you will never downgrade to the previous release, remove the ORACLE_HOME:
Detach old ORACLE_HOME:
# $OLD_HOME/oui/bin/runInstaller -detachHome -silent -local
Confirm old ORACLE_HOME is removed from central inventory:
# $OLD_HOME/OPatch/opatch lsinventory -all
Remove files in old ORACLE_HOME manually:
# rm -rf $OLD_HOME
*////////////////////////////////////////////
