Oracle新手求助：基于DBUA跨服务器从11g迁移至12C并升级的方法

Hey there! Since you’ve already nailed migrating a single table with SQL Developer, let’s level up to a full cross-server migration from Oracle 11g to 12c, then walk through upgrading that 12c instance. I’ll break this down into clear, actionable steps tailored for someone new to Oracle admin work:

I’ll cover two reliable methods—pick the one that fits your environment best.

Method 1: Oracle Data Pump (EXPDP/IMPDP) – Recommended for Most Cases #

This is flexible, easy to troubleshoot, and great if you don’t need an exact replica of the source server’s setup.

• Prep the Source (11g) Server

  1. Create a directory for your dump files (replace the path with a folder you control):

     CREATE OR REPLACE DIRECTORY dump_dir AS '/opt/oracle/dumps';
     

  2. Grant read/write access to the user you’ll use for exporting:

     GRANT READ, WRITE ON DIRECTORY dump_dir TO your_export_user;
     

  3. Double-check the OS folder exists and the Oracle user has permissions to write to it.

• Run the Full Database Export
  Use nohup to avoid losing progress if your session drops:

  nohup expdp your_export_user/your_password@11g_service full=Y directory=dump_dir dumpfile=full_11g_dump.dmp logfile=expdp_log.log &
  

  Tip: If you only need specific schemas (not the entire DB), replace full=Y with schemas=SCHEMA1,SCHEMA2 to save time and disk space.

• Transfer the Dump File to the Target (12c) Server
  Use scp or a file transfer tool to copy the dump file over:

  scp /opt/oracle/dumps/full_11g_dump.dmp oracle@12c-server:/opt/oracle/dumps/
  

• Prep the Target (12c) Server

  1. Create the same directory object and grant permissions:

     CREATE OR REPLACE DIRECTORY dump_dir AS '/opt/oracle/dumps';
     GRANT READ, WRITE ON DIRECTORY dump_dir TO your_import_user;
     

  2. Recreate any tablespaces from the source (adjust sizes/paths as needed):

     CREATE TABLESPACE users DATAFILE '/opt/oracle/oradata/12c/users01.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G;
     

  3. Create target schemas if they don’t exist:

     CREATE USER schema1 IDENTIFIED BY your_password DEFAULT TABLESPACE users;
     GRANT CONNECT, RESOURCE TO schema1;
     

• Run the Full Import
  Again, use nohup for long-running imports:

  nohup impdp your_import_user/your_password@12c_service full=Y directory=dump_dir dumpfile=full_11g_dump.dmp logfile=impdp_log.log &
  

  Note: If you need to map source tablespaces to different ones on the target, add remap_tablespace=old_source_ts:new_target_ts to the command.

Method 2: RMAN Cross-Server Restore – For Exact Replicas #

Use this if you want a 1:1 copy of the 11g database on the 12c server (great for staging environments).

• Take a Full Backup on the Source (11g)

  rman target /
  RUN {
    ALLOCATE CHANNEL ch1 TYPE DISK;
    BACKUP DATABASE PLUS ARCHIVELOG;
    BACKUP CURRENT CONTROLFILE;
    RELEASE CHANNEL ch1;
  }
  

• Copy Backup Files to the Target Server
  Transfer all backup sets and the control file backup to the target server’s RMAN backup directory.

• Prep the Target (12c) Environment

  1. Install Oracle 12c software (match the source’s bitness if using the same OS; check Oracle docs for cross-OS compatibility).
  2. Create the same directory structure for datafiles/redo logs as the source (or plan to rename them during restore).

• Restore and Recover the Database

  rman target /
  STARTUP NOMOUNT;
  RESTORE CONTROLFILE FROM '/path/to/backup/controlfile.bkp';
  ALTER DATABASE MOUNT;
  
  RUN {
    ALLOCATE CHANNEL ch1 TYPE DISK;
    SET NEWNAME FOR DATABASE TO '/new/path/to/datafiles/%b';
    RESTORE DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
    RELEASE CHANNEL ch1;
  }
  

• Open the Database for Upgrade

  ALTER DATABASE OPEN UPGRADE;
  cd $ORACLE_HOME/rdbms/admin
  perl catctl.pl catupgrd.sql
  

Once your 12c database is up and running, here’s how to upgrade it—either to the latest 12c patch set or a newer major version like 19c.

Option 1: Apply the Latest 12c Release Update (RU) #

• Pre-Upgrade Checks
  Run the pre-upgrade script to catch issues early:

  @$ORACLE_HOME/rdbms/admin/utlu122s.sql
  

  Fix any invalid objects or deprecated features it flags.

• Apply the Patch

  1. Shut down the database:

     sqlplus / as sysdba
     SHUTDOWN IMMEDIATE;
     

  2. Use OPatch to apply the RU (replace the path with your patch folder):

     cd /path/to/12c-ru-patch
     opatch apply
     

• Post-Patch Tasks

  1. Start the database in upgrade mode:

     STARTUP UPGRADE;
     

  2. Run the upgrade script:

     cd $ORACLE_HOME/rdbms/admin
     perl catctl.pl catupgrd.sql
     

  3. Compile invalid objects:

     @$ORACLE_HOME/rdbms/admin/utlrp.sql
     

Option 2: Upgrade 12c to a Major Version (e.g., 19c) #

• Pre-Upgrade Prep

  1. Install the target version (19c) software on the same or a new server.
  2. Run the pre-upgrade tool from the 19c home:

     cd $NEW_ORACLE_HOME/rdbms/admin
     @preupgrade.sql
     

  Follow all recommendations in the generated preupgrade.log file.

• Run the Upgrade

  1. Shut down the 12c database.
  2. Launch the upgrade utility from the 19c home:

     cd $NEW_ORACLE_HOME/rdbms/admin
     perl catctl.pl catupgrd.sql
     

• Post-Upgrade Tasks

  1. Compile invalid objects:

     @$NEW_ORACLE_HOME/rdbms/admin/utlrp.sql
     

  2. Update listener.ora and tnsnames.ora to point to the new 19c database.
  3. Test all applications to ensure compatibility.

• Backup first, always: Take a full backup of your source 11g database before migrating, and another of your 12c database before upgrading.
• Test in staging: Never run migrations/upgrades directly on production—test everything on a copy first.
• Watch the logs: Keep an eye on export/import logs, RMAN logs, and upgrade logs for errors. Most issues have clear fixes documented in the logs.

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