Step-by-Step Guide to dbForge Schema Compare for Oracle
Overview
dbForge Schema Compare for Oracle is a tool that compares and synchronizes Oracle database schemas, helping you detect differences, create update scripts, and keep environments aligned (development, staging, production). This guide walks through an end-to-end comparison and synchronization workflow.
Prerequisites
- dbForge Schema Compare for Oracle installed.
- Access credentials for source and target Oracle databases.
- Proper network access and necessary permissions (CONNECT, SELECT, and object DDL privileges).
- A backup or version control snapshot of target schema (recommended).
1. Launch and create a new project
- Open dbForge Schema Compare for Oracle.
- Click New Project (or File → New → Schema Comparison).
- Enter a project name and optional description.
- Choose a folder to save the project file (.scmp) for later reuse.
2. Connect to source and target databases
- In the project window, click New Connection for the Source.
- Enter connection details: host, port, service/SID, username, and password. Test connection and save.
- Repeat for the Target connection.
- Select specific schemas if you don’t want to compare entire databases.
3. Configure comparison options
- Click Options (gear icon) to open comparison settings.
- Choose object types to compare (tables, views, packages, procedures, triggers, sequences, etc.).
- Set rules for object mapping and filters:
- Ignore whitespace and comments in routines (optional).
- Case sensitivity for object names.
- Ignore user-specified objects or patterns (e.g., temp tables).
- Save options to apply them consistently across runs.
4. Run the comparison
- Click Compare to start.
- Monitor progress in the status bar. For large schemas, this may take time.
- When complete, the results grid shows differences categorized as:
- Equal — identical objects
- Different — objects exist in both but differ
- Source only — objects only in source
- Target only — objects only in target
5. Review differences
- Use the left pane to browse objects by type and status.
- Select an object marked Different to see a side-by-side diff of DDL.
- Use the inline highlighting to spot changes in column definitions, constraints, indexes, or PL/SQL code.
- For tables, review data-related differences if comparing data (if supported in your workflow).
6. Select synchronization actions
- In the results grid, choose actions for each object:
- Synchronize to Target — apply source changes to target
- Synchronize to Source — apply target changes to source
- Skip — do not synchronize
- Use bulk actions to select all objects of a certain status (e.g., synchronize all “Source only” objects).
7. Generate and review synchronization script
- Click Create Script (or Generate SQL) to produce a deployment script.
- Review the SQL script carefully:
- Check DDL order to avoid dependency errors (e.g., create tables before constraints).
- Look for potentially destructive operations (DROP TABLE, ALTER TABLE with data loss).
- Optionally, export the script to a file for version control or manual review.
8. Backup target and execute script
- Take a backup of the target schema or ensure a restore point exists.
- In dbForge, you can apply the script directly by clicking Synchronize or execute the exported script in your SQL client.
- Monitor execution and resolve any runtime errors (missing privileges, object locks, dependent objects).
9. Post-synchronization checks
- Re-run the comparison to confirm no remaining differences.
- Run application tests or sanity checks against the target environment.
- Review logs and save the comparison report for auditing.
10. Automate and schedule comparisons (optional)
- Use the command-line interface (if available) or scheduling tools to run comparisons regularly.
- Save project files and comparison options to automate repeatable runs.
- Integrate with CI/CD pipelines to validate schema drift before deployments.
Best Practices
- Always backup before synchronization.
- Use version control for generated scripts.
- Run comparisons in a non-production environment first.
- Review destructive changes manually.
- Use filters to narrow comparisons to relevant objects.
Troubleshooting tips
- Connection failures: verify network, listener, and credentials.
- Permission errors: ensure the user has required DDL/SELECT privileges.
- Dependency errors during sync: adjust script order or create placeholder objects first.
- Large schemas slow: compare in chunks by object type or schema subsets.
Summary
dbForge Schema Compare for Oracle streamlines schema comparison and synchronization through a visual diff interface, options to fine-tune comparisons, script generation, and synchronization. Follow the steps above—connect, configure, compare, review, generate scripts, backup, and synchronize—to safely keep schemas aligned.