Daily Use Query/Command


For single tablesapce with max size 

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,
SUM (DECODE (Autoextensible,'YES', maxbytes/ 1048576,'NO', 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
and tu.tablespace_name='&tablespace_name';


Autoextensible file

col file_name for a65
select file_name,autoextensible,bytes/1024/1024 as "File Size",maxbytes/1024/1024 as "File Maxsize" from
dba_data_files where tablespace_name='&tbs_name';


Utilization of ASM diskgroup
select NAME,STATE,TOTAL_MB,FREE_MB, round ((FREE_MB/TOTAL_MB)*100,2) PCT_FREE from v$asm_diskgroup ;

select name,
round((total_mb-free_mb)/1024) USED_GB,
round(free_mb/1024) FREE_GB,
round(total_mb/1024) TOTAL_GB,
round(free_mb/total_mb*100) "FREE_%"
from v$asm_diskgroup where name like '%&name%' order by name;


 for resize Tablespace
 column value new_val blksize
select value
 from v$parameter
 where name = 'db_block_size'
/
select /*+ rule */ 'alter database datafile ''' ||
 file_name || ''' resize ' ||
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )
 || 'm;' cmd
from dba_data_files a,
    ( select file_id,
        max(block_id+blocks-1) hwm
        from dba_extents
       group by file_id ) b
where a.tablespace_name='&TBSPACE' and a.file_id = b.file_id(+)
 and
 ceil(blocks*&&blksize/1024/1024)-
     ceil((nvl(hwm,1)*
     &&blksize)/1024/1024 ) > 0
/




All tablespace details including Temp Tablespace
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select     tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,    decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,    decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
    from     dba_data_files group by tablespace_name union all
    select     tablespace_name || '  **TEMP**'
    ,    sum(bytes)/1024/1024 used_mb
    from     dba_temp_files group by tablespace_name) tsu
,    (select tablespace_name, sum(bytes)/1024/1024 free_mb
    from     dba_free_space group by tablespace_name) tsf
where    tsu.tablespace_name = tsf.tablespace_name (+)
order    by 4
/




All tablespace details including Temp Tablespace
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select     tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,    decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,    decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
    from     dba_data_files group by tablespace_name union all
    select     tablespace_name || '  **TEMP**'
    ,    sum(bytes)/1024/1024 used_mb
    from     dba_temp_files group by tablespace_name) tsu
,    (select tablespace_name, sum(bytes)/1024/1024 free_mb
    from     dba_free_space group by tablespace_name) tsf
where    tsu.tablespace_name = tsf.tablespace_name (+)
order    by 4
/


Tablespaces that are >=80% full, and how much to add to make them 80% again
set pages 999 lines 100
col    "Tablespace"    for a50
col    "Size MB"     for 999999999
col    "%Used"     for 999
col    "Add (80%)"     for 999999
select    tsu.tablespace_name "Tablespace"
,    ceil(tsu.used_mb) "Size MB"
,    100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
,    ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
    from    dba_data_files group by tablespace_name) tsu
,        (select ts.tablespace_name
    ,       nvl(sum(bytes)/1024/1024, 0) free_mb
    from    dba_tablespaces ts, dba_free_space fs
    where   ts.tablespace_name = fs.tablespace_name (+)
    group by ts.tablespace_name) tsf
where    tsu.tablespace_name = tsf.tablespace_name (+)
and    100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order    by 3,4
/


TEMP tablespace related query
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

   
select file_name,autoextensible,bytes/1024/1024 as "File Size",maxbytes/1024/1024 as "File Maxsize" from
dba_temp_files where tablespace_name='&tbs_name';


SELECT
   A.tablespace_name tablespace,
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
   v$sort_segment A,
(
SELECT
   B.name,
   C.block_size,
   SUM (C.bytes) / 1024 / 1024 mb_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.mb_total
/



 Database start time etc
set line 500
col OPEN_MODE format a15
col LOG_MODE format a20
col CREATED format a30
col host_name format a15
col INSTANCE_NAME format a15
col DATABASE_ROLE format a18
col DB_NAME format a15
col startup_time format a20
col LOGINS format a15
select d.dbid,i.instance_name,d.name "DB_NAME",d.open_mode,d.log_mode,d.database_role,i.LOGINS,to_char(i.startup_time,'DD-MM-YY HH24:MI:SS') "STARTUP_TIME",host_name from v$database d, v$instance i;


set line 500
select d.dbid,i.instance_name,d.name "DB_NAME",d.open_mode,i.LOGINS,to_char(i.startup_time,'DD-MM-YY HH24:MI:SS') "STARTUP_TIME",host_name from v$database d, v$instance i;


srvctl status database -d Instance_name1
srvctl status database -d Instance name

select INSTANCE_NAME,HOST_NAME,to_char(STARTUP_TIME,'dd-mon-yy hh24:mi:ss') created,STATUS,LOGINS from v$instance;

select name,open_mode,LOG_MODE,to_char(created,'dd-mon-yy hh24:mi:ss') created from v$database;



Daily Use Important Command


Count Number of Dtabafile at filesystem

find oradata*  -name '*.dbf' |wc -l                           


Crontab Entry

crontab -l
#  *    *    *    *    *     command to be executed
#  |    |    |    |    |
#  |    |    |    |    +---  day of week (0 - 6) (Sunday = 0)
#  |    |    |    +--------  month (1 - 12)
#  |    |    +-------------  day of month (1 - 31)
#  |    +------------------  hour (0 - 23)
#  +-----------------------  min (0 - 59)




Database Size 
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
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
/


Find Number of database running on linux server - 
ls -l $ORACLE_HOME/dbs/orapw*


Flashback related 

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;
Database altered.

SQL> SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES


Query check fregmented size of schemas::
select owner,sum(round((blocks*16)/1024,2))"Fragmented size MB",sum(round((num_rows*avg_row_len/1024/1024),2))"Actual size MB",sum(round
  2 (round((blocks*16)/1024,2)-round((num_rows*avg_row_len/1024/1024),2),2)) "Diff" from dba_tables where owner in('EPIMETA','EPIOP','EPIMART')
  3 group by owner;
to check fregmented size of all schmas in user :
SQL> select owner,sum(round((blocks*16)/1024,2))"Fragmented size MB",sum(round((num_rows*avg_row_len/1024/1024),2))"Actual size MB",sum(round(round((blocks*16)/1024,2)-round((num_rows*avg_row_len/1024/1024),2),2)) "Diff" from dba_tables group by owner;


DBA grant to normal user
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
RMAN                           TRUE  FALSE FALSE

SQL> select name from v$database;
NAME
---------
XXXX

SQL> grant sysdba to USERNAME;
Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
RMAN                           TRUE  FALSE FALSE
USERNAME                       TRUE  FALSE FALSE



Know the Growth trend of database
select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB 
from   v$datafile 
group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM') 
order by   1, 2;  



High water mark related query

set lines 1000
col file_name for a65
col tablespace for a15
col actual for a20


select tablespace_name tablespace
, file_name
, decode(actual_size,-1,'n/a (offline)',actual_size) actual
, decode(shrink_to,-1,'n/a',null,'can''t be shrinked',actual_size,'can''t be shrinked', shrink_to) shrink_to
from (
select -- locally managed files that can be resized (last used block < last free block)
d.tablespace_name
, d.file_id
, d.file_name
, d.bytes/1024 actual_size --MB
, nvl((m.maxblock-1)*t.blocksize/1024, (64*1024+t.bitmapped*t.blocksize)/1024) shrink_to
from dba_data_files d
, sys.ts$ t
, (select u.fileid file_id, max(u.block+u.length) maxblock --last used block +1
from dba_lmt_used_extents u group by fileid ) m
where m.file_id(+)=d.file_id
and d.tablespace_name =t.name
and t.bitmapped > 0
and d.bytes is not null --online
union -- Offline LMT (Offline DMT info is still available in datadict.)
select d.tablespace_name, d.file_id, d.file_name, -1, -1 /* -1 => n/a, null => cannot be resized */
from dba_data_files d
where bytes is null -- Offline LMT: Size is unknown
union -- dictionary managed (online + offline)
select ts.name, a.file#, df.name, ts.blocksize*f.blocks/1024
, case when nvl(min(a.bl-1),f.blocks) > 2 --nvl: no free space
then nvl(min(a.bl-1),f.blocks)*(ts.blocksize)/1024
else 2*(ts.blocksize)/1024 end
from (select f.ts#, f.file#, block# bl
from sys.fet$ f
start with block#=(select d.blocks-f.length+1 m from sys.file$ d where f.file#=d.relfile#)
--end of free space area = end of datafile
connect by prior block#=length+block# --if area before is also free space
) a
, v$dbfile df
, sys.file$ f
, sys.ts$ ts
where f.relfile# = a.file# (+) -- a no rows -> no free space
and f.ts# = ts.ts#
and f.file# = df.file#
and ts.bitmapped=0 --0=dictionary managed, else=locally managed
group by ts.name, a.file#, df.name, f.blocks , ts.blocksize
)
order by tablespace_name, file_name
/

 

Index rebuilding dynamic query
SQL> select 'alter index '||owner||'.'||SEGMENT_NAME||' rebuild tablespace XXXX;' from dba_segments where SEGMENT_TYPE='INDEX' and owner='XX1' and bytes/1024/1024 > 1;

'ALTERINDEX'||OWNER||'.'||SEGMENT_NAME||'REBUILDTABLESPACEREDWOOD;'
--------------------------------------------------------------------------------------------------------------------------------------------------------
alter index SYSJCS.RWS_CCOL$_PK rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_PK rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_FK_FMT$_IDX rebuild tablespace XXXX;
alter index SYSJCS.RWS_JOB$_FK_OBJ#_IDX rebuild tablespace XXXX;
 


Archive Generation Details
This is used to check archive log generated in MB for database in last few days -
set pages 100

set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated
from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
 



Longops related Query

col username format a15
set linesize 32767
col OPNAME format a40
col SOFAR format 999999999
col START_TIME format a20
select SID, username,OPNAME,SOFAR,TOTALWORK,SOFAR/TOTALWORK*100 "% of work done",to_char(START_TIME,'DD-MON-YY hh24:mi:ss') "START_TIME",
TIME_REMAINING,ELAPSED_SECONDS from v$session_longops where sofar!=TOTALWORK and TOTALWORK!=0
order by OPNAME desc;

 
Analyze how fast import is working

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;


To find out Expected Time of Completion for an Oracle Query

SELECT
opname,
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(time_remaining/60) Max_Time_Remaining_In_Min,
FLOOR(elapsed_seconds/60) Time_Spent_In_Min
FROM v$session_longops
WHERE sofar != totalwork;



SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
 

RMAN Backup related query
Set lines 300 pages 300
col START_TIME for a15
col COMPLETION_TIME for a15
col MESSAGE  for a50
col STATUS for a10
col BACKUP_TYPE for a30
col BACKUP_METHOD for a30
select START_TIME,COMPLETION_TIME,MESSAGE,STATUS,BACKUP_TYPE,BACKUP_METHOD from RMAN.RMAN_BACKUP order by START_TIME asc;

FRA Utilization 

select sum(PERCENT_SPACE_USED) from  v$FLASH_RECOVERY_AREA_USAGE;

set lines 200
select * from v$FLASH_RECOVERY_AREA_USAGE;

show parameter DB_RECOVERY_FILE_DEST

Utilization 

Database Component related

set lines 300 pages 300
col COMP_NAME for a50
archive log list
select * from NLS_DATABASE_PARAMETERS;
sho parameter db_block_size
select COMP_NAME,VERSION,STATUS from dba_registry order by 1;

Logfine Related
set linesize 300
col MEMBER format a90
col GROUP# format 99
select member,group#,status,type from v$logfile;

select GROUP#,members,status,bytes/1024/1024 "size-MB" from v$log order by group#;


Session Related

SQL> select * from user_resource_limits a where a.resource_name in ('IDLE_TIME','CONNECT_TIME');

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME                        UNLIMITED
CONNECT_TIME                     UNLIMITED




SQL> SELECT COUNT(*)   FROM v$session;

  COUNT(*)
----------
        51


SQL>  SELECT name, value FROM v$parameter WHERE name = 'sessions';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
sessions
555



SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     555
shared_server_sessions               integer

Basic tasks of an Oracle DBA

Evaluate the Database Server Hardware

Install the Oracle Software

Plan the Database

Create and Open the Database

Back Up the Database

Enroll System Users

Implement the Database Design

Back Up the Fully Functional Database

Tune Database Performance

Difference between Oracle database 9i and 10g & Difference Between Oracle database 10g and 11g

Summary Of Differences Between oracle Database 9i and 10g.

The below Points are completely New in oracle database 10g (Not there in oracle 9i)

• Automatic Storage Management(ASM).

• Automatic Shared Memory Management(ASMM).

• Automatic Database Diagnostic Monitor(ADDM).

• Automatic Workload Repository(AWR).

• Flashback Technologies.

• Data Pump replaces Traditional EXP/IMP.

• Automatic Checkpoint Tunning(FAST_START_MTTR_TARGET).

• Automatic Undo Retention Tunning.

• Introduced Default Permanent Tablespace (USERS).

• Introduced SYSAUX tablespace.

• Streams Technology(STREAMS POOL).

• Introduced Big file Tablespace Option and Rename Tablespace Command.
• Automatic SQL Tunning.

• Temporary Tablespace Group and Default Temporary Tablespace.

• Recovery Manager Enhancements(RMAN).

• DBMS Scheduler Packages and DBMS File Transfer Packages.

Detailed Explanation of the above summary of differences  between oracle database 10g and 9i.

Automatic Storage Management(ASM).

ASM  means Automatic Storage Management to simplify the Storage of datafiles , controlfiles and  Redolog files.ASM  is the extension of OMF (oracle managed files).ASM functionality is controlled by ASM instance.It is not full database instance its just  a memory structures.

The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files.

ASM provides the following the functionalities

• Manages groups of disks, called disk groups.

• Manages disk redundancy within a disk group

• Supports large files.

Initialization Parameters and ASM instance Creation

• The initialization parameters that are of specific interest for an ASM instance are

• INSTANCE_TYPE – Set to ASM or RDBMS depending on the instance type. The default is RDBMS.

• DB_UNIQUE_NAME – Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.

• ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.

• ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.

• ASM_DISKSTRING – Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

Automatic Shared Memory Management(ASMM).

Oracle database 10g’s New features.By default sga_target  is enabled.But  always sga_max_size is greater than or equal to sga_target.So once sga_target is enabled means, no need to set the auto tuned parameters like

1. db_cache_size
2. shared_pool_size
3. large_pool_size
4. java_pool_size

So ASMM automatically readjusts the sizes of the main pools.

Automatic Database Diagnostic Monitor(ADDM).

ADDM means automatic database diagnostic monitor and is a oracle database 10g’s new features. ADDM does analysis of the database, identifies problems and their potential causes, and comes up with recommendations for fixing the problems. It can call all other advisors also.

ADDM stores the snapshot in SYSAUX tablespace.

The main features of the ADDM are as follows

• ADDM runs automatically in the background process MMON whenever a snapshot of in-memory statistics is taken. ADDM does analysis of the statistics collected between two snapshots.

• ADDM analysis results are written back to the workload repository for further use.

• ADDM uses the new wait and time statistics model, where activities with high time consumption are analyzed on a priority basis. This is where the big impact lies.

• ADDM can also be invoked manually

The Automatic Database Diagnostic Monitor (ADDM) is a new diagnosis tool that runs automatically every hour, after the AWR takes a new snapshot. The ADDM uses the AWR performance snapshots to locate the root causes for poor performance and saves recommendations for improving performance in SYSAUX.

ADDM also gives the recommendations for

• Sql tuning advisor
• Sql access advisor
• Segment advisor
• Undo advisor
• Redo log file size advisor

Automatic Workload Repository (AWR)

AWR is the oracle database 10g’s new features.Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR). In your database some repository tables are created automatically when database is created.Each and every seconds AWR collects issues and activities in the database.

The repository is a source of information for several other Oracle 10g features.

• Automatic Database Diagnostic Monitor

• SQL Tuning Advisor

• Undo Advisor

• Segment Advisor

To check the AWR status, Show parameter statistics_level. If you want to enable the AWR report set statistics_level=Typical.If you want to disable the AWR report statistics_level=normal.

When you enable the AWR report the MMON (Manageability Monitor) background process will active.It will write issues in the repository tables. By default Every one hour AWR will generate a snapshot in SYSAUX tablespace and the default retention period is 7 days.If you want to take snapshot every 15 minutes and retention period 10 days means

begin

dbms_workload_repository.modify_snapshot_settings(

retention=>14400,       —–Minutes (=30 days). Current Value retained if null

interval=>15);              — Minutes .Current value retained if null.

End;

/

Flashback Technologies

Flashback technologies is the oracle database 10g’s new features.

Types of Flashback Technologies

There are six basic types of Flashback recovery, discussed below in detail:

• Flashback Query

• Flashback Version Query

• Flashback Transaction Query

• Flashback Table

• Flashback Drop (Recycle Bin)

• Flashback Database

How to Configure the flashback

In mount stage only we can enable the flashback technologies and  disable

SQL>alter database flashback on;         —–Enable

SQL>alter database flashback off;        ——–Disable

Flashback Query

You can perform a Flashback Query using a SELECT statement with an AS OF clause. You can use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.

his example uses a Flashback Query to examine the state of a table at a specified time in the past. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JON has been deleted from the employee table, and the DBA knows that at 9:30AM the data for JON was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data has been lost. If appropriate, the DBA can then re-insert the lost data in the database.

The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2006:

sql>select * from  employee as of timestamp to_timestamp(‘

2006-04-04 09:30:00′ , ‘YYYY-MM-DD HH:MI:SS ‘) where name=’JHON’;

this updates restores Jhon’s information to the employee table

sql> insert into employee (select * from employee as of timestamp to_timestamp(‘ 2006-04-04 09:30:00′ , ‘YYYY-MM-DD HH:MI:SS ‘) where name=’JHON’);

Flashback Version Query

Not only can the DBA run a manual analysis, but this is a powerful tool for the application’s developer as well. You can build customized applications for auditing purposes. Now everyone really is accountable for his or her actions. Various elements for this are shown below

Versions_xid-The transaction id that created this version of the row

Versions_operations-The action that created this version of the row (such as delete, insert, and update).

Versions_startscn-The SCN in which this row version first occurred

Versions_Endscn-The SCN in which this row version was changed

For example: we use the Dept table in Scott schema and update dept 10 to 11, then 12, and then 13 (with a commit after every update). Thus we have done three updates <!–[if gte mso 9]–>For extended help options we can use the help extended command from within the ADR command tool and shown below

We can get detailed explanations of an option such as the BEGIN BACKUP command which will present a series of screens much like that found in the old UNIX man pages.

What you should finally realize at this point is that Oracle 11g has taken monitoring to a whole new level over 10g release. In fact, think of it as monitoring on steroids. We will next view an example of how to look at the log files for database monitoring with Oracle 11g on Red Hat Linux

Lets scroll down and see more options for the show alert option with ADRCI

As you can see, there are now options to review the old style alert.log files as well as the ability to view the log files from the Oracle listener and host. We can also parse the log file to see just a recent activity which is useful for quick monitoring tasks. Lets take a peek at one of the recent log files in the next example.
We choose option 1 and the alert log file is displayed below:
By default the log files are stored under the Linux /tmp directory structure.

To exit and return to the main ADR command screen we use the :q command within the Linux vi editor.

The ADR command tool also lets us view the new health monitor reports and functions.

For instance, if I wish to look at the most recent health monitor execution tasks then I can simply issue the show hm_run command within the ADR command interface

Also of note with Oracle 11g are several new V$ tables that correspond to the ADR and new health monitoring features:

V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_FINDING
V$HM_INFO
V$HM_RECOMMENDATION
V$HM_RUN

Online Patching

Online patching introduced in oracle database 11g. This simplifies administration, because no downtime is needed, and also results in a much quicker turnaround time for installing or de-installing Online Patches.

A regular RDBMS patch can require many minutes to install, since it requires instance shutdown, a relink, and instance startup. On the other hand, you can install an online patch in just a few seconds

Online patches are only applicable for Oracle RDBMS and not any other products. Online patches are currently not supported in Windows, and only supported on the following UNIX platforms for version 11.1.0.7.0 and later:

• Linux x86
• Linux x86_64
• HP-UX Itanium (HP-UX 11.31 and later)

Solaris SPARC 64-bit (Solaris 10 and later)

Temporary Tablespace shrink

Oracle database 11g introduced the temporary tablesapce shrink feature.

SQL>alter tablespace temp1 shrink space;

This deallocates all the unused segments from the tablespace and shrinks it. After the above operation, you can check the view DBA_TEMP_FREE_SPACE to check how much the allocated space and free space currently is.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP            179306496       179306496      178257920

Tags: 10g, 11g, 9i, ADDM, ASM, ASMM, AWR, Between, database, datapump, dataQapps, Difference, differences, expdp, Flashback, impdp, Oracle