如何在Linux上升级Oracle数据库

How to Upgrade Oracle Database on Linux

Upgrading Oracle Database on Linux requires meticulous planning to avoid data loss or downtime. Below is a structured, step-by-step guide covering pre-upgrade checks, execution, and post-upgrade validation.

1. Pre-Upgrade Preparation

Preparation is critical to a successful upgrade. Follow these steps to minimize risks:

• Backup Everything: Use RMAN to create a full database backup (including archived logs) and back up configuration files (listener.ora, tnsnames.ora, init.ora, spfile). Verify backup integrity with RESTORE VERIFY ONLY.
• Check Compatibility: Review Oracle’s official upgrade documentation for your current and target versions to ensure compatibility with your Linux distribution, kernel version, and software dependencies (e.g., libaio, libaio-devel).
• Download Software: Obtain the Oracle Database software package for your target version from the official website. For major upgrades (e.g., 11g to 19c), you may need intermediate patch sets.
• Install Dependencies: Install required Linux packages using your distribution’s package manager (e.g., yum install libaio libaio-devel gcc glibc for Oracle 19c on CentOS).
• Prepare Environment Variables: Set ORACLE_BASE (e.g., /u01/app/oracle), ORACLE_HOME (e.g., /u01/app/oracle/product/19.0.0/dbhome_1), and ORACLE_SID (e.g., orcl) in the Oracle user’s shell profile (.bash_profile or .bashrc). Source the profile to apply changes.
• Run Pre-Upgrade Scripts: Use the utlu112i.sql script (for 11g to 12c) or equivalent for your upgrade path to check for issues. This script identifies incompatible parameters, missing patches, and objects that need modification. Address all warnings/errors before proceeding.

2. Stop Oracle Services

Stop all Oracle-related services to prevent data corruption during the upgrade:

• Stop the Listener: Run lsnrctl stop as the Oracle user to stop the Oracle Net listener.
• Stop the Database: Connect to the database as SYSDBA using SQL*Plus (sqlplus / as sysdba) and execute SHUTDOWN IMMEDIATE. Wait for the database to shut down completely.

3. Install/Upgrade Oracle Software

Choose one of two methods to upgrade the Oracle software:

Option A: Database Upgrade Assistant (DBUA) – Graphical Method

• Navigate to the new Oracle home directory: cd /u01/app/oracle/product/19.0.0/dbhome_1/.
• Run DBUA: ./dbua.
• Follow the on-screen wizard: Select the existing database, choose the upgrade option, and review settings. DBUA automates most steps, including running pre-upgrade checks and updating the database dictionary.

Option B: Manual Upgrade – Command-Line Method

• Run the upgrade script from the new Oracle home: sqlplus / as sysdba.
• Start the database in upgrade mode: STARTUP UPGRADE;.
• Execute the catalog upgrade script: @?/rdbms/admin/catupgrd.sql. This script updates data dictionary tables to the new version.
• Recompile invalid PL/SQL objects: @?/rdbms/admin/utlrp.sql. This ensures all stored procedures, functions, and triggers work with the new version.

4. Post-Upgrade Validation

Verify the upgrade was successful and the database is functional:

• Check Version: Connect to the database as SYSDBA and run SELECT * FROM v$version;. The output should show the target Oracle version.
• Review Alert Logs: Check $ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/alert_<instance_name>.log for errors or warnings during the upgrade.
• Validate Objects: Run SELECT COMP_NAME, VERSION, STATUS FROM dba_registry;. All components should have a STATUS of VALID.
• Test Functionality: Perform basic operations (e.g., create a table, insert data, run a query) to confirm the database works as expected.

5. Post-Upgrade Tasks

Complete these tasks to finalize the upgrade and optimize the database:

• Update Statistics: Gather optimizer statistics to ensure the query optimizer uses efficient execution plans: EXEC dbms_stats.gather_schema_stats(user); (for individual schemas) or EXEC dbms_stats.gather_database_stats; (for the entire database).
• Recompile Invalid Objects: Run @?/rdbms/admin/utlrp.sql again to recompile any remaining invalid PL/SQL objects.
• Adjust Parameters: Update initialization parameters (e.g., MEMORY_TARGET, SGA_TARGET) in the spfile or pfile to optimize performance for the new version.
• Upgrade DB Control (if used): If you use Oracle Enterprise Manager Database Control, run emca to upgrade the management repository to the new version.
• Test Applications: Verify all applications connecting to the database work correctly with the new version.
• Schedule a Backup: Perform a full backup of the upgraded database to protect against future failures.

Key Considerations

• Test in a Non-Production Environment: Always test the upgrade process in a replica of your production environment to identify and resolve issues before affecting live systems.
• Check Oracle Support Notes: Review My Oracle Support (MOS) for version-specific issues, bugs, and workarounds related to your upgrade path.
• Document Changes: Keep a record of all steps performed, including pre-upgrade checks, scripts executed, and post-upgrade validation results. This helps troubleshoot issues later.

By following these steps, you can ensure a smooth and successful Oracle Database upgrade on Linux.