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.dbfThe 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 TAG20120902T221123I 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.dbfNow 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.bckIf 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 bytesIssue 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:04If 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 openedNow 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.dbfStart 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 bytesNow 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:27Open your database
RMAN> alter database open; database openedRMAN 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