Database Migration

 

Contents

1. Database Migration.

1.1        New DB Creation.

We create the new database MAP7APP, MAP7POS via Automation scripts
1.	Create database MAP7APP, run automation scripts (The Script in SVN directory: SVN/TDSQLScripts/2015 Q1-InitialSetup/OPTI7APP Set up). Modify parameter refer to readme.txt.
             ---------Don’t forget to add role in map7app database (this role only exists in 7th
 Schema beta)    
      SQL>conn sys/password@SID as sysdba                                   
      SQL>create role BACKUP_APPBETA_ROLE;
      SQL>create role BACKUP_APPBETA;
      SQL>create role EFSNROLE;
      
2.	Create database MAP7POS, run automation scripts (The Script in SVN directory: SVN/TDSQLScripts/2015 Q1-InitialSetup/OPTI7POS Set up).Modify parameter refer to readme.txt.

1.2        Backup data.

1.	Shutdown Tomcat.
2.	Disable any scheduled backup tasks.
3.	Using data pump tool (expdp) to export the dump for beta (save as beta.dmp) on the source databases.

1.3        DB Migration.

1.	Using data pump tool (impdp) to generate the ddl for schema appbeta and posbeta from the above dump.Considering that the database type is consistent, after discuss we decided migrate all the contents of the beta to MAP7APP database instead of MAP7POS database. Prepare ddl sql for appbeta and posbeta.
i.	Generate the ddl for schema APPBETA 
(Dump file use step1.2 Backup 7th data save as beta.dmp), and we can’t check in appbeta_ddl.sql for 7th database backup files are variable.
Impdp appbeta/password DIRECTORY=dbdump dumpfile=beta.dmp schemas=beta transform=segment_attributes:n:index transform=segment_attributes:n:constraint transform=OID:n:type sqlfile=appbeta_ddl.sql

We can use “Replace” in the Tool UltralEdit modify appbeta_ddl.sql:
a)	Find what: (VARCHAR2|CHAR)\((\d*)\sBYTE\) Replace with: \1 (\2).  

Due to the effect of the initial parameter “NLS_LENGTH_SEMANTICS”, all the CHAR/VARCHAR2 columns definition in the DDL file ends with “BYTE”.
For example, "NAME" VARCHAR2 (30 BYTE)”, we should use regular expression to replace it with "NAME" VARCHAR2 (30);
b)	Find what: USING \ (.*\); Replace with:;
 The script for all the mview logs end with “USING ("MLOG$_BODY_CLASSIFICATION_...;”, we should remove the using clause, otherwise, it will cause some errors when create the mview logs. 
c)	Find what: scheme beta. Replace with: appbeta. ;
Note: Create Index maximum key length (6398) exceeded may cause an error (Refer to Bug#28754).this error we just had discussed 

ii.	Generate the ddl sql for schema POSBETA
Note: this step I used TD production database map7pos dump base on release after.2016.12.09 -TD 2017 MAR - GA7.2.0.0, save as create_pos_db_structure.sql. 

2.	After the replacement, Run appuser_ddl.sql on the target APP database to generate all the database objects of APPBETA in SQLPlus as follow:
SQL>conn appbeta/password
SQL>spool appbeta_result.txt
SQL>set echo on;
SQL>set sqlblanklines on;
SQL>@appbeta_ddl.sql;

3.	Run a script (disable_all_fk.sql) to disable all constraints, otherwise it will cause some errors when importing the data in the next step.
(The Script in SVN directory: SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016 SEPT - Migration Phase 1.2 (Data)/ 1 sql scripts for init setup/ disable_all_fk.sql)
4.	Using data pump tool(impdp) to import the data for APPBETA
Impdp appbeta/password DIRECTORY=dbdump dumpfile=beta.dmp remap_schema=beta:appbeta CONTENT=data_only transform=segment_attributes:n:index exclude=statistics parallel=4  
5.	Run a script (enable_all_fk.sql) to enable all constraints as APPBETA.
(The Script in SVN directory:SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016 SEPT - Migration Phase 1.2 (Data)/ 1 sql scripts for init setup/ enable_all_fk.sql)

6.	Run posbeta_ddl.sql on the target POS database to generate all the database objects of posuser as follow:
SQL>conn posbeta/password 
SQL>spool posbeta_result.txt
SQL>set echo on;
SQL>set sqlblanklines on;
SQL>@ create_pos_db_structure.sql;
(The Script in SVN directory:SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016 SEPT - Migration Phase 1.2 (Data)/ 1 sql scripts for init setup/ create_pos_db_structure.sql)
Note: 1. This step must be run after #5 so that all the mview data can be   synchronized from appbeta, also, please make sure set sqlblanklines on, otherwise, it will cause some errors when creating mview.
      2.Data migration contents pos structure, we only need upgrade  database to latest version

7.	Run scripts data_mig_techtask22580.sql and data_mig_techtask22598.sql
(The Script in SVN directory:SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016 SEPT-MigrationPhase1.2(Data)/1 sql scripts for init setup/
data_mig_techtask22580.sql& data_mig_techtask22598.sql).

8.	TechTask #22593 [TD Data Migration] - Data Testing Point - Report Builder.   create the small database dump including all tables with name prefix "rpt_" from TD dump, and import the temporary table with the necessary values.Manual modify TD_Data_Migration_RB_export_rpt_table.sql =>TD_Data_Migration_RB_export_rpt_table.bat 
TD_Data_Migration_RB_import_rpt_table.sql =>TD_Data_Migration_RB_import_rpt_table.bat 
(The Script in SVN directory:SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016 SEPT-MigrationPhase1.2 (Data)/1 sql scripts for init setup/
TD_Data_Migration_RB_export_rpt_table.sql &TD_Data_Migration_RB_import_rpt_table.sql).

9.	Compare to TD which base on release. after.2016.12.09 -TD 2017 MAR - GA7.2.0.0   
i.	login PL/SQL for td environment, use “tools-compare user object”, get the differences Between TD production database and 7th save difference as “7th_structure_merge_to_td_all.sql”, and we also need comparison sequences and save as “7th_structure_merge_to_td_sequence.sql”;
SQL>conn appbeta/password 
SQL>spool 7th_structure_merge_to_td.txt
SQL>set echo on;
SQL>set sqlblanklines on;
SQL>@ 7th_structure_merge_to_td_all.sql;
SQL>@ 7th_structure_merge_to_td_sequence.sql;
(The Script in SVN directory: SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016 SEPT - Migration Phase 1.2 (Data)/ 1 sql scripts for init setup/
7th_structure_merge_to_td_all.sql).
(The Script in SVN directory: SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016SEPT - Migration Phase 1.2 (Data)/ 1 sql scripts for init setup/
7th_structure_merge_to_td_sequence.sql).

10.	Run scripts recreate_app_mv_logs.sql and recreate_pos_mv.sql 
SQL>conn appbeta/password 
SQL>@ recreate_app_mv_logs.sql;

SQL>conn posbeta/password 
SQL>@ recreate_pos_mv.sql;
(The Script in SVN directory: SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016 SEPT - Migration Phase 1.2 (Data)/ 1 sql scripts for init setup/
recreate_app_mv_logs.sql).
The Script in SVN directory: SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016SEPT - Migration Phase 1.2 (Data)/ 1 sql scripts for init setup/
recreate_pos_mv.sql).


Note: When finished #10, suggest backup the database.

11.	Gather the database stats for appbeta.
SQL>conn appbeta/password 
SQL>@ gather_appbeta_stats.sql
The Script in SVN directory: SVN/TDSQLScripts/release.after.2016.07.25 - TD 2016SEPT - Migration Phase 1.2 (Data)/ 1 sql scripts for init setup/
 gather_appbeta_stats.sql).

12.	Use sql update tool ‘SQLAPP’ to apply branch release.after.2017.04.07 - TD 2017 MAR – GA7.2.0.20 Patch

13.	Use sql update tool ‘SQLAPP’ to apply branch release.after.2016.07.25 - TD 2016 SEPT - Migration Phase 1.2 (Data)\4 sql scripts based on 2018 Apr release

1.4        Validation.

We can use automated tools to do comparison on DB schema level, if everything is ok, it will be wonderful.
There are two methods:
1.Compare to TD which base on one release (“one release” mean we can choose Release. after.2016.12.09 - TD 2017 MAR - GA7.2.0.0 (TD) or we can choose latest release after upgrade below step 5), take the union of two database environment structure, and use sqlscripts.
-- Create database link which conn td environment
---For example:
CREATE PUBLIC DATABASE LINK OPTI7APP_LINK237
             CONNECT TO APPBETA IDENTIFIED BY ******
              USING '10.125.2.237/OPTI7APP'; 
----COMPARE TABLE COLUMN AND DATA_LENGTH
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH
  FROM USER_TAB_COLUMNS A
 WHERE NOT EXISTS (SELECT 1
  FROM USER_TAB_COLUMNS@OPTI7APP_LINK237 
 WHERE A.COLUMN_NAME = B.COLUMN_NAM
   AND A.DATA_LENGTH = B.DATA_LENGTH
   AND A.DATA_TYPE = B.DATA_TYPE);
----COMPARE USER OBJECTS          
SELECT *
FROM USER_OBJECTS A
 WHERE NOT EXISTS (SELECT 1
  FROM USER_OBJECTS@OPTI7APP_LINK237 B
        WHERE A.OBJECT_NAME = B.OBJECT_NAME);

1.5        Backup migration Environment

Reconfigure RMAN backup strategy for both OPTI7APP and OPTI7POS as before by running the Script in SVN directory: \SVN\CMMSQLScripts\RMAN_Backup
1.	Enable database archive log.
2.	Config the backup retention
3.	Config the fast recovery area size, please note, this step will configure the initial parameter

 

 

 

 

---