Oracle 11gR2 RMAN – Recovering of single datafile (ORA-01109)

Oracle 11gR2 RMAN – Recovering of single datafile(ORA-01109)

Today, I learnt something new about Oracle RMAN, thus the need for this post to document it down.

Verify that tables in SCOTT schema is stored in USERS tablespace which is in /u01/app/oracle/oradata/orcl/orcl/users01.dbf

SQL> select owner,table_name,tablespace_name from dba_tables where owner='SCOTT';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT DEPT USERS
SCOTT EMP USERS
SCOTT BONUS USERS
SCOTT SALGRADE USERS

Remove USERS tablespace datafile

# rm /u01/app/oracle/oradata/orcl/orcl/users01.dbf

Verify that there’s indeed an error with the database!

SQL> alter system flush buffer_cache
SQL> select count(*) from scott.emp;
select count(*) from scott.emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate;

Bring the USERS tablespace offline first

SQL> startup mount;
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01109: database not open

Now, note the above error that I can’t offline the tablespace. I was unable to offline a tablespace when the database is not in OPEN mode. However, we can offline a datafile while not in OPEN mode 🙂

SQL> SELECT * FROM v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
 4 ONLINE ONLINE FILE NOT FOUND 0
SQL> alter database datafile 4 offline;
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME ONLINE_
-------------------------------------------------- ---------- 
/u01/app/oracle/oradata/orcl/orcl/users01.dbf 4 USERS OFFLINE
/u01/app/oracle/oradata/orcl/orcl/undotbs01.dbf 3 UNDOTBS1 ONLINE
/u01/app/oracle/oradata/orcl/orcl/sysaux01.dbf 2 SYSAUX ONLINE
/u01/app/oracle/oradata/orcl/orcl/system01.dbf 1 SYSTEM SYSTEM
SQL> alter database open;
Database altered.

Now that the database is open, we should offline the USERS tablespace before performing recovery.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SQL> alter tablespace users offline immediate;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS OFFLINE

Perform RMAN restore and recover

[oracle@localhost admin]$ rman target / catalog rcuser@rman

connected to target database: ORCL (DBID=1426604546)
recovery catalog database Password:
connected to recovery catalog database
RMAN> restore datafile 4;
Starting restore at 02-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 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 00004 to /u01/app/oracle/oradata/orcl/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /archival/05qqcl6n_1_1
channel ORA_DISK_1: piece handle=/archival/05qqcl6n_1_1 tag=LONG01
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-JAN-16
RMAN> recover datafile 4;
Starting recover at 02-JAN-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-JAN-16

Verify that everything is back to normal 🙂

SQL> alter tablespace users online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME ONLINE_
-------------------------------------------------- ---------- ------------------------------ -------
/u01/app/oracle/oradata/orcl/orcl/users01.dbf 4 USERS ONLINE
/u01/app/oracle/oradata/orcl/orcl/undotbs01.dbf 3 UNDOTBS1 ONLINE
/u01/app/oracle/oradata/orcl/orcl/sysaux01.dbf 2 SYSAUX ONLINE
/u01/app/oracle/oradata/orcl/orcl/system01.dbf 1 SYSTEM SYSTEM

Summary

This was something that I encounter recently while practicing my backup and recovery skills for the upcoming OCM Exam. I forgotten that I could only offline datafile but not tablespace while the database is not in OPEN mode. The reason is because while the database is in MOUNTED state, only the controlfile is read, hence, you can only modify the physical structure but not the logical structure. (link1, link2)

Regards,
Wei Shan

Advertisements

One thought on “Oracle 11gR2 RMAN – Recovering of single datafile (ORA-01109)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s