64 Bit Upgradation
Of Oracle server
Oracle software comes in 64bit and 32 bit
version. The 32 bit oracle has a limitation of addressing the 4 GB of physical
memory. The main reason for up gradation is to surmount this limitation posed
by 32 bit addressing scheme and also help increasing the scalability of the
database server. This document clearly explains the step by step process of
upgrading a database bit version from 32-bit to 64-bit.
Step 1 determining the bit version of
oracle binaries:
The bit version of oracle binaries can be
checked using file command in UNIX.
For 32 bit it will produce output like
this.
cd $ORACLE_HOME/bin
$
file oracle
oracle: ELF 32-bit MSB executable SPARC
Version 1, dynamically linked, not stripped
and for 64 bit versions the output will be
like below.
oracle: ELF 64-bit MSB executable SPARCV9
Version 1, dynamically linked, not stripped
Step 2: Down load the oracle binaries for
upgrade process.
2.1 Download the patch set 9.2.0.8 from metalink and
ftp it to the target server.
We have 9.2.0.6.0
version of oracle in order to mitigate the errors that might occur during the process of up gradation it’s
recommended to apply patch set of 9.2.0.8 (latest patch set is 9.2.0.8).
2.2 Download the 64 bit version and 9.2.0.8 patch set
Down load the 9.2.0.1 base version and
install 64 bit oracle in predetermined oracle HOME. After this activity the new
9.2.0.8 patch should be applied to 64 bit home. The installation of 64 bit
oracle and patching up will not affect the availability of instance. So this
activity can be completed well in advance.
Step 3: Prepare the OS for addressing
larger SGA
Change the kernel
parameter for oracle for large SGA. By changing the
The shm_max should
be greater than SGA_MAX_SIZE. Otherwise the OS will create multiple shared
segments for oracle which may result in suboptimal performance.
shmsys:
shm_max=12884901888 (12g. Max RAM is 24g). This can be tuned after the
release based on the requirement.
semsys:
sem_mns=5000
set semsys:
sem_mni=256
set semsys:
sem_opm=256
Kernel parameter
can be changed by editing the /etc/system(in Solaris) files by logging as root. After changing the
values server needs to be restarted to reflect the new values.
Step 4 : Backing up the database .
In this section we need to take the
both logical and physical backup of database which will be required if the
whole activity needs to be rolled back.
4.1 Take the full export dump if it’s feasible. If it will
take a long time take only the full
structure of database.
4.2 Create the backup of control file in binary and ascii
format.
SQL>
create backup control file to ‘absolutefilepath’ --- for binary backup
SQL>create
backup control file to trace – for ascii format.
Copy the ascii format form user_dump_dest .
4.3 Create the list of invalid objects. This list needs to
be compared with the invalid
Objects after the upgradation. Any discrepancy
at that time should be addressed. Spool the output of below sql.
SQL>Select owner,object_name,object_type,status from
dba_objects where status <> ‘VALID’
4.4 Save the list of jobs in the database and their intervals
as these jobs may go into broken state during the migration process.
4.5 Prepare the
script for db links in the database by login as sys. Since the database has db links pointing to Sybase database.
Since its 9i we can get the password for db links by querying the sys.link$.
4.5 Check the components installed
in the target database. These components needs to be installed in the after the
64 bit up gradation.
SQL>select
comp_id,comp_name,version,status from dba_registry;
Step 5: Patching of 32bit oracle .
5.1.
Check the saherd_pool_size and shared_pool_reserved_size in database and set it
above 150 MB.
5.2.
Apply the patch set 9.2.0.8 on top of 9.2.0.6 oracle.
The patch set can be applied either
through GUI or from command line through response files. For GUI VNc server
needs to be started first.
Then run the oracle
installer ./runinstaller.
5.3. After the
patching the oracle migrate scripts needs to be executed from sqlplus.
shutdown
database & listener
LSNRTCTL>STOP (default listener is configured for this
db)
SQL>startup
migrate
SQL>spool
patch.log
SQL>@?/rdbms/admin/catpatch.sql
SQL>shutdown
SQL>startup
SQL>@?/rdbms/admin/utlrp.sql
----for compiling the invalid objects
check invalid
objects count
Note: The alert
logs should be monitored while startup and shutdown.
Step 6: Starting up of database in 64 bit
oracle Home
6.1 copy the initfile, spfile,pwfile into new ORACLE_HOME/dbs
6.2 copy the listener.ora
and tnsnames.ora to new $ORACLE_HOME/network/admin
6.3
Copy the init files for gateways to their respective locations. All the Sybase gateways should be copied to
$ORACLE_HOME(Newone)/ tg4sybs/admin from $ORACLE_HOME(Newone)/ tg4sybs/admin.
6.4 Check
for ORACLE_HOME variables in .profile & change it and also update
wherever applicable
and also check for the Oracle database links including the Sybase connectivity.
6.5.
Add the variable LD_LIBRARY_PATH = ORACLE_HOME/lib32 In .profile.
6.6.
Make following changes to init.ora
aq_tm_processes=0
job_queue_processes=0
_system_trig_enabled= FALSE
These settings are
recommended by oracle during the upgrade process.
6.7.
Increasing the following memory parameters to address double the memory.
SHARED_POOL_SIZE
SHARED_POOL_RESERVED_SIZE
LARGE_POOL_SIZE. These memories need to be
doubled as the internal oracle structures
will use the double memory size. As the internal oracle pointers will be using
double the size in 64bit versions (As in case of float data type in c)
|
Parameter name
|
Current Value (Bytes)
|
Values to be
resized(Bytes)
|
|
SHARED_POOL_SIZE
|
15000000
|
30000000
|
|
SHARED_POOL_RESERVED_SIZE
|
436207616
|
872415232
|
|
LARGE_POOL_SIZE
|
67108864
|
134217728
|
.
6.8.
Startup the database using pfile
SQL>
Startup migrate pfile= ‘$RACLE_HOME/dbs/initMNDP.ora’
SQL>
@?/rdbms/admin/catalog.sql
6.9 Check the
validity of the DBMS_STANDARD package:
SQL>
select status from dba_objects where object_name='DBMS_STANDARD' and
object_type='PACKAGE' and owner='SYS';
If the package is invalid recompile it.
SQL> alter package dbms_standard compile;
6.10 Run the following scripts:
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
After running this script,
check for invalid objects:
SQL> select owner, object_name, object_type from dba_objects
where status <> 'VALID';
Recompile any invalid objects to avoid
problems while running the utlirp.sql script
The components catproc and catalog should be in
valid state. If any of these components are invalid , ORA-600 error will be
logged in alert logs. These components can be made valid by executing the
cataproc.sql for CATPROC, and catalog.sql for CATALOG.
6.11 Set the system to spool results to
a log file for later verification of success:
SQL> SPOOL catoutw.log
If
you want to see the output of the script you will run on your screen,
then you can also issue a SET ECHO ON
statement:
SQL> SET ECHO ON
Run utlirp.sql:
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
The utlirp.sql script recompiles
existing PL/SQL modules in the format required by the new database. This script
first alters certain dictionary tables. Then, it reloads package STANDARD and
DBMS_STANDARD, which are necessary for
using PL/SQL. Finally, it triggers a recompile of all PL/SQL modules, such as
packages, procedures, types, and so on.
Turn off the spooling
of script results to the log file:
SQL>
SPOOL OFF
Then, check the spool file
and verify that the packages and procedures
compiled successfully. You named the
spool file in Step 6 the suggested
name was catoutw.log. Correct any
problems you find in this file.
If you specified SET ECHO ON, then
you may want to SET ECHO OFF now:
SQL> SET ECHO OFF
6.12. Remove these from the pfile
aq_tm_processes=0
job_queue_processes=0
_system_trig_enabled=false
6.13. The SGA and memory related parameters needs to be checked and tuned
after the upgrade based on the requirements:
shared_poolsize=600 (doubled the current
one 304)
db_cache_size=3500m
db_keep_cache_size=500m
db_recycle_cache_size=500m
sga_max_size=6000m
pga_aggregate_target=3000m
log buffer = 4096000 (the current one of
20mb is way above normal, may cause log file sync waits at times)
6.14. Bounce the DB and release to Application team for testing
DBA can verify the changes from
select address from v$sql (it would be 16 char long as compared to the 8chr of
32-bit).
