Thursday, July 11, 2013

My first steps with Oracle 12c

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 START;

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 <machine: Oracle-12-1c.localdomain, pid: 3138>
         (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.

No comments:

Post a Comment