:

Oracle 10g to 11g Upgrade Guide

Oracle 10g to 11g database upgrade

In this article I would like to walk you through an example of how to upgrade your Oracle database from version 10g to 11g. We will be covering the following topics:

  • DBCA vs Manual Upgrade – which should you use?
  • Documentation to read before you upgrade
  • How to deal with Data Guard physical standby DBs when upgrading to 11g
  • 10g to 11g upgrade guide, step-by-step examples

Oracle Upgrade Considerations

11gR2 was release in September 2009, with the 2nd major patchset (11.2.0.2) being released 1 year later in September 2010. Personally, I always like to let a product bed in for a little while before I start using it. There are always bugs in such complex releases of software because it’s impossible for Oracle to test all of the different combinations of platforms that the software is being run on. As of 11g Oracle started to release the patches as full installs, preferring the full install approach over the patching of an existing home. As such, to future proof your database server please ensure that you have enough space on the drive where you are installing the Oracle software – you usually need around 3-4GB per installation so if you patch from 11.2.0.1 to 11.2.0.2 and then 11.2.0.3, you will need up to 12GB.

I should point out at this point that I also have two physical standby Data Guard DBs in this configuration. I will be covering the steps taken to do this as part of the upgrade steps below, but I would also recommend that you read my guide on upgrading your primary database with physical standby DBs. That article has great detail about how to create your physical standby databases and then get everything prepared for the primary upgrade. Although this is written for a 10g to 11g upgrade, I’m sure that it will apply to other versions.

DBUA vs Manual Upgrade

I have written previously about the DBCA in another article, which was referring to the 10g version of the configuration assistant utility which Oracle provide. My thoughts about the DBUA are very similar, to be honest. I’m sure that the utility is great and makes life a lot easier but the fact remains that you don’t know what is going on under the hood. When running the scripts in manually I know exactly what is going on, where it fails or gets stuck, what takes time and I can be very specific about what is and is not run. As long as the GUI remains this way I will continue to use the manual approach. You also learn a lot more about your databases when you do it manually. In this step-by-step guide I will be walking you through the steps on how to upgrade your database to 11g manually.

Oracle Upgrade Documentation

The first guide which you should read is the Oracle Database Upgrade Guide. This is compulsory reading. It will give you an insight into the new features, what precautions to take and what to expect as part of the upgrade process. A great section in it for 11g is called “Behaviour Changes”, within which you will find out what parameters are being deprecated, along with new parameters and changes to the optimizer.

Step-by-Step Guide for 10g to 11g Upgrade

Within this section I show you exactly how to upgrade your oracle database from 10g to 11g R2.

Note: Although these steps are very specific and taken from the upgrade guide they are also applicable to my environment. I compiled this list while reading through the upgrade guide to determine which steps I needed to take. Please read the upgrade guide to ensure that you are completing the correct steps, not missing any or not running in unnecessary scripts. Having said that, I’m sure that most of the steps are compulsory for all databases, but I can’t be held responsible for a failed upgrade!

How to deal with Data Guard physical standby DBs when upgrading to 11g

I am using a SAN based snapshot technology in this guide. The snap is a point in time image of the data drive which can be reverted back to. Although I use this approach, you could probably use Flashback but I opted not to for a few reasons which you can read about in my other article on how to upgrade your primary database with physical standby DBs, under the heading “Why am I using SAN Snap Technology and not Oracle Flashback?”

These are more in the form of notes than anything else, with comments to give you an idea about what I am doing and why I am doing it.

####################
# Pre-Upgrade work #
####################

-- Check all queries are using bind variables
-- Ensure cursor_sharing is set to EXACT and was for all testing environments
-- Check Metalink note 1169017.1
-- Remove automatic SQL Tuning advisor task from DB jobs
-- Check for pre_11g_enable_capture parameter which needs to be removed if it is in the SPFile

-- ###############
-- # Standby DBs #
-- ###############

-- Switch logfile on primary database so it gets shipped and applied to physical standby
alter system switch logfile;

-- Ensure logfile has been applied and then cancel recovery
-- On Standby DB
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Defer log shipping to standby DBs
-- Run against the primary DB. Check that 2 and 3 are the correct log_arhive_dests
alter system set log_archive_dest_state_2='ENABLE' scope=both;
alter system set log_archive_dest_state_3='ENABLE' scope=both;

-- ##############
-- # Primary DB #
-- ##############

-- Purge the recyclebin
PURGE DBA_RECYCLEBIN;

-- drop plan table to prevent issues with upgrade
DROP TABLE SYS.PLAN_TABLE;
DROP PUBLIC SYNONYM PLAN_TABLE;

-- Check that there are no other PLAN_TABLES in the database owned by random schemas
set lines 120
set pages 1000
col object_name for a30
select object_name, owner, object_type from dba_objects where object_name like '%PLAN%TABLE%';

-- ################
-- # Stats Gather #
-- ################

-- Run as SYS
set timing on
spool gdict_stats_TESTDB.log

grant analyze any to sys;
exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
spool off

-- ####################
-- # Pre Upgrade Info #
-- ####################
-- Run as SYS
-- Must be script taken from the 11.2.0.3 oracle_home\RDBMS\ADMIN

spool pre11g_upgrade_TESTDB.log
@utlu112i.sql
spool off
ALTER SYSTEM SWITCH LOGFILE;

select max(sequence#)
from v$archived_log;

-- Note down logfile number which will be used later on to register on the standby DB

-- #################
-- # Change Params #
-- #################

-- Always be explicit with your pfile and spfile creation
CREATE PFILE='D:\oracle\product\11.2.0\db_1\database\initTESTDB.ora' FROM SPFILE='D:\oradata\TESTDB\spfileTESTDB.ora';

--Copy this to new 11g home and edit parameters as required, I needed to change these:

*** ADD/CHANGE ***
-- Make sure that you know what the original parameters were just in case you need to change them back
-- I change the java and shared pool sizes back afterwards

diagnostic_dest = 
compatible = '11.2.0'
shared_pool_size = 164M
java_pool_size = 164M

*** REMOVE ***

background_dump_dest
core_dump_dest
user_dump_dest
streams_pool_size
pre_page_sga

-- ########
-- # ACLs #
-- ########
-- Check which users are going to require to be on the Access Control List
-- This is a new 11g feature
-- Keep the spool file safe for use later on

col owner for a25
set lines 500
set pages 1000
set trimspool on

spool users_ACLs_TESTDB.log

SELECT distinct owner, referenced_name
 FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
 AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

spool off

-- ###################
-- # Invalid Objects #
-- ###################
-- Check this table to see invalid SYS/SYSTEM objects

col object_name for a30
select * from registry$sys_inv_objs;

select 'alter PACKAGE '||owner||'.'||object_name||' compile;'
from registry$sys_inv_objs
where owner not in ('PUBLIC')
and object_type in ('PACKAGE BODY');

-- New plan table - done after upgrade - results in DBMS_SQLPA, DBMS_SUMADVISOR and DBMS_REGISTRY being invalid
-- Check this table to see invalid non SYS/SYSTEM objects

col object_name for a30
select * from registry$nonsys_inv_objs;

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from registry$nonsys_inv_objs
where owner not in ('PUBLIC')
and object_type not in ('PACKAGE BODY');

select 'alter PACKAGE '||owner||'.'||object_name||' compile;'
from registry$nonsys_inv_objs
where owner not in ('PUBLIC')
and object_type in ('PACKAGE BODY');

-- ###########
-- # Compile #
-- ###########
-- Script to compile all invalid objects

spool compile.out
set head off
set pagesize 5000

select 'alter ' || object_type || ' '||owner||'.'|| object_name || ' compile;'
from dba_objects
where status != 'VALID'
and object_type in ('VIEW','TRIGGER','PACKAGE','PROCEDURE','SYNONYM')
and owner not in ('PUBLIC')
UNION
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes
where status != 'VALID'
UNION
select 'alter PACKAGE '||owner||'.'|| object_name || ' compile;'
from dba_objects
where status != 'VALID'
and object_type ='PACKAGE BODY'
union
select 'alter public synonym '||object_name||' compile;'
from dba_objects
where status != 'VALID'
and owner = 'PUBLIC';

spool off

@compile.out

-- #####################
-- # New Password File #
-- #####################

D:\Oracle\bin\orapwd file=D:\Oracle\Product\11.2.0\db_1\database\pwdTESTDB.ora password=test force=y ignorecase=y

-- ###################
-- # Listener Config #
-- ###################

Update listener.ora file in 11g home - add config
Update TNSNames.ora file in 10g and 11g home - modify if needed
Update listener.ora file in 10g home - remove config
Email systems and ask to change Change DNS entry
Reload 11g listener and check it is working and DNS name resolves to correct IP address

-- ###############
-- # New Service #
-- ###############
-- Run against primary database
SHUTDOWN IMMEDIATE

-- Remove old service and add new service
D:\oracle\product\10.2.0\db_2\bin\ORADIM -DELETE -SID TESTDB
D:\Oracle\Product\11.2.0\dbhome_11203\bin\ORADIM -NEW -SID TESTDB -STARTMODE m

-- ###########
-- # Upgrade #
-- ###########
-- Run from the 11g Oracle home\rdbms\admin directory as other scripts are called from there

-- Set the oracle_home to ensure that the other scripts being called can be found
-- If not set, this can cause the upgrade to fail
set oracle_home=D:\Oracle\Product\11.2.0\dbhome_11203
cd D:\Oracle\Product\11.2.0\dbhome_11203\RDBMS\admin
D:

SET ORACLE_SID=TESTDB

D:\Oracle\Product\11.2.0\dbhome_11203\bin\sqlplus "sys as sysdba"

STARTUP UPGRADE
SPOOL upgrade_TESTDB.log
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
@D:\Oracle\Product\11.2.0\dbhome_11203\RDBMS\ADMIN\catupgrd.sql

-- Used to search through the upgrade logs for any ORA- and SP2- errors
findstr /B /N "ORA-" upgrade_TESTDB.log
findstr /B /N "SP2-" upgrade_TESTDB.log

D:\Oracle\Product\11.2.0\dbhome_11203\bin\sqlplus "sys as sysdba"
STARTUP

SET LINES 120
SET PAGES 1000
COl COMP_NAME FOR A60
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

SPOOL upgrade_TESTDB_pt2.log

@D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\utlu112s.sql
@D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\catuppst.sql
@D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\utlrp.sql

SPOOL OFF

-- ##############
-- # Check Logs #
-- ##############

-- Checking the logs for the 2nd set of upgrade scripts
findstr /B /N "ORA-" upgrade_TESTDB_pt2.log
findstr /B /N "SP2-" upgrade_TESTDB_pt2.log

-- ########################
-- # Invalid Object Check #
-- ########################

SELECT count(*) FROM dba_invalid_objects;

-- Compares original invalid to new invalid objects
@D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\utluiobj.sql

-- ####################################
-- # Oracle Workspace Manager Removal #
-- ####################################

SPOOL OWM_Removal_TESTDB.log
@D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\owmuinst.plb
SPOOL OFF

-- ##################
-- # Component Info #
-- ##################

-- Shows a list of components, the status and their version
SET LINES 120
SET PAGES 1000
COl COMP_NAME FOR A60
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

-- ################
-- # Stats Config #
-- ################
-- Show current values for default stats gather parameters
-- Change the parameters to the preferred values to be used as default

SPOOL dbms_stats_config_TESTDB.log

select dbms_stats.GET_PREFS('AUTOSTATS_TARGET') from dual;
select dbms_stats.GET_PREFS('CASCADE') from dual;
select dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual;
select dbms_stats.GET_PREFS('METHOD_OPT') from dual;
select dbms_stats.GET_PREFS('NO_INVALIDATE') from dual;
select dbms_stats.GET_PREFS('GRANULARITY') from dual;
select dbms_stats.GET_PREFS('PUBLISH') from dual;
select dbms_stats.GET_PREFS('INCREMENTAL') from dual;
select dbms_stats.GET_PREFS('DEGREE') from dual;

-- Setting new parameters for default stats gather parameters

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','ORACLE');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CASCADE','TRUE');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('ESTIMATE_PERCENT','100');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT','FOR ALL COLUMNS SIZE 1');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('NO_INVALIDATE','FALSE');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('GRANULARITY','ALL');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL','TRUE');

SPOOL OFF

-- ##############
-- # ACL Config #
-- ##############

-- Assign each user the privs they need
-- This step needs to run as part of the post upgrade
-- The users were found in the pre-upgrade work for ACLs
-- They can be found in users_ACLs_TESTDB.log
-- Make sure to use the correct IP address for the HOST email server

SPOOL ACL_CONFIG_TESTDB.log

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
 acl => 'UTL_SMTP.xml',
 description => 'Granting privs to required users for UTL_SMTP.xml',
 principal => 'ADMIN',
 is_grant => TRUE,
 privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
 acl => 'UTL_SMTP.xml',
 principal => 'DBA',
 is_grant => TRUE,
 privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
 acl => 'UTL_SMTP.xml',
 principal => 'TESTING_SCHEMA',
 is_grant => TRUE,
 privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
 acl => 'UTL_SMTP.xml',
 host => '127.0.0.1');
END;
/

COMMIT;

SPOOL OFF

-- ##########
-- # SPFile #
-- ##########

CREATE SPFILE='D:\oradata\TESTDB\spfileTESTDB.ora' FROM PFILE='D:\oracle\product\11.2.0\db_1\database\initTESTDB.ora';
SHUTDOWN IMMEDIATE
STARTUP

-- Make sure that the new SPfile is being used and then remove the PFILE if it is
SHOW PARAMETER SPFILE

-- Reset parameters back to original values in init.ora
-- This may be 0 or some other figure which you should have noted down before changing them
ALTER SYSTEM SET shared_pool_size=0M SCOPE=BOTH;
ALTER SYSTEM SET java_pool_size=0M SCOPE=BOTH;

-- ###########
-- # Compile #
-- ###########

spool compile.out
set head off
set pagesize 5000

select 'alter ' || object_type || ' '||owner||'.'|| object_name || ' compile;'
from dba_objects
where status != 'VALID'
and object_type in ('VIEW','TRIGGER','PACKAGE','PROCEDURE','SYNONYM')
and owner not in ('PUBLIC')
UNION
select 'ALTER INDEX '||owner||'.'||index_name || ' REBUILD;'
from dba_indexes
where status = 'UNUSABLE'
UNION
select 'alter PACKAGE '||owner||'.'|| object_name || ' compile;'
from dba_objects
where status != 'VALID'
and object_type ='PACKAGE BODY'
union
select 'alter public synonym '||object_name||' compile;'
from dba_objects
where status != 'VALID'
and owner = 'PUBLIC';

spool off

@compile.out

-- ####################
-- # Diagnostic Check #
-- ####################
-- Run the following script to diagnose possible issues with upgrade. Note 556610.1
-- This script should be downloaded from Metalink

@dbupgdiag.sql D:\DBA\Rob\11g_Upgrade

-- Metalink note 1030426.6 Duplicate objects owned by SYS and SYSTEM
-- Read through if you have duplicate objects in SYS and SYSTEM

-- #######################
-- # Retention of traces #
-- #######################
-- Controls the duration the trace files will be stored in the diagnostic_dest
-- Specified in hours, so short policy is 3 months and long policy is 6 months

D:\Oracle\product\11.2.0\dbhome_1\bin\adrci
 set control (SHORTP_POLICY = 2160)
 set control (LONGP_POLICY = 4320)

-- #################
-- # Timezone File #
-- #################

-- For timezone. Run as SYS
select * from v$timezone_file;

-- To Fix the time zone files , After completing the database upgrade.

-- Patch 815679.1
-- FAQ 340512.1
-- Aplying DST 977512.1
-- DST Transitions 412160.1

-- Use the DBMS_DST PL/SQL package
-- Follow the instructions which can be seen in
-- "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" in Oracle Database Globalization Support Guide

-- Checking for affected tables, to be done before upgrading
-- Run as SYS

SPOOL Timezons_upgrd_TESTDB.log

set serverout on
EXEC DBMS_DST.CREATE_ERROR_TABLE('DST_ERROR_TABLE')
EXEC DBMS_DST.CREATE_AFFECTED_TABLE('DST_AFFECTED_TABLE')

EXEC DBMS_DST.BEGIN_PREPARE(14)
EXEC DBMS_DST.FIND_AFFECTED_TABLES

select count(*) from sys.dst$affected_tables;
select count(*) from sys.DST_ERROR_TABLE;
select count(*) from sys.DST_AFFECTED_TABLE;

EXEC DBMS_DST.END_PREPARE

-- Upgrading the timezone file

TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.DST_ERROR_TABLE;
TRUNCATE TABLE sys.DST_AFFECTED_TABLE;

SHUTDOWN IMMEDIATE
STARTUP UPGRADE

EXEC DBMS_DST.BEGIN_UPGRADE(14)

select count(*) from sys.dst$affected_tables;
select count(*) from sys.DST_ERROR_TABLE;
select count(*) from sys.DST_AFFECTED_TABLE;
select count(*) from sys.dst$trigger_table;

SHUTDOWN IMMEDIATE
STARTUP

TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.DST_ERROR_TABLE;
TRUNCATE TABLE sys.DST_AFFECTED_TABLE;
TRUNCATE TABLE sys.dst$trigger_table;

set serverout on
variable num_failures NUMBER;
begin
:num_failures := 0;
DBMS_DST.UPGRADE_DATABASE(  :num_failures -
                          , parallel=>TRUE -
                          , log_errors=>TRUE -
                          , log_errors_table=>'DST_ERROR_TABLE'
                          , error_on_overlap_time=>TRUE -
                          , error_on_nonexisting_time=>TRUE);
DBMS_OUTPUT.PUT_LINE(:num_failures);
END;
/

select count(*) from sys.DST_ERROR_TABLE;
select count(*) from sys.DST_ERROR_TABLE;
select count(*) from sys.DST_AFFECTED_TABLE;
select count(*) from sys.dst$trigger_table;

set serverout on
variable num_failures NUMBER;
begin
 :num_failures := 0;
 DBMS_DST.END_UPGRADE(:num_failures);
 DBMS_OUTPUT.PUT_LINE(:num_failures);
END;
/

SPOOL OFF

-- ###################
-- # Profile Changes #
-- ###################
-- Ensure that the profile allows unlimited logons

SPOOL profile_changes_TESTDB.log

select * from dba_profiles where profile = 'DEFAULT';

alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;

select * from dba_profiles where profile = 'DEFAULT';

-- What happens with the password security features
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE SCOPE=BOTH;
ALTER SYSTEM SET SEC_PROTOCOL_ERROR_TRACE_ACTION = LOG SCOPE=BOTH;
ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS = 999999999 SCOPE=SPFILE;

SPOOL OFF

-- ########################
-- # Upgrade Stats Tables #
-- ########################

SPOOL Upgrade_statstab_TESTDB.log

select 'EXEC DBMS_STATS.UPGRADE_STAT_TABLE('''||owner||''', '''||table_name||''');'
from dba_tables
where table_name like '%STATS%'
and owner not in ('SYS','EXFSYS','SYSTEM');

SPOOL OFF

-- #######################
-- # Disable Audit Trail #
-- #######################

SPOOL audit_trail_TESTDB.log

select count(*) from sys.aud$;
alter system set audit_trail=NONE scope=spfile;
truncate table sys.aud$;

SPOOL OFF

-- ##################
-- # Automated Jobs #
-- ##################

-- Check out auto tasks which were scheduled
-- AUTOTASK_CONSUMER_GROUP
-- MAINTENANCE_WINDOW_GROUP

-- Shows a list of all auto tasks
select * from DBA_AUTOTASK_CLIENT;

-- Disables auto stats collection job

BEGIN
 dbms_auto_task_admin.disable(
 client_name => 'auto optimizer stats collection',
 operation => NULL,
 window_name => NULL);
END;
/

-- Disable the SQL Tuning advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

-- Disable specific windows when jobs might run
BEGIN
 DBMS_SCHEDULER.DISABLE (
 name => 'WEEKNIGHT_WINDOW,MONDAY_WINDOW,TUESDAY_WINDOW,WEDNESDAY_WINDOW,THURSDAY_WINDOW,FRIDAY_WINDOW, -
          SATURDAY_WINDOW,SUNDAY_WINDOW'
 );
END;
/

-- Check to see whether SQL profiles are used
-- Check to see if stored outlines are used
-- Run AWR to check parameters

SHOW PARAMETER RECYCLEBIN
SHOW PARAMETER OPTIMIZER

SELECT *
FROM DBA_RECYCLEBIN;

PURGE DBA_RECYCLEBIN;

-- Change AWR retention time to 180 days
-- Check what retention policy is first
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 259200)

-- ####################
-- # Stats Collection #
-- ####################

-- Gather stats for appropriate schema users
-- Import if already collected on DEV environment
-- EXEC DBMS_STATS.GATHER_SCHEMA_STATS('TEST_USER',DEGREE=>12, CASCADE=>TRUE)

-- exec dbms_stats.create_stat_table('TEST_USER','ADMIN_STATS_11G');

-- EXEC DBMS_STATS.export_SCHEMA_STATS('TEST_USER','ADMIN_STATS_11G',NUll);

create directory TEMP as 'D:\DBA\Rob\11203';

-- D:\db_1\bin\expdp 'sys@TESTDB as sysdba' dumpfile=TEMP:STATS_11G.DMP nologfile=Y tables=TEST_USER.ADMIN_STATS_11G

-- D:\db_1\bin\impdp 'sys@TESTDB as sysdba' dumpfile=TEMP:STATS_11G.DMP nologfile=Y

EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('TEST_USER','ADMIN_STATS_11G')

-- Find out when the stats were last collected and ensure they are on all the objects
-- select owner, substr(last_analyzed,1,9), count(*)
-- from dba_tables
-- where owner in ('TEST_USER')
-- group by owner, substr(last_analyzed,1,9)
-- order by owner, substr(last_analyzed,1,9);

-- ################
-- # NATIVE PLSQL #
-- ################

*** Recompile all PLSQL objects into NATIVE type ***

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
@D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\dbmsupgnv.sql FALSE
SHUTDOWN IMMEDIATE;
STARTUP;
@D:\Oracle\Product\11.2.0\dbhome_11203\rdbms\admin\utlrp.sql

set lines 120
set pages 1000
col plsql_code_type for a30

SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;

-- Set system stats to what they were on testing environments and have been signed off
exec dbms_stats.set_system_stats(pname=>'sreadtim', pvalue=>'5');
exec dbms_stats.set_system_stats(pname=>'mreadtim', pvalue=>'15');
exec dbms_stats.set_system_stats(pname=>'mbrc', pvalue=>'32');
exec dbms_stats.set_system_stats(pname=>'cpuspeed', pvalue=>'1900');

exec dbms_stats.gather_dictionary_stats(estimate_percent=>'100', method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>8 -
    , cascade=>TRUE);
exec dbms_stats.gather_fixed_objects_stats();

-- ##############
-- # Parameters #
-- ##############

alter system set optimizer_features_enable='10.2.0.4' scope=both;
alter system set optimizer_use_sql_plan_baselines=FALSE scope=both;
alter system set plsql_code_type = 'NATIVE' scope=SPFILE;

SHUTDOWN IMMEDIATE
STARTUP;

-- ####################
-- # Standby Database #
-- ####################
-- Only do this once upgrade is complete and all is OK

CREATE PFILE FROM SPFILE on both standby DBs
Copy to new 11g home
Make alterations for 11g DB

*** ADD/CHANGE ***

diagnostic_dest
# memory_target= (sum of pga and sga) -- don't use because there are too many bugs
compatible = '11.2.0'
shared_pool_size = 164M
java_pool_size = 164M

*** REMOVE ***

background_dump_dest
core_dump_dest
user_dump_dest
streams_pool_size
remote_archive_enable
pre_11g_enable_capture

SHUTDOWN IMMEDIATE

-- New password file for 11g DBs
D:\Oracle\Product\11.2.0\db_1\BIN\orapwd file=D:\Oracle\Product\11.2.0\db_1\database\pwdTESTDB.ora password=testing

-- Remove old service
D:\oracle\Product\10.2.0\db_1\BIN\oradim -DELETE -SID TESTDB

-- New service
D:\Oracle\Product\11.2.0\dbhome_11203\BIN\oradim -NEW -SID TESTDB

STARTUP MOUNT
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Check that the logs are applying

That’s it, you have successfully upgraded your Oracle database from 10g to 11g. If you are having problem getting the logs to start shipping it might be because you are receiving the ORA-16191 or Error 1017 issue whereby your primary cannot make a connection to the physical standby DB in order to ship the logs across, which produces an “Error 1017 received logging on to the standby” error message in the alert log.

Like it, share it...

Category: Articles


Related Posts

Comments (8)

Trackback URL | Comments RSS Feed

  1. Stacia says:

    Nice blog! Is your theme custom made or did you download it frfom somewhere?
    A design like yours with a feew simple tweeks would really make my blog shine.
    Please let me know whjere you got your design. Bless you

Leave a Reply

Your email address will not be published. Required fields are marked *