Recovering NOLOGGING Tables and Indexes Scenario.
Step 1: Create table and insert data.
==================================================
SQL> alter table pqrs.xyz nologging;
Table altered.
SQL> insert into pqrs.xyz values (20);
1 row created.
SQL> commit;
Commit complete.
SQL> conn pqrs/pqrs
Connected.
SQL> create index ind1 on pqrs.xyz(a);
Index created.
SQL>
===============================================
SQL> create table pqrs.abc (a number);
Table created.
SQL> insert into pqrs.abc values (10);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> create index pqrs.ind2 on pqrs.abc(a);
Index created.
SQL>
===========================================================================
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/sysaux01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/undotbs01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf
Step 2: Remove datafile for scenario purpose.
SQL> !rm /u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf
SQL> alter database datafile 4 offline;
Database altered.
SQL>
============================================================================
Step 3: Restore datafile 4
$ rman target /
connected to target database: VIRTUALD (DBID=1142008032)
RMAN> run
2> {
3> ALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576,
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so,
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda.cfg)';
4> restore datafile 4;
5> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=1 device type=SBT_TAPE
channel C1: Database Application Agent Oracle v4.6.0.0
Starting restore at 11-DEC-18
channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00004 to
/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf
channel C1: reading from backup piece VIRTUALD_01tkgimp_1_1
channel C1: piece handle=VIRTUALD_01tkgimp_1_1 tag=VIRTUALD_HOT_30DAYS
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:03
Finished restore at 11-DEC-18
released channel: C1
RMAN>
================================================================================
Recover datafile
RMAN> recover datafile 4;
Starting recover at 11-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/VIRTUALDB/
archivelog/2018_12_11/o1_mf_1_6_g0zysgm3_.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/11/2018 12:13:14
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06102: no channel to restore a backup or copy of archived log for
thread 1 with sequence 5 and starting SCN of 488698
RMAN-06102: no channel to restore a backup or copy of archived log for
thread 1 with sequence 4 and starting SCN of 488682
RMAN-06102: no channel to restore a backup or copy of archived log for
thread 1 with sequence 3 and starting SCN of 488589
RMAN-06102: no channel to restore a backup or copy of archived log for
thread 1 with sequence 2 and starting SCN of 488575
RMAN>
Note: Recover datafile 4 failed because of missing of archive logfiles
===============================================================================
Step 4: Restore archive logs.
RMAN> run
2> {
3> ALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576,
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so,
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda.cfg)';
4> restore archivelog from sequence 1 until sequence 5;
5> }
released channel: ORA_DISK_1
allocated channel: C1
channel C1: SID=1 device type=SBT_TAPE
channel C1: Database Application Agent Oracle v4.6.0.0
Starting restore at 11-DEC-18
channel C1: starting archived log restore to default destination
channel C1: restoring archived log
archived log thread=1 sequence=1
channel C1: restoring archived log
archived log thread=1 sequence=2
channel C1: restoring archived log
archived log thread=1 sequence=3
channel C1: restoring archived log
archived log thread=1 sequence=4
channel C1: restoring archived log
archived log thread=1 sequence=5
channel C1: reading from backup piece VIRTUALD_05tkgiq8_1_1
channel C1: piece handle=VIRTUALD_05tkgiq8_1_1 tag=TAG20181211T120005
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:01
Finished restore at 11-DEC-18
released channel: C1
RMAN>
========================================================================================
Step 5: Recover datafile 4.
RMAN> recover datafile 4;
Starting recover at 11-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/VIRTUALDB/archivelog/
2018_12_11/o1_mf_1_2_g0zzpcy8_.arc
archived log for thread 1 with sequence 3 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/VIRTUALDB/archivelog/
2018_12_11/o1_mf_1_3_g0zzpcyd_.arc
archived log for thread 1 with sequence 4 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/VIRTUALDB/archivelog/
2018_12_11/o1_mf_1_4_g0zzpcz1_.arc
archived log for thread 1 with sequence 5 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/VIRTUALDB/archivelog/
2018_12_11/o1_mf_1_5_g0zzpd02_.arc
archived log for thread 1 with sequence 6 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/VIRTUALDB/archivelog/
2018_12_11/o1_mf_1_6_g0zysgm3_.arc
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_11/o1_mf_1_2_g0zzpcy8_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_11/o1_mf_1_3_g0zzpcyd_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_11/o1_mf_1_4_g0zzpcz1_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-DEC-18
RMAN>
===========================================================================
Step 6: Verify DB and tables.
$ sqlplus "/as sysdba"
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
SQL> alter database datafile 4 online;
Database altered.
SQL>
=======================================================================
1* select object_name,status from dba_objects where owner='PQRS'
SQL> /
OBJECT_NAME STATUS
-------------------- -------
ABC VALID
EMP VALID
IND1 VALID
IND2 VALID
T1 VALID
XYZ VALID
6 rows selected.
==================================================================================
SQL> select table_name,owner,LOGGING,STATUS from dba_tables where owner='PQRS';
TABLE_NAME OWNER LOG STATUS
------------------------------ ------------------------------ --- --------
EMP PQRS YES VALID
T1 PQRS YES VALID
XYZ PQRS NO VALID
ABC PQRS YES VALID
SQL>
=====================================================================
See also
Oracle Database backup configurations.
Recovering After the Loss of Archived Redo Log Files
Recovery of Read-Only Files with a Re-Created Control File
Recover oracle DB after loss of datafiles scenarios
Recover From a DROP/TRUNCATE/DELETE TABLE with RMAN
Restore Oracle database from Cold backup
Restore and recovering the Oracle database
https://support.oracle.com
