Reasons Why Oracle Autonomous Database (ADB) Cloud May NOT Be Suitable For Me.
Environment: Always Free Oracle Autonomous Database [database-as-a-service]
1. ADMIN user: In the Oracle Cloud ADB, you will be given access as ADMIN user. Which is less privileged for many normal DBA tasks that I currently do.
2. Tablespace Creation: You cannot create tablespaces with this user, it will result in an "ORA-01031: insufficient privileges" error.
ADMIN@cwtp_low> set lines 120
ADMIN@cwtp_low> col file_name for a85
ADMIN@cwtp_low> select file_name, bytes/1024/1024/1024 size_gb from dba_data_files order by 1;
FILE_NAME SIZE_GB
------------------------------------------------------------------------------------- ----------
+DATA/FEKY1POD/A9CA021DABDFFA2CE0539F14000ADF5F/DATAFILE/data.1827.1045070211 .09765625
+DATA/FEKY1POD/A9CA021DABDFFA2CE0539F14000ADF5F/DATAFILE/dbfs_data.1831.1045070211 .09765625
+DATA/FEKY1POD/A9CA021DABDFFA2CE0539F14000ADF5F/DATAFILE/sysaux.1828.1045070211 3.22607422
+DATA/FEKY1POD/A9CA021DABDFFA2CE0539F14000ADF5F/DATAFILE/system.1825.1045070211 .420898438
+DATA/FEKY1POD/A9CA021DABDFFA2CE0539F14000ADF5F/DATAFILE/undo_2.4299.1045538735 1
+DATA/FEKY1POD/A9CA021DABDFFA2CE0539F14000ADF5F/DATAFILE/undotbs1.1826.1045070211 1
+DATA/sampleschema_dbf 200
7 rows selected.
ADMIN@cwtp_low> CREATE TABLESPACE xxxx DATAFILE
2 '+DATA' SIZE 100M AUTOEXTEND ON NEXT 1024K MAXSIZE 2048M
3 EXTENT MANAGEMENT LOCAL
4 AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE xxxx DATAFILE
*
ERROR at line 1:
ORA-01031: insufficient privileges
3. SYS User: If you are in a situation to create an Enterprise Geodatabase (Esri), the tool will need SYS Password in order to execute it. Where as in the Oracle Cloud ADB, you will not be provided with SYS Password.
ADMIN@cwtp_low> select username,account_status from dba_users order by 1;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
ADBSNMP LOCKED
ADMIN OPEN
APEX_190200 LOCKED
APEX_200100 LOCKED
APEX_INSTANCE_ADMIN_USER LOCKED
APPQOSSYS LOCKED
AUDSYS LOCKED
C##ADP$SERVICE OPEN
C##API OPEN
C##CLOUD$SERVICE OPEN
C##CLOUD_OPS OPEN
C##DV_ACCT_ADMIN LOCKED
C##DV_OWNER LOCKED
C##OMLIDM OPEN
CTXSYS LOCKED
DBSFWUSER LOCKED
DBSNMP OPEN
DIP LOCKED
DVF LOCKED
DVSYS LOCKED
FLOWS_FILES LOCKED
GGADMIN LOCKED
GGSYS LOCKED
GSMADMIN_INTERNAL LOCKED
GSMCATUSER LOCKED
GSMUSER LOCKED
LBACSYS LOCKED
MDDATA LOCKED
MDSYS LOCKED
OML$PROXY OPEN
ORACLE_OCM LOCKED
ORDS_METADATA EXPIRED & LOCKED
ORDS_PUBLIC_USER OPEN
OUTLN LOCKED
REMOTE_SCHEDULER_AGENT LOCKED
SH LOCKED
SSB LOCKED
SYS OPEN
SYS$UMF LOCKED
SYSBACKUP LOCKED
SYSDG LOCKED
SYSKM LOCKED
SYSRAC LOCKED
SYSTEM OPEN
XDB LOCKED
XS$NULL EXPIRED & LOCKED
4. Library Objects to
Create Spatial Type
Create / Enable Enterprise Geodatabase
The database user "SDE" will need to have "CREATE LIBRARY" privilege. If you try to grant "CREATE LIBRARY" privilege in ADB, it will fail:-
ADMIN@cwtp_low> grant CREATE LIBRARY to SDE;
grant CREATE LIBRARY to SDE
*
ERROR at line 1:
ORA-01031: insufficient privileges
Without the Library and without SYS user, you can't create SDE.ST_GEOMETRY type which will refer to "st_shapelib.dll". Which means, you cannot have an ESRI Geodatabase in an Oracle ADB.
5. Transportable Tablespace - Bulk Data Loads: I currently leverage this option to move bulk datasets (like Raster Catalogs and Mosaiced Raster Datasets) from one environment to another (for example, Test to Production). The initial load involves heavy-duty data processing which is complex by nature, which not only takes lots of resources, but also longer duration. So, moving it to another environment through transportable tablespace is a valuable option, which cannot be used in Oracle Cloud ADB.
6. Network performance - Random disruption and very slow.
Here I have listed some limitations that I am currently facing with the Oracle Autonomous Database (ADB) Cloud, please share your experience with regards to limitations, if any. Thanks.
No comments:
Post a Comment