IBM Support

How to restore a RPM Oracle Database, from an exp *.dmp file

Troubleshooting


Problem

This technote describes how to restore an IBM® Rational® Portfolio Manager (RPM) Oracle database from an exp *.dmp file even though temporary tables and leveling libraries are missed.

Resolving The Problem

Important notes:

  • Any import of a RPM Oracle database must have six NLS environment variable set before executing (Appendix A).
  • You must have a location to store the data file for the tablespaces in case the original tablespaces need to be recreated using the CreatePmoTbs.sql script (Appendix B).
  • The previous owner of the RPM object will be removed and then recreated using the script CreatePmoUser.sql (Appendix C).
  • Modifying user right during this process might impact the applications functionality.
  • The RPM Oracle database might be dependent on the open source, GCC compiler. Verifying this using use ldd LevelingLib.so (Appendix E).
  • In older versions of RPM the Leveling Library is called pmoffice.so, this is equivalent to the ldd LevelingLib.so.

RPM Oracle database restore procedure:
  1. Set Environment variables as described in Appendix A
  2. Drop the two users, Owner and Connected User ( RPM and RPM2) using the following commands:

    SQL> DROP USER RPM CASCADE;
    SQL> DROP USER RPM2 CASCADE;
  3. If you need to drop the existing Tablespaces perform that now and skip to step 6 otherwise continue with step 4.
  4. Replace all PATH_TO_DATAFILE with a valid directory as in Appendix B
  5. If you dropped your Tablespaces in step 3, execute the CreatePmoTbs.sql as SYSDBA or SYSTEM as described in Appendix B.
    • A log file CreatePmoTbs.log should be created.
    • Validate the script executed without error by reviewing the CreatePmoTbs.log.
  6. Restore the two users RPM and RPM2 using file CreatePmoUser.sql using SYSDBA or SYSTEM as described in Appendix C.
    • A log file CreatePmoUser.log should be created.
    • Execute the following command and validate that the RPM and RPM2 users have been created:

      SQL> @CreatePmoTbs.sql

      Note: The DROP ROLE PMO_USER_ROLE; might fail if the role was never present in the database.
  7. Import the FileName.dmp
    Note: If you plan to use the RPM user to import the *.dmp file make sure it has been granted the import right.
    • Use the Following command to import the *.dmp file.
      Note: Validate the NLS environment variables are set before executing.

      bash-2.05$ imp rpm/rpm file=FileName.dmp log=FileName.log fromuser=rpm touser=rpm
    • Once the import if complete review the Filename.log file for errors.
  8. Update the PLACE_PATH_TO_ORACLE_HOME_BIN in the DropCreateLib.sql script to your Oracle Home.
    Note: This is case sensitive as in Appendix D
  9. Copy and paste the recompile.sql file from the utils directory of the latest migration or installation package into the directory you will be working in.
  10. Execute the following script and validate the small report displayed making sure all the object are valid: (Review Appendix D)

    SQL> @DropCreateLib.sql
  11. Generally the GCC Version needed for the leveling code is GCC 3.4.2. Use the ldd LevelingLib.so command to identify all library dependancies needed. Depending on the version of RPM, the GCC dependencies will change. GCC 3.3.x is needed for RPM 61, 62 and 70. Also the pmoffice.so (6.1 version) is interchangeable with LevelingLib.so (6.2 and 7.0 version).
    To determine the current version used, search for the file libstdc++.so.x (x stands for the version) as described in Appendix E.
  12. Setup the LD_LIBRARY_PATH using the following command:
    Note: This technote assume that the GCC software is installed in the /usr/local/lib/sparcv9 directory.

    export LD_LIBRARY_PATH=%ORACLE_HOME/lib:%ORACLE_HOME/lib:/usr/local/lib/sparcv9
    • Validate this with the ldd pmoffice.so as described in Appendix E.
  13. Setup the listener.ora based on the example in Appendix F.
    KEY value in tnsnames.ora has to match KEY value of Listener.ora
    SID value in tnsnames.ora has to match SID_NAME value of Listener.ora
    ENVS value in Listener.ora has to include the path to RPM leveling lib
    For example:

    ENVS="EXTPROC_DLLS=$PATH_TO_LIBRARY"

    or

    ENVS="EXTPROC_DLLS=ANY"
  14. Setup the tnsnames.ora, based on example of Appendix G.
    KEY value in tnsnames.ora has to match KEY value of Listener.ora
    SID value in tnsnames.ora has to match SID_NAME value of Listener.ora
  15. Test the RPM by executing the following stored procedure to SP_RES_DEP_FOR_PROJ.


Appendix A:

NLS settings required before the import of the RPM Schema OWNER.


EXPORT NLS_SORT=”BINARY”
EXPORT NLS_TIMESTAMP_TZ_FORMAT=”YYYY-MM-DD hh24:mi:ss.ff6”
EXPORT NLS_TIMESTAMP_FORMAT=”YYYY-MM-DD hh24:mi:ss.ff6”
EXPORT NLS_NUMERIC_CHARACTERS=”.,”
EXPORT NLS_TERRITORY=”AMERICA”
EXPORT NLS_DATE_FORMAT="YYYY-MM-DD"


Back to top



Appendix B:

Contents of the CreatePmoTbs.sql file.


-- RUN AS SYSDBA or SYSTEM
spool CreatePmoTbs.log
CREATE  TEMPORARY TABLESPACE "PMO_TEMP" TEMPFILE
    'PATH_TO_DATAFILES/RPM/PMO_TEMP.dbf' SIZE 512M AUTOEXTEND ON
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;


-- Create Tablespace:PMO_LOB_64M
CREATE TABLESPACE PMO_LOB_64M LOGGING
    DATAFILE '/PATH_TO_DATAFILE/RPM/PMO_LOB_64M001.dbf'
    SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE 4096M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M SEGMENT SPACE MANAGEMENT AUTO;

-- Create Tablespace:PMO_LOB_64K
CREATE TABLESPACE PMO_LOB_64K LOGGING
    DATAFILE '/PATH_TO_DATAFILE/RPM/PMO_LOB_64K001.dbf'
    SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE 4096M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;

-- Create Tablespace:PMO_DATA_64M
CREATE TABLESPACE PMO_DATA_64M LOGGING
    DATAFILE '/PATH_TO_DATAFILE/RPM/PMO_DATA_64M001.dbf'
    SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE 4096M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M SEGMENT SPACE MANAGEMENT AUTO;

-- Create Tablespace:PMO_IDX_64K
CREATE TABLESPACE PMO_IDX_64K LOGGING
    DATAFILE '/PATHTO_DATAFILE/RPM/PMO_IDX_64K001.dbf'
    SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE 4096M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;

-- Create Tablespace:PMO_DATA_64K
CREATE TABLESPACE PMO_DATA_64K LOGGING
    DATAFILE '/PATH_TO_DATAFILE/RPM/PMO_DATA_64K001.dbf'
    SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE 4096M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;

spool off;
-- exit;


Back to top



Appendix C:

Contents of the CreatePmoUser.sql file.


-- RUN AS SYSDBA OR SYSTEM
spool CreatePmoUser.log
-- Create owner of PMO objects

CREATE USER rpm IDENTIFIED BY rpm
    DEFAULT TABLESPACE PMO_DATA_64K
    QUOTA UNLIMITED ON PMO_LOB_64M
    QUOTA UNLIMITED ON PMO_LOB_64K
    QUOTA UNLIMITED ON PMO_DATA_64M
    QUOTA UNLIMITED ON PMO_IDX_64K
    QUOTA UNLIMITED ON PMO_DATA_64K
    TEMPORARY TABLESPACE PMO_TEMP
    ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE, OEM_MONITOR, ALTER SESSION,
    CREATE SESSION, CREATE TABLE, CREATE SEQUENCE,CREATE SYNONYM,
    CREATE VIEW,CREATE DATABASE LINK, CREATE PROCEDURE, CREATE TRIGGER,
    CREATE TYPE,CREATE LIBRARY, PMO_USER_ROLE TO rpm;
GRANT EXECUTE ON SYS.DBMS_DDL TO rpm;

-- Create Connected User:RPM2
CREATE USER RPM2 IDENTIFIED BY rpm2
    DEFAULT TABLESPACE PMO_DATA_64K
    QUOTA UNLIMITED ON PMO_LOB_64M
    QUOTA UNLIMITED ON PMO_LOB_64K
    QUOTA UNLIMITED ON PMO_DATA_64M
    QUOTA UNLIMITED ON PMO_IDX_64K
    QUOTA UNLIMITED ON PMO_DATA_64K
    TEMPORARY TABLESPACE PMO_TEMP
    ACCOUNT UNLOCK;
GRANT CONNECT,RESOURCE,CREATE SESSION,ALTER SESSION,CREATE ANY SYNONYM,DROP ANY SYNONYM,PMO_USER_ROLE TO RPM2;
GRANT SELECT ON SYS.DBA_OBJECTS TO RPM2;
/

-- Create PMO_USER_ROLE
DROP ROLE PMO_USER_ROLE;
CREATE ROLE PMO_USER_ROLE;

spool off;


Back to top



Appendix D:

Contents of the DropCreateLib.sql file.


spool DropCreateLib.log

-- REPLACE THE PLACE_PATH_TO_ORACLE_HOME_BIN WITH THE PATH TO THE $ORACLE_HOME\bin DIRECTORY
-- RUN AS OWNER OF THE RPM OBJECTS

CREATE OR REPLACE LIBRARY LEVELING_LIB AS 'PLACE_PATH_TO_ORACLE_HOME_BIN\LevelingLib.so';
/

@recompile.sql
@recompile.sql

select * from user_libraries;

select object_type, status, count(status) as Total from user_objects group by object_type, status order by status;

spool off;


Back to top



Appendix E:


bash-2.05$ ldd -r pmoffice.so
        libclntsh.so.10.1 =>     /oracle/ora10/lib/libclntsh.so.10.1
        librt.so.1 =>    /usr/lib/64/librt.so.1
        libkstat.so.1 =>         /usr/lib/64/libkstat.so.1
        libm.so.1 =>     /usr/lib/64/libm.so.1
        libthread.so.1 =>        /usr/lib/64/libthread.so.1
        libnsl.so.1 =>   /usr/lib/64/libnsl.so.1
        libsocket.so.1 =>        /usr/lib/64/libsocket.so.1
        libgen.so.1 =>   /usr/lib/64/libgen.so.1
        libdl.so.1 =>    /usr/lib/64/libdl.so.1
        libstdc++.so.5 =>        /usr/local/lib/sparcv9/libstdc++.so.5  
        libnnz10.so =>   /oracle/ora10/lib/libnnz10.so
        libaio.so.1 =>   /usr/lib/64/libaio.so.1
        libc.so.1 =>     /usr/lib/64/libc.so.1
        libmd5.so.1 =>   /usr/lib/64/libmd5.so.1
        libmp.so.2 =>    /usr/lib/64/libmp.so.2
        libgcc_s.so.1 =>         /usr/lib/64/libgcc_s.so.1
        /usr/platform/SUNW,Ultra-4/lib/sparcv9/libc_psr.so.1
        /usr/platform/SUNW,Ultra-4/lib/sparcv9/libmd5_psr.so.1


Back to top



Appendix F:

ENTRY FOR THE LEVELING CONFIGURATION FOR listener.ora

LISTENER =
 (ADDRESS_LIST =
  (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = IP_ADDRESS)
    (PORT = 1521)
   )
   (ADDRESS =
    (PROTOCOL = IPC)
    (KEY = EXTPROC0)
   )
 )

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /oracle/ora10)
   (PROGRAM = extproc)
   (ENVS="EXTPROC_DLLS=ANY")
  )
  (SID_DESC =
   (GLOBAL_DBNAME = ORA4SUN)
   (ORACLE_HOME = /oracle/ora10)
   (SID_NAME = ORA4SUN)
  )
 )


Back to top


Appendix G:

ENTRY FOR THE LEVELING CONFIGURATION FOR tnsnames.ora

EXTPROC_CONNECTION_DATA =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS =
    (PROTOCOL = IPC)
    (KEY = EXTPROC0)
    )
   )
   (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
   )
  )


Back to top



[{"Product":{"code":"SSRR2G","label":"Rational Portfolio Manager"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database Configuration","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"6.0;6.1;6.1.1.1;6.1.1.2;6.1.1.3;6.1.2.7;6.2;6.2.2.3;7.0.0.0;7.0.1.1;7.0.2.2;7.0.3.3;7.0.4.4;7.0.5.5","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

More support for:
Rational Portfolio Manager

Software version:
6.0, 6.1, 6.1.1.1, 6.1.1.2, 6.1.1.3, 6.1.2.7, 6.2, 6.2.2.3, 7.0.0.0, 7.0.1.1, 7.0.2.2, 7.0.3.3, 7.0.4.4, 7.0.5.5

Operating system(s):
AIX, HP-UX, Linux, Solaris

Document number:
90153

Modified date:
16 June 2018

UID

swg21256747

Page Feedback