Skip to Main Content
  • Questions

  • RMAN - "report unrecoverable" command not listing the expected tablespace

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manikandan.

Asked: April 12, 2017 - 6:47 am UTC

Last updated: April 19, 2017 - 9:28 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

I have added some rows to a table TAB_1 with NOLOGGING option after taking an RMAN backup. The tablespace TBS_1 is used for the table. When I tried to find the unrecoverable tablespaces using the command "REPORT UNRECOVERABLE", the expected tablespace is not reported! What else could be the reason for this? Below are the some information you may want to know
1 The version used is Oracle 12c
2. Used rman catalog
3. The database is running in ARCHIVELOG mode

and Connor said...

Can you give us a test case ? Here's mine:

C:\>rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Apr 14 18:28:07 2017

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

connected to target database: DB122 (DBID=872342268)

RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------


--
-- then into SQL Plus
--
SQL> create table t1 nologging tablespace demo
  2  as select * from dba_objects;

Table created.


--
-- then back into rman
--

RMAN> report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
8    full or incremental     C:\ORACLE\ORADATA\DB122\DEMO.DBF


Dont forget that you have to a *direct* mode operation (eg, insert /*+ APPEND */ ).

Just because a table is marked as NOLOGGING, does *not* mean that standard dml (insert, update, delete) will not be logged.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perhaps the issue is with the older version "12.1.0.2.0"!

Manikandan Krishnan, April 18, 2017 - 10:56 am UTC

I could notice one thing that you are using a higher version which is "12.2.0.1.0" and im using "12.1.0.2.0"
Below is the test case for your perusal

RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

RMAN> report unrecoverable database;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

RMAN> host;

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 17 09:53:59 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table t1 nologging tablespace ADMIN as select * from dba_objects;

Table created.

SQL> select table_name from user_tables where table_name = 'T1';

TABLE_NAME
--------------------------------------------------------------------------------
T1

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Windows\system32>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 17 09:54:32 2017

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

connected to target database: XXXX
RMAN> report unrecoverable;

using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

RMAN> report unrecoverable database;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------

RMAN>

Connor McDonald
April 19, 2017 - 9:28 am UTC

try this:

select force_logging from v$database;
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.