DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Recovery of Index tablespace

  1. #1
    Join Date
    Jan 2002
    Posts
    40

    Thumbs up Recovery of Index tablespace

    RECOVERY OF DAMAGED/DELETED INDEX DATAFILE

    OPEN SVRMGR30
    CONNECT AS INTERNAL/ORACLE
    >STARTUP MOUNT (HERE SEE THAT THE INIT.ORA FILE POINTS TO THE
    REQUIRED DATABASE)
    >ALTER DATABASE DATAFILE ‘D:\ORANT\DATABASE\INDX2ORDB.ORA’
    OFFLINE DROP; --- INDX2ORDB.ORA IS THE CORRUPT DATA FILE.
    >ALTER DATABASE OPEN;
    THIS WILL OPEN THE DATABASE BUT THEN THE TABLES WHO’S INDEXES
    ARE MAPPED IN THE DAMAGED DATAFILE WILL SHOW ERROR SAYING
    ORA- : CURRENTLY OFFLINE’

    THE ENTIRE INDEX TABLESPACE WILL HAVE TO BE DROPPED AND RECREATED.
    BUT >DROP TABLESPACE INDX INCLUDING CONTENTS; WILL GIVE ERRORS ORA-00604, ORA-02429

    FOLLOWING STEPS HAS TO BE CARRIED OUT:

    (1) INDENTIFY THE USERS WHICH USE THE INDEX TABLESPACE. USING
    STORAGE MANAGER=>INDEXES
    (2) CREATE USERS EXACTLY LIKE THESE USERS USING CREATE LIKE
    OPTION IN SECURITY MANAGER
    (3) EXPORT ENTIRE DATABASE USING EXP80 UTILITY
    (4) DROP THE ORIGINAL USERS USING SECURITY MANAGER
    (5) DROP TABLESPACE INDX INCLUDING CONTENTS
    (6) CREATE THE TABLESPACE IN STORAGE MANAGER
    (7) IMPORT THE USERS FROM THE DMP FILE CREATED IN STEP 3


    I HAVE ONE MORE QUESTION THOUGH, CAN I CONFIGURE THE DATA FILE TO AUTOEXTENT MODE WITHOUT MAKING THE DATAFILE OFFLINE?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Recovery of Index tablespace

    Originally posted by sfdba
    THE ENTIRE INDEX TABLESPACE WILL HAVE TO BE DROPPED AND RECREATED.
    BUT >DROP TABLESPACE INDX INCLUDING CONTENTS; WILL GIVE ERRORS ORA-00604, ORA-02429

    arent you complicating your life...? just disable the primary keys and enable them later

  3. #3
    Join Date
    Jan 2002
    Posts
    40
    ...yeah we tried that initially but then there were too many tables whose primary keys had to be disabled......

    ...is there a way to diable the primary keys of all tables for a given database in a single command?

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by sfdba
    ...is there a way to diable the primary keys of all tables for a given database in a single command?
    All tables? Even SYS' tables :-)

    Here is something that will generate a script for you (for schema SCOTT):

    --
    set serveroutput on
    set verify off
    prompt
    --
    DECLARE
    --
    w_owner varchar2(30);
    w_table varchar2(30);
    w_name varchar2(50);
    sql_text varchar2(256);
    Dynamic_Cursor integer;
    dummy integer;
    table_usage pls_integer;
    --
    cursor trii is
    select
    TABLE_NAME, CONSTRAINT_NAME
    from
    DBA_CONSTRAINTS
    where
    owner = 'SCOTT' and status = 'ENABLED' and constraint_type = 'P'
    order by
    TABLE_NAME desc;
    --
    BEGIN
    --
    dbms_output.enable(60000);
    open trii;
    fetch trii into w_table, w_name;
    while trii%FOUND loop
    begin
    Dynamic_Cursor := dbms_sql.open_cursor;
    sql_text := 'alter table SCOTT.'||w_table||' disable constraint '||w_name||' cascade';
    dbms_sql.parse(Dynamic_Cursor, sql_text, dbms_sql.v7);
    dummy := dbms_sql.execute(Dynamic_Cursor);
    dbms_sql.close_cursor(Dynamic_Cursor);
    exception
    when others then null;
    end;
    fetch trii into w_table, w_name;
    end loop;
    close trii;
    --
    END;
    /
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    You can try this:


    set feedback off
    set heading off
    set echo off
    set pagesize 0
    set trimspool on
    set linesize 132
    spool disable_cons.sql
    select 'alter table '||table_name|| ' disable constraint ' || constraint_name || ' cascade;'
    from dba_constraints
    where owner = 'SCOTT' and status = 'ENABLED' and constraint_type = 'P'
    order by TABLE_NAME desc
    /
    spool off
    set feedback on
    set heading on
    set trimspool off
    @disable_cons.sql


    Hope this helps.
    Nagesh

  6. #6
    Join Date
    Jan 2002
    Posts
    40

    Smile

    THANK TO YOU ALL!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width