Posts

Decommission v3

 #!/bin/bash # Check if ORACLE_SID is provided if [ -z "$1" ]; then     echo "Error: ORACLE_SID not provided."     echo "Usage: $0 <ORACLE_SID> <second_node_instance>"     exit 1 fi # Set environment variables export ORACLE_SID=$1 export ORAENV_ASK=NO . oraenv second_node_instance="$2" LOG_FILE="decommission_$ORACLE_SID.log" ORACLE_HOME="<oracle_home>" # Function to log messages log() {     echo "$(date +'%Y-%m-%d %H:%M:%S') - $1" | tee -a $LOG_FILE } # Function to execute SQL commands run_sql() {     echo "$1" | $ORACLE_HOME/bin/sqlplus -S "/ as sysdba" } # Function to execute DGMGRL commands run_dgmgrl() {     dgmgrl -silent <<EOF connect / $1 exit; EOF } # Start decommissioning process log "Decommissioning process started for ORACLE_SID: $ORACLE_SID" # Retrieve the database unique name dynamically db_unique_name=$(sqlplus -s / as sysdba <<EOF set ...
Post a Comment
Read more

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

Decommission pre check

 #!/bin/bash # Function to log messages with timestamp LOG_FILE="decommission_precheck_${ORACLE_SID}.log" log() {     local message=$1     echo "$(date +'%Y-%m-%d %H:%M:%S') $message" | tee -a $LOG_FILE } # Check if correct arguments are provided if [ $# -ne 2 ]; then     echo "Usage: $0 <ORACLE_SID> <frame>"     exit 1 fi # Export environment variables export ORAENV_ASK=NO export ORACLE_SID=$1 export frame=$2 db_name=${ORACLE_SID%?} # Load Oracle environment . oraenv # Start logging log "Precheck gather information process started for ORACLE_SID: $ORACLE_SID" # Generate report file report_file="pre_check_report_$(date +%F).log" # Execute SQL script log "Executing queries.sql to gather database information." sqlplus -s / as sysdba <<EOF >> $report_file @queries.sql exit; EOF # Retrieve DB unique name log "Retrieving DB unique name from the database." db_unique_name=$(sqlplus -s / as sy...
Post a Comment
Read more

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