DBA survival BLOG

DBA stuff and Oracle Data Guard

Where are Extended Data Types stored? (12c)

Posted on May 23, 2014 by Ludovico

Oracle has introduced Extended Data Types in 12c. If you still don’t know what they are and how enable them, refer to the awesome oracle-base blog or the official documentation.

Here, I’ll show where they are stored, and when.

Tables created with extended varchar2 (length >4000)

If the table is created with a varchar2 length above 4000, a LOBSEGMENT and a LOBINDEX segments are also created automatically. But actually, the rows inserted may be short enough to be stored inline.

SQL>  create table ludovico.t32k2 (a number, b varchar2(20000));

Table created.

SQL>  DECLARE
  j number;
BEGIN
  FOR j IN 1..1000
  LOOP
    INSERT INTO ludovico.t32k2 values (j, RPAD('LONGSTRING',100,'*') );
  END LOOP;
END;
/   2    3    4    5    6    7    8    9

PL/SQL procedure successfully completed.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES     BLOCKS
------------------------------ ------------------ ---------- ----------
T32K2                          TABLE                  196608         24
SYS_IL0000096543C00002$$       LOBINDEX                65536          8
SYS_LOB0000096543C00002$$      LOBSEGMENT             131072         16

SQL> create table ludovico.t32k2 (a number, b varchar2(20000));

Table created.

SQL> DECLARE

j number;

BEGIN

FOR j IN 1..1000

LOOP

INSERT INTO ludovico.t32k2 values (j, RPAD('LONGSTRING',100,'*') );

END LOOP;

END;

/ 2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

------------------------------ ------------------ ---------- ----------

T32K2 TABLE 196608 24

SYS_IL0000096543C00002$$ LOBINDEX 65536 8

SYS_LOB0000096543C00002$$ LOBSEGMENT 131072 16

As you can see from the previous output, the LOB segments are almost empty after 1000 rows inserted, whereas the table has 24 blocks. Not enough to be a proof. Let’s try to update the rows with a column size of 1000:

SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',1000,'*');

1000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES     BLOCKS
------------------------------ ------------------ ---------- ----------
T32K2                          TABLE                 2097152        256
SYS_IL0000096537C00002$$       LOBINDEX                65536          8
SYS_LOB0000096537C00002$$      LOBSEGMENT             131072         16

SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',1000,'*');

1000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

------------------------------ ------------------ ---------- ----------

T32K2 TABLE 2097152 256

SYS_IL0000096537C00002$$ LOBINDEX 65536 8

SYS_LOB0000096537C00002$$ LOBSEGMENT 131072 16

A-ah! The row size is increasing but the blocks are actually allocated in the table segment, this prove that the rows are stored inline!

Where’s the break point? When the rows will be migrated to the out-of-line storage?

SQL>  update ludovico.t32k2 set b=RPAD('LONGSTRING',3950,'*');

1000 rows updated.

SQL>  commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME                  SEGMENT_TYPE            BYTES     BLOCKS
----------------------------- ------------------ ---------- ----------
SYS_LOB0000096549C00002$$     LOBSEGMENT             131072         16
SYS_IL0000096549C00002$$      LOBINDEX                65536          8
T32K2                         TABLE                 8388608       1024

SQL>  update ludovico.t32k2 set b=RPAD('LONGSTRING',3960,'*');

1000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME                  SEGMENT_TYPE            BYTES     BLOCKS
----------------------------- ------------------ ---------- ----------
SYS_LOB0000096549C00002$$     LOBSEGMENT             131072         16
SYS_IL0000096549C00002$$      LOBINDEX                65536          8
T32K2                         TABLE                 8388608       1024

SQL>  update ludovico.t32k2 set b=RPAD('LONGSTRING',3970,'*');

1000 rows updated.

SQL>  commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME                 SEGMENT_TYPE            BYTES     BLOCKS
---------------------------- ------------------ ---------- ----------
SYS_LOB0000096549C00002$$    LOBSEGMENT           10682368       1304 <<<<<<<<< HERE!
SYS_IL0000096549C00002$$     LOBINDEX                65536          8
T32K2                        TABLE                 8388608       1024

SQL>

SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',3950,'*');

1000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

----------------------------- ------------------ ---------- ----------

SYS_LOB0000096549C00002$$ LOBSEGMENT 131072 16

SYS_IL0000096549C00002$$ LOBINDEX 65536 8

T32K2 TABLE 8388608 1024

SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',3960,'*');

1000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

----------------------------- ------------------ ---------- ----------

SYS_LOB0000096549C00002$$ LOBSEGMENT 131072 16

SYS_IL0000096549C00002$$ LOBINDEX 65536 8

T32K2 TABLE 8388608 1024

SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',3970,'*');

1000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

---------------------------- ------------------ ---------- ----------

SYS_LOB0000096549C00002$$ LOBSEGMENT 10682368 1304 <<<<<<<<< HERE!

SYS_IL0000096549C00002$$ LOBINDEX 65536 8

T32K2 TABLE 8388608 1024

SQL>

Actually, it’s somewhere between 3960 and 3970 bytes, but it may depend on other factors (I havn’t tested it deeply).

Lesson learned: when you design your table with extended types, you should plan how many rows you expect with a size below 4000, because they can make your row access slower and chained rows higher than expected.

Tables created with standard varchar2 and altered afterward

Let’s try almost the same excercise, but this time starting with a table created with a small length (lower than 4000) and altered afterward to make use of the extended size.

 SQL> create table ludovico.t32k (a number, b varchar2(500));

Table created.

SQL>

SQL> DECLARE  
2  j number;  
3 BEGIN  
4   FOR j IN 1..1000  
5  LOOP  
6    INSERT INTO ludovico.t32k values (j, RPAD('LONGSTRING',100,'*') );  
7  END LOOP;  
8 END;  
9 /  
  
PL/SQL procedure successfully completed.  
  
SQL> select count(*) from ludovico.t32k;  
  
  COUNT(*)  
----------  
      1000  

SQL>  exec dbms_stats.gather_table_stats('LUDOVICO','T32K');  
  
PL/SQL procedure successfully completed.  

SQL> select TABLE_NAME, OBJECT_TYPE, NUM_ROWS, BLOCKS, 
2 EMPTY_BLOCKS, AVG_ROW_LEN from dba_tab_statistics 
3 where owner='LUDOVICO';

TABLE_NAME OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ------------ ---------- ---------- ------------ -----------
T32K       TABLE              1000         24            0         105

SQL> create table ludovico.t32k (a number, b varchar2(500));

Table created.

SQL>

SQL> DECLARE

2 j number;

3 BEGIN

4 FOR j IN 1..1000

5 LOOP

6 INSERT INTO ludovico.t32k values (j, RPAD('LONGSTRING',100,'*') );

7 END LOOP;

8 END;

9 /

PL/SQL procedure successfully completed.

SQL> select count(*) from ludovico.t32k;

COUNT(*)

----------

1000

SQL> exec dbms_stats.gather_table_stats('LUDOVICO','T32K');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME, OBJECT_TYPE, NUM_ROWS, BLOCKS,

2 EMPTY_BLOCKS, AVG_ROW_LEN from dba_tab_statistics

3 where owner='LUDOVICO';

TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

---------- ------------ ---------- ---------- ------------ -----------

T32K TABLE 1000 24 0 105

So far, so good. Let’s try to extend the column:

SQL>  alter table ludovico.t32k  modify (b varchar2(20000));

Table altered.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS
--------------- ------------------ ---------- ----------
T32K            TABLE                  196608         24

SQL> alter table ludovico.t32k modify (b varchar2(20000));

Table altered.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

--------------- ------------------ ---------- ----------

T32K TABLE 196608 24

The LOB segments have not been created. Meaning that the rows are still stored inline. Let’s try then to update all the rows to a size above 4000:

SQL> update ludovico.t32k set b=RPAD('LONGSTRING',15000 ,'*') ;

1000 rows updated.

SQL> commit;

Commit complete.

SQL>  select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS
--------------- ------------------ ---------- ----------
T32K            TABLE                16777216       2048

SQL> update ludovico.t32k set b=RPAD('LONGSTRING',15000 ,'*') ;

1000 rows updated.

SQL> commit;

Commit complete.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

--------------- ------------------ ---------- ----------

T32K TABLE 16777216 2048

The blocks have grown in the table segment, the rows are still inline!!

SQL> analyze table ludovico.t32k list chained rows into chained_rows;  
  
Table analyzed.  
  
SQL> select count(*) from chained_rows;  
  
  COUNT(*)  
----------  
      1000

SQL> analyze table ludovico.t32k list chained rows into chained_rows;

Table analyzed.

SQL> select count(*) from chained_rows;

COUNT(*)

----------

1000

This lead to a huge amount of chained rows!! In this example, 2 blocks per row, but it can be as high as 5 blocks for a single column varchar2(32767) with db_block_size=8192.

So we need to rebuild the table, right?

SQL> alter table  ludovico.t32k move;

Table altered.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS
--------------- ------------------ ---------- ----------
T32K            TABLE                17825792       2176

SQL> alter table ludovico.t32k move;

Table altered.

SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';

SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS

--------------- ------------------ ---------- ----------

T32K TABLE 17825792 2176

WRONG! Even after a rebuild the extended varchars are stored INLINE if they have been created as standard varchars. Actually you need to recreate the table and migrate with dbms_redefinition.

Migrating to extended datatypes instead of converting your application to use secure files can be a disaster for the physical layout of your tables, thus for the performance of your application. Be careful!

Update:

the limit is actually 3964 and it’s an internal mechanism of lobs since longtime: http://www.juliandyke.com/Presentations/LOBInternals.ppt
there is a hidden parameter: _scalar_type_lob_storage_threshold that controls the threshold of the varchar size specified in the definition before creating a segment for the lobs (default 4000). see this post: http://orasql.org/2013/07/13/oracle-12c-extended-varchars/
using extended datatypes instead of clobs may save tons of roundtrips when retrieving many rows: http://www.dbi-services.com/index.php/blog/entry/12c-extended-datatypes-better-than-clob

Many thanks to Sayan Malakshinov (@xtner) and Franck Pachot (@FranckPachot) for the information 🙂

Oracle Database Backup Logging Recovery Appliance – a preview

Posted on May 3, 2014 by Ludovico

Please see the disclaimer at the end of the post.

Oracle has announced the new Oracle Database Backup Logging Recovery Appliance at the last Open World 2013, but since then it has not been released to the market yet, and very few information is available on the Oracle website.

During the last IOUG Collaborate 14, Oracle master product manager of Data Guard and MAA, Larry Carpenter, has unveiled something more about the DBRLA (call it “Debra” to simplify your life 🙂 ) , and I’ve had the chance to discuss about it directly with Larry.

At Trivadis we think that this appliance will be a game changer in the world of backup management.

Why?

Well, if you have ever worked for a big company with many hundreds of databases, you have certainly encountered many of those common problems:

Oracle Backup and restore penalized by a shared infrastructure
Poor backup or restore performance
Tape drives busy when you need them urgently
Complex management of backup retentions

That’s not all. As of now, your best recovery point in case of restore is directly related to your backup archive frequency. Oh yes, you have to low down your archive_lag_target parameter, increase your log switch frequency (and thus, the I/O) and still have… 10, 15, 30 minutes of possible data loss? Unless you protect your transactions with a Data Guard. But this will cost you money. For the additional server and storage. For the licenses. And for the effort required to put in place a Data Guard instance for every database that you want to protect. You want to protect your transactions from a storage failure and there’s a price to pay.

The Database Backup Logging Recovery Appliance (wow, I need to copy and paste the name to save time! :-)) overcomes these problems with a simple but brilliant idea: leveraging the existing redo log transport processes and ship the redo stream directly to the backup appliance (the DBLRA, off course) or to its Cloud alter ego, hosted by Oracle.

As you can infer from the picture, 12c databases will work natively with the appliance, while previous releases will have a plugin that will enable all the capabilities.

Backups can be mirrored selectively to another DBLRA, or copied to the cloud or to a 3rd party (Virtual) Tape Library.

The backup retention is enforced by the appliance and the expiration and deletion is done automatically using the embedded RMAN catalog.

Lightning fast backups and restores are guaranteed by the hardware: DBLRA is based on the same hardware used by Exadata, with High Capacity disks. Optional storage extensions can be added to increase the capacity, but all the data, as I’ve said, can be offloaded to VTLs in order to use a cheaper storage for older backups.

To resume, the key values are:

No transaction loss!!
Lightning fast backups and restores
Integrated, Oracle engineered, scalable solution for hundreds to thousands of databases

Looking forward to see it in action!

I cannot cover all the information I have in a single post, but Trivadis is working actively to be ready to implement it at the time of the launch to the market (estimated in 2014), so feel free to contact me if you are interested in boosting your backup environment. 😉

By the way, I expect that the competitors (IBM, Microsoft?) will try to develop a solution with the same characteristics in terms of reliability, or they will lose terrain.

Cheers!

Ludovico

Disclaimer: This post is intended to outline Oracle’s general product direction based on the information gathered through public conferences. It is intended for informational purposes only. The development and release of these functionalities and features including the release dates remain at the sole discretion of Oracle and no documentation is available at this time. The features and commands shown may or may not be accurate when the final product release goes GA (General Availability).
Please refer Oracle documentation when it becomes available.

Oracle Multitenant and custom DBCA templates

Posted on March 28, 2014 by Ludovico

Today I’ve encountered an annoying issue while adapting a few scripts for automatic database creation. I track it here so hopefully it may save a few hours of troubleshooting to someone…

I’ve used the DBCA to prepare a new template:

dbca -> Create Database -> Advanced Mode -> General Purpose -> 
Create As Container Database -> Create an Empty Container Database

1 2	dbca -> Create Database -> Advanced Mode -> General Purpose -> Create As Container Database -> Create an Empty Container Database

Then continued by customizing other options, including init parameters and datafile/logfile paths. Finally, I’ve saved it as a new template instead of creating the database.

I’ve checked the resulting .dbc and seen that there was, as expected, the parameter “enable_pluggable_database”=”true”.

Then I’ve moved the template file to my $HOME directory and tested the silent database creation with the option “-createAsContainerDatabase true”:

$ ${ORACLE_HOME}/bin/dbca -silent \
-createDatabase \
-createAsContainerDatabase true  \
-templateName /home/oracle/Template_12_EE_MULTITENANT.dbc \
-gdbname cdb1 \
-sid cdb1 \
-responseFile NO_VALUE \
-characterSet AL32UTF8 \
-SYSPASSWORD *** \
-SYSTEMPASSWORD *** \
-redoLogFileSize 256 \
-initparams open_cursors=1300 \
-initparams processes=3000 \
-initparams enable_pluggable_database=true

$ ${ORACLE_HOME}/bin/dbca -silent \

-createDatabase \

-createAsContainerDatabase true \

-templateName /home/oracle/Template_12_EE_MULTITENANT.dbc \

-gdbname cdb1 \

-sid cdb1 \

-responseFile NO_VALUE \

-characterSet AL32UTF8 \

-SYSPASSWORD *** \

-SYSTEMPASSWORD *** \

-redoLogFileSize 256 \

-initparams open_cursors=1300 \

-initparams processes=3000 \

-initparams enable_pluggable_database=true

The database configuration has completed successfully, without errors. I’ve accessed my new container, and I’ve been surprised by seing:

SQL> select * from v$pdbs;

no rows selected

SQL>

SQL> select * from v$pdbs;

no rows selected

SQL>

In fact, there were no pdb$seed datafiles:

SQL> select distinct con_id from cdb_data_files;

    CON_ID
----------
         1

SQL>

SQL> select distinct con_id from cdb_data_files;

CON_ID

----------

SQL>

After little investigation, I’ve found these lines in the dbca trace.log:

[main] [ 2014-03-28 10:37:53.956 CET ] [Host.startOperation:2651]  is Template CDB false
[main] [ 2014-03-28 10:37:53.956 CET ] [TemplateManager.isInstallTemplate:2314]  Selected Template by user:=Template CEI 8K 12 EE MULTITENANT
[main] [ 2014-03-28 10:37:53.956 CET ] [TemplateManager.isInstallTemplate:2321]  The Message Id to be searched:=null
[main] [ 2014-03-28 10:37:53.957 CET ] [Host.startOperation:2663]  Template Selected is User created NON-CDB Template. Creating database as NON-CDB
[main] [ 2014-03-28 10:37:53.957 CET ] [HAUtils.getCurrentOracleHome:490]  Oracle home from system property: /ccv/app/oracle/product/12.1.0.1

[main] [ 2014-03-28 10:37:53.956 CET ] [Host.startOperation:2651] is Template CDB false

[main] [ 2014-03-28 10:37:53.956 CET ] [TemplateManager.isInstallTemplate:2314] Selected Template by user:=Template CEI 8K 12 EE MULTITENANT

[main] [ 2014-03-28 10:37:53.956 CET ] [TemplateManager.isInstallTemplate:2321] The Message Id to be searched:=null

[main] [ 2014-03-28 10:37:53.957 CET ] [Host.startOperation:2663] Template Selected is User created NON-CDB Template. Creating database as NON-CDB

[main] [ 2014-03-28 10:37:53.957 CET ] [HAUtils.getCurrentOracleHome:490] Oracle home from system property: /ccv/app/oracle/product/12.1.0.1

Then I’ve struggled with dbca and templates a few times before finding that, actually, the magic “enable pluggable database” is done by dbca only if the template file name is not customized.

Running the same exact command with the very same template file but renamed to $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc actually works (notice the diff at the first line):

$ diff /home/oracle/Template_12_EE_MULTITENANT.dbc $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
$ ${ORACLE_HOME}/bin/dbca -silent \
-createDatabase \
-createAsContainerDatabase true  \
-templateName General_Purpose.dbc \
-gdbname cdb1 \
-sid cdb1 \
-responseFile NO_VALUE \
-characterSet AL32UTF8 \
-SYSPASSWORD *** \
-SYSTEMPASSWORD *** \
-redoLogFileSize 256 \
-initparams open_cursors=1300 \
-initparams processes=3000 \
-initparams enable_pluggable_database=true

$ diff /home/oracle/Template_12_EE_MULTITENANT.dbc $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

$ ${ORACLE_HOME}/bin/dbca -silent \

-createDatabase \

-createAsContainerDatabase true \

-templateName General_Purpose.dbc \

-gdbname cdb1 \

-sid cdb1 \

-responseFile NO_VALUE \

-characterSet AL32UTF8 \

-SYSPASSWORD *** \

-SYSTEMPASSWORD *** \

-redoLogFileSize 256 \

-initparams open_cursors=1300 \

-initparams processes=3000 \

-initparams enable_pluggable_database=true

SQL> select * from v$pdbs;

    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE
---------- ----------
         2 4086042395 4086042395 F5A8226121F93B96E0434B96780A8C91
PDB$SEED                       READ ONLY  NO
28-MAR-14 10.20.42.813 AM
   1720341  283115520

SQL> select distinct con_id from cdb_data_files;

    CON_ID
----------
         2
         1

SQL>

SQL> select * from v$pdbs;

CON_ID DBID CON_UID GUID

---------- ---------- ---------- --------------------------------

NAME OPEN_MODE RES

------------------------------ ---------- ---

OPEN_TIME

---------------------------------------------------------------------------

CREATE_SCN TOTAL_SIZE

---------- ----------

2 4086042395 4086042395 F5A8226121F93B96E0434B96780A8C91

PDB$SEED READ ONLY NO

28-MAR-14 10.20.42.813 AM

1720341 283115520

SQL> select distinct con_id from cdb_data_files;

CON_ID

----------

SQL>

I’ve also tried to cheat and use a symlink to my previous custom template, and surprisingly, it still works:

$ ls -l $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc
lrwxrwxrwx 1 oracle dba 74 Mar 28 13:07 /u01/app/oracle/product/12.1.0.1/assistants/dbca/templates/General_Purpose.dbc -> /home/oracle/Template_12_EE_MULTITENANT.dbc
$

$ ls -l $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

lrwxrwxrwx 1 oracle dba 74 Mar 28 13:07 /u01/app/oracle/product/12.1.0.1/assistants/dbca/templates/General_Purpose.dbc -> /home/oracle/Template_12_EE_MULTITENANT.dbc

In the dbca trace log the message saying that the DB will be NON-CDB disappears:

[main] [ 2014-03-28 10:12:14.683 CET ] [Host.startOperation:2651]  is Template CDB false
[main] [ 2014-03-28 10:12:14.683 CET ] [TemplateManager.isInstallTemplate:2314]  Selected Template by user:=General Purpose
[main] [ 2014-03-28 10:12:14.683 CET ] [TemplateManager.isInstallTemplate:2321]  The Message Id to be searched:=GENERAL_PURPOSE
[main] [ 2014-03-28 10:12:14.683 CET ] [HAUtils.getCurrentOracleHome:490]  Oracle home from system property: /ccv/app/oracle/product/12.1.0.1

[main] [ 2014-03-28 10:12:14.683 CET ] [Host.startOperation:2651] is Template CDB false

[main] [ 2014-03-28 10:12:14.683 CET ] [TemplateManager.isInstallTemplate:2314] Selected Template by user:=General Purpose

[main] [ 2014-03-28 10:12:14.683 CET ] [TemplateManager.isInstallTemplate:2321] The Message Id to be searched:=GENERAL_PURPOSE

[main] [ 2014-03-28 10:12:14.683 CET ] [HAUtils.getCurrentOracleHome:490] Oracle home from system property: /ccv/app/oracle/product/12.1.0.1

So the problem is really caused by the different filename/location of the template.

IMHO it’s a kind of bug, the decision between a CDB and NON-CDB should not be taken by DBCA. Moreover, it’s not based on the content of the template, which would be logic. But today I’m late and lazy, I will not open a SR for this.

:-/

RAC Attack 12c at Collaborate 14 and advanced labs

Posted on March 28, 2014 by Ludovico

I’ve just published an advanced lab on SlideShare that RAC Attack attendees may do at Collaborate this year, instead of just doing the basic 2-node RAC installation on their laptop.

We’ll offer also an advanced lab about Flex Clusters and Flex ASM (written by Maaz Anjum). Moreover, I’m working on an additional lab that allows to implement a multi-node RAC by using Virtual Box linked clones and GI Home clones like I’ve shown in my previous post.

RAC Attack at #C14LV will be like fun again. We’ll have a few t-shirts for the attendants, designed by me and Chet “Oraclenerd” Justice, kindly sponsored by OTN.

The workshop will end up with beers and snaks (again, thank you OTN for sponsoring this :-)).

If you’re planning to attend Collaborate, join us and start your conference week in a good mood 🙂

Multinode RAC 12c cluster on VirtualBox using linked clones

Posted on March 16, 2014 by Ludovico

Recently I’ve had to install a four-node RAC cluster on my laptop in order to do some tests. I have found an “easy” (well, easy, it depends), fast and space-efficient way to do it so I would like to track it down.

The quick step list

Install the OS on the first node
Add the shared disks
Install the clusterware in RAC mode on on the first node only
Remove temporarily the shared disks
Clone the server as linked clone as many times as you want
Reconfigure the new nodes with the new ip and naming
Add back the shared disks on the first node and on all other nodes
Clone the GI + database homes in order to add them to the cluster

Using this method the Oracle binaries (the most space consuming portion of the RAC installation) are installed and allocated on the first node only.

The long step list

Actually you can follow many instruction steps from the RAC Attack 12c book.

Review the HW requirements but let at least 3Gb RAM for each guest + 2Gb more for your host (you may try with less RAM but everything will slow down).
Download all the SW components , additionally you may download the latest PSU (12.1.0.1.2) from MOS.
Prepare the host and install linux on the first node. When configuring the OS, make sure you enter all the required IP addresses for the additional nodes. RAC Attack has two nodes collabn1, collabn2. Add as many nodes as you want to configure. As example, the DNS config may have four nodes

collabn1        A       192.168.78.51
collabn2        A       192.168.78.52
collabn3        A       192.168.78.53
collabn4        A       192.168.78.54
collabn1-vip    A       192.168.78.61
collabn2-vip    A       192.168.78.62
collabn3-vip    A       192.168.78.63
collabn4-vip    A       192.168.78.64
collabn1-priv   A       172.16.100.51
collabn2-priv   A       172.16.100.52
collabn3-priv   A       172.16.100.53
collabn4-priv   A       172.16.100.54
collabn-cluster-scan     A       192.168.78.251
collabn-cluster-scan     A       192.168.78.252
collabn-cluster-scan     A       192.168.78.253

collabn1 A 192.168.78.51

collabn2 A 192.168.78.52

collabn3 A 192.168.78.53

collabn4 A 192.168.78.54

collabn1-vip A 192.168.78.61

collabn2-vip A 192.168.78.62

collabn3-vip A 192.168.78.63

collabn4-vip A 192.168.78.64

collabn1-priv A 172.16.100.51

collabn2-priv A 172.16.100.52

collabn3-priv A 172.16.100.53

collabn4-priv A 172.16.100.54

collabn-cluster-scan A 192.168.78.251

collabn-cluster-scan A 192.168.78.252

collabn-cluster-scan A 192.168.78.253

Create the shared disks and configure the persistent storage naming using udev.

At this point, the procedure starts differing from the RAC Attack book.

Skip the creation of the second host and go directly to the VNC Server setup.
Install the Grid Infrastructure and the Database software using only the first node.
You may want to install the latest PSU (12.1.0.1.2), use my previous post as guideline
Once the GI + DB are installed correctly, stop and disable the crs on the first node:

# <GIHOME>/bin/crsctl stop crs
# <GIHOME>/bin/crsctl disable crs
# shutdown -h now

# <GIHOME>/bin/crsctl stop crs

# <GIHOME>/bin/crsctl disable crs

# shutdown -h now

Go to the VirtualBox VM settings and delete all the shared disks
Clone the first server as linked clone (right-click, clone, choose the name, flag “Linked Clone” as many times as the number of additional servers you want.

By using this method the new servers will use the same virtual disk file of the first server and a second file will be used to track the differences. This will save a lot of space on the disk.
Add back the shared disks to all the servers.
Start the other nodes and configure them following the RAC Attack instructions again.
Once all the nodes are configured, the GI installation has to be cleaned out on all the cloned servers using these guidelines:

cd $GI_HOME
rm -rf log/$(hostname)
rm -rf gpnp/$(hostname)
find gpnp -type f -exec rm -f {} \;
rm -rf cfgtoollogs/*
rm -rf crs/init/*
rm -rf cdata/*
rm -rf crf/*
rm -rf network/admin/*.ora
rm -rf crs/install/crsconfig_params
find . -name '*.ouibak' -exec rm {} \;
find . -name '*.ouibak.1' -exec rm {} \;
rm -rf root.sh*
rm -rf rdbms/audit/*
rm -rf rdbms/log/*
rm -rf inventory/backup/*
chown -R oracle:oinstall /u01/app

rm -f /etc/init.d/ohasd 
rm -rf /etc/oracle 
rm -rf /u01/app/oraInventory/*

cd $GI_HOME

rm -rf log/$(hostname)

rm -rf gpnp/$(hostname)

find gpnp -type f -exec rm -f {} \;

rm -rf cfgtoollogs/*

rm -rf crs/init/*

rm -rf cdata/*

rm -rf crf/*

rm -rf network/admin/*.ora

rm -rf crs/install/crsconfig_params

find . -name '*.ouibak' -exec rm {} \;

find . -name '*.ouibak.1' -exec rm {} \;

rm -rf root.sh*

rm -rf rdbms/audit/*

rm -rf rdbms/log/*

rm -rf inventory/backup/*

chown -R oracle:oinstall /u01/app

rm -f /etc/init.d/ohasd

rm -rf /etc/oracle

rm -rf /u01/app/oraInventory/*

Then, on each cloned server, run the perl clone.pl as follows to clone the GI home, but change the LOCAL_NODE accordingly (note: the GI Home name must be identical to the one specified in the original installation!):

[oracle@collabn2 bin]$ perl clone.pl -silent ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/12.1.0/grid \
 ORACLE_HOME_NAME=OraGI12Home1 INVENTORY_LOCATION=/u01/app/oraInventory \
 LOCAL_NODE=collabn2 "CLUSTER_NODES={collabn1,collabn2,collabn3,collabn4}"  CRS=TRUE 

 ./runInstaller -clone -waitForCompletion "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/12.1.0/grid" "ORACLE_HOME_NAME=OraGI12Home1" "INVENTORY_LOCATION=/u01/app/oraInventory" "LOCAL_NODE=collabn2" "CLUSTER_NODES={collabn1,collabn2}" "CRS=TRUE" -silent -paramFile /u01/app/12.1.0/grid/clone/clone_oraparam.ini 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 5537 MB Passed 
Checking swap space: must be greater than 500 MB. Actual 3012 MB Passed 
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-02-18_03-40-00PM. Please wait ...
You can find the log of this install session at: /u01/app/oraInventory/logs/cloneActions2014-02-18_03-40-00PM.log
 .................................................. 5% Done.
 .................................................. 10% Done.
 .................................................. 15% Done.
 .................................................. 20% Done.
 .................................................. 25% Done.
 .................................................. 30% Done.
 .................................................. 35% Done.
 .................................................. 40% Done.
 .................................................. 45% Done.
 .................................................. 50% Done.
 .................................................. 55% Done.
 .................................................. 60% Done.
 .................................................. 65% Done.
 .................................................. 70% Done.
 .................................................. 75% Done.
 .................................................. 80% Done.
 .................................................. 85% Done.
 .................................................. 90% Done.
 .................................................. 95% Done.
 Copy files in progress.

 Copy files successful.

 Link binaries in progress.

 Link binaries successful.

 Setup files in progress.

 Setup files successful.

 Setup Inventory in progress.

 Setup Inventory successful.

 Finish Setup successful.
 The cloning of OraGI12Home1 was successful. 
 Please check '/u01/app/oraInventory/logs/cloneActions2014-02-18_03-40-00PM.log' for more details.

 As a root user, execute the following script(s): 
 	1. /u01/app/12.1.0/grid/root.sh 

Execute /u01/app/12.1.0/grid/root.sh on the following nodes: 
[collabn2,collabn2,collabn3,collabn4]
.................................................. 100% Done.
[oracle@collabn2 bin]$ su - Password: 
[root@collabn2 ~]# /u01/app/oraInventory/orainstRoot.sh 
Changing permissions of /u01/app/oraInventory. 
Adding read,write permissions for group. 
Removing read,write,execute permissions for world. 
Changing groupname of /u01/app/oraInventory to oinstall. 
The execution of the script is complete. 
[root@collabn2 ~]#

[oracle@collabn2 bin]$ perl clone.pl -silent ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/12.1.0/grid \

ORACLE_HOME_NAME=OraGI12Home1 INVENTORY_LOCATION=/u01/app/oraInventory \

LOCAL_NODE=collabn2 "CLUSTER_NODES={collabn1,collabn2,collabn3,collabn4}" CRS=TRUE

./runInstaller -clone -waitForCompletion "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/12.1.0/grid" "ORACLE_HOME_NAME=OraGI12Home1" "INVENTORY_LOCATION=/u01/app/oraInventory" "LOCAL_NODE=collabn2" "CLUSTER_NODES={collabn1,collabn2}" "CRS=TRUE" -silent -paramFile /u01/app/12.1.0/grid/clone/clone_oraparam.ini

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 5537 MB Passed

Checking swap space: must be greater than 500 MB. Actual 3012 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-02-18_03-40-00PM. Please wait ...

You can find the log of this install session at: /u01/app/oraInventory/logs/cloneActions2014-02-18_03-40-00PM.log

.................................................. 5% Done.

.................................................. 10% Done.

.................................................. 15% Done.

.................................................. 20% Done.

.................................................. 25% Done.

.................................................. 30% Done.

.................................................. 35% Done.

.................................................. 40% Done.

.................................................. 45% Done.

.................................................. 50% Done.

.................................................. 55% Done.

.................................................. 60% Done.

.................................................. 65% Done.

.................................................. 70% Done.

.................................................. 75% Done.

.................................................. 80% Done.

.................................................. 85% Done.

.................................................. 90% Done.

.................................................. 95% Done.

Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.

The cloning of OraGI12Home1 was successful.

Please check '/u01/app/oraInventory/logs/cloneActions2014-02-18_03-40-00PM.log' for more details.

As a root user, execute the following script(s):

1. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/12.1.0/grid/root.sh on the following nodes:

[collabn2,collabn2,collabn3,collabn4]

.................................................. 100% Done.

[oracle@collabn2 bin]$ su - Password:

[root@collabn2 ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.

The execution of the script is complete.

[root@collabn2 ~]#

Then, on the first node (that you have started and you have reactivated the clusterware stack on it with crsctl enable crs / crsctl start crs ;-)), run this command to add the new nodes in the definition of the cluster:

[oracle@collabn1 addnode]$ ./addnode.sh -silent -noCopy ORACLE_HOME=/u01/app/12.1.0/grid "CLUSTER_NEW_NODES={collabn2,collabn3,collabn4}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={collabn2-vip,collabn3-vip,collabn4-vip}"
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 6125 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3017 MB    Passed
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/addNodeActions2014-02-18_03-43-22PM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/addNodeActions2014-02-18_03-43-22PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

Prepare Configuration in progress.

Prepare Configuration successful.
..................................................   40% Done.

As a root user, execute the following script(s):
        1. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/12.1.0/grid/root.sh on the following nodes:
[collabn2,collabn3,collabn4]

The scripts can be executed in parallel on all the nodes. If there are any policy managed databases managed by cluster, proceed with the addnode procedure without executing the root.sh script. Ensure that root.sh script is executed after all the policy managed databases managed by clusterware are extended to the new nodes.
..................................................   60% Done.

Update Inventory in progress.
..................................................   100% Done.

Update Inventory successful.
Successfully Setup Software.

[oracle@collabn1 addnode]$ ./addnode.sh -silent -noCopy ORACLE_HOME=/u01/app/12.1.0/grid "CLUSTER_NEW_NODES={collabn2,collabn3,collabn4}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={collabn2-vip,collabn3-vip,collabn4-vip}"

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 6125 MB Passed

Checking swap space: must be greater than 150 MB. Actual 3017 MB Passed

[WARNING] [INS-13014] Target environment does not meet some optional requirements.

CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/addNodeActions2014-02-18_03-43-22PM.log

ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/addNodeActions2014-02-18_03-43-22PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

Prepare Configuration in progress.

Prepare Configuration successful.

.................................................. 40% Done.

As a root user, execute the following script(s):

1. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/12.1.0/grid/root.sh on the following nodes:

[collabn2,collabn3,collabn4]

The scripts can be executed in parallel on all the nodes. If there are any policy managed databases managed by cluster, proceed with the addnode procedure without executing the root.sh script. Ensure that root.sh script is executed after all the policy managed databases managed by clusterware are extended to the new nodes.

.................................................. 60% Done.

Update Inventory in progress.

.................................................. 100% Done.

Update Inventory successful.

Successfully Setup Software.

from the first server copy these files on all the other nodes:

scp -rp /u01/app/12.1.0/grid/crs/install/crsconfig_addparams collabn2:/u01/app/12.1.0/grid/crs/install/crsconfig_addparams
scp -rp /u01/app/12.1.0/grid/crs/install/crsconfig_params collabn2:/u01/app/12.1.0/grid/crs/install/crsconfig_params
scp -rp /u01/app/12.1.0/grid/gpnp collabn2:/u01/app/12.1.0/grid/gpnp

scp -rp /u01/app/12.1.0/grid/crs/install/crsconfig_addparams collabn2:/u01/app/12.1.0/grid/crs/install/crsconfig_addparams

scp -rp /u01/app/12.1.0/grid/crs/install/crsconfig_params collabn2:/u01/app/12.1.0/grid/crs/install/crsconfig_params

scp -rp /u01/app/12.1.0/grid/gpnp collabn2:/u01/app/12.1.0/grid/gpnp

Then clone also the DB Home (again, run it on each new server and specify the same DB home name that you have used in the original installation):

[oracle@collabn2 bin]$ perl clone.pl -O 'CLUSTER_NODES={collabn1,collabn2,collabn3,collabn4}' -O LOCAL_NODE=collabn2 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 ORACLE_HOME_NAME=OraDB12Home1 -O -noConfig
./runInstaller -clone -waitForCompletion   "CLUSTER_NODES={collabn1,collabn2,collabn3,collabn4}"  "LOCAL_NODE=collabn2" "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1" "ORACLE_HOME_NAME=OraDB12Home1"  -noConfig  -silent -paramFile /u01/app/oracle/product/12.1.0/dbhome_1/clone/clone_oraparam.ini
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 3896 MB    Passed
Checking swap space: must be greater than 500 MB.   Actual 3005 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-02-18_05-22-22PM. Please wait ...You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2014-02-18_05-22-22PM.log
..................................................   5% Done.
..................................................   10% Done.
..................................................   15% Done.
..................................................   20% Done.
..................................................   25% Done.
..................................................   30% Done.
..................................................   35% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
..................................................   85% Done.
..................................................   90% Done.
..................................................   95% Done.

Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup in progress.

Finish Setup successful.
The cloning of OraDB12Home1 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2014-02-18_05-22-22PM.log' for more details.

As a root user, execute the following script(s):
        1. /u01/app/oracle/product/12.1.0/dbhome_1/root.sh

Execute /u01/app/oracle/product/12.1.0/dbhome_1/root.sh on the following nodes:
[collabn2]

..................................................   100% Done.

[oracle@collabn2 bin]$ perl clone.pl -O 'CLUSTER_NODES={collabn1,collabn2,collabn3,collabn4}' -O LOCAL_NODE=collabn2 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 ORACLE_HOME_NAME=OraDB12Home1 -O -noConfig

./runInstaller -clone -waitForCompletion "CLUSTER_NODES={collabn1,collabn2,collabn3,collabn4}" "LOCAL_NODE=collabn2" "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1" "ORACLE_HOME_NAME=OraDB12Home1" -noConfig -silent -paramFile /u01/app/oracle/product/12.1.0/dbhome_1/clone/clone_oraparam.ini

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 3896 MB Passed

Checking swap space: must be greater than 500 MB. Actual 3005 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-02-18_05-22-22PM. Please wait ...You can find the log of this install session at:

/u01/app/oraInventory/logs/cloneActions2014-02-18_05-22-22PM.log