ITPub博客

首页 > 数据库 > Oracle > 只读表空间的恢复

只读表空间的恢复

原创 Oracle 作者:oracle_mao 时间:2013-11-26 13:35:56 0 删除 编辑

 对于只读表空间来说,数据文件头部的scn一直是不变化的,所以对于备份来说,只读表空间只备份一次即可,所以对于只读表空间的恢复也只需要restore即可,而对于读写表空间,就需要restore和recover。
SQL> create table ff1(id int)  tablespace ff;

Table created.

SQL> insert into ff1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ff1;

        ID
----------
         1
SQL> alter tablespace ff read only;

Tablespace altered.

SQL> alter table t1 move tablespace users;

Table altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEMP1                          ONLINE
FF                             READ ONLY

7 rows selected.
RMAN> backup tablespace users format '/home/oracle/usersbak';
RMAN>  backup tablespace ff format '/home/oracle/ffbak';
[oracle@baobao ~]$ rm -rf /u01/app/oracle/oradata/dong/ff.dbf /u01/app/oracle/oradata/dong/users01.dbf
[oracle@baobao ~]$ sqlplus / as sysdba
SQL> select count(*) from tff;
select count(*) from tff
                     *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/dong/ff.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
恢复:
RMAN> restore tablespace users;

Starting restore at 25-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

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 00004 to /u01/app/oracle/oradata/dong/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/usrebak
channel ORA_DISK_1: piece handle=/home/oracle/usrebak tag=TAG20131125T210301
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-NOV-13

RMAN> restore tablespace ff;

Starting restore at 25-NOV-13
using channel ORA_DISK_1

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 00005 to /u01/app/oracle/oradata/dong/ff.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/ffbak
channel ORA_DISK_1: piece handle=/home/oracle/ffbak tag=TAG20131125T210319
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-NOV-13

SQL> /

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/dong/system01.dbf          SYSTEM
/u01/app/oracle/oradata/dong/sysaux01.dbf          ONLINE
/u01/app/oracle/oradata/dong/undotbs01.dbf         ONLINE
/u01/app/oracle/oradata/dong/users01.dbf           RECOVER
/u01/app/oracle/oradata/dong/ff.dbf                ONLINE

SQL> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/dong/users01.dbf'


SQL> select count(*) from tff;

  COUNT(*)
----------
     71892
SQL> col file_name for a50
SQL> /

FILE_NAME                                          STATUS
-------------------------------------------------- ---------
/u01/app/oracle/oradata/dong/users01.dbf           AVAILABLE
/u01/app/oracle/oradata/dong/undotbs01.dbf         AVAILABLE
/u01/app/oracle/oradata/dong/sysaux01.dbf          AVAILABLE
/u01/app/oracle/oradata/dong/system01.dbf          AVAILABLE
/u01/app/oracle/oradata/dong/ff.dbf                AVAILABLE

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEMP1                          ONLINE
FF                             READ ONLY

7 rows selected.

SQL> recover tablespace users;
ORA-00279: change 1297932 generated at 11/25/2013 21:03:01 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/diag/rdbms/dong/1_4_832092572.dbf
ORA-00280: change 1297932 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEMP1                          ONLINE
FF                             READ ONLY

7 rows selected.

SQL> select name,status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/dong/system01.dbf          SYSTEM
/u01/app/oracle/oradata/dong/sysaux01.dbf          ONLINE
/u01/app/oracle/oradata/dong/undotbs01.dbf         ONLINE
/u01/app/oracle/oradata/dong/users01.dbf           OFFLINE
/u01/app/oracle/oradata/dong/ff.dbf                ONLINE

SQL> alter tablespace users online;

Tablespace altered.

SQL> select name,status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/dong/system01.dbf          SYSTEM
/u01/app/oracle/oradata/dong/sysaux01.dbf          ONLINE
/u01/app/oracle/oradata/dong/undotbs01.dbf         ONLINE
/u01/app/oracle/oradata/dong/users01.dbf           ONLINE
/u01/app/oracle/oradata/dong/ff.dbf                ONLINE

SQL> select count(*) from t1;

  COUNT(*)
----------
     71892

SQL> select count(*) from tff;

  COUNT(*)
----------
     71892

SQL>

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/24500180/viewspace-1061115/ ,如需转载,请注明出处,否则将追究法律责任。

上一篇: 索引的nologging
请登录后发表评论 登录
全部评论

注册时间:2011-03-28

  • 博文量
    94
  • 访问量
    787869