Shulou(Shulou.com)06/01 Report--

This article is to share with you about the Oracle database upgrade or data migration method is, the editor thinks it is very practical, so share it with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

This paper discusses the pre-upgrade preparation, upgrade process and post-upgrade test and adjustment of oracle database upgrade, and tests various upgrade methods on a variety of operating system platforms.

The necessity of database upgrade

Database upgrade is a problem that database administrators often face, if your application wants to use the new version of the database

New features; if the database is overloaded and cannot be fundamentally improved through hardware and software adjustment; if you want to change the operating system platform; if you want to enhance the security of the database Another reason is that with the emergence and maturity of the new version of the database, oracle stopped the technical support for the old version of the database, upgraded to a high version, and can continue to get the support of oracle, and can also take advantage of the new and special features of the new version of the database, which can improve the performance, robustness, expansibility and availability of the system, and so on. In the face of these problems, we need to upgrade the database to solve. However, if your system is stable and does not have major performance bottlenecks, do not easily upgrade the database, because upgrading a product database will face a lot of risks.

Analysis of database upgrade or data migration

Database upgrade or data migration can encounter a variety of situations, generally in the following situations:

1. Upgrade from low-version database to high-version database under the same host conditions.

2. The database has a new patch level and a new patch needs to be installed.

3. The host of the system has been replaced, such as changing from windows to unix system, the database needs to be migrated.

4. Achieve cross-platform, cross-database version migration.

5. Upgrade the 32-bit database to 64 for the database.

6. Upgrade the standard version of the database to the enterprise version.

Develop a detailed database upgrade plan

The database upgrade operation will face great risks, so it is necessary to make a detailed and thorough upgrade and test plan before the database upgrade, and to repeatedly verify and test the various stages of the database upgrade. only in this way can we ensure that the various stages of upgrading the product database are understandable, predictable and successful. In general, the test plan for a database upgrade should include the following test types:

1. Upgrade Test (Upgrade testing)

2. Minimize testing (Minimal testing).

3. Functional testing (Functional testing).

4. Integration testing (Integration testing).

5. Performance testing (Performance testing).

6. Capacity and load stress test (Volume and load stress testing).

7. Testing before and after a specific upgrade (Specific Pre-Upgrade and Post-Upgrade Tests).

8. Develop a backup plan before the upgrade.

9. Repeated testing of upgrade steps (test the upgrade process).

10. Test the upgraded database (test the upgraded database).

These are the issues that need to be considered and tested before the database upgrade. You can refer to the upgrade documents provided by the oracle database, and the testing process of various requirements will not be discussed in detail here.

Prepare for database upgrade:

1. Before upgrading the database, you should first be familiar with various new features of the new database, such as table space, segment management, parameter changes, memory management, database flashback characteristics, storage management, sql optimization improvement and so on.

2. Determine the upgrade path to the new version database. The upgrade path to 10gr2 depends on the release number (release number) of the current database. It is likely that you will not be able to upgrade from the current database version to the latest version. In this case, you will first upgrade to an intermediate version, and then 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 to oracle10gr2 for various versions:

Current version

Upgrade path

7.3.3 and lower

7.3.4

8.0.3

8.0.4

8.0.5

8.0.6

8.1.5

8.1.6

Direct upgrade is not supported. You need to upgrade to an intermediate version before upgrading to oracle10g:

7.3.3 (or lower)-> 7.3.4-> 8.1.7.4-> 10.2

7.3.4-> 8.1.7.4-> 10.2

8.0. 8.1.

8.1.7.4

9.0.1.4

9.2.0.4

10.1.0.2

Can be upgraded directly to oracle10gr2

3. Select the upgrade method.

4. Select the oracle home directory.

5. Make a good database backup.

Discussion on various methods of database upgrade

There are many methods to realize database upgrade, such as database upgrade assistant (database upgrade assistant), manual script upgrade, exp/imp (exdmp/imdmp) tool, tablespace transfer feature (transportable tablespace), table copy, materialized view (material ized view) and so on. Before upgrading, first determine the upgrade path, choose the appropriate upgrade method according to the specific situation, and test each stage of the upgrade process. This paper takes the information system of Junwei No.1 Hospital as an example, the main database version involved is oracle8i,oracle9i,oracle10gr2, and the operating system platform is mainly windows2000,oracle unbreakable linux as 4, which mainly realizes the upgrade process from oracle8i,oracle9i to oracle10gr2. Oracle8i,oracle9i runs on windows2000 and oracle10gr2 mainly runs on windows2000,oracle unbreakable linux as 4. I use the above methods to test all kinds of upgrade process. The upgrade process for 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, but if it does not follow 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, cannot be used for different os platforms, and the upgrade needs to follow a strict upgrade path.

This test is upgraded from oracle9.2.0.1 to oracle10.2.0.3. Since oracle9.2.0.1 cannot be upgraded directly to oracle10.2.0.3, first install a patch (patch number p3095277_9204_WINNT) for oracle9.2.0.1 to upgrade to oracle9.2.0.4, and then upgrade oracle9.2.0.4 to oracle10.2.0.3. The following is the test process for the upgrade:

1. Install the software for oracle 10g R2.

two。 With the latest patch of oracle 10g, I am upgrading to patch 10.2.0.2 (patch number p4547817_10202_WINNT) and 10.2.0.3 (patch number p5948242_10203_WINNT). On how to install the oracle patch, you need to carefully read the readme provided by each patch and follow the readme steps to install it.

3. Run Pre-Upgrade Information Tool. Analyze some actions to be done before upgrading to 10g, including:

Analysis of database version, log file size, tablespace size, server options, initialization parameters (new, degraded, obsolete), database components, sysaux tablespaces, cluster information, etc. Various problems generated by this script should be resolved before upgrading.

Start the DB to be upgraded, run the utlu102i.sql under the 10g ORACLE_HOME/rdbms/admin with SYSDBA, and view the resulting log.

SQL > SPOOL info.log

SQL > @ utlu102i.sql

SQL > SPOOL OFF

Pfile (D:oracleora92databaseINITzhangye.ORA) of copy oracle 9i to pfile (D:oracle10gdatabaseINITzhangye.ORA) of oracle10g, check info.log, pfile modification of oracle10g.

4. Run Oracle Net Configuration Assistant to generate 10g listener.ora

5.rman backup DB.

6.stop all oracle 9i services.

Delete oracle 9i services. C: > ORADIM-DELETE-SID test

Create a new oracle10g service C: > ORADIM-NEW-SID orcl-MAXUSERS 10-STAMODE AUTO-PFILE D:oracle10gdatabaseINITzhangye.ORA

The 7.UPGRADE option starts DB. SQL > STARTUP UPGRADE

8. Create the SYSAUX tablespace CREATE TABLESPACE sysaux DATAFILE 'Dereoradatazhangyesaux01.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 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, handle the error and run catupgrd.sql.

10. Restart DB to compile invalid objects.

SQL > shutdown immediate

SQL > startup pfile=D:oracle10gdatabaseINITzhangye.ORA

SQL > @ D:oracle10gRDBMSADMINutlrp.sql

There are no invalid objects after compilation, and 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 upgrade processes, automating the following tasks:

one. Run Pre Upgrade Information Utility, make some automatic adjustments to tablespaces, redo logs, etc.

two. Check

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.