The journey of Junior DBA support

Monday, 24 January 2022

Failed to start runInstaller on Oracle Linux 8.5 - [INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'

Failed using runInstaller to install 19c Oracle Home on Oracle Linux 8.5 with below message.

[INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'

Please set "export CV_ASSUME_DISTID=OEL7.6" before start runInstaller

shell> export CV_ASSUME_DISTID=OEL7.6

shell> ./runInstaller

Thursday, 2 July 2020

How to config and enable MySQL Enterprise Audit in MySQL

Here is the steps to show you how to config and enable the MySQL Enterprise Audit in MySQL.

A. Config and enable the MySQL Enterprise Audit in MySQL.

Step 1: Append the following lines in config file

plugin-load-add=authentication_pam.so
plugin-load-add=audit_log.so
audit_log_flush=on
audit_log_rotate_on_size=200MB
#Example of audit log name is audit_host01_3306.log
audit_log_file=/db_log/audit/audit_host01_3306.log 
#Log connection error only
audit_log_connection_policy=ERRORS

Step 2: Check plugin status

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'audit%';

Step 3: Create audit function and tables

USE mysql;
CREATE TABLE IF NOT EXISTS audit_log_filter(NAME VARCHAR(64) BINARY NOT NULL PRIMARY KEY, 
FILTER JSON NOT NULL) engine= InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;

CREATE TABLE IF NOT EXISTS audit_log_user(USER VARCHAR(32) BINARY NOT NULL, 
HOST VARCHAR(60) BINARY NOT NULL, FILTERNAME VARCHAR(64) BINARY NOT NULL, 
PRIMARY KEY (USER, HOST), FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME)) 
engine= InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci;
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_set_filter RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_remove_filter RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_set_user RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_remove_user RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_filter_flush RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_read_bookmark RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_read RETURNS STRING SONAME 'audit_log.so';
CREATE FUNCTION audit_log_encryption_password_set RETURNS INTEGER SONAME 'audit_log.so';
CREATE FUNCTION audit_log_encryption_password_get RETURNS STRING SONAME 'audit_log.so';
SELECT audit_log_filter_flush() AS 'Result';

Step 4: Rotate the log file when the audit log reached 200MB

SET GLOBAL audit_log_rotate_on_size = 1024*1024*1024;

B. Create the log filter and assigned it to specified user.

Reference:

https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/audit-log-filter-definitions.html#audit-log-general-event-fields

Step 5: Create the log filter for logging specified DDL

set @log_ddl_filter='
{
"filter": {
  "class": {
    "name": "general",
    "event": {
      "name": "status",
      "log": {
        "and": [
        {
           "or": [
           {"field": { "name": "general_command.str", "value": "Query" }},
           {"field": { "name": "general_command.str", "value": "Execute" }}
           ]
        },
       {
          "or": [
          {"field": { "name": "general_sql_command.str", "value": "alter_db" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_db_upgrade" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_event" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_function" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_instance" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_procedure" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_server" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_table" }},
          {"field": { "name": "general_sql_command.str", "value": "alter_tablespace" }},
          {"field": { "name": "general_sql_command.str", "value": "create_db" }},
          {"field": { "name": "general_sql_command.str", "value": "create_event" }},
          {"field": { "name": "general_sql_command.str", "value": "create_function" }},
          {"field": { "name": "general_sql_command.str", "value": "create_index" }},
          {"field": { "name": "general_sql_command.str", "value": "create_procedure" }},
          {"field": { "name": "general_sql_command.str", "value": "create_server" }},
          {"field": { "name": "general_sql_command.str", "value": "create_table" }},
          {"field": { "name": "general_sql_command.str", "value": "create_trigger" }},
          {"field": { "name": "general_sql_command.str", "value": "create_udf" }},
          {"field": { "name": "general_sql_command.str", "value": "create_view" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_db" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_event" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_function" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_index" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_procedure" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_server" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_table" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_trigger" }},
          {"field": { "name": "general_sql_command.str", "value": "drop_view" }},
          {"field": { "name": "general_sql_command.str", "value": "rename_table" }}
          ]
       }
    ]
    }
   }
  }
 }
}';

Step 6: Apply the filter for all DB users

Verify the filter is a valid JSON object:

use mysql;
select json_valid(@log_ddl_filter);

Remove any previous version of the filter:

select audit_log_filter_remove_filter('log_specified_ddl');

Set the new filter:

select audit_log_filter_set_filter('log_specified_ddl',@log_ddl_filter);

Specify which user(s) the filter should apply to, e.g. apply the filter to all user.

Remark: The filter can be assigned to any user account if the user has no assigned filter.

select audit_log_filter_set_user('%','log_specified_ddl');

Enquiry the created audit filter

select * from audit_log_filter where name = 'log_specified_ddl' \G

Enquiry the audit user account that has assigned filter.

select * from audit_log_user;

Wednesday, 1 July 2020

Automate MongoDB daily compress backup with email notification via crontab

Automate taken full compress daily backup and housekeep the backup copied for MongoDB with email notification via crontab.

By separation of duty, I suggest to create the user for backup and restoration on MongoDB.

1. Create the user if not created

shell> mongo
> use admin;
> db.createUser(
        { user: "<username>",
           pwd: "<password>",
           roles: [
                     { role: "backup" },
                     { role: "restore" }
           ]
         }
   )

Enquiry the user after creation.

> use admin;
> db.getUser("<username>")

2. Create the backup script:

Remark: The below script will housekeep the backup copy more than one day.

shell> vi backup_onlinefull_compress_mongodb.sh
## Declare variables
vstartdate=`date +%d-%m-%y_%H:%M`
vdate=`date +%d%m_%H%M`
vhost=$1
vmongodbport=$2
vbackuppathlog=/backup/$vmongodbport/mongod
vbackuppath=/backup/$vmongodbport/mongod
log=$(echo $vdate"_"$vhost"_"$vmongodbport"_full_backup_compress.log")
## Check if the user has input enough parameter
if [ ! $# -eq 2 ]; then
        echo ""
        echo "This is the operation script for Backup MongoDB Instance."
        echo "Error: Wrong Input!"
        echo ""
        echo "Example: ./backup_onlinefull_compress_mongodb.sh host01 27017"
        exit 1;
fi

#password file
pwd=$(cat passwordfile)

log_file=$vbackuppathlog/$log


echo $vstartdate
echo $vhost
echo $config_file
echo $socket_file
echo $log_file
find $vbackuppathlog/*$vmongodbport*.log -type f -mtime +2 -exec rm {} +
find $vbackuppath -mindepth 1 -maxdepth 1 -type d -mtime +0 -exec rm -r {} +
vbackupdir=$vbackuppath/`date +%y%m%d_%H%M`
mkdir -p $vbackupdir
echo "Host: " $vhost >> $log_file
echo "Date: " $vstartdate>> $log_file
echo "mongodb Port: " $vmongodbport >> $log_file
mongodump --archive="$vbackupdir/mongodump_backup_$vmongodbport_`date +%d%m_%H%M`.gz" 
--oplog  --gzip  -u <username> -p$pwd --authenticationDatabase=admin 
--port=$vmongodbport &>> $log_file

cp $log_file $vbackupdir
# Set mailing list and email content
MAILLIST="user01@dummy.com" 
HOSTNAME=$(hostname)
BODY=$vbackuppathlog/body.txt
echo "Host: " $vhost >> $BODY
echo "Date: " $vstartdate>> $BODY
echo "mongodb Port: " $vmongodbport >> $BODY
# Send Email - MongoDB Backup Report
    MAILSUB="[$HOSTNAME Port: $vmongodbport] - MongoDB Backup Report ($vstartdate)"
    mailx -a $log_file -s "$MAILSUB" "$MAILLIST" < $BODY
    rm $BODY

3. setup cron job

Example: start the backup job at 01:00 everyday, the backup script is located at $HOME.

shell> crontab -e
## Daily backup for MongoDB
0 1 * * * ($HOME/backup_onlinefull_compress_mongodb.sh host01 27017 > $HOME/run_backup_onlinefull_compress_mongodb.log)

Tuesday, 31 March 2020

Add online redo log group in standby database

How to add online redo log group in standby database

1. Change the value of standby database state to "APPLY-OFF" in dgmgrl

DGMGRL> show database verbose testdgbdr;

Database - testdgbdr

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

DGMGRL> edit database testdgbdr set state='APPLY-OFF';

DGMGRL> show database verbose testdgbdr;

Database - testdgbdr

Role: PHYSICAL STANDBY

Intended State: APPLY-OFF

2. Add online redo log group in standby database

SQL> select db_unique_name from v$database

DB_UNIQUE_NAME

------------------------------

testdgbdr

SQL> select group#, bytes/1024/1024 MB from v$log;

GROUP# MB

---------- ----------

1 50

2 50

3 50

SQL> alter database add logfile group 4 ('/u01/app/redo/testdgb/redo04.log') SIZE 50M;

Database altered.

SQL> select group#, bytes/1024/1024 MB from v$log;

GROUP# MB

---------- ----------

1 50

2 50

3 50

4 50

3. Change the value of standby database state to "APPLY-ON" in dgmgrl

DGMGRL> show database verbose testdgbdr;

Database - testdgbdr

Role: PHYSICAL STANDBY

Intended State: APPLY-OFF

DGMGRL> edit database testdgbdr set state='APPLY-ON';

DGMGRL> show database verbose testdgbdr;

Database - testdgbdr

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Show configuration with warning ORA-16792: configurable property value is inconsistent with database setting

Here is the example how to resolve the warning with ORA-16792: configurable property value is inconsistent with database setting in dgmgrl.

DGMGRL> show configuration;

Configuration - testdgb_dg_config

Protection Mode: MaxAvailability
Members:
testdgb - Primary database
testdgbdr - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING (status updated 32 seconds ago)

DGMGRL> show database testdgbdr 'InconsistentProperties';

INCONSISTENT PROPERTIES

INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

testdgb StandbyFileManagement MANUAL MANUAL AUTO

The broker value of "StandbyFileMangement" is inconsistent with database spfile and memory value, so we need to align the broker value "StandbyFileMangement" with database config.

DGMGRL> edit database testdgbdr set property StandbyFileManagement='MANUAL';

Property "standbyfilemanagement" updated

Check again the database DG broker inconsistent property after updated the value.

DGMGRL> show database testdgbdr 'InconsistentProperties';

INCONSISTENT PROPERTIES

INSTANCE_NAME   PROPERTY_NAME          MEMORY_VALUE   SPFILE_VALUE    BROKER_VALUE 

The warning ORA-16792 has been resolved after amended the value in database property. 

DGMGRL> show configuration;

Configuration - testdgb_dg_config

  Protection Mode: MaxAvailability

  Members:

  testdgb      - Primary database

    testdgbdr - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 41 seconds ago)

Friday, 16 August 2019

Restoring the failed member in MySQL InnoDB cluster with MySQL Enterprise Backup

Using MySQL Enterprise Backup to restore and rebuild the failed member in secondary node

Testing Environment:

Linux OS: CentOS 7.4

MySQL Server Version: 8.0

MySQL Enterprise Backup 8.0

MySQL Enterprise Transparent Encryption Encryption

MySQL InnoDB cluster 8.0

Host: primary01 as primary node

Host: second02 as secondary node

A. Pre-Task:

1. Taking Full-backup with Compression on secondary using MySQL Enterprise Backup

mysqlbackup --defaults-file=my.cnf --backup_image=/backup/mysqld/my.mbi_`date +%d%m_%H%M` --backup-dir=/backup/mysqld/backup_`date +%d%m_%H%M` --user=mysbackup -p --host=second02 --port=3306 --encrypt-password= --compress --compress-level=5 backup-to-image &>> `date +%d%m_%H%M`_full_compress_tde_mysqld_backup.log

2. check backup log with "completed" and please ignore the warning since the backup was taken on secondary node.

ySQL Enterprise Backup Ver 8.0.15-commercial for linux-glibc2.12 on x86_64 (MySQL Enterprise - Commercial)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

190816 16:56:26 MAIN INFO: A thread created with Id '140676524181376'

190816 16:56:26 MAIN INFO: Starting with following command line ...

mysqlbackup

--defaults-file=my.cnf

--backup_image=/backup/mysqld/my.mbi_1608_1656

--backup-dir=/backup/mysqld/backup_1608_1656

--user=mysbackup -pxxxxxxxxxxxx --host=second02 --port=3306

--encrypt-password=xxxxxxxxxx --compress --compress-level=5

backup-to-image

190816 16:56:26 MAIN INFO:

190816 16:56:26 MAIN INFO: No SSL options specified.

190816 16:56:26 MAIN INFO: MySQL server version is '8.0.15-commercial'

190816 16:56:26 MAIN INFO: MySQL server compile os version is 'linux-glibc2.12'

190816 16:56:26 MAIN INFO: SSL/TLS version used for connection is TLSv1.2

190816 16:56:26 MAIN WARNING: This backup operation cannot write to backup progress. The MySQL server is running with the --super-read-only option.

....

mysqlbackup completed OK! with 1 warnings

B. Task: Restore the backup on secondary node

1.Stop MySQL server

mysql -u mysql -p password -hsecond02 -P3306 -e "shutdown"

2. remove all data files under mysql database data directory

rm -rf /data/mysqld

mkdir -p /data/mysqld

3. remove all log files and the keyring files

rm -rf /data/mysql-keyring

mkdir -p /data/mysql-keyring

rm -rf /data/log

mkdir -p /data/log

4. create restore directory

mkdir -p /backup/mysqld/restore

5. restore backup

mysqlbackup --defaults-file=/my.cnf --backup-image=/backup/mysqld/ --backup-dir=/backup/mysqld/restore --datadir=/data/mysqld --user=mysbackup -p password --host=second02 --port=3306 --encrypt-password= encryption_password --uncompress copy-back-and-apply-log &>> `date +%d%m_%H%M`_full_compress_tde_mysqld_restore.log

C. Post-Task: Re-join as a New Member in MySQL InnoDB cluster

1. Start restored MySQL Server

mysqld_safe --defaults-file=my.cnf &

2. Reset group replication member status

mysql -u mysql -p password -hsecond02 -P3306

mysql> RESET MASTER;

mysql> RESET SLAVE ALL;

*Remark: please follow the below steps if enabled auto recover using group replication's built-in mechanism with the variable "gtid_executed"

//start

mysql> SET sql_log_bin=off;

mysql> source /data/mysqld/backup_gtid_executed.sql

mysql> SET sql_log_bin=on;

//end

3. Re-join the restored server in MySQL InnoDB cluster

mysqlsh --uri=mysql:password @primary01:3306

MySQL primary01:3306 ssl JS > var x = dba.getCluster();

MySQL primary01:3306 ssl JS > x.rescan();

Rescanning the cluster...

Result of the rescanning operation for the 'default' ReplicaSet:

{

"name": "default",

"newTopologyMode": null,

"newlyDiscoveredInstances": [],

"unavailableInstances": [

{

"host": "second02:3306",

"label": "second02:3306",

"member_id": "4e1eff7d-bf22-11e9-93f4-080027ea2dcf"

}

]

}

The instance 'second02:3306' is no longer part of the ReplicaSet.

The instance is either offline or left the HA group. You can try to add it to the cluster again with the cluster.rejoinInstance('second02:3306') command or you can remove it from the cluster configuration.

Would you like to remove it from the cluster metadata? [Y/n]: y

Removing instance from the cluster metadata...

The instance 'second02:3306' was successfully removed from the cluster metadata.

MySQL primary01:3306 ssl JS > dba.checkInstanceConfiguration('group_replication_user:group_replication_password@second02:3306');

MySQL primary01:3306 ssl JS > dba.configureInstance('mysadm:mysadm43d@second02:3306', {clusterAdmin:'group_replication_user',clusterAdminPassword:'group_replication_password'});

dba.checkInstanceConfiguration('group_replication_user:group_replication_password@second02:3306');

MySQL primary01:3306 ssl JS > x.status();

MySQL primary01:3306 ssl JS >x.addInstance('group_replication_user:group_replication_password>@second02:3306');

MySQL primary01:3306 ssl JS > x.status();

{

"clusterName": "CC_CLU01",

"defaultReplicaSet": {

"name": "default",

"primary": "primary01:3306",

"ssl": "REQUIRED",

"status": "OK_NO_TOLERANCE",

"statusText": "Cluster is NOT tolerant to any failures.",

"topology": {

"primary01:3310": {

"address": "primary01:3306",

"mode": "R/W",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

"second02:3310": {

"address": "second02:3306",

"mode": "R/O",

"readReplicas": {},

"role": "HA",

"status": "ONLINE"

}

"topologyMode": "Single-Primary"

"groupInformationSourceMember": "primary01:3306"

}

3. check member status after re-join the restored server in MySQL InnoDB cluster

mysql -u mysql -p password -hsecond02 -P3306

mysql> SELECT member_host, member_port, member_state FROM performance_schema.replication_group_members;

+-------------+-------------+--------------+

| member_host | member_port | member_state |

+-------------+-------------+--------------+

| primary01 | 3306 | ONLINE |

| second02 | 3306 | ONLINE |

+-------------+-------------+--------------+

2 rows in set (0.00 sec)

mysql>

Monday, 18 March 2019

Resolve ORA-01034 and ORA-27101

Got error message "ORA-01034" & "ORA-27101" when connect the database with "@", this issue may caused by the listener service is not started well.

I tried to stop and start again the listener, the issue has been resolved.

1. Please check ORACLE_SID

2. Please check listener.ora configuration

3. Please try to restart the listener service

Here is the example:
Got error message when connect to the database with TNS name "@"

[oracle@primary01 ~]$ sqlplus 'sysbackup/abc123@testdgbdr as sysbackup'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 18 18:09:19 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "SYSBACKUP"
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

SQL>exit

Try to startup the DB instance as sysdba
[oracle@primary01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 18 18:10:24 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2999888 bytes
Variable Size 1207962032 bytes
Database Buffers 872415232 bytes
Redo Buffers 13774848 bytes
Database mounted.
Database opened.
SQL> show parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_testdgbdr
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Check the listener service status
[oracle@primary01 ~]$ lsnrctl status p01lsnr1521

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-MAR-2019 18:01:15

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01)(PORT=1528)))
STATUS of the LISTENER
------------------------
Alias p01lsnr1521
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 19-JAN-2019 16:59:55
Uptime 28 days 0 hr. 3 min. 20 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/primary01/p01lsnr1521/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1528)))
Services Summary...
Service "testdgbdr" has 2 instance(s).
Instance "testdgbdr", status UNKNOWN, has 1 handler(s) for this service...
Instance "testdgbdr", status READY, has 1 handler(s) for this service...
Service "testdgbdrXDB" has 1 instance(s).
Instance "testdgbdr", status READY, has 1 handler(s) for this service...

Try to stop the listener service

[oracle@primary01 ~]$ lsnrctl stop p01lsnr1521

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-MAR-2019 18:17:49

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01)(PORT=1521)))
The command completed successfully

Start the listener again.

[oracle@primary01 ~]$ lsnrctl start p01lsnr1521
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-MAR-2019 18:01:15

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01)(PORT=1528)))
STATUS of the LISTENER
------------------------
Alias p01lsnr1521
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 18-MAR-2019 18:17:55
Uptime 28 days 0 hr. 3 min. 20 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/primary01/p01lsnr1521/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1528)))
Services Summary...
Service "testdgbdr" has 2 instance(s).
Instance "testdgbdr", status UNKNOWN, has 1 handler(s) for this service...
Instance "testdgbdr", status READY, has 1 handler(s) for this service...
Service "testdgbdrXDB" has 1 instance(s).
Instance "testdgbdr", status READY, has 1 handler(s) for this service...

Try to connect to the database with @, to stop and start the DB instance.
The issue has been resolved after restarted the listener, to stop the database instance without error ORA-01034 & ORA-27101

[oracle@primary01 ~]$ sqlplus 'sysbackup/abc123@testdgbdr as sysbackup'
The command completed successfully
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 18 18:19:59 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2999888 bytes
Variable Size 1207962032 bytes
Database Buffers 872415232 bytes
Redo Buffers 13774848 bytes
Database mounted.
Database opened.
SQL> exit