Posts

Showing posts from July, 2024

Decommission v1

 #!/bin/bash # Check if ORACLE_SID is provided if [ -z "$1" ]; then   echo "Error: ORACLE_SID not provided."   echo "Usage: $0 <ORACLE_SID>"   exit 1 fi # Set environment variables using oraenv export ORAENV_ASK=NO export ORACLE_SID=$1 oraenv LOG_FILE="decommission_$ORACLE_SID.log" log() {   local message=$1   echo "$(date +'%Y-%m-%d %H:%M:%S') $message" | tee -a $LOG_FILE } log "Decommissioning process started for ORACLE_SID: $ORACLE_SID" db_unique_name=$(sqlplus -s / as sysdba <<EOF set heading off set feedback off set pagesize 0 select db_unique_name from v\$database; exit; EOF ) db_unique_name=$(echo $db_unique_name | xargs) # trim spaces log "Retrieved database unique name: $db_unique_name" # Helper function to execute SQL commands run_sql() {   sqlplus -s "/ as sysdba" <<EOF $1 exit; EOF } # Helper function to execute DGMGRL commands and capture output run_dgmgrl() {   dgmgrl...
Post a Comment
Read more

Modified

 -- Update modified records in master table MERGE INTO $master_table m USING (     SELECT DISTINCT         child_correlation_id,         parent,         parent_sys_class_name,         child_sys_class_name,         child_u_cmdb_ci_used_for,         parent_correlation_id,         type,         child,         modified_date     FROM $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,   ...
Post a Comment
Read more

Db app

 -- Insert new records into master table INSERT INTO $master_table (     parent,     parent_sys_class_name,     parent_u_cmdb_ci_used_for,     parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,     parent_ref_cmdb_ci_service_u_cmdb_ci_ser,     modified_date ) SELECT     c.parent,     c.parent_sys_class_name,     c.parent_u_cmdb_ci_used_for,     c.parent_ref_cmdb_ci_service_u_cmdb_ci_service_application_code,     c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser,     c.modified_date FROM $child_table c LEFT JOIN $master_table m ON c.parent_ref_cmdb_ci_service_u_cmdb_ci_ser = m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser WHERE m.parent_ref_cmdb_ci_service_u_cmdb_ci_ser IS NULL; -- Log inserted rows SPOOL $inserted_rows_file SELECT     c.parent,     c.parent_sys_class_name,     c.parent_u_cmdb_ci_used_for,     c.parent_ref_cm...
Post a Comment
Read more

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 ...
Post a Comment
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...
Post a Comment
Read more

Compare update sql

 -- Insert new records into master table INSERT INTO &master_table (support_group, install_status, oracle_home, u_cmdb_ci_used_for, u_cmdb_owner_group, name, correlation_id, version, modified_date) SELECT c.support_group, c.install_status, c.oracle_home, c.u_cmdb_ci_used_for, c.u_cmdb_owner_group, c.name, c.correlation_id, c.version, c.modified_date FROM &child_table c LEFT JOIN &master_table m ON c.correlation_id = m.correlation_id WHERE m.correlation_id IS NULL; -- Log inserted rows SPOOL &inserted_rows_file SELECT c.support_group, c.install_status, c.oracle_home, c.u_cmdb_ci_used_for, c.u_cmdb_owner_group, c.name, c.correlation_id, c.version, c.modified_date FROM &child_table c LEFT JOIN &master_table m ON c.correlation_id = m.correlation_id WHERE m.correlation_id IS NULL; SPOOL OFF; -- Update modified records in master table MERGE INTO &master_table m USING &child_table c ON (m.correlation_id = c.correlation_id) WHEN MATCHED THEN   UPDATE SE...
Post a Comment
Read more

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 ...
Post a Comment
Read more

SQL Loader V2

#!/bin/bash # Variables CSV_FILE="/path/to/your/file.csv" USERNAME="your_username" PASSWORD="your_password" DATABASE="your_database" MASTER_TABLE="master_table" CHILD_TABLE="child_table" TEMP_CHILD_TABLE="temp_child_table" # Create a temporary table to load the new CSV data TEMP_CREATE_SQL="create_temp_table.sql" cat <<EOF > $TEMP_CREATE_SQL CREATE TABLE $TEMP_CHILD_TABLE AS SELECT * FROM $CHILD_TABLE WHERE 1=0; EOF # Run SQL*Plus to create the temporary table sqlplus -s $USERNAME/$PASSWORD@$DATABASE <<EOF @$TEMP_CREATE_SQL EXIT EOF # Create control file for temporary child table CONTROL_FILE="control_file.ctl" cat <<EOF > $CONTROL_FILE LOAD DATA INFILE '$CSV_FILE' INTO TABLE $TEMP_CHILD_TABLE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (   id,   name,   age,   last_updated "SYSDATE" ) EOF # Run SQL*Loader for the temporary chi...
Post a Comment
Read more