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
5. After changing the init parameter file for the obsolete values, edit the control_files parameterCode: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>
Note: the control file is to be recreated. Here we are putting the path and the name of the control files.Code:control_files=("e:\oracle\oradata\ORCL\control01.ctl", "e:\oracle\oradata\ORCL\control02.ctl", "e:\oracle\oradata\ORCL\control03.ctl")
6. Edit the controlfile create script to point the datafiles to the right path.
Save it as ctl.sqlCode: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 ;
7. Startup the database in nomount mode and run the ctl.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.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>
contd...
Reply With Quote
Bookmarks