【唯讀資料表空間--恢複-1】，唯讀表---1

【唯讀資料表空間--恢複-1】，唯讀表---1
1>唯讀 ---備份唯讀  故障 唯讀  還原有故障的資料檔案
2>唯讀 ---備份唯讀  可寫 故障  還原備份資料檔案，重做recover
3>唯讀 ---備份可寫  唯讀 故障  還原備份資料檔案，recover
1>
進入rman，確保是否有備份的檔案：
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 09:44:08 2014
Copyright (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 catalog
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    609.23M    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
修改資料表空間為唯讀資料表空間：
[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, 2010, Oracle.  All Rights Reserved.
Connected to:
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.
進入rman，重新備份資料庫，刪除以前備份的可以節省空間的使用delete backup命令可以刪除；
[oracle@oracle ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 09:50:29 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: JADL10G (DBID=2011508104)


RMAN> 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/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.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/o1_mf_ncsnf_TAG20141106T095036_b5oo15kf_.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.
刪除users資料表空間(唯讀檔案)
[oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/users01.dbf
串連到DB，執行查詢發現報錯：
[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, 2010, Oracle.  All Rights Reserved.
Connected to:
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
串連到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, 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 catalog
sql statement: alter database datafile 4 offline


RMAN> restore datafile 4; --恢複資料檔案，此處可以是檔案的路徑 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/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.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 '; ---串連資料檔案


sql statement: alter database datafile 4 online


RMAN> exit
Recovery Manager complete.
串連到DB，執行查詢發現可以查詢到結果：
[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, 2010, Oracle.  All Rights Reserved.
Connected to:
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>
串連DB，修改資料表空間為可寫：
[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, 2010, Oracle.  All Rights Reserved.
Connected to:
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
刪除users資料表空間(可寫的資料表空間)：
[oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/users01.dbf
串連到DB，執行查詢發現報錯：
[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, 2010, Oracle.  All Rights Reserved.
Connected to:
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
串連到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, 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 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/o1_mf_nnndf_TAG20141106T095036_b5onzdz1_.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.
串連到DB，執行查詢發現可以查詢到結果：
[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, 2010, Oracle.  All Rights Reserved.
Connected to:
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