2 Common Data Replication and Integration Tasks

This chapter describes how to complete common tasks that are required in many data replication and integration environments.

This chapter contains the following sections:

• Setting the GLOBAL_NAMES Initialization Parameter to TRUE

• Tutorial: Creating an Oracle Streams Administrator

• Creating an ANYDATA Queue

• Tutorial: Creating a Database Link

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 Messaging"

• "Preparing for Materialized View Replication"

• "Preparing for Oracle Streams Replication"

See Also:

• "Tutorial: Creating a Database Link"

• Oracle Database 2 Day DBA

• Oracle Database Reference for more information about the GLOBAL_NAMES initialization parameter

Tutorial: Creating 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

• 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"

To configure an Oracle Streams administrator, either create a new user with the appropriate privileges or grant these privileges to an existing user. You should not use the SYS or SYSTEM user as an Oracle Streams administrator, and the Oracle Streams administrator should not use the SYSTEM tablespace as its default tablespace.

To create an Oracle Streams administrator named strmadmin:

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

2. Tutorial: Creating the Oracle Streams Administrator

Tutorial: Creating the Tablespace for the Oracle Streams Administrator

The Oracle Streams administrator should use a dedicated tablespace that is not used by any other user. Queue tables and other Oracle Streams components require disk space, and a dedicated tablespace can meet these space requirements efficiently.

To create a new tablespace for the Oracle Streams administrator:

1. Log in to Enterprise Manager as an administrative user.

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

3. Click Server to open the Server subpage.

4. Click Tablespaces in the Storage section.

5. On the Tablespaces page, click Create.

   The Create Tablespace page appears, showing the General subpage.

   
   Description of the illustration tdpii_create_tbs.gif
   
   

6. Enter streams_tbs in the Name field.

7. Click Add in the Datafiles section to open the Add Datafile page.

   
   Description of the illustration tdpii_add_datafile.gif
   
   

8. Enter streams_tbs.dbf in the File Name field.

9. Check the directory in the File Directory field and change it if necessary.

10. Change the size in the File Size field to 25 and ensure that the list is set to MB.

11. Select Automatically extend datafile when full (AUTOEXTEND) in the Storage section.

12. Enter 5 in the Increment field and set the list to MB.

13. Set the Maximum File Size. Typically, it is best to leave it set to Unlimited.

14. Click Continue.

15. On the Create Tablespace page, click OK.

16. Complete the steps in "Tutorial: Creating an Oracle Streams Administrator" to finish creating the Oracle Streams administrator.

Note:

You can also use the CREATE TABLESPACE SQL statement to create a tablespace.

Tutorial: Creating the Oracle Streams Administrator

This topic describes creating an Oracle Streams administrator that uses the tablespace configured in "Tutorial: Creating the Tablespace for the Oracle Streams Administrator".

To create a new Oracle Streams administrator named strmadmin:

1. Log in to Enterprise Manager as an administrative user.

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

3. Click Server to open the Server subpage.

4. Click Users in the Security section.

5. On the Users page, click Create.

   The General subpage of the Create User page appears.

   
   Description of the illustration tdpii_create_user.gif
   
   

6. Enter strmadmin in the Name field.

7. Enter a password for the new user in the Enter Password and Confirm Password fields.

   Enter an appropriate password for the administrative user. See Oracle Database 2 Day + Security Guide for information about choosing passwords.

8. Click the flashlight icon for the Default Tablespace field to select the streams_tbs tablespace created in "Tutorial: Creating the Tablespace for the Oracle Streams Administrator".

9. Click the flashlight icon for the Temporary Tablespace field to select a temporary tablespace for the new user.

10. Click Roles.

    The Roles subpage of the Create User page appears.

11. Click Edit List.

    The Modify Roles page appears.

12. Move DBA from the Available Roles list to the Selected Roles list.

13. Click OK.

14. On the Create User page, click OK to create the user.

    Note:

    You can also use the CREATE USER SQL statement to create a user and the GRANT SQL statement to grant privileges to a user.

15. Grant additional privileges to the user with the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure.

    1. On a command line, open SQL*Plus and connect to the database as an administrative user who can grant privileges.

       See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

    2. Run the following procedure:

       BEGIN
         DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
           grantee          => 'strmadmin',    
           grant_privileges => TRUE);
       END;
       /
       

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

• "Preparing for Messaging"

• "Tutorial: Configuring Two-Database Replication with Local Capture Processes"

• "Tutorial: Configuring Two-Database Replication with a Downstream Capture Process"

• "Tutorial: Configuring Hub-and-Spoke Replication with Local Capture Processes"

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

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 Setup in the Streams section.

5. On the Oracle Streams: Setup Options page, click Messaging.

   The Streams page appears, showing the Messaging subpage.

6. Click Create to open the Create Queue: Queue Type page.

   
   Description of the illustration tdpii_create_queue_type.gif
   
   

7. 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.

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

   
   Description of the illustration tdpii_create_queue.gif
   
   

9. 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.

10. 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.

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

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

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

Note:

You can also use the DBMS_STREAMS_ADM.SET_UP_QUEUE procedure to create an ANYDATA queue.

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

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

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

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

See Also:

• "Modifying Queues"

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.

Note:

You can also use the CREATE DATABASE LINK SQL statement to create a 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"

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

• "Tutorial: Configuring Two-Database Replication with Local Capture Processes"

• "Tutorial: Configuring Two-Database Replication with a Downstream Capture Process"

• "Tutorial: Configuring Hub-and-Spoke Replication with Local Capture Processes"

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

• "Tutorial: Adding Databases to a Replication Environment"

• "Tutorial: Preparing to Compare and Converge Data"

See Also:

• "Setting the GLOBAL_NAMES Initialization Parameter to TRUE"

• "About Data Replication and Integration"

• "About Data Replication and Integration Features"