There are two important improvements in making big datafile backups and restores in Oracle 12C release 1

• multisection backup of image copies
• multisection backup of incremental backups for level higher than 0

Multisection option was introduced in 11G and possible only for:

• multisection backup of backup sets
• multisection backup of full backup
• multisection backup of incremental backups for level 0

Requirements and restrictions

• COMPATIBLE must be set to 12.0 or higher
• Not for control files or spfiles

Continue reading →

In Oracle Database 12C new keywords SESSION, GLOBAL are available that can be specified during a sequence creation

CREATE SEQUENCE session_seq START WITH 1 INCREMENT BY 1 SESSION;
CREATE SEQUENCE global_seq START WITH 1 INCREMENT BY 1 GLOBAL;

GLOBAL – creates standard sequence well known in previous release. This is the default.

SESSION – creates new type session sequence, which is a special type of sequence that is specifically designed to be used with global temporary tables that have session visibility.  Session sequence returns a unique range of sequence numbers only within a session, but not across sessions. Another difference is that session sequences are not persistent. If a session goes away, so does the state of the session sequences that were accessed during the session.

Session sequences must be created by a read-write database but can be accessed on any read-write or read-only databases (either a regular database temporarily open read-only or a standby database).

CACHE, NOCACHE,NOORDER are ignored when specified with SESSION calues

Continue reading →

In Oracle 12C release 1 most of SQL and PL/SQL commands are supported in rman:

• no SQL prefix or quotes required
• DESCRIBE is supported

In previous releases

RMAN> sql 'create table test_tbl(id number)';

In Oracle Database 12C

RMAN> create table test_tbl(id number);
RMAN> select * from test_tbl;
RMAN> desc test_tbl

Have a fun 🙂

Tomasz

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

Continue reading →

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 →

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

Continue reading →

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

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 →

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 →