Dba

 #!/bin/bash


# Set environment variables using oraenv

export ORAENV_ASK=NO

export ORACLE_SID=$1

. oraenv


# Define output report file

report_file="pre_check_report_$(date +%F).txt"


# Create a new report file and add header

echo "Pre-check Report for Database: $ORACLE_SID" > $report_file

echo "Generated on: $(date)" >> $report_file

echo "----------------------------------------" >> $report_file


# Function to run a SQL command and append output to the report

run_sql() {

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

SET PAGESIZE 100 LINESIZE 255

$1

EOF

  echo "----------------------------------------" >> $report_file

}


# List of SQL queries to execute

queries=(

  "SELECT name FROM v\$database;"

  "SELECT sysdate FROM dual;"

  "SELECT service_name, COUNT(*) FROM gv\$session GROUP BY service_name;"

  "SELECT * FROM v\$encryption_wallet;"

  "SELECT tablespace_name, encrypted FROM dba_tablespaces;"

  "SELECT * FROM dba_encrypted_columns;"

  "SELECT os_username, username, MAX(timestamp), COUNT(*) FROM DBA_AUDIT_TRAIL WHERE action_name = 'LOGON' AND timestamp > sysdate-31 GROUP BY os_username, username;"

  "SELECT db_unique_name, open_mode FROM v\$database;"

  "SELECT DISTINCT SUBSTR(module, 5, 8) AS process FROM gv\$session WHERE username = 'GGUSER' AND module NOT LIKE 'ZenGate%' ORDER BY process;"

  "SELECT capture_name FROM dba_capture;"

  "SELECT module, action FROM gv\$session WHERE action LIKE 'OGG\$%';"

)


# Execute each query

for query in "${queries[@]}"; do

  run_sql "$query"

done


# Database size check

echo "Database Size Check" >> $report_file

echo "----------------------------------------" >> $report_file

run_sql "SELECT

  (SELECT SUM(bytes)/1024/1024/1024 FROM dba_data_files) AS \"Total Size (GB)\",

  (SELECT SUM(bytes)/1024/1024/1024 FROM dba_segments) AS \"Used Space (GB)\",

  (SELECT SUM(bytes)/1024/1024/1024 FROM dba_data_files) - (SELECT SUM(bytes)/1024/1024/1024 FROM dba_segments) AS \"Free Space (GB)\"

FROM dual;"


# SRVCTL status check

echo "SRVCTL Status Check" >> $report_file

echo "----------------------------------------" >> $report_file

srvctl status database -d $ORACLE_SID >> $report_file


echo "Pre-check report generated successfully at $report_file"

Comments

Post a Comment

Popular posts from this blog

SQL Loader V3

Create the Configuration File (config.cfg):Create a file named config.cfg with the following content. Adjust the values to match your environment:[database] username=your_username password=your_password database=your_database [files] csv_file=/path/to/your/file.csv log_file=/path/to/your/log_file.log inserted_rows_file=/path/to/your/inserted_rows.log updated_rows_file=/path/to/your/updated_rows.log ********** #!/bin/bash # Load configuration source config.cfg # Variables CSV_FILE=$csv_file USERNAME=$username PASSWORD=$password DATABASE=$database MASTER_TABLE="master_table" CHILD_TABLE="child_table" LOG_FILE=$log_file INSERTED_ROWS_FILE=$inserted_rows_file UPDATED_ROWS_FILE=$updated_rows_file # Logging function log() {   echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> $LOG_FILE } log "Starting data load process." # SQL Loader Control File for child table CONTROL_FILE="control_file.ctl" # Create control file for child table cat ...
Read more

Db Unix

-- Insert new records into master table INSERT INTO $master_table (     parent,     parent_sys_class_name,     child_sys_class_name,     child_u_cmdb_ci_used_for,     parent_correlation_id,     type,     child_correlation_id,     child,     modified_date ) SELECT     c.parent,     c.parent_sys_class_name,     c.child_sys_class_name,     c.child_u_cmdb_ci_used_for,     c.parent_correlation_id,     c.type,     c.child_correlation_id,     c.child,     c.modified_date FROM $child_table c LEFT JOIN $master_table m ON c.child_correlation_id = m.child_correlation_id WHERE m.child_correlation_id IS NULL; -- Log inserted rows SPOOL $inserted_rows_file SELECT     c.parent,     c.parent_sys_class_name,     c.child_sys_class_name,     c.child_u_cmdb_ci_used_for,     c.pare...
Read more

Standby redo log add

********* #!/bin/bash # Function to run SQL*Plus commands run_sql() {     sqlplus -s / as sysdba <<EOF     SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF     $1     EXIT EOF } # Get the max group, max thread, and max size from the v$log and v$logfile views redo_info=$(run_sql "SELECT MAX(l.group#), MAX(l.thread#), MAX(l.bytes/1024/1024)  FROM v\$log l, v\$logfile f  WHERE f.group# = l.group#;") # Extract the max group, max thread, and max size values max_group=$(echo "$redo_info" | awk '{print $1}') max_thread=$(echo "$redo_info" | awk '{print $2}') max_size=$(echo "$redo_info" | awk '{print $3}') # Get the number of redo groups per thread groups_per_thread=$(run_sql "SELECT COUNT(DISTINCT group#) FROM v\$log WHERE thread#=1;") # Get the recovery file destination reco_dest=$(run_sql "SHOW PARAMETER db_recovery_file_dest") reco_dest=$(echo "$reco_dest" | awk 'NR==2 {print $3}' |...
Read more