ArcSDE10.2.1forOracle12c database creation mode

We have introduced how to create Geodatabase10.2.1 in the Oracle12c environment, but these are all created in the default environment. If you want to create a new PDB, then, create an ArcSDE geographic database on the newly created PDB, and the user wants to create another common SDE database. What should the user do? Note:

We have introduced how to create Geodatabase10.2.1 in the Oracle12c environment, but these are all created in the default environment. If you want to create a new PDB, then, create an ArcSDE geographic database on the newly created PDB, and the user wants to create another common SDE database. What should the user do? Note:

We have introduced how to create Geodatabase10.2.1 in the Oracle12c environment, but these are all created in the default environment. If you want to create a new PDB, then, create an ArcSDE geographic database on the newly created PDB, and the user wants to create another common SDE database. What should the user do?

Note: The following operations are based on the use of PDB. Because PDB charges fees, if you use non-PDB Oracle instances, the related operations are basically the same as those in Oracle11g.

You can leave the Create as Container database check box unspecified. No PDB is created.

Test environment:

Linux 5.5, Oracle12.1.0.1 (CDB: orcl PDB: pdborcl), ArcGIS10.2.1

Do you know?
The multi-tenant feature of the Oracle 12c database is billed separately for $17.5 thousand/processor, while the price of the 12c database for the Enterprise Edition is still USD 47.5 thousand/processor.

1: Create a New PDB: pdbsde

There are multiple ways to create a new PDB. We use the PDB Seed to create a PDB.

We need to connect the SYS user of CDB and use the create pluggable database statement to create pdb.

SQL> CREATE PLUGGABLE DATABASE pdbsde ADMIN USER sdeadmin IDENTIFIED BY sdeadmin  2  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M) DEFAULT TABLESPACE pdbsdeDATAFILE '/home/oracle/app/oracle/oradata/orcl/pdbsde/pdbsde.dbf' SIZE 500M AUTOEXTEND ON  3    4  PATH_PREFIX = '/home/oracle/app/oracle/oradata/orcl/pdbsde/'  5  FILE_NAME_CONVERT = ('/home/oracle/app/oracle/oradata/orcl/pdbseed/',  6  '/home/oracle/app/oracle/oradata/orcl/pdbsde/');Pluggable database created.

For more information about the parameters, see:

Bytes --------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301
WEAVER: http://www.weibo.com/linghe301
Bytes --------------------------------------------------------------------------------------------

After creating the PDB, let's check the status of the PDB, as shown below. The newly created PDBSDE is in the Mounted state. We need to manually open it and then check the changes in the data file.

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;    CON_ID       DBID NAME                           OPEN_MODE---------- ---------- ------------------------------ ----------         2 4062262812 PDB$SEED                       READ ONLY         3 2266793495 PDBORCL                        READ WRITE         4  323980054 PDBSDE                         MOUNTEDSQL> alter pluggable database pdbsde open;Pluggable database altered.SQL>  select name from v$datafile;NAME--------------------------------------------------------------------------------/home/oracle/app/oracle/oradata/orcl/system01.dbf/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf/home/oracle/app/oracle/oradata/orcl/pdbseed/system01.dbf/home/oracle/app/oracle/oradata/orcl/users01.dbf/home/oracle/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf/home/oracle/app/oracle/oradata/orcl/pdborcl/system01.dbf/home/oracle/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf/home/oracle/app/oracle/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf/home/oracle/app/oracle/oradata/orcl/pdborcl/example01.dbf/home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/sde01NAME--------------------------------------------------------------------------------/home/oracle/app/oracle/oradata/orcl/esri.dbf/home/oracle/app/oracle/oradata/orcl/pdborcl/esri.dbf/home/oracle/app/oracle/oradata/orcl/pdborcl/esri2.dbf/home/oracle/app/oracle/oradata/orcl/pdbsde/system01.dbf/home/oracle/app/oracle/oradata/orcl/pdbsde/sysaux01.dbf/home/oracle/app/oracle/oradata/orcl/pdbsde/pdbsde.dbf17 rows selected.SQL>

Next, we use the SYS user to connect to PDBSDE, table space data for SDE creation (because SDE tablespace files created using Create Enterprise Geodatabase of ArcGIS10.2.1 for Desktop are stored in/home/oracle/app/oracle/product/12.1.0/dbhome_1/by default/ dbs/sde01 path, however, this file does not clearly show which PDB or CDB is used to create the file. Therefore, we recommend that you manually create the SDE data file and specify the relevant PDB name, for example,/home/oracle/app/oracle/oradata/ora/pdbsde/

SQL> conn sys/oracle@192.168.220.203/pdbsde as sysdbaConnected.SQL> CREATE SMALLFILE TABLESPACE SDE DATAFILE '/home/oracle/app/oracle/oradata/orcl/pdbsde/sde.dbf' SIZE 1000M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS  2  ;Tablespace created.

After the creation, we can use the create geographic database tool to create a geographic database stored in PDBSDE

After the creation, we can use the sde user to connect.

Bytes --------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301
WEAVER: http://www.weibo.com/linghe301
Bytes --------------------------------------------------------------------------------------------

2: As mentioned above, if you want to create a tablespace for your business data and related users, assume that you have created an ESRI tablespace and test2 user for the tablespace.

SQL> conn sys/oracle@192.168.220.203/pdbsde as sysdbaConnected.SQL> CREATE SMALLFILE TABLESPACE SDE DATAFILE '/home/oracle/app/oracle/oradata/orcl/pdbsde/sde.dbf' SIZE 1000M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS  2  ;Tablespace created.SQL>SQL> CREATE SMALLFILE TABLESPACE ESRI DATAFILE '/home/oracle/app/oracle/oradata/orcl/pdbsde/esri.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS;Tablespace created.

After the tablespace is created, we will become accustomed to using Oracle client software such as PL/SQL to create related database user test2 and assign the roles such as Connect and Resouce.

However, when you connect to the test2 user through ArcCatalog, the following error is prompted when you create a feature class: ORA-01950

Let's check the Ora-01950 error.

SQL> !oerr ora 0195001950, 00000, "no privileges on tablespace '%s'"// *Cause:  User does not have privileges to allocate an extent in the//          specified tablespace.// *Action: Grant the user the appropriate system privileges or grant the user//          space resource on the tablespace.

Error message: the user does not have the Extent permission assigned to the table space ESRI, that is, the user is not granted the Quota permission.

The Oracle official website defines quota as follows: A limit on a resource, such as a limit on the amount of database storage used by a database user. A database administrator can set tablespace quotas for each Oracle Database username.

Quota is used to restrict the use of table space. For example, if you set the quota value of user test in tablespace SDE to 10 MB, when the data volume of user test in tablespace SDE reaches 10 MB, test cannot use tablespace SDE no matter how much space your tablespace SDE has.

In Oracle11g and earlier versions, if the user assigns the resouce role, the system automatically assigns the default tablespace quota umlimited to THE tablespace to which the user belongs. However, after Oracle12c, even if the resouce role is assigned, the system does not grant the quota permission by default. You need to manually grant the permission.

This issue is also clearly indicated by Oracle help.

Http://docs.oracle.com/cd/E16655_01/network.121/e17607/release_changes.htm#DBSEG941

Bytes --------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301
WEAVER: http://www.weibo.com/linghe301
Bytes --------------------------------------------------------------------------------------------

Therefore, you only need to set the quota permission when creating a new user.

Method 1:

SQL> create user test2 identified by test2 DEFAULT TABLESPACE ESRI TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK  quota unlimited on esri container=current;User created.SQL> grant connect,resource to test2;Grant succeeded.

Method 2:

SQL> create user test2 identified by test2 DEFAULT TABLESPACE ESRI TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK   container=current;User created.SQL> grant connect,resource to test2;Grant succeeded. SQL> grant unlimited tablespace to test2;Grant succeeded.

The following users can use the test2 user to load space data.

Next, let's take a look at the relationship between CDB and PDB and users.

From our test environment, we can see that the system already has a CDB and PDBORCL by default, and the ArcSDE geographic database is created in PDBORCL, the default SYS/SYSTEM user is the super user of the entire Oracle database. It can operate both CDB and PDBORCL, the default SDE user created by PDBORCL can only operate the SDE database in PDBORCL. The system creates a new PDBSDE, and then creates a SDEADMIN management user, the default SDE user and the new TEST2 user can only operate on PDBSDE, but cannot manage PDBORCL.

In fact, the CDB and PDB of Oracle12c can be compared to CDB, which is the original Oracle database, and PDB is an instance of the database. You can create multiple PDB instances, you can also create multiple instances in a database, but each instance has its own super administrator. However, Oracle12c only needs a CDB Administrator Account to manage all the PDB instances.

Bytes --------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/linghe301
WEAVER: http://www.weibo.com/linghe301
Bytes --------------------------------------------------------------------------------------------

You can leave the Create as Container database check box unspecified. No PDB is created.