Research on Oracle database upgrade or data migration

Time:2020-10-15

1、 The necessity of database upgrade
Database upgrade is a problem that database administrators often have to face. If your application wants to use the new features of the new version of the database; if the database is overloaded, and there is no fundamental improvement through software and hardware adjustment; if you want to change the operating system platform; if you want to enhance the security of the database; there is another reason that with the emergence of the new version of the database and Mature, Oracle stopped technical support for the old version of the database, upgrade to a higher version, can continue to get support from Oracle, can also take advantage of the new version of the database, can improve the performance of the system, robustness, scalability and availability, etc., in the face of these problems, we need to upgrade the database to solve. However, if your system runs stably and there is no big performance bottleneck, it is still not easy to upgrade the database, because upgrading a product database will face many risks

2、 Analysis of database upgrade or data migration
Database upgrade or data migration will encounter many situations, including the following situations:
1. Upgrade the low version database to the high version database under the same host condition.
2. The database has a new patch level and needs to be installed with a new patch.
3. If the system host is changed from windows to UNIX, the database needs to be migrated.
4. Realize cross platform and cross database version migration.
5. Upgrade 32-bit database to 64 database.
6. Upgrade the standard database to the enterprise database.

3、 Make detailed database upgrade plan
Database upgrade operation will face huge risks, so it is necessary to make detailed and thorough upgrade and test plan before database upgrade, and repeatedly verify and test each stage of upgrading database, so as to ensure that each stage of upgrading product database is understandable, predictable and successful. Generally speaking, the test plan for database upgrade should include the following test types:
1. Upgrade testing
2. Minimum testing.
3. Functional testing.
4. Integration testing.
5. Performance testing.
6. Volume and load stress testing.
7. Specific pre upgrade and post upgrade tests.
8. Make backup plan before upgrade.
9. Test the upgrade process.
10. Test the upgraded database.
The above are the issues that need to be considered and tested before upgrading the database. For details, please refer to the upgrade document provided by Oracle database. The test process of various requirements is not discussed in detail here.

4、 Database upgrade preparation
1. Before upgrading the database, you should be familiar with the new features of the new database, such as the management of table space and segment, the change of parameters, the management of memory, the flashback feature of database, the management of storage, and the improvement of SQL optimization.
2. Determine the upgrade path to upgrade to the new version database. The upgrade path to 10gr2 depends on the release number of the current database. It is likely that the current database version cannot be upgraded to the latest version. In this case, upgrade to an intermediate version first, and then upgrade to the final version. For example: the current version is oracle9.1.0.1. First upgrade to oracle9.1.0.4, and then upgrade from oracle9.1.0.4 to Oracle10gR2.
The following table lists the paths to upgrade various versions to Oracle10gR2:

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

5、 Discussion on various methods of database upgrade
There are many ways to upgrade the database, such as database upgrade assistant, Manual script upgrade, exp / imp (exdmp / imdmp) tool, transportable tablespace, table copy, materialized view and other technologies, before upgrading, first determine the upgrade path, select the appropriate upgrade method according to the specific situation, and test each stage of the upgrade process. This paper takes the No.1 Military Medical Hospital information system as an example, the database versions involved are mainly Oracle8i, Oracle9i, Oracle10gR2, and the operating system platforms mainly include Windows 2000, Oracle unbreakable Linux as 4, etc., which mainly realizes the upgrade process from Oracle8i, Oracle9i to Oracle10gR2, Oracle8i, Oracle9i run on Windows2000, Oracle10gR2 mainly runs on Windows2000, Oracle I used the above methods to test all kinds of upgrade process. The upgrade process of these methods is described in detail below.

1. Manual script upgrade
Manual script upgrade runs some SQL scripts and tools on the command line. Manual upgrade provides more control over the upgrade process. However, if you do not follow the strict upgrade steps, it is easy to make mistakes in the upgrade process. Like dbua, it can only be upgraded across database versions between the same operating system platforms. It cannot be used for different OS platforms, and the upgrade needs to follow strict rules Upgrade path of.
This test starts fromUpgrade from oracle9.2.0.1 to oracle10.2.0.3Because oracle9.2.0.1 cannot be directly upgraded to oracle10.2.0.3, first install the patch p3095277 for oracle9.2.0.1_ 9204_ WinNT) to oracle9.2.0.4, then oracle9.2.0.4 to oracle10.2.0.3. The following is the test process of the upgrade:
1. Install Oracle 10gr2 software.
2. Apply the latest patch of Oracle 10g, I upgrade to 10.2.0.2 (patch number p4547817)_ 10202_ WinNT) and 10.2.0.3 (patch number p5948242)_ 10203_ For how to install Oracle patches, you need to read the readme provided by each patch carefully and install them according to the steps of readme.
3. Run the pre upgrade information tool. Analyze some operations to be done before upgrading to 10g, including:
Database version, log file size, table space size, server options, initialization parameters (new, degraded, obsolete), database components, sysaux table space, cluster information, etc. Before upgrading, we need to solve various problems of the script generation.
Start the DB to be upgraded and use SYSDBA to run 10g oracle_ Under home / RDBMS / Admin, view the generated log
SQL> SPOOL info.log
SQL> @utlu102i.sql
SQL> SPOOL OFF

Pfile of copy Oracle 9i (D: oracleora92 databaseINITzhangye.ORA )Pfile to Oracle 10g (D: oracle10 gdatabaseINITzhangye.ORA ), check info.log To modify the pfile of Oracle 10g
4. Run Oracle net configuration assistant to generate 10g listener.ora
5. RMAN backup dB
6. Stop all Oracle 9i services
Delete Oracle 9i service. C: > oradim – delete – Sid test
Create Oracle 10g Service C: > oradim – New – Sid orcl – maxusers 10 – stamode auto – pfile D: oracle10 gdatabaseINITzhangye.ORA
7. Upgrade option starts dB. SQL > startup upgrade
8. Create a sysaux tablespace sysaux datafile’d: oracleoradatazhangyesysaux01. DBF ‘
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
9. Run 10g oracle_ HOME/rdbms/admin/ catupgrd.sql And then run utlu102s. SQL to see the upgrade results
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> @utlu102s.sql
SQL> SPOOL OFF
inspect upgrade.log If there is an error, handle the error and run it again catupgrd.sql .
10. Restart dB and compile invalid objects
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 above manual upgrades.

The above is the whole content of this article, I hope to help you in your study, and I hope you can support developeppaer more.

Recommended Today

Use of Android WebView (super detailed usage)

1.1 overview of WebView Android WebView is a special view on the Android platform. It can be used to display web pages. This WebView class can be used to display only one online web page in the app. Of course, it can also be used to develop browsers. The internal implementation of WebView uses WebKit […]