Pre check database

 #!/bin/bash


export ORAENV_ASK=NO

export ORACLE_SID=$1


export frame=$2


db_name=${ORACLE_SID%?}


. oraenv


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


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

@queries.sql

exit;

EOF


autosys.ksh -h $frame -d $db_name -c delete


dgmgrl / <<EOF >> $report_file

show configuration;

EOF


if grep -qE "ORA-16525|ORA-16596" "$report_file"; then

    echo "Data Guard not available for $db_name" >> $report_file

fi


DB_NAME=$(grep -E "Standby Physical" $report_file | awk '{print $1}')


dgmgrl / <<EOF >> $report_file

show database "$DB_NAME";

EOF


export ASM_SID=$(ps -ef | grep [a]sm_smon.*+ASM | awk '{print $NF}' | cut -d'+' -f3)


. oraenv <<< "$ASM_SID"


srvctl status database -d $db_name -v

agctl status goldengate > goldengate_status_log.log


running_instances_with_manager=$(grep "is running" goldengate_status_log.log | awk '{gsub(/^\**|\**$/, "", $0); print $NF":"$3}')


echo "$running_instances_with_manager" >> $report_file


for instance_with_manager in $running_instances_with_manager; do

    node_name=$(echo $instance_with_manager | cut -d':' -f1)

    manager_name=$(echo $instance_with_manager | cut -d':' -f2)


    echo "Logging into $node_name to check Goldengate status for $manager_name" >> "$report_file"


    ssh $node_name <<EOF >> $report_file

export ORACLE_SID=$(ps -ef | grep [a]sm_smon.*+ASM | awk '{print $NF}' | cut -d'+' -f3)

. oraenv <<< "\$ORACLE_SID"

/auto/data.misc/oracle/ogg_distribution/automation_scripts/66_processes.sh

/auto/data.misc/oracle/ogg_distribution/automation_scripts/66_pumps.sh

EOF


done


# Extracting database info from report file

grep "$db_name" $report_file && echo "GG process available for this DB" >> $report_file || echo "No GG process found for this DB" >> $report_file


# Content of queries.sql

cat <<EOL > queries.sql

set linesize 200

set pagesize 100


col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20


SELECT round(sum(used.bytes)/1024/1024/1024) || 'GB' "Database Size",

       round(sum(used.bytes)/1024/1024/1024) "Used space",

       round(free.p/1024/1024/1024) || 'GB' "Free space"

FROM (SELECT bytes FROM v\$datafile

      UNION ALL

      SELECT bytes FROM v\$tempfile

      UNION ALL

      SELECT bytes FROM v\$log) used,

     (SELECT sum(bytes) as p FROM dba_free_space) free

GROUP BY free.p;


SELECT db_unique_name, open_mode FROM v\$database;

SELECT sysdate FROM dual;

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


set linesize 200

set pagesize 100

column WRL_TYPE format a20

column WRL_PARAMETER format a30

column STATUS format a10

column WALLET_TYPE format a10

column WALLET_ORDER format a10

column KEYSTORE_MODE format a10

column FULLY_BACKED_UP format a10

column CON_ID format 9999


SELECT * FROM v\$encryption_wallet;

clear columns


set linesize 80

set pagesize 14

set colsep ' '


SELECT tablespace_name, encrypted FROM dba_tablespaces;


echo "*********DBA Encrypted column********"


SELECT * FROM dba_encrypted_columns;


set linesize 200

set pagesize 100

column os_username format a30

column username format a30


SELECT os_username, username, MAX(timestamp), COUNT(*)

FROM DBA_AUDIT_TRAIL

WHERE action_name = 'LOGON' AND timestamp > sysdate-31

GROUP BY os_username, username;


column MAX(timestamp) format a30


echo "*********GoldenGate Process*********"


SELECT capture_name FROM dba_capture;

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

SELECT DISTINCT substr(module, 5, 8) process

FROM gv\$session

WHERE username='GGUSER' AND module NOT LIKE '%endate%'

ORDER BY process;

EOL

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