Note:

Use Oracle Estate Explorer to Analyze Customer Database Inventory for Moving to Oracle Autonomous Database

Introduction

Oracle Estate Explorer (OEE) is a tool that enables customers to programmatically evaluate groups of Oracle databases for migration readiness to Oracle Autonomous Database (ADB). OEE enables for small, medium, and large estates of databases to be explored, analysed, and prioritized in a highly automated, rapid, and flexible way.

The output from OEE provides a high-level estate overview of the tested group of databases, ranks them according to their alignment with ADB prerequisites and delivers a graded relative effort of any remediation required.

Objective

Prerequisites

Task 1: Set up OEE

  1. Connect to Oracle Autonomous Database from your system using SQLcl installed earlier, this will validate your connection to the database and will not cause any related error during OEE installation. Ensure that TNS_ADMIN is set to the ADB unzipped wallet location .

    Image1

  2. Before installing OEE, you need to edit 3 files present under downloaded OEE zip file. Navigate to the downloaded location (<OEE download location>\oee_install\oee_install_on_cloud) and modify the following 3 files.

    • connect_to_db.sql:

      set cloudconfig ‘<Path where you have downloaded OEE zip file >’ - Specify that path where OO zip file is downloaded
set SQLPROMPT "@|white ADMIN|@@@|green <service_name>|@@|white >|@" - Do not make any changes in this line
connect ADMIN/***@<adb service name> - Mention the ADB service name and it's credentials where OEE will be installed

    • set_config.sql:

      BEGIN
-- These must be set by the user
    :db_user := 'MPACK_DB_OEE';
    :db_user_pwd := '<set password for MPACK_DB_OEE user>' ;
    :apex_workspace_name := 'MPACK_OEE';
    :apex_workspace_user := 'MPACK_OEE';
    :apex_workspace_pwd := '<set password for APEX workspace>' ;
    :apex_app_id := '103';
    :apex_app_name := 'Oracle Estate Explorer V2.3';
END;
/

    • connect_as_oee.sql:

      set SQLPROMPT "MPACK_OEE@<service_name> > " - Do not make any changes in this line
connect MPACK_OEE/<password used above for MPACK_OEE>@<adb service name>  - Mention the ADB service name and credentials for MPACK_OEE that was given in "set_config.sql" script.

  3. Connect to Oracle Autonomous Transaction Processing (ATP) as validated earlier and execute oee_install.sql.

    Image2

    There will be some errors during drop of some tables and sequences which need to be ignored as it checks for their existence before creating those objects.

  4. Log in to the OCI console, navigate to Oracle Autonomous Database used above for installing OEE and launch the APEX link shown within ADB homepage.

  5. Click Launch APEX and log in to APEX url with workspace name as MPACK_OEE and password that was given in set_config.sql script.

    Image3

    Image4

  6. Once inside APEX homepage, click App Builder and you should see Oracle Estate Explorer banner stating that OEE has now been installed in the Oracle Autonomous Database.

    Image5

  7. Click Oracle Estate Explorer, click Run Application which should take you to the login page for OEE utility.

    Image6

    Till this step we have successfully configured the connection to Oracle Autonomous Database, installed OEE and accessed the same through inbuilt APEX.

In Task 2, we will start looking at database inventory that needs to be analyzed for moving to Oracle Autonomous Database.

Task 2: Use OEE to analyze Database Estate

  1. Build an Estate Catalog, since the scope of this tutorial assumes the use of Oracle Enterprise Manager (OEM), we will restrict our specifics accordingly. The catalog building invokes the script oee_dbcatalog.sh located inside extract scripts folder of downloaded OEE zip file. The script needs to be modified in order to login to OEM repository database.

    Modify following lines in the script oee_dbcatalog.sh.

    SQLPLUS_EXE=/u01/app/oracle/em/middleware_135/bin/sqlplus (This needs to be changed depending on the sqlplus location that exists on your OEM host)
UNPWCS=sysman/yourPassword@empdb (Correct password for sysman user and repository database is required here)

    Execute the script at your Oracle Management Server (OMS) host and once it gets executed successfully, a csv file is generated that has the details from all of the databases being monitored through OEM and this csv will be uploaded to OEE link at a later stage.

  2. Login at OEE and navigate to OEE Administration, Manage Data Sources, Create data source (Give it a meaningful name ) and click Save.

    Image7

  3. Navigate to OEE Administration, Manage Data Sources, Manage Catalogs (Give catalog a name along with customer name and select data source created above) and click Save.

    Image8

  4. Navigate to Data Maintenance, Load Data Source (This will load the csv file generated in step 1 during catalog extraction).

    Image9

    Once the data loading is complete, a message will be displayed.

    Image10

  5. In order to select fewer databases from the catalog uploaded earlier, we need to group them in respective groups. For instance databases can be grouped based on their application, based on RTO/RPO, based on version and so on.

    To do the same navigate to Groups section from homepage, Create group and add required databases that we need to be part of our first group that needs to be considered for migration .

    Image11

  6. Click Add/Remove databases from group for the group created above and select desired databases from the displayed list.

    Image12

  7. After creating the group and adding required databases, we need to create a driver file for the group which will be consumed for data extraction and processing at a later stage. Navigate back to the groups page and click Create Driver File. While creating the driver file there are 2 check boxes that need to be selected or de-selected according to the use case. Download the driver file which will be used at a later stage.

    Image13

  8. The driver file created in the last step needs to be passed against OEM repository database when executing group extraction script oee_group_extract-2.1.0.sh. Log in to the OEM repository database and execute the script as shown, the driver file needs to be passed as an argument and a resulting txt file would be generated which will be used for further processing in OEE tool.

    Image14

  9. Log in to OEE link and navigate to Data Maintenance, Load Database extract file to staging, browse the text file generated in Step 8 and click Load Data.

    Image15

  10. Navigate to Data Maintenance, Process Database Extract file. After the file is processed you are now at the final stage of running the scenario and viewing the results.

  11. Navigate back to Groups on OEE page, and click Run Scenario for the group. OEE provides default Scenario Autonomous Shared v7 that has 19 tests however you can always create your own scenario and add the required checks. For this tutorial, we have used default Autonomous scenario and run the report against it.

    Image16

    Once the scenario is run successfully, you should see a detailed report that has been categorized into different buckets depending on the estimated efforts needed by each of the database.

The resulting reports can be downloaded and shared across with customer, application team to review and take necessary actions.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.