The behavior of Oracle Database 12c is slightly different when it comes to installation of Sample schemas as compared to the previous versions. Though the process of installation of sample schemas or say users in Oracle Database 12c is a bit lengthy but still, it’s worth learning. 

In Oracle Database 12c tutorial 2 I demonstrated the process of unlocking sample schemas in the default pluggable database and by default I mean the one which is made during the creation of a fresh container database. But in today’s tutorial, I will show you how you can install sample schemas in the user-created pluggable database. Thus along with the installation of the sample schema, today in this tutorial you will also learn how to create a user pluggable database.

So without wasting much of your time let’s jump directly over to the tutorial.

As we are installing the sample schemas in user pluggable database thus we will start with its creation and to create a user pluggable database we will use the Database Configuration Assistant also know by the name of DBCA

 

Step 0: Launch the DBCA with Administrative Privilege.

To launch the DBCA with Administrative privilege, open your START Menu and search for DBCA and then Right Click “dbca.bat” file and choose “Run as Administrator”. 

how to create pluggable database in oracle database 12c by manish sharma

Let’s start with the DBCA wizard

Step 1: Database Operation.

Here on the first screen of the DBCA, you have 5 database operations among these, choose the last one which is “Manage Pluggable Databases”.

how to create pluggable database in oracle database 12c by manish sharma

Step 2: Manage Pluggable Databases

In the Next step, you have to select the operation which you want to perform on your pluggable database. As in our case, we want to create a new pluggable database thus we will go with the first option which is “Create a Pluggable database”. 

how to create pluggable database in oracle database 12c by manish sharma

Step 3: Database List

In Step 3 you have to choose the name of the container database in which you want to create your pluggable database.

how to create pluggable database in oracle database 12c by manish sharma

Step 4: Create Pluggable Database

4th step is the most important one because here we will install all our sample schemas in our pluggable database. In normal case, we select the first option and move on to the next step. But in this scenario, Oracle engine will create a pluggable database for you but without any sample schema. 

Since we want to install the sample schemas in our pluggable database thus we will select the 3rd option which is “Create pluggable database using PDB file set”. Choosing this option will enable both its text fields.

In the first field we will have to specify the location of metadata file. The name of the Metadata file is “Sampleschema.xml” and in the second field you have to specify the location of Database Datafile backup. The name of Database Datafile Backup is Sampleschema.dfb. You can find both these files at “%Oracle_home%/assistant/dbca ” directory.

Location of metadata files is
%Oracle_Home%/assistant/dbca
Where %Oracle_Home is the directory where your Oracle Database software is installed.

Both these files comes pre-installed with the oracle database software but in case you don’t have them then check the documentation of your software. 

To select the files click and press the browse button corresponding to fields and then select the files.

When you are done with this click next and move ahead.

how to crate pluggable database in oracle 12c by manish sharma

Step 5: Pluggable Database Option

In this step you can configure the name, storage and Admin user for your pluggable database. You can give name of your choice to your pluggable database. In Storage type you have two options: 

  1. Automatic Storage Management (ASM)
  2. File System

If you have ASM configured on your system then you can go with the first option otherwise choose the second option which is File System.

Next comes the PDB user. Here you can create an administrative user for your PDB. Through this user you can administer your PDB. Let this “lock all existing PDB users” box remain unchecked. Now hit next. 

how to create pluggable database in oracle database 12c by manish sharma

Step 6: Summary

In the last step you can see the summary of your pluggable database. Next you have to hit the Finish button; doing so will start the creation of PDB along with the installation of our sample schemas. 

how to create pluggable database in oracle database 12c by manish sharma

Confirm The Creation Of Pluggable Database

After this process is done you can confirm the creation of your pluggable database by querying v$pdbs view. For example suppose I have created a pluggable database by the name of YouTubePDB then the query will be

SELECT  NAME,  open_mode  FROM  v$pdbs  WHERE  name = ‘YOUTUBEPDB’;

This query will return the row showing the name of your database along with its open mode if pluggable database is successfully created otherwise no row will be returned.

Update TNSNAME.ORA file and add a TNS service in it.

Next for the hassle free login to our newly created pluggable database we have to make a TNS service entry in our TNSNAME.ORA File for that first open up your TNS file. This file normally resides in the “%Oracle_Home%\NETWORK\ADMIN” directory.

Remember in my case the name of the pluggable database is “YOUTUBEPDB” and the service name is also “youtubepdb”. I will use this information to add an entry in my tnsname.ora file. 

Before making any changes in the tnsname.ora file I would suggest you to make its duplicate copy as a backup.

The quickest way to enter a TNS Service to your TNSNAME.ORA file is by copying whole ORCL (in my case it is the SID however this can be different in your case) block and paste it at the end of the file then change its name from ORCL to the name of your pluggable database which you created and the value of SERVICE_NAME tag from ORCL to the corresponding service of your pluggable database. 

Most of the time both the name of your database and its corresponding services are the same. In my case the name of my pluggable database and its corresponding service is YOUTUBEPDB. Thus sample entry will look like this. You can change the value for hostname and port number accordingly. 

YOUTUBEPDB =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = youtubepdb)
   )
  )

how to create pluggable database in oracle database 12c by manish sharma

Info Bite:To know the corresponding service name of your pluggable database use this query 

SELECT con_name AS “PDB Con” ,name AS “Service Name” FROM v$active_services WHERE con_name =’YOUTUBEPDB’;

Next save your TNSNAME.ORA file and close it.

Reload The Listener

Now as we added a new network service to the listener thus in order to make it active we have to reload the listener. To reload the listener, open your command prompt with “Administrative Privilege” and then write

C:/>  lsnrctl  reload;

how to create pluggable database in oracle database 12c by manish sharma

Everything is done. 

Now the only step which remains left is to unlock the users or say sample schemas. For that we need to log on to our pluggable database using sys user.

To log on to the specific pluggable database which in our case is YouTubePDB we have to specify the name of the TNS service while executing connection command using @ sign. Let me show you how.

Sqlplus  sys/oracle@YouTubePDB  as  sysdba.

Here in this connection command I specified the name of my TNS service which we just created in TNSNAME.ORA file using @sign right after the username and password of my sys user.

Unlock HR Schema

Now you are free to unlock all the sample schemas or users in your database. 
For example 
To unlock HR user write ALTER USER DDL as

ALTER  USER  hr  IDENTIFIED  BY  hr  ACCOUNT  UNLOCK;

Similarly you can unlock other sample users also such as SH, OE, PM and IX.
You can now log on to your database using these sample users. The syntax for logging into the database is – 

Sqlplus  username/password  @TNS-Service

For Example

Sqlplus hr/hr@YOUTUBEPDB

That’s all in this edition on How to unlock sample schema in Oracle Database 12c. Hope this blog was helpful. You can watch the video tutorial for better understanding Kindly share it on your social networking and help me reach out to more people. Thanks and have a great day.