Container & Pluggable Database | Oracle 12c

Container & Pluggable Database | Oracle 12c

In this blog we will explore the container database concept in Oracle Database 12c. We are going to create the pluggable database manually & schema under it.


I have installed the Oracle 12c Database in Windows 10 & logged in using sys as dba. Refer this blog for installation & setup Oracle 12c Database Oracle Database 12c Installation & Setup| Windows 10

In connected session V$DATABASE showing database which was created at the time of installation of Oracle Database 12c. If you refer below screenshot column value CDB=’YES’ i.e. its a container database(CDB).

v$database output

Multitenant architecture enable the Oracle database to work as multi-tenant container database (CDB). All Oracle databases before 12c were non-CDBs.

Container Database (CDB) has following containers in it :

  • Root: Container is named as CDB$ROOT. Oracle supplied metadata & main database holding all control files, redo etc.
  • Seed PDB: Named as PDB$SEED. System supplied template that CDB can use to create new PDBs.
  • User-created PDB: Pluggable database created by user for business requirements. Actual schemas will be created under here for code & data.

You can run below command to check your connected container. I’m running this command in my sys as dba connection. Here it is showing that you are connected to the CDB$ROOT i.e. root container.

sho con_name;
sho con_name ouput

Create Pluggable Database :

Here we will see how we can create pluggable database under our root container. See below sample SQL for creating manually the pluggable database. You have to provide DB name, admin user name i.e. which will be admin of your PDB.

You can also create PDB using dbca. Just do Windows search.

CREATE PLUGGABLE DATABASE <database-name> ADMIN USER <username> IDENTIFIED BY <password>
DEFAULT TABLESPACE USERS
DATAFILE '<location>'
SIZE <size> AUTOEXTEND ON
FILE_NAME_CONVERT=(
'<location of pdbseed pdb>',
'<location of new pdb>');
Pluggable Database Setup

I have created the pluggable database using my sys connection i.e. root. You can check your data files created in the provided location.

Datafiles

After creating the pluggable database you can check its initial status should be present as MOUNTED. Check the data in v$pbs

v$pdbs output

You have to fire below command to put your database in read write mode.

alter pluggable database PDB open;
v$pdbs output

Now your pluggable database is altered & its showing “Read Write” mode. You can also check the status of PDBs using sho pdbs;

Creating Pluggable Database Schema:

In this step we are going to create the database schema under our pluggable container. Just need to alter the container.

alter session set container=PDB;
sho con_name;

CREATE USER DEV_SCHEMA IDENTIFIED BY DEV_SCHEMA;
GRANT CONNECT, RESOURCE, DBA TO DEV_SCHEMA;
GRANT CREATE SESSION TO DEV_SCHEMA;
GRANT ALL PRIVILEGES TO DEV_SCHEMA;

Setting up SQL Developer Connection:

Refer this blog for setting up the new sample schema ‘Customer-Orders’ : Customer-Orders Sample Schema | Oracle 12c


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Database 12c Installation & Setup| Windows 10

Oracle Database 12c  Installation & Setup| Windows 10

In this blog we will see how we can setup the Oracle Database 12c on Windows 10 – 64 Bit PC. First you have to download the latest Oracle Database 12c using below link.

https://www.oracle.com/database/technologies/oracle12c-windows-downloads.html


I have downloaded the Oracle Database 12c (12.2.1.4.0) version.

Go to the database directory & start the setup. It will automatically start the Oracle Universal Installer.

Here creating database as container database
Setup is in-progress
Checking the status of database after installation using lsnrctl status
Oracle Database 12c is now up & running.

Refer this blog for creating pluggable database & schema : Container & Pluggable Database – Oracle 12c


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Data Integrator 11g (ODI) Repositories | Master & Work Repository | Connection & Setup

Folks,

In this blog we will learn about Oracle Data Integrator Repositories.

You can refer this blog for ODI 11g Installation & Setup on Windows OS.


Suppose you are just done with the ODI installation on your machine:

Now you are going to use the ODI Studio to develops, operate & administrate some ELT project work. But you need some space/location to start working & to save your all work. Basically you need ODI repository to store the information you are going to develop, configure or used by the ODI.

There are two types of ODI repositories: Master & Work Repository

  • Objects developed, configured, operated/used by the ODI stored in one of these two types of repositories.
  • Repositories are accessed in client/server mode by various components of the ODI architecture.
  • Master Repository is usually associated with multiple Work Repositories.
  • Each Work repository can associate with only one Master Repository.
  • Work repository can either be Development Work Repository or Execution Work repository. (We will discuss this point later in this blog)

repo1

Refer this YouTube video to setup Master & Work Repositories in ODI 11g using Oracle.


Master Repository:

Master Repositories are used to store:

  • ODI Connection Information: JDBC URLs, LDAP information, user names and passwords for source/target connections.
  • ODI Security Information: ODI users names and passwords, ODI users privileges and profiles information, if security is handled by ODI.
  • Version Information: When a new version of an object is created in the ODI, that information is saved in the Master Repository.

Information contained in the Master Repository is maintained with the Topology Navigator and the Security Navigator in ODI Studio.

  • Data contained in master repository is mostly static data and will be going through minimum changes, whenever any topology or security information added/updated.
  • ODI components access the Master Repository, whenever they need the topology and security information data stored in it.

Work Repository:

Work Repositories are used to store:

 

  • Project Information: Folders/Sub-folders, packages, interface, procedure, variables, sequences, functions,  knowledge module etc.
  • Models: Data Store structures and metadata, fields, columns, constraints etc.
  • Load Plans & Scenarios: Load plans information & scenarios.
  • Operations:  Execution details, scheduling information, and logs.

The contents of a Work Repository are managed by using Designer and Operator. They are also accessed by the agent at run time.

When a Work Repository is used only to store execution, operations logs information (typically for production environment), it is called an execution work repository. Designer Navigator is disabled in this execution repository.

Work repository consists of two sub-types: Development Work RepositoryExecution Work repository.

Development Work Repository:

  • Repository which contains all the objects which are used for developing the development project i.e. packages, interface, procedure, variables, sequences, load plans, data stores etc.
  • Designer Navigator & Operator Navigator both are available for Development Work Repository.
  • Usually, used for development projects.

2.PNG

Execution Work Repository:

  • Repository which stores only the scenarios (executable files) & execution information, no source code & designer information available here.
  • Usually, used for production and test environments to make sure that source code will not be modified in a live environment.
  • Operator Navigator is available, but Designer Navigator is restricted for Execution Work Repository.

3

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Refer this YouTube video to Install ODI 11g on Windows OS.


Thanks!

Happy Learning! Your feedback would be appreciated!

 

 

Oracle Database 11g Installation & Setup| Windows OS

Oracle Database 11g  Installation & Setup| Windows OS

Folks,

In this vlog we will learn how to download, install & setup Oracle Database 11g on Windows 10 OS.

Step by step installation guide:

You can download the Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (x64) using this Link

Refer this blog setup your first schema/user: Oracle Database 11g | Create User | Check Instance Name| Host Name | Services | Parameters |


Thanks!

Happy Learning! Your feedback would be appreciated!

Accessing Database (MS SQL Server/Oracle/MySQL) from R!

Folks,

In this blog we will learn how to access Oracle, MySQL & SQL Server databases from R!


Accessing Oracle Database using RJDBC Package

Below is my database hosted on AWS Cloud. See here how to setup Oracle Db on cloud (free tier).

11

R Packages required:-  install.packages(“RJDBC”)

Also download Oracle JDBC Drivers from here Link. After downloading, copy file location.

location.png

Commands:


driver <- JDBC("oracle.jdbc.driver.OracleDriver"," <paste JDBC Driver file location>")

dbConnect(driver , "jdbc:oracle:thin:@Host:Port:ServiceName or SID", "<username>", "<password>")

code1

R users have few more choices to access their Oracle Database: Using RODBC and ROracle packages.


MySQL Database

Here is my database hosted on AWS Cloud. See here how to setup MySQL Db on cloud (free tier).

1

a) Accessing MySQl Database using ODBC:

R Packages required:-  install.packages(“RODBC”)

For Windows: Go to start, search ODBC. Follow below steps of setup.

1

Click on “Add”.

2

Select MySQL ODBC Driver. If not present, then download using this link.

3

Provide your server details, username & password. Test your connection. Note down Data Source name.

4

Commands:


my_conn <- odbcConnect("<ODBC Data Source Name >")

sqlQuery(my_conn, paste("<Query>"))

code

b) Accessing MySQL DB using RMySQL Package:

R Packages required:-  install.packages(“RMySQL”)

Commands:


my_conn <- dbConnect(MySQL(), user="<username>", password="<password>", database= "<Database name>", host="<your host>")

result<- dbSendQuery(my_conn, "<Query>")

dbFetch(result)

code 2.png


Accessing SQL Server Database using ODBC

Here is my SQL Server database hosted locally.

server.png

a) Accessing SQL Server using ODBC:

For Windows: Go to start, search ODBC.

1

Follow below steps to setup ODBC datasource.

output_KUL4JC.gif

Commands:


my_conn <- odbcConnect("<Data Source Name>", uid="<username>", pwd="<password>")

sqlQuery(my_conn, "<Query>")

code2.png

b) Accessing SQL Server DB using RJDBC Package:

Suppose SQL Server database hosted on cloud. We can use below commands to connect.

R Packages required:-  install.packages(“RJDBC”) & Download SQL JDBC Drivers file from internet.


driver <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
"<location of SQL JDBC Drivers file on computer>")

conn <- dbConnect(driver , "jdbc:sqlserver://<serverName>", "<userID>", "<password>")

result <- dbGetQuery(conn, "<Query>")


Thanks!

Happy Learning!

Connecting MySQL DB Instance present on AWS Cloud

Folks,

For creation of any Amazon RDS DB Instance (Free Tier), kindly visit this blog: Link

In this blog we will learn how to connect MySQL Database present on AWS Cloud.

Here is my MySQL DB Instance in “available” Status. Please note the End Point, which is basically the HOST.

1
RDS Dashboard

Step for adding new Inbound Rule for allowing connection on port 3306.

Select MySQL Instance & Click on “Instance Actions“->”See Details“.

2

After that click on “Security Groups“.

3

Now click on the “Inbound Rules” in Security Groups page.

4
Security Groups Page

Create a Inbound Rule for Type: MYSQL, Protocol: TCP , Port: 3306 Source: Anywhere. So that you can connect this instance from any machine. After creation of rule click on Save.

Security Group is basically allowing the connections on the port.

5

After Saving Inbound Rule. Go to RDS Dashboard.

1
RDS Dashboard

Now MySQL Instance is ready to connect & use it.


 

Steps for connecting MySQL Database: Using CMD

Install MySQL Server on your machine. Copy the location on bin folder in program files.

Also check your firewall settings for port 3306.

7

Also check the MySQL service is running properly or not.

6

Open Command Prompt: Use this command: cd <bin folder location> then Press Enter.

Example:  cd C:\Program Files\MySQL\MySQL Server 5.1\bin

Now you are in bin folder.

8

Use below command in cmd & it will ask for password.

mysql – h <host> -P <port> – u <username> – p

Where <host>: Your End Point, <post>:3306, <username>: Master username

9

Now we are connected to the MySQL DB Instance.

10


Steps for connecting MySQL Database: Using MySQL GUI

I’m using SQLyog GUI. Please provide below details.

01

Provide your password & test the connection.

02

Click on connect to connect the database.

03

Now we are connected to the MySQL DB Instance.


Thanks!

Happy Learning!

Oracle DB on Cloud (AWS Free Tier)

Folks,

AWS Free Tier (Link), the Amazon RDS Free Tier helps us to get started with a database instance in the cloud. Free for 12 months starting with the date on which we create our AWS account.

We can use this to develop new applications or simply gain hands-on experience with Cloud Computing. It is easy to set up, operate, and scale DB.

Database Available:- MySQL, MariaDB, PostgreSQL, Oracle & SQL Server.

Steps for creating your own: Oracle DB Instance on Cloud (Free Tier)

Login AWS with your amazon account. Link

0
Amazon Web Services Login Page

After login AWS redirect to this below Home Page.

Select “RDS” in the Database Section. RDS –  Relational Database Service.

1
AWS Home Page

After selecting “RDS” in the Database Section. Below RDS Dashboard comes up.

Click on “Launch a DB Instance” in this screen.

2
AWS RDS Dashboard

Now from here the main setup starts…


 Step 1: Select Engine: Select the DB Engine:

I’m selecting the Oracle EE Database Engine.

3

Step 2: Production Or Free Tier

For Free Tier: Select Option Second: “Dev/Test”

4

After that click on Next Step.

Step 3: Specify DB Details:

Select check box for options available for free tier RDS.

5

Provide DB details here like master username & password. Please note down these details for future reference.

6

After that click on Next Step.

Step 4:  Configure Advanced Settings:

Use default setting here for free tier service.

7

Now we can launch the Instance.

8

After launching: Db instance is being created.

9

Go to the RDS Dashboard: Instance Tab:  You can check your instance here.

10
DB Instance Creation is in process

After some time…

Oracle DB Instance is ready. Note down your Endpoint, which is basically the HOST.

11
Db Instance is available now

My End Point: shobhitdbdemo.cbxoxihdzrvi.us-west-2.rds.amazonaws.com:1521


Steps for Connecting Oracle Database: Windows

Install Oracle Client on machine. Also check your firewall settings for port 1521. Open Command Prompt & use below command to connect DB.

sqlplus username/password@host:port/service 

Where username: Your Database Master Username in Step 3

Example:

12
12

Now you can create your own database & other database objects.


Thanks!

Happy Learning!