Being an Oracle DBA we are aware that there are many types of backups are available for Oracle database maintenance in the same way we have multiple ways to restore our database.
One question raises here that why do we need have a different kind of ways to backup and recovery in Oracle database?
Answer of this question is that we can find a different kind of business with different requirements of business operation. On daily basis, if we have a full backup which takes hours which is not useful for an organization .
In normal life we do not have the same kind of problems and solutions, in the same manner, we can not apply only one way of backup and recovery for a different kind of conditions occurs in Oracle database.
We have multiple conditions with business requirements.Today we are going to have a look on recovery where we well know about How to recover from a loss of a read-only tablespace.
We want to test how to restore and recover database from a loss of a read-only tablespace. We can anticipate there is a new feature introduced since Oracle Database 11gR1 that inverted the logic used until 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 we 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 we 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 we 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.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
