My first steps with Oracle 12c

Posted on

Oracle 12c was released just over a week ago and I’ve finally managed to get round to installing it.
This must be the first time that I have done an install of an newly release Oracle Database, where everything worked first time.  Typically I have learned from the past and have left it a few months before attempting an install.
Many thanks to Tim Hall (www.oracle-base.com) for his install instructions for Linux 6 and Oracle 12c. These are a lot simpler to follow than the actual Oracle Install documentation.
After the install had finished I was able to log into the Database Express webpage on the server. This is a cut down version of the old EM and it looks like Oracle is pushing everyone to their standalone EM tool.
[The following is what I did. I’m sure there are better and quicker ways of doing the following]
I had rebooted the VM I created for 12c and when I logged back in I could not log into the container DB or to Database Express. After a bit of digging around I found out that I needed to create a could of scripts that will run every time the VM is started so that it will start the DBs. So to get things (DB) started I ran
sqlplus / as sysdba
This got be logged into the container in nomount mode. Now I needed to start the container with the START command.
SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size            2293928 bytes
Variable Size          578817880 bytes
Database Buffers      255852544 bytes
Redo Buffers            2318336 bytes
Database mounted.
Database opened.
SQL> show user
USER is “SYS”

To see the container DB details
SQL> select name,DB_UNIQUE_NAME from v$database;
NAME      DB_UNIQUE_NAME
——— ——————————
CDB12C      cdb12c

and to see its current status
SQL> select status from v$instance;
STATUS
————
OPEN

To see what pluggable DBs you have
SQL> SELECT v.name, v.open_mode, NVL(v.restricted, ‘n/a’) “RESTRICTED”, d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;  2    3    4 

NAME                           OPEN_MODE  RES STATUS
—————————— ———- — ————-
PDB$SEED                       READ ONLY  NO  NORMAL
PDB12C                         MOUNT      NO  NORMAL

If PDB12C has an OPEN_MODE of MOUNT do the following to open the pluggable database
SQL> ALTER PLUGGABLE DATABASE pdb12c OPEN;  
(I previous had  START instead of OPEN)
To see what active services I have
SQL> select name FROM v$active_services;
NAME
—————————————————————-
pdb12c.localdomain
cdb12cXDB
cdb12c.localdomain
SYS$BACKGROUND
SYS$USERS

To create a new schema in the PDB (pdb12c) I did
sqlplus / as sysdba
SQL> ALTER SESSION SET CONTAINER = pdb12c;
SQL> create user brendan identified by brendan
  2  default tablespace users
  3  temporary tablespace temp;

User created.
SQL> grant connect, resource to brendan1;
Grant succeeded.
SQL>
Next I opened the listener and reloaded to take in the services and the PDB that I wanted to use called PDB12c.
oracle@Oracle-12-1c etc]$ lsnrctl
LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 09-JUL-2013 15:26:50
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Welcome to LSNRCTL, type “help” for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start           stop            status          services       
version         reload          save_config     trace          
spawn           quit            exit            set*           
show*          

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary…
Service “cdb12c.localdomain” has 1 instance(s).
  Instance “cdb12c”, status READY, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0 state:ready
         LOCAL SERVER
Service “cdb12cXDB.localdomain” has 1 instance(s).
  Instance “cdb12c”, status READY, has 1 handler(s) for this service…
    Handler(s):
      “D000” established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=Oracle-12-1c.localdomain)(PORT=27389))
Service “pdb12c.localdomain” has 1 instance(s).
  Instance “cdb12c”, status READY, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
LSNRCTL> exit

Next I needed to add an entry for the PDB into the tnsnames.ora file located in
/u01/app/oracle/product/12.1.0/db_01/network/admin
and added the following for the PDB and saved the tnsnames.ora file.
PDB12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Oracle-12-1c.localdomain)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB12C.localdomain)
      )
  )

Then I was able to connect to the PDB for my ‘brendan’ schema
[oracle@Oracle-12-1c etc]$ sqlplus brendan/brendan@pdb12c
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 9 17:22:30 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Last Successful login time: Tue Jul 09 2013 15:29:18 +01:00
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>

I hope you might find this useful.  All of the above are my notes and for me to remember what I did on my first time using 12c.

Advertisements

2 thoughts on “My first steps with Oracle 12c

    Sanjeev Senapati said:
    April 27, 2014 at 3:43 am

    Thanks Brendan.

    Little bit correction to the below statement

    ALTER PLUGGABLE DATABASE pdb12c START;

    SQL> ALTER PLUGGABLE DATABASE pdb12c START;
    ALTER PLUGGABLE DATABASE pdb12c START
    *
    ERROR at line 1:
    ORA-00922: missing or invalid option

    SQL> ALTER PLUGGABLE DATABASE pdb12c open;

    Pluggable database altered.

    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
    PDB12C READ WRITE NO NORMAL
    PDB_SAMPLE MOUNTED n/a NORMAL

    Like

    Brendan Tierney said:
    April 28, 2014 at 2:36 pm

    you are correct here. I've been meaning to change that for months. I'm not sure how START got added.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s