Donald K. Burleson Oracle Tips

A foolproof Oracle8 migration check script

As you know, there are quite a few steps in the Oracle8 migration utility guide and many things that must be checked before migrating from Oracle7 to Oracle8.  Here is the script that I use to ensure that my Oracle 7.3 database is ready for migration.

There are quite a few initialization parameters that have changed in Oracle8, and this handy script below identifies any init.ora parameters that need to be changed.

set pages 999;

Spool check_mig.lst

-- Written by Don Burleson

Prompt This section checks for obsolete init.ora parameters

Prompt - Remove the following entries from your init.ora file . .

Select

   name,

   value

from

   v$parameter

where

 name IN (

'checkpoint_process',

'fast_cache_flush',

'gc_db_locks',

'gc_freelist_groups',

'gc_save_rollback_locks',

'gc_rollback_segments',

'gc_segments',

'gc_tablespaces',

'io_timeout',

'init_sql_files',

'ipq_address',

'ipq_net',

'lm_domains',

'lm_non_fault_tolerant',

'mls_label_format',

'optimizer_parallel_pass',

'parallel_default_max_scans',

'parallel_default_scan_size',

'post_wait_device',

'sequence_cache_hash_buckets',

'unlimited_rollback_segments',

'use_ipq',

'use_readv',

'use_post_wait_driver',

'v733_plans_enabled',

'use_sigio',

'allow_partial_sn_results',

'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',

'db_block_checkpoint_batch',

'db_block_lru_extended_statistics',

'db_block_lru_statistics',

'db_file_simultaneous_writes',

'delayed_logging_block_cleanouts',

'discrete_transactions_enabled',

'distributed_lock_timeout',

'distributed_recovery_connection_hold_time',

'fast_full_scan_enabled',

'freeze_db_for_fast_instance_recovery',

'gc_latches',

'gc_lck_procs',

'job_queue_keep_connections',

'large_pool_min_alloc',

'lgwr_io_slaves',

'lock_sga_areas',

'log_archive_buffer_size',

'log_archive_buffers',

'log_block_checksum',

'log_files',

'log_simultaneous_copies',

'log_small_entry_max_size',

'max_transaction_branches',

'mts_listener_address',

'mts_multiple_listeners',

'mts_rate_log_size',

'mts_rate_scale',

'mts_service',

'ogms_home',

'ops_admin_group',

'parallel_default_max_instances',

'parallel_min_message_pool',

'parallel_server_idle_time',

'parallel_transaction_resource_timeout',

'push_join_predicate',

'reduce_alarm',

'row_cache_cursors',

'sequence_cache_entries',

'sequence_cache_hash_buckets',

'shared_pool_reserved_min_alloc',

'snapshot_refresh_keep_connections',

'snapshot_refresh_processes',

'sort_direct_writes',

'sort_read_fac',

'sort_spacemap_size',

'sort_write_buffer_size',

'sort_write_buffers',

'spin_count',

'temporary_table_locks',

'text_enable',

'use_ism'

)

and

   isdefault = 'FALSE'

;

Also, a part of Oracle8 migration requires that you have no database objects called “migrate”.  In addition, there are a host of new reserved names that cannot be used inside the Oracle database.  The following script will identify any objects in your Oracle7 database that violate the Oracle8 standards.

-- Preparation for Oracle8i migration testing script

-- 2/1/00 by Donald Keith Burleson

Prompt Make sure you do not have a user named migrate

select * from dba_users where username = 'MIGRATE';

Prompt Make sure you do not have a role named migrate

select * from dba_roles where role = 'MIGRATE';

Prompt Check for reserved words in object names . . .

-- You should get "no rows selected"

Select

   object_name

from

   dba_objects

where

object_name in (

'ACCOUNT',

'NCLOB',

'ARRAY',

'NESTED',

'BFILE',

'NOLOGGING',

'BLOB',

'NOPARALLEL',

'CST',

'NOREVERSE',

'CFILE',

'NORMAL',

'CHAR_CS',

'NVARCHAR2',

'CHUNK',

'OBJECT',

'CLOB',

'OBJNO_REUSE',

'CLONE',

'OID',

'DANGLING',

'OIDINDEX',

'DATAOBJNO',

'ORGANIZATION',

'DEFERRABLE',

'OVERFLOW',

'DEFERRED',

'PASSWORD',

'DEREF',

'PCTTHRESHOLD',

'DIRECTORY',

'PCTVERSION',

'ENFORCE',

'PRESERVE',

'EXCHANGE',

'PURGE',

'EXPIRE',

'QUEUE',

'EXTENT',

'REF',

'FLOB',

'REPLACE',

'GLOBALLY',

'RETURN',

'HASH',

'RETURNING',

'HASHKEYS',

'REVERSE',

'HEAP',

'SCOPE',

'IDGENERATORS',

'SEG_BLOCK',

'INITIALLY',

'SEG_FILE',

'LIBRARY',

'SKIP',

'LOCKED',

'SYS_OP_NTCIMG',

'LOGGING',

'THAN',

'LOGICAL_READS_PER_CALL',

'THE',

'LOGICAL_READS_PER_SESSION',

'TOPLEVEL',

'MASTER',

'UNLOCK',

'NATIONAL',

'USAGE',

'NCHAR',

'VALUE',

'NCHAR_CS',

'VARYING'

);

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm