DGMGRL Commands

 The following commands are available:

@              Execute DGMGRL script file

!              Host operating system command

/              Repeat the last command

--             Comment to be ignored by DGMGRL

add            Adds a member to the broker configuration

connect        Connects to an Oracle database instance

convert        Converts a database from one type to another

create         Creates a broker configuration

disable        Disables a configuration, a member, or fast-start failover

edit           Edits a configuration or a member

enable         Enables a configuration, a member, or fast-start failover

exit           Exits the program

export         Export Data Guard Broker configuration to a file.

failover       Changes a standby database to be the primary database

help           Displays description and syntax for a command

host           Host operating system command

import         Import Data Guard Broker configuration from a file.

migrate        Migrate a pluggable database from one configuration to another.

quit           Exits the program

reinstate      Changes a database marked for reinstatement into a viable standby

rem            Comment to be ignored by DGMGRL

remove         Removes a configuration or a member

set            Set a DGMGRLI CLI property to a specified value

show           Displays information about a configuration or a member

shutdown       Shuts down a currently running Oracle database instance

spool          store input and output of DGMGRL CLI in a file

sql            Executes a SQL statement

start          Starts the fast-start failover observer

startup        Starts an Oracle database instance

stop           Stops the fast-start failover observer

switchover     Switches roles between a primary and standby database

validate       Performs an exhaustive set of validations for a member

Use "help <command>" to see syntax for individual commands

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

DGMGRL> help show

Displays information about a configuration or a member

Syntax:

  SHOW ALL;

  SHOW CONFIGURATION [LAG] [VERBOSE];

  SHOW CONFIGURATION WHEN PRIMARY IS <database name>;

  SHOW { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC } [VERBOSE]

    <object name> [<property name>];

  SHOW { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC | MEMBER }

    <object name> PARAMETER [<initialization parameter name>];

  SHOW INSTANCE [VERBOSE] <instance name> [<property name>]

    [ON { DATABASE | FAR_SYNC } <object name>];

  SHOW FAST_START FAILOVER;

  SHOW OBSERVER;

  SHOW OBSERVERS [FOR <configuration group name>];

  SHOW OBSERVERCONFIGFILE;

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

DGMGRL> help edit

Edits a configuration or a member

Syntax:

  EDIT CONFIGURATION SET PROTECTION MODE [AS]

    { MaxProtection | MaxAvailability | MaxPerformance };

  EDIT CONFIGURATION SET PROPERTY <property name> = <value>;

  EDIT CONFIGURATION RESET PROPERTY <property name>;

  EDIT CONFIGURATION RENAME TO <new configuration name>;

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC }

    <object name> SET PROPERTY <property name> = <value>;

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC }

    <object name> RESET PROPERTY <property name>;

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC }

    <object name> RENAME TO <new object name>;

  EDIT DATABASE <database name> SET STATE = <state>

    [WITH APPLY INSTANCE = <instance name>];

  EDIT INSTANCE <instance name> [ON { DATABASE | FAR_SYNC } <object name>]

    SET AUTO PFILE [ = {<initialization file path> | OFF} ];

  EDIT INSTANCE <instance name> [ON { DATABASE | FAR_SYNC } <object name>]

    SET PROPERTY <property name> = <value>;

  EDIT INSTANCE <instance name> [ON { DATABASE | FAR_SYNC } <object name>]

    RESET PROPERTY <property name>;

  EDIT INSTANCE * ON { DATABASE | FAR_SYNC } <object name>

    SET PROPERTY <property name> = <value>;

  EDIT INSTANCE * ON { DATABASE | FAR_SYNC } <object name>

    RESET PROPERTY <property name>;

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC | MEMBER }

    <object name> SET PARAMETER <parameter name> = <value>

                                [ <initialization parameter options> ];

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC | MEMBER }

    <object name> RESET PARAMETER <parameter name>;

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

DGMGRL> help validate

Performs an exhaustive set of validations for a member

Syntax:

  VALIDATE DATABASE [VERBOSE] <database name>;

  VALIDATE DATABASE [VERBOSE] <database name> DATAFILE <datafile number>

    OUTPUT=<file name>;

  VALIDATE DATABASE [VERBOSE] <database name> SPFILE;

  VALIDATE FAR_SYNC [VERBOSE] <far_sync name>

    [WHEN PRIMARY IS <database name>];

  VALIDATE NETWORK CONFIGURATION FOR { ALL | <member name> };

  VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | <database name> };

  VALIDATE DGConnectIdentifier <connect identfier>;

  VALIDATE FAST_START FAILOVER;

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

DGMGRL> help switchover

Switches roles between a primary and standby database

Syntax:

  SWITCHOVER TO <standby database name> [WAIT [<timeout in seconds>]];

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

DGMGRL> help export

Export Data Guard Broker configuration to a file.

Syntax:

  EXPORT CONFIGURATION [TO <file_name>];

Archive Gap Check on Standby Database

select database_role,open_mode from v$database;

select inst_id,process,pid,status,thread#,sequence#, block# from gv$managed_standby where process like 'MRP%';

select to_char(sysdate,'DD.MM.RR HH24:MI: SS') time, a.thread#, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived, max (a.sequence#) applied, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max (a.sequence#) gap from v$archived_log a where a.applied='YES' group by a.thread#;

PROMPT Oracle Log Apply Gap
SELECT ROWNUM, DEST_ID, Thread, Last_Sequence_Received, Last_Sequence_Applied, Difference FROM (SELECT distinct ARCH.DEST_ID,ARCH.THREAD# Thread, ARCH.SEQUENCE# Last_Sequence_Received, APPL.SEQUENCE# Last_Sequence_Applied, (ARCH.SEQUENCE# - APPL.SEQUENCE#) Difference FROM (SELECT THREAD# ,DEST_ID,SEQUENCE# FROM GV$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM GV$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#) ORDER BY 2;

ADRCI - Purge Database Logs/traces

 #!/bin/bash
# Script By Mohamed Fowjil
# Function to purge logs and traces older than 30 days using ADRCI for a specific ADR home
purge_adrci() {
    local home_path="$1"
    echo "Purging logs and traces older than 30 days for ADR home: $home_path"
    adrci exec="set homepath $home_path; purge -age 43200"  # 43200 minutes = 30 days
}
# Function to rotate alert logs
rotate_alert_log() {
    local alert_log="$1"
    if [ -f "$alert_log" ]; then
        echo "Rotating alert log: $alert_log"
        mv "$alert_log" "${alert_log}.$(date +%Y%m%d%H%M%S).bak"
        touch "$alert_log"
    else
        echo "Alert log not found: $alert_log"
    fi
}
# Function to rotate listener logs
rotate_listener_log() {
    local listener_log="$1"
    if [ -f "$listener_log" ]; then
        echo "Rotating listener log: $listener_log"
        mv "$listener_log" "${listener_log}.$(date +%Y%m%d%H%M%S).bak"
        touch "$listener_log"
    else
        echo "Listener log not found: $listener_log"
    fi
}
# Function to purge audit files older than 30 days
purge_audit_files() {
    local audit_directory="$1"
    if [ -d "$audit_directory" ]; then
        echo "Purging audit files older than 30 days in: $audit_directory"
        find "$audit_directory" -type f -name "*.aud" -mtime +30 -exec rm -f {} \;
    else
        echo "Audit directory not found: $audit_directory"
    fi
}
# Main script execution
echo "Starting Oracle Database log management..."
# Fetch the list of ADR homes
adr_homes=$(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "^$")
# Check if there are any ADR homes to process
if [ -z "$adr_homes" ]; then
    echo "No ADR homes found."
    exit 1
fi
# Purge logs for Oracle Database homes
for home in $adr_homes; do
    if [[ "$home" == *"rdbms"* ]]; then
        purge_adrci "$home"
    fi
done
# Rotate alert logs for database  instances
alert_logs=("/u01/app/oracle/diag/rdbms/*/*/trace/alert*.log")
for log in "${alert_logs[@]}"; do
    for log_file in $log; do
        rotate_alert_log "$log_file"
    done
done
# Rotate listener logs
##listener_logs=("/u01/app/oracle/diag/tnslsnr/*/listener*/alert/log.xml" )
for log in "${listener_logs[@]}"; do
    for log_file in $log; do
        rotate_listener_log "$log_file"
    done
done


# Purge audit files in common directories
audit_directories=("/u01/app/oracle/admin/*/adump")
for dir in "${audit_directories[@]}"; do
    for audit_dir in $dir; do
        purge_audit_files "$audit_dir"
    done
done
echo "Oracle Database log management completed."

ASM Usage

set lines 999;
col diskgroup for a15
col diskname for a15
col path for a35
select a.name DiskGroup,b.name DiskName, ROUND(b.total_mb /1024,2) as Total_GB, ROUND((b.total_mb-b.free_mb) /1024,2)As Used_GB, ROUND(b.free_mb / 1024,2) as Free_GB,b.path,b.header_status, ROUND((b.total_mb - b.free_mb) / b.total_mb * 100, 2) AS "USED%"
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number
order by b.group_number,b.name;

FRA Usage

set linesize 500
col NAME for a50
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;