CN110019138B

CN110019138B - Automatic transfer table space migration method and system based on Zabbix

Info

Publication number: CN110019138B
Application number: CN201711478219.2A
Authority: CN
Inventors: 李永辉; 裴照华; 郭强; 鄢兵安; 彭庆军; 王峥; 李�远
Original assignee: China Mobile Communications Group Co Ltd; China Mobile Group Henan Co Ltd
Current assignee: China Mobile Communications Group Co Ltd; China Mobile Group Henan Co Ltd
Priority date: 2017-12-29
Filing date: 2017-12-29
Publication date: 2022-06-03
Anticipated expiration: 2037-12-29
Also published as: CN110019138A

Abstract

The invention provides a transmission table space automatic migration method and system based on Zabbix, wherein the method comprises the following steps: connecting Oracle of a source end database to be migrated through Zabbix, acquiring a table space of the source end database, checking whether the table space is self-contained, and calling a transmission table space XTTS migration process through Zabbix if the table space is self-contained; creating a related directory required in an XTTS migration process, performing full backup recovery from a source end database to a target end database in an XTTS form, and performing multiple incremental backup recovery; and (4) deriving metadata from the source end database, carrying out grouping loading on the target end database, and carrying out consistency check. Migration is carried out by automatically constructing an XTTS transmission table space, centralized scheduling is carried out by Zabbix, and XTTS subprocesses with corresponding quantity are derived according to the number of data migration subtasks. The down time is reduced, and the storage cost is saved.

Description

Automatic transfer table space migration method and system based on Zabbix

Technical Field

The invention relates to the technical field of database migration, in particular to a transmission table space automatic migration method and system based on Zabbix.

Background

Oracle Database, also known as Oracle RDBMS, or simply Oracle. The Oracle database is already widely used as a database (database) in various industries to bear enterprise production activities, but with the popularization of cloud technology, an X86 architecture is widely used, and many original small computer systems are migrated to an X86 platform to realize cloudization, but during migration, the service downtime is long, the storage space requirement is large, hundreds of TBs are frequent, the service migration efficiency is reduced, and the migration cost is increased.

In the oracle, the downtime and the storage space are reduced by migrating the database, so that the cost is effectively saved. Taking the data size of 20T as an example, if a conventional exppdp or EXP data pump is used for database migration, the minimum storage of 10T is needed for exporting data first, and if various conditions such as a host network are optimal, the data export time is 20 hours, the data transmission time is 10 hours, and the data import time is 40 hours. To summarize, a 20T database migration requires 10T storage, 70 hours of business down time.

The migration efficiency of the conventional EXDP technology is completely determined by the performance of the host and the database, the stability of the conventional EXDP technology is far from the stability, and a fault cannot be predicted in the data migration process, which may cause data loss during the fault. When the condition is serious, the service system is paralyzed due to online hot standby, and the availability of the database is reduced, so that the consistency of data transmission is ensured, the time consumption and possible faults of service transmission are known, and the decision of service key points is facilitated; generally, 10T data takes 40 hours, so that the core service downtime is greatly increased, and the service continuity is influenced; when unpredictable faults occur and the processing difficulty is high in the backup process, maintenance personnel are required to have high technical capability and abundant operation and maintenance fault experience, the field environment is familiar with the service knowledge, which kind of service is preferentially ensured, and if an automatic backup management platform is provided, the data migration time can be shortened, and the fault probability can be reduced. Meanwhile, an effective alarm triggering mechanism helps maintenance personnel to quickly process, so that the technical level of the maintenance personnel is also dependent. If the technical level of an engineer cannot achieve the two aspects at the same time, the production of the enterprise is reduced and perceived, and the repair progress of the fault is delayed. For example, in the backup process, an operator accidentally operates at rm without caution to delete a large number of data files, so that irreparable loss is caused, great influence is caused on companies, clients and safety, the engineer is lack of experience or operates mistakenly to delete data on a production server, and the problems of lack of supervision and unreasonable authority management and control of operation are directly reflected. Therefore, the authority and safety management of the staff must be strengthened, the automation level is improved, and the manual misoperation is reduced; the price of a 512G enterprise-level SSD solid state disk is 8 ten thousand famous currencies, if 10T data migration is met, the total price is 80 thousands, and the SSD disk cannot be reused, so that resource waste is caused, and the cost pressure is increased.

Disclosure of Invention

The invention provides a transmission table space automatic migration method and system based on Zabbix, which overcomes or at least partially solves the problems, and solves the problems that in the prior art, the data transmission efficiency is low, the risk of artificial misoperation is high, and the SSD space is greatly wasted.

According to one aspect of the invention, a method for automatically migrating a transmission table space is provided, which comprises the following steps:

connecting Oracle of a source end database to be migrated through Zabbix, acquiring a table space of the source end database, checking whether the table space is self-contained, and calling a transmission table space XTTS migration process through Zabbix if the table space is self-contained;

creating a related directory required in an XTTS migration process, performing full backup recovery from a source end database to a target end database in an XTTS form, and performing multiple incremental backup recovery;

and (4) leading out metadata from the source end database, carrying out grouping loading on the target end database, and carrying out consistency check.

Preferably, the obtaining, by connecting Zabbix to the source database, a tablespace of the source database to be migrated includes:

acquiring connection information from Zabbix, connecting to a source database, acquiring tablespace, version, user and total amount of a remote database to be migrated, and determining the size of migration data, wherein the migration data comprises the number of tablespaces, indexes, invalid objects, functions and sequences.

Preferably, the performing full backup restoration from the source-end database to the target-end database in the form of XTTS specifically further includes:

and transmitting the corresponding data file of the XTTS from the source database to the final position of the target end database by a dbms _ file _ transfer method, transmitting the data from the source database to the target end database, and performing format conversion.

Preferably, before performing full backup restoration on the source-end database to the target-end database in the form of XTTS, the method further includes:

and verifying migration environment information, wherein the migration environment information comprises a user, a directory, a proc, a data volume, a source database newly-added table, a source database newly-added user, a source database newly-added storage process and an old table cleaning.

Preferably, creating the relevant directory required in the transport table space XTTS migration process specifically includes:

and creating a related directory required in the XTTS migration process of the transmission table space to ensure that the directory size of the target end database is equal to the directory size corresponding to the source end database, and establishing a link DBLINK pointing to the source end database in the target end database.

Preferably, the metadata includes Oracle table metadata, Oracle file metadata, Oracle process metadata, and Oracle script metadata.

Preferably, the performing the consistency check specifically includes:

comparing the information of the original end database with the information of the target end database, analyzing the difference value of the source end database and the target end database, and judging that the source end database is the same as the target end database based on the difference value;

if the two are the same, the migration work is judged to be completed, and if the two are different, the migration error is reported.

A transport table space auto-migration system, comprising:

the Zabbix Server scheduling platform is used for connecting Oracle needing to be migrated to a source end database through Zabbix, acquiring a table space of the source end database, checking whether the table space is self-contained or not, and calling a transmission table space XTTS migration process through the Zabbix if the table space is self-contained;

the XTTS subtask process module is used for creating a related directory required in an XTTS migration process, performing full backup recovery from a source end database to a target end database in an XTTS form, and performing multiple incremental backup recovery; and (4) deriving metadata from the source end database, carrying out grouping loading on the target end database, and carrying out consistency check.

A transport table space auto-migration apparatus comprising:

at least one processor, at least one memory, a communication interface, and a bus; wherein,

the processor, the memory and the communication interface complete mutual communication through the bus;

the communication interface is used for information transmission between the test equipment and the communication equipment of the display device;

the memory stores program instructions executable by the processor, the processor invoking the program instructions to perform the transfer table space auto-migration method described above.

A computer program product comprising a computer program stored on a non-transitory computer readable storage medium, the computer program comprising program instructions which, when executed by a computer, cause the computer to perform the above-described transmission table space auto-migration method.

The invention provides a transmission table space automatic migration method and system based on Zabbix, wherein an XTTS transmission table space is automatically constructed for migration, centralized scheduling is carried out through Zabbix, the XTTS can be in communication connection with Zabbix software construction API, Zabbix derives a corresponding number of XTTS sub-processes according to the number of data migration sub-tasks, and the data migration process is implemented by each sub-process in parallel to process corresponding tasks. In the processing process of each XTTS subprocess, an application program interface API provided by zabbix software is called to realize task processing corresponding to the XTTS, and whether full backup, migrated users, migration table space, self-inclusion, object information and the like are carried out on a source end database and a target end database in the initialization stage of the XTTS, so that the method provides more convenient and faster migration work of the existing database, an effective migration technology, reduces downtime, saves storage cost and avoids risks in a limited way.

Drawings

FIG. 1 is a diagram illustrating an exemplary method for automatically migrating a transmission table space according to an embodiment of the present invention;

FIG. 2 is a data call presentation diagram according to an embodiment of the present invention;

FIG. 3 is a flow diagram illustrating the Zabbix call according to an embodiment of the present invention;

FIG. 4 is a diagram illustrating analysis of the results of a Zabbix call, according to an embodiment of the invention;

FIG. 5 is a block diagram of an automated migration system for a transfer table space according to an embodiment of the present invention.

Detailed Description

The following detailed description of embodiments of the present invention is provided in connection with the accompanying drawings and examples. The following examples are intended to illustrate the invention but are not intended to limit the scope of the invention.

zabbix (audio and zaebix) is an enterprise-level open source solution based on a WEB interface that provides distributed system monitoring and network monitoring functions. zabbix can monitor various network parameters to ensure the safe operation of the server system; and provides a flexible notification mechanism to allow system administrators to quickly locate/resolve various existing problems. zabbix consisted of 2 parts, zabbix server and an optional component zabbix agent. The zabbix server can provide functions of monitoring the state of a remote server/network, collecting data and the like by SNMP, zabbix agent, ping, port monitoring and the like, and can be operated on platforms such as Linux, Solaris, HP-UX, AIX, Free BSD, Open BSD, OS X and the like.

xtts (Cross Platform Transportable Tablespaces), i.e. the transport space table in this embodiment, can be said to be an enhanced version of tts, and is used to reduce the downtime through the concept of increment. The deficiency is that the target can only be linux 86-64, and the target database must be 11.2.0.4 or software loaded 11.2.0.4.

The cross-platform migration tablespace is a physical migration method based on tablespace transmission introduced from Oracle 8i, named as TTS, and is subject to continuous evolution of various versions, starting from 11gR2, under the condition that the requirement on relative downtime is increasingly reduced, in order to cope with cross-platform migration of larger and larger data volume, Oracle provides a new solution, namely, an enhanced XTTS (XTTS for short), the XTTS realizes cross-platform data migration by using an incremental backup mode, and the downtime is greatly shortened in a real sense. Today, U2L is fierce, it has become a great skill to migrate the Oracle database "small machine + centralized storage" environment to "X86 architecture platform + distributed storage" quickly, efficiently, smoothly, and safely through XTTS.

As shown in fig. 1 and fig. 2, a method for automatically migrating a transmission table space is shown, which includes:

connecting Oracle of a source end database to be migrated through Zabbix, acquiring a table space of the source end database, checking whether the table space is self-contained, and calling a transmission table space XTTS migration process through Zabbix if the table space is self-contained; if not, preparing the table space needing to be migrated from the new or solving the self-contained problem;

and (4) deriving metadata from the source end database, carrying out grouping loading on the target end database, and carrying out consistency check.

Self-contained means that objects in the current set of tablespaces do not depend on objects outside the set of tablespaces. Self-contained tablespaces may guarantee that tablespace migration will occur, but objects in dependent tablespaces other than tablespaces will be lost during tablespace migration.

Complete self-containment can ensure complete tablespace migration without object loss.

Conversely, if an object of a tablespace depends on an object outside the tablespace, then the self-contained condition is not satisfied.

For example, there is a table in table space a and an index above it in table space B. Then for tablespace a, the objects in it are independent of objects outside the tablespace and are therefore self-contained, but indices outside the tablespace are dependent on objects inside the tablespace and are not a complete self-contained. Tablespace migration may be complete, but INDEX is lost in tablespace migration, and conversely, for tablespace B, object INDEX in tablespace depends on TABLE object in tablespace A. The self-contained condition is not satisfied here. Problems arise with tablespace migration.

The shutdown phase is implemented, metadata export is carried out at the source end, and in order to reduce the shutdown time, the metadata export can be carried out in a grouping mode according to the self-contained condition of the table space, and then the metadata are loaded in a grouping mode at the target end.

Data consistency checking: after the above operations are completed, the database migration is basically completed, and in order to ensure the successful migration, the following dimensional data consistency checks are performed:

number of check tables (tables), fields;

check indexes (indexes) including a failure index, a bitmap index, a BIT index, etc.;

checking invalid objects, functions, sequences, dirty data, stored procedures, tables, etc.

In the embodiment, based on an internet automation operation and maintenance thought, theoretical application guidance is taken as a core thought, and a brand-new automatic migration method based on the zabbix XTTS transmission table space is constructed by following CAP law.

CAP Law: for a distributed system, the following guarantees cannot be provided at the same time: consistency, availability, partition tolerance.

CAP law, if it provides a uniform public safety control perspective for business continuity of enterprises, a flexible way to deal with current and future threats, needs to sacrifice one of the above 3 items to meet production requirements.

CMDB theorem: the CMDB stores and manages various configuration information of equipment in the IT architecture of the enterprise, is closely connected with all service support and service delivery processes, supports the operation of the processes, exerts the value of the configuration information and simultaneously depends on the related processes to ensure the accuracy of data. In practical projects, CMDB is often considered as the basis for building other ITIL processes to prioritize, i.e., manage changes through an automated, repeatable process so that when a change occurs, there is a standardized process to execute, and the impact of the change on overall system management can be predicted and evaluated and controlled.

In a data migration system with ZABBIX as a core, CAP law and CMDB theorem are used as system construction guiding ideas, a platform frame structure is designed by the CAP theorem, and CMDB is used for guiding design flow change, namely 'CC law' methodology for short, so that the method is beneficial to constructing an automatic migration method with optimal flow, simple operation and moderate cost.

In this embodiment, specifically, connecting the source database through Zabbix to obtain a table space of the source database to be migrated includes:

when automatic migration is initiated, firstly, connection information is obtained from Zabbix, the Zabbix is connected to a source end database, table space, version, user and total amount of Oracle to be migrated are obtained, the size of data to be migrated, including the number of tables, indexes, invalid objects, functions, sequences and other related data, is checked, and comparison is carried out for final data verification. Zabbix provides online modification of XTTS scripting functions, creating the dependency directory needed during XTTS migration.

In this embodiment, performing full backup and recovery from the source-end database to the target-end database in the form of XTTS specifically further includes:

in the stage, full backup recovery from a source library to a target library is carried out, backup data do not fall to the ground in the process, and backup recovery is carried out in a form of transmission table space; and transmitting the corresponding data file of the XTTS from the source database to the final position of the target end database by a dbms _ file _ transfer method, transmitting the data from the source database to the target end database, and performing format conversion.

Incremental backup recovery and N times of incremental backup recovery, namely, in order to reduce the downtime, the incremental backup recovery is carried out for a plurality of times, and the time of the last backup recovery during the downtime is reduced, thereby reducing the downtime.

In this embodiment, the auto-migration method uses the XTTS transmission table space as a core script, and ZABBIX as a calling method; the method comprises six links of data migration environment information verification, preparation before migration, data file conversion, 1 st incremental backup and recovery, last incremental backup and recovery, metadata synchronization and the like.

Specifically, when the data migration environment information is verified, the verification of the data environment information is the central importance of the automatic migration method, and the information verification at the XTTS initialization stage includes: whether to backup the full, the user who migrates, migrate the tablespace, whether to contain by itself, the object information, etc. (source end and target end), the check statement is as follows:

subject comparison

Verify that the new and old environment segment _ type, TMP temporary table space and CLOB index do not match and default to no exception:

- - - - - - [ new environment execution ]:

select r.owner,r.segment_type,r.remote_cnt Source_Cnt,l.local_cnt Target_Cntfrom(select owner,segment_type,count(owner)remote_cnt from dba_segments@ttslink where owner not in(select name f rom system.logstdby$skip_support where action＝0)AND SEGMEN T_NAME NOT LIKE'BIN％'group by owner,segment_type)r,(select owner,segment_type,count(owner)local_cnt from dba_seg ments where owner not in(select name from system.logstdby$skip_support where action＝0)AND SEGMENT_NAME NOT LIKE'B IN％'group by owner,segment_type)l where l.owner(+)＝r.own er and l.segment_type(+)＝r.segment_type and nvl(l.local_cnt,-1)！＝r.remote_cnt order by 1,3desc；

verifying the object _ type of the old and new contexts

- - - - [ new environment execution ]:

select r.owner,r.object_type,r.remote_cnt Source_Cnt,l.local_cnt T arget_Cnt from(select owner,object_type,count(owner)remote_c nt from dba_objects@ttslink where owner not in(select name fro m system.logstdby$skip_support where action＝0)group by owner,object_type)r,(select owner,object_type,count(owner)local_cnt from dba_objects where owner not in(select name from system.l ogstdby$skip_support where action＝0)group by owner,object_type)l where l.owner(+)＝r.owner and l.object_type(+)＝r.object_type and nvl(l.local_cnt,-1)！＝r.remote_cnt order by 1,3desc；

new and old environment check if the failed objects are consistent:

- - - - - - [ new environment execution ]:

select l.owner,l.object_type,r.remote_cnt Source_Cnt,l.local_cnt T arget_Cnt from(select owner,object_type,count(owner)remote_c nt from dba_objects@ttslink

where owner not in(select name from system.logstdby$skip_suppo rt where action＝0)and status＝'INVALID'group by owner,object_t ype)r,(select owner,object_type,count(owner)local_cnt from db a_objects where owner not in(select name from system.logstdby$s kip_support where action＝0)and status＝'INVALID'group by owne r,object_type)l where l.owner＝r.owner(+)and l.object_type＝r.object_type(+)and l.local_cnt！＝nvl(r.remote_cnt,-1)order by 1,3desc；

the following terms are remarked for environment verification scripts:

preparing before migration, and specifically creating a relevant directory required in the XTTS migration process of a transmission table space, wherein the relevant directory comprises the following steps:

and creating related directories required in the process of transferring the table space XTTS migration, so that the size of the directory of the target end database is equal to the size of the directory corresponding to the source end database, the size of the directory of the target end database is enough to store all table space data files, and establishing a link DBLINK pointing to the source end database in the target end database. The specific preparation contents are as follows:

(1) if the database version of the target end is 11.2.0.4, file conversion is carried out by using a dbms _ file _ transfer mode by configuring an Oracle directory object and a dblink;

(2) creating a source-side DIR: create direction source as 'XXXXX';

(3) configuring parameter srcdir in xtt.

(4) Creating a target DIR: create direction destdir as' XXXXX;

(5) parameters dstdir of configuration xtt.

Data file conversion, namely transmitting the corresponding data file of the migration table space from the source end to the final position of the target end through a dbms _ file _ transfer method, namely performing full backup on the database, wherein the data file cannot fall to the ground in the process, is directly transmitted from the source end to the target end, and simultaneously performs cross-platform format conversion, and a specific script execution command is as follows:

source database end) with oracle user, the following commands are executed at the source end:

perl/bin/perl xttdriver.pl-S

this step generates two files, xtnewdatafiles.

Target-side database the following commands are executed with oracle users:

perl/bin/perl xttdriver.pl-G

an incremental backup (incremental backup) is a type of backup, and means that after a full backup or a last incremental backup, each backup later only needs to backup files which are added or modified compared with the previous backup.

Incremental backup refers to that after a full backup or the last incremental backup, each backup only needs to backup files which are increased or modified compared with the previous backup. This means that the object of the first incremental backup is the added and modified file generated after full backup; the object of the second incremental backup is the added and modified files generated after the first incremental backup, and so on. The most significant advantages of this backup approach are: there is no duplicated backup data, so the amount of data to be backed up is not large and the time required for backup is short. But data recovery for incremental backups is cumbersome. It is necessary to have the last full backup and all incremental backup tapes (which will cause failure of recovery once a tape therein is lost or damaged), and they must be restored one by one in reverse order along the time sequence from full backup to sequential incremental backup, thus greatly extending the recovery time.

The greatest benefit of using incremental backups is the backup speed: the speed of the method is much faster than that of the full backup, and simultaneously, because the incremental backup can automatically judge the backup time point and whether the file is changed or not before the backup is carried out, the method is also beneficial to saving the storage space compared with the full backup. Incremental backups have the disadvantage of long data recovery time and relatively inefficient, e.g., if a backup file is to be restored, all incremental backups must be backed up until the disk is found, and if the entire file system is to be restored, the last full backup must be restored first, and then one incremental backup after another.

In this embodiment, after the step is completed, the data will be transmitted from the source end to the target end, and will undergo format conversion.

And (3) performing 1 st incremental backup and recovery, and replacing the conventional incremental backup verification by a simplified graph dragging operation based on a verification driving technology. The principle is to split a complex flow, and the method comprises the following steps of: and (5) operating, object and backup times, and further checking a result value to form a data file. The backup logic is encapsulated in a script file in the form of keywords, and the keywords realize backup and recovery automation. The keyword driving technology simplifies script writing work of the recover, an executive worker can realize graphical interface operation without knowing what kind of tool is used or even knowing the writing mode of the script, an incremental backup link covers the whole process, and reference to the restored script content is as follows:

transmitting incremental backup files and key files to target end database

1. Copy file copy to [ target end database ] stageondest parameter configuration path;

2. the copy corresponding to the increment is backed up to a stageondest parameter configuration path;

copying the file recorded by the file to the target end when the file name of the incremental backup set generated in the above step is incrbackups

Path specified by stageondest parameter

scp`cat incrbackups.txt`oracle@dest:/stage_dest

(target end database) for 1 st recovery

$ export XTTDEBUG ═ 1 (open debug function)

The $ export TMPDIR ═ opt/oracle/yjb/rmanxtts < - > -must

$env|grep ORA

$cd$TMPDIR

Executing receiver in the target end database after the confirmation is finished

nohup perl xttdriver.pl–r>xttdriver.pl_r.out&

Where the target library automatically undergoes a reboot operation.

The From _ SCN increment is performed [ source database ].

In this embodiment, the period from the first incremental backup to the last incremental backup means that verification is required if the first backup fails or data is newly added every day. Multiple backups are required. And script management, load balancing of multiple backup resource allocation, logging, scheduling and backup reports are displayed as a platform. The centralized cross-version automatic data migration system provided by the invention is mainly executed according to multiple backup objects and is divided into a root object and a child object, for example: when the tablespace is backed up, objects such as user objects, function objects, index objects and the like are control objects of the automatic migration system, the control objects are in a master-slave relationship, the platform finds the objects first and then operates the objects to which the objects belong, and specific operation verification scripts are as follows:

multiple incremental backup check phase

Creating an owner user, a temporary table space and an object at a target end, and mainly comprising the following three steps:

step 1, creating an owner user, namely creating the owner user in a target end database according to the user to be migrated, and firstly using a users table space as a default table space

Step 2, creating a temporary table space, namely creating a same-name temporary table space in the target-end database according to the size of the temporary table space in the original database

Step 3, creating a temporary table, namely inquiring the temporary table in the original library and acquiring a creating statement

Select table_name,owner from dba_tables where temzaiporary＝’Y’；

Set long 9999

Select dbms_metadata.get_ddl(‘table’,’table_name’,’owner)from dual；

If the temporary table objects are more, the table structures of the temporary tables can be imported into the target end database after the metadata of the transmission stage is imported

Incremental backup operation procedure

[ Source database ] tablespace is set as read only

alter tablespace‘xxxxx’read only；

………

alter tablespace‘xxxxx’read only；

-checking the table space state: (check for omission)

select TABLESPACE_NAME,status from dba_tablespaces；

Checking the tablespace state after read only

select tablespace_name,status from dba_tablespaces where tablespace_name not in(select tablespace_name from dba_temp_files)order by 2；

(Source database) for the last 1 incremental backup, and repeating the incremental backup recovery steps

$su–oracle

$ export XTTDEBUG ═ 1 (open debug function)

The TMPDIR must be/xxxx/xxxxx/xxxx < - >, and

$export TMPDIR

$cd$TMPDIR

$nohup perl xttdriver.pl-i>xttdriver.pl_i.out

scp incrbackups.txt oracle@dest:/home/oracle/xtt

scp xttplan.txt oracle@dest:/home/oracle/xtt

scp tsbkupmap.txt oracle@dest:/home/oracle/xtt

target end database

su–oracle

cd$TMPDIR

env | grep ORA (examination of ORA. environmental variables)

env | grep TMP (check TMPDIR settings)

export XTTDEBUG 1 (open debug function)

nohup perl xttdriver.pl-r>xttdriver.pl_r.out&

-database status

CONVERTED BACKUP PIECE/ogg/11204/xtts/XXXX。

The process is automatically executed without manual intervention, and if the incremental backup is abnormal, a short message warning engineer manually executes a subsequent command according to the situation.

And synchronizing metadata, wherein the metadata comprises Oracle table metadata, Oracle file metadata, Oracle process metadata and Oracle script metadata. Through classifying the Oracle metadata information, metadata management, metadata synchronization work and the like are realized, and the synchronization condition is known. The synchronization statement is as follows:

[ OBJECT ] Expdp/impdp export import metadata

A. Ora creates a corresponding connection string

B. Target library creates corresponding directory

create directory dump_dir_tts as'xxxxxx'；

grant all on directory dump_dir_tts to public；

C. Creating a corresponding db link

D. Deriving metadata

nohup expdp\"/as sysdba\"PARFILE＝xtts_dump.par&

xtts _ dump.par file content:

DUMPFILE＝XXXX

DIRECTORY＝XXXX

TRANSPORT_TABLESPACES＝XXXX

E. importing metadata

Impdp system/oracle directory＝DATA_PUMP_DIR

logfile＝tts_imp.log network_link＝ttslink

transport_full_check＝no

transport_tablespaces＝AA,BB,CC,...

transport_datafiles＝’xxxx’,’xxxx’,...

The script sets the original library | target library table space to read write.

After the synchronous import is finished, the table space of the target table space is set as read write, and the original table space is set as read write.

alter tablespace XXXX read write；

……………

alter tablespace XXXX read write；

[ OBJECT END ] CHECK METADATA CONFORMANCE

Data replication of the whole XTTS transmission table space works synchronously.

In this embodiment, Zabbix calls an XTTS transmission table space process as a core key point, and Zabbix is an enterprise-level open source solution providing distributed system monitoring and automation operation and maintenance functions based on a WEB interface. Zabbix can monitor various network and host parameters, ensure the safe operation of a server system, and provide a flexible notification mechanism to enable technicians to quickly locate/solve various problems. Zabbix moieties include Zabbix server and Zabbix agent. The zabbix server can provide functions of monitoring the state of a remote server/network, collecting data and the like by SNMP, zabbix agent, ping, port monitoring and the like, and can simultaneously monitor a plurality of servers. According to the invention, a friendly and visual WEB access interface is provided for technical personnel through zabbix software, and the technical personnel can complete multiple operations such as adding of a migration component, deleting of a database server, modification of a monitoring item, improvement of an environment variable and the like through simple mouse clicking on the WEB interface. Therefore, the method has the characteristics of intuition and easy operability.

The task processing template can be predefined. For example, an "XTTS tablespace" template may predefine the process flow of bulk adding databases and tablespaces. In the process, the XTTS is in communication connection with a Zabbix software construction API (Application Programming Interface), Zabbix derives a corresponding number of XTTS sub-processes according to the number of data migration sub-tasks, and the data migration process processes corresponding tasks in parallel by the sub-processes. During processing, each XTTS sub-process calls an application program interface API provided by zabbix software to implement task processing of the corresponding XTTS. For example, in the present invention, each time a database migration task is added, the XTTS task is implemented by calling the API function database for adding a database provided by zabbix.

As shown in fig. 3, the Zabbix call flow is a program call initiated by a user, compares a call result with existing information, and then initiates a data migration task to transmit metadata, bottom layer dump data, and the like, and the whole call flow is described as follows:

(1) and the task scheduling process acquires connection information from the zabbix Server scheduling platform, is connected to the source Oracle library, and acquires task information including information such as task creation, deletion, query, update, scheduling and the like.

(2) And opening an Open Api to call an XTTS subtask process, and opening a migration process.

(3) XTTS environment variable check, the check content includes: scripts, views, directories, etc. (4) validate synchronization content and transfer data from the source database to the target database. And the Zabbix Server initiates a migration instruction to the target end database according to the acquired information, starts migration work and the like.

(5) The verification environment of the source end database and the target end database comprises the following contents: the method comprises the steps of bash _ file, user, directory, proc, data volume (daily timing check, all automatic program detection), newly added tables of a source database, newly added users of the source database, newly added storage processes of the source database, old tables cleaning and the like.

(6) And (3) between the source end database and the target end database, opening dbms _ file _ transfer, converting the data file corresponding to the migration table space, creating a link, and ensuring the migration work.

(7) And (4) sorting the load table, establishing a new load table (combining a zabbix server task initiating layer and automatically establishing an external table) in a source database, starting data transmission of the load table through XTTS, compressing the data in gz, zip and other formats, and realizing multiple incremental backup and recovery.

(8) The source end database and the bottom layer data of the target end database are transmitted, the transmission magnitude reaches TB, and the data transmitted every day can reach more than 4T.

As shown in fig. 4, in this embodiment, Zabbix invokes result analysis, and this link is responsible for result set verification, and outputs a migration result, a migration optimization suggestion, a migration error report, an alarm transmission, and the like, and the overall process is as follows:

1) inputting information, acquiring information by a zbbix scheduling library: the method comprises the following steps of collecting various information such as a user \ a table \ a result set \ a calculation set \ metadata information \ service scheduling information and the like;

2) analyzing the difference value between a source end database and a target end database, XTTS migration rules, configuration information change, data change information and variable self-definition, defining variables in an execution script, and finishing variable assignment, parameter transmission and the like;

3) and (4) judging by combining the metadata calibration result, and finishing the migration work if the data of the source end database is the same as that of the target end database. And if the data results are different, combining the error information, adjusting the migration strategy and restarting the migration.

In this embodiment, after the preparation of the above links is completed, the deployment of the automatic migration method of the XTTS transmission table space based on zabbix is completed, and when data is migrated by a production system using Oracle as the production system, the method in this embodiment is adopted, so that the source database can be automatically migrated to the target database at the first time, and the continuity of the service is ensured.

4) The verification server is scheduled by a platform, and the type of the migration task is divided into manual starting and timing starting;

5) and the execution framework distributes the XTTS migration script, firstly detects the running state of the database, and if the running state is the preparation state, the execution framework issues the migration script to the database.

6) The Zabbix records an execution log, and after triggering the XTTS framework, the Zabbix can simultaneously start log service and record all logs of the XTTS framework operation, including a transfer calling process, feedback information and error reporting of the transfer process and the like.

7) The platform regularly scans logs in each migration link, and generated log information can be stored in the platform through the CSV, so that maintenance and retrieval of reasons for problems are facilitated

The principle, scene, problem, summary and the like of the 'automatic migration method of XTTS transmission table space based on zabbix' are explained through an all-dimensional, multi-level and multi-dimensional system, so that the migration requirement of an actual production system is met, and the 'three-in-one' high-efficiency, high-performance and high-intelligent mode is provided with an automatic alarm function, and the normal production capacity of the production system is automatically guaranteed. The method has the advantages that the traditional manual execution is replaced by the objectified graph dragging operation, so that the method is suitable for different script languages and version databases, the operation is simple, the modification and the maintenance are easy, and the script reusability rate is high.

The embodiment further provides an automatic transfer system of a transfer table space, as shown in fig. 5, including:

the XTTS subtask process module (namely the script scheduling platform in FIG. 3) is used for creating a related directory required in the XTTS migration process, performing full backup recovery from the source end database to the target end database in an XTTS form, and performing multiple incremental backup recovery; and (4) deriving metadata from the source end database, carrying out grouping loading on the target end database, and carrying out consistency check.

By using "CC law", from actual services, an implementation architecture of an automated migration system is established, a high-availability and high-performance scheduling platform is maintained, and finally, an "automated migration of XTTS transmission table space based on zabbix" overall architecture design is formed as shown in fig. 3, which includes:

application presentation layer: and backing up and calling a page, visually displaying the change, time, progress and backup data volume of the backup data, judging whether a backup program is scheduled or not, defining when and where to initiate a backup task, designing a backup task flow, skipping if no change exists, and otherwise initiating a new task strategy.

A data acquisition layer: the data backup monitoring system comprises a script scheduling platform (ZABBIX server) and an automatic verification server, wherein a data backup monitoring task and a backup process are defined, the script scheduling platform is responsible for issuing the backup script process, and the automatic verification server is responsible for verifying whether database data is changed or not, initiating a backup task or not and considering data consistency.

Device type layer: and the bottom layer divides different database versions and different table spaces, is connected to a target database at regular time in an XTTS + table space mode, discriminates records according to the acquired DUMP and LOG information, and then feeds back the changed data to the application presentation layer.

The synchronous task can be configured to run once, is closed after running is finished, and outputs the data migration progress to maintenance personnel, the synchronous task can be configured to be periodically and circularly executed, and when the synchronous task is found to be abnormal, an alarm is immediately sent to the maintenance personnel through task monitoring, and intervention processing is carried out.

In this embodiment, the processing method of Zabbix Server is the same as the above-mentioned transmission table space automatic migration method.

Specifically, the data calling links of the Zabbix Server scheduling platform comprise six major links of data migration environment information verification, preparation before migration, data file conversion, 1 st incremental backup and recovery, last incremental backup and recovery, metadata synchronization and the like.

In this embodiment, Zabbix is an enterprise-level open source solution providing distributed system monitoring and automation operation and maintenance functions based on a WEB interface. Zabbix can monitor various network and host parameters, ensure the safe operation of a server system, and provide a flexible notification mechanism to enable technicians to quickly locate/solve various problems. Zabbix components include Zabbix server and Zabbix agent. The zabbix server can provide functions of monitoring the state of a remote server/network, collecting data and the like by SNMP, zabbix agent, ping, port monitoring and the like, and can simultaneously monitor a plurality of servers. The invention provides a friendly and visual WEB access interface for technical personnel through zabbix software, and the technical personnel can complete a plurality of operations such as adding of a migration component, deleting of a database server, modifying of a monitoring item, perfecting of an environment variable and the like through simple mouse clicking on the WEB interface. Therefore, the method has the characteristics of intuition and easy operability.

The task processing template of the embodiment can be predefined. For example, an "XTTS tablespace" template may predefine the process flow of bulk adding databases and tablespaces. In the process, XTTS can be in communication connection with Zabbix software construction API, Zabbix derives a corresponding number of XTTS subprocesses according to the number of data migration subtasks, and each subprocess in the data migration process processes corresponding tasks in parallel. During processing, each XTTS sub-process calls an application program interface API provided by zabbix software to implement task processing of the corresponding XTTS. For example, in the present invention, each time a database migration task is added, the XTTS task is implemented by calling the API function database for adding a database provided by zabbix.

Specifically, as shown in fig. 3, the Zabbix call flow is a program call initiated by a user, compares a call result with existing information, and then initiates a data migration task to transmit metadata, bottom layer dump data, and the like. The whole calling flow is described as follows:

(3) XTTS environment variable check, the check content includes: scripts, views, directories and the like

(4) And confirming the synchronous content, and transmitting data from the source database to the target database. And the Zabbix Server initiates a migration instruction to the target end database according to the acquired information, starts migration work and the like.

(5) The source end database and the target end database check environment comprise the following contents: the method comprises the steps of bash _ file, user, directory, proc, data volume (daily timing check, all automatic program detection), newly added tables of a source database, newly added users of the source database, newly added storage processes of the source database, old tables cleaning and the like.

As shown in fig. 4, Zabbix invokes result analysis, and this link is responsible for result set verification, output of migration result, migration optimization suggestion, migration error reporting, alarm sending, and the like, and the overall process is as follows:

(1) inputting information, acquiring information by a zbbix scheduling library: the method comprises the following steps of collecting various information such as a user \ a table \ a result set \ a calculation set \ metadata information \ service scheduling information and the like;

(2) analyzing the difference value between a source end database and a target end database, XTTS migration rules, configuration information change, data change information and variable self-definition, defining variables in an execution script, and finishing variable assignment, parameter transmission and the like;

(3) and (4) judging by combining the metadata calibration result, and finishing the migration work if the data of the source end database is the same as that of the target end database. And if the data results are different, combining the error information, adjusting the migration strategy and restarting the migration.

(4) The verification server is scheduled by a platform, and the type of the migration task is divided into manual starting and timing starting;

(5) and the execution framework distributes the XTTS migration script, firstly detects the running state of the database, and if the running state is the preparation state, the execution framework issues the migration script to the database.

(6) The Zabbix records an execution log, and after triggering the XTTS framework, the Zabbix can simultaneously start log service and record all logs of the XTTS framework operation, including a transfer calling process, feedback information and error reporting of the transfer process and the like.

(7) The platform regularly scans logs in each migration link, and generated log information can be stored in the platform through the CSV, so that maintenance and retrieval of reasons for problems are facilitated

The principle, scene, problem, summary and the like of the 'automatic migration system based on zabbix XTTS transmission table space' solution are explained through an all-dimensional, multi-level and multi-dimensional system, so that the migration requirement of an actual production system is met, and the 'three-in-one' high-efficiency, high-performance and high-intelligent mode is provided with an automatic alarm function, and the normal production capacity of the production system is automatically guaranteed. The method has the advantages that the traditional manual execution is replaced by the objectified graph dragging operation, so that the method is suitable for different script languages and version databases, the operation is simple, the modification and the maintenance are easy, and the script reusability rate is high.

The scheme in the embodiment has the following advantages:

1. from a global perspective, automated construction for XTTS transport table space migration. Compared with the existing EXPDP, the invention not only aims at the transmission of individual information, but also provides an optimal scheme for automatic migration and operation and maintenance from the whole situation through careful algorithm analysis and actual data acquisition.

2. And the automatic early warning of the migration problem is realized. Objective changes of the database are captured by customizing a cyclic detection task and collecting and analyzing data, development changes of services are complied with, abnormal warning information of the migration platform is automatically sent in advance, and faults can be effectively avoided.

3. Efficient continuity of traffic is maintained. By the novel elastic computing platform method, the simultaneous use of managed services and non-managed services can be realized, the system is automatically switched, and the continuity of the service is guaranteed.

4. The dependence on human is greatly reduced. In the invention, the operation and maintenance of the automatic migration platform are realized by program scheduling without the need of the service knowledge familiar to maintenance personnel. Because early warning is realized, maintenance personnel have sufficient time management systems, and the technical level dependence on the maintenance personnel is greatly reduced.

5. The device is simple and easy to use. The multi-layer scheduling framework is creatively provided, the customization of Oracle migration scheduling, the operation of later information acquisition, analysis and the like can be completed through simple operation, the automatic completion is realized, and the requirement on the skills of users is low.

6. The invention reduces the migration cost. The method provides a more convenient and effective migration technology for the migration work of the existing database, reduces the downtime, saves the storage cost and avoids risks to a limited extent.

The embodiment further provides an automatic transfer device for a transmission table space, which includes: a processor (processor), a memory (memory), a communication Interface (Communications Interface), and a bus;

wherein,

the processor is configured to call program instructions in the memory to perform the method for automatically migrating a transmission table space provided in the embodiments of the methods described above, including:

The embodiment further provides an automatic transfer device for a transmission table space, which includes:

the memory stores program instructions executable by the processor, and the processor calls the program instructions to execute the automatic transfer table space migration method provided by the above method embodiments, for example, the method includes:

The present embodiment also discloses a computer program product comprising a computer program stored on a non-transitory computer-readable storage medium, the computer program comprising program instructions, which when executed by a computer, enable the computer to perform the method for automatic migration of a transmission table space provided by the above-mentioned method embodiments, for example, the method comprising:

The present embodiment also provides a non-transitory computer-readable storage medium, which stores computer instructions, where the computer instructions cause the computer to execute the method for automatically migrating a transmission table space provided in the foregoing method embodiments, for example, the method includes:

Those of ordinary skill in the art will understand that: all or part of the steps for implementing the method embodiments may be implemented by hardware related to program instructions, and the program may be stored in a computer readable storage medium, and when executed, the program performs the steps including the method embodiments; and the aforementioned storage medium includes: various media that can store program codes, such as ROM, RAM, magnetic or optical disks.

The above-described embodiments of the test equipment and the like of the display device are merely illustrative, wherein the units described as separate parts may or may not be physically separate, and the parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of the present embodiment. One of ordinary skill in the art can understand and implement it without inventive effort.

Through the above description of the embodiments, those skilled in the art will clearly understand that each embodiment can be implemented by software plus a necessary general hardware platform, and certainly can also be implemented by hardware. With this understanding in mind, the above-described technical solutions may be embodied in the form of a software product, which can be stored in a computer-readable storage medium such as ROM/RAM, magnetic disk, optical disk, etc., and includes instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the methods described in the embodiments or some parts of the embodiments.

Finally, the method of the present invention is only a preferred embodiment and is not intended to limit the scope of the present invention. Any modification, equivalent replacement, or improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for automatic migration of a transmission table space is characterized by comprising the following steps:

deriving metadata from a source end database, carrying out grouping loading on a target end database, and carrying out consistency check;

the obtaining of the table space of the source database to be migrated by connecting the source database through Zabbix specifically includes:

acquiring connection information from Zabbix, connecting the connection information to a source database, acquiring tablespace, version, user and total amount of a remote database to be migrated, and determining the size of migration data, wherein the migration data comprises the number of tablespaces, indexes, invalid objects, functions and sequences;

before the full backup recovery from the source end database to the target end database in the form of XTTS, the method further comprises the following steps:

2. The method for automatically migrating a transport table space according to claim 1, wherein the performing full backup restoration from a source-side database to a target-side database in an XTTS format further comprises:

3. The method of claim 1, wherein creating the dependency directory needed during the transport table space XTTS migration specifically comprises:

4. The method of claim 1, wherein the metadata comprises Oracle table metadata, Oracle file metadata, Oracle process metadata, and Oracle script metadata.

5. The method for automatically migrating transmission table space according to claim 1, wherein the performing consistency check specifically includes:

if the data are the same, the migration work is judged to be finished, and if the data are different, the migration error is reported.

6. A system for automated migration of a transport table space, comprising:

the XTTS subtask process module is used for creating a related directory required in an XTTS migration process, performing full backup recovery from a source end database to a target end database in an XTTS form, and performing multiple incremental backup recovery; deriving metadata from a source end database, carrying out grouping loading on a target end database, and carrying out consistency check;

the Zabbix Server scheduling platform is specifically configured to:

before the full backup recovery is performed on the source-end database to the target-end database in the form of the XTTS, the XTTS subtask process module is further configured to:

7. A transport table space auto-migration apparatus, comprising:

the memory stores program instructions executable by the processor, the processor invoking the program instructions to perform the method of any of claims 1 to 5.

8. A non-transitory computer-readable storage medium on which a computer program is stored, the computer program comprising program instructions that, when executed by a computer, cause the computer to perform the method of any of claims 1 to 5.