Flashback Database setup in Oracle

Enable Flash back on :

Starting from Oracle 11g R2 we don't have to bounce the Database for these effects to get affected. If you are using pre 11G R2, you have to re bounce your system for these settings to get affected.

[oracle@Linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 4 10:25:05 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


Lets check if Flash back is enabled or not :

SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON       STATUS
------------------ ------------
NO                 OPEN

Since it is not enabled. Lets turn flash back ON:

SQL> alter database flashback on;

Database altered.


SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON       STATUS
------------------ ------------
YES                OPEN

Confirm the size of DB_RECOVERY_FILE_DEST_SIZE (Better keep it at least 30GB+ until it is Dev or Test environment)

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 20000M

I want to set it to 30GB:

SQL> alter system set db_recovery_file_dest_size=30G  ;

System altered.

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 30G

Lets verify the location of recovery area. This is where the flashback logs are stored:

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 30G

You can reset the location by simple command :

 alter system set db_recovery_file_dest= '/u01/app/oracle/flash_recovery/oraflash';


 Lets confirm the retention period :

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440 

You can Change the retention period :

sql > alter system set db_flashback_retention_target=2880;  -- 2days

Note : This retention period is in minutes so 1440/60 = 24 hours. 




If you have a standby Database configured:
Activating flashback logging on the standby database Flashback operations, such as activating or accessing restore points, can only be undertaken in the MOUNT stage of the database. An active recovery will also first have to be canceled.


Manual method First cancel the recovery on the standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Then activate Flashback:

 ALTER DATABASE FLASHBACK ON;

Then restart the recovery: 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Deactivating To deactivate flashback mode, use the command:

 ALTER DATABASE FLASHBACK OFF; 

ORA-01113: file string needs media recovery

Question:  I get this error when starting my Oracle database.  What is the ORA-01113 error?

SQL> ALTER TABLESPACE sample
 ONLINE  2  ;
ALTER TABLESPACE sample
*
ERROR at line 1:
ORA-01113: file 27 needs media recovery
ORA-01110: data file 27: '/u02/oracle/oradata/ORCL/sample.dbf'


SQL> recover datafile '/u02/oracle/oradata/ORCL/sample.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;
ALTER TABLESPACE sample ONLINE
*
ERROR at line 1:
ORA-01113: file 29 needs media recovery
ORA-01110: data file 29: '/u02/oracle/oradata/ORCL/SAMPLE10.dbf'


SQL> recover datafile '/u02/oracle/oradata/ORCL/SAMPLE10.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;
ALTER TABLESPACE sample ONLINE
*
ERROR at line 1:
ORA-01113: file 30 needs media recovery
ORA-01110: data file 30: '/u01/app/QPDEV/SAMPLE11.dbf'


SQL> recover datafile '/u01/app/QPDEV/SAMPLE11.dbf';
Media recovery complete.
SQL> ALTER TABLESPACE sample ONLINE;

Tablespace altered.

The ORA-01113 error can be thrown when you begin to start Oracle with the "startup" command. or sometimes if u take Tablespace offline and try to bring it back online.

 The ORA-01113 has many causes: 

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener

When you start or stop your oracle service using dbstart/dbshut scripts on Unix/Linux system and you may get the error ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener error.

[oracle@linux1bin]$ echo $ORACLE_SID

qptest

[oracle@linux1 bin]$ . dbshut

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener

Usage: -bash ORACLE_HOME

Then you need to edit the “dbstart” & “dbshut” file, the should be located at $ORACLE_HOME\bin

Go through the file and find line

ORACLE_HOME_LISTNER=$1

and change to

ORACLE_HOME_LISTNER=$ORACLE_HOME

find the LAST_DDL_TIME change time of an Oracle object

SQL to find the LAST_DDL_TIME change time of an Oracle object in the database.

-- Get the name, type, date of change of the DDL of a user object.

select OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where
owner not in ('SYS','SYSTEM');

Dropping large columns in database - ORACLE

alter table table_name set unused 

There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused.

MARKING UNUSED COLUMN

sql> 

desc abc_test




Name       Null Type         

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

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 

sql>  

alter table abc_test add (lname varchar2(20))




table ABC_TEST altered.

sql>  

desc abc_test




Name       Null Type         

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

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER       

LNAME           VARCHAR2(20) 

sql> 

alter table abc_test set unused (lname)

table ABC_TEST altered.

sql>  

desc abc_test

Name       Null Type         

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

NAME            VARCHAR2(20) 

TOTAL_ROWS      NUMBER                                                                                                 




Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following. 

Note : 

Dropping the unused column will still put a lock on the base table. I suggest you to drop 
unused column during maintenance period, to avoid locking.

ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.

Physically dropping column 

To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns.

ALTER TABLE table_name DROP COLUMN column_name; -- 1 column
ALTER TABLE table_name DROP (column_name1, column_name2);  -- multiple columns

Configure email server to send job notifcations- Oracle

Sample for adding scheduler e-mail notification

    Connected to SQL*PLUS using a privileged user.Using the set_scheduler_attribute procedure we have set the email_sender attribute to the SMTP server IP address, and specified the port to 25:

SQL> connect / as sysdba
Connected.

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','10.155.252.333:25');

PL/SQL procedure successfully completed.

        where:

             host is the host name or IP address of the SMTP server.
            port is the TCP port on which the SMTP server listens. If not specified, the default port of 25 is used.

        If this attribute is not specified, set to NULL, or set to an invalid SMTP server address, the Scheduler cannot send job state e-mail notifications. SMTP servers that require secure sockets (SSL) connections or require user authentication are not supported.

Optional Setup (Default email sender )

    Using the same procedure we have set the email_sender attribute to set the default email address for the email notifications:

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','DBMS_NOTIFICATION@organization.net');

PL/SQL procedure successfully completed.

Confirm the email server has been set

Using the get_scheduler_attribute procedure we can check the current values of both email_server and email_sender attributes.

declare
 v_att VARCHAR2(64);
 v_att2 varchar2(64);
 BEGIN
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);
 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);
 dbms_output.put_line('server: ' || v_att);
 dbms_output.put_line('sender: ' || v_att2);
 END;
 /


 Create Sample Job

    Connecting to the test user to create a sample job and created the test_mail job, that will start after 10 seconds from now.

SQL> BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
 job_name => 'TEST_MAIL',
 job_type => 'PLSQL_BLOCK',
 job_action => 'begin
 for i in 1..200 loop
 for j in 1..200 loop
 null;
 end loop;
 end loop;
 end;
 ',
 start_date => systimestamp + interval '10' second ,
 repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
 enabled => True,
comments => '');
 END;
 /

Add Job Notifications to send emails :


    Added the mail notification to the job, we have altered the value of the sender, so the default sender will not be used. Also we used job_all_events that will send mail notification for all events:

SQL> BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
 job_name => 'TEST_MAIL',
 recipients => 'arvind@organization.net',
 sender => 'DBMS_NOTIFICATION@organization.net',
 subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
 body => '%event_type% occurred at %event_timestamp%. %error_message%',
 events => 'job_all_events');
 END;
 /

PL/SQL procedure successfully completed.


    To review the scheduler email notifications setup, we will use the newly introduced views:
    USER | ALL | DBA_SCHEDULER_NOTIFICATIONS

SQL> desc user_SCHEDULER_NOTIFICATIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB_NAME                                  NOT NULL VARCHAR2(30)
 JOB_SUBNAME                                        VARCHAR2(30)
 RECIPIENT                                 NOT NULL VARCHAR2(4000)
 SENDER                                             VARCHAR2(4000)
 SUBJECT                                            VARCHAR2(4000)
 BODY                                               VARCHAR2(4000)
 FILTER_CONDITION                                   VARCHAR2(4000)
 EVENT                                              VARCHAR2(19)
 EVENT_FLAG                                NOT NULL NUMBER

SQL> select EVENT from user_SCHEDULER_NOTIFICATIONS where job_name='TEST_MAIL';

EVENT
-------------------
JOB_STARTED
JOB_SUCCEEDED
JOB_FAILED
JOB_BROKEN
JOB_COMPLETED
JOB_STOPPED
JOB_SCH_LIM_REACHED
JOB_DISABLED
JOB_CHAIN_STALLED
JOB_OVER_MAX_DUR

10 rows selected


Finally, after all the testing is done we can now remove the (Just) email notification part for the job:

SQL> connect test_job/test_job
Connected.

SQL> exec DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ('TEST_MAIL');

PL/SQL procedure successfully completed.

Drop scheduler job :

SQL> BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'TEST_MAIL',
                                defer => false,
                                force => false);
END;
/



Note : this has been tested on Oracle 11g Databases

Automating Oracle ADDM Reports with PL/SQL: A Guide to Sending Performance Reports via Email

Blog Article Title: "Automating Oracle ADDM Reports with PL/SQL: A Guide to Sending Performance Reports via Email"

Article Description:

In this article, we will explore how to automate the process of generating and sending Oracle Automatic Database Diagnostic Monitor (ADDM) reports using PL/SQL. ADDM provides valuable insights into database performance, identifying potential issues and offering recommendations for tuning. By integrating PL/SQL with email functionalities, you can streamline the delivery of these performance reports directly to your inbox, making it easier to monitor and address database health. This guide will cover the components of a PL/SQL package designed for this purpose, with detailed explanations of each section, making it an essential resource for Oracle DBAs looking to enhance their monitoring capabilities.

Note: You can use either DBMS_Sceduler to set job inside the database or trigger through any shell script through the command prompt (sqlplus)

This is an awesome script that I found online blog post by Gokhan Atil (ORACLE ACE). I wanted to share this with my friends, as it will be very helpful in daily maintenance.

PLSQL for ADDM sent via EMAIL:

DECLARE
   dbid           NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name      VARCHAR2(64);
   status         VARCHAR2(11);
   starttime      CHAR (5);
   endtime        CHAR (5);
   output         VARCHAR2 (32000);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'arvind@domain.com';
   v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';
   v_mail_conn    UTL_SMTP.connection;
   tname varchar2(50);
   tid   number;
BEGIN
   starttime := '01:00';
   endtime := '12:00';

   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

   SELECT dbid, db_unique_name
     INTO dbid, db_unique_name
     FROM v$database;

   SELECT host_name INTO host_name
     FROM v$instance;
   
    DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'ADDM Report( '
    || bid || ',' || eid || ' )');
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'START_SNAPSHOT',bid );
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'END_SNAPSHOT',eid );
    DBMS_ADVISOR.EXECUTE_TASK( tname );  
   
    status := 0;
 
    while status <> 'COMPLETED' loop
    select status into status from dba_advisor_tasks where task_id = tid;
    dbms_lock.sleep(5);  
    end loop;

   v_from := db_unique_name ||  '@' || host_name;

   v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
   UTL_SMTP.HELO (v_mail_conn, v_mail_host);
   UTL_SMTP.MAIL (v_mail_conn, v_from);
   UTL_SMTP.RCPT (v_mail_conn, v_recipient);
   UTL_SMTP.OPEN_DATA( v_mail_conn );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '
        || 'ADDM Report of ' || v_from || ' '
        || SYSDATE || ' ' || starttime || '-' || endtime
        || UTL_TCP.CRLF || UTL_TCP.CRLF );


   SELECT DBMS_ADVISOR.GET_TASK_REPORT( tname) INTO output FROM DUAL;
   UTL_SMTP.WRITE_DATA (v_mail_conn, output );
 
   UTL_SMTP.CLOSE_DATA (v_mail_conn);
   UTL_SMTP.QUIT (v_mail_conn);
 
EXCEPTION
   WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR
   THEN
      RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM);
END;
/

Note : 

works only if you have set the SMTP server and ACL if you are working on Database 11G or higher



Extracted from :

http://www.gokhanatil.com/2011/07/create-awr-and-addm-reports-and-send-them-via-email.html

Plsql Package to receive an Oracle Database AWR Report sent to EMAIL

In this article, we delve into the practical implementation of using a PL/SQL package to automate the generation and distribution of Oracle Database Automatic Workload Repository (AWR) reports via email. AWR reports are essential tools for database administrators, providing in-depth performance metrics and insights that help in diagnosing issues, optimizing queries, and improving overall database performance. Manually generating these reports and sharing them with stakeholders can be time-consuming and prone to delays.

To streamline this process, we demonstrate how to create a PL/SQL package that automates the generation of AWR reports and sends them directly to your inbox or other designated recipients. We will cover the package setup, key PL/SQL procedures, and how to configure the email functionality using Oracle’s UTL_MAIL or UTL_SMTP package. This solution not only saves time but also ensures that critical performance data is consistently monitored and shared with the right team members.

By the end of this article, you will have a ready-to-use script that simplifies AWR report distribution, enhancing your ability to maintain optimal database performance with minimal manual intervention. Whether you are a seasoned DBA or new to Oracle performance tuning, this guide will help you automate a critical aspect of your database management workflow.

This is an awesome script that I found online blog post by Gokhan Atil (ORACLE ACE).I wanted to share this with my friends, as it will be very helpful in daily maintenance.

PLSQL to send the AWR report to your email directly.

DECLARE
   dbid           NUMBER;
   inst_id        NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name       VARCHAR2(64);
   starttime      CHAR (5);
   endtime        CHAR (5);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'arvind@domain.com';
   v_mail_host    VARCHAR2 (30) := 'YOUR_SMTP_SERVER';
   v_mail_conn    UTL_SMTP.connection;
BEGIN
   starttime := '06:00';
   endtime := '10:00';

   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

   SELECT dbid, inst_id, db_unique_name
     INTO dbid, inst_id, db_unique_name
     FROM gv$database;

   SELECT host_name INTO host_name
     FROM v$instance;

   v_from := db_unique_name ||  '@' || host_name;

   v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
   UTL_SMTP.HELO (v_mail_conn, v_mail_host);
   UTL_SMTP.MAIL (v_mail_conn, v_from);
   UTL_SMTP.RCPT (v_mail_conn, v_recipient);
   UTL_SMTP.OPEN_DATA( v_mail_conn );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '
        || 'AWR Report of ' || v_from || ' '
        || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF  );
   UTL_SMTP.WRITE_DATA ( v_mail_conn,
        'Content-Type: text/html; charset=utf8'
        || UTL_TCP.CRLF || UTL_TCP.CRLF );  

   FOR c1_rec IN
      (SELECT output
         FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(dbid,
           inst_id, bid, eid, 8 )))
   LOOP
      UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
   END LOOP;
 
   UTL_SMTP.CLOSE_DATA (v_mail_conn);
   UTL_SMTP.QUIT (v_mail_conn);
 
EXCEPTION
   WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR
   THEN
      RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM);
END;



Note : 

works only if you have set the SMTP server and ACL if you are working on Database 11G or higher



Extracted from :

http://www.gokhanatil.com/2011/07/create-awr-and-addm-reports-and-send-them-via-email.html