Consider the following situation
Node A - IBM AIX 4.3.3 RS6000, Oracle 8i Enterprise Edition.
-- Has a 8i database.
Node B - IBM AIX 4.3.3 RS6000 service level 10, Oracle 9i Enterprise Edition.
-- No current database.
Now what we need is to migrate the 8i database to 9i.
1. Create the control file creation scripts.
refer to
http://www.dbasupport.com/forums/sho...threadid=34259
2. Shutdown the database on node A.
Shutdown should be done normal or immediate.(V. Imp)
3. Copy or FTP the datafiles, initparameter file to NODE B.
Now we need to have an init parameter file for 9i
4. Copy the 8i init parameter file. Remove the obsolete values from the parameter file.
The following code gives the list of eliminated parameters in 9i and those which have been hidded by _underscore. For further reference RTFM or metalink.oracle.com
Code:
SQL> SELECT kspponm NAME
2 ,DECODE (ksppoflg
3 ,1, 'Eliminated'
4 ,'Underscore'
5 ) setting
6 FROM x$ksppo
7 ORDER BY setting
8 ,NAME
9 /
NAME SETTING
----------------------------------------- ---------------
_average_dirties_half_life Eliminated
_db_no_mount_lock Eliminated
_lm_multiple_receivers Eliminated
_lm_statistics Eliminated
_seq_process_cache_const Eliminated
allow_partial_sn_results Eliminated
cache_size_threshold Eliminated
db_block_checkpoint_batch Eliminated
db_block_lru_extended_statistics Eliminated
db_block_lru_statistics Eliminated
db_file_simultaneous_writes Eliminated
delayed_logging_block_cleanouts Eliminated
distributed_transactions Eliminated
freeze_DB_for_fast_instance_recovery Eliminated
gc_lck_procs Eliminated
gc_releasable_locks Eliminated
gc_rollback_locks Eliminated
instance_nodeset Eliminated
job_queue_interval Eliminated
job_queue_keep_connections Eliminated
log_block_checksum Eliminated
log_files Eliminated
log_small_entry_max_size Eliminated
max_transaction_branches Eliminated
ops_admin_group Eliminated
ops_interconnects Eliminated
parallel_transaction_resource_timeout Eliminated
sequence_cache_entries Eliminated
sequence_cache_hash_buckets Eliminated
snapshot_refresh_interval Eliminated
snapshot_refresh_keep_connections Eliminated
snapshot_refresh_processes Eliminated
sort_direct_writes Eliminated
sort_read_fac Eliminated
sort_spacemap_size Eliminated
sort_write_buffer_size Eliminated
sort_write_buffers Eliminated
standby_preserves_names Eliminated
temporary_table_locks Eliminated
text_enable Eliminated
always_anti_join Underscore
always_semi_join Underscore
arch_io_slaves Underscore
b_tree_bitmap_plans Underscore
backup_disk_io_slaves Underscore
cleanup_rollback_entries Underscore
close_cached_open_cursors Underscore
compatible_no_recovery Underscore
complex_view_merging Underscore
cpu_count Underscore
db_block_lru_latches Underscore
db_block_max_dirty_target Underscore
discrete_transactions_enabled Underscore
distributed_recovery_connection_hold_time Underscore
fast_full_scan_enabled Underscore
gc_defer_time Underscore
gc_latches Underscore
hash_multiblock_io_count Underscore
large_pool_min_alloc Underscore
lgwr_io_slaves Underscore
lm_locks Underscore
lm_procs Underscore
lm_procs Underscore
lm_ress Underscore
lock_sga_areas Underscore
log_simultaneous_copies Underscore
ogms_home Underscore
optimizer_percent_parallel Underscore
optimizer_search_limit Underscore
parallel_broadcast_enabled Underscore
parallel_default_max_instances Underscore
parallel_min_message_pool Underscore
parallel_server_idle_time Underscore
push_join_predicate Underscore
row_cache_cursors Underscore
shared_pool_reserved_min_alloc Underscore
sort_multiblock_read_count Underscore
spin_count Underscore
use_ism Underscore
79 rows selected.
SQL>
5. After changing the init parameter file for the obsolete values, edit the control_files parameter
Code:
control_files=("e:\oracle\oradata\ORCL\control01.ctl", "e:\oracle\oradata\ORCL\control02.ctl", "e:\oracle\oradata\ORCL\control03.ctl")
Note: the control file is to be recreated. Here we are putting the path and the name of the control files.
6. Edit the controlfile create script to point the datafiles to the right path.
Code:
CREATE CONTROLFILE SET DATABASE "F51SYS" RESETLOGS NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 (
'/f5oradata1/oradata/F51SYS/redo01.log',
'/f5oradata3/oradata/F51SYS/redo07.log'
) SIZE 50M,
GROUP 2 (
'/f5oradata1/oradata/F51SYS/redo02.log',
'/f5oradata3/oradata/F51SYS/redo05.log'
) SIZE 50M,
GROUP 3 (
'/f5oradata1/oradata/F51SYS/redo03.log',
'/f5oradata3/oradata/F51SYS/redo08.log'
) SIZE 50M
DATAFILE
'/f5oradata1/oradata/F51SYS/system01.dbf',
'/f5oradata1/oradata/F51SYS/undotbs01.dbf',
'/f5oradata1/oradata/F51SYS/indx01.dbf',
'/f5oradata1/oradata/F51SYS/tools01.dbf',
'/f5oradata1/oradata/F51SYS/users01.dbf',
'/f5oradata1/oradata/F51SYS/i_eqm_m_01.dbf',
'/f5oradata2/oradata/F51SYS/t_gpm_l_02.dbf',
'/f5oradata1/oradata/F51SYS/i_gpm_l_01.dbf',
'/f5oradata2/oradata/F51SYS/i_gpm_l_02.dbf',
'/f5oradata1/oradata/F51SYS/t_gpm_m_01.dbf',
'/f5oradata1/oradata/F51SYS/i_gpm_m_01.dbf',
'/oradata2/oradata/F51SYS/t_rcm_m_01.dbf',
'/oradata2/oradata/F51SYS/i_rcm_m_01.dbf',
'/oradata2/oradata/F51SYS/t_rcm_l_01.dbf',
'/oradata2/oradata/F51SYS/i_rcm_l_01.dbf',
'/oradata2/oradata/F51SYS/t_rdm_l_01.dbf',
'/f5oradata3/oradata/F51SYS/i_gpm_l_03.dbf',
'/f5oradata3/oradata/F51SYS/i_acd_l_02.dbf',
'/f5oradata3/oradata/F51SYS/t_sem_l_01.dbf',
'/f5oradata4/oradata/F51SYS/i_sem_m_03.dbf',
'/f5oradata3/oradata/F51SYS/t_gdr_l_01.dbf',
'/f5oradata3/oradata/F51SYS/T_GPM_LG.dbf'
CHARACTER SET US7ASCII
;
Save it as ctl.sql
7. Startup the database in nomount mode and run the ctl.sql
Code:
SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 20:21:21 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 303790504 bytes
Fixed Size 739752 bytes
Variable Size 268435456 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes
SQL>@ctl.sql
Control file created.
SQL>
8. Now when we open the database with alter database open resetlogs it will give an error and close. Just ignore it. Don't worry, your redologs would be create. Just that the opening part gives error as for upgradataion the database need to be opened as startup migrate in 9i.
contd...