Migrating Oracle to PostgreSQL

Hands-on lab step-by-step

September 2021

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links may be provided to third-party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein.

Microsoft and the trademarks listed at https://www.microsoft.com/en-us/legal/intellectualproperty/Trademarks/Usage/General.aspx are trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners.

Contents

Migrating Oracle to PostgreSQL hands-on lab step-by-step

Migrating Oracle to PostgreSQL hands-on lab step-by-step

Abstract and learning objectives

In this hands-on lab, you implement a proof of concept (POC) for conducting a site analysis for a customer to compare cost, performance, and level of effort required to migrate from Oracle to Azure Database for PostgreSQL. You evaluate the dependent applications and reports that need to be updated and come up with a migration plan. Also, you help the customer take advantage of new PostgreSQL features to improve performance and resiliency.

At the end of this hands-on lab, you will be better able to design and build a database migration plan and implement any required application changes associated with changing database technologies.

Overview

Wide World Importers (WWI) has experienced significant growth in the last few years. As the size of their data grows, they have started to experience issues with their existing Oracle OLTP database, including complex upgrade processes, complex licensing, and even a major failure caused by an overflowing audit table.

The WWI CIO has learned of the many benefits that Azure Database for PostgreSQL provides, including AD support, simple pricing, high performance, and high availability. She is also excited about the many similarities between PL/SQL and PL/pgSQL, since that will reduce the migration effort significantly.

WWI's CIO would like a POC of an OLTP database migration and proof that the new technology will make her company's operations faster, cheaper, and more efficient.

Solution architecture

Below is a diagram of the solution architecture you build in this lab. Please study this carefully, so you understand the whole of the solution as you are working on the various components.

The solution begins by installing and using ora2pg to assess the task of migrating the Oracle XE database supporting the application to Azure Database for PostgreSQL. Then, the tool will be used to migrate the table schema without indexes or constraints to the target. ora2pg will migrate the data to the landing zone. Lastly, remaining objects, including stored procedures and views, will be modified and exported to PostgreSQL through ora2pg. At this point, we will need to modify the MVC application. The first step is to create new entity models. Then, we will scaffold controllers and views from the new models. Lastly, we will use Visual Studio 2019 to deploy the modified app to Azure App Service.

Requirements

Microsoft Azure subscription must be pay-as-you-go or MSDN.
- Trial subscriptions will not work.
A virtual machine configured with Visual Studio 2019 Community edition.

Note: If you find that your Visual Studio 2019 VM image comes with Visual Studio 2017, and not 2019, you will need to manually install 2019 Community from here. Ensure that the ASP.NET and web development and Azure development Workloads are enabled for your installation.

Exercise 1: Setup Oracle 18c Express Edition

Duration: 45 minutes

In this exercise, you will load a sample database supporting the application. Ensure that you installed Oracle XE, Oracle Data Access Components, and Oracle SQL Developer, as detailed in the Before the Hands-on Lab documents.

Task 1: Install the pre-req applications.

On the LabVM, navigate to the path C:\LabFiles\OracleXE213_Win64. Right-click setup.exe, and select Run as administrator.
Select Next to step through each screen of the installer, accepting the license agreement and default values, until you get to the Specify Database Passwords screen.
On the Oracle Database Information screen, set the password to Password.1!!, and select Next.
Select Install. Once the installation completes, take note of the ports assigned.
Select Finish on the final dialog to complete the installation.
Now to install oracle data access component, Navigate to the path "C:\LabFiles\oracledataaccesscomponent", and right-click setup.exe, then select Run as administrator to begin the installation.
Select Next to accept the default language, English, on the first screen.
On the Specify Oracle Home User screen, accept the default, Use Windows Built-in Account, and select Next.
Accept the default installation locations, and select Next.
On the Available Product Components, uncheck Oracle Data Access Components Documentation for Visual Studio, and select Next.
On the ODP.NET screen, check the box for Configure ODP.NET and/or Oracle Providers for ASP.NET at machine-wide level, and select Next.
If the Next button is disabled on the Perform Prerequisite Checks screen, check the Ignore All box, and then select Next. This screen will be skipped by the installer if no missing prerequisites are found.
On the Summary screen, select Install.
On the Finish screen, select Close.
Now to install SSMS, Navigate to "C:\LabFiles" And open ssms.msi. Then Select Next.
Accept the license agreement. Select Next.
On the Choose Setup Type window, select Typical.
On the Ready to Install window, accept the defaults. Then, select Install.
Wait for the installation to complete.

Task 2: Create the Northwind database in Oracle 18c XE

WWI has provided you with a copy of their application, including a database script to create their Oracle database. They have asked that you use this as a starting point for migrating their database and application to Azure SQL DB. In this task, you will create a connection to the Oracle database on your Lab VM.

Now launch SQL Developer from the C:\Tools\sqldeveloper path from earlier. In the Database Connection window, select Create a Connection Manually.

Note: If you are prompted to import preferences from a previous installation, select No.
Provide the following parameters to the New / Select Database Connection window. Select Connect when you are complete.
- Name: Northwind
- Username: system
- Password: Enter the password set in the ARM template Password.1!!
- Keep the Details at their defaults
Once the connection completes, select the Open File icon (1). Navigate to C:\handsonlab\MCW-Migrating-Oracle-to-Azure-SQL-and-PostgreSQL-master\Hands-on lab\lab-files\starter-project\Oracle Scripts\1.northwind.oracle.schema. Then, execute the DDL statements (2).

Note : If you are prompted with Select Connection tab, leave the default option for Connection and Click on OK.
Right-click the Northwind connection and select Properties. Then, edit the Username to NW, and the Password to oracledemo123. Select Connect. Note that you may be asked to enter the password again.
In the Open File dialog, navigate to C:\handsonlab\MCW-Migrating-Oracle-to-Azure-SQL-and-PostgreSQL-master\Hands-on lab\lab-files\starter-project\Oracle Scripts, select the file 2.northwind.oracle.tables.views.sql, and then select Open.
As you did previously, run the script. Note that SQL Developer provides an output pane to view any errors.

Note : If you are prompted with Select Connection tab, leave the default option for Connection and Click on OK
Repeat steps 7 - 8, replacing the file name in step 26 with each of the following:
- 3.northwind.oracle.packages.sql
- 4.northwind.oracle.sps.sql
  - During the Execute script step for this file, you will need to execute each CREATE OR REPLACE statement independently.
  - Using your mouse, select the first statement, starting with CREATE and going to END. Then, run the selection, as highlighted in the image.
  - Repeat this for each of the remaining CREATE OR REPLACE... END; blocks in the script file (there are 7 more to execute, for 8 total).
- 5.northwind.oracle.seed.sql
  
  Important: This query can take several minutes to run, so make sure you wait until you see the Commit complete message in the output window before executing the next file.
After you finish running these scripts, validate that the database objects were created. The image below demonstrates the Tables and the Views created by the script.

Task 3: Configure the Starter Application to use Oracle

In this task, you will add the necessary configuration to the NorthwindMVC solution to connect to the Oracle database you created in the previous task.

On your LabVM, navigate to C:\handsonlab\MCW-Migrating-Oracle-to-Azure-SQL-and-PostgreSQL\Hands-on lab\lab-files\starter-project. Launch NorthwindMVC.sln.
If you are prompted to choose a particular version of Visual Studio, select Visual Studio 2019 and continue.
As this is your first time opening Visual Studio 2019 on the LabVM, you will be prompted to enter the email address of your Visual Studio account. Enter it and proceed to the following steps.
In Visual Studio on your LabVM, select Build from the menu, then select Build Solution.
Open the appsettings.json file in the NorthwindMVC project by double-clicking the file in the Solution Explorer, on the right-hand side in Visual Studio.

In the appsettings.json file, locate the ConnectionStrings section, and verify the connection string named OracleConnectionString matches the values you have used in this hands-on lab:

DATA SOURCE=localhost:1521/XE;PASSWORD=oracledemo123;USER ID=NW

"ConnectionStrings": {
   "OracleConnectionString": "DATA SOURCE=localhost:1521/XE;PASSWORD=oracledemo123;USER ID=NW"
}

Run the solution by selecting the green Start button on the Visual Studio toolbar.
You should see the Northwind Traders Dashboard load in your browser.
Close the browser to stop debugging the application, and return to Visual Studio.

Exercise 2: Assess the Oracle 18c Database before Migrating to PostgreSQL

Duration: 15 minutes

In this exercise, you will prepare the existing Oracle database for its migration to PostgreSQL. Preparation involves two main steps. The first step is to update the database statistics. Statistics about the database become outdated as data volumes and activity change over time. Second, you will need to identify invalid objects in the Oracle database. The migration utility will not migrate invalid objects.

Task 1: Update Statistics and Identify Invalid Objects

Run the following statements in SQL Developer.
```
-- 18c script
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'NW');
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
```
Note: This script can take over one minute to run. Ensure that you receive confirmation that the script has executed successfully.
Now, we will utilize a query that lists database objects that are invalid and unsupported by the ora2pg utility. It is recommended to fix any errors and compile the objects before starting the migration process.
```
SELECT owner, object_type, object_name
FROM all_objects
WHERE status = 'INVALID';
```
Note: You should not see any invalid objects. If you have invalid objects, right-click on the correct folder and compile.

Exercise 3: Prepare to Migrate the Oracle database to PostgreSQL

Duration: 60 minutes

In this exercise, you will create an assessment report that outlines the difficulty of the migration process.

Task 1: Prepare the PostgreSQL instance using pgAdmin

In this task, we will create the new application user and create the NW database.

To open pgAdmin, use the Windows Search utility. Type pgAdmin.
PgAdmin will prompt you to set a password to govern access to database credentials. Enter oracledemo123. Confirm your choice. For now, our configuration of pgAdmin is complete.
Now launch pgAdmin and enter your password oracledemo123.
Under the Quick Links section of the Dashboard, there is the option to Add New Server. When selected, the Create - Server dialog box will open.
Under the General tab, enter a name for your connection.
Navigate to the Connection tab.
- You can pull your instance's hostname from the Azure portal (it is available in the resource's overview).
- For Username, enter the admin username available on the instance's overview.
- For Password, enter the admin user password you provided during deployment.
- Select Save when you are ready to connect.
If the connection is successful, it should appear under the Servers browser dropdown.
Create a new role, which the application will reference.
- Under your connection, right-click Login/Group Roles.
- Select Create > Login/Group Role....
- Name the role NW.
Under Definition, provide a secure password.
Under Privileges, change the Can log in? slider to the Yes position.
Finally, navigate to Membership.
- Add the azure_pg_admin role.
- Do not select the checkbox next to the role name (this user will not be granting the azure_pg_admin role to others).
- Select Save.

Our configuration in pgAdmin is now complete.

Task 2: Create an ora2pg project structure

Ora2pg allows database objects to be exported in multiple files so that is simple to organize and review changes. In this task, you will create the project structure that will make it easy to do this.

Open a command prompt window and navigate to the directory C:\ora2pg, where we will create the project structure.
```
cd C:\ora2pg
rename ora2pg_conf.dist ora2pg.conf.dist
```
Note: If the ora2pg folder is not exist in the C:\ directory then please run the below commands in Windows Powershell and repeat the Step 1.
```
 
cd 'C:\handsonlab\MCW-Migrating-Oracle-to-Azure-SQL-and-PostgreSQL-master\Hands-on lab\lab-files'
.\installora2pg.ps1
```
To create a project, we will use the ora2pg command with the --init_project flag. In the example below, our migration project is titled nw_migration.
```
ora2pg --init_project nw_migration
```
Note: In some cases, ora2pg may fail to find its configuration file. In scenarios such as these, you may need to provide the -c flag with the name of the actual configuration file in your ora2pg directory. For instance, ora2pg.conf.dist did not exist in my directory, but the file ora2pg_conf.dist was available.

Note: You may receive an error that ora2pg cannot find Perl. If this is the case, just ensure that C:\Strawberry\perl\bin has been added to the PATH variable.
```
ora2pg -c ora2pg_dist.conf --init_project nw_migration
```
Verify that the command succeeded. There should be a folder with the same name as your migration project in the C:\ora2pg directory.
Navigate to the project directory.
- Locate config\ora2pg.conf.
- Select the file to open it. If you are asked to select an application to open the file, select Notepad. We will need to collect multiple parameters of the local Oracle database to enter into the configuration file. These parameters are available by entering lsnrctl status into the command prompt.

In the config\ora2pg.conf file, replace the old values in the file with the correct information.

# Set the Oracle home directory
ORACLE_HOME	C:\app\demouser\product\18.0.0\dbhomeXE

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN	dbi:Oracle:host=LabVM;sid=XE;port=1521
ORACLE_USER	NW
ORACLE_PWD	oracledemo123

Moreover, you need to populate the schema name correctly.

# Oracle schema/owner to use
SCHEMA	NW

Confirm that all information entered is correct. The command below should display the version of your local Oracle database.
```
cd nw_migration
ora2pg -t SHOW_VERSION -c config\ora2pg.conf
```
We will also need to populate connection information for our Postgre instance. We will use the role we created in the previous task.

Task 3: Create a migration report

The migration report tells us the "man-hours" required to fully migrate to our application and components. The report will provide the user with a relative complexity value. In this task, we will retrieve the migration report for our migration.

Navigate to the C:\ora2pg\nw_migration directory in command prompt.
Ora2pg provides a reporting functionality that displays information about the objects in the existing schema and the estimated effort required to ensure compatibility with PostgreSQL. The command below creates a report titled 6-23-report.html in the reports folder (when executed within the C:\ora2pg\nw_migration directory).
```
ora2pg -c config\ora2pg.conf -t SHOW_REPORT --estimate_cost --dump_as_html > reports\6-23-report.html
```
Note: The report displays information for the provided schema--in our case, we placed schema information in config\ora2pg.conf before executing the command.

Note: The invalid objects count was zero. Also, if the utility assessed database objects, they were listed in the report details.

Of particular interest is the migration level. In our case, it is B-5, which implicates code rewriting, since there are multiple stored procedures that must be altered.

Exercise 4: Migrate the Database and Application

Duration: 90 minutes

In this exercise, we will begin the migration of the database and the application. This includes migrating database objects, the data, application code, and finally, deploying to Azure App Service.

Task 1: Migrate the basic database table schema using ora2pg

In this task, we will migrate the database table schema using ora2pg and psql, a command-line utility that makes it easy to run SQL files against the database.

Exercise 3 covered planning and assessment steps. To start the database migration, DDL statements must be created for all valid Oracle objects.

In almost all migration scenarios, it is advised that table, index, and constraint schemas are kept in separate files. For data migration performance reasons, constraints should be applied to the target database only after tables are created and data copied. To enable this feature, open config\ora2pg.conf file. Set FILE_PER_CONSTRAINT, FILE_PER_INDEX, FILE_PER_FKEYS, and FILE_PER_TABLE to 1.
Call the following command in the C:\ora2pg\nw_migration directory to obtain object schemas (table schemas will be created in a file called NW-psql.sql).
```
ora2pg -c config\ora2pg.conf -o NW-psql.sql -t TABLE -b schema\tables\
```
In our scenario, 13 tables are exported. If you see an unreasonably large number, verify that you provided a schema in the configuration file (see step 8 of the previous task). If all was successful, you will see four files in the schema\tables directory.

Note: Open the schema\tables\NW-psql.sql file. Notice that all table names are lowercase--using uppercase names for tables and/or columns will require quotations whenever referenced. Furthermore, ora2pg converts data types fairly well. If you have strong knowledge of the stored data, you can modify types to improve performance. You can export individual table schemas in separate files to facilitate the review.
In the command prompt in the C:\ora2pg\nw_migration directory.
- Enter the following command to run the NW-psql.sql file to create tables in the NW database.
- [Server Name] - Enter your Azure PostgreSQL database's DNS name as the value passed to the -h flag. You can find this Server name in the Azure PostgreSQL overview.
- If the connection is successful, you will be asked to enter your password.
- Then, the command prompt should show a sequence of CREATE TABLE statements.
```
cd C:\ora2pg\nw_migration
psql -U NW@[Server Name] -h [Server Name].postgres.database.azure.com -d NW < schema\tables\NW-psql.sql
```
- Navigate to pgAdmin. Refresh the database objects. Verify the tables exist in pgAdmin.

Task 2: Migrate Table Data with ora2pg

In this Task, we will use the ora2pg utility to migrate table data to the PostgreSQL instance, now that we have created the table schema on the landing zone.

Open Explorer and navigate to C:\ora2pg\nw_migration\config. Edit the ora2pg.conf file. Make sure the PG_DSN, PG_USER, and PG_PWD parameters have the correct values.
Navigate to C:\ora2pg\nw_migration\data in command prompt and enter the following command.
```
cd C:\ora2pg\nw_migration\data
ora2pg -t COPY -o data.sql -c ..\config\ora2pg.conf
```
You should see the following once the command completes. Notice how all 3,308 rows are accounted for.

Note: It may take up to 5 minutes for the export to start. If you get authentication errors, double-check your ora2pg config file PG_DSN, PG_USER, and PG_PWD parameters. NW must be in upper case. Capitalization matters.

Task 3: Finishing the table schema migration

We migrated the data before the constraints were applied to reduce the time required to copy data into the tables. In addition, if foreign keys were present on the target tables, data migration would fail and take longer to import. So, in this task, we will add constraints, foreign keys, and indexes to the target tables. This task assumes that you are in the C:\ora2pg\nw_migration directory in command prompt.

First, layer on constraints (not foreign keys):

cd C:\ora2pg\nw_migration
psql -U NW@[Server Name] -h [Server Name].postgres.database.azure.com -d NW < schema\tables\CONSTRAINTS_NW-psql.sql

Now, Add foreign keys:

psql -U NW@[Server Name] -h [Server Name].postgres.database.azure.com -d NW < schema\tables\FKEYS_NW-psql.sql

Next, layer on the indexes:

psql -U NW@[Server Name] -h [Server Name].postgres.database.azure.com -d NW < schema\tables\INDEXES_NW-psql.sql

Navigate to pgAdmin and Refresh the tables in the left panel. Verify the indexes and constraints have been applied.
Before migrating views in the next task, let's verify that table data has been properly migrated. Open pgAdmin and connect to the database as the NW user. To use Query Tool, select Query Tool under the Tools dropdown.

Note: You will need to select the NW database before accessing the Query Tool.

Enter the following query into the editor:

SELECT CONCAT(firstname, ' ', lastname) as name,
   territorydescription
FROM employees e
 JOIN employeeterritories et ON e.employeeid = et.employeeid
 JOIN territories t ON et.territoryid = t.territoryid;

Now, execute the query by selecting the execution button on the toolbar.
If you were successful, you should see an output similar to the following. The result set should have 49 rows. It is available under the Data Output tab.

Next, let's take a look at migrating views.

Task 4: Migrate Views

Views are not referenced by the sample application, but we are including this task here to show you how to do it manually. When we migrate stored procedures, we will show you how to enable an extension that greatly simplifies the migration of objects which reference Oracle-specific functions.

Navigate to the C:\ora2pg\nw_migration\schema\views directory, where we will run ora2pg and psql.
```
cd c:\ora2pg\nw_migration\schema\views
ora2pg -c ..\..\config\ora2pg.conf -t VIEW -o NW-views.sql
```
Note: Views are exported into individual files. The file specified in the command (NW-views.sql) references the individual files.
Before we invoke NW-views.sql, we will need to make changes to four files. This is because our application uses a to_date() function that is not supported in PostgreSQL. We will need to replace the command in the code with the equivalent DATE() function in PostgreSQL.
Open SALES_TOTALS_BY_AMOUNT_NW-views.sql and replace the existing last line:

with this:
Open the QUARTERLY_ORDERS_NW-views.sql and replace the to_date(Orders.OrderDate, 'MM/DD/YYYY') function with DATE(Orders.OrderDate) function. Remember, the DATE() function does NOT have the same parameters.

Note: The other two applications of the to_date() function in that file are acceptable, as seen below.
Open the PRODUCT_SALES_FOR_1997_NW-views.sql and replace the to_date(Orders.ShippedDate, 'MM/DD/YYYY') function with DATE(Orders.ShippedDate) function.
Open the SALES_BY_CATEGORY_NW-views.sql and replace the to_date(Orders.OrderDate, 'MM/DD/YYYY') function with DATE(Orders.OrderDate) function.

Now that all modifications are complete, run the NW-views.sql file in psql:

psql -U NW@[DB Name] -h [DB Name].postgres.database.azure.com -d NW < NW-views.sql

With that, we have migrated views.
- Navigate to the Query Editor and test these migrated views.
- Utilize the query below, which will show data where productsales is greater than 5000. You can envision how this would be useful in an organization to identify successful items in a given year (1997).
```
SELECT *
FROM product_sales_for_1997
WHERE productsales > 5000;
```
When the query is executed, you should see the following result set, with 42 rows. This shows that we have successfully migrated the views.

Let's migrate stored procedures next.

Task 5: Migrate the Stored Procedure

Our application utilizes a single stored procedure, so we must be able to migrate it. To do this, we will be using the orafce extension, which provides functions that are compatible with Oracle code. We will then call the procedure and view its results using a refcursor.

Open C:\ora2pg\nw_migration\config\ora2pg.conf. There is a directive titled PLSQL_PGSQL. Uncomment it and set the value to 1. This is necessary for the stored procedure migration.

# Enable PLSQL to PLPSQL conversion. This is a work in progress, feel
# free modify/add you own code and send me patches. The code is under
# function plsql_toplpgsql in Ora2PG/PLSQL.pm. Default enabled.
PLSQL_PGSQL	1

Only one stored procedure, NW.SALESBYYEAR, is in use by the application. So, we will export this stored procedure from the Oracle database for analysis. Run the command below in C:\ora2pg\nw_migration\schema\procedures.
```
cd C:\ora2pg\nw_migration\schema\procedures
ora2pg -c ..\..\config\ora2pg.conf -t PROCEDURE -a SALESBYYEAR -o NW-proc.sql
```
Open SALESBYYEAR_NW-proc.sql. Notice that ora2pg exported the Oracle procedure as a PostgreSQL procedure. In some cases, ora2pg exports procedures as functions. Whether that is acceptable depends on if the object needs to return a value and if transactions must be defined within the object. Note that the exported stored procedure is defined as SECURITY DEFINER, removing support for transaction control.

A second detail to keep in mind is NULLs vs. empty strings. In PostgreSQL, they are handled differently. This is a small distinction in Oracle that can be overlooked, leading to incomplete query results.
We will need to edit the procedure's parameter list, and we can do this by using a refcursor. Replace the existing last parameter of the procedure:

with this:

Save your edits.
A useful PostgreSQL extension that facilitates greater compatibility with Oracle database objects is orafce, which is provided with Azure Database for PostgreSQL. To enable it, navigate to pgAdmin, enter your master password, and connect to your PostgreSQL instance. Then, enter the following command into the query editor and execute it:
```
CREATE EXTENSION orafce;
```

Now, you will need to execute the NW-proc.sql file against the PostgreSQL instance.

psql -U NW@[Server Name] -h [Server Name].postgres.database.azure.com -d NW < NW-proc.sql

Execute the following statements. Note that pgAdmin requires that each statement be executed independently.

BEGIN;
CALL salesbyyear('1996-01-01'::timestamp, '1999-01-01'::timestamp, 'cur_out');
FETCH ALL FROM cur_out;
COMMIT;

If all is successful, 809 rows should be returned. The following is an excerpt from the result set, which can be retrieved by executing the FETCH statement.

Task 6: Create new Entity Data Models and update the application on the Lab VM

In this task, we will be recreating the ADO.NET data models to accurately represent our PostgreSQL database objects.

On your Lab VM, return to Visual Studio, and open appsettings.json from the Solution Explorer.
Add a connection string called PostgreSqlConnectionString. Ensure that it correctly references the remote Azure Database for PostgreSQL credentials.
- Replace the value of Server with your Azure Database for PostgreSQL DNS name.
- Substitute the Server Name.
- Verify the value of Password is set.
```
"ConnectionStrings": {
   "OracleConnectionString": "DATA SOURCE=localhost:1521/XE;PASSWORD=oracledemo123;USER ID=NW",
   "PostgreSqlConnectionString": "Server={Server};Database=NW;Port=5432;User Id=NW@{Server Name};Password={Password};Ssl Mode=Require;"
}
```
Save the appsettings.json file.

Note: In production scenarios, it is not recommended to store connection strings in files that are checked into version control. Consider using Azure Key Vault references in production and user secrets in development.
In visual studio select Tools (1), NuGet Package Manager (2), and Manage NuGet Packages for Solution... (3).
Now, navigate to the Updates tab (1). Click the Select all packages button (2). Lastly, select Update (3).

Once this step completes, restart Visual Studio 2019.
Open the Package Manager console by selecting Tools (1), NuGet Package Manager (2), and Package Manager Console (3).
Enter the following command in the Package Manager console. It will install the open-source Npgsql Entity Framework Core provider.
```
Install-Package Npgsql.EntityFrameworkCore.PostgreSQL
```
Enter the following command in the Package Manager console to produce the models. The -Force flag eliminates the need to manually clear the Data directory.
```
Scaffold-DbContext Name=ConnectionStrings:PostgreSqlConnectionString Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir Data -Context DataContext -Schemas public -Force
```
Note: This command will reverse-engineer more tables than are needed. The -Tables flag, referencing schema-qualified table names, provides a more accurate approach.
Attempt to build the solution to identify errors.
Expand the Views folder. Delete the following folders, each of which contains five views:

Customers
Employees
Products
Shippers
Suppliers

Expand the Controllers folder. Delete all controllers, except HomeController.cs.

Open DataContext.cs. Add the following line to the top of the file, below the other using directives.

using NorthwindMVC.Models;

Add the following below the other property definitions.

// Existing property definitions
public virtual DbSet<Supplier> Suppliers { get; set; }
public virtual DbSet<Territory> Territories { get; set; }

// Add SalesByYearDbSet
public virtual DbSet<SalesByYear> SalesByYearDbSet { get; set; }

Lastly, add the following statement to the OnModelCreating() method, after setting the collation information.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Collation information

    // Add this:
    modelBuilder.Entity<SalesByYear>(entity =>
    {
        entity.HasNoKey();
    });

    // Other Fluent API configuration
}

Build the solution. Ensure that no errors appear. We added SalesByYearDbSet to DataContext because HomeController.cs references it. We deleted the controllers and their associated views because we will scaffold them again from the models.
Right-click the Controllers folder and select Add (1). Select New Scaffolded Item... (2).

Select MVC Controller with views, using Entity Framework. Then, select Add.
In the ADD MVC Controller with views, using Entity Framework dialog box, provide the following details. Then, select Add. Visual Studio will build the project.
- Model class: Select Customer.
- Data context class: Select DataContext.
- Select all three checkboxes below Views.
- Controller name: Keep this set to CustomersController.

Repeat steps 12-14, according to the following details:
- EmployeesController.cs
  - Based on the Employee model class
- ProductsController.cs
  - Based on the Product model class
- ShippersController.cs
  - Based on the Shipper model class
- SuppliersController.cs
  - Based on the Supplier model class

Navigate to Startup.cs. Ensure that PostgreSQL is configured as the correct provider and the appropriate connection string is referenced in the ConfigureServices method.

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    // Verify this line:
    services.AddDbContext<DataContext>(options => options.UseNpgsql(Configuration.GetConnectionString("PostgreSqlConnectionString")));
}

Task 7: Update the Dashboard Stored Procedure Call

With PostgreSQL, stored procedures cannot return output values without a cursor. This Task details how to write a function to replicate the same logic. Functions can return result sets directly, without a cursor.

Open PostgreSQL pgAdmin. Connect to the NW database as the NW user. Launch the query tool.

Into the query tool, copy the following statement and execute it.

CREATE OR REPLACE FUNCTION SALESBYYEAR_func (p_begin_date TIMESTAMP, p_end_date TIMESTAMP)
RETURNS TABLE (
	ShippedDate TIMESTAMP,
	OrderID bigint,
	Subtotal double precision,
	Year text
)
AS $$
BEGIN
    RETURN QUERY 
    SELECT Orders.ShippedDate, 
            Orders.OrderID, 
            Order_Subtotals.Subtotal, 
            TO_CHAR(Orders.ShippedDate, 'YY') AS Year
    FROM Orders INNER JOIN Order_Subtotals ON Orders.OrderID = Order_Subtotals.OrderID
    WHERE Orders.ShippedDate Between p_begin_date And p_end_date;
END;
$$ 
LANGUAGE 'plpgsql';

In the Visual Studio solution, navigate to the HomeController. Comment out the code under the Oracle comment. First, select the lines for the Oracle code, then select the Comment button in the toolbar.
In the HomeController.cs, add the following using references:
```
using NpgsqlTypes;
using Npgsql;
```

Below the commented Oracle code and before the LINQ query, add the following:

var beginDate = new NpgsqlParameter { ParameterName = "beginDate", NpgsqlDbType = NpgsqlDbType.Timestamp, Direction = ParameterDirection.Input, Value = new NpgsqlDateTime(DateTime.Parse("Jan 1, 1996")) };
var endDate = new NpgsqlParameter { ParameterName = "endDate", NpgsqlDbType = NpgsqlDbType.Timestamp, Direction = ParameterDirection.Input, Value = new NpgsqlDateTime(DateTime.Parse("Jan 1, 1999")) };

var salesByYear = await _context.SalesByYearDbSet.FromSqlRaw("SELECT * FROM SALESBYYEAR_func(@beginDate, @endDate);", beginDate, endDate).ToListAsync();

Navigate to the Models folder in the Visual Studio Solution. In the SalesByYear.cs class, update the type of the OrderID property to long.
```
public long OrderID { get; set; }
```
Run the application again by selecting the green Start button in the Visual Studio toolbar.
Verify the graph is showing correctly on the Northwind Traders dashboard.

Task 8: Deploy the application to Azure

As part of the PoC, the finished app will be hosted on Azure App Service. In this task, you will add a connection string to the App Service resource and use Visual Studio 2019 to complete the deployment.

In the Azure portal, navigate to your App Service instance. Navigate to Configuration below Settings.
Below Connection strings, select + New connection string. In the Add/Edit connection string window, provide the following:
- Name: Use PostgreSqlConnectionString.
- Value: Use the connection string from the appsettings.json file.
- Type: Select Custom (with ASP.NET Core, it is not possible to use the PostgreSQL connection string type).
Select OK and then select Save.
In Visual Studio's Solution Explorer, right-click the NorthwindMVC project (not the solution) and select Publish....
The Publish window should open. Select Azure. Select Next.
Select Azure App Service (Linux). Select Next.
In the Publish window, select your Subscription name. Expand the correct resource group and select the App Service resource. Select Next.
Select Publish (generates pubxml file) for the Deployment type tab. Select Finish.
Select Publish next to the new publish profile.
First, your application will build. Then, all relevant files will be copied into a ZIP archive for deployment.
Once the build completes, navigate to your app's link. Test the web application.

Note: If you still see the default page display, try publishing again.

Note: Feel free to remove the connection string from the appsettings.json file, as it is securely provided to the application through Azure App Service. This is usually done to avoid committing connection strings into version control.

After the hands-on lab

Duration: 10 minutes

In this exercise, you will delete any Azure resources that were created in support of the lab. You should follow all steps provided after attending the Hands-on lab to ensure your account does not continue to be charged for lab resources.

Task 1: Delete the resource group

Using the Azure portal, navigate to the Resource group you used throughout this hands-on lab by selecting Resource groups in the left menu.
Search for the name of your research group, and select it from the list.
Select Delete in the command bar, and confirm the deletion by re-typing the Resource group name, and selecting Delete.

You should follow all steps provided after attending the Hands-on lab.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

HOL step-by-step - Migrating Oracle to PostgreSQL.md

HOL step-by-step - Migrating Oracle to PostgreSQL.md

Migrating Oracle to PostgreSQL hands-on lab step-by-step

Abstract and learning objectives

Overview

Solution architecture

Requirements

Exercise 1: Setup Oracle 18c Express Edition

Task 1: Install the pre-req applications.

Task 2: Create the Northwind database in Oracle 18c XE

Task 3: Configure the Starter Application to use Oracle

Exercise 2: Assess the Oracle 18c Database before Migrating to PostgreSQL

Task 1: Update Statistics and Identify Invalid Objects

Exercise 3: Prepare to Migrate the Oracle database to PostgreSQL

Task 1: Prepare the PostgreSQL instance using pgAdmin

Task 2: Create an ora2pg project structure

Task 3: Create a migration report

Exercise 4: Migrate the Database and Application

Task 1: Migrate the basic database table schema using ora2pg

Task 2: Migrate Table Data with ora2pg

Task 3: Finishing the table schema migration

Task 4: Migrate Views

Task 5: Migrate the Stored Procedure

Task 6: Create new Entity Data Models and update the application on the Lab VM

Task 7: Update the Dashboard Stored Procedure Call

Task 8: Deploy the application to Azure

After the hands-on lab

Task 1: Delete the resource group

Files

HOL step-by-step - Migrating Oracle to PostgreSQL.md

Latest commit

History

HOL step-by-step - Migrating Oracle to PostgreSQL.md

File metadata and controls

Migrating Oracle to PostgreSQL hands-on lab step-by-step

Abstract and learning objectives

Overview

Solution architecture

Requirements

Exercise 1: Setup Oracle 18c Express Edition

Task 1: Install the pre-req applications.

Task 2: Create the Northwind database in Oracle 18c XE

Task 3: Configure the Starter Application to use Oracle

Exercise 2: Assess the Oracle 18c Database before Migrating to PostgreSQL

Task 1: Update Statistics and Identify Invalid Objects

Exercise 3: Prepare to Migrate the Oracle database to PostgreSQL

Task 1: Prepare the PostgreSQL instance using pgAdmin

Task 2: Create an ora2pg project structure

Task 3: Create a migration report

Exercise 4: Migrate the Database and Application

Task 1: Migrate the basic database table schema using ora2pg

Task 2: Migrate Table Data with ora2pg

Task 3: Finishing the table schema migration

Task 4: Migrate Views

Task 5: Migrate the Stored Procedure

Task 6: Create new Entity Data Models and update the application on the Lab VM

Task 7: Update the Dashboard Stored Procedure Call

Task 8: Deploy the application to Azure

After the hands-on lab

Task 1: Delete the resource group