Oracle 12c - Basic Administration task  in 12c- Multi tenant Architecture

 

Hi, I am Smitha Sunil  and I would like to share some basics of Oracle 12c Database  Administration tasks in our day to day administration in Oracle 12c, which is very helpful  for Beginners.

As we know oracle 12c database is a multi-tenant architecture, we have a container database and all pluggable databases can be plugged and unplugged from this container database-root database.

• Connect to a Container Database and to a Pluggable Database.
• Create a Pluggable Database from the seed Pluggable Database.

Prerequisite:

1. Oracle 12c Installed
2. Create one Container and one pluggable database

The procedure to complete the task is as below:

Here I am using the Linux environment and logged in as Oracle user.

[oracle@localhost oracle]$ . oraenv

ORACLE_SID = [orcl] ? orcl

1.Use the Easy Connect to connect to the database

[oracle@localhost oracle]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 1 04:17:49 2017

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

Enter user-name: sys/oracle@localhost:1521/orcl as sysdba

Connected to :

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

2. SQL> show con_id;

3. Display the available services in the root and pdbs.

     SQL> SELECT NAME,CON_ID FROM V$ACTIVE_SERVICES;

 

NAME                                                                                           CON_ID

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

pdborcl                                                                                                  3

orclXDB                                                                                                 1

orcl                                                                                                       1

SYS$BACKGROUND                                                                            1

SYS$USERS                                                                                        1

 

4. Connect to the PDBORCL

SQL> conn sys/oracle@localhost:1521/pdborcl as sysdba

Connected.

SQL> show con_name

 

CON_NAME

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

PDBORCL

SQL> show con_id

 

CON_ID

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

3

 

Creating a PDB FROM SEED PDB

In this  section let us create PDB FROM the seed database. CDB has a template called PDB$Seed

1. Create the OS Directory for the New Data files and Temp Files of the PDB

 Let us create a destination directory to store the data files.

mkdir  /u01/app/oracle/oradata/pdb3

2. Create the PDB

 

SQL> create pluggable database pdb3

  2  admin user odb3_admin identified by oracle

  3  roles = (DBA)

  4  file_name_convert=('/u01/app/oracle/oradata/orcl/pdbseed','/u01/app/oracle/oradata/pdb3');

2. Verify the status and open_mode and Service names of the PDBs in the container database.

If the status shows need sync, then connect to pdb and run the package dbms_pdb.sync_pdb procedure to change the status Normal.

 

SQL> Select pdb_name,status from cdb_pdbs;

 

PDB_NAME   STATUS

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

PDBORCL    NORMAL

PDB$SEED   NORMAL

PDB3      NEW

SQL> select name,open_mode from v$pdbs;

 

NAME                                 OPEN_MODE

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

PDB$SEED                          READ ONLY

PDBORCL                            MOUNTED

PDB3                                   MOUNTED

 

SQL> select name,con_id from v$active_services ;

 

NAME                                                                                           CON_ID

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

pdb3                                                                                                      4

pdborcl                                                                                                  3

orclXDB                                                                                                 1

orcl                                                                                                       1

SYS$BACKGROUND                                                                             1

SYS$USERS