Complete recovery of a user tablespace loss RAC -12.1.0.2.0

Preview:

• The database is running in ARCHIVELOG mode

• One or some datafiles of a user tablespace are lost

• Recovery Catalog is not being used

Solution :

• Remove all datafile from asmcmd
• Check datafile users
• restore database preview summary
• validate database
• restore tablespace users
• recover tablespace users
• Database open

• Check and remove table space users

ASMCMD> ls
IDATA.273.1015521745
ILOG.258.1015521745
INDX.259.1015521745
SOETBS.270.1015521745
SYSAUX.271.1015521745
SYSTEM.269.1015521745
UNDOTBS1.265.1015521745
UNDOTBS2.260.1015521745
USERS.257.1015521749
ASMCMD> rm -rf SOETBS.270.1015521745
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/RAc/DATAFILE/SOETBS.270.1015521745' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASMCMD> rm -rf USERS.257.1015521749
ASMCMD> ls
IDATA.273.1015521745
ILOG.258.1015521745
INDX.259.1015521745
SOETBS.270.1015521745
SYSAUX.271.1015521745
SYSTEM.269.1015521745
UNDOTBS1.265.1015521745
UNDOTBS2.260.1015521745
ASMCMD>

• Check datafile status of tablespace users 

SQL> SELECT NAME FROM V$DATAFILE WHERE TS# = (SELECT TS# FROM V$TABLESPACE WHERE NAME='USERS');

NAME

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

+DATA/RAC/DATAFILE/users.257.1015521749

• Check restore preview status 

RMAN> restore database preview summary;

Starting restore at 05-AUG-19

using channel ORA_DISK_1

List of Backups

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

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

169     B  F  A DISK        05-AUG-19       1       1       NO         FULL_DB_MOUNT

List of Archived Log Copies for database with db_unique_name RAC

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

Key     Thrd Seq     S Low Time

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

842     1    1       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_1.477.1015520811

846     1    2       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_2.465.1015522613

848     1    3       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_3.458.1015523179

849     1    4       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_4.454.1015523293

851     1    5       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_5.445.1015523881

841     2    1       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_1.479.1015520387

843     2    2       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_2.476.1015522439

844     2    3       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_3.473.1015522613

845     2    4       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_4.469.1015522613

847     2    5       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_5.461.1015523177

850     2    6       A 05-AUG-19

        Name: +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_6.450.1015523295

recovery will be done up to SCN 16289786

Media recovery start SCN is 16289786

Recovery must be done beyond SCN 16289786 to clear datafile fuzziness

Finished restore at 05-AUG-19

• Validate backup piece 

RMAN> validate database;

Starting validate at 05-AUG-19

using channel ORA_DISK_1

RMAN-06169: could not read file header for datafile 6 error reason 4

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of validate command at 08/05/2019 18:04:31

RMAN-06056: could not access datafile 6

RMAN>

RMAN>  SELECT NAME FROM V$TABLESPACE WHERE TS# = (SELECT D.TS# FROM V$DATAFILE D WHERE FILE#=6);

NAME

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

USERS

RMAN>

• Restore tablespace users 

RMAN> restore tablespace users;

Starting restore at 05-AUG-19

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 00006 to +DATA/RAC/DATAFILE/users.257.1015521749

channel ORA_DISK_1: reading from backup piece /u01/Backup/ora_df1015515357_s220_s1

channel ORA_DISK_1: piece handle=/u01/Backup/ora_df1015515357_s220_s1 tag=FULL_DB_MOUNT

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 05-AUG-19

• Recover table space users

RMAN> recover tablespace users;

Starting recover at 05-AUG-19

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_1.477.1015520811

archived log for thread 1 with sequence 2 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_2.465.1015522613

archived log for thread 1 with sequence 3 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_3.458.1015523179

archived log for thread 1 with sequence 4 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_4.454.1015523293

archived log for thread 1 with sequence 5 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_5.445.1015523881

archived log for thread 2 with sequence 1 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_1.479.1015520387

archived log for thread 2 with sequence 2 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_2.476.1015522439

archived log for thread 2 with sequence 3 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_3.473.1015522613

archived log for thread 2 with sequence 4 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_4.469.1015522613

archived log for thread 2 with sequence 5 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_5.461.1015523177

archived log for thread 2 with sequence 6 is already on disk as file +FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_6.450.1015523295

archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_1.477.1015520811 thread=1 sequence=1

archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_1.479.1015520387 thread=2 sequence=1

archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_2.476.1015522439 thread=2 sequence=2

archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_3.473.1015522613 thread=2 sequence=3

archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_2.465.1015522613 thread=1 sequence=2

archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_4.469.1015522613 thread=2 sequence=4

archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_05/thread_1_seq_3.458.1015523179 thread=1 sequence=3

archived log file name=+FRA/RAC/ARCHIVELOG/2019_08_05/thread_2_seq_5.461.1015523177 thread=2 sequence=5

media recovery complete, elapsed time: 00:00:03

Finished recover at 05-AUG-19

• Open database 

RMAN> alter database open;

Statement processed