In terms of code compatibility, EDB Postgres Advanced Server executes PL/SQL stored procedures, triggers, packages, and understands extensions like DUAL, Rownum, Decode, NVL(2) and Substr(). Its execution of PL/SQL is not emulation, translation, nor a layer on top of PL/pgSQL. Its procedural language is compiled, interpreted, and debugged just like in Oracle. For database code embedded in client programs, it has ECPGPlus (Pro*C support). Oracle’s OCI (Oracle Call Interface) is supported through EDB’s OCL (Open Client Library).
There are tools included in the EDB Postgres Platform that support mission-critical applications, such as Postgres Enterprise Manager (provides much of the same functionality DBAs use in Oracle Enterprise Manager) for monitoring, managing, tuning, and alerting. High-availability and failover are handled by EDB Failover Manager along with the binary replication and hot standby features (similar to Oracle’s Active Data Guard).
2. Re-training Risks.
Oracle users migrating to EDB Postgres Advanced Server need not worry that their current Oracle skill sets will go to waste.
Oracle developers can still code in PL/SQL. They can still use query hints (default optimizer hints, access method hints, join order hints, join relation hints, global hints, append hints). The following packages are still available to them: DBMS_ALERT, DBMS_CRYPTO, DBMS_JOB, DBMS_LOB, DBMS_LOCK, DBMS_OUTPUT, DBMS_PIPE, DBMS_PROFILER, DBMS_SCHEDULER, DBMS_SQL, DBMS_RANDOM, DBMS_RLS, DBMS_UTILITY, UTL_ENCODE, UTL_FILE, UTL_HTTP, UTL_MAIL, UTL_SMTP, UTL_URL, and custom packages. Virtual private databases can be implemented using a package compatible with Oracle’s DBMS_RLS (row level security).
Oracle DBA’s will thoroughly appreciate the following tools at their disposal:
- EDB’s Resource Manager (CPU & I/O resource allocation). Similar to Oracle’s Resource Management and Profile.
- EDB’s password profiles (profiles for password management). Provides Oracle compatible user account and password policy management functions. DB-level security rules are migrated from Oracle to EDB Postgres Advanced Server.
- The ability to capture, review, and diagnose “Wait” events in DB processing through EDB Postgres Advanced Server’s DRITA (Dynamic Runtime Instrumentation Tools Architecture) functions.
Functions: get_snaps(), sys_rpt(), sess_rpt(), sessid_rpt(), sesshist_rpt(), purgesnap(), truncsnap()
Statspack-like AWR (automatic workload repository) reports: edbreport(), stat_db_rpt(), stat_tables_rpt(), statio_tables_rpt(), stat_indexes_rpt(), statio_indexes_rpt(), edb$system_waits, edb$session_waits, edb$session_wait_history
Catalog views: edb$system_waits, edb$session_waits, edb$session_wait_history
- EDB Postgres Advanced Server includes over 50 of Oracle’s most popular dictionary views (Catalog views), including all DBA_views and USER_views.
- EDB Postgres Advanced Server supports Oracle’s table partitioning syntax.
- EDB*Plus – command line tool for working directly with the database which is nearly indistinguishable from its Oracle counterpart.
- EDB*Loader – for bulk data loads and provides support for direct path and parallel direct path loading.
3. Integration Risks.
With EDB Replication Server, replicating and migrating data to and from Oracle becomes a breeze.
From the official website: “EDB Replication Server (EPRS) provides a robust data replication platform that replicates between Postgres databases in a single-master or multi-master mode, or from non-Postgres databases to Postgres in a single master mode. EPRS provides geographic load balancing, simplifies real-time migration, and enables heterogeneous data integration.”