Creating the UDFs after Upgrade

During the upgrade process, the installer prompts whether to create the UDF or otherwise. If the no option is selected, the installer skips the UDF creation and proceeds to complete the upgrade. In such scenarios:

• The existing types and UDFs remain unchanged in the database.
• The new types and UDFs are not created.
• The previous Database Protector installation directory is retained as a backup.
• Manually intervention is required to drop the old types and UDFs and create the new UDFs.

The version of the SQL scripts to drop the Oracle Database Protector types and UDFs must be the same as the version that was used to create them. This is important because:

• The installer retains the previous Database Protector installation directory.
• The dropobjects.sql script from this retained backup must be used to drop the existing database objects.
• The createobjects.sql script from the new installation must be used to create the new objects.

Prerequisites

• Administrator access to the Oracle database is available.
• Database credentials for:
  • The user that owns the existing types and UDFs.
  • The user that will create the new types and UDFs.
• Access to the retained backup directory (<path_to_prev_installation_dir>/databaseprotector_<timestamp> –> example with timestamp)
• Access to the new Database Protector installation directory is available.

Dropping the Existing UDFs

1. To navigate to the directory containing the backup scripts, run the following command:

   cd <path_to_prev_installation_dir>/oracle/sqlscripts/
   

2. Press ENTER.
3. To drop the existing UDFs, run the following command with the database user that owns the existing types and UDFs with the required permissions:

   sqlplus <user_name>/<password> @dropobjects.sql
   

   where:
   • <user_name> is the database user that owns the existing types and UDFs. The user must have all required privileges to drop the Oracle Database Protector objects.

   Important: During upgrades, the existing types and UDFs may have been created by a different database user than the one you plan to use going forward. Ensure that the user used here owns the existing objects or has sufficient privileges.

Creating the New UDFs

1. To navigate to the directory containing the scripts for the new installation, run the following command:

   cd <installation_dir>/databaseprotector/oracle/sqlscripts
   

2. To create the new UDFs, run the following command with the database user that has all required permissions to create Oracle Database Protector types and UDFs:

   sqlplus <user_name>/<password> @createobjects.sql
   

   where:
   • <user_name> is the database user that owns the existing types and UDFs. The user account must comply with the requirements listed in the product documentation.

Conclusion

• The old Oracle Database Protector types and UDFs are removed.
• The new types and UDFs are created using the upgraded version.
• The upgrade process is considered complete from a database perspective.

Note: If any issues occur while dropping or creating database objects, review the SQL output and consult the product documentation. Alternatively, contact Protegrity Support.