Using Toad Data Modeler with Oracle DB on AWS RDS - I

    Nov 5, 2018 11:15:00 AM by Deepak Vohra

    In this tutorial we shall discuss using Toad Data Modeler with Oracle Database running on AWS RDS. We shall create an Oracle Database instance on AWS RDS and reverse engineer the database to a model in Toad Data Modeler.

    Toad Data Modeler is a tool for modeling data. It supports both logical and physical data modeling. It includes support for modeling Entities, Relationships, Views, Procedures, Functions, Categories, User Groups, Users, Materialized Views, Packages, Sequences, Synonyms, Tablespaces, Data Types and User Data Types, among other data structures and database objects.  All the commonly used databases, including Oracle, MySQL, PostgreSQL, SQL Server, DB2, and Redshift, may be forward- and reverse-engineered. This tutorial has the following sections.

    Setting the Environment

    Creating an Oracle Database Instance on AWS RDS

    Obtaining Connection Parameters

    Creating a Model

    Adding Oracle Database Instance from AWS RDS to Model

    Verifying a Model

    Exploring Database Objects

    Generating DDL for Model

    Browsing Generated DDL in Toad Oracle

    Saving a Model

    Searching for Database Objects

    Creating a Report

    Converting a Model

    Setting the Environment

    As we are using Toad Data Modeler, download and install Toad Data Modeler.  Create an AWS account, which is the only other prerequisite.

    Creating an Oracle Database Instance on AWS RDS

    Create an AWS RDS instance at https://aws.amazon.com/rds with Oracle Database engine. The procedure to create an AWS RDS instance with Oracle Database has been discussed in several other articles, such as Using Toad Data Point with Oracle Database on AWS RDS; some of the features may have changed since the tutorial but the procedure is same or similar.  Click on Launch database instance as shown in Figure 1.

    Figure 1. Clicking on Launch Database Instance, Amazon RDS

    Figure 1. Launch database instance

    Select engine as Oracle as shown in Figure 2.

    Figure 2. Selecting Oracle as the database engine.

    Figure 2. Selecting engine as Oracle

    Select one of the editions; Oracle Standard Edition is shown selected in Figure 3. Click on Next.

    Figure 3. Selecting an Edition

    Figure 3. Selecting an Edition

    Next, specify DB details as shown in Figure 4. Select License model as bring-your-own-license and, if the Free tier option is selected, Oracle Database 11.2.0.4.v17 is the latest available edition.

    Figure 4. Specifying database details

    Figure 4. Specifying DB Details

    Select a DB instance class as shown in Figure 5. The CPU and RAM are set based on the DB instance class selected. Storage type is SSD for the selected DB instance class and default allocated storage is 20 GiB.

    Figure 5. Selecting DB instance class

    Figure 5. Selecting DB instance Class

    In Settings specify DB instance identifier as orcldb as shown in Figure 6. Specify Master username and Master password. Click on Next.

    Figure 6. Specifying database settings

    Figure 6. Specifying Database Settings

    Next, configure advanced settings, as shown in Figure 7.  Select to Create a new VPC. Select to Create a new Subnet groupPublic accessibility must be set to Yes.

    Figure 7. Selecting network settings

    Figure 7.  Selecting Network Settings

    Set Availability zone to No preference as shown in Figure 8. For VPC security groups select Create new VPC security group.

    Figure 8. Setting Availability Zone and VPC Security Group

    Figure 8. Setting Availability Zone and VPC Security Group

    Next, specify Database options as shown in Figure 9. Set Database name (ORCL) and Port (1521). Keep the default settings for DB parameter group, Option group, and Character set name.

    Figure 9. Selecting or setting Database Options

    Figure 9. Selecting or setting Database Options

    Keep the default settings for Encryption, Backup, Monitoring, Log exports, and Maintenance.  Optionally, deletion protection may be enabled. Click on Create database as shown in Figure 10.

    Figure 10. Clicking on the Create Database button

    Figure 10. Create database

    The DB instance starts to get created, as shown in Figure 11. Click on View DB Instance details.

    Figure 11 The DB instance is being created

    Figure 11. DB Instance being created

    When the DB instance has been created its Status becomes available as shown in Figure 12.

    Figure 12. The DB instance status shows as available

    Figure 12. DB instance available

    Obtaining Connection Parameters

    To obtain connection parameters click on the DB instance link as shown in Figure 13.

    Figure 13. Clicking on the DB instance link

    Figure 13. DB instance link

    The summary for the DB instance gets listed. Scroll down to the Connect section, which lists the instance Endpoint, and Port, as shown in Figure 14.  Copy the Endpoint value and the Port value.  Publicly accessible must be set to Yes.

    Figure 14. Summary for the DB instance, Connect section

    Figure 14. Connect section

    We need to enable access to the DB instance security group. Click on the Security group link as shown in Figure 15.

    Figure 15. Selecting the Security group link

    Figure 15. Selecting Security group link

    In the Security group select Inbound tab and click on Edit as shown in Figure 16.

    Figure 16. Clicking on Edit in the Inbound tab of the security group

    Figure 16. Inbound>Edit

    In Edit inbound rules set Type to All traffic, Protocol to All, Port Range to 0-65535, Source to Anywhere and click on Save as shown in Figure 17.

    Figure 17. Editing inbound rules

    Figure 17.  Edit inbound rules>Save

    The Inbound rules get set to allow all traffic, as shown in Figure 18.

    Figure 18. Inbound rules set to allow all traffic

    Figure 18. Inbound rules set to allow all traffic

    If the DB Name (SID) is not noted when creating the DB instance, obtain its value from the Details section as shown in Figure 19.

    Figure 19. Obtaining DB Name from the Details section

    Figure 19. Obtaining DB Name

    Creating a Model

    To reverse engineer the Oracle Database instance on AWS RDS we need to create a model in Toad Data Modeler. Select File>New>Model as shown in Figure 20.

    Figure 20. Selecting File, then New, then Model in Toad Data Point menus

    Figure 20. File>New>Model

    In New Model window select Physical Data Model as Oracle 11g Release 2 as shown in Figure 21 and click on OK. The Model Name gets set to same as the data model selected.

    Figure 21. Selecting Physical Data Model

    Figure 21. Selecting Physical Data Model

    A new data model gets created and added to Data Modeler in Physical Model Explorer, as shown in Figure 22.

    Figure 22. New Physical Data Model, Oracle 11g Release 2

    Figure 22. New Physical Data Model Oracle 11g Release 2

    Reverse Engineering Oracle Database Instance to Model

    We have created an Oracle Database instance on AWS RDS and also created a model in Toad Data Modeler. In this section we shall reverse engineer the Oracle Database instance to the model.  With the model tab selected, click on Update Model from Database as shown in Figure 23.

    Figure 23. Clicking on Update Model from Database

    Figure 23. Update Model from Database

    The Model Update Wizard gets launched, as shown in Figure 24. Select data source Database RE (Reverse Engineer) as Oracle 11g Release 2 and click on Next.

    Figure 24. Selecting the data source

    Figure 24. Selecting the Data Source 

    Next, select the Data Provider.  Select Connection via TCP/IP as shown in Figure 25.

    Figure 25. Selecting Data Provider

    Figure 25. Selecting Data Provider

    Next, specify the connection parameters with Connecting selected in the margin as shown in Figure 26. Specify Host as the Endpoint value for the DB Instance on AWS RDS as shown in Figure 13. Specify Port as 1521. Specify SID as ORCL. Specify User Name and Password in Login Information.  Set  Connect as to Normal. Click on Next.

    Figure 26. Specifying Connection Parameters

    Figure 26. Specifying Connection Parameters

    Next, select What to Reverse as shown in Figure 27.  Select all the Model properties and click on Next.

    Figure 27. Selecting What to Reverse

    Figure 27. What to Reverse

    Next, select the Options for Reverse Engineering as shown in Figure 28.

    Figure 28. Options for Reverse Engineering

    Figure 28. Options for Reverse Engineering

    Select all the three options for reverse engineering as shown in Figure 29.

    Figure 29. Selecting Options for Reverse Engineering

    Figure 29. Selecting Options for Reverse Engineering

    Select the Items to Load and click on Next as shown in Figure 30.

    Figure 30. Selecting options to load

    Figure 30. Options>Next

    Specify a Connection Name and click on Save as shown in Figure 31.

    Figure 31. Specifying a connection name

    Figure 31. Saving Connection Name

    In Save Connection specify a connection name and click on OK as shown in Figure 32.

    Figure 32. Save Connection

    Figure 32. Save Connection

    A connection name gets added as shown in Figure 33. Click on Next.

    Figure 33. The connection has been added.

    Figure 33. Save Connection>Next

    A connection gets established to the Oracle Database instance as shown in log in Figure 34. Next, select the database objects to reverse engineer.

    Figure 34. Connection established to Oracle Database on AWS RDS

    Figure 34. Connection established to Oracle Database on AWS RDS

    Select a User/Schema from which database objects are to be reverse engineered.  As an example, select User/Schema as SYS as shown in Figure 35.

    Figure 35. Selecting a schema

    Figure 35. Selecting Schema

    The Tables, Views, Procedures, Functions, and Synonyms get listed, as shown in Figure 36. Select the database objects to reverse engineer. To select all tables click on Select All with Tables tab selected.

    Figure 36. Selecting all tables

    Figure 36. Selecting all tables

    All tables get selected, as shown in Figure 37.

    Figure 37. All tables selected

    Figure 37. All tables selected

    Similarly select Views, Procedures, Functions and Synonyms. The selected Views are shown in Figure 38.

    Figure 38. Selected Views

    Figure 38. Selected Views

    Click on Execute to reverse engineer as shown in Figure 39.

    Figure 39. Starting Reverse Engineering

    Figure 39. Starting Reverse Engineering

    Next, configure the Comparison Settings, which include the Comparison Rules and whether to log progress to file. With default Comparison Settings click on Next as shown in Figure 40.

    Figure 40. Comparison Settings

    Figure 40. Comparison Settings

    Next, select Object Types as shown in Figure 41. Compare All is selected by default. Click on Next.

    Figure 41. Selecting Object Types to compare

    Figure 41. Selecting Object Types

    Next, the Source Model and Model to Merge are displayed with the database objects to merge as shown in Figure 42. Click on Next. The Model to Merge is empty because we are merging into a new model.

    Figure 42. Source Model and Model to Merge

    Figure 42. Source Model and Model to Merge

    Next, review the information, which includes the Source and Merge models, number of objects to add/remove/merge/commit as shown in Figure 43. All objects are to be added from the AWS RDS database instance. The objects are listed in the same order as in Source Model/Model to Merge, which is Users, UserGroups, Tablespaces, Directories, and Editions. Click on Finish.

    Figure 43. Review Source and Merge models, number of objects to add, remove, merge, and commit

    Figure 43. Review>Finish

    The reverse engineering gets performed and the database objects get merged as shown in Figure 44.

    Figure 44. The Model Merged

    Figure 44. Model Merged

    The Message Explorer lists the database objects merged as shown in Figure 45.

    Figure 45. Message Explorer lists the database objects merged

    Figure 45. Message Explorer

    Click on the folder for a model item type reverse engineered to display the model items, as shown for User Groups in Figure 46.

    Figure 46. User Groups

    Figure 46. User Groups

    The Tablespaces reverse engineered from RDS are shown in Figure 47.

    Figure 47. Tablespaces reverse engineered to model

    Figure 47. Tablespaces reverse engineered to model

    The Users reverse engineered are shown in Figure 48.

    Figure 48. Users reverse engineered to model

    Figure 48. Users reverse engineered to model

    The Directories added are shown in Figure 49.

    Figure 49. Directories reverse engineered to model

    Figure 49. Directories reverse engineered to model

    The Object Viewer tab displays the objects as shown in Figure 50.

    Figure 50. Object Viewer

    Figure 50. Object Viewer

    Summary

    In the first of two articles on using Toad Data Modeler to reverse engineer Oracle Database running on AWS RDS we created an Oracle Database instance on AWS RDS, created a model in Toad Data modeler, and reverse engineered the Oracle Database to the model. In a subsequent article we shall explore some of the other features in Toad Data Modeler with the model reverse engineered.

     

     

    Tags: Toad Data Modeler Oracle Cloud

    Deepak Vohra

    Written by Deepak Vohra

    Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.