Renaming A Datafile In A Physical Standby Environment 12c/10g

physical standby database.

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

1. Verify primary and standby databases and also change the initialization parameter STANDBY_FILE_MANAGEMENT from AUTO to MANUAL.

On Primary :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
——— —————————— —————- ——————–
ORCL      orcl                           PRIMARY          TO STANDBY

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence     524
Next log sequence to archive   526
Current log sequence           526

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
—————- ——————–
PRIMARY          TO STANDBY

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
———————————— ———– ——————————
standby_file_management              string      AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
———————————— ———– ——————————
standby_file_management              string      MANUAL

On Standby :

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
——— —————————— —————- ——————–
ORCL      sbyorcl                        PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     524
Next log sequence to archive   0
Current log sequence           526

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
—————- ——————–
PHYSICAL STANDBY NOT ALLOWED

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
———- ———
517 YES
518 YES
519 YES
520 YES
521 YES
522 YES
523 YES
524 YES
525 IN-MEMORY 

9 rows selected.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
———————————— ———– ——————————
standby_file_management              string      AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE
———————————— ———– ——————————
standby_file_management              string      MANUAL
SQL>

2. Verify the tablespace to which the datafile belongs and make it offline. This step is performed on primary database.

SQL> select file_name from dba_data_files where tablespace_name=’EXAMPLE';

FILE_NAME
——————————————————————————–
/home/oracle/app/oracle/oradata/orcl/example01.dbf

SQL> alter tablespace example offline;

Tablespace altered.

3. Rename the datafile to new location and bring tablespace back online. This step is performed on primary database.

[oracle@dgaskmpri01 OPatch]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf  /tmp/askm/example01_temp.dbf
[oracle@dgaskmpri01 OPatch]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:47:17 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace example rename datafile ‘/home/oracle/app/oracle/oradata/orcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf';

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name=’EXAMPLE';

FILE_NAME
——————————————————————————–
/tmp/askm/example01_temp.dbf

SQL>

4. Verify the same  tablespace on standby database.

SQL> select ts#,name from v$tablespace where name=’EXAMPLE';

TS# NAME
———- ——————————
6 EXAMPLE

SQL> select name from v$datafile where ts#=6;

NAME
——————————————————————————–
/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf

SQL>

5. Stop recovery on standby database and shut it down.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>

6. Rename the datafile on standby database.

[oracle@dgaskmsby01 askm]$ mv /home/oracle/app/oracle/oradata/sbyorcl/example01.dbf /tmp/askm/example01_temp.dbf
[oracle@dgaskmsby01 askm]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 22 05:53:49 2011

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             343935672 bytes
Database Buffers          104857600 bytes
Redo Buffers                6008832 bytes
Database mounted.

SQL> alter database rename file ‘/home/oracle/app/oracle/oradata/sbyorcl/example01.dbf’ to ‘/tmp/askm/example01_temp.dbf';

Database altered.

SQL> select name from v$datafile where ts#=6;

NAME
——————————————————————————–
/tmp/askm/example01_temp.dbf

7. Keep standby database in recovery mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

8. Set initialization parameter STANDBY_FILE_MANAGEMENT value back to AUTO.

On Primary :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

On Standby :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Reference :

MOS ID : 733796.1

ORACLE 12c NEW FEATURE AVAILABLE

Online Move (12c)

Oracle 12c includes the command, which performs an online move of a datafile.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf';

Database altered.

RMAN

RMAN can be used to move files with less downtime by copying them in advance of the move, then recovering them as part of the move itself. First, log in to RMAN and list the current files.

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    750      SYSTEM               ***     /u01/app/oracle/oradata/DB11G/system01.dbf

2    1150     SYSAUX               ***     /u01/app/oracle/oradata/DB11G/sysaux01.dbf

3    444      UNDOTBS1             ***     /u01/app/oracle/oradata/DB11G/undotbs01.dbf

4    120      USERS                ***     /u01/app/oracle/oradata/DB11G/users01.dbf

5    345      EXAMPLE              ***     /u01/app/oracle/oradata/DB11G/example01.dbf

8    3277     SOE                  ***     /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    370      TEMP                 32767       /u01/app/oracle/oradata/DB11G/temp01.dbf

RMAN>

Copy the file(s) to the new location.

RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/DB11G/soe.dbf';

Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method.

RMAN> SQL 'ALTER TABLESPACE soe OFFLINE';

Switch to the new datafile copy(s) and recover the tablespace.

RMAN> SWITCH DATAFILE 8 TO COPY;

RMAN> RECOVER TABLESPACE soe;

Turn the tablespace online again.

RMAN> SQL 'ALTER TABLESPACE soe ONLINE';

Remove the old datafile(s).

RMAN> HOST 'rm /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf';

Listing the current files shows the move is complete.

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/DB11G/system01.dbf
2    1150     SYSAUX               ***     /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3    444      UNDOTBS1             ***     /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4    120      USERS                ***     /u01/app/oracle/oradata/DB11G/users01.dbf
5    345      EXAMPLE              ***     /u01/app/oracle/oradata/DB11G/example01.dbf
8    3277     SOE                  ***     /u01/app/oracle/oradata/DB11G/soe.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    370      TEMP                 32767       /u01/app/oracle/oradata/DB11G/temp01.dbf

RMAN>

Moving the SYSTEM tablespace is possible using a similar method, but the database must be shutdown and mounted before the switch and recover can be done.

Recreating the Controlfile

For largescale rearrangements it may be easier to manipulate the controlfile contents manually by backing up the controlfile to trace.

SQL> CONN sys/password AS SYSDBA

Connected.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

SQL>

The resulting trace file in the user_dump_dest directory contains commands and instructions for recreating the controlfile. The paths in the CREATE CONTROLFILE command can be manipulated to rename all datafiles and logfiles on one step.
This is quite a drastic step and it may affect the usefulness of existing backups, especially if the controlfile is being used as the recovery catlog.

Delete or Cleanup Orphaned Data Pump Jobs

Sometimes we need stop the datapump operation due to some reason .  To cleanup orphaned datapump jobs we perform the following steps.

1.)  Check the orphaned datapump jobs.
SQL>select owner_name,job_name,operation,job_mode,state,attached_sessions from dba_datapump_jobs; 

OWNER_NAME                     JOB_NAME                       OPERATION                      JOB_MODE                       STATE                          ATTACHED_SESSIONS

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

SYSTEM                         SYS_EXPORT_SCHEMA_01           EXPORT                         SCHEMA                NOT RUNNING                                    0

SYSTEM                         SYS_EXPORT_SCHEMA_03           EXPORT                         SCHEMA                NOT RUNNING                                    0

SYSTEM                         SYS_EXPORT_SCHEMA_02           EXPORT                         SCHEMA                NOT RUNNING                                    0

2.)  Check the status of  "state"  field

For orphaned jobs the state will be NOT RUNNING. So from the output we can say all the three are orphaned jobs. Orphaned Data Pump jobs do not have an impact on new Data Pump jobs. If a new Data Pump job is started, a new entry will be created, which has no relation to the old Data Pump jobs.

3.)  Drop the master table  

Since  the  above  jobs  are  stopped  or  not running  won't  be  restarted  anymore,  so  drop  the master table. The master  tables  above  are  SYS_EXPORT_SCHEMA_01,   SYS_EXPORT_SCHEMA_03,SYS_EXPORT_SCHEMA_02) .

SQL> drop table  system.SYS_EXPORT_SCHEMA_03 ; 

Table dropped.

SQL> drop table  system.SYS_EXPORT_SCHEMA_01 ; 

Table dropped.

SQL> drop table  system.SYS_EXPORT_SCHEMA_02 ; 

Table dropped.


4.) Check  for  existing  data  pump  jobs 
Now check the existing datapump job by  query  issued  in  step 1.  If  objects  are  in  recyclebin  then purge the objects from the recyclebin. 

SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions from dba_datapump_jobs;

No row selected

SQL> purge table system.SYS_EXPORT_SCHEMA_01;
Table purged.
SQL> purge table system.SYS_EXPORT_SCHEMA_02;
Table purged

SQL> purge table system.SYS_EXPORT_SCHEMA_03;

Table purged

SQL>select owner_name, job_name, operation, job_mode, state, attached_sessions from dba_datapump_jobs; 

 no rows selected

Data Pump 10g / 11g Flashback_Scn and flashback_Time parameter

Flashback Exports 10g

The exp utility used the CONSISTENT=Y parameter to indicate the export should be consistent to a point in time. By default the expdp utility exports are only consistent on a per table basis. If you want all tables in the export to be consistent to the same point in time, you need to use the FLASHBACK_SCN or FLASHBACK_TIME parameter.
The FLASHBACK_TIME parameter value is converted to the approximate SCN for the specified time.

expdp ..... flashback_time=systimestamp

# In parameter file.

flashback_time="to_timestamp('09-05-2011 09:00:00', 'DD-MM-YYYY HH24:MI:SS')"

# Escaped on command line.

expdp ..... flashback_time=\"to_timestamp\(\'09-05-2011 09:00:00\', \'DD-MM-YYYY HH24:MI:SS\'\)\"

Not surprisingly, you can make exports consistent to an earlier point in time by specifying an earlier time or SCN, provided you have enough UNDO space to keep a read consistent view of the data during the export operation.
If you prefer to use the SCN, you can retrieve the current SCN using one of the following queries.

SELECT current_scn FROM v$database;

SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;

SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;

That SCN is then used with the FLASHBACK_SCN parameter.

expdp ..... flashback_scn=5474280

The following queries may prove useful for converting between timestamps and SCNs.

SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;

SELECT SCN_TO_TIMESTAMP(5474751) FROM dual;

In 11.2, the introduction of legacy mode means that you can use the CONSISTENT=Y parameter with the expdp utility if you wish.

If you want to use a parameter file, you should make a file with for example this content and give it for example the name scott.par:

schemas=scott
dumpfile=exp_scott.dmp
logfile=exp_scott.log
directory=DATA_PUMP_DIR
flashback_time=systimestamp
..

You then can execute the export using:


expdp system/password parfile=scott.par

If you want a time consistent export on another timestamp, let say september 3rd 2014 on 14:41:00 then you should set the flashback_time parameter as follows:


flashback_time=”to_timestamp(’03-09-2014 14:41:00′, ‘DD-MM-YYYY HH24:MI:SS’)”


11g  ver: - 11.1

Flashback_Scn  and  flashback_Time  are  two  important  feature  of  the  datapump 11g . If  we  want  to  run  a  large  export  whilst  the  database  is  in  use  then  ideally  we  should  always use  one  of  the  two  flashback  parameters. The export  operation  is  performed  with  data  that is  consistent  as  of  the  specified  SCN .  FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive .

FLASHBACK_TIME : The SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. The export operation is performed with data that is consistent as of this SCN. TheFLASHBACK_SCN parameter pertains only to the Flashback Query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data Archive. We can get the scn number from the following query :

SQL> select current_scn from v$database ;       or

SQL>select dbms_flashback.get_system_change_number from dual ; 

Let's have a Demo of the flashback_scn

SQL> select current_scn from v$database;

CURRENT_SCN
------------------------
    1140271

SQL> create table hr.test as select * from test;
Table created.

SQL> select current_scn from v$database;

CURRENT_SCN
-------------------------
    1140487

Let's take a export using flashback_scn  parameter

oracle$ expdp system/ramtech@terminal directory=dpump schemas=hr dumpfile=flashback_hr.dmp logfile=flashlog.log       flashback_scn=1140271

Export: Release 11.1.0.6.0 - Production on Saturday, 16 MAY, 2014 11:35:45
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@terminal directory=dpump schemas=hr dumpfile=flashback_hr.dmp logfile=flashlog.log    flashback_scn=1140271
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."COUNTRIES"                            6.375 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.015 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . exported "HR"."JOBS"                                 6.984 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.484 KB       4 rows
ORA-31693: Table data object "HR"."TEST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  D:\DPUMP\FLASHBACK_HR.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 11:37:50

The above error show that the table "test"  is not include in the  export operation because the SCN mention  is of before the table "test" creation. The below export will show the export upto current SCN when database is in use.

Oracle$ expdp system/ramtech@terminal directory=dpump schemas=hr dumpfile=flashback_hr1.dmp  logfile=flashback_log.log  flashback_scn=1140487

Export: Release 11.1.0.6.0 - Production on Saturday, 16 MAY, 2014 11:44:50
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@terminal directory=dpump schemas=hr dumpfile=flashback_hr1.dmp logfile=flashback_log.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "HR"."COUNTRIES"                                6.375 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.015 KB      27 rows
. . exported "HR"."EMPLOYEES"                             16.80 KB     107 rows
. . exported "HR"."JOBS"                                             6.984 KB      19 rows
. . exported "HR"."JOB_HISTORY"                             7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                                8.273 KB      23 rows
. . exported "HR"."REGIONS"                                      5.484 KB       4 rows
. . exported "HR"."TEST"                                              5.054 KB       8 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  D:\DPUMP\FLASHBACK_HR1.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:46:41

Oracle$ impdp system/ramtech@terminal directory=dpump schemas=hr dumpfile=flashback_hr1.dmp  logfile=impflashback_log.log  flashback_scn=1140487

From version 11.2 and higher it is also possible to use the so called legacy mode: you can use the parameters from the old exp utilities! You can use the consistent=y parameter again to make a time consistent export:


$ expdp schemas=scott consistent=y dumpfile=exp_scott.dmp logfile=exp_scott.log directory=DATA_PUMP_DIR

This is the output you will get:
Export: Release 11.2.0.4.0 – Production on Wed Sep 3 15:32:03 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Release 11.2.0.4.0 – 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: “consistent=TRUE” Location: Command Line, Replaced with: “flashback_time=TO_TIMESTAMP(‘2014-09-03 15:32:03′, ‘YYYY-MM-DD HH24:MI:SS’)”
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** schemas=RPCR_DEV flashback_time=TO_TIMESTAMP(‘2014-09-03 15:32:03′, ‘YYYY-MM-DD HH24:MI:SS’) dumpfile=rpcr_dev2.dmp logfile=rpcr_dev.log directory=EXP_DIR reuse_dumpfiles=true
Estimate in progress using BLOCKS method…
….

So you see that expdp is translating consistent=y to a flashback_time parameter.

As I said this works only in version 11.2 and higher.

Oracle Installation On RHEL 5.x / 6.x

====================
Oracle 10g On RHEL 5.x
====================

#Unpack Files
Unzip cmd
Unzip the files: unzip 10201_database_linux32.zip

#Hosts File
# hostname
# hostname linux

 The /etc/hosts file must contain a fully qualified name for the server:
<IP-address>  <fully-qualified-machine-name>  <machine-name>
linux.com linux

#Set Oralce Kernel Parameters
 Add the following lines to the /etc/sysctl.conf file:

#shmax set of 50% of ram in byte when 1 GB of ram then 512*1024*1024
kernel.shmmax = 2147483648

#shmall is shmmax/PAGE_SIZE default pagesize is 4096 (4 KB)--- # getconf PAGE_SIZE
kernel.shmall = 2097152

#shmmni minimum size of shared segment is 4 KB (4096 in bytes)
kernel.shmmni = 4096

# Increase the size of shmall without reboot linux reboot system
# cat /etc/proc/sys/kernel/shmall
2097152

# echo 2097152> /etc/proc/sys/kernel/shmall

 Alternative command 
# sysctl -w kernel.shmall=2097152
# echo "kernel.shmall=2097152">> /etc/sysctl.conf

 Removing shared memory when show status 'dest' destroy then crash the shared memory segments.
# $ ipcs -m  //see the shmid column where status is dest then following next command
# $ ipcs -m -i 32768
# $ ipcrm shm 32768  //remove shmid


# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

# Run the following command to change the current kernel parameters:
/sbin/sysctl -p   (# sysctl -p)

# Add the following lines to the /etc/security/limits.conf file:
oracle           soft     nproc            2047
oracle           hard    nproc           16384
oracle           soft     nofile            1024
oracle           hard    nofile           65536

# Add the following line to the /etc/pam.d/login file, if it does not already exist:
session    required     pam_limits.so


# Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows:

SELINUX=disabled
Alternatively, this alteration can be done using the GUI tool (System > Administration > Security Level and Firewall). Click on the SELinux tab and disable the feature.




Setup
 Install the following packages:
 rpm -ivh binutils*
 rpm -ivh elfutils*
 rpm -ivh setarch-2*
 rpm -ivh make-3*
 rpm -ivh glibc-2.5-12.i386.rpm
 rpm -ivh glibc-2.5-12.i686.rpm
 rpm -ivh libaio-0*
 rpm -ivh compat-libstdc++-*
 rpm -ivh compat-libstdc++-33-3.2.3-61.i386.rpm
 rpm -ivh compat-libf2c*
 rpm -ivh glibc-headers-2.5-12.i386.rpm
 rpm -ivh glibc-devel-2.5-12.i386.rpm
 rpm -ivh compat-gcc-34-3.4.6-4.i386.rpm
 rpm -ivh compat-gcc-34-3*
 rpm -ivh compat-gcc-34-c++-3*
 rpm -ivh libgomp-4.1.1-52.el5.i386.rpm
 rpm -ivh gcc-4*
 rpm -ivh libXp-1*
 rpm -ivh openmotif-2*
 rpm -ivh compat-db-4*
 rpm -ivh glibc-common*
 rpm -ivh libstdc*
 rpm -ivh libgcc*
 rpm -ivh unixODBC*
 rpm -ivh sysstat*



# COMMAND
# cat /etc/passwd
# cat /etc/group
# userdel oracle
# groupdel oracle

# Create the new groups and users:
groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba oracle
passwd oracle

# Create the directories in which the Oracle software will be installed:
mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R oracle.oinstall /u01
chown -R oracle.oinstall /u02
chmod -R 775 /u01
chmod -R 775 /u02

# Login as root and issue the following command:
xhost +<machine-name>

# Edit the /etc/redhat-release file replacing the current release information
(Red Hat Enterprise Linux Server release 5 (Tikanga)) with the following:
vim /etc/redhat-release

redhat-4



# Login as the ORACLE user and ADD the following lines at the end of the .bash_profile file:

# su - oracle
$ vi .bash_profile


# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi
:wq!

$. .bash_profile  //ROOT COMMANDS:

[root@beam oracle]# chown oracle:oinstall database/
# ls -lrt
# ls -li
# cd database
[root@beam database]# ls -lrt
[root@beam database]# chown -R oracle:oinstall *
[root@beam database]# ls -lrt
[root@beam database]# cd response
[root@beam response]# ls -lrt


# Installation
LOGIN oracle user LOGOUT root user



Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=<machine-name>:0.0; export DISPLAY

 Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory:
./runInstaller

 During the installation enter the appropriate ORACLE_HOME and name then continue installation. For a more detailed look at the installation process, click on the links below to see screen shots of each stage.

[root@beam oraInventory]# ./orainstRoot.sh

Run the following scripts as root:
   /u01/app/oracle/oraInventory/orainstRoot.sh
   /u01/app/oracle/product/10.2.0/db_1/root.sh




================
11g On RHEL 5.x
================
umount tmpfs
mount -t tmpfs shmfs -o size=2000m /dev/shm

/etc/fstab
shmfs /dev/shm tmpfs size=2000m 0 0

physical recomment 1024Mb > 1100
swap double


 The /etc/hosts file must contain a fully qualified name for the server:
<IP-address>  <fully-qualified-machine-name>  <machine-name>
linux.com linux

#Set Oralce Kernel Parameters
 Add the following lines to the /etc/sysctl.conf file:
fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048576

# Run the following command to change the current kernel parameters:
/sbin/sysctl -p   (# sysctl -p)

# Add the following lines to the /etc/security/limits.conf file:
oracle           soft     nproc         2047
oracle           hard    nproc         16384
oracle           soft     nofile          1024
oracle           hard    nofile          65536
oracle           soft     stack          10240

# Add the following line to the /etc/pam.d/login file, if it does not already exist:
session    required     pam_limits.so


# Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows:

SELINUX=disabled
Alternatively, this alteration can be done using the GUI tool (System > Administration > Security Level and Firewall). Click on the SELinux tab and disable the feature.




Setup
 Install the following packages:
 rpm -ivh binutils*
 rpm -ivh elfutils*
 rpm -ivh setarch-2*
 rpm -ivh make-3*
 rpm -ivh glibc-*
 rpm -ivh libaio-0*
 rpm -ivh compat-libstdc++-*
 rpm -ivh compat-libstdc++-33*
 rpm -ivh compat-libf2c*
 rpm -ivh glibc-headers-*
 rpm -ivh glibc-devel-*
 rpm -ivh compat-gcc-*
 rpm -ivh compat-gcc-34-3*
 rpm -ivh compat-gcc-34-c++-3*
 rpm -ivh libgomp-*
 rpm -ivh gcc-4*
 rpm -ivh libXp-1*
 rpm -ivh openmotif-2*
 rpm -ivh compat-db-4*
 rpm -ivh glibc-common*
 rpm -ivh libstdc*
 rpm -ivh libgcc*
 rpm -ivh unixODBC*
 rpm -ivh sysstat*



# COMMAND
# cat /etc/passwd
# cat /etc/group
# userdel oracle
# groupdel oracle

# Create the new groups and users:
groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba oracle
passwd oracle

# Create the directories in which the Oracle software will be installed:
mkdir -p /u01/oracle/product/10.2.0/db_1
chown -R oracle.oinstall /u01
chown -R oracle.oinstall /u02
chmod -R 775 /u01
chmod -R 775 /u02

# Login as root and issue the following command:
xhost +<machine-name>

# Edit the /etc/redhat-release file replacing the current release information
(Red Hat Enterprise Linux Server release 5 (Tikanga)) with the following:
vim /etc/redhat-release

redhat-4



# Login as the ORACLE user and ADD the following lines at the end of the .bash_profile file:

# su - oracle
$ vi .bash_profile


# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi
:wq!

$. .bash_profile  //ROOT COMMANDS:

[root@beam oracle]# chown oracle:oinstall database/
# ls -lrt
# ls -li
# cd database
[root@beam database]# ls -lrt
[root@beam database]# chown -R oracle:oinstall *
[root@beam database]# ls -lrt
[root@beam database]# cd response
[root@beam response]# ls -lrt


# Installation
LOGIN oracle user LOGOUT root user


Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=<machine-name>:0.0; export DISPLAY

 Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory:
./runInstaller




===================
Oracle  11g On RHEL 6
===================

linux 6.1 on Oracle

---tmp approx 5 gb

#Unpack Files
Unzip cmd
Unzip the files: unzip 10201_database_linux32.zip

#Hosts File
# hostname
# hostname linux

 The /etc/hosts file must contain a fully qualified name for the server:
<IP-address>  <fully-qualified-machine-name>  <machine-name>
linux.com linux

#Set Oralce Kernel Parameters
 Add the following lines to the /etc/sysctl.conf file:

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
fs.aio-max-nr = 1048576
fs.suid_dumpable= 1
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

# Run the following command to change the current kernel parameters:
/sbin/sysctl -p   (# sysctl -p)

# Add the following lines to the /etc/security/limits.conf file:
oracle           soft     nproc            2047
oracle           hard    nproc           16384
oracle           soft     nofile          4096
oracle           hard    nofile           65536
oracle soft stack 10240


# Add the following lines to the /etc/security/limits.d/90- file:



# Add the following line to the /etc/pam.d/login file, if it does not already exist:
session    required     pam_limits.so


# Disable secure linux by editing the /etc/selinux/config file, making sure the SELINUX flag is set as follows:

SELINUX=permissive
Alternatively, this alteration can be done using the GUI tool (System > Administration > Security Level and Firewall). Click on the SELinux tab and disable the feature.






-----Setup
 Install the following packages:



rpm -ivh libstdc++-devel-4.4.5*
rpm -ivh kernel-header-2.6.32-131*
rpm -ivh glibc-headers-2.12*
rpm -ivh glibc-devel-2.12*

rpm -ivh gnome-icon-theme-2*
rpm -ivh dmz-cursor-themes-0.4.4*
rpm -ivh sgml-common-0.6*
rpm -ivh libaio-devel-0.3.107*
rpm -ivh ncurses-devel-5.7-3*
rpm -ivh elfutils-libelf-devel-0.152*

rpm -ivh compat-gcc-34-3*
rpm -ivh libXxf86misc-1.0.2*
rpm -ivh libXmu-1.0.5*
rpm -ivh mpfr-2.4*
rpm -ivh cpp-4.4.5*

rpm -ivh xorg-x11-xauth-1.0*
rpm -ivh compat-gcc-34*
rpm -ivh libdaemon-0.14-1*
rpm -ivh avahi-0.6.25*


rpm -ivh avahi-glib-0.6.25*
rpm -ivh shared-mime-info-0.70*
rpm -ivh liblDL-0.8*
rpm -ivh ORBit2-2.14*

rpm -ivh GConf2-2.28*
rpm -ivh gnome-vfs2-2*
rpm -ivh libbonobo-ltdl-2.24*
rpm -ivh libtool-ltddl-2.2*

rpm -ivh unixODBC-2.2.14*
rpm -ivh gtk2-engines-2.18.4*
rpm -ivh libmcpp-2.7*
rpm -ivh mcpp-2.7.2*
rpm -ivh xorg-x11-server-utils-7.4*
rpm -ivh ConsoleKit-x11-xinit-1.0*
rpm -ivh libXp-1.0.0*
rpm -ivh libXxf86dga-1.1.1-1*
rpm -ivh libdmx-1.1.0*
rpm -ivh xorg-x11-utils-7.4-8*
rpm -ivh compat-db43-4.3.29*
rpm -ivh compat-db42-4.2.52*
rpm -ivh ppl-0.10.2-11*
rpm -ivh cloog-ppl-0.15.7*
rpm -ivh gcc-4.4.5-6*
rpm -ivh gcc-c++-4.4.5-6*
rpm -ivh compat-libstdc++-33-3*
rpm -ivh compat-db-4.6.21*
rpm -ivh gnome-themes-2.28.1-6*
rpm -ivh system-icon-themes-6*
rpm -ivh system-gnome-themes-6*
rpm -ivh unixODBC-devel-2.2*
rpm -ivh readline-devel-6.0*
rpm -ivh libgnome-2.28.0*




# COMMAND
# cat /etc/passwd
# cat /etc/group
# userdel oracle
# groupdel oracle

# Create the new groups and users:
groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba oracle
passwd oracle

# Create the directories in which the Oracle software will be installed:
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle.oinstall /u01
chown -R oracle.oinstall /u02
chmod -R 775 /u01
chmod -R 775 /u02

# Login as root and issue the following command:
xhost +<machine-name>


# Edit the /etc/redhat-release file replacing the current release information
(Red Hat Enterprise Linux Server release 6 (Tikanga)) with the following:
vim /etc/redhat-release

redhat-4







# Login as the ORACLE user and ADD the following lines at the end of the .bash_profile file:
# su - oracle
$ vi .bash_profile

# Oracle Settings
#TMP=/tmp; export TMP
#TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi
:wq!

$. .bash_profile  //ROOT COMMANDS:



# Installation
LOGIN oracle user LOGOUT root user



Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=<machine-name>:0.0; export DISPLAY

 Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory:
./runInstaller

 During the installation enter the appropriate ORACLE_HOME and name then continue installation. For a more detailed look at the installation process, click on the links below to see screen shots of each stage.


[root@beam oraInventory]# ./orainstRoot.sh



Run the following scripts as root:
   /u01/app/oracle/oraInventory/orainstRoot.sh
   /u01/app/oracle/product/10.2.0/db_1/root.sh





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

What is oraInventory

The Orainventory is the location for the OUI's Book keeping
The inventory stores information about.

• All the Oracle software products installed on all ORACLE_HOMES on a machine
• Other non-oracle products such as Java Runtime env's (JRE)

1. Global Inventory

Global Inventory holds information about Oracle Products on a Machine, The inventory contains the high level list of all oracle products installed on a machine such as ORACLE_HOMES or JRE.
It doesn't have any information about the details of patches applied on each ORACLE_HOMES.
There should be only one per machine. Its locations is defined in the oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris).

1. Local Inventory

There is one Local inventory per ORACLE_HOME.
Inventory inside each Oracle Home is called as local Inventory or ORACLE_HOME Inventory. This Inventory holds information to that ORACLE_HOME only.

Can I have multiple Global Inventories on a machine?
Can you have multiple global Inventory and answer is YES you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location.
If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well.

What to do if my Global Inventory is corrupted?
Follow Link else below : RE-CREATE ORAINVENTORY
If your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option
-attachHome
./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc ORACLE_HOME=Oracle_Home_Location ORACLE_HOME_NAME=Oracle_Home_Name CLUSTER_NODES={}

Do I need to worry about oraInventory during oracle Apps 11i cloning ?
No, Rapid Clone will update both Global & Local Inventory with required information, you don't have to worry about Inventory during Oracle Apps 11i cloning.

How to Move oraInventory from one location to other?Find the current location of the central inventory (Normally $ORACLE_BASE/oraInventory):

Open the oraInst.loc file in /etc and check the value of inventory_loc
cat /etc/oraInst.loc
inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall 

Remark: The oraInst.loc file is simply a pointer to the location of the central inventory (oraInventory)
Copy the oraInventory directory to the destination directory
cp -Rp /u01/app/oracle/oraInventory /u02/app/oracle/oraInventory
Edit the oraInst.loc file to point to the new location
vi /etc/oraInst.loc
inventory_loc=/u02/app/oracle/oraInventory
inst_group=dba




How To Install oracle in silent mode

ORA-00354 ORA-00353 ORA-00312 Corrupt redo block header

Error Description:
----------------------------
I ran DML operation on database and it failed with exception. I looked for alert log and the entry is ,
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 11037 change 118820174 time 04/22/2008 11:15:12
ORA-00312: online log 2 thread 1: '/oradata1/system/ARJU/redo02.log'

Cause of The Problem:
----------------------------
The redo log file has been corrupted. It may be corrupted through various reasons. Suppose there is disk full or hardware failure.

Solution of The Problem:
------------------------------.
Solution 1 or 2 is the option while database is open and solution 3 is the option if database is closed.
Solution 1: Look at the disk space usage containing redo log. If disk is full then free it by removing unwanted files. If disk is ok then try to clear the log file. Here from error we see log 2 is corrupted. So , clear group 2 Like,

SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;

Clear logfile does the thing of dropping logfile and then create. The advantage of this procedure is it can be used if you have only two redo log group.

Solution 2: If redo log was not archived (query from V$LOG) then you must specify UNARCHIVED LOGFILE. Otherwise error will come.
Try, SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
After issuing this you have lost archived data, so take backup immediately.

In both 1 and 2 if it was the logfile member of online redo log group then you must issue,
SQL> alter system checkpoint;after clearing it.
Solution 3: If both does not work then do a point in time recovery.
SQL>SHUTDOWN ABORT;
SQL>STARTUP MOUNT;
RMAN>RESTORE DATABASE;
SQL>RECOVER DATABASE UNTIL CANCEL; CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS;

Point in time recovery is described in DBPITR
If database can't be opened that solution 3 is the only option

How to perform Database Point in time Recovery DBPITR

About Database Point in time recovery
----------------------------------------------
Database point-in-time recovery is helpful whenever we want to back the whole database to an earlier time. With RMAN you can give a specified target time and RMAN restores the database from backups prior to that time, and then applies archived redo log or incremental backups to perform media recovery to recreate all changes between the time of the datafile backups and the target time.

Disadvantages of Database Point in time Recovery
------------------------------------------------------
1)Unlike TSPITR you can't get back a set of objects to their past state instead you have to back to an earlier with of the entire database.

2)The entire database will be unavailable during the operation.

3)It is time-consuming, because all datafiles must be restored, and redo logs and incremental backups must be restored from backup and used to recover the datafiles.

Requirements of Database Point in time Recovery
--------------------------------------------------------------
1)Your database must be in archivelog mode.
2)You must have backups of all datafiles from before the target SCN for DBPITR and archived redo logs or incremental backups for the period between the SCN of the backups and the target SCN.

Database Point-in-Time Recovery Within the Current Incarnation
----------------------------------------------------------------------
If you want to perform database point in time recovery within current incarnation then you don't have to perform extra work as RMAN by default search for backups within current incarnation. Only you need SET UNTIL clause and then RESTORE and RECOVER. However you can get back your database to an ancestor incarnation .In that case before performing operation set incarnation. Like RESET DATABASE INCARNATION TO 1. In order to do so as well as to know about incarnation please have a look About Database Incarnations

In the following steps I demonstrate an example of how we can perform DBPITR.

1)Create a Table. Just an an extra work. I just created it and want to perform DBPITR before the time of table creation in order to show that this table would not found after DBPITR.

SQL> CONN A/A
Connected.

SQL> CREATE TABLE BEFORE_PITR TABLESPACE USERS AS SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <100;
Table created.

2)Shutdown the database.
SQL> CONN / AS SYSDBA
Connected.

SQL> SHUTDOWN ABORT
ORACLE instance shut down.

3)Connect to rman and Perform DBPITR. Here I wanted to get back of database to 30 minutes ago from current date. So I used SYSDATE-1/24/60*30.

SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 14 22:31:25 2008

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

connected to target database (not started)
RMAN> RUN{
2> RESTORE DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
3> RECOVER DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
4> } 
Starting restore at 14-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
.
.
media recovery complete, elapsed time: 00:00:27
Finished recover at 14-MAY-08

4)Open the database with RESETLOGS option.
RMAN> SQL'ALTER DATABASE OPEN RESETLOGS';

using target database control file instead of recovery catalog
sql statement: ALTER DATABASE OPEN RESETLOGS

5)Check the objects under Arju Schema.

SQL> conn a/a
Connected.
SQL> select table_name from tabs;

TABLE_NAME
------------------------------
TesT
MY_TABLE

And see that BEFORE_PITR is lost.

In stead of giving 'SYSDATE-1/24/60*30' you can also use time expressions,SCN restore points,SCN or log sequence numbers with SET UNTIL clause.

Like,
RMAN>RUN{
#SET UNTIL TIME 'Nov 12 2007 06:00:00'; --Set NLS_DATE_FORMAT setting.
#SET UNTIL SEQUENCE 9923;
#SET UNTIL RESTORE POINT before_update; --The restore point you created early.
SET UNTIL SCN 123456;
RESTORE DATABASE;
RECOVER DATABASE;

Delete Archivelog Using RMAN in Oracle

If your storage is about full, you must either delete old backup and archivelog or move to tape.
If you want to delete archivelog from FRA(ASM Storage-Flash Revovery Area) or filesystem for win space, you can use below commands. You can delete archivelog safely, because archivelog deleting does not harm to database.

Archivelog List Commands

RMAN>list archivelog all;
RMAN>list copy of archivelog until time 'SYSDATE-10';
RMAN>list copy of archivelog from time 'SYSDATE-10';
RMAN>list copy of archivelog from time 'SYSDATE-10' until time 'SYSDATE-2';
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;
RMAN> crosscheck archivelog until time 'SYSDATE-10';

Archivelog Delete Commands

RMAN>delete archivelog all;
RMAN>delete archivelog until time 'SYSDATE-10';
RMAN>delete archivelog from time 'SYSDATE-10'
RMAN>delete archivelog from time 'SYSDATE-10' until time 'SYSDATE-2';
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;
RMAN> delete expired archivelog until time 'SYSDATE-10';

Note : Also, you can use noprompt statement for do not yes-no question.
RMAN>delete noprompt archivelog until time 'SYSDATE-10';