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.
-
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.
-
-
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';
-
-
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';
-
Add the tns entries to the tnsnames.ora file on each server.
Verify that the database link is working
-
Execute the following query:
Select * from dual@<global db name>
-
<global db name> - The fully qualified name of the Oracle server.
-