Script nms_schema_pre_migrate.sql Is Producing an Error With NMS v2.5.0.0.4 (Doc ID 2761772.1)

Last updated on OCTOBER 15, 2021

Applies to:

Symptoms

Script nms_schema_pre_migrate.sql is producing an error with the 2.5.0.0.4 version

You're attempting to migrate a database from a prior release NMS v2.4 or earlier.

First time you ran through the migration, NMS v2.5.0.0.0 was installed and you didn't have any issues.
Now you're trying to run through the migration again with 2.5.0.0.4 installed.

Your migration is progressing nowhere because of a database error when nms-apply-migrations tries to run nms_schema_pre_migrate.sql. The diffs between the 2 versions are as follows with the new file version indicated with <:

nms_schema_pre_migrate.sql
51c51
< IF table_exists('PR_MIGRATIONS')
---
> IF NOT prod_synonym_exists('PR_MIGRATIONS')

53c53
< IF NOT prod_synonym_exists('PR_MIGRATIONS')
---
> IF table_exists('PR_MIGRATIONS')

57d56
< EXECUTE IMMEDIATE 'grant select, insert, update, delete on pr_migrations to ces_rw';

59,60d57

I ran the file's pl/sql by hand to try to pinpoint the issue and get this:
50 BEGIN
51 IF table_exists('PR_MIGRATIONS')
52 THEN
53 IF NOT prod_synonym_exists('PR_MIGRATIONS')
54 THEN
55 EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM __@__.pr_migrations FOR pr_migrations';
END IF;
56 57 -- EXECUTE IMMEDIATE 'grant select, insert, update, delete on pr_migrations to ces_rw';
END IF;
58 59 end;
60 /
DECLARE
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 55

Changes

Updated to NMS v2.5.0.0.4

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.