Project and Portfolio Management Practitioners Forum
cancel

Errors while migrating PPM database schema into a new database

SOLVED
Go to solution
Highlighted
HamedKhan
Regular Contributor.

Errors while migrating PPM database schema into a new database

Hello

 

I am in the process of migrating PPM filesystem instance and database schemas and am following the steps in the PPM 9.10 installation guide.

 

However during the db schema import I am getting errors which look like below. They seem to be related to the fact that there are oracle schema differences between the source vs the destination. This is to be expected and I have configured the imp command like this:

 

imp USERID=system/<password>@ppmdemo FILE=C:/PPM_DATA_SCHEMA.DMP IGNORE=Y TOUSER=ppm_db_user FROMUSER=PPM LOG=c:/import_ppm_data.log

 

Are these errors normal and to be expected? Can someone please share their experience of how they remediated this?

 

========================================================

IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "DSH_CLOB_TYPE" ("ID" NUMBER(19, 0) NOT NULL ENABLE, "VALUE" C"
 "LOB, "VERSION" NUMBER(10, 0) NOT NULL ENABLE)  PCTFREE 10 PCTUSED 40 INITRA"
 "NS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS"
 " 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" LOGGING NO"
 "COMPRESS LOB ("VALUE") STORE AS BASICFILE  (TABLESPACE "USER_DATA" ENABLE S"
 "TORAGE IN ROW CHUNK 8192 RETENTION  NOCACHE LOGGING  STORAGE(INITIAL 65536 "
 "NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT"
 "))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USER_DATA' does not exist

...

...

IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT SELECT ON "KPMO_PROGRAM_ISSUES_V" TO "PPM_RML""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PPM_RML' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT SELECT ON "KPMO_PROGRAM_LIST_V" TO "PPM_RML""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PPM_RML' does not exist

 

========================================================

9 REPLIES
mdadok
Respected Contributor.

Re: Errors while migrating PPM database schema into a new database

Hi,

 

From what I see in the logs the target database is missing required objects like tablespace USER_DATA and user PPM_RML. The target database has to be prepared properly before import as it is described in the installation guide. Have you followed the steps to get the target database ready?


 

Regards,

Maciek

HamedKhan
Regular Contributor.

Re: Errors while migrating PPM database schema into a new database

Hello thanks for the prompt response. Here is what I did to prep the target database. What am I missing?

 

 

1. create new file in C:\ITG\utilities\db\system\HK_CreateTableSpaces.sql and copy in data below:

 

 CREATE TABLESPACE PPM_Data
 datafile 'c:/app/hamedkhan/oradata/ppmdemo/ppmdemo_ppm_data01.dbf'
 size 1024m
 AUTOEXTEND ON MAXSIZE 4096m
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
 SEGMENT SPACE MANAGEMENT AUTO;

 

 CREATE TABLESPACE PPM_Index
 datafile 'c:/app/hamedkhan/oradata/ppmdemo/ppmdemo_ppm_index01.dbf'
 size 1024m
 AUTOEXTEND ON MAXSIZE 4096m
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
 SEGMENT SPACE MANAGEMENT AUTO;

 

 CREATE TABLESPACE PPM_Clob
 datafile 'c:/app/hamedkhan/oradata/ppmdemo/ppmdemo_ppm_clob01.dbf'
 size 1024m
 AUTOEXTEND ON MAXSIZE 4096m
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
 SEGMENT SPACE MANAGEMENT AUTO;

 


2. open cmd and change to C:\ITG\utilities\db\system\ and type:

 

sqlplus "sys/<password> as sysdba" @HK_CreateTableSpaces.sql

 

3. create main ppm schema user ppm_db_user by running @CreateKintanaUser.sql

 

4. then create rml schema user ppm_rml_user by running - sqlplus "sys/0ptimizE as sysdba" @CreateRMLUser.sql

 

5. in cmd change to C:\app\hamedkhan\product\11.2.0\dbhome_1\BIN

 

6. run the following comand:

 

imp USERID=system/<password>@ppmdemo FILE=C:/HK_PPM_DATA_SCHEMA.DMP IGNORE=Y TOUSER=ppm_db_user FROMUSER=PPM LOG=c:/hk_import_ppm_data.log

 

And here is where I get the error. Please help.

HamedKhan
Regular Contributor.

Re: Errors while migrating PPM database schema into a new database

The schema names are different between the source and target. That should be ok right?

mdadok
Respected Contributor.
Solution

Re: Errors while migrating PPM database schema into a new database

Hi,

 

It seems that you have used suggested naming convention for tablespace and user creation in target database. However, you had different naming convention in your source database.

 

From those logs it can be easily seen that you have the following different names:

 

Source db - USER_DATA, Target db - PPM_Data

Source db - PPM_RML , Target db- ppm_rml_user

 

There are several solutions for staying with this approach, however what would be the simplest is to create same tablespace names for target DB as you have for source DB. Regarding RML schema the question is if you are using it, because it is just a matter of grants that cannot be properly transferred in this case while you have different schema names in source as well as target db.

 

Regards,

Maciek

HamedKhan
Regular Contributor.

Re: Errors while migrating PPM database schema into a new database

I am prepared to change the schema names but are you sure your response is correct. I thought that:

Source db schema - PPM, Target - ppm_db_user
Source db schema - PPM_RML , Target - ppm_rml_user

Where did USER_DATA come from?

Here is the source server.conf line item:

com.kintana.core.server.DB_USERNAME=ppm
HamedKhan
Regular Contributor.

Re: Errors while migrating PPM database schema into a new database

Are you saying that:

source tablespace - USER_DATA, Target tablespace PPM_Data

should match? Should the same apply for index and clob tablespaces?
mdadok
Respected Contributor.

Re: Errors while migrating PPM database schema into a new database

Hi,

 

Correct!

 

Regards,

Maciek

HamedKhan
Regular Contributor.

Re: Errors while migrating PPM database schema into a new database

Trying it now :)

In the source DB I can see the following tablespaces. What do you suppose the RPT tablespace relate to?

Would it be safe to create these also in the target? If so are there any post-import tasks that I must perform on these tablespaces?:

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
USER_CLOB
USER_DATA
USER_INDEX
RPT_DATA
RPT_INDEX
RPT_CLOB

TABLESPACE_NAME
------------------------------
RPT_NOLOGGING_DATA
RPT_NOLOGGING_INDEX

13 rows selected.

mdadok
Respected Contributor.

Re: Errors while migrating PPM database schema into a new database

Hi,

 

Have no idea what it could be, if you have access you can verify this.

If they are used in source DB I would created them in target DB as well.

 

Please have a deeper look at you source database and think what is really used and required to have in target database. If you want to have a target database as a clone of source database, please clone all tablespaces as well. It all depends upon the requirements you have for your target DB.

 

Regards,

Maciek