Test Database Connection

#!/bin/bash

# Set Oracle environment variables

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/19c/db_1

export ORACLE_SID=DBSPRD

export PATH=$ORACLE_HOME/bin:$PATH

# Log file

LOG_FILE=connection_test.log

# Function to test connection

test_connection() {

  echo "Testing Oracle connection..."

  start_time=$(date +%s.%N)

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

WHENEVER SQLERROR EXIT SQL.SQLCODE

SELECT 'Connected to Oracle' FROM dual;

EXIT;

EOF

  end_time=$(date +%s.%N)

  duration=$(echo "$end_time - $start_time" | bc)

  echo "Connection time: ${duration} seconds" >> $LOG_FILE

}

# Run the test multiple times

for i in {1..10}; do

  test_connection

done

echo "Test completed. Check $LOG_FILE for details."

Oracle Log Apply Gap

PROMPT Oracle Log Apply Gap

SELECT DISTINCT ROWNUM,appl.dest_id, cu.thread#, SUM (la.lastarchived) Last_Sequence_Archived,SUM (appl.lastapplied) Last_Sequence_Applied, decode(SUM (appl.lastapplied),0,0,SUM (la.lastarchived) - SUM (appl.lastapplied)) Difference FROM (SELECT gvi.thread#, gvd.destination, gvd.dest_id, gvd.target, MAX (gvd.log_sequence) currentsequence FROM gv$archive_dest gvd, gv$instance gvi WHERE gvd.status in ('VALID','ERROR') AND gvi.inst_id = gvd.inst_id GROUP BY thread#, dest_id, destination, target) cu, (SELECT thread#, dest_id, MAX (sequence#) lastarchived FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND archived = 'YES' and dest_id=1 GROUP BY thread#, dest_id) la, (SELECT thread#, dest_id, MAX (sequence#) lastapplied FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND applied = 'YES' GROUP BY thread#, dest_id) appl WHERE cu.thread# = la.thread# AND cu.thread# = appl.thread# and cu.dest_id=appl.dest_id and cu.target='STANDBY' GROUP BY ROWNUM, appl.dest_id, cu.thread# ORDER BY 2;

PROMPT Oracle Archive Destination Details

select V$ARCHIVE_DEST.DEST_ID,LOG_SEQUENCE,TRANSMIT_MODE,NET_TIMEOUT,RECOVERY_MODE,AFFIRM,V$ARCHIVE_DEST.DEST_NAME,V$ARCHIVE_DEST_STATUS.STATUS,V$ARCHIVE_DEST_STATUS.TYPE,V$ARCHIVE_DEST_STATUS.DESTINATION,PROTECTION_MODE,FAIL_SEQUENCE,ARCHIVED_SEQ#,APPLIED_SEQ#,DELAY_MINS,V$ARCHIVE_DEST.ERROR from V$ARCHIVE_DEST,V$ARCHIVE_DEST_STATUS where V$ARCHIVE_DEST.STATUS!='INACTIVE' and V$ARCHIVE_DEST.DEST_ID=V$ARCHIVE_DEST_STATUS.DEST_ID;

SFTP - Parallel File Transfer

 

Using lftp

lftp is a sophisticated file transfer program that supports multiple protocols, including SFTP.

1. Install lftp if it's not already installed:

   
   sudo apt-get install lftp   # On Debian-based systems
   sudo yum install lftp       # On RedHat-based systems
   

2. Use lftp with parallel transfers:

   
   lftp -u username,password sftp://destination_server -e "mput -P 5 exp_schemas_*.dmp; quit"
   

   Here, -P 5 specifies the number of parallel transfers.

Archive logs generated per day

SELECT
    TRUNC(FIRST_TIME) AS "DATE",
    COUNT(*) AS "NUMBER_OF_ARCHIVE_LOGS",
    ROUND(SUM(BLOCKS * BLOCK_SIZE) / (1024 * 1024), 2) AS "TOTAL_SIZE_MB"
FROM
    V$ARCHIVED_LOG
GROUP BY
    TRUNC(FIRST_TIME)
ORDER BY
    TRUNC(FIRST_TIME);

Daily Archive Log Generation :

select trunc(COMPLETION_TIME,'DD') Day, thread#, 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives_Generated from v$archived_log 

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Hourly Archive Log Generation :

set pages 1000

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives from v$archived_log 

group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

How To Start OBIEE 12c Automatically When The Server Reboots Or Starts

Windows

For example on Windows 2012 you could create a scheduled task that would get executed when the server starts and point it to OBIEE start script by following these steps:

1. Open Task Scheduler from Administrative Tools
2. Create a new Task, give it a name.
3. Select "Run whether user is logged on or not"
4. Create a New Trigger to run "At system startup".
5. Create a New Action to "Start a program". Click Browse button, navigate to [DOMAIN_HOME]/bitools/bin directory and select start.cmd