Re: migration from oracle 8i to 9i
Date: 4 Dec 2003 12:54:21 -0800
Message-ID: <7989e439.0312041254.41dc712c_at_posting.google.com>
susmita_ganguly_at_yahoo.com wrote in message news:<f79db97d.0311211202.2419ed69_at_posting.google.com>...
Do not recommend the dbua if your DB is not the default sample
database, I think it is a garbage of Oracle. Please refer to the
manual upgrade guide:
Complete upgrade checklist for manual upgrades from 8.X to Oracle9i
(incl. 9.2)
PURPOSE
This document is created for use as a guideline and checklist when
manually upgrading Oracle 8 or Oracle 8i to Oracle 9i.
SCOPE & APPLICATION
Database administrators
UPGRADE CHECKLIST
UPGRADING THE DATABASE
If your old release version is 8.0.5 or less (8.0.4 or 8.0.3), then
direct
If your old release is 8.0.5 or less, look at not 133920.1 for manual
upgrading
What version is running? What option is installed?
Select * from v$version;
PERFORM a Full cold backup!!!!!!!
Avoid running out of space during the migration:
If you run out of space in one of these tablespaces during the
upgrade, you will
Verify the certification of oracle 9i on the OS version you are using.
Verify all necessary OS patches are installed.
Example for Solaris:
Upgrade will leave all objects (packages,views,...) invalid, except
for tables.
List all objects that are not VALID before the upgrade.
This list of fatal objects.
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';
To create a script to compile all invalid objects, before upgrading,
run the
$ cd $ORACLE_HOME/rdbms/admin
Run the script and than rerun the query to get invalid objects.
spool invalid_pre.lst
This last query will return a list of all objects that cannot be
recompiled
Verify the kernel parameters according to the installation guide of
the
Ensure ORACLE_SID is set to instance you want to upgrade.
Echo $ORACLE_SID
As of Oracle 9i the sql NCHAR datatypes will be limited to the Unicode
character
If you are upgrading from the 8.0.6 release check no users or roles
are called
Select from * dba_users where username in ('MIGRATE','OUTLN');
Select from * dba_roles where role in ('MIGRATE','OUTLN');
Check for corruption in the dictionary, use:
Set verify off
This script (analyze.sql) should not return any errors.
Ensure that all Snapshot refreshes are successfully completed.
And replication is stopped.
Stop the listener for the database
Ensure no files need media recovery:
This should return no rows
Ensure no files are in backup mode:
This should return no rows.
Resolve any outstanding unresolved distributed transaction:
Select * from dba_2pc_pending;
If this returns rows you should do the following:
Select local_tran_id from dba_2pc_pending;
Execute dbms_transaction.purge_lost_db_entry('<LOCAL_TRAN_ID>');
Commit;
Disable all batch and cron jobs.
Ensure the users sys and system have 'system' as their default
tablespace.
Select username, default_tablespace from dba_users where username
in ('SYS','SYSTEM');
To modify use:
Optionally ensure the aud$ is in the system tablespace when auditing
is enabled.
Note down where all control files are located.
Select * from v$controlfile;
Note down all sysdba users.
If a passwordfile is used copy it to the new location. On unix the
default
On windows NT this is %ORACLE_HOME%\database\orapw<SID>
Shutdown the database
Change the init.ora file:
Check for adequate freespace on archive log destination file systems.
Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.
For Oracle 8.0 this is:
For Oracle 8i or higher this is:
And create the new Oracle 9i service use ORADIM of the 9i ORACLE_HOME:
C:\ORADIM -NEW -SID <SID> -INTPWD <internalpwd> -MAXUSERS n
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\init<SID>.ora
If needed copy the init.ora file to the new oracle_home or
Create a link to the init.ora.
Update the environment variables like ORACLE_HOME and PATH
$ . oraenv
Make sure the following enviroment variables point to the new
Release directories:
HP-UX:
Run the upgrade script:
Use Startup RESTRICT when you are upgrading to Oracle 9.0.1:
SQL> Startup restrict
Use Startup MIGRATE when you are upgrading to Oracle 9.2:
SQL> Startup Migrate
Spool the output so you can take a look at possible errors after the
upgrade:
Run the appropriate script for your version.
From Only Script to Run
Each of these scripts is a direct upgrade path from the version you
are
Display the contents of the component registry to determine which
components
Run the script cmpdbmig.sql to upgrade the components which can be
upgrade
SQL> _at_cmpdbmig.sql
The components upgraded by this script are:
Jserver JAVAVM, oracle XDK for Java, Oracle 9i RAC, Oracle Data
Mining,
Display the components which were upgraded:
SQL> Select comp_name, version, status from dba_registry;
End the spool of the upgrade:
Restart the database:
Executing this clean shutdown flushes all caches, clears buffers and
performs
Run script to recompile invalid pl/sql modules:
SQL> _at_utlrp
If there are still objects which are not valid after running the
script run
Now compare the invalid objects in the file 'invalid_post.lst' with
the invalid
Edit init.ora file:
Shutdown the database and startup the database.
$ sqlplus /nolog
Upgrade user tables with NCHAR datatype columns:
$ sqlplus /nolog
Now edit the init.ora:
Create a server parameter file with a initialization parameter file
SQL> Create spfile from pfile;
This will create a spfile as a copy of the init.ora file located in
the
Modify the listener.ora file:
Start the listener
Enable cron and batch jobs
Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y
To use the new features in 9i change the compatible parameter to the
new release.
UPGRADING THE JAVA ENGINE(Only for upgrades to 9.0.1)
If this is >0 then java has been installed. If this value =0 java is
not
Restart the database:
Run the appropriate script depending on what release you are coming
from:
From Only Script to Run
After you have run this script, all user classes are invalid. These
become
SQL> Alter Java Class <ClassName> resolve;
Restart the database:
UPGRADING INTERMEDIA TEXT(Only for upgrades towards 9.0.1)
Restart the database:
If you have Oracle Image Cartridge release 8.0.n, or Oracle interMedia
release 8.1.n installed,run the script imdbma to see whether or not
you need to upgrade:
> Hi
>
> I am trying to upgrade from oracle 8i to oracle 9i on the same server
> .I don't know much abt migration .
>
> Can anyone help me out.
>
> Thanks.
> Susmita
I post here because I can not open the original web
site(http://www.tilsor.com.uy/soporte/Documentos_Tecnicos/9.htm).
upgrade is NOT supported. You must first upgrade this version to
8.0.6. After
the upgrade to 8.0.6 or your version IS 8.0.6 or higher, you can
directly
upgrade your database to 9i.
the database to 8.0.6 or higher.
Select * from v$option;
2. -----------------------------------------------------------------------------
3. -----------------------------------------------------------------------------
need to perform the upgrade again.
4. -----------------------------------------------------------------------------
$ showrev -p
5. ------------------------------------------------------------------------------
All other objects must be recompiled manually.
the script called utlrp.sql in the $ORACLE_HOME/rdbms/admin directory.
This
script recompiles all invalid PL/SQL in the database including views.
$ sqlplus sys/<password for SYS> as sysdba
SQL> _at_utlrp.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';
spool off
before the upgrade in the file 'invalid_pre.lst'
6. -----------------------------------------------------------------------------
new version.
Example for Solaris:
$ cat /etc/system
7. ------------------------------------------------------------------------------
Echo $ORACLE_HOME
8. -----------------------------------------------------------------------------
set encoding only (UTF8 and AL16UTF16). Any other NCHAR datatype will
no longer
be supported. When upgrading to 9i the value of the NCHAR is based on
the NCHAR
datatype used in the Oracle8 version. If the old National character
set is UTF8,
the new will be UTF8. Otherwise the national character set is changed
to
AL16UTF16.
Verify character set of the database:
$ Sqlplus SYS/<password for SYS>
Select name, substrb(value$,1,40) value from props$;
9. -----------------------------------------------------------------------------
either MIGRATE or OUTLN.
Set space 0
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
Select 'Analyze '||object_type||' '||object_name
||' validate structure;'
from dba_objects
where owner='SYS'
and object_type in ('INDEX','TABLE','CLUSTER');
spool off
This creates a script called analyze.sql.
Run the script.
$ Sqlplus SYS/<password for SYS>
Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
$ lsnrctl
Lsnrctl> stop <listenername>
$ sqlplus SYS/<password for SYS>
Select * from v$recover_file;
Select * from v$backup where status!='NOT ACTIVE';
Alter user sys default tablespace SYSTEM;
Alter user system default tablespace SYSTEM;
Select tablespace_name from dba_tables where table_name='AUD$';
20. ----------------------------------------------------------------------------
Select * from v$pwfile_users;
is $ORACLE_HOME/dbs/orapw<SID>.
21. ----------------------------------------------------------------------------
$ sqlplus SYS/<password for SYS>
SQL> Shutdown immediate
22. ----------------------------------------------------------------------------
- Make a backup of the init.ora file.
24. ----------------------------------------------------------------------------
25. ----------------------------------------------------------------------------
If needed copy the listener.ora and the tnsnames.ora to the new
location
(when no TNS_ADMIN env. Parameter is used)
cp $ORACLE_HOME/network/admin <NEW_ORACLE_HOME>/network/admin
26. ----------------------------------------------------------------------------
C:\ORADIM80 -DELETE -SID <SID>
C:\ORADIM -DELETE -SID <SID>
27. ----------------------------------------------------------------------------
cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora
OR
Ln ?s /init/ora/file/path/init.ora $ORACLE_HOME/dbs/init.ora
Also check 'ifile' parameters in the init.ora, to be set to the
correct file.
if an IFILE is used, verify the above mentioned parameter for the
init.ora
and copy this to the correct location. Change the IFILE entry in the
init.ora
file when this file changes from location.
28. ----------------------------------------------------------------------------
Update the oratab entry, to set the new ORACLE_HOME and disable
automatic
startup:
<SID>:<new ORACLE_HOME>:N
29. ----------------------------------------------------------------------------
30. ----------------------------------------------------------------------------
- ORACLE_HOME
For HP-UX systems verify the SHLIB_PATH parameter points to the new
release
directories.
$ env | grep ORACLE_HOME
$ env | grep PATH
$ env | grep ORA_NLS33
$ env | grep ORACLE_BASE
$ env | grep LD_LIBRARY_PATH
$ env | grep ORACLE_PATH
$ env | grep SHLIB_PATH
31. ----------------------------------------------------------------------------
$ cd $ORACLE_HOME/rdbms/admin
Sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL> Spool Upgrade.log
==== ==================
8.0.6 u0800060.sql
8.1.7 u0801070.sql
9.0.1 u0900010.sql
on to 9i. You do not need to run catalog.sql and catproc.sql as these
two scripts are called from within the upgrade script.
need to be upgraded:
SQL> Select comp_name, version, status from dba_registry;
with the SYSDBA privilege (This step is only valid for upgrades
towards 9.2):
OLAP analytical Workspace, Oracle 9i Java Packages, Messaging Gateway,
Oracle Workspace Manager, OLAP Catalog, Oracle Label Security.
SQL> Spool Off
32. ----------------------------------------------------------------------------
SQL> Shutdown Immediate (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SQL> Startup restrict
other database housekeeping tasks. Which is needed if you want to
upgrade
specific components.
33. ----------------------------------------------------------------------------
the following:
spool invalid_post.lst
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';
spool off
objects in the file 'invalid_pre.lst' you create in step 5.
34. ----------------------------------------------------------------------------
- Leave the parameter JOB_QUEUE_PROCESSES=0
SQL> Connect sys/passwd_for_sys as sysdba
SQL> Shutdown
SQL> Startup restrict
36. ----------------------------------------------------------------------------
SQL> connect sys/passwd_for_sys as sysdba
SQL> _at_utlnchar.sql
SQL> _at_n_switch.sql
SQL> shutdown immediate
37. ----------------------------------------------------------------------------
- put back the old value for the JOB_QUEUE_PROCESSES parameter
$ORACLE_HOME/dbs directory.
39. ----------------------------------------------------------------------------
For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.
40. ----------------------------------------------------------------------------
$ lsnrctl
LSNRCTL> start <listenername>
41. ----------------------------------------------------------------------------
42. ----------------------------------------------------------------------------
43. ----------------------------------------------------------------------------
When everything is well tested, update the compatible parameter in the
init.ora
and restart to the new release number.
COMPATIBLE=9.0.X where x is the release number
installed and there is no reason to upgrade the java engine.
2. -----------------------------------------------------------------------------
$ Sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> Shutdown immediate
SQL> startup restrict
3. -----------------------------------------------------------------------------
$ cd $ORACLE_HOME/javavm/install
Sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL> Spool catoutjava.log
==== ==================
8.1.5 jvmu815.sql
8.1.6 jvmu816.sql
8.1.7 jvmu817.sql
implicitly valid when you executed them. You can explicitly revalidate
them
when executing the following command:
4. -----------------------------------------------------------------------------
$ Sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> Shutdown immediate
SQL> startup restrict
----------------------------------------------------------------------------
$ Sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> Shutdown immediate
SQL> startup restrict
2. ----------------------------------------------------------------------------------------
$ cd $ORACLE_HOME/ord/im/admin
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as SYSDBA
This script displays one of the following strings:
NOT_INSTALLED - if no prior release of interMedia components
were installed on your system. You must install
interMedia, rather than an upgrade.
INSTALLED - if interMedia release 9.0.1 is already installed
u080nnn0.sql - the script that performs the upgrade. nnn
is the release of interMedia or Image Cartridge
that is currently installed. For example,
u0800050.sql upgrades from Image Cartridge
release 8.0.5.0.0. Note: imdbma cannot
distinguish between 8.0 releases of Image
Cartridge. If you have an 8.0 release installed,
the 8.0.5 script is always chosen.
3. ----------------------------------------------------------------------------------------
If an upgrade is required, invoke imuphelp to determine if your system
is ready for an interMedia upgrade.
$sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> _at_imuphelp.sql
This script tells you:
- if an earlier release of Audio must be deinstalled before upgrading
- if an earlier release of Video must be deinstalled before upgrading Correct any problems found by imuphelp.sql before proceeding. 4. ----------------------------------------------------------------------------------------
Upgrade Oracle interMedia Common Files.
$ cd $ORACLE_HOME/ord/admin
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
Run the appropriate script for the version you are coming from:
From Only Script to Run
==== ================== 8.0.3 u0800030.sql 8.0.4 u0800040.sql 8.0.5 u0800050.sql 8.0.6 u0800060.sql 8.1.5 u0801050.sql
8.1.6 u0801060.sql
8.1.7 u0801070.sql
Enter the password for ORDSYS when the script asks for it.
5. ----------------------------------------------------------------------------------------
Upgrade interMedia.
$ cd $ORACLE_HOME/ord/im/admin
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
Run the appropriate script for the version you are coming from:
From Only Script to Run
==== ================== 8.0.3 u0800030.sql 8.0.4 u0800040.sql 8.0.5 u0800050.sql 8.0.6 u0800060.sql 8.1.5 u0801050.sql
8.1.6 u0801060.sql
8.1.7 u0801070.sql
Enter the password for ORDPLUGINS when the script asks for it and enter the password for ORDSYS when the script asks for it.
5. ----------------------------------------------------------------------------------------
Run the script imchk.sql to verify the upgrade connected as ORDSYS:
$ cd $ORACLE_HOME/ord/im/admin
$ Sqlplus ORDSYS/passwd_for_ordsys
SQL> _at_imchk.sql
6. ----------------------------------------------------------------------------------------
Restart the database:
$ Sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba SQL> Shutdown immediate SQL> startup restrict ---------------------------------------------------------------------------------------
Appendix A: Obsolete parameters in 9i:
_average_dirties_half_life
_lm_statistics
allow_partial_sn_results
always_anti_join
always_semi_join
arch_io_slaves
b_tree_bitmap_plans
backup_disk_io_slaves
cache_size_threshold
cleanup_rollback_entries
close_cached_open_cursors
compatible_no_recovery
complex_view_merging
cpu_count
db_block_checkpoint_batch db_block_lru_extended_statisti db_block_lru_latches db_block_lru_statistics db_block_max_dirty_target db_file_simultaneous_writes
delayed_logging_block_cleanout
discrete_transactions_enabled
distributed_lock_timeout
distributed_recovery_connectio
fast_full_scan_enabled
freeze_DB_for_fast_instance_re
gc_defer_time gc_latches gc_lck_procs gc_releasable_locks gc_rollback_locks
hash_multiblock_io_count
instance_nodeset
job_queue_interval
job_queue_keep_connections
large_pool_min_alloc
lgwr_io_slaves
lm_locks lm_procs lm_procs lm_ress
lock_sga_areas
log_block_checksum log_files log_simultaneous_copies log_small_entry_max_size
ogms_home
ops_admin_group
ops_interconnects
optimizer_percent_parallel
optimizer_search_limit
parallel_default_max_instances parallel_min_message_pool parallel_server_idle_time parallel_transaction_resource_
push_join_predicate
row_cache_cursors
sequence_cache_entries
sequence_cache_hash_buckets
shared_pool_reserved_min_alloc
snapshot_refresh_interval snapshot_refresh_keep_connecti snapshot_refresh_processes sort_direct_writes sort_multiblock_read_count sort_read_fac sort_spacemap_size sort_write_buffer_size
sort_write_buffers
spin_count
temporary_table_locks
text_enable
use_ism Received on Thu Dec 04 2003 - 21:54:21 CET