Tag: datafile

Database, Errors and Bugs

ORA-19665: size % in file header does not match actual file size of %

 , , ,

That’s an unexpected message to get, right?
I got it related to an ORA-7445: ORA-07445: exception encountered: core dump [kcflfi()+1016] [SIGFPE] [Integer divide by zero] [0x10047EF18] [] []

What’s next?
After some checks, found the following (got from related trace the file name, matching to file_id 106):

SQL> select file_name, bytes from dba_data_files where file_id=106;

FILE_NAME                                         BYTES 
------------------------------------------------ --------------
+DATA/MYDB/DATAFILE/DATAFILE_XX.558.1015447173   14529069056

SQL> select name, bytes from v$datafile where file#= 106;

NAME                                             BYTES 
------------------------------------------------ --------------
+DATA/MYDB/DATAFILE/DATAFILE_XX.558.1015447173   14529067281

This means the Database Dictionary has different sizes for the datafile.
Looking at MOS, it seems to be a match to ORA-07445: Exception Encountered (Doc ID 1958870.1).

How to resolve it?

SQL> alter database datafile 106 offline to drop;
RMAN> restore datafile 106;
RMAN> recover datafile 106;
SQL> alter database datafile 106 online;

This resolved my case, fixing the views.

BE CAREFUL:

  • Make sure you have a backup before dropping the datafile.
  • Make sure you can put the datafile offline or proceed in a non-business hour.
  • Follow change procedures for Production, of course. Things may get wild.

And what if I don’t have a backup?
1. You may want to take it. It may not work, though, considering the original mismatch.
2. Export/Import logically:
– Export the data from the related tablespace (Datapump or Legacy Export, check for limitations and datatypes).
– Drop the tablespace and recreate it.
– Import the data back.

As usual, test it in a non-production environment to validate your plan and commands.

I hope it helps!

Database, RAC | Data Guard

After adding Datafile in Primary the MRP Stopped in Physical Standby (Dataguard)

 , , , , ,

Hi all!
After add a datafile in PRIMARY database, the STANDBY MRP stopped. An “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE” does not solved te problem, as you see:

SQL> SELECT SEQUENCE#, Name, APPLIED FROM V$ARCHIVED_LOG where APPLIED  'YES' and SEQUENCE#  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> SELECT SEQUENCE#, Name, APPLIED FROM V$ARCHIVED_LOG where APPLIED  'YES' and SEQUENCE# < (select max(SEQUENCE#) -1 from V$ARCHIVED_LOG);
SEQUENCE#  NAME                                                                              APPLIED
----------  --------------------------------------------------------------------------------  ---------
15075  /db/u1004/oracle/admin/MYDB/arch/arch_1_823102978_15075.arc                    NO

More“After adding Datafile in Primary the MRP Stopped in Physical Standby (Dataguard)”

ASM, Database, Errors and Bugs

Adding datafile hang on “enq: TT – contention”

 , , ,

Yesterday a colegue asked me about “enq: TT – contention” event on his session that is adding a a datafile in a tablespace wich run out of space in a 11.1.0.7 Database.
I’ve faced this situation another time and decided to document it.

Oracle refer Bug 8332021 : CANNOT ADD A DF WHEN SESSIONS ARE REPORTING ORA-1653 ON 11.1.0.7 for this situation.

The pointing solutions are:
– “Apply Patch 8332021”
– “Alternatively, you can upgrade to 11.2.0.2 or higher as the patch is included in the 11.2.0.2 patch set.”

The not documented workaround (just for you, by Matheus :D) is:
– Cancel session adding datafile.
– Extend any datafile to resume sessions waiting (in resumable state).
– Readd datafile.
This extend action relieves the blocks and will allow you to add the datafile.

Hugs!

Matheus.