All posts by dpafumi

Playing with Oracle 12c and Virtual Box

I keep “playing’” with 12c, and this time I’d like to show you how easy is to install the Pre-Built VMs provided by Oracle.

As I mentioned, the process is very simple, these are the requirements to install it:

  • At least 2GB RAM. Default VM is 1G RAM, for better performance increase.
  • At least 15GB of free space (Note: virtualization works best with contiguous space so it is a good idea if on Windows to run a defrag program, and make sure you are using NTFS for your file system to handle large files on Windows. )
  • 2GHz Processor (a lesser processor will be acceptable but slower)
  • Mozilla Firefox 2.0 or higher, Internet Explorer 7 or higher, Safari 3.0 and higher or Google Chrome 1.0 or higher
  • Adobe Acrobat reader
  • Admin privileges on your box

So now that you know the minimum requirements, let’s move to the installation process:

  1. Download and install Oracle VM VirtualBox on your host system from HERE.
  2. Download the Virtual Machine from HERE. (DownloadOTN_Developer_Day_VM.ova (5,215,947,264 bytes, md5sum: 4e8ef75cdbd0fbe3d3beee8f873b2d4a)
  3. Open Oracle VM VirtualBox and Import your VM: File > Import Appliance to launch Appliance Import Wizard.
    Click Choose… to browse to the directory you re-assembled all the files in and select the OTN_Developer_Day_VM.ova.  Then click Next to begin importing the virtual machine. It will prompt you to agree to the appropriate developer licenses while importing. You will see ‘Oracle Developer Days (Powered Off)’ when it is finished importing.
  4. Test your VM: Once the import has completed, double-click the OTN Developer Days VM. Click OK to close the Virtualbox Information dialogs. When you get to the Enterprise Linux 6 screen you can now login. (Username and password is oracle.) Allow the process to complete; it is ready when you see a terminal window, which you can close.
  5. Once you are finished working in the guest VM you can shut it down via System > Shut Down; this will return the guest VM to the Powered Off state.

As you can see, the process is pretty simple right?

Have a nice day!!

12c, First Steps

After the Holidays, 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:

ORA-01033: ORACLE initialization or shutdown in progress

So, to bring the database up (by the way, I’m on 64 bit Linux) after booting the Linux VM the following steps were required:

lsnrctl start
sqlplus / as sysdba
startup
alter session set container=PDBORCL;
startup

Interestingly there is only one pmon:

$ ps -ef | grep pmon
oracle   11865     1  0 09:12 ?        00:13:46 ora_pmon_DB12C

But you get different results when you query dba_data_files depending on whether connected to the CDB or PDB:

DB12C

FILE_NAME                                 
------------------------------------------
/u01/app/oracle/oradata/DB12C/system01.dbf 
/u01/app/oracle/oradata/DB12c/sysaux01.dbf 
/u01/app/oracle/oradata/DB12C/undotbs01.dbf
/u01/app/oracle/oradata/DB12C/users01.dbf

PDB

FILE_NAME                                                     
--------------------------------------------------------------
/u01/app/oracle/oradata/DB12C/pdborcl/system01.dbf             
/u01/app/oracle/oradata/DB12C/pdborcl/sysaux01.dbf             
/u01/app/oracle/oradata/DB12C/pdborcl/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/DB12C/pdborcl/example01.dbf
/u01/app/oracle/oradata/DB12C/pdborcl/dbfs01.dbf

So, I guess each PDB has its own SYSTEM and SYSAUX tablespaces?

Lastly when running my scripts to poke around I edited my sqlplus header script to report which container you are in.  It looks like this now:

set linesize 32000
set pagesize 1000
set long 2000000000
set longchunksize 1000
set head off;
set verify off;
set termout off;

column u new_value us noprint;
column n new_value ns noprint;
column c new_value cs noprint;

select name n from v$database;
select user u from dual;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') c FROM DUAL;

set sqlprompt &ns:&cs:&us>

set head on
set echo on
set termout on
set trimspool on

It puts out a prompt like this:

CDB

DB12C:CDB$ROOT:SYSTEM>

PDB

DB12C:CDB$ROOT:SYSTEM>

 

Using ADRCI Properly

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
or

adrci> show alert -tail 

or

adrci> show alert 

To check for severe problems in database.

adrci> show problem

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2                    ORA 600 [723]                                               3807                 2007-06-18 21:35:47.862114 -07:00
1                    ORA 29770                                                   24129                2011-10-21 19:08:24.097000 +00:00
2 rows fetched

To check for incedents related to problems.

adrci> show incident

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
*****************************************************************************
INCIDENT_ID       PROBLEM_KEY               CREATE_TIME
----------------- ------------------------- ---------------------------------
3808              ORA 603                   2007-06-18 21:35:49.322161 -07:00
3807              ORA 600 [723]             2007-06-18 21:35:47.862114 -07:00
3805              ORA 600 [723]             2007-06-18 21:35:25.012579 -07:00
3804              ORA 1578                  2007-06-18 21:35:08.483156 -07:00
4 rows fetched

To get more details on the incident execute the following

adrci> show incident -mode detail -p "incident_id=3807"

adrci> show trace <trace_file_location>

This statement lists the names of all trace files related to incident number 1681:

adrci> SHOW TRACEFILE -I 1681

adrci> show control

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
3783208084           720                  8760                 2013-12-16 19:28:24.827055 +00:00        2014-01-17 21:09:07.527157 +00:00        2014-01-17 10:15:21.303696 +00:00        1                    2                    80                   1                    2013-12-16 19:28:24.827055 +00:00
1 rows fetched


All the tracefiles will stay for 30 days (720 hours),
but all the incident files would be kept for one year (8760 hours) by default.
This can be changed by the following command

adrci> set control (SHORTP_POLICY = 48)
adrci> set control (LONGP_POLICY = 48)


Purging manually
 
Files can be purged manually by the following commands. The value "2880" is in minutes.

adrci> purge -age 2880 -type incident
adrci> purge -age 2880 -type ALERT
adrci> purge -age 2880 -type TRACE
adrci> purge -age 2880 -type CDUMP
adrci> purge -age 2880 -type HM


Creation of Packages & ZIP files to send to Oracle Support

adrci> ips create package problem 1 correlate all

Created package 2 based on problem id 1, correlation level all 

#First create a logical package.

This did not yet create a ZIP file and is therefore referred to as logical Package. The ZIP file is generated from the Logical Package that was created:

adrci> ips generate package 2 in "/home/oracle"  

Generated package 2 in file /home/oracle/ORA7445qc_20110601112533_COM_1.zip, mode complete

#Now create a zip file from the logical package
 

Select Clause in 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.

  • OFFSET provides a way to skip the N first rows in a result set before starting to return any rows
  • The FETCH clause limits the number of rows returned in the result set
  • For the result offset clause, the value of the integer literal must be equal to 0 (default if the clause is not given), or positive. If it is larger than the number of rows in the underlying result set, no rows are returned.
  • For the fetch first clause, the value of the literal must be 1 or higher. The literal can be omitted, in which case it defaults to 1. If the clause is omitted entirely, all rows (or those rows remaining if a result offset clause is also given) will be returned.

Here are several examples:

Select the First 3 rows:
select USER_ID, USERNAME from DEMO1 order by USER_ID FETCH FIRST 3 ROWS ONLY;
USER_ID    USERNAME
---------- ------------
 0         SYS
 7         AUDSYS
 8         SYSTEM
Select 3 rows after the first 4:
select USER_ID, USERNAME from DEMO1 order by USER_ID OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;
USER_ID    USERNAME
---------- ------------
21         GSMADMIN_INTERNAL
22         GSMUSER
23         DIP
Select all the rows after the 5th one
select USER_ID, USERNAME from DEMO1 order by USER_ID OFFSET 5 rows ;
 USER_ID   USERNAME
---------- --------------------
 22        GSMUSER
 23        DIP
 36        ORACLE_OCM
 47        DBSNMP
 48        APPQOSSYS
 49        XDB
 50        ANONYMOUS
 60        GSMCATUSER
......
......
Select 31% of the rows:
select USER_ID, USERNAME from DEMO1 order by USER_ID FETCH FIRST 31 PERCENT ROWS ONLY;
USER_ID    USERNAME
---------- --------------------
 0         SYS
 7         AUDSYS
 8         SYSTEM
 13        OUTLN
 21        GSMADMIN_INTERNAL
 22        GSMUSER
 23        DIP
 36        ORACLE_OCM
 47        DBSNMP
 48        APPQOSSYS
 49        XDB
 50        ANONYMOUS
 60        GSMCATUSER
 61        WMSYS

 

Working with AWR Reports

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

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