Install Oracle and create a database - database configuration/design tasks

Install the ORACLE database software on your computer.

Create and setup the Oracle instance and database. Consider creating a multitenant database when using Oracle 12c and higher, to create several pluggable databases (PDB) in the same Oracle instance.

Specify the database character set when creating the database instance. If you plan to use UTF-8, use also character length semantics (See NLS_LENGTH_SEMANTICS session parameter).

Create a database context dedicated to your application.

According the Oracle version, define a db user / schema to hold application tables, or create a pluggable database (starting with Oracle 12c).

With Oracle version 11g and lower, group application tables in a schema by creating a dedicated database user.
Connect as system user with:
```
$ sqlplus / AS SYSDBA
```
and execute the following SQL command to create the db user:
```
CREATE USER appadmin IDENTIFIED BY password;
```
Grant privileges to the application administrator user:
```
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO appadmin;
```
With Oracle version 12c and higher, group application tables in a pluggable database (PDB).
Connect as system user with:
```
$ sqlplus / AS SYSDBA
```
and create a pluggable database and its PDB administrator user. This is a basic PDB creation example using Oracle Managed Files, consider planing the PDB creation with the person in charge of Oracle database administration:
```
CREATE PLUGGABLE DATABASE mypdb
  ADMIN USER pdbadmin IDENTIFIED BY password ROLES = (DBA)
  DEFAULT TABLESPACE mypdb_01
     DATAFILE 'path_01' SIZE 250M AUTOEXTEND ON ;
```
For now the PDB is only mounted, it must be opened for regular usage:
```
ALTER PLUGGABLE DATABASE mypdb OPEN;
```
PDBs must be identified as separate database services (i.e. different from the CDB service). By default Oracle creates a database service with the same name as the PDB. To access the PDB through TNS, create the mypdb record in TNSNAMES.ORA file in addition to the default database service (ORC*):
```
tnsname = 
 (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA = 
       (SERVER = DEDICATED)
       (SERVICE_NAME = mypdb)
    )
 )
```
By default when Oracle starts, the PDBs are mounted but are not open for regular usage. With Oracle 12c, you can create a database trigger to open all PDBs automatically:
```
CREATE OR REPLACE TRIGGER open_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
    EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/ 
```
Or, save the state of a specific PDB when it is open:
```
ALTER PLUGGABLE DATABASE test1 OPEN;
ALTER PLUGGABLE DATABASE test1 STAVE STATE;
```
Re-connect as PDB administrator and create a user dedicated to application tables administration:
```
CONNECT pdbadmin/password@mypdb
CREATE USER appadmin IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO appadmin;        
```

If programs create temporary tables, you must create a dedicated tablespace and schema depending on the type of temporary table emulation used.

For more details about temporay table emulations, see Temporary tables.

Create the application tables by connecting to the database context as the application administrator:

$ sqlplus appadmin/password@tnsname

Convert Informix® data types to Oracle data types. See issue data type Conversion Tables for more details.

If you plan to use SERIAL emulation, you must choose a serial emulation method.

Select the best emulation technique that matches your needs. You need to prepare the database depending on the emulation type. For more details, see SERIAL and BIGSERIAL data types.