Migrate databases using third-party tools-import, export, backup, and restore

Migrate databases using third-party tools-import, export, backup, and restore
To put it bluntly, the so-called database transfer means to copy the tables, data, and stored procedures on the database servers used for development, and then import them to another server. In general, there are three methods: Restore; SQL Server Integration Service and "backup/restore") for graphical interface operations, essentially the same as the first method, however, because of its simplicity and convenience, it is often despised by the old DBA. Third, it uses a third-party tool to create a "Script output/script execution" function for users, that is, the CREATE file of the database object is output as an SQL file, and then the SQL file can be used to CREATE a database at any time. This is available in PL/SQL and Object Browser. This article describes the last two methods based on the Object Browser, because the Chinese interface is easy to understand. Finally, let's briefly talk about DataPump. First, let's introduce the Oracle Import and Export function. To put it bluntly, the database object is output in the form of a binary file (. DMP), then parsed and input to another database environment. Oracle export 1: Go to the export page 2 from the menu. On the [select] page, you can specify the export object range. Full database: all objects of all users in the database are exported. Users specify: All object tables under a specified user specify: only the specified tables are exported. You can specify multiple table spaces: all objects in the specified tablespace are exported. In the file name column, enter the full path of the DMP file to be saved. You can also browse and select. The object to be exported is displayed in the target object, and all optional objects are listed using the Export method you specified. 3. You can set more advanced options in [Options], which is almost the same as the parameters required by oracle exp. [Connection user id] specifies the user used for export. It can be specified as the user currently connected or another user. Enter the user ID and password of another user. The [Buffer length] and [record length] can be set to the number of buffer bytes and the number of record bytes. The default value can be used. However, if many fields exist in some tables or large data (such as LOB type) is stored, you need to increase the value to avoid errors. [Database objects exported at the same time] You can specify whether to export permissions, indexes, data, and constraints at the same time. If the database transfers only the table structure but does not contain the data, the [data] will be deselected. [Types of incremental export]: None: export all objects completely: export all object Increments: export only the total number of objects that have changed since the last export execution: only the objects that have changed since the last execution of the export are exported, but the compressed DMP is included in the DMP. [Object analysis] You can set no statistical information generated during import: no statistical estimation: only a part of the sample is counted. Full: You can set the character set encoding to be used to export DMP in [NLS_LANG] for statistics. If this parameter is not added, it is the default value for the database. When [expansion space compression] is selected, the data stored in the extended space is integrated into the exported DMP [Table constraints, the data consistency will be checked during the export process [run in the background], and the export process will not be reflected in the interface. The export record allows you to define the log storage of the result. Use the tool to compile exp.exe 4 on the oracleclient. Click the [execute] button. Run the Export command. The execution result is displayed in the [result log. After successful Oracle import and export, a binary. DMP file is generated under the specified directory. database transfer uses this file to import it to the new database. First, connect to the new database. 1. Go to the import page from the menu. 2. On the [select] Page, set the [import method] full database: Import DMP as a whole database: import the specified user in the DMP file to the specified user currently connected to the database (click the [Overview] button on the right to list all users in the DMP: only import the specified table in the DMP file (click the [Overview] button on the right to list all the tables in the DMP). You can enter the absolute path of the DMP file in the directory, you can also find the DMP file through file browsing. The setting items on the [Options] Page are basically the same as those during export. 3. Click the [execute] button to start importing. The Database Import is completed after the normal operation. Script output is also a method for database backup and database transfer. The script mentioned here is the SQL statement. SQL (DDL) file for creating tables, stored procedures, and other database objects. Of course, it also includes the INSERT file for inserting data into the table. Any third-party tool can directly view, modify, and execute these. SQL files to facilitate the establishment of a database. 1. on the [Object List] Page, all database objects under the current connected user are listed, including tables, views, stored procedures, indexes, and so on. Select the object to be exported, right-click [Script output], and call out all the SQL statements to output to a file when the output interface [summarizes all the scripts Simultaneously] is selected. When not selected, each object generates a separate SQL file. When you select [Create a subdirectory corresponding to the type], the SQL files of all tables are generated in the Table folder. The SQL files of all views are generated in the View folder. Different object types are classified in different folders. If this parameter is not selected, all files are generated in the same folder. [Name table] is a user-friendly setting. It allows you to create a table in the database to indicate the matching relationship with the file name of the SQL script file. That is to say, the generated SQL script file name can be customized according to your own wishes and rules. [Output SCHEMA name] You can set whether to include SHEMA information in SQL. [When the TABLE script is output, it contains the index script from the TABLE] [when the TABLE script is output, data is also output (INSERT) statement] Here, you can set whether the insert statement is appended to the script file of the table, stored in the new "DATA" directory, or saved as "Table name_data. SQL file. In [Output EXTANT information], you can set the EXTENT information to be contained in the script file. [Project name included in output View Script] set whether to add field name after "create view View name AS" [FORCE option used in output VIEW Script] set whether to add FORCE to View Script settings (Oracle only) [Output object permission] whether to output the GRANT statement that grants the object permission at the end of each script file. [Do not export blank rows] Empty rows in the script can be automatically deleted during export. Many users often fail to execute scripts using SQL * Plus because of empty rows in the script. 2. Click the [start] button to export the script file. The script has been output to see how neat Our folder is! All kinds of objects are stored in _ DATA. SQL. Having these scripts is equivalent to having the entire database! These scripts can be understood as Database backups, but they are not hard backups that cannot be modified. Instead, you can view the soft backups you have modified at any time. Run the script to create a new database. 1. Go to the [script execution] page from the menu and select the folder or script file where the script is located from the directory on the right. The script file or folder to be executed can be moved to the left as a script execution object. 2. Click the [execute] button. In the confirmation dialog box, you can set whether to delete the script file after execution and whether to interrupt processing when an error occurs. In particular, many script files are often interrupted due to errors during execution, most of which are due to execution sequence. For example, when a VIEW is created, the TABLE referenced by the VIEW is not created, and an error occurs. In this case, if we select [delete a correctly completed SCRIPT from the list], we can execute the SCRIPT repeatedly until the list is cleared, which indicates that all scripts are correctly executed. How can I choose to use that method? We have introduced how to import and export data using DMP and script. In practice, someone will ask, "What method should I use? "Let's summarize the advantages and disadvantages of the two methods: binary or SQL text. Binary storage, which occupies a small amount of space. It is more convenient to view and edit SQL texts. If the database contains LOB data (SQLServer is BINARY), you can only use the DMP Import and Export method. However, Oracle's import and export functions do not guarantee backward compatibility. Therefore, problems often occur when the two database versions used for import and export are different. SQLServer has the same version compatibility problem. Errors often occur when "SQL Server2005 is transferred to 2008. Therefore, when the exported and imported database versions are inconsistent, we recommend that you use the Script output/script execution method. About DataPump from Oracle10g, in addition to the traditional exp/imp DMP file, the family has a new guy called DataPump (Data Pump ). It has been controversial since its birth, and it has been found that it is not essentially different from exp/imp. The main differences are as follows: 1. exp/imp is based on the Oracle client. DataPump is based on the Oracle server. At this point, it only solves the problem that the Oracle server does not need to install the client... As a result, the DataPum exported files can only be stored on the server. To solve this problem, Oracle enables it to support network transmission... 2. Although the data exported by DataPump is also. dmp, it is not fully compatible with the. dmp file exported by exp/imp. 3. DataPump supports parallel threads, suspension and restart, and space estimation... What makes the noble and powerful Oracle servers feel like. 4. If you need to find out an advantage, DataPump allows you to control the version of the imported and exported objects, solving the problem of the following incompatibility of the DMP version mentioned above. Because the basic usage of DataPump is almost the same as that of exp/imp, there is a problem with more parameters.