Purpose:
Create a test replication environment that is similar to the production environment for MYDBP and MYDBB
References:
MetaLink Note:117434. Initial Steps Required to Create a Multi Master Replication Environment v8.1/v9.x
Procedure:
1. Create MYDBQA and MYDBQB databases
a. Shutdown the MYDBQA
b. Delete all the datafiles, controlfiles, redologfiles
c. Recreate the password file at $ORACLE_HOME/dbs
orapw file=orapwMYDBQA.ora password=xxxx
d. Edit a simple initMYDBQA.ora (see note)
e. Start sqlplus, startup the instance in nomount mode (using the simple initMYDBQA.ora)
f. Create the database
Run the script: cr_mydbqa.sql ( only created system tablespace)
After succeed, shutdown, restart with original initMYDBQA.ora
Create some additional tablespaces and users
g. Add rollback segment
h. Run catalog.sql and catproc.sql ( as SYS)
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
As system
@?/sqlplus/admin/pupbld.sql
Note: keep an old copy of initMYDBQA.ora file. Create a simple initMYDBQA.ora, including only necessary parameters such as those dump dir, db_name, db_block_size etc in the file first. After successfully start up the instance using this simple init.ora, running the create database script. If directly use the old initMYDBQA.ora to start up the instance, it will encounter an error at the creating database stage: ORA-02084: database name is missing a component. Not sure why it happened. After that, shutdown the database, using the old initMYDBQA.ora,
,check it can be startup/shutdown normally.
2. Preparations for replication environment:
2.1 Check init.ora parameter of MYDBQA, MYDBQB database, compared with MYDBP, MYDBB
2.2 Check tablespace requirements
3. Install the Replication Catalogue
Note: Create an new tablespace mydb_rep_data, change the default tablespace for user ‘SYSTEM’ to mydb_rep_data, after install the replication catalogue, change the default tablespace back to system for user ‘SYSTEM’
3.1 Connect to the database as sys (mydb_rep_data is the tablespace used)
a. Change user SYSTEM’s default tablespace to MYDB_REP_DATA
ALTER USER SYSTEM DEFAULT TABLESPACE MYDB_REP_DATA;
b. change sys and system user’s temporary tablespace to temp (optional – not critical for setting up replication)
alter user sys temporary tablespace temp;
alter user system temporary tablespace temp;
3.2. Execute the script CATREP.SQL once the database has started. ( as SYS)
a. execute the script catdefer.sql ( as system, not sure if necessary, it does not hurt)
@?/rdbms/admin/catdefer
b. execute catrep.sql as sys
@?/rdbms/admin/catrep
SQL> SPOOL output.log
Once CATREP.SQL has completed, turn off the output spooling.
SQL> SPOOL OFF
Check OUTPUT.LOG for errors before proceeding.
3.3 Confirm that CATREP.SQL ran correctly by running a query on ALL_OBJECTS
where STATUS = 'INVALID'. For example,
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE STATUS = 'INVALID' and OWNER=’SYS’;
2
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------
SYS DBMS_OBFUSCATION_TOOLKIT PACKAGE BODY
SYS DBMS_PSP PACKAGE BODY
SYS DBMS_SNAP_INTERNAL PACKAGE BODY
SYS DBMS_UTILITY PACKAGE BODY
SYS UTL_TCP PACKAGE BODY
If you find that any of the SYS or SYSTEM package bodies have compiled
incorrectly, recompile them manually.
SQL> ALTER PACKAGE
alter package DBMS_OBFUSCATION_TOOLKIT compile body;
alter package DBMS_PSP compile body;
alter package DBMS_PSP compile body;
alter package DBMS_SNAP_INTERNAL compile body;
alter package DBMS_UTILITY compile body;
alter package UTL_TCP compile body;
If CATREP.SQL has run successfully, a number of replication catalog tables are created in the MYDB_REP_DATA tablespace. The database is now setup for advanced replication.
3.4 Change the default tablespace of the user ‘SYSTEM’ back to system.
alter user system default tablespace system;
Note: Oracle9i creates the replication catalog as part of the CATALOG.SQL script,
so customers running Oracle9i 9.0 / 9.2 can skip this section. However it might be useful to check the catalog is valid. ( In this case we may want to move the replication packages from SYSTEM tablespace to MYDB_REP_DATA tablespace)
4. Configure Oracle Net
Configuration file: listner.ora and tnsname.ora, already exist, no changes needed
5. Using OEM to setup a sample replication environment
( just to get a feeling about replication, clean objects created in this once done. Skip this step normally!)
5.1 Create a user: nbk9lsj on both MYDBQA and MYDBQB database. Create table t1 and t2.
5.2 Using OEM to create a replication group
nbk9lsj_grp, t1, t2 are replicated objects in this group
In OEM, Click: Distributed -> Advanced Replication -> right click: Multimaster replication, then follow the wizard to set up multimaster replication environment
Click Setup Master Sites... to launch a wizard that helps you set up a multimaster replication environment.
5.3. Create Master Group.
Login as repadmin, follow the wizard.
Upon completion, the new group will appear in the navigator, and a request will be submitted to start the replication process.
From the navigator, select the master group you just created. Multimaster replication is running if the master group status is Started.
( undo everything in this step)
6. Import schema objects to the newly created MYDBQA and MYDBQB.
Do creating public synonym, grant, compile invalid object, analyze etc after importing
Schema: MYDB, DEV, GWH, MYDBRSV
/oracle/admin/MYDBQA/create/scripts/impmydb/MYDBQA/REFRESH_ALL/reload_obj.sh
Note: when imp from MYDBP backup, there are many replication packages in mydb schema remain invalid, ignore. ( drop those replication package as well in the future)
7. Implement the replication using script:
( script directory: …/create/rep)
1) Create the replication administrator (edit and execute the following at /create/rep/admin as system).[1 ]
cr_admin.sql
Note: the following should be issued first to avoid some error:
system@MYDBQA> @?/sqlplus/admin/pupbld.sql
2) Create directories (rg_repgroupname) under the …/create/rep directory for each replication group.[2]
3) Copy the files from the rg_x directory to each rg_repgroupname directory.[2]
4) Edit the files in each of the rg_repgroupname directory to correspond to the replication group.[2 ]
5) Synchronize the data in all databases, if necessary.
6) Execute each of these scripts for each replication group (as repadmin):[2]
While running these, check dba_repcatlog for admin request.
Make sure that all admin request have completed before executing the next script.
cr_rg - after running this one for a group, change the job interval!
mydb_cr_ro2
cr_ro
mydb_cr_rs2
cr_rs
cr_ms
Note:
(1) mydb_cr_ro2 and mydb_cr_rs2 are created to replace the original mydb_cr_ro and mydb_cr_rs. They use db link to obtain the replicated table names from MYDBP.
As repadmin, create the db link as follows:
SQL> create database link MYDBP.WORLD
connect to system identified by
(2) The script doitall.sql contains all above scripts for convenience. (It seems not guaranteed that the rep group will be in normal status after applying this script. The status of rep group could be still quiesced. But using OEM to adjust after running doitall is usually easy.)
(3) Using OEM to change the interval of the dbms_repcat.do_deferred_repcat_admin job.
If you want to use command line, follow these steps:
a. Login as repadmin
b. Find the job id for the replication group, e.g.
SQL> select job from dba_jobs where what like '%RG_MYDB_CF_AC%';
JOB
----------
5
c. Change the interval
BEGIN DBMS_JOB.CHANGE(
job => 5,
next_date => sysdate,
interval => '/*10:Secs*/ sysdate + 10/(60*60*24)',
what => NULL);
END;
/
d. Run the job
execute dbms_job.run(5);
After the replication has been created.
7) Schedule the purge (as repadmin).[1]
sch_purge
8) Schedule the push (as repadmin).[1]
sch_push
(Note: 7,8 only need to execute once for the first group)
9) Resume master activity (as repadmin).[2]
rsum_master_act
10) Schedule jobs to monitor the dba jobs and deferred transactions.[1]
[1]= Execute the step on all sites.
[2] = Execute the step on the master definition site only.
No comments:
Post a Comment