如何确认Oracle中IMPDP导入是否成功及验证对象导入情况？

Hey there, let's break this down step by step since you hit those 5345 errors after your 10g to 11g Data Pump full database import. First off, seeing that error count doesn't automatically mean the import failed entirely—many errors are harmless (like duplicate grants or pre-existing system objects), but we need to dig into the details to be sure.

The final job completion message only tells you the job finished, not whether your critical data made it over. Start with these checks:

• Pull up your Data Pump log file (you specified this with the LOGFILE parameter in your impdp command). Look for:
  • The line Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded—if this exists, the core import framework worked, and the job didn't crash mid-process.
  • The final summary section, which will list:
    • Total objects imported: X
    • Total objects skipped: Y
    • Total objects failed: Z
      This gives you a high-level picture of how much actually made it into the target 11g database.
• Filter for critical errors: Search the log for ORA- codes. Ignore errors like ORA-00955: name is already used by an existing object (these are just objects that were already in the target) and focus on ones like ORA-01555: snapshot too old (indicates a data consistency issue) or ORA-00942: table or view does not exist (means a dependent object was missing during import).

Once you confirm the job didn't fail catastrophically, you need to make sure every object from your 10g source is present in 11g:

• Compare object counts between source and target
  Run these queries in both your source 10g and target 11g databases, focusing on your business schemas (skip system users like SYS/SYSTEM):

  -- Count active business users
  SELECT COUNT(*) FROM DBA_USERS WHERE ACCOUNT_STATUS = 'OPEN' AND OWNER NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP');
  
  -- Count tables per business schema
  SELECT OWNER, COUNT(*) FROM DBA_TABLES WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') GROUP BY OWNER;
  
  -- Count indexes, views, procedures (repeat for other object types)
  SELECT OWNER, COUNT(*) FROM DBA_INDEXES WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') GROUP BY OWNER;
  

  Compare the counts—if they match for your business schemas, that's a good sign.
• Find missing objects with MINUS queries
  If counts don't line up, use MINUS to pinpoint exactly what's missing:

  -- Find tables present in source but not target
  SELECT OWNER, TABLE_NAME FROM SOURCE_DB.DBA_TABLES
  WHERE OWNER IN ('YOUR_BUSINESS_SCHEMA1', 'YOUR_BUSINESS_SCHEMA2')
  MINUS
  SELECT OWNER, TABLE_NAME FROM TARGET_DB.DBA_TABLES
  WHERE OWNER IN ('YOUR_BUSINESS_SCHEMA1', 'YOUR_BUSINESS_SCHEMA2');
  

  Swap DBA_TABLES with DBA_VIEWS, DBA_PROCEDURES, or DBA_SEQUENCES to check other object types.
• Validate data consistency
  For core business tables, verify row counts and key data:

  -- Source 10g
  SELECT COUNT(*) FROM YOUR_SCHEMA.CORE_ORDERS;
  SELECT MAX(ORDER_ID), MIN(ORDER_DATE) FROM YOUR_SCHEMA.CORE_ORDERS;
  
  -- Target 11g
  SELECT COUNT(*) FROM YOUR_SCHEMA.CORE_ORDERS;
  SELECT MAX(ORDER_ID), MIN(ORDER_DATE) FROM YOUR_SCHEMA.CORE_ORDERS;
  

  For small tables, you can even compare full data sets:

  SELECT * FROM YOUR_SCHEMA.SMALL_LOOKUP_TABLE
  MINUS
  SELECT * FROM YOUR_SCHEMA.SMALL_LOOKUP_TABLE;
  

  If this returns no rows, the data is identical.

Beyond the above, here are go-to techniques for thorough validation:

• Use Data Pump's VALIDATE parameter
  If you want to check your dump file's integrity or confirm the target database is ready for import (without actually importing), run:

  impdp system/your_password@targetdb DIRECTORY=dpump_dir DUMPFILE=full_db.dmp VALIDATE=YES LOGFILE=validate_dump.log
  

  This will flag issues like missing tablespaces, insufficient privileges, or corrupted dump files.
• Check Oracle component status
  For full database imports, ensure all Oracle components are valid in the target:

  SELECT COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY;
  

  All components should show VALID and match your 11g version.
• Validate constraints and indexes
  Make sure all constraints are enabled and indexes are valid (imports sometimes leave them in a disabled state):

  -- Check disabled constraints for business schemas
  SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE STATUS != 'ENABLED' AND OWNER IN ('YOUR_BUSINESS_SCHEMAS');
  
  -- Check invalid indexes for business schemas
  SELECT OWNER, INDEX_NAME, TABLE_NAME FROM DBA_INDEXES WHERE STATUS != 'VALID' AND OWNER IN ('YOUR_BUSINESS_SCHEMAS');
  

  Re-enable constraints or rebuild indexes as needed.
• Run DBVERIFY for physical integrity
  If you're worried about data block corruption in the target database's data files, use the dbv utility:

  dbv FILE=/u01/app/oracle/oradata/targetdb/users01.dbf LOGFILE=dbv_validation.log
  

  This checks for physical and logical corruption in the data file.

Remember, focus on your business-critical objects first—system object errors are often irrelevant to your application's functionality.

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