ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 加快data_tablespace的恢复

加快data_tablespace的恢复

原创 Linux操作系统 作者:oracleclub 时间:2011-12-02 08:36:42 0 删除 编辑
实验思路:现备份数据表空间,模拟数据文件的丢失,然后startup-->offline datafile --->rename--->recover

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME from dba_data_files;

   FILE_ID FILE_NAME            TABLESPACE_NAME
---------- --------------------------------------------------         4 /u01/app/oracle/oradata/orcl/users01.dbf           USERS
         3 /u01/app/oracle/oradata/orcl/sysaux01.dbf          SYSAUX
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1
         1 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM
         5 /u01/app/oracle/oradata/orcl/example01.dbf         EXAMPLE
         6 /u01/disk1/test_undo.dbf                           TEST_UNDO
         7 /u01/disk1/test_index.dbf                          TEST_INDEX

7 rows selected.

SQL> alter tablespace USERS begin backup;

Tablespace altered.

SQL> ho cp  /u01/app/oracle/oradata/orcl/users01.dbf /u01/disk1/

SQL> alter tablespace USERS end backup;

Tablespace altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ho rm -rf /u01/app/oracle/oradata/orcl/users01.dbf

SQL> ho ls /u01/app/oracle/oradata/orcl/users01.dbf
ls: /u01/app/oracle/oradata/orcl/users01.dbf: No such file or directory
模拟丢失

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'


SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> select FILE#,STATUS from v$datafile;——说明v$是动态的,所以比较真实

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 OFFLINE——真的
         5 ONLINE
         6 ONLINE
         7 ONLINE

7 rows selected.

SQL> select tablespace_name,status from dba_tablespaces;——这个dba_tablespaces是通过oracle晚上采集得到的
                                                          所以不是最真实的数据
TABLESPACE_NAME STATUS                                    
--------------- ---------
SYSTEM          ONLINE
UNDOTBS1        ONLINE
SYSAUX          ONLINE
TEMP            ONLINE
USERS           ONLINE——还显示online,因为oracle的统计信息还未收集
EXAMPLE         ONLINE
TEST_UNDO       ONLINE
TEST_INDEX      ONLINE

8 rows selected.


SQL> alter tablespace USERS rename ——通过rename 重新指明新路径
  2  datafile '/u01/app/oracle/oradata/orcl/users01.dbf'
  3  to '/u01/disk1//users01.dbf';

Tablespace altered.

SQL> alter database datafile 4 online;——不能直接online的原因是:datafile 的SCN比controlfile的SCN要旧
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/disk1/users01.dbf'


SQL> recover datafile 4;——所以要进行recover读redo日志
Media recovery complete.
SQL> alter database datafile 4 online;

Database altered.

以下是从alert.log日志中截取出来的信息,看出recover时,oracle读redo_log来恢复
Thu Dec  1 10:28:35 2011
Media Recovery Start
Thu Dec  1 10:28:35 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 10 Reading mem 0
  Mem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo03.log
Thu Dec  1 10:28:35 2011
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER  datafile 4  
Thu Dec  1 10:28:41 2011
alter database datafile 4 online
Completed: alter database datafile 4 online


SQL> select FILE#,STATUS from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE

7 rows selected.

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

请登录后发表评论 登录
全部评论

注册时间:2011-11-22

  • 博文量
    61
  • 访问量
    82243