2 Common Data Replication and Integration Tasks

Setting the GLOBAL_NAMES Initialization Parameter to TRUE

To access data in multiple locations, you must first ensure that each location can be uniquely identified. Next, you must establish a communication path between these locations.

The unique identifier for each database is referred to as its global database name. By setting the initialization parameter GLOBAL_NAMES to TRUE, you guarantee that each database in your distributed database environment can be uniquely identified. A database forms a global database name by prefixing the database network domain, specified by the DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by the DB_NAME initialization parameter.

The GLOBAL_NAMES parameter specifies whether a database link is required to have the same name as the database to which it connects. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to TRUE at each database to ensure the use of consistent naming conventions for databases and links in a networked environment.

To set the GLOBAL_NAMES initialization parameter to TRUE at a database: 

1. Log in to Enterprise Manager as an administrative user who can change initialization parameters. For example, you can log in as a user with SYSDBA privilege.

2. Go to the Database Home page for the database instance.

3. Click Server to open the Server subpage.

4. Click Initialization Parameters in the Database Configuration section.

5. If you are using a server parameter file, then click SPFile. Otherwise, proceed to the next step.

6. On the Initialization Parameters page, enter GLOBAL_NAMES in the search tool.

   
   Description of the illustration tdpii_init_params.gif
   
   

7. Click Go.

8. Set the GLOBAL_NAMES initialization parameter to TRUE.

9. Click Apply to save your changes.

   Ensure that you set the parameter permanently in either the server parameter file or in your initialization parameter file.

10. Complete Steps 1 through 9 for each database in your distributed environment. By default, the GLOBAL_NAMES initialization parameter is set to FALSE. Therefore, it must be set to TRUE explicitly at each database.

If you were directed to this topic from another topic, then go back to the topic now:

• "Preparing to Access and Modify Information in Multiple Oracle Databases"

• "Preparing for Oracle Streams Replication"

• "Preparing for Materialized View Replication"

• "Preparing for Messaging"

Tutorial: Configuring an Oracle Streams Administrator

If you plan to use any of the components of Oracle Streams in your environment, then configure an Oracle Streams administrator. Oracle Streams components include:

• Queues

• Queue tables

• Capture processes

• Synchronous captures

• Propagations

• Apply processes

• Rules and rule sets

An Oracle Streams administrator configures and manages these components at each database where they are used. See the following topics in this guide for information about these components:

• Chapter 4, "Replicating Data Using Oracle Streams"

• Chapter 9, "Sending Messages Using Oracle Streams Advanced Queuing"

This tutorial creates the tablespace for an Oracle Streams administrator, creates the Oracle Streams administrator, and grants the Oracle Streams administrator the following privileges:

• The privileges granted by the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package

  These privileges are essential for an Oracle Streams administrator. See Oracle Database PL/SQL Packages and Types Reference for more information about the privileges granted by the GRANT_ADMIN_PRIVILEGE procedure.

• The DBA role

  The DBA role is required to create or alter capture processes, synchronous captures, and apply processes. When a user does not need to perform these tasks, DBA role can be revoked from the user.

• EXP_FULL_DATABASE role and IMP_FULL_DATABASE role

  These roles are required if the Oracle Streams administrator will use Data Pump to perform export and import operations on database objects in other schemas during an Oracle Streams instantiation. If the Oracle Streams administrator will not perform export and import operations on database objects in other schemas, then these roles can be revoked from the user.

• Database Control administrative privileges

  These privileges enable users to perform administrative tasks with Oracle Enterprise Manager, such as running Oracle Enterprise Manager jobs. If an Oracle Streams administrator exists in a database, and you want to grant Database Control administrative privileges to this user, then follow the instructions in Oracle Database 2 Day DBA.

To create an Oracle Streams administrator named strmadmin: 

1. Tutorial: Creating the Tablespace for the Oracle Streams Administrator

2. Tutorial: Creating an Oracle Streams Administrator

Creating an ANYDATA Queue

Queues store messages in an Oracle Streams environment. In an Oracle Streams replication environment, queues store messages that contain information about database changes. In an Oracle Streams messaging environment, queues store the messages produced and consumed by applications and users. Typically, each database in an Oracle Streams environment has one or more queues.

ANYDATA queues make it easy to store messages of almost any type. When you use an ANYDATA queue, you can, for example, store several different types of application messages in the same queue. Also, ANYDATA queues must be used to store information about database changes in an Oracle Streams replication environment.

To create an ANYDATA queue and its associated queue table: 

1. In Oracle Enterprise Manager, log in to the database as the Oracle Streams administrator.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage Advanced Queues in the Streams section.

5. On the Manage Advanced Queues page, click Create to open the Create Queue: Queue Type page.

   
   Description of the illustration tdpii_create_queue_type.gif
   
   

6. Select Normal Queue, SYS.ANYDATA Datatype.

   A queue of the ANYDATA data type enables you to store messages of almost any type in a single queue.

7. Click Continue to open the Create Queue: Normal Queue, SYS.ANYDATA Datatype page.

   
   Description of the illustration tdpii_create_queue.gif
   
   

8. Enter the name of the queue in the Name field. A typical queue name used in Oracle Streams environments is streams_queue, but you can enter a different name.

9. Enter the name of the queue table owner and the queue table name in the Queue Table field. Typically, the Oracle Streams administrator owns Oracle Streams queues, and a typical queue table name is streams_queue_table. Therefore, you can enter strmadmin.streams_queue_table, or you can enter a different owner and name.

10. Ensure that the name of the Oracle Streams administrator is entered in the Queue User field.

11. Optionally enter a description for the queue in the Description field.

12. Click Finish to create the queue table and the queue.

If you were directed to this topic from another topic, then go back to the topic now:

• "Tutorial: Configuring Two-Database Replication with Synchronous Captures"

• "Task 2: Configuring the Queues and Propagation Between Them"

• "Task 2: Configuring a Queue and a Messaging Client"

Tutorial: Creating a Database Link

To establish a communication path between two locations in a distributed database environment, you must create a database link. A database link is a pointer that defines a one-way communication path from one database to another database. An Oracle database uses database links to enable users on one database to access objects in a remote database. A local user can use a database link to a remote database even if the local user is not a user on the remote database.

Database links are required in most environments that store data in multiple databases or share information between databases. These environment include those that use distributed SQL, Oracle Streams replication, materialized view replication, and messaging.

Because the GLOBAL_NAMES initialization parameter is set to TRUE for each database in your distributed environment, you must use a global database name when you establish a link between two databases. Doing so ensures that each database link connects to the correct remote database.

Before you can create a database link between two databases, you must configure network connectivity so that the databases can communicate with each other. See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.

To create a database link from the ii1.example.com database to the ii2.example.com database: 

1. Log in to Enterprise Manager as an administrative user, such as the Oracle Streams administrator strmadmin or SYSTEM. The database link is created in the schema of this user.

2. Go to the Database Home page for the ii1.example.com database instance.

3. Click Schema to open the Schema subpage.

4. Click Database Links in the Database Objects section.

5. On the Database Links page, click Create to open the Create Database Link page.

   
   Description of the illustration tdpii_create_db_link.gif
   
   

6. Enter the name of the database link in the Name field. The name must be the global name of the database to which you are linking. In this example, the database link name is ii2.example.com.

7. In the Net Service Name field, enter the net service name of the database to which you are linking. In this example, the net service name is ii2.example.com.

8. Select Fixed User in the Connect As section.

9. In the Username field, enter the user name of the user who will own the database link. The database link connects to this user on the remote database. In this example, you can enter an administrative user, such as system SYSTEM, the Oracle Streams administrator strmadmin, or a regular database user, such as oe.

10. In the Password and Confirm Password fields, enter the password for the specified user on the remote database.

11. Click OK to create the database link.

If you were directed to this topic from another topic, then go back to the topic now:

• "Tutorial: Querying Multiple Oracle Databases"

• "Tutorial: Modifying Data in Multiple Oracle Databases"

• "Tutorial: Running a Stored Procedure in a Remote Oracle Database"

• "Configuring Oracle Databases to Work with Non-Oracle Databases"

• "Tutorial: Configuring Two-Database Replication with Synchronous Captures"

• "Task 2: Configuring the Queues and Propagation Between Them"

• "Tutorial: Preparing to Compare and Converge Data"