Step by Step MMR Replication Setup

Oracle 8i  Replication Step By Step

1. PRE REPLICATION ENVOIRNMENT SETUP

BACKUP PRIMARY/SECONDARY SITE DB

1. Backup all the DB Links and synonyms for SECONDARY Environment.

2. Shutdown the Existing secondary Database Normal.

[ ] Shutdown

3. Take the cold backup from the existing SECONDARY Database Server (O/s Level).

4. Clean up SECONDARY Data base Environment.

(Remove all the SECONDARY DB from different mount point for refresh new DB from PRIMARY)

5. Cross Check the Mount points on the SECONDARY database server have sufficient space

6. Take the control file Backup on existing PRIMARY database As :

Alter database backup controlfile to trace;

(This will create a back script of the controlfile at the destination: user_dump_dest with some name with .trc extension, kindly find it with the latest timestamp and rename it as ‘create_control_file.sql’)

Note: This file has information about PRIMARY Environment.

7. Take all the DB Links and synonyms Backup.

8. List All the Data files.

Select name from dba_data_files OR select * from v$datafile

9. Shutdown the Existing PRIMARY Database Normal.

  

            [ ] Shutdown

10. Take a Cold Backup of PRIMARY Database.

2. REPLICATION ENVOIRNMENT SETUP

DB Refresh & Replication Setup Step By Step with:

1. Restore the backup to the new database server to the relevant mount points.

Copy the backup controlfile to the new server $ORACLE_HOME/dbs directory.

     Note: copy the redo.log files and multiplex the files in different mount points.

2.  Copy the pfile from the source Primary database server to  the location

Change the modification of path of:

- Controlfile location. (The control files must be multiplex)

- user_dump_dest.

- core_dump_dest.

- background_dump_dest.

3.   Login to new Primary database server as sys user :

$ sqlplus ‘ / as sysdba’

      Note : it will connect  to the oracle ideal instance.

4. Startup the database in nomount mode .

[ ] startup nomount  pfile=’pfile_path/pfilename’. ( it set the parameter that are set in the                 parameterfile)

I.e # cd $ORACLE_HOME/dbs/

5.  Create new Control file .

             Run the script ‘create_control_file.sql’ that is created in the step-8

[ ] @create_control_file.sql;

Note: Make changes according to SECONDARY Environment

I.e. change in path of Datafiles as below

6. Take on the DATABASE into mount state

Alter database mount;

7.  Take on the DATABASE into open state

Alter database open resetlogs;

8.  To check the database is in open state

[ ] select name,open_mode From v$database;

9. To check the database is refreshed properly

[ ] select *  from V$RECOVER_FILE;

10. Configure following Step in each Environment ( Primary & Secondary)

1.      Connect SECONDARY QA DB as sysdba as follows

$ ORACLE_SID=SECONDARY

$ export ORACLE_SID

$ sqlplus '/ as sysdba'

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Aug 10 07:16:17 2010

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

With the Partitioning option

Jserver Release 8.1.7.4.0 - Production

SQL>

2.      Create replication administrator / propagator / receiver

CREATE USER REPADMIN

             IDENTIFIED BY REPADMIN

             DEFAULT TABLESPACE USERS

             TEMPORARY TABLESPACE TEMP

             PROFILE DEFAULT

             ACCOUNT UNLOCK;

GRANT CONNECT TO REPADMIN;

  GRANT DBA TO REPADMIN;

  GRANT RESOURCE TO REPADMIN;

  GRANT SELECT_CATALOG_ROLE TO REPADMIN;

  ALTER USER REPADMIN DEFAULT ROLE ALL;

  -- 45 System Privileges for REPADMIN

  GRANT ALTER ANY CLUSTER TO REPADMIN;

  GRANT ALTER ANY INDEX TO REPADMIN;

  GRANT ALTER ANY PROCEDURE TO REPADMIN;

  GRANT ALTER ANY SEQUENCE TO REPADMIN;

  GRANT ALTER ANY SNAPSHOT TO REPADMIN;

  GRANT ALTER ANY TABLE TO REPADMIN;

  GRANT ALTER ANY TRIGGER TO REPADMIN;

  GRANT ALTER SESSION TO REPADMIN;

  GRANT COMMENT ANY TABLE TO REPADMIN;

3.      Grant privs to the propagator, to propagate changes to remote sites

               EXECUTE Dbms_Defer_Sys.Register_Propagator(username=>'REPADMIN');

4.      Grant privs to the receiver to apply deferred transactions

               GRANT EXECUTE ANY PROCEDURE TO repadmin;

5.      Authorize the administrator to administer replication groups and schemas

               EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Repgroup('REPADMIN');

               EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Schema (username => 'REPADMIN');

6.      Authorize the administrator to lock and comment tables

               GRANT LOCK ANY TABLE TO repadmin;

               GRANT COMMENT ANY TABLE TO repadmin;

7.       Connect to the replication administrator

               CONNECT repadmin/repadmin

8.      Create private db links for all repadmin users

Create database link "IAS.QA.SECONDARY.COM"

Using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=10.277.93.169)(Port=1521)))(CONNECT_DATA=(SID=SECONDARY)))';

Create database link " IAS.QA.PRIMARY.COM"

Using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=10.237.93.141)(Port=1521)))(CONNECT_DATA=(SID=SECONDARY)))';

9.      Schedule job to push transactions to QA master sites with appropriate intervals

EXECUTE Dbms_Defer_Sys.Schedule_Push(        -

        Destination   => ' IAS.QA.PRIMARY.COM ',       -

        Interval      => 'sysdate+1/24/60',  -

        Next_date     => sysdate+1/24/60,    -

        Stop_on_error => FALSE,              -

        Delay_seconds => 0,                  -

        Parallelism   => 1);

10.  Schedule job to push transactions to INT master sites with appropriate intervals

EXECUTE Dbms_Defer_Sys.Schedule_Push(        -

        Destination   => ' IAS.QA.SECONDARY.COM ',       -

        Interval      => 'sysdate+1/24/60',  -

        Next_date     => sysdate+1/24/60,    -

        Stop_on_error => FALSE,              -

        Delay_seconds => 0,                  -

        Parallelism   => 1);

11.  Schedule job to delete successfully replicated transactions

EXECUTE Dbms_Defer_Sys.Schedule_Purge(       -

        Next_date     => sysdate+1/24,       -

        Interval      => 'sysdate+1/24');

CONNECT repadmin/repadmin

12.  Create replication group for QA site

EXECUTE Dbms_Repcat.Create_Master_Repgroup('IREPLICATION');

13.  Add master destination sites

EXECUTE Dbms_Repcat.Add_Master_Database('REPLICATION', 'IAS.QA.RENNES.EQUANT.COM');

14.   Wait until IAS_REPLICATION appears in the DBA_REPSITES view\

SELECT * FROM dba_repsites WHERE gname = ‘REPLICATION';

15.  Register objects within the group

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'TEST'
oname => 'NEW_CHECKPOINT'
type => 'TABLE',
min_communication => TRUE);
END;

/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'TEST'
oname => 'NEW_CORRELATION'
type => 'TABLE',
min_communication => TRUE);
END;

1.2.2 Start/Stop Replication  (PRIMARY & SECONDARY)

Once replication support has been generated for all objects relevant objects replication can be started or stopped as follows:

-- Start Replication

EXECUTE Dbms_Repcat.Resume_Master_Activity(gname => 'REPLICATION');

-- Stop Replication

EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname => 'REPLICATION');

1.2.3 Check  Replication Status  (PRIMARY & SECONDARY)

Select gname, status from dba_repgroup where gname ='REPLICATION' ;











Thanks :)







Please share your really important  comments and views  for improvement......