Oracle Database Data Migration Method

As the upgrading of database management systems and operating system platforms accelerates, 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. When the development environment is switched to the running environment, the lower version database is switched to the High Version database, and the two different databases are switched, the data in the database (including the structure definition) needs to be transferred and run properly, this is the data migration in the database. For small and medium databases, such as * In Foxpro *. Dbf, 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. The general steps of data migration are extremely challenging for database administrators. If improper measures are taken, precious data resources may be lost. To achieve smooth migration of database data, well-planned and fully prepared, and completed according to certain steps. Design a data migration solution design a data migration solution mainly includes the following aspects: study 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. Based on the designed data migration scheme, a simulated data migration environment is created to simulate the actual environment without affecting the actual data, then, test the effect of data migration 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 simulation migration: test the data simulation migration based on the designed data migration test scheme, that is, check whether the data and application software are normal after the data simulation migration, including: data Consistency testing, application software functional testing, performance testing, data backup and recovery testing, etc. Prepare for data migration: After the data migration test is successful, you need to do the following before formally implementing the data migration: complete data backup, determine data migration solutions, 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 at www.2cto.com: test the data migration effect according to the data migration test scheme, and adjust the database parameters and performance after data migration to meet the needs of the actual application system after data migration. Transplant system application software: the application software of the actual application system is transplanted to the database system after data migration, and it runs properly. Formally run the application system: After the data migration is formally implemented 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, upgrade), or from one master version to another (that is, transplantation ), 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 a variety of tools for Oracle database data migration. We can also use the Data Pipeline Function of PowerBuilder to migrate Oracle database data. The earlier version of Oracle Database is Oracle8i. The following describes how to migrate database data to Oracle8i: Method 1: migrate the Utility (Migration Utility) MIG is a command line utility provided by Oracle for data migration between Oracle databases. The general steps for migrating data from an oracle 7 instance to an Oracle8i instance using MIG are as follows: Set the environment variable of the Oracle7 database, run the registration table editor (regedit.exe) and control panel, and set the Oracle7 main directory, Character Set, and executable file path, 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 environment variable of the oracle8idata database, run the registration table editor (regedit.exe) and control panel, and set the main directory, Character Set, and executable file path of Oracle8i. 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. Data migration is implemented. Internal user accounts connect to the Server Manager. The Oracle8i database is started in non-login mode. The alter database covert command generates a new control file, 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. Www.2cto.com Method 2: Oracle data Migration Assistant (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 Oracle7 databases of earlier versions to Oracle7 databases of later versions. The general procedure for migrating data from an Oracle database to an Oracle8i instance using the Oracle data migration assistant is to close the Oracle7 database, terminate all Oracle database services, 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. Run Oracle data migration assistant. On the Database Instance page, select the Database Instance to be migrated, enter the internal user account and password, 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 the Migration, you can view the Migration Report page to 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 option. When you use the Oracle Parallel Server option, run catparr. SQL. Method 3: Import/Export tool (Export/Import) when you need to migrate a database from a non-PC System Platform (such as a Unix System, the import/export method is a very useful policy. Of course, the data migration and export methods on the same system platform also apply. The general procedure for migrating Oracle7 Database on Unix to Oracle8i Database on Windows NT Database through import/export is to shut down the Oracle7 Database System of the Unix system in normal mode. Do not terminate the Database immediately or terminate the Database abnormally, then cold backup 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 execute it to the target database. Check the import log file and confirm that the database has been imported successfully. Method 4: SQL * Loader tool SQL * Loader can load data in ASCII files 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. General steps for migrating data to Oracle databases using SQL * Loader are as follows: Use the text file editor to create a control file. Run the sqlldr command on the doscommand line to load data files. Verify that the data is correctly loaded. Www.2cto.com Method 5: the SQL * plus copy command uses the SQL * PLUS COPY command to migrate the Oracle7 database to Oracle8i in a similar way as the import/export method. However, both databases must be online, 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 the Oracle7 database to the Oracle8i Database. Use the SQL * PLUS COPY command to COPY the table and table content from the Oracle7 database to the Oracle8i Database. Add views, synonyms, and stored procedures to the target database. Method 6: PowerBuilder Data Pipeline tool (Pipeline) PowerBuilder is a smart object provided by PowerBuilder for data conversion, it can migrate data 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 step is to start PowerBuilder. Click the "DBProfile" button on the toolbar to configure the connection between the source and target databases through ODBC or special 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 you want to transfer 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 Execute to run the defined data pipeline to complete data migration.
Author picluo