Primary and secondary database construction method, device and system

Abstract

A method, device and system for building a master and standby database can be used in the financial field or other fields. The method includes: obtaining the IP address of the master database server and the standby database server, determining the file size and number of the master database instance information, directory list and online redo log; creating a standby database redo log in the master database according to the file size and the number of files; generating a connection string and adding it to the descriptor configuration file of the master database server; obtaining a password file, a parameter file and a descriptor configuration file from the master database, and synchronizing them to the standby database according to the standby database server IP; creating a standby database directory list in the standby database according to the master database directory list, and storing each file under the standby database directory list, so that the standby database obtains the standby database redo log from the master database and copies the data. The present invention reduces the professional threshold for building master and standby databases and improves the accuracy, efficiency and quality of daily operation and maintenance.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method, device and system for building a primary and standby database. Background Art

[0002] Currently, the construction of primary and standby databases can usually be achieved through Data Guard. Data Guard is an enterprise-level database high availability solution launched by Oracle, which aims to achieve data consistency by continuously synchronizing the online log stream RedoStream between the primary and standby nodes. When the primary node fails, Data Guard can achieve data disaster recovery through rapid switching.

[0003] Currently, Data Guard is built manually. The traditional manual construction method is cumbersome and has relatively high labor and time costs, which are specifically reflected in:

[0004] 1. This involves professional knowledge in Oracle, SQL and other related fields, and only a full-time DBA (database administrator) can be competent. If the DBA cannot arrange time, this work can only be postponed.

[0005] 2. The construction process modifies many database parameters and most of the parameters need to be modified according to the primary and standby machine environments. The steps are complicated and there are too many human intervention factors, which can easily lead to human errors and cause the entire construction to fail.

[0006] 3. With one person as the unit, the traditional solution cannot achieve parallel construction and can only be executed sequentially, which seriously affects work efficiency and progress. Summary of the invention

[0007] In view of the problems existing in the prior art, the main purpose of the embodiments of the present invention is to provide a method, device and system for building a primary and backup database, which reduces the professional requirements of personnel building the primary and backup databases and greatly improves the accuracy, efficiency and quality of the construction.

[0008] In order to achieve the above object, an embodiment of the present invention provides a method for building a primary and standby database, the method comprising:

[0009] Obtain the IP address of the primary database server and the standby database server, and determine the instance information, directory list, and file size and number of online redo logs of the primary database based on the primary database server IP address;

[0010] Creating a standby database redo log in the primary database according to the file size and the number of files;

[0011] Generate a connection string according to the primary database server IP, the standby database server IP and instance information, and add the connection string to the descriptor configuration file of the primary database server;

[0012] Obtaining a password file, a parameter file, and a descriptor configuration file with a connection string added thereto from the primary database, and synchronizing the password file, the parameter file, and the descriptor configuration file with a connection string added thereto to the standby database according to the standby database server IP;

[0013] According to the primary database directory list, a standby database directory list identical to the primary database directory list is created in the standby database, and the password file, the parameter file, and the descriptor configuration file with the connection string added thereto are stored under the standby database directory list, so that the standby database obtains the standby database redo log from the primary database and copies the data in the primary database.

[0014] Optionally, in an embodiment of the present invention, the method includes:

[0015] Obtaining a primary database server password and a standby database server password, and encrypting the primary database server password and the standby database server password to obtain a primary database server encrypted password and a standby database server encrypted password;

[0016] A database table is generated according to the primary database server IP, the standby database server IP, the primary database server encryption password and the standby database server encryption password, an execution progress field is inserted into the database table, and the value of the execution progress field is initialized.

[0017] Optionally, in an embodiment of the present invention, synchronizing the password file, the parameter file, and the descriptor configuration file with the connection string added thereto to the standby database according to the standby database server IP comprises:

[0018] The standby database is accessed according to the standby database server encryption password and the standby database server IP, and the password file, parameter file and the descriptor configuration file with the connection string added thereto are synchronized to the standby database.

[0019] Optionally, in an embodiment of the present invention, the method further includes: after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database, updating the value of the execution progress field.

[0020] The embodiment of the present invention further provides a device for building a master-slave database, the device comprising:

[0021] An information acquisition module is used to obtain the IP address of the primary database server and the standby database server, and determine the instance information, directory list, and file size and number of online redo logs of the primary database based on the IP address of the primary database server;

[0022] A log creation module, used for creating a standby database redo log in the primary database according to the file size and the number of files;

[0023] A connection string module, used to generate a connection string according to the IP address of the primary database server, the IP address of the standby database server and the instance information, and add the connection string to the descriptor configuration file of the primary database server;

[0024] A file synchronization module, used to obtain a password file, a parameter file, and a descriptor configuration file with a connection string added thereto from the primary database, and synchronize the password file, the parameter file, and the descriptor configuration file with a connection string added thereto to the standby database according to the standby database server IP;

[0025] The connection establishment module is used to create a standby database directory list that is identical to the main database directory list in the standby database according to the main database directory list, and store the password file, parameter file and the descriptor configuration file with the connection string added thereto under the standby database directory list, so that the standby database obtains the standby database redo log from the main database and copies the data in the main database.

[0026] Optionally, in one embodiment of the present invention, the device further includes:

[0027] A password acquisition module is used to acquire the password of the primary database server and the password of the standby database server, and encrypt the password of the primary database server and the password of the standby database server to obtain the encrypted password of the primary database server and the encrypted password of the standby database server;

[0028] The database table module is used to generate a database table according to the primary database server IP, the standby database server IP, the primary database server encryption password and the standby database server encryption password, insert an execution progress field into the database table, and initialize the value of the execution progress field.

[0029] Optionally, in one embodiment of the present invention, the file synchronization module is also used to access the standby database according to the standby database server encryption password and the standby database server IP, and synchronize the password file, parameter file and descriptor configuration file with the connection string added to the standby database.

[0030] Optionally, in an embodiment of the present invention, the database table module is further configured to update the value of the execution progress field after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database.

[0031] The embodiment of the present invention also provides a master-slave database construction system, the system comprising: a master database, a standby database, and a master-slave database construction server connected to the master database and the standby database in communication;

[0032] The master-slave database construction server is used to obtain the master database server IP and the standby database server IP, and determine the instance information, directory list and file size and number of online redo logs of the master database according to the master database server IP; create the standby database redo log in the master database according to the file size and the number of files; generate a connection string according to the master database server IP, the standby database server IP and the instance information, and add the connection string to the descriptor configuration file of the master database server;

[0033] The master-slave database construction server is also used to obtain a password file, a parameter file, and a descriptor configuration file with a connection string added thereto from the master database, and synchronize the password file, the parameter file, and the descriptor configuration file with a connection string added thereto to the standby database according to the standby database server IP; create a standby database directory list identical to the master database directory list in the standby database according to the master database directory list, and store the password file, the parameter file, and the descriptor configuration file with a connection string added thereto under the standby database directory list;

[0034] The standby database establishes a connection with the primary database according to the descriptor configuration file to which the connection string is added, obtains the standby database redo log from the primary database, replicates data from the primary database, and stores the replicated data according to the standby database directory list.

[0035] Optionally, in one embodiment of the present invention, the master-standby database setup server is also used to obtain the master database server password and the standby database server password, and encrypt the master database server password and the standby database server password to obtain the master database server encrypted password and the standby database server encrypted password; generate a database table according to the master database server IP, the standby database server IP, the master database server encrypted password and the standby database server encrypted password, insert an execution progress field into the database table, and initialize the value of the execution progress field.

[0036] Optionally, in one embodiment of the present invention, the primary-standby database setup server is also used to access the standby database according to the standby database server encryption password and the standby database server IP, and synchronize the password file, parameter file and the descriptor configuration file with the connection string added to the standby database.

[0037] Optionally, in one embodiment of the present invention, the primary-standby database construction server is further used to update the value of the execution progress field after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database.

[0038] The present invention also provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the above method when executing the program.

[0039] The present invention also provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program for executing the above method.

[0040] The present invention lowers the professional threshold for building a primary and standby database through automatic building of the primary and standby databases. The building process is not only simple to operate and easy to use, but also supports parallel operation, greatly improving the accuracy, efficiency and quality of daily operation and maintenance work. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0042] Figure 1 This is a flow chart of a method for building a master-slave database according to an embodiment of the present invention;

[0043] Figure 2 A flowchart of creating a database table in an embodiment of the present invention;

[0044] Figure 3 A schematic diagram of the structure of a master-slave database construction system according to an embodiment of the present invention;

[0045] Figure 4 A schematic diagram of module connections of a master-slave database construction system in an embodiment of the present invention;

[0046] Figure 5 This is a workflow diagram of a system for building a primary and standby database in an embodiment of the present invention;

[0047] Figure 6A schematic diagram of the structure of a device for building a master-slave database according to an embodiment of the present invention;

[0048] Figure 7 It is a structural schematic diagram of a device for building a master-slave database in a specific embodiment of the present invention;

[0049] Figure 8 The figure is a schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0050] The embodiments of the present invention provide a method, device and system for establishing a master-standby database, which can be used in the financial field or other fields. It should be noted that the method, device and system for establishing a master-standby database of the present invention can be used in the financial field, and can also be used in any field other than the financial field. The application field of the method, device and system for establishing a master-standby database of the present invention is not limited.

[0051] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0052] like Figure 1 The flowchart of a method for building a master and standby database according to an embodiment of the present invention is shown. The execution subject of the method for building a master and standby database according to an embodiment of the present invention includes but is not limited to a computer, and specifically can be a master and standby database building server. The method shown in the figure includes:

[0053] Step S1, obtaining the IP address of the primary database server and the IP address of the standby database server, and determining the instance information, directory list, and file size and number of online redo logs of the primary database according to the IP address of the primary database server.

[0054] The server IP address of the primary database and the server IP address of the standby database are obtained. In addition, Oracle tools are installed in the servers of the primary and standby databases to complete the construction of the primary and standby databases (Data Guard). The primary database is accessed according to the obtained server IP address of the primary database, thereby determining the instance information, directory list, and file size and number of online redo logs in the primary database.

[0055] Specifically, the instance information includes the instance name, etc. The online redo log records all changes in the primary database. Further, the size and number of the primary database online redo log files are confirmed based on the online redo log view.

[0056] Step S2: creating a standby database redo log in the primary database according to the file size and the number of files.

[0057] In this case, refer to the official Oracle specification to create a standby database redo log in the primary database. Specifically, the number of standby database redo logs is equal to the number of primary database online redo log files plus one, and the size of the standby database redo log is the same as the file size of the primary database online redo log.

[0058] Step S3: Generate a connection string according to the IP address of the primary database server, the IP address of the standby database server and the instance information, and add the connection string to the descriptor configuration file of the primary database server.

[0059] The connection string is used to establish a connection between the primary and standby databases. The connection string contains information such as the primary database server IP, the standby database server IP, and instance information. Add the primary and standby database connection strings to the primary database server connection descriptor configuration file to prepare for subsequent connections.

[0060] Step S4, obtaining the password file, parameter file and descriptor configuration file with added connection string from the master database, and synchronizing the password file, parameter file and descriptor configuration file with added connection string to the standby database according to the standby database server IP.

[0061] The password file records the user password information, and the parameter file records the parameter information in the database, such as the storage space and storage address required for different types of data. The standby database is accessed according to the standby database server IP, and the password file, parameter file, and the descriptor configuration file with the connection string added are synchronized to the standby database. Specifically, the Python third-party module paramiko can be used to synchronize each file to the standby database, and each file synchronized to the standby database can be stored in the form of caching in a temporary cache area.

[0062] Step S5: Create a standby database directory list identical to the main database directory list in the standby database according to the main database directory list, and store the password file, parameter file, and descriptor configuration file with the connection string added thereto under the standby database directory list, so that the standby database obtains the standby database redo log from the main database and copies the data in the main database.

[0063] According to the main database directory list, a standby database directory list identical to the main database directory list is created in the standby database, and the password file, parameter file, and descriptor configuration file with added connection string are stored in the location corresponding to the standby database directory list.

[0064] Furthermore, the standby database establishes a connection with the primary database using the descriptor configuration file with the connection string added. The standby database obtains the standby database redo log from the primary database through the connection and executes the copy (duplicate) command to copy the data from the primary database. The copied data is stored in the corresponding location under the standby database directory list.

[0065] As an embodiment of the present invention, Figure 2 As shown, the method also includes:

[0066] Step S21, obtaining a master database server password and a standby database server password, and encrypting the master database server password and the standby database server password to obtain a master database server encrypted password and a standby database server encrypted password.

[0067] The main database server password and the standby database server password are used to control access rights. To ensure the security of the password, the main database server password and the standby database server password can be encrypted. Specifically, the main database server password and the standby database server password can be encrypted using the md5 algorithm to obtain the main database server encrypted password and the standby database server encrypted password.

[0068] Step S22: Generate a database table according to the primary database server IP, the standby database server IP, the primary database server encryption password and the standby database server encryption password, insert an execution progress field into the database table, and initialize the value of the execution progress field.

[0069] The IP address of the primary database server, the IP address of the standby database server, the encryption password of the primary database server, and the encryption password of the standby database server are stored, and a database table is created to store the IP address of the primary database server, the IP address of the standby database server, the encryption password of the primary database server, and the encryption password of the standby database server. An execution progress field is inserted into the database table, and the value of the execution progress field is initialized, that is, the value of the execution progress field is set to zero.

[0070] Furthermore, the execution progress field represents the current status of the primary and standby database construction process. Specifically, the construction process status can be recorded by updating the value of the execution progress field. For example, when the value of the execution progress field is zero, it means that it is a new construction task and preprocessing such as status check of the primary and standby databases is required; when the value of the execution progress field is 1, the preprocessing is completed and the construction begins; when the value of the execution progress field is 2, the construction of the primary and standby databases has been completed.

[0071] In this embodiment, according to the standby database server IP, synchronizing the password file, the parameter file, and the descriptor configuration file with the connection string added to the standby database includes: accessing the standby database according to the standby database server encryption password and the standby database server IP, and synchronizing the password file, the parameter file, and the descriptor configuration file with the connection string added to the standby database.

[0072] The standby database is accessed according to the standby database server IP, and the password file, parameter file, and descriptor configuration file with added connection string are synchronized to the standby database. Specifically, the Python third-party module paramiko can be used to synchronize each file to the standby database, and each file synchronized to the standby database can be stored in the form of caching in a temporary cache area.

[0073] In this embodiment, the method further includes: after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database, updating the value of the execution progress field.

[0074] Among them, the execution progress field represents the current status of the primary and standby database construction process. Specifically, the construction process status can be recorded by updating the value of the execution progress field. For example, when the value of the execution progress field is zero, it means that it is a new construction task, and preprocessing such as status check of the primary and standby databases is required; when the value of the execution progress field is 1, the preprocessing is completed and the construction begins; when the value of the execution progress field is 2, the construction of the primary and standby databases has been completed. Specifically, after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database, it means that the construction of the primary and standby databases has been completed, and the value of the execution progress field is updated to 2.

[0075] like Figure 3 The figure shows a schematic diagram of the structure of a master-slave database construction system according to an embodiment of the present invention. The system shown in the figure includes: a master database, a standby database, and a master-slave database construction server connected to the master database and the standby database in communication;

[0076] The master-slave database construction server is used to obtain the master database server IP and the standby database server IP, and determine the instance information, directory list, and file size and number of online redo logs of the master database based on the master database server IP; create the standby database redo log in the master database based on the file size and the number of files; generate a connection string based on the master database server IP, the standby database server IP and the instance information, and add the connection string to the descriptor configuration file of the master database server;

[0077] The master-slave database construction server is also used to obtain the password file, parameter file and descriptor configuration file with the connection string added from the master database, and synchronize the password file, parameter file and descriptor configuration file with the connection string added to the standby database; create a standby database directory list identical to the master database directory list in the standby database according to the standby database server IP and the master database directory list, and store the password file, parameter file and descriptor configuration file with the connection string added under the standby database directory list;

[0078] The standby database establishes a connection with the primary database according to the descriptor configuration file to which the connection string is added, obtains the standby database redo log from the primary database, copies data from the primary database, and stores the copied data according to the standby database directory list.

[0079] The primary and standby database building server also includes a prerequisite (Prereq_check) verification module, a DataGuard automatic building (Autodg_process) module, and a synchronization check (Autodg_check) module. Figure 4 As shown, the connection and working process diagram of each module.

[0080] Specifically, the process of automatically building the master and standby database modules includes:

[0081] 1. The online redo log records all changes in the database. To ensure the subsequent master-slave consistency, first adjust the master database forced log mode parameter Force Logging to Yes, and check the online redo log view v$log to confirm the online redo log file size and number n. Based on the above results and in combination with Oracle official specifications, create n+1 standby redo logs of the same size as online redo.

[0082] 2. According to the IP addresses, instance names and other information of the primary and standby servers, add the connection strings of the primary and standby databases in the primary server connection descriptor configuration file tnsnames.ora to prepare for subsequent connections.

[0083] 3. By default, the main database parameters do not meet the prerequisites for construction. Before the operation, the main database parameters are automatically modified through the shell: local archive path log_archive_dest_1, remote (standby database) archive path log_archive_dest_2, file automatic management parameter standby_file_management, etc.

[0084] 4. Automatically collect the main database tnsnames.ora, password file, parameter file pfile and other related files, and record the main database instance data files, redo logs, audits and other related directory lists for the standby database to use.

[0085] 5. Use the Python third-party module paramiko to synchronize the master database's tnsnames.ora, password file, and directory list file to the standby database.

[0086] 6. Read the directory list synchronized from the primary database and create necessary directories (data file directory, redo log directory, audit directory, etc.) in the standby database, and then grant read and write permissions so that DataGuard can successfully generate corresponding files. Refer to the primary database tnsnames.ora and add the primary and standby database connection strings to the standby database configuration file. At the same time, add the listener configuration file listener.ora to the standby database for subsequent network connection through listening. According to the standby database situation, modify some parameters of the primary database pfile (log_archive_dest_1, log_archive_dest_2, standby_file_management, etc.) as the standby database parameter file.

[0087] 7. Start the standby database in the nomount state and start monitoring. Then use the Oracle backup and recovery tool rman (Recovery Manager) to connect to the primary and standby databases and execute the duplicate command to complete the standby database setup. The duplicate execution process will start the standby database in the mount state. After the duplicate command is executed, start the application process MRP (managed recovery process) to apply the redo logs to the standby database. The setup is now complete.

[0088] As an embodiment of the present invention, the master-slave database construction server is also used to obtain the master database server password and the standby database server password, and encrypt the master database server password and the standby database server password to obtain the master database server encrypted password and the standby database server encrypted password; generate a database table according to the master database server IP, the standby database server IP, the master database server encrypted password and the standby database server encrypted password, insert an execution progress field into the database table, and initialize the value of the execution progress field.

[0089] The master database server password and the standby database server password are used to control access rights. To ensure the security of the passwords, the master and standby database server installation server can encrypt the master database server password and the standby database server password. Specifically, the master database server password and the standby database server password can be encrypted using the md5 algorithm to obtain the master database server encrypted password and the standby database server encrypted password.

[0090] Furthermore, the main and standby database construction server stores the main database server IP, the standby database server IP, the main database server encryption password and the standby database server encryption password, and creates a database table to store the main database server IP, the standby database server IP, the main database server encryption password and the standby database server encryption password. Insert an execution progress field into the database table, and initialize the value of the execution progress field, that is, set the value of the execution progress field to zero.

[0091] In this embodiment, the primary and standby database construction server is also used to access the standby database according to the standby database server encryption password and the standby database server IP, and synchronize the password file, parameter file and the descriptor configuration file with the connection string added to the standby database.

[0092] The primary and standby database construction servers access the standby database according to the standby database server IP, and synchronize the password file, parameter file, and the descriptor configuration file with the connection string added to the standby database. Specifically, the Python third-party module paramiko can be used to synchronize each file to the standby database, and each file synchronized to the standby database can be stored in the form of caching in a temporary cache area.

[0093] In this embodiment, the primary-standby database construction server is further configured to update the value of the execution progress field after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database.

[0094] Among them, the execution progress field represents the current status of the primary and standby database construction process. Specifically, the construction process status can be recorded by updating the value of the execution progress field. For example, when the value of the execution progress field is zero, it means that it is a new construction task, and preprocessing such as status check of the primary and standby databases is required; when the value of the execution progress field is 1, the preprocessing is completed and the construction begins; when the value of the execution progress field is 2, the construction of the primary and standby databases has been completed. Specifically, after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database, it means that the construction of the primary and standby databases has been completed, and the value of the execution progress field is updated to 2.

[0095] In addition, the prerequisite verification module in the primary and standby database construction server is used to check and confirm the primary and standby database environment before construction. Specifically, each server can be connected and logged in normally; the software and hardware environment is consistent, including but not limited to the Oracle version number (the version number view v$version needs to be checked); the file system, permissions and owner are the same; confirm that the primary database instance is running through the instance operation view v$instance; execute the archive log list command to ensure that the primary database has enabled archiving.

[0096] Furthermore, the synchronization check module in the primary and standby database construction server is used for verification work after the construction is completed to ensure the synchronization of the primary and standby databases. Specifically, the synchronization check module confirms that the redo log corresponding to the remote (i.e., standby database) archive path dest_id=2 in the primary database archive log view v$archived_log has been continuously applied (applied); or, after inserting a random number into the primary database test table, it confirms that the standby database has been synchronized within the specified time.

[0097] In addition, the prerequisite verification module, the master-slave database automatic construction module and the synchronization check module implement all functions through multiple (11 in this embodiment) shell scripts, and save the relevant information in the database table auto_dg_config to facilitate automatic program calls. The table structure example is shown in Table 1.

[0098] Table 1

[0099] module_name Module Name operation Step Name which_server On which role server the step is executed (primary or standby) command Shell script corresponding to the step filename The file names involved are used for subsequent audit tracking or master-slave database synchronization

[0100] The information exchange and sequence control between the above shell scripts are uniformly scheduled by the Python program, so that the construction of a single set of master and standby database environments can be completed. In order to fully improve the efficiency of operation and maintenance and liberate labor. Based on the above automation, the system uses the MySQL database trigger event-driven mechanism to start multiple processes in parallel, and can complete the automatic construction of multiple environments at the same time. The system workflow diagram is as follows: Figure 5 As shown, specifically including:

[0101] 1. The user logs in to the browser interface of the automatic system construction of the primary and standby database and enters the core elements such as the primary and standby server IP, user name, and password.

[0102] 2. The system performs basic checks and judgments on the input elements. For example, if the IP address meets the specifications, the user name and password have been entered, and the verification passes, it proceeds to the next step.

[0103] 3. To prevent information from being maliciously tampered with or intercepted, the system completes the encryption of the primary and backup server passwords using the md5 algorithm to obtain the encrypted password authentication_string, and then inserts it into the database table (dataguard_env) together with the standby server IP, user name, and execution progress field PROCESS_FLAG (initial value is 0). dataguard_env records the progress of DataGuard construction, and examples of related fields are shown in Table 2.

[0104] Table 2

[0105] primary_ip Primary server IP address primary_username Primary Server User primary_authentication_string Primary server password encrypted string standby_ip Backup server IP address standby_username Backup server user standby_authentication_string Encrypted string of the backup server password PROCESS_FLAG Execution Progress Field

[0106] Among them, the values ​​of PROCESS_FLA are: 0 is the initial value; 1 means that the Prereq_check module has been executed normally; 2 means that the Autodg_process module has been executed normally; 3 means that the Autodg_check module has been executed normally and the entire construction project has been completed normally; 100 means that the module is being executed.

[0107] Furthermore, if multiple PROCESS_FLAG fields equal to 0, 1, or 2 appear in the dataguard_env table at the same time, multiple related processes will be started for concurrent operation.

[0108] 4. In order to achieve multi-concurrency in the construction work, the trigger feature is used to monitor the database table, and relevant operations are performed according to the specified rules for the addition, deletion and modification of records or fields. The trigger name of this system is: dataguard_env_trigger, which monitors the PROCESS_FLAG field of the dataguard_env table, and calls the operating system script through the sys_eval function (open source project mysqludf) according to the different code values ​​of the field. The specific process is as follows:

[0109] 1) Insert a new record with PROCESS_FLAG=0, call the prerequisite verification module Prereq_check through sys_eval to generate a new process, and modify PROCESS_FLAG=100 to go to step 5. If multiple records are inserted at the same time (representing the need to build multiple sets of primary and standby databases at the same time), the above actions are executed multiple times at the same time.

[0110] 2) The execution progress field PROCESS_FLAG is changed from 0 to 1, and the automatic building module Autodg_process is called through sys_eval to create a new process, and PROCESS_FLAG is changed to 100 to go to step 6. As above, if multiple records are modified at the same time, this operation will be performed multiple times.

[0111] 3) The execution progress field PROCESS_FLAG is changed from 1 to 2, indicating that the main work of the build has been completed. The synchronization status of the master and standby machines is checked by calling the sys_eval synchronization check module Autodg_check. Similarly, if there are multiple records where the field PROCESS_FLAG is changed from 1 to 2, multiple check processes will be generated at the same time. After this step is completed, PROCESS_FLAG is changed to 3, and the entire build project is completed (prerequisite verification, automatic build, and synchronization check). The program will feedback to the front-end page according to different situations.

[0112] 4) The execution progress field PROCESS_FLAG is changed from 2 to 3, or from other values ​​to 100, and the system does not need to generate additional processes.

[0113] 5. This step mainly completes the prerequisite verification work based on module_name = 'Prereq_check' in auto_dg_config, and after completion, changes the corresponding record PROCESS_FLAG field from 0 to 1, so that dataguard_env_trigger can detect the modification and call the automatic construction module Autodg_process.

[0114] 6. This step mainly completes the automatic construction work based on module_name = 'Autodg_process' in auto_dg_config, and after completion, changes the corresponding record PROCESS_FLAG field from 1 to 2, so that dataguard_env_trigger can detect the modification and call the synchronization check module Autodg_check.

[0115] The present invention overcomes the shortcomings of traditional manual construction of master and standby databases, such as high professionalism, excessive manual intervention, easy errors, and inability to run in parallel, and lowers the learning threshold for building master and standby databases. Even if the operator does not understand SQL operations and does not understand the obscure principles of master and standby databases, a set of master and standby databases can be easily built. The entire construction process is not only simple to operate and easy to use, but also supports parallel operations, greatly improving the accuracy, efficiency and quality of daily operation and maintenance work.

[0116] like Figure 6 The figure is a schematic diagram of the structure of a device for building a master-slave database according to an embodiment of the present invention. The device shown in the figure includes:

[0117] The information acquisition module 10 is used to obtain the IP address of the primary database server and the IP address of the standby database server, and determine the instance information, directory list, and file size and number of online redo logs of the primary database according to the IP address of the primary database server.

[0118] The server IP address of the primary database and the server IP address of the standby database are obtained. In addition, Oracle tools are installed in the servers of the primary and standby databases to complete the construction of the primary and standby databases (Data Guard). The primary database is accessed according to the obtained server IP address of the primary database, thereby determining the instance information, directory list, and file size and number of online redo logs in the primary database.

[0119] Specifically, the instance information includes the instance name, etc. The online redo log records all changes in the primary database. Further, the size and number of the primary database online redo log files are confirmed based on the online redo log view.

[0120] The log creation module 20 is used to create a standby database redo log in the primary database according to the file size and the number of files.

[0121] In this case, refer to the official Oracle specification to create a standby database redo log in the primary database. Specifically, the number of standby database redo logs is equal to the number of primary database online redo log files plus one, and the size of the standby database redo log is the same as the file size of the primary database online redo log.

[0122] The connection string module 30 is used to generate a connection string according to the IP address of the primary database server, the IP address of the standby database server and the instance information, and add the connection string to the descriptor configuration file of the primary database server.

[0123] The connection string is used to establish a connection between the primary and standby databases. The connection string contains information such as the primary database server IP, the standby database server IP, and instance information. Add the primary and standby database connection strings to the primary database server connection descriptor configuration file to prepare for subsequent connections.

[0124] The file synchronization module 40 is used to obtain the password file, parameter file and descriptor configuration file with added connection string from the main database, and synchronize the password file, parameter file and descriptor configuration file with added connection string to the standby database according to the standby database server IP.

[0125] The password file records the user password information, and the parameter file records the parameter information in the database, such as the storage space and storage address required for different types of data. The standby database is accessed according to the standby database server IP, and the password file, parameter file, and the descriptor configuration file with the connection string added are synchronized to the standby database. Specifically, the Python third-party module paramiko can be used to synchronize each file to the standby database, and each file synchronized to the standby database can be stored in the form of caching in a temporary cache area.

[0126] The connection establishment module 50 is used to create a standby database directory list that is identical to the main database directory list in the standby database according to the main database directory list, and store the password file, parameter file and the descriptor configuration file with the connection string added thereto under the standby database directory list, so that the standby database obtains the standby database redo log from the main database and copies the data in the main database.

[0127] According to the main database directory list, a standby database directory list identical to the main database directory list is created in the standby database, and the password file, parameter file, and descriptor configuration file with added connection string are stored in the location corresponding to the standby database directory list.

[0128] Furthermore, the standby database establishes a connection with the primary database using the descriptor configuration file with the connection string added. The standby database obtains the standby database redo log from the primary database through the connection and executes the copy (duplicate) command to copy the data from the primary database. The copied data is stored in the corresponding location under the standby database directory list.

[0129] As an embodiment of the present invention, Figure 7 As shown, the device also includes:

[0130] The password acquisition module 60 is used to acquire the password of the primary database server and the password of the standby database server, and encrypt the password of the primary database server and the password of the standby database server to obtain the encrypted password of the primary database server and the encrypted password of the standby database server;

[0131] The database table module 70 is used to generate a database table according to the primary database server IP, the standby database server IP, the primary database server encryption password and the standby database server encryption password, insert an execution progress field into the database table, and initialize the value of the execution progress field.

[0132] In this embodiment, the file synchronization module is also used to access the standby database according to the standby database server encryption password and the standby database server IP, and synchronize the password file, parameter file and descriptor configuration file with added connection string to the standby database.

[0133] In this embodiment, the database table module is further used to update the value of the execution progress field after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database.

[0134] Based on the same application concept as the above-mentioned method for building a master-standby database, the present invention also provides the above-mentioned device for building a master-standby database. Since the principle of solving the problem by the device for building a master-standby database is similar to that of the method for building a master-standby database, the implementation of the device for building a master-standby database can refer to the implementation of the method for building a master-standby database, and the repeated parts will not be repeated.

[0135] The present invention automatically builds the primary and standby databases, thereby lowering the professional threshold for building the primary and standby databases. The building process is not only simple to operate and easy to use, but also supports parallel operations, greatly improving the accuracy, efficiency and quality of daily operation and maintenance work.

[0136] The present invention also provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the above method when executing the program.

[0137] The present invention also provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program for executing the above method.

[0138] like Figure 8 As shown, the electronic device 600 may further include: a communication module 110, an input unit 120, an audio processing unit 130, a display 160, and a power supply 170. It is worth noting that the electronic device 600 does not necessarily have to include Figure 8 In addition, the electronic device 600 may also include Figure 8 For components not shown, reference may be made to the prior art.

[0139] like Figure 8 As shown, the central processor 100 is sometimes also referred to as a controller or an operation control, and may include a microprocessor or other processor devices and / or logic devices. The central processor 100 receives inputs and controls the operations of various components of the electronic device 600.

[0140] The memory 140 may be, for example, one or more of a cache, a flash memory, a hard drive, a removable medium, a volatile memory, a non-volatile memory or other suitable devices. The above-mentioned information related to the failure may be stored, and a program for executing the relevant information may also be stored. The CPU 100 may execute the program stored in the memory 140 to implement information storage or processing.

[0141] The input unit 120 provides input to the CPU 100. The input unit 120 is, for example, a key or a touch input device. The power supply 170 is used to provide power to the electronic device 600. The display 160 is used to display display objects such as images and texts. The display may be, for example, an LCD display, but is not limited thereto.

[0142] The memory 140 may be a solid-state memory, such as a read-only memory (ROM), a random access memory (RAM), a SIM card, etc. It may also be a memory that saves information even when the power is off, can be selectively erased, and is provided with more data, examples of which are sometimes referred to as EPROMs, etc. The memory 140 may also be some other type of device. The memory 140 includes a buffer memory 141 (sometimes referred to as a buffer). The memory 140 may include an application / function storage unit 142, which is used to store application programs and function programs or processes for executing the operation of the electronic device 600 through the central processor 100.

[0143] The memory 140 may also include a data storage unit 143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 144 of the memory 140 may include various drivers for communication functions of the electronic device and / or for executing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0144] The communication module 110 is a transmitter / receiver 110 that transmits and receives signals via an antenna 111. The communication module (transmitter / receiver) 110 is coupled to the central processor 100 to provide input signals and receive output signals, which may be the same as the case of a conventional mobile communication terminal.

[0145] Based on different communication technologies, multiple communication modules 110 may be provided in the same electronic device, such as a cellular network module, a Bluetooth module and / or a wireless LAN module. The communication module (transmitter / receiver) 110 is also coupled to a speaker 131 and a microphone 132 via an audio processor 130 to provide an audio output via the speaker 131 and receive an audio input from the microphone 132, thereby realizing a common telecommunication function. The audio processor 130 may include any suitable buffer, decoder, amplifier, etc. In addition, the audio processor 130 is also coupled to the central processor 100, so that the sound can be recorded on the local machine through the microphone 132, and the sound stored on the local machine can be played through the speaker 131.

[0146] Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0147] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0148] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture including an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0149] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process in the computer or other programmable device. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0150] The present invention uses specific embodiments to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only used to help understand the method of the present invention and its core idea. At the same time, for those skilled in the art, according to the idea of ​​the present invention, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on the present invention.

Claims

1. A method for building a master and standby database, characterized in that: The method comprises: Obtain the IP address of the primary database server and the standby database server, and determine the instance information, directory list, and file size and number of online redo logs of the primary database based on the primary database server IP address; Creating a standby database redo log in the primary database according to the file size and the number of files; Generate a connection string according to the primary database server IP, the standby database server IP and instance information, and add the connection string to the descriptor configuration file of the primary database server; Obtaining a password file, a parameter file, and a descriptor configuration file with a connection string added thereto from the primary database, and synchronizing the password file, the parameter file, and the descriptor configuration file with a connection string added thereto to the standby database according to the standby database server IP; According to the primary database directory list, a standby database directory list identical to the primary database directory list is created in the standby database, and the password file, the parameter file, and the descriptor configuration file with the connection string added thereto are stored under the standby database directory list, so that the standby database obtains the standby database redo log from the primary database and copies the data in the primary database; Wherein, the method further comprises: Obtaining a primary database server password and a standby database server password, and encrypting the primary database server password and the standby database server password to obtain a primary database server encrypted password and a standby database server encrypted password; Wherein, synchronizing the password file, the parameter file, and the descriptor configuration file with the connection string added to the standby database according to the standby database server IP includes: The standby database is accessed according to the standby database server encryption password and the standby database server IP, and the password file, parameter file and the descriptor configuration file with the connection string added thereto are synchronized to the standby database.

2. The method according to claim 1, characterized in that The method further comprises: A database table is generated according to the primary database server IP, the standby database server IP, the primary database server encryption password and the standby database server encryption password, an execution progress field is inserted into the database table, and the value of the execution progress field is initialized.

3. The method according to claim 2, characterized in that The method further includes: after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database, updating the value of the execution progress field.

4. A device for building a master and standby database, characterized in that: The device comprises: An information acquisition module is used to obtain the IP address of the primary database server and the standby database server, and determine the instance information, directory list, and file size and number of online redo logs of the primary database based on the IP address of the primary database server; A log creation module, used for creating a standby database redo log in the primary database according to the file size and the number of files; A connection string module, used to generate a connection string according to the IP address of the primary database server, the IP address of the standby database server and the instance information, and add the connection string to the descriptor configuration file of the primary database server; A file synchronization module, used to obtain a password file, a parameter file, and a descriptor configuration file with a connection string added thereto from the primary database, and synchronize the password file, the parameter file, and the descriptor configuration file with a connection string added thereto to the standby database according to the standby database server IP; A connection establishment module, configured to create a standby database directory list identical to the main database directory list in the standby database according to the main database directory list, and store the password file, the parameter file and the descriptor configuration file with the connection string added thereto under the standby database directory list, so that the standby database obtains the standby database redo log from the main database and copies the data in the main database; Wherein, the device further comprises: A password acquisition module is used to acquire the password of the primary database server and the password of the standby database server, and encrypt the password of the primary database server and the password of the standby database server to obtain the encrypted password of the primary database server and the encrypted password of the standby database server; The file synchronization module is further used to access the standby database according to the standby database server encryption password and the standby database server IP, and synchronize the password file, parameter file and the descriptor configuration file with the connection string added to the standby database.

5. The device according to claim 4, characterized in that The device also includes: The database table module is used to generate a database table according to the primary database server IP, the standby database server IP, the primary database server encryption password and the standby database server encryption password, insert an execution progress field into the database table, and initialize the value of the execution progress field.

6. The device according to claim 5, characterized in that The database table module is also used to update the value of the execution progress field after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database.

7. A system for building a master and standby database, characterized in that: The system comprises: a primary database, a standby database, and a primary and standby database construction server connected to the primary database and the standby database in communication; The master-slave database construction server is used to obtain the master database server IP and the standby database server IP, and determine the instance information, directory list and file size and number of online redo logs of the master database according to the master database server IP; create the standby database redo log in the master database according to the file size and the number of files; generate a connection string according to the master database server IP, the standby database server IP and the instance information, and add the connection string to the descriptor configuration file of the master database server; The master-slave database construction server is also used to obtain a password file, a parameter file, and a descriptor configuration file with a connection string added thereto from the master database, and synchronize the password file, the parameter file, and the descriptor configuration file with a connection string added thereto to the standby database according to the standby database server IP; create a standby database directory list identical to the master database directory list in the standby database according to the master database directory list, and store the password file, the parameter file, and the descriptor configuration file with a connection string added thereto under the standby database directory list; The standby database establishes a connection with the primary database according to the descriptor configuration file to which the connection string is added, obtains the standby database redo log from the primary database, replicates data from the primary database, and stores the replicated data according to the standby database directory list; The master-slave database server is further used to obtain the master database server password and the standby database server password, and encrypt the master database server password and the standby database server password to obtain the master database server encrypted password and the standby database server encrypted password; The main standby database construction server is also used to access the standby database according to the standby database server encryption password and the standby database server IP, and synchronize the password file, parameter file and the descriptor configuration file with the connection string added to the standby database.

8. The system according to claim 7, characterized in that The master-slave database construction server is also used to generate a database table according to the master database server IP, the standby database server IP, the master database server encryption password and the standby database server encryption password, insert an execution progress field into the database table, and initialize the value of the execution progress field.

9. The system according to claim 8, characterized in that The primary-standby database construction server is further used to update the value of the execution progress field after the standby database obtains the standby database redo log from the primary database and copies the data in the primary database.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the method according to any one of claims 1 to 3 is implemented.

11. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program for executing the method according to any one of claims 1 to 3.

Images