如何导入/打开.dmp文件？Oracle 12cR2导入报错求助

Hey there! Let's walk through getting that 50GB .dmp file imported into your Oracle 12c Release 2 instance. I'll break this down into actionable steps based on the issues you've mentioned.

1. First, Confirm Key Details About Your Dump File #

Since you don't know the export version or method, let's pull that info first:

• Run this command on your .dmp file (from the OS terminal) to check the export version and confirm it's a Data Pump file:

  strings your_dump_file.dmp | head -50
  

  Look for lines like EXPORT:V12 (for 12c) or EXPORT:V11 (for 11g) to confirm the source version. Oracle 12cR2 can import dumps from older versions (10gR2+, usually), but if the source is newer (like 19c), you'll need to use a matching or newer Oracle version to import.

2. Fix the "Cannot Create User" Error with IMPDP #

You mentioned you tried creating the user but still hit errors—here's what's likely going wrong:

a. Preview the Export Metadata First #

Instead of jumping into a full import, generate a preview of the original schema, tablespaces, and permissions to avoid guesswork:

impdp system/your_system_password@your_db_sid \
directory=DMP_DIR \
dumpfile=your_dump_file.dmp \
logfile=import_preview.log \
content=metadata_only \
sqlfile=preview_schema.sql

Open preview_schema.sql to see:

• The original username(s) that were exported
• The tablespaces they used
• The permissions assigned to those users

b. Create the User Correctly (or Remap It) #

• If you want to use the same username as the export:
  • Make sure the user doesn't already exist (if it does, drop it first with drop user your_username cascade;), then create it with matching tablespace settings from preview_schema.sql:

    create user your_username identified by your_password
    default tablespace your_default_tbs
    temporary tablespace temp
    quota unlimited on your_default_tbs;
    

  • Grant the necessary permissions (check preview_schema.sql for exact grants, but start with basics):

    grant connect, resource, create session, unlimited tablespace to your_username;
    

• If you want to use a different username, add the remap_schema parameter to your IMPDP command/par file:

  remap_schema=original_username:new_username
  

  Just make sure you've created the new user with the correct tablespace settings first.

c. Check for Table Space Issues #

If the original export used tablespaces that don't exist in your 12cR2 instance, use the remap_tablespace parameter to map them to existing ones:

remap_tablespace=original_tablespace:existing_tablespace

3. Validate Your .par File Configuration #

Double-check these critical settings in your parameter file:

• Directory Object: The directory parameter refers to an Oracle database directory object, not an OS path. If you haven't created it yet:

  create directory DMP_DIR as '/path/to/your/dmp/file/location';
  grant read, write on directory DMP_DIR to system; -- Or whichever user you're using for IMPDP
  

  Make sure the OS user running Oracle has read/write permissions on that OS directory too.
• Core Parameters: Ensure your .par file includes (at minimum):

  directory=DMP_DIR
  dumpfile=your_dump_file.dmp
  logfile=import_log.log
  -- Add remap_schema/remap_tablespace if needed
  -- Add table_exists_action=append/replace if you need to handle existing tables
  

• Permissions for IMPDP User: The user running IMPDP (like system) needs the DATAPUMP_IMP_FULL_DATABASE role. Grant it if you haven't:

  grant datapump_imp_full_database to system;
  

4. Fix Your .sh Script #

Make sure your shell script has the right environment setup before running IMPDP:

#!/bin/bash
# Set Oracle environment variables
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export ORACLE_SID=your_db_sid
export PATH=$ORACLE_HOME/bin:$PATH

# Run IMPDP with your par file
impdp system/your_system_password parfile=your_par_file.par

• Give the script execute permissions: chmod +x your_import_script.sh
• Run it from the terminal, and monitor the log file (import_log.log) for any ongoing errors.

5. Final Checks Before Full Import #

• Disk Space: Ensure your database server has enough disk space—50GB of dump file will likely expand to 60GB+ once imported. Check both the OS disk and Oracle tablespace free space.
• Version Compatibility: If the dump file is from a newer Oracle version (e.g., 19c), you can't import it into 12cR2—you'll need to upgrade your Oracle instance to match or newer.

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