restore single table from expdp full backup

Lets take a full Backup:


F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8


Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016

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

Password:

UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8


.

.

.

.

.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

. . exported "SCOTT1"."DEPT"                             4.976 KB       4 rows

. . exported "SCOTT1"."EMP"                              5.625 KB      14 rows

. . exported "SCOTT1"."SALGRADE"                         4.890 KB       5 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

.

.

.

.

.

Master table "ATOORPU"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ATOORPU.SYS_EXPORT_FULL_01 is:

  C:\DPUMP\FULLDB_01.DMP

  C:\DPUMP\FULLDB_02.DMP

  C:\DPUMP\FULLDB_03.DMP

  C:\DPUMP\FULLDB_04.DMP

  C:\DPUMP\FULLDB_05.DMP

  C:\DPUMP\FULLDB_06.DMP

  C:\DPUMP\FULLDB_07.DMP

  C:\DPUMP\FULLDB_08.DMP

Job "ATOORPU"."SYS_EXPORT_FULL_01" successfully completed at Tue May 17 14:27:13 2016 elapsed 0 00:05:36


****************************************************************************************************************

FROM THIS FULL BACKUP I WOULD LIKE TO RESTORE A TABLE FROM SCHEMA: SCOTT & TABLE: BONUS FULL BACKUP INTO SCHEMA.

****************************************************************************************************************

F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS


Import: Release 11.2.0.4.0 - Production on Tue May 17 16:04:51 2016


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

Password:


UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SCOTT"."BONUS"                                 0 KB       0 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 17 16:05:05 2016 elapsed 0 00:00:08


****************************************************************************************************************

FROM THIS FULL BACKUP I WOULD LIKE TO RESTORE A TABLE FROM FULL BACKUP AND RESTORE IT FROM SCHEMA : SCOTT INTO ANOTHER SCHEMA : SCOTT1.

****************************************************************************************************************

F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS REMAP_SCHEMA=scott:scott1


Import: Release 11.2.0.4.0 - Production on Tue May 17 16:20:31 2016


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

Password:


UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS REMAP_SCHEMA=scott:scot

1

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SCOTT1"."BONUS"                                0 KB       0 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 17 16:20:45 2016 elapsed 0 00:00:08

Restore and recover database from RMAN backup - DB in No archive log mode

Simple example for restore and recover database from RMAN backup.

Assuming that : I have a need to restore my DB as some important tables/data was dropped/removed and its really mission critical. I have already got some RMAN backups from past.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1728053248 bytes
Fixed Size                  3046512 bytes
Variable Size            1224737680 bytes
Database Buffers          486539264 bytes
Redo Buffers               13729792 bytes
Database mounted.

C:\Users\oracle>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 19 12:46:12 2016

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

connected to target database: ORCLDB (DBID=1196XXXX70)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
20      B  F  A DISK        05-FEB-15       1       1       NO         TAG20160205T155541
21      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
22      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
23      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
24      B  F  A DISK        05-FEB-15       1       1       NO         TAG20160205T160507


RMAN> restore database from TAG='FULL_ORCLDB';

Starting restore at 19-FEB-15
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 00003 to E:\APP\ORADATA\ORCLDB\UNDOTBS01.DBF
:
:
:
channel ORA_DISK_1: restoring datafile 00119 to E:\APP\ORADATA\TLAPP.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\BACKUP\ORCLDB_BKP_0UQT63BQ_1_1
channel ORA_DISK_1: piece handle=E:\APP\BACKUP\ORCLDB_BKP_0UQT63BQ_1_1 tag=FULL_ORCLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:15
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 00120 to E:\APP\ORADATA\TLLARGE.DBF
channel ORA_DISK_1: restoring datafile 00121 to E:\APP\ORADATA\TLWORK.DBF
channel ORA_DISK_1: restoring datafile 00122 to E:\APP\ORADATA\WAAPP.DBF
channel ORA_DISK_1: restoring datafile 00123 to E:\APP\ORADATA\ORCLDB\PSMATVW.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\BACKUP\ORCLDB_BKP_0VQT63ER_1_1
channel ORA_DISK_1: piece handle=E:\APP\BACKUP\ORCLDB_BKP_0VQT63ER_1_1 tag=FULL_ORCLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:36
Finished restore at 19-FEB-15

Now lets try opening DB (I know it wont work but lets try..)

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\APP\ORADATA\ORCLDB\SYSTEM01.DBF'

Now lets Recover DB

RMAN> recover database;

Starting recover at 19-FEB-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3390 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO03.LOG
archived log for thread 1 with sequence 3391 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO01.LOG
archived log for thread 1 with sequence 3392 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO02.LOG
RMAN-08187: WARNING: media recovery until SCN 69107847 complete
Finished recover at 19-FEB-15



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>

We must open the database in open resetlogs mode. in order for recover to be completed. (WE CAN DO OPEN RESET LOGS FROM RMAN or SQLPLUS PROMPT)

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit


Recovery Manager complete.

C:\Users\oracle>