1. Introduction
Pre/Post processing is Oracle specific feature which enables you to trigger automatically scripted actions after certain Dbvisit specific evens, such as archivelog apply, failover or switchover. There is a single bash / batch script which is used for your custom actions. The list of actions is predefined and can’t be changed:
Archivelog transfer from primary
Archivelog apply on standby
Graceful Switchover execution on primary
Graceful Switchover execution on standby
Activation (Failover) of standby database
Standby Database is opened read-only
You are able to trigger specific action before and/or after each of the listed actions.
Pre/Post processing is completely different feature than Custom Scripts . We recommend to use either Pre/Post processing or custom scripts - not both.
2. Configuration
For configuring and enabling Pre/Post processing you need to prepare DDC variable DBV_PRE_POST_PROC and create the Pre/Post processing script itself.
2.1 Prepare DDC Parameter
By default, Dbvisit expects that Pre/Post processing script is $DBVISIT_BASE/standbymp/oracle/dbv_pre_post_processing.sh on Linux or %DBVISIT_BASE\standbymp\oracle\dbv_pre_post_processing.cmd on Windows. This is represented by variable DBV_PRE_POST_PROC which has initial value “dbv_pre_post_processing” You can modify this variable as follows:
In this example, we modified the variable to value “prepost”. Since we’re on Linux, Dbvisit will then automatically assume the Pre/Post processing script is: $DBVISIT_BASE/standbymp/oracle/prepost.sh. It is not possible to use different path, but you can choose custom script name. The executable file extension will always be always added automatically according to the platform.
If we would be on Windows platform, then Dbvisit would assume “C:\Program Files\Dbvisit\standbymp\oracle\prepost.cmd” as action script for the same paramater value as shown: “prepost”
2.2 Creating Pre/Post Script
Pre/Post script is invoked internally by Dbvisit Oracle engine (dbvctl) before and after performing specific actions. The script is called with two parameters:
pre-post-dbvisit.sh [pre|post] [1|2|3|4|5|6]
The definition of how the script is called is internally built in Dbvisit code. It can’t be customized.
First parameter defines whether the script is called before (pre) or after (post) action.
The second parameter “action” is represented by numbers:
1= Archivelog transfer from primary
2= Archivelog apply on standby
3= Graceful Switchover execution on primary
4= Graceful Switchover execution on standby
5= Activation (Failover) of standby database
6= Standby Database is opened read-only
As another example, when Dbvisit will transfer an archivelog from primary database to standby, it will trigger Pre/Post processing two times and call the script like so:
pre-post-dbvisit.sh pre 1 # do the transfer internally pre-post-dbvisit.sh post 1
Therefore, the Pre/Post Script needs to be able to handle different parameters and values. In Linux bash script this is usually achieved by specifing CASEs and on Windows by IFs and GOTO commands.
Pre/Post processing always considers actual server roles. So for example execution “pre-post-dbvisit.sh post 3” will be always done on server whose role is PRIMARY AFTER successful switchover.
Example script for Linux:
Example script for Windows:
The example scripts only contain bare minimum of code. For your own sake, you should always add some basic logging and comments. You can check more complex scripts in the following examples.
If you define “exit 1” in your code, it is possible to terminate parent Dbvisit process with help of Pre/Post Script.
Because using Pre/Post Script can lead to potentially dangerous situations, we recommend to first try the implementation on test environment.
2.3 Deploying the Pre/Post Script
Last step is to copy your action script to primary and standby server. The script must be always in $DBVISIT_BASE/oracle (%DBVISIT_BASE%\oracle) and must have same name (which you defined in DBV_PRE_POST_PROC variable) on both servers.
There’s no built in check to verify if the script is the same on primary and standby. Your script can potentially have different content on primary and standby server. There’s also no error if the script is present only on one server.
We strongly recommend to have identical Pre/Post script on both servers. Make sure to double check this after the deployment.
3. Example Implementation of Pre/Post processing - Linux
In our first example we will implement additional precheck for switchover, where we want to terminate the switchover process if there are any application user sessions (non SYS sessions) connected to the primary database.
3.1 Configuration
We’re happy with default value DBV_PRE_POST_PROC = dbv_pre_post_processing so there’s no need for any DDC modification. So we can completely skip step “2.1 Prepare DDC Parameter” and we will only create script /usr/dbvisit/standbymp/oracle/dbv_pre_post_processing (as per 2.2 Creating Pre/Post Script) and upload it to /tmp:
Script contains handling for “pre” and action “3” meaning that our code will be triggered before graceful switchover actually begins on primary.
There’s no need to write CASE / handling for every Pre/Post Action combination, if they are not used.
Then we will copy it to primary and standby and make sure it’s executable (2.3 Deploying the Pre/Post Script):
cp /tmp/dbv_pre_post_processing.sh /usr/dbvisit/standbymp/oracle chmod +x /usr/dbvisit/standbymp/oracle/dbv_pre_post_processing.sh scp /usr/dbvisit/standbymp/oracle/dbv_pre_post_processing.sh czlin0232:/usr/dbvisit/standbymp/oracle
3.2 Testing
We will then create test user session and leave it open in the primary database:
sqlplus system/system@SLASH
Then we try to run switchover. The result should be that switchover fails:
Switchover fails, because our Pre/Post processing script found SYSTEM user session in the database and ended with “exit code 1” - dbvisit swicthover process will then interpret this as a failure, aborting the whole switchover process.
When we disconnect the SYSTEM session, the switchover is able to proceed.
4. Example Implementation of Pre/Post processing - Windows
In our second example we will implement functionality to start custom database service and register it with listener once standby database is opened read-only.
4.1 Configuration
Same as for the Linux example - we’re not going to change DBV_PRE_POST_PROC, we can skip step “2.1 Prepare DDC Parameter” and we will only create script dbv_pre_post_processing.cmd (as per 2.2 Creating Pre/Post Script) and upload it to both servers to C:\Program Files\Dbvisit\standbymp\oracle (2.3 Deploying the Pre/Post Script):
Note that the script assumes you have created service “reporting” in your primary database and synchronized via archivelogs this change to standby database, for example:
exec DBMS_SERVICE.create_service(service_name => 'reporting',network_name => 'reporting');
4.2 Testing
We open our standby database read only in command line and check if commands are indeed executed:
C:\dbvisit\app\oracle>dbvctl.exe -d TEST01 -o open ============================================================= Dbvisit Standby Database Technology (11.6.0) (pid 4800) dbvctl.exe started on DBVISIT-VIP12.DBVISIT.CZ: Fri Feb 9 14:44:28 2024 ============================================================= >>> Running pre-checks please wait... done Dbvisit Standby pre processing (C:\dbvisit\app\oracle\dbv_pre_post_processing.cmd starting Dbvisit Standby pre processing completed (C:\dbvisit\app\oracle\dbv_pre_post_processing.cmd) Shutting down Standby instance TEST01... Starting Standby instance TEST01 in READ ONLY mode... Dbvisit Standby post processing (C:\dbvisit\app\oracle\dbv_pre_post_processing.cmd starting Start post Processing SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 9 14:47:43 2024 Version 19.21.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.21.0.0.0 SQL> PL/SQL procedure successfully completed. SQL> Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.21.0.0.0 SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 9 14:47:44 2024 Version 19.21.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.21.0.0.0 SQL> System altered. SQL> Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.21.0.0.0 Dbvisit Standby post processing completed (C:\dbvisit\app\oracle\dbv_pre_post_processing.cmd) Standby instance TEST01 on DBVISIT-VIP12.DBVISIT.CZ opened in READ ONLY mode. Log files cannot be applied to Database while in READ ONLY mode. Database tempfile(s) may need to be added to this database. ============================================================= dbvctl.exe ended on DBVISIT-VIP12.DBVISIT.CZ: Fri Feb 9 14:47:44 2024 =============================================================
We can clearly see the post processing took place, so all is good and service is correctly started and registered with listener.