Hey there! Migrating from Oracle 11g to 12c is a common upgrade path, and I’ve walked through this with several teams before. Let’s break down your questions clearly:

1. Complete Migration Workflow #

Follow this step-by-step process to ensure a smooth upgrade:

Pre-Migration Preparation #

• Full Backup: Take a complete cold or hot backup of your 11g database (use RMAN for reliability: RMAN> BACKUP DATABASE PLUS ARCHIVELOG;). Store it in a secure location—you’ll need this if anything goes wrong.
• System Requirements Check: Verify your server meets 12c’s OS, memory, and storage requirements (e.g., 12c needs at least 2GB of RAM for a basic install, more for production).
• Install 12c Software: Set up the Oracle 12c database software (don’t create a new database yet—we’ll upgrade the existing 11g instance).
• Pre-Upgrade Health Check: Run the official pre-upgrade script utlu121s.sql (located in $ORACLE_HOME/rdbms/admin on the 12c server) against your 11g database. It’ll flag issues like invalid objects, deprecated features, and configuration conflicts.

Choose Your Migration Method #

Pick the method that fits your environment:

• DBUA (Database Upgrade Assistant): The easiest option for most cases. It’s a graphical tool that guides you through pre-checks, upgrade execution, and post-upgrade tasks. Great if you’re not comfortable with command-line tools.
• Manual Upgrade: For advanced users, use SQL scripts like catctl.pl and catupgrd.sql. Gives you more control but requires careful planning.
• Data Pump Export/Import: Ideal if you need to move data to a new server, change character sets, or clean up unused data during migration. Export from 11g with expdp, then import into 12c with impdp.
• RMAN Upgrade: Fastest for same-platform migrations. Use RMAN> UPGRADE DATABASE to upgrade the 11g database directly to 12c.

Execute the Migration #

• Shut Down Applications: Stop all client applications connecting to the 11g database to avoid data inconsistencies.
• Run the Upgrade: Follow the steps for your chosen method (e.g., launch DBUA and follow the prompts). Monitor the alert log (alert_<SID>.log) for errors during the process.
• Post-Upgrade Compilation: After the upgrade completes, run utlrp.sql (in $ORACLE_HOME/rdbms/admin) to recompile invalid PL/SQL objects, views, and triggers. This is critical—many objects will be marked invalid after the upgrade.

Post-Migration Validation & Tuning #

• Check Alert Log: Review the alert log for any post-upgrade errors or warnings.
• Validate Business Functionality: Run test cases for all critical applications—verify that SQL queries, reports, and transactions work as expected.
• Performance Testing: Compare query execution times between 11g and 12c. 12c’s adaptive features (like adaptive cursor sharing) might improve performance, but some queries may need tuning.
• Update Configuration: Adjust initialization parameters (e.g., MEMORY_TARGET, PROCESSES) to match 12c’s capabilities. If migrating to a CDB (Container Database), set up PDBs and adjust user permissions.

2. Code-Level Adjustments for SQL/Procedures Compatibility #

Even though 12c is backward-compatible, you’ll need to check these areas to avoid issues:

• Replace Deprecated Features:
  • Swap old USERENV calls with SYS_CONTEXT (e.g., replace USERENV('CLIENT_INFO') with SYS_CONTEXT('USERENV', 'CLIENT_INFO')—the old syntax works but is deprecated).
  • Migrate from DBMS_JOB to DBMS_SCHEDULER (12c supports DBMS_JOB but DBMS_SCHEDULER is more robust and feature-rich).
  • Avoid the old outer join syntax ((+))—use ANSI-standard LEFT OUTER JOIN instead for better compatibility and readability.
• Fix Invalid Objects:
  • After migration, use SELECT object_name, object_type FROM dba_objects WHERE status = 'INVALID'; to find invalid objects. Recompile them manually with ALTER PROCEDURE <name> COMPILE; or use UTL_RECOMP.recomp_serial().
• Adjust for CDB/PDB Architecture (if using):
  • If migrating to a CDB, ensure your code doesn’t reference CDB-level objects directly from a PDB. Use container-specific synonyms or adjust permissions to access shared objects.
• Check Data Type Limits:
  • 12c extends VARCHAR2, NVARCHAR2, and RAW to 32767 bytes (up from 4000 in 11g). If your code has hard-coded length checks (e.g., SUBSTR(col, 1, 4000)), adjust them to leverage the new limits if needed.
• Validate PL/SQL Constructs:
  • 12c introduces stricter checks for some PL/SQL syntax. For example, ensure your stored procedures don’t use deprecated parameter modes or outdated exception handling.

3. Key 12c Syntax & Feature Changes Not Present in 11g #

12c adds several powerful features that you can start using right away:

• Multi-Tenant (CDB/PDB) Syntax:
  • Create pluggable databases: CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb_admin IDENTIFIED BY password FILE_NAME_CONVERT = ('/old/path/', '/new/path/');
  • Switch between containers: ALTER SESSION SET CONTAINER = pdb1;
• Enhanced SQL Syntax:
  • Row Limiting: Replace ROWNUM with FETCH FIRST n ROWS ONLY and OFFSET for cleaner pagination:

    SELECT emp_id, emp_name, salary
    FROM employees
    ORDER BY salary DESC
    OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
    

  • WITH Clause in DML: Use CTEs (Common Table Expressions) directly in INSERT, UPDATE, and DELETE statements:

    INSERT INTO employee_stats (dept_id, avg_salary)
    WITH dept_avg AS (
      SELECT dept_id, AVG(salary) avg_sal
      FROM employees
      GROUP BY dept_id
    )
    SELECT dept_id, avg_sal FROM dept_avg;
    

• Identity Columns:
  • Auto-generate primary key values without sequences/triggers:

    CREATE TABLE orders (
      order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      order_date DATE DEFAULT SYSDATE
    );
    

• Native JSON Support:
  • Use functions like JSON_VALUE, JSON_QUERY, and JSON_TABLE to query JSON data directly:

    SELECT JSON_VALUE(customer_data, '$.name') AS cust_name
    FROM customers
    WHERE JSON_VALUE(customer_data, '$.country') = 'USA';
    

• PL/SQL Enhancements:
  • Inline PL/SQL compilation for better performance: ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE = NATIVE;
  • Default parameter values in stored procedures: CREATE PROCEDURE calculate_bonus(emp_id INT, bonus_pct NUMBER DEFAULT 0.10) IS ...

内容的提问来源于stack exchange，提问作者Ganapathy