Creating Synonyms and Database Links

The mechanism that is used to make database objects in the standby database available in the PeopleSoft implementation of Oracle Active Data Guard requires the existence of a secondary access ID. The secondary access ID does not own any objects, so sufficient access to and awareness of objects in the primary database must be made to the secondary access ID.

To associate standby database objects with the equivalents in the primary database, you must create:

  • Local synonyms.

  • Database links to the primary database.

  • Remote synonyms.

PeopleSoft delivers the following scripts (located in PS_HOME\scripts) to create the synonyms and database links:

  • createlocalsynonyms.sql

  • createdblinktoprimary.sql

  • createremotesynonyms.sql

The following table describes each script, and any modifications that you need to make before you run them.

Script Description Usage

createlocalsynonyms.sql

This script generates the following four sql scripts:

  • createlocaltablesynonyms.sql

  • createlocalviewsynonyms.sql

  • grantselectontables.sql

  • grantselectonviews.sql

Execute the generated scripts to create local synonyms for all PeopleSoft tables and views, and to grant select privileges to the secondary access ID for all PeopleSoft tables and views.

Run these scripts against the primary database while connected as the secondary access ID:

  • createlocaltablesynonyms.sql

  • createlocalviewsynonyms.sql

Run these scripts against the primary database while connected as the primary access ID.

  • grantselectontables.sql

  • grantselectonviews.sql

Run this script against the primary database while connected as the secondary access ID.

Before you run the script, edit it to make the following changes:

  • Replace <SYSADM> with the primary access ID.

  • Replace <SYSADMS> with the secondary access ID.

createdblinktoprimary.sql

This script creates a fixed database link between the secondary access ID and the primary access ID on the primary database.

Run this script against the primary database while connected as the secondary access ID

Before you run the script, edit it to make the following changes:

  • Replace <DBNAME> with the primary database TNSALIAS.

  • Replace <SYSADM> with the primary access ID.

  • Replace <PASSWORD> with the primary access ID password.

createremotesynonyms.sql

This script creates remote synonyms.

Remote synonyms are required for the tables identified as requiring DML access. If you decide to set a component to "read-only" after your analysis, then you need to include the underlying tables that require DML access to this script manually.

Run this script against the primary database while connected as the secondary access ID.