Steps to Install Oralce 12C Database

  1. Install Oracle-12c on the server.
  2. The installation will require that the OIPA database and application be preinstalled. For information on OIPA installation please the relevant guides for OIPA.
  3. The installation assumes that the following schemas for OIPA pre-exist
    • OIPA_PAS
    • OIPA_IVS
  4. Create a schema named ggowner on the Oracle instance where OIPA installed. (for Goldengate replication)
  5. Create separate oracle database instance for each and create the mentioned schemas
    • ODS MODEL : This will be used to store ODS application metadata
      • schema: ODS_MODEL
    • ODS STAGING : This will be a replica of OIPA and will be synced by golden gate
      • schema: ODS_STAGING, ggowner
    • ODS TARGET : This will host the operational data store
      • schema: ODS_TARGET

Note: ggowner schema will be used for goldenGate replication. Grant connect,resource,dba roles to ggowner both in OIPA and ODS STAGING

  1. Grant connect, resource and dba to the above schemas.
  2. Execute the below script in STAGING as ggowner to create the exception table to capture goldengate exceptions.

CREATE TABLE ODSGGEXCEPTIONS

( EXCP_DATE TIMESTAMP (6) DEFAULT systimestamp,

REP_NAME VARCHAR2(10 BYTE),

TABLE_NAME VARCHAR2(56 BYTE),

ERRNO NUMBER,

ERRMSG VARCHAR2(1000 BYTE),

ERRTYPE VARCHAR2(6 BYTE),

OPTYPE VARCHAR2(24 BYTE),

TRANSIND VARCHAR2(12 BYTE),

TRANSIMGIND VARCHAR2(8 BYTE),

COMMITTIMESTAMP VARCHAR2(26 BYTE),

RECCSN NUMBER,

RECSEQNO NUMBER,

RECRBA NUMBER,

RECTRANSPOS NUMBER,

RECLENGTH NUMBER,

LOGRBA NUMBER,

LOGPOSITION NUMBER,

GROUPTYPE VARCHAR2(12 BYTE),

FILENAME VARCHAR2(50 BYTE),

FILENO NUMBER,

SRCROWID VARCHAR2(40 BYTE),

SRCDBCHARSET VARCHAR2(40 BYTE),

REPLAG NUMBER,

CNT_CDR_CONFLICTS NUMBER,

CNT_CDR_RESOLUTIONS NUMBER,

CNT_CDR_FAILED NUMBER

);

  1. Execute 'migrate-ods.sql' on ODS MODEL database(ODS_MODEL schema).
  2. Restore the latest OIPA dump to ODS_STAGING schema.
  3. Create below Database Links.
    1. Database link named DBLINK_ODS_MODEL from ODS_STAGING to ODS_MODEL
    2. Database link named DBLINK_ODS_MODEL from ODS_TARGET to ODS_MODEL
    3. Database link named DBLINK_ODS_TARGET from ODS_STAGING to ODS_TARGET
  4. Execute the below as SYS in the STAGING database:

grant execute on utl_http to ODS_STAGING

/

grant execute on DBMS_NETWORK_ACL_ADMIN to ODS_STAGING

/

grant select on dba_network_acls to ODS_STAGING

/

grant select on dba_network_acl_privileges to ODS_STAGING

/

grant create any job to ODS_STAGING

/

grant execute on dbms_lock to ODS_STAGING

/

grant create synonym to ODS_STAGING

/

  1. Execute the below as SYS in the TARGET database.

grant execute on utl_http to ODS_TARGET

/

grant execute on DBMS_NETWORK_ACL_ADMIN to ODS_TARGET

/

grant select on dba_network_acls to ODS_TARGET

/

grant select on dba_network_acl_privileges to ODS_TARGET

/

  1. Execute 'migrate-staging.sql' on ODS STAGING database.
  2. Data migration process from STAGING to TARGET allows a maximum of 50 tables to be populated on parallel. Make sure the Oracle instance will be able to handle those additional requests. If the number of allowed processes is less, increase the limit.

SQL>  select resource_name, current_utilization,max_utilization, limit_value from v$resource_limit where resource_name in ('processes');

  1. To increase the number of allowed process, execute the below as sys and restart the database.

SQL>  alter system set processes=<<input the limit as int>> scope=spfile;

  1. Execute 'migrate-target.sql' on ODS_TARGET database.

 

 

Oracle Insurance Logo Copyright © 2017, Oracle and/or its affiliates. All rights reserved. About Oracle Insurance | Contact Us