Shifting from Container to Pluggable Database
In this article, I am going to discuss Shifting from Container to Pluggable Database. We have learned about the pluggable database previously and we have created a container database and one single pluggable database. Let us discuss how to connect to those databases. These container and pluggable databases were started from oracle 12c and continued for 18c,19c and 21c as Multitenant Architecture. We will have a big container CDB$ROOT database and inside this ROOT database, we have a pluggable database. For further information about multitenant architecture, you can check here Oracle Multitenant Architecture – Dot Net Tutorials
Open the Linux machine and log in as an oracle user and open the terminal. Log in to the database as “sysdba”.
Once you have logged in to the database always check the database name using the command “Show con_name”.
After checking for the database name. We need to check how many pluggable databases are present inside this ROOT database. There is a command to check how many pluggable databases were present inside the container root database.
Command: show pdbs
Currently, we have two pluggable databases installed in the ROOT database. One of the databases is PDB$SEED. It has the container ID as 2 and the mode of the database is READ_ONLY mode. We have discussed this before. This is the database we use to create another pluggable database. This database is not meant for any other purpose. We cannot make any changes to this database because this database is in READ_ONLY mode.
We have another container database that presents the database that we created during database creation. It has container_id as 3 and the mode of the database is in the MOUNTED state because we have closed the database and no other user can log in to this “JAYAPDB” database.
Let us try to make the pluggable database “JAYAPDB” open. We use the following command to open the database. The syntax will be as follows “alter pluggable database pluggable_database_name open;” and press enter.
Command: alter pluggable database JAYAPDB open;
Now the pluggable database is open for all the users. Let us use the same “show pdbs” command to check the mode of the database.
For example, let us assume that there are hundred pluggable databases present inside this container database. We cannot open each and every database by using the above command. So, in this case, there is an alternative command which will open all the pluggable databases present inside the container database.
Command: alter pluggable database all open;
Until now we have learned how to open the pluggable database. Let us try to close the pluggable database. The command is almost similar to how we open the pluggable database with slight modifications. After running the command check for the status of the pluggable database using the command “Show pdbs”
Command: alter pluggable database JAYAPDB close;
You can see the status of the database is again changed to a MOUNTED state after closing the pluggable database.
Now, let us try to learn about how to shift from the container database to the pluggable database. There is a command that is used to move from the container database to the pluggable database. The syntax of the command is as follows “alter session set container =pluggable_database_name” and press enter.
Command: alter session set container =JAYAPDB;
Once the command is executed check the name of the database using the command “show con_name”. You can see the database name is JAYAPDB. The status of the database is closed because we have closed the pluggable database above. Let us try to open the pluggable database. The command is almost the same but no need to enter the pluggable database name because we are already present inside the pluggable database. If we are connected to any other database and need to open another pluggable database then we use the previous command.
Command: alter pluggable database open;
Once the command is executed check the status of the database using the command “Show pdbs”
Here you can only have one pluggable database because “JAYAPDB” is the only pluggable database and there are no other pluggable databases present inside this “JAYAPDB” pluggable database. Now, let us try to connect back to the CDB$ROOT database. We use the same command as previously while moving from the ROOT database to the JAYAPDB database.
Command: alter session set container=CDB$ROOT;
You can see that we have moved back to the CDB$ROOT database.
We have learned about how to open and close a pluggable database and how to move from the container database to the pluggable database and from the pluggable database to the container database. In the further session, we will try to understand how to create a new pluggable database and how to drop a pluggable database.
In the next article, I am going to discuss Starting and Stopping Listeners in Oracle. Here, in this article, I try to explain Shifting from Container to Pluggable Database and I hope you enjoy this Shifting from Container to Pluggable Database article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.