nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle eGDB SDE Repo Upgrade mit Oracle Restore Point

MMBR

_________________________________________________________________________________________________

Oracle Enterprise Geodatabase SDE Repository Upgrade mit einem

Oracle Restore Point zum Zurücksetzen der Änderungen im Falle von Problemen.

PDF-Version herunterladen

_________________________________________________________________________________________________

1. ArcGIS Dokumentation - Bevor Sie upgraden

Lesen Sie die ArcGIS-Dokumentation, bevor Sie versuchen, das Geodatabase-Repository zu aktualisieren.

Geodatabase in Oracle upgraden - Bevor Sie upgraden

Lesen Sie die ArcGIS-Anforderungen für Oracle um zu bestätigen, dass Esri die Oracle und ArcGIS Versionskombination unterstützt, die Sie verwenden möchten.

_________________________________________________________________________________________________

2. Oracle Archivierung und Oracle Flashback

a. Oracle Archivierung muss aktiviert sein

b. Oracle Flashback muss aktiviert sein

c. Oracle RMAN inkrementelles Level 0 (Voll-)Backup muss erstellt werden

d. Oracle RMAN inkrementelles Level 1 (inkrementelles) Backup muss erstellt werden

e. Oracle RMAN Archivelog-Backup muss erstellt werden

f. Pluggable Database Logging muss aktiviert sein

sqlplus /nolog
SQL> connect /as sysdba
SQL> ALTER SESSION SET CONTAINER = cdb$root;
SQL> SHOW CON_NAME
*******************************************************************************************
SQL> show parameter db_recovery_file_dest
NAME TYP WERT
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 100G
*******************************************************************************************
SQL> show parameter undo_retention
NAME TYP WERT
------------------------------------ ----------- ------------------------------
undo_retention integer 86400 <== 24 Stunden
( Hinweis: Die Größe des Undo-Tablespace muss groß sein )
*******************************************************************************************
SQL> show parameter db_flashback_retention_target
NAME TYP WERT
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440 <== Standard in Minuten = 24 Stunden 
*******************************************************************************************
SQL> show parameter log_archive_dest 
NAME TYP WERT 
------------------------------------ ----------- ------------------------------ 
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_DEST 
log_archive_format string mcs1_log_%r_%t_%s.arc 
******************************************************************************************* 
'SQL> archive log list' 
'Datenbank-Protokollmodus Archivmodus' 
'Automatische Archivierung aktiviert' 
'Archivziel USE_DB_RECOVERY_FILE_DEST' 
'Älteste Online-Protokollsequenz 3739' 
'Nächste Protokollsequenz zum Archivieren 3741' 
'Aktuelle Protokollsequenz 3741' 
'*******************************************************************************************' 
SQL> select name,open_mode,log_mode,flashback_on from v$database;
NAME OPEN_MODE LOG_MODE FLASHBACK_ON
--------- -------------------- ------------ ------------------
MCS1 READ WRITE ARCHIVELOG YES
*******************************************************************************************
--Pluggable Database Logging must be enabled
SQL> SELECT * FROM dba_pdbs ORDER BY pdb_name;
--if logging is disabled
SQL> ALTER SESSION SET CONTAINER = cdb$root;
SQL> SHOW CON_NAME
SQL> ALTER PLUGGABLE DATABASE <PDB_NAME> CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE <PDB_NAME> OPEN RESTRICTED;
SQL> ALTER PLUGGABLE DATABASE <PDB_NAME> LOGGING;
SQL> ALTER PLUGGABLE DATABASE <PDB_NAME> CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE <PDB_NAME> OPEN READ WRITE;
SQL> SELECT * FROM dba_pdbs ORDER BY pdb_name;
SQL> SELECT * FROM v$pdbs ORDER BY name;
*******************************************************************************************

_________________________________________________________________________________________________

3. Create the Restore Point

sqlplus /nolog
SQL> connect /as sysdba
SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> CREATE RESTORE POINT before_gdb_upgrade GUARANTEE FLASHBACK DATABASE;
SQL> SELECT * FROM V$RESTORE_POINT;

_________________________________________________________________________________________________

4. Check Invalid Objects

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL>  SELECT * FROM DBA_OBJECTS
       WHERE STATUS!= 'VALID'
       ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

_________________________________________________________________________________________________

5. Privileges Required for Geodatabase Upgrade

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAMESQL> CREATE ROLE GIS_SDE_MASTER;
SQL> GRANT CREATE SESSION,
     CREATE TABLE,
     CREATE VIEW,
     CREATE PROCEDURE,
     CREATE SEQUENCE,
     CREATE TRIGGER,
     CREATE TYPE,
     CREATE INDEXTYPE,
     CREATE LIBRARY,
     CREATE PUBLIC SYNONYM,
     DROP PUBLIC SYNONYM,
     ADMINISTER DATABASE TRIGGER,
     CREATE OPERATOR 
   TO GIS_SDE_MASTER;

SQL> GRANT SELECT_CATALOG_ROLE TO SDE;
SQL> GRANT SELECT_CATALOG_ROLE TO GIS_SDE_MASTER;
SQL> GRANT EXECUTE ON DBMS_CRYPTO TO GIS_SDE_MASTER;
SQL> GRANT GIS_SDE_MASTER TO SDE;

--Execute privileges on required Oracle builtin package DBMS_CRYPTO 

--granting them to a ROLE with execute access is insufficient.
SQL> GRANT EXECUTE ON DBMS_CRYPTO TO SDE;
SQL> GRANT EXECUTE ON DBMS_PIPE TO SDE;

SQL> CREATE ROLE GIS_SDE_MASTER_UPGRADE;
SQL> GRANT CREATE SESSION,
     CREATE TABLE,
     CREATE PROCEDURE,
     CREATE SEQUENCE,
     CREATE TRIGGER,
     CREATE TYPE,
     CREATE LIBRARY,
     CREATE PUBLIC SYNONYM,
     DROP PUBLIC SYNONYM,
     ALTER ANY INDEX,
     CREATE ANY INDEX,
     DROP ANY INDEX,
     CREATE ANY TRIGGER,
     CREATE ANY VIEW,
     DROP ANY VIEW,
     SELECT ANY TABLE,
     CREATE OPERATOR,
     ADMINISTER DATABASE TRIGGER,
     CREATE INDEXTYPE 
  TO GIS_SDE_MASTER_UPGRADE;

SQL> GRANT GIS_SDE_MASTER_UPGRADE TO SDE;

For more information read - Privileges required for geodatabase creation or upgrade

_________________________________________________________________________________________________

6. Active Connections

Make sure all user connections are closed.

Tip: ArcGIS Pro > Right Click Geodatabase Connection File for the "sde" user > Properties > Database Properties > Connections > Show connected user and locks...

This includes ArcMap, ArcCatalog and ArcGIS Pro.

You will need to ask users to close the applications and/or have the Oracle Database Administrator to kill the user connections.

Also, stop all ArcGIS Server Services that connect to the Enterprise Geodatabase.

Tip: when you open ArcCatalog or ArcGIS Pro to perform the Geodatabase Repository Upgrade make sure to click only on the Geodatabase Connection File for the "sde" user, if you click on any other connection file (e.g. data owner user, editor/viewer users) then you create a geodatabase lock that will prevent the upgrade to proceed, you must close ArcCatalog or ArcGIS Pro and re-open, only the "sde" user can perform the upgrade.

After all users and services connections are closed you can disable geodatabase connections to prevent any new connections to the geodatabase, of course only the "sde" user is allowed to connect.

Tip: ArcGIS Pro > Right Click Geodatabase Connection File for the "sde" user > Properties > Database Properties > Connections > turn off "Geodatabase is accepting connections" > Click Ok

_________________________________________________________________________________________________

7. Upgrade Geodatabase Repository

Follow the steps described in the documentation to upgrade the geodatabase repository.

Upgrade a geodatabase in Oracle

Use the Upgrade Geodatabase Geoprocessing tool

Upgrade Geodatabase (Data Management)

Right Click Geodatabase Connection File for the "sde" user > Properties > Database Properties > Upgrade Status

Tip: if you turned off the option "Geodatabase is accepting connections", then after the upgrade the option is turned on automatically

ArcGIS Pro > Right Click Geodatabase Connection File for the "sde" user > Properties > Database Properties > Connections > "Geodatabase is accepting connections" >

_________________________________________________________________________________________________

!!! Note: If there are any problems with the geodatabase repository upgrade and you want to roll back the changes then see below "Appendix - Restore using flashback database".

_________________________________________________________________________________________________

8. Check Invalid Objects

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL>  SELECT * FROM DBA_OBJECTS
       WHERE STATUS!= 'VALID'
       ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

_________________________________________________________________________________________________

9. Recompile Invalid Objects

Recompile any invalid objects in the SDE user and data owner users, then check again for invalid objects.

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> EXEC dbms_utility.compile_schema( 'SDE', compile_all => FALSE );
SQL> EXEC dbms_utility.compile_schema( 'GIS', compile_all => FALSE );

_________________________________________________________________________________________________

10. Check Invalid Indexes

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> SELECT * FROM ALL_INDEXES d WHERE d.status NOT IN ('VALID','N\/A');

_________________________________________________________________________________________________

11. Privilegien für das Geodatabase-Upgrade widerrufen

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> REVOKE GIS_SDE_MASTER_UPGRADE FROM SDE;

Wiederholen Sie die Schritte 8, 9, 10 oben, um ungültige Objekte und ungültige Indizes zu überprüfen

_________________________________________________________________________________________________

12. Aktualisieren Sie die ST_GEOMETRY-Bibliothek

Kopieren Sie die neue Version der ST_GEOMETRY-Bibliothek auf den Oracle-Datenbankserver.

Für weitere Informationen lesen Sie - Konfigurieren Sie extproc, um auf ST_Geometry in Oracle zuzugreifen

a. Aktualisieren Sie die sde.st_shapelib-Bibliothek, wenn sich der Pfad geändert hat.

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> CREATE OR REPLACE LIBRARY sde.st_shapelib
      AS '/orahome1/dbbase1/esrilibs/pro_30/libst_shapelib.so';

b. Überprüfen Sie die Bibliothek

SELECT * FROM ALL_LIBRARIES WHERE OWNER = 'SDE' ORDER BY OWNER, LIBRARY_NAME;

c. Überprüfen Sie ungültige Objekte

SQL> SELECT * FROM DBA_OBJECTS
       WHERE STATUS!= 'VALID'
       ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

d. Kompilieren Sie ungültige Objekte neu und überprüfen Sie dann erneut auf ungültige Objekte.

SQL> EXEC dbms_utility.compile_schema( 'SDE', compile_all => FALSE );

e. Validieren Sie die extproc-Konfiguration

Hinweis: Melden Sie sich als Benutzer sde oder als Datenbesitzer an!!!
SQL> SELECT sde.st_geometry(1,1,1,1,0) FROM dual;
SQL> SELECT sde.st_geometry('point (1 1)', 0) FROM dual;
SQL> SELECT sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) FROM dual;

_________________________________________________________________________________________________

13. Überprüfen Sie ungültige Objekte

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> SELECT * FROM DBA_OBJECTS
       WHERE STATUS!= 'VALID'
       ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

_________________________________________________________________________________________________

14. Überprüfen Sie ungültige Indizes

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> SELECT * FROM ALL_INDEXES d WHERE d.status NOT IN ('VALID','N\/A');

_________________________________________________________________________________________________

!!! Hinweis: Wenn es Probleme mit dem Upgrade des Geodatabase-Repository gibt und Sie die Änderungen rückgängig machen möchten, siehe unten 'Anhang - Wiederherstellung mit Flashback-Datenbank'.

_________________________________________________________________________________________________

15. Neue Statistiken sammeln

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;

SQL> SHOW CON_NAME

SQL> BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'SDE'
,Granularity => 'ALL'
,Options => 'GATHER'
,Gather_Temp => FALSE
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;

SQL> BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => 'GIS'
,Granularity => 'ALL'
,Options => 'GATHER'
,Gather_Temp => FALSE
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;

_________________________________________________________________________________________________

16. Wiederherstellungspunkt löschen

SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> SELECT * FROM V$RESTORE_POINT;
SQL> DROP RESTORE POINT before_gdb_upgrade;
SQL> SELECT * FROM V$RESTORE_POINT;

_________________________________________________________________________________________________

17. RMAN-Backup ausführen

Führen Sie ein RMAN-Inkrementelles Level-0-(Voll-)Backup und/oder ein RMAN-Inkrementelles Level-1-(Inkrementelles) Backup aus und führen Sie außerdem ein RMAN-Archivlog-Backup durch, um Speicherplatz im FRA - Fast Recovery Area freizugeben.

_________________________________________________________________________________________________

'Anhang - Wiederherstellung mit Flashback-Datenbank'

'sqlplus /nolog' 
'SQL> connect /as sysdba' 

'SQL> ALTER SESSION SET CONTAINER = cdb$root;' 

'SQL> SHOW CON_NAME' 

'SQL> SELECT * FROM V$RESTORE_POINT;' 

'SQL> SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;' 

'SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;' 

'SQL> ALTER PLUGGABLE DATABASE <PDB_NAME> CLOSE;' 

'SQL> FLASHBACK PLUGGABLE DATABASE <PDB_NAME> TO RESTORE POINT before_gdb_upgrade;' 

'SQL> ALTER PLUGGABLE DATABASE <PDB_NAME> OPEN RESETLOGS;' 

'SQL> SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;' 

'SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;'

' ' '_________________________________________________________________________________________________'

'FAQ - Häufig gestellte Fragen'

1. Can I upgrade the Geodatabase Repository if the Geodatabase has Traditional Versions or Branch Versions?

Answer: Yes, you can. There is no problem to upgrade.

2. Is necessary to Reconcile and Post the Geodatabase Versions before the Geodatabase Repository Upgrade?

Answer: No, it is not necessary.

_________________________________________________________________________________________________

Finde mehr Beiträge mit dem Tag

Geodatabase

Geodatabase Administration

Oracle

Nehmen Sie an der Diskussion teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registrieren Sie ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.