[Read-Only tablespace -- restore-1], read-only table --- 1
1> Read-Only --- backup read-only fault read-only restoration of faulty data files
2> Read-Only --- backup read-only writable fault recovery backup data files, recover
3> Read-Only --- backup writable read-only fault restore backup data file, recover
1>
Go to rman to ensure that there are backup files:
[Oracle @ oracle ~] $ Rman target/
Recovery Manager: Release 10.2.0.5.0-Production on Thu Nov 6 09:44:08 2014
Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connected to target database: JADL10G (DBID = 2011508104)
RMAN> list backup of tablespace users;
Using target database control file instead of recovery catalog
List of Backup Sets
==============================
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------------------------------------------------------------
1 Full 609.23 m disk 00:00:38 06-NOV-14
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141106T063059
Piece Name:/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T063059_b5o994hx _. bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---------------------------------
4 Full 422818 06-NOV-14/u01/oracle/oradata/jadl10g/users01.dbf
Change the tablespace to a read-only tablespace:
[Oracle @ oracle ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Thu Nov 6 09:45:35 2014
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
SQL> alter tablespace users read only;
Tablespace altered.
Log on to rman and back up the database again. If you delete a backup, you can use the delete backup command to delete it;
[Oracle @ oracle ~] $ Rman target/
Recovery Manager: Release 10.2.0.5.0-Production on Thu Nov 6 09:50:29 2014
Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connected to target database: JADL10G (DBID = 2011508104)
RMAN> backup database; -- backup database
Starting backup at 06-NOV-14
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 159 devtype = DISK
Channel ORA_DISK_1: starting full datafile backupset
Channel ORA_DISK_1: specifying datafile (s) in backupset
Input datafile fno = 00001 name =/u01/oracle/oradata/jadl10g/system01.dbf
Input datafile fno = 00003 name =/u01/oracle/oradata/jadl10g/sysaux01.dbf
Input datafile fno = 00005 name =/u01/oracle/oradata/jadl10g/example01.dbf
Input datafile fno = 00002 name =/u01/oracle/oradata/jadl10g/undotbs01.dbf
Input datafile fno = 00004 name =/u01/oracle/oradata/jadl10g/users01.dbf
Channel ORA_DISK_1: starting piece 1 at 06-NOV-14
Channel ORA_DISK_1: finished piece 1 at 06-NOV-14
Piece handle =/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/empty _. bkp tag = TAG20141106T095036 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Channel ORA_DISK_1: starting full datafile backupset
Channel ORA_DISK_1: specifying datafile (s) in backupset
Including current control file in backupset
Including current SPFILE in backupset
Channel ORA_DISK_1: starting piece 1 at 06-NOV-14
Channel ORA_DISK_1: finished piece 1 at 06-NOV-14
Piece handle =/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/empty _. bkp tag = TAG20141106T095036 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 06-NOV-14
RMAN> exit
Recovery Manager complete.
Delete users tablespace (read-only files)
[Oracle @ oracle ~] $ Rm/u01/oracle/oradata/jadl10g/users01.dbf
Connect to the database and run the query. An error is returned:
[Oracle @ oracle ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Thu Nov 6 09:52:33 2014
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
SQL> select * from scott. dept;
Select * from scott. dept
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oracle/oradata/jadl10g/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
Connect to rman:
[Oracle @ oracle ~] $ Rman target/
Recovery Manager: Release 10.2.0.5.0-Production on Thu Nov 6 09:53:40 2014
Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connected to target database: JADL10G (DBID = 2011508104)
RMAN> SQL 'alter database datafile 4 offline'; -- data file offline
Using target database control file instead of recovery catalog
SQL statement: alter database datafile 4 offline
RMAN> restore datafile 4; -- restore the data file. The file path can be restore datafile '/u01/oracle/oradata/jadl10g/users01.dbf ';
Starting restore at 06-NOV-14
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 136 devtype = DISK
Channel ORA_DISK_1: starting datafile backupset restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Restoring datafile 00004 to/u01/oracle/oradata/jadl10g/users01.dbf
Channel ORA_DISK_1: reading from backup piece/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1 _. bkp
Channel ORA_DISK_1: restored backup piece 1
Piece handle =/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/empty _. bkp tag = TAG20141106T095036
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 06-NOV-14
RMAN> SQL 'alter database datafile 4 online'; --- connect a data file
SQL statement: alter database datafile 4 online
RMAN> exit
Recovery Manager complete.
Connect to the database and run the query to find the result:
[Oracle @ oracle ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Thu Nov 6 09:55:00 2014
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
SQL> select * from scott. dept;
DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2>
Connect to the database and change the tablespace to writable:
[Oracle @ oracle ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Thu Nov 6 10:04:27 2014
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
SQL> alter tablespace users read write;
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
Delete the users tablespace (writable tablespace ):
[Oracle @ oracle ~] $ Rm/u01/oracle/oradata/jadl10g/users01.dbf
Connect to the database and run the query. An error is returned:
[Oracle @ oracle ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Thu Nov 6 10:05:32 2014
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
SQL> select * from scott. dept;
Select * from scott. dept
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oracle/oradata/jadl10g/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
Connect to rman:
[Oracle @ oracle ~] $ Rman target/
Recovery Manager: Release 10.2.0.5.0-Production on Thu Nov 6 10:06:31 2014
Copyright (c) 1982,200 7, Oracle. All rights reserved.
Connected to target database: JADL10G (DBID = 2011508104)
RMAN> SQL 'alter database datafile 4 offline ';
Using target database control file instead of recovery catalog
SQL statement: alter database datafile 4 offline
RMAN> restore datafile 4;
Starting restore at 06-NOV-14
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 159 devtype = DISK
Channel ORA_DISK_1: starting datafile backupset restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Restoring datafile 00004 to/u01/oracle/oradata/jadl10g/users01.dbf
Channel ORA_DISK_1: reading from backup piece/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1 _. bkp
Channel ORA_DISK_1: restored backup piece 1
Piece handle =/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/empty _. bkp tag = TAG20141106T095036
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-NOV-14
RMAN> recover datafile 4;
Starting recover at 06-NOV-14
Using channel ORA_DISK_1
Starting media recovery
Media recovery complete, elapsed time: 00:00:03
Finished recover at 06-NOV-14
RMAN> SQL 'alter database datafile 4 online ';
SQL statement: alter database datafile 4 online
RMAN> exit
Recovery Manager complete.
Connect to the database and run the query to find the result:
[Oracle @ oracle ~] $ Sqlplus/as sysdba
SQL * Plus: Release 10.2.0.5.0-Production on Thu Nov 6 10:08:18 2014
Copyright (c) 1982,201 0, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
And Real Application Testing options
SQL> select * from scott. dept;
DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON