11g Upgrade !!!

Step-by-step Upgrade 11g


Matrix:

9.2.0.4.0 (or higher)	11.1.x
10.1.0.2.0 (or higher)	11.1.x
10.2.0.1.0 (or higher)	11.1.x

1. Install the Software (RDBMS) only on new location. (11g RDBMS home)
10g Home -> /oracle/product/10.2.0/db_1
11gHOME >> /oracle/app/11.1.0/db_1

2. Log in to the system as the owner of the new 11gR1 ORACLE_HOME and copy the following files from the 11gR1 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the /tmp directory on your system:
I Used /usr/tmp to store the files

The files are:
$ORACLE_HOME/rdbms/admin/utlu111i.sql
$ORACLE_HOME/rdbms/admin/utltzuv2.sql

Change to the directory where utlu111i.sql and utltzuv2.sql had been copied in the previous step.

Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu111i.sql file. Please note that the database should be started using the Source Oracle Home (9.2.0.x or 10.1.0.x or 10.2.0.x)

SQL> spool on
SQL> spool '/usr/tmp/utlu111i.log

3. dbupgdiag.sql (This script can be get from DOC: 556610.1)
4. Run the dbupgdiag.sql as sys

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/

utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.


Prior to upgrade, resolving invalid objects under SYS and SYSTEM is mandatory.

After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

4. After upgrading to Oracle Database 11g Release 1 (11.1) from Oracle Database9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:

SELECT grantee
FROM dba_role_privs
WHERE granted_role = 'CONNECT'
AND grantee
NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP','LOGSTDBY_ADMINISTRATOR',
'ORDSYS','ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY','WK_TEST',
'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS','WMSYS', 'OLAPDBA', 'OLAPSVR',
'OLAP_USER','OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA','SI_INFORMTN_SCHEMA',
'XDB', 'ODM');

If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrade. The upgrade scripts adjust the privileges for the Oracle-supplied users.

SQL for identifing the users and roles require privileges:
SELECT GRANTEE,PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE ='CONNECT'


5. DBLINK:
During the upgrade to Oracle Database 11g Release 1 (11.1) from Oracle Database 9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), any passwords in database links are encrypted. To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links do not exist in the downgraded database. If you anticipate a requirement to be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can re-create the database links after the downgrade.

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

6. Check for TIMESTAMP WITH TIMEZONE Datatype (Important)

To find the Version 4 Patch for your current Database:

SQL> select * from v$timezone_file;

Note 413671.1 Applying "version 4" Time Zone Files on an Oracle Database

If patch is not available for your patchset, then refer

Note 396387.1 Workarounds when Database time zone patches are not available for your patchset

I Used patch No. 5632264 and copied the 2 .dat files from the unzipped Patch then pasted in the 10g Oracle HOME/oracore/zoninfo. (Take backup of the file prior to copy) Most important (Dont neglect).

(By doing this, your orainventory does not know this. so please make a note of it yourself)

Eventhough this copy doesnot require any downtime, you require to restart the DB to change the version: After restart, you check with Timezone with this sql:
SQL> select * from v$timezone_file;

FILENAME VERSION
------------ ----------
timezlrg.dat 4

7. Change to the directory that you copied files to in Step 2 (in my scenario it is /usr/tmp) Start SQL*Plus and connect to the database instance 10g as a user with SYSDBA privileges.

$ sqlplus "/as sysdba"

SQL> spool TimeZone_Info.log
SQL> @utltzuv2.sql
SQL> spool off

If the utltzuv2.sql script identifies columns with time zone data affected by a database upgrade, then make sure you are backing up the data in varchar2 format before you apply the patch and upgrade the database.

8. Starting in Oracle 9i the National Characterset (NLS_NCHAR_CHARACTERSET) will be limited to UTF8 and AL16UTF16.


9. Optimizer Statistics:

Use Note ID: 560336.1 to get a query to identify stale statistics.

If any row identified by this query (please use the query provided by the script itself) else,
$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

10. Disable Oracle database vault:

Please use Note ID: 453903.1 for doing this.

11. Copy Enterprise Manager database control data. (Important if you use GRID. I Configured Grid already in 10g)
Steps:
1. Set ORACLE_HOME to your old Oracle home
2. Set ORACLE_SID to the SID of the database being upgraded.
3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.
4. Change directory to Oracle Database 11g release 1 (11.1) home.

Command for this:
$ emdwgrd -save -sid old_SID -path save_directory

I used:
$ emdwgrd -save -sid BALAJI -path /oracle/app/11.1.0/db_1/bin (BALAJI is my SID)

6. Provide SYS password which will be prompted.

12. Configuring Network ACLs (Network Access Control Lists)

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XMLDB. If you have applications that use one of these packages, you must install OracleXML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases. Actions are discussed in Post Upgrade tasks as the DBMS_NETWORK_ACL_ADMIN package is introduced after upgrading the database and not available in prior releases.

Script is this. It will give you a script called analyze.sql.

Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off

13. Ensure that all the snapshots are completed without errors. and also the all the replicators are stopped. I did not use any Replicator.

$ sqlplus "/as sysdba"
SQL> SELECT distinct(trunc(last_refresh))
FROM dba_snapshot_refresh_times;

14. Stop the listener and other executables like emctl and also isqlplusctl (I used Grid also the isqlplus)

1. $lsnrctl stop
2. emctl stop dbconsole
3. isqlplusctl stop

15. Ensure no media for media recovery (using select * from v$recover_file)

16. Resolve any unresolved pending transactions using (select * from dba_2pc_pending)
if this query returns any rows, you should do,
SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

17. Ensure the users SYS and SYSTEM have system tablespace as their default. (select username, default_tablespace from dba_users where username in ('SYS', 'SYSTEM'))
if anything is missed out, try to fix as (alter user username default_tablespace system.)

18. Ensure that auditing is enabled in system tablespace by (select tablespace_name from dba_tabels where table_name='AUD$';)

19. Note down the location of files (Controlfile, datafile and redologfile) and Shutdown the instance;

20. Backup the database (COLD BACKUP) I used RMAN.

21. Make a backup of init.ora file (pfile) Comment out obsolete parameters and change the deprecated parameters (List available with Note ID: 454442.1)

make a copy of the file to your 11g oracle_home /dbs directory (Important)

22. Check for the space availability and also it is better to use the database in noarchivelog mode. later you can change the database to archive log more.. (Eventhough Oracle recommended, I did the upgrade with archivelog mode :-))

23. Now create a .profile file for the new oracle 11g (Including ORACLE_BASE,ORACLE_HOME, PATH and with all the environment settings)

24. In the oratab entry do this:
/etc/oratab

#balaji:/oracle/product/10.2.0/db_1:N (Comment out previous entry and add the new entry)
BALAJI:/oracle/app/11.1.0/db_1:N

25. After /etc/oratab is updated to have sid and Oracle Home (11.1), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the sid which is entered in /etc/oratab against 11g home.

[oracle@genius ~]$ . oraenv
ORACLE_SID = [BALAJI] ? BALAJI
The Oracle base for ORACLE_HOME=/oracle/app/11.1.0/db_1 is /oracle/app/11.1.0
[oracle@localhost ~]$

26. At the operating system prompt (Set the .profile for 11g home)

at $ORACLE_HOME/rdbms/bin

type sqlplus "/as sysdba"
sql> startup upgrade

after this..

type
SQL> set echo on
sql> spool on
sql> spool '/usr/tmp/upgrade.log'
sql> @catupgrd.sql
sql> spool off

For me the script run for 01:10:36 hours.

Database will be shutdown by catupgrd.sql. Then restart in the normal mode.

27. Run the utlu111s.sql to get the summary of your upgrade.

28. Compile the invalid objects with utlrp.sql

29. Run dbupgdiag.sql again to check the inegrity of your database.

30. configure listener for lates 11g. I Used netca.

31. Upgrade the statistics for SYS

32. Create spfile from pfile.

Upgradation finished..

Cheers!!!!