_________________________________________________________________________________________________
Oracle Restore Pointを使用したOracle Enterprise Geodatabase SDEリポジトリのアップグレード
問題が発生した場合にロールバックするための変更。
PDF版をダウンロード
_________________________________________________________________________________________________
_________________________________________________________________________________________________
1. ArcGISドキュメント - アップグレード前に
ジオデータベースリポジトリをアップグレードする前にArcGISドキュメントをお読みください。
Oracleでジオデータベースをアップグレードする - アップグレード前に
ArcGISの要件を読み、 Oracle用ArcGIS要件
_________________________________________________________________________________________________
2. OracleアーカイブとOracleフラッシュバック
a. Oracleアーカイブが有効になっていること
b. Oracleフラッシュバックが有効になっていること
c. Oracle RMAN インクリメンタル レベル 0（フル）バックアップが作成されていること
d. Oracle RMAN インクリメンタル レベル 1（インクリメンタル）バックアップが作成されていること
e. Oracle RMAN アーカイブログバックアップが作成されていること
f. プラガブルデータベースログ記録が有効になっていること
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 TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 100G
*******************************************************************************************
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 86400 <== 24時間
( 注：undo tablespaceサイズは大きくする必要があります )
*******************************************************************************************
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440 <== デフォルト（分単位）= 24時間
*******************************************************************************************
SQL> show parameter log_archive_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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
データベースログモード Archive Mode（アーカイブモード）
自動アーカイブ 有効化済み
アーカイブ先 USE_DB_RECOVERY_FILE_DEST
最古のオンラインログシーケンス 3739
次にアーカイブするログシーケンス 3741
現在のログシーケンス 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
--must be granted directly to the SDE DBA user,
--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. ジオデータベースアップグレードに必要な権限の取り消し
SQL> ALTER SESSION SET CONTAINER = <PDB_NAME>;
SQL> SHOW CON_NAME
SQL> REVOKE GIS_SDE_MASTER_UPGRADE FROM SDE;
手順を繰り返します 8、9、10 上記の無効なオブジェクトと無効なインデックスを確認するために
_________________________________________________________________________________________________
12. ST_GEOMETRYライブラリの更新
新しいバージョンのST_GEOMETRYライブラリをOracle Database Serverにコピーします。
詳細については以下を参照してください - OracleでST_Geometryにアクセスするためのextprocの設定
a. パスが変更された場合はsde.st_shapelibライブラリを更新します。
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. ライブラリの確認
SELECT * FROM ALL_LIBRARIES WHERE OWNER = 'SDE' ORDER BY OWNER, LIBRARY_NAME;
c. 無効なオブジェクトの確認
SQL> SELECT * FROM DBA_OBJECTS
WHERE STATUS!= 'VALID'
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
d. 無効なオブジェクトを再コンパイルし、その後再度無効なオブジェクトを確認します。
SQL> EXEC dbms_utility.compile_schema( 'SDE', compile_all => FALSE );
e. extproc設定の検証
注意: sdeユーザーまたはデータ所有者ユーザーとして接続してください!!!
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. 無効なオブジェクトの確認
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. 無効なインデックスの確認
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');
_________________________________________________________________________________________________
!!! 注意: ジオデータベースリポジトリのアップグレードに問題があり、変更をロールバックしたい場合は以下を参照してください "付録 - フラッシュバックデータベースを使用した復元".
_________________________________________________________________________________________________
15. 新しい統計情報の収集
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. リストアポイントの削除
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バックアップの実行
RMANインクリメンタルレベル0（フル）バックアップおよび／またはRMANインクリメンタルレベル1（インクリメンタル）バックアップを実行し、さらにFRA（Fast Recovery Area）の空き容量を確保するためにRMANアーカイブログバックアップも実行します。
_________________________________________________________________________________________________
付録 - フラッシュバックデータベースを使用した復元方法
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 - Frequent Asked Questions
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.
_________________________________________________________________________________________________
