Ivan Kartik - Oracle and Linux Blog

About 12c new feature: Online Datafile move

I consider as best of the new features those which allow to perform a maintenance or structure changes with no requirement to close the database or putting any part of it offline and especially when this feature is not bound to an extra paid option.
One of those comes with Oracle Database 12cR1 version and it can help reduce downtime for operation during which until 11gR2 needed downtime of database or tablespace needed to be offline. Simply said now you can move datafile online while the database/tablespace is open/online.

So we've got new command which we can use either to rename or relocate datafile:

ALTER DATABASE MOVE DATAFILE 'oldfilename' TO 'newfilename';

- Before moving or renaming of datafile ensure that you have sufficient free disk space that at minimum equals to size of moved datafile.

- Above command works for datafiles and it doesn't work for control files, temporary files or redo logs

- Command works irrespective of ARCHIVELOG or NOARCHIVELOG mode

- FLASHBACK DATABASE doesn't have any impact to this operation

- As always, do full backup of database after operation  (and do backup before as well)

Anyway, the idea behind this post is not to replace documentation (for detailed information about command and it's options take a look here: http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837) but to test how much time this operation needs to complete (or what is the expected overhead) and comparing to existing solution(s) used until now. I've skipped the rename of datafile on OS level as it doesn't have sense because operation using "mv" command is being done within a second while using MOVE DATAFILE method performs a full copy of datafile first. So this test consist of moving datafile only.

At first, the baseline using dd and cp commands which are common methods in existing solutions prior 12c (skipped shutdown/startup mount, tablespace offline/online operations and move/rename commands):

[oracle@lab00 ORA12CR1]$ time dd if=data01.dbf of=/storage/oradata01/ORA12CR1/newdata01.dbf bs=16384
1966081+0 records in
1966081+0 records out
32212271104 bytes (32 GB) copied, 29.0502 s, 1.1 GB/s

real    0m29.051s
user    0m0.286s
sys     0m28.719s

[oracle@lab00 ORA12CR1]$ time cp data02.dbf /storage/oradata01/ORA12CR1/newdata02.dbf

real    0m27.204s
user    0m0.074s
sys     0m27.094s

 Second test using DBMS_FILE_TRANSFER package:

SYS@ORA12CR1:SQL> create directory SRCDIR as '/storage/oradata00/ORA12CR1';

Directory created.

SYS@ORA12CR1:SQL> create directory TGTDIR as '/storage/oradata01/ORA12CR1';

Directory created.

SYS@ORA12CR1:SQL> set timing on
SYS@ORA12CR1:SQL> ALTER TABLESPACE TEST offline;

Tablespace altered.

Elapsed: 00:00:00.11
SYS@ORA12CR1:SQL> BEGIN DBMS_FILE_TRANSFER.COPY_FILE('SRCDIR','data01.dbf', 'TGTDIR', 'newdata01.dbf'); END;  
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:15.76
SYS@ORA12CR1:SQL> ALTER TABLESPACE test RENAME DATAFILE '/storage/oradata00/ORA12CR1/data01.dbf' TO '/storage/oradata01/ORA12CR1/newdata01.dbf';

Tablespace altered.

Elapsed: 00:00:00.06
SYS@:SQL> ALTER TABLESPACE data ONLINE;

Tablespace altered.

Elapsed: 00:00:00.08

Now with new feature, online move operation:

SYS@ORA12CR1:SQL> set timing on
SYS@ORA12CR1:SQL> ALTER DATABASE MOVE DATAFILE '/storage/oradata00/ORA12CR1/data01.dbf' TO '/storage/oradata01/ORA12CR1/newdata01.dbf';

Database altered.

Elapsed: 00:07:42.79

 We can see significant difference between old method (offline tablespace) and new feature (online datafile move). I would say it's a tax or penalty for online operation.

According to times as above I would personally choose the "offline" method in case of huge or massive migration of datafiles and especially when time matters. But in case of mission critical system where of course the uptime is the main target and in case serveral changes have to be made or mean time for the operation is not important then this option becomes very handy.

 

SecureFiles - using, licensing and changes in 12c

SecureFiles were introduced in 11gR1 as a replacement  for (or better enhancement of) existing LOB storage architecture (now known as BasicFiles) which used to be the default storage until 12cR1. You can of course use both types together in the same table.

Idea behind this post is to discuss changes in12c and also to eliminate confusion which is still around this feature and this confusion is very often created due to misinterpretation of Licensing terms.

The irony is that there are some companies specialized for Software Asset Management are very often providing partial or vague information which creates misinterpretations and confusion on customer's side or even worse they are presenting these misinterpretations to customers.

First of all there is no licensing implication in use of SecureFiles (you don't have to pay additional fees) unless you are using any of option such as Compression, Deduplication or Encryption.

According to Oracle Database documentation and Oracle Database Licensing information you need license only for:

  • Compression - Advanced Compression option
  • Deduplication - Advanced Compression option
  • Encryption - Advanced Security option

 Let's create a tables using standard command without additional options.

SYS@ORA11GR2:SQL> CREATE TABLE test.t1 ( a CLOB, b BLOB) LOB(a, b) STORE AS SECUREFILE;

Table created.

SYS@ORA11GR2:SQL> col table_name format a15
SYS@ORA11GR2:SQL> col segment_name format a32
SYS@ORA11GR2:SQL> select table_name, segment_name, securefile, encrypt, compression, deduplication from dba_lobs where owner = 'TEST';

TABLE_NAME      SEGMENT_NAME                     SEC ENCR COMPRE DEDUPLICATION
--------------- -------------------------------- --- ---- ------ ---------------
T1              SYS_LOB0000024831C00001$$        YES NO   NO     NO
T1              SYS_LOB0000024831C00002$$        YES NO   NO     NO

If you see similar output everything is fine and you're not required to pay any additional licensed option. There is a quotation of Kevin Jernigan, Senior Director Product Management at Oracle which confirm this conclusion:

"The change we made in Oracle Database 12c wrt SecureFiles and LOBs is that SecureFiles is now the default. So, when you create a LOB column in a table, if you don't specify BasicFiles or SecureFiles, it will default to SecureFiles. There is no change to licensing requirements - SecureFiles is available in all editions of Oracle Database 12c with no licensing requirements beyond the database license, and using the compression and deduplication features of SecureFiles LOBs requires licensing the Advanced Compression Option.

-KJ"

 Above statement talks about another important change which comes with 12cR1. SecureFiles is now default LOB storage. Until now you had to specify using of SecureFiles explicitly, now it's implicit. Let's check it.

SYS@ORA12CR1:SQL> CREATE TABLE test.t1 ( a CLOB, b BLOB);

Table created.

SYS@ORA12CR1:SQL> col table_name format a15
SYS@ORA12CR1:SQL> col segment_name format a32
SYS@ORA12CR1:SQL> select table_name, segment_name, securefile, encrypt, compression, deduplication from dba_lobs where owner = 'TEST';

TABLE_NAME      SEGMENT_NAME                     SEC ENCR COMPRE DEDUPLICATION
--------------- -------------------------------- --- ---- ------ ---------------
T1              SYS_LOB0000024836C00001$$        YES NO   NO     NO
T1              SYS_LOB0000024836C00002$$        YES NO   NO     NO

 This behaviour is caused by change of default value of DB_SECUREFILE instance parameter from PERMITTED to PREFERRED.

 Note that this parameter affects results of upgrade to 12c. Consider to remove (if explicitly set) this parameter before upgrade database.

Results of upgrade when DB_SECUREFILE is set to PERMITTED (default value for 11gR1 and 11gR2), PREFERRED (default value for 12cR1) and ALWAYS:

SYS@ORA12CR1> col owner format a20
SYS@ORA12CR1> select distinct owner, count(*) from dba_lobs where securefile = 'YES' group by owner;

OWNER                  COUNT(*)
-------------------- ----------
GSMADMIN_INTERNAL             1
XDB                         291
SYS                          19

Results of upgrade when DB_SECUREFILE is set to NEVER or IGNORE:

SYS@ORA12CR1> col owner format a20
SYS@ORA12CR1> select distinct owner, count(*) from dba_lobs where securefile = 'YES' group by owner;

no rows selected

 

Another change which comes with Oracle Database 12c is support for parallel DML operations for LOB columns stored as SecureFiles LOBs in non-partitioned tables and Direct load support for SecureFiles LOB columns that have context index defined on them. This extends Parallel DML functionality introduced in Oracle Database 11gR2. Note Parallel DML support applies also to BasicFiles in case of partitioned tables.

 

Archive for obsolete Installation papers

It's been quite long time since I made last post on my blog. Last couple of years were really busy full of very interesting but also very time consuming projects I had been working on. As my blog by the time became outdated, many paper obsolete and I hadn't been satisfied with blog engine itself I decided to make couple of changes.

I've changed the Blog engine, which is better to customize and easy to manage as well. Also I have removed all installation papers from main page, so this post is in fact archive of all those papers, just in case anybody would have been looking for, for any of reason.

 

Installation papers for Oracle Database Software

9i
Installation of Oracle 9i(R2) on Fedora 2,3,4,5,6
Installation of Oracle 9i(R2) on Enteprise Linux 4
Installation of Oracle 9i(R2) on SuSE 9.x,10.1

10g
Installation of Oracle 10g(R2) on EL and RH EL 3,4,5
Installation of Oracle 10g(R2) on Fedora 2,3,4
Installation of Oracle 10g(R2) on SuSE 9.x
Installation of Oracle 10g(R2) on Solaris 10 x86

11g
Installation of Oracle 11g(R1) on EL and RH EL 4,5
Installation of Oracle 11g(R1) on SLES10 and OpenSuSE
Installation of Oracle 11g(R2) on Solaris x86(64)
Installation of Oracle 11g(R2) on Solaris 11 Express

 

 

Couple of photos from meetup(s)...

Couple of photos from annual meetups. Spent time with a lot of brainy technologists (Oracle ACE's or ACE Director's of course) and worldwide well known people in Oracle community. Just name of few: great mentor and great guy Hans Forbrich, Kamran Agayev, Gwen Shapira, Alex Gorbachev, Arup Nanda, Frits Hoogland, Kyle Hailey, Ben Prusinski and Lillian Buziak as well...

undefined

undefinedundefined

undefinedundefined

undefined

undefinedundefined

undefined

undefined

 

Wrong count of CPU on HP-UX 11.31

We have two identical machines, two identical HW resources, two identical configurations but there is one significant difference. Output from machine A:


$ uname -srm
HP-UX B.11.31 ia64

SQL> select CPU_COUNT_CURRENT,CPU_CORE_COUNT_CURRENT,CPU_SOCKET_COUNT_CURRENT,CPU_COUNT_HIGHWATER from v$license;

CPU_COUNT_CURRENT CPU_CORE_COUNT_CURRENT CPU_SOCKET_COUNT_CURRENT CPU_COUNT_HIGHWATER
----------------- ---------------------- ------------------------ -------------------
               28                                              28                  28

$ machinfo
CPU info:
  8 Intel(R) Itanium 2 9100 series processors (1.6 GHz, 24 MB)
          533 MT/s bus, CPU version A1
          28 logical processors


# ioscan -kf | grep processor | wc -l
28

So everything seems to be fine (just output from v$license shows misleading info) and we've got 28 cores. No, it's not! Output from machine B:


$ uname -srm
HP-UX B.11.31 ia64

SQL> select
CPU_COUNT_CURRENT,CPU_CORE_COUNT_CURRENT,CPU_SOCKET_COUNT_CURRENT,CPU_COUNT_HIGHWATER from v$license;

CPU_COUNT_CURRENT CPU_CORE_COUNT_CURRENT CPU_SOCKET_COUNT_CURRENT CPU_COUNT_HIGHWATER
----------------- ---------------------- ------------------------ -------------------
               14                                              14                  14
$ machinfo
CPU info:
  8 Intel(R) Itanium 2 9100 series processors (1.6 GHz, 24 MB)
          533 MT/s bus, CPU version A1
          14 logical processors


# ioscan -kf | grep processor | wc -l
14

Machine A shows doubled amount of CPUs than on machine B. Both VPARs have 7x Intel Itanium 2 processors (2 cores each). So that means the vPar has 14 CPU cores. Machine B shows correct info but A doesn't. How to find correct information about CPUs in this case? We can use /sbin/vparstatus to clearly see that there are 14 cores assigned to vPar. Machine A:


[Virtual Partition Resource Summary]
                                CPU      Num   Num     Memory Granularity
Virtual Partition Name          Min/Max  CPUs  IO       ILM         CLM
==============================  =======  ====  ====  ==========  ==========
DB1                               1/ 24    14     7         512         512

Machine B:


[Virtual Partition Resource Summary]
                                CPU      Num   Num     Memory Granularity
Virtual Partition Name          Min/Max  CPUs  IO       ILM         CLM
==============================  =======  ====  ====  ==========  ==========
DB2                               1/ 24    14     6         512         512

It is very important to know correct information, at least in two cases, performance (monitoring, tuning or troubleshooting) and for correct product licensing. What is the difference between those two machines? The difference is that machine "B" has latest patches whilst machine "A" even it is patched but (according to swlist) certainly latest patches weren't used. Unfortunately I can't provide wich patch or bundle is fixing this problem for this moment but when I find it then I'll post it right here. Update: My theory was correct and the final answer is very simple - multithreading. This type of Itanium Processors have two threads for each core. You can set multithreading on partition level (this has been done on both of machines) and also on OS level by lcpu_attr and there was the difference. You can verify the settings using kctune |grep lcpu. So the conclusion is no matter what patches are installed in this case.

Really (not) useful example

I was reading official PHP documentation few days ago. While searching around for some information I found something really surprising. To be specific I found an example of using PDO::commmit() which was quite funny for me. Few years ago I was wondering during code review where some (really bad) ideas come from. Now I finally understand because when we can see this really "useful" example in PHP official guide, so at least I don't need to wonder anymore. I took the original code and changed it slightly to prepare the example for newcomers in this area for better understanding what's going on and why this example is bad. Original code:


<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Change the database schema */
$sth = $dbh->exec("DROP TABLE fruit");

/* Commit the changes */
$dbh->commit();

/* Database connection is now back in autocommit mode */
?>

New code:


<?php

try {
  $dbh = new PDO("oci:dbname=//nnn.nnn.nnn.nnn:1521/test", "usr", "pwd");

  // Check contents of "fruit" table

  echo "First check: ";

  $sth = $dbh->prepare("SELECT id FROM fruit");
  $sth->execute();
  $err = $sth->errorInfo();
  echo "<font color=\"red\">".$err[2]."";

  while($row = $sth->fetch()) {
       echo $row["ID"]."";
  }

  // ==== BEGIN OF ORIGINAL CODE ====

  /* Begin a transaction, turning off autocommit */
  $dbh->beginTransaction();

  /* Change the database schema */
  $sth = $dbh->exec("DROP TABLE fruit");

  /* Replaced Commit to Rollback */
  $dbh->rollback();

  /* Database connection is now back in autocommit mode */

  // ==== END OF THE ORIGINAL CODE ====

  // check contents of "fruit" table again
  echo "Second check: ";

  $sth = $dbh->prepare("SELECT id FROM fruit");
  $sth->execute();
  $err = $sth->errorInfo();
  echo "<font color=\"red\">".$err[2]."";

  while($row = $sth->fetch()) {
       echo $row["ID"] . "";
  }
} catch(PDOException $e) {
       echo $e->getMessage();
}

?>

So we have a table called namaed "FRUIT" which contains ten rows (increasing value from 1 to 10). The script (as shown above) selects and shows all rows from table "FRUIT" then executes the original code and then selects all rows from "FRUIT" table again. There is just one difference in original code. I changed COMMIT to ROLLBACK for better understanding of uselessnes of the Original code shown in example on PHP.net documentation.


First check:

1
2
3
4
5
6
7
8
9
10
Second check:
OCIStmtExecute: ORA-00942: table or view does not exist(...)

So as we can see we did do DROP TABLE and even we rollbacked the transaction the table remains dropped. Yes, this is pretty correct behaviour as DROP TABLE is DDL statement and you can only rollback (or commit) DML statement (INSERT, UPDATE, DELETE, MERGE). So if ROLLBACK doesn't work thus it means we don't need to use COMMIT at all. The second point is that DDL statement almost always does implicit commit before and after (I used the "almost" word because this is not true for all DDL statements for example for "ALTER SESSION" statement). So no matter we do before DDL it will be commited, so in case of possible rollback DML executed before DDL within a transaction will be not rollbacked. There are couple of things or statements which could be used as example but they used the worst ever and created totally useless example.

Installation article for 11g Release 2 for Solaris 11 Express (2010.11)

Oracle killed the OpenSolaris project (fork of former OpenSolaris is now OpenIndiana - http://openindiana.org) and released Solaris 11 Express. So the new Solaris is officially out and comes with several very interresting features but I'll mention about few which user may facing to during his work with this version. Root account is now Root role In fact this thing is not new. RBAC was here for long but now it is the default. When you try to log on as the "root" user then logon will fail. So you can't to logon directly. You need to log on as non-root user and then execute "su -". For successfull logon via "su -" command you need to have granted "root" role otherwise you will get "Roles can only be assumed by authorized users" message. Only first created user (you are creating it during installation) has this role granted by default. So for the other users root role needs to be granted. Example:


usermod -R root johndoe

Of course you can specify role (root role in this case) also during user creation. Example:


useradd -R root -m -d /export/home/johndoe johndoe

You may ask "Is there possibility to set old fashioned behaviour?" Yes, it is but be sure you really want to do that. To enable login as root directly execute following command:

rolemod -K type=normal root

Also to enable connect using SSH and allow root login you need to change PermitRootLogin in SSH configuration and restart SSH service.


PermitRootLogin yes

and then

svcadm restart ssh

Done. Some keywords for study: RBAC, sudo, pfexec, pf(shell name) The home directory and default shell Root role is not the only thing that first user will get. When you create new user using following command:


useradd oracle

Assume that first account created (during installation) was "ivan". If you compare entries in /etc/passwd you will find one difference. "ivan" account has /bin/bash as default shell and "oracle" has /bin/sh (which is symlink to ksh93 in fact). For change of shell to Bash just use the usermod command:


usermod -s /bin/bash oracle

The second difference is that /home/oracle does not exist even if it is stored in /etc/passwd file. When you try to create it manually you will get: "mkdir: cannot create directory `oracle': Operation not applicable". You may delete the user and try to create it using -d an -m switch. In that case you will get: "UX: useradd: ERROR: Unable to create the home directory: Operation not applicable.". The answer for this is simple. You can't do that because /home directory is maintained by AutoFS. Possible solutions (classic well known way): 1. Use different (existing) home directory. Example:


useradd -R root -m -d /export/home/johndoe johndoe

2. Modify /etc/auto_home add relevant entry for future user and then create it. Example:


echo "johndoe localhost:/export/home/&" >> /etc/auto_home
useradd -m -d /home/johndoe johndoe

Bugs and Fun No, this is not name of some feature. This will be just light description of issues I was facing to during the installation of Oracle Database.

- 11.2.0.1 version doesn't know Solaris 11 OS. In fact it will install correctly also it is functional it just skips the prerequistes check step. You need to download latest release 11.2.0.2 from My Oracle Support website.

- 11.2.0.2 version knows the Solaris 11 OS but during prerequisites check it requests to install non existing packages (replaced) packages i.e.: SUNWi1of and SUNWxwfnt (I belive there were replaced by SUNWfont-xorg-core and SUNWfont-xorg-iso8859-1 packages).

- 11.2.0.2 version requests to set LoginGraceTime 0 but I don't think this is a good idea even there is limit to max connections.

- There is lot of URLs to OpenSolaris website and lot of "OpenSolaris" texts in configuration files.

 

Installation article you can find here http://ivan.kartik.sk/oracle/install_ora11gR2_solaris11.html or in menu of this page. I belive that this paper will work also for last OpenSolaris versions and OpenIndiana (I wish long life to this project) as well. Enjoy.

Home ← Older posts