Create local user in oracle pluggable database

In this short article, I will give a brief idea about how to create a local user in a pluggable database and how to check its status as well.

Basically, with this multitenant architecture concept, there are two types of users common user and local user, common users are shared across multiple pluggable databases while local user is specific to a particular database, here we will discuss how to create a local user

SQL> alter session set container=technopdb; 

Session altered.

 

SQL> create user techno_user identified by test123;

 

User created.

 

SQL> grant create session to techno_user;

 

Grant succeeded.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 TECHNOPDB                      READ WRITE NO

SQL>

SQL> select username, con_id from cdb_users where username='TECHNO_USER';

 

USERNAME                      CON_ID

------------------------- ----------

TECHNO_USER                        3

 

SQL> select d.username, c.con_id,p.name from dba_users d, cdb_users c , v$pdbs p where d.username='TECHNO_USER' and d.username=c.username and p.con_id=c.con_id;

 

USERNAME                      CON_ID NAME

------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------

TECHNO_USER                        3 TECHNOPDB

 

SQL>

Here we can check, how to connect to pluggable database using  local user

if you are trying it from your local machine/desktop then below command will work fine, you just need to have ip address and port number and if you are using tnsnames then mention the in the connection string

C:\Users\****>sqlplus techno_user/test123@192.168.0.107:1521/technopdb

 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 15 16:29:48 2021

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL>

SQL> select * from global_name;

 

GLOBAL_NAME

--------------------------------------------------------------------------------

TECHNOPDB

 

SQL> show user

USER is "TECHNO_USER"

SQL>

Read More

Save state of oracle Pluggable database (PDB)

 Oracle has introduced to save state of pluggable database in order to save time, effort and efficiency, suppose you want to keep pluggable database to open state even after restart of root container database then you can simple instruct to database the same.

Let see, how we can do this with example, first we see how to save state for open state and discard it as well

Currently pluggable database is in open mode and we want to save the same state after restart, this how we can do it

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

no rows selected

 

SQL> alter pluggable database technopdb save state;

 

Pluggable database altered.

 

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

CON_NAME             INSTANCE_NAME                  STATE

-------------------- ------------------------------ --------------

TECHNOPDB            technodb                       OPEN

Now we will see how it really works and we will restart the database and if see it works

SQL> startup

ORACLE instance started.

 

Total System Global Area 2365584120 bytes

Fixed Size                  9270008 bytes

Variable Size             587202560 bytes

Database Buffers         1761607680 bytes

Redo Buffers                7503872 bytes

Database mounted.

Database opened.

SQL>

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

CON_NAME             INSTANCE_NAME                  STATE

-------------------- ------------------------------ --------------

TECHNOPDB            technodb                       OPEN

 

SQL>

Here we see, pluggable database got started in read write open. 

Now we will see how to discard the state. 

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

CON_NAME             INSTANCE_NAME                  STATE

-------------------- ------------------------------ --------------

TECHNOPDB            technodb                       OPEN

 

SQL>

SQL> alter pluggable database technopdb discard state;

 

Pluggable database altered.

 

SQL> select con_name, instance_name, state from dba_pdb_saved_states;

 

no rows selected

 SQL>

these are mostly steps we need to perform if we need to save state of pluggable database

Read More

Close and Open pluggable database

With current 12c and 19c database multitenant databases, we may come across situation where we may need to restart oracle pluggable database. Here we will discuss simple steps to discuss the same.

pluggable database technopdb is 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

SQL>

SQL> alter pluggable database technopdb close;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      MOUNTED

SQL>

SQL> alter pluggable database technopdb open;

 

Pluggable database altered.

 SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

Mostly two commands take care of our task

alter pluggable database technopdb close;

alter pluggable database technopdb open;

Read More

12cR2 New Feature -sql prompt "history"

12cR2 has really great features to use in day to day life for dba. Here we will be discussing one such feature i.e. SQL prompt "history". Many dba's use history command on UNIX prompt to analyze the things or events, Before 12c, this option was not there to see history of sql's you have executed but from 12cR2 onward this is very much possible.

Lets' try this thing out, below is the demonstration 

Check if history parameter is ON/OFF

    SQL> show history; 

    history is OFF

Set history ON

    SQL> set history ON;

    SQL> show history;

    history is ON and set to "100"

Now execute few queries

    SQL> select name,open_mode from v$database;

    NAME      OPEN_MODE

    --------- --------------------

    TECHCDB   READ WRITE

    SQL> show con_id

    CON_ID

    ------------------------------

    1

    SQL> select count(*) from dba_db_links;

      COUNT(*)

    ----------

             2

    SQL> select sysdate from dual;

    SYSDATE

    ---------

    28-JUL-20

 

Now check the history, you will be able see all the sql's executed.

    SQL> history

      1  show history;

      2  select name,open_mode from v$database;

      3  show con_id

      4  select count(*) from dba_db_links;

      5  select sysdate from dual;

    SQL>

set history OFF

    SQL> set history off

    SQL>

    SQL> history

    SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.

this is use of "history" command. Suppose if you forget to turn off history after your work, don't worry it will be only available for that session, it will be automatically turned off.

Read More

Physical location of datafiles in PostgreSQL

In this article, we will discuss finding the physical location of data files for a particular database.

In the logical structure, we can see different databases and tables or any other objects under it but as DBA we should also be aware of how to find out the location of data files associated with a database or a table.

In the article table creation in Postgres database we learned to create tables now we use the same tables here to perform a demonstration. Assume we have the below details

Database name- tech_db

Table name - employee

tech_db=# \c tech_db

You are now connected to database "tech_db" as user "postgres".

tech_db=# \dt

          List of relations

 Schema |   Name   | Type  |  Owner

--------+----------+-------+----------

 public | employee | table | postgres

(1 row)

tech_db=# select * from employee;

 emp_id | emp_name | age

--------+----------+-----

    123 | sam      |  25

    124 | John     |  24

(2 rows)

as of now we do not physical location of this database or table, now we will try to find out the same

Read More : Database Creation in PostgreSQL

Data Directory

First of all check data directory using show command

tech_db=# show data_directory;

  data_directory

------------------

 E:/postgres/data

(1 row)

Above we got data directory, as by default folders for each database gets created under data_directory/base location

as we see folder as created with some random numbers which is called as OID , value of OID can be find using below query.

tech_db=# select datname, oid from pg_database where datname='tech_db';

 datname |  oid

---------+-------

 tech_db | 16393

(1 row) 

we can see value of oid for database tech_db is 16393 and in above screenshot we can find the same folder under base directory. Under it you can find number of files and now lets find the file related to our table employee

tech_db=# select relname,oid, relfilenode from pg_class where relname='employee';

 relname  |  oid  | relfilenode

----------+-------+-------------

 employee | 16394 |       16394

(1 row)

pick up the relfilenode from above output, you will be able to see same file under folder 16393

Read More