Monthly Archives: January 2014

12c, First Steps

12c, Installation,

I was finally able to start with 12C!! So once I finish the installation, I tried to connect to the DB and create a user. That is when I discover that you need to connect to a special Database in order to perform that.

I got an error message creating a user.  Well, I figured out how to create a new user and a few other things.  I’m working with the DB12C database that comes with the install and all the parameters, etc. that come with it.

Evidently the default install comes with a PDB called PDBORCL.  So, I have two tns entries one for the parent CBD and one for the child PDB and they look like this:

DB12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)
(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB12C)
    )
  )

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)
(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

So, if I connect as SYSTEM/password@DB12C I’m connected to the CDB and if I connect to SYSTEM/password@pdb I’m connected to the PDB.  When I connected to the PDB I could create a new user without getting an error.

But, when I first tried connecting to the PDB I got this error, even though the database was up:

Continue reading 12c, First Steps →

Using ADRCI Properly

Tips, Tuning,

The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data and is part of the fault diagnosability infrastructure introduced in Oracle 11g. ADRCI enables you to:

  • View diagnostic data within the Automatic Diagnostic Repository (ADR).
  • View Health Monitor reports.
  • Package incident and problem information into a zip file for transmission to Oracle Support.

Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.
ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

Invoke ADRCI as the Oracle OS user:

> adrci
ADRCI: Release 11.2.0.3.0 - Production on Fri Jan 17 22:38:57 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle" adrci> show home ADR Homes:
diag/diagtool/user_oracle/host_12454656_11
diag/rdbms/orcl/ORCL
diag/asm/+asm/+ASM1
diag/tnslsnr/orcl/listener_cad Set the home adrci> set home diag/rdbms/orcl/ORCL
To check the alert log use the following option.

adrci> show alert -tail -f
2014-01-21 10:20:23.418000 -05:00
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1176
Successful close of redo thread 1
Completed: alter database close normal
alter database dismount
Completed: alter database dismount
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
2014-01-21 10:20:24.852000 -05:00
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Stopping background process VKTM:
2014-01-21 10:20:27.036000 -05:00
Instance shutdown complete

Continue reading Using ADRCI Properly →

Select Clause in 12c

12c,

Prior to Oracle12c you needed to use an inner query to get the 10 first rows of a table. Example:

Get the 10 first rows ordered by user_id in ascending order:

drop table DEMO1;

create table DEMO1 as select substr(username,1,20) username, user_id, from all_users;

select * from 
(select username, user_id from DEMO1 order by user_id asc)
where rownum <= 10;

USERNAME             USER_ID
-------------------- ----------
XS$NULL              2147483638
SYSKM                2147483619
SYSDG                2147483618
SYSBACKUP            2147483617
DVSYS                   1279990
DBFS_USER                   111
SCOTT                       109
BI                          108
SH                          107
IX                          106

 

Now with 12c, this is extremely simple!!!
Oracle 12c provides enhanced support for top-n analysis.

Continue reading Select Clause in 12c →

Working with AWR Reports

Tuning

AWR is the best tool available to identify performance issues on your database. It has several tools to help you identify performance issues and all the information is displayed in a single HTML page. AWR periodically gathers and stores system activity and workload data which is then analyzed by ADDM.

Every layer of Oracle is equipped with instrumentation that gathers information on workload which will then be used to make self-managing decisions. AWR is the place where this data is stored. AWR looks periodically at the system performance (by default every 60 minutes) and stores the information found (by default up to 7 days). AWR runs by default and Oracle states that it does not add a noticeable level of overhead. A new background server process (MMON) takes snapshots of the in-memory database statistics (much like STATSPACK) and stores this information in the repository.

For more details on its usage, please ckick HERE

Oracle Database 12: Architecture Diagram

Installation

I found the following Database Architecture Diagram for Oracle Database 12c on Oracle’s website. It lists all the processes and the relationship between processes and other database components.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/poster/OUTPUT_poster/pdf/Database%20Architecture.pdf

 

DB Architecture Diagram
And below is a YouTube link which gives database architecture overview.
Oracle Database 12c Architecture Overview