Use shell to generate scripts for integrated data migration in batches

Use shell to generate scripts for integrated data migration in batches

For integrated data migration, it is basically the two-in-one, four-in-one, and eight-in-One rhythm of the Overlord, which integrates data from databases that are as relevant to the business as much as possible into a single database. They are still independent schemas, but they are also safe.

In this integrated migration, the tangled part is that the performance is not ranked first, but the preparations before the migration are trivial.

If there are a lot of db links in the environment, it is like a spider network. Each environment is closely related. If the preparation is improper, a small error occurs, it may be the impact of the bones and muscles. Or there are a large number of connected users in the environment. In some environments, there are many associated businesses, and there may be dozens or hundreds of connected users. At this time, it is very complicated to prepare scripts. For example, you must first obtain the table space ddl and profile ddl before creating a statement.

Some environments have a large number of test users or temporary users. The data of these users may be unclaimed for historical reasons. However, there are some data in it, which is not migrated. It may be too late to find the problem after a period of time. If the data is migrated, it may take a lot of time for migration, the feeling of chicken ribs.

If there are multiple sets of environments, the tablespace ddl of each environment is obtained. If the selected import target environment is not added, it is likely that some of the tablespaces are named again. For the sake of rigor, we still need to make a comparison in advance.

If some environments need to be migrated, and these detours need to be taken every time, you can use scripts to simplify this part of the work. In the future, the troops will block the water.

Therefore, we took the time to write the following shell script. The script content is as follows. The general idea is to dynamically call some common check items. For example, to check the table space, the tablespace of the source and target databases will be compared. If the table space does not exist in the target database, generate the corresponding ddl statement. On this basis, the user profile information to be migrated, user ddl statements, and permission statements are further generated. More information about object permissions is planned to be imported through dump.

Function check_ts
{
Sqlplus-s $1 <EOF
Set pages 0
Set feedback off
Set linesize 100
Col tablespace_name format a30
Select tablespace_name from dba_tablespaces;
EOF
}

Function gen_ts_ddl
{
Sqlplus-s $1 <EOF
Set pages 0
Set feedback off
Set linesize 200
Sets long 99999
Col TS_DDL format a150
Exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'store', FALSE );
Select dbms_metadata.get_ddl ('tablespace', '$ 2') |'; 'ts_ddl from dual;
EOF
}

Function check_user
{
Sqlplus-s $1 <EOF
Set pages 0
Set feedback off
Set linesize 100
Col tablespace_name format a30
Select username from dba_users;
EOF
}

Function gen_user_ddl
{
Sqlplus-s $1 <EOF
Set pages 0
Set feedback off
Set linesize 200
Sets long 99999
Col TS_DDL format a200
Select dbms_metadata.get_ddl ('profile ', PROFILE) |'; 'ts_ddl from dba_profiles where profile in (select profile from dba_users where username = upper ('$ 2') and profile! = 'Default' and rownum <2;
Select dbms_metadata.get_ddl ('user', '$ 2') |'; 'ts_ddl from dual;
-- Select dbms_metadata.get_granted_ddl ('System _ grants', '$ 2') TS_DDL from dual;
Select dbms_metadata.get_granted_ddl ('Role _ GRANT ',' $ 2') | '; 'ts _ DDL from dual;
-- Select dbms_metadata.get_granted_ddl ('object _ GRANT ',' $ 2') TS_DDL from dual;
EOF
}

BASE_DIR = '/home/Oracle/data_mig'

If [[-z $1] | [[-z $2]; then
Echo '************************************** *********'
Echo 'Warning: Source And Target Conn details are Needed! '
Echo '************************************** *********'
Exit
Fi

Source_alias = 'echo $1 | awk-F @ '{print $1 "_" $2}' | awk-F \/'{print $2 }''
Target_alias = 'echo $2 | awk-F @ '{print $1 "_" $2}' | awk-F \/'{print $2 }''

Function diff_run
{
Opr_type = $1
Check _ $ {opr_type} $2 | sort >$ {BASE_DIR}/source _ $ {opr_type }_$ {source_alias}. lst
Check _ $ {opr_type} $3 | sort >$ {BASE_DIR}/target _ $ {opr_type }_$ {target_alias}. lst
Diff $ {BASE_DIR}/source _ $ {opr_type }_$ {source_alias }. lst $ {BASE_DIR}/target _ $ {opr_type }_$ {target_alias }. lst | grep \ <| sed-e's/</g '-e's // G' >$ {BASE_DIR}/target _ $ {opr_type} _ diff. lst

For tmp_opr_type in 'cat $ {BASE_DIR}/target _ $ {opr_type} _ diff. lst | awk '{print $1 }'';
Do
Echo "#" $ {tmp_opr_type}
Gen _ $ {opr_type} _ ddl $2 $ {tmp_opr_type}
Done
}
### MAIN
Diff_run ts $1 $2
Diff_run user $1 $2
For example, run the script
Sh data_mig_pre.sh xxxx/xxx @ source xxxx/xxx @ target
The result is as follows, including the tablespace, profile information, user ddl and permission statements, which seems to be much simplified and can be generated at any time.
# ORDERMOB_INDEX
Create tablespace "ORDERMOB_INDEX" DATAFILE
'/U01/app/oracle/oradata/ordermob0/ordermob_index01.dbf' SIZE 1073741824
Autoextend on next 104857600 MAXSIZE 32767 M
Logging online permanent blocksize 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'/U01/app/oracle/oradata/ordermob0/ordermob_index01.dbf' RESIZE 7784628224;
Create profile "PF_APP_ORDERMOB_STAT"
LIMIT
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 50
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME DEFAULT
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS DEFAULT
PASSWORD_LIFE_TIME DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT;
Create user "APP_ORDERMOB_STAT" identified by values 'xxxx'
Default tablespace "USERS"
Temporary tablespace "TEMP"
PROFILE "PF_APP_ORDERMOB_STAT ";
GRANT "CONNECT" TO "APP_ORDERMOB_STAT ";

This article permanently updates the link address: