Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why did RMAN ignore SET UNTIL TIME clause in Point-in-time restore ?

greenyOct 14 2015 — edited Oct 15 2015

DB version:11.2.0.4

Platfor: Oracle Linux 6.5

Standalone DB in archive log mode

Following is a test restore I did in my test DB.

I created a test tablespace. Created a table on this tablespace. INSERTed few records in the table.

At "14-10-2015 10:43" , I took a tablespace backup. After the tablespace backup (4 minutes after 10:43), I INSERTed few more records to the table (at 14-10-2015 10:47 )

Then, to simulate the loss of datafile, I deleted a datafile.

I only want data till 10:43. Nothing after that . So, I did a TSPITR (Tablespace point-in-time recovery) to 10:43. I did the recovery using SET UNTIL TIME clause as shown below. But, I can see records inserted at 10:47 (shown in red below). It shouldn't be there. Why did RMAN ignore the time I specified using SET UNTIL TIME clause ?

SQL> select * from v$version where rownum < 2;

BANNER

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

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

DB is in archive log mode

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 37

Next log sequence to archive 39

Current log sequence 39

SQL> create tablespace GHR_DATA datafile '/u01/app/oracle/oradata/mtxcprod/ghr_data_01.dbf' size 300m

autoextend off

extent management local

uniform size 4M

segment space management auto; 2 3 4 5

Tablespace created.

SQL> alter tablespace GHR_DATA add datafile '/u01/app/oracle/oradata/mtxcprod/ghr_data_02.dbf' size 100m autoextend off;

Tablespace altered.

set lines 200

col TABLESPACE_NAME format a10

col file_name format a55

SQL> select file_id, tablespace_name, file_name, status from dba_Data_Files order by file_id asc;

FILE_ID TABLESPACE FILE_NAME STATUS

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

1 SYSTEM /u01/app/oracle/oradata/mtxcprod/system01.dbf AVAILABLE

2 SYSAUX /u01/app/oracle/oradata/mtxcprod/sysaux01.dbf AVAILABLE

3 UNDOTBS1 /u01/app/oracle/oradata/mtxcprod/undotbs01.dbf AVAILABLE

4 USERS /u01/app/oracle/oradata/mtxcprod/users01.dbf AVAILABLE

5 EXAMPLE /u01/app/oracle/oradata/mtxcprod/example01.dbf AVAILABLE

6 USERS /u01/app/oracle/oradata/mtxcprod/users02.dbf AVAILABLE

7 GHR_DATA /u01/app/oracle/oradata/mtxcprod/ghr_data_01.dbf AVAILABLE

8 GHR_DATA /u01/app/oracle/oradata/mtxcprod/ghr_data_02.dbf AVAILABLE

8 rows selected.

SQL> create user GHR_APP identified by Intense#2 default tablespace GHR_DATA;

User created.

SQL> alter user GHR_APP quota unlimited on GHR_DATA;

User altered.

SQL> grant create session, create table to GHR_APP;

Grant succeeded.

SQL> conn GHR_APP/Intense#2

Connected.

SQL> alter session set nls_date_format='DD-MM-YYYY HH24:MI';

create table EMP2 (ename varchar2(15), join_date date);

insert into EMP2 values('JAMES',SYSDATE);

insert into EMP2 values('GILBERT',SYSDATE);

commit;

SQL> select * from emp2;

ENAME JOIN_DATE

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

JAMES 14-10-2015 10:42

GILBERT 14-10-2015 10:42

SQL> select sysdate from dual;

SYSDATE

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

14-10-2015 10:43

--- Now, I took a tablespace level backup.

--- 400 MB Tablespace's backup size only 2.3MB . Cool !

[oracle@gpwavmp204 tempBKP]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 14 10:44:24 2015

connected to target database: MTXCPROD (DBID=1853277602)

RMAN> BACKUP TABLESPACE GHR_DATA FORMAT = '/u01/app/oracle/tempBKP/GHR_DATA_TBSbkp_%u%p%c';

Starting backup at 14-OCT-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/u01/app/oracle/oradata/mtxcprod/ghr_data_01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/mtxcprod/ghr_data_02.dbf

channel ORA_DISK_1: starting piece 1 at 14-OCT-15

channel ORA_DISK_1: finished piece 1 at 14-OCT-15

piece handle=/u01/app/oracle/tempBKP/GHR_DATA_TBSbkp_08qjnges11 tag=TAG20151014T214500 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-OCT-15

RMAN> exit

[oracle@gpwavmp204 tempBKP]$ ls -lh

total 2.3M

-rw-r-----. 1 oracle oinstall 2.3M Oct 14 10:45 GHR_DATA_TBSbkp_08qjnges11

---- Now , INSERT 4 more rows

sqlplus GHR_APP/Intense#2

insert into EMP2 values('SUNITA',SYSDATE);

insert into EMP2 values('GARRY',SYSDATE);

insert into EMP2 values('KAREN',SYSDATE);

insert into EMP2 values('ABDUL',SYSDATE);

SQL> select * from emp2;

ENAME JOIN_DATE

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

JAMES 14-10-2015 10:42

GILBERT 14-10-2015 10:42 --------> RMAN tablespace ad-hoc Backup has info only till here

SUNITA 14-10-2015 10:47

GARRY 14-10-2015 10:47

KAREN 14-10-2015 10:47

ABDUL 14-10-2015 10:47

6 rows selected.

--- Now , to simulate the loss of a datafile, remove a datafile from the tablespace

$ rm /u01/app/oracle/oradata/mtxcprod/ghr_data_01.dbf

--- Now, log back as GHR_APP user

SQL> conn GHR_APP/Intense#2

SQL> insert into EMP2 values('sven',sysdate);

insert into EMP2 values('sven',sysdate)

ERROR at line 1:

ORA-01116: error in opening database file 7

ORA-01110: data file 7: '/u01/app/oracle/oradata/mtxcprod/ghr_data_01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

---- Performing point-in-time Tablespace restore. I only want data till 10:43. Nothing after that

run

{

SET UNTIL time "to_date('14-10-2015 10:43:00','DD-MM-YYYY hh24:MI:SS')" ;

restore TABLESPACE GHR_DATA;

RECOVER TABLESPACE GHR_DATA;

}

Log of the above command shown below :

[oracle@gpwavmp204 tempBKP]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 14 10:50:08 2015

connected to target database: MTXCPROD (DBID=1853277602, not open)

RMAN> run

{

SET UNTIL time "to_date('14-10-2015 10:43:00','DD-MM-YYYY hh24:MI:SS')" ;

restore TABLESPACE GHR_DATA;

RECOVER TABLESPACE GHR_DATA;

}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 14-OCT-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

creating datafile file number=7 name=/u01/app/oracle/oradata/mtxcprod/ghr_data_01.dbf

creating datafile file number=8 name=/u01/app/oracle/oradata/mtxcprod/ghr_data_02.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 14-OCT-15

Starting recover at 14-OCT-15

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 14-OCT-15

col file_name format a55SQL> SQL>

SQL>