RMAN separation of duties Oracle Database 12C release 1 (12.1)

Posted on by
1

In 12C release 1 Oracle has introduced new administrative privilege SYSBACKUP. It’s less powerful than SYSDBA:

  • it enables to execute backup and restore commands in rman
  • it enables to start stop the database
  • it’s more restrictive – it hasn’t got SELECT ANY TABLE privilege

During installation of oracle database binaries the privilege is associated with OS group backupdba.

Example of adding oracle dedicated groups

#groups for database management
groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
groupadd -g 54324 backupdba
groupadd -g 54325 dgdba
groupadd -g 54326 kmdba
groupadd -g 54327 asmdba
groupadd -g 54328 asmoper
groupadd -g 54329 asmadmin
add user Oracle for database software
useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba oracle

when installation of binaries is going on automatically new group is recognized

oracle_db12c_20

Continue reading →

APPROX_COUNT_DISTINCT – fast count distinct values Oracle Database 12C release 1 (12.1)

Posted on by
Reply

APPROX_COUNT_DISTINCT is new function available since version 12.1.0.2. It returns approximate number of rows that contain distinct values of expr.

APPROX_COUNT_DISTINCT(expr)

It’s alternative to the COUNT (DISTINCT expr) function, which returns the exact number of rows that contain distinct values of expr.

For processing large amounts of data it’s significantly faster than COUNT, with negligible deviation from the exact result.

Continue reading →

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Posted on by
6

If your database was started in nomount mode you can encounter following error

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:39:28 2013

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

Enter user-name: sys@ora12c as sysdba
Enter password:
ERROR:
ORA-12528: TNS:listener: all appropriate instances 
are blocking new connections

To solve this problem you need to make static registration of your service in listener.ora.

Settings before solving the error

tnsnames.ora

ORA12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA12C)
    )
  )

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    )
  )

New settings

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORA12C)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (SID_NAME = ORA12C)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\oracle\product\12.1.0\dbhome_1\log

You need to restart listener to make it effective. Once it’s done you should be able to connect to your database which is working in nomount.

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:50:15 2013

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

Enter user-name: sys@ora12c as sysdba
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>

Have a fun :)

Tomasz

Data Redaction Oracle Database 12C release 1 (12.1)

Posted on by
6

Data Redaction: Overview

This new feature enables to easily prevent the display of sensitive data to end-users by performing redaction in each application. It’s very simple to implement and very efficient. Data are modified on the-fly right before results are returned to applications.

  • on-the-fly redaction based on username, ip address, application context and other factors
  • transparent, consistent enforcement in the database
  • no measurable impact on production workloads
  • appropriate for call centers, decision support systems, and systems with PII, PHI,and PCI data

Data redaction is automatically turned off for following operations:

  • backup and restore
  • import and export
  • patching and upgrades
  • replication
  • sys connections are always exempt from redaction policies

Data Redaction 1

Continue reading →

ORA-01000: maximum open cursors exceeded

Posted on by
Reply

ORA-01000: maximum open cursors exceeded – It’s typical error encountered in Oracle database. It happens when too much cursors are opened on an oracle instance.

Maximum number of opened cursors for an instance is limited by parameter

open_cursors

usually for small databases it’s about 300 and can be very huge for bigger ones. To increase this parameter it requires to bounce database instance.

To find out who opened many cursors on instance use following query

SELECT SID, count(*) ses_cursors, sum(count(*)) OVER() total_cursors
FROM v$open_cursor
GROUP BY SID
ORDER BY 2 DESC;

       SID ses_cursors total_cursors 
---------- ----------- -------------
       572         115           837 
      2835          95           837 
...
      1987           1           837 
      1417           1           837 

 41 rows selected

then you can investigate a given session why so many cursors are opened.

Have a fun 🙂

Tomasz

Temporal Validity in Oracle Database 12C release 1 (12.1)

Posted on by
Reply

Temporal Validity is very interesting feature in Oracle 12C that provides ability to scan effectively Gantt data:

  • adds (one or more) “time dimension” to a table by using current columns or using columns automatically created by database
  • enable using simple SQL syntax to filter the columns to access only active data using Oracle flashback technology

Following picture shows five records Rec1 … Rec5 that are active only in a period of time (represented in the picture as |————|). Usually the most complex part for programmers is to filter active records in specific dedicated time:

  • for Filter1 – valid records are: Rec1, Rec2, Rec3, Rec5
  • for Filter2 – valid records are: Rec1, Rec2, Rec4, Rec5
  • for Filter3 – valid records are: Rec1, Rec4, Rec5

temporal_validity_1

Typical example could be Employee table with two “time dimension” columns :

  • hire_start_date – when employee started to work
  • hire_end_data – when employee ended work

Continue reading →

Partition outer join in Oracle (data densification)

Posted on by
Reply

Partition outer join is a method for “data-densification”. If you have sparse data it helps to easily duplicate data with new SQL syntax:

SELECT  
  select_expression
FROM    
  table_reference PARTITION BY (expr [,expr ]...)
RIGHT OUTER JOIN  
  table_reference

or

SELECT  
  select_expression
FROM    
  table_reference
LEFT OUTER JOIN  
  table_reference PARTITION BY (expr [,expr ]...)

Continue reading →

DML Error Logging in Oracle Database 11G release 2 (11.2)

Posted on by
4

This article presents extension for standard DML operations (INSERT, UPDATE, DELETE, MERGE) .. LOG ERRORS INTO. It enables to execute successfully DML operation into target table regardless of errors during processing of rows. Informations about errors are loaded together with rows content into dedicated error table.

dml error log

Syntax

Here is general syntax for DML

INSERT/UPDATE/DELETE/MERGE ...
...
LOG ERRORS [INTO [schema_name.]table_name] [('simple_expression')] 
[REJECT LIMIT integer|UNLIMITED]

where

  • schema_name.table_name – is error table created with DBMS_ERRLOG package
  • simple_expression – is tag that can be applied to failed records. It’s stored in error table in column ORA_ERR_TAG$
  • REJECT LIMIT specifies maximum number of accepted errors before the statment fails and rollback all. Default value is 0 and maximum UNLIMITED

Continue reading →

In-Database Archiving in Oracle Database 12C release 1 (12.1)

Posted on by
Reply

This article presents following new feature of Oracle Database 12C

In-Database Archiving – this option enables to mark records in a table as not active (called later archive records). As default archive records are not visible in Oracle sessions. The records can be later compressed or deleted.

The reason to add such option was to keep both versions of records (active and not active) in the same table instead of making backup(not active) on tape and delete them(not active)  from a table.

Continue reading →