How to recover from a loss of a read-only tablespace

Tweet

This time I want to test how to restore and recover my database from a loss of a read-only tablespace. I can anticipate there is a new feature introduced since Oracle Database 11gR1 that inverted the logic used untill 10gR2. Let's start with the examples. I've created a read-only tablespace named READ_ONLY using the following command:

SQL> CREATE TABLESPACE READ_ONLY DATAFILE '/home/oracle/app/oracle/oradata/orcl/read_only01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 10M;
SQL> ALTER TABLESPACE READ_ONLY READ ONLY;

The "report schema" command shows READ_ONLY tablespace information

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 ***     /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf

The following are my current backup pieces:

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
20      B  A  A DISK        28-08-2012 23:27:18 1       1       YES        TAG20120828T232717
21      B  F  A DISK        28-08-2012 23:31:06 1       1       YES        TAG20120828T232719
22      B  A  A DISK        28-08-2012 23:31:18 1       1       YES        TAG20120828T233118
23      B  F  A DISK        28-08-2012 23:31:20 1       1       NO         TAG20120828T233119
24      B  F  A DISK        29-08-2012 08:25:11 1       1       NO         TAG20120829T082509
25      B  F  A DISK        02-09-2012 21:46:12 1       1       NO         TAG20120902T214611
26      B  F  A DISK        02-09-2012 22:11:24 1       1       NO         TAG20120902T221123

I want to list all the datafiles included in one of my backup, so you can verify I haven't any backup of my read-only tablespace.

RMAN> list backup tag TAG20120828T232719;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
21      Full    759.52M    DISK        00:03:47     28-08-2012 23:31:06
        BP Key: 21   Status: AVAILABLE  Compressed: YES  Tag: TAG20120828T232719
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_08_28/o1_mf_nnndf_TAG20120828T232719_83vfl839_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 13907792   28-08-2012 23:27:19 /home/oracle/app/oracle/oradata/orcl/system01.dbf
  2       Full 13907792   28-08-2012 23:27:19 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 13907792   28-08-2012 23:27:19 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 13907792   28-08-2012 23:27:19 /home/oracle/app/oracle/oradata/orcl/users01.dbf
  5       Full 13907792   28-08-2012 23:27:19 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  6       Full 13907792   28-08-2012 23:27:19 /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

It's confirmed even when you issue the "report need backup" command, showing which datafiles, according to your rman backup policy configuration, need to be backed up. The command says datafile read_only01.dbf belonging to READ_ONLY tablespace must still be backed up.

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
7    0     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf

Now let's simulate a loss of that datafile

[oracle@localhost ~]$ cd /home/oracle/app/oracle/oradata/orcl
[oracle@localhost orcl]$ ll
total 2512876
-rw-rw---- 1 oracle oracle    7348224 Aug 29 07:39 APEX_1930613455248703.dbf
-rw-rw---- 1 oracle oracle    9814016 Sep  2 22:22 control01.ctl
-rw-rw---- 1 oracle oracle   85991424 Aug 29 07:39 example01.dbf
drwxrwxr-x 2 oracle oracle       4096 Jul 27 07:53 non_default_location
-rw-rw---- 1 oracle oracle    1056768 Sep  2 22:03 read_only01.dbf
-rw-rw---- 1 oracle oracle   52429312 Aug 29 07:39 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Aug 29 07:39 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Sep  2 22:22 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Sep  2 22:22 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Sep  2 22:22 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Sep  2 21:56 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Sep  2 22:22 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Aug 29 07:39 users01.dbf

[oracle@localhost orcl]$ mv read_only01.dbf read_only01.dbf.20120902_222234.bck

If i try to start the database some errors are shown:

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             352324280 bytes
Database Buffers           96468992 bytes
Redo Buffers                6008832 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/oracle/app/oracle/oradata/orcl/read_only01.dbf'

Now the first recovery scenario. Let's shutdown the instance

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Connect with RMAN and start the instance in MOUNT mode

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Sep 2 22:31:25 2012

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                352324280 bytes
Database Buffers              96468992 bytes
Redo Buffers                   6008832 bytes

Issue a simply "restore tablespace" command. RMAN will create again the tablespace looking in the redo log.

RMAN> restore tablespace read_only;

Starting restore at 02-09-2012 22:33:02
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=/home/oracle/app/oracle/oradata/orcl/read_only01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 02-09-2012 22:33:04

If I try to open the database it says of course one datafile needs to be recovered.

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/02/2012 22:33:57
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/home/oracle/app/oracle/oradata/orcl/read_only01.dbf'

Issue the recover command so you can be able to open your database.

RMAN> recover tablespace read_only;

Starting recover at 02-09-2012 22:34:35
using channel ORA_DISK_1

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

Finished recover at 02-09-2012 22:34:36

RMAN> alter database open;

database opened

Now have a look at the second recovery scenario. What it really makes the difference is when you have to recover a read-only tablespace after a restore of the entire database. Until a certain release Oracle didn't restore the read-only tablespace: you had to expressly issue the restore command.Let's simulate a lost of all datafiles.

[oracle@localhost orcl]$ rm APEX_1930613455248703.dbf example01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@localhost orcl]$ mv read_only01.dbf read_only01.dbf.20120903_074547.bck
[oracle@localhost orcl]$ ll
total 165544
-rw-rw---- 1 oracle oracle  9814016 Sep  3 07:47 control01.ctl
drwxrwxr-x 2 oracle oracle     4096 Jul 27 07:53 non_default_location
-rw-rw---- 1 oracle oracle  1056768 Sep  2 22:34 read_only01.dbf.20120903_074547.bck
-rw-rw---- 1 oracle oracle 52429312 Sep  3 07:45 redo01.log
-rw-rw---- 1 oracle oracle 52429312 Sep  3 07:45 redo02.log
-rw-rw---- 1 oracle oracle 52429312 Sep  3 07:45 redo03.log
-rw-rw---- 1 oracle oracle 20979712 Sep  2 21:56 temp01.dbf

Start again RMAN and open your instance in MOUNT mode.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Sep 3 07:47:09 2012

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                360712888 bytes
Database Buffers              88080384 bytes
Redo Buffers                   6008832 bytes

Now it's time to restore and recover the entire database

RMAN> restore database;

Starting restore at 03-09-2012 07:48:05
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=/home/oracle/app/oracle/oradata/orcl/read_only01.dbf
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 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_08_28/o1_mf_nnndf_TAG20120828T232719_83vfl839_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_08_28/o1_mf_nnndf_TAG20120828T232719_83vfl839_.bkp tag=TAG20120828T232719
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:15
Finished restore at 03-09-2012 07:52:22

RMAN> recover database;

Starting recover at 03-09-2012 07:52:59
using channel ORA_DISK_1

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

Finished recover at 03-09-2012 07:53:27

Open your database

RMAN> alter database open;

database opened

RMAN is able to gather information about READ_ONLY tablespace

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 ***     /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf

RMAN> 

Connecting to the instance I can verify my tablespace is there and available.

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 3 07:57:44 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from hr.TEST_RO_TABLE;    

         A
----------
         1
         2
         3
         4
         5

As you can see there's no more any difference.
Until 11gr1 by default, the restore command skipped datafiles associated with read-only tablespaces. If you needed read-only tablespaces restored, then you had to use the "check readonly" command or restore each read-only tablespace individually.
Have a look at the restore syntax on "Oracle Database Backup and Recovery Reference 10g Release 2 (10.2)" Part Number B14194-03  and compare it with the syntax used on "Oracle Database Backup and Recovery Reference 11g Release 1 (11.1) Part Number B28273-03" .

In the latter syntax Oracle introduced the SKIP READONLY option, inverting the previous logic: the SKIP READONLY does not restore read-only files.

That's all