Profile configuration steps
Main site: Oratom
Substation site: chenlp
Host name: guoxh, chenlp
Copy user: TAX
1. Check initialization parameters
The replication is not much limited to the initialization parameter of the database, and the main payment is two points.
Global_names is True and Job_Queue_Process greater than 0.
Perform the following two SQLPLUS commands at the primary site and materialized view site, check if the database initialization parameter is compliant.
Claim.
SHOW Parameter Global_Names
SHOW parameter job
If the initialization parameter setting is not satisfied, you can dynamically modify the following statement.
Alter system set global_names = true;
Alter system set job_queue_processes = 20;
2. Check the global database name
The DB_DOMAIN name of the two databases should be the same, only DB_NAME is different.
Check the global database name of the primary site and materialized view site by following statements
Select * from global_name;
If the global database name setting does not meet the specification, you can dynamically modify the following statement.
Alter Database Rename Global_name to Oratom;
Alter Database Rename Global_name to chenlp;
3. Modify the following contents in the parameter file of the TNSNames.ora file, the primary site, and the primary view site.
4. Establish a main site
- Connect to the SYSTEM user to the primary site
CONN System / ORATIATOM @ oratom
- Establish a replication management user REPADMIN and authorize
Create user repadmin Identified by repadmin
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;
- Registered to disseminate users and authorize, here the management user repadmin can also be established separately
Begin
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'repadmin');
END;
/
- Establish a materialized view site copy administrator's agent user, for simple consideration, here also use repadmin users
Begin
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
Username => 'repadmin',
Privilege_type => 'proxy_snapadmin',
List_of_gnames => null);
END;
/
- Set the agent to refresh the user, and authorize, here still use the repadmin user
- For repadmin, don't need Create session permissions
- But here if you create a new user, Create session permissions are required
Grant Create session to repadmin;
Grant Select Any Table To Repadmin;
- Set Job for the clearance delay sequence
- Log in to the primary site as a replication administrator
Connect repadmin / repadmin @ oratombegin
DBMS_DEFER_SYS.SCHEDULE_PURGE
Next_date => SYSDATE,
Interval => 'sysdate 1/24',
DELAY_SECONDS => 0);
END;
/
COMMIT;
- Multi-master settings require multiple sites to establish database chains and establish scheduling mechanisms
- But these settings are not required for the body site replicated to the materialized view.
5. Set the materialized view site
- Connect to the SYSTEM user to the materialized view site
Conn
chenlp / c @ chenlp
- Establish a materialized view administrator and authorize
Create User Mvadmin Identified by mvadmin;
Begin
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
Username => 'mvadmin');
END;
/
Grant Comment Any Table To Mvadmin;
Grant Lock Any Table To MvAdmin;
Grant Select Any Dictionary to mvadmin;
- Establish a communicator and authorize, use the mvadmin user here, or you can establish a separate user
Begin
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'mvadmin');
END;
/
- Establish a refresh person, and authorize, use the mvadmin user to refresh the materialized view
- For mvadmin, do not need Create session permissions
- But here if you create a new user, Create session permissions are required
Grant Create session to mvadmin;
Grant alter any cared view to mvadmin
- Registered recipient
Begin
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
Username => 'mvadmin',
Privilege_type => 'Receiver',
List_of_gnames => null);
END;
/
- Create a public database chain
Create public database link oratom using 'oratom_syax-guoxh';
- Create database chain to the agent materialization view administrator at the primary site
- Connect to the physical chemical view site as a materialized view administrator
Connect mvadmin / mvadmin @ chenlp
Create Database Link Oracom Connect To Repadmin Identified by
Repadmin;
- Create a database chain to the copy administrator on the primary site
- Log in to the physical chemical view site as a communication
- In this example, this database chain is the same as the above database chain, so it is omitted.
- Set Job for the clearance delay sequence
- If the materialized view site only includes read-only view, this step can be omitted
Begin
DBMS_DEFER_SYS.SCHEDULE_PURGE
Next_date => SYSDATE,
Interval => 'sysdate 1/24',
DELAY_SECONDS => 0,
Rollback_segment => ');
END;
/
- Settings to modify the job to the primary site
- If the materialized view site only includes read-only view, this step can be omitted
Begin
DBMS_DEFER_SYS.SCHEDULE_PUSH (
Destination => 'rep.yangtingkun',
Interval => 'sysdate 1/24',
Next_date => SYSDATE,
STOP_ON_ERROR => False,
DELAY_SECONDS => 0,
Parallelism => 0);
END;
/
- If this material view site is required, this material is needed as the primaryized view site
- The agent materialized view management user and agent refresh user needs to establish a physical chemical view management user and agent refresh user.
- This example is omitted
COMMIT;
6. Establish a main group
- Log in to copy the site as a replication administrator
Connect repadmin / repadmin @ oratom
- Create a replication group named rep_test
Begin
DBMS_REPCAT.CREATE_MASTER_REPGROUP
GNAME => 'rep_test');
END;
/
- Add a copy object to a replication group
- Automatic replication used by the primary key, other indexes need to be explicitly added to the replication group
Begin
DBMS_REPCAT.CREATE_MASTER_REPOBJECT
gname => 'rep_test',
TYPE => 'Table',
ONAME => 'Test_rep',
Sname => 'Yangtk',
Use_existing_object => true,
Copy_Rows => false);
END;
/
Begin
DBMS_REPCAT.CREATE_MASTER_REPOBJECT
gname => 'rep_test',
TYPE => 'INDEX',
ONAME => 'Ind_test_rep_name',
Sname => 'Yangtk',
Use_existing_object => true,
Copy_Rows => false);
END;
/
- Generate replication support
Begin
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT
Sname => 'Yangtk',
ONAME => 'Test_rep',
TYPE => 'Table',
min_communication => true);
END;
/
- Start copying
Begin
DBMS_REPCAT.RESUME_MASTER_ACTIVITY
GNAME => 'rep_test');
END;
/
COMMIT;
7. Establish a materialized view
- Connect to copy users to the primary site
Connect Yangtk @ Rep
- Create a materialized view log list, FAST refresh mode must be required to establish a materialized view log, Complete does not need
Create Materialized View Log On Yangtk.test_rep;
- Establish and grant appropriate permissions if it is copied by the copy user
- In this example, the user already exists, this step is omitted
/ *
Connect System @ yangtkcreate user yangtk ideified by yangtk;
Alter User Yangtk Default TableSpace Users Quota Unlimited on Use;
Alter User Yangtk Temporary TableSpace Temp;
Grant
Create session,
Create Table,
CREATE Procedure,
Create sequence,
Create Trigger,
Create View,
Create Synonym,
Alter session,
Create Materialized View,
Alter Any Materialized View,
Create Database Link
To yangtk;
* /
- Establish a replication user to the primary site agent to refresh the database chain
Connect Yangtk @ yangtk
Create Database Link Rep.yangtingkun Connect To Repadmin Identified by
Repadmin;
- Creating a materialized view group
- Login materialized view site as a materialized view administrator
Connect mvadmin / mvadmin @ yangtk
- The materialized view group must be the same as the replication group name on the replication site.
Begin
DBMS_REPCAT.CREATE_MVIEW_REPGROUP
gname => 'rep_test',
Master => 'rep.yangtingkun',
Propagation_mode => 'askNCHRONOUS');
END;
/
- Create a refresh group
- For sites containing only read-only view, don't need this step
Begin
DBMS_REFRESH.MAKE
Name => 'mvadmin.rep_refresh',
List => '',
Next_date => SYSDATE,
Interval => 'sysdate 1/24',
Implicit_destroy => false,
Rollback_seg => ',
Push_deferred_rpc => True,
Refresh_after_errors => false);
END;
/
- Create a materialization view
- For read-only-based views, omitted for Update statements
Create Materialized View Yangtk.test_rep
Refresh Fast With Primary Key for Update
As select * from yangtk.test_rep@rep.yangtingkun;
- Add a materialized view to a materialized view group
- For read-only view, this step can be omitted
Begin
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT
gname => 'rep_test',
Sname => 'Yangtk',
ONAME => 'Test_rep',
TYPE => 'Snapshot',
min_communication => true);
END;
/
Begin
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT
gname => 'rep_test', sname => 'yangtk',
ONAME => 'Ind_test_rep_name',
TYPE => 'INDEX',
min_communication => true);
END;
/
- Add a materialized view to the refresh group
Begin
DBMS_REFRESH.ADD (
Name => 'mvadmin.rep_refresh',
List => 'yangtk.test_rep',
LAX => true);
END;
/
COMMIT;
8. Test object script on the primary object
Create Table Test_rep (ID Number Not Null, Name Varchar2 (100));
ALTER TABLE TEST_REP ADD CONSTRAINT PK_TEST_REP Primary Key (ID);
CREATE INDEX IND_TEST_REP_NAME ON TEST_REP (NAME);
INSERT INTO TEST_REP VALUES (1, 'Ytk');
INSERT INTO TEST_REP VALUES (2, 'ENLY');
COMMIT;
__
