Share the content if you found it is useful (You can share using 300 community websites) click "share" at the end of the post.

You are encouraged to leave a comment.








Wednesday, December 10, 2008

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!!!!

No comments:

Related Posts Plugin for WordPress, Blogger...

Let us be Friends...

Share |

Popular Posts

Labels

11.5.1. to 11.5.5 Cloning Procedure. 11g 11i 11i and R12 11i Autoconfig 11i Cloning 12.1.1 SSL 2 Node RAC Adconfig.sh errors out adgennls.pl adop Advanced Replication Apache version Apex Apex for Oracle database Apex Installation Apex Installation on Oracle E Business Suite. 11i with Apex Apex on 11i Apex on Oracle 11i Apex402 apexins.sql APPL_TOP Character Application Express Application server Issue APPLSYS password Apps Password Asynchronous Replication Autoconfig Autoconfig Managed Beehive Bit of operating system Block block media corrupt BPEL Central Inventory Character Set Conversion Cloning of 11i Cloning of Oracle Applications Clusterware Concurrent CPU 2011 CPU 2012 April CPU 2012 January. CPU 2012 October CRS custom top Custom Top Creation Database . Database Query Dataguard DBMS_REPAIR Demantra . Domain Name E Business Suite Migration EBS 12.2 EPM exp/imp Oracle Applications Flashback table flows_020200 FND_TOP/resource FNDCPASS FNDCPUCF Forms server upgrade Forms startup FRM-92101 Fusion Middleware Hostname Hot Backup Hot cloning HotBackup Hotbackup Cloning of Oracle Apps hrglobal.drv. NLS Saudi Arabian HRMS hyperion Import and Export Oracle Applications Install Oracle Installation of R12 Installation Steps integration Oracle EBS Issues Database issues Oracle EBS Issues with Upgrade 12.1.1 Java Mission Control Java Upgrade java/sql/SavePoint JInitiator Junk Characters Language Translation Linux Linux Migration listener trace Listner version Local Inventory Login Page Master-Master Replication Memory Tuning Multiple Language NLS Non-Autoconfig Enabled OBIEE 11g Installation OBIEE Issues OIM Opatch version OpenSSL ora-01031 Oracle oracle 11g Oracle Announcement Oracle Application servers Oracle Application services Oracle Applications Oracle Applications 11i oracle applications 12.2 Oracle Applications Cloning Oracle Applications Upgrade Oracle Applications. Oracle Apps cloning Oracle BPEL oracle certifications oracle database Oracle Database RMAN Oracle Database upgradation Oracle DB oracle E Business suite 12.2 Oracle EBS oracle EPM Oracle Forms Logo oracle Fusion Applications Oracle Inventory oracle news Oracle OUI Oracle R12 oracle Sun Solaris Oracle Tuning Oracle Virtual Box Oracle Webgate Oracle WMA configuration orcladmin password for Oracle Applications Pasta Configuration pasta.cfg patch PDF Performance Physical Standby Platform migration Printer Configuration Profile Option R12 RAC Rapid Install Rapidwiz Real Application Clusters 10g Recovery RMAN RMAN Recovery SA Gosi Service Oriented Architecture SOA SQLTXPLAIN SSL Configuration step-by-step upgrade Sun solaris Swap Tablespace Trace Enable Trace file Translation Synchronization Patch transport tablespace tuning Tuning SGA uifont.ali Underscore Universal Content Management updates.oracle.com Upgradation of 12.0.5 to 12.1.1 Upgrade Upgrade 11g Upgrade Apps Upgrade DB Upgrade to 11g UTF8 version of oracle applications versions Web server upgrade weblogic windows workflow version XML Publisher version