=====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 ==== Support: * 12.2 Database Upgrade Has Marked SYSTEM TABLESPACE As LOCAL_TEMP_TABLESPACE For Few Database Users (Doc ID 2385430.1) Doku: * => https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-93848D41-A32B-494F-87A0-A090FF1B2E9A Web: * https://mikedietrichde.com/2018/03/22/upgrade-to-oracle-12-2-0-1-check-your-default-temporary-tablespaces/ * https://dban00b.wordpress.com/2018/03/14/new-local-temporary-tablespace-in-12-2-defaults-to-system-for-some-users-after-upgrade/ Siehe auf jeden Fall, sehr gute Zusammenfassung => * https://www.markusdba.de/2020/03/03/local-temporary-tablespaces-im-rac/