Please find the following details for understanding the problem -
Platform Details:
Oracle 12c RAC (3 node) cluster.
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
[oracle@host01 trace]$ uname -r
2.6.39-400.209.1.el6uek.x86_64
[oracle@host01 trace]$ uname -a
Linux host01 2.6.39-400.209.1.el6uek.x86_64 #1 SMP Tue Sep 10 20:39:39 PDT 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@host01 trace]$
I created a user and created few tables in the same.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> create user h3 identified by h3;
User created.
SQL> grant dba to h3;
Grant succeeded.
SQL> conn h3/h3
Connected.
SQL> create table t3 (name varchar2(20));
Table created.
SQL> insert into t3 select last_name from hr.employees;
107 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into t3 select first_name from hr.employees;
107 rows created.
SQL> commit;
Commit complete.
SQL> show user
USER is "H3"
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@host03 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jul 1 08:35:45 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1443466872)
RMAN> backup database plus archivelog;
Starting backup at 01-JUL-16
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 instance=orcl_2 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=8 RECID=7 STAMP=916042367
input archived log thread=3 sequence=11 RECID=6 STAMP=916042364
input archived log thread=1 sequence=15 RECID=8 STAMP=916042369
input archived log thread=3 sequence=12 RECID=11 STAMP=916042645
input archived log thread=2 sequence=9 RECID=9 STAMP=916042503
input archived log thread=1 sequence=16 RECID=10 STAMP=916042645
input archived log thread=2 sequence=10 RECID=12 STAMP=916042651
input archived log thread=1 sequence=17 RECID=14 STAMP=916043755
input archived log thread=3 sequence=13 RECID=15 STAMP=916043756
input archived log thread=2 sequence=11 RECID=13 STAMP=916043754
channel ORA_DISK_1: starting piece 1 at 01-JUL-16
pchannel ORA_DISK_1: finished piece 1 at 01-JUL-16
piece handle=+FRA/ORCL/BACKUPSET/2016_07_01/annnf0_tag20160701t083558_0.279.916043759 tag=TAG20160701T083558 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 01-JUL-16
Starting backup at 01-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORCL/DATAFILE/example.285.915686227
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/sysaux.277.915685823
input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.278.915685927
input datafile file number=00004 name=+DATA/ORCL/DATAFILE/undotbs1.280.915686075
input datafile file number=00007 name=+DATA/ORCL/DATAFILE/undotbs3.287.915687283
input datafile file number=00002 name=+DATA/ORCL/DATAFILE/undotbs2.286.915687267
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.279.915686073
channel ORA_DISK_1: starting piece 1 at 01-JUL-16
channel ORA_DISK_1: finished piece 1 at 01-JUL-16
piece handle=+FRA/ORCL/BACKUPSET/2016_07_01/nnndf0_tag20160701t083615_0.280.916043777 tag=TAG20160701T083615 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:15
Finished backup at 01-JUL-16
Starting backup at 01-JUL-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=12 RECID=16 STAMP=916044092
input archived log thread=1 sequence=18 RECID=18 STAMP=916044092
input archived log thread=3 sequence=14 RECID=17 STAMP=916044092
channel ORA_DISK_1: starting piece 1 at 01-JUL-16
channel ORA_DISK_1: finished piece 1 at 01-JUL-16
piece handle=+FRA/ORCL/BACKUPSET/2016_07_01/annnf0_tag20160701t084136_0.283.916044097 tag=TAG20160701T084136 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-JUL-16
Starting Control File and SPFILE Autobackup at 01-JUL-16
piece handle=+FRA/ORCL/AUTOBACKUP/2016_07_01/s_916044098.284.916044103 comment=NONE
Finished Control File and SPFILE Autobackup at 01-JUL-16
RMAN>exit
Recovery Manager complete.
I had checked that my user and table belong to USERS tablespace which is datafile 6.
[oracle@host03 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jul 1 08:51:20 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1443466872)
RMAN> sql 'alter tablespace users offline';
using target database control file instead of recovery catalog
sql statement: alter tablespace users offline
RMAN> restore datafile 6;
Starting restore at 01-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=81 instance=orcl_2 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 00006 to +DATA/ORCL/DATAFILE/users.279.915686073
channel ORA_DISK_1: reading from backup piece +FRA/ORCL/BACKUPSET/2016_07_01/nnndf0_tag20160701t083615_0.280.916043777
channel ORA_DISK_1: piece handle=+FRA/ORCL/BACKUPSET/2016_07_01/nnndf0_tag20160701t083615_0.280.916043777 tag=TAG20160701T083615
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 01-JUL-16
RMAN> recover datafile 6;
Starting recover at 01-JUL-16
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 18 is already on disk as file +FRA/ORCL/ARCHIVELOG/2016_07_01/thread_1_seq_18.282.916044093
archived log for thread 2 with sequence 12 is already on disk as file +FRA/ORCL/ARCHIVELOG/2016_07_01/thread_2_seq_12.270.916044091
archived log for thread 3 with sequence 14 is already on disk as file +FRA/ORCL/ARCHIVELOG/2016_07_01/thread_3_seq_14.281.916044093
archived log for thread 3 with sequence 15 is already on disk as file +FRA/ORCL/ARCHIVELOG/2016_07_01/thread_3_seq_15.285.916044529
archived log file name=+FRA/ORCL/ARCHIVELOG/2016_07_01/thread_3_seq_14.281.916044093 thread=3 sequence=14
media recovery complete, elapsed time: 00:00:03
Finished recover at 01-JUL-16
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
RMAN> exit
Recovery Manager complete.
[oracle@host03 ~]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 1 08:52:35 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: h3/h3
ERROR:
ORA-01017: invalid username/password; logon denied
I tried the same thing with recover full database, recover datafile and tablespace too, but it's not able to recover my newly created user or the table.
Kindly suggest if there's something can be done.