Points to remember in Couchbase

GitHub Copilot

Used 1 reference
Your script does a good job of estimating SGA requirements based on the init.ora file. However, to fully reproduce and diagnose SGA/ORA-00821/ORA-00838 issues in real-world Oracle environments, you should also consider the following OS and system-level factors:
1. HugePages Configuration
If HugePages are not configured or are insufficient, Oracle may fail to allocate large SGA values, even if enough RAM is available.
Check HugePages status:
grep Huge /proc/meminfo
Check Oracle's HugePages recommendations:
grep Huge /proc/meminfo
cat /etc/sysctl.conf | grep hugepages
Oracle script for HugePages:
$ORACLE_HOME/bin/hugepages_settings.sh
2. OS Memory Limits
ulimit settings for the Oracle user can restrict memory allocation.
ulimit -a
/etc/security/limits.conf should have appropriate values for memlock, nofile, etc.
3. Transparent HugePages (THP)
THP should be disabled for Oracle databases.
cat /sys/kernel/mm/transparent_hugepage/enabled
4. Kernel Shared Memory Parameters
Check and tune shmmax, shmall, etc., in /etc/sysctl.conf:
sysctl -a | grep shm
5. NUMA Settings
On systems with Non-Uniform Memory Access (NUMA), improper configuration can affect SGA allocation.
6. Swap Space
Ensure swap is not overused, and enough physical RAM is available.
7. Oracle Version/Platform Bugs
Some Oracle versions/platforms have bugs or quirks with certain SGA sizes or OS configurations. Always check Oracle support notes for your version.
Summary Table for Reproduction Checklist
Factor
How to Check/Test
Why Important
HugePages
/proc/meminfo, Oracle scripts
SGA allocation efficiency
ulimit/limits.conf
ulimit -a, /etc/security/limits.conf
Process memory limits
Transparent HugePages
/sys/kernel/mm/transparent_hugepage/enabled
Should be disabled
Kernel shm params
`sysctl -a
grep shm`
NUMA
numactl --hardware
Memory locality/performance
Swap
free -m
Avoid swapping SGA
Recommendation
Include these OS-level checks in your test script or documentation.
Document the OS/kernel settings and their values when reproducing or troubleshooting SGA-related startup issues.
If possible, automate the collection of these values for easier diagnosis.
Including these OS and kernel settings will make your SGA/ORA-00821/ORA-00838 reproduction and troubleshooting much more robust and reliable.

#!/bin/bash
#!/bin/bash

#!/bin/bash
# --- Configuration ---
LOG_FILE="noncdb_to_pdb_conversion.log"
PROMPT_USER=true
SCRIPT_STATE_FILE="conversion_state.txt"
TOTAL_TIME=0
ALERT_LOG_DIR="/path/to/alert/logs" # Update this path to your alert log directory
# --- Function Definitions ---
log() {
  echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}
prompt_user() {
  if [[ "$PROMPT_USER" == "true" ]]; then
    local response
    while true; do
      read -p "$1 (yes/no): " response
      case "$response" in
        [yY][eE][sS]) log "User confirmed: $1"; return 0 ;;
        [nN][oO]) log "User rejected: $1"; log "Exiting script."; exit 1 ;;
        *) echo "Invalid input. Please enter 'yes' or 'no'." ;;
      esac
    done
  else
    log "Automatic: $1"
    return 0
  fi
}
get_current_state() {
  [[ -f "$SCRIPT_STATE_FILE" ]] && cat "$SCRIPT_STATE_FILE" || echo "START"
}
set_current_state() {
  echo "$1" > "$SCRIPT_STATE_FILE"
}
get_sids_from_pmon() {
  ps -ef | grep pmon | grep -v grep | grep -iv asm | grep -iv apx | awk '{print $8}' | awk -F "_" '{print $3}'
}
choose_sid() {
  local options=("$@")
  local num_options=${#options[@]}
  if (( num_options == 0 )); then
    log "No SIDs provided to choose from."
    exit 1
  fi
  echo "Please choose a SID:"
  for ((i=0; i<num_options; i++)); do
    echo "$((i+1))) ${options[$i]}"
  done
  while true; do
    read -p "Enter your choice (1-$num_options): " choice
    if [[ "$choice" =~ ^[0-9]+$ ]] && (( choice >= 1 && choice <= num_options )); then
      echo "${options[$((choice-1))]}"
      return 0
    else
      echo "Invalid choice. Please enter a number between 1 and $num_options."
    fi
  done
}
is_cdb() {
  local sid="$1"
  export ORACLE_SID="$sid"
  local result=$(sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select cdb from v\\$database;
    exit
EOF
  )
  [[ "$result" == "YES" ]]
}
get_datafile_path() {
  local sid="$1"
  export ORACLE_SID="$sid"
  sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select name from v\\$datafile where rownum=1;
    exit
EOF
}
execute_sql_command() {
  local sql_command="$1"
  local sid="$2"
  export ORACLE_SID="$sid"
  local start_time=$(date +%s)
  log "Executing SQL command: $sql_command for database: $sid"
  sqlplus -s "/ as sysdba" <<-EOF
    WHENEVER SQLERROR EXIT FAILURE;
    $sql_command;
    exit;
EOF
  local status=$?
  local end_time=$(date +%s)
  local duration=$((end_time - start_time))
  if [[ "$status" -eq 0 ]]; then
    log "SQL command for $sid succeeded."
  else
    log "SQL command for $sid failed."
    check_alert_log "$sid"
    exit 1
  fi
  if (( duration > 60 )); then
    log "SQL command took $((duration / 60)) minutes and $((duration % 60)) seconds to complete."
  else
    log "SQL command took $duration seconds to complete."
  fi
  ((TOTAL_TIME+=duration))
}
check_alert_log() {
  local sid="$1"
  local alert_log_file="$ALERT_LOG_DIR/alert_$sid.log"
  if [[ -f "$alert_log_file" ]]; then
    log "Checking alert log for $sid: $alert_log_file"
    grep -i "error" "$alert_log_file" | tail -n 10 | tee -a "$LOG_FILE"
  else
    log "Alert log file not found for $sid."
  fi
}
time_step() {
  local step_name="$1"
  local -n step_code_ref="$2"
  local start_time=$(date +%s)
  log "Starting step: $step_name"
  "${step_code_ref[@]}"
  local end_time=$(date +%s)
  local duration=$((end_time - start_time))
  if (( duration > 60 )); then
    log "Step '$step_name' took $((duration / 60)) minutes and $((duration % 60)) seconds to complete."
  else
    log "Step '$step_name' took $duration seconds to complete."
  fi
  ((TOTAL_TIME+=duration))
}
# --- Script Start ---
> "$LOG_FILE"
log "Script started."
CURRENT_STATE=$(get_current_state)
log "Current state: $CURRENT_STATE"
# --- Step 1: Find PMON processes and select SIDs ---
step1_code=(
  prompt_user "Proceed with finding PMON processes and selecting SIDs?"
  if command -v mapfile &> /dev/null; then
    mapfile -t SIDS < <(get_sids_from_pmon)
  else
    IFS=$'\n' read -d '' -r -a SIDS < <(get_sids_from_pmon)
  fi
  PMON_COUNT=${#SIDS[@]}
  log "Found $PMON_COUNT PMON processes: ${SIDS[*]}"
  if (( PMON_COUNT < 2 )); then
    log "Error: Less than 2 PMON processes found. Need a non-CDB and a CDB running."
    exit 1
  fi
  # Prompt user to select SIDs
  echo "Available Oracle SIDs:"
  for i in "${!SIDS[@]}"; do
    echo "$((i+1))) ${SIDS[$i]}"
  done
  read -p "Enter the number for the CDB SID (or press Enter to auto-select): " cdb_choice
  read -p "Enter the number for the NONCDB SID (or press Enter to auto-select): " noncdb_choice
  if [[ "$cdb_choice" =~ ^[0-9]+$ ]] && (( cdb_choice >= 1 && cdb_choice <= PMON_COUNT )); then
    CDB_SID="${SIDS[$((cdb_choice-1))]}"
  fi
  if [[ "$noncdb_choice" =~ ^[0-9]+$ ]] && (( noncdb_choice >= 1 && noncdb_choice <= PMON_COUNT )); then
    NONCDB_SID="${SIDS[$((noncdb_choice-1))]}"
  fi
  # If user didn't select, auto-detect
  if [[ -z "$CDB_SID" || -z "$NONCDB_SID" || "$CDB_SID" == "$NONCDB_SID" ]]; then
    log "Auto-selecting CDB and NONCDB SIDs..."
    for sid in "${SIDS[@]}"; do
      if is_cdb "$sid"; then
        CDB_SID="$sid"
      else
        NONCDB_SID="$sid"
      fi
    done
  fi
  # Final validation
  if [[ -z "$CDB_SID" || -z "$NONCDB_SID" || "$CDB_SID" == "$NONCDB_SID" ]]; then
    log "Error: Could not determine valid CDB and NONCDB SIDs."
    exit 1
  fi
  log "Selected CDB_SID: $CDB_SID"
  log "Selected NONCDB_SID: $NONCDB_SID"
  set_current_state "SIDS_SELECTED"
  CURRENT_STATE="SIDS_SELECTED"
)
if [[ "$CURRENT_STATE" == "START" ]]; then
  time_step "Step 1: Finding PMON processes and selecting SIDs." step1_code
fi
# --- Step 2: Check CDB vs Non-CDB ---
step2_code=(
  log "Step 2: Checking CDB vs. Non-CDB."
  prompt_user "Proceed with checking CDB vs. Non-CDB?"
  if ! is_cdb "$CDB_SID"; then
    log "Error: $CDB_SID is not a CDB."
    exit 1
  fi
  if is_cdb "$NONCDB_SID"; then
    log "Error: $NONCDB_SID is a CDB."
    exit 1
  fi
  log "CDB_SID: $CDB_SID"
  log "NONCDB_SID: $NONCDB_SID"
  set_current_state "DB_TYPES_CHECKED"
  CURRENT_STATE="DB_TYPES_CHECKED"
)
if [[ "$CURRENT_STATE" == "SIDS_SELECTED" ]]; then
  time_step "Step 2: Checking CDB vs. Non-CDB." step2_code
fi
# --- Step 3: Get Datafile Paths ---
step3_code=(
  log "Step 3: Getting Datafile Paths."
  prompt_user "Proceed with getting datafile paths?"
  NONCDB_DATAFILE_PATH=$(get_datafile_path "$NONCDB_SID")
  if [[ -z "$NONCDB_DATAFILE_PATH" ]]; then
    log "Error getting non-CDB datafile path"
    exit 1
  fi
  log "Non-CDB datafile path: $NONCDB_DATAFILE_PATH"
  CDB_DATAFILE_PATH=$(get_datafile_path "$CDB_SID")
  if [[ -z "$CDB_DATAFILE_PATH" ]]; then
    log "Error getting CDB datafile path"
    exit 1
  fi
  log "CDB datafile path: $CDB_DATAFILE_PATH"
  NONCDB_DIR=$(dirname "$NONCDB_DATAFILE_PATH")
  CDB_DIR=$(dirname "$CDB_DATAFILE_PATH")
  log "Non-CDB directory: $NONCDB_DIR"
  log "CDB directory: $CDB_DIR"
  set_current_state "DATAFILE_PATHS_RETRIEVED"
  CURRENT_STATE="DATAFILE_PATHS_RETRIEVED"
)
if [[ "$CURRENT_STATE" == "DB_TYPES_CHECKED" ]]; then
  time_step "Step 3: Getting Datafile Paths." step3_code
fi
# --- Step 4: Non CDB Preparation ---
step4_code=(
  log "Step 4: Non-CDB Preparation."
  prompt_user "Proceed with non-CDB preparation?"
  execute_sql_command "alter database open read only" "$NONCDB_SID"
  # Validate open mode
  mode=$(sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select open_mode from v\\$database;
    exit
EOF
  )
  if [[ "$mode" != *"READ ONLY"* ]]; then
    log "Error: Non-CDB is not in READ ONLY mode after command. Current mode: $mode"
    exit 1
  fi
  execute_sql_command "exec dbms_pdb.describe(pdb_descr_file=>'/tmp/new_pdb.xml');" "$NONCDB_SID"
  execute_sql_command "shutdown immediate;" "$NONCDB_SID"
  set_current_state "NONCDB_PREPARED"
  CURRENT_STATE="NONCDB_PREPARED"
)
if [[ "$CURRENT_STATE" == "DATAFILE_PATHS_RETRIEVED" ]]; then
  time_step "Step 4: Non-CDB Preparation." step4_code
fi
# --- Step 5: Create PDB ---
step5_code=(
  log "Step 5: Create PDB."
  prompt_user "Proceed with PDB creation?"
  PDB_NAME=PDBNEW
  NEW_LOCATION="$CDB_DIR/$PDB_NAME"
  # Prompt for method
  echo "Choose the method for PDB creation:"
  echo "1. nocopy (default)"
  echo "2. copy"
  echo "3. move"
  read -p "Enter your choice (1-3, default 1): " method_choice
  case "$method_choice" in
    2) METHOD="copy" ;;
    3) METHOD="move" ;;
    *) METHOD="nocopy" ;;
  esac
  case "$METHOD" in
    nocopy)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' NOCOPY;" "$CDB_SID"
      ;;
    copy)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' COPY file_name_convert=('$NONCDB_DIR', '$NEW_LOCATION');" "$CDB_SID"
      ;;
    move)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' MOVE file_name_convert=('$NONCDB_DIR', '$NEW_LOCATION');" "$CDB_SID"
      ;;
  esac
  set_current_state "PDB_CREATED"
  CURRENT_STATE="PDB_CREATED"
)
if [[ "$CURRENT_STATE" == "NONCDB_PREPARED" ]]; then
  time_step "Step 5: Create PDB." step5_code
fi
# --- Step 6: Post conversion process ---
step6_code=(
  log "Step 6: Post PDB creation process"
  prompt_user "Proceed with post PDB creation process?"
  execute_sql_command "alter pluggable database $PDB_NAME open read write;" "$CDB_SID"
  execute_sql_command "@\$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;" "$PDB_NAME"
  set_current_state "COMPLETE"
  CURRENT_STATE="COMPLETE"
)
if [[ "$CURRENT_STATE" == "PDB_CREATED" ]]; then
  time_step "Step 6: Post PDB creation process" step6_code
fi
# --- Finalization ---
if [[ "$CURRENT_STATE" == "COMPLETE" ]]; then
  log "Conversion completed successfully."
  log "You should verify that everything is working properly."
  rm -f "$SCRIPT_STATE_FILE"
else
  log "Conversion failed or was interrupted at state: $CURRENT_STATE"
  log "You can restart the script to continue."
fi
if (( TOTAL_TIME > 60 )); then
  log "Total time for the entire procedure: $((TOTAL_TIME / 60)) minutes and $((TOTAL_TIME % 60)) seconds"
else
  log "Total time for the entire procedure: $TOTAL_TIME seconds"
fi
log "Script finished."
exit 0

===test mode 
#!/bin/bash
# --- Configuration ---
LOG_FILE="noncdb_to_pdb_conversion.log"
PROMPT_USER=true
SCRIPT_STATE_FILE="conversion_state.txt"
TOTAL_TIME=0
ALERT_LOG_DIR="/path/to/alert/logs" # Update this path to your alert log directory
TEST_MODE=true # Set to true for testing mode, false for actual execution
# --- Function Definitions ---
log() {
  echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}
prompt_user() {
  if [[ "$PROMPT_USER" == "true" ]]; then
    local response
    while true; do
      read -p "$1 (yes/no): " response
      case "$response" in
        [yY][eE][sS]) log "User confirmed: $1"; return 0 ;;
        [nN][oO]) log "User rejected: $1"; log "Exiting script."; exit 1 ;;
        *) echo "Invalid input. Please enter 'yes' or 'no'." ;;
      esac
    done
  else
    log "Automatic: $1"
    return 0
  fi
}
get_current_state() {
  [[ -f "$SCRIPT_STATE_FILE" ]] && cat "$SCRIPT_STATE_FILE" || echo "START"
}
set_current_state() {
  echo "$1" > "$SCRIPT_STATE_FILE"
}
get_sids_from_pmon() {
  ps -ef | grep pmon | grep -v grep | grep -iv asm | grep -iv apx | awk '{print $8}' | awk -F "_" '{print $3}'
}
choose_sid() {
  local options=("$@")
  local num_options=${#options[@]}
  if (( num_options == 0 )); then
    log "No SIDs provided to choose from."
    exit 1
  fi
  echo "Please choose a SID:"
  for ((i=0; i<num_options; i++)); do
    echo "$((i+1))) ${options[$i]}"
  done
  while true; do
    read -p "Enter your choice (1-$num_options): " choice
    if [[ "$choice" =~ ^[0-9]+$ ]] && (( choice >= 1 && choice <= num_options )); then
      echo "${options[$((choice-1))]}"
      return 0
    else
      echo "Invalid choice. Please enter a number between 1 and $num_options."
    fi
  done
}
is_cdb() {
  local sid="$1"
  export ORACLE_SID="$sid"
  local result=$(sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select cdb from v\\$database;
    exit
EOF
  )
  [[ "$result" == "YES" ]]
}
get_datafile_path() {
  local sid="$1"
  export ORACLE_SID="$sid"
  sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select name from v\\$datafile where rownum=1;
    exit
EOF
}
execute_sql_command() {
  local sql_command="$1"
  local sid="$2"
  export ORACLE_SID="$sid"
  if [[ "$TEST_MODE" == "true" ]]; then
    log "TEST MODE: Would execute SQL command: $sql_command for database: $sid"
    return 0
  fi
  local start_time=$(date +%s)
  log "Executing SQL command: $sql_command for database: $sid"
  sqlplus -s "/ as sysdba" <<-EOF
    WHENEVER SQLERROR EXIT FAILURE;
    $sql_command;
    exit;
EOF
  local status=$?
  local end_time=$(date +%s)
  local duration=$((end_time - start_time))
  if [[ "$status" -eq 0 ]]; then
    log "SQL command for $sid succeeded."
  else
    log "SQL command for $sid failed."
    check_alert_log "$sid"
    exit 1
  fi
  if (( duration > 60 )); then
    log "SQL command took $((duration / 60)) minutes and $((duration % 60)) seconds to complete."
  else
    log "SQL command took $duration seconds to complete."
  fi
  ((TOTAL_TIME+=duration))
}
check_alert_log() {
  local sid="$1"
  local alert_log_file="$ALERT_LOG_DIR/alert_$sid.log"
  if [[ -f "$alert_log_file" ]]; then
    log "Checking alert log for $sid: $alert_log_file"
    grep -i "error" "$alert_log_file" | tail -n 10 | tee -a "$LOG_FILE"
  else
    log "Alert log file not found for $sid."
  fi
}
time_step() {
  local step_name="$1"
  local -n step_code_ref="$2"
  local start_time=$(date +%s)
  log "Starting step: $step_name"
  "${step_code_ref[@]}"
  local end_time=$(date +%s)
  local duration=$((end_time - start_time))
  if (( duration > 60 )); then
    log "Step '$step_name' took $((duration / 60)) minutes and $((duration % 60)) seconds to complete."
  else
    log "Step '$step_name' took $duration seconds to complete."
  fi
  ((TOTAL_TIME+=duration))
}
# --- Script Start ---
> "$LOG_FILE"
log "Script started."
CURRENT_STATE=$(get_current_state)
log "Current state: $CURRENT_STATE"
# --- Step 1: Find PMON processes and select SIDs ---
step1_code=(
  prompt_user "Proceed with finding PMON processes and selecting SIDs?"
  if command -v mapfile &> /dev/null; then
    mapfile -t SIDS < <(get_sids_from_pmon)
  else
    IFS=$'\n' read -d '' -r -a SIDS < <(get_sids_from_pmon)
  fi
  PMON_COUNT=${#SIDS[@]}
  log "Found $PMON_COUNT PMON processes: ${SIDS[*]}"
  if (( PMON_COUNT < 2 )); then
    log "Error: Less than 2 PMON processes found. Need a non-CDB and a CDB running."
    exit 1
  fi
  # Prompt user to select SIDs
  echo "Available Oracle SIDs:"
  for i in "${!SIDS[@]}"; do
    echo "$((i+1))) ${SIDS[$i]}"
  done
  read -p "Enter the number for the CDB SID (or press Enter to auto-select): " cdb_choice
  read -p "Enter the number for the NONCDB SID (or press Enter to auto-select): " noncdb_choice
  if [[ "$cdb_choice" =~ ^[0-9]+$ ]] && (( cdb_choice >= 1 && cdb_choice <= PMON_COUNT )); then
    CDB_SID="${SIDS[$((cdb_choice-1))]}"
  fi
  if [[ "$noncdb_choice" =~ ^[0-9]+$ ]] && (( noncdb_choice >= 1 && noncdb_choice <= PMON_COUNT )); then
    NONCDB_SID="${SIDS[$((noncdb_choice-1))]}"
  fi
  # If user didn't select, auto-detect
  if [[ -z "$CDB_SID" || -z "$NONCDB_SID" || "$CDB_SID" == "$NONCDB_SID" ]]; then
    log "Auto-selecting CDB and NONCDB SIDs..."
    for sid in "${SIDS[@]}"; do
      if is_cdb "$sid"; then
        CDB_SID="$sid"
      else
        NONCDB_SID="$sid"
      fi
    done
  fi
  # Final validation
  if [[ -z "$CDB_SID" || -z "$NONCDB_SID" || "$CDB_SID" == "$NONCDB_SID" ]]; then
    log "Error: Could not determine valid CDB and NONCDB SIDs."
    exit 1
  fi
  log "Selected CDB_SID: $CDB_SID"
  log "Selected NONCDB_SID: $NONCDB_SID"
  set_current_state "SIDS_SELECTED"
  CURRENT_STATE="SIDS_SELECTED"
)
if [[ "$CURRENT_STATE" == "START" ]]; then
  time_step "Step 1: Finding PMON processes and selecting SIDs." step1_code
fi
# --- Step 2: Check CDB vs Non-CDB ---
step2_code=(
  log "Step 2: Checking CDB vs. Non-CDB."
  prompt_user "Proceed with checking CDB vs. Non-CDB?"
  if ! is_cdb "$CDB_SID"; then
    log "Error: $CDB_SID is not a CDB."
    exit 1
  fi
  if is_cdb "$NONCDB_SID"; then
    log "Error: $NONCDB_SID is a CDB."
    exit 1
  fi
  log "CDB_SID: $CDB_SID"
  log "NONCDB_SID: $NONCDB_SID"
  set_current_state "DB_TYPES_CHECKED"
  CURRENT_STATE="DB_TYPES_CHECKED"
)
if [[ "$CURRENT_STATE" == "SIDS_SELECTED" ]]; then
  time_step "Step 2: Checking CDB vs. Non-CDB." step2_code
fi
# --- Step 3: Get Datafile Paths ---
step3_code=(
  log "Step 3: Getting Datafile Paths."
  prompt_user "Proceed with getting datafile paths?"
  NONCDB_DATAFILE_PATH=$(get_datafile_path "$NONCDB_SID")
  if [[ -z "$NONCDB_DATAFILE_PATH" ]]; then
    log "Error getting non-CDB datafile path"
    exit 1
  fi
  log "Non-CDB datafile path: $NONCDB_DATAFILE_PATH"
  CDB_DATAFILE_PATH=$(get_datafile_path "$CDB_SID")
  if [[ -z "$CDB_DATAFILE_PATH" ]]; then
    log "Error getting CDB datafile path"
    exit 1
  fi
  log "CDB datafile path: $CDB_DATAFILE_PATH"
  NONCDB_DIR=$(dirname "$NONCDB_DATAFILE_PATH")
  CDB_DIR=$(dirname "$CDB_DATAFILE_PATH")
  log "Non-CDB directory: $NONCDB_DIR"
  log "CDB directory: $CDB_DIR"
  set_current_state "DATAFILE_PATHS_RETRIEVED"
  CURRENT_STATE="DATAFILE_PATHS_RETRIEVED"
)
if [[ "$CURRENT_STATE" == "DB_TYPES_CHECKED" ]]; then
  time_step "Step 3: Getting Datafile Paths." step3_code
fi
# --- Step 4: Non CDB Preparation ---
step4_code=(
  log "Step 4: Non-CDB Preparation."
  prompt_user "Proceed with non-CDB preparation?"
  if [[ "$TEST_MODE" == "true" ]]; then
    log "TEST MODE: Would execute 'alter database open read only' for $NONCDB_SID"
    log "TEST MODE: Would execute 'exec dbms_pdb.describe(pdb_descr_file=>'/tmp/new_pdb.xml');' for $NONCDB_SID"
    log "TEST MODE: Would execute 'shutdown immediate;' for $NONCDB_SID"
  else
    execute_sql_command "alter database open read only" "$NONCDB_SID"
    # Validate open mode
    mode=$(sqlplus -s "/ as sysdba" <<-EOF
      set heading off feedback off pagesize 0
      select open_mode from v\\$database;
      exit
EOF
    )
    if [[ "$mode" != *"READ ONLY"* ]]; then
      log "Error: Non-CDB is not in READ ONLY mode after command. Current mode: $mode"
      exit 1
    fi
    execute_sql_command "exec dbms_pdb.describe(pdb_descr_file=>'/tmp/new_pdb.xml');" "$NONCDB_SID"
    execute_sql_command "shutdown immediate;" "$NONCDB_SID"
  fi
  set_current_state "NONCDB_PREPARED"
  CURRENT_STATE="NONCDB_PREPARED"
)
if [[ "$CURRENT_STATE" == "DATAFILE_PATHS_RETRIEVED" ]]; then
  time_step "Step 4: Non-CDB Preparation." step4_code
fi
# --- Step 5: Create PDB ---
step5_code=(
  log "Step 5: Create PDB."
  prompt_user "Proceed with PDB creation?"
  PDB_NAME=PDBNEW
  NEW_LOCATION="$CDB_DIR/$PDB_NAME"
  # Prompt for method
  echo "Choose the method for PDB creation:"
  echo "1. nocopy (default)"
  echo "2. copy"
  echo "3. move"
  read -p "Enter your choice (1-3, default 1): " method_choice
  case "$method_choice" in
    2) METHOD="copy" ;;
    3) METHOD="move" ;;
    *) METHOD="nocopy" ;;
  esac
  if [[ "$TEST_MODE" == "true" ]]; then
    log "TEST MODE: Would execute PDB creation with method $METHOD for $CDB_SID"
  else
    case "$METHOD" in
      nocopy)
        execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' NOCOPY;" "$CDB_SID"
        ;;
      copy)
        execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' COPY file_name_convert=('$NONCDB_DIR', '$NEW_LOCATION');" "$CDB_SID"
        ;;
      move)
        execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' MOVE file_name_convert=('$NONCDB_DIR', '$NEW_LOCATION');" "$CDB_SID"
        ;;
    esac
  fi
  set_current_state "PDB_CREATED"
  CURRENT_STATE="PDB_CREATED"
)
if [[ "$CURRENT_STATE" == "NONCDB_PREPARED" ]]; then
  time_step "Step 5: Create PDB." step5_code
fi
# --- Step 6: Post conversion process ---
step6_code=(
  log "Step 6: Post PDB creation process"
  prompt_user "Proceed with post PDB creation process?"
  if [[ "$TEST_MODE" == "true" ]]; then
    log "TEST MODE: Would execute 'alter pluggable database $PDB_NAME open read write;' for $CDB_SID"
    log "TEST MODE: Would execute '@\$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;' for $PDB_NAME"
  else
    execute_sql_command "alter pluggable database $PDB_NAME open read write;" "$CDB_SID"
    execute_sql_command "@\$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;" "$PDB_NAME"
  fi
  set_current_state "COMPLETE"
  CURRENT_STATE="COMPLETE"
)
if [[ "$CURRENT_STATE" == "PDB_CREATED" ]]; then
  time_step "Step 6: Post PDB creation process" step6_code
fi
# --- Finalization ---
if [[ "$CURRENT_STATE" == "COMPLETE" ]]; then
  log "Conversion completed successfully."
  log "You should verify that everything is working properly."
  rm -f "$SCRIPT_STATE_FILE"
else
  log "Conversion failed or was interrupted at state: $CURRENT_STATE"
  log "You can restart the script to continue."
fi
if (( TOTAL_TIME > 60 )); then
  log "Total time for the entire procedure: $((TOTAL_TIME / 60)) minutes and $((TOTAL_TIME % 60)) seconds"
else
  log "Total time for the entire procedure: $TOTAL_TIME seconds"
fi
log "Script finished."
exit 0



==nomore

#!/bin/bash
# --- Configuration ---
LOG_FILE="noncdb_to_pdb_conversion.log"
PROMPT_USER=true
SCRIPT_STATE_FILE="conversion_state.txt"
TOTAL_TIME=0
# --- Function Definitions ---
log() {
  echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}
prompt_user() {
  if [[ "$PROMPT_USER" == "true" ]]; then
    local response
    while true; do
      read -p "$1 (yes/no): " response
      case "$response" in
        [yY][eE][sS]) log "User confirmed: $1"; return 0 ;;
        [nN][oO]) log "User rejected: $1"; log "Exiting script."; exit 1 ;;
        *) echo "Invalid input. Please enter 'yes' or 'no'." ;;
      esac
    done
  else
    log "Automatic: $1"
    return 0
  fi
}
get_current_state() {
  [[ -f "$SCRIPT_STATE_FILE" ]] && cat "$SCRIPT_STATE_FILE" || echo "START"
}
set_current_state() {
  echo "$1" > "$SCRIPT_STATE_FILE"
}
get_sids_from_pmon() {
  ps -ef | grep pmon | grep -v grep | grep -iv asm | grep -iv apx | awk '{print $8}' | awk -F "_" '{print $3}'
}
choose_sid() {
  local options=("$@")
  local num_options=${#options[@]}
  if (( num_options == 0 )); then
    log "No SIDs provided to choose from."
    exit 1
  fi
  echo "Please choose a SID:"
  for ((i=0; i<num_options; i++)); do
    echo "$((i+1))) ${options[$i]}"
  done
  while true; do
    read -p "Enter your choice (1-$num_options): " choice
    if [[ "$choice" =~ ^[0-9]+$ ]] && (( choice >= 1 && choice <= num_options )); then
      echo "${options[$((choice-1))]}"
      return 0
    else
      echo "Invalid choice. Please enter a number between 1 and $num_options."
    fi
  done
}
is_cdb() {
  local sid="$1"
  export ORACLE_SID="$sid"
  local result=$(sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select cdb from v\\$database;
    exit
EOF
  )
  [[ "$result" == "YES" ]]
}
get_datafile_path() {
  local sid="$1"
  export ORACLE_SID="$sid"
  sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select name from v\\$datafile where rownum=1;
    exit
EOF
}
execute_sql_command() {
  local sql_command="$1"
  local sid="$2"
  export ORACLE_SID="$sid"
  local start_time=$(date +%s)
  log "Executing SQL command: $sql_command for database: $sid"
  sqlplus -s "/ as sysdba" <<-EOF
    WHENEVER SQLERROR EXIT FAILURE;
    $sql_command;
    exit;
EOF
  local status=$?
  local end_time=$(date +%s)
  local duration=$((end_time - start_time))
  if [[ "$status" -eq 0 ]]; then
    log "SQL command for $sid succeeded."
  else
    log "SQL command for $sid failed."
    exit 1
  fi
  if (( duration > 60 )); then
    log "SQL command took $((duration / 60)) minutes and $((duration % 60)) seconds to complete."
  else
    log "SQL command took $duration seconds to complete."
  fi
  ((TOTAL_TIME+=duration))
}
time_step() {
  local step_name="$1"
  local -n step_code_ref="$2"
  local start_time=$(date +%s)
  log "Starting step: $step_name"
  "${step_code_ref[@]}"
  local end_time=$(date +%s)
  local duration=$((end_time - start_time))
  if (( duration > 60 )); then
    log "Step '$step_name' took $((duration / 60)) minutes and $((duration % 60)) seconds to complete."
  else
    log "Step '$step_name' took $duration seconds to complete."
  fi
  ((TOTAL_TIME+=duration))
}
# --- Script Start ---
> "$LOG_FILE"
log "Script started."
CURRENT_STATE=$(get_current_state)
log "Current state: $CURRENT_STATE"
# --- Step 1: Find PMON processes and select SIDs ---
step1_code=(
  prompt_user "Proceed with finding PMON processes and selecting SIDs?"
  mapfile -t SIDS < <(get_sids_from_pmon)
  PMON_COUNT=${#SIDS[@]}
  log "Found $PMON_COUNT PMON processes: ${SIDS[*]}"
  if (( PMON_COUNT < 2 )); then
    log "Error: Less than 2 PMON processes found. Need a non-CDB and a CDB running."
    exit 1
  fi
  # Prompt user to select SIDs
  echo "Available Oracle SIDs:"
  for i in "${!SIDS[@]}"; do
    echo "$((i+1))) ${SIDS[$i]}"
  done
  read -p "Enter the number for the CDB SID (or press Enter to auto-select): " cdb_choice
  read -p "Enter the number for the NONCDB SID (or press Enter to auto-select): " noncdb_choice
  if [[ "$cdb_choice" =~ ^[0-9]+$ ]] && (( cdb_choice >= 1 && cdb_choice <= PMON_COUNT )); then
    CDB_SID="${SIDS[$((cdb_choice-1))]}"
  fi
  if [[ "$noncdb_choice" =~ ^[0-9]+$ ]] && (( noncdb_choice >= 1 && noncdb_choice <= PMON_COUNT )); then
    NONCDB_SID="${SIDS[$((noncdb_choice-1))]}"
  fi
  # If user didn't select, auto-detect
  if [[ -z "$CDB_SID" || -z "$NONCDB_SID" || "$CDB_SID" == "$NONCDB_SID" ]]; then
    log "Auto-selecting CDB and NONCDB SIDs..."
    for sid in "${SIDS[@]}"; do
      if is_cdb "$sid"; then
        CDB_SID="$sid"
      else
        NONCDB_SID="$sid"
      fi
    done
  fi
  # Final validation
  if [[ -z "$CDB_SID" || -z "$NONCDB_SID" || "$CDB_SID" == "$NONCDB_SID" ]]; then
    log "Error: Could not determine valid CDB and NONCDB SIDs."
    exit 1
  fi
  log "Selected CDB_SID: $CDB_SID"
  log "Selected NONCDB_SID: $NONCDB_SID"
  set_current_state "SIDS_SELECTED"
  CURRENT_STATE="SIDS_SELECTED"
)
if [[ "$CURRENT_STATE" == "START" ]]; then
  time_step "Step 1: Finding PMON processes and selecting SIDs." step1_code
fi
# --- Step 2: Check CDB vs Non-CDB ---
step2_code=(
  log "Step 2: Checking CDB vs. Non-CDB."
  prompt_user "Proceed with checking CDB vs. Non-CDB?"
  if ! is_cdb "$CDB_SID"; then
    log "Error: $CDB_SID is not a CDB."
    exit 1
  fi
  if is_cdb "$NONCDB_SID"; then
    log "Error: $NONCDB_SID is a CDB."
    exit 1
  fi
  log "CDB_SID: $CDB_SID"
  log "NONCDB_SID: $NONCDB_SID"
  set_current_state "DB_TYPES_CHECKED"
  CURRENT_STATE="DB_TYPES_CHECKED"
)
if [[ "$CURRENT_STATE" == "SIDS_SELECTED" ]]; then
  time_step "Step 2: Checking CDB vs. Non-CDB." step2_code
fi
# --- Step 3: Get Datafile Paths ---
step3_code=(
  log "Step 3: Getting Datafile Paths."
  prompt_user "Proceed with getting datafile paths?"
  NONCDB_DATAFILE_PATH=$(get_datafile_path "$NONCDB_SID")
  if [[ -z "$NONCDB_DATAFILE_PATH" ]]; then
    log "Error getting non-CDB datafile path"
    exit 1
  fi
  log "Non-CDB datafile path: $NONCDB_DATAFILE_PATH"
  CDB_DATAFILE_PATH=$(get_datafile_path "$CDB_SID")
  if [[ -z "$CDB_DATAFILE_PATH" ]]; then
    log "Error getting CDB datafile path"
    exit 1
  fi
  log "CDB datafile path: $CDB_DATAFILE_PATH"
  NONCDB_DIR=$(dirname "$NONCDB_DATAFILE_PATH")
  CDB_DIR=$(dirname "$CDB_DATAFILE_PATH")
  log "Non-CDB directory: $NONCDB_DIR"
  log "CDB directory: $CDB_DIR"
  set_current_state "DATAFILE_PATHS_RETRIEVED"
  CURRENT_STATE="DATAFILE_PATHS_RETRIEVED"
)
if [[ "$CURRENT_STATE" == "DB_TYPES_CHECKED" ]]; then
  time_step "Step 3: Getting Datafile Paths." step3_code
fi
# --- Step 4: Non CDB Preparation ---
step4_code=(
  log "Step 4: Non-CDB Preparation."
  prompt_user "Proceed with non-CDB preparation?"
  execute_sql_command "alter database open read only" "$NONCDB_SID"
  # Validate open mode
  mode=$(sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select open_mode from v\\$database;
    exit
EOF
  )
  if [[ "$mode" != *"READ ONLY"* ]]; then
    log "Error: Non-CDB is not in READ ONLY mode after command. Current mode: $mode"
    exit 1
  fi
  execute_sql_command "exec dbms_pdb.describe(pdb_descr_file=>'/tmp/new_pdb.xml');" "$NONCDB_SID"
  execute_sql_command "shutdown immediate;" "$NONCDB_SID"
  set_current_state "NONCDB_PREPARED"
  CURRENT_STATE="NONCDB_PREPARED"
)
if [[ "$CURRENT_STATE" == "DATAFILE_PATHS_RETRIEVED" ]]; then
  time_step "Step 4: Non-CDB Preparation." step4_code
fi
# --- Step 5: Create PDB ---
step5_code=(
  log "Step 5: Create PDB."
  prompt_user "Proceed with PDB creation?"
  PDB_NAME=PDBNEW
  NEW_LOCATION="$CDB_DIR/$PDB_NAME"
  # Prompt for method
  echo "Choose the method for PDB creation:"
  echo "1. nocopy (default)"
  echo "2. copy"
  echo "3. move"
  read -p "Enter your choice (1-3, default 1): " method_choice
  case "$method_choice" in
    2) METHOD="copy" ;;
    3) METHOD="move" ;;
    *) METHOD="nocopy" ;;
  esac
  case "$METHOD" in
    nocopy)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' NOCOPY;" "$CDB_SID"
      ;;
    copy)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' COPY file_name_convert=('$NONCDB_DIR', '$NEW_LOCATION');" "$CDB_SID"
      ;;
    move)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' MOVE file_name_convert=('$NONCDB_DIR', '$NEW_LOCATION');" "$CDB_SID"
      ;;
  esac
  set_current_state "PDB_CREATED"
  CURRENT_STATE="PDB_CREATED"
)
if [[ "$CURRENT_STATE" == "NONCDB_PREPARED" ]]; then
  time_step "Step 5: Create PDB." step5_code
fi
# --- Step 6: Post conversion process ---
step6_code=(
  log "Step 6: Post PDB creation process"
  prompt_user "Proceed with post PDB creation process?"
  execute_sql_command "alter pluggable database $PDB_NAME open read write;" "$CDB_SID"
  execute_sql_command "@\$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;" "$PDB_NAME"
  set_current_state "COMPLETE"
  CURRENT_STATE="COMPLETE"
)
if [[ "$CURRENT_STATE" == "PDB_CREATED" ]]; then
  time_step "Step 6: Post PDB creation process" step6_code
fi
# --- Finalization ---
if [[ "$CURRENT_STATE" == "COMPLETE" ]]; then
  log "Conversion completed successfully."
  log "You should verify that everything is working properly."
  rm -f "$SCRIPT_STATE_FILE"
else
  log "Conversion failed or was interrupted at state: $CURRENT_STATE"
  log "You can restart the script to continue."
fi
if (( TOTAL_TIME > 60 )); then
  log "Total time for the entire procedure: $((TOTAL_TIME / 60)) minutes and $((TOTAL_TIME % 60)) seconds"
else
  log "Total time for the entire procedure: $TOTAL_TIME seconds"
fi
log "Script finished."
exit 0


#!/bin/bash

# --- Configuration ---
LOG_FILE="noncdb_to_pdb_conversion.log"
SCRIPT_STATE_FILE="conversion_state.txt"
PROMPT_USER=true
TEST_MODE=false

# --- Parse arguments ---
for arg in "$@"; do
  case "$arg" in
    --test) TEST_MODE=true ;;
    --auto) PROMPT_USER=false ;;
  esac
done

# --- Logging ---
log() {
  echo "$(date '+%Y-%m-%d %H:%M:%S') - $*" | tee -a "$LOG_FILE"
}

# --- Prompt user for yes/no ---
prompt_user() {
  if [[ "$PROMPT_USER" == "true" ]]; then
    local response
    while true; do
      read -p "$1 (yes/no): " response
      case "$response" in
        [yY][eE][sS]) log "User confirmed: $1"; return 0 ;;
        [nN][oO]) log "User rejected: $1"; log "Exiting script."; exit 1 ;;
        *) echo "Invalid input. Please enter 'yes' or 'no'." ;;
      esac
    done
  else
    log "Automatic: $1"
    return 0
  fi
}

# --- Get current state ---
get_current_state() {
  [[ -f "$SCRIPT_STATE_FILE" ]] && cat "$SCRIPT_STATE_FILE" || echo "START"
}

set_current_state() {
  echo "$1" > "$SCRIPT_STATE_FILE"
}

# --- Get SIDs from PMON ---
get_sids_from_pmon() {
  ps -ef | grep pmon | grep -v grep | grep -iv asm | grep -iv apx | awk '{print $8}' | awk -F "_" '{print $3}'
}

# --- Choose SID from list ---
choose_sid() {
  local options=("$@")
  local num_options=${#options[@]}
  if (( num_options == 0 )); then
    log "No SIDs provided to choose from."
    exit 1
  fi
  echo "Please choose a SID:"
  for ((i=0; i<num_options; i++)); do
    echo "$((i+1)) - ${options[$i]}"
  done
  while true; do
    read -p "Enter your choice (1-$num_options): " choice
    if [[ "$choice" =~ ^[1-9][0-9]*$ ]] && (( choice > 0 && choice <= num_options )); then
      echo "${options[$((choice-1))]}"
      return 0
    else
      echo "Invalid choice. Please enter a number between 1 and $num_options."
    fi
  done
}

# --- Check if DB is CDB ---
is_cdb() {
  local sid="$1"
  export ORACLE_SID="$sid"
  local result=$(sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select cdb from v\\$database;
    exit
EOF
  )
  [[ "$result" == "YES" ]]
}

# --- Get datafile path ---
get_datafile_path() {
  local sid="$1"
  export ORACLE_SID="$sid"
  sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select name from v\\$datafile where rownum=1;
    exit
EOF
}

# --- Check open mode ---
get_open_mode() {
  local sid="$1"
  export ORACLE_SID="$sid"
  sqlplus -s "/ as sysdba" <<-EOF
    set heading off feedback off pagesize 0
    select open_mode from v\\$database;
    exit
EOF
}

# --- Execute SQL with error/state check and test mode ---
execute_sql_command() {
  local sql_command="$1"
  local sid="$2"
  export ORACLE_SID="$sid"
  local start_time=$(date +%s)

  if [[ "$TEST_MODE" == "true" ]]; then
    log "[TEST MODE] Would execute SQL: $sql_command for database: $sid"
    sqlplus -s "/ as sysdba" <<-EOF
      select 1 from dual;
      exit;
EOF
    return 0
  fi

  log "Executing SQL command: $sql_command for database: $sid"
  sqlplus -s "/ as sysdba" <<-EOF
    WHENEVER SQLERROR EXIT FAILURE;
    $sql_command;
    exit;
EOF
  local status=$?
  local end_time=$(date +%s)
  local duration=$((end_time - start_time))

  if [[ "$status" -eq 0 ]]; then
    log "SQL command for $sid succeeded."
  else
    log "SQL command for $sid failed."
    exit 1
  fi
  if (( duration > 60 )); then
    log "SQL command took $((duration / 60)) minutes and $((duration % 60)) seconds to complete."
  else
    log "SQL command took $duration seconds to complete."
  fi
  ((TOTAL_TIME+=duration))
}

# --- Choose PDB creation method ---
choose_method() {
  if [[ "$TEST_MODE" == "true" ]]; then
    echo "nocopy"
    log "[TEST MODE] Defaulting method to nocopy"
    return
  fi
  echo "Choose the method for PDB creation:"
  echo "1. nocopy (default)"
  echo "2. copy"
  echo "3. move"
  read -p "Enter your choice (1-3, default 1): " method_choice
  case "$method_choice" in
    2) echo "copy" ;;
    3) echo "move" ;;
    *) echo "nocopy" ;;
  esac
}

# --- Main Script Start ---

> "$LOG_FILE"
log "Script started."
TOTAL_TIME=0

# Check for Oracle environment
if [[ -z "$ORACLE_HOME" ]]; then
  log "Error: ORACLE_HOME must be set."
  exit 1
fi
log "ORACLE_HOME: $ORACLE_HOME"

# Interactive mode prompt
if [[ "$TEST_MODE" == "false" ]]; then
  prompt_user "Do you want to run in interactive mode (yes/no)?"
  [[ "$REPLY" =~ ^[nN] ]] && PROMPT_USER=false
fi
log "Interactive Mode set to: $PROMPT_USER"
log "Test Mode set to: $TEST_MODE"

CURRENT_STATE=$(get_current_state)
log "Current state: $CURRENT_STATE"

# --- Step 1: Find PMON processes and select SIDs ---
if [[ "$CURRENT_STATE" == "START" ]]; then
  log "Step 1: Finding PMON processes."
  prompt_user "Proceed with finding PMON processes?"

  mapfile -t SIDS < <(get_sids_from_pmon)
  PMON_COUNT=${#SIDS[@]}
  log "Found $PMON_COUNT PMON processes: ${SIDS[*]}"

  if (( PMON_COUNT < 2 )); then
    log "Error: Less than 2 PMON processes found. Need a non-CDB and a CDB running."
    exit 1
  fi

  if (( PMON_COUNT > 2 )); then
    log "More than 2 SIDs detected: ${SIDS[*]}"
    log "The script requires 1 CDB and 1 NON CDB for the procedure."
    CDB_SID=$(choose_sid "${SIDS[@]}")
    NONCDB_SID=$(choose_sid "${SIDS[@]/$CDB_SID}")
    log "User chosen CDB_SID: $CDB_SID"
    log "User chosen NONCDB_SID: $NONCDB_SID"
  else
    # Try to auto-detect which is CDB and which is non-CDB
    if is_cdb "${SIDS[0]}"; then
      CDB_SID="${SIDS[0]}"
      NONCDB_SID="${SIDS[1]}"
    else
      CDB_SID="${SIDS[1]}"
      NONCDB_SID="${SIDS[0]}"
    fi
    log "Automatically selected CDB_SID: $CDB_SID"
    log "Automatically selected NONCDB_SID: $NONCDB_SID"
  fi

  set_current_state "SIDS_SELECTED"
  CURRENT_STATE="SIDS_SELECTED"
fi

# --- Step 2: Check CDB vs Non-CDB ---
if [[ "$CURRENT_STATE" == "SIDS_SELECTED" ]]; then
  log "Step 2: Checking CDB vs. Non-CDB."
  prompt_user "Proceed with checking CDB vs. Non-CDB?"

  if ! is_cdb "$CDB_SID"; then
    log "Error: $CDB_SID is not a CDB."
    exit 1
  fi
  if is_cdb "$NONCDB_SID"; then
    log "Error: $NONCDB_SID is a CDB."
    exit 1
  fi

  log "CDB_SID: $CDB_SID"
  log "NONCDB_SID: $NONCDB_SID"
  set_current_state "DB_TYPES_CHECKED"
  CURRENT_STATE="DB_TYPES_CHECKED"
fi

# --- Step 3: Get Datafile Paths ---
if [[ "$CURRENT_STATE" == "DB_TYPES_CHECKED" ]]; then
  log "Step 3: Getting Datafile Paths."
  prompt_user "Proceed with getting datafile paths?"

  NONCDB_DATAFILE_PATH=$(get_datafile_path "$NONCDB_SID")
  if [[ -z "$NONCDB_DATAFILE_PATH" ]]; then
    log "Error getting non-CDB datafile path"
    exit 1
  fi
  log "Non-CDB datafile path: $NONCDB_DATAFILE_PATH"

  CDB_DATAFILE_PATH=$(get_datafile_path "$CDB_SID")
  if [[ -z "$CDB_DATAFILE_PATH" ]]; then
    log "Error getting CDB datafile path"
    exit 1
  fi
  log "CDB datafile path: $CDB_DATAFILE_PATH"

  NONCDB_DIR=$(dirname "$NONCDB_DATAFILE_PATH")
  CDB_DIR=$(dirname "$CDB_DATAFILE_PATH")
  log "Non-CDB directory: $NONCDB_DIR"
  log "CDB directory: $CDB_DIR"

  set_current_state "DATAFILE_PATHS_RETRIEVED"
  CURRENT_STATE="DATAFILE_PATHS_RETRIEVED"
fi

# --- Step 4: Non CDB Preparation ---
if [[ "$CURRENT_STATE" == "DATAFILE_PATHS_RETRIEVED" ]]; then
  log "Step 4: Non-CDB Preparation."
  prompt_user "Proceed with non-CDB preparation?"

  execute_sql_command "alter database open read only" "$NONCDB_SID"
  # Validate open mode
  mode=$(get_open_mode "$NONCDB_SID")
  if [[ "$mode" != *"READ ONLY"* ]]; then
    log "Error: Non-CDB is not in READ ONLY mode after command. Current mode: $mode"
    exit 1
  fi

  execute_sql_command "exec dbms_pdb.describe(pdb_descr_file=>'/tmp/new_pdb.xml');" "$NONCDB_SID"
  execute_sql_command "shutdown immediate;" "$NONCDB_SID"

  set_current_state "NONCDB_PREPARED"
  CURRENT_STATE="NONCDB_PREPARED"
fi

# --- Step 5: Create PDB ---
if [[ "$CURRENT_STATE" == "NONCDB_PREPARED" ]]; then
  log "Step 5: Create PDB."
  prompt_user "Proceed with PDB creation?"

  PDB_NAME=PDBNEW
  NEW_LOCATION="$CDB_DIR/$PDB_NAME"
  METHOD=$(choose_method)

  case "$METHOD" in
    nocopy)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' NOCOPY;" "$CDB_SID"
      ;;
    copy)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' COPY file_name_convert=('$NONCDB_DIR', '$NEW_LOCATION');" "$CDB_SID"
      ;;
    move)
      execute_sql_command "CREATE PLUGGABLE DATABASE $PDB_NAME USING '/tmp/new_pdb.xml' MOVE file_name_convert=('$NONCDB_DIR', '$NEW_LOCATION');" "$CDB_SID"
      ;;
  esac

  set_current_state "PDB_CREATED"
  CURRENT_STATE="PDB_CREATED"
fi

# --- Step 6: Post conversion process ---
if [[ "$CURRENT_STATE" == "PDB_CREATED" ]]; then
  log "Step 6: Post PDB creation process"
  prompt_user "Proceed with post PDB creation process?"

  execute_sql_command "alter pluggable database $PDB_NAME open read write;" "$CDB_SID"
  execute_sql_command "@\$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;" "$PDB_NAME"

  set_current_state "COMPLETE"
  CURRENT_STATE="COMPLETE"
fi

# --- Finalization ---
if [[ "$CURRENT_STATE" == "COMPLETE" ]]; then
  log "Conversion completed successfully."
  log "You should verify that everything is working properly."
  rm -f "$SCRIPT_STATE_FILE"
else
  log "Conversion failed or was interrupted at state: $CURRENT_STATE"
  log "You can restart the script to continue."
fi

if (( TOTAL_TIME > 60 )); then
  log "Total time for the entire procedure: $((TOTAL_TIME / 60)) minutes and $((TOTAL_TIME % 60)) seconds"
else
  log "Total time for the entire procedure: $TOTAL_TIME seconds"
fi

log "Script finished."
exit 0

===raj
#!/bin/bash
# Oracle CPU Capacity Report with Solid Validation
# Version 2.4 - Robust Output Parsing
REPORT_FILE="/tmp/cpu_capacity_report_$(date +%Y%m%d_%H%M%S).txt"
VALIDATION_FILE="/tmp/cpu_validation_$(date +%Y%m%d_%H%M%S).log"
# Improved SQL execution function
get_sql_value() {
    local sql=$1
    local sid=$2
    ORACLE_SID=$sid sqlplus -s / as sysdba <<EOF | awk '/^[[:space:]]*[0-9]/{print $1; exit} /^[[:space:]]*-/{print $2; exit}'
set pagesize 0
set feedback off
set heading off
$sql
EOF
}
# Function to get safe numeric value
safe_number() {
    local num=$1
    if [[ $num =~ ^[+-]?[0-9]+\.?[0-9]*$ ]]; then
        echo "$num"
    else
        echo "0"
    fi
}
# Function to validate calculations
validate_cpu_metrics() {
    local total_cores=$1
    local system_usage=$2
    local db_usage=$3
    local os_usage=$4
    
    echo "CPU METRICS VALIDATION" > $VALIDATION_FILE
    echo "======================" >> $VALIDATION_FILE
    echo "Validation Time: $(date)" >> $VALIDATION_FILE
    echo "" >> $VALIDATION_FILE
    
    # Rule 1: System + Available = Total
    local calculated_total=$(echo "$system_usage + $available_cores" | bc)
    if [ "$(echo "$calculated_total == $total_cores" | bc -l)" -eq 1 ]; then
        echo "[✓] System + Available = Total" >> $VALIDATION_FILE
        echo "    $system_usage (System) + $available_cores (Available) = $total_cores (Total)" >> $VALIDATION_FILE
    else
        echo "[✗] System + Available ≠ Total" >> $VALIDATION_FILE
        echo "    $system_usage + $available_cores = $calculated_total (expected $total_cores)" >> $VALIDATION_FILE
    fi
    
    # Rule 2: DB + OS = System
    local calculated_system=$(echo "$db_usage + $os_usage" | bc)
    if [ "$(echo "$calculated_system == $system_usage" | bc -l)" -eq 1 ]; then
        echo "[✓] DB + OS = System Usage" >> $VALIDATION_FILE
        echo "    $db_usage (DB) + $os_usage (OS) = $system_usage (System)" >> $VALIDATION_FILE
    else
        echo "[✗] DB + OS ≠ System Usage" >> $VALIDATION_FILE
        echo "    $db_usage + $os_usage = $calculated_system (expected $system_usage)" >> $VALIDATION_FILE
        echo "    Difference: $(echo "$system_usage - $calculated_system" | bc) cores unaccounted" >> $VALIDATION_FILE
    fi
    
    # Rule 3: Percentages sum to 100%
    local total_pct=$(echo "$system_usage_pct + $available_pct" | bc)
    if [ "$(echo "$total_pct == 100" | bc -l)" -eq 1 ]; then
        echo "[✓] System% + Available% = 100%" >> $VALIDATION_FILE
        echo "    $system_usage_pct% + $available_pct% = 100%" >> $VALIDATION_FILE
    else
        echo "[✗] System% + Available% ≠ 100%" >> $VALIDATION_FILE
        echo "    $system_usage_pct% + $available_pct% = $total_pct%" >> $VALIDATION_FILE
    fi
    
    echo "" >> $VALIDATION_FILE
    echo "VALIDATION NOTES:" >> $VALIDATION_FILE
    echo "1. Minor discrepancies (<0.5%) may be due to timing differences in metric collection" >> $VALIDATION_FILE
    echo "2. Large differences indicate either measurement errors or unaccounted CPU usage" >> $VALIDATION_FILE
}
# Get system CPU metrics
TOTAL_CORES=$(nproc 2>/dev/null || grep -c ^processor /proc/cpuinfo)
CPU_USAGE_PCT=$(top -bn1 | grep "Cpu(s)" | awk '{print 100 - $8}' | cut -d. -f1)
SYSTEM_CPU=$(echo "scale=2; $CPU_USAGE_PCT * $TOTAL_CORES / 100" | bc)
# Get database CPU usage
DATABASES=$(ps -eo command | grep -E '[p]mon|[_]pmon' | awk -F_ '{print $NF}' | sort | uniq)
TOTAL_DB_CPU=0
for DB in $DATABASES; do
    DB_CPU_CENTISEC=$(get_sql_value "SELECT ROUND(SUM(value)) FROM v\$sysmetric WHERE metric_name='CPU Usage Per Sec' AND group_id=2;" "$DB")
    DB_CPU_CENTISEC=$(safe_number "$DB_CPU_CENTISEC")
    DB_CPU=$(echo "scale=2; $DB_CPU_CENTISEC / 100" | bc)
    TOTAL_DB_CPU=$(echo "$TOTAL_DB_CPU + $DB_CPU" | bc)
done
# Calculate derived metrics
OTHER_CPU=$(echo "scale=2; $SYSTEM_CPU - $TOTAL_DB_CPU" | bc)
AVAILABLE_CPU=$(echo "scale=2; $TOTAL_CORES - $SYSTEM_CPU" | bc)
AVAILABLE_PCT=$(echo "scale=2; 100 - $CPU_USAGE_PCT" | bc)
# Run validation
validate_cpu_metrics "$TOTAL_CORES" "$SYSTEM_CPU" "$TOTAL_DB_CPU" "$OTHER_CPU"
# Generate report
{
echo "ORACLE CPU CAPACITY REPORT WITH VALIDATION"
echo "========================================="
echo "Generated: $(date)"
echo ""
echo "1. SYSTEM RESOURCES"
echo "-------------------"
echo "  Total CPU Cores    : $TOTAL_CORES"
echo "  System CPU Usage   : $SYSTEM_CPU cores ($CPU_USAGE_PCT%)"
echo "  - Database Usage   : $TOTAL_DB_CPU cores"
echo "  - OS/Other Usage   : $OTHER_CPU cores"
echo "  Available Cores    : $AVAILABLE_CPU cores ($AVAILABLE_PCT%)"
echo ""
echo "2. VALIDATION SUMMARY"
echo "---------------------"
grep -E '^\[[✓✗]\]' "$VALIDATION_FILE"
echo ""
echo "3. DETAILED METRICS VALIDATION"
echo "------------------------------"
cat "$VALIDATION_FILE"
} > "$REPORT_FILE"
echo "Report generated: $REPORT_FILE"
echo "Validation details: $VALIDATION_FILE"

#!/bin/bash
# Oracle CPU Capacity Report with Validation
# Version 2.3 - Includes Cross-Verification
REPORT_FILE="/tmp/cpu_capacity_report_$(date +%Y%m%d_%H%M%S).txt"
VALIDATION_FILE="/tmp/cpu_validation_$(date +%Y%m%d_%H%M%S).log"
# Function to validate calculations
validate_cpu_metrics() {
    local total_cores=$1
    local system_usage=$2
    local db_usage=$3
    local os_usage=$4
    
    echo "VALIDATION REPORT" > $VALIDATION_FILE
    echo "=================" >> $VALIDATION_FILE
    echo "Timestamp: $(date)" >> $VALIDATION_FILE
    echo "" >> $VALIDATION_FILE
    
    # Rule 1: System + Available should equal Total
    local calculated_total=$(echo "$system_usage + $available_cores" | bc)
    if [ "$(echo "$calculated_total == $total_cores" | bc)" -eq 1 ]; then
        echo "[PASS] System + Available = Total: $system_usage + $available_cores = $total_cores" >> $VALIDATION_FILE
    else
        echo "[FAIL] System + Available ($system_usage + $available_cores) != Total ($total_cores)" >> $VALIDATION_FILE
    fi
    
    # Rule 2: DB + OS should equal System
    local calculated_system=$(echo "$db_usage + $os_usage" | bc)
    if [ "$(echo "$calculated_system == $system_usage" | bc)" -eq 1 ]; then
        echo "[PASS] DB + OS = System: $db_usage + $os_usage = $system_usage" >> $VALIDATION_FILE
    else
        echo "[FAIL] DB + OS ($db_usage + $os_usage) != System ($system_usage)" >> $VALIDATION_FILE
        echo "  Difference: $(echo "$system_usage - ($db_usage + $os_usage)" | bc) cores" >> $VALIDATION_FILE
    fi
    
    # Rule 3: Percentages should sum to 100%
    local total_pct=$(echo "$system_usage_pct + $available_pct" | bc)
    if [ "$(echo "$total_pct == 100" | bc)" -eq 1 ]; then
        echo "[PASS] System% + Available% = 100%" >> $VALIDATION_FILE
    else
        echo "[FAIL] System% ($system_usage_pct) + Available% ($available_pct) = $total_pct%" >> $VALIDATION_FILE
    fi
    
    echo "" >> $VALIDATION_FILE
    echo "Note: Small discrepancies may occur due to timing differences in metric collection" >> $VALIDATION_FILE
}
# Get accurate CPU metrics
TOTAL_CORES=$(grep -c ^processor /proc/cpuinfo)
CPU_USAGE_PCT=$(top -bn1 | grep "Cpu(s)" | sed "s/.*, *\([0-9.]*\)%* id.*/\1/" | awk '{print 100 - $1}')
SYSTEM_CPU=$(echo "scale=2; $CPU_USAGE_PCT * $TOTAL_CORES / 100" | bc)
# Get database CPU usage
DATABASES=$(ps -eo command | grep -E '[p]mon|[_]pmon' | awk -F_ '{print $NF}' | sort | uniq)
TOTAL_DB_CPU=0
for DB in $DATABASES; do
    export ORACLE_SID=$DB
    DB_CPU_CENTISEC=$(sqlplus -s / as sysdba <<EOF | awk '/^ / {print $1}'
set pagesize 0
set feedback off
set heading off
SELECT ROUND(SUM(value)) 
FROM v\$sysmetric 
WHERE metric_name='CPU Usage Per Sec' 
AND group_id=2;
EOF
)
    [ -z "$DB_CPU_CENTISEC" ] && DB_CPU_CENTISEC=0
    TOTAL_DB_CPU=$(echo "scale=2; $TOTAL_DB_CPU + ($DB_CPU_CENTISEC / 100)" | bc)
done
# Calculate derived metrics
OTHER_CPU=$(echo "scale=2; $SYSTEM_CPU - $TOTAL_DB_CPU" | bc)
AVAILABLE_CPU=$(echo "scale=2; $TOTAL_CORES - $SYSTEM_CPU" | bc)
AVAILABLE_PCT=$(echo "scale=2; 100 - $CPU_USAGE_PCT" | bc)
# Run validation
validate_cpu_metrics $TOTAL_CORES $SYSTEM_CPU $TOTAL_DB_CPU $OTHER_CPU
# Generate report
echo "CPU CAPACITY REPORT WITH VALIDATION" > $REPORT_FILE
echo "===================================" >> $REPORT_FILE
echo "Generated: $(date)" >> $REPORT_FILE
echo "" >> $REPORT_FILE
echo "1. SYSTEM RESOURCES" >> $REPORT_FILE
echo "-------------------" >> $REPORT_FILE
echo "  Total CPU Cores    : $TOTAL_CORES" >> $REPORT_FILE
echo "  System CPU Usage   : $SYSTEM_CPU cores ($CPU_USAGE_PCT%)" >> $REPORT_FILE
echo "  - Database Usage   : $TOTAL_DB_CPU cores" >> $REPORT_FILE
echo "  - OS/Other Usage   : $OTHER_CPU cores" >> $REPORT_FILE
echo "  Available Cores    : $AVAILABLE_CPU cores ($AVAILABLE_PCT%)" >> $REPORT_FILE
echo "" >> $REPORT_FILE
echo "2. VALIDATION SUMMARY" >> $REPORT_FILE
echo "---------------------" >> $REPORT_FILE
grep -E '^\[(PASS|FAIL)\]' $VALIDATION_FILE >> $REPORT_FILE
echo "" >> $REPORT_FILE
echo "3. DETAILED VALIDATION" >> $REPORT_FILE
echo "----------------------" >> $REPORT_FILE
cat $VALIDATION_FILE >> $REPORT_FILE
echo "" >> $REPORT_FILE
echo "Report generated: $REPORT_FILE" >> $REPORT_FILE
echo "Validation log: $VALIDATION_FILE" >> $REPORT_FILE
echo "CPU report with validation generated:"
echo $REPORT_FILE
echo "Validation details:"
echo $VALIDATION_FILE

RAJ===

#!/bin/bash
# filepath: /path/to/cpu_analysis.sh
# Set environment variables
LOG_FOLDER="/x/home/oracle/admin/scripts/logs/cpu_analysis"
mkdir -p "$LOG_FOLDER"
# Oracle environment variables (update as per your environment)
source ~/.profile
# Temporary files
TOTAL_CPUS_FILE="$LOG_FOLDER/total_cpus.txt"
SYSTEM_CPU_USAGE_FILE="$LOG_FOLDER/system_cpu_usage.txt"
DATABASE_COUNT_FILE="$LOG_FOLDER/database_count.txt"
DATABASE_LIST_FILE="$LOG_FOLDER/database_list.txt"
DATABASE_CPU_USAGE_FILE="$LOG_FOLDER/database_cpu_usage.txt"
CAPACITY_FILE="$LOG_FOLDER/capacity.txt"
# Function to get total number of CPUs on the host
get_total_cpus() {
    grep -c ^processor /proc/cpuinfo > "$TOTAL_CPUS_FILE"
}
# Function to get system-level CPU usage
get_system_cpu_usage() {
    mpstat -P ALL 1 1 | awk '/all/ {print 100 - $NF}' > "$SYSTEM_CPU_USAGE_FILE"
}
# Function to get the number of databases on the host
get_number_of_databases() {
    sqlplus -s / as sysdba <<EOF > "$DATABASE_COUNT_FILE"
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT COUNT(*) FROM v\$database;
EXIT;
EOF
}
# Function to get list of databases
get_database_list() {
    sqlplus -s / as sysdba <<EOF > "$DATABASE_LIST_FILE"
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT name FROM v\$database;
EXIT;
EOF
}
# Function to get CPU usage for each database
get_database_cpu_usage() {
    databases=$(cat "$DATABASE_LIST_FILE")
    > "$DATABASE_CPU_USAGE_FILE"
    for db in $databases; do
        echo "Database: $db" >> "$DATABASE_CPU_USAGE_FILE"
        sqlplus -s / as sysdba <<EOF >> "$DATABASE_CPU_USAGE_FILE"
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT ROUND(SUM(VALUE) / COUNT(*), 2) FROM v\$sysmetric WHERE METRIC_NAME = 'Host CPU Utilization (%)' AND BEGIN_TIME > SYSDATE - 1/24;
EXIT;
EOF
    done
}
# Function to calculate current available capacity
calculate_capacity() {
    total_cpus=$(cat "$TOTAL_CPUS_FILE")
    used_capacity=$(cat "$SYSTEM_CPU_USAGE_FILE")
    available_capacity=$(echo "$total_cpus - $used_capacity" | bc)
    echo "Current available capacity (CPUs): $available_capacity" > "$CAPACITY_FILE"
    echo "Total used capacity (CPUs): $used_capacity" >> "$CAPACITY_FILE"
}
# Function to print the report
print_report() {
    echo "==================== CPU Analysis Report ===================="
    echo "Date: $(date)"
    echo "-------------------------------------------------------------"
    echo "Total number of CPUs on the host: $(cat "$TOTAL_CPUS_FILE")"
    echo "-------------------------------------------------------------"
    echo "System CPU Usage:"
    echo "  - Number of CPUs currently in use: $(cat "$SYSTEM_CPU_USAGE_FILE")"
    echo "  - Percentage of CPU being used: $(cat "$SYSTEM_CPU_USAGE_FILE")%"
    echo "-------------------------------------------------------------"
    echo "Number of databases on the host: $(cat "$DATABASE_COUNT_FILE")"
    echo "-------------------------------------------------------------"
    echo "Database CPU Usage:"
    cat "$DATABASE_CPU_USAGE_FILE" | while read line; do
        echo "  $line"
    done
    echo "-------------------------------------------------------------"
    echo "Capacity Analysis:"
    cat "$CAPACITY_FILE" | while read line; do
        echo "  $line"
    done
    echo "============================================================="
}
# Main function
main() {
    # Get total number of CPUs
    get_total_cpus
    # Get system-level CPU usage
    get_system_cpu_usage
    # Get number of databases
    get_number_of_databases
    # Get CPU usage for each database
    get_database_list
    get_database_cpu_usage
    # Calculate current available capacity
    calculate_capacity
    # Print the report
    print_report
}
# Run the main function
main
 

DECLARE
    users_to_lock SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('abc', 'def', 'ghi', 'jkl', 'mno');
    account_status VARCHAR2(30);
BEGIN
    FOR i IN 1 .. users_to_lock.COUNT LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER USER ' || users_to_lock(i) || ' ACCOUNT LOCK';

            -- Check the account status after attempting to lock
            SELECT account_status INTO account_status
            FROM dba_users
            WHERE username = UPPER(users_to_lock(i));

            IF account_status = 'LOCKED' THEN
                DBMS_OUTPUT.PUT_LINE('User ' || users_to_lock(i) || ' is now locked.');
            ELSE
                DBMS_OUTPUT.PUT_LINE('User ' || users_to_lock(i) || ' is not locked.');
            END IF;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('User ' || users_to_lock(i) || ' does not exist.');
            WHEN OTHERS THEN
                IF SQLCODE = -1918 THEN
                    DBMS_OUTPUT.PUT_LINE('User ' || users_to_lock(i) || ' does not exist.');
                ELSE
                    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred for user ' || users_to_lock(i) || '.');
                END IF;
        END;
    END LOOP;
END;
/

DECLARE
    users_to_check SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('abc', 'def', 'ghi', 'jkl', 'mno');
    account_status VARCHAR2(30);
BEGIN
    FOR i IN 1 .. users_to_check.COUNT LOOP
        BEGIN
            SELECT account_status INTO account_status
            FROM dba_users
            WHERE username = UPPER(users_to_check(i));

            DBMS_OUTPUT.PUT_LINE('User ' || users_to_check(i) || ' is ' || account_status || '.');
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('User ' || users_to_check(i) || ' does not exist.');
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('An unexpected error occurred for user ' || users_to_check(i) || '.');
        END;
    END LOOP;
END;
/

 Yes, you can achieve this by checking if the user exists before attempting to lock the account. Here's how you can modify your SQL to handle this and send it as a single statement:

sql

BEGIN

EXECUTE IMMEDIATE 'ALTER USER abc ACCOUNT LOCK';

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -1918 THEN

DBMS_OUTPUT.PUT_LINE('User abc does not exist.');

ELSE

RAISE; -- Re-raise other exceptions

END IF;

END;

/

BEGIN

EXECUTE IMMEDIATE 'ALTER USER def ACCOUNT LOCK';

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -1918 THEN

DBMS_OUTPUT.PUT_LINE('User def does not exist.');

ELSE

RAISE; -- Re-raise other exceptions

END IF;

END;

/

Explanation:

BEGIN...END; Blocks: We enclose each ALTER USER statement within a PL/SQL block. This allows us to handle exceptions for each user individually.

EXECUTE IMMEDIATE: We use dynamic SQL (EXECUTE IMMEDIATE) to execute the ALTER USER command. This is necessary because we're embedding SQL within PL/SQL.

Exception Handling: The EXCEPTION block catches any errors that occur during the execution of the ALTER USER command.

WHEN OTHERS: This handles all exceptions.

IF SQLCODE = -1918 THEN: We check if the specific error ORA-01918 (user not found) occurred.

DBMS_OUTPUT.PUT_LINE(...): If the user doesn't exist, we print a message to the output (you'll need to enable server output to see this message, e.g., SET SERVEROUTPUT ON; in SQL*Plus or SQL Developer). You could replace this with other actions like logging to a table or raising a different exception.

ELSE RAISE;: For any other exception besides ORA-01918, we re-raise the exception so that it's not swallowed and can be handled by a higher-level error handler if needed.

/: This is a SQL*Plus or SQL Developer command that separates PL/SQL blocks and tells the database to execute the preceding block.

How to send it:

You would send the entire block of code above as a single SQL statement to your $sql1 and $sql2 variables. The database will execute each PL/SQL block sequentially. Because of the error handling, if a user doesn't exist, the code will continue to the next block without stopping. It will only attempt to lock users that actually exist (or at least, it will handle the "user not found" error gracefully if they don't).

Rate this answer:

Mattewada Rajkiran

can you do it in one block for 5 users?

Gemini

Sure, I can consolidate that into a single block for five users! Here's how you can do it:

sql

BEGIN

FOR user_name IN ('abc', 'def', 'user3', 'user4', 'user5') LOOP

BEGIN

EXECUTE IMMEDIATE 'ALTER USER ' || user_name || ' ACCOUNT LOCK';

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -1918 THEN

DBMS_OUTPUT.PUT_LINE('User ' || user_name || ' does not exist.');

ELSE

RAISE; -- Re-raise other exceptions

END IF;

END;

END LOOP;

END;

/

This approach uses a FOR loop to iterate through a list of usernames. Within the loop, it attempts to lock each user's account. The exception handling remains the same, specifically addressing the ORA-01918 error. If a user doesn't exist, it prints a message; otherwise, it moves on to the next user in the list. Now, you can provide all five usernames in a single, concise block!

To execute multiple SQL statements in one go and handle cases where a user might not exist, you can use PL/SQL blocks with conditional logic. Here's how you can achieve this:

BEGIN

-- Attempt to lock user 'abc'

BEGIN

EXECUTE IMMEDIATE 'ALTER USER abc ACCOUNT LOCK';

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -1918 THEN

DBMS_OUTPUT.PUT_LINE('User abc does not exist.');

ELSE

RAISE;

END IF;

END;

-- Attempt to lock user 'def'

BEGIN

EXECUTE IMMEDIATE 'ALTER USER def ACCOUNT LOCK';

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -1918 THEN

DBMS_OUTPUT.PUT_LINE('User def does not exist.');

ELSE

RAISE;

END IF;

END;

END;

/

Explanation:

PL/SQL Block: The entire operation is wrapped in a PL/SQL block, allowing for procedural logic.

EXECUTE IMMEDIATE: This is used to execute dynamic SQL statements.

Exception Handling: Each ALTER USER statement is wrapped in its own BEGIN...EXCEPTION...END block to handle exceptions individually.

SQLCODE -1918: This specific error code corresponds to ORA-01918, which indicates that the user does not exist. The block checks for this error and handles it gracefully by printing a message.

DBMS_OUTPUT.PUT_LINE: This is used to output messages. Ensure that DBMS_OUTPUT is enabled in your SQL client to see these messages.

Usage:

You can send this entire block to your SQL execution environment (e.g., SQL*Plus, SQL Developer) as a single command.

This approach ensures that each user is only locked if they exist, and it provides feedback if a user does not exist.

Is this helpful?

can you do it in one block for 5 users? instead of multiple $sql1 $sq2, etc.,

Certainly! You can handle multiple users in a single PL/SQL block by iterating over a list of usernames and applying the same logic to each. Here's how you can do it for five users:

DECLARE

users_to_lock SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('abc', 'def', 'ghi', 'jkl', 'mno');

BEGIN

FOR i IN 1 .. users_to_lock.COUNT LOOP

BEGIN

EXECUTE IMMEDIATE 'ALTER USER ' || users_to_lock(i) || ' ACCOUNT LOCK';

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -1918 THEN

DBMS_OUTPUT.PUT_LINE('User ' || users_to_lock(i) || ' does not exist.');

ELSE

RAISE;

END IF;

END;

END LOOP;

END;

/

Explanation:

SYS.ODCIVARCHAR2LIST: This is a collection type that can hold a list of strings. It's used here to store the usernames you want to lock.

FOR LOOP: Iterates over each username in the list.

EXECUTE IMMEDIATE: Dynamically constructs and executes the ALTER USER statement for each username.

Exception Handling: Catches the ORA-01918 error for non-existent users and outputs a message using DBMS_OUTPUT.PUT_LINE.

DBMS_OUTPUT.PUT_LINE: Outputs messages for each user that does not exist. Ensure DBMS_OUTPUT is enabled in your SQL client to see these messages.

Usage:

You can execute this block in your SQL environment as a single command.

This approach efficiently handles multiple users in one block, checking for existence and locking accounts as needed.

To enhance the document for the "move" method in the context of converting a non-CDB to a CDB, we need to outline the specific steps, benefits, and considerations associated with this approach. The "move" method involves relocating datafiles without copying them, similar to the NOCOPY method, but with additional considerations for file management and metadata updates.

Enhanced Document: Non-CDB to CDB Conversion Using MOVE Method

Internal Actions:

1. CREATE PLUGGABLE DATABASE Statement:

   • This command is used within the CDB to initiate the creation of the new PDB using the MOVE method.

2. XML File Parsing:

   • Oracle reads and parses the new_pdb.xml file generated earlier to understand the structure and metadata of the non-CDB.

3. PDB Creation:

   • A new PDB entry is created in the CDB's data dictionary.

4. Tablespace and Datafile Setup:

   • Based on the XML file:
     • New tablespaces are created within the CDB to match the tablespaces found in the XML.
     • Datafiles are moved from the non-CDB location to the CDB location, updating paths as necessary.

5. Metadata Update:

   • Oracle updates the metadata in the new PDB to reference the moved datafiles. The path is modified using the file_name_convert clause.

6. Shared Files:

   • The PDB now uses the moved datafiles, which are no longer associated with the original non-CDB.

7. Control File:

   • The PDB will get its own control file, separate from the non-CDB.

8. Object Recreation:

   • Database objects (tables, views, etc.) are recreated inside the PDB based on the XML metadata.

9. User Accounts:

   • User accounts, roles, and privileges are created and assigned within the PDB.

10. PDB Status:

    • The new PDB is created in a MOUNTED state. It is not yet OPEN.

11. Log Files:

    • The new PDB will have its own redo log files, separate from the CDB and other PDBs.

12. Undo Tablespace:

    • The new PDB will have its own undo tablespace.

13. Temporary Tablespace:

    • The new PDB will have its own temporary tablespace.

14. Original Non-CDB Data Files:

    • The original non-CDB data files are now considered to belong to the PDB.

Pros:

• Speed: The conversion is fast because files are moved rather than copied.
• Storage Efficiency: No additional storage space is required, as the original datafiles are moved.

Cons:

• Risk to Original Data: The PDB now uses the moved non-CDB datafiles. Any corruption within the PDB could affect the data within the moved files.
• Complex Rollback: Rolling back to the original non-CDB is significantly more complex and risky.
• Interdependency: If the PDB is dropped, the files are also dropped, as they are now considered to belong to the PDB.

Rollback for MOVE

Warning: Rollback for MOVE is complicated and carries a significant risk of data loss if not performed carefully.

1. Stop and Drop the PDB:

   • If the PDB is open, you must close it.
   • Drop the PDB, including datafiles.

2. Verification (Critical):

   • Check oldspec: Even though you executed the drop including datafiles, the files under oldspec were dropped. They were considered part of the PDB.

3. Recover from Backup:

   • If the files were dropped, you have to restore them from backup. If you didn't make a backup of the files, there is no way to recover them.

4. Recover to a New Name:

   • If you don't have a backup of the non-CDB, you can try to start the non-CDB as a new database, with a new name.

5. Clean Up:

   • Remove any new files created, like log files and undo tablespace.
   • Try to start the non-CDB again.

6. Phase 3: Post-Creation Cleanup:

   • Run a cleanup to get rid of non-CDB items in the new pluggable database using @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql.

Internal Actions:

• Script Execution: The noncdb_to_pdb.sql script is designed to remove remnants of the non-CDB structure from the newly created PDB.
• SYS Connection: This script needs to be run connected as a user with SYSDBA privileges, and connected to the PDB.
• Script Actions: The specific actions in the script are version-dependent, but they generally include:
  • Invalidating Obsolete Objects: It might drop or invalidate some objects that are no longer relevant in the PDB environment.
  • Removing Non-PDB User: It likely removes the database user SYSBACKUP, as well as other database objects that are no longer used in a PDB environment.
  • Updating Dictionary: It modifies some data dictionary information to correctly reflect the PDB's identity.
  • Resource Management: It modifies some parameters in the database to suit it to the new PDB environment.
  • Cleaning up: Removes data, settings, and items that are no longer used.
  • Recompiles: Recompile invalid objects.

Why it is Important:

This step is vital for a clean conversion. It ensures that the PDB is properly integrated into the CDB environment and doesn't retain unnecessary or conflicting information from the original non-CDB. This cleanup script should be run in the PDB, and not in the CDB.

Method 1: COPY vs Method 2: NOCOPY vs Method 3: MOVE - Summary Table

Feature	COPY	NOCOPY	MOVE
Datafiles	Copied to new location.	Reused from the original non-CDB.	Moved from original non-CDB.
Speed	Slower (due to file copy).	Faster (no file copy).	Fast (file move).
Storage	Requires double the storage initially.	No extra storage initially.	No extra storage initially.
Original Data	Isolated. Changes in PDB do not affect non-CDB data.	Shared. Changes in PDB can affect non-CDB data (risky).	Shared. Changes in PDB can affect non-CDB data (risky).
Rollback	Easier: Drop PDB, copied files are removed. Original files untouched.	Difficult & Risky: Requires careful manual intervention. Data loss possible. ORA-01122 errors likely.	Difficult & Risky: Requires careful manual intervention. Data loss possible.
Risk	Lower risk to original non-CDB data.	Higher risk to original non-CDB data.	Higher risk to original non-CDB data.
File removal	If the PDB is dropped, only the copied files are removed.	If the PDB is dropped, all files from the old non-CDB are removed.	If the PDB is dropped, all files from the old non-CDB are removed.
Dependencies	No dependencies with the non-CDB data files.	Strong dependency with non-CDB data files.	Strong dependency with non-CDB data files.
ORA errors	No errors will happen with the original non-CDB if you shut down the CDB and try to start it.	ORA-01122, ORA-01110, ORA-01204 will happen if you shut down the CDB and try to start the non-CDB.	ORA-01122, ORA-01110, ORA-01204 will happen if you shut down the CDB and try to start the non-CDB.

Key Points & Considerations:

• Datafile Management: The MOVE method involves relocating datafiles, which requires careful management to ensure paths are updated correctly.
• file_name_convert is Essential: This clause is critical for relocating datafiles to the correct location in the CDB.
• Metadata vs. Data: The DBMS_PDB.DESCRIBE procedure only extracts metadata. The data itself is moved during the CREATE PLUGGABLE DATABASE process.
• Downtime: The non-CDB is unavailable during the SHUTDOWN and data file move. The time required for the move is directly related to the size of the non-CDB's datafiles.
• CDB Requirements: The CDB must have sufficient disk space for the new PDB's datafiles, and it must be running and accessible.
• Non-CDB files: After the conversion, you might decide to keep the non-CDB files as a backup, or remove them (only if you used COPY).
• Compatibility: The Oracle version of the CDB should be the same or later than the non-CDB.
• Backup: Always take a backup of the files before using MOVE.

This enhanced document provides a comprehensive overview of the MOVE method for converting a non-CDB to a CDB, highlighting the internal actions, pros and cons, rollback procedures, and key considerations.

Migrating a non-CDB with Transparent Data Encryption (TDE) enabled to a CDB involves several steps to ensure that encryption keys and encrypted data are properly handled. TDE is used to encrypt data at rest, and it's crucial to manage the encryption keys during the migration process. Below are detailed steps and commands to facilitate this migration:

Pre-Migration Steps

1. Verify TDE Configuration:

   • Ensure that TDE is properly configured in the non-CDB. Check the status of the wallet and the encryption keys.

   SELECT * FROM V$ENCRYPTION_WALLET;
   

2. Backup the Wallet:

   • Backup the wallet directory from the non-CDB. This is crucial as the wallet contains the encryption keys.

   cp -r $ORACLE_BASE/admin/<non-cdb>/wallet /backup/location/
   

3. Backup the Database:

   • Perform a full backup of the non-CDB to ensure data safety.

Migration Steps

1. Prepare the CDB Environment:

   • Ensure that the CDB is configured to use TDE. If not, set up TDE in the CDB.

   ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/path/to/cdb/wallet' IDENTIFIED BY "password";
   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "password";
   ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "password" WITH BACKUP;
   

2. Copy the Wallet:

   • Copy the wallet from the non-CDB to the CDB's wallet location.

   cp -r /backup/location/wallet /path/to/cdb/wallet
   

3. Open the Wallet in CDB:

   • Open the wallet in the CDB to access the encryption keys.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "password";
   

4. Create Pluggable Database:

   • Use the CREATE PLUGGABLE DATABASE command to migrate the non-CDB to a PDB within the CDB.

   CREATE PLUGGABLE DATABASE pdb_name USING '/path/to/new_pdb.xml' NOCOPY
   FILE_NAME_CONVERT = ('/path/to/non-cdb/datafiles', '/path/to/cdb/datafiles');
   

5. Verify Encryption in PDB:

   • Check that the encryption keys are accessible and that encrypted data is intact in the new PDB.

   SELECT * FROM V$ENCRYPTION_WALLET;
   SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES WHERE ENCRYPTED = 'YES';
   

Post-Migration Steps

1. Close the Wallet:

   • After verifying the migration, close the wallet in the CDB.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "password";
   

2. Backup the CDB Wallet:

   • Backup the wallet in the CDB to ensure encryption keys are safe.

   cp -r /path/to/cdb/wallet /backup/location/
   

3. Test the PDB:

   • Perform tests to ensure that the PDB is functioning correctly and that encrypted data is accessible.

4. Cleanup:

   • Remove any temporary files or backups that are no longer needed.

Additional Considerations

• Compatibility: Ensure that the Oracle version of the CDB supports TDE and is compatible with the non-CDB.
• Security: Maintain security protocols during the migration, especially when handling encryption keys and wallets.
• Documentation: Document each step of the migration process for future reference and auditing purposes.

By following these detailed steps, you can successfully migrate a non-CDB with TDE enabled to a CDB, ensuring that encryption keys and encrypted data are properly managed throughout the process.

Draj

#!/bin/bash

# Oracle CPU Capacity Report Generator

# Works for both Exadata and non-Exadata systems

# Version 1.0

# Configuration

OUTPUT_DIR="/tmp/oracle_cpu_reports"

REPORT_DATE=$(date +%Y%m%d_%H%M%S)

REPORT_FILE="$OUTPUT_DIR/cpu_capacity_report_$REPORT_DATE.txt"

ORACLE_ENV="/etc/oratab" # Default oratab location

# Create output directory

mkdir -p "$OUTPUT_DIR"

# Function to check if Exadata

is_exadata() {

if grep -q "Exadata" /etc/oracle-release 2>/dev/null ||

dmidecode | grep -q "Exadata" 2>/dev/null ||

[ -d /opt/oracle.cell ]; then

return 0

else

return 1

fi

}

# Function to run SQL and format output

run_sql() {

local sql=$1

local conn=$2

echo -e "\n$sql" >> "$REPORT_FILE"

if [ "$conn" == "sys" ]; then

sqlplus -s / as sysdba << EOF >> "$REPORT_FILE"

set pagesize 1000

set linesize 200

set heading on

set feedback off

$sql

EOF

else

sqlplus -s "/ as sysoper" << EOF >> "$REPORT_FILE"

set pagesize 1000

set linesize 200

set heading on

set feedback off

$sql

EOF

fi

}

# Start report

echo "Oracle CPU Capacity Report - $(date)" > "$REPORT_FILE"

echo "====================================" >> "$REPORT_FILE"

# System Overview

echo -e "\n1. SYSTEM OVERVIEW" >> "$REPORT_FILE"

echo "1.1. Host Information" >> "$REPORT_FILE"

echo "---------------------" >> "$REPORT_FILE"

hostnamectl >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

echo "1.2. CPU Information" >> "$REPORT_FILE"

echo "--------------------" >> "$REPORT_FILE"

lscpu >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

echo "1.3. Memory Information" >> "$REPORT_FILE"

echo "-----------------------" >> "$REPORT_FILE"

free -h >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

echo "1.4. Uptime and Load Average" >> "$REPORT_FILE"

echo "----------------------------" >> "$REPORT_FILE"

uptime >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

echo "1.5. Top Processes by CPU" >> "$REPORT_FILE"

echo "-------------------------" >> "$REPORT_FILE"

ps -eo pid,user,pcpu,pmem,cmd --sort=-pcpu | head -20 >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

# Check if Exadata

if is_exadata; then

echo "1.6. Exadata Detected - Additional Metrics" >> "$REPORT_FILE"

echo "------------------------------------------" >> "$REPORT_FILE"

echo "Exadata storage servers:" >> "$REPORT_FILE"

cellcli -e list cell detail >> "$REPORT_FILE" 2>/dev/null || echo "Could not query cellcli" >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

fi

# Oracle Database CPU Usage

echo -e "\n2. ORACLE DATABASE CPU USAGE" >> "$REPORT_FILE"

# Get list of running databases from oratab

DATABASES=$(grep -v '^#' $ORACLE_ENV | grep -v '^$' | cut -d: -f1)

for DB in $DATABASES; do

export ORACLE_SID=$DB

echo -e "\n2.1. Database: $DB" >> "$REPORT_FILE"

echo "=========================" >> "$REPORT_FILE"

# High-level CPU usage

SQL_HIGHLEVEL="

SELECT

TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as report_time,

instance_name,

ROUND(value/100, 2) as cpu_cores_used,

(SELECT ROUND(value/100, 2) FROM v\$sysmetric WHERE metric_name='CPU Usage Per Sec' AND group_id=2) as cpu_cores_used_sysmetric,

(SELECT ROUND(utilization, 2) FROM v\$sysmetric_summary WHERE metric_name='CPU Usage Per Sec') as cpu_pct

FROM v\$sysmetric

WHERE metric_name='CPU Usage Per Sec'

AND group_id=2;"

run_sql "$SQL_HIGHLEVEL" "sys"

# Top SQL by CPU

SQL_TOPSQL="

SELECT

sql_id,

executions,

ROUND(elapsed_time/1000000, 2) as elapsed_sec,

ROUND(cpu_time/1000000, 2) as cpu_sec,

ROUND(buffer_gets/decode(executions,0,1,executions)) as buffer_gets_per_exec,

SUBSTR(sql_text, 1, 100) as sql_text_sample

FROM (

SELECT sql_id, executions, elapsed_time, cpu_time, buffer_gets, sql_text,

RANK() OVER (ORDER BY cpu_time DESC) as cpu_rank

FROM v\$sqlstats

WHERE executions > 0)

WHERE cpu_rank <= 10;"

run_sql "$SQL_TOPSQL" "sys"

# CPU usage history

SQL_HISTORY="

SELECT

TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') as time,

ROUND(value/100, 2) as cpu_cores_used

FROM v\$sysmetric_history

WHERE metric_name='CPU Usage Per Sec'

AND group_id=2

AND begin_time > SYSDATE - 1/24 -- Last hour

ORDER BY begin_time;"

run_sql "$SQL_HISTORY" "sys"

done

# Detailed CPU Analysis

echo -e "\n3. DETAILED CPU ANALYSIS" >> "$REPORT_FILE"

echo "3.1. CPU Usage History (sar)" >> "$REPORT_FILE"

echo "----------------------------" >> "$REPORT_FILE"

sar -u >> "$REPORT_FILE" 2>/dev/null || echo "sar command not available" >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

echo "3.2. CPU Usage by Core" >> "$REPORT_FILE"

echo "----------------------" >> "$REPORT_FILE"

mpstat -P ALL >> "$REPORT_FILE" 2>/dev/null || echo "mpstat command not available" >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

# Exadata-specific reports

if is_exadata; then

echo -e "\n4. EXADATA-SPECIFIC METRICS" >> "$REPORT_FILE"

echo "4.1. Cell CPU Utilization" >> "$REPORT_FILE"

echo "-------------------------" >> "$REPORT_FILE"

cellcli -e "list metriccurrent where name = 'CD_IO_UTIL' or name = 'CD_CPU_UTIL'" >> "$REPORT_FILE" 2>/dev/null

echo "4.2. Smart Scan Efficiency" >> "$REPORT_FILE"

echo "-------------------------" >> "$REPORT_FILE"

SQL_EXADATA="

SELECT

name,

value

FROM v\$sysstat

WHERE name LIKE '%cell%'

AND (name LIKE '%offload%' OR name LIKE '%smart scan%');"

run_sql "$SQL_EXADATA" "sys"

fi

# Recommendations

echo -e "\n5. RECOMMENDATIONS" >> "$REPORT_FILE"

echo "5.1. Immediate Actions" >> "$REPORT_FILE"

echo "----------------------" >> "$REPORT_FILE"

echo "- Review top CPU-consuming SQL statements identified in section 2" >> "$REPORT_FILE"

echo "- Check for runaway processes in section 1.5" >> "$REPORT_FILE"

echo "" >> "$REPORT_FILE"

echo "5.2. Long-term Considerations" >> "$REPORT_FILE"

echo "-----------------------------" >> "$REPORT_FILE"

echo "- Consider database consolidation if multiple instances show high CPU" >> "$REPORT_FILE"

echo "- Evaluate hardware upgrades based on peak CPU usage metrics" >> "$REPORT_FILE"

if is_exadata; then

echo "- Review Exadata storage cell CPU utilization for potential rebalancing" >> "$REPORT_FILE"

fi

# Final summary

echo -e "\nREPORT GENERATION COMPLETE" >> "$REPORT_FILE"

echo "Report saved to: $REPORT_FILE" >> "$REPORT_FILE"

# Print completion message

echo "CPU capacity report generated successfully at $REPORT_FILE"

DRAJ2

#!/bin/bash # Oracle CPU Capacity Report - Vertical Format # Works for both Exadata and non-Exadata # Version 2.0 - Simplified Output # Configuration REPORT_FILE="/tmp/cpu_capacity_report_$(date +%Y%m%d_%H%M%S).txt" ORACLE_ENV="/etc/oratab" # Function to check if Exadata is_exadata() { if grep -q "Exadata" /etc/oracle-release 2>/dev/null || dmidecode | grep -q "Exadata" 2>/dev/null || [ -d /opt/oracle.cell ]; then return 0 else return 1 fi } # Function to run SQL and get single value get_sql_value() { local sql=$1 local conn=$2 if [ "$conn" == "sys" ]; then sqlplus -s / as sysdba << EOF | awk '/^ / {print $1}' set pagesize 0 set feedback off set heading off $sql EOF else sqlplus -s "/ as sysoper" << EOF | awk '/^ / {print $1}' set pagesize 0 set feedback off set heading off $sql EOF fi } # Header echo "==============================================" > $REPORT_FILE echo " CPU CAPACITY REPORT - $(hostname)" >> $REPORT_FILE echo " Generated: $(date)" >> $REPORT_FILE echo "==============================================" >> $REPORT_FILE echo "" >> $REPORT_FILE # 1. System CPU Overview echo "1. HOST CPU RESOURCES" >> $REPORT_FILE echo "---------------------" >> $REPORT_FILE # Get CPU info TOTAL_CORES=$(grep -c ^processor /proc/cpuinfo) TOTAL_THREADS=$(lscpu | grep 'Thread(s) per core' | awk '{print $4}') TOTAL_SOCKETS=$(lscpu | grep 'Socket(s)' | awk '{print $2}') CPU_MODEL=$(lscpu | grep 'Model name' | cut -d':' -f2 | sed 's/^[ \t]*//') # Get CPU usage (1 min avg) CPU_LOAD=$(awk '{print $1}' /proc/loadavg) CPU_USAGE_PCT=$(top -bn1 | grep "Cpu(s)" | sed "s/.*, *\([0-9.]*\)%* id.*/\1/" | awk '{print 100 - $1}') echo " Hostname : $(hostname)" >> $REPORT_FILE echo " CPU Model : $CPU_MODEL" >> $REPORT_FILE echo " Total Sockets : $TOTAL_SOCKETS" >> $REPORT_FILE echo " Total Cores : $TOTAL_CORES" >> $REPORT_FILE echo " Total Threads : $((TOTAL_CORES * TOTAL_THREADS))" >> $REPORT_FILE echo " Current CPU Usage : $CPU_USAGE_PCT%" >> $REPORT_FILE echo " Load Average (1m) : $CPU_LOAD" >> $REPORT_FILE echo "" >> $REPORT_FILE # 2. Oracle Database CPU Usage echo "2. ORACLE DATABASE CPU USAGE" >> $REPORT_FILE echo "----------------------------" >> $REPORT_FILE # Get list of running databases DATABASES=$(ps -ef | grep pmon | grep -v grep | awk -F_ '{print $3}') TOTAL_DBS=$(echo "$DATABASES" | wc -l) echo " Total Databases : $TOTAL_DBS" >> $REPORT_FILE echo "" >> $REPORT_FILE TOTAL_DB_CPU=0 for DB in $DATABASES; do export ORACLE_SID=$DB DB_CPU=$(get_sql_value " SELECT ROUND(SUM(value)/100, 2) FROM v\$sysmetric WHERE metric_name='CPU Usage Per Sec' AND group_id=2;" "sys") DB_CPU_PCT=$(get_sql_value " SELECT ROUND(SUM(value)/$TOTAL_CORES, 2) FROM v\$sysmetric WHERE metric_name='CPU Usage Per Sec' AND group_id=2;" "sys") TOTAL_DB_CPU=$(echo "$TOTAL_DB_CPU + $DB_CPU" | bc) echo " Database: $DB" >> $REPORT_FILE echo " CPU Cores Used : $DB_CPU" >> $REPORT_FILE echo " CPU Percentage : $DB_CPU_PCT%" >> $REPORT_FILE # Get top consuming SQL for this DB TOP_SQL=$(get_sql_value " SELECT sql_id FROM ( SELECT sql_id, cpu_time FROM v\$sqlstats ORDER BY cpu_time DESC) WHERE rownum = 1;" "sys") echo " Top SQL ID : $TOP_SQL" >> $REPORT_FILE echo "" >> $REPORT_FILE done # 3. Capacity Summary echo "3. CAPACITY SUMMARY" >> $REPORT_FILE echo "-------------------" >> $REPORT_FILE AVAILABLE_CPU=$(echo "$TOTAL_CORES - $TOTAL_DB_CPU" | bc) AVAILABLE_PCT=$(echo "100 - $CPU_USAGE_PCT" | bc) echo " Total CPU Cores : $TOTAL_CORES" >> $REPORT_FILE echo " Used by Databases : $TOTAL_DB_CPU" >> $REPORT_FILE echo " Used by OS/Other : $(echo "$CPU_USAGE_PCT*$TOTAL_CORES/100 - $TOTAL_DB_CPU" | bc | awk '{printf "%.2f", $1}')" >> $REPORT_FILE echo " Available Cores : $AVAILABLE_CPU" >> $REPORT_FILE echo " Available Percent : $AVAILABLE_PCT%" >> $REPORT_FILE echo "" >> $REPORT_FILE # 4. Exadata Specific (if detected) if is_exadata; then echo "4. EXADATA METRICS" >> $REPORT_FILE echo "------------------" >> $REPORT_FILE CELL_CPU=$(cellcli -e "list metriccurrent CD_CPU_UTIL" 2>/dev/null | awk '{print $4}') CELL_COUNT=$(cellcli -e "list cell" 2>/dev/null | wc -l) echo " Storage Cells : $CELL_COUNT" >> $REPORT_FILE echo " Avg Cell CPU : $CELL_CPU%" >> $REPORT_FILE echo "" >> $REPORT_FILE fi # 5. Recommendations echo "5. RECOMMENDATIONS" >> $REPORT_FILE echo "------------------" >> $REPORT_FILE if (( $(echo "$CPU_USAGE_PCT > 80" | bc -l) )); then echo " * CRITICAL: CPU usage exceeds 80%" >> $REPORT_FILE echo " - Investigate top SQL statements immediately" >> $REPORT_FILE echo " - Consider adding CPU capacity" >> $REPORT_FILE elif (( $(echo "$CPU_USAGE_PCT > 60" | bc -l) )); then echo " * WARNING: CPU usage exceeds 60%" >> $REPORT_FILE echo " - Review performance of top SQL statements" >> $REPORT_FILE echo " - Plan for capacity expansion" >> $REPORT_FILE else echo " * CPU utilization is within normal range" >> $REPORT_FILE fi echo "" >> $REPORT_FILE echo "Report generated: $REPORT_FILE" >> $REPORT_FILE # Display report path echo "" echo "CPU capacity report generated:" echo $REPORT_FILE

=== DRAJ4

#!/bin/bash

# Oracle CPU Capacity Report - No Sudo Required

# Works for both Exadata and non-Exadata

# Version 2.1 - No Privileged Commands

# Configuration

REPORT_FILE="/tmp/cpu_capacity_report_$(date +%Y%m%d_%H%M%S).txt"

ORACLE_ENV="/etc/oratab"

# Function to check if Exadata (without privileged commands)

is_exadata() {

    if [ -f /etc/oracle-release ] && grep -q "Exadata" /etc/oracle-release 2>/dev/null; then

        return 0

    elif [ -d /opt/oracle.cell ]; then

        return 0

    else

        return 1

    fi

}

# Function to run SQL and get single value

get_sql_value() {

    local sql=$1

    local conn=$2

    if [ "$conn" == "sys" ]; then

        sqlplus -s / as sysdba << EOF | awk '/^ / {print $1}'

set pagesize 0

set feedback off

set heading off

$sql

EOF

    else

        sqlplus -s "/ as sysoper" << EOF | awk '/^ / {print $1}'

set pagesize 0

set feedback off

set heading off

$sql

EOF

    fi

}

# Header

echo "==============================================" > $REPORT_FILE

echo " CPU CAPACITY REPORT - $(hostname)" >> $REPORT_FILE

echo " Generated: $(date)" >> $REPORT_FILE

echo "==============================================" >> $REPORT_FILE

echo "" >> $REPORT_FILE

# 1. System CPU Overview

echo "1. HOST CPU RESOURCES" >> $REPORT_FILE

echo "---------------------" >> $REPORT_FILE

# Get CPU info (no sudo needed)

TOTAL_CORES=$(nproc 2>/dev/null || grep -c ^processor /proc/cpuinfo 2>/dev/null || echo "N/A")

CPU_MODEL=$(grep 'model name' /proc/cpuinfo | head -1 | cut -d':' -f2 | sed 's/^[ \t]*//' 2>/dev/null || echo "N/A")

# Get CPU usage (1 min avg) - no privileged commands

CPU_LOAD=$(awk '{print $1}' /proc/loadavg 2>/dev/null || echo "N/A")

CPU_USAGE_PCT=$(top -bn1 2>/dev/null | grep "Cpu(s)" | sed "s/.*, *\([0-9.]*\)%* id.*/\1/" | awk '{print 100 - $1}' 2>/dev/null || echo "N/A")

echo "  Hostname           : $(hostname)" >> $REPORT_FILE

echo "  CPU Model          : $CPU_MODEL" >> $REPORT_FILE

echo "  Total Cores        : $TOTAL_CORES" >> $REPORT_FILE

echo "  Current CPU Usage  : $CPU_USAGE_PCT%" >> $REPORT_FILE

echo "  Load Average (1m)  : $CPU_LOAD" >> $REPORT_FILE

echo "" >> $REPORT_FILE

# 2. Oracle Database CPU Usage

echo "2. ORACLE DATABASE CPU USAGE" >> $REPORT_FILE

echo "----------------------------" >> $REPORT_FILE

# Get list of running databases (no sudo needed)

DATABASES=$(ps -eo command | grep -E '[p]mon|[_]pmon' | awk -F_ '{print $NF}' | sort | uniq)

TOTAL_DBS=$(echo "$DATABASES" | wc -l)

echo "  Total Databases    : $TOTAL_DBS" >> $REPORT_FILE

echo "" >> $REPORT_FILE

TOTAL_DB_CPU=0

for DB in $DATABASES; do

    export ORACLE_SID=$DB

    

    DB_CPU=$(get_sql_value "

    SELECT ROUND(SUM(value)/100, 2) 

    FROM v\$sysmetric 

    WHERE metric_name='CPU Usage Per Sec' 

    AND group_id=2;" "sys" 2>/dev/null || echo "N/A")

    

    if [ "$DB_CPU" != "N/A" ]; then

        TOTAL_DB_CPU=$(echo "$TOTAL_DB_CPU + $DB_CPU" | bc)

        DB_CPU_PCT=$(echo "scale=2; $DB_CPU * 100 / $TOTAL_CORES" | bc)

    else

        DB_CPU_PCT="N/A"

    fi

    

    echo "  Database: $DB" >> $REPORT_FILE

    echo "    CPU Cores Used    : $DB_CPU" >> $REPORT_FILE

    echo "    CPU Percentage    : $DB_CPU_PCT%" >> $REPORT_FILE

    

    # Get top consuming SQL for this DB

    TOP_SQL=$(get_sql_value "

    SELECT sql_id FROM (

        SELECT sql_id, cpu_time 

        FROM v\$sqlstats 

        ORDER BY cpu_time DESC)

    WHERE rownum = 1;" "sys" 2>/dev/null || echo "N/A")

    

    echo "    Top SQL ID        : $TOP_SQL" >> $REPORT_FILE

    echo "" >> $REPORT_FILE

done

# 3. Capacity Summary

echo "3. CAPACITY SUMMARY" >> $REPORT_FILE

echo "-------------------" >> $REPORT_FILE

if [[ "$TOTAL_CORES" != "N/A" && "$TOTAL_DB_CPU" != "N/A" ]]; then

    AVAILABLE_CPU=$(echo "$TOTAL_CORES - $TOTAL_DB_CPU" | bc)

    AVAILABLE_PCT=$(echo "scale=2; 100 - ($TOTAL_DB_CPU * 100 / $TOTAL_CORES)" | bc)

    

    echo "  Total CPU Cores    : $TOTAL_CORES" >> $REPORT_FILE

    echo "  Used by Databases  : $TOTAL_DB_CPU" >> $REPORT_FILE

    echo "  Available Cores    : $AVAILABLE_CPU" >> $REPORT_FILE

    echo "  Available Percent  : $AVAILABLE_PCT%" >> $REPORT_FILE

else

    echo "  Could not calculate capacity summary" >> $REPORT_FILE

    echo "  (Missing CPU or database metrics)" >> $REPORT_FILE

fi

echo "" >> $REPORT_FILE

# 4. Exadata Detection (no privileged commands)

if is_exadata; then

    echo "4. EXADATA DETECTED" >> $REPORT_FILE

    echo "-------------------" >> $REPORT_FILE

    echo "  Exadata environment detected" >> $REPORT_FILE

    echo "  (Detailed cell metrics require cellcli access)" >> $REPORT_FILE

    echo "" >> $REPORT_FILE

fi

# 5. Recommendations

echo "5. RECOMMENDATIONS" >> $REPORT_FILE

echo "------------------" >> $REPORT_FILE

if [[ "$CPU_USAGE_PCT" =~ ^[0-9.]+$ ]]; then

    if (( $(echo "$CPU_USAGE_PCT > 80" | bc -l) )); then

        echo "  * CRITICAL: CPU usage exceeds 80%" >> $REPORT_FILE

        echo "    - Investigate top SQL statements immediately" >> $REPORT_FILE

    elif (( $(echo "$CPU_USAGE_PCT > 60" | bc -l) )); then

        echo "  * WARNING: CPU usage exceeds 60%" >> $REPORT_FILE

        echo "    - Review performance of top SQL statements" >> $REPORT_FILE

    else

        echo "  * CPU utilization is within normal range" >> $REPORT_FILE

    fi

else

    echo "  * Could not determine CPU usage level" >> $REPORT_FILE

fi

echo "" >> $REPORT_FILE

echo "Report generated: $REPORT_FILE" >> $REPORT_FILE

# Display report path

echo ""

echo "CPU capacity report generated:"

echo $REPORT_FILE

#!/bin/bash

# Function to summarize CPU usage per command

summarize_cpu_per_cmd() {

echo "SUMMARY PER CMD"

echo "Time USER_NAME PID CPU_SEC NB_CPU OS_COMMAND"

# Collect data

ps -eo user,pid,pcpu,time,args | while read -r USER PID CPU TIME CMD; do

CPU_SEC=$(convert_to_seconds "$TIME")

echo "$USER $PID $CPU_SEC $CPU $CMD"

done | awk -v interval="$INTERVAL" '

{

user = $1

pid = $2

cpu_sec = $3

nb_cpu = $4

cmd = $5

cmd_stats[pid]["user"] = user

cmd_stats[pid]["cpu_sec"] += cpu_sec

cmd_stats[pid]["nb_cpu"] += nb_cpu

cmd_stats[pid]["cmd"] = cmd

}

END {

for (pid in cmd_stats) {

printf "%-10s %10d %10.1f %10.1f %s\n", cmd_stats[pid]["user"], pid, cmd_stats[pid]["cpu_sec"], cmd_stats[pid]["nb_cpu"], cmd_stats[pid]["cmd"]

}

}'

}

# Function to display metrics explanation

display_metrics_explanation() {

echo "==================== Metrics Explanation ===================="

echo "1. DB_NAME: The name of the database or ASM instance."

echo "2. CPU_SEC: Total CPU time consumed by the database in seconds."

echo "3. NB_CPU: The number of CPUs currently being used by the database."

echo "4. AVG_NB_CPU: The average number of CPUs used by the database over the reporting interval."

echo "5. MAX_NB_CPU: The maximum number of CPUs used by the database during the reporting interval."

echo "6. MIN_NB_CPU: The minimum number of CPUs used by the database during the reporting interval."

echo "7. OS CPU Usage: The percentage of total CPU capacity currently being used by the operating system."

echo "8. Database CPU Usage: The total number of CPUs currently being used by all databases combined."

echo "9. Available Capacity: The number of CPUs available for additional workloads after accounting for OS and database usage."

echo "============================================================="

}

# Main loop

for ((i = 0; i < COUNT; i++)); do

echo "==================== CPU Analysis Report ===================="

echo "Date: $(date)"

echo "-------------------------------------------------------------"

summarize_cpu_per_db

echo "-------------------------------------------------------------"

if [[ "$DISPLAY_USER" == "Y" ]]; then

summarize_cpu_per_user

echo "-------------------------------------------------------------"

fi

if [[ "$DISPLAY_CMD" == "Y" ]]; then

summarize_cpu_per_cmd

echo "-------------------------------------------------------------"

fi

# Display metrics explanation

display_metrics_explanation

echo "-------------------------------------------------------------"

sleep "$INTERVAL"

done

=== 2nd

#!/bin/bash

# Ensure required commands are available

command -v ps >/dev/null 2>&1 || { echo "ps command is required but not found. Exiting."; exit 1; }

command -v mpstat >/dev/null 2>&1 || { echo "mpstat command is required but not found. Exiting."; exit 1; }

# Default parameters

INTERVAL=1

COUNT=10 # Set a reasonable default value

TOPN=10

DISPLAY_USER="N"

DISPLAY_CMD="N"

SORT_FIELD="CPU_SEC"

# Parse command-line arguments

for ARG in "$@"; do

case $ARG in

help)

echo "Usage: $0 [Interval [Count]] [top=] [sort_field=] [displayuser=[Y|N]] [displaycmd=[Y|N]]"

echo "Default Interval: 1 second."

echo "Default Count: 10."

echo "Parameters:"

echo " top= Number of rows to display (default: 10)"

echo " sort_field= CPU_SEC|AVG_NB_CPU|MAX_NB_CPU|MIN_NB_CPU (default: CPU_SEC)"

echo " displayuser= Report on user too (Y/N, default: N)"

echo " displaycmd= Report on command too (Y/N, default: N)"

exit 0

;;

top=*)

TOPN="${ARG#*=}"

;;

sort_field=*)

SORT_FIELD="${ARG#*=}"

;;

displayuser=*)

DISPLAY_USER="${ARG#*=}"

;;

displaycmd=*)

DISPLAY_CMD="${ARG#*=}"

;;

*)

if [[ "$ARG" =~ ^[0-9]+$ ]]; then

if [ -z "$INTERVAL" ]; then

INTERVAL="$ARG"

else

COUNT="$ARG"

fi

fi

;;

esac

done

# Function to convert time (HH:MM:SS or D-HH:MM:SS) to seconds

convert_to_seconds() {

local TIME=$1

local SECONDS=0

local DAYS=0

local H=0

local M=0

local S=0

# Check if the time includes days (e.g., 1-08:00:00)

if [[ "$TIME" =~ ([0-9]+)-([0-9]+):([0-9]+):([0-9]+) ]]; then

DAYS=${BASH_REMATCH[1]}

H=${BASH_REMATCH[2]}

M=${BASH_REMATCH[3]}

S=${BASH_REMATCH[4]}

elif [[ "$TIME" =~ ([0-9]+):([0-9]+):([0-9]+) ]]; then

H=${BASH_REMATCH[1]}

M=${BASH_REMATCH[2]}

S=${BASH_REMATCH[3]}

else

# Invalid time format, return 0 seconds

echo 0

return

fi

# Remove leading zeros and convert to base 10

DAYS=$((10#$DAYS))

H=$((10#$H))

M=$((10#$M))

S=$((10#$S))

# Calculate total seconds

SECONDS=$((DAYS * 86400 + H * 3600 + M * 60 + S))

echo "$SECONDS"

}

# Function to summarize CPU usage per database

summarize_cpu_per_db() {

echo "SUMMARY PER DB"

echo "DB_NAME CPU_SEC NB_CPU AVG_NB_CPU MAX_NB_CPU MIN_NB_CPU"

# Collect data

ps -eo pcpu,time,args | grep -E 'ora_|asm_' | while read -r CPU TIME CMD; do

DB=$(echo "$CMD" | sed -E 's/.*ora_...._//; s/ \(DESCRIPTION=.*//; s/ \(LOCAL=.*//; s/oracle//; s/_.*//; s/.* .*//; s/\+asm/+ASM/; s/asm/+ASM/; s/\+

ASM[1-9]*/+ASM/')

[ -z "$DB" ] && continue

CPU_SEC=$(convert_to_seconds "$TIME")

echo "$DB $CPU_SEC $CPU"

done | awk -v interval="$INTERVAL" '

{

db = $1

cpu_sec = $2

nb_cpu = $3

db_stats[db]["cpu_sec"] += cpu_sec

db_stats[db]["nb_cpu"] += nb_cpu

db_stats[db]["count"]++

if (cpu_sec > db_stats[db]["max_cpu"]) db_stats[db]["max_cpu"] = cpu_sec

if (db_stats[db]["min_cpu"] == 0 || cpu_sec < db_stats[db]["min_cpu"]) db_stats[db]["min_cpu"] = cpu_sec

}

END {

total_db_cpu = 0

for (db in db_stats) {

avg_cpu = db_stats[db]["cpu_sec"] / db_stats[db]["count"]

total_db_cpu += db_stats[db]["nb_cpu"]

printf "%-10s %10.1f %10.1f %10.1f %10.1f %10.1f\n", db, db_stats[db]["cpu_sec"], db_stats[db]["nb_cpu"], avg_cpu, db_stats[db]["max_cpu"], db_s

tats[db]["min_cpu"]

}

print total_db_cpu > "/tmp/total_db_cpu.txt" # Save total DB CPU usage for capacity calculation

}'

}

# Function to calculate OS CPU usage

calculate_os_cpu_usage() {

# Use mpstat to get the percentage of idle CPU time

local idle_cpu

idle_cpu=$(mpstat 1 1 | awk '/all/ {print $NF}' | head -n 1) # Ensure single-line output

# Calculate the percentage of CPU used by the OS

local os_cpu_usage

os_cpu_usage=$(echo "100 - $idle_cpu" | bc)

echo "$os_cpu_usage"

}

# Function to calculate total and available CPU capacity

calculate_capacity() {

local total_cpus

local os_cpu_usage

local db_cpu_usage

local normalized_db_cpu_usage

local available_capacity

# Get the total number of CPUs

total_cpus=$(nproc)

# Get the OS CPU usage

os_cpu_usage=$(calculate_os_cpu_usage)

# Get the total database CPU usage

db_cpu_usage=$(cat /tmp/total_db_cpu.txt)

# Normalize database CPU usage to a percentage of total CPUs

normalized_db_cpu_usage=$(echo "$db_cpu_usage / $total_cpus" | bc -l)

# Calculate the available capacity

available_capacity=$(echo "$total_cpus - $os_cpu_usage - $normalized_db_cpu_usage" | bc -l)

# Display the results

echo "Total CPUs: $total_cpus"

echo "OS CPU Usage: $(printf "%.2f" "$os_cpu_usage")%"

echo "Database CPU Usage: $(printf "%.2f" "$normalized_db_cpu_usage") CPUs"

echo "Available Capacity: $(printf "%.2f" "$available_capacity") CPUs"

}

# Main loop

for ((i = 0; i < COUNT; i++)); do

echo "==================== CPU Analysis Report ===================="

echo "Date: $(date)"

echo "-------------------------------------------------------------"

summarize_cpu_per_db

echo "-------------------------------------------------------------"

if [[ "$DISPLAY_USER" == "Y" ]]; then

summarize_cpu_per_user

echo "-------------------------------------------------------------"

fi

if [[ "$DISPLAY_CMD" == "Y" ]]; then

summarize_cpu_per_cmd

echo "-------------------------------------------------------------"

fi

# Calculate and display capacity

calculate_capacity

echo "-------------------------------------------------------------"

sleep "$INTERVAL"

done

The script you provided is a Bash script designed to generate a CPU analysis report. It offers several modes and methods for running the report, based on the command-line arguments and options specified. Here are the different ways you can run this script, along with the modes and methods available:

Command-Line Arguments and Options

1. Interval and Count:

   • Interval: The time interval in seconds between each report generation. Default is 1 second.
   • Count: The number of times the report is generated. Default is 10.

   Usage: ./script.sh [Interval] [Count]

2. Top N Rows:

   • top=: Specifies the number of rows to display in the report. Default is 10.

   Usage: ./script.sh top=5

3. Sort Field:

   • sort_field=: Determines the field by which the report is sorted. Options include CPU_SEC, AVG_NB_CPU, MAX_NB_CPU, MIN_NB_CPU. Default is CPU_SEC.

   Usage: ./script.sh sort_field=AVG_NB_CPU

4. Display User Information:

   • displayuser=: Whether to include user information in the report. Options are Y (Yes) or N (No). Default is N.

   Usage: ./script.sh displayuser=Y

5. Display Command Information:

   • displaycmd=: Whether to include command information in the report. Options are Y (Yes) or N (No). Default is N.

   Usage: ./script.sh displaycmd=Y

6. Help:

   • help: Displays usage information and exits.

   Usage: ./script.sh help

Combining Options

You can combine these options to customize the report generation. For example:

• Example 1: Run the report every 2 seconds, 5 times, displaying the top 5 rows, sorted by average number of CPUs, including user and command information.

  ./script.sh 2 5 top=5 sort_field=AVG_NB_CPU displayuser=Y displaycmd=Y
  

• Example 2: Run the report with default settings but display command information.

  ./script.sh displaycmd=Y
  

Summary

In the script you provided, the CPU time consumed by the database in seconds and other metrics are calculated based on the output of the ps command, which provides information about currently running processes. The reference point for the CPU time consumed is the cumulative CPU time used by each process since it started. This means that the CPU time reported is not limited to a specific time window like the last hour; rather, it reflects the total CPU time consumed by the process from its start until the moment the script is run.

Key Points:

1. Cumulative CPU Time:

   • The ps command outputs the cumulative CPU time for each process, which is the total time the process has been using the CPU since it started. This is represented in the TIME field in the ps output.

2. Real-Time Snapshot:

   • The script provides a real-time snapshot of CPU usage at the moment it is run. It does not inherently track or limit the data to a specific historical period like the last hour.

3. Interval and Count:

   • The script runs in a loop based on the specified INTERVAL and COUNT, which means it can provide repeated snapshots of CPU usage at regular intervals. However, each snapshot is independent and reflects the state at that particular moment.

4. Aggregation:

   • The script aggregates CPU usage data per database by summing up the CPU time for processes associated with each database. This aggregation is done for each snapshot taken during the script's execution.

5. Normalization:

   • The script calculates average, maximum, and minimum CPU usage metrics based on the cumulative data collected during its execution.

Considerations:

• Historical Data: If you need historical data or CPU usage over a specific time window, you would need to implement additional logging or use a monitoring tool that tracks CPU usage over time.
• Real-Time Monitoring: The script is useful for real-time monitoring and analysis but does not provide historical trends unless modified to store and analyze past data.

In summary, the CPU time consumed by the database as reported by the script is cumulative from the start of each process, and the script provides real-time snapshots of this data at specified intervals.

Is this helpful?

give me what actually the script is doine logic wise

Certainly! Let's break down the logic of the script step by step to understand what it is doing:

Script Overview

The script is designed to generate a CPU analysis report for Oracle database processes running on a system. It uses the ps and mpstat commands to gather data about CPU usage and processes, and it provides a summary of CPU usage per database, as well as overall system CPU capacity.

Detailed Logic Breakdown

1. Command Availability Check:

   • The script first checks if the ps and mpstat commands are available on the system. If either command is missing, the script exits with an error message.

2. Default Parameters:

   • Sets default values for various parameters such as INTERVAL, COUNT, TOPN, DISPLAY_USER, DISPLAY_CMD, and SORT_FIELD.

3. Command-Line Argument Parsing:

   • Parses command-line arguments to override default parameters. It supports options for interval, count, top rows, sort field, and whether to display user and command information.

4. Time Conversion Function:

   • Defines a function convert_to_seconds to convert time from the format HH:MM:SS or D-HH:MM:SS into total seconds. This is used to convert the CPU time reported by ps into seconds.

5. Summarize CPU Usage Per Database:

   • Collects data using ps to list processes related to Oracle databases (identified by patterns like ora_ and asm_).
   • Extracts the database name from the command line of each process.
   • Converts the CPU time for each process into seconds.
   • Aggregates CPU usage statistics per database, including total CPU time, number of CPUs used, average, maximum, and minimum CPU usage.

6. Calculate OS CPU Usage:

   • Uses mpstat to determine the percentage of idle CPU time and calculates the percentage of CPU used by the operating system.

7. Calculate Total and Available CPU Capacity:

   • Determines the total number of CPUs using nproc.
   • Calculates the normalized database CPU usage as a percentage of total CPUs.
   • Computes the available CPU capacity by subtracting OS and database CPU usage from the total CPU capacity.

8. Main Loop:

   • Runs the report generation in a loop for the specified COUNT times, with a delay of INTERVAL seconds between each iteration.
   • Displays the date and CPU analysis report for each iteration.
   • Calls functions to summarize CPU usage per database, optionally per user and command, and calculates CPU capacity.

Summary

The script provides a real-time analysis of CPU usage by Oracle database processes, summarizing the data per database and calculating overall system CPU capacity. It allows customization through command-line arguments to adjust the frequency, duration, and detail level of the reports. The script is useful for monitoring and analyzing CPU usage patterns in environments running Oracle databases.

perform

Overview of the Comparison

You’re comparing snapshots across three RAC nodes (MDBD01P/mdbdb22p1, mdbdb22p2, mdbdb22p3) for two different time windows (presumably same time yesterday vs today).

The section visible shows a Wait Class breakdown, which includes:

• % of DB Time per class
• Waits/sec
• Total Wait Time (s)
• Average Wait Time

Each section is marked with a label (e.g., 1, 2, 3) for each of the RAC nodes.

 Key Findings

1. 

Application Wait Class is a Major Contributor

• For all three nodes, Application wait class is dominating the % of DB Time.
  
• Node 3: 3588.3 vs 132.4 (% Diff: +3,455.9!)
• Node 2: 96.0 vs 67.3
• Node 1: Not leading here, but still shows higher waits in Administrative and System I/O
• 
  

 What This Means:

• Application waits often indicate contention on resources like latches, locks, or enqueue waits.
• Could point to user locking, row-level contention, or serialization due to poorly tuned batch jobs.

2. 

System I/O and Cluster Waits Are Also Elevated

• Node 3 shows an increase in System I/O (693.4 vs 180.4 waits/sec).
• Cluster waits are elevated (e.g., 13,184.1 waits/sec vs 2,987.4 on Node 1).

 What This Means:

• System I/O: May point to slow disk access, increased reads/writes from poorly performing SQL.
• Cluster: Indicates interconnect latency or excessive block shipping (common in poorly partitioned RAC workloads).

3. 

DB CPU Usage is Lower

• Across nodes, the % of DB time spent on CPU is lower compared to the previous period.
  
• E.g., Node 3: 50.4% → 119.5% (diff -69.1), Node 2: 96.0% → 67.3%
• 
  

 What This Means:

• The system is spending less time on useful work and more time waiting.
• Supports the claim that batch jobs slowed down, since more time was spent waiting than on CPU.

4. 

Higher Average Wait Time in Key Areas

• Application: Avg wait time on Node 3 increased from 481.58 ms → 610.11 ms
• System I/O and Administrative wait times have also increased, especially on Node 1.

 Suggested Message to Application Team

Summary of AWR Analysis for Slower Batch Processing:

We observed a significant increase in Application, System I/O, and Cluster wait classes during the timeframe in question compared to the same period the previous day. These classes indicate contention and possible locking or row-level serialization issues during batch execution.

• Application Waits: High volume suggests contention possibly from uncommitted transactions, DML-heavy operations, or resource serialization.
• System I/O: Elevated I/O times suggest the database was waiting on physical reads/writes — possibly large table scans or lack of proper indexing.
• Cluster Waits: High inter-node coordination overhead, which may indicate poor SQL execution plans or data blocks being excessively transferred between nodes.

Action items:

1. Review batch job logs for DML/locking contention.
2. Identify SQLs executed during the time period (we can extract from AWR SQL by Elapsed Time).
3. Investigate whether partitioning, statistics, or execution plans changed recently.
4. We can also check for blocking sessions or enqueue waits if available from ASH.

Database Platforms Experience:

• Oracle – Expert level. Managed large-scale databases (80+ TB), provisioning, backup & recovery, high availability, disaster recovery.

• MongoDB – Expert level. Automated setup of single instances and replica sets, experience with Ops Manager and Atlas, performance monitoring with mongostat/mongotop, and data management using mongodump/restore.

• Couchbase – Expert level. Installed and configured 15-node clusters across diverse data centers with XDCR (Cross Data Center Replication).

• SQL Server – [Add details here if applicable, or omit if not used.]

---

Cloud Experience:

• Oracle Cloud (DBaaS) – Worked as part of Oracle's DBaaS and SRE teams. Expert level experience in provisioning databases and infrastructure, high availability, disaster recovery, and backup solutions.

• Google Cloud Platform (GCP) – Expert level. Experienced in virtual machine provisioning, budgeting, billing analysis, database provisioning, and migrating Oracle databases from on-prem to GCP.

#!/bin/bash

PFILE="$1"

if [[ -z "$PFILE" || ! -f "$PFILE" ]]; then

echo "Usage: $0 /path/to/init.ora"

exit 1

fi

# List of SGA-related parameters to check (add more if needed)

PARAMS=(db_cache_size shared_pool_size large_pool_size java_pool_size streams_pool_size log_buffer)

total_mb=0

for param in "${PARAMS[@]}"; do

# Extract value (case-insensitive, ignore commented lines)

value=$(grep -i "^[^#]*${param}" "$PFILE" | awk -F= '{print $2}' | tr -d ' ' | tr 'A-Z' 'a-z')

if [[ -n "$value" ]]; then

# Convert to MB

if [[ "$value" =~ ([0-9]+)m$ ]]; then

mb=${BASH_REMATCH[1]}

elif [[ "$value" =~ ([0-9]+)g$ ]]; then

mb=$(( ${BASH_REMATCH[1]} * 1024 ))

elif [[ "$value" =~ ([0-9]+)k$ ]]; then

mb=$(( ${BASH_REMATCH[1]} / 1024 ))

elif [[ "$value" =~ ^[0-9]+$ ]]; then

# Assume bytes if no unit

mb=$(( $value / 1024 / 1024 ))

else

mb=0

fi

total_mb=$(( total_mb + mb ))

printf "%-20s: %6d MB\n" "$param" "$mb"

fi

done

# Add buffer for fixed SGA and other components (adjust as needed)

buffer_mb=200

echo "Fixed SGA and others: $buffer_mb MB"

total_mb=$(( total_mb + buffer_mb ))

echo "-----------------------------------"

echo "Estimated minimum SGA needed: $total_mb MB"

####

#!/bin/bash

# Path to your init.ora file

INIT_ORA_PATH="/x/home/oracle/initTEST1.ora"

# Function to extract parameter values from init.ora

get_param_value() {

local param_name=$1

grep -i "^${param_name}" "${INIT_ORA_PATH}" | awk -F= '{print $2}' | tr -d '[:space:]'

}

# Extract key parameters

db_cache_size=$(get_param_value "db_cache_size")

shared_pool_size=$(get_param_value "shared_pool_size")

large_pool_size=$(get_param_value "large_pool_size")

java_pool_size=$(get_param_value "java_pool_size")

# Default values if not set in init.ora

db_cache_size=${db_cache_size:-"0"}

shared_pool_size=${shared_pool_size:-"0"}

large_pool_size=${large_pool_size:-"0"}

java_pool_size=${java_pool_size:-"0"}

# Convert sizes to MB if they are specified in KB or GB

convert_to_mb() {

local size=$1

if [[ $size == *K ]]; then

echo $(( ${size%K} / 1024 ))

elif [[ $size == *G ]]; then

echo $(( ${size%G} * 1024 ))

else

echo $size

fi

}

db_cache_size_mb=$(convert_to_mb "$db_cache_size")

shared_pool_size_mb=$(convert_to_mb "$shared_pool_size")

large_pool_size_mb=$(convert_to_mb "$large_pool_size")

java_pool_size_mb=$(convert_to_mb "$java_pool_size")

# Calculate estimated SGA size

estimated_sga_size=$(( db_cache_size_mb + shared_pool_size_mb + large_pool_size_mb + java_pool_size_mb ))

# Output estimated SGA size

echo "Estimated SGA size based on init.ora settings: ${estimated_sga_size} MB"

# Suggest a minimum SGA target

suggested_sga_target=$(( estimated_sga_size + 1024 )) # Add 1GB buffer for other components

echo "Suggested SGA target: ${suggested_sga_target} MB"

Here is a documentation of your findings regarding the "MEMORY_TARGET Too Small (ORA-00838)" and ORA-00821 errors, including your commands and observations:

---

Oracle SGA/Memory Target Testing and Findings

Test Environment

• Oracle 19c (19.24.0.0.0)
• Instance: TEST1
• OS: Linux

---

Test Steps and Commands

1. Startup with Large SGA (Default CPU Count)

export ORACLE_SID=TEST1

sqlplus / as sysdba

startup nomount pfile='/odb/ods01/orabin/admin/test.ora';

test.ora:

db_name=TEST1

sga_target=4096M

sga_max_size=4096M

Result:

ORACLE instance started.

Total System Global Area 4279113856 bytes

Fixed Size                  8947840 bytes

Variable Size             805306368 bytes

Database Buffers         3456106496 bytes

Redo Buffers                8753152 bytes

---

2. Lower SGA to 2048M, Set cpu_count=4

test.ora:

db_name=TEST1

sga_target=2048M

sga_max_size=2048M

cpu_count=4

Command:

startup nomount pfile='/odb/ods01/orabin/admin/test.ora';

Result:

ORACLE instance started.

Total System Global Area 2131624048 bytes

Fixed Size                  8941680 bytes

Variable Size             469762048 bytes

Database Buffers         1644167168 bytes

Redo Buffers                8753152 bytes

---

3. Lower SGA to 1024M, cpu_count=4

test.ora:

db_name=TEST1

sga_target=1024M

sga_max_size=1024M

cpu_count=4

Result:

ORACLE instance started.

Total System Global Area 1070471296 bytes

Fixed Size                  8947840 bytes

Variable Size             285212672 bytes

Database Buffers          767557632 bytes

Redo Buffers                8753152 bytes

---

4. Lower SGA to 1024M, Remove cpu_count

test.ora:

db_name=TEST1

sga_target=1024M

sga_max_size=1024M

Result:

ORA-00821: Specified value of sga_target 1024M is too small, needs to be at least 6256M

ORA-01078: failure in processing system parameters

---

5. Increase SGA to 5024M, No cpu_count

test.ora:

db_name=TEST1

sga_target=5024M

sga_max_size=5024M

Result:

ORA-00821: Specified value of sga_target 5024M is too small, needs to be at least 6256M

ORA-01078: failure in processing system parameters

---

Findings and Analysis

A. Setting cpu_count Explicitly

• When cpu_count is set to a lower value (e.g., 4), Oracle allows the instance to start with a much smaller SGA.
• When cpu_count is not set, Oracle auto-detects all CPUs (in your case, likely a large number, e.g., 128), and calculates much larger minimum SGA requirements.

B. Oracle's SGA Calculation Logic

• Minimum db_cache_size is calculated as:
  min db_cache_size = max(4MB * cpu_count, granule_size * processor_groups)
  • If cpu_count is high, this value is very large.
• If you do not set cpu_count, Oracle uses all available CPUs, which can result in a huge minimum SGA requirement.
• If you set cpu_count to a reasonable value, Oracle's minimum SGA requirement drops accordingly.

C. Error ORA-00821

• This error means your sga_target is less than the minimum required by Oracle, based on its internal calculations (which depend heavily on cpu_count and other parameters).
• Even if you set a large sga_target (e.g., 5024M), if Oracle's calculated minimum is higher (e.g., 6256M), startup fails.

---

Summary Table

test.ora Settings	Startup Result	SGA Allocated	Notes
sga_target=4096M, cpu_count unset	Success	~4GB	Oracle uses all CPUs, large SGA
sga_target=2048M, cpu_count=4	Success	~2GB	Lower SGA, Oracle accepts
sga_target=1024M, cpu_count=4	Success	~1GB	Lower SGA, Oracle accepts
sga_target=1024M, cpu_count unset	Fails	N/A	ORA-00821, needs at least 6256M
sga_target=5024M, cpu_count unset	Fails	N/A	ORA-00821, needs at least 6256M

---

Key Takeaways

• Explicitly set cpu_count in your PFILE to control Oracle's minimum SGA calculation, especially on servers with many CPUs.
• If you do not set cpu_count, Oracle may require a much larger SGA than you expect, leading to ORA-00821 errors.
• Always check Oracle's error message for the minimum required SGA and adjust your parameters accordingly.