RMAN restoration status monitoring

In most of cases, its difficult to guess "RMAN completion time" ( Backup/ Restore), below query will give estimate idea for your RMAN job,

Please note, query output will be based on "allocated channels" in RMAN backup jobs.

In enterprise edition, all channels will be allocated parallel, so you can see session for each channel.

But in Standard edition, though you have allocated multiple channels channels will be allocated serially, i.e one after other, hence in this edition you will see only one session for channel.


RMAN restoration status


SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE",time_remaining/60 "time_remaining"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK
;

Please note: In restoration process, you will not get session through tns, you will have to take session from server itself.


 

setting password for listener in oracle

Most of the time in IT Audit, one issue always raise  "No password is set for listener" .

Today I will show how to set password for listener.

There are 3 types in lsnrctl status for Security

1) ON: Local OS Authentication
2) ON: Password or Local OS Authentication
3) ON: Password

Will see below steps to configure password on listener.


LSNRCTL>status

Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication

check Security

LSNRCTL>change_password
LSNRCTL>save_config

 in listener.ora you will get following things

 #----ADDED BY TNSLSNR 14-OCT-2014 18:33:11---
PASSWORDS_LISTENER = 18463FBDF70465CE
#--------------------------------------------


LSNRCTL>status

Uptime                    0 days 0 hr. 1 min. 31 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication

Now Security : password + OS authentication

Oracle 10g, operating system authentication for the listener has been implemented.
The listener checks to make sure you are part of the privileged dba group,
and if so it will grant you access to change the password, save configuration.

Remove OS authentication if required?

shutdown listener

put following in listener.ora
LOCAL_OS_AUTHENTICATION_LISTENER=OFF


LSNRCTL>status

Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Password

now listener will work only through passsword

LSNRCTL>status    (gives error)
TNS-01169: The listener has not recognized the password.

how to logging through listner

LSNRCTL>set password

LSNRCTL> status   ( it will execute )

Cold backup with RMAN

Cold backup with RMAN

Scenario 1:
Most of the time Interviewers ask candidates, I want to take cold backup with RMAN, can I?
And 90% candidates give answer as "No sir, we cant take cold backup with RMAN".

Scenario 2:
Though 10% of people give above answer correct, they fail by commenting "For RMAN backup DB always must be in archivelog mode"

In fact, we can take cold backup with RMAN no matter whether your DB is in archivelog mode or not.

Quick steps:
Backup

1) Shutdown DB  
2) Startup mount
3) RMAN>backup database   (make sure, controlfile autoback is on / or use COPY CURRENT CONTROLFILE TO 'location';)
4) Alter database open;
5) Create object 2305 in scott schema.
6)shut immediate;

Recovery steps
7) startup nomount;
8) restore controlfile from '';
9) alter database mount;
10) restore database;
11) sql> recover database using backup controlfile until cancel;
12) alter database open resetlogs;

transactions after step 3: wont be recover, if we dont have archivelog.



C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:52:29 2014

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


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Current log sequence           1
SQL>
SQL>
SQL>
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exiit
SP2-0042: unknown command "exiit" - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

C:\Users\oradb>rman target sys/orcl1@orcl1

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 12:53:28 2014

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     348127232 bytes

Fixed Size                     1248936 bytes
Variable Size                 83886424 bytes
Database Buffers             260046848 bytes
Redo Buffers                   2945024 bytes

RMAN> backup database;

Starting backup at 23-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 23-MAY-14
channel ORA_DISK_1: finished piece 1 at 23-MAY-14
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NNNDF_TAG20140523T125343_9QXX
W0CJ_.BKP tag=TAG20140523T125343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-MAY-14
channel ORA_DISK_1: finished piece 1 at 23-MAY-14
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NCSNF_TAG20140523T125343_9QXX
WV57_.BKP tag=TAG20140523T125343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAY-14

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.

C:\Users\oradb>
C:\Users\oradb>
C:\Users\oradb>
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:54:37 2014

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


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> create table scott.test2305 as select * from scott.emp;

Table created.

SQL> select count(*) from scott.test2305;

  COUNT(*)
----------
         0

SQL>
SQL>
SQL>
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
         0

SQL>
SQL>
SQL> select * from dba_objects where object_name='TEST2305';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
SCOTT
TEST2305
                                    56376          56376 TABLE
23-MAY-14 23-MAY-14 2014-05-23:12:55:03 VALID   N N N


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:56:41 2014

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  348127232 bytes
Fixed Size                  1248936 bytes
Variable Size              83886424 bytes
Database Buffers          260046848 bytes
Redo Buffers                2945024 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

C:\Users\oradb>rman target sys/orcl1@orcl1

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 12:56:56 2014

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

connected to target database: ORCL1 (DBID=1227596891, not open)

RMAN> restore database;

Starting restore at 23-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1
_MF_NNNDF_TAG20140523T125343_9QXXW0CJ_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NNNDF_TAG20140523T125343_9QXX
W0CJ_.BKP tag=TAG20140523T125343
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 23-MAY-14

RMAN> restore database using backup controlfile until cancel;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "using": expecting one of: "archivelog, channel, check, controlfile, clone, database, da
tafile, device, from, force, high, (, preview, ;, skip, spfile, standby, tablespace, until, validate"
RMAN-01007: at line 1 column 18 file: standard input

RMAN> exit


Recovery Manager complete.

C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:57:51 2014

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


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3042883 generated at 05/23/2014 12:52:58 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_05_23\O1_MF_1
_1_%U_.ARC
ORA-00280: change 3042883 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
         0

SQL> select count(*) from scott.test2305;
select count(*) from scott.test2305
                           *
ERROR at line 1:
ORA-00942: table or view does not exist

Oracle Universal Installer (OUI) Silent Installations

Oracle Universal Installer (OUI) Silent Installations


1) Create response file of Oracle installation

To create a response file, start the OUI with the following command and perform an installation as normal.

./runInstaller -record -destinationFile /tmp/10gR2.rsp

The "-record" parameter tells the installer to write to the response file and
the "-destinationFile" parameter defines the name and location of the response file.


2) Execute below command to invoke Oracle silent installation
A silent installation is initiated using the following command.

./runInstaller -silent -responseFile /tmp/10gR2.rsp


Soon updates will be uploaded for Silent Installations.. 

Password verify function in Oracle

Demonstration

setting Password_verify_function in Oracle


1) Create user:

SQL> create user shreyas identified by shreyas;

User created.


2) For password verify function execute below file from location ($ORACLE_HOME/rdbms/admin)

SQL> @/u01/oracle/product/10.2.0/db_1/rdbms/admin/utlpwdmg.sql;

Function created.


Profile altered.

3) Check whether function got created or not.

SQL> select object_name from dba_objects where object_type='FUNCTION' and object_name like 'VERIFY%';

OBJECT_NAME
--------------------------------------------------------------------------------
VERIFY_FUNCTION


4) check users profile

SQL> select username,profile from dba_users where username='shreyas';

USERNAME                       PROFILE
------------------------------ ------------------------------
shreyas                      DEFAULT


5) alter default profile and enabled passowrd verify function

SQL> alter profile default limit password_verify_function verify_function;  

Profile altered.


6) Now try to change password ( here I am passing password as "pass")
SQL> password shreyas
Changing password for shreyas
New password:                                                   (password=pass)
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one character and one
punctuation

Password unchanged



/////Disable Verify_function//////////

1) Make password_verify_function null in profile

SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.


2) Now try to change password ( here I am passing password as "pass")
SQL> password shreyas
Changing password for shreyas
New password:                                                                  (password=pass)
Retype new password:
Password changed

It allowed me to change password.

Note: Kindly test 1st in UAT.

Oracle 10g DBA workshop I workshop II

Use following link to download Oracle 10g DBA workshop I & Workshop II

https://drive.google.com/file/d/0B56wEvD7Qe6LRDVCS2cxaWtIWE0/edit?usp=sharing

SQL Fundamental II

https://drive.google.com/file/d/0B56wEvD7Qe6LWDMzYkw1dlVmd3M/edit?usp=sharing

Wha is Deadlock?

A deadlock occurs when two or more session are waiting for data locked by each other, resulting in all the sessions being blocked.
Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock.


Error in alert file: Ora 00060



In above case deadlock occurs.

because each one have lock on each other tables.
1) like when user 1 updates t1 it put lock on it, at same time user2 updates t2 it put lock on it.
2) then user 1 updates t2,at same time user 2 updates on t1.
3) When user 1 tries to commit on it doesn't allow to commit since t2 hv lock by user2.
4) when user 2 tries to commit on it doesn't allow to commit since t1 hv lock by user1.

RMAN Backup type (Differential & Cumulative)

RMAN backup types

RMAN incremental backup for an Oracle DB

Sometimes we need to backup the database changes only from the last backup (only the last changes are backed up). This is an incremental backup. There are 2 types of incremental backup: DIFFERENTIAL (by default) & CUMULATIVE.

NOTE: The incremental backups are only for the DATA files.




DIFFERENTIAL backup = which backs up all blocks changed after the most recent incremental backup at level 1 or 0. (See Picture 1). The following RMAN command is used to take a DIFFERENTIAL database backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;






CUMULATIVE backup =  which backs up all blocks changed after the most recent incremental backup at level 0. (See Picture 2)
The following RMAN command is used to take a CUMULATIVE database backup:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;


The advantages of each types of incremental backup



Here are the advantages of each types of incremental backup:


Advantages of the DIFFERENTIAL backup
 

• Less space disk used
• Less network traffic
• Less time to take a backup

Advantages of the CUMULATIVE backup

• Less time to recover

Download SQL fundamental PPT file

Download SQL fundamental PPT file


https://drive.google.com/file/d/0B56wEvD7Qe6LeEs2RGxGS0d6T2c/edit?usp=sharing

Playing with dbms_jobs

create job
From TOAD and PL/SQL developer you can generate create job statement.









In sql developer you can't generate create job statement.





On sqlplus level using get_ddl you can generate create job statement

variable job number;
begin
  sys.dbms_job.submit(job => :job,
                      what => 'Proc_Dashboard(1,next_date,job);',
                      next_date => to_date('04-01-2013 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'SYSDATE+3/1440');
  commit;
end;
/

select * from dba_jobs order by schema_user

DBA_JOBS_RUNNING lists all jobs that are currently running in the instance.

SID                Identifier of process that is executing the job. See "V$LOCK".
JOB                  Identifier of job. This job is currently executing.
FAILURES               Number of times this job started and failed since its last success.
LAST_DATE               Date that this job last successfully executed.
LAST_SEC                  Same as LAST_DATE. This is when the last successful execution started.
THIS_DATE              Date that this job started executing.
THIS_SEC              Same as THIS_DATE. This is when the last successful execution started.
INSTANCE NUMBER        Indicates which instance can execute or is executing the job; the default is 0.


Jobs that are currently running:

SELECT * FROM user_jobs_running;

Loging to perticular schmeas and remove the jobs
========================================
 NEXT_DATE
You can alter the next date that Oracle executes a job by calling the DBMS_JOB.NEXT_DATE procedure,
as shown in the following example:

BEGIN
DBMS_JOB.NEXT_DATE(2, SYSDATE + 3/1400);
commit;
END;
/

INTERVAL
The following example illustrates changing the execution interval for a job by calling the DBMS_JOB.INTERVAL procedure:

BEGIN
DBMS_JOB.INTERVAL(41, 'SYSDATE + 4/1400');
commit;
END;
/

BEGIN
DBMS_JOB.BROKEN(41,TRUE);
commit;
END;
/

BEGIN
DBMS_JOB.REMOVE( 41);
commit;
END;
/



To run the job manually
EXEC dbms_job.run(job_no);

SGA memory structure

show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
sga_max_size                         big integer 280M
sga_target                           big integer 280M



SQL> select name,bytes/1024/1024 "Mbs" from v$sgainfo;

NAME                                    Mbs
-------------------------------- ----------

Fixed SGA Size                   1.19078064
Redo Buffers                     2.80859375
Buffer Cache Size                       196
Shared Pool Size                         68
Large Pool Size                           4
Java Pool Size                            4
Streams Pool Size                         4
Granule Size                              4
Maximum SGA Size                        280
Startup overhead in Shared Pool          36
Free SGA Memory Available                 0


============================================================
============================================================
Buffer Cache Size                       196
Shared Pool Size                         68
Large Pool Size                           4
Java Pool Size                            4
Streams Pool Size                         4
Granule Size                              4
----------------------------------------------------
Maximum SGA Size                        280



SQL> show sga

Total System Global Area  293601280 bytes   280   Mbs
Fixed Size                  1248624 bytes   1.190 Mbs
Variable Size              83886736 bytes   80    Mbs
Database Buffers          205520896 bytes   196   Mbs       
Redo Buffers                2945024 bytes   2.808 Mbs


Database buffers(196)=Buffer Cache Size (196)

Variable Size(80)=Shared Pool Size(68) + Large Pool Size(4) + Java Pool Size(4) + Streams Pool Size(4)


select name,bytes/1024/1024 "mbs" from v$sgainfo where name like '%Pool%' or name like '%Buffer%';

How to set Environmental variables in linux & windows

How to set Environmental variables in linux & windows

In linux

export ORACLE_SID=name
echo $ORACLE_SID

In windows

set ORACLE_HOME=C:\ss
echo %ORACLE_HOME%

How To recover lost of all controlfile.

How To recover lost of controlfile,

As a DBA on proactive basis always include controlfile text based backup in every backups.


How to take controlfile text based backup

sql>alter database backup controlfile to trace as 'somepath/bkp_controlfile.txt';



Steps to recover controlfile
        
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  348127232 bytes
Fixed Size                  1248936 bytes
Variable Size             104857944 bytes
Database Buffers          239075328 bytes
Redo Buffers                2945024 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL1" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG'  SIZE 50M,
  9    GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG'  SIZE 50M,
 10    GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF',
 14    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF',
 15    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF',
 16    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF'
 17  CHARACTER SET WE8MSWIN1252
 18  ;

Control file created.

SQL> alter database open
  2  ;

Database altered.

SQL>

Recovery Steps for Lost of system datafile

Recovery Steps for Lost of system datafile

SQL>shut abort;

SQL> startup
ORACLE instance started.

Total System Global Area  348127232 bytes
Fixed Size                  1248936 bytes
Variable Size             104857944 bytes
Database Buffers          239075328 bytes
Redo Buffers                2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF'

While database open in read write mode, it thrown an error.

We have last Level 0 backup.

Note: DB is already in mount stage


Steps to Restoration

C:\Users\oradb>rman target sys/orcl1@orcl1

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 6 13:08:12 2014

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

connected to target database: ORCL1 (DBID=1225511391, not open)

RMAN> restore datafile 3;

Starting restore at 06-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
channel ORA_DISK_1: reading from backup piece D:\LOCAL\HOTLEVEL0_ORCL1_DB_02P2GDIV_2_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\LOCAL\HOTLEVEL0_ORCL1_DB_02P2GDIV_2_1 tag=LVL0_ORCL
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 06-MAR-14


RMAN> recover datafile 3;

Starting recover at 06-MAR-14
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 5 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_5_9KJ8S929
_.ARC
archive log thread 1 sequence 6 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_6_9KJ8SB18
_.ARC
archive log thread 1 sequence 7 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_7_9KJ8SGP1
_.ARC
archive log thread 1 sequence 8 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_8_9KJ8SLJY
_.ARC
archive log thread 1 sequence 9 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_9_9KJ8SM47
_.ARC
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_5_9KJ8S929_.ARC thread=1 sequence=5
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_6_9KJ8SB18_.ARC thread=1 sequence=6
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_7_9KJ8SGP1_.ARC thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-MAR-14

RMAN> alter database open;

database opened

Same can be used for system datafile as well.