oracle upgrade from 8i to 9i v2

7/29/2019 Oracle Upgrade From 8i to 9i V2

1/11

Oracle Upgrade Procedure:Sl.No.

Description Remarks

1. Required SAP Notes:

Note 156548 - Released operating systems SAP

kernel 4.6x ORACLENote 493143 - ORACLE upgrade to 9.2.0: WindowsNote 567301 - Upgrade from 8.1.7 to 9.2 fails with ORA-3114 and ORA-7445Note 578683 - Installation of Newest Patch Set for Oracle9.2.0Note 650638 - Oracle 7.2 datafile conversion tool forWindowsNote 306408 - OPatch: Oracle tool for patch installationNote 684106 - Microsoft runtime DLLs

Read all the SAP Noteslisted here for anyrelevance. Hold a copy of

the note for offlinereference.

2. Get the access details:

3. OS and DB version meets SAP requirements.

Please check SAPNotes:Note 156548 - Released operating systems

SAP kernel 4.6x ORACLE

to check if the Oracle and OS versions match the SAPrequirements.

4. Login as ADM

5. SAP and Oracle Version Details SAP Version 4.6CSAP KernelOracle Version 8.1.7Oracle Patchset

6. Confirm the SAP Instance(s) are shutdown clean

7. Set the NLS_LANG environment variable for the OS serADM.

NLS Character Set can be found from the SourceDatabase using the following query:

SQLPLUS Connect / as sysdba

SELECT * FROM V$NLS_PARAMETERS WHEREPARAMETER LIKE 'NLS_CHAR%';

Take the value of the parameter NLS_CHARACTERSET
https://service.sap.com/sap/support/notes/156548https://service.sap.com/sap/support/notes/156548https://service.sap.com/sap/support/notes/156548https://service.sap.com/sap/support/notes/156548https://service.sap.com/sap/support/notes/156548https://service.sap.com/sap/support/notes/156548https://service.sap.com/sap/support/notes/156548https://service.sap.com/sap/support/notes/156548


2/11

Set the variable NLS_LANG in the Windows userenvironment of adm.

If you are upgrading or migrating a database client, dothis on the client machine.

If NLS_CHARACTERSET has the value WE8DEC, setNLS_LANG to AMERICAN_AMERICA.WE8DEC

8. (Oracle 10g only.)

Check the Oracle SYSTEM and SYSAUX tablespaces for750MB free space each.

brspace f dbshow c tsinfo s SYSTEMbrspace f dbshow c tsinfo s SYSAUX

9. (Oracle 10g only.)

List the number of invalid objects in the database

SQL> spool invalid_objectsSQL> select unique object_name, object_type, ownerfrom dba_objects where status= 'INVALID';SQL> @?/rdbms/admin/utlrp.sqlSQL> select unique object_name, object_type, ownerfrom dba_objects where status= 'INVALID';


3/11

10.

Shutdown the Oracle database Done.

11.

Backup the Oracle Database (Offline mode preferred)Backup should be for the following folders:\ORACLE Save the database

\Save the Oracle software

12.

Backup the Operating System Registry Backup the full registry

13.

Shutdown all the Oracle related services on the server

14.

Run the executable R3DLLINS.EXE (SAPNOTE 684106)

The DLLs can be downloaded with the SAP Note 68410615.

Start the Oracle installation.

Choose a new ORACLE_HOME for the upgrade. Theexisting ORACLE_HOME must not be overwritten.

SAP recommends using (ex:C11920)

Choose the path for the Oracle Home as\ORACLE\\ (ex:D:\ORACLE\C11\920)

Done.


4/11

16

.

Choose NO for creation of Database Done.

17.

For Oracle Net Configuration Assistant, select the Performtypical Configuration option and continue

Done.

Experienced errors duringRemote installation.Configured from theConsole.

18.

Install the required Oracle Patch set.

The Patch set must be installed in the sameORACLE_HOME path as the new Oracle software

Before starting the Patch set installation, check that allthe Oracle services are shutdown.

Here, you have toshutdown the OracleDatabase Servercompulsorily.

Install the latest patchset. The currentavailable patch set is9.2.0.8.0

19.

Restart the Oracle8 services and bring up the database.

Bringing up Oracle8i is required for the Databaseupgrade assistant to run.

If you have problemlogging thru theSQL*Plus, connect viaSVRMGR tool


5/11

20.

Delete the following Environment variables:ORACLE_HOME andTNS_ADMIN

The OS user will be ADM

You can set theORACLE_HOME path toOracle9i instead ofdeleting, though.

21

.

Edit the Oracle parameter file INIT.ORA and set

JOB_QUEUE_PROCESSES=0

The file is in the Old Oracle_home directory

If the parameter does not

exist, you can add the

22

.

Copy the Oracle init.ora file from Oracle8i to Oracle

9iFrom C:\oracle\ora81\database toC:\oracle\PRA\920\dbs

23.

Log off and log on to the user ADM again

24.

Confirm SAP instance is shutdown SAP instance must beshutdown nowcompulsorily

25.

Start the Upgrade process:

a. Start the Oracle Database Upgrade Assistant.START >> Programs >> Oracle >>

>> Configuration andMigration Tools >> Database Upgrade Assistant.

b. Select the SID you are upgrading.

This process takes lessthan 20mins.


6/11


7/11

c. Do not change the passwords for any of the Oracleusersd. Close the Database Upgrade Assistant

Post-Upgrade Procedure:26.

Create a Oracle Listener with the SID and SAP Port detailsusing the Oracle Net8 Configuration Assistant and Net8Manager.

1. Stop the old Oracle Listener service.2. Start the New Oracle Net Manager

Start Programs Oracle -

Configuration and

Migration Tools Net Manager3. on the right side of the Net Manager, in the

default domain field add world

4. On the right side, in the Methods tab, remove theentries hostname and onames


8/11

5. On the Left side, select Listener, on the rightside, under the Listening Locations option, on the

address tab, modify the port number to 1527

6. In the tree on the left, expand Service Namingand select . ChooseEdit Rename. Enter the name .worldand choose OK

7. On the right, inAddress Configuration choosethe plus icon to open a dialog box for a newaddress. Enter the port number 1527

8. Under Service Identification select Use Oracle8Release Compatible Identification. For SID enter.

9. Save and Close the Net Manager

10. Start the Oracle9i Listener Service.


9/11

Follow the Oracle920 upgrade document strictly.27.

Add the following line in the Oracle Network fileSQLNET.ORA

Tcp.nodelay = TRUE

SQLNET.ORA file can be found in the folder\\ORACLE_HOME\NETWORK\ADMIN\

28.

In the directory for 9.2.0,

create the subdirectory SAP.

From the Oracle RDBMS CD3, copy all the files from

the directory:\NT\I386\SAP to the new SAP

directory

Copy the filesdbatools.sar andsapcar.exe

29.

Copy the INITSID.ORA and INITSID.SAP files from the oldORACLE_HOME\database folder to the newORACLE_HOME\database\ folder

Done.

30

.

Startup the Database and create the PFILE for modifying

the Oracle Parameters from the SPFILE.

Command:CREATE PFILE FROM SPFILE;

Delete the SPFILE.ora

Done.

31.

Make sure that the following parameters point to thecorrect directory:

background_dump_dest has to point to:\oracle\\saptrace\background

core_dump_dest has to point to

:\oracle\\saptrace\background

user_dump_dest has to point to:\oracle\\saptrace\usertrace

Done.

32.

Start theAdministration Assistantwith Start Programs Oracle-

Configuration andMigration Tools

Administration Assistant for Windows NT

In the Properties dialog box, change the entry forORA__PFILE to:

\DATABASE\init.ora

v. Set the entry for ORA__AUTOSTART toFALSE and confirm with OK.

33.

Set the environment variable TNS_ADMIN to\\\sapmnt\\SYS\profile\oracle

Done.

34.

Modify the Parameter Compatible as required to thecurrent version.

Done.

35 Modify the OS Environment variables to point from old Done.


10/11

ORACLE_HOME to new Oracle_home

36.

Install the New BR*TOOLS in the SAP folder

\SAP\sapcar -xvf\SAP\DBATOOLS.SAR

Done.

37

.

Delete the Old Oracle Software

Shutdown the databaseStop the Oracle Services.De-install the Old Oracle software using the OracleUniversal InstallerDelete the Old Oracle software services with thecommandntscmgr remove

38.

Delete all the Old Oracle software from the OS

39.

Reset the PATH environment variable to point to the newORACLE_HOME folder

Done.

40.

Start the Oracle Database.

Confirm the New Database version.

Done.

41.

(Oracle 10g only)

Execute the SQL script sapconn_role.sql

Creates a new Oracle Role related to SAP for External userauthentication

Done.

42.

(Oracle 10g only)

Install the Oracle Database client software

43. (Oracle 10g only)

Execute the script SAPDBA_ROLE.SQLsqlplus /nolog @sapdba_role

Done.

44.

(Oracle 10g only)

Deactivate the Oracle recycle bin by executing thefollowing SQL command as the adm user:SQL> purge dba_recyclebin;SQL> alter system set recyclebin = 'off' scope = spfile;

Done.

45.

(Oracle 10g only)

Gather Oracle optimizer statistics as follows:

Execute the following commands as the adm user:SQL> spool %ORACLE_HOME%/cfgtoollogs/dbstats.logSQL> executedbms_scheduler.disable('GATHER_STATS_JOB');SQL> execute dbms_stats.gather_system_stats;SQL> execute dbms_stats.gather_dictionary_stats(ESTIMATE_PERCENT => NULL,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',GRANULARITY => 'ALL',

Done.


11/11

CASCADE => TRUE,OPTIONS => 'GATHER',NO_INVALIDATE => FALSE);SQL> execute dbms_stats.gather_fixed_objects_stats;

46.

(Oracle 10g only)

Modify the Oracle parameters as suggested in SAPNOTE830576

Always download the latest version of SAPNote from theSAP Market place.

Done.

47.

(Oracle 10g only)

Gather statistics for SAP schemas:

sqlplus SAP/SQL> update DBSTATC set activ = 'A' where activ in ('N,'R');SQL> commit;

Done.

48. Update the Oracle database statistics with BRCONNECT asfollows:brconnect -c -u / -f stats -t all f collect -p 4

Done.

49.

Create the Oracle password file:

Set the parameter remote_login_passwordfile toEXCLUSIVE

Open a command prompt and create the pwd.orafile with the following command:orapwd file=%Oracle_Home%\database\pwd.orapassword=entries=100 force=yEnter a password for .

Stop and restart the Oracle database.

Done.

50.

To allow a database user, for example sap, or asystem to connect to the Oracle databaseas sysdba or sysoper (connect dbusername/dbpassword assysdba) although the database isnot started, enter the following commands:sqlplus /nologSQL>connect / as sysdbaSQL>grant sysdba to ;SQL>grant sysoper to ;

Done.

51.

To deactivate user locking after invalid logon attempts,enter the following information in sqlplus:sqlplus /nologsql>connect / as sysdbasql>ALTER PROFILE DEFAULT LIMITFAILED_LOGIN_ATTEMPTS UNLIMITED;sql>exit

Done.

oracle upgrade from 8i to 9i v2

Documents