10G

Leave a comment Posted on Oracle

ORA-01110: data file 2504 Errors occurred during index rebuild

This morning i came across this :

ORA-01110: data file 2504 Errors occurred during index rebuild

I examined and I followed steps below after that index rebuild was success again.

## first checked for the properties since this is a 10g environment:
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM   database_properties
WHERE  property_name like ‘%TABLESPACE’;SQL> SQL> SQL> SQL> SQL>   2    3  

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
—————————— —————————— ————————————————–
DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   SYSTEM                         Name of default permanent tablespace

##Created a new temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/db/MYDB/temp/temp_99.dbf’ size 1024M;

##Made this the new default temporary tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

## checked if temp tablespace was in use (would have to kill those sessions in case of)
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

##Dropped the old tablespace.
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

## Removed the file on Unix. i do know i might have settled for a reuse, given the fact of possible corrupt thought this was better.
rm temp_01.dbf

## Created  new temp tablespace.
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘/db/MYDB/temp/temp_01.dbf’ size 2000M;

## Removed all temp files on the os
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_02.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_03.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_04.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_05.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_06.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_07.dbf

##  temp files added
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_02.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_03.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_04.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_05.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_06.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_07.dbf’ size 2000m;

## Default tablespace was put back to temp again
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

##  Temp2 tablespace dropped
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

## This tablepace also a temp one had issues in same way:
DROP TABLESPACE tools_temp  INCLUDING CONTENTS AND DATAFILES;

rm  tools_temp_01.dbf

## tools_temp hatte auch macke deshalb die auch raus und rein.
CREATE TEMPORARY TABLESPACE TOOLS_TEMP  TEMPFILE ‘/db/MYDB/temp/tools_temp_01.dbf’ size 1024M;

## All back to normal again .

Happy reading,

Mathijs

Leave a comment Posted on Oracle

Alert log shows Private strand flush not complete

Introduction

Recently came across the following issue that a database is throwing  this message : Private strand flush not complete  during the switch of the redo logfile group. It is interesting to see the various opinions on this matter on the web, and in Mos. That is why i think taking inventory   and a plan might still be useful.

Details:

Database at hand is a 10.2.3.0 EE. on a Solaris box.

Looking under the hood i see:

SQL> select GROUP#,THREAD#,bytes/1024/1024 MB,members, STATUS from v$log order by 2,1 ;
GROUP#    THREAD#         MB    MEMBERS STATUS
 ---------- ---------- ---------- ---------- ----------------
 1          1       2000          2 INACTIVE
 2          1       2000          2 INACTIVE
 3          1       2000          2 CURRENT
 4          1       2000          2 INACTIVE
 5          1       2000          2 INACTIVE

Database is 1.24 TB

and looking at:

show parameter db_writer_processes
NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 db_writer_processes                  integer     2
Switches per hour:
Date            Switches
 ------------- ----------
 2013-03-13 02          8
 2013-03-13 03          7
 2013-03-13 04          3
 2013-03-13 05         13
 2013-03-13 06          9
 2013-03-13 07          2
 2013-03-13 08          2
 2013-03-13 09          9
 2013-03-13 10          1

According to MOS note Alert Log Messages: Private Strand Flush Not Complete [ID 372557.1] i should/could consider increasing the  db_writer_processes  at least from the current 2 to  4..  Which  sounds like a plan 2 Me.

Happy reading.

Mathijs