Re: Datafile in RECOVER online_status
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-lReceived on Sun Jul 26 2015 - 17:04:23 CEST