Differences

This shows you the differences between two versions of the page.

Link to this comparison view

en:database_replication_oracle_manual [2016/12/16 10:16] (current)
Line 1: Line 1:
 +Last modified: Mar 03, 2009 by A├»del\\
 +\\
 +
 +====== Database replication [ Oracle Manual ] ======
 +
 +\\
 +\\
 +
 +=====  Getting started ​ =====
 +
 +
 +====  Version ​ ====
 +
 +The procedure of replication is released with the Oracle version 10.2.. ​
 +====  Preparation ​ ====
 +
 +WARNING : To applicate this procedure, the SYSDBA rights are indispensable.\\
 +To make sure that the replication will not meet any problems , it is preferable that the duplicated database is set up like this :\\
 +- tablespace name : CIC\\
 +- size : 200 Mbytes\\
 +The creation of Tablespace is a necessary step to duplicate the CIC database.
 +=====  Environnemt ​ =====
 +
 +
 +===  How to know if the CIC user exists ​ ===
 +
 +Connect you as SYSDBA and run this command :
 +<​code>​
 +[oracle@egee004 db_2]$ sqlplus
 +
 +SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 6 15:51:44 2006
 +
 +Copyright (c) 1982, 2005, Oracle. ​ All rights reserved.
 +
 +Enter user-name: SYS as SYSDBA
 +Enter password: ​
 +
 +Connected to:
 +Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 +With the Partitioning,​ OLAP and Data Mining options
 +
 +SQL> select USERNAME from DBA_USERS where USERNAME='​CIC';​
 +
 +USERNAME
 +------------------------------
 +CIC 
 +</​code>​
 +
 +===  Delete CIC user  ===
 +
 +To delete an user, you have to , in first time , locked connections from the CIC user.\\
 +In this way, no new connection from CIC user will be possible :
 +<​code>​
 +SQL> ALTER USER CIC ACCOUNT LOCK;
 +
 +User altered.
 +
 +</​code>​
 +Now, we have to kill all old connections done with the CIC user :\\
 +To see all connections with the CIC user, run this command :
 +<​code>​
 +SQL> select SID,​SERIAL# ​ from v$session where username='​CIC';​
 +
 +       ​SID ​   SERIAL#
 +---------- ----------
 +       ​128 ​      8719
 +       ​129 ​      8371
 +
 +2 rows selected.
 +</​code>​
 +In this example, there are 2 connections in progress from CIC user :\\
 +To close them, you have to run this query :
 +<​code>​
 +SQL> alter system kill session '​128,​8719'​ immediate;
 +
 +System altered.
 +
 +SQL> alter system kill session '​129,​8371'​ immediate;
 +
 +System altered.
 +
 +</​code>​
 +Closing of Connections may take few minutes , so check all connections have been closed before to kill the CIC user :
 +<​code>​
 +SQL> select SID,​SERIAL# ​ from v$session where username='​CIC';​
 +
 +no rows selected
 +
 +
 +Now, you are ready to drop the CIC user :
 +
 +SQL> drop user CIC cascade;
 +
 +User dropped.
 +
 +</​code>​
 +
 +===  account status ​ ===
 +
 +CIC db contains all VO data therefore many partnairs like Gridview,​bazaar,​yaim and GGUS need to access it.\\
 +Consequently,​ you should check following users exist in the target database :\\
 +\\
 +EXT_CIC\\
 +BAZAAR\\
 +YAIM\\
 +\\
 +You can check the status of these users like this :
 +<​code>​
 +SQL> select USERNAME,​ACCOUNT_STATUS from DBA_USERS where USERNAME='​EXT_CIC'​ or USERNAME='​BAZAAR'​ or USERNAME='​YAIM';​
 +
 +USERNAME ​                      ​ACCOUNT_STATUS
 +------------------------------ --------------------------------
 +EXt_CIC ​                       OPEN
 +BAZAAR ​                        OPEN
 +YAIM                           ​LOCKED
 +
 +</​code>​
 +If one or many users doesn'​t exist, please add them [more details may be get in the "One or many CIC users doesn'​t exist" chapter].\\
 +If one or many users have a ACCOUNT_STATUS=LOCKED,​ please alter them [more details may get in the "One or many CIC users are locked"​ chapter]. ​
 +===  One or many CIC users doesn'​t exist  ===
 +
 +Create EXT_CIC:
 +<​code>​
 +create user EXT_CIC identified by values '​AED77C3DF871FDBA'​ default tablespace CIC temporary tablespace TEMP profile DEFAULT;
 +grant connect to EXT_CIC;
 +</​code>​
 +Create BAZAAR :
 +<​code>​
 +create user bazaar identified by values '​527F3BBF3BA42725'​ default tablespace CIC temporary tablespace TEMP profile DEFAULT;
 +
 +grant connect to BAZAAR;
 +</​code>​
 +Create YAIM :
 +<​code>​
 +create user YAIM identified by values '​A76BC2ADBB02F829'​ default tablespace CIC temporary tablespace TEMP profile DEFAULT;
 +grant connect to YAIM ;
 +</​code>​
 +
 +===  One or many CIC users are locked ​ ===
 +
 +For each account where the ACCOUNT_STATUS is LOCKED, please alter it with the following command :
 +<​code>​
 +SQL> alter user YAIM ACCOUNT UNLOCK;
 +
 +User altered.
 +
 +SQL> select USERNAME,​ACCOUNT_STATUS from DBA_USERS where USERNAME='​EXT_CIC'​ or USERNAME='​BAZAAR'​ or USERNAME='​YAIM'​ or USERNAME='​YAIM';​
 +
 +USERNAME ​                      ​ACCOUNT_STATUS
 +------------------------------ --------------------------------
 +EXT_CIC ​                       OPEN
 +BAZAAR ​                        OPEN
 +YAIM                           OPEN
 +CIC                            OPEN
 +
 +</​code>​
 +
 +===  Initialisation of the CIC user  ===
 +
 +to avoid the connection problems between various applications,​ It adviced to keep the password below .
 +<​code>​
 +sqlplus / as sysdba
 + // Create the CIC user with his password
 +SQL > create user CIC identified by values '​5E5B46F0B38446FD'​ default tablespace CIC temporary tablespace TEMP profile DEFAULT;
 +
 + // Add the privileges to the CIC users
 +SQL > conn / as sysdba ​
 +SQL > grant CICFAILOVER to cic;
 +SQL > grant read,write on direcory CICDUMP directory to CIC;
 +SQL > alter user CIC quota UNLIMITED on CIC;
 +SQL > create database link CIC.IN2P3.FR USING '​CIC.IN2P3.FR';​
 +</​code>​
 +=====  Moving Data from IN2P3 to CNAF  =====
 +
 +Before importing the backup to CNAF, you have to INTIALIZE the CIC account.\\
 +To do that you have to connect on CNAF Oracle Server :
 +<​code>​
 +1) Connect you to the bastion.cnaf.infn.it host
 +2) ssh oracle@egee004
 +</​code>​
 +if the CIC account exists, you should drop it by following instructions described in the '​Delete CIC user' chapter.\\
 +Once the CIC user does not exist any more you should create it again as indicated in the "​Initialization of the CIC user" chapter.\\
 +Check all external accounts like EXT_CIC,​BAZAAR and YAIM already exist [refer to '​account status'​ chapter] in case or one of them does not exist create them [refer to 'One or many CIC users doesn'​t exist' chapter].\\
 +\\
 +Now, you are ready to export the database from IN2P3 to CNAF.\\
 +\\
 +To copy data from IN2P3 to CNAF, you need to achieve the following command :\\
 +This command will automatically import all data into the CNAF database.\\
 +WARNING : The migration may take 1h30.
 +<​code>​
 +[oracle@egee004 ]cd ~/​CIC-Portal-dump
 +[oracle@egee004 CIC-Portal-dump]$ impdp cic/​cicinfo2006 PARFILE=cic.par
 +
 +Import: Release 10.2.0.1.0 - Production on Thursday, 11 December, 2008 14:36:58
 +
 +Copyright (c) 2003, 2005, Oracle. ​ All rights reserved.
 +
 +Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 +With the Partitioning,​ OLAP and Data Mining options
 +FLASHBACK automatically enabled to preserve database integrity.
 +Starting "​CIC"​."​SYS_IMPORT_SCHEMA_01": ​ cic/​******** PARFILE=cic.par ​
 +Estimate in progress using BLOCKS method...
 +Processing object type SCHEMA_EXPORT/​TABLE/​TABLE_DATA
 +Total estimation using BLOCKS method: 261 MB
 +Processing object type SCHEMA_EXPORT/​PRE_SCHEMA/​PROCACT_SCHEMA
 +Processing object type SCHEMA_EXPORT/​DB_LINK
 +Processing object type SCHEMA_EXPORT/​SEQUENCE/​SEQUENCE
 +Processing object type SCHEMA_EXPORT/​TABLE/​TABLE
 +...
 +
 +</​code>​
 +When the migration is finished, check all accounts such as EXT_CIC,​BAZAAR and YAIM are well unlocked [refer to '​account status'​ chapter].\\
 +Once all accounts are available, you will have to contact the IN2P3 dba so that they lock the CIC,​EXT_CIC,​YAIM and BAZAAR accounts on the IN2P3 side.
 +=====  Moving Data from CNAF to IN2P3  =====
 +
 +Before importing, make sure all account is locked at CNAF [refer to '​account status'​ chapter ].
 +<​code>​
 +SQL> alter user CIC ACCOUNT LOCK;
 +
 +User altered.
 +
 +</​code>​
 +Now, delete all connections in progress :
 +<​code>​
 +SQL> select SID,​SERIAL# ​ from v$session where username='​CIC';​
 +
 +       ​SID ​   SERIAL#
 +---------- ----------
 +       ​128 ​      8719
 +       ​129 ​      8371
 +
 +2 rows selected.
 +</​code>​
 +In this example, there are 2 connections in progress from CIC user :\\
 +To close them, you have to run this query :
 +<​code>​
 +SQL> alter system kill session '​128,​8719'​ immediate;
 +
 +System altered.
 +
 +SQL> alter system kill session '​129,​8371'​ immediate;
 +
 +System altered.
 +
 +</​code>​
 +Closing of Connections may take few minutes :
 +<​code>​
 +SQL> select SID,SERIAL# from v$session where username='​CIC';​
 +
 +no rows selected
 +</​code>​
 +Now you are ready to export data :
 +<​code>​
 +[oracle@egee004 db_2]$ bin/exp
 +
 +Export: Release 10.2.0.1.0 - Production on Wed Dec 6 13:41:00 2006
 +
 +Copyright (c) 1982, 2005, Oracle. ​ All rights reserved.
 +
 +
 +Username: SYS as SYSDBA
 +Password: ​
 +
 +Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
 +With the Partitioning,​ OLAP and Data Mining options
 +Enter array fetch buffer size: 4096 > 
 +
 +Export file: expdat.dmp > 
 +
 +(1)E(ntire database), (2)U(sers), or (3)T(ables):​ (2)U > 
 +
 +Export grants (yes/no): yes > 
 +
 +Export table data (yes/no): yes > 
 +
 +Compress extents (yes/no): yes > 
 +
 +Export done in US7ASCII character set and AL16UTF16 NCHAR character set
 +server uses WE8ISO8859P1 character set (possible charset conversion)
 +
 +About to export specified users ...
 +User to be exported: (RETURN to quit) > CIC
 +
 +User to be exported: (RETURN to quit) > 
 +
 +. exporting pre-schema procedural objects and actions
 +. exporting foreign function library names for user CIC 
 +. exporting PUBLIC type synonyms
 +. exporting private type synonyms
 +. exporting object type definitions for user CIC 
 +About to export CIC's objects ...
 +. exporting database links
 +. exporting sequence numbers
 +. exporting cluster definitions
 +. about to export CIC's tables via Conventional Path ...
 +. . exporting table                      ASTREINTE ​       206 rows exported
 +...
 +
 +</​code>​
 +Once the data dump is generated copy it on the web cluster and inform dba so that they import data into IN2P3 Oracle server.
 +
  
  • en/database_replication_oracle_manual.txt
  • Last modified: 2016/12/16 10:16
  • (external edit)