Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Is is alos possible to do a full database import from Oracle 10g to 11g, but this is not recommended, since SYS objects are often also imported accidentially.

Create new target database

Install Software

Install new 11g database software home and install a new Custom database using custom scripts or DBCA.

Create tablespaces in target database

Create Tablepspaces as in source database.
See Generate Create Tablespace DDL.
See Free.sql also to control available space.

Create Data Pump directory

Panel

SQL> select 'CREATE DIRECTORY ' || directory_name || ' as || directory_path ||*;'
from dba_directories
where user = 'SYS'
and directory_name not in ('DATA_PUMP_DIR', 'ORACLE_OCM_CONFIG_DIR');

Compare database parameters

Generate a pfile from both databases and compare.

Panel

SQL> create pfile='/tmp/db1.ora' from spfile;
SQL> create pfile='/tmp/db2.ora' from spfile;
$ diff /tmp/db1.ora /tmp/db2.ora

Especially investigate on parameters

  • All "_size" - Parameters
  • cursor_sharing
  • db_domain
  • db_files
  • dbwr_io_slaves
  • dml_locks
  • fast_start_mttr_target
  • fast_start_parallel_rollback

Global Names has consequences on the way database links are resolved.
If you get ORA-02085 after migration, try resetting this parameter.

  • instance_name
  • job_queue_processes
  • log_buffer
  • log_checkpoint_interval
  • max_dump_file_size
  • nls_date_format
  • nls_length_semantics
  • open_cursors
  • open_links
  • optimizer_index_caching
  • optimizer_index_cost_adj
  • os_authent_prefix
  • pga_aggregate_target
  • pre_page_sga
  • processes
  • query_rewrite_enabled
  • session_max_open_files
  • sga_target
  • star_transformation_enabled
  • tape_asynch_io
  • timed_statistics
  • undo_retention
  • undo_tablespace

In many cases, these parameters have to be reset:

Panel

SQL> alter system reset control_file_record_keep_time scope=spfile;
SQL> alter system reset cpu_count scope=spfile;
SQL> alter system reset global_names scope=spfile;
SQL> alter system reset O7_DICTIONARY_ACCESSIBILITY scope=spfile;
SQL> alter system reset remote_os_authent scope=spfile

Export data

Create Export Directory

Panel

SQL> create directory OPS_MIG as '/tank/spool/opsmis';

Create Data Pump Parameter File

Panel

$ cat ops.par
full=y
parallel=1
dumpfile=<sid>_migration.dmp
directory=OPS_MIG
logfile=<sid>_migration.log
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'),'YYYYMMDD HH24MISS')"

Restart Source database

Restart Sorce database in Restricted Mode to prevent users from logging in if schedules and possible.

...

Panel

$ srvctl stop database -d OPS
$ sqlpus "/ AS SYSDBA"
SQL> startup restrict

Export & Transport

Start Export

Panel

$ expdp \"/ AS SYSDBA\" parfile=ops.par

You can schedule this with "at", but take care of the environment.

Move Dump File

Move the dump file to the new system, using scp or ftp or cp.

Import

Import Data

Panel

$ vi ops-imp.par
parallel=1
full=y
#schemas=<user>
dumpfile=<sid>_migration.dmp
directory=OPS_MIG
logfile=<sid>_migration.log
$ impdp \"/ AS SYSDBA\" parfile=ops-imp.par

...