Select Menu

Ads

Random Posts

Powered By Blogger
Powered By Blogger

Blog Archive

Search This Blog
Thirupal Boreddy. Powered by Blogger.

My Blog List

Followers

Lorem 1

Technology

Circle Gallery

‹
›

Shooting

Racing

News

Lorem 4

 Using Normal and Guaranteed Restore Points

Requirements for Using Guaranteed Restore Points

To support the use of guaranteed restore points, the database must satisfy the following requirements:
  • The COMPATIBLE initialization parameter must be set to 10.2 or greater.
  • The database must be running in ARCHIVELOG mode. The FLASHBACK DATABASE operation used to return your database to a guaranteed restore point requires the use of archived redo logs from around the time of the restore point.
  • A flash recovery area must be configured, as described in "Setting Up a Flash Recovery Area for RMAN". Guaranteed restore points use a mechanism similar to flashback logging, and as with flashback logging, Oracle must store the required logs in the flash recovery area.
  • If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).
Note:
There are no special requirements for using normal restore points.

Creating Normal and Guaranteed Restore Points

To create normal or guaranteed restore points, use the CREATE RESTORE POINT statement in SQL*Plus, providing a name for the restore point and specifying whether it is to be a guaranteed restore point or a normal one (the default).
The database can be open or mounted when creating restore points. If it is mounted, then it must have been shut down cleanly (unless this is a physical standby database).
This example shows how to create a normal restore point:
SQL> CREATE RESTORE POINT before_upgrade;
Restore point created.
This example shows how to create a guaranteed restore point:
SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
Restore point created.


Listing Restore Points

To see a list of the currently defined restore points, use the V$RESTORE_POINT control file view, by means of the following query:
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
        FROM V$RESTORE_POINT;
You can view the name of each restore point, the SCN, wall-clock time and database incarnation number at which the restore points were created, whether each restore point is a guaranteed restore point, and how much space in the flash recovery area is being used to support information needed for Flashback Database operations to that restore point.
You can also use the following query to view only the guaranteed restore points:
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
        GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
        FROM V$RESTORE_POINT
      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.
Full information about the columns of V$RESTORE_POINT can be found in the Oracle Database Reference.

 Dropping Restore Points

When you are satisfied that you do not need an existing restore point, or when you want to create a new restore point with the name of an existing restore point, you can drop the restore point, using the DROP RESTORE POINT SQL*Plus statement. For example:
SQL> DROP RESTORE POINT before_app_upgrade;
Restore point dropped.
The same statement is used to drop both normal and guaranteed restore points.
Note:
Normal restore points eventually age out of the control file, even if not explicitly dropped. The rules governing retention of restore points in the control file are:
  • The most recent 2048 restore points are always kept in the control file, regardless of their age.
  • Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained, regardless of how many restore points are defined.
Normal restore points that do not meet either of these conditions may age out of the control file.
Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.
See also:
Oracle Database SQL Reference for reference information about the SQL DROP RESTORE POINT statement

In 11g R2 : Enabling Flashback Database

Follow these are the steps to enable Flashback Database.

To enable flashback logging:

Ensure the database instance is open or mounted. If the instance is mounted, then the database must be shut down cleanly unless it is a physical standby database. Other Oracle RAC instances can be in any mode.

Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day (1440 minutes).

Enable the Flashback Database feature for the whole database:

ALTER DATABASE FLASHBACK ON;

Optionally, disable flashback logging for specific tablespaces.

By default, flashback logs are generated for all permanent tablespaces. You can reduce overhead by disabling flashback logging for specific tablespaces as in the following example:

ALTER TABLESPACE tbs_3 FLASHBACK OFF;

You can re-enable flashback logging for a tablespace later with this command:

ALTER TABLESPACE tbs_3 FLASHBACK ON;

If you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

When you enable Flashback Database while the database is open, there is a very small chance the command may not be able to obtain the memory it needs. If the command fails because of that reason, retry the command after a while or retry after a shutdown and restart of the instance.

Disabling Flashback Database Logging
On a database instances that is either in mount or open state, issue the following command:

ALTER DATABASE FLASHBACK OFF;

PRIOR 11Gr2:

 How to Configure and use Flashback Database
Oracle Database » How To Guides » How to Configure and use Flashback Database
Flashback database is useful feature introduced with 10g that allows the database to be effictively rewound to a previous point in time.

This feature is particularly useful for test, V&P, and development environments where you may wish to try out a new piece of code or fuctionality over and over, 

refining as you go. Rather than having to restore the database after each test, flashback database allows the database to be rewound very quickly to a previous point 

in time.

Flashback database can be effectively run in two modes. One mode where you log every change to every block, which allows you to flahsback the database to any previous 

point in time. Or another mode, where Oracle only tracks the "before" images of changed blocks, so that you can flashback to the start of the restore point.

How to Set-up Flashback Database

1. Ensure db_recovery_file_dest is set.

sqlplus '/ as sysdba'
SQL> alter system set db_recovery_file_dest='+<FRA Diskgroup>' SCOPE=spfile;
2. Ensure db_recovery_file_dest_size is set

SQL> alter system set db_recovery_file_dest_size=100G SCOPE=spfile;
3. Stop and start the database

sqlplus '/ as sysdba'
SQL> shutdown immediate;
SQL> startup mount;
If flashback to any previous point in time is required, then turn flashback on using the following command
SQL> alter database flashback on;
SQL> alter database open;
SQL> alter system set db_flashback_retention_target=2880;
NOTES

Set the db_recovery_file_dest to an appropriate location for the flashback recovery files.
Set the db_recovery_file_dest_size to an appropriate size for the amount and size of the testing required.
Set the db_flashback_retention_target to an appropriate time, in mins, to retain flashbackability.
Only run alter database flashback on; if there is a requirement to flashback to ANY previous point in time.
Determine if Flashback Database is Already Enabled

1. Run the following commands to determing Flashback is turned on.

sqlplus '/ as sysdba'
SQL> select flashback_on from v$database;
Creating and Using Flashback Restore points.

This worked example assumes the database is using ASM to manage its storage.

Createing a Restore point

Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure 

that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.

1. You may want to create the restore point in mount mode. If so, put the database into mount mode now.

2. Create a restore point

sqlplus '/ as sysdba'
SQL> create restore point <restore point name> [GUARANTEE FLASHBACK DATABASE];
Rolling Back to a Restore Point

1. Identify the Restore point

sqlplus '/ as sysdba'
SQL> select name, time,guarantee_flashback_databse from v$restore_point;
SQL> quit
2. For a non RAC environment use the following commands to flashback to a restore point.

sqlplus '/ as sysdba'
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to restore point <restore point name>;
SQL> alter database open resetlogs;
3. For RAC instances use the following commands.

One one of the nodes run, srvctl stop database -d <database name> -o immediate
sqlplus '/ as sysdba'
SQL> startup mount;
SQL> flashback database to restore point <restore point name>;
SQL> alter database open resetlogs;
SQL> shutdown immediate;
SQL> quit
srvctl start database -d <database name>
Run crs_stat -t to confirm that the database is backup okay.
NOTES

Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by 

issuing the TRUNCATE TABLE command against the affected object(s).
Dropping a Restore Point

1. Restore points can be dropped with the database open using the following commands

sqlplus '/ as sysdba'
SQL> drop restore poijnt <restore point name>;
SQL> quit
Monitoring Flashback Logging

After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from v$flashback_database_log, the estimate gets better 

with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.

1. Monitor flashback logs

sqlplus '/ as sysdba'
SQL> select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;
SQL> quit
Finding the Earliest Flashback Point

Querying V$flashback_database_log will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available 

flashback logs.

1. Find the earliest flashback point

sqlplus '/ as sysdba'
SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
SQL> quit
Disabling Flashback Database

Full any previous point in time flashback can be disabled with the database open. Any unused Flashback logs will be automatically removed at this point and a message 

detailing the file deletion written to the alert log.

1. Disabling flashback

sqlplus '/ as sysdba'
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> quit
Troubleshooting
--  AWR-Generator.sql: Script for creating multiple consecutive Oracle AWR Reports
--                     Creates an output SQL script which, when run, will generate
--                     all AWR Reports between the specificed start and end snapshot
--                     IDs, for all instances
set feedback off
set echo off
set verify off
set timing off

-- Set AWR_FORMAT to "text" or "html"
define AWR_FORMAT = 'text'
define DEFAULT_OUTPUT_FILENAME = 'awr-generate.sql'
define NO_ADDM = 0

-- Get values for dbid and inst_num before calling awrinput.sql

set echo off heading on
column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
column inst_name heading "Instance"  new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;

prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~

select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;


-- Call the Oracle common input script to setup start and end snap ids
@@?/rdbms/admin/awrinput.sql

-- Ask the user for the name of the output script
prompt
prompt Specify output script name
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt This script produces output in the form of another SQL script
prompt The output script contains the commands to generate the AWR Reports
prompt
prompt The default output file name is &DEFAULT_OUTPUT_FILENAME
prompt To accept this name, press <return> to continue, otherwise enter an alternative
prompt

set heading off
column outfile_name new_value outfile_name noprint;
select 'Using the output file name ' || nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME')
     , nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') outfile_name
  from sys.dual;

set linesize 800
set serverout on
set termout off

-- spool to outputfile
spool &outfile_name

-- write script header comments
prompt REM Temporary script created by awr-generator.sql
prompt REM Used to create multiple AWR reports between two snapshots
select 'REM Created by user '||user||' on '||sys_context('userenv', 'host')||' at '||to_char(sysdate, 'DD-MON-YYYY HH24:MI') from dual;

set heading on
 
-- Begin iterating through snapshots and generating reports
DECLARE

  c_dbid           CONSTANT NUMBER := :dbid;
  c_inst_num       CONSTANT NUMBER := :inst_num;
  c_start_snap_id  CONSTANT NUMBER := :bid;
  c_end_snap_id    CONSTANT NUMBER := :eid;
  c_awr_options    CONSTANT NUMBER := &&NO_ADDM;
  c_report_type    CONSTANT CHAR(4):= '&&AWR_FORMAT';
  v_awr_reportname VARCHAR2(100);
  v_report_suffix  CHAR(5);

  CURSOR c_snapshots IS
  select inst_num, start_snap_id, end_snap_id
  from (
    select s.instance_number as inst_num,
           s.snap_id as start_snap_id,
           lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id
      from dba_hist_snapshot s
     where s.dbid            = c_dbid
       and s.snap_id        >= c_start_snap_id
       and s.snap_id        <= c_end_snap_id
  )
  where end_snap_id is not null
  order by inst_num, start_snap_id;

BEGIN

  dbms_output.put_line('');
  dbms_output.put_line('prompt Beginning AWR Generation...');

  dbms_output.put_line('set heading off feedback off lines 800 pages 5000 trimspool on trimout on');

  -- Determine report type (html or text)
  IF c_report_type = 'html' THEN
    v_report_suffix := '.html';
  ELSE
    v_report_suffix := '.txt';
  END IF;

  -- Iterate through snapshots
  FOR cr_snapshot in c_snapshots
  LOOP
    -- Construct filename for AWR report
    v_awr_reportname := 'awrrpt_'||cr_snapshot.inst_num||'_'||cr_snapshot.start_snap_id||'_'||cr_snapshot.end_snap_id||v_report_suffix;

    dbms_output.put_line('prompt Creating AWR Report '||v_awr_reportname
        ||' for instance number '||cr_snapshot.inst_num||' snapshots '||cr_snapshot.start_snap_id||' to '||cr_snapshot.end_snap_id);
    dbms_output.put_line('prompt');

    -- Disable terminal output to stop AWR text appearing on screen
    dbms_output.put_line('set termout off');

    -- Set spool to create AWR report file
    dbms_output.put_line('spool '||v_awr_reportname);

    -- call the table function to generate the report
    IF c_report_type = 'html' THEN
        dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html('
            ||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));');
    ELSE
        dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text('
            ||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));');
    END IF;

    dbms_output.put_line('spool off');

    -- Enable terminal output having finished generating AWR report
    dbms_output.put_line('set termout on');

  END LOOP;

  dbms_output.put_line('set heading on feedback 6 lines 100 pages 45');

  dbms_output.put_line('prompt AWR Generation Complete');

-- EXCEPTION HANDLER?

END;
/

spool off

set termout on

prompt
prompt Script written to &outfile_name - check and run in order to generate AWR reports...
prompt

--clear columns sql
undefine outfile_name
undefine AWR_FORMAT
undefine DEFAULT_OUTPUT_FILENAME
undefine NO_ADDM
undefine OUTFILE_NAME

set feedback 6 verify on lines 100 pages 45
export ORACLE_SID=agent12C
export ORACLE_HOME=AGENT_HOME
export PATH=$ORACLE_HOME/bin:$PATH

 emctl status agent

 emctl stop agent
emctl clearstate agent
emctl start agent
emctl status agent
emctl upload agent
Steps to run the tuning adviser manually

 1)

 set serveroutput on
declare
  l_sql_tune_task_id  varchar2(100);
begin
  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
                          sql_id      => 'xyxasdf',
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'test_1123',
                          description => 'tuning task for statement your_sql_id.');
 dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

2)

exec dbms_sqltune.execute_tuning_task(task_name => 'test_1123');

 3)

-- displaying the recommendations
set long 100000;
set longchunksize 1000
set pagesize 10000
set linesize 100
select dbms_sqltune.report_tuning_task('test_1123') as recommendations from dual;

 select NAME,CREATED,LAST_MODIFIED,TYPE,STATUS from dba_sql_profiles order by 2;

Recipie #1 - find blocking sessions with v$session

 SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;

 Recipie #2 - find blocking sessions using v$lock

 SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   v$lock l1, v$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;
SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;

Recipie #3 - blocking sessions with all available information

 The next query prints a few more information, it let's you quickly see who's blocking who. 

 Run this query and you can immediately call the colleague who's locking your table:

 SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l2.id2 = l2.id2 ;  

 Locks:

 SET PAGESIZE 14
SET VERIFY ON
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

 COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

 SELECT b.session_id AS sid,
       NVL(b.oracle_username, '(oracle)') AS username,
       a.owner AS object_owner,
       a.object_name,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
       b.os_user_name
FROM   dba_objects a,
       gv$locked_object b
WHERE  a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
Archive generation rate:
========================
set linesize 2000
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1;

Remote Diagnostic Agent (RDA) - Getting Started (Doc ID 314422.1)

Step 1: unzip the file xyz.zip
Step 2: Read readme UNIX file
Step 3: chmod 755 rda.sh
Step 4: ./rda.sh -S

------------------------------------------------------------------------------
DB.DB: Controls Oracle RDBMS Data Collection
------------------------------------------------------------------------------
Is there a Oracle database for RDA to analyze (Y/N)?
Hit "Return" to accept the default (Y)
>

Enter an Oracle User ID (user identifier only) to view DBA_ and V$ tables. If
RDA will be run under the Oracle software owner's ID, then enter a '/' here,
and select Y at the SYSDBA prompt to avoid being prompted for the database
password at run time.
Hit "Return" to accept the default (system)
> /

Is '/' a SYSDBA user (will connect as SYSDBA) (Y/N)?
Hit "Return" to accept the default (Y)
>



Step 5: ./rda.sh -v
Step 6: Upload the file to SR

Packaging collection results ...
RDA-00033: Warning - 8 command(s) not executed in EM.AGT, OS.OS module(s)


EM.AGT: Collects Oracle Enterprise Manager Agent Information
------------------------------------------------------------------------------
Do you want RDA to analyze Oracle Enterprise Manager Agent (Y/N)?
Hit "Return" to accept the default (N)
> Y

For Oracle Enterprise Manager Cloud Control 12c, enter the agent instance
directory location. For earlier versions, enter the Grid Control agent home
directory location.
Hit "Return" to accept the default (/usr/xysz/product/agent10g)
> /u01/xyz/agent12c/agent_inst

Enter the Oracle Enterprise Manager Cloud Control Agent core Oracle home
directory location
Hit "Return" to accept the default
( /u01/xyz/agent12c/core/12.1.0.3.0)
>


Step 5: ./rda.sh -v
Step 6: Upload the file to SR
If ORA-08104 error (ORA-08104: this index object < -- > is being online built or rebuilt) declare isclean boolean; begin isclean := false; while isclean = false loop isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN (dbms_repair.all_index_id, dbms_repair.lock_wait); dbms_lock.sleep (10); end loop; end; /
#################################################################################
Automated Database Maintenance Task Management
#################################################################################

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
END;

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;


SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

BEGIN
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;
Subscribe to: Comments (Atom)

QUERY FOR CPU USAGE

select  a.target_name as HOST, to_char(a.rollup_timestamp,' dd-Mon-yy::hh24:mi') as "DAY-TIME", sum(c.cpu_count) as ...

Copyright © Designed By: Templatezy