oracle8 advanced replication n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle8 Advanced Replication PowerPoint Presentation
Download Presentation
Oracle8 Advanced Replication

Loading in 2 Seconds...

play fullscreen
1 / 55

Oracle8 Advanced Replication - PowerPoint PPT Presentation


  • 76 Views
  • Uploaded on

Oracle8 Advanced Replication. A Beginner’s Perspective Brian Hitchcock, DBA Sun Microsystems brian.hitchcock@sun.com. Historical Background. Our group Track all Real Estate holdings for Sun who is in which building, office, cubicle etc. Our application

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oracle8 Advanced Replication' - mia-saunders


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
oracle8 advanced replication

Oracle8 Advanced Replication

A Beginner’s Perspective

Brian Hitchcock, DBA

Sun Microsystems

brian.hitchcock@sun.com

Oracle8 Advanced Replication

A Beginner’s Perspective

historical background
Historical Background
  • Our group
    • Track all Real Estate holdings for Sun
    • who is in which building, office, cubicle etc.
  • Our application
    • tracks occupancy of Sun facilities world-wide
    • forecasting of short and long-term needs
  • Replicate application tables only
    • keep it simple
    • start with basic table replication

Oracle8 Advanced Replication

A Beginner’s Perspective

my background
My Background
  • DBA for 6 years
  • Asymmetric replication
    • non-Oracle, single master, multiple replicate sites
  • LAN
    • replication from production system to reporting system

Oracle8 Advanced Replication

A Beginner’s Perspective

why use replication
Why Use Replication?
  • Application deployed globally
  • Network performance
  • Disaster Recovery
  • Distributed data

Oracle8 Advanced Replication

A Beginner’s Perspective

why use replication1
Why Use Replication?
  • Application deployed globally
    • US, Holland, Singapore
    • users want consistent application response time(s)
  • Network performance
    • gets worse for sites far from US
    • total network delay can become unacceptable for Europe/Asia

Oracle8 Advanced Replication

A Beginner’s Perspective

why use replication2
Why Use Replication?
  • Disaster Recovery
    • backup copy of critical data up-to-date within seconds
    • each site independent during network downtime
  • Distributed data
    • each site can run reports locally
    • all data available at all sites

Oracle8 Advanced Replication

A Beginner’s Perspective

additional replication benefits
Additional Replication Benefits
  • Provides automatic archiving versus full db dumps
    • only changes are moved to other sites
    • don’t have to store multiple copies of all existing data
  • Improved Disaster Recovery
    • can reduce data loss window with less impact to network and disk space
    • don’t have to make complete backups of all data, only the updates

Oracle8 Advanced Replication

A Beginner’s Perspective

issues
Issues
  • Conflicts
  • Support/Training
  • Existing application design
  • Resynchronization
    • is more than just rows of data
    • must synch all portions of replicated table (constraints, indexes etc.)
    • replication won’t do this automatically

Oracle8 Advanced Replication

A Beginner’s Perspective

asynchronous
Asynchronous
  • Local commands
  • Remote execution
  • Latency
  • What’s happening?

Oracle8 Advanced Replication

A Beginner’s Perspective

system diagram
System Diagram

Holland

US

Ultra2, 512 Mb

75 Gb (mirrored)

Solaris 2.5.1

E4000

512 Mb

75 Gb

(mirrored)

Solaris 2.5.1

Ultra2, 512 Mb

75 Gb (mirrored)

Solaris 2.5.1

Singapore

Oracle8 Advanced Replication

A Beginner’s Perspective

schema diagram
Schema Diagram

Holland

US

Singapore

4 schemas

-- plan

-- planning

-- allocation

-- supplyplan

Oracle8 Advanced Replication

A Beginner’s Perspective

my experience
My Experience
  • Consultant
  • Snapshots
    • Updateable bring data back from remote sites
    • Simple move lookup data to remote sites
  • Replaced cron jobs
  • Didn’t work…

Oracle8 Advanced Replication

A Beginner’s Perspective

what went wrong
What Went Wrong?
  • Consultant
    • No replication experience, no training
    • Couldn’t use OEM, had to use API
    • Oracle8 Adv Rep manual assumes OEM
    • Used 7.3 manuals for API
  • Specific problems
    • was setup as ‘repadmin’, granted DBA role
    • scheduling ‘push’ job, db links were ‘creative’
    • global_name was not understood

Oracle8 Advanced Replication

A Beginner’s Perspective

why we went to advanced replication
Why We Went to Advanced Replication
  • Conflicts avoided by application design
    • only one user can update data
    • don’t require snapshots to prevent conflicts
  • Data owners travel worldwide
    • want to be able to update their data anywhere
    • and have best possible response time
  • Disaster Recovery
    • all data can be updated at any site

Oracle8 Advanced Replication

A Beginner’s Perspective

oracle training
Oracle Training
  • 90% API
  • Covers what manuals should cover
  • Jenny Tsai -- a ‘goddess’
    • ‘dog years’ joke is too old...
  • Labs
    • provides template for replication setup
    • excellent practice starting replication

Oracle8 Advanced Replication

A Beginner’s Perspective

oracle training message
Oracle Training Message
  • You must understand API, OEM good for monitoring
  • Retrofit of replication to existing application is ‘virtually impossible’
  • 3 sites may never converge
  • Conflict issues drive implementation
  • Oracle replication uses after row triggers

Oracle8 Advanced Replication

A Beginner’s Perspective

my message
My Message
  • Don’t rely on the manuals
  • Do take the Oracle training
  • Get copies of Oracle Tech Support Tech Notes, scripts, FAQs
  • Setup rep config on development servers
  • API -- get the book...
    • Oracle Built-in Packages, O’Reilly
    • Steven Feuerstein, et al, ISBN 1-56592-375-8
    • chapters 14 thru 17

Oracle8 Advanced Replication

A Beginner’s Perspective

replicated tables
Replicated Tables
  • Must have primary key
  • Can’t replicate without
  • Don’t assume they exist
  • Check each table for PK

Oracle8 Advanced Replication

A Beginner’s Perspective

referential integrity
Referential Integrity
  • Rep doesn’t recreate at master sites
  • You must ensure that all parts of each replicated table are re-created at each site
    • use export/import to create replicate objects

Oracle8 Advanced Replication

A Beginner’s Perspective

configuration recommendations
Configuration Recommendations
  • Implementation
  • Link naming
  • Link configuration
  • Server configuration
  • Practice complete rep installation

Oracle8 Advanced Replication

A Beginner’s Perspective

recommendation implementation
Recommendation -- Implementation
  • export tables from master definition site
  • import to all master sites
  • create repgroup as master definition site
    • reuse=>TRUE, copy_rows=>FALSE
  • add master site(s)
  • resume master activity

Oracle8 Advanced Replication

A Beginner’s Perspective

recommendations link naming
Recommendations -- Link Naming
  • init parameters
    • db_name = <SID>
    • db_domain = WORLD
    • global_names = TRUE
    • check that global_name = <SID>.WORLD
    • links named <SID>.WORLD
    • tnsnames.ora use <SID> for service identifier

Oracle8 Advanced Replication

A Beginner’s Perspective

recommendation link configuration
Recommendation -- Link Configuration
  • Follow setup shown in training class
  • public link
    • create link to remote site, but don’t use ‘connect as’ clause
    • easier to maintain when master sites change
    • only need to change tnsnames service identifier(s)
  • private link
    • create link that only specifies the ‘connect as’ clause
    • protects security of the link password
    • stays that same even when public link has to change

Oracle8 Advanced Replication

A Beginner’s Perspective

recommendations server config
Recommendations -- Server Config
  • SGA 40 - 80 Mb
  • RBS tablespace - 30 Mb
  • init<SID>.ora parameters
    • Background processes
      • snapshot_refresh_processes = 2
      • job_queue_processes = 2
      • job_queue_interval = 60 (seconds -- default)
    • Other
      • distributed_lock_timeout = 300 (seconds)

Oracle8 Advanced Replication

A Beginner’s Perspective

recommendations practice
Recommendations -- Practice
  • Complete script to start and stop replication
    • Appendix B and C
  • Use script to setup development
    • correct syntax errors etc.
  • Use script to setup production
  • Script then serves
    • training doc
    • tech support doc

Oracle8 Advanced Replication

A Beginner’s Perspective

supporting replication
Supporting Replication
  • Central organization supplies DBA support
  • DBA’s have ever supported replication
    • None have Oracle8 replication training
  • Existing application support persons have no replication experience
  • Application developers have not been exposed to replication issues
  • must have SYS access to install/config
  • repadmin user must have DBA role

Oracle8 Advanced Replication

A Beginner’s Perspective

if replication breaks
If Replication Breaks...
  • Whatta ya gonna do?
    • default is keep replicating after error is detected
      • ok for some apps, but not for others
    • Checking account balance...
    • Updating status of an order...
  • Fallback plan
    • cron jobs
    • easier for ‘simple’ replication scheme

Oracle8 Advanced Replication

A Beginner’s Perspective

once replication breaks
Once Replication Breaks
  • Business impact of replication downtime
    • can business users work around?
  • Time frame
    • how long can replication be down?
    • how long to re-synch?
    • system locked while synchronizing?
    • synch time increases as datasets grow

Oracle8 Advanced Replication

A Beginner’s Perspective

troubleshooting example 1
Troubleshooting Example 1
  • No PK on table at master definition site
  • create PK, add table to repgroup, add master site
  • Table at master site has no PK
  • Solution
    • re-create master repobject at master definition site with retry=>true

Oracle8 Advanced Replication

A Beginner’s Perspective

troubleshooting example 2
Troubleshooting Example 2
  • Added master site, table already exists at master site
  • Dropped master site
  • Can’t add master site again
    • repgroup already at master site
  • Can’t drop repobject from master def site
    • master def doesn’t know about master site anymore
  • Read Manuals -- look at options on various commands
    • can drop repgroup from any site
    • drop repgroup at master site
    • at master def site add repobject with reuse=>TRUE, copy_rows=>FALSE

Oracle8 Advanced Replication

A Beginner’s Perspective

troubleshooting example 3
Troubleshooting Example 3
  • Replication setup seems to work
    • all admin commands had to be manually pushed to complete
    • update to replicated table just sits
    • nothing seems to be happening…
  • None of the scheduled ‘jobs’ are running
    • in dba_jobs view, LAST_DATE and LAST_SEC are NULL (no entry)
    • in dba_jobs view, NEXT_DATE and NEXT_SEC never change
    • deferred job queue doesn’t seem to be working

Oracle8 Advanced Replication

A Beginner’s Perspective

troubleshooting example 31
Troubleshooting Example 3
  • Advanced Replication Manual -- repcatlog errors section
    • job queue for newly created db doesn’t start until database restarted
    • our dbs are plenty old, but restarting the db did start the job queue
    • this ‘detail’ is not mentioned under troubleshooting job queues...

Oracle8 Advanced Replication

A Beginner’s Perspective

troubleshooting example 4
Troubleshooting Example 4
  • At master definition site
    • create replication group(s), add objects
    • add second master site
  • Errors in repcatlog
    • ERROR ORA-00060: deadlock detected while waiting for resource
  • But, everything looks ok…
  • Oracle automatically re-applied the transactions, no problem!
  • Set init<SID>.ora parameter distributed_lock_timeout = 300 (seconds)

Oracle8 Advanced Replication

A Beginner’s Perspective

documentation issues
Documentation Issues
  • Replication dba_* views documented
    • never referenced as dba_<viewname> in the docs
  • init parameter -- snapshot_refresh_processes
    • described in Advanced Replication manual
    • not listed in server reference (under init parameters)
  • init parameter -- job_queue_processes
    • what is different from snapshot_refresh_processes?
    • I can’t find any difference...
  • scheduled links
    • the database link is always there
    • a ‘push’ job is scheduled which will use that link

Oracle8 Advanced Replication

A Beginner’s Perspective

command line vs oem
Command Line vs OEM
  • OEM -- not all options of the command line
  • Example -- create user repadmin
    • OEM can’t assign repadmin default and temp tablespaces
  • OEM good for monitoring
    • once replication is setup and running
  • Command line offers more flexibility
  • Tech support assumes the command line
  • Training uses command line, not OEM

Oracle8 Advanced Replication

A Beginner’s Perspective

replication setup process
Replication Setup Process
  • At each step
    • is it ok to start the next step?
    • how to know if previous step succeeded? (or finished failing…?)

Oracle8 Advanced Replication

A Beginner’s Perspective

replication setup process1
Replication Setup Process
  • Look at
    • catrep.sql -- check for all valid packages and for 46 DBMS_REPCAT packages
    • deferror -- conflicts (unresolved)
    • deftran -- entries stay until purge job runs
    • deftrandest -- gone when tran delivered to destination
    • regroup -- status
    • number of repobjects -- 5 per table, table + 2 packages + 2 package bodies
    • create repgroup -- new admin push job appears
    • create replication support -- ‘generated’ in dba_repobject

Oracle8 Advanced Replication

A Beginner’s Perspective

replication views
Replication Views
  • Too much info in most views
  • Need consistent output format
  • Use SQL scripts (Appendix A)
    • dba_jobs_psycho.sql (dba_jobs view)
    • place these scripts in $ORACLE_HOME/local/sql/psycho_scripts
    • maintain scripts at one site copy to all servers
  • Scripts make troubleshooting much easier
  • Scripts helps work with tech support

Oracle8 Advanced Replication

A Beginner’s Perspective

lessons learned
Lessons Learned
  • Oracle8 Advanced Replication Documents don’t do the job
    • much too focused on selling OEM
    • shielding user from API is a dis-service, API must be understood
  • Oracle training class is a pre-requisite to using Advanced Replication
  • We shouldn’t have been so eager to try replication in production

Oracle8 Advanced Replication

A Beginner’s Perspective

lessons learned1
Lessons Learned
  • To have success, must practice setup on development servers
  • catrep.sql must be run in SVRMGRL
    • in SQL*Plus, catrep.sql asks for input (scary!)
    • manuals don’t discuss running catrep.sql
  • User education
    • once replicating, can’t drop/recreate table
    • need to have data in place, not just schema
    • update data through DML only

Oracle8 Advanced Replication

A Beginner’s Perspective

brian r hitchcock
Brian R. Hitchcock
  • brian.hitchcock@sun.com
  • Appendix A -- Scripts
  • Appendix B -- Script Starting Replication
  • Appendix C -- Script Stopping Replication

Oracle8 Advanced Replication

A Beginner’s Perspective

appendix a scripts
Appendix A -- Scripts
  • $ORACLE_HOME/local/sql/psycho_scripts
    • check_catrep_psycho
    • check_ques_psycho
    • dba_db_links_psycho
    • dba_jobs_psycho
    • dba_repcatlog_psycho
    • dba_repgroup_psycho
    • dba_repobject_psycho
    • dba_repobject_tables_psycho
    • dba_repsites_psycho
    • deferror_psycho
    • deftran_psycho
    • deftrandest_psycho

Oracle8 Advanced Replication

A Beginner’s Perspective

scripts
Scripts

SQL> @check_ques_psycho

GLOBAL_NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PLAN.WORLD

USER is "REPADMIN"

rows in dba_repcatlog...

COUNT(*)

----------

0

rows in deferror...

COUNT(*)

----------

3

rows in deftran...

COUNT(*)

----------

5

rows in deftrandest...

COUNT(*)

----------

0

Oracle8 Advanced Replication

A Beginner’s Perspective

scripts1
Scripts

SQL> @dba_jobs_psycho

GLOBAL_NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PLAN.WORLD

USER is "REPADMIN"

number of jobs currently...

COUNT(*)

----------

7

selecting jobs info from dba_jobs view...

JOB LOG_USER PRIV_USER SCHEMA_USE

---------- ---------- ---------- ----------

59 REPADMIN REPADMIN REPADMIN

57 REPADMIN REPADMIN REPADMIN

58 REPADMIN REPADMIN REPADMIN

63 REPADMIN REPADMIN REPADMIN

64 REPADMIN REPADMIN REPADMIN

65 REPADMIN REPADMIN REPADMIN

66 REPADMIN REPADMIN REPADMIN

7 rows selected.

current time at this site

Mon Nov 2 13:14:20 PST 1998

JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC

---------- --------- -------- --------- -------- --------- --------

59 02-NOV-98 07:20:30 03-NOV-98 07:20:30

57 02-NOV-98 13:01:30 02-NOV-98 14:01:30

58 02-NOV-98 13:02:30 02-NOV-98 14:02:30

63 02-NOV-98 13:11:48 02-NOV-98 13:21:48

64 02-NOV-98 13:06:47 02-NOV-98 13:16:47

65 02-NOV-98 13:07:30 02-NOV-98 13:17:30

66 02-NOV-98 13:13:48 02-NOV-98 13:23:48

7 rows selected.

Oracle8 Advanced Replication

A Beginner’s Perspective

scripts2
Scripts

JOB TOTAL_TIME B INTERVAL FAILURES

---------- ---------- - -------------------- ----------

59 54 N sysdate + 1 0

57 214 N sysdate + 1/24 0

58 2553 N sysdate + 1/24 0

63 72 N SYSDATE + (1/144) 0

64 139 N SYSDATE + (1/144) 0

65 37 N SYSDATE + (1/144) 0

66 40 N SYSDATE + (1/144) 0

7 rows selected.

JOB WHAT

---------- --------------------------------------------------------------------------------

59 declare rc binary_integer; begin rc := sys.dbms_defer_sys.purge( delay_seconds=>

0); end;

57 declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'EUR

O.WORLD', stop_on_error=>FALSE, delay_seconds=>0, parallelism=>1); end;

58 declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'ASI

A.WORLD', stop_on_error=>FALSE, delay_seconds=>0, parallelism=>1); end;

63 dbms_repcat.do_deferred_repcat_admin('"PLAN"', FALSE);

64 dbms_repcat.do_deferred_repcat_admin('"PLANNING"', FALSE);

JOB WHAT

---------- --------------------------------------------------------------------------------

65 dbms_repcat.do_deferred_repcat_admin('"ALLOCATION"', FALSE);

66 dbms_repcat.do_deferred_repcat_admin('"SUPPLYPLAN"', FALSE);

7 rows selected.

Oracle8 Advanced Replication

A Beginner’s Perspective

scripts3
Scripts

::::::::::::::

dba_db_links_psycho.sql

::::::::::::::

select * from global_name;

show user

column owner format a10

column db_link format a15

column username format a15

column host format a15

select owner, db_link, username, host, created from

dba_db_links;

::::::::::::::

dba_jobs_psycho.sql

::::::::::::::

select * from global_name;

show user

column log_user format a10

column priv_user format a10

column schema_user format a10

column interval format a20

column what format a80

PROMPT

PROMPT number of jobs currently...

select count(*) from dba_jobs;

PROMPT selecting jobs info from dba_jobs view...

select job, log_user, priv_user, schema_user from dba_jobs;

PROMPT current time at this site

! date

select job, last_date, last_sec, this_date, this_sec,

next_date, next_sec from dba_jobs;

select job, total_time, broken, interval, failures from

dba_jobs;

select job, what from dba_jobs;

::::::::::::::

check_catrep_psycho.sql

::::::::::::::

select * from global_name;

show user

set linesize 200

select substr(owner, 1,6), substr(object_name, 1, 20), object_type,

status from dba_objects where object_name like '%DBMS%';

PROMPT count(*) from dba_objects where object_name like '%DBMS_REPCAT%';

select count(*) from dba_objects where object_name like '%DBMS_REPCAT%';

PROMPT count(*) from dba_objects where object_name like '%DBMS_REPCAT%' and

status='VALID';

select count(*) from dba_objects where object_name like '%DBMS_REPCAT%' and

status='VALID';

::::::::::::::

check_ques_psycho.sql

::::::::::::::

select * from global_name;

show user

PROMPT rows in dba_repcatlog...

select count(*) from dba_repcatlog;

PROMPT rows in deferror...

select count(*) from deferror;

PROMPT rows in deftran...

select count(*) from deftran;

PROMPT rows in deftrandest...

select count(*) from deftrandest;

Oracle8 Advanced Replication

A Beginner’s Perspective

scripts4
Scripts

::::::::::::::

dba_repcatlog_psycho.sql

::::::::::::::

column id format 9999

column source format a15

column master format a15

column userid format a10

column sname format a20

column gname format a20

select * from global_name;

show user

select id, status, source, master, timestamp, role from

dba_repcatlog;

select id, status, sname, gname, type from dba_repcatlog;

select id, status, request, errnum from dba_repcatlog;

select id, status, message from dba_repcatlog;

::::::::::::::

dba_repgroup_psycho.sql

::::::::::::::

select * from global_name;

show user

column sname format a10

column gname format a10

column master format a4

column schema_comment format a30

select gname, sname, master, status, schema_comment from

dba_repgroup;

::::::::::::::

dba_repobject_psycho.sql

::::::::::::::

select * from global_name;

show user

column sname format a10

column oname format a20

column gname format a10

column object_comment format a40

select oname, gname, type, status, generation_status, id from

dba_repobject;

select oname, sname, object_comment, min_communication from

dba_repobject;

::::::::::::::

dba_repobject_tables_psycho.sql

::::::::::::::

select * from global_name;

show user

column sname format a10

column oname format a20

column gname format a10

column object_comment format a40

select oname, gname, type, status, generation_status, id from

dba_repobject

where type='TABLE';

::::::::::::::

dba_repsites_psycho.sql

::::::::::::::

select * from global_name;

show user

column gname format a10

column dblink format a20

column masterdef format a4

column snapmaster format a4

column master format a4

column my_dblink format a6

column master_comment format a10

column masterdef heading MDEF

column snapmaster heading SMAS

column master heading MAST

column my_dblink heading MYLINK

select gname, dblink, masterdef, snapmaster, master,

prop_updates, my_dblink, master_comment from dba_repsites;

Oracle8 Advanced Replication

A Beginner’s Perspective

scripts5
Scripts

::::::::::::::

deferror_psycho.sql

::::::::::::::

select * from global_name;

show user

column deferred_tran_id format a15

column origin_tran_db format a15

column origin_tran_id format a15

column callno format 9999

column destination format a15

column error_msg format a50

column receiver format a15

select deferred_tran_id, origin_tran_db, origin_tran_id, callno, destination,

start_time from deferror;

select error_number, receiver, error_msg from deferror;

::::::::::::::

deftran_psycho.sql

::::::::::::::

select * from global_name;

show user

select * from deftran;

::::::::::::::

deftrandest_psycho.sql

::::::::::::::

select * from global_name;

show user

column dblink format a15

select * from deftrandest;

Oracle8 Advanced Replication

A Beginner’s Perspective

appendix b start replication
Appendix B -- Start Replication

ALLOCATION schema

-----------------

ALLOC_ALLOCATION

ALLOC_INT_LEASE

ALLOC_QUARTER

ALLOC_LEASE_FUNCTION

ALLOC_LEASE_PLAN

ALLOC_LEASE_PLAN_SEGMENT

ALLOC_SEGMENT_TYPE

********PLAN -- check for tables, primary key on each table -- REPADMIN in SQLPLUS

column object_name format a30

column owner format a15

column constraint_name format a20

select object_name, object_type, status from dba_objects

where object_name like 'PLAN_%' and object_type='TABLE' and owner='PLAN'

order by object_name;

select owner, constraint_name, constraint_type, table_name from dba_constraints

where table_name like 'PLAN_%' and owner='PLAN' and constraint_type='P'

order by table_name;

select object_name, object_type, status from dba_objects

where object_name like 'OCC_%' and object_type='TABLE' and owner='PLANNING'

order by object_name;

select owner, constraint_name, constraint_type, table_name from dba_constraints

where table_name like 'OCC_%' and owner='PLANNING' and constraint_type='P'

order by table_name;

select object_name, object_type, status from dba_objects

where object_name like 'ALLOC_%' and object_type='TABLE' and owner='ALLOCATION'

order by object_name;

select owner, constraint_name, constraint_type, table_name from dba_constraints

where table_name like 'ALLOC_%' and owner='ALLOCATION' and constraint_type='P'

order by table_name;

select object_name, object_type, status from dba_objects

where object_name like 'ALLOC_%' and object_type='TABLE' and owner='SUPPLYPLAN'

order by object_name;

select owner, constraint_name, constraint_type, table_name from dba_constraints

where table_name like 'ALLOC_%' and owner='SUPPLYPLAN' and constraint_type='P'

order by table_name;

*******PLAN create any needed primary keys...

*******PLAN -- tables to replicate and check for primary keys...

tables to be replicated...

PLAN schema

-----------

PLAN_BU <-------------not currently used, no need to replicate

PLAN_BUILDING

PLAN_COUNTRY

PLAN_LEASE

PLAN_LOCATION

PLAN_LOCATION_LEVEL

PLAN_ORG

PLAN_ORG_LEVEL

PLAN_ORG_MAPPING

PLAN_PERSON

PLAN_SITE

PLANNING schema

---------------

OCC_ACCESS

OCC_COMPANY

OCC_DEPARTMENT

OCC_DESIGN_VALUES

OCC_DIVISION

OCC_FLOOR

OCC_GEO

OCC_HR_ORG

OCC_LOAD

OCC_METRO_AREA

OCC_ORG_MAP

OCC_PLANNER

OCC_QUARTER

OCC_RED_SEAT_SUMMARY

OCC_ROOM

OCC_SEAT

OCC_SEAT_SUMMARY

OCC_SOS

Oracle8 Advanced Replication

A Beginner’s Perspective

script start replication
Script -- Start Replication

*******PLAN -- export schemas

cd /home/dbdump/export

exp plan/****** owner=plan file=PLAN_plan_103098.exp

exp planning/****** owner=planning file=PLAN_planning_103098.exp

exp allocation/****** owner=allocation file=PLAN_allocation_103098.exp

exp supplyplan/****** owner=supplyplan file=PLAN_supplyplan_103098.exp

ls -l

*******EURO -- drop/recreate user schemas -- SYS in SQL*PLUS

select * from global_name;

select count(*) from dba_objects where owner='PLAN';

select count(*) from dba_objects where owner='PLANNING';

select count(*) from dba_objects where owner='ALLOCATION';

select count(*) from dba_objects where owner='SUPPLYPLAN';

select * from dba_users where username='PLAN';

select * from dba_users where username='PLANNING';

select * from dba_users where username='ALLOCATION';

select * from dba_users where username='SUPPLYPLAN';

drop user PLAN cascade;

drop user PLANNING cascade;

drop user ALLOCATION cascade;

drop user SUPPLYPLAN cascade;

select username from dba_users;

create user PLAN identified by plan

default tablespace DATA1

temporary tablespace TEMP

quota unlimited on DATA1;

grant connect to PLAN;

grant resource to PLAN;

create user PLANNING identified by planning

default tablespace DATA1

temporary tablespace TEMP

quota unlimited on DATA1;

grant connect to PLANNING;

grant resource to PLANNING;

create user ALLOCATION identified by allocation

default tablespace DATA1

temporary tablespace TEMP

quota unlimited on DATA1;

grant connect to ALLOCATION;

grant resource to ALLOCATION;

create user SUPPLYPLAN identified by supplyplan

default tablespace DATA1

temporary tablespace TEMP

quota unlimited on DATA1;

grant connect to SUPPLYPLAN;

grant resource to SUPPLYPLAN;

select * from dba_users where username='PLAN';

select * from dba_users where username='PLANNING';

select * from dba_users where username='ALLOCATION';

select * from dba_users where username='SUPPLYPLAN';

*******ASIA -- drop/recreate user schemas

select * from global_name;

select count(*) from dba_objects where owner='PLAN';

select count(*) from dba_objects where owner='PLANNING';

select count(*) from dba_objects where owner='ALLOCATION';

select count(*) from dba_objects where owner='SUPPLYPLAN';

select * from dba_users where username='PLAN';

select * from dba_users where username='PLANNING';

select * from dba_users where username='ALLOCATION';

select * from dba_users where username='SUPPLYPLAN';

drop user PLAN cascade;

drop user PLANNING cascade;

drop user ALLOCATION cascade;

drop user SUPPLYPLAN cascade;

select username from dba_users;

Oracle8 Advanced Replication

A Beginner’s Perspective

script start replication1
Script -- Start Replication

********EURO -- ftp export files from PLAN

cd /db_export/EURO

ftp rewplanning.ebay

cd /home/dbdump/export

bin

get PLAN_plan_103098.exp

get PLAN_planning_103098.exp

get PLAN_allocation_103098.exp

get PLAN_supplyplan_103098.exp

ls -l

*******ASIA -- ftp export files from PLAN

cd /backup/oracle/ASIA/export

ftp rewplanning.ebay

cd /home/dbdump/export

bin

get PLAN_plan_103098.exp

get PLAN_planning_103098.exp

get PLAN_allocation_103098.exp

get PLAN_supplyplan_103098.exp

ls -l

*******EURO -- import schemas

cd /db_export/EURO

imp plan/****** fromuser=plan touser=plan file=PLAN_plan_103098.exp

imp sys/****** fromuser=planning touser=planning file=PLAN_planning_103098.exp

imp sys/****** fromuser=allocation touser=allocation file=PLAN_allocation_103098.exp

imp supplyplan/****** fromuser=supplyplan touser=supplyplan file=PLAN_supplyplan_103098.exp

*******ASIA -- import schemas

cd /backup/oracle/ASIA/export

imp plan/****** fromuser=plan touser=plan file=PLAN_plan_103098.exp

imp planning/****** fromuser=planning touser=planning file=PLAN_planning_103098.exp

imp allocation/****** fromuser=allocation touser=allocation file=PLAN_allocation_103098.exp

imp supplyplan/****** fromuser=supplyplan touser=supplyplan file=PLAN_supplyplan_103098.exp

create user PLAN identified by ******

default tablespace DATA1

temporary tablespace TEMP

quota unlimited on DATA1;

grant connect to PLAN;

grant resource to PLAN;

create user PLANNING identified by ******

default tablespace DATA1

temporary tablespace TEMP

quota unlimited on DATA1;

grant connect to PLANNING;

grant resource to PLANNING;

create user ALLOCATION identified by ******

default tablespace DATA1

temporary tablespace TEMP

quota unlimited on DATA1;

grant connect to ALLOCATION;

grant resource to ALLOCATION;

create user SUPPLYPLAN identified by ******

default tablespace DATA1

temporary tablespace TEMP

quota unlimited on DATA1;

grant connect to SUPPLYPLAN;

grant resource to SUPPLYPLAN;

select * from dba_users where username='PLAN';

select * from dba_users where username='PLANNING';

select * from dba_users where username='ALLOCATION';

select * from dba_users where username='SUPPLYPLAN';

Oracle8 Advanced Replication

A Beginner’s Perspective

script start replication2
Script -- Start Replication

*******PLAN -- setup repgroups -- REPADMIN in SQLPLUS

@dba_repsites_psycho

@dba_repgroup_psycho

execute DBMS_REPCAT.CREATE_MASTER_REPGROUP ('PLAN');

execute DBMS_REPCAT.CREATE_MASTER_REPGROUP ('PLANNING');

execute DBMS_REPCAT.CREATE_MASTER_REPGROUP ('ALLOCATION');

execute DBMS_REPCAT.CREATE_MASTER_REPGROUP ('SUPPLYPLAN');

--> check for new jobs @dba_jobs_psycho

--> check for new repgroup, status='QUIESCED'

@dba_jobs_psycho

@dba_repsites_psycho

@dba_repgroup_psycho

*******PLAN -- register objects within rep groups -- REPADMIN in SQLPLUS

***execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_BU','TABLE',copy_rows=>FALSE);*** not currently being replicated

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_BUILDING','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_COUNTRY','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_LEASE','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_LOCATION','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_LOCATION_LEVEL','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_ORG','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_ORG_LEVEL','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_ORG_MAPPING','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_PERSON','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLAN','PLAN_SITE','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_ACCESS','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_COMPANY','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_DEPARTMENT','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_DESIGN_VALUES','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_DIVISION','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_FLOOR','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_GEO','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_HR_ORG','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_LOAD','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_METRO_AREA','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_ORG_MAP','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_PLANNER','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_QUARTER','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_RED_SEAT_SUMMARY','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_ROOM','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_SEAT','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_SEAT_SUMMARY','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('PLANNING','OCC_SOS','TABLE',copy_rows=>FALSE);

Oracle8 Advanced Replication

A Beginner’s Perspective

script start replication3
Script -- Start Replication

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('ALLOCATION','ALLOC_ALLOCATION','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('ALLOCATION','ALLOC_INT_LEASE','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('ALLOCATION','ALLOC_QUARTER','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('SUPPLYPLAN','ALLOC_LEASE_FUNCTION','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('SUPPLYPLAN','ALLOC_LEASE_PLAN','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('SUPPLYPLAN','ALLOC_LEASE_PLAN_SEGMENT','TABLE',copy_rows=>FALSE);

execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT -

('SUPPLYPLAN','ALLOC_SEGMENT_TYPE','TABLE',copy_rows=>FALSE);

@dba_repobject_psycho

@dba_repobject_tables_psycho

*******PLAN generate replication support -- REPADMIN in SQLPLUS

***execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT -

('PLAN','PLAN_BU','TABLE');*** not currently being replicated

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_BUILDING','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_COUNTRY','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_LEASE','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_LOCATION','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_LOCATION_LEVEL','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_ORG','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_ORG_LEVEL','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_ORG_MAPPING','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_PERSON','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLAN','PLAN_SITE','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_ACCESS','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_COMPANY','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_DEPARTMENT','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_DESIGN_VALUES','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_DIVISION','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_FLOOR','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_GEO','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_HR_ORG','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_LOAD','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_METRO_AREA','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_ORG_MAP','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_PLANNER','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_QUARTER','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_RED_SEAT_SUMMARY','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_ROOM','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_SEAT','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_SEAT_SUMMARY','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('PLANNING','OCC_SOS','TABLE');

Oracle8 Advanced Replication

A Beginner’s Perspective

script start replication4
Script -- Start Replication

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('ALLOCATION','ALLOC_ALLOCATION','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('ALLOCATION','ALLOC_INT_LEASE','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('ALLOCATION','ALLOC_QUARTER','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('SUPPLYPLAN','ALLOC_LEASE_FUNCTION','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('SUPPLYPLAN','ALLOC_LEASE_PLAN','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('SUPPLYPLAN','ALLOC_LEASE_PLAN_SEGMENT','TABLE');

execute DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('SUPPLYPLAN','ALLOC_SEGMENT_TYPE','TABLE');

@dba_repobject_tables_psycho

@dba_repobject_psycho

*******PLAN -- add master site EURO -- REPADMIN in SQLPLUS

@check_ques_psycho

execute DBMS_REPCAT.ADD_MASTER_DATABASE -

('PLAN','EURO.WORLD',TRUE,FALSE);

execute DBMS_REPCAT.ADD_MASTER_DATABASE -

('PLANNING','EURO.WORLD',TRUE,FALSE);

execute DBMS_REPCAT.ADD_MASTER_DATABASE -

('ALLOCATION','EURO.WORLD',TRUE,FALSE);

execute DBMS_REPCAT.ADD_MASTER_DATABASE -

('SUPPLYPLAN','EURO.WORLD',TRUE,FALSE);

@dba_repcatlog_psycho

--> check on EURO

@dba_repgroup_psycho

--> check on ASIA

@dba_repgroup_psycho

*******PLAN -- add master site ASIA -- REPADMIN in SQLPLUS

@check_ques_psycho

execute DBMS_REPCAT.ADD_MASTER_DATABASE -

('PLAN','ASIA.WORLD',TRUE,FALSE);

execute DBMS_REPCAT.ADD_MASTER_DATABASE -

('PLANNING','ASIA.WORLD',TRUE,FALSE);

execute DBMS_REPCAT.ADD_MASTER_DATABASE -

('ALLOCATION','ASIA.WORLD',TRUE,FALSE);

execute DBMS_REPCAT.ADD_MASTER_DATABASE -

('SUPPLYPLAN','ASIA.WORLD',TRUE,FALSE);

@dba_repcatlog_psycho

--> check on EURO

@dba_repgroup_psycho

--> check on ASIA

@dba_repgroup_psycho

*******PLAN -- resume master activity for each repgroup -- REPADMIN in SQLPLUS

execute DBMS_REPCAT.RESUME_MASTER_ACTIVITY -

('PLAN',FALSE);

execute DBMS_REPCAT.RESUME_MASTER_ACTIVITY -

('PLANNING',FALSE);

execute DBMS_REPCAT.RESUME_MASTER_ACTIVITY -

('ALLOCATION',FALSE);

execute DBMS_REPCAT.RESUME_MASTER_ACTIVITY -

('SUPPLYPLAN',FALSE);

@dba_repgroup_psycho

--check EURO and ASIA for repgroup status

Oracle8 Advanced Replication

A Beginner’s Perspective

appendix c stop replication
Appendix C -- Stop Replication

--> turn off replication

suspend master activity to EURO and ASIA

drop repgroups on PLAN (leaving objects behind)

drop master sites

--> commands needed...

*******PLAN -- suspend other master sites -- REPADMIN in SQLPLUS

execute DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('PLAN');

execute DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('PLANNING');

execute DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('ALLOCATION');

execute DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('SUPPLYPLAN');

*******PLAN -- drop repgroups, leave objects behind, drop at all sites -- REPADMIN in SQLPLUS

execute DBMS_REPCAT.DROP_MASTER_REPGROUP('PLAN',FALSE,TRUE);

execute DBMS_REPCAT.DROP_MASTER_REPGROUP('PLANNING',FALSE,TRUE);

execute DBMS_REPCAT.DROP_MASTER_REPGROUP('ALLOCATION',FALSE,TRUE);

execute DBMS_REPCAT.DROP_MASTER_REPGROUP('SUPPLYPLAN',FALSE,TRUE);

*******PLAN -- drop master sites -- REPADMIN in SQLPLUS

execute DBMS_REPCAT.REMOVE_MASTER_DATABASES('PLAN','EURO.WORLD');

execute DBMS_REPCAT.REMOVE_MASTER_DATABASES('PLAN','ASIA.WORLD');

execute DBMS_REPCAT.REMOVE_MASTER_DATABASES('PLANNING','EURO.WORLD');

execute DBMS_REPCAT.REMOVE_MASTER_DATABASES('PLANNING','ASIA.WORLD');

execute DBMS_REPCAT.REMOVE_MASTER_DATABASES('ALLOCATION','EURO.WORLD');

execute DBMS_REPCAT.REMOVE_MASTER_DATABASES('ALLOCATION','ASIA.WORLD');

execute DBMS_REPCAT.REMOVE_MASTER_DATABASES('SUPPLYPLAN','EURO.WORLD');

execute DBMS_REPCAT.REMOVE_MASTER_DATABASES('SUPPLYPLAN','ASIA.WORLD');

Oracle8 Advanced Replication

A Beginner’s Perspective