Sql1

#!/bin/bash


LOGFILE="/home/oracle/pdb_output.log"

: > $LOGFILE # Clear the log file


get_pdb_details() {

  echo "CDB_UNIQUE_NAME|PDB_NAME|CPU|SGA_MAX_SIZE_GB|SGA_TARGET_GB|PGA_GB|DB_SIZE_GB" > $LOGFILE


  # Iterate over each CDB

  for ENV_FILE in /home/oracle/*.env; do

    source "${ENV_FILE}"


    # Validate environment variables

    if [[ -z "$ORACLE_SID" || -z "$ORACLE_HOME" ]]; then

      continue

    fi


    # Check if CDB is running

    INSTANCE_STATUS=$(timeout 5 sqlplus -s / as sysdba <<EOF

SET HEAD OFF FEEDBACK OFF

SELECT instance_name FROM v\$instance WHERE status = 'OPEN';

EXIT;

EOF

)


    if [[ -z "$INSTANCE_STATUS" ]]; then

      continue

    fi


    # Fetch CDB unique name

    CDB_UNIQUE_NAME=$(sqlplus -s / as sysdba <<EOF

SET HEAD OFF FEEDBACK OFF

SELECT value FROM v\$parameter WHERE name = 'db_unique_name';

EXIT;

EOF

 | tr -d "\n")


    # Fetch PDB names

    PDBS=$(sqlplus -s / as sysdba <<EOF

SET HEAD OFF FEED OFF

SELECT pdb_name FROM cdb_pdbs WHERE pdb_name != 'PDB\$SEED';

EXIT;

EOF

)


    # Fetch and display PDB details in a single line

    for PDB_NAME in $PDBS; do

      DETAILS=$(sqlplus -s / as sysdba <<EOF

SET HEAD OFF FEEDBACK OFF

ALTER SESSION SET CONTAINER = $PDB_NAME;

SELECT

  (SELECT value FROM v\$parameter WHERE name = 'cpu_count') || '|' ||

  TO_CHAR(ROUND((SELECT value FROM v\$parameter WHERE name = 'sga_max_size') / 1024 / 1024 / 1024, 2)) || '|' ||

  TO_CHAR(ROUND((SELECT value FROM v\$parameter WHERE name = 'sga_target') / 1024 / 1024 / 1024, 2)) || '|' ||

  TO_CHAR(ROUND((SELECT value FROM v\$parameter WHERE name = 'pga_aggregate_target') / 1024 / 1024 / 1024, 2)) || '|' ||

  TO_CHAR(ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2))

FROM dba_data_files;

EXIT;

EOF

 | tr -d "\n")


      # If DETAILS is empty or contains multiple lines, handle it appropriately

      if [[ -n "$DETAILS" && "$DETAILS" != *"|"* ]]; then

        echo "$CDB_UNIQUE_NAME|$PDB_NAME|Error in fetching details" >> $LOGFILE

      else

        echo "$CDB_UNIQUE_NAME|$PDB_NAME|$DETAILS" >> $LOGFILE

      fi

    done

  done

}


get_pdb_details



The script has been updated to ensure that the entire output is captured in a single line without line breaks.


Run it a

gain and verify the output. Let me know if further adjustments are needed.


Comments

Post a Comment

Popular posts from this blog

Database growth

#!/bin/bash # Log file LOGFILE="db_growth_check.log" echo "Checking yearly database growth on all running Oracle databases..." > $LOGFILE echo "DATABASE_NAME | YEAR | GROWTH_GB" >> $LOGFILE echo "---------------------------------" >> $LOGFILE # Find running Oracle instances (excluding ASM and APX) ps -ef | grep pmon | grep -v grep | grep -Ev "(\+ASM|APX)" | awk -F'_' '{print $3}' | while read -r ORACLE_SID do     if [[ -n "$ORACLE_SID" ]]; then         echo "Processing database: $ORACLE_SID"         # Set Oracle environment         export ORACLE_SID=$ORACLE_SID         export ORAENV_ASK=NO         . oraenv > /dev/null 2>&1         # Run SQL query to check yearly database growth         SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF; SET FEEDBACK OFF; SELECT '$ORACLE_SID' AS DATABASE_NAME,  ...
Read more

DBA Day-2 ve

 Operating system  CPU mpstat top  load average  pidstat dstat -vr strace---> debug if system CPU usage % is high but if you dont know then use strace -tp `grep testdb1 2>&1 | head 100 debug if User CPU usage % is high but if you dont know then use 6. jobs (hit enter) 6.1 perf record -F 99 -a -g --sleep 10 (Profiling ) 6.2 perf report -n --stdio 6.3 ./perf-tools/execsnoop Memory vmstat I/O iostat -xmd 1 iotop Network sar -n DEV 1 mtr netstat -antpl | grep 1521 nmcli device status ethtool iperf3 -c 10.1.90.51 https://www.youtube.com/watch?v=eWUeJBAiX80 fs.aio-max-nr--> Async I/O max fs.file-max --> Max file open kernal.shmmin --> how many program segment can access(permission) the shared memory kernal.shmall --> maximum amount of shared memory can accessed by all the process other than OS(set to sum of all SGAs on the server divided by page size – ‘getconf PAGESIZE’) kernal.shmmax --> maximum amount of shared memory can accessed by ind...
Read more

Asm

#!/bin/bash # Set environment variables ORACLE_SID=YOUR_SID ORACLE_HOME=/path/to/oracle_home export ORACLE_SID ORACLE_HOME LOG_DIR=/path/to/logs SQL_DIR=/path/to/sql mkdir -p "$LOG_DIR" mkdir -p "$SQL_DIR" DB_USER="/ as sysdba" # Function to generate SQL data for each target type generate_sql() {   local TARGET_TYPE=$1   local SQL_FILE="$SQL_DIR/${TARGET_TYPE}_data.sql"   local OUTPUT_FILE="/tmp/${TARGET_TYPE}_data.txt"   case "$TARGET_TYPE" in     cluster)       echo "SET HEADING OFF; SET FEEDBACK OFF; SET PAGESIZE 0;" > "$SQL_FILE"       echo "SELECT TARGET_NAME || '|' || HOST_NAME || '|' || CREDENTIALS || '|' || CLUSTER_NAME || '|' || VIP_ADDRESS FROM CLUSTER_TARGETS;" >> "$SQL_FILE"       ;;     # Additional target types can be added here     *)       echo "[ERROR] Unknown target type: $TARGET_TYPE"       return 1       ;;   esac ...
Read more