Temp and diff table

 #!/bin/bash


source /auto/home/oracledb_ci.cfg


log "Starting Day 1: Daily Operations."


# Step 1: Truncate CMDB_DB_CI_TEMP

sqlplus -s $username/$password@$database <<EOF

TRUNCATE TABLE CMDB_DB_CI_TEMP;

exit

EOF


if [ $? -ne 0 ]; then

    log "Failed to truncate CMDB_DB_CI_TEMP."

    exit 1

fi

log "CMDB_DB_CI_TEMP truncated successfully."


# Step 2: Load CMDB_DB_CI_TEMP with current data

control_file="/auto/home/oracledb_ci_temp.ctl"

cat <<EOF > $control_file

LOAD DATA

INFILE '$csv_file'

INTO TABLE CMDB_DB_CI_TEMP

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(

support_group, install_status, oracle_home, u_cmdb_ci_used_for, u_cmdb_owner_group, name, correlation_id, version

)

EOF


sqlldr $username/$password@$database control=$control_file log="$log_location/temp_loader_oracledb_ci.log"

if [ $? -ne 0 ]; then

    log "Failed to load data into CMDB_DB_CI_TEMP."

    exit 1

fi

log "Data loaded successfully into CMDB_DB_CI_TEMP."


# Step 3: Insert differences into CMDB_DB_CI_DIFF

sqlplus -s $username/$password@$database <<EOF

INSERT INTO CMDB_DB_CI_DIFF

SELECT * FROM CMDB_DB_CI_DAILY MINUS SELECT * FROM CMDB_DB_CI_TEMP;

UPDATE CMDB_DB_CI_DIFF SET modified_date = SYSDATE;

exit

EOF


if [ $? -ne 0 ]; then

    log "Failed to populate CMDB_DB_CI_DIFF."

    exit 1

fi

log "Differences calculated and inserted into CMDB_DB_CI_DIFF."


# Step 4: Validate SQL Loader and Step 5 & 6 if successful

if [ -s "$log_location/temp_loader_oracledb_ci.bad" ]; then

    log "Errors found in SQL Loader. Stopping process."

    exit 1

else

    log "SQL Loader successful. Proceeding with updates."

fi


# Step 5: Truncate CMDB_DB_CI_DAILY

sqlplus -s $username/$password@$database <<EOF

TRUNCATE TABLE CMDB_DB_CI_DAILY;

exit

EOF


if [ $? -ne 0 ]; then

    log "Failed to truncate CMDB_DB_CI_DAILY."

    exit 1

fi

log "CMDB_DB_CI_DAILY truncated successfully."


# Step 6: Insert into CMDB_DB_CI_DAILY from CMDB_DB_CI_TEMP

sqlplus -s $username/$password@$database <<EOF

INSERT INTO CMDB_DB_CI_DAILY

SELECT * FROM CMDB_DB_CI_TEMP;

exit

EOF


if [ $? -ne 0 ]; then

    log "Failed to update CMDB_DB_CI_DAILY."

    exit 1

fi

log "CMDB_DB_CI_DAILY updated successfully."


rm $control_file

log "Day 1 operations completed."

Comments

Post a Comment

Popular posts from this blog

Database growth

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

DBA Day-2 ve

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

Sql1

#!/bin/bash LOGFILE="/home/oracle/pdb_output.log" : > $LOGFILE # Clear the log file get_pdb_details() {   echo "CDB_UNIQUE_NAME|PDB_NAME|CPU|SGA_MAX_SIZE_GB|SGA_TARGET_GB|PGA_GB|DB_SIZE_GB" > $LOGFILE   # Iterate over each CDB   for ENV_FILE in /home/oracle/*.env; do     source "${ENV_FILE}"     # Validate environment variables     if [[ -z "$ORACLE_SID" || -z "$ORACLE_HOME" ]]; then       continue     fi     # Check if CDB is running     INSTANCE_STATUS=$(timeout 5 sqlplus -s / as sysdba <<EOF SET HEAD OFF FEEDBACK OFF SELECT instance_name FROM v\$instance WHERE status = 'OPEN'; EXIT; EOF )     if [[ -z "$INSTANCE_STATUS" ]]; then       continue     fi     # Fetch CDB unique name     CDB_UNIQUE_NAME=$(sqlplus -s / as sysdba <<EOF SET HEAD OFF FEEDBACK OFF SELECT value FROM v\$parameter WHERE name = 'db_unique_name'; EXIT; EO...
Read more