DbSchema for Oracle Database

DbSchema is a comprehensive tool for designing and managing Oracle databases. It provides powerful features, like visual schema design, team collaboration with Git, easy schema deployment, and interactive HTML5 documentation.

How to Connect to Oracle Database

Watch the step-by-step video tutorial below:

For written documentation, please continue reading...

Open DbSchema, then click "Connect to Database" on the Main Screen and choose your database system. DbSchema will automatically download the required JDBC driver. Connect to Database in DbSchema

Before starting the connection process in DbSchema, verify that the Oracle Database server is running and ready to accept incoming connections.

Your Guide to Oracle Connection Types

  1. Configure the Local Connection via SID in DbSchema
  2. Configure the Remote Connection via SID in DbSchema
  3. Connect to Oracle Cloud Autonomous Database
  4. Use Kerberos Authentication
  5. Docker Oracle Installation

I. Configure the Local Connection via SID in DbSchema

  1. Select 'Connect via SID from the JDBC URL
  2. To connect to Oracle from your computer, insert the database username (usually sys as sysdba) and password configured during the Oracle Database installation.
  3. Specify the SID (e.g., ORCLCDB) of the database you want to connect to. You can run the command SHOW PARAMETER INSTANCE_NAME in SQL*Plus, and the SID will appear as the value of the instance_name parameter in the list.
  4. Click 'Connect' and select the schema you want to reverse engineer.
Local Connection via SID in Oracle

II. Configure the Remote Connection via SID in DbSchema

  1. To allow remote connections, you need to check the Firewall Rules : Ensure that the firewall on the server permits traffic through port 1521.
  2. Select the Remote computer or custom port
  3. Here, you can enter the IP address or the Hostname (Windows Computer Name) of your remote Oracle Server to connect from your local computer to another remote machine.
  4. Test the connection (Ping)
  5. Insert the database username (usually sys as sysdba) and password configured during the Oracle server installation.
  6. Specify the SID (e.g., ORCLCDB) of the database you want to connect to. You can run the command SHOW PARAMETER INSTANCE_NAME in SQL*Plus, and the SID will appear as the value of the instance_name parameter in the list.
  7. Click 'Connect' and select the schema you want to reverse engineer.
Remote Connection via SID in Oracle

III. Connect to Oracle Cloud Autonomous Database

  1. In the Oracle Cloud Console create an autonomous database.
  2. Create administrator credentials; these will be used to establish the connection in DbSchema.
    3. Administrator Credentials from Oracle Cloud Autonomous Database
  3. You will need this JDBC URL pattern from DbSchema jdbc:oracle:thin:@ + the Connection String URL from the Oracle Console - for example: 
    1
    jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=g158801ea2b709a_dbschema_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
  4. Click the 'Database Connection' tab: Download and unzip the wallet to a local folder, such as C:/Temp/OracleWallet. Set a Wallet Password: During the download process, you will be prompted to create a wallet password. For example, you can use 'dbschemasecret'.

    5. Database Connection & Wallet from Oracle Console

  5. Open and edit in any text editor the DbSchema.vmoptions file, located in the DbSchema installation folder. Add the following parameters to configure the connection by referencing the downloaded wallet files and the password you created during the wallet setup: 
    1
    2
    3
    4
    5
    6
    7
    -Djavax.net.ssl.trustStore=C:/Temp/OracleWallet/truststore.jks
    -Djavax.net.ssl.trustStorePassword=dbschemasecret
    -Djavax.net.ssl.trustStoreType=JKS
    -Djavax.net.ssl.keyStore=C:/Temp/OracleWallet/keystore.jks
    -Djavax.net.ssl.keyStoreType=JKS
    -Djavax.net.ssl.keyStorePassword=dbschemasecret
    -Doracle.net.authentication_services=(TCPS)

    6. DbSchema.vmoptions File from the Installer Folder

  6. Important! Start or restart DbSchema, and connect to the Oracle database.
  7. In DbSchema application, choose 'Edit Manually' from the JDBC URL
  8. Enter all the required details as explained in the previous steps.
  9. Click 'Connect' and select the schema you want to reverse engineer.

    10. JDBC Connection to Oracle Cloud Autonomous Database

Connect to Oracle using Kerberos Authentication

This require setting in the Connection Dialog Settings pane the Properties field to:
oracle.net.authentication_services=( KERBEROS5 );oracle.net.kerberos5_mutual_authentication=true

Connect to Oracle using Kerberos Authentication

Please also edit the file DbSchema.vmoptions ( look in the same folder as DbSchema executable or in DbSchema.app/Contents/vmoptions.txt ) and add this oracle.net.kerberos5_cc_name=path_to_credential

Download and Use Oracle Database with Docker

Follow these steps to download and run Oracle Database using Docker:

  1. Log in to the Oracle Container Registry:
    2.         docker login container-registry.oracle.com
  2. Pull the latest Oracle Database Enterprise Edition image:
    3.         docker pull container-registry.oracle.com/database/enterprise:latest
  3. Run the Oracle Database container:
    4.         
docker run -d --name oracle-db -p 1521:1521 -p 5500:5500
-e ORACLE_SID=ORCLCDB
-e ORACLE_PDB=ORCLPDB1
-e ORACLE_PWD=YourPassword123
container-registry.oracle.com/database/enterprise:latest

Note: Replace YourPassword123 with a secure password of your choice. The ORACLE_SID specifies the database SID, and ORACLE_PDB specifies the pluggable database name.

Note: The database username is usually SYS as SYSDBA, and the password is the one you set in ORACLE_PWD.