Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: replication question

Re: replication question

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Tue, 16 Nov 1999 21:03:53 -0800
Message-ID: <80t9bn$l7t$1@inet16.us.oracle.com> 





CREATING A REPLICATION ENVIRONMENT: 7.3.X




  REM **************************************************************
  REM
  REM     CREATING A REPLICATION ENVIRONMENT:  7.3.X
  REM
  REM     The following is an example of creating a ADVANCED
  REM     REPLICATION ENVIRONMENT. These scripts are based on the
  REM     SCOTT/TIGER scheme. The following scripts are provided
  REM     to assist in setting up an example replication environment.


  REM


  REM **************************************************************
  REM
  REM     Oracle Disclaimer:
  REM
  REM     These script is provided for educational purposes only.
  REM     It is NOT supported by Oracle Wide World Technical
  REM     Support.  The script has been tested and appears to
  REM     work as intended.  However, you should always test any
  REM     script before relying on it.
  REM
  REM     Please proofread this script before using it.  Due
  REM     to differences in the way text editors, email packages
  REM     and operating systems handle text formation (spaces,
  REM     tabs and carriage returns), this script may not be in
  REM     an executable state when you first receive it.  Check
  REM     over the script to ensure that errors of this type
  REM     are corrected.
  REM
  REM     These scripts are based on the MARS/VENUS (lab) setup.
  REM     from Oracle Education Symmetric Replication class
  REM     Modified by: Dennis Horton, Technical Analyst
  REM     Oracle Worldwide Support




  REM



  ------------------------------cut
line--------------------------------------

  REM *********************************************************************


  REM script1.sql MASTER DEFINITION SITE SETUP: (Run @ Master Definition
Site)

  REM *********************************************************************





  REM create log file for script1.sql



        spool Script1.log;



  REM Assign the global name for the database
  REM (Master Definition Site.World -- Master Definition Site)



        connect sys/change_on_install



        alter database rename GLOBAL_NAME TO Master Definition Site.World;



  REM Create Surrogate Replication user & Database links



        drop user REPSYS cascade;


        create user REPSYS identified by REPSYS
        default tablespace USERS
        temporary tablespace TEMP;





  REM Grant surrogate replication administrator privileges to
  REM local REPSYS user.



        execute DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT('REPSYS');



        drop public database link Remote Master.World



        drop database link Remote Master.World



  REM All private database links will reference the public database
  REM link for their destinations. Use the TNS ALIAS assigned in the
  REM tnsnames.ora' configuration file for the correct destination
  REM of the 'Remote Master' node in the database link creation.


        create public database link Remote Master.world
        using 'Remote Master TNS_ALIAS'(tnsnames.ora);





  REM Test database link to confirm created correctly



        select * from global_name_at_Remote Master.World



  REM Create a private database link from SYS to
  REM surrogate SYS (REPSYS).


        create database link Remote Master.World
        connect to REPSYS identified by REPSYS;





  REM Test database link to confirm created correctly.



        select * from global_name_at_Remote Master.World



  REM Create replication Administrator user & Database links.



        drop user REPADMIN cascade;


        create user REPADMIN identified by REPADMIN
        default tablespace USERS
        temporary tablespace TEMP;





  REM  Grant replication catalog and replication procedures to
  REM  replication administrator.



        execute DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP('REPADMIN');



        grant execute on  sys.DBMS_DEFER to REPADMIN with GRANT option;



        connect REPADMIN/REPADMIN



  REM Create private database link from REPADMIN to REPADMIN at the
  REM Remote Master Node.


        create database link Remote Master.World
        connect to REPADMIN identified by REPADMIN;





  REM Test database link to confirm created correctly.



       select * from global_name_at_Remote Master.World



  REM Execute the following script to create private
  REM synonyms for DBA_ views.



        @ORACLE_HOME/rdbms/admin/catdbsyn.sql



        spool off;


  ------------------------------cut
line--------------------------------------

  REM ***************************************************************


  REM Script2.sql -- REMOTE MASTER SITE SETUP: (Run @ Remote Master Site)

  REM ***************************************************************





  REM create log file for script2.sql



        spool script2.log;



  REM Assign the global name for the database



        connect sys/change_on_install



        alter database rename GLOBAL_NAME TO Remote Master.World;



  REM Create Surrogate Replication user & Database links



        drop user REPSYS cascade;


        create user REPSYS identified by REPSYS
        default tablespace USERS
        temporary tablespace TEMP;





  REM Grant surrogate replication administrator privileges to
  REM local REPSYS user.




        execute DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT('REPSYS');



        drop public database link Master Definition Site.World



        drop database link Master Definition Site.World



  REM All private database links will reference the public database
  REM link for there destinations. Use the TNS ALIAS assigned in the
  REM 'tnsnames.ora' configuration file for the correct destination
  REM of the 'Master Definition Site' node in the database link creation.



        Create public database link Master Definition Site.  World
        using 'Master Definition Site TNS_ALIAS' (tnsnames.ora);





  REM Test database link to confirm created correctly.



        select * from global_name_at_Master Definition Site.World



  REM Create a private database link from SYS to surrogate
  REM SYS (REPSYS).



        create database link Master Definition Site.World
        connect to REPSYS identified by REPSYS;





  REM Test database link to confirm created correctly.



        select * from global_name_at_Master Definition Site.World



  REM Create replication Administrator user and Database links.



        drop user REPADMIN cascade;


        create user REPADMIN identified by REPADMIN
        default tablespace USERS
        temporary tablespace TEMP;





  REM Grant replication catalog and replication procedures to
  REM replication administrator.



        execute DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP('REPADMIN');



        grant execute on  sys.DBMS_DEFER to REPADMIN with GRANT option;



        connect REPADMIN/REPADMIN



  REM Create private database link from REPADMIN to REPADMIN at the
  REM Remote Master Node.


        create database link Master Definition Site.World
        connect to REPADMIN identified by REPADMIN;





  REM Test database link to confirm created correctly.



        select * from global_name_at_Master Definition Site.World



  REM Execute the following script to create private synonyms
  REM for DBA_ views;



        @ORACLE_HOME/rdbms/admin/catdbsyn.sql



        spool off;


  ------------------------------cut
line--------------------------------------

  REM *********************************************************


  REM Script3.sql CREATE SCHEMA USER GRANT PRIVILEGES
  REM (Run at Master Definition Site)

  REM *********************************************************





  REM create log file for script3.sql



        spool script3.log;



  REM  Create schema user and grant privileges to user.



        connect REPADMIN/REPADMIN



  REM If you have existing schema objects REM the following command.



        drop user SCOTT cascade


        create user SCOTT identified by TIGER
        default tablespace USERS
        temporary tablespace TEMP

        grant CONNECT, RESOURCE to SCOTT;

        grant EXECUTE on sys.DBMS_DEFER to SCOTT;





  REM  Connect as schema owner and create schema objects



        connect SCOTT/TIGER


        create database link Remote Master.World
        connect to SCOTT identified by TIGER;





  REM Test database link to confirm created correctly.



        select * from global_name_at_Remote Master.World



  REM Create tables DEPTTEST and EMPTEST


      create table DEPTTEST
      as select * from SCOTT.DEPT;

      create table EMPTEST
      as select * from SCOTT.EMP;





  REM Create primary key and Foreign Key constraints.  All replicated tables
  REM need Primary Keys defined!


      alter table SCOTT.DEPTTEST
      add constraint DEPTTEST_DEPTNO_PK PRIMARY KEY (DEPTNO);

      alter table SCOTT.EMPTEST
      add constraint EMPTEST_EMPNO_PK PRIMARY KEY (EMPNO);

      alter table SCOTT.EMPTEST
      add constraint EMPTEST_DEPTNO_FK FOREIGN KEY (DEPTNO)
      references SCOTT.DEPTTEST(DEPTNO);

      spool off;

  ------------------------------cut
line--------------------------------------


  REM ***********************************************************


  REM Script4.sql CREATE SCHEMA USERS (Run @ Remote Master Site)

  REM ***********************************************************


  REM create log file for script4.sql



        spool script4.log;



  REM Create user and grant privileges.



        connect REPADMIN/REPADMIN



        drop user SCOTT cascade;


        create user SCOTT identified by TIGER
        default tablespace USERS
        temporary tablespace TEMP

        grant CONNECT, RESOURCE to SCOTT;

        grant EXECUTE on sys.DBMS_DEFER to SCOTT;





  REM  Connect as schema owner and create private database link



        connect SCOTT/TIGER


        create database link Master Definition Site.World
        connect to SCOTT identified by TIGER;





  REM Test database link to confirm created correctly.



        select * from global_name_at_Master Definition Site.World



        spool off;



  ------------------------------cut
line--------------------------------------


  REM **********************************************************************


  REM Script5.sql REGISTER REPLICATION OBJECTS (Run @ Master Definition
site)

  REM **********************************************************************





  REM create log file for script5.sql



        spool script5.log;



  REM As user REPADMIN create replication group SCOTT.



        connect REPADMIN/REPADMIN



        execute DBMS_REPCAT.CREATE_MASTER_REPGROUP('SCOTT');



  REM By executing the CREATE_MASTER_REPGROUP, you are implying that
  REM the "MASTER DEFINITION SITE" for replication group SCOTT
  REM is initially located within the "Master Definition Site" replication
  catalog.




  REM Register objects for replication from Scott's schema within
  REM the replication group SCOTT.



        execute


DBMS_REPCAT.CREATE_MASTER_REPOBJECT('SCOTT','DEPTTEST','TABLE');


        execute


DBMS_REPCAT.CREATE_MASTER_REPOBJECT('SCOTT','EMPTEST','TABLE');



  REM Generate replication support for objects in the SCOTT replication
  REM group.


        execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('SCOTT','DEPTTEST'
        ,'TABLE');

        execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('SCOTT','EMPTEST'
        ,'TABLE');





  REM Query the replication catalog views to see the objects that will be
  REM replicated. Confirm the new objects.



        select * from DBA_REPCAT;



        select * from DBA_REPOBJECT;



  REM Select trigger body from DBA_TRIGGERS to look at what constituents
  REM the PL/SQL code generated for replication support.


        select TRIGGER_BODY from DBA_TRIGGERS
        where TRIGGER_NAME = 'EMPTEST$RT';





  REM Add Master replication database (Remote Master site).



        execute DBMS_REPCAT.ADD_MASTER_DATABASE('SCOTT','Remote
Master.World');



  REM Check for errors in DBA_REPCATLOG.



        select ERRNUM, MESSAGE from DBA_REPCATLOG;



  REM Change the status of the schema from QUIESCED mode to NORMAL mode



        execute DBMS_REPCAT.RESUME_MASTER_ACTIVITY('SCOTT',FALSE);



  REM Check the status of the replication group



        select * from DBA_REPCAT;



        select * from DBA_REPCAT_at_Remote Master.World;



  REM Once the status is NORMAL, the replication environment is now set.



        spool off;





"The Views expressed here are my own and not necessarily those of Oracle
Corporation"



<bmolish_at_my-deja.com> wrote in message news:80rn3e$u2v$1_at_nnrp1.deja.com...


> I am new to the 'replication' feature of Oracle.


> I am using Oralce 7.34, Enterprise manager 1.60 on an NT 4.0 server


>


> I am using the Enterprise manager replication to set it up and I keep


> getting an error that 'ORCL2 appears to be a pre-7.3 version of the


> server or replication support is not installed.


> Replication manager is designed to work only with


> server version 7.3 or above with replication


> installed.'


>


> Is there a script I need to run to setup the replication?


> Is there suppose to be a service to start replication?


> How can I determine that replication is setup correctly?


>


>


> Sent via Deja.com http://www.deja.com/


> Before you buy.




Received on Tue Nov 16 1999 - 23:03:53 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US