Creating Pluggable Database from SEED in Oracle

Back to: Oracle DBA Tutorials

Creating Pluggable Database from SEED in Oracle

In this article, I am going to discuss Creating Pluggable Database from SEED in Oracle with Examples. Please read our previous article where we discussed Creating Pluggable Databases in Oracle with Examples.

Creating Pluggable Database from SEED in Oracle

We have a container database. The container database should contain one root container. It should also contain the SEED database. Now, we can create a pluggable database from the SEED pluggable database. We do this by using create pluggable database statement.

So, in SQL*Plus or SQL developer we can execute this statement. By running this statement, it will copy the data files from the seed database location to the new pluggable database location. This will also create new SYSTEM and SYSAUX tablespaces. This will create a default schema and common users. SYS user will be a super user. SYSTEM users can manage the pluggable database. This will create the services automatically. We already discussed that each database should have a service on its name. So, when we create a pluggable database, it automatically creates a service.

Prerequisites for using create pluggable database statement:

• The current container must be root. We should be logged in to the CDB$ROOT database.
• We must contain create pluggable database privileges.
• The container database must be in READ & WRITE mode.

So, let’s proceed to create the pluggable database using SEED step-wise.

Step 1: Check the data files in the pdbseed pluggable database.

Open the Linux machine and open the file manager and go to the below location.

Location: /u01/app/oracle

We have a folder called oradata. This folder contains all the data files which include both container and pluggable databases. Go inside this folder. There is a folder with the instance name. If we go inside that folder. We can see all the data files and two folders named jayapdb and pdbseed. These folders contain the datafiles of the pluggable databases in the container database.

If we open this pdbseed folder we can see the below files.

These data files are present in the pdbseed pluggable database. We will be copying all these files to the new pluggable database.

Step 2: Connecting to the ROOT database

Make sure to start the database and the listener up and running. Once the database is started connect to the database as sysdba in the SQL developer. Once you are connected to the database check the container name using the command “show con_name”.

We are in the CDB$ROOT database.

Step 3: Check the list of databases present in the container database.

We use the query to check the list of pluggable databases.

Query: select con_id,name,open_mode from v$containers;

We can see that we have CDB$ROOT. In order to create a pluggable database, the CDB$ROOT database should be in READ WRITE mode. We have PDB$SEED it is READ ONLY. We have a pluggable database JAYAPDB which is also READ WRITE.

Step 4: Connect to the SEED database and check the users present in the SEED database.

Let us go ahead and check the PDB$SEED database and check the users present in SEED. We have to connect to the PDB$SEED using the command “alter session set container=PDB$SEED”.

Command: alter session set container=PDB$SEED;

So, now we are connected to the PDB$SEED. Let us go ahead and check the users present in the PDB$SEED.

Query: select con_id, username, DEFAULT_TABLESPACE, common from cdb_users;

We can see all the users present in PDB$SEED which is having con_id as 2. If we use the view cdb_users it gives us the same output.

Query: select username, DEFAULT_TABLESPACE, common from dba_users;

From the output, we can see the same number of users even though we are using a different view. Let’s check the number of users using count rows.

Step 5: Check the tablespaces and data files for the pdbseed database.

Let’s run a query to check the data files present in the pdseed database.

Query: select con_id, file#,name from v$datafile;

We can see three datafiles system01.dbf, sysaux01.dbf, undotbs01.dbf present in the pdbseed database. Now, let us check the same in the file manager in the pdbseed folder. The path is as /u01/app/oracle/oradata/JAYADB/pdbseed/

From the folder, we can see the same data files in addition to tempfile. Now, let’s go ahead and check the tablespaces present in the pdbseed.

Query: select * from v$tablespace;

We can see there are 4 tablespaces. System, sysauz, undotbs1, Temp tablespaces. When we create a new pluggable database using pdbseed these tablespaces and the datafiles will be copied to the new pluggable database. But, before we create the new pluggable we have to switch back to the CDB$ROOT database and create the pluggable database from the CDB$ROOT database.

Step 6: Switch back to CDB$ROOT and create a new pluggable database folder.

We have to create the pluggable database from the CDB$ROOT database. So, let us connect to the root database.

Command: alter session set container=cdb$root;

We have to create a directory for the new pluggable databse.

Command: mkdir /u01/app/roracle/oradata/JAYADB/jayapdb1

Go to the below path and open the terminal from the path.

Now, that we are in the path use the command to create the folder using above command.

We have created the folder using the command and we can see the folder is created in the file manager.

Step 7: Creating a Pluggable Database.

Query:

Create pluggable database jayapdb1 
Admin user pdb1admin identified by goodluck
Roles=(dba)
DEFAULT TABLESPACE users
Datafile ‘/u01/app/oracle/oradata/JAYADB/jayapdb1/users01.dbf’ size 250M autoextend on
FILE_NAME_CONVERT=(‘/u01/app/oracle/oradata/JAYADB/pdbseed/’,
    ‘/u01/app/oracle/oradata/JAYADB/jayapdb1/’)

We are creating the pluggable database name as “jayapdb1” and creating the admin user as pdb1admin, and the password for the admin user pdb1admin is good luck and the roles that are going to assign to the user pdb1admin is dba roles. These are the first important three lines and the default script from oracle.

We have to provide a default tablespace for the new pluggable database. The default tablespace will contain the actual data for the new pluggable database. We are creating the user’s tablespace. We can use whatever name we want but we are creating it as users. If we are creating the tablespace we have to create a datafile as well which is going to store the data that will be stored in the tablespace. So, we have to mention the datafile line data file with the tablespace users is ‘/u01/app/oracle/oradata/JAYADB/jayapdb1/users01.dbf’ and size is 250M and after the 250M, the data file will be auto-extend.

FILE_NAME_CONVERT:

The FILE_NAME_CONVERT is the clause that is used to determine how the database generates the names of the files such as data files and Walter files for the pluggable databases. This determines how the data files are created. The first phrase should be the name of the pdbseed location and the second phrase should be the path of the newly created database path. We always use this command to create a new pluggable database using the pdbseed database.

So, the overview would be we have to mention the new pluggable database name, admin name and the password for the admin name and the roles that need to be assigned for the admin user and the default tablespace, and the datafiles for the default tablespace and need to mention the file_name_convert.

Note: Please do remember to log in to CDB$ROOT user.

The pluggable database creation will take around 2 minutes to create. Once the database is created we will get displayed as Pluggable database JAYAPDB1 created.

Step 8: Check the data files of the new pluggable database in files

So, now go ahead and open the Linux machine and open the file manager and go to the below path.

We are in the path /u01/app/oracle/oradata/JAYADB/jayapdb1/ and we can see the datafiles sysaux01.dbf,system01.dbf,undotbs01.dbf, and the tempfile and we will have the users datafile as well. Let’s go ahead and check the list of pluggable databases.

Query: select con_id,name,open_mode from v$containers;

We can see there is a new entry for the newly created pluggable database JAYAPDB1. The state of the database is mounted state.

Step 9: Connect to the new pluggable database and open the database and check the users in the pluggable database.

The state of the database is mounted. Let’s connect to the pluggable database and open the database.

Command: alter session set container=JAYAPDB1;

Now, that we are connected to the pluggable database let’s open the database using the command “alter pluggable database open”;

Now, that the database is open let’s go ahead and check the list of users in the new pluggable database JAYAPDB1.

Query: select con_id, username, DEFAULT_TABLESPACE, common from cdb_users;

We can see there is a new user named pdb1admin because we created it during the new database creation. This pdb1admin is the admin user who manages the admin privileges of the database. Let’s check the number of users.

We can see the user count is 37 because we have created the new admin user pdb1admin. We can check the list of users with the view dba_users.

Query: select username,DEFAULT_TABLESPACE,common from dba_users;

Here also we can see the same users as the cdb_users.

Step 10: Check the tablespace and datafiles for the pluggable database jayapdb1.

Now that we got all the users from the pdbseed. Let’s go ahead and check the datafiles in the new pluggable database.

Query: select con_id,file#,name from v$datafile;

So, we can see there are four datafiles named system, sysaux, undo, users datafiles. Let’s now check the tablespaces.

Query: select * from v$tablespace;

We can see there are five tablespaces system, sysaux, undo, temp, users tablespaces. All these tablespaces are copied from pdbseed except users because we have created users tablespace while creating the new pluggable database.

We have learned how to create a new pluggable database. In the next article, I am going to discuss the Connection to Pluggable Databases in Oracle with Examples. Here, in this article, I try to explain Creating Pluggable Database from SEED in Oracle with Examples and I hope you enjoy this Creating Pluggable Database from SEED article.