How to recreate the XE database in Oracle 11g Express

Based on my own research, google results and ansible's answer, I was able to come up with complete solution for Oracle Linux 7. Everytime you read Kitet - this is my Oracle Instance SID.

To start or stop database, dbstart and dbshut scripts are used respectively. They are located in $ORACLE_HOME/bin. Both of them need a parameter, which is $ORACLE_HOME. If there's no $ORACLE_HOME environment variable, first there's a need to set it (and some more) by running

. /usr/local/bin/oraenv

(note the preceding period, and it's not $ORACLE_HOME/bin/oraenv). Oraenv script will ask for a SID, which is needed to set correct $ORACLE_BASE. With this, using simply

dbstart $ORACLE_HOME

or

dbshut $ORACLE_HOME

will start and stop the listener and database.

To start listener using lsnrctl or start SQL*Plus using sqlplus / as sysdba, running . oraenv beforehand is also required, unless user wants to set manually some environment variables. If our linux box has only one instance, there's a possibility of automating running of . oraenv by editing user's bash profile. Enter

nano ~/.bash_profile

and type following lines (there's probably already umask 022 entry in this file, as per Oracle Database Installation Guide 12c Release 1 for Linux E41491-10):

export ORACLE_SID=Kitet
export ORAENV_ASK=NO
. /usr/local/bin/oraenv
export NLS_LANG=POLISH_POLAND.AL32UTF8
unicode_start

Line one and three are self explanatory. Line two will make oraenv not ask for database SID and use exported variable. Line four is optional and will set your language preference for client connections (e.g. SQL*Plus or lsnrctl will speak in your native language, if available). Line five is needed for console to display accented characters, and is also optional.

Automating startup and shutdown of Listener and Database

1) Once the instance is created, edit the /etc/oratab file setting the restart flag for each instance to 'Y'. My line is as follows:

Kitet:/home/oracle/app/oracle/product/12.1.0/dbhome_1:Y

2) Next, create a file called /etc/init.d/dbora as the root user, containing the following:

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the 
# Oracle database in ORA_HOME.

# path to oracle home (needed only to check if dbstart exists)
ORA_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1
# this is the user who installed oracle
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle database and listener:
        # Remove "&" if you don't want startup as a background process.
        export ORACLE_SID=Kitet
        export ORAENV_ASK=NO
        . /usr/local/bin/oraenv
        # at this point we have $ORACLE_HOME env variable set
        su $ORA_OWNER -c "dbstart $ORACLE_HOME" &
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle database and listener:
        export ORACLE_SID=Kitet
        export ORAENV_ASK=NO
        . /usr/local/bin/oraenv
        su $ORA_OWNER -c "dbshut $ORACLE_HOME"
        rm -f /var/lock/subsys/dbora
        ;;
esac

3) Use the chmod command to set the privileges to 750:

chmod 750 /etc/init.d/dbora

4) Associate the dbora service with the appropriate run levels and set it to auto-start using the following command:

chkconfig --add dbora

That's all. dbstart and dbshut should write to their respective logs at $ORACLE_HOME/startup.log and $ORACLE_HOME/shutdown.log. There also is $ORACLE_HOME/listener.log. There we can verify if and how instance and listener started and stopped. We could also use ps -ax | grep ora or ps -ax | grep lsnr to see if listener / oracle are running.

First of all check whether your pluggable database is opened or not.

SQL> select name, open_mode from v$pdbs where name='PDBORCL';

If it is in mounted mode then,

SQL> alter pluggable database pdborcl open;

Now on SQL Developer Create new connection as,

Connection Name: demo-pdborcl

Username: demo

Password: *****

Connection Type: Basic(If you wish to use TNS connection type you have to create TNS Network Alias in client side.)

Hostname: ip/dns

Port: 1521

SID: (If you use SID like 'ORCL' you will be connected to root container, therefore use Service name instead of SID)

Service name: (You can get your pdb's service name by firing $ lsnrctl serivce, also you can use USE_SID_AS_SERVICE_listener parameter on $ORACLE_HOME/network/admin/listner.ora file as USE_SID_AS_SERVICE_listener=on and any given SID will be treated as service name.

Some useful links-