Oracle database Upgrade or data migration method research _oracle

First, the need for database upgrades
Database upgrades are a common problem for database administrators, if your application uses new features of the new version of the database, and if the database is overloaded, there can be no fundamental improvement through hardware and software adjustments; If you want to enhance the security of your database ; Another reason is that with the advent and maturation of the new version of the database, Oracle stops technical support for older versions of databases, upgrades to newer versions, continues to receive Oracle support, and can take advantage of new new versions of databases that improve system performance, robustness, scalability, and usability, and so on, Faced with these problems, you need to upgrade the database to be resolved. However, if your system is running stably with no major performance bottlenecks, do not upgrade the database easily, because there are many risks involved in upgrading a product database.

Second, database upgrade or data migration analysis
There are several situations in which a database upgrade or data migration can occur, typically in the following situations:
1. The lower version of the database is upgraded to a higher version of the database under the same host condition.
2. The database has a new patch level and needs to be installed with new patches.
3. Replaced the system host, such as changing from Windows to a UNIX system, and the database needs to be migrated.
4. Enables Cross-platform, cross database versions of migrations.
5. The 32-bit database is upgraded to 64 as a database.
6. The standard version of the database is upgraded to an enterprise version of the database.

Third, the development of a detailed database upgrade plan
Database Upgrade operations are at great risk, so it is necessary to develop detailed and thorough upgrades and test plans prior to the database upgrade, and to repeatedly validate and test the phases of the upgrade database to ensure that each phase of upgrading the product database is understandable, predictable, and successful. In general, the test plan for database upgrades should include the following types of tests:
1. Upgrade test (Upgrade testing)
2. Minimize the test (minimal testing).
3. Functional testing (functional testing).
4. Integration testing (integration testing).
5. Performance test (performance testing).
6. Capacity and load stress tests (Volume and stress testing).
7. Tests before and after a specific upgrade (specific pre-upgrade and Post-upgrade Tests).
8. Develop a backup plan before upgrading.
9. Iterative testing of upgrade steps (test the upgrade process).
10. Test of the upgraded database (test the upgraded DB).
These are the issues that need to be considered and tested before the database is upgraded, and you can refer to the upgrade documentation provided by the Oracle database, where the testing process for various requirements is not discussed in detail.

Iv. Database Upgrade Preparation
1. Before upgrading the database, you should familiarize yourself with the new features of the new database, such as: Table space, segment management, parameter changes, memory management, database flash characteristics, storage management, SQL optimization improvements, and so on.
2. Determine the upgrade path to upgrade to the new version database, the path to upgrade to 10GR2 depends on the current database's release number, and it is likely that you will not be able to upgrade from the current database version to the latest version, which is first upgraded to an intermediate version before being upgraded to the final version. For example: The current version is oracle9.1.0.1, first upgraded to oracle9.1.0.4, and then upgraded from oracle9.1.0.4 to ORACLE10GR2.
The following table lists the paths to upgrade to ORACLE10GR2 for various versions:

3. Select the upgrade method.
4. Select the Oracle home directory.
5. Do a database backup.

V. Discussion on various methods of database upgrade
There are a number of ways to implement database upgrades, typically: Database Upgrade Assistant (DB Upgrade Assistant), manual script upgrades, Exp/imp (EXDMP/IMDMP) tools, Table Space Transfer characteristics (transportable tablespace), table copy, materialized view (material ized view) and other technologies, before upgrading, first determine the upgrade path, according to the specific circumstances to choose the appropriate upgrade method, Test all phases of the upgrade process. In this paper, a hospital information system for example, the main database version has ORACLE8I,ORACLE9I,ORACLE10GR2, operating system platform mainly windows2000,oracle Unbreakable Linux as 4, The main implementation of oracle8i,oracle9i to ORACLE10GR2 upgrade process, oracle8i,oracle9i run on the Windows2000, ORACLE10GR2 mainly run in windows2000,oracle Unbreakable Linux as 42 platforms, I have used all of these methods to test the various upgrade processes. The upgrade process for these methods is described in detail below.

1. Manual script Upgrade
Manual script upgrades run some SQL scripts and tools on the command line, and manual upgrades provide more control over the upgrade process, but if you do not follow the strict upgrade steps and are prone to error during the upgrade process, it can only be upgraded across the database version between the same operating system platforms as Dbua. Not available for different OS platforms, and upgrades need to follow a strict upgrade path.
This Test fromoracle9.2.0.1 upgrade to oracle10.2.0.3, since oracle9.2.0.1 cannot be upgraded directly to oracle10.2.0.3, the oracle9.2.0.1 patch is first installed (patch number p3095277_9204_ WINNT) upgrade to oracle9.2.0.4, then oracle9.2.0.4 upgrade to oracle10.2.0.3. The following is an upgrade test process:
1. Installation of Oracle 10g R2 software.
2. The latest patch for Oracle 10g, I'm upgrading to 10.2.0.2 (patch number P4547817_10202_winnt) and 10.2.0.3 (patch number P5948242_10203_winnt) patches, For instructions on how to install an Oracle patch, you need to carefully read the Readme provided by each patch and follow the steps in the Readme to install it.
3. Run pre-upgrade information Tool. Analyze some of the actions to be done before upgrading to 10g, including:
Analysis of database version, log file size, tablespace size, server options, initialization parameters (new, degraded, deprecated), database components, Sysaux tablespace, cluster information, etc. Resolve the various issues that the script generates before you upgrade.
Start the DB to be upgraded, and use SYSDBA to run the Utlu102i.sql under 10g oracle_home/rdbms/admin to view the resulting log.
sql> SPOOL Info.log
sql> @utlu102i. sql
Sql> SPOOL off
Copy Oracle 9i pfile (D:oracleora92databaseinitzhangye.ora) to Oracle 10g Pfile (D:oracle10gdatabaseinitzhangye.ora), Check Info.log for Oracle 10g pfile modifications.
4. Run Oracle Net Configuration Assistant to generate 10g Listener.ora
5.rman Backup DB.
6.stop of all Oracle 9i services.
Remove Oracle 9i Services. C:> oradim-delete-sid Test
New Oracle 10g Services c:> oradim-new-sid orcl-maxusers 10-stamode auto-pfile D:oracle10gdatabaseinitzhangye.ora
7.UPGRADE option to start db. Sql> STARTUP UPGRADE
8. Create tablespace sysaux datafile ' d:oracleoradatazhangyesysaux01.dbf ' sysaux table space
SIZE 500M Reuse
EXTENT MANAGEMENT Local
SEGMENT Space MANAGEMENT AUTO
ONLINE;
9. Run the 10g Oracle_home/rdbms/admin/catupgrd.sql and run Utlu102s.sql to view the upgrade results.
sql> SPOOL Upgrade.log
sql> @catupgrd. sql
sql> @utlu102s. sql
Sql> SPOOL off
Check Upgrade.log, and if there is an error, run Catupgrd.sql again after handling the error.
10. Restart DB, compile invalid object.
sql> Shutdown Immediate
Sql> Startup Pfile=d:oracle10gdatabaseinitzhangye.ora
Sql> @d:oracle10grdbmsadminutlrp.sql
There are no invalid objects after compilation, the upgrade is complete.
sql> SELECT Count (*) from dba_objects WHERE status= ' INVALID ';

2, Database Upgrade assistant Dbua:
Dbua is a graphical tool that automates all of the procedures for manual upgrades.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.