Monday, April 09, 2012

How to Recreate a Temporary Tablespace

N.B. Since I first wrote this I have found out that one of steps is unnecessary.
Please see How to Create a New Temporary Tablespace for details.

We had an issue with a production database, which caused it to output several ORA-07445 and ORA-00600 messages to the alert log e.g.
 
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+980] [SIGBUS] [Invalid address alignment] [0xB38F000000000F] [] []
ORA-07445: exception encountered: core dump [kcbzdh()+784] [SIGSEGV] [Address not mapped to object] [0x4B454C544F534718] [] []
ORA-07445: exception encountered: core dump [strlen()+80] [SIGSEGV] [Address not mapped to object] [0x4442576E20636B70] [] []
ORA-00600: internal error code, arguments: [kcbzfb_4], [0x38FFF40B8], [0], [0x3A658E3C0], [0x3A658E3B0], [0x3A658E3C0], [], []
 
It also produced a number of large trace files and became very slow. Eventually when it showed no sign of improvement, it had to be closed using SHUTDOWN ABORT. When the database was reopened, it appeared to be OK. I raised a Service Request and Oracle diagnosed corruption in one of the temporary tablespace datafiles. They suggested I should recreate the tablespace, which I did. I have reproduced the procedure I followed using an Oracle 10 database on my home PC. The name of the tablespace was as follows:

SQL> select tablespace_name
  2  from dba_tablespaces
  3  where contents = 'TEMPORARY'
  4  /

TABLESPACE_NAME
------------------------------
TEMP

SQL>

It contained the following temporary files:

SQL> select file_name, bytes
  2  from dba_temp_files
  3  where tablespace_name = 'TEMP'
  4  /

FILE_NAME                           BYTES
------------------------------ ----------
/home/oracle/DB1/temp01.dbf      20971520

SQL>

I created a new temporary tablespace:

SQL> l
  1  create temporary tablespace andrew
  2  tempfile '/home/oracle/DB1/andrew.dbf'
  3* size 20m
SQL> /

Tablespace created.

SQL>

... and made it the default temporary tablespace:

SQL> alter database
  2  default temporary tablespace andrew
  3  /

Database altered.

SQL>

Then I wrote some Meta SQL to make all the users use this new temporary tablespace:

set feedback off
set pages 0
spool use_new_temp_ts.sql
select 'alter user '||username||
' temporary tablespace andrew;'
from dba_users
/
spool off

When I ran it, it produced a file with a piece of SQL for each user like this:

alter user SCOTT temporary tablespace andrew;

And, after running this file, all the users had the new default temporary tablespace:

SQL> l
  1  select temporary_tablespace, count(*)
  2  from dba_users
  3* group by temporary_tablespace
SQL> /

TEMPORARY_TABLESPACE             COUNT(*)
------------------------------ ----------
ANDREW                                 21
 

SQL>

Then I dropped the old temporary tablespace:

SQL> drop tablespace temp
  2  /
SQL>

... and removed its datafile:

[oracle@localhost DB1]$ pwd
/home/oracle/DB1
[oracle@localhost DB1]$ rm temp01.dbf
[oracle@localhost DB1]$

I recreated the original temporary tablespace:

SQL> create temporary tablespace temp
  2  tempfile '/home/oracle/DB1/temp01.dbf'
  3  size 20m
  4  /

Tablespace created.

SQL>

I made this the default temporary tablespace again:

SQL> alter database
  2  default temporary tablespace temp
  3  /

Database altered.

SQL>

Then using Meta SQL as above, I made this the temporary tablespace for all users (you don't need to see this again):

SQL> select distinct temporary_tablespace
  2  from dba_users
  3  /

TEMPORARY_TABLESPACE
------------------------------
TEMP

SQL>

I dropped tablespace ANDREW:

SQL> drop tablespace andrew
  2  /

Tablespace dropped.

SQL>

... and removed its datafile:

[oracle@localhost DB1]$ pwd
/home/oracle/DB1
[oracle@localhost DB1]$ rm andrew.dbf
[oracle@localhost DB1]$

I checked that the recreated temporary tablespace was working OK as follows:

SQL> l
  1  alter database tempfile
  2  '/home/oracle/DB1/temp01.dbf'
  3* autoextend on
SQL> /

Database altered.

SQL> select a.tablespace_name, b.tablespace_name
  2  from dba_tables a, dba_tables b
  3  order by 1
  4  /


TABLESPACE_NAME      TABLESPACE_NAME
-------------------- --------------------
SYSAUX               SYSTEM
SYSAUX               SYSTEM
SYSAUX               SYSTEM
SYSAUX               SYSTEM
SYSAUX               SYSTEM
SYSAUX               SYSTEM
SYSAUX               SYSTEM
SYSAUX               SYSTEM
etc

No comments: