Db host

 #!/bin/bash


# Load configuration

source config.cfg


# Function to log messages

log() {

    echo "$(date +'%Y%m%d') $1" >> $log_file

}


log "Starting data load process."


# Create control file for SQL*Loader

control_file="control_file.ctl"

cat <<EOF > $control_file

LOAD DATA

INFILE '$csv_file'

INTO TABLE $child_table

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

(

    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 "SYSDATE"

)

EOF


log "Truncating child table."


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

TRUNCATE TABLE $child_table;

EXIT;

EOF


if [ $? -eq 0 ]; then

    log "Child table truncated successfully."

else

    log "Failed to truncate the child table."

    exit 1

fi


# Load data into child table

sqlldr $username/$password@$database control=$control_file log="$log_location/child_loader.log" BAD="$log_location/child_loader.bad"


if [ $? -eq 0 ]; then

    log "Data loaded successfully into child table."

else

    log "Failed to load data into child table."

    exit 1

fi


log "Comparing and updating master table."


# Compare and update SQL script

sql_compare_update="compare_update_master.sql"

cat <<EOF > $sql_compare_update


-- 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.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;

SPOOL OFF;


-- Update modified records in master table

MERGE INTO $master_table m

USING $child_table c

ON (m.child.correlation_id = c.child.correlation_id)

WHEN MATCHED THEN

UPDATE SET

    m.parent = c.parent,

    m.parent.sys_class_name = c.parent.sys_class_name,

    m.child.sys_class_name = c.child.sys_class_name,

    m.child.u_cmdb_ci_used_for = c.child.u_cmdb_ci_used_for,

    m.parent.correlation_id = c.parent.correlation_id,

    m.type = c.type,

    m.child = c.child,

    m.modified_date = SYSDATE

WHERE

    m.parent != c.parent OR

    m.parent.sys_class_name != c.parent.sys_class_name OR

    m.child.sys_class_name != c.child.sys_class_name OR

    m.child.u_cmdb_ci_used_for != c.child.u_cmdb_ci_used_for OR

    m.parent.correlation_id != c.parent.correlation_id OR

    m.type != c.type OR

    m.child != c.child;


-- Log updated rows

SPOOL $updated_rows_file

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

JOIN $master_table m ON c.child.correlation_id = m.child.correlation_id

WHERE

    m.parent != c.parent OR

    m.parent.sys_class_name != c.parent.sys_class_name OR

    m.child.sys_class_name != c.child.sys_class_name OR

    m.child.u_cmdb_ci_used_for != c.child.u_cmdb_ci_used_for OR

    m.parent.correlation_id != c.parent.correlation_id OR

    m.type != c.type OR

    m.child != c.child;

SPOOL OFF;


EOF


# Execute SQL script

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

@$sql_compare_update

EXIT;

EOF


if [ $? -eq 0 ]; then

    log "Comparison and update executed successfully."

else

    log "Failed to execute comparison and update."

    exit 1

fi


rm $control_file

rm $sql_compare_update


log "Data load process completed."

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