Friday, July 05, 2013

Connect to pluggable database

After creating the second pluggable database, next step is to connect to the pluggable databases and cretae a tablespace in one of them.

Before connecting, be sure the pluggable databases are listed in the listener.ora..

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PDBORATEST)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0)
    )
    (SID_DESC =
      (SID_NAME = PLUGDB02)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0)
    )
)


So now we are able to make a connection to one of the pluggable databases

[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 11:05:13 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


This is still the container database, to connect to the pluggable database..

[oracle@localhost admin]$ sqlplus sys@pdboratest as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 11:06:59 2013

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
PDBORATEST


In the pdboratest I have created a basefarm tablespace..

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS
BASEFARM01











Let's check the other pluggable database

SQL> show con_name

CON_NAME
------------------------------
PLUGDB02

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS


Tablespace just created is not there.
Lets check the container database..

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS


Also not visible...

Wednesday, July 03, 2013

Oracle 12c plugable databases

Last week I downloaded the new Oracle 12c database to check this new feature called 'Plugable database'.
Using the runnstaller I fist created the 12c database with the 'Create as container database' option enabled.












After installing the database, I dicided to use the dbca to create another plugable database.



So, there should be 2 plugable databases and one container database right now.
I checked the datafiles location.
Datafiles are located in this example in /media/oracle_data
cd /media/oracle_data/ORATEST
[oracle@localhost ORATEST]$ ls
controlfile  datafile  E0982A74548A0DF8E0430100007FA928  E098787A3ED20F81E0430100007FFC1B  onlinelog






 The E0... directories are the two plugable database directories. Inside these directories are datafiles that belong to that plugable database. Controlfiles and onlinelog files are not in the plugable database directories, only in the container database directory.
At first the plugable directories only have a users, temp, system and sysaux datafile.

Connect to the database (container)

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [oracle] ? oratest
The Oracle base has been set to /u01/app/oracle
 [oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 13:46:34 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
 


SQL> SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
  2  FROM v$pdbs v, dba_pdbs d
  3  WHERE v.guid = d.guid
  4  ORDER BY v.create_scn;

NAME                                OPEN_MODE    RES   STATUS
------------------------------   -----------------      ----    ------------
PDB$SEED                       READ  ONLY      NO    NORMAL
PDBORATEST                  READ  WRITE    NO    NORMAL
PLUGDB02                      READ  WRITE    NO    NORMAL



Next step is to connect to a plugable database and create tablespaces for example...