ITPub博客

首页 > Linux操作系统 > Linux操作系统 > db_file之index损坏恢复实验

db_file之index损坏恢复实验

原创 Linux操作系统 作者:yuecaibo 时间:2012-05-06 21:10:42 0 删除 编辑


生产库中索引和表是IO分离存储的.索引不需要备份,而且表的更改 索引也会联动更改,即使备份也毫无意义
即使索引表空间损坏,索引的定义也存储在数据字典中,取出元数据重建即可

如何取元数据
 dbms_metadata.get_ddl('类型','对象','模式')

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   ( "EMPNO" NUMBER(4,0),
 "ENAME" VARCHAR2(10),
 "JOB" VARCHAR2(9),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE,
 "SAL" NUMBER(7,2),
 "COMM" NUMBER(7,2),
 "DEPTNO" NUMBER(2,0),
  CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE,
  CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
   REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

 

SQL>

取scott用户下所有表的元数据
SQL> select (select dbms_metadata.get_ddl('TABLE',table_name,owner) from dual)
 from dba_tables where wner='SCOTT';

取scott用户下所有索引的元数据
SQL> select (select dbms_metadata.get_ddl('INDEX',index_name,owner) from dual)
 from dba_indexes where wner='SCOTT';


SQL> create tablespace ind_tbs datafile '/db254/ind_tbs01.dbf' size 10M;

SQL> create index i1 on scott.emp(ename) tablespace ind_tbs;
SQL> create index i2 on scott.emp(sal) tablespace ind_tbs;
SQL> create index i3 on scott.emp(job) tablespace ind_tbs;


SQL> select segment_type,segment_name,owner from dba_segments where tablespace_name='IND_TBS';

SEGMENT_TYPE    SEGMENT_NAME       OWNER
------------------ ------------------------- ------------------------------
INDEX     I1        SYS
INDEX     I2        SYS
INDEX     I3        SYS


SQL> select * from scott.emp where sal < 1000;

     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80     809      20
      7900 JAMES      CLERK       7698 03-DEC-81     959      30

SQL>
SQL> ! cp 1.txt /db254/ind_tbs01.dbf
 

SQL> alter system switch logfile;

System altered.

SQL> select * from scott.emp where sal <1000;

     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80     809      20
      7900 JAMES      CLERK       7698 03-DEC-81     959      30

SQL> alter system checkpoint;

System altered.

SQL> select * from scott.emp where sal <1000;
select * from scott.emp where sal <1000
                    *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/db254/ind_tbs01.dbf'

6号文件有问题 6号文件在哪个表空间

SQL> select tablespace_name ,file_id from dba_data_files where file_id=6;

TABLESPACE_NAME    FILE_ID
--------------- ----------
IND_TBS    6

存的是什么内容
SQL> select segment_type,segment_name,owner from dba_segments where tablespace_name='IND_TBS';

SEGMENT_TYPE    SEGMENT_NAME       OWNER
------------------ ------------------------- ------------------------------
INDEX     I1        SYS
INDEX     I2        SYS
INDEX     I3        SYS

SQL>
SQL> 获取元数据
SQL> SELECT DBMS_METADATA.GET_DDL(segment_type,segment_name,owner)||';' FROM dba_segments where tablespace_name='IND_TBS';

DBMS_METADATA.GET_DDL(SEGMENT_TYPE,SEGMENT_NAME,OWNER)||';'
--------------------------------------------------------------------------------

  CREATE INDEX "SYS"."I1" ON "SCOTT"."EMP" ("ENAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IND_TBS"
 ;


  CREATE INDEX "SYS"."I2" ON "SCOTT"."EMP" ("SAL")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IND_TBS"
 ;


  CREATE INDEX "SYS"."I3" ON "SCOTT"."EMP" ("JOB")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IND_TBS"
 ;


删除原表空间
SQL> DROP TABLESPACE IND_TBS;
DROP TABLESPACE IND_TBS
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


SQL> DROP TABLESPACE IND_TBS INCLUDING CONTENTS;

Tablespace dropped.


SQL> CREATE TABLESPACE IND_TBS DATAFILE '/db254/ind_tbs01.dbf' size 10M reuse;

Tablespace created.

执行元数据

SQL> select segment_type,segment_name,owner from dba_segments where tablespace_name='IND_TBS';

SEGMENT_TYPE    SEGMENT_NAME       OWNER
------------------ ------------------------- ------------------------------
INDEX     I1        SYS
INDEX     I2        SYS
INDEX     I3        SYS

SQL> select * from scott.emp where sal < 1000;

     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80     809      20
      7900 JAMES      CLERK       7698 03-DEC-81     959      30

SQL>
  

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

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

注册时间:2012-03-03

  • 博文量
    42
  • 访问量
    36364