Regenerate the reports database with linked Oracle servers - Intergraph Smart 3D - Administration & Configuration - Hexagon

If the site, catalog, and model databases are not on a single server, then you cannot regenerate report databases until you configure linked servers, as shown in the following procedure.

1. In Oracle, run the following scripts to create a database login on both servers:

   DROP USER "LINKUSER" CASCADE;

   CREATE USER "LINKUSER” PROFILE DEFAULT IDENTIFIED BY linkpass

   DEFAULT

   TABLESPACE USERS TEMPORARY

   TABLESPACE TEMP ACCOUNT UNLOCK;

   GRANT UNLIMITED TABLESPACE TO "LINKUSER" WITH ADMIN OPTION;

   GRANT EXECUTE ON SYS.UTL_FILE TO "LINKUSER" WITH GRANT OPTION;

   GRANT EXECUTE ON SYS.DBMS_RANDOM TO "LINKUSER" WITH GRANT OPTION;

   GRANT SELECT ON V_$SESSION TO "LINKUSER" WITH GRANT OPTION;

   GRANT SELECT ON V_$INSTANCE TO "LINKUSER" WITH GRANT OPTION;

   GRANT ANALYZE ANY TO "LINKUSER";

   GRANT SP3D_PROJECT_ADMINISTRATORS TO "LINKUSER";

   COMMIT;

   • LINKUSER - The user name for the link.

   • linkpass - The password for the link.

2. Create the database link on one server by executing the following query:

   CREATE PUBLIC DATABASE LINK <global db name>

   CONNECT TO "<link user>" IDENTIFIED BY <link user password> USING '<service name>';

   • <global db name> - The linked Oracle Service name.

   • <link user> - The user name for the link.

   • <link user password> - The password for the link.

   • <service name> - The service name in the tnsnames.ora file.

   For example:

   Execute Query On Server 1

   CREATE PUBLIC DATABASE LINK MyServer2.MyCompany.COM

   CONNECT TO "LINKUSER" IDENTIFIED BY linkpass USING 'MyServer2';

3. Repeat the previous step for the other server, changing the server name as necessary.

   For example:

   Execute Query On Server 2

   CREATE PUBLIC DATABASE LINK MyServer1.MyCompany.COM

   CONNECT TO "LINKUSER" IDENTIFIED BY linkpass USING 'MyServer1';

4. Add the tns entries to the tnsnames.ora file on each server.

Verify that the database link is working

1. Execute the following query:

   Select * from dual@<global db name>

   • <global db name> - The fully qualified name of the Oracle server.