Logical Standby Databases

We use a logical standby database. The logical standby database is inherently fragile, because it mines the primary logs, and attempts to rebuild the SQL to apply on the logical.

This rebuilt SQL is fine most of the time, but if an application release has altered a table, or updated most of the rows in a large table, this generated SQL often performs very poorly.

Logical Standby Failure Modes

Typically there are two failure modes for the logical standby database. Either it will fall over because a SQL statement it has generate doesn’t work, or the generated SQL statement will perform really badly and take forever. The first is easy to deal with. Find the error message, fix the error, or ignore the SQL and continue. The second is more difficult, as there is no error message to investigate. Typically we will notice that the logical standby is getting a backlog of redo to apply. We need to investigate what is taking a long time.

The initial investigation of this issue is written in the Production Emergency post. You might want to read that first if you haven’t already.

Addressing the underlying problem

We had identified the source of the problem. The SQL text can be extracted from the database as follows:

1
2
3
4
5
6

set linesize 300
set pagesize 0
set long 30000
set longchunksize 300

select sql_text from v$sql where sql_id = '67bqun92ngrsj';

This displays the SQL text. If the application populates the module, program and client_id of v$session using dbms_application_info, then there is enough information to find out what code is causing the problem and even who was running the program that caused it.

Recently our PeopleSoft system locked up. Nobody could do anything, they just got a blank page in the browser.

The System Model

The approach to use in this situation is to consider how the application works. In our case a user’s web browser will connect to the load balancer, which will connect to a web server. The web server will pass the query to an available application server out of the pool, which will then pass the query to the database. Then the results go back up the chain.

GitLab Organization

It seemed reasonable when specifying the repositories, to have three (at first):

• One to hold the Ansible roles to build the VMs
• One to hold the custom code that the above repository will deploy
• One to define what the environments look like, things like
  • Memory
  • Number of VMs at each tier
  • The names of the VMs
  • Passwords
  • And so on.

Problem

The problem is that when the developer commits code to his repository, they would like it to be deployed to an environment so they can test it. It makes sense to do this automatically, we have all the information about the environment in the environment repository. This is different to the repository where the code is being checked in. It would be nice if we could call across to that and trigger the automated deploy.

Introduction

Copying large numbers of files around - you would have thought this would be easy using Ansible. Most of what we do with Ansible is copying files and editing them. It turns out to be rather difficult to get right.

Copy module

The copy module seems like the correct thing to use at first glance. It copies files from the build host to remote locations. So I can clone the repository of files to deploy and send them off to where they need to go? Brilliant! There is a note at the bottom

We had a process error with the above error message. I believe this is related to Oracle bug 27496360 which is a duplicate of 29942554, which is apparently in QA, but is targeted for database 20.1. It being 2019, Oracle haven’t released Oracle 20 yet, so we have no fix. We note it seems to be triggered by applying critical patches. We have an SR open for this which is attached to the bug.

The debugger requires application designer to be run in three tier mode, i.e. it should connect to the application server workstation listener rather than to the database itself.

We found that to achieve this we had to do the following:

On the application server, run psadmin, and administer the domain where the debugger is to be switched on. As of tools 8.57 this is done by choosing the following options from the menu. Note that the domain will be shut down once the configure option is chosen.

Secrets and the Problem with Build Servers

I am not totally sure I understand this, so let’s see if I do when I write it down.

I decided that gitlab needs to stop accessing hosts using keys from the gitlab runner, because this means:

1. Everything on gitlab runner can access every VM
2. If the gitlab runner moves, nothing can access any VM

So 1 is too open, and 2 just doesn’t work. While I could configure a gitlab runner per environment, it also strikes me that the secrets are in the ansible build directory and shouldn’t be. We need a better approach.