Monday, July 13, 2009

Oracle Upgrade From 9i To 10g (Unix Platform)

Hi All,

Here is the procedure to upgrade the oracle 9i to 10g (9.2.0.6 to 10.2.0.4) ...

Some Notes You might need in between:

1) Note 841728 - 10.2: Solutions for installation and upgrade problems

2) Note 819830 - Oracle Database 10g: Additional Information on upgrade:UNIX

3) Note 886783 - Installing Oracle 9207 Client Software on UNIX

4) Note 600141 - Oracle9i: Automatic UNDO Management

5) Note 819829 - Oracle Instant Client Installation and Configuration on Unix

6) Note 830577 - Oracle Database 10g: ORA-21700 when upgrading to 10.1/10.2

7) Note 871735 - Current patch set for Oracle 10.2.0


Prerequisites for unix system :

1. Checking the Correct UNIX Users, Groups, and Authorizations:
Enter the following commands with user orasid:
touch /oracle/write_test
rm /oracle/write_test
umask 022
2) Checking the Directory Structure
Create the staging directory /oracle/stage/102_64.
Extract OR110264.SAR from CD 51031670 to /oracle/stage/102_64
Now perform the following:
cd /oracle/stage/102_64/database OR
cd /oracle/stage/102_64/database/Disk1
mv SAP SAP_ORIG

3) Download RDBMS_SAP_64.zip from note #819830 and copy it over to the
/oracle/stage /102_64/database directory

4) Extract the zip file:
cd /oracle/stage/102_64/database OR
cd /oracle/stage/102_64/database/Disk1
Unzip RDBMS_SAP_64.zip (You should now see the directory SAP extracted which
contains the updated version of SAP-specific files)

5) Check the Oracle inventory directory /var/opt/oracle/oraInst.loc
The default location for the Oracle inventory is $ORACLE_BASE/oraInventory

6) Create the empty Oracle home directory before starting the OUI.
/oracle//102_64

7) Running the Pre-Upgrade Script
Execute the following commands:
sqlplus /nolog
SQL> connect / as sysdba
SQL> spool /tmp/pre_upgrade.log
SQL> @/oracle/stage/102_64/database/Disk1/SAP/utlu102i.sql
SQL> spool off
SQL> exit

(You can check the results of the pre-upgrade script by looking at the log file /tmp/pre_upgrade.log.)

8) Checking the Prerequisites for the Oracle Universal Installer
Log in as the Oracle user ora.
Execute the following commands:
cd /oracle/stage/102_64/database/Disk1
./runInstaller –executeSysPrereqs
(If the OUI complains that the operating system version is not certified or
supported, you can ignore this.If OUI reports missing OS patches, you need to
apply these before you can continue. )

9) Download the latest version of the Oracle Client software ‒ OCL1020464.SAR
Before you start installing / extracting the Oracle client software please
shutdown the SAP application running on that host.Updating the Oracle client
software online (when the SAP system is still running) is not supported and may
result in system errors/inconsistencies.
Now do the following:
mv /oracle/client/10x_64 /oracle/client/10x_64_ORIG
mkdir /oracle/client/10x_64
IMPORTANT!! Go up to this directory, cd /oracle/client/10x_64
SAPCAR -xvf /tmp/OCL1020464.SAR
(Refer Note # 886783)

10) Execute the SQL script sapconn_role.sql as follows:
cp /oracle/stage/102_64/database/Disk1/SAP/sapconn_role.sql $ORACLE_HOME/dbs
cd $ORACLE_HOME/dbs
sqlplus /nolog @sapconn_role

11) 9. Migrating to Automatic Undo Management
Read SAP Note 600141,

12) 10. Stopping Statspack Reports (If running)
Log on as the user under which the statspack jobs run (usually PERFSTAT):
SQL> connect PERFSTAT/
SQL> select job, what from user_jobs;
This displays a list of the jobs that are currently entered under the user PERFSTAT.
Delete all jobs with the print job number :
SQL> connect PERFSTAT/
SQL> execute dbms_job.remove( );


13)Checking the SYSTEM and SYSAUX Tablespace for Free space
Make sure that the SYSTEM and SYSAUX tablespaces have at least between 500 MB and 750 MB freespace.
(If your source database is Oracle 9.2, there is no SYSAUX tablespace. In this case you can ignore references to the SYSAUX tablespace in this section. )
Check Space by:
brspace –f dbshow –c tsinfo –s SYSTEM
brspace –f dbshow –c tsinfo –s SYSAUX
If the size is less then 750 MB of freespace extend the tablespace.


14) Checking for Invalid Database Objects
a). Execute the following commands:
SQL> spool invalid_objects
SQL> select unique object_name, object_type, owner from dba_objects where status ='INVALID';
SQL> @?/rdbms/admin/utlrp.sql
SQL> select unique object_name, object_type, owner from dba_objects where status ='INVALID';
SQL> spool off
A) list of invalid objects before the upgrade is now generated.Invalid Objects Before upgrade
B) Check the list of invalid objects after the upgrade.If any objects in the list from before the upgrade are also in the list of invalid objects after the upgrade, you can be sure that this is not due to the upgrade process.

Oracle Software Installation( started )

1) Set the environment variables below by editing the files .dbenv_.sh and .dbenv_.csh in the home directory of ora.
(Do not make the changes to the environment permanent as long as the old database is still running)

a). Make sure the following environment variables are not set:
n TWO_TASK
n ORA_NLS
n ORA_NLS32
n ORA_NLS33

b). Set the following environment variables:
DISPLAY :0.0 (It is the host on which the OUI will be displayed.)
ORACLE_BASE /oracle
ORACLE_SID
ORACLE_HOME /oracle//102_64
NLS_LANG Set this to the output of the script CHECKS.SQL (/oracle/stage/102_64/database/Disk1/SAP . The database must be open before you execute the script CHECKS.SQL. )
ORA_NLS10 $ORACLE_HOME/nls/data
SAPDATA_HOME /oracle/
SHLIB_PATH $ORACLE_HOME/lib:$SHLIB_PATH


c). Include $ORACLE_HOME/bin in your PATH or path variable, replacing the existing variable with the new one:
set path = ($ORACLE_HOME/bin $path)
Check if the temporary directory (usually /tmp) has at least 400 MB of free space.


2) BackUp the Oracle Inventory

3)Running the Oracle Universal Installer

Start the OUI with the user ora as follows:
cd /oracle/stage/102_64/database/Disk1/SAP
./RUNINSTALLER


4) On The first screen press Next



5)



6) After this press Next next and you will see this screen




7) Installing the Current Patch Set
Only have to install the latest (that is, the current) patch set.
SAP Note 871735.
51036965_4 Patch CD.
Now enter the following command to unzip and extract the installation files:
$ unzip *.zip
Stop all listeners and other processes running in the Oracle home directory, where you need to install the patch set.
cd patchset_directory/Disk1
./runInstaller

8) Specify the detail and continue with installation

Prerequisites for DB Upgrade

1. Copying and Adapting the SQL*Net Files
I. Log on as user ora.
II. If ora have environment variable TNS_ADMIN set, do the following:
a) Choose a new location for the new SQL*Net parameter files and set TNS_ADMIN to point to the new location .
b) Copy the SQL*Net parameter files from the old location to
c) Edit the new SQL*Net files by adapting all file paths for the new directory. Be sure to adapt the value of ORACLE_HOME in the listener.ora file.

III. If ora has not set the TNS_ADMIN environment variable, Oracle first searches in /etc, in
$ORACLE_HOME/network/admin for the SQL*Net parameter files. Choose which of the following applies to your installation:
n The SQL*Net parameter files are located in directory /etc:
a) Make a backup copy of the files.
b) Edit the new SQL*Net files by adapting all file paths for the new directory. Be sure to adapt the value of ORACLE_HOME in the listener.ora file.
n The SQL*Net parameter files are located in /network/admin:
a) Copy the SQL*Net parameter files from to .
b) Edit the new SQL*Net files by adapting all file paths for the new directory. Be sure to adapt the value of ORACLE_HOME in the listener.ora file.

IV. Since your database is still running in the old environment, do not start the listener with the new
listener.ora file at this point.

2) Shutting Down the SAP System and Oracle Listener Process
stopsap r3
lsnrctl stop
Database Upgrade( Started)

1) Checking the oratab Entry
oratab file( /etc )
a). Make sure that the oratab file contains the following entry:
::N
Where old_ORACLE_HOME stands for the path to the old oracle software installation.
b). If this entry does not exist, insert it using a text editor.

2) Starting the Database Upgrade Assistant
Start the Oracle Database Upgrade Assistant (DBUA).
Before Starting Check:
Confirm that the following environment variables point to Oracle 10.2 directories:
ORACLE_HOME points to /oracle//102_64
PATH
ORA_NLS10
LD_LIBRARY_PATH or SHLIB_PATH or LIBPATH
Before you start the DBUA make sure that the DISPLAY environment variable is correctly set.

Now start the DBUA:
cd $ORACLE_HOME/bin
./dbua

3) After this you should be able to see this



4) Choose the database SID in the database screen. And click on “Next”

5) Check the Database Assistant Information window detail after analysis. If the information
is regarding the following points, you can ignore and proceed further by clicking “yes”
(Default is “No”)
1. Stale optimizer statistics
2. Changes to Oracle connect role
3. Information on invalid objects

6) In the SYSAUX creation screen apply the following changes
1. Datafile name and location
a. Change the data file path and file name as
/oracle/SID/sapdata1/sysaux_1/sysaux.data1
b. Increase the initial size to 1000Mb
c. Select the Autoextend on check box
d. Increment 100Mb
e. Modify the selection of Maximum size value to 2000Mb from Unlimited

7) In the Recompilation of invalid objects screen

1. Choose check for the recompilation of invalid objects
2. Select the check box for disable the Archive log during the upgrade.
3. Click on “Next” to proceed

8) Select the “I already backed up my database” and choose next

9) In the “Management Options” screen disable the Configure the database with
enterprise manager option

10) In the “Management Options” screen disable the Configure the database with
enterprise manager option

11) Click Finish on the summary Screen

12) a. Conversion Started By 09:00 PM
b. Conversion End By 11:07 Pm
c. Total Conversion Time 2Hr (Approx)

Post-Upgrade Steps

1). Finalizing the Environment of the oraUser
Make sure that the changes you made to the Oracle user environment are permanently stored in the profiles (.cshrc, .dbenv_.csh, and so on).
2) Checking the Upgraded Database
a) Log onto the database as the ora user.
b) Enter the following command to start SQLPLUS:
sqlplus / as sysdba
c) Enter the following SQL commands:
A) Enter the following command to determine the release of the installed software:
SQL> select version from V$INSTANCE;
B) Enter the following command to determine the release and status of the installed database components:
SQL> select comp_id, version, status from dba_registry;
Make sure that the command returns the status VALID.
C) Check that the version returned in the previous two commands is identical.

After the upgrade check that the following document is created:
$ORACLE_HOME/assistants/dbua/doc/help/DefaultBehaviorChangesin10g.html
3) Extract the latest BR*Tools to the kernel directory /sapmnt/SID/exe
(i) Cd /sapmnt/SYS/exe
(ii) Sapcar –xvf DBATL700O10_18-20001045.SAR

4) Copy the initSID.sap from /oracle/SID/920_64/dbs directory
(i) Cd $ORACLE_HOME/dbs
(ii) Cp /oracle/SID/920_64/dbs/initSID.sap .

5) Update the permissions for the OS$ users for BR*Tools usage
i) cp /sapmnt/SID/exe/sapdba_role.sql /oracle/SID/102_64/dbs
ii) cd /oracle/SID/102_64/dbs
iii) ./sqlplus /nolog @sapdba_role SID

6) Change database compatibility by executing the following SQL command as the ora user
a) Login as oraSID
b) Sqlplus /nolog
c) Connect / as sysdba
i) SQL> alter system set compatible='10.2.0' scope = spfile;
d) Restart the Oracle database.
i) Shutdown immediate
ii) startup

7) Deactivate the Oracle recycle bin by executing the following SQL command as the ora user:
a) SQL> purge dba_recyclebin;
b) SQL> alter system set recyclebin = 'off' scope = spfile;

8) Gather Oracle optimizer statistics as follows:
a) Execute the following commands as the ora user:
b) SQL> spool $ORACLE_HOME/cfgtoollogs/dbstats.log
c) SQL> execute dbms_scheduler.disable('GATHER_STATS_JOB');
d) SQL> execute dbms_stats.gather_system_stats;
e) SQL> execute dbms_stats.gather_dictionary_stats (ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',GRANULARITY => 'ALL',
CASCADE => TRUE, OPTIONS => 'GATHER', NO_INVALIDATE => FALSE);
f) SQL> execute dbms_stats.gather_fixed_objects_stats;
g) Check the log file dbstats.log created at $ORACLE_HOME/cfgtoollogs for any errors during the gathering of statistics

9) Identify the objects that are invalid after the upgrade as follows:
a) Execute the following commands as the ora user:
b) SQL> @?/rdbms/admin/utlrp.sql;
c) SQL> spool $ORACLE_HOME/cfgtoollogs/invalid_post.log
d) SQL> select count(*) from dba_objects where status = 'INVALID';
e) SQL> select substr(owner,1,12) owner, substr(object_name,1,30) object, substr(object_type,1,30) type, status from dba_objects where status <>'VALID' order by owner;
f) SQL> spool off;
g) Check the log file to compare the invalid objects before the start of the upgrade to the upgraded system
10) Disable the following oracle parameters
a) Alter system reset commit_write scope = spfile sid=’*’;
b) Alter system reset cursor_space_for_time scope = spfile sid=’*’;
c) Alter system reset db_file_multiblock_read_count scope = spfile sid=’*’;
d) Alter system reset hash_join_enabled scope = spfile sid=’*’;
e) Alter system reset log_archive_start scope = spfile sid=’*’;
f) Alter system reset optimizer_index_cost_adj scope = spfile sid=’*’;
g) Alter system reset optimizer_mode scope = spfile sid=’*’;
h) Alter system reset statistics_level scope = spfile sid=’*’;
i) Alter system reset timed_statistics scope = spfile sid=’*’;
j) Alter system reset trace_enabled scope = spfile sid=’*’;

11) Restart the database to activate the changes
a) Sqlplus /nolog
b) Connect / as sysdba
c) Shutdown immediate;
d) Startup

12) Gather Statistics for SAP Schema
a) sqlplus /nolog
b) SQL> connect / as sysdba
c) SQL> @/oracle/stage/102_64/database/SAP/updDBSTATC10.sql

13) Update the DBCHECKORA control table after the upgrade as follows:
a) cd /oracle/stage/102_64/database/SAP
b) sqlplus sap/ @dbcheckora10upgr

14) Checking the oratab entry
a) cd /var/opt/oracle
b) Make sure that there is an entry like the following in your oratab file:
i) SID:/oracle/C11/102_64:N

15) Start the Listener process
a) Login oraSID
b) Check the status of the listener process
(i) lsnrctl staus
c) if the process is already not running, start the process
i) To start the listener lsnrctl start

16) Don’t rename the old oracle home directory, just rename the /oracle/SID/920_64 directory.
a) mv /oracle/SID/920_64/bin /oracle/SID/920_64/bin.old

17) Change the following R3 instance parameter to the new oracle home /oracle/SID/102_64.
a) DIR_ORAHOME
b) DIR_CLIENT_ORAHOME
c) DIR_SERVER_ORAHOME

18) Install the Oracle 9.2.0.7 Client software as per the note 886783
a) Login as oraSID
b) cd /oracle/client
c) mkdir 92x_64
d) cd 92x_64
e) SAPCAR –xvf /oracle/stage/920_64/client/OCL92064.SAR

That's all If you have any Problem Start Troubleshooting

Dont forget to apply SAP recommeneded opatches after this..


Hope it will make your upgrade smooth...


Regards,

Gagan Deep Kaushal

42 comments:

AndresACastle said...

女學生自拍色情貼片哈拉色情遊戲網哈啦色情咪咪色情网台灣色情偷拍台灣色情光碟台灣色情妹妹台灣色情小說台灣色情小說站台灣色情影片台灣色情影片免費台灣色情按摩台灣色情文學台灣辣妹色情台灣kiss色情網台灣卡通色情免費看台灣成人色情台灣成人色情自拍影片台灣正妹色情同志色情女明星色情圖片女人色情片女人色情影片幼幼色情dvd弘爺色情網強姦色情網後宮色情後宮色情影片性愛色情性愛成人色情性感美女a片色情性感色情女郎情色小說,色情小說情趣色情小說情人色情圖愛情色情片愛愛色情貼影愛愛免費色情圖片成色情影片幼女色情照片

明秋 said...

很用心的blog,推推哦 ........................................

皇雯 said...

廢話不多,祝你順心~^^........................................

v佳馥虹玲 said...

how do u do?................................................................

嘉雯 said...

how do u do?xvideo打飛機專用網洪爺免費洪爺色情片洪爺貼圖區洪爺成人線上洪爺影城洪爺色論壇洪爺貼圖洪爺成年人網洪爺免費色情洪爺色情貼援交妹辣妹野球拳情色文學情趣聊天室性感辣妹裸體遊戲做愛偷拍一夜情視訊洪爺色情貼洪爺免費色情洪爺成年人網洪爺貼圖洪爺色論壇洪爺影城洪爺成人線上洪爺貼圖區洪爺色情片洪爺免費洪爺色情貼洪爺免費色情洪爺成年人網洪爺貼圖洪爺色論壇洪爺影城洪爺成人線上洪爺貼圖區洪爺色情片洪爺免費洪爺免費洪爺色情片洪爺貼圖區洪爺影城洪爺色論壇洪爺貼圖洪爺成年人網洪爺免費色情洪爺色情貼洪爺成人線上

NicolaSigel0508 said...

辛苦了!祝你愈來愈好!........................................

向霖 said...

看看blog放鬆一下,工作累死了............................................

SungR_Auclair0佳亦 said...

若對自己誠實,日積月累,就無法對別人不忠了。........................................

玄雨 said...

thank u........................................

LoganO_Kellam1 said...

噴泉的高度,不會超過它的源頭。一個人的事業也是如此,它的成就絕不會超過自己的信念。.............................................

GladisSe皓珊gal1119 said...

不要把生命看得太嚴肅,反正我們不會活著離開。..................................................

健豪 said...

弱者等待時機,強者製造時機........................................

于呈均名 said...

the food is delicious!............................................................

哲維 said...

Learning makes life sweet.............................................................

hernande said...

成人分享 視訊, 未成年聊天 免費色情片觀賞 交友 日本av女優 性感美女 成人影片免費下載 偷拍成人貼圖站 6k聊天室 台灣愛愛片 情趣貼徒區 18免費線上 台灣美少女自拍a片免費 光碟影城 a片家庭教師姊姊h的偏差 情色五月天 日本色情短片 性感美女成人一夜情 免費影片下載383 完美女人影音 美女視訊 完美女人 辣妹成人網 人妻熟女bt 歐美熟女作愛 視訊做愛 空姐 a片a圖貼圖網 情色寫真集 85cc免費影城長片 18禁微風 成人嘟嘟往 www.777 皮卡丘貼圖 援交綱站 嘟嘟情人網  鋼管 av女憂 洪爺影音館 大奶av女優 gogosexy 遊戲下載 強姦 女性性高潮 後宮無碼情色網 限制級小說作者 0204成人影片欣賞 無碼性愛 林志玲走光

千惠千惠 said...

Many a little makes a mickle...................................................................

宛齊陳 said...

去冬眠以前,臨去秋波一下~~大家順利!!!.................................................................

雅玲 said...

河水永遠是相同的,可是每一剎那又都是新的。.................................................................                           

韋以韋以 said...

卡爾.桑得柏:「除非先有夢,否則一切皆不成。」共勉!.................................................................                           

倩亮倩亮 said...

人不能像動物一樣活著,而應該追求知識和美德............................................................

李志吉 said...

工作,是愛的具體化~~~~努力吧!.......................................................

said...

人生是故事的創造與遺忘。............................................................

JasonBirk佳琪 said...

A friend in need is a friend indeed...................................................................

婷珊 said...

要用心經營哦~~祝福你~~
............................................................

姿柯瑩柯dgdd憶曾g智曾 said...

好久沒有這樣輕鬆享受閱讀的樂趣了~~留個言邀您分享我的快樂~~............................................................

陳佑發 said...

Fancy may kill or cure...................................................

莊雅和莊雅和莊雅和 said...

Many a true word is spoken in jest..................................................................

RicoLisi0802志竹 said...

一個人的價值,應該看他貢獻了什麼,而不是他取得了什麼....................................................

徐禎緯 said...

真正仁慈的人,會忘記他們做過的善行,他們全心投入現在的工作,過去的事已被遺忘。..................................................

怡謝伶 said...

堅持是為著某種目的或目標,而持續不斷朝向既定方向努力的一種意念。..................................................

凱v胡倫 said...

旁觀自己的悲傷是解脫,主觀自己的悲傷是更加悲傷................................................

凱v胡倫 said...

只用微笑說話的人,才能擔當重任。...............................................

吳沛黃以富民 said...

相見亦無事,不來常思君......................................................................

誠陳侑 said...

生活總是起起伏伏,心情要保持快樂才好哦!!............................................................

文汝劉文汝劉文汝劉 said...

謝謝大大分享!!經典!~(。・ω・)............................................................

怡屏 said...

財富並非永遠的朋友,但朋友卻是永遠的財富。......................................................................

靜蔡蔡蔡蔡怡 said...

加油!期待更新哦!..................................................................

舒夏怡 said...

回應是最大的支持^^y~~~甘吧嗲............................................................

said...

打聲招呼,祝你一切平安!............................................................

建枫 said...

培養健全孩子最好的方法是父母先成為健全的人。......................................................................

Rits said...

Whats you mobile no Gagan
Ritesh Soni

Teena Seguin said...

Read all the related Posts:


How to do Website Compatibility Testing (Made Simple)

7 steps to select Automated Software Testing Tools

64 Software Manual Testing Interview Questions

Why need Software Testing Tools?

Overview of Manual Software Testing

Embedded Software Testing - Overview

Automation vs Manual Software Testing

Standard definition of software testing

What parameters to consider for Performance Testing?

Post a Comment