Thursday, September 3, 2020

Potential solution to dgmgrl error ORA-16665: time out waiting for the result from a member

After having added a terminal standby database to an existing configuration, the Data Guard Broker configuration seemed unhappy with communicating with the new member. The output from "show configuration" showed the following:
DGMGRL> show configuration;

Configuration - DB01

  Protection Mode: MaxPerformance
  Members:
  DB01      - Primary database
    DB01_STB  - Physical standby database
      DB01_TSTB - Physical standby database (receiving current redo)
        Error: ORA-16665: time out waiting for the result from a member

    DB01_RO   - Physical standby database
When looking at the details by using
show database verbose "DB01_TSTB"
the entire operation would take very long, and at the, the following message is displayed:
Database Status:
DGM-17016: failed to retrieve status for database "DB01_TSTB"
ORA-16665: time out waiting for the result from a member
The broker log file showed:
09/02/2020 15:08:52
Data Guard Broker Status Summary:
  Type                        Name                            Severity  Status
  Configuration               DB01                            Warning  ORA-16607
  Primary Database            DB01                            Success  ORA-0
  Physical Standby Database   DB01_STB                        Success  ORA-0
  Physical Standby Database   DB01_RO                         Success  ORA-0
  Physical Standby Database   DB01_TSTB                       Error  ORA-16665
Root cause here was firewalls. The terminal standby database could not reach the primary database. Although the terminal standby database isn't set up to receive redo data from the primary database directly, in a broker configuration all members must be able to communicate with eachother. A good tool for troubleshooting issues dealing with ports and firewalls is nmap. I installed it on the terminal server and issued:
[root@db04_server ~]# nmap -n -p 1511 db01_sever.oric.no

Starting Nmap 6.40 ( http://nmap.org ) at 2020-09-02 14:23 CEST
Nmap scan report for db01_sever.oric.no (xxx.xxx.xxx.xxx)
Host is up (0.016s latency).
PORT     STATE    SERVICE
1511/tcp filtered 3l-l1

Nmap done: 1 IP address (1 host up) scanned in 0.49 seconds
A filtered port means that it is not possible to determine whether the port is open or closed, most often due to firewalls along the way. Further checks in the firewall log files showed
action=Drop service=1511 dst=xxx.xxx.xxx.xxx scr=yyy.yyy.yyy.yyy
where xxx.xxx.xxx.xxx was matching the ipadress of the terminal standby server, while yyy.yyy.yyy.yyy was matching the ipadress of the primary server. The network admin opened the port, and the ORA-16665 immediately disappeared from the dgmgrl output.

Monday, August 31, 2020

How to zip files in the current directory

In its simplest form, to zip a number of similary named files, use this syntax:
zip client_sql_trace client*.trc

where the resulting zip file will be called "client_sql_trace.zip" and contain all files in the current directory named client_.trc.

Tuesday, August 18, 2020

How to check the password file version

oracle@oric01.oric.no:[PRODDB01]# orapwd describe file=/u01/oracle/product/12201/dbs/orapwPRODDB01
Password file Description : format=LEGACY
If you try to create the password file with a password that is too simple, you may see this error from orapwd:
OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters
The format is set to 12.2 by default, and from the orapwd help text there doesn't seem to be anything lower than this.
If you for some reason need to keep an old password which is too simple for today's default settings, you can create a "legacy" password by using the format "legacy". This is not documented in the help text:
orapwd file=u01/oracle/product/12201/dbs/orapwPRODDB01 format=legacy force=Y

Enter password for SYS:

Thursday, August 13, 2020

How to perform a full export and import of an entire database in PostgreSQL


Export the database "db01" from your source postgres server:

pg_dump db01 -v -Fc >db01.dmp

The -v means "verbose" and -Fc means "custom format" (as opposed to "directory format", which would be indicated by -Fd)

Import the database "db01" in your destination postgres server

First, transfer the directory db01.dmp file from the source server to the target server using scp.
pg_restore -v -d db01 -1 db01.dmp
The -1 option means "all or nothing"; either the entire operation succeed, or nothing is done.

If the database doesn't exist, you need to connect to another database in the cluster first:
pg_restore -v -C -d postgres /exports/full_db_export.dmp

Export the database "db01" using the directory format:

pg_dump -Fd db01 -f /exports/full_db01 -v

This will create the directory /exports/full_db01 which contains all the files necessary for restore. The -v means "verbose" and -Fd means "directory format"

Import the database "db01":


Again, transfer the needed files, this time the entire directory /exports/full_db01 from the source server to the target server using scp.
Then import the database, either by recreating the database indicated by the -C option:
pg_restore /exports/full_db01 -C -c -d postgres -j 2 -v

or by importing the objects into an existing database, in my case "db02":
pg_restore /exports/full_db01 -c -d db02 -j 2 -v

  • The -C option means "create database".
  • The -d postgres option indicates the database to which the pg_dump utility makes an initial connection
  • The -c option means "drop the database objects before creating"
  • -j is the number of jobs started in parallel

    The official documentation for the pg_dump utility can be found here
    here
  • Monday, August 10, 2020

    what is the difference between DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL?

    The difference between these views is that DBA_COMMON_AUDIT_TRAIL contains information from both standard and fine-grained auditing, while DBA_AUDIT_TRAIL only contains information from standard auditing.

    DBA_AUDIT_TRAIL displays all standard audit trail entries. 
    This view is populated only in an Oracle Database where unified auditing is not enabled
    The SQL_BIND and SQL_TEXT columns are only populated if the AUDIT_TRAIL initialization parameter is set to 'db, extended'



    • All standard audit trail entries 
    • Fine-grained audit trail entries 
    • Mandatory audit trail entries 
    • SYS audit records written in XML format 

    The SQL_BIND and SQL_TEXT columns are only populated if the AUDIT_TRAIL initialization parameter is set to 'db, extended' or 'xml, extended' or if the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE.

    How to set parallelism for a data guard recovery process

    If you need to manually set the apply-process DOP (Degree of Parallelism), you can do this by setting the property ApplyParallel=num where num is the number of parallel processes. 

    In my setup, I have three databases: 

    •  A primary database (hdal) 
    • A physical standby database (hdal_stb) which serves as a "cascading" database 
    • A terminal standby database (hdal_tstb) which receives redo from the cascading standby database  
    Logged in on any one of the participating servers of your data guard configuration, start dgmgrl: 
    dgmgrl / as sysdba
    Connected to "hdal_stb"
    Connected as SYSDBA.
    DGMGRL> show configuration;
    
    Configuration - DGConfig1
    
      Protection Mode: MaxPerformance
      Members:
      hdal      - Primary database
        hdal_stb  - Physical standby database
          hdal_tstb - Physical standby database (receiving current redo)
    
    DGMGRL> edit database 'hdal_stb' set property ApplyParallel=4;
    
    In the database's alert log, we can se that Oracle is cancelling the ongoing recovery process:
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
    2020-08-10T09:26:24.249847+02:00
    MRP0: Background Media Recovery cancelled with status 16037
    Managed Standby Recovery not using Real Time Apply
    Recovery interrupted!
    Recovered data files to a consistent state at change 27754456
    2020-08-10T09:26:24.510262+02:00
    ORA-16037: user requested cancel of managed recovery operation
    2020-08-10T09:26:24.617519+02:00
    MRP0: Background Media Recovery process shutdown (hdal)
    2020-08-10T09:26:25.250904+02:00
    Managed Standby Recovery Canceled (hdal)
    
    And then starting it again:
    Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT PARALLEL 4 NODELAY
    2020-08-10T09:26:25.284547+02:00
    Attempt to start background Managed Standby Recovery process (hdal)
    Starting background process MRP0
    2020-08-10T09:26:25.306150+02:00
    MRP0 started with pid=77, OS id=115854
    2020-08-10T09:26:25.311038+02:00
    MRP0: Background Managed Standby Recovery process started (hdal)
    2020-08-10T09:26:30.339894+02:00
     Started logmerger process
    2020-08-10T09:26:30.378497+02:00
    Managed Standby Recovery starting Real Time Apply
    2020-08-10T09:26:30.515607+02:00
    Parallel Media Recovery started with 4 slaves
    2020-08-10T09:26:30.741846+02:00
    Media Recovery Waiting for thread 1 sequence 8299 (in transit)
    
    Verify the new setting by looking at the properties (output truncated for brevity):
    DGMGRL> show database verbose hdal_stb
    
    Database - hdal_stb
    
      Role:               PHYSICAL STANDBY
      Intended State:     APPLY-ON
      Transport Lag:      0 seconds (computed 1 second ago)
      Apply Lag:          0 seconds (computed 1 second ago)
      Average Apply Rate: 0 Byte/s
      Active Apply Rate:  0 Byte/s
      Maximum Apply Rate: 0 Byte/s
      Real Time Query:    OFF
      Instance(s):
        hdal
    
      Properties:
        DGConnectIdentifier             = 'hdal_stb'
        LogXptMode                      = 'ASYNC'
        ApplyParallel                   = '4'
    
    The change will be propagated to the other members of the data guard configuration. The data guard broker logfile, located in $ORACLE_BASE/diag/rdbms/$UNIQUE_NAME/$ORACLE_SID/trace, shows:
    08/10/2020 09:26:24
    Forwarding EDIT_RES_PROP operation to member hdal for processing
    08/10/2020 09:26:31
    Updated broker configuration file available, loading from "$ORACLE_HOME/dbs/dr2hdal_stb.dat"
    08/10/2020 09:42:35
    Forwarding MON_PROPERTY operation to member hdal_tstb for processing
    
    The cascading database hdal_tstb simply confirms that the broker configuration has been updated:
    08/10/2020 09:26:31
    Updated broker configuration file available, loading from "$ORACLE_HOME/dbs/dr2hdal_tstb.dat"
    
    The primary database is also verifying the change, although a bit more detailed:
    08/10/2020 09:26:24
    EDIT DATABASE hdal_stb SET PROPERTY applyparallel = 4
    08/10/2020 09:26:31
    EDIT INSTANCE hdal ON DATABASE hdal_stb SET PROPERTY applyparallel = 4 completed successfully
    
    Documentation for dgmgrl version 19c can be found here

    Tuesday, August 4, 2020

    What constitute "mandatory auditing" under Unified Auditing?

    • Activities from administrative users such as SYSDBA, SYSBACKUP, and SYSKM. 
    •  The following audit-related activities are mandatorily audited:
    CREATE AUDIT POLICY AUDIT EXECUTE of the DBMS_FGA PL/SQL package
    ALTER AUDIT POLICY NOAUDIT EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package
    DROP AUDIT POLICY Access to sensitive columns in the optimizer dictionary tables. ALTER TABLE attempts on the AUDSYS audit trail table
    Top level statements by the administrative users SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM, until the database opens All user-issued DML statements on the SYS.AUD$ and SYS.FGA_LOG$ dictionary tables Any attempts to modify the data or metadata of the unified audit internal table. SELECT statements on this table are not audited by default or mandatorily.
    All configuration changes that are made to Oracle Database Vault


    The audit information can be found in the view UNIFIED_AUDIT_TRAIL.
    Documentation for Mandatory Unified Auditing in Oracle 12.2 can be found here