Can we stop table to be globally accessed in Oracle RAC Environment

Leave a reply

Limit Global Access in Oracle RAC Efficiently

In Oracle RAC, tables are designed to be globally accessible by default, as RAC’s architecture is built on shared access to all database objects across multiple nodes. This is a key feature of RAC, enabling high availability and scalability. However, while you cannot completely stop a table from being globally accessible in the same way you might in a non-RAC system, you can control access and limit visibility or usage on a per-instance or per-service basis.

Here are some techniques you can use to limit the global access of tables in RAC:

1. Use Instance or Service-Level Affinity

As mentioned earlier, Oracle RAC allows you to configure services that are associated with specific instances. While this does not fully isolate the table itself, it ensures that specific workloads and connections tied to an instance or service only access certain tables.

Steps:

  • Define an Oracle service that directs connections to a specific instance:
srvctl add service -db <db_name> -service <service_name> -preferred <instance_name>
srvctl start service -db <db_name> -service <service_name>
  • Configure your application to use this service, so that users connected via this service will only access certain tables from the specified instance.

Note: The table is still globally accessible, but the traffic and access to it can be restricted to specific instances using services.

2. Restrict Access Using Database Roles or Schemas

Another way to limit access to tables is through user roles, privileges, or schemas. By assigning specific schemas or roles to certain instances or services, you can limit who can see or modify specific tables, effectively restricting access.

Example:

  • Create roles or users that have access to specific tables and assign them to connections based on services that are tied to instances:
CREATE ROLE role1;
GRANT SELECT, INSERT ON table1 TO role1;
CREATE USER user1 IDENTIFIED BY password1;
GRANT role1 TO user1;

3. Table Partitioning with Service-Based Access

While partitioning does not stop global access to the table, it allows you to control which instances access specific partitions. By partitioning a table and using Oracle services to direct specific instances to work with certain partitions, you can effectively limit the usage of certain parts of the table by instance.

Then, direct the NORTH region partition to be accessed by a specific service running on one instance, and the SOUTH region partition by another service running on a different instance:

CREATE TABLE sales_data (
  sale_id NUMBER,
  region VARCHAR2(30),
  sale_amount NUMBER
) PARTITION BY LIST (region)
(
  PARTITION p_north VALUES ('NORTH'),
  PARTITION p_south VALUES ('SOUTH')
);


srvctl add service -db <db_name> -service north_service -preferred <instance_name_north>
srvctl add service -db <db_name> -service south_service -preferred <instance_name_south>

4. Materialized Views or Table Replication for Local Copies

You can use Materialized Views (MVs) or table replication to create local copies of tables that are only accessible on specific instances, effectively isolating table access.

Example:

  • Create a materialized view that replicates the data from a master table:
CREATE MATERIALIZED VIEW mv_local_instance AS SELECT * FROM master_table;
  • Schedule the refresh of the materialized view on specific instances, so that only those instances have access to the local copy of the table.

This ensures that data access is limited to the instance hosting the materialized view, though the master table remains globally accessible.

Leave a Reply