4) Upgrade database from 10g to 11g

1. Check Invalids using

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;

       select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from

       dba_objects where status='INVALID' order by owner,object_type;

       select owner,object_type,count(*) from dba_objects where status='INVALID' group by

       owner,object_type order by owner,object_type ;

2.Turn off recycle bin and purge DBA_Recycle bin

3.Run utlrp.sql to compile invalids

4.Copy pre Upgrade script from 11g Home to local directory

$ORACLE_HOME/rdbms/admin/utlu112i.sql

spool upgrade_info.log

@utlu112i.sql

spool off

5. Check Spool Upgrade for any issues

6.Run dbupgdiag.sql to find any duplicate objects in sys or system schema

7.Run Stats

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSMAN');

8.Stop the datbase(10g) and the listener.

9.Copy pfile from 10gORA_HOME/dbs to 11g ORA_HOME/dbs

10.Creat a Environment File for Software installation and Patches

Login as oracle (DB owner) to the DB server and create a file like this and put under Oracle user’s $HOME:

 [orcrp1@catos-nrdbdcl01 CRP111202]$ cat temp11gR2.env

ORACLE_HOSTNAME=catos-nrdbdcl01.mdsinc.com; export ORACLE_HOSTNAME

ORACLE_HOME=/d01/CRP111202/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=CRP1; export ORACLE_SID

export PATH=$ORACLE_HOME/bin:$PATH

ORA_NLS10=/d01/CRP111202/product/11.2.0/dbhome_1/nls/data/9idata/; export ORA_NLS10

11.Start a VNC server session and connect using VNC viewer (VNC must be running on some port assigned to the DB owner).

Login as oracle (DB owner) user.

Run the env file created above (tempenvdb112.env).

Commnd: $ . ./tempenvdb112.env

Check a sqlplus session from VNC server to see if its using right set of binaries

run dbua

12.Once DBUA completes do following

a)change the compatible initialization parameter to 11.2.0.2.0.

$ sqlplus /as sysdba

SQL> alter system set compatible= '11.2.0.2.0 ' scope=spfile;

SQL> shutdown immediate

SQL> startup

SQL> show parameter compatible

b)Now run the post upgrade tool (utlu112s.sql) to verify the status of the upgrade. Script location: $ORACLE_HOME/rdbms/admin.

This can be run from the session in Step (b)

SQL> spool post_upgrade.log

SQL> @utlu112s.sql

SQL> SPOOL OFF

c)Run utluiobj.sql to enlist new invalid objects during upgrade. Script location: $ORACLE_HOME/rdbms/admin.

Can be run from the session in step (b)

SQL> spool utluiobj.sql.lst

SQL> @utluiobj.sql

SQL> SPOOL OFF

d)Run @$ORACLE_HOME/md/admin/catmgdidcode.sql

e)select count(*) from dba_objects where status = 'INVALID ';

f)Check/Configure Listener for New Oracle 11g Database in 11g ORACLE_HOME

g)Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql to the database server node (to RDBMS ORACLE_HOME/admin. Keep a copy of the older one first, if any).

 1. As oracle user in another session:

cd $ORACLE_HOME/admin (11g DB)

mv adgrants.sql adgrants.sql.bkp<date> à if any file of that name is present

cp $APPL_TOP/admin/adgrants.sql $ORACLE_HOME/admin

2. $ sqlplus / as sysdba @adgrants.sql [APPS schema name]

h)cp $AD_TOP/patch/115/sql/adctxprv.sql $ORACLE_HOME/admin

$ sqlplus apps/[APPS password] @adctxprv.sql [SYSTEM password] CTXSYS]

i)SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

j)Update the RDBMS DST version in 11gR2 (11.2.0.2) to DSTv14 using DBMS_DST

1. Check current RDBMS DST version and "DST UPGRADE STATUS".

SQL> conn / as sysdba

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 3

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE

2. SQL> SELECT version FROM v$timezone_file; 

-Output should be same as above.

3. Check UPFRONT using DBMS_DST if there is affected data that cannot be resolved automatically.

SQL> conn / as sysdba

-- start prepare window

-- these steps will NOT update any data yet.

-- this alter session might speed up DBMS_DST on some db's

-- see Bug 10209691

SQL> alter session set "_with_subquery"=materialize;

SQL> exec DBMS_DST.BEGIN_PREPARE(14)

**-- check for prepare status

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 3

-- DST_SECONDARY_TT_VERSION 14

-- DST_UPGRADE_STATE PREPARE

-- truncate logging tables if they exist.

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;

**-- log affected data

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;

/

**-- check what tables have affected data that cannot be resolved automatically.

-- if this gives no rows then there is no problem at all

SELECT * FROM sys.dst$affected_tables;

-- IF previous select gives rows then you can see

-- what kind of problem there are in those rows

SELECT * FROM sys.dst$error_table;

-- error_on_overlap_time is error number ORA-1883

-- error_on_nonexisting_time is error number ORA-1878

-- for a explanation of the reported data please see

-- "Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data"

-- For the "error_on_overlap_time" and "error_on_nonexisting_time" you do not HAVE to

-- take action on this data to upgrade the DST version, but it is advised

-- to at least to check the results AFTER the update.

-- all "error_on_overlap_time" rows

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

-- all "error_on_nonexisting_time" rows

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

**-- check for all other possible problems

SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

When updating from DSTv1 or DSTv2 (mainly after 9.2 upgrades) to a higher DST version it is possible to have also '1882' errors.

The cause is explained in Note 414590.1. These can be ignored, they will be corrected during the actual update of the dst version.

-- end prepare window, the rows above will stay in those tables.

SQL> EXEC DBMS_DST.END_PREPARE;

-- check if this is ended

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 <the old DST version number>

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE

4. Do the actual RDBMS DST version update of the database using DBMS_DST:

SQL> conn / as sysdba

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> set serveroutput on

-- check if previous prepare window is ended

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 <the old DST version number>

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE

5. If there are objects containing TSTZ data in recycle bin, please purge the bin now. Otherwise dbms_dst.begin_upgrade will report "ORA-38301: Can not perform DDL/DML over objects in Recycle Bin".

SQL> purge dba_recyclebin;

-- clean used tables

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;

SQL> alter session set "_with_subquery"=materialize;

-- start upgrade window

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(<the new DST version number>);

-- the message "An upgrade window has been successfully started." will be seen

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

-- restart the database

SQL> shutdown immediate

SQL> startup

SQL> alter session set "_with_subquery"=materialize;

**-- now upgrade the tables that need action

SQL> set serveroutput on

SQL> 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;

/

-- ouput of this will be a list of tables like:

-- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S

-- Number of failures: 0

-- Table list: SYSMAN.MGMT_PROV_ASSIGNMENT

-- Number of failures: 0

-- Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES

-- Number of failures: 0

-- Failures:0

-- if there where no failures then end the upgrade.

SQL> VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

END;

/

-- output that will be seen:

-- An upgrade window has been successfully ended.

-- Failures:0

-- last checks

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

-- needed output:

-- PROPERTY_NAME VALUE

-- ---------------------------- ------------------------------

-- DST_PRIMARY_TT_VERSION 14

-- DST_SECONDARY_TT_VERSION 0

-- DST_UPGRADE_STATE NONE

SQL> SELECT * FROM v$timezone_file;