How to migrate to 9i across machines having same OS (without using export import)

How to migrate to 9i across machines having same OS

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...

Contd.. from above

9. Startup the database again with startup migrate option.
In this mode the database is in restricted mode.

Code:

SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 20:07:26 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 migrate 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 Database mounted. Database opened. SQL> select status from v$instance; STATUS ------------------------------------ OPEN MIGRATE SQL>

10. Add tempfiles to your temporary tablespaces.

Code:

SQL> ALTER TABLESPACE TEMP ADD 2 TEMPFILE '/f5oradata1/oradata/F51SYS/temp01.dbf' SIZE 52428800 3 REUSE AUTOEXTEND OFF; Tablespace altered. SQL> ALTER TABLESPACE TEMP_SPC ADD 2 TEMPFILE '/oradata3/oradata/F51SYS/temp_spc_03.dbf' 3 SIZE 52428800 REUSE AUTOEXTEND OFF; Tablespace altered. SQL> ALTER TABLESPACE TEMP_SPC ADD 2 TEMPFILE '/f5oradata1/oradata/F51SYS/temp_spc02.dbf' 3 SIZE 20971520 REUSE AUTOEXTEND OFF; Tablespace altered. SQL> ALTER TABLESPACE TEMP_SPC ADD 2 TEMPFILE '/f5oradata1/oradata/F51SYS/temp_spc_01.dbf' 3 SIZE 20971520 REUSE AUTOEXTEND OFF; Tablespace altered.

11. Add ample space to your system tablespace by resizing or adding datafiles.Should have around 200-300MB of freespace.

12. upgrade by running the scripts

Code:

SQL> spool upgrade.log SQL> @?/rdbms/admin/u0801070.sql

Other scripts are
Orcale 7 u0703040.sql
Oracle 8 u0800060.sql
Oracle 8i u0801070.sql
You can find scripts corresponding to your database in $ORACLE_HOME/admin/rdbms. Here since we are upgrading from 8.1.7 hence we are using u0801070.sql

13. Check the upgrade.log file for errors

Post upgradataion tasks....(Optional)
a. Create undo tablespaces.
b. Change Dictionary Managed Tablespaces to LMT
c. Enable Archiving.

Other way of upgradation is export/import

NOTE: The above method of upgradataion is not mentioned in the ORACLE's Upgradation manual. The upgradation method is of my own experience so proceed with care and please don't mess up your database. I have used this method to upgrade all my 8 databases and are running without hitch for past 10 months.

Recommendations: Try on test databases first.

HTH

Regards

How to migrate 9i to 10g

How to manage the temp tablespace for the database which size is 60GB and migrated from
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

To 10g release 2

Assuming your using temporary tablespaces instead of permanent tablespaces for your TEMP, you don't need to "migrate" anything, just recreate it.