Edit

Share via

Tutorial: Set up Oracle database mirroring in Microsoft Fabric (Preview)

Important

This feature is in preview.

Database mirroring in Microsoft Fabric is a cloud-based, zero-ETL solution that creates a mirror of your Oracle database. When you set up mirroring, you'll get a read-only copy of your Oracle data in OneLake that updates in near real-time.

Prerequisites

You need:

  • A Microsoft Fabric workspace with Trial or Premium Fabric capacity
  • An Oracle server that's one of these types, version 11 or above with LogMiner enabled:
    • Oracle on-premises (VM, Azure VM, etc.)
    • Oracle OCI
    • Exadata
    • Note: Oracle Autonomous Database isn't supported in this preview

Note

  • LogMiner needs to be enabled on your Oracle server. This tool helps track changes in your Oracle database for real-time mirroring.
  • Oracle Autonomous Database isn't supported in this preview.

Set up archive of redo log files

If archive mode isn't enabled on your Oracle database, follow these steps. If it's already enabled, you can move to the next section.

  1. Connect Oracle Recovery Manager (RMAN) to your database. See Connecting to the Target Database Using RMAN.

  2. Shut down the database:

    SHUTDOWN IMMEDIATE;

  3. Back up your database. This safeguards your data before making changes. See Performing a Whole Database Backup.

  4. Start and mount the database (don't open):

    STARTUP MOUNT;

    Note

    Keep the database mounted but not open to enable archiving.

  5. Set up your archive log destinations:

    ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/u02/oracle/logfiles';

    Note

    Make sure this directory exists on your system.

    You can optionally set a second archive location:

    ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'LOCATION=/u03/oracle/logfiles';

  6. Turn on archive log mode and open the database:

    ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

  7. Shut down the database again to ensure all changes are applied:

    SHUTDOWN IMMEDIATE;

  8. Create another backup. This is needed because enabling archive log mode changes the control file. See Performing a Whole Database Backup.

  9. Start the database:

    STARTUP;

  10. Check that archive log mode is enabled:

    SELECT LOG_MODE FROM V$DATABASE;

Tip

As a best practice for Mirroring for Oracle, our recommendation is to clean archive logs on a regular cadence to ensure optimal performance and stability

Set up Oracle permissions and enable supplemental logging

Your Oracle database needs supplemental logging enabled. If your user doesn't have the required permissions, ask your database administrator (DBA) to run these commands:

  1. Enable supplemental logging for the database:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;

  2. Enable supplemental logging for each table you want to mirror:

    ALTER TABLE {schemaName}.{tableName} ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  3. Grant these permissions to your sync user:

    GRANT CREATE SESSION TO user;
GRANT SELECT_CATALOG_ROLE TO user;
GRANT CONNECT, RESOURCE TO user;
GRANT EXECUTE_CATALOG_ROLE TO user;
GRANT FLASHBACK ANY TABLE TO user;
GRANT SELECT ANY DICTIONARY TO user;
GRANT SELECT ANY TABLE TO user;
GRANT LOGMINING TO user;

Install the On-Premises Data Gateway

  1. Install the latest On-Premises Data Gateway. Follow this link to learn about the machine requirements and how to install and register a gateway.

    Tip

    You might need to alter the cloud firewall to allow mirroring to connect to the Oracle instance. We support mirroring for Oracle for OPDG version 3000.282.5 or greater. Any OPDG instance before that will not support Exadata and the limited DDL support that we have.

    We also recommend updating the OPDG instance every month for updates that we release related to Mirroring for Oracle. Latest OPDG releases can be found here.

  2. Connect the gateway to your Fabric workspace

Set up database mirroring

  1. Open your Fabric workspace and check that it has a Trial or Premium Fabric capacity

  2. Select New > Mirrored Oracle (preview)

    Screenshot of Microsoft Fabric workspace showing the Mirrored Oracle (preview) option selected to create a new artifact.

  3. Select Oracle Database

    Screenshot of the database connection screen, with the oracle database option highlighted under New sources.

  4. In the connection dialog, enter your database details:

    Screenshot of Oracle connection setup screen showing fields for server, connection name, and data gateway configuration.

  5. Specify the server, connection, connection name, and data gateway

    • Server: Specify the location of your Oracle database using one of these methods:

      Method Example
      Oracle Net Services Name (TNS Alias) sales
      Connect Descriptor (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))
      Easy Connect (Plus) Naming salesserver1:1521/sales.us.example.com

    • Connection: Select Create new connection

    • Connection name: Enter a name for your connection

    • Data gateway: Select your on-premises data gateway

    • Authentication:

      • Under Authentication kind, select Basic authentication
      • Username: Enter your Oracle database username
      • Password: Enter your Oracle database password

  6. Select Connect to test the connection

  7. Choose how to select tables for mirroring:

    • Auto mode: Let Fabric select 500 random tables
    • Manual: Pick up to 500 tables yourself

    Screenshot of the interface prompting the user to select the data they want to be mirrored before connecting.

  8. Finish the setup:

    • Select Connect
    • Name your mirror
    • Select Create mirrored database

    Screenshot of the mirrored database creation interface showing the connect button, mirror name field, and Create button after table selection.

  9. Watch the replication progress. After a few minutes, you'll see the number of rows replicated and can view your data in the data warehouse.

    Screenshot of the interface showing Rows Replicated status and data visible in the data warehouse view after mirror creation.

Your Oracle database is now connected to Microsoft Fabric and will stay in sync automatically.

Monitor Fabric mirroring

Once mirroring is configured, you're directed to the Mirroring Status page. Here, you can monitor the current state of replication. For more information and details on the replication states, see Monitor Fabric mirrored database replication.

Related content

  • Last updated on