Oracle materialized View Configuration experiment

1 Preparation work

1.1 Check Initialization parameters

Confirm that the gloal_names is true,job_queue_process greater than 0. Perform the following two Sqlplus commands at both the primary and materialized view sites to check that database initialization parameters meet the requirements.

Show Parameter Global_names

Show parameter Job

If the initialization parameter setting does not meet the requirements, it can be dynamically modified with the following statement.

alter system set Global_names = true;

alter system set job_queue_processes = 20;

1.2 Check Global database name

The Db_domain name of two databases should be the same, only db_name different.

Check the global database name of the primary and materialized view sites with the following statement

SELECT * from Global_name;

If the global database name setting does not conform to the specification, it can be modified dynamically with the following statement.

ALTER DATABASE rename Global_name to MAIN.HWTT.NET.COM; (primary site)

ALTER DATABASE rename Global_name to MATEVIEW.HWTT.NET.COM; (Materialized view site)

1.3 Modifying Tnsnames.ora files

The main site and materialized view site in the Tnsnames.ora parameter files are added below, note that you need to modify the host's IP address, as well as the service_name value, port generally defaults to 1521.

MAIN =

(DESCRIPTION =

(Address_list =

(address = (PROTOCOL = TCP) (HOST = 19.168.2.105) (PORT = 1521))

)

(Connect_data =

(service_name = portal)

)

)

Mateview =

(DESCRIPTION =

(Address_list =

(address = (PROTOCOL = TCP) (HOST = 19.168.2.101) (PORT = 1521))

)

(Connect_data =

(service_name = portal)

)

)

2 Principal Site Configuration

2.1 Establish replication management user and authorize

Log in to the principal site with the SYS or system user

Conn sys/mcp@ MAIN as SYSDBA;

--Establish a replication administrator, in the actual application, need to modify the password of the Repadmin account to ensure security.

CREATE USER repadmin identified by repadmin;

--Authorize

BEGIN

Dbms_repcat_admin. Grant_admin_any_schema (username => ' repadmin ');

End;

/

GRANT COMMENT any TABLE to repadmin;

GRANT LOCK any TABLE to repadmin;

GRANT SELECT any DICTIONARY to repadmin;