Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:oracle_12c_local_temp_tablespace

Ab Oracle 12c User Eigenschaft "Local Temp_Tablespace" - Upgade Fehler "local_temp_tablespace=SYSTEM" - Import Fehler ORA-12911: permanent tablespace cannot be temporary tablespace

Bei einer Migration auf Oracle 19c von einer 12c Datenbank per Import ⇒ Export ist der Folgende Fehler aufgetreten:

ORA-12911: permanent tablespace cannot be temporary tablespace 

Die Ursache dahinter liegt aber viel tiefer in der Upgarde Historie der 12c Datenbank!

Diese Datenbank wird seit Jahren migriert, zuletzt von 11g nach 12c R2.

Bei einer der 12c Migrationen ist die User Eigenschaft „Local Temp_Tablespace“ auf SYSTEM gesetzt worden.

Siehe dazu auch ⇒ 12.2 Database Upgrade Has Marked SYSTEM TABLESPACE As LOCAL_TEMP_TABLESPACE For Few Database Users (Doc ID 2385430.1), der interne Bug dazu ist wohl „BUG 23715518 – APPSST12201::SYSTEM TABLESPACE IS MARKED AS LOCAL_TEMP_TABLESPACE AFTER UPGRADE and that it will be fixed in Oracle Database 18c.] “

D.h. als einer der vorzubereitenden Maßnahmen bei einer aktuellen 19c Migration ist es zu nun, zu testen ob diese Eigenschaft korrekt gesetzt ist!

Bei in 12c neu angelegten Usern ist der eingestellte Default Wert für diese Eigenschaft wohl meist TEMP.


Überprüfen und Anpassen

Abfrage der aktuelle DB User:

SELECT COUNT(username),local_temp_tablespace
  FROM dba_users 
GROUP BY   local_temp_tablespace;
 
COUNT(USERNAME) LOCAL_TEMP_TABLESPACE
--------------- ----------------------
             42 SYSTEM
             .....            

„SYSTEM“ sollte nun bei möglichst keinem User vorkommen.

Anpassen mit „alter user XXXXX LOCAL TEMPORARY TABLESPACE TEMP“ pro User.

Skript für alle User erstellen:

SELECT 'alter user '||username||' LOCAL TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';' 
   FROM dba_users 
  WHERE username NOT IN ('XS$NULL')
    AND local_temp_tablespace = 'SYSTEM';
 
 

Default Tablespace und Default Wert für den LOCAL TEMPORARY TABLESPACE anlegen/setzen

Akutelle Property der DB abfragen:

SELECT PROPERTY_NAME,PROPERTY_VALUE 
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DEFAULT%SPACE';
 
PROPERTY_NAME                  PROPERTY_VALUE 
-----------------------        ---------------
DEFAULT_TEMP_TABLESPACE	       TEMP
DEFAULT_PERMANENT_TABLESPACE   USER

LOCAL TEMPORARY TABLESPACE setzen mit:

ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE TEMP;

Fehler: „ORA-32788: Operation is not allowed due to invalid or mismatched tablespace type.“

ORA-32788: Operation IS NOT allowed due TO invalid OR mismatched tablespace TYPE.
Cause: LOCAL TEMPORARY tablespace could NOT be used AS the DEFAULT TEMPORARY tablespace; TEMPORARY tablespace could NOT be used AS the DEFAULT LOCAL TEMPORARY tablespace.
 
Action: CHANGE the tablespace TYPE AND USE the valid tablespace.

Lösung:

(getestet in 19c)

Das muss nun ein ganz besonderer Temp Tablespace (Big File Type! ) sein, mit der Option „ FOR ALL “ oder „ FOR LEAF “ angelegt. Dieser Tablespace kann dann lokal auf jeden Cluster Knoten liegen und wird nicht zwischen den Instanzen geshared.

Lokalen Temp Tablespace für jeden Cluster Knoten / Jede Instance anlegen:

# wo liegen meine Datendateien
SELECT TABLESPACE_NAME,file_name FROM dba_temp_files;
 
-- ==================
-- anlegen eines Big File temporary Tablespace  mit der "for all" Klausel für alle Instancen:
 
CREATE LOCAL TEMPORARY tablespace 
  FOR ALL 
  TMP_LOCAL 
  tempfile 'C:\ORACLE\ORADATA\GPI\TEMP03.DBF' SIZE 10M;
 
Tablespace created.
 
-- ===================
-- Propertiy abfragen
 
SELECT PROPERTY_NAME,PROPERTY_VALUE 
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DEFAULT%SPACE';
 
..
DEFAULT_LOCAL_TEMP_TABLESPACE  TMP_LOCAL
..

Zuordnen:

ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE TEMP_LOKAL;
 
DATABASE altered.
Nach dem Anlegen und Zuordnen eines LOCAL TEMPORARY TABLESPACE kann dieser nur ersetzt aber nicht mehr die Einstellung komplett gelöscht werden!

Bedeutung diese Parameters und seine Auswirkungen

Ein „local temporary tablespace“ ist ein eigener Tablespace der je einer Instance zugeordnet ist.

Anlegen: Aus der Doku:

..Creates separate temporary tablespaces for every database instance. The FOR LEAF option creates tablespaces only for read-only instances. The FOR ALL option creates tablespaces for all instances, both read-only and read/write. …

Wie erkennen, Spalte „SHARED“ beachten!:

-- Tablespace
 
SELECT tablespace_name,contents,bigfile,shared  
  FROM dba_tablespaces 
 ORDER BY tablespace_name;
 
 
Tablespace
Name          CONTENTS  BIGFILE     SHARED
------------ ---------- ---------   ---------
 
......
 
TMP_LOCAL  TEMPORARY     YES        LOCAL_ON_ALL
 
......
TEMP      TEMPORARY      NO         SHARED
 
...
 
-- Datendateien
 
SELECT inst_id,file_id,file_name,SHARED FROM dba_temp_files;
 
    INST_ID      FILE_ID FILE_NAME                                SHARED
----------- ------------ ---------------------------------------- ----------------
                       3 C:\ORACLE\ORADATA\GPI\TEMP02.DBF         SHARED
          1            4 C:\ORACLE\ORADATA\GPI\TEMP03.DBF_1       LOCAL_ON_ALL
 
 
--BigFile Tablespace Nummeriert durch! daher TEMP03.DBF_1

Wie wirkt sich die Fehlerhafte Einstellung mit "SYSTEM aus?

aus der Doku ⇒ https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-5387D7B2-C0CA-4C1E-811B-C7EB9B636442:

Access to Temporary Storage

If a user has a temporary tablespace assigned, then the database accesses it first; otherwise, the database accesses the default temporary tablespace. After the database accesses a temporary tablespace for a query, it does not switch to a different one.

A user query can access either shared or local temporary storage. Furthermore, a user could have one default local temporary tablespace assigned for read-only instances, and a different default local temporary tablespace assigned for read/write instances.

For read/write instances, the database gives higher priority to shared temporary tablespaces. For read-only instances, the database gives higher priority to local temporary tablespaces. If the database instance is read/write, then the database searches for space in the following order:

    Is a shared temporary tablespace assigned to the user?

    Is a local temporary tablespace assigned to the user?

    Does the database default temporary tablespace have space?

If the answer to any preceding question is yes, then the database stops the search and allocates space from the specified tablespace; otherwise, space is allocated from the database default local temporary tablespace.

If the database instance is read-only, then the database searches for space in the following order:

    Is a local temporary tablespace assigned to the user?

    Does the database default local temporary tablespace assigned have space?

    Is a shared temporary tablespace assigned to the user?

If the answer to any preceding questions is yes, then the database stops the search and allocates space from the specified tablespace; otherwise, space is allocated from the database default shared temporary tablespace.
...

D.h. da ja der Shared Tempory Tablespace bei den Usern korrekt auf TEMP steht, und diese Einstellung als erstes verwendet wird, fällt in der Praxis das wohl nur bei Read Only Instancen auf.

Die Frage ist nun ob das dann im normalen Betrieb in einem Default Cluster ohne Flex Architekter etc. und einer Read Only Instancen überhaupt genützt wird, wohl eher nicht.

Im Betrieb sollte es also zu keinen Auswirkungen kommen, nur beim nächsten Full Import wird eines Exports aus so einer Datenbank wird einen Fehler enthalten!



Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Diese Website verwendet Cookies. Mit der Nutzung der Website erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Computer gespeichert werden. Außerdem bestätigen Sie, dass Sie unsere Datenschutzerklärung gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website. Weitere Information
"Autor: Gunther Pipperr"
dba/oracle_12c_local_temp_tablespace.txt · Zuletzt geändert: 2021/03/15 18:32 von gpipperr