How to get the time difference between two date columns?

Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.

Let's investigate some solutions. Test data:

SQL> CREATE TABLE dates (date1 DATE, date2 DATE);
Table created.
SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);
1 row created.
SQL> SELECT (date1 - date2) FROM dates;
DATE1-DATE2
-----------
1
.041666667
.000694444

Solution 1:
---------------------------------------------

SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2 || ' HOURS ' ||
3 floor((((date1-date2)*24*60*60) -
4 floor(((date1-date2)*24*60*60)/3600)*3600)/60)
5 || ' MINUTES ' ||
6 round((((date1-date2)*24*60*60) -
7 floor(((date1-date2)*24*60*60)/3600)*3600 -
8 (floor((((date1-date2)*24*60*60) -
9 floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
10 || ' SECS ' time_difference
11 FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS


Solution 2:
-----------------------

If you don't want to go through the floor and ceiling maths, try this method.

SQL> SELECT to_number( to_char(to_date('1','J') +
2 (date1 - date2), 'J') - 1) days,
3 to_char(to_date('00:00:00','HH24:MI:SS') +
4 (date1 - date2), 'HH24:MI:SS') time
5 FROM dates;
DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00

User Managed Recover of Datafile when Backup is not Available

If a datafile is damaged and you don't have any backup of the datafile then still you can recover your data file if the following conditions met.

1)You have all archived redo logs available since the data file creation.

2)The control file contains the name of the lost file. That means either the control file is current or it is taken after the data file creation.

3)The datafile does not belong to SYSTEM tablespace.

To illustrate the scenario I will demonstrate the whole procedure with an example.

A)Add a datafile to a tablespace.

SQL> ALTER TABLESPACE USER_TBS ADD DATAFILE '/oradata2/data1/dbase/datafile03.dbf' SIZE 1M;
Tablespace altered.

B)Drop the datafile.

SQL> !rm /oradata2/data1/dbase/datafile03.dbf

C)Make the affect data file offline if the database is open.

SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 7 - file is in use or recovery
ORA-01110: data file 7: '/oradata2/data1/dbase/datafile03.dbf'

It can't rename as database currently using this file. So, make it offline.

SQL> alter database datafile '/oradata2/data1/dbase/datafile03.dbf' offline;
Database altered.

D) a new, empty datafile to replace a damaged datafile that has no corresponding backup. Here I craeted the damaged file 7 as to new location '/oradata1/arju/created_new.dbf'.

SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
Database altered.

E)Perform media recovery on the empty datafile.
SQL> RECOVER DATAFILE '/oradata1/arju/created_new.dbf';
Media recovery complete.

F)Make the datafile online.

SQL> alter database datafile '/oradata1/arju/created_new.dbf' ONLINE;
Database altered.

Now we will look at the scenario if while starting database it can't find datafile.

SQL> !rm /oradata1/arju/created_new.dbf

Shutdown immediate will fail as it can't find one datafile.

SQL> shutdown immediate;
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 88080424 bytes
Database Buffers 71303168 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'


SQL> ALTER DATABASE CREATE DATAFILE '/oradata1/arju/created_new.dbf' AS '/oradata2/data1/dbase/datafile03.dbf';

Database altered.

SQL> RECOVER DATAFILE 7;
Media recovery complete.

SQL> ALTER DATABASE OPEN;
Database altered.

How to Restore spfile from backup

1.Connect to target Database with RMAN.

i)If you have lost your spfile while your database is running then,

RMAN>CONNECT TARGET /

ii)If your database is not up and you don't use recovery catalog then use,
RMAN>CONNECT TARGET /
RMAN>SET DBID 3386862614

2)Start the instance with dummy parameter file.
RMAN>STARTUP FORCE NOMOUNT

3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;

If you want to restore to a pfile then use,
RMAN> RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';

4)Start the instance.
RMAN>STARTUP;

Recover a Lost Datafile without backup.

In my database I have added one data file. I have not back up that data file. Now someone accidentally drop that datafile. Can I get back the my data file now? The answer is yes if my database run in archivelog mode and I have the available archive logs science the creation of datafile.

With an example I will demonstrate the procedure.

1)I create tablespace data with one datafile and after some time I have added another datafile. Both data file don't have any backup.

SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG

SQL> create tablespace data datafile '/oradata2/data.dbf' size 2M;
Tablespace created.

SQL> alter tablespace data add datafile '/oradata2/data1/data02.dbf' size 2M;
Tablespace altered.

SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/users01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/system01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
6 rows selected.

2)Creating Tables to Populate the Datafiles.

SQL> create table before_delete tablespace data as select level a1 from dual connect by level <9999; Table created. SQL> insert into before_delete select level a1 from dual connect by level <9999; 9998 rows created. SQL> commit;
Commit complete.

SQL> select file_name from dba_data_files where file_id in (select file_id from dba_extents where segment_name='BEFORE_DELETE');

FILE_NAME
--------------------------------------------------------------------------------
/oradata2/data.dbf
/oradata2/data1/data02.dbf

3)Now delete both datafile by OS command.

SQL> !rm /oradata2/data.dbf

SQL> !rm /oradata2/data1/data02.dbf

4)Now I want to get back both datafile. Connect to RMAN and make the affected Tablespace offline immediate.

SQL> !rman target /
RMAN> sql'ALTER TABLESPACE DATA OFFLINE IMMEDIATE';

5)Perform Recovery of The tablespace.
RMAN> RECOVER TABLESPACE DATA;

6)Make the status online of the tablespace.
RMAN> sql'ALTER TABLESPACE DATA ONLINE';

In this case oracle at first create and empty datafile and then apply all archived redo logs and online redo logs on the tablespace up to the current time.

Performing Tablespace Point-in-time Recovery.

With an example I will make you understand RMAN Fully Automated TSPITR. Workaround I will truncate a table which resides on USERS tablespace and later I will recover the tablespace to get back my data.

Note that my interaction with the RMAN client is shown as BOLD.

A)Get the rows of my_table.

SQL> select count(*) from my_table;
COUNT(*)
----------
49792
B)Truncate the Table.

SQL> TRUNCATE TABLE MY_TABLE;
Table truncated.

C)Determine the Tablespace which is belong my Dropped Table.

SQL> SELECT TABLESPACE_NAME from DBA_TABLES WHERE TABLE_NAME='MY_TABLE';
TABLESPACE_NAME
------------------------------
USERS

D)Connect to rman and perform TSPITR operation on USERS Tablespace.

SQL> !rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 04:49:54 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)

RMAN> RECOVER TABLESPACE USERS UNTIL TIME 'SYSDATE-1/24/60*5' AUXILIARY DESTINATION '/export/home/oracle';

Starting recover at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time

List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1

Creating automatic instance, with SID='jDiz'

initialization parameters used for automatic instance:
db_name=DBASE
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DBASE_jDiz
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/export/home/oracle
control_files=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f


starting up automatic instance DBASE

Oracle instance started

Total System Global Area 205520896 bytes

Fixed Size 2019576 bytes
Variable Size 146804488 bytes
Database Buffers 50331648 bytes
Redo Buffers 6365184 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until time "SYSDATE-1/24/60*5";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f
Finished restore at 08-MAY-08

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "SYSDATE-1/24/60*5";
plsql <<<-- tspitr_2 declare sqlstatement varchar2(512); offline_not_needed exception; pragma exception_init(offline_not_needed, -01539); begin sqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover'; krmicd.writeMsg(6162, sqlstatement); krmicd.execSql(sqlstatement); exception when offline_not_needed then null; end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 4 to
"/oradata2/data1/dbase/users01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 4;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 4 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace USERS offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_temp_%u_.tmp in control file

Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_%u_.dbf
restoring datafile 00002 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_%u_.dbf
restoring datafile 00004 to /oradata2/data1/dbase/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 08-MAY-08

datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_425hqsjn_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_425hqsjr_.dbf

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 4 online

Starting recover at 08-MAY-08
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc
archive log thread 1 sequence 4 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc thread=1 sequence=3
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:07
Finished recover at 08-MAY-08

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\
(PROGRAM=/oracle/app/oracle/product/10.2.0/db_1/bin/oracle\)\
(ARGV0=oraclejDiz\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\
(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=jDiz^'\)\)\(CONNECT_DATA=\(SID=jDiz\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
USERS file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace USERS online";
sql "alter tablespace USERS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script


Export: Release 10.2.0.1.0 - Production on Thu May 8 04:51:58 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table DEPT
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table BONUS
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE
EXP-00091: Exporting questionable statistics.
. . exporting table MY_TABLE
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.1.0 - Production on Thu May 8 04:52:19 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "DEPT"
. . importing table "EMP"
. . importing table "BONUS"
. . importing table "SALGRADE"
. importing ARJU's objects into ARJU
. . importing table "MY_TABLE"
. importing SCOTT's objects into SCOTT
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace USERS online

sql statement: alter tablespace USERS offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /export/home/oracle/cntrl_tspitr_DBASE_jDiz.f deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_system_425hqsjn_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_undotbs1_425hqsjr_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_temp_425hs9ot_.tmp deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_1_425hs5oy_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_2_425hs6hl_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_3_425hs7kc_.log deleted
Finished recover at 08-MAY-08

E)Make the backup of Tablespace and make it Online.

RMAN> SQL'ALTER TABLESPACE USERS ONLINE';
sql statement: ALTER TABLESPACE USERS ONLINE

RMAN> exit;

Recovery Manager complete.

F)Check the Objects.

SQL> select count(*) from my_table;

COUNT(*)
----------
49792

Various RMAN Backup Commands.

1)Full Database Backup:
------------------------------
RMAN>BACKUP DATABASE;

2)Individual Tablespaces Backup:
------------------------------------
To Backup tablespace data01 and data02 only to tape,
BACKUP DEVICE TYPE sbt TABLESPACE data01,data02;

3)Individual Datafiles and Datafile Copies with RMAN
--------------------------------------------------------------
To Backup datafile 1 through 4 to tape and to backup datafile system01.dbf located in /oradata from disk to tape use the following,
BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY '/oradata/system01.dbf';

4)Backing Up Control Files
---------------------------------
•If CONFIGURE CONTROLFILE AUTOBACKUP is ON (by default it is OFF), then RMAN automatically backs up the control file and server parameter file after every backup and after database structural changes.

•Manually controlfile can be backed up by BACKUP CURRENT CONTROLFILE;

•Also manually it is backed up when we use BACKUP TABLESPACE users INCLUDE CURRENT CONTROLFILE;

•Manually, when we back up datafile 1, RMAN automatically includes the control file and SPFILE in backups of datafile 1. But if the control file block size is not the same as the block size for datafile 1, then the control file cannot be written into the same backup set as the datafile. RMAN writes the control file into a backup set by itself if the block size is different.

•When controlfile is backed up manually (the above three), the only RMAN repository data for backups within the current RMAN session is in the control file backup, and also a manually backed-up control file cannot be automatically restored.

5)Backing Up SP Files:
------------------------------
•If CONFIGURE CONTROLFILE AUTOBACKUP is ON (by default it is OFF), then RMAN automatically backs up the control file and server parameter file after every backup and after database structural changes.

•Explicitly SPfile can be backed up by BACKUP SPFILE;

•If database is not started with SPfile then SPfile can't be backed up.

6)Backing Up Archived Redo Logs
------------------------------------------
BACKUP ARCHIVE ALL DELETE INPUT;
-This command will backup just one archivelog and delete just one log archive destination.

BACKUP ARCHIVE ALL DELETE ALL INPUT;
-This command will backup just one archivelog and delete all log archive destination.

BACKUP DATABASE PLUS ARCHIVELOG;
-This command backs up the database and all archived logs.
-If CONFIGURE BACKUP OPTIMIZATION ON is set then RMAN skips backups of archived logs that have already been backed up to the specified device.

The LIST, REPORT, and DELETE Commands in RMAN

List Command:
The LIST command uses RMAN repository information and provide lists of backups, archived logs, and database incarnations.

Output of LIST command can be filtered BY BACKUP and BY FILE option.

1)RMAN> list backup; #List all your backup sets.
2)RMAN>LIST BACKUPSET; #Lists only backup sets and proxy copies.
3)RMAN>LIST COPY; #Lists of Image copies and Archive Logs.
4)RMAN>LIST EXPIRED BACKUP; #Backups did not found after crosscheck. That is backup is manually moved or deleted from OS.
5)RMAN>LIST BACKUP BY FILE; #List backup by Datafile, controlfile, spfile.
6)RMAN>LIST BACKUP SUMMARY; #Lists backup sets, proxy copies, and disk copies.
7)LIST BACKUP OF DATABASE; LIST BACKUP LIKE '/tmp/%'; list backup of datafile 1; LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '17-MAR-2008' AND '22-MAR-2008'; are also available.
8) LIST INCARNATION; LIST INCARNATION OF DATABASE; to see the incarnations of your database.

Report Command:
RMAN REPORT command analyzes the available backups and return results about while files need backup which files are obsolete etc.

Remember the result of REPORT command is based on repository data. So, if backups have been deleted from disk or tape outside of RMAN, reports generated by RMAN do not automatically reflect these changes.

So before report it is good to run CROSSCHECK of all backup in order to update repository.

1)REPORT NEED BACKUP; # Determine which database files need backup under a specific retention policy.

2)REPORT UNRECOVERABLE; #Report which database files require backup because they have been affected by some NOLOGGING operation.

3)REPORT SCHEMA; #Lists and displays information about the database files.

4)REPORT OBSOLETE; #REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy.

5)REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE data01;

If you use Recovery Catalog then past data can be shown.
Like, REPORT SCHEMA AT TIME 'SYSDATE-10';


Delete Command:
Delete commands is used to delete any backup or backupsets.
1)Delete all your backup sets RMAN> delete backupset all;
2)Delete all image copies. RMAN> delete copy all;
3)Delete obsolete Backups. RMAN>DELETE OBSOLETE;

About RMAN Incremental Backup

RMAN incremental backups back up only datafile blocks that have changed since a
specified previous backup.

Each data block in a datafile contains a system change number (SCN), which is the
SCN at which the most recent change was made to the block. During an incremental
backup, RMAN reads the SCN of each data block in the input file and compares it to
the checkpoint SCN of the parent incremental backup. If the SCN in the input data
block is greater than or equal to the checkpoint SCN of the parent, then RMAN copies
the block.

If you use block change tracking feature then RMAN can refer change tracking file the changed block in the datafile in stead of scanning the whole datafile. I will show in other topic how I can enable block change tracking.

Incremental Backup can be either level 0 or level 1 backup.

Level 0 Incremental Backup:
------------------------------------
A level 0 incremental backup is the base backup for subsequent incremental backups.

It copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup
is not included in an incremental strategy. That is after taking full backup , you can’t perform incremental backup over it.

Level 0 backup of database is taken by

RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;

Level 1 Incremental Backup:
------------------------------------------------------
Level 1 incremental backup can be two types,

1) Differential Level 1 Backup: It backs up all blocks changed after the most recent
incremental backup at level 1.

If no level 1 found then backs up all blocks after most recent incremental backup at level 0.

If no level 1 and level 0 is found then the behavior is based on COMPATIBILITY settings.

If compatibility is >=10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup.

If compatibility <10.0.0, RMAN generates a level 0 backup. This is the default level 1 incremental backup. Whenever you use RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE;
differential backup is performed on database.

2) Cumulative Level 1 Backup: It backs up all blocks changed after the most recent incremental backup at level 0. Level 1 cumulative backup can be taken by,

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

How to Change Database Name and DBID?

Prior to introduction of DBNEWID utility it was possible to change the name of the database by manually creating a new control file but it was not possible to give new dbid to the database.

The DBID is an internal, unique identifier for a database. RMAN distinguishes databases by DBID, so you could not register a seed database and a manually copied database together in the same RMAN repository.

DBNEWID solves this. With DBNEWID utility you can change either database name or database id or both.

However, changing DBID is a serious procedure. When you change DBID previous backups, archived redo logs become invalid.

Procedure of changing DBID and Database Name:

1)Take a recoverable full database backup.
2)Mount the database.
3)With sysdba privilege, invoke nid
i) To change only DBID just invoke nid target=username/pass
ii) To change both DBID and DBNAME invoke nid target=username/pass DBNAME=new_database_name
iii)To change only DBNAME invoke nid target=username/pass DBNAME=new_database_name SETNAME=y

i)Change only DBID:
To change only DBID just enter the following command,
SQL>host nid target=arju/a
Where arju is a user having sysdba system priviege. And password of arju is a.

ii)Change both DBID and DBNAME:
To change the database name in addition to DBID enter the following command.
SQL>host nid=arju/a DBNAME=arjut
which changes the DBID to a new DBID (You can't set DBID though as your wish) and change the database name to arjut.
In this case the follow operations are performed is below.

1)The DBNEWID utility performs validations in the headers of the datafiles and
control files before attempting I/O to the files.

2)If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt)

3)Then changes the DBID and the DBNAME for each datafile, including offline normal and read-only datafiles,

4)Shuts down the database, and then exits.

iii)Change only Database Name:
In the following example I will try to demonstrate to change the Database name.


1)SQL> select dbid, name from v$database;

DBID NAME
---------- ---------
246608360 ARJU

2)SQL> shutdown imemdiate;
startup mount;
3)SQL> host nid target=arju/a DBNAME=ARJUT setname=Y

DBNEWID: Release 10.2.0.1.0 - Production on Wed Apr 9 16:21:33 2008

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

Connected to database ARJU (DBID=246608360)

Connected to server version 10.2.0

Control Files in database:
/oradata/Arju/arju/control01.ctl
/oradata/Arju/arju/control02.ctl
/oradata/Arju/arju/control03.ctl

Change database name of database ARJU to ARJUT? (Y/[N]) => y
Instance shut down

Database name changed to ARJUT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.


4)SQL> !export ORACLE_SID=ARJUT
5)SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
6)SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string arju
SQL> alter system set db_name=ARJUT scope=spfile;

System altered.

7)SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
8)SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
9)SQL> select name,dbid from v$database;

NAME DBID
--------- ----------
ARJUT 246608360

RMAN-06026, RMAN-06023 During Restore Using RMAN

RMAN-06026, RMAN-06023 During Restore Using RMAN
Problem Description:
--------------------
When attempting to restore database the error stack follows.
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
<.......>
RMAN-06023: no backup or copy of datafile 1 found to restore


Solution Description
--------------------
Some common reasons why a file can not be restored are that,

1)There is no backup or copy of the file that is known to recovery manager, or there are no backups or copies that fall within the criteria specified on the RESTORE command, or

2)Some datafile copies have been made but not cataloged.

Scenario 1:
-------------
You have added a new tablespace since your last backup.Now you issued just RESTORE DATABASE command without "SET UNTIL" clause. The restore job fails because it is looking for a backup of the datafile that belongs to the the new tablespace that has been added since the last backup was taken.

Solution Description:
You need to specify the "SET UNTIL" time clause in the restore job to a time
before the new tablespace was added.

Explanation:
When a restore command is run from RMAN and if no "SET UNTIL" clause is defined, RMAN will try and restore backups for all the datafiles in the database at the present time. If a "SET UNTIL" clause is defined, then RMAN will restore only the datafiles that exist in the database that match the specified point of time in the "SET UNTIL" time clause.


Scenario 2:
--------------
You are attempting to restore a database using Oracle Recovery Manager (RMAN) using a 'set time' parameter to do a point-in-time recovery and the error stack came as above. Whenever you invoke 'list backupset of database' command shows there to be multiple backups of these files available.

Solution Description:
You have issued a 'resetlogs' prior to the last backup but before the 'Until Time' clause in the RMAN script. For instance, the last backup of the database was June 8, 2008.On June 9, you opened the database with resetlogs. Then, you decide to restore the database to a point in time on June 10. Because you cannot roll forward through the resetlogs, RMAN cannot find any legitimate backups to restore from within this incarnation.

The solution is to set the 'until time' clause to a time before the resetlogs.


Explanation:
You need to check the incarnation of the database:

rman>list incarnation of database;

If the current incarnation reset time falls between the last backup and the time specified for 'Set Time,' then the recovery catalog acknowledges that there are no backups that match the time criteria specified, and errors out with RMAN-6023.
Related Documents

How does one select EVERY Nth row from a table?

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:

Method 1: Using a subquery


SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);


Method 2: Use dynamic views (available from Oracle7.2):


SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.ROWNUM,4) = 0;


Method 3: Using GROUP BY and HAVING


SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n;


Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation

How does one select the LAST N rows from a table?

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows. Examples:

Get the bottom 10 employees based on their salary


SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank
FROM emp )
WHERE sal_rank <= 10;


Select the employees getting the lowest 10 salaries



SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;

How does one select the TOP N rows from a table?

After Oracle 9i there is the RANK() and DENSE_RANK() functions which can be used to determine TOP N rows.

Below is the examples to find the top 5 employees based on their salary.

Way 1: Using RANK()

SELECT employee_name, salaryFROM
( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rank
FROM employee )
WHERE salary_rank <= 5;

Way 2: Using Dense_Rank() 
SELECT employee_name, salary FROM
( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rank FROM employee ) WHERE salary_dense_rank <= 5;

Way3: Using inner query This is an example of using an inner-query with an ORDER BY clause: 
SELECT * FROM
(SELECT * FROM employee ORDER BY salary DESC)
WHERE ROWNUM < 5;

Way 4: Using count distinct combination 
SELECT * FROM employee e WHERE 5 >= (SELECT COUNT(DISTINCT salary)
FROM employee b
WHERE b.salary >= e.salary)
ORDER BY salary DESC;

Map/ concatenate several rows to a column

Start by creating this function:


SQL> CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
2 ret VARCHAR2(4000);
3 hold VARCHAR2(4000);
4 cur sys_refcursor;
5 BEGIN
6 OPEN cur FOR q;
7 LOOP
8 FETCH cur INTO hold;
9 EXIT WHEN cur%NOTFOUND;
10 IF ret IS NULL THEN
11 ret := hold;
12 ELSE
13 ret := ret || ',' || hold;
14 END IF;
15 END LOOP;
16 RETURN ret;
17 END;
18 /
Function created.


This function returns a string result with the concatenated non-NULL values from a SQL statement. It returns NULL if there are no non-NULL values.

Here is an example of how to map several rows to a single concatenated column:



SQL> SELECT rowconcat('SELECT dname FROM dept') AS departments
2 FROM dual;
DEPARTMENTS
--------------------------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS


This example is more interesting, it concatenates a column across several rows based on an aggregation:


SQL> col employees format a50
SQL> SELECT deptno,
2 rowconcat('SELECT ename FROM emp a WHERE deptno='||deptno) AS Employees
3 FROM emp
4 GROUP BY deptno
5 /
DEPTNO EMPLOYEES
---------- --------------------------------------------------
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
20 SMITH,JONES,SCOTT,ADAMS,FORD
10 CLARK,KING,MILLER

How does one code a matrix/crosstab/pivot report in SQL?

Newbies frequently ask how one can display "rows as columns" or "columns as rows". Look at these example crosstab queries (also sometimes called transposed, matrix or pivot queries):



SELECT *
FROM (SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job)
ORDER BY 1;
JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600


Here is the same query with some fancy headers and totals:


SQL> ttitle "Crosstab Report"
SQL> break on report;
SQL> compute sum of dept10 dept20 dept30 dept40 total on report;
SQL>
SQL> SELECT *
2 FROM (SELECT job,
3 sum(decode(deptno,10,sal)) DEPT10,
4 sum(decode(deptno,20,sal)) DEPT20,
5 sum(decode(deptno,30,sal)) DEPT30,
6 sum(decode(deptno,40,sal)) DEPT40,
7 sum(sal) TOTAL
8 FROM emp
9 GROUP BY job)
10 ORDER BY 1;

Mon Aug 23 page 1
Crosstab Report

JOB DEPT10 DEPT20 DEPT30 DEPT40 TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
---------- ---------- ---------- ---------- ----------
sum 8750 10875 9400 29025


Here's another variation on the theme:


SQL> SELECT DECODE(MOD(v.row#,3)
2 ,1, 'Number: ' ||deptno
3 ,2, 'Name: ' ||dname
4 ,0, 'Location: '||loc
5 ) AS "DATA"
6 FROM dept,
7 (SELECT rownum AS row# FROM user_objects WHERE rownum < deptno =" 30"

Find indexes and assigned columns for a table

It is very common that you want to know / see the available index on a table. And also you sometime want to know the columns which are assigned to the indexes.

The view dba_ind_columns or user_ind_columns or all_ind_columns help lot in this regard. As you may know dba_* show all available index information, user_* is for the available in current schema and all_* is for all indexes that current user has permission to it.

The following script will help to identify the owner, table_name associated index and associated column.

SQL>COL index_owner FORMAT A20
column table_owner format a30
column table_name format A28
column index_name format A28
column column_name format A28
COL POS FORMAT 999

SQL> Select index_owner, table_name, index_name, column_name, column_position POS
FROM dba_ind_columns
Where index_owner='&owner_name'
AND table_name='&table_name' Order by index_NAME;

In order to know the avilable index on a table issue,

SQL> SELECT UNIQUE INDEX_NAME FROM dba_ind_columns WHERE TABLE_NAME='&TABLE_NAME';

Example:
------------
SQL> create table test_index_col( a number primary key, b number);
Table created.


SQL> create index test_I on test_index_col(b);
Index created.

SQL> Select index_owner, table_name, index_name, column_name, column_position POS
FROM dba_ind_columns
Where index_owner='&owner_name'
AND table_name='&table_name' Order by index_NAME; 2 3 4
Enter value for owner_name: ARJU
old 3: Where index_owner='&owner_name'
new 3: Where index_owner='ARJU'
Enter value for table_name: TEST_INDEX_COL
old 4: AND table_name='&table_name' Order by index_NAME
new 4: AND table_name='TEST_INDEX_COL' Order by index_NAME

INDEX_OWNE TABLE_NAME INDEX_NAME COLUMN_NAME POS
---------- ------------------ ------------------ ------------------ ----
ARJU TEST_INDEX_COL SYS_C006341 A 1
ARJU TEST_INDEX_COL TEST_I B 1

Task Manager has been disabled by your administrator

Symptoms of The Problem:
-----------------------------------------
Whenever you try to open the task manager using CTRL+ALT_DEL or right click on the startup bar and task manager button is disable of if enabled clicking it shows the following message,
"Task Manager has been disabled by your administrator".

Cause of The Problem:
--------------------------------------------
There may be several causes behind the problem.
1)The account your are using is blocked via the "Local Group Policy" or "Domain Group Policy".
2)Registry settings block to use "Task Manager".
3)The virus attack.

Solution of The Problem:
------------------------------------------------------
Method 1: With Console Registry Tool
--------------------------------------------------
Go to "Start" -> "Run" -> Write the following command,
REG add HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\System /v DisableTaskMgr /t REG_DWORD /d 0 /f
and press enter. Better you can copy the command and paste on to your run window.

Method 2: Edit Registry Settings
----------------------------------------------------------
1.Open notepad as an administrator.

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\System]
"DisableTaskMgr"=dword:00000000

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Group Policy Objects\LocalUser\Software\Microsoft\Windows\CurrentVersion\Policies\System]
"DisableTaskMgr"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system\]
"DisableTaskMgr"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]
"DisableCAD"=dword:00000000

2. Copy the above contents and paste it into notepad.
3. Save the file as fix.reg
4.Double click on the file and reboot your computer.

Method 3:
----------------------
1. Go to "Start" -> "Run" -> Write "Gpedit.msc" and press enter.
2. Go to following branch
User Configuration / Administrative Templates / System / Ctrl+Alt+Delete Options / Remove Task Manager
3.Select Remove Task Manager and set the policy to Not Configured.
4.Close "Gpedit.msc" memory management console.

5.Go to "Start" -> "Run" -> Write "gpupdate /force" and press enter.

Method 4: With regedit.exe with explorer.
---------------------------------------------------------
1. Go to "Start" -> "Run" -> Write "regedit.exe" and press enter.
2. Go to following branch HKEY_CURRENT_USER \ Software \ Microsoft \ Windows \ CurrentVersion \ Policies\ System
3.In the right-pane, delete the value named DisableTaskMgr
4.Exit registry editor.

Difference or Advantage between AWR and STATSPACK report

1)The AWR is the next evolution of the STATSPACK utility.

2)The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not.

3)STATSPACK does not store the Active Session History (ASH) statistics which are available in the AWR dba_hist_active_sess_history view.

4)An important difference between STATSPACK and the AWR is that STATSPACK does not store history for new metric statistics introduced in Oracle10g. The key AWR views, dba_hist_sysmetric_history and dba_hist_sysmetric_summary.

5)The AWR also contains views such as dba_hist_service_stat , dba_hist_service_wait_class and dba_hist_service_name , which store history for performance cumulative statistics tracked for specific services.

6)The latest version of STATSPACK included with Oracle10g contains a set of specific tables, which track history of statistics that reflect the performance of the Oracle Streams feature. These tables are stats$streams_capture , stats$streams_apply_sum , stats$buffered_subscribers , stats$rule_set , stats$propagation_sender , stats$propagation_receiver and stats$buffered_queues . The AWR does not contain the specific tables that reflect Oracle Streams activity; therefore, if a DBA relies heavily on the Oracle Streams feature, it would be useful to monitor its performance using STATSPACK utility.

7)Statspack snapshots must be run by an external scheduler (dbms_jobs, CRON, etc.). AWR snapshots are scheduled every 60 minutes by default. Administrators can manually adjust the snapshot interval if so desired.

8)ADDM captures a much greater depth and breadth of statistics than Statspack does. During snapshot processing, MMON transfers an in-memory version of the statistics to the permanent statistics tables.

9)Statspack snapshot purges must be scheduled manually. When the Statspack tablespace runs out of space, Statspack quits working. AWR snapshots are purged automatically by MMON every night. MMON, by default, tries to keep one week's worth of AWR snapshots available. If AWR detects that the SYSAUX tablespace is in danger of running out of space, it will free space in SYSAUX by automatically deleting the oldest set of snapshots. If this occurs, AWR will initiate a server-generated alert to notify administrators of the out-of-space error condition. Administrators can manually adjust the amount of information retained by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the RETENTION parameter input variable.

10)AWR snapshots provide a persistent view of database statistics. They are stored in the system-defined schema, which resides in a new tablespace called SYSAUX. A snapshot is a collection of performance statistics that are captured at a specific point in time. The snapshot data points are used to compute the rate of change for the statistic being measured. A unique SNAP_ID snapshot identifier identifies each snapshot.

How to monitor alert log file in Oracle

How to monitor alert log file in Oracle
We know in a UNIX system with tail -f we can monitor alert log file to see how alert log file is populated. For example the latest happenings in alert log file can be seen by,
$tail -f background_dump_dest location/alert_$ORACLE_SID.ora

But in windows there is not tail command. We can monitor the alert log within oracle itself regardless of platform. This can be archived by following steps.

1)Create an External Table to read the alert log.

column a_log new_value ALOG noprint
column value new_value bkgd_dmp noprint

select 'alert_'||instance_name||'.log' a_log
from v$instance;

select value
from v$parameter
where name = 'background_dump_dest';

create or replace directory data_dir as '&&bkgd_dmp';

CREATE TABLE alert_t
(
text_line varchar2(255)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
records delimited by newline
fields
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'&&ALOG'
)
)
REJECT LIMIT unlimited;

2)Query from the External Table.

For example, you can then query the last 21 lines of the alert log in this manner:

select text_line
from (
select rownum rn, text_line
from alert_t
)
where rn between (select max(rn) - 20 from (select rownum rn from
alert_t))
and (select max(rn) from (select rownum rn from alert_t));

How to Download Patchset or Opatch from metalink

To download patchset as well as logon to metalink you must need a MetaLink account. To register for MetaLink, you will need a valid Support Identifier (CSI). To have an account go to https://metalink.oracle.com/ and select Register For MetaLink under First Time Users. Then give your CSI number Support Identifier Country and click proceed. After that follow instruction as indicated.

After you have your metalink account you can downlaod patchset or Opatch from metalink. Steps are stated below.
1)Login to metalink with username and password.

2)Beside quick find drop down menu select Patch Number.

3)Beside this box there is another box where you can give the patch number if you know the patch number previous. It is an integer number. For example,
-If you try to get patchset 10.2.0.3 then give number as 5337014 and click go. In order to know the patchset number just have a look at, List of Patchset number in metalink
-New pop up windows will appear. Enter again username and password.
-Select you platform from drop down menu and click download.
-You have finished download in this stage.

4)If you don't know patch number and you wish to find it out then simply click go.
5)Patches & Updates page will appear. From it you can proceed as you like.

List of Patchset number in metalink

You want to download patchset from metalink but you don't know the patchset number. In that case it may take some moments to find patchset number. For example you want to upgrade your database version from 10.2.0.1 to 10.2.0.2. Now which patchset number you want to download? In this post the patchset number along with oracle version is below.

A)For Oracle9iR2, Base version 9.2.0.1
i)To upgrade to 9.2.0.2 patchset number 2632931.
ii)To upgrade to 9.2.0.3 patchset number 2761332
iii)To upgrade to 9.2.0.4 patchset number 3095277
iv)To upgrade to 9.2.0.5 patchset number 3501955
v)To upgrade to 9.2.0.6 patchset number 3948480
vi)To upgrade to 9.2.0.7 patchset number 4163445
vii)To upgrade to 9.2.0.8 patchset number 4547809

B)For Oracle10g, Base version 10.1.0.2
i)To upgrade to 10.1.0.3 patchset number 3761843
ii)To upgrade to 10.1.0.4 patchset number 4163362
iii)To upgrade to 10.1.0.5 patchset number 4505133

C)For Oracle10gR2 Base version 10.2.0.1
i)To upgrade to 10.2.0.2 patchset number 4547817
ii)To upgrade to 10.2.0.3 patchset number 5337014
iii)To upgrade to 10.2.0.4 patchset number 6810189
iv)For Warehouse build of 10.2.0.4 patchset number 7005587

D)For Oracle11gR1 Base version 11.1.0.6
i)To upgrade to 11.1.0.7.0 Patchset number 6890831

Understanding Execution Plan Statistics

Below is the output of an execution plan statistics.
SQL> set autot trace statistics
SQL> select * from tab;

107 rows selected.


Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
3459 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed

You sometimes want to know what these fields indicates. Below is the details of these fields.

1)recursive calls: For processing of sql statements oracle database maintain internal tables. Sometimes for processing sql statements change is needed in the internal tables and sometimes not. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.


2)db block gets: Number of times a CURRENT block was requested.

3)consistent gets: Number of times a consistent read was requested for a block. This is called the logical reads indicates for processing of a query how many blocks needs to be accessed.

4)physical reads: Total number of data blocks read from disk. This number equals the value of "physical reads direct" + all reads into buffer cache.

5)redo size: For processing of a query total amount of redo generated in bytes.

6)bytes sent through SQL*Net to client: Total number of bytes sent to the client from the foreground processes.

7)bytes received through SQL*Net from client: Total number of bytes received from the client over Oracle Net.

8)SQL*Net round-trips to/from client: Total number of Oracle Net messages sent to and received from the client.
9)sorts (memory):Number of sort operations that were performed completely in memory and did not require any disk writes.

10)sorts (disk):Number of sort operations that required at least one disk write.

11)rows processed: Number of rows processed during the operation.