Oracle Database 12c – Getting my hands dirty

Oracle Database 12c – Getting my hands dirty

I attended a Oracle database 12c In-Memory workshop recently. It’s one of the new database advancement where they introduce columnar storage inside memory area for faster access. It is quite similar to IBM DB2 BLU Acceleration. You can enable a column, table, tablespace or the entire database to be stored in-memory.

How to turn on the In-Memory option.

SQL> ALTER SYSTEM SET INMEMORY_SIZE=1G SCOPE=SPFILE;

SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 1920M

SQL> shutdown immediate;
SQL> startup;

SQL> show parameter inmemory_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_size big integer 1G

SQL> SELECT NAME, VALUE FROM V$SGA;
NAME VALUE
-------------------- ----------
Fixed Size 2926080
Variable Size 301992448
Database Buffers 620756992
Redo Buffers 13848576
In-Memory Area 1073741824

SQL> SELECT POOL, ALLOC_BYTES, USED_BYTES FROM V$INMEMORY_AREA;

POOL ALLOC_BYTES USED_BYTES
-------------------------- ----------- ----------
1MB POOL 854589440 0
64KB POOL 201326592 0

Note: In-Memory uses a sub-heap in SGA. It is part of SGA. Your SGA_TARGET value must be greater than your INMEMORY_SIZE than else it’s won’t startup. AMM/ASMM will not manipulate your INMEMORY_SIZE automatically. The memory area is split into In Memory Compression Unit (IMCU) 1MB pool and Snapshot Metadata Unit (SMU) 65KB pool. The ratio is determined internally by Oracle and is usually in 80-20 ratio.

Playing around with In-Memory:

SQL> alter table TEST inmemory; => enable a table for inmemory
SQL> exec DBMS_INMEMORY.POPULATE('<SCHEMA_NAME>','<TABLE_NAME>');
SQL> SELECT SEGMENT_NAME, POPULATE_STATUS, BYTES, BYTES_NOT_POPULATED FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'TEST';
SEGMENT_NAME POPULATE_ BYTES BYTES_NOT_POPULATED
---------------------------------------- --------- ---------- -------------------
TEST COMPLETED 13631488 0
SQL> SELECT INMEMORY, INMEMORY_PRIORITY, INMEMORY_COMPRESSION, INMEMORY_DISTRIBUTE, INMEMORY_DUPLICATE FROM USER_TABLES WHERE TABLE_NAME = 'TEST';
INMEMORY INMEMORY INMEMORY_COMPRESS INMEMORY_DISTRI INMEMORY_DUPL
-------- -------- ----------------- --------------- -------------
ENABLED NONE FOR QUERY LOW AUTO NO DUPLICATE
SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION FROM V$IM_COLUMN_LEVEL WHERE TABLE_NAME = 'TEST';
SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION FROM USER_TAB_PARTITION;

Note.1: Just because a table is enabled for in-memory doesn’t mean that it get populated to memory automatically! It will only get populated when it gets triggered(DBMS_INMEMORY) manually, when the table get accessed or when the priority is set.

Note.2: USER_TABLES will not show if the table have only a few columns in-memory. You will have to query V$IM_COLUMN_LEVEL.

Note.3: USER_TABLES does not show if the in-memory table is partitioned. You will have to query USER_TAB_PARITIONS.

My 2-cents on Oracle database 12c In-Memory Option:

The idea of having both row and columnar storage in the same database looks extremely tempting. However, this does not come cheap. It is licensed by core and it is priced as the same range as Oracle RAC (Link). Storing data in column format is especially useful for DataWareHouse due to the nature of the query. However, if your tables are updated frequently (DML), it will be pretty expensive to keep the data in both row and column format synchronised.

I think it all boils down to the nature of the workload. Please do perform your own internal testing and evaluation. You never know, a free feature like “Full Database Caching” might suit your environment better! (Link).

Regards,
Wei Shan

2 Comments

Debate on a Facebook DBA group that leads to a better understanding of Oracle Database

Debate on a Facebook DBA group that leads to a better understanding of Oracle Database

I recently had a debate with someone on a DBA group who says that it is possible to recover a datafile without any sort of backups.This does not tally with any of my understanding at all! He claims that he have successfully done it on his laptop and challenged me to try it myself. So I took the challenge :)

Setting up the stage for the testing.

SQL> create tablespace tbs_test datafile '/u01/oradata/orcl/tbstest1.dbf' size 5M;
SQL> alter tablespace tbs_test add datafile '/u01/oradata/orcl/tbstest2.dbf' size 5M;

SQL> alter database datafile '/u01/oradata/orcl/tbstest1.dbf' offline;
SQL> create table testtable2 tablespace tbs_test as select * from dba_data_files;

SQL> select count(*) from testtable2;
COUNT(*)
----------
7

SQL> select owner,table_name,tablespace_name from dba_tables where table_name='TESTTABLE2';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS TESTTABLE2 TBS_TEST

SQL> select B.FILE_NAME from dba_segments a, dba_data_files b where a.header_file=b.file_ID and segment_name='TESTTABLE2';
FILE_NAME
----------------------------------------
/u01/oradata/orcl/tbstest2.dbf

The above ensures that the table only exists in ‘/u01/oradata/orcl/tbstest2.dbf’ datafile. Now comes test 1 which successfully restored the datafile without any backups!

 SQL> !rm '/u01/oradata/orcl/tbstest2.dbf'
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@oel ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 01:19:54 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 310381952 bytes
Database Buffers 92274688 bytes
Redo Buffers 4308992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/oradata/orcl/tbstest2.dbf'
SQL> alter database datafile '/u01/oradata/orcl/tbstest2.dbf' offline;
Database altered.
SQL> alter database create datafile '/u01/oradata/orcl/tbstest2.dbf';
Database altered.

SQL> recover datafile '/u01/oradata/orcl/tbstest2.dbf';
Media recovery complete.
SQL> alter database datafile '/u01/oradata/orcl/tbstest2.dbf' online;
Database altered.

OMG! Why did it WORK??!! I must admit I was quite intrigued at this point. Please be patient and read on about my second test!

SQL> !rm '/u01/oradata/orcl/tbstest2.dbf'

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 310381952 bytes
Database Buffers 92274688 bytes
Redo Buffers 4308992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/oradata/orcl/tbstest2.dbf'
SQL> alter database datafile '/u01/oradata/orcl/tbstest2.dbf' offline;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> !ls -al /u01/archive
total 732
drwxr-xr-x. 2 oracle dba 4096 Oct 30 01:23 .
drwxr-xr-x. 6 root root 4096 Oct 30 00:39 ..
-rw-r-----. 1 oracle dba 262656 Oct 30 01:22 1_21_838508177.dbf
-rw-r-----. 1 oracle dba 466944 Oct 30 01:23 1_22_838508177.dbf
-rw-r-----. 1 oracle dba 1024 Oct 30 01:23 1_23_838508177.dbf
-rw-r-----. 1 oracle dba 2048 Oct 30 01:23 1_24_838508177.dbf

SQL> !rm /u01/archive/*

SQL> alter database create datafile '/u01/oradata/orcl/tbstest2.dbf';
Database altered.

SQL> recover datafile '/u01/oradata/orcl/tbstest2.dbf';
ORA-00279: change 1447147 generated at 10/30/2014 00:49:02 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_20_838508177.dbf
ORA-00280: change 1447147 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
^C

So the reason for the successful restore was due to the archivelogs or the data that resides in the redo log. I almost forgotten about my basics!

Regards,
Wei Shan

Leave a comment

NoSQL Tech Comparison Report

NoSQL Tech Comparison Report

In the relational RDMS world, there are only a few companies who owns the majority of the market share. It is usually very easy to determine what technology to go with, depending on the budget and business objectives.

  • Oracle
  • Microsoft
  • DB2
  • Teradata

However, in the non-relational RDMS world, it is far more difficult to have a decision. There are plenty of variants, each having its own advantages and disadvantages. The following report from DBTA is a very good source to aid in their decision making.

http://www.dbta.com/DBTA-Downloads/WhitePapers/NoSQL-Tech-Comparison-Report-4906.aspx

Leave a comment

Clean it up

Wei Shan:

An extremely good read for understanding how Oracle deals with clean/dirty buffer using UNDO tablespace. It’s pretty confusing though!

Originally posted on Oracle Scratchpad:

There is some confusion about the expression “clean” in Oracle circles, so I thought I’d write a short note to explain the different ways in which the word may be applied to Oracle blocks. There are five terms to consider:

  1. clean
  2. commit cleanout
  3. block cleanout
  4. delayed block cleanout
  5. delayed logging block cleanout

View original 1,187 more words

Leave a comment

[ALERT] – Oracle products affected by Shellshock Bash Bug

[ALERT] – Oracle products affected by Shellshock Bash Bug

This Security Alert addresses CVE-2014-7169 (initially identified as CVE-2014-6271), a publicly disclosed vulnerability affecting GNU Bash. GNU Bash is a popular open source command line shell incorporated into Linux and other widely used operating systems. This vulnerability affects multiple Oracle products. This vulnerability may be remotely exploitable without authentication, i.e. it may be exploited over a network without the need for a username and password. A remote user can exploit this vulnerability to execute arbitrary code on systems that are running affected versions of Bash.

Some of the affected products are;

  • Solaris
  • Linux
  • Oracle Audit Vault and Database Firewall
  • All Oracle Engineered Systems

Systems that are public facing SHOULD be patched immediately.

Please refer to this link.

Regards,
Wei Shan

Leave a comment

Oracle Database – How to create control files from scratch

Oracle Database – How to create control files from scratch

Below is an example the “Create Controlfile” commands”. 

CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS FORCE LOGGING ARCHIVELOG
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 292
LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/testdb/redo1a.log','/u01/app/oracle/oradata/testdb/redo1b.log') SIZE 5M,
GROUP 2 ('/u01/app/oracle/oradata/testdb/redo2a.log','/u01/app/oracle/oradata/testdb/redo2b.log') SIZE 5M,
GROUP 3 ('/u01/app/oracle/oradata/testdb/redo3a.log','/u01/app/oracle/oradata/testdb/redo3b.log') SIZE 5M
DATAFILE
'/u01/app/oracle/oradata/testdb/system01.dbf',
'/u01/app/oracle/oradata/testdb/undotbs01.dbf',
'/u01/app/oracle/oradata/testdb/sysaux01.dbf',
'/u01/app/oracle/oradata/testdb/users01.dbf'
CHARACTER SET WE8ISO8859P1;

Do note the following though;

  • The database has lost the online redo logs
  • If ASM is used, change the path to “+ASM/../..” accordingly
  • The database have archive logging enabled
  • Character set of WE8ISO8859P1

Hope this helps.

Regards,
Wei Shan

Leave a comment

Oracle DB 11gR2 – How to list all hidden/undocumented parameters

How to list all hidden/undocumented parameters in Oracle Database

Oracle database is extremely powerful due to many reasons. One of it is the level of tuning and optimisation that you can perform on it. You can optimise an Oracle database using parameters in PFile or SPFile. It has many parameters, both documented and undocumented for you to configure. Documented parameters are easy to configure due to the extremely clear documentation by Oracle. However, this does not apply to undocumented or hidden parameters. Of course, the parameters are hidden for a reason and it should NOT be changed unless you are extremely confident of what you are changing!

The query below will be able to list down all the hidden parameters for the database release.

SQL> select a.ksppinm, b.ksppstvl
 FROM x$ksppi a, x$ksppcv b
 WHERE a.indx=b.indx;

This is the output in Oracle Database 11.2.0.3. There are about than 2500 hidden parameters. I’m only showing a portion about it.

KSPPINM                                  KSPPSTVL
---------------------------------------- ---------------------------------------------
 _appqos_qt                              10
 _ior_serialize_fault                    0
 _shutdown_completion_timeout_mins       60
 _inject_startup_fault                   0
 _latch_recovery_alignment               65534
 _spin_count                             1
 _latch_miss_stat_sid                    0
 _max_sleep_holding_latch                4
 _max_exponential_sleep                  0
 _other_wait_threshold                   0
 _other_wait_event_exclusion             0

Hope this helps!

Regards,
WeiShan

Leave a comment

Solaris 11 – Free Hands On Practise Lab!

Good Day!

I was doing some researching on database migration from AIX and Solaris and I found some juicy stuff! Oracle has provided some free lab session for new Solaris administrators to get their hands dirty. If you are new to Unix and want to try them out, you can check out the links below!

Just a side note, I was recently exposed to Wintel environments, either  Oracle/MSSQL on Windows Server 2008/2012. It was a utter pain to work with the permissions and services. Unix/Linux is still the number one enterprise operating systems, period.

Regards,
WeiShan

Leave a comment

Follow

Get every new post delivered to your Inbox.