Re: Datafile in RECOVER online_status

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Sun, 26 Jul 2015 12:04:23 -0300
Message-ID: <CAJdDhaNtyDo1ynnxnBVCa6F8gQqz6CWV7gvfyBMh0Rippd9NRQ_at_mail.gmail.com>



First off all, thanks everybody that helped on this solution.

Let me explain the problem from begin:

*THE ISSUE :* My purpose was release disk space.
1.) I had a tablespace with nameTBS_IDX (only indexes). It has 3 datafiles with file_id : 12, 13 and 14 with 32gb each one. 2.) I query the dba_extents looking for all objects where file_id = 14 I run the query below in order to identify all objets in the file_id = 14

            select distinct owner, segment_name, file_id from dba_extents
             where file_id = 14
              and segment_name NOT LIKE 'MDRT_%'
              and segment_name NOT LIKE 'MLOG$_%'
                order by 1,2

3.) I created a new tablespace called TBS_BKP, then I moved all objects (in the list I got in the item 2) to TBS_BKP. After moving objects, the datafile 14 got empty. 4.) I did the following command:
ALTER DATABASE DATAFILE 14 OFFLINE DROP; alter tablespace TBS_IDX drop datafile 14; At this moment the tablespace TBS_IDX was with 2 datafiles (12 and 13 only)

5.) I created a new datafile in the TBS_IDX with 5m only, using the same OS file name, that were dropped in the item 4. "Here is my mistake". The correct shoud resize the datafile 14 to 5m and finish.

So this is my cenary, now.
Now a have a TBS_IDX with 3 datafiles again (12, 13 and 14).

I run this query:
SQL> select distinct owner, segment_name, file_id from dba_extents
  2 where file_id = 14
  3 /

OWNER                          SEGMENT_NAME
                                       FILE_ID
------------------------------
--------------------------------------------------------------------------------


 SQL> There is no objects allocated in the datafile with file-id = 14. The datafile that I dropped and re-created.

But, I don´t know the reason, it is like this: Asking for recover.

SQL> select FILE_ID, STATUS, ONLINE_STATUS from dba_data_files where online_status = 'RECOVER';

   FILE_ID STATUS         ONLINE_STATUS
---------- ---------           -------------
        14    AVAILABLE   RECOVER

6.) I tried it, with no sucesses:
SQL> recover datafile 14;
ORA-00279: change 22509044175 generated at 07/17/2015 07:03:18 needed for thread 1
ORA-00289: suggestion :
/l/disk0/app/oracle/product/11.2.0/db_1/dbs/arch1_125326_819018967.dbf ORA-00280: change 22509044175 for thread 1 is in sequence #125326

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
---> I entered <ENTER> for <RET>

ORA-00308: cannot open archived log
'/l/disk0/app/oracle/product/11.2.0/db_1/dbs/arch1_125326_819018967.dbf' ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory Additional information: 3

7.) I know that I don´t have objects on this datafile 14. Look the query result from dba_extents above.
If the datafile 14 became ONLINE with 5m or the datafile 14 be erased (from tablespace and from disk) , it solves my issue.

So I tougth in move all data from TBS_IDX (doing rebuild for all indexes) to anoter TBS_X, drop TBS_IDX with datafiles and contents, and then re-create TBS_IDX again and return data from TBS_X to TBS_IDX (rebuilding all indexes).

*THE SOLUTION* 1.) I used these scripts to drop and move indexes from TBS_IDX to TBS_DAT set serveroutput on
set pages 0
set lines 120
spool rebuild_normal.txt
DECLARE
BEGIN
  FOR L IN (

            SELECT DISTINCT OWNER
              FROM DBA_INDEXES
             WHERE INDEX_TYPE != 'LOB'
               AND TABLESPACE_NAME = 'TBS_IDX'
            )
   LOOP
      DBMS_OUTPUT.PUT_LINE ('/* OWNER = ' || L.OWNER || ' */');
      FOR O IN (SELECT INDEX_NAME
                  FROM DBA_INDEXES
                 WHERE OWNER = L.OWNER
                  AND INDEX_TYPE != 'LOB'
                  AND TABLESPACE_NAME = 'TBS_IDX')
          LOOP
            DBMS_OUTPUT.PUT_LINE ('ALTER INDEX ' || L.OWNER || '.' ||
O.INDEX_NAME || ' REBUILD TABLESPACE TBS_DAT;');
          END LOOP;

   END LOOP;
END;
/
spool off

set serveroutput on
set pages 0
set lines 100
spool drop_spatial.txt
DECLARE
BEGIN
  FOR L IN (

            SELECT DISTINCT OWNER
              FROM DBA_INDEXES
             WHERE INDEX_TYPE = 'DOMAIN'
            )
   LOOP
      DBMS_OUTPUT.PUT_LINE ('/* OWNER = ' || L.OWNER || ' */');
      FOR O IN (
                SELECT INDEX_NAME
                  FROM DBA_INDEXES
                 WHERE OWNER = L.OWNER
                  AND INDEX_TYPE = 'DOMAIN'
               )
          LOOP
            DBMS_OUTPUT.PUT_LINE ('DROP INDEX ' || L.OWNER || '.' ||
O.INDEX_NAME || ';');
          END LOOP;

   END LOOP;
END;
/
spool off

set serveroutput on
set pages 0
set lines 180
spool create_spatial.txt
DECLARE
BEGIN
  DBMS_OUTPUT.ENABLE(NULL);
  FOR L IN (

            SELECT DISTINCT OWNER
              FROM DBA_INDEXES
             WHERE INDEX_TYPE = 'DOMAIN'
            )
   LOOP
      DBMS_OUTPUT.PUT_LINE ('/* OWNER = ' || L.OWNER || ' */');
      FOR O IN (
                SELECT INDEX_NAME,
                       TABLE_NAME,
                       DECODE(PARAMETERS, NULL, NULL , UPPER(PARAMETERS))
PARAMETERS
                  FROM DBA_INDEXES
                 WHERE OWNER = L.OWNER
                  AND INDEX_TYPE = 'DOMAIN'
               )
          LOOP
            DBMS_OUTPUT.PUT_LINE ('CREATE INDEX ' || L.OWNER || '.' ||
O.INDEX_NAME || ' ON ' || L.OWNER || '.' || O.TABLE_NAME
                                   || '(GEOMETRY) INDEXTYPE IS
MDSYS.SPATIAL_INDEX PARAMETERS('
                                   || CHR(39) || O.PARAMETERS || CHR(39) ||
');');
          END LOOP;

   END LOOP;
END;
/
spool off

2.) I drop tablespace TBS_IDX

drop tablespace TBS_IDX INCLUDING CONTENTS AND DATAFILES;

3.) I create tablespace TBS_IDX again

CREATE TABLESPACE TBS_IDX
DATAFILE '<disk_datafile_name>/tbs_idx_001.dbf' SIZE 30G,

               '<disk_datafile_name>/tbs_idx_002.dbf' SIZE 30G AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO; 4.) And moved all indexes again from TBS_DAT to TBS_IDX

using the rebuild for all indexes from TBS_DAT to TBS_IDX.

And got success.

Best regards
Eriovaldo

2015-07-25 21:56 GMT-03:00 MacGregor, Ian A. <ian_at_slac.stanford.edu>:

>  I think you can ALSO drop an empty data file from tablespace which has
> multiple datafilee unless that data file is the original datafile  of that
> tablespace which again has multiple data files.
>
>  Ian MacGregor
> SLAC National Accelerator  Laboratory
>
>  On Jul 25, 2015, at 10:01 AM, Ls Cheng <exriscer_at_gmail.com> wrote:
>
>   hi
>
>  you cannot drop a datafile from a tablespace (unless it is the only
> datafile of course), you must restore a backup and recover it
>
>  thanks
>
>
> On Sat, Jul 25, 2015 at 6:46 PM, Eriovaldo Andrietta <
> ecandrietta_at_gmail.com> wrote:
>
>>  Hello,
>>
>>  I have a datafile that is in this situation:
>>
>>  SQL> select FILE_ID, STATUS, ONLINE_STATUS from dba_data_files where
>> online_status = 'RECOVER';
>>
>>    FILE_ID STATUS    ONLINE_STATUS
>> ---------- --------- -------------
>>         14 AVAILABLE RECOVER
>>
>>  1.) Can I do it ? Is there any impact ?
>> alter tablespace TBS drop datafile 14;
>>
>>  or
>>
>>  2.) Must I do it:
>> alter database datafile 14 offline
>> and them via O/S command : delete the file
>>
>>  Regards
>>  Eriovaldo
>>
>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jul 26 2015 - 17:04:23 CEST

Original text of this message