Pluggable Database 12c EM Express configuration

Pluggable Database 12c PDB configuration: 

When you install 12c Database with container database (CDB), by default 12c EM Express PORT configure’ s for only CDB database. 

Creating New Port for PDB: 

Connect to Database: 

sqlplus / as sysdba

            -- default it connects to CDB

View all Ports for PDB's :

            --- to list all PDB instances

select c.name,c.open_mode,e.port 

            from v$pdbs c, xdb.xdb$cdbports e 

            where c.con_id = e.pdb (+);

Change Session to PDB:

            alter session set container = PDB_DB12CR2;

            --session changed PDB,  PDB_DB12CR2 -- PDB instance name

Verify EM Port on PDB: 

            select dbms_xdb_config.gethttpsport() from dual;

Set New Port to PDB: 

            --If above query returns NULL or required to change the PORT, verify PORT availability

            exec dbms_xdb_config.sethttpsport(5501);

            --- 5501 PORT will assign to current PDB

Verify Ports from CDB:

select c.name,c.open_mode,e.port 

            from v$pdbs c, xdb.xdb$cdbports e 

            where c.con_id = e.pdb (+);

Verify listners on new PORT:

snrctl status | grep -i 5501

PDB - Direct Connection TNS Entries: 

Add bellow tns entry into existing tnsnames.ora file, change PDB service name, CDB hostname and PORT number.

$ vi $ORACLE_HOME/network/admin/tnsnames.ora

#PDB Instance details

PDB_DB12CR2 =

(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )))

(CONNECT_DATA = (SERVICE_NAME = ) ))

Esc : wq

# save your tns file

Now client SQL Developer/TOAD/SQL Plus can connect directly to PDB database.

PDB - Start Stop Services: 

Startup:

            --from Container Database

ALTER PLUGGABLE DATABASE PDB_DB12CR2 OPEN;

            OR

            ALTER SESSION SET CONTAINER = PDB_DB12CR2;

            STARTUP

Stop:

            --from Container Database

ALTER PLUGGABLE DATABASE PDB_DB12CR2 CLOSE IMMEDIATE;

OR

            ALTER SESSION SET CONTAINER = PDB_DB12CR2;

            SHUTDOWN IMMEDIATE;

Status:

            --from Container Database

            SELECT name, open_mode FROM v$pdbs;

All PDB’s Start & Stop:

            ALTER PLUGGABLE DATABASE ALL OPEN;

ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Startup & Stop Except:

            ALTER PLUGGABLE DATABASE ALL EXCEPT pdb_1 OPEN;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb_1 CLOSE IMMEDIATE;

Automatic PDB Startup:

            --Create trigger on CDB startup

            CREATE OR REPLACE TRIGGER OPEN_ALL_PDBS

            AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

END OPEN_ALL_PDBS;

     

Oracle Database 12cR1 install step by step on Oracle Linux 6.8

Oracle Database 12cR1 Install


How to Install Oracle Database 12cR1 on Oracle Linux 6.8 – 64bit

Environment:

Oracle Linux 6.7 64bit, 6 GB RAM

150 GB Disk Space

Download:

·         

1. V38500-01_1of2.zip
2. V38500-01_2of2.zip

·         Download from OTN

Unix Required:

·         Directories

Login to unix server with "oracle – user account name" account

$su # enter the password for root access

[root@obiee12c /]# mkdir oracle

[root@obiee12c /]# chown oracle:dba /oracle

[root@obiee12c /]# chmod -r 775 oracle

[root@obiee12c /]# exit

[obiee@obiee12c ~]$ cd /oracle

[obiee@obiee12c oracle]$ mkdir database

[obiee@obiee12c oracle]$ mkdir orainventory

·         Inventory Setup

[oracle@obiee12c oracle]$ su

Password:

[root@obiee12c oracle]# cd /etc

[root@obiee12c etc]# vi oraInst.loc

Add following lines

inventory_loc=/oracle/database/orainventory

inst_group=dba

Save and exit

·         Host Name Setup

Check the host name

[root@obiee12c etc]# vi hostname

#add bellow line at host file, if host name not exist

192.168.64.13 obiee12c.oracle    oracle

"IP Address might be differ"

Oracle Pre Install RPM:

·       To Install Oracle Database 12cR1 on a Linux system, need to pre-configure the operating environment since the database requires certain software packages, package versions, and tweaks to kernel parameters.

Using the Oracle RDBMS Server 12cR1 Pre-install RPM, you can complete most of the pre-installation configuration tasks

·         Install RPM

[obiee@obiee oracle]$ su

Password:

[root@obiee oracle]# yum install oracle-rdbms-server-12cR1-preinstall

Restart the server

Manual Install and Configurations:

·         Limits:

Add following values to /etc/security/limits.conf

obiee   soft   nofile    1024

obiee   hard   nofile    65536

obiee   soft   nproc    16384

obiee   hard   nproc    16384

obiee   soft   stack    10240

obiee   hard   stack    32768

obiee   hard   memlock    134217728

obiee   soft   memlock    134217728

·         System Controls:

Add following Values /etc/sysctl.confto

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 4294967295

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

/etc/security/limits.d/90-nproc.conf:

* - nproc 16384

·         Users and groups:

groupadd -g 501 oinstall

groupadd -g 502 dba

groupadd -g 503 oper

groupadd -g 504 asmadmin

groupadd -g 506 asmdba

groupadd -g 505 asmoper

Creating Users and Groups:

useradd -u 502 -g oinstall -G dba,asmdba,oper oracle

passwd oracle

·         Required RPMS:

Following RPM’s are required for Oracle  linux 6 64 bit

binutils-2.20.51.0.2-5.11.el6 (x86_64)

compat-libcap1-1.10-1 (x86_64)

compat-libstdc++-33-3.2.3-69.el6 (x86_64)

gcc-4.4.4-13.el6 (x86_64)

gcc-c++-4.4.4-13.el6 (x86_64)

glibc-2.12-1.7.el6 (x86_64)

glibc-devel-2.12-1.7.el6 (x86_64)

ksh

libgcc-4.4.4-13.el6 (x86_64)

libstdc++-4.4.4-13.el6 (x86_64)

libstdc++-devel-4.4.4-13.el6 (x86_64)

libaio-0.3.107-10.el6 (x86_64)

libaio-devel-0.3.107-10.el6 (x86_64)

make-3.81-19.el6

sysstat-9.0.4-11.el6 (x86_64)

unixODBC-2.2.14-11.el6 (x86_64) or later

unixODBC-devel-2.2.14-11.el6 (x86_64) or later

·         Restart Server:

After Installation and configuration, needs to be restart to effect the new changes

Environment Variables:

·         Execute following variables before installing

# Oracle Settings

TMP=/oracle/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=obiee.oracle; export ORACLE_HOSTNAME

ORACLE_UNQNAME=OBIEE12C; export ORACLE_UNQNAME

ORACLE_BASE=/oracle/database/obase; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db; export ORACLE_HOME

ORACLE_SID=OBIEE12C; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Database Install:
 [oracle@obiee12c oracle]$ cd /oracle/software/database12c
[oracle@obiee12c oracle]$ ./runInstaller
 

     1. Our Installer has been launched here. For every steps we need to move forward by Clicking Next or OK

     2. Configure Security Updates:
I’m going to skip this step as I don’t want security updates. Un-check the check box and mark the checkbox that says “Wish to receive security updates via My Oracle Support“.
Click on Next, you will get a error saying that you’ve not provided and email address click Yes to continue.

     3. Software Updates:
While we skiped the email step by default it will choose skip software updates Click next to continue.

4. Installation Option:
Next, choose the type of installation, I’m choosing the first option to Create and configure a database.

5.System Class:
I’m going to choose Server Class here. If we need to install in any Desktop machines we can choose the above Option as Desktop Class.

6.Grid Installation Options:
We are going to setup only single instance database installation here. So, select the first option.

7.Install Type:
Choose the Advance install option to get more option while going through Installation steps.

8.Product Languages:
By Default Language will be chooses as English. If you need to change according to your language, choose from the below list.

9.Database Edition:
Time to choose which edition of database installation we looking for. For large scale Productions we can use Enterprise or if we need standard edition or we can choose the options as mentioned there. We need more than 6.5 GB space for Enterprise installation because database Population will grow soon/increase.

10.Installation Location:
Enter the Oracle base installation location, here all installed configurations files will be stored. Here you need to define the location of oracle installation path, as we created the location above in the second part of this article.

11.Creating Inventory:
For the first time installation, every Inventory files will be created under ‘/oracle/database/oralnventory‘ directory. We have created the group oracle for installation. So now the oracle group has permission to access Inventory Directory. Let us choose the Oracle as the Group for Operating system group.

12.Configuration Type:
Select the type of database, you want to create. Since, we are using for General purpose, so choosing general from the below options and click Next.

13.Database identifiers:
Specify the Global Database name for uniquely identified and un-check the Create as Container database, as here we are not going to create multiple databases.

14.Configuration Options:
In my installation, I have assigned 6GB of Memory to my virtual machine, but this is not enough for Oracle. Here we need to Enable allocate memory automatically for the use of system global Area.
Check the box that says Enable Automatic Memory Management and keep the default allocate memory. If we need some sample schema’s we can check and continue for installation.

    Change Character Set to AL32UTF8

 

15.Database Storage:
We need to choose the location to store the database storage. Here I’m going to assign ‘/oracle/database/obase/oradata‘ location to save the databases and Click Next to continue to installer steps.

16.Management Options:
I don’t have a Cloud control manager credentials from oracle, so I have to skip this step.

17.Recovery Options:
If we have to Enable recovery options, then we have to check the Enable Recovery. In real environment these options are Compulsory to setup. Here to enable this option we need to add separate group and we need to define one of the file system location rather than default location where our database save.

18.Schema Passwords:
We need to define the password for starter database which is all pre-loaded while the installations. Password must contain alphanumeric, upper_case and lower_case. For example, my password is Oracle123. This password we will use in web interface login too.

19.Operating system Groups:
We need to provide system privileges to create database for that we need to choose the "dba" group. Choose oracle for every options.

20.Summary:
At last we can review every settings before database population. If we need any changes we can edit the settings.

21.Install Product:
Installation started to Preparation and copying files. This will take long time to complete according to our Hardware Resource.

22. During setup process, it will ask to run two scripts as a root user 

[oracle@obiee12c oracle]$ su
Password:
[root@obiee12c oracle]# cd /
[root@obiee12c oracle]# ./oracle/database/oralnventory/orainstRoot.sh
[root@obiee12c oracle]# ./oracle/database/obase/product/12.1.0/db_1/root.sh 

     After successfully execution of above two scripts, we need to move forward by clicking on OK.
     After finishing all the above tasks successfully, we will receive the Database Configuration Assistant window with the all the details and it will show you the EM Database Express URL. Click OK to move forward.

Finally Oracle Database installation was successfully completed. Click on Close to quit the Oracle Installer.

Verify Install:

·         Start and Stop

[oracle@obiee12c oracle]$ sqlplus / as sysdba

SQL> Shutdown immediate

SQL> exit

[obiee@obiee12c oracle]$ sqlplus / as sysdba

SQL> startup

SQL> exit

·         Listeners Start and Stop

[oracle@obiee12c oracle]$ lsnrctl stop

[oracle@obiee12c oracle]$ lsnrctl start

·         DB EM Express:

Open following link in browser

http://obiee12c.oracle:5500/em/
When EM Express prompts you for your username and password, Use to log in as a user with DBA privilege such as SYS or SYSTEM and use the password which we used for Schema password.After login into the Oracle panel, you can see the main interface as Database Home and few screen shot as shown below.