Oracle Database 12c Tutorial 4: How to install sample schemas / users in Oracle Database 12c

Oracle Database 12c Tutorial 4: How to install sample schemas / users in Oracle Database 12c

What’s up internet? Welcome back once again
I am Manish from Today in this Oracle Database tutorial we
will learn how to install sample schemas in Oracle Database 12c. And by sample schemas
I mean HR, SH OE and many more. As I have already mentioned in my previous
tutorials that in Oracle Database 12c only pluggable database can have the user data
and not the container database. Thus we can install sample schema only in a pluggable
database rather than the container database. Therefore today in this tutorial we will create
a pluggable database in which we will install all our sample schemas.
In case you want to learn how to unlock sample schema in default pluggable database you can
refer to my tutorial 61, link is in the description box. By default pluggable database I mean
the one which is created either during the installation of the oracle database 12c or
during the creation of a new container database. Ok now let’s move ahead and start the tutorial.
As we are going to create a new pluggable database thus we will use “Database Configuration
Assistant” also know by the name of DBCA, a utility provided by oracle itself. I would
like to mention here that I am using Oracle Database 12c Enterprise Edition in case you
were wondering. To launch DBCA utility just click and open
your start menu and search for DBCA. And then right click on DBCA.bat file and choose run
as administrator. Here on the first screen of the DBCA you have
5 database operations among these, choose the last one which is “Manage Pluggable
Databases” and then click next. 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”. Select the first option and click next.
In the third step you have to choose the Container Database in which you want to create your
pluggable database. In my case I have only one container database which ORCL so I will
go with this one only. Which is already selected ok. Click next and move ahead
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, where
in the first field we will have to specify the location of metadata file. The name of
the metadata file is “Sampleschema.xml”. This file comes pre-installed with the oracle
database software but in case you don’t have this file then check the documentation
of your software. To select the metadata file, click the browse
button by default it will open location of the “Templates” folder and as you can
see, here is our sampleschem.xml file. But in case it doesn’t, then let me tell
you where you can find this template folder, which has our sampleschema.xml metadata file.
For that first I have to zoom out a little bit. And here we go. Now first go to your
“Oracle Home” Directory. And here I am. Right now I am in my dbhome_1 directory which
is my Oracle Home in your case it may be different. Now next you have to go to the “Assistants”
directory. And inside the assistants directory go to this “dbca” directory, and as you
can see this dbca directory has our “templates” folder which has the metadata file “Sampleschema.xml”.
Let me show you and here it is. Now you know the location of your metadata
file and can easily navigate to it so let’s get back to our dbca window. Let me set zoom
before moving ahead. I think now its ok nicely focused and clearly visible.
Now here select “Sampleschema.xml” as you can see software has automatically filled
the location of data-file backup. If it doesn’t then choose the “Sampleschema.dfb” file
from the same templates folder. Once you are done with this just click next
and jump to the next step. Here in the 5th step you will have to do some
configuration setting for your pluggable database. Let’s do it.
First I will name my pluggable database as YouTubePDB. Next is storage if you have configured
ASM then you choose ASM here and configure accordingly but I will let it be on file system.
In the database location field you have to give the location where oracle can create
and save files such as data files, log files and so on and so forth. I will again let it
be on default. But if you want then you can change it and give a location of your choice.
Next is PDB user where you can create an administrative user for your PDB. Through this user you can
administer your PDB. Let me quickly make a PDB admin user.
Let this “lock all existing PDB users” box remain unchecked. Now hit next and move
ahead. Here you can see the summary.
Next you have to hit the Finish button; doing so will start the creation of PDB along with
the installation of our sample schemas. Once this installation is finished we will
have to do the further configurations. This will gonna take time thus I will fast forward
this process. Ok creation of pluggable database is done.
Hit ok and then hit close Now let’s quickly check whether our pluggable database is created
or not. For that we have to log on to our database using sys user.
Here I am, now let’s query v$pdbs view to check the existence of our pluggable database.
SELECT NAME, open_mode FROM v$pdbs WHERE name=’MANN’
As you can see we have our pluggable database created and it’s open for read and write operations.
Next we have to update the TNSNAME.ORA file and add a TNS service entry in it for our
pluggable database YouTubePDB. First open your TNSNAME.ORA file for that
you have to go to your Oracle Home. Here I am now go to the NETWORK folder and next go
to ADMIN folder. Here is our TNSNAME.ORA file. Open this file with any editor of your choice.
Next to make a TNS service entry copy this whole ORCL block and paste it at the end of
the file. Now change the name of this block from ORCL to YouTubePDB which is the name
of our pluggable database and also change the value of this SERVICE_NAME tag from ORCL
to YouTubePDB. In most of the cases the name of database
and its associated service is same but if you want you can confirm it using v$active_services
view. To know how you can confirm the name of associated service please visit my blog
on the same topic link is in the description box.
Save and close this file. As we just changed the listener settings so
to make them active we have to reload the listener. Open your command prompt with administrative
privilege. Ok here write 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 is YouTubePDB in our case we have to specify the name of the TNS service while
executing connection command using @sign. Let me show you how.
Sqlplus sys/[email protected] 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. Let’s hit enter and connect to the database.
Here we are. Let me clear the screen for you first.
Now I will unlock the HR, SH and OE sample schemas using ALTER user DDL commands.
ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK; HR account is unlocked. Let’s unlock other
OE also unlocked. Similarly you can unlock other sample users
also. Let’s confirm whether we have successfully
unlocked these accounts or not by logging on to the database using all these accounts
one by one. Conn hr/[email protected]
Connected, Let me quickly show you the user. SHOW USER;
User is HR which means user HR is successfully unlocked. Now let’s check SH user
Conn sh/[email protected] Connected SHOW USER;
As you can see the user is SH which means SH is also unlocked
Conn oe/[email protected] OE also Connected let me quickly show you
the user also SHOW USER; User is OE which means OE is also successfully
unlocked. So guys that’s how we install sample schema
or say sample user in oracle database 12c. I have also done a video on how to unlock
HR user in Oracle 12c you can also watch that tutorial. The link is in the description box.
You can also check my blog on the same topic for more in-depth knowledge. Again, all the
links are in the description box. With this I will wind up today’s tutorial
on How to install sample schema in oracle database 12c or should I say how to create
pluggable database with sample schema in oracle database 12c.
Hope you have learnt something and enjoyed watching. Please tell me how you like this
video for that you can either leave a comment or write me a tweet @RebellionRider. And if
you liked this video then please hit that thumbs up button also don’t forget to subscribe
to my channel. Will see you soon with another hot and crispy
oracle database tutorial, till then take care. This is Manish from RebellionRider.

Danny Hutson

60 thoughts on “Oracle Database 12c Tutorial 4: How to install sample schemas / users in Oracle Database 12c

  1. Thank you Manish. The way you explain is very clear and easily understood. I'm totally beginner in Oracle environment yet got no difficulties in following your tutorial. Keep the good job!

  2. Hi,
    Thank you for the in-depth tutorial. I am  fresh to Database admistration.

    However do you know what might cause the error during install:
    Error While restoring PDB backup piece
    Activity log does not give much information.
    ALTER SYSTEM SET _catalog_foreign_restore=TRUE SCOPE=BOTH;
    ALTER SYSTEM SET _catalog_foreign_restore=FALSE SCOPE=BOTH;
    Installer exits after the error

  3. HI Manish? Is it the same procedure for the BISAMPLE schema? I'm following the tutorial on

  4. dear sir please how to install Forms and report builder for oracle 12c because i install it but i can see the interface for it how to please. thanks

  5. I have finally found the right bus stop having walked for miles. Thanks man. I really appreciate your work. What a relief?

  6. Hey Manish.. Thanks man. Your video helped me a lot. I appreciate your effort for making this video.You explained all steps clearly..

  7. Hi, when I connect to sql usingsys/[email protected] as sysdba, the following error occurs.

    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

    What do I have to do

  8. hi Manish good video.
    I have a doubt..
    my production db is on redhat linux now i want the same to be configured on oracle linux.
    How can i proceed..

  9. Hello Manish,

    Your video is clear and well explained; however, there are somethings that are not matching according to your whole installation process videos I've been following all along.

    1. In "Database List" Step (3 of 8), I am being forced to specify SYSDBA Credentials whereas your installation is not being required.

    2. In this step, The "Database Configuration Assistant" is telling me that "Container Database 'ORCL' is not open". What does this mean?. Despite this, I chose to continue.

    3. In "Pluggable Database Options", after having specified "PDB Storage" and "PDB User" as well, when trying to click on "Next", I get this error wich is hindering my installation: "CONTAINER DATABASE VALIDATION CHECK FAILED", whereas in your installation step, that issue never happens.

    I've spent several hours investigating though the web and still, I've still haven't found any useful answers related to this matter in particular. I'm a newbie in Oracle and I'm starting from scratch. Honestly, dealing with Oracle, is harder than I was told about.

    Still I'm not going to give up, so easily.

    Thanks for your help anyway.

  10. I have been pulling my hair out for all sorts of answers regarding 12c. Most of the time, I end up at your tutorials. They are straightforward, simple without extraneous technical jargon. My only "complaint" is that sometimes you talk so fast I don't always catch what you are saying. I just try to keep up on the visual stuff. Anyway, this tutorial finally answered my questions and I was able to open the rest of the sample schemas beside hr and move on with my OCA studying.

  11. great. everything is working fine. i restarted my system and i cannot login am not able to login into oracle. am getting the error ORA-12541 and sometimes am getting the error ORA-01033.

  12. Hi Manish,

    When I try to connect to sql using system/[email protected]{MYPDBFILENAME} as sysdba , this error occurs,

    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

    How to resolve it. Followed each step as shown in the video

  13. I'm a noob in Oracle Database, you helped me with the uninstall of Oracle Database in Windows and now you helped me with this, +1 subscriber, thanks Manish!! 😀

  14. I am getting this :
    Maximum PDBs limit reached for the container database. Creating more than one PDB requires Enterprise Edition license. 🙁

  15. Manish, thank you for your teaching, you are good!. Can you please help me? after I type: sqlplus sys/[email protected] as sysdba give an error: ORA-12514: TNS: listener does not currently know of service requested in connect descriptor.
    Thank you for your time.

  16. sir I have one personal request… I am an oracle apex developer, can you provide also a instructional video for configuring and running an oracle apex within the PDB of Oracle12C with the Oracle Listener or either embedded pl/sql gateway. Thanks.. your vids really helps a lot.

  17. Manish, I just downloaded Oracle 12c R2 on my Windows 10 laptop. I was able to unlock the HR schema but I do not see sampleschema.xml in the location you mention in your video. Is there somewhere I can download this file?

  18. Manish, how is the process of installing the sample schema in oracle 12c on a non container DB ( like the previous DB architecture, that is supported in 12c installation) . Can you comment on it, if you chose 1) Create a database with default configuration and not the 2) Advanced Mode. This DB was created using dbca with the "Container DB unchecked".

  19. HI while installing Oracle 12c release in my PC I am getting following error "add acl for user exception_desc". Can you please help me with this error.

  20. Superb Rebellion Rider … no husk no delay only subject … what a presentation… Splendid job … for aspirants like us

  21. Hi Manish, I don't find Sampleschema.xml metadata file in the "ässistants""folder, I am using 12cr2. any help please.

  22. Hi Manish,

    Right, but now the question would be how to execute that scheme in Toad for example. How to do the configuration in Toad or SQLDeveloper?
    Thank you.

  23. Can you show how sample schemas can be installed by running the mksample.sql file in the command line

  24. I keep getting "ORA-12154: TNS:could not resolve the connect identifier specified" because I think my username/password must be wrong. Where can I find this information if I lost it?

Leave a Reply

Your email address will not be published. Required fields are marked *