How to migrate to 9i across machines having same OS (without using export import)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

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

Hybrid View

  1. #1
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    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...
    Last edited by adewri; 03-07-2003 at 03:25 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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
    Last edited by adewri; 02-28-2003 at 10:51 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Dec 2006
    Posts
    1

    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Assuming your using temporary tablespaces instead of permanent tablespaces for your TEMP, you don't need to "migrate" anything, just recreate it.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width