We can use three methods to upgrade our oracle database.
- Export / Import
- Database Upgrade Assistant
- Manually by using Scripts (We prefer manually method to upgrade our database)
Step 1 (Prepare the Database to be Upgraded)
1. Make sure the DB_DOMAIN initialization parameter in your initialization parameter file is set to one of the following:
- .WORLD
- A valid domain setting for your environment
2. Make sure the _SYSTEM_TRIG_ENABLED initialization parameter is set to FALSE in the initialization parameter file. If this initialization parameter is not currently set, then explicitly set it to FALSE:
_SYSTEM_TRIG_ENABLED = FALSE
3. Check free Space in SYSTEM and Rollback Segment Tablepace
Upgrading to a new release requires more space in your SYSTEM tablespace and in the tablespaces where you store rollback segments. In general, you need at least 20 MB of free space in your SYSTEM tablespace to upgrade.
How to check the free space?
clear buffer
clear columns
clear breaks
set linesize 500
set pagesize 5000
column a1 heading 'Tablespace' format a15
column a2 heading 'Data File' format a45
column a3 heading 'TotalSpace [MB]' format 99999.99
column a4 heading 'FreeSpace [MB]' format 99999.99
column a5 heading 'Free%' format 9999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
SELECT a.tablespace_name a1,a.file_name a2,a.avail a3,NVL(b.free,0) a4,NVL(ROUND(((free/avail)*100),2),0) a5
FROM
(SELECT tablespace_name,SUBSTR(file_name,1,45) file_name,file_id,ROUND(SUM(bytes/(1024*1024)),3) avail
FROM sys.dba_data_files GROUP BY tablespace_name, SUBSTR(file_name,1,45),file_id) a,(SELECT tablespace_name, file_id, ROUND(SUM(bytes/(1024*1024)),3) free
FROM sys.dba_free_space GROUP BY tablespace_name, file_id) bWHERE a.file_id = b.file_id (+)ORDER BY 1, 2
How to add more space to the SYSTEM tablespace:
ALTER TABLESPACE system
ADD DATAFILE '
SIZE 16M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
4. Run SHUTDOWN IMMEDIATE on the database and backup your database.
SQL> SHUTDOWN IMMEDIATE
Step 2 (Upgrade the Database)
1. Stop the OracleServiceSID Oracle service of the oracle 8.1.6 database
C:\> NET STOP OracleService
C:\>ORADIM –DELETE –SID
C:\>ORADIM –NEW –SID
4. Put your init file in database folder at new oracle 8.1.7 home from 8.1.6 and adjust the initialization parameter file for use with the new release.
db_domain = .WORLD
optimizer_mode = choose
job_queue_processes = 0
aq_tm_processes = 0
5. Connect to the new oracle 8.1.7 instance as a user SYSDBA privilege and issue following command:
SQL>STARTUP RESTRICT
You don’t need to use the PFILE option to specify the location of your initialization parameter file in our case because we are using INIT
6. Execute following scripts:
SPOOL c:\revoke_restricted_session.log;
SELECT 'REVOKE restricted session FROM ' username ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
SPOOL OFF;
7. Run Spool File:
@c:\revoke_restricted_session.log;
8. Enable Restricted Session
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
9. Run Migration Scripts
SPOOL catoutu.log
SET ECHO ON
@u0801060.sql # Script for 8.1.6 -> 8.1.7
SET ECHO OFF
SPOOL OFF
10. ALTER SYSTEM DISABLE RESTRICTED SESSION;
11. SHUTDOWN IMMEDIATE
NOTE: The script creates and alters certain dictionary tables. It also runs the catalog.sql and catproc.sql scripts that come with the release to which you are upgrading, which create the system catalog views and all the necessary packages for using PL/SQL.
Step 3: (Post migration Steps)
You may execute additional scripts:
# Run all sql scripts for replication option
@$ORACLE_HOME/rdbms/admin/catrep.sql
# Collect I/O per table (actually object) statistics by statistical sampling
@$ORACLE_HOME/rdbms/admin/catio.sql
# This package creates a table into which references to the chained rows for an IOT (Index-Only-Table) can be placed using the ANALYZE command.
@$ORACLE_HOME/rdbms/admin/dbmsiotc.sql
# Wrap Package which creates IOTs (Index-Only-Table
@$ORACLE_HOME/rdbms/admin/prvtiotc.plb
# This package allows you to display the sizes of objects in the shared pool, and mark them for keeping or unkeeping in order to reduce memory fragmentation.
@$ORACLE_HOME/rdbms/admin/dbmspool.sql
# Creates the default table for storing the output of the ANALYZE LIST CHAINED ROWS command
@$ORACLE_HOME/rdbms/admin/utlchain.sql
# Creates the EXCEPTION table
@$ORACLE_HOME/rdbms/admin/utlexcpt.sql
# Grant public access to all views used by TKPROF with verbose=y option
@$ORACLE_HOME/rdbms/admin/utltkprf.sql
# Create table PLAN_TABLE that is used by the EXPLAIN PLAN statement. The explain statement requires the presence of this table in order to store the descriptions ofthe row sources.
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
# Create performance tuning views
@$ORACLE_HOME/rdbms/admin/catperf.sql
# Create v7 style export/import views against the v8 RDBMS so that EXP/IMP v7 can be used to read out data in a v8 RDBMS. These views are necessary if you want to exportfrom Oracle8 and import in an Oracle7 database.
@$ORACLE_HOME/rdbms/admin/catexp7.sql
# Create views of oracle locks
@$ORACLE_HOME/rdbms/admin/catblock.sql
# Print out the lock wait-for graph in a tree structured fashion
@$ORACLE_HOME/rdbms/admin/utllockt.sql
# Creates the default table for storing the output of the analyze validate command on a partitioned table
@$ORACLE_HOME/rdbms/admin/utlvalid.sql
# PL/SQL Package of utility routines for raw datatypes
@$ORACLE_HOME/rdbms/admin/utlraw.sql
@$ORACLE_HOME/rdbms/admin/prvtrawb.plb
# Contains the PL/SQL interface to the cryptographic toolkit
@$ORACLE_HOME/rdbms/admin/dbmsoctk.sql
@$ORACLE_HOME/rdbms/admin/prvtoctk.plb
# This package provides a built-in random number generator. It is faster than generators written in PL/SQL because it calls Oracle's internal random number generator.
@$ORACLE_HOME/rdbms/admin/dbmsrand.sql
# DBMS package specification for Oracle8 Large Object This package provides routines for operations on BLOB and CLOB datatypes.
@$ORACLE_HOME/rdbms/admin/dbmslob.sql
# Procedures for instrumenting database applications DBMS_APPLICATION_INFO package spec.
@$ORACLE_HOME/rdbms/admin/dbmsapin.sql
# Run obfuscation toolkit script.
@$ORACLE_HOME/rdbms/admin/catobtk.sql
# Create Heterogeneous Services data dictionary objects.
@$ORACLE_HOME/rdbms/admin/caths.sql
# Stored procedures for Oracle Trace server
@$ORACLE_HOME/rdbms/admin/otrcsvr.sql
# Oracle8i Profiler for PL/SQL Profilers are helpful tools to investigate programs and identify slow program parts and bottle necks. Furthermore you can determine which procedure; function or any other code part is executed how many times. To be able to usethe DBMS_PROFILER package you have to install once for your database the following packages. Do this as user SYS
@$ORACLE_HOME/rdbms/admin/profload.sql
@$ORACLE_HOME/rdbms/admin/proftab.sql
@$ORACLE_HOME/rdbms/admin/dbmspbp.sql
@$ORACLE_HOME/rdbms/admin/prvtpbp.plb
Recompiling Invalid PL/SQL Modules
The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, etc.
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Additional Checks after the Migration
Check for Bad Date Constraints
A bad date constraint involves invalid date manipulation, which is a date manipulation that implicitly assumes the century in the date, causing problems at the year 2000. The utlconst.sql script runs through all of the check constraints in the database and marks constraints as bad if they include any invalid date manipulation. This script selects all the bad constraints at the end. Oracle7 let you create constraints with a two-digit year date constant. However, version 8 returns an error if the check constraint date constant does not include a four-digit year.
To run the utlconst.sql script, complete the following steps:
SQL> SPOOL utlresult.log
SQL> @utlconst.sql
SQL> SPOOL OFF
Server Output ON
Statement processed.
Statement processed.
Checking for bad date constraints
Finished checking -- All constraints OK!
After you run the script, the utlresult.log log file includes all the constraints that have invalid date constraints. The utlconst.sql script does not correct bad constraints, but instead it disables them. You should either drop the bad constraints or recreate them after you make the necessary changes.
Rebuild Unusable Bitmap Indexes
During migration, some bitmap indexes may become unusable. To find these indexes, issue the following SQL statement:
SELECT index_name, index_type, table_owner, status FROM dba_indexesWHERE index_type = 'BITMAP'AND status = 'UNUSABLE';
Rebuild Unusable Function-Based Indexes
During upgrade, some function-based indexes may become unusable. To find these indexes, issue the following SQL statement:
SELECT owner, index_name, funcidx_statusFROM dba_indexesWHERE funcidx_status = 'DISABLED';
Change the Password for the OUTLN User
The OUTLN user is created automatically during installation of Oracle8i. This user has DBA privileges. Use the ALTER USER statement to change the password for this user.
Now ! Your Database has been upgrated.
Dear Readers, If you want to get Hands - On Experiance on Oracle Database Migration on Windows/Solaris/Linux plateform . Shoot me mail at anup@indiandba.com.
