Step by Step to Restore RMAN Incremental Backup in Oracle 11g Database

In this scenario we will restore incremental backup when all data files, control files including redo logs are lost

Step 1: take full RMAN backup (level 0)
Step 2: create two tables and insert data in these table
Step 3: take incremental level 1 backup 
Step 4: delete all data, redo and control files
Step 5: bring database in nomount mode
Step 6. restore control file
Step 7: mount database
Step 8: restore database from full backup
Step 9: restore database from incremental backup
Step 10: recover archive log
Step 11: open database

Thats it !!!!!!!!!!!!!!!!!

-------------Details------------

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> set time on
09:50:39 SQL> alter user scott identified by tiger account unlock;

User altered.

09:51:00 SQL> conn scott/tiger
Connected.
09:51:08 SQL> create table abc(
09:51:23   2  id number,
09:51:28   3  name varchar2(30));

Table created.

09:51:39 SQL> insert into abc values (10,'sebl');

1 row created.

09:51:57 SQL> insert into abc values (20,'BB');

1 row created.

09:52:09 SQL> commit;

Commit complete.

09:52:12 SQL> select * from abc;

ID NAME
---------- ------------------------------
10 sebl
20 BB

09:52:22 SQL>

Step 1: take full RMAN backup

[oracle@said rman]$ ./full.sh
RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> [oracle@said rman]$

Step 2: create table and insert data

[oracle@said backup_log]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 09:55:03 2017

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

Enter user-name: scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> set time on 
09:55:26 SQL> create table bcd(
09:55:32   2  id number,
09:55:41   3  salary number);

Table created.

09:55:49 SQL> insert into bcd values(10,1000);

1 row created.

09:56:10 SQL> insert into bcd values(20,20000);

1 row created.

09:56:21 SQL> create table cde(
09:56:28   2  id number,
09:56:34   3  dept varchar2(30));

Table created.

09:56:47 SQL> insert into cde values(10,'IT');

1 row created.

09:57:07 SQL> insert into cde values(20,'HR');

1 row created.

09:57:19 SQL> commit;

Commit complete.

09:57:22 SQL> select * from bcd;

ID     SALARY
---------- ----------
10 1000
20 20000

09:57:29 SQL> select * from cde;

ID DEPT
---------- ------------------------------
10 IT
20 HR

09:57:35 SQL>

Step 3: take incremental backup

[oracle@said rman]$ ./incre.sh
RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> [oracle@said rman]$

----Delete all Datafile /Redo Log/Control File--------------

Step 4: delete all data, redo and control files

[oracle@said oradata]$ cd orcl/
[oracle@said orcl]$ ls -lr
ttotal 1942628
-rw-r----- 1 oracle oinstall   5251072 Nov 14 10:08 users01.dbf
-rw-r----- 1 oracle oinstall 125837312 Nov 14 10:08 undotbs01.dbf
-rw-r----- 1 oracle oinstall  22028288 Nov 14 07:02 temp01.dbf
-rw-r----- 1 oracle oinstall 817897472 Nov 14 10:08 system01.dbf
-rw-r----- 1 oracle oinstall 744497152 Nov 14 10:08 sysaux01.dbf
-rw-r----- 1 oracle oinstall  52429312 Nov 14 10:08 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Nov 14 10:11 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Nov 14 10:09 redo01.log
-rw-r----- 1 oracle oinstall 104865792 Nov 14 10:09 encryptedtbs01.dbf
-rw-r----- 1 oracle oinstall   9748480 Nov 14 10:12 control02.ctl
-rw-r----- 1 oracle oinstall   9748480 Nov 14 10:12 control01.ctl
[oracle@said orcl]$ rm -rf *
[oracle@said orcl]$ ls -lrrt
total 0
[oracle@said orcl]$

-----Now Restore Full and Incremental Backup-----------
startup nomount
restore control file
mount database
restore full backup

Step 5: bring database in nomount mode.

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 10:21:19 2017

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  764121088 bytes
Fixed Size     2257152 bytes
Variable Size   469765888 bytes
Database Buffers   285212672 bytes
Redo Buffers     6885376 bytes
SQL>

[oracle@said ~]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 14 10:21:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> list backup summary;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 11/14/2017 10:22:04
ORA-01507: database not mounted

RMAN>

Step 6. restore control file

-----Restore Control File from full backup and mount the database---

RMAN> restore controlfile from '/fra/backup/Tuesday2017-11-1410:05:08_full/orcl_37_960026761_20171114_1.ctl';

Starting restore at 14-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/oradata/orcl/control02.ctl
Finished restore at 14-NOV-17

Step 7: mount database

RMAN> alter database mount
2> ;

database mounted
released channel: ORA_DISK_1

RMAN>
MAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
20      B  0  X DISK        14-NOV-17       1       1       YES        DATAFILE_BACKUP
21      B  1  X DISK        14-NOV-17       1       1       YES        DATAFILE_BACKUP
22      B  1  X DISK        14-NOV-17       1       1       YES        DATAFILE_BACKUP
23      B  A  X DISK        14-NOV-17       1       1       NO         TAG20171114T100035
25      B  F  X DISK        14-NOV-17       1       1       NO         TAG20171114T100038
26      B  F  A DISK        14-NOV-17       1       1       NO         TAG20171114T100508
27      B  F  A DISK        14-NOV-17       1       1       YES        DATAFILE_BACKUP
28      B  F  A DISK        14-NOV-17       1       1       YES        DATAFILE_BACKUP
29      B  A  A DISK        14-NOV-17       1       1       NO         TAG20171114T100558
30      B  F  A DISK        14-NOV-17       1       1       NO         SPFILE_BACKUP

RMAN>

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20      Incr 0  299.55M    DISK        00:00:38     14-NOV-17     
        BP Key: 20   Status: EXPIRED  Compressed: YES  Tag: DATAFILE_BACKUP
        Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/incre_orcl_0qsjhlog_26_1_20171114.DBF
  List of Datafiles in backup set 20
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 1972437    14-NOV-17 /u01/app/oracle/oradata/orcl/system01.dbf
  2    0  Incr 1972437    14-NOV-17 /u01/app/oracle/oradata/orcl/sysaux01.dbf
  3    0  Incr 1972437    14-NOV-17 /u01/app/oracle/oradata/orcl/undotbs01.dbf
  4    0  Incr 1972437    14-NOV-17 /u01/app/oracle/oradata/orcl/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Incr 1  1.02M      DISK        00:00:01     14-NOV-17     
        BP Key: 21   Status: EXPIRED  Compressed: YES  Tag: DATAFILE_BACKUP
        Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/incre_orcl_0rsjhlpt_27_1_20171114.DBF
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    1  Incr 1972551    14-NOV-17 /u01/app/oracle/oradata/orcl/encryptedtbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22      Incr 1  1.05M      DISK        00:00:01     14-NOV-17     
        BP Key: 22   Status: EXPIRED  Compressed: YES  Tag: DATAFILE_BACKUP
        Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/incre_orcl_0ssjhlq0_28_1_20171114.DBF
  SPFILE Included: Modification time: 14-NOV-17
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1972591      Ckp time: 14-NOV-17

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
23      1.79M      DISK        00:00:00     14-NOV-17     
        BP Key: 23   Status: EXPIRED  Compressed: NO  Tag: TAG20171114T100035
        Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/orcl_0tsjhlq3_29_1_20171114.arch

  List of Archived Logs in backup set 23
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    61      1972025    14-NOV-17 1972068    14-NOV-17
  1    62      1972068    14-NOV-17 1972363    14-NOV-17
  1    63      1972363    14-NOV-17 1972367    14-NOV-17
  1    64      1972367    14-NOV-17 1972428    14-NOV-17
  1    65      1972428    14-NOV-17 1972603    14-NOV-17

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    9.33M      DISK        00:00:01     14-NOV-17     
        BP Key: 25   Status: EXPIRED  Compressed: NO  Tag: TAG20171114T100038
        Piece Name: /fra/backup/Tuesday2017-11-1409:59:39_incre/orcl_31_960026438_20171114_1.ctl
  Control File Included: Ckp SCN: 1972670      Ckp time: 14-NOV-17

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26      Full    9.33M      DISK        00:00:01     14-NOV-17     
        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20171114T100508
        Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/orcl_10sjhm2k_32_1_20171114.ctl
  Control File Included: Ckp SCN: 1972931      Ckp time: 14-NOV-17

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    301.95M    DISK        00:00:42     14-NOV-17     
        BP Key: 27   Status: AVAILABLE  Compressed: YES  Tag: DATAFILE_BACKUP
        Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/full_orcl_11sjhm2n_33_1_20171114.DBF
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1972952    14-NOV-17 /u01/app/oracle/oradata/orcl/system01.dbf
  2       Full 1972952    14-NOV-17 /u01/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 1972952    14-NOV-17 /u01/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 1972952    14-NOV-17 /u01/app/oracle/oradata/orcl/users01.dbf
  5       Full 1972952    14-NOV-17 /u01/app/oracle/oradata/orcl/encryptedtbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28      Full    1.05M      DISK        00:00:01     14-NOV-17     
        BP Key: 28   Status: AVAILABLE  Compressed: YES  Tag: DATAFILE_BACKUP
        Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/full_orcl_12sjhm44_34_1_20171114.DBF
  SPFILE Included: Modification time: 14-NOV-17
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1972978      Ckp time: 14-NOV-17

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
29      1.79M      DISK        00:00:00     14-NOV-17     
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20171114T100558
        Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/orcl_13sjhm46_35_1_20171114.arch

  List of Archived Logs in backup set 29
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    65      1972428    14-NOV-17 1972603    14-NOV-17
  1    66      1972603    14-NOV-17 1972943    14-NOV-17
  1    67      1972943    14-NOV-17 1972990    14-NOV-17

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30      Full    80.00K     DISK        00:00:00     14-NOV-17     
        BP Key: 30   Status: AVAILABLE  Compressed: NO  Tag: SPFILE_BACKUP
        Piece Name: /fra/backup/Tuesday2017-11-1410:05:08_full/orcl_SPFILE_14sjhm47_36_1_20171114.ORA
  SPFILE Included: Modification time: 14-NOV-17
  SPFILE db_unique_name: ORCL

Step 8: restore database from full backup

-------Now restore Database----
Catalog the backup set

RMAN> restore database;

Starting restore at 14-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/encryptedtbs01.dbf
channel ORA_DISK_1: reading from backup piece /fra/backup/Tuesday2017-11-1410:05:08_full/full_orcl_11sjhm2n_33_1_20171114.DBF
channel ORA_DISK_1: piece handle=/fra/backup/Tuesday2017-11-1410:05:08_full/full_orcl_11sjhm2n_33_1_20171114.DBF tag=DATAFILE_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 14-NOV-17

RMAN>

Step 9: restore database from incremental backup

--------Catalog Incremental Backup-------------

RMAN> CATALOG START WITH '/fra/backup/Tuesday2017-11-1410:08:52_incre';

searching for all files that match the pattern /fra/backup/Tuesday2017-11-1410:08:52_incre

List of Files Unknown to the Database
=====================================
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_16sjhm9k_38_1_20171114.ctl
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_17sjhm9p_39_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_18sjhmb6_40_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_19sjhmb7_41_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_1asjhmb9_42_1_20171114.arch
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_SPFILE_1bsjhmbb_43_1_20171114.ORA
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_44_960026988_20171114_1.ctl

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_16sjhm9k_38_1_20171114.ctl
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_17sjhm9p_39_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_18sjhmb6_40_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_19sjhmb7_41_1_20171114.DBF
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_1asjhmb9_42_1_20171114.arch
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_SPFILE_1bsjhmbb_43_1_20171114.ORA
File Name: /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_44_960026988_20171114_1.ctl

RMAN> restore database;

Starting restore at 14-NOV-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_17sjhm9p_39_1_20171114.DBF
channel ORA_DISK_1: piece handle=/fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_17sjhm9p_39_1_20171114.DBF tag=DATAFILE_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/encryptedtbs01.dbf
channel ORA_DISK_1: reading from backup piece /fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_18sjhmb6_40_1_20171114.DBF
channel ORA_DISK_1: piece handle=/fra/backup/Tuesday2017-11-1410:08:52_incre/incre_orcl_18sjhmb6_40_1_20171114.DBF tag=DATAFILE_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-NOV-17

RMAN>

Step 10: recover archive log

---------------Recover Database--------------
RMAN> recover database;

Starting recover at 14-NOV-17
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=68
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=69
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=70
channel ORA_DISK_1: reading from backup piece /fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_1asjhmb9_42_1_20171114.arch
channel ORA_DISK_1: piece handle=/fra/backup/Tuesday2017-11-1410:08:52_incre/orcl_1asjhmb9_42_1_20171114.arch tag=TAG20171114T100945
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/fra/archivelog/1_68_957612745.dbf thread=1 sequence=68
archived log file name=/fra/archivelog/1_69_957612745.dbf thread=1 sequence=69
archived log file name=/fra/archivelog/1_70_957612745.dbf thread=1 sequence=70
unable to find archived log
archived log thread=1 sequence=71
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/14/2017 10:39:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 71 and starting SCN of 1973237

RMAN>

Step 11: open database 

---------Open Database--------

RMAN> alter database open resetlogs;

database opened

RMAN>


-------------Now check the data exist or not------------

[oracle@said ~]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 14 10:40:55 2017

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> conn scott/tiger
Connected.
SQL> select * from tab; 

TNAME        TABTYPE CLUSTERID
------------------------------ ------- ----------
ABC        TABLE
BCD        TABLE
BONUS        TABLE
CDE        TABLE
DEPT        TABLE
EMP        TABLE
SALGRADE        TABLE
TDE_TEST        TABLE
TDE_TEST1        TABLE

9 rows selected.

SQL>

SQL> select * from abc;

ID NAME
---------- ------------------------------
10 sebl
20 BB

SQL> select * from bcd;

ID     SALARY
---------- ----------
10 1000
20 20000

SQL> select * from cde;

ID DEPT
---------- ------------------------------
10 IT
20 HR

SQL>

Export/Import in Datapump using query parameter

Purpose

Enables you to filter the data that is exported by specifying a clause for a SELECT statement, which is applied to all/multiple tables in the export job or to a specific table.

Solution

[oracle@said ~]$ cat bak.sh
expdp system/passwd@orcl parfile=/home/oracle/parameter.par
[oracle@said ~]$


[oracle@said ~]$ cat parameter.par
tables=SCOTT.EMP
query=SCOTT.EMP:"WHERE (liability_issued - nvl(liability_reversed, 0) != 0
or  paid_margin_amount_lcy - nvl(refunded_margin_lcy, 0) != 0
or  indem_liability_issued - nvl(indem_liability_reversed, 0) != 0)"
directory=dpump
dumpfile=emp.dmp
logfile=emp.log
[oracle@said ~]$  ./back.sh

Export: Release 11.2.0.3.0 - Production on Thu Mar 16 14:39:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@orcl parfile=/home/oracle/parameter.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 683.1 GB
Processing object type TABLE_EXPORT/TABLE/TABLE

.

.

.

.

.

.

Export Backup exclude table in Data Pump Error: ORA-39071

Error :

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00911: invalid character


Solution:

[oracle@saidrasel]$ expdp system/password schemas= "exclude=TABLE:\"IN ('OUTWDCLR','INWARD_IMAGE')\"" directory=DATA_P dumpfile=MICR-20160512.dmp logfile=MICR-20160512.log EXCLUDE=STATISTICS CONTENT=ALL

EXPDP in Oracle 12c (ORA-39002 ORA-39070 ORA-39087)

DROP DIRECTORY DUMP_DATA;

CREATE OR REPLACE DIRECTORY
DUMP_DATA AS
'G:\InHouse_DB_Backup';



GRANT EXECUTE, READ, WRITE ON DIRECTORY DUMP_DATA TO system WITH GRANT OPTION;

impdp system/sys123 schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

C:\Users\Administrator>impdp system/sys123 schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

Import: Release 12.1.0.2.0 - Production on Tue Aug 25 14:51:00 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_DATA is invalid

Note: In Oracle 12 c during expdp and impdp pluggable Database name must be mentioned

impdp system/sys123@orcl_pdb schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

ORA-31693 ORA-02354 ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_933907484$" too small in expdp

Problem: Processing object type DATABASE_EXPORT/AUDIT
ORA-31693: Table data object "MICR"."OUTWDCLR" failed to load/unload and is being skipped due to error:ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_933907484$" too small

select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER='MICR' and TABLE_NAME='OUTWDCLR';

COLUMN_NAME SECUREFILE PCTVERSION RETENTION

IMAGE_FRONT NO 10
IMAGE_REAR NO 10


SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>


SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
            3291

SQL>

We see the retention comes back showing 900 seconds (15 minutes) which is the same as the current UNDO_RETENTION,
but the maxquery length is 3291 seconds.


When the LOB was created, the actual setting for RETENTION was defined by the current setting for UNDO_RETENTION.
This time is not long enough.

Solution:::

 1. Modify the current UNDO_RETENTION for the database:

SQL>ALTER SYSTEM SET UNDO_RETENTION = 4500 scope=both sid='*';

2. Modify the LOB retention to become greater than the undersized retention parameter following the steps from Note:563470.1

SQL> alter table MICR.OUTWDCLR modify lob(IMAGE_FRONT) (retention);
Table altered.

SQL> alter table MICR.OUTWDCLR modify lob(IMAGE_REAR) (retention);
Table altered.


3. Query the lob retention again to verify that the change has taken hold:
SQL> select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER=upper('&OWNER') and TABLE_NAME=upper('&TABLE_NAME') ;

COLUMN_NAME                    SEC PCTVERSION  RETENTION
------------------------------ --- ---------- ----------
IMAGE_FRONT                NO                  4500
IMAGE_REAR                 NO                  4500

4. Perform the export again.

Import (IMPDP) Oracle Database backup from network location

Scenario:

Suppose we want to restore backup into destination database server (192.168.10.6) and backup resides in source database server (192.168.10.32)

Solution:

Step1: We need to keep the backup dump file into source database server (192.168.10.32)

Step2: We need to create a database link into source database server (192.168.10.32)

CREATE PUBLIC DATABASE LINK DPUMP
 CONNECT TO SYSTEM
 IDENTIFIED BY
 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.6)(PORT=1521))(CONNECT_DATA=(SID=orcl)))';

Step3: Now we need to create a directory where database backup will reside 

DROP DIRECTORY data_dump;
CREATE OR REPLACE DIRECTORY data_dump AS '/back/exp/';

GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.data_dump TO SYSTEM WITH GRANT OPTION;

Step4: Now we need to execute import (impdp) command from source database server (192.168.10.32) to restore
       backup into destination database server (192.168.10.6)

Step5: Put the schemas value which schema you want to import

impdp system/sys123 schemas=scott,hr,test network_link=DPUMP directory=data_dump dumpfile=orcl_full.dmp logfile=orcl_full.log    


  
Step6: Now the data values that you want to import..

Cheers.....  

Export/Import Specific Table in Oracle

Export/Import Specific Table in Oracle 

Step1: Export specific table/tables backup using following command

exp /password@db_name tables=  file=D:\cnsstnt.dmp  statistics=NONE log=D:\cnsstnt.log

Step2: Import specific table/tables backup using following command

imp /password@db_name fromuser= touser= tables=  file=D:\cnsstnt.dmp  statistics=NONE ignore=Y log=D:\cnsstnt.log

Cheers....