DataGuard


Can ChatGPT steal our (Oracle DBA) job?

I would not say it will steal our job, but it can facilitate in some cases to write small pieces of code without having much to think. Well, help first to be lazy, before getting our job. Last example today:

Request ChatGPT to write a trigger that enables BCT after the database opens.

The power is amazing. After looking at the result, you think about a small detail, ask again and it just answers:

Ask ChatGPT to improve so it triggers only when a switchover happened.

But one must always test carefully the answers. Like this case, where the EVENT_TYPE column does not exist on V$DATAGUARD_STATUS table. No idea how he discovers it. I also got non existing syntax and complex solutions that could be easily made simpler.


gDBClone – Good and easy Oracle cloning tool with potential

Instead of writing and adapting cloning scripts for each client, I was looked today at gDBClone script, provided by Oracle note gDBClone Powerful Database Clone/Snapshot Management Tool (Doc ID 2099214.1).

Ruggero Citton did an excellent work and I believe that for most of situations and mainly in a development environment with the need of fast DB Snapshots this tool provides an alternative to developing new tools.

I find great that for database cloning it creates a temporary listener, and does not mixup with existing configuration. Another great thing is the possibility to use a pre-created passwordfile to clone remote DBs, without the need to provide any password at run time (or hardcode, or having a wallet).

It is a tool that works with DBs from Oracle 11.2 up, and also with RAC, RAC One node, it can perform upgrades, create standby, it works in ODA, etc.

The long set of pre-checks is very nice:

MacroStep1 - Getting information and validating setup...
INFO: 2022-01-13 15:40:09: Validating environment
INFO: 2022-01-13 15:40:09: Checking superuser usage
INFO: 2022-01-13 15:40:09: Checking if target database name 'xpto' is a valid name
INFO: 2022-01-13 15:40:09: Checking if target database home 'OraHome3' exists
INFO: 2022-01-13 15:40:09: Checking if Oracle Restart
INFO: 2022-01-13 15:40:09: Checking ping to host 'server27'
INFO: 2022-01-13 15:40:09: Getting ORACLE_BASE path from orabase
INFO: 2022-01-13 15:40:09: Checking if target database 'xpto' exists
INFO: 2022-01-13 15:40:09: Checking registered instance 'xpto'
INFO: 2022-01-13 15:40:12: Checking listener on 'server27:1521'
INFO: 2022-01-13 15:40:12: Checking ASM command options
INFO: 2022-01-13 15:40:15: Checking if '+U02' is a valid ASM diskgroup
INFO: 2022-01-13 15:40:15: Checking '+U02' RDBMS compatible
INFO: 2022-01-13 15:40:16: Checking if '+U01' is a valid ASM diskgroup
INFO: 2022-01-13 15:40:16: Checking '+U01' RDBMS compatible
INFO: 2022-01-13 15:40:16: Checking if '+U01' is a valid ASM diskgroup
INFO: 2022-01-13 15:40:17: Checking '+U01' RDBMS compatible
INFO: 2022-01-13 15:40:20: Checking source and target database version
INFO: 2022-01-13 15:40:21: Checking source database size
INFO: 2022-01-13 15:40:23: Checking source database role
INFO: 2022-01-13 15:40:23: Checking source log mode
INFO: 2022-01-13 15:40:24: Checking Flash Cache setting
SUCCESS: 2022-01-13 15:40:24: Environment validation complete

At the client I’m these days however I would be happy with some more flexibility.

  • The database unique name convention include underscores (xptodb_2 for instance). gDBClone only accepts alphanumeric elements to the clone database name.
  • gDBClone needs to run with root privileges. You can (recommended) configure in /etc/sudoers, however this is far too much for my client.
  • Cloning a dataguard protected database configured with broker failed at the end of the duplicate, as the clone automatically started the broker and connected back to the primary, failing then with ORA-16649: possible failover to another database prevents this database from being opened . The half-baked clone needs to be dropped manually.
  • It would be nice to have commands (or be part of clone) to create new ACFS mountpoints

At the end I get the impression that gDBClone was developed for specific customer needs and I’m sure it does it really well. For the needs and restrictions I’ve in different clients unfortunately I still need to use my set of tools. Luckily with the most recent Oracle versions the cloning, snapping, upgrades has become much easier and scripting it does not require many lines of code.


DGMGRL error: Object “a1021p_site1” was not found

While testing some scripts, I was getting an error on DGMGRL, while querying some databases:

DGMGRL> show database A1021P_SITE1
Object "a1021p_site1" was not found

But it would work if I put single quotes around:

DGMGRL> show database 'A1021P_SITE1'

Database - A1021P_SITE1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    A1021P

Database Status:
SUCCESS

The problem is on the way the database was added to the configuration, and it is quite simple to fix it…

  1. after disabling configuration
  2. and connecting with password to the database
  3. then just rename the database to a name without using quotes.
DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
Error: ORA-16602: database must be disabled to perform this operation
Failed.

DGMGRL> disable fast_start failover;
Disabled.

DGMGRL> disable configuration
Disabled.

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
ORA-16541: database is not enabled
Configuration details cannot be determined by DGMGRL

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
ORA-16541: database is not enabled
Configuration details cannot be determined by DGMGRL

DGMGRL> connect sys/XXXX@a1021p_site1
Connected.

DGMGRL> edit database 'A1021P_SITE1' rename to a1021p_site1;
Succeeded.

DGMGRL> show database A1021P_SITE1

Database - a1021p_site1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    A1021P

Database Status:
DISABLED

DGMGRL> enable configuration;
Enabled.

DGMGRL> enable fast_start failover
Enabled.

DGMGRL> show configuration

Configuration - A1021P

  Protection Mode: MaxAvailability
  Databases:
    a1021p_site1 - Primary database
    a1021p_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

Dataguard – convert from MaxPerformance to MaxAvailability

We just found that one of the setups was wrongly configured as MaxPerformance. Since Oracle 11.2 it is possible to increase the protection mode without restarting the primary (if going from MaxPerformance to MaxProtection you need to do in two steps, through MaxPerformance).

Here how to move from MaxPerformance to MaxAvailability in a config with DataGuard Broker (removed some lines to make it shorter):

DGMGRL> connect /
Connected.

DGMGRL> show configuration

Configuration - azores

  Protection Mode: MaxPerformance
  Databases:
    azores_site1 - Primary database
    azores_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Error: ORA-16654: fast-start failover is enabled

Failed.

DGMGRL> stop observer
Done.
DGMGRL> show configuration

Configuration - azores

  Protection Mode: MaxPerformance
  Databases:
    azores_site1 - Primary database
      Warning: ORA-16819: fast-start failover observer not started

    azores_site2 - (*) Physical standby database
      Warning: ORA-16819: fast-start failover observer not started

Fast-Start Failover: ENABLED

Configuration Status:
WARNING

DGMGRL>  DISABLE FAST_START FAILOVER
Disabled.
DGMGRL> show configuration

Configuration - azores

  Protection Mode: MaxPerformance
  Databases:
    azores_site1 - Primary database
    azores_site2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.

DGMGRL> show database verbose azores_site1

Database - azores_site1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    azores

  Properties:
    DGConnectIdentifier             = 'azores_site1.portugal'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'async'
	...
	
Database Status:
SUCCESS

DGMGRL> show database verbose azores_site2

Database - azores_site2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 2 seconds ago)
  Apply Lag:       0 seconds (computed 2 seconds ago)
  Apply Rate:      1.84 MByte/s
  Real Time Query: OFF
  Instance(s):
    azores

  Properties:
    DGConnectIdentifier             = 'azores_site2.portugal'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'async'
	...
	
Database Status:
SUCCESS

DGMGRL> edit database azores_site2 set state=APPLY-OFF;
Succeeded.
DGMGRL> edit database azores_site2 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database azores_site1 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
DGMGRL> show configuration;

Configuration - azores

  Protection Mode: MaxAvailability
  Databases:
    azores_site1 - Primary database
    azores_site2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable fast_start failover;
Enabled.

DGMGRL> edit database azores_site2 set state=APPLY-ON;
Succeeded.

DGMGRL> show database verbose azores_site2

Database - azores_site2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - azores

  Protection Mode: MaxAvailability
  Databases:
    azores_site1 - Primary database
    azores_site2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

Query DGMGRL silently from observer site

When the observer is “checking” several dataguard configurations, we can use this server to easily make queries on all of them, for instance using:

for db in `ps -ef | grep observer | awk -F '/' '{print $6}' | sort`; do
conn=`sed -n 1p /u00/app/oracle/admin/${db}/observer/fsfo_${db}.conf`
conndb=`echo $conn | cut -d "@" -f2`
dgmgrl -silent ${conn} "show configuration" | grep -E 'Primary'
done;

It does for every observed configuration:

  1. gets the DB name
  2. find the connection string in the config file
  3. connects to DGMGRL in silent more and runs a command

ORA-17628: Oracle error 19505 returned by remote Oracle server

Today I was doing a procedure for duplicating databases at a client environment. One error that pop up time to time was the one below:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/13/2014 18:49:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on t1 channel at 05/13/2014 18:49:00
ORA-17628: Oracle error 19505 returned by remote Oracle server

At several websites you can see different causes:

– Control file location specification was wrong;

– db_file_name_convert were not specified;

– FRA diskgroup inexisting in the target;

– etc.

I had the following two cases on my experiments:

– destination database directory not existing on the DGDATA diskgroup (+DGDATA/<db_unique_name>)

– I did not connect to auxiliary using a standard network naming (I was using ezconnect).


Duplicate problem with non standard cache sizes

Today I had a problem creating a standby database. Just after RMAN shutdowns and restarts the standby for copying the files, I was getting:

RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-04031: unable to allocate 4128 bytes of sha
red memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”object queue ha
sh buckets”)

After investigating the problem was because I was decreasing the size of SGA on the target database (and putting it on Automatic Memory Management) but not decreasing the non-standard cache settings.
The solution was to add to the duplicate command the decrease of the non-standard cache (on the source it is 800M):
  SET memory_target ‘1G’
  SET db_32k_cache_size ’50M’