Recreation / Rebuild of Reporting database (for the EBS customers using Shareplex replication)

Introduction

Steps for Recreation / Rebuild of Reporting database

Posted by 

If you have Reporting database configured for your UAT or Pre-Prod instance , you may have to recreate/re-configure the reporting database associated with them after a database refresh.

Here I have given a detailed list of Steps to re-build your reporting database (replication database) and configure shareplex replication after Primary database refresh (UAT/Pre-Prod) .

Non-EBS users , please follow only the Database related steps .

1 Shutdown shareplex on both SOURCEDB & REPORTING databases.
2 Take a backup of $vardir of Shareplex in both SOURCEDB & REPORTING
3 Take a backup of Existing TNS Entries of SOURCEDB & REPORTING on both the DB nodes before SOURCEDB clone/refresh.
4 Take a backup of existing Pfile along with the complete DBS Folder.
5 Take a backup of existing XML file of REPORTING . (Very important)
6 Comment (#) the Apps RSYNC cronjob on SOURCEDB Apps server.
7 Once the SOURCEDB refresh is complete, Create REPORTING DB as a standby of SOURCEDB and make sure it is in sync until we decide on downtime of SOURCEDB to configure REPORTING & shareplex.
8 Copy the Application Filesystem from SOURCEDB to REPORTING
9 Once the downtime is decided, Shutdown SOURCEDB Apps + DB and do not start until REPORTING & Shareplex are configured.
10 Break the sync and Activate the Standby Database as Read/Write mode.
11 Open the Standby database.
12 Change the Standby database name to REPORTING using NID.
13 Start REPORTING as RAC on src_01 & tgrt_01
14 Make sure only 2 redo log Threads are enabled
15 Perform rest of the DB post clone and Apps clone steps. (as part of post clone do not mask email addresses & do not do any WF steps)
16 Start SOURCEDB DB
17 Run ora_cleanup (oracle@src_01/tgrt_01: cd $proddir/bin # ./ora_cleansp) in both SOURCEDB & REPORTING
18 Drop & Recreate SPLEX schema with SPLEX & SPLEX_TEMP as default Tablespace & Temp tablespace respectively.
19 Cleanup & recreate SPLEX_TEMP tablespace
20 Disable Triggers in REPORTING database (Create a script to disable them from the schemas that are part of replication config)
21 Disable Constraints in REPORTING  database (Create a script to disable them from the schemas that are part of replication config)
Note : Collect Dictionary stats before disabling constraints , to get better performance during disabling
SQL> exec dbms_stats.gather_dictionary_stats(options => ‘GATHER’);
22 Run ora_setup (exactly as per the attached notes)
23 Create Config using the existing config file itself on SP Source
24 on SP Source, Disable the Sequence replication : set param  SP_OCT_REPLICATE_SEQUENCES 0
25 on SP Source , Enable DDL replication : set param SP_OCT_REPLICATE_ALL_DDL 1
26 on SP Source , increase Thread count : set param SP_OCF_THREAD_COUNT 9
27 Start Shareplex services on both Source & Target
28 Verify Config :  verify config REP_CONFIG_12JUL19 (config name may vary)
29 Activate Config : activate config REP_CONFIG_12JUL19 (config name may vary)
30 Start SOURCEDB Apps
31 Run Autoconfig on REPORTING Apps
32 Start REPORTING Apps
33 Enable Apps Rsync cronjob on SOURCEDB
34 Convert REPORTING Apps to Read Only

 

Recent Posts