Oracle Data Migration with expdp and impdp

-

Oracle Data Migration with expdp and impdp

For Oracle DBA data migration using Export Import is common activity in day to day activity. Either you need to migrate data from Different version of oracle or between two different platform like windows server o linux platform. Export import is used mostly but require complete downtime for the database migration. In this blog ,We will share the steps to migrate the database from one database to another database using export and import. 

Below steps can be used for the same:

1) Create an empty database in target server and make sure database is in noarchive log mode. 

Make sure that the parameter for the source and target databases are same. 

Make list of application tablespace from source database to target database. You can get the DDL for all tablespaces in source databases using below command:

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
FROM   dba_tablespaces
WHERE  tablespace_name = DECODE(UPPER('&1'), 'ALL', tablespace_name, UPPER('&1'));
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

2) On Source database , Stop the application and stop listener for the database to be migrated. 
3) Create a copy of performance specific table. (In order to compare the perform statistics post migration in case of any performance statistics)

set timing on
set echo on
spool performance_migration_obj_creation.log
create table miguser.dba_users as select * from dba_users order by 1 asc;
create table miguser.dba_tablespaces as select * from dba_tablespaces order by 1 asc;
create table miguser.dba_objects as select * from dba_objects order by 1 asc;
create table miguser.OPTSTAT_TAB_HISTORY as select * from WRI$_OPTSTAT_TAB_HISTORY ;
create table miguser.dba_tables as select * from dba_tables;
create table miguser.dba_objects as select * from dba_objects;
create table miguser.dba_indexes as select * from dba_indexes;
create table miguser.dba_synonyms as select * from dba_synonyms;
create table miguser.dba_ext_tables as select * from dba_external_tables;
create table miguser.dba_directories as select * from dba_directories;
create table miguser.dba_sys_privs as select * from dba_sys_privs;
create table miguser.dba_role_privs as select * from dba_role_privs;
create table miguser.dba_tab_privs as select * from dba_tab_privs;
create table miguser.dba_profiles as select * from dba_profiles;
create table miguser.ROLE_TAB_PRIVS as select * from ROLE_TAB_PRIVS;
create table miguser.role_sys_privs as select * from role_sys_privs;
create table miguser.dba_roles as select * from dba_roles;
create table miguser.dba_constraints as select * from dba_constraints;
create table miguser.dba_context as select * from dba_context;
create table miguser.dba_source as select * from dba_source;
create table miguser.dba_triggers as select  * from dba_triggers;
create table miguser.dba_ind_columns as select * from dba_ind_columns;
create table miguser.dba_db_links as select * from dba_db_links;
create table miguser.dba_tab_statistics as select * from dba_tab_statistics;
create table miguser.v_$parameter as select * from v_$parameter;
create table miguser.dba_sequences as select * from dba_sequences;
create table miguser.DBA_CONS_COLUMNS as select * from DBA_CONS_COLUMNS;
create table miguser.dba_segments  as select * from dba_segments;|
create table miguser.dba_procedures as select * from dba_procedures;
create table miguser.dba_lobs   as select * from dba_lobs;
create table miguser.dba_views as select * from dba_views;
create table miguser.role_role_privs as select * from ROLE_ROLE_PRIVS;
create table miguser.dba_tab_statistics as select * from dba_tab_statistics;
create table miguser.dba_sql_plan_baselines as select * from DBA_SQL_PLAN_BASELINES;
create table miguser.dba_data_files as select * from dba_data_files;
create table miguser.dba_temp_files as select * from dba_temp_files;
create table miguser.dba_roles as select * from dba_roles;
create table miguser.dba_sql_profiles as select * from DBA_SQL_PROFILES;
create table miguser.dba_types as select * from DBA_TYPES;
create table miguser.dba_ts_quotas as select * from dba_ts_quotas;
create table miguser.dba_registry as select * from dba_registry;
create table miguser.dba_sql_patches as select * from DBA_SQL_PATCHES;
create table miguser.OPTSTAT_HIST_CONTROL as select * from SYS.OPTSTAT_HIST_CONTROL$;
spool off


4) Trigger export backup using below parafile: 

userid='/ as sysdba'
directory=MIG_DIR
dumpfile=expdp_<DBNAME>_DATE_%U.dmp
logfile=expdp_<DBNAME>_DATE.log
EXCLUDE=STATISTICS
FULL=Y
parallel=<2 * no of CPU on server>
cluster=N
filesize=10G
LOGTIME=ALL

Check the export log and make sure there is no error in the export logs. 

5) Transfer the export backup to the target server.

NOTE: You can use NFS for export import to avoid this step. 

6) Import export backup into target database. 

 Only import application schema , Importing default schema might lead to the corruption of oracle components.

userid='/ as sysdba'
directory=EXP_DB
dumpfile=expdp_<DBNAME>_DATE_%U.dmp
logfile=impdp_<DBNAME>_DATE.log
parallel=<2 * no of cpu on the server >
cluster=N
exclude=password_history
LOGTIME=ALL
EXCLUDE=SCHEMA:"in ('SYS','SYSTEM','ANONYMOUS','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN','WMSYS','XDB','XS$NULL','SYSBACKUP','SYSDG','SYSKM','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER')"

Check import log and make sure there is no error in the import log. Also compare the export and import log for the object and row count. 

7) Compare object ,constraints ,grants and index  count between source and target database.

Purge the recycle bin on both database and run utlrp on both database. 

  • Compare object count using below query:
    select owner,object_type,count(*) from dba_objects where owner not in ('XDB','WMSYS','SYSTEM','SYS','APPQOSSYS','AUDSYS','DBSNMP','PERFSTAT','OUTLN','ORACLE_OCM','OJVMSYS','GSMADMIN_INTERNAL','SQLTXADMIN','SQLTXPLAIN') group by owner,object_type order by 1;
  • Compare Constraints using below query:
     select OWNER,CONSTRAINT_TYPE,count(*) from dba_constraints where OWNER not in ('XDB','WMSYS','SYSTEM','SYS','OJVMSYS','GSMADMIN_INTERNAL','DBSNMP','AUDSYS','SQLTXADMIN','SQLTXPLAIN') group by OWNER,CONSTRAINT_TYPE order by 1;
  • Compare Index count using below query:
    select OWNER,INDEX_TYPE,count(*) from dba_indexes where OWNER not in ('XDB','WMSYS','SYSTEM','SYS','OJVMSYS','OUTLN','GSMADMIN_INTERNAL','AUDSYS','DBSNMP','SQLTXADMIN','SQLTXPLAIN') group by OWNER,INDEX_TYPE order by 1;
  • Compare objects grants using below query:
    select GRANTEE,TYPE,count(*) from dba_tab_privs where GRANTEE not in ('XDB','WMSYS','SYSTEM','SYS','OJVMSYS','OUTLN','GSMADMIN_INTERNAL','AUDSYS','DBSNMP','SYSUMF_ROLE','SYSRAC','SYSKM','SYSDG','SYSBACKUP','SYS$UMF','SELECT_CATALOG_ROLE','PUBLIC','ORACLE_OCM','OEM_MONITOR','APPQOSSYS','AUDIT_ADMIN','AUDIT_VIEWER','CAPTURE_ADMIN','DATAPUMP_EXP_FULL_DATABASE','DBA','DBA_NV_ROLE','GSMUSER','GGSYS','EXP_FULL_DATABASE','IMP_FULL_DATABASE','ADM_PARALLEL_EXECUTE_TASK','DATAPUMP_IMP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','GSMUSER_ROLE','OPTIMIZER_PROCESSING_RATE','RECOVERY_CATALOG_OWNER','RECOVERY_CATALOG_OWNER_VPD','RECOVERY_CATALOG_USER','SQLTXADMIN','SQLTXPLAIN') group by GRANTEE,TYPE order by 1;

8) Gather stats for full database and dictionary stats:


set echo on;
set timing on;
spool stat_<DATE>.log
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
EXEC dbms_stats.gather_database_stats
spool off;
exit;

9) Export Import the SQL Profiles

SELECT name FROM dba_sql_profiles; 

Create table for sql_profiles:
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_TT',schema_name=>'MIGUSER');

Pack all the profiles on Staging table:

SELECT name FROM dba_sql_profiles;
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>'SYS_SQLPROF_XXXXXXXX'); 

export profiles

exp  file=SQL_PROFILES.dmp tables=SQL_PROFILES_TT

Import sql_profiles

imp file=SQL_PROFILES.dmp full=y

Execute below to import profiles

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROFILES_TT');

10) Backup AWR dump from source database and import in target database. 

Export AWR Dump : @?/rdbms/admin/awrextr.sql
Import AWR Dump: @?/rdbms/admin/awrload.sql

11) Enable archive log mode , Block change tracking and Perform full backup of the target database. 

==========================================================

Please check our other blogs for Migration


Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restore MySQL Database from mysqlbackup

-
Image
 Restore MySQL Database from mysqlbackup  In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.  In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup. In order to restore Mysql database from MySQL backup , We will follow below steps: 1) Shut down MySQL Database Server/Service Check the status of the mysql service using command : systemctl status mysql Shut down mysql service using command : systemctl stop mysql 2) Delete all existing file of MySQL Server Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory. 3) Restore full Backup mysqlbackup  --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --datadir=/var/lib
Read more

Oracle 19c New Features

-
Oracle 19c New Features As Oracle Support have made 12.2 version of Oracle ,its has become important for us to upgrade our 12c database to 19c.And before upgrade any database to latest version , We need to know the latest new features of that particular version.  In the current blog , We are going to discuss about new features of the Oracle 19C database : 1)Automatic Indexing  This is one of the important Oracle database 19c New feature. Automatic Indexing is one of the great feature included in 19c . It automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle Database based on changes in the application workload. This feature improves database performance by managing indexes automatically in an Oracle Database. Oracle has provided new package DBMS_AUTO_INDEX for this. This enables automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements: EXEC DBMS_AUTO_INDEX.CONFIGURE
Read more

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more about
Read more