[Read-Only tablespace -- restore-1]

1 read-only --- backup read-only fault read-only restoration of faulty data files 2 read-only --- backup read-only writable fault restoration of backup data files, redo recover3 read-only --- backup writable read-only fault restoration of backup data files, recover1 enters rman to ensure that there are backup files: [oracle @ oracle ~] $ RmantargetRecoveryMan

1 read-only --- backup read-only fault read-only restoration of faulty data files 2 read-only --- backup read-only writable fault restoration of backup data files, rerecover recover 3 read-only --- backup writable read-only fault restoration of backup data files, recover 1 enters rman to ensure that there are backup files: [oracle @ oracle ~] $ Rman target/Recovery Man

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 2014Copyright (c) 1982, 2007, 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 catalogList of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------1 Full 609.23M DISK 00:00:38 06-NOV-14BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20141106T063059Piece Name: /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T063059_b5o994hx_.bkpList of Datafiles in backup set 1File 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 sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 09:45:35 2014Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engineand Real Application Testing optionsSQL> 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-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid = 159 devtype = DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile (s) in backupsetinput datafile fno = 00001 name =/u01/oracle/oradata/jadl10g/system01.dbfinput datafile fno = 00003 name =/u01/oracle/oradata/jadl10g/export datafile fno = 00005 name = /u01/oracle/oradata/jadl10g/export datafile fno = 00002 name =/u01/oracle/oradata/jadl10g/export datafile fno = 00004 name =/u01/oracle/oradata/jadl10g /users01.dbfchannel ORA_DISK_1: starting piece 1 at 06-NOV-14channel ORA_DISK_1: finished piece 1 at 06-NOV-14piece handle =/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1 _. bkp tag = TAG20141106T095036 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 00: 55 channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile (s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 06-NOV-14channel ORA_DISK_1: finished piece 1 at 06-NOV-14piece handle =/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/assist _. bkp tag = TAG20141106T095036 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 00: 02 Finished backup at 06-NOV-14RMAN> exitRecovery 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 sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 09:52:33 2014Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engineand Real Application Testing optionsSQL> select * from scott.dept;select * from scott.dept*ERROR at line 1:ORA-01116: error in opening database file 4ORA-01110: data file 4: '/u01/oracle/oradata/jadl10g/users01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engineand 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 catalogsql statement: alter database datafile 4 offlineRMAN> restore datafile 4; -- restore the data file, which can be the file path restore datafile '/u01/oracle/oradata/jadl10g/users01.dbf '; starting restore at 06-NOV-14allocated channel: ORA_DISK_1channel ORA_DISK_1: sid = 136 devtype = DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile (s) to restore from backup setrestoring datafile 00004 to/u01/oracle/oradata/jadl10g/users01.dbfchannel ORA_DISK_1: reading from backup piece/u01/oracle/flash_recovery_area/JADL10G/backupset/Snapshot/snapshot _. bkpchannel ORA_DISK_1: restored backup piece 1 piece handle =/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1 _. bkp tag = TAG20141106T095036channel ORA_DISK_1: restore complete, elapsed time: 00: 00: 02 Finished restore at 06-NOV-14RMAN> SQL 'alter database datafile 4 online'; --- connect data file SQL statement: alter database datafile 4 onlineRMAN> exitRecovery Manager complete.

Connect to the database and run the query to find the result:

[oracle@oracle ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 09:55:00 2014Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engineand Real Application Testing optionsSQL> select * from scott.dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON2>

Connect to the database and change the tablespace to writable:

[Oracle @ oracle ~] $ Sqlplus/as sysdbaSQL * 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 to: Oracle Database 10g Enterprise Editio [from the Internet (http://www.68idc.cn)] n Release 10.2.0.5.0-64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, data Mining Scoring Engineand Real Application Testing optionsSQL> alter tablespace users read write; Tablespace altered. SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engineand 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 sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 10:05:32 2014Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engineand Real Application Testing optionsSQL> select * from scott.dept;select * from scott.dept*ERROR at line 1:ORA-01116: error in opening database file 4ORA-01110: data file 4: '/u01/oracle/oradata/jadl10g/users01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engineand 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 2014Copyright (c) 1982, 2007, 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 catalogsql statement: alter database datafile 4 offlineRMAN> restore datafile 4;Starting restore at 06-NOV-14allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00004 to /u01/oracle/oradata/jadl10g/users01.dbfchannel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.bkpchannel ORA_DISK_1: restored backup piece 1piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.bkp tag=TAG20141106T095036channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 06-NOV-14RMAN> recover datafile 4;Starting recover at 06-NOV-14using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:03Finished recover at 06-NOV-14RMAN> sql 'alter database datafile 4 online ';sql statement: alter database datafile 4 onlineRMAN> exitRecovery Manager complete.

Connect to the database and run the query to find the result:

[oracle@oracle ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 10:08:18 2014Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engineand Real Application Testing optionsSQL> select * from scott.dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON