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”.
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”.
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”.
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.
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
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.
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:
- Automatic Storage Management (ASM)
- 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.
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.
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.
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = youtubepdb)
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;
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.
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
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.