Steps to enable flashback mode

Steps to enable flashback mode:

Flashback mode in Oracle Database is a powerful feature that allows you to view and recover data from a previous point in time. It provides a way to rewind the database to a specific moment in the past, enabling you to analyze historical data, undo transactions, or recover from logical errors.

With flashback mode enabled, you can query data as it existed at a specific timestamp or system change number (SCN), providing a consistent view of the database as it appeared in the past. This is particularly useful when you need to diagnose issues, track changes, or retrieve lost data.

Oracle Database offers several flashback features, including:

Flashback Query: Allows you to perform queries on historical data by specifying a timestamp or SCN. This enables you to retrieve previous versions of rows and track changes over time.

Flashback Table: Provides the ability to revert a table to a previous state, effectively undoing changes made since a specified timestamp or SCN. This can be useful for correcting mistakes or recovering from data corruption.

Flashback Transaction: Enables you to undo a specific transaction or a set of transactions, restoring the affected data to its previous state. This is particularly useful when you need to reverse unintended changes or recover from logical errors.

Flashback Database: Allows you to perform a point-in-time recovery of the entire database, rolling it back to a specified SCN or timestamp. This feature is helpful for recovering from major incidents, such as data corruption or human errors affecting the entire database.

These flashback features in Oracle Database 19c provide administrators and developers with powerful tools for data recovery, analysis, and correction. They offer increased flexibility and efficiency in managing and restoring data, ultimately contributing to better data integrity and system reliability.

select instance_name from v$instance;

select log_mode,flashback_on from v$database;

SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

------------------

NO

show parameter DB_FLASHBACK_RETENTION_TARGET;

select * from v$flash_recovery_area_usage order by 2;

ALTER DATABASE FLASHBACK ON;

SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

------------------

YES To perform a flashback query in Oracle Database 12c and Oracle 19c: Ensure that the UNDO_RETENTION parameter is appropriately  set in the database initialization parameters.  This parameter determines the duration for which undo data is retained,  enabling flashback queries. Use the AS OF clause in your SQL query to specify the desired timestamp or  SCN (system change number For example: SELECT * FROM table_name AS OF TIMESTAMP TO_TIMESTAMP ('2022-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'); This query retrieves the data from the table_name as it existed at the  specified timestamp. To implement Flashback Table in Oracle Database 12c and 19c: you can use the FLASHBACK TABLE statement.  This feature allows you to quickly restore a table to a previous state  without the need for performing a full database restore or recovery.  Here's how you can use Flashback Table in both versions: Ensure that the UNDO_RETENTION parameter is appropriately set in the  database initialization parameters. Execute the FLASHBACK TABLE statement to restore the table to a  specific point in time.  For example: FLASHBACK TABLE table_name TO TIMESTAMP TO_TIMESTAMP('2022-01-01 12:00:00',  'YYYY-MM-DD HH24:MI:SS'); This statement restores the table_name to its state as of the specified timestamp Note that to use Flashback Table, you need to have the necessary privileges  (such as the FLASHBACK ANY TABLE system privilege or be the owner of the table).  Also, ensure that you have sufficient undo retention to support the  flashback operation. To implement Flashback Transaction in Oracle Database 12c and 19c:  you can use the FLASHBACK TRANSACTION statement.  This feature allows you to undo one or more transactions, rolling back the  changes made by those transactions.  Here's how you can use Flashback Transaction in both versions: Oracle Database 12c and Oracle Database 19c: To perform a Flashback Transaction operation in Oracle Database 12c: Ensure that the UNDO_RETENTION parameter is appropriately set in the  database initialization parameters. Execute the FLASHBACK TRANSACTION statement to undo specific transactions. For example: SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('000200030000002D'); SELECT xid, logon_user FROM flashback_transaction_query WHERE xid IN (   SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP   TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND   TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS') ); FLASHBACK TRANSACTION transaction_id; Replace transaction_id with the identifier of the transaction you want to undo.  You can obtain the transaction ID from the Oracle database transaction logs. To implement Flashback Database in Oracle Database 12c and 19c: you can use the FLASHBACK DATABASE statement.  This feature allows you to perform a point-in-time recovery of the  entire database, rolling it back to a specific SCN or timestamp.  Here's how you can use Flashback Database in both versions: Ensure that the database is running in ARCHIVELOG mode. Set the DB_FLASHBACK_RETENTION_TARGET parameter to specify the  desired duration for flashback data retention. Execute the FLASHBACK DATABASE statement to roll back the entire  database to a specific SCN or timestamp.  For example: FLASHBACK DATABASE TO SCN scn_number; Replace scn_number with the system change number (SCN) corresponding  to the desired point in time  to which you want to flash back the database. For more information check in the below link https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/flashback.html

Enjoy the work and do like me if you like this article!!!

Inserting data into a table

Inserting  a data through Dblinks:
 
Insert into  ADMIN.T71 (C1,
C2,
C3,
C4,
C5,
C6,
C7,
C8)
 select C1,
C2,
C3,
C4,
C5,
C6,
C7,
C8
from ARADMIN.T71@xyz.WORLD 


SELECT * FROM ARADMIN.T71  WHERE DATE_SUB(NOW(), INTERVAL 1 HOUR) < UPDATE_TIME 

INSERT INTO target_table (column1, column2, column3, ...)

SELECT column1, column2, column3, ...

FROM source_table;

INSERT INTO target_table (col1, col2, col3)

SELECT col1, col2, col3

FROM source_table;

Enjoy the work and do like me if you like this article!!!

Oracle Tablespace management

An Oracle tablespace is a logical storage container within the database. It organizes and manages data efficiently, storing tables, indexes, and other objects. It helps with performance optimization and simplifies backup and recovery. Tablespaces use data files to store data and metadata. They offer flexibility in storage characteristics and allocation, including different block sizes and storage quotas. Overall, tablespaces are crucial for data organization and management in Oracle databases.

Nice script for tablespace usage checking:

set lines 223 pages 999;

select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,

round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where USED_PERCENT>80

order by  USED_PERCENT; 

               or

select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,

round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where  tablespace_name like '%TEMP%' order by USED_PERCENT;

Create Tablespace script:

SELECT 'CREATE TABLESPACE ' || TABLESPACE_NAME || ' DATAFILE SIZE 500M;' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','UNDO1','SYSAUX','TEMP1','TOOLS'); 

SELECT 'DROP TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','UNDO1','SYSAUX','TEMP1','TOOLS' ); 

                                         

or

CREATE TABLESPACE "MARS"  DATAFILE '+DATA' size 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Query to tablespace autoextend:

set lines 200 pages 600

col file_name for a70

select file_name,autoextensible,ceil(maxbytes/(1024*1024)) maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb from dba_data_files where tablespace_name='MARS';

To check if tablespace is having bigfile:

SELECT tablespace_name, bigfile FROM dba_tablespaces where tablespace_name='MARS';

Adding datafile:

alter tablespace PUB add datafile '/u01/app/oracle/oradata/mkeustg1/pub02.dbf' size 1G autoextend on maxsize unlimited;

              or

ALTER TABLESPACE MARS   ADD DATAFILE '+DATA1' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;

To check users default tablespace:

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where USERNAME='SCOTT';

Temp Tablespace:

select file_name,autoextensible,ceil(maxbytes/(1024*1024)) maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb from dba_temp_files where tablespace_name='TEMP';

Resize Temp tablespace:

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON ;

To find the temp tablespace size:

SELECT A.tablespace_name tablespace, D.gb_total,

    SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_used,

    D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024  gb_free

   FROM v$sort_segment A,

    (

   SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 gb_total

    FROM v$tablespace B, v$tempfile C

     WHERE B.ts#= C.ts#

      GROUP BY B.name, C.block_size) D

    WHERE A.tablespace_name = D.name

    GROUP by A.tablespace_name, D.gb_total;

To find what is occupied in the temp:

select * from v$temp_extent_pool;

Temp Tablespace usage by session:

SELECT b.tablespace,

ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,

a.inst_id as Instance,

a.sid||','||a.serial# AS sid_serial,

NVL(a.username, '(oracle)') AS username,

a.program, a.status, a.sql_id

FROM gv$session a, gv$sort_usage b, gv$parameter p

WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr

AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id

ORDER BY temp_size desc;

To Chk is sorting is going on 

Select tablespace_name,USED_BLOCKS,FREE_BLOCKS,MAX_SORT_BLOCKS from   v$sort_segment;

Select   EXTENT_SIZE,CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS  from v$sort_segment;

Shrink TEMP Tablespace to the smallest possible size:

ALTER TABLESPACE temp SHRINK SPACE;

ALTER TABLESPACE temp01 SHRINK SPACE KEEP 50M;

To check the space available in all tablespace:

col "Tablespace" for a22

col "Used MB" for 99,999,999

col "Free MB" for 99,999,999

col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))

"Pct. Free"

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;

Output:

Tablespace                 Used MB     Free MB    Total MB  Pct. Free

---------------------- ----------- ----------- ----------- ----------

SYSAUX                       2,786         814       3,600         23

To check the space available in that particular tablespace

select c.tablespace_name,

round(decode(d.autoext,NULL,decode(e.free,NULL,0,e.free)/(1024*1024),(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz)-f.totsz+decode(e.free,NULL,0,e.free))/ (1024*1024))) free_mb,

round(f.totsz/(1024*1024)) Total_mb,

round((f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))/(1024*1024)) maxsize_mb,

decode(d.autoext, NULL, 'NO','YES') ae,

round(decode(d.autoext,NULL,(f.totsz-decode(e.free,NULL,0,e.free))/f.totsz*100,(f.totsz-decode(e.free,NULL,0,e.free))/(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))*100)) as Used_pct

from dba_tablespaces c,

(select a.tablespace_name, count(b.autoextensible) autoext

from dba_tablespaces a, dba_data_files b

where a.tablespace_name = b.tablespace_name(+) and b.autoextensible = 'YES'

group by a.TABLESPACE_NAME

having count(b.autoextensible) > 0) d,

(select tablespace_name, sum(bytes) free

from dba_free_space

group by tablespace_name) e,

(select df1.tablespace_name, sum(df1.bytes) totsz, sum(df1.maxbytes) maxsz

from dba_data_files df1, dba_tablespaces ts1

where ts1.tablespace_name = df1.tablespace_name

group by df1.tablespace_name) f,

(select df2.tablespace_name, sum(df2.bytes) nonauto_sz

from dba_data_files df2, dba_tablespaces ts2

where ts2.tablespace_name = df2.tablespace_name and df2.autoextensible = 'NO'

group by df2.tablespace_name) g

where c.tablespace_name = d.tablespace_name(+) and

c.tablespace_name = e.tablespace_name(+) and

c.tablespace_name = f.tablespace_name and

c.tablespace_name = g.tablespace_name(+) and c.tablespace_name like '%UNDO%' order by c.tablespace_name;

Calculate how much undo tablespace we need exactly

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024)

      "NEEDED UNDO SIZE [MByte]"

  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a, v$tablespace b, dba_tablespaces c

        WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'

          AND b.name = c.tablespace_name AND a.ts# = b.ts#

       ) d,

      v$parameter e, v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

         undo_block_per_sec

         FROM v$undostat

       ) g

 WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

Solution:

Increasing UNDO tablespace size is not the solution for this.

Run Undo advisory

Increase UNDO Retension Period

Enable Undo Retention Guarantee

Run this Select Query during non-business hours where heavy transactions are not happening.

Tune the Query

Query to find tablespace growth in last week:

select b.tsname tablespace_name , MAX(b.used_size_Gb) cur_used_size_Gb , round(AVG(inc_used_size_Gb),2)avg_increas_Gb  

from ( SELECT a.days,a.tsname , used_size_Gb , used_size_Gb - LAG (used_size_Gb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_Gb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) used_size_Gb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# 

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 

ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

Resize.sh (Script)

#!/bin/csh

$ORACLE_HOME/bin/sqlplus -S aradmin/Gu1nn3ss@ITSMLDP4 << EOF

Spool Resize.lst

@Resize.sql

spool off

exit

EOF

Hostname: $ nohup  Resize.sh & (Press enter)

Enjoy the work and do like me if you like this article!!!

Database Monitoring scripts

Cronjob:

0 8 * * * /path/to/your/Monitoring.sh

Save the below file with name Monitoring.sh

chmod +x script_name.sh

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Path to the alert log file

ALERT_LOG_FILE="/path/to/alert.log"

# Function to send an email

send_email() {

    SUBJECT="Database Alert Log Error"

    MESSAGE="An error was found in the database alert log. Please check the log file for details."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check the alert log for errors

check_alert_log() {

    ERROR_COUNT=$(grep -i "ORA-" "${ALERT_LOG_FILE}" | wc -l)

    if [ $ERROR_COUNT -gt 0 ]; then

        send_email

    fi

}

# Main script execution

check_alert_log

****Try this**************************************************

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Path to the alert log file

ALERT_LOG_FILE="/path/to/alert.log"

# Function to send an email

send_email() {

    SUBJECT="Database Alert Log Error"

    MESSAGE="An error was found in the database alert log. Please check the log file for details."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check the alert log for errors

check_alert_log() {

    ERROR_COUNT=$(awk '/ORA-/ {count++} END {print count}' "${ALERT_LOG_FILE}")

    if [ $ERROR_COUNT -gt 0 ]; then

        send_email

    fi

}

# Main script execution

check_alert_log

***********************************************************

Here's a script that checks if the database and listener are down and sends an email notification if either of them is not running:

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Listener configuration

LISTENER_NAME="your_listener_name"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Function to send an email

send_email() {

    SUBJECT="Database or Listener Down"

    MESSAGE="The database or listener is not running. Please check the status."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check if the database is running

check_database() {

    DB_STATUS=$(echo "SELECT 'Database Open' FROM dual;" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Database Open")

    if [ $DB_STATUS -eq 0 ]; then

        send_email

    fi

}

# Check if the listener is running

check_listener() {

    LISTENER_STATUS=$(lsnrctl status ${LISTENER_NAME} | grep -c "The listener supports no services")

    if [ $LISTENER_STATUS -eq 1 ]; then

        send_email

    fi

}

# Main script execution

check_database

check_listener

***********************************************************

Here's a script that checks if there are any RMAN backups, export backups, or statistics jobs running and sends an email notification if any of them are in progress:

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Function to send an email

send_email() {

    SUBJECT="Database Backup or Job Running"

    MESSAGE="There is a database backup or job running. Please check the status."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check if there are any RMAN backups running

check_rman_backup() {

    RMAN_STATUS=$(echo "SELECT 'RMAN Backup Running' FROM v\$rman_status WHERE status = 'RUNNING';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "RMAN Backup Running")

    if [ $RMAN_STATUS -gt 0 ]; then

        send_email

    fi

}

# Check if there are any export backups running

check_export_backup() {

    EXPORT_STATUS=$(echo "SELECT 'Export Backup Running' FROM v\$session WHERE program LIKE 'expdp%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Export Backup Running")

    if [ $EXPORT_STATUS -gt 0 ]; then

        send_email

    fi

}

# Check if there are any statistics jobs running

check_stats_job() {

    STATS_STATUS=$(echo "SELECT 'Statistics Job Running' FROM dba_scheduler_running_jobs WHERE job_name LIKE '%GATHER_STATS_JOB%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Statistics Job Running")

    if [ $STATS_STATUS -gt 0 ]; then

        send_email

    fi

}

# Main script execution

check_rman_backup

check_export_backup

check_stats_job

***********************************************************

Here's a script that performs a basic health check on the database and sends an email notification if the database performance is below a certain threshold

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Threshold values (you can adjust these values as per your requirements)

CPU_THRESHOLD=80

MEMORY_THRESHOLD=80

# Function to send an email

send_email() {

    SUBJECT="Database Health Check Alert"

    MESSAGE="The database performance is below the acceptable threshold. Please investigate the issue."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check CPU usage

check_cpu_usage() {

    CPU_USAGE=$(top -b -n 1 | grep "%Cpu(s)" | awk '{print $2}' | cut -d. -f1)

    if [ $CPU_USAGE -ge $CPU_THRESHOLD ]; then

        send_email

    fi

}

# Check memory usage

check_memory_usage() {

    MEMORY_USAGE=$(free | awk '/Mem/ { printf("%.2f"), $3/$2 * 100 }')

    if (( $(echo "$MEMORY_USAGE >= $MEMORY_THRESHOLD" | bc -l) )); then

        send_email

    fi

}

# Main script execution

check_cpu_usage

check_memory_usage

***********************************************************

Here's a script that checks if a tablespace has less space available than a specified threshold and sends an email notification immediately

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Tablespace and threshold configuration

TABLESPACE_NAME="your_tablespace_name"

THRESHOLD_PERCENTAGE=80

# Function to send an email

send_email() {

    SUBJECT="Tablespace Space Alert"

    MESSAGE="The tablespace '${TABLESPACE_NAME}' has less space available. Please check and take appropriate actions."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check tablespace space usage

check_tablespace_space() {

    SPACE_USAGE=$(echo "SELECT ROUND((1 - (df.totalspace - fs.freespace) / df.totalspace) * 100, 2) AS used_percentage FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS totalspace FROM dba_data_files WHERE tablespace_name = '${TABLESPACE_NAME}' GROUP BY tablespace_name) df, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS freespace FROM dba_free_space WHERE tablespace_name = '${TABLESPACE_NAME}' GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name;" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | awk '{print $1}')

    if (( $(echo "$SPACE_USAGE >= $THRESHOLD_PERCENTAGE" | bc -l) )); then

        send_email

    fi

}

# Main script execution

check_tablespace_space

*******************************************************

Here's a script that checks if there are any failed RMAN backups, export backups, or statistics jobs, and sends an email notification immediately

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Function to send an email

send_email() {

    SUBJECT="Backup or Job Failure Alert"

    MESSAGE="There is a failure in one or more backups or jobs. Please check the status."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check for failed RMAN backups

check_failed_rman_backups() {

    FAILED_BACKUPS=$(echo "SELECT 'RMAN Backup Failed' FROM v\$rman_backup_job_details WHERE status = 'FAILED';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "RMAN Backup Failed")

    if [ $FAILED_BACKUPS -gt 0 ]; then

        send_email

    fi

}

# Check for failed export backups

check_failed_export_backups() {

    FAILED_EXPORTS=$(echo "SELECT 'Export Backup Failed' FROM dba_datapump_jobs WHERE state = 'NOT RUNNING' AND operation = 'EXPORT' AND status = 'EXCEPTION';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Export Backup Failed")

    if [ $FAILED_EXPORTS -gt 0 ]; then

        send_email

    fi

}

# Check for failed statistics jobs

check_failed_stats_jobs() {

    FAILED_JOBS=$(echo "SELECT 'Statistics Job Failed' FROM dba_scheduler_job_run_details WHERE status = 'FAILED' AND job_name LIKE '%GATHER_STATS_JOB%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Statistics Job Failed")

    if [ $FAILED_JOBS -gt 0 ]; then

        send_email

    fi

}

# Main script execution

check_failed_rman_backups

check_failed_export_backups

check_failed_stats_jobs

Enjoy the work and do like me if you like this article!!!

Schema refresh steps from Test to Pre-production environment

Scenario 1:
To copy the MNPHIST schema (only the structure not the data) from SIT1  to Pre-production environment:


Source: HOST = Test1
Target: HOST = preTest 

SQL> select name,instance_name,open_mode,to_char(STARTUP_TIME,'dd/mm/yy hh24:mi:ss'),STATUS ,HOST_NAME from v$database,v$instance;
NAME      INSTANCE_NAME    OPEN_MODE            TO_CHAR(STARTUP_T STATUS    HOST_NAME
--------- ---------------- -------------------- ----------------- --------------------
MVMNPTS1  mvmnpts1_2       READ WRITE           30/06/12 00:09:01 OPEN    prser032

SQL>  SELECT OBJECT_TYPE,COUNT(*) FROM dba_objects WHERE OWNER='MNPHIST' GROUP BY OBJECT_TYPE;

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                   42
INDEX                      124
TABLE                       62


srvctl status database -d MVMNPT -v -f

SELECT Sum(bytes)/1024 AS total_size_GB FROM dba_segments WHERE owner ='GPCOMP_MT02';

SELECT Sum(bytes)/1024/1024/1024 AS total_size_GB FROM dba_segments WHERE owner like 'MNPHIST';

SELECT OBJECT_TYPE,COUNT(*) FROM dba_objects WHERE OWNER='MNPHIST' GROUP BY OBJECT_TYPE;
NAME      INSTANCE_NAME    OPEN_MODE            TO_CHAR(STARTUP_T STATUS    HOST_NAME
--------- ---------------- -------------------- ----------------- ------------
MNPT      MNPT_2           READ WRITE           18/07/12 14:58:39 OPEN    prser035


SQL> SELECT * FROM dba_directories;
OWNER                          DIRECTORY_NAME    DIRECTORY_PATH
------------------------------ ------------------------------
SYS                            MNP_EXP_DUMP_PRE    /export_MNP/preprod === =here dump file is there
SYS                            MNP_EXP_DUMP_PROD    /export_MNP/prod
SYS                            XMLDIR    /u01/app/oracle/product/11.2.0/MNPT/rdbms/xml
SYS                            EXP_BKP_MNPT2    /dump/exp_bkp_MNPT2
SYS                            DATA_PUMP_DIR    /u01/app/oracle/product/11.2.0/MNPT/rdbms/log/
SYS                            ORACLE_OCM_CONFIG_DIR    /u01/app/oracle/product/11.2.0/MNPT/ccr/state

expdp \'/ as sysdba \' directory=DATA_PUMP_DIR dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_expdp_source.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp 

Ftp the dump file from source to the target

impdp \'/ as sysdba \' directory=DATA_PUMP_DIR dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_expdp_source.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp

Sytax to run Export and Import in background  if its too big:
nohup expdp \'/ as sysdba \' directory=MNP_EXP_DUMP_PRE dumpfile=MNPHIST_expdp_target.dmp logfile=MNPHIST_expdp_target.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp &

nohup impdp \'/ as sysdba \' directory=MNP_EXP_DUMP_PRE dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_impdp_source.log schemas=MNPHIST job_name=MNPHISTimpdp &

@?/rdbms/admin/utlrp.sql: 
ORACLE_HOME/rdbms/admin/utlrp.sql to compile the invalid objects
Count the objects:
select object_type,status,count(*) from dba_objects where owner='MNPHIST' group by object_type,status;

Enjoy the work and do like me if you like this article!!!

Database growth scripts

Query to find out the Oracle Database size:
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM    (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;

Example output :
Create Ti Database Name Database Size Used Space  Used in %   Free Space   Free in %   Growth DAY  Growth DAY in %  Growth WEEK  Growth WEEK in %
--------- ------------- ------------- ----------- ----------- ------------ ----------- ----------- ---------------- ------------ ----------------
06/DEC/10 xxxxxxxx       11645818.5 MB 2996866.5 MB 25.73% MB   8648951.56 MB 74.27% MB   8075.16 MB  .069% MB         56526.13 MB  .485% MB
 

To check database growth in months:

SELECT TO_CHAR(creation_time, 'YYYY-MM') AS month,

       SUM(bytes) / 1024 / 1024 / 1024 AS growth_in_gb

FROM dba_segments

WHERE creation_time >= TRUNC(SYSDATE, 'MM')

GROUP BY TO_CHAR(creation_time, 'YYYY-MM')

ORDER BY month;

SET FEEDBACK ON

select to_char(creation_time, 'RRRR Month') "Month",

    sum(bytes)/1024/1024/1024 "Growth in GBs"

    from sys.v_$datafile

    where creation_time > SYSDATE-365

    group by to_char(creation_time, 'RRRR Month');

Query to find tablespace growth in last week:

select b.tsname tablespace_name , MAX(b.used_size_Gb) cur_used_size_Gb , round(AVG(inc_used_size_Gb),2)avg_increas_Gb  

from ( SELECT a.days,a.tsname , used_size_Gb , used_size_Gb - LAG (used_size_Gb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_Gb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) used_size_Gb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# 

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 

ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

Enjoy the work and do like me if you like this article!!!

User management

Create user script

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where USERNAME='MARS';

create user MARS identified by Z96bix#123;

CREATE USER MARS IDENTIFIED BY XXXXXXX DEFAULT TABLESPACE USERS PROFILE CUSTOMER;

Change User Default Tablespace

alter user usr1 default tablespace example;

CREATE USER SCOTT

  IDENTIFIED BY  xyz123##

  DEFAULT TABLESPACE ARSYSTEM

  TEMPORARY TABLESPACE ARTMPSPC

  PROFILE SCOTT_USER

  ACCOUNT UNLOCK;

  -- 4 Roles for SCOTT 

  GRANT ARADMIN_READ_ONLY TO SCOTT;

  GRANT AROLE TO SCOTT;

  GRANT DATA_MANAGER TO SCOTT WITH ADMIN OPTION;

  GRANT SELECT_CATALOG_ROLE TO SCOTT;

  ALTER USER SCOTT DEFAULT ROLE ALL;

 
ALTER USER SCOTT IDENTIFIED BY "Sc123!"  ACCOUNT LOCK PASSWORD EXPIRE;
 (This will ensure scott the Scott user password will changed and on successfull login it will prompt to change the password and the account will be lock

To make the password as expire:

alter user DATASAFE password expire;

To set the same password

alter user MARS identified by values 'S:6854B22E744A65D6C10456319075DC87CA65A3BAF93C6126F539818AC2E3;T:196';

To unlock the account:

alter user dev_class account lock;

alter user dev_class account unlock;

To check when the password of a user has changed:

select name, to_char(ptime,'DD/MM/YY HH24:MIS') as pw_last_changed from sys.user$

Query to find the user password change and account lock history:

SELECT NAME, CTIME, PTIME, LTIME, LCOUNT FROM SYS.USER$ WHERE NAME='CT2467408';

  -- 7 System Privileges for SCOTT 

  GRANT CREATE ANY SNAPSHOT TO SCOTT;

  GRANT CREATE ANY TABLE TO SCOTT;

  GRANT CREATE DATABASE LINK TO SCOTT;

  GRANT CREATE ROLE TO SCOTT;

  GRANT CREATE TABLE TO SCOTT;

  GRANT ON COMMIT REFRESH TO SCOTT;

  GRANT UNLIMITED TABLESPACE TO SCOTT;

GRANT CONNECT TO MARS;

GRANT RESOURCE TO MARS;

GRANT CREATE SESSION TO MARS;

GRANT SELECT ANY DICTIONARY TO MARS;

CREATE PROFILE SERVICE_ACCOUNT

LIMIT

COMPOSITE_LIMIT DEFAULT

SESSIONS_PER_USER DEFAULT

CPU_PER_SESSION DEFAULT

CPU_PER_CALL DEFAULT

LOGICAL_READS_PER_SESSION DEFAULT

LOGICAL_READS_PER_CALL DEFAULT

IDLE_TIME DEFAULT

CONNECT_TIME DEFAULT

PRIVATE_SGA DEFAULT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_REUSE_TIME 31536000/86400

PASSWORD_REUSE_MAX 10

PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION

PASSWORD_LOCK_TIME 432000/86400

PASSWORD_GRACE_TIME UNLIMITED

INACTIVE_ACCOUNT_TIME DEFAULT

/

alter user MARS profile new_profile;

 alter profile PROFILE_DBA_USER limit PASSWORD_VERIFY_FUNCTION "PWD_VERIFY_FUNCTION_12C";

Tablespace Quotas for REPORTER:

ALTER USER REPORTER QUOTA UNLIMITED ON REPASSM_TEMP;

ALTER USER REPORTER QUOTA 50M ON REPHOME;

To get metadata of a user:

set heading off;

set echo off;

SET TRIMSPOOL ON

SET FEEDBACK OFF

set lines 1000 pages 4000; 

set long 1000000 

select dbms_metadata.get_ddl('USER','T466464') from dual;

SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','T466464') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','VAIBHAVE') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SUB') from dual;

SELECT dbms_metadata.get_ddl('ROLE','SUB') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE') from dual;

Enjoy the work and do like me if you like this article!!!