Oracle Database Data Migration Method

Oracle Database Data Migration Method

---- With the upgrading of database management systems and operating system platforms accelerating, database administrators often need to migrate data between two different databases or between two different system platforms. This article describes the general steps of database data migration and several methods for migrating data to the Oracle8i Database, and compares their advantages and disadvantages.

---- Data in the database (including structure definition) during conversion from the development environment to the running environment, from a lower-version database to a higher-version database, and between two different data libraries) to be transferred and run properly, this is the data migration in the database. For small and medium databases, such as *. DBF in FoxPro, this migration is very simple and generally can be completed through simple copy. However, for large database systems, such as Oracle databases, data migration is not that simple. It requires certain technologies and experience to be completed in steps as planned.

General steps of data migration

---- For database administrators, database data migration is extremely challenging. If improper measures are taken, precious data resources will be at risk of loss. To achieve smooth database data migration, well-planned and fully prepared, and completed according to certain steps.

---- Design a data migration Solution

---- The design of the data migration solution mainly involves the following aspects: Researching data related to data migration, you can also query related content on the website, evaluate and select the software and hardware platform for data migration, select the data migration method, select the data backup and recovery policies, and design the data migration and testing solutions.

---- Simulate data migration

---- Create a simulated data migration environment based on the designed data migration solution. It can simulate the actual environment without affecting the actual data, and then test the data migration effect in the data simulation migration environment. The simulated data should be backed up according to the backup policy before the simulated data migration, so that the recovery test can be conducted according to the recovery policy after the data migration.

---- Test data simulated migration

---- Test the simulated data migration based on the designed data migration test scheme, that is, check whether the data and application software are normal after the simulated data migration, including: data Consistency testing, application software functional testing, performance testing, data backup and recovery testing, etc.

---- Prepare for data migration

---- After the data simulation migration test is successful, you need to do the following before formally implementing the data migration: complete data backup, determine the data migration solution, install and configure software and hardware.

---- Formally implement data migration

---- Formally implement data migration according to the determined data migration solution.

---- Test the data migration Effect

---- Test the data migration effect according to the data migration test scheme, and adjust the database parameters and performance after the data migration to meet the needs of the actual application system after the data migration.

---- Porting System Application Software

---- Transplant the application software of the actual application system to the database system after data migration and make it run normally.

---- Officially run the application system

---- After successful data migration and the database parameters and performance meet the requirements, the application system can be formally run and put into practical use.

Migrate data to Oracle8i

---- Oracle database data migration can be migrated from one version to another (that is, upgrading), or from one master version to another (that is, porting ), for example, migrating data from oracle7 to oracle8 can be migrated from one system platform to another, for example, from a UNIX system to a Windows NT System, or from a non-Oracle database to an Oracle database, for example, migrating data from ms SQL Server to Oracle database. Before migrating Oracle database data, you need to understand the following issues: requirements for database systems, db_block_size requirements for Oracle databases, character set requirements for Oracle, SQL * Net version requirements, and operating system platform requirements.

---- The Oracle database system provides multiple tools for Oracle database data migration. We can also use the PowerBuilder Data Pipeline Function to migrate Oracle database data. The latest version of Oracle Database is Oracle8i. The following describes how to migrate database data to Oracle8i:

---- Method 1: portingProgram(Migration utility)

---- MiG is a command line utility provided by Oracle for data migration between Oracle databases. The general steps for migrating data from Oracle 7 to Oracle8i using MIG are as follows:

---- ☆Set the environment variable of the oracle7 data warehouse, run the registration table editor (regedit.exe) and control panel, set the main directory, Character Set, and executable file path of oracle7, set oracle_sid = oracle7_sid at the DOS command prompt, and shut down the oracle7 database normally.

---- ☆Run the MiG application of Oracle8i to generate a conversion file for database conversion.

---- ☆Terminate the oracle7 Database Service from the Windows NT control panel and use the instance manager to delete the oracle7 database service.

---- ☆Set the oracle8idata database environment variable, run the registration table editor (regedit.exe) and control panel, and set the Oracle8i main directory, Character Set, and executable file path. The oradim command uses oracle7_sid and init. create the Oracle8i service in the ora file, rename the Database Control file, and edit init. ora file, set compatible to 8.1.5.0.0; Delete init. parameters that are no longer used in the ora file; Set oracle_sid = oracle8_sid at the doscommand prompt.

---- ☆Implement data migration, connect to the Server Manager using internal user accounts; start the Oracle8i Database in non-login mode; generate a new control file using the alter database covert command, the file header is converted to the Oracle8i Database file format. The alter database open resetlog command is used to open the database and reset the log file. The spool migscripts. log command to create the output target file, which saves the running results of the preceding Command Script file; run u0703030. SQL, and run catrep when you use the advanced database copy function option. SQL. When the Oracle Parallel Server option is used, run catparr. SQL; results are output by the spool out command, and the database and backup database are closed.

---- ☆Check the migscripts. Out spool file and confirm that the script file is successfully executed. If the script file is not successfully executed, return to the script file and check for possible problems.

---- Method 2: Oracle data migration assistant)

---- ORACLE data migration assistant is used to port oracle7 database to oracle8 database. Upgrade the earlier version of oracle8 database to the new version of oracle8 database. Oracle data migration assistant is not used to upgrade an earlier version of oracle7 to a later version of oracle7. The general steps for migrating data from an Oracle database to an Oracle8i instance using the Oracle data migration assistant are as follows:

---- ☆Shut down the oracle7 database, terminate all Oracle database services, edit the init. ora file, set compatible to 8.1.5.0.0, and delete the parameters that are no longer used in the init. ora file.

---- ☆Run ORACLE data migration assistant. On the database instance page, select the database instance to be migrated, enter the password of the internal user account, and provide init. ora file location; on the moving the database page, specify a new location for database migration; if the user has not backed up, on the backup the database page, allow the user to back up the database; on the character set page, specify the character set used by the transplanted database.

---- ☆Start the transplantation process. The time required for porting depends on the system memory capacity, hard disk speed, CPU speed, and other factors.

---- ☆After transplantation, you can view the Migration Report page and check whether the migration is successful. If the migration fails, the database system should be returned to the backed up oracle7 database.

---- ☆Edit the init. ora file and add the parameters that will be used in the Oracle8i Database. Delete the old control file used during the transplantation.

---- ☆Run catrep. SQL when you use the advanced database replication feature option; run catparr. SQL when you use the Oracle Parallel Server option.

---- Method 3: import/export tool (export/import)

---- When a database needs to be migrated from a non-PC System Platform (such as a UNIX system), the wizard/export method is a very useful policy, of course, the data migration and export methods on the same system platform also apply. The general steps for migrating oracle7 database on UNIX to Oracle8i Database on Windows NT database through import/export are as follows:

---- ☆Shut down the oracle7 Database System of the UNIX system in normal mode. Do not terminate it immediately or terminate it abnormally, and then perform cold backup of the database. Open the database and use the export utility to completely export the database.

---- ☆Install Oracle8i Database software on Windows NT and create a startup database.

---- ☆Use FTP to transfer files to a Windows NT computer. The binary transmission mode of FTP must be used. Otherwise, an error may occur in the Read/export dump file.

---- ☆Confirm that the target database is running and set oracle_sid = oracle8i_sid at the doscommand prompt.

---- ☆Use the export dump file to completely export the data to the target database.

---- ☆View the import log file and confirm that the Database Import is successful.

---- Method 4: SQL * loader Tool

---- SQL * loader can load the data in the ASCII file into the Oracle database. The information in the ASCII file can come from different data sources, such as other Oracle databases, non-Oracle databases, and workbooks.

---- The general steps for migrating data to an Oracle database using SQL * loader are as follows:

---- ☆Use the text file editor to create a control file.

---- ☆Use the sqlldr command on the doscommand line to load data files.

---- ☆Verify whether the data is correctly loaded.

---- Method 5: SQL * Plus Copy command

---- Use the SQL * Plus Copy command to migrate the oracle7 database to Oracle8i in a similar way as the import/export method. However, the two databases must be online at this time. The general steps are as follows:

---- ☆First install the Oracle8i Database and create a database as the target database.

---- ☆Add the required tablespace and users to the target database; install the required SQL * net2 or net8 software and configure the alias used to connect to the database.

---- ☆Set oracle_sid = oracle7_sid at the doscommand prompt.

---- ☆Use an internal user account to connect to SQL * Plus.

---- ☆Create a database link from oracle7 database to Oracle8i Database; Use the SQL * Plus Copy command to copy the table and table content from oracle7 database to Oracle8i Database.

---- ☆Add views, synonyms, and stored procedures to the target database.

---- Method 6: PowerBuilder Data Pipeline tool (pipeline)

---- PowerBuilder Data Pipeline tool is a smart object provided by PowerBuilder for data conversion. It can achieve data migration between different types of databases in graphical mode without SQL programming. The operation of the PowerBuilder Data Pipeline is implemented in the graphical mode, so it is relatively simple. The general steps are as follows:

---- ☆Start PowerBuilder.

---- ☆Click the "dbprofile" button on the toolbar to configure the connection between the source and target databases through ODBC or dedicated interfaces, and connect to both the source and target databases.

---- ☆Click the "Pipeline" button on the toolbar to create a new data pipeline and determine the source and target databases.

---- ☆Select the table for data transmission and select the columns to be transmitted as needed.

---- ☆Select the data migration method, create-addtable (create a new table in the target database and copy the data), replace-drop/addtable (delete the old table and copy the data), refresh-deleterows (delete existing data in the table and copy the data), append-insertrows (add new data based on existing data), update-update/insertrows (if the key value of the newly added data is the same as the key value of a row of the existing data, the existing data will be overwritten; otherwise, the new data will be inserted ).

---- ☆Click the "execute" button to run the defined data pipeline to complete data migration.

Comparison of several data migration methods

---- The above describes several ORACLE data migration methods, which have their own characteristics. The following table provides their application scenarios and comparative analysis of their advantages and disadvantages for your reference only.

Reference from:

Comment (0) Reference reading (119) Circle editing printing Prize