Skip navigation.

Yann Neuhaus

Syndicate content
All blog entries from http://www.dbi-services.com/
Updated: 14 hours 5 min ago

How to remap tablespaces using Oracle Import/Export Tool

Tue, 2014-08-26 02:04

Since Oracle 10g, Oracle provides a great tool to import and export data from databases: Data Pump. This tool offers several helpful options, particularly one that allows to import data in a different tablespace than the source database. This parameter is REMAP_TABLESPACE. However, how can you do the same when you cannot use Data Pump to perform Oracle import and export operations?

I was confronted with this issue recently, and I had to deal with different workarounds to accomplish my import successfully. The main case where you will not be able to use Datapump is when you want to export data from a pre-10g database. And believe me, there are still a lot more of these databases running over the world than we think! The Oracle Import/Export utility does not provide a built-in way to remap tablespace like Datapump. In this blog posting, I will address the different workarounds to import data in a different tablespace with the Oracle Import Export Tool.

I have used an Oracle 11g R2 database for all examples.

My source schema 'MSC' on the Oracle Database DB11G is using the default tablespace USERS. I want to import the data to a different schema 'DBI' using the USER_DATA tablespace.

The objects contained in the source schema are as follows:

 

SQL> select object_type, object_name from dba_objects where owner='MSC';
OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
SEQUENCE            MSCSEQ
TABLE               USER_DATA
TABLE               USER_DOCUMENTS
INDEX               SYS_IL0000064679C00003$$
LOB                 SYS_LOB0000064679C00003$$
INDEX               PK_DATAID
INDEX               PK_DOCID

 

I will now export the schema MSC using exp:

 

[oracle@srvora01 dpdump]$ exp msc/Passw0rd file=exp_MSC.dmp log=exp_MSC.log consistent=y
Export: Release 11.2.0.3.0 - Production on Tue Aug 12 14:40:44 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MSC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MSC
About to export MSC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MSC's tables via Conventional Path ...
. . exporting table                      USER_DATA      99000 rows exported
. . exporting table                 USER_DOCUMENTS         25 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

 

I tried different ways to accomplish the tablespace remapping with imp. They are summarized below.

 

Revoke quota on USERS tablespace for the destination schema

I have read somewhere that a workaround could be to revoke UNLIMITED TABLESPACE (if granted) and any quota on USERS tablespace for the destination schema, and to grant UNLIMITED QUOTA on the target tablespace only (USER_DATA). This way, imp tool is supposed to import all objects into the schema default tablespace.

Let's try this. I have created the destination schema with the right default tablespace and temporary tablespace and the required privileges:

 

SQL> create user DBI identified by Passw0rd default tablespace USER_DATA temporary tablespace TEMP quota unlimited on USER_DATA;
User created.

 SQL> grant create session, create table to DBI;
Grant succeeded.

 

The privilege UNLIMITED TABLESPACE is not granted to DBI user. Now, I will try to run the import:

 

[oracle@srvora01 dpdump]$ imp system/Passw0rd file=exp_MSC.dmp log=imp_to_DBI.log fromuser=msc touser=dbi
Import: Release 11.2.0.3.0 - Production on Tue Aug 12 14:53:47 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by MSC, not by you
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing MSC's objects into DBI
. . importing table                    "USER_DATA"      99000 rows imported
IMP-00017: following statement failed with ORACLE error 1950:
 "CREATE TABLE "USER_DOCUMENTS" ("DOC_ID" NUMBER, "DOC_TITLE" VARCHAR2(25), ""
 "DOC_VALUE" BLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
 "TIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
 "OOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("DOC_VALUE") STORE "
 "AS BASICFILE  (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTIO"
 "N  NOCACHE LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELIS"
 "TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
Import terminated successfully with warnings.

 

The import has worked only for a part of the objects. As we can see, it seems that the table app_documents, containing a BLOB column, has not been imported. All objects associated to this table are not imported:

 

SQL> select object_type, object_name from dba_objects where owner='DBI';
OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
TABLE               USER_DATA
SEQUENCE            MSCSEQ
INDEX               PK_DATAID

 

With no quota on the source tablespace, imp tool imports data in the target schema default tablespace, but LOBS are not supported.

 

Drop USERS tablespace prior to import data

Another method could be to drop the source tablespace, to be sure that the import tool does not try to import data in the USERS tablespace.

In this example, I will drop the USERS tablespace and try to import data again with the same command. Note that I have previously dropped the MSC and DBI schemas prior to dropping the USERS tablespace.

 

SQL> alter database default tablespace SYSTEM;
Database altered.

 

SQL> drop tablespace USERS including contents and datafiles;
Tablespace dropped.

 

I will now recreate the empty DBI schema, as shown in example 1:

 

SQL> create user DBI identified by Passw0rd default tablespace USER_DATA temporary tablespace TEMP quota unlimited on USER_DATA;
User created. 

 

SQL> grant create session, create table to DBI;
Grant succeeded.

 

Now let's try to import the data again from the dump file:

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] imp system/Passw0rd file=exp_MSC.dmp log=imp_to_DBI.log fromuser=msc touser=dbi
Import: Release 11.2.0.3.0 - Production on Tue Aug 12 17:03:50 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by MSC, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing MSC's objects into DBI
. . importing table                    "USER_DATA"      99000 rows imported
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "USER_DOCUMENTS" ("DOC_ID" NUMBER, "DOC_TITLE" VARCHAR2(25), ""
 "DOC_VALUE" BLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
 "TIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
 "OOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("DOC_VALUE") STORE "
 "AS BASICFILE  (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTIO"
 "N  NOCACHE LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELIS"
 "TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USERS' does not exist
Import terminated successfully with warnings.

 

You can see that we have still have an error when importing the USER_DOCUMENTS table, but this time the error says "Tablespace USERS does not exist". So, whatever we do, imp tool tries to import LOBS in the same tablespace. But other data is imported in the new DEFAULT tablespace of the schema.

We can say that imp has the same behavior no matter whether we revoke the quota on the source tablespace or whether we drop it. Clearly, LOBs are not supported by this method. But if you have a database with standard data, these two methods would help you to remap the tablespace at import time.

 

Pre-create objects in the new tablespace using the INDEXFILE option

Imp tool provides the option INDEXFILE which corresponds to the METADATA ONLY with expdp. There is one difference: while impdp directly creates object structures with METADATA_ONLY=Y in the database without any data, imp with the INDEXFILE option will just generate an sql file with all CREATE statements (tables, indexes etc.) and you will have to manually run this file with sqlplus to create the empty objects.

As you may expect (or not), this SQL file will allow you to change the tablespace name when objects have to be created, prior to importing data. The inconvenience is that several manual steps are involved in this workaround - the solution is described below.

 

1) Generate the SQL file

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] imp system/Passw0rd file=exp_MSC.dmp log=imp_to_DBI.log fromuser=msc touser=dbi indexfile=imp_to_DBI.sql
Import: Release 11.2.0.3.0 - Production on Tue Aug 12 18:04:13 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by MSC, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. . skipping table "USER_DATA"
. . skipping table "USER_DOCUMENTS"
Import terminated successfully without warnings.

 

2) Edit the SQL file

 

In this file, you will have two modifications to do. First, remove all REM keywords from the CREATE statements. All rows are created as a comment in the file. Then, change the tablespace name for all objects you want to create on a different tablespace.

This is how my SQL file looks like after the modifications:

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] cat imp_to_DBI.sqlCREATE TABLE "DBI"."USER_DATA" ("DATA_ID" NUMBER, "DATA_VALUE"
VARCHAR2(250)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 16777216 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" LOGGING
NOCOMPRESS ;
REM  ... 99000 rows
CONNECT DBI;
CREATE UNIQUE INDEX "DBI"."PK_DATAID" ON "USER_DATA" ("DATA_ID" ) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS
1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA"
LOGGING ;
ALTER TABLE "DBI"."USER_DATA" ADD CONSTRAINT "PK_DATAID" PRIMARY KEY
("DATA_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" LOGGING
ENABLE ;
CREATE TABLE "DBI"."USER_DOCUMENTS" ("DOC_ID" NUMBER, "DOC_TITLE"
VARCHAR2(25), "DOC_VALUE" BLOB) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA"
LOGGING NOCOMPRESS LOB ("DOC_VALUE") STORE AS BASICFILE (TABLESPACE
"USER_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT)) ;
REM  ... 25 rows
CREATE UNIQUE INDEX "DBI"."PK_DOCID" ON "USER_DOCUMENTS" ("DOC_ID" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"USER_DATA" LOGGING ;
ALTER TABLE "DBI"."USER_DOCUMENTS" ADD CONSTRAINT "PK_DOCID" PRIMARY
KEY ("DOC_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER_DATA" LOGGING ENABLE ;
ALTER TABLE "DBI"."USER_DOCUMENTS" ADD CONSTRAINT "FK_DOCID" FOREIGN
KEY ("DOC_ID") REFERENCES "USER_DATA" ("DATA_ID") ENABLE NOVALIDATE ;
ALTER TABLE "DBI"."USER_DOCUMENTS" ENABLE CONSTRAINT "FK_DOCID" ;

 

3) Execute the SQL file with SQL Plus

 

Simply use SQL Plus to execute the SQL file (the user DBI must exist prior to running the script):

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] sqlplus / as sysdba @imp_to_DBI.sql
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 18:13:14 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Table created.

 

Enter password:
Connected.

 

Index created.

 

Table altered.

 

Table created.

 

Index created.

 

Table altered.

 

Table altered.

 

Table altered.

 

SQL> select object_type, object_name from user_objects;
OBJECT_TYPE         OBJECT_NAME
------------------- -----------------------------------
INDEX               PK_DATAID
TABLE               USER_DOCUMENTS
TABLE               USER_DATA
INDEX               SYS_IL0000064697C00003$$
LOB                 SYS_LOB0000064697C00003$$
INDEX               PK_DOCID

 

4) Disable all constraints

 

When importing data with imp or impdp tools, constraints are created and enabled at the end of the import. This allows Oracle to import data without taking into account any referential integrity constrainst. As we already have created empty objects with enabled constraints, we must manually disable the constraints before importing the data.

 

SQL> select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE CONSTRAINT ' || CONSTRAINT_NAME || ';' "SQL_CMD" from DBA_CONSTRAINTS WHERE OWNER='DBI';
SQL_CMD
-----------------------------------------------------------
ALTER TABLE DBI.USER_DOCUMENTS DISABLE CONSTRAINT FK_DOCID;
ALTER TABLE DBI.USER_DATA DISABLE CONSTRAINT PK_DATAID;
ALTER TABLE DBI.USER_DOCUMENTS DISABLE CONSTRAINT PK_DOCID;

 

SQL> ALTER TABLE DBI.USER_DOCUMENTS DISABLE CONSTRAINT FK_DOCID;
Table altered.

 

SQL> ALTER TABLE DBI.USER_DATA DISABLE CONSTRAINT PK_DATAID;
Table altered.

 

SQL> ALTER TABLE DBI.USER_DOCUMENTS DISABLE CONSTRAINT PK_DOCID;
Table altered.

 

5) Import the data with the IGNORE=Y option

 

Now we must import the data from the dump file using the IGNORE=Y option to ignore warnings about already existing objects. It will allow the imp tool to load data to the empty tables and indexes. Additionaly, I have set the CONSTRAINTS=N option because imp tried to enable the constraints after the import, which was generating an error...

 

oracle@srvora01:/u00/app/oracle/admin/DB11G/dpdump/ [DB11G] imp system/Passw0rd file=exp_MSC.dmp log=imp_to_DBI.log fromuser=msc touser=dbi ignore=y constraints=n
Import: Release 11.2.0.3.0 - Production on Tue Aug 12 19:43:59 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by MSC, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing MSC's objects into DBI
. . importing table                    "USER_DATA"      99000 rows imported
. . importing table               "USER_DOCUMENTS"         25 rows imported
About to enable constraints...
Import terminated successfully without warnings.

 

All objects have been imported successfully:

 

SQL> select object_type, object_name from dba_objects where owner='DBI';
OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
INDEX               PK_DATAID
TABLE               USER_DATA
TABLE               USER_DOCUMENTS
INDEX               SYS_IL0000064704C00003$$
LOB                 SYS_LOB0000064704C00003$$
INDEX               PK_DOCID
SEQUENCE            MSCSEQ

 

6) Enable constraints after the import

 

Constraints previously disabled must be enabled again to finish the import:

 

SQL> select constraint_name, status from dba_constraints where owner='DBI';CONSTRAINT_NAME                STATUS
------------------------------ --------
FK_DOCID                       DISABLED
PK_DOCID                       DISABLED
PK_DATAID                      DISABLED

 

SQL> select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE CONSTRAINT ' || CONSTRAINT_NAME || ';' "SQL_CMD" from DBA_CONSTRAINTS WHERE OWNER='DBI';
SQL_CMD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE DBI.USER_DOCUMENTS ENABLE CONSTRAINT FK_DOCID;
ALTER TABLE DBI.USER_DOCUMENTS ENABLE CONSTRAINT PK_DOCID;
ALTER TABLE DBI.USER_DATA ENABLE CONSTRAINT PK_DATAID;

 

Enable PRIMARY KEYS:

 

SQL> ALTER TABLE DBI.USER_DOCUMENTS ENABLE CONSTRAINT PK_DOCID;
Table altered.

 

SQL> ALTER TABLE DBI.USER_DATA ENABLE CONSTRAINT PK_DATAID;
Table altered.

 

And then FOREIGN KEY:

 

SQL> ALTER TABLE DBI.USER_DOCUMENTS ENABLE CONSTRAINT FK_DOCID;
Table altered.

 

Constraints are now enabled:

 

SQL> select constraint_name, status from dba_constraints where owner='DBI';CONSTRAINT_NAME                STATUS
------------------------------ --------
FK_DOCID                       ENABLED
PK_DATAID                      ENABLED
PK_DOCID                       ENABLED

 

We do not have to carry on quota on tablespaces here. As you can see, even if I have recreated my USERS tablespace prior to importing the data with the INDEXFILE option, the USERS tablespace contains no segment after the import:

 

SQL> select segment_name from dba_segments where tablespace_name='USERS';
no rows selected

 

Conclusion

Workaround 1 and 2, which are very similar, are simple and fast methods to remap tablespaces for import into a database without any LOB data. But in the presence of LOB data, the workaround 3 is the right method to successfully move every object of the database into the new database. The major constraint of this workaround is that you will have to manually edit an SQL file, which can become very fastidious if you have several hundred or thousand of objects to migrate...

It is also possible to first import the data in the same tablespac, and then use the MOVE statement to move all objects into the new tablespac. However, you may not be able to move ALL objects this way. Workaround 3 seems to be the best and "cleanest" way to do it.

Oracle 12.1.0.2.1 Set to Join Conversion

Thu, 2014-08-21 01:17

Recently, I described the Partial Join Evaluation transformation that appeared last year in Oracle 12c. I did it as an introduction for another transformation that appeared long time ago in 10.1.0.3 but was not used by default. And even in the latest Oracle 12c patchset 1 (aka 12.1.0.2.0) it is still not enabled. But it's there and you can use it if you set optimizer_features_enabled to 12.1.0.2.1 (that's not a typo!).

Yes, that number looks like the future PSU for the 12c Release 1 Patchset 1 that was available recently and has no PSU yet. Lost in the release numbers? No problem. This is only default values for the _convert_set_to_join paramter but you can also use the hint to get that transformation, which is available in previous versions as well.

So what does this transformation? It transforms an INTERSECT or MINUS into a join. When the tables are large but the result is small, that transformation can bring new access path avoiding full table scans and deduplication for each branch. And thanks to the Partial Join Evaluation the performance is even better in 12c. Let's look at an example.

 


SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 27 22:10:57 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table DEMO1(n constraint DEMO1_N primary key) as select rownum n from (select * from dual connect by level  Table created.

SQL> create table DEMO2(n constraint DEMO2_N primary key) as select rownum n from dual connect by level  Table created.

SQL> alter session set statistics_level=all;
Session altered.

 

So I have two tables, one with 100000 rows and one with only 10. And I want the rows from DEMO1 which are not in DEMO2:

 

SQL> alter session set optimizer_features_enable='12.1.0.2.1';
Session altered.

SQL> select * from DEMO1 intersect select * from DEMO2;

         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

 

Let's have a look at the plan:

 

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 9fpg8nyjaqb5f, child number 0
-------------------------------------
select * from DEMO1 intersect select * from DEMO2

Plan hash value: 4278239763

------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows || Used-Mem |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     10 ||          |
|   1 |  INTERSECTION       |         |      1 |        |     10 ||          |
|   2 |   SORT UNIQUE       |         |      1 |    100K|    100K|| 4078K (0)|
|   3 |    TABLE ACCESS FULL| DEMO1   |      1 |    100K|    100K||          |
|   4 |   SORT UNIQUE NOSORT|         |      1 |     10 |     10 ||          |
|   5 |    INDEX FULL SCAN  | DEMO2_N |      1 |     10 |     10 ||          |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1
   3 - SEL$1 / DEMO1@SEL$1
   5 - SEL$2 / DEMO2@SEL$2

 

This is the expected plan. There is an INTERSECTION operation that implements our INTERSECT. But look: each branch had to be deduplicated (SORT UNIQUE). Note that the SORT UNIQUE NOSORT has a funny name - it's just a SORT UNIQUE that doesn't have to sort because its input comes from an index. Each branch had to read all the rows. Look at the big table: we read 100000 rows and use 4MB of memory to sort them in order to deduplicate them. But it's an intersection and we have a small table that has only 10 rows. We know that the result cannot be large. Then a more efficient way would be to read the small table and for each row check if they are in the big one - through an index access. We still have to deduplicate, but we do that at the end, on the small rowset.

And this is exactly what the Set to Join Conversion is doing. Let's force it with a hint:

 

SQL> select /*+ SET_TO_JOIN(@"SET$1") */ * from DEMO1 intersect select * from DEMO2;

         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID        01z69x8w7fmu0, child number 0
-------------------------------------
select /*+ SET_TO_JOIN(@"SET$1") */ * from DEMO1 intersect select *
from DEMO2

Plan hash value: 169945296

------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     10 |
|   1 |  SORT UNIQUE NOSORT |         |      1 |     10 |     10 |
|   2 |   NESTED LOOPS SEMI |         |      1 |     10 |     10 |
|   3 |    INDEX FULL SCAN  | DEMO2_N |      1 |     10 |     10 |
|*  4 |    INDEX UNIQUE SCAN| DEMO1_N |     10 |    100K|     10 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEMO1"."N"="DEMO2"."N")

 

The intersect has been transformed to a join thanks to the Set to Join transformation, and the join has been transformed to a semi-join thanks to the Partial Join Evaluation transformation. The result is clear here:

  • No full table scan on the big table because the join is able to access with an index
  • No deduplication which needs a large workarea
  • The join can stop as soon as one row matches thanks to the semi-join
  • Deduplication occurs only on result, which is small. And here it does not even require a workarea because the rows comes sorted from the index.

 

We can see the SET_TO_JOIN and PARTIAL_JOIN hints in the outline:

 

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$02B15F54")
      MERGE(@"SEL$1")
      MERGE(@"SEL$2")
      OUTLINE(@"SET$09AAA538")
      SET_TO_JOIN(@"SET$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SET$1")
      INDEX(@"SEL$02B15F54" "DEMO2"@"SEL$2" ("DEMO2"."N"))
      INDEX(@"SEL$02B15F54" "DEMO1"@"SEL$1" ("DEMO1"."N"))
      LEADING(@"SEL$02B15F54" "DEMO2"@"SEL$2" "DEMO1"@"SEL$1")
      USE_NL(@"SEL$02B15F54" "DEMO1"@"SEL$1")
      PARTIAL_JOIN(@"SEL$02B15F54" "DEMO1"@"SEL$1")
      END_OUTLINE_DATA
  */

 

So we are in 12.1.0.2 and we need a hint for that. Let's go to 12.1.0.2.1 (which implicitely set _convert_set_to_join=true).

 

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID        9fpg8nyjaqb5f, child number 1
-------------------------------------
select * from DEMO1 intersect select * from DEMO2

Plan hash value: 118900122

------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows || Used-Mem |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     10 ||          |
|   1 |  HASH UNIQUE        |         |      1 |     10 |     10 || 1260K (0)|
|   2 |   NESTED LOOPS SEMI |         |      1 |     10 |     10 ||          |
|   3 |    INDEX FULL SCAN  | DEMO2_N |      1 |     10 |     10 ||          |
|*  4 |    INDEX UNIQUE SCAN| DEMO1_N |     10 |    100K|     10 ||          |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEMO1"."N"="DEMO2"."N")

Note
-----
   - this is an adaptive plan

 

Ok, we have the Set to Join Conversion here in 12.1.0.2.1

But don't you see another difference?
.
.
.
.
The deduplication needs a workarea here. It is not a NOSORT operation - even if the result comes from the index. It seems that the CBO cannot guarantee that the result comes sorted. The clue is in the execution plan note.

But that's for a future blog post.

How to find the SQL Server Management Pack in the SCOM database

Wed, 2014-08-20 00:05

While working on a customer project, I needed to retrieve the SQL Server SCOM Management Pack version. Unfortunately, the SCOM team took a while to answer and as I am the DBA, I have access to the SCOM databases. So let’s have a look at how we can get this the "hard" way.

Deploying an SQL Server database to Azure

Tue, 2014-08-12 18:46

Deploying an SQL Server database to a Windows Azure virtual machine is a feature introduced with SQL Server 2014. It can be useful for an organization  that wants to reduce its infrastucture management, simplify the deployment, or have a fast virtual machine generation.

 

Concept

This new feature is a wizard which allows either to copy or migrate an On-Premise SQL Server database to a Windows Azure virtual machine.

The following schema explains the main process of the feature:
 PrtScr-capture_20.pngAn existing SQL Server instance is present on an On-Premise machine of an organization, hosting one or several user databases.

Once the new feature is used, a copy of the on-premise database will be available on the SQL Server instance in the Cloud.

 

Prerequisites

Azure Account Creation

Obviously, the first requirement is an Azure account! To create an Azure account, go to the official Azure website.

 

Azure Virtual Machine Creation

Once the Azure account has been set, a Windows Azure virtual machine has to be created.

There are two ways to create a Virtual Machine: by a "Quick Create" or by a "Custom Create". It is recommended to perform a "Custom Create" (from gallery) because it offers more flexibility and control on the creation.

 

PrtScr-capture_20_20140730-120018_1.png

 

This example is done with “FROM GALLERY”. So the creation of the virtual machine will be performed with a wizard in four steps.

 

The first step of the wizard is the selection of the virtual machine type.

Unlike prejudices, Windows Azure offers a large panel of virtual machines, which do not only come from the Microsoft world.

See more details about Virtual Machines on the Microsoft Azure website.


 

PrtScr-capture_20_20140730-120252_1.png

The targeted feature is only available on SQL Server 2014, so a virtual machine including SQL Server 2014 has to be created. This example will be made with a Standard edition to have the more restrictive edition of SQL Server 2014.

The first step of the wizard is configured as follows: 


 PrtScr-capture_20_20140730-120551_1.png

For this example, default settings are used. Indeed the configuration of the virtual machine is not the main topic here, and can change depending on one’s need.

The release date from June is selected, so the SP1 will be included for SQL Server 2014.

The "Standard Tier" is selected so load-balancer and auto-scaling will be included. See more details about Basic and Standard Tier on the Microsoft Azure website.

The virtual machine will run with 2 cores and 3.5 GB memory, which will be enough for this demo. See more details about prices and sizes on the Microsoft Azure website.

The virtual machine is named “dbiservicesvm” and the first (admin) user is “dbiadmin”.

 

The second step of the wizard is configured as follows:

 

PrtScr-capture_20_20140730-121233_1.png

 

The creation of a virtual machine in Azure requires a cloud service, which is a container for virtual machines in Azure. See more details about Cloud Service on the Microsoft Azure website.

Furthermore, a storage account ("dbiservices") and an affinity group ("IT") are also required to store the disk files of the virtual machine. To create a storage account and an affinity group, see the Azure Storage Account Creation part from a previous blog.

 

The third step of the wizard is configured as follows:

 

PrtScr-capture_20_20140730-121705_1.png

 

This screen offers the possibility to install extensions for the virtual machine. Virtual machine extensions simplify the virtual machine management. By default, VM Agent is installed on the virtual machine.

The Azure VM Agent will be responsible for installing, configuring and managing VM extensions on the virtual machine.

For this example, VM extensions are not required at all, so nothing is selected.

 

Security

At the SQL Server level, an On-Premise SQL user with Backup Operator privilege is required on the targeted Azure database. Of course the user must be mapped with a SQL Server login to be able to connect to the SQL Server instance.

If the user does not have the Backup Operator privilege, the process will be blocked at the database backup creation step:

 

PrtScr-capture_20_20140730-122146_1.png

 

Moreover, an Azure account with Administrator Service privilege linked to the subscription containing the Windows Azure virtual machine is also required. Without this account, it is impossible to retrieve the list of Windows Azure virtual machines available on the Azure account, so the SQL Server Windows Azure virtual machine created previously.

Finally, the end point of the Cloud Adapter must be configured to access the virtual machine during the execution of the wizard. If not, the following error message will occur:

 

PrtScr-capture_201.png

 

The Cloud Adapter is a service which allows the On-Premise SQL Server instance to communicate with the SQL Server Windows Azure virtual machine. More details about Cloud Adapter for SQL Server on TechNet.

To configure the Cloud Adapter port, the Azure administrator needs to access to the Endpoints page in the SQL Server Windows Azure virtual machine on the Azure account. Then a new endpoint needs to be created as follows:

 

PrtScr-capture_201_20140730-122732_1.png

 

Now, the SQL Server Windows Azure virtual machine allows to connect to the Cloud Adapter port.

 

The credentials of a Windows administrator user are also required on the Windows Azure virtual machine to connect to the virtual machine in Azure. This administrator also needs a SQL Server login. If these two requirements are not met, the following error message will occur:

 

PrtScr-capture_201_20140730-122929_1.png

 

The login created in the SQL Server instance in Azure must also have the dbcreator server role, otherwise the following error message will occur:

 

PrtScr-capture_201_20140730-123033_1.png

 

 

Deploy a database to the Windows Azure VM wizard

Launch the wizard

The wizard can be found with a right-click on the targeted database, then "Tasks" and finally "Deploy Database to a Windows Azure VM…"

 

PrtScr-capture_201_20140730-132221_1.png

 

In fact, it does not matter from which user databases the wizard is launched, because the wizard will ask to connect to an instance, and then it will ask to select a database.

 

Use the wizard

The first step is an introduction of the wizard, which is quite without interests.

 

The second step needs three fields: the SQL Server instance, the SQL Server database and the folder to store the backup files.

 

PrtScr-capture_201_20140730-132446_1.png

 

The "SQL Server" field is the SQL Server instance hosting the SQL Server database which is planned to be deployed to Azure.

The "Select Database" field must obviously reference the database to deploy.

 

The third step needs two fields: the authentication to the Azure account and the selection of the subscription.

 

PrtScr-capture_201_20140730-132747_1.png

 

After clicking on the Sign in… button, a pop-up will require the Administrator privilege from the Azure account.

As soon as the credentials are entered to connect to the Azure account, a certificate will be generated.

If several subscriptions are linked to the Azure account, select the correct subscription ID. In this example, there is only one subscription linked to the Azure account.

For more information, see what’s the difference between an Azure account and a subscription on TechNet.

 

The fourth step of the wizard is divided in several parts. First, there is the virtual machine selection, as follows:

 

PrtScr-capture_201_20140730-133036_1.png

 

The cloud service ("dbiservicescloud") needs to be selected. Then, the virtual machine ("dbiservicesvm") can be selected.

 

Credentials to connect to the virtual machine must be provided, as follows:

 

PrtScr-capture_201_20140730-133421_1.png

 

The SQL Server instance name and the database name need to be filled, as follows:

 

PrtScr-capture_201_20140730-133756_1.png

 

Finally, all information are filled for the wizard, the deployment can be launched. The deployment finished successfully!

 

After the deployment

First, the On-Premise database used for the deployment is still present on the SQL Server instance.

 

PrtScr-capture_201_20140730-134024_1.png

 

Then, the folder used to store the temporary file is still present. In fact, this is a "bak" file because the mechanism behind the wizard is a simple backup and restore of the database from an On-Premise SQL Server instance to an Azure SQL Server instance.

 

PrtScr-capture_201_20140730-134116_1.png

 

So do not forget to delete the bak file after the deployment because this file uncessarly fills your storage space for big databases.

 

Finally, the deployed database can be found on the Azure SQL Server instance!

 

PrtScr-capture_201_20140730-134220_1.png

Limitations

The first limitation of this new feature is the database size of this operation: it cannot exceed 1 TB.

Moreover, it does not support hosted services that are associated with an Affinity Group.

The new feature does not allow to deploy all versions of SQL Server database. Indeed, only SQL Server 2008 database versions or higher are allowed to be deployed.

 

Similar features

If this new feature, for any reason , does not meet the organization’s need, two similar features exist.

 

SQL database in Azure

This feature introduced with SQL Server 2012 is quite similar to the feature presented in this blog, because the SQL Server database and the Windows Azure virtual machine are hosted on Azure.

However, the management of the infrastructure is much more reduced: no virtual machine management nor sql server management. A good comparison between these two functionalities is available: Choosing between SQL Server in Windows Azure VM & Windows Azure SQL Database.

More details about SQL Database on Azure.

 

SQL Server data files in Azure

This is a new feature introduced with SQL Server 2014, which allows to store data files from the SQL Server database in Azure Blob storage, but the SQL Server instance runs On-Premise.

It simplifies the migration process, reduces the On-Premise space storage and management, and simplifies the High Availability and recovery solutions…

More details about SQL Server data files in Azure on TechNet.

 

Conclusion

With this feature, Microsoft simplifies the SQL Server database deployment process from On-Premise to Azure.

Azure is a rather attractive and interesting tool that is highly promoted by Microsoft.

Documentum: some useful queries (DQL/IAPI)

Thu, 2014-08-07 02:37

In this blog post I want to sharing some useful DQL and IAPI queries that I am using all the time. They are more dedicated to Documentum support/debug and grouped by components. In order to use them, I recommend Qdman: it is the best tool I know to use DQL and IAPI scripts.

1. xPlore

It regroups both the search engine and the index management.

DQL Dsearchselect * from dm_document search document contains 'manual.doc'

 

This query will perform a search just as if you put the 'manual.doc' in the search field of DA. It can be used to check if the dsearch is working fine and if the indexing has been performed correctly. If not, it will return 0 results for a document that you know does exist in the docbase.

Index Waitingselect count(*) as awaiting_15 FROM dmi_queue_item a, dm_sysobject (all) b WHERE b.r_object_id = a.item_id AND a.name='dm_fulltext_index_user' AND date_sent > date(now)-(15*1/24/60) AND (a.task_state is NULL or a.task_state = ' ')

 

This query will return the number of items waiting to be indexed since 15 minutes. The parameter can be changed to 60 minutes or whatever, you just have to change the '15' in bold in the previous query.

Index In Progressselect count(*) as in_progress_15 FROM dmi_queue_item a, dm_sysobject (all) b WHERE b.r_object_id = a.item_id AND a.name='dm_fulltext_index_user' AND date_sent > date(now)-(15*1/24/60) AND a.task_state = 'acquired'

 

This one is similar to the previous, but it returns the number of 'in progress' indexing requests. Note that the parameter can still ne changed.

Index By Stateselect task_state,count(*) from dmi_queue_item where name = 'dm_fulltext_index_user' group by task_state

 

This query lists the number of indexes by state:

  • blank -> awaiting indexing
  • aquired -> in progress
  • warning
  • error
  • done

Delete Indexing Requestdelete dmi_queue_item object where item_id='09xxxxxxxxxxxxxx'

 

Sometimes I noticed there are indexing requests on deleted documents. In fact, it can happen if someone saved a document, then deleted it right after. The indexing request remains in the queue for life. Thus, you may want to delete it. First, check if the file is deleted by running the IPAI: dump,c,09xxxxxxxxxxxxxx. If an error occurs telling the document doesn't exist anymore, you can delete it.

Index Agent Infoselect fti.index_name,iac.object_name as instance_name from dm_fulltext_index fti, dm_ftindex_agent_config iac where fti.index_name = iac.index_name and fti.is_standby = false and iac.force_inactive = false

 

This query returns your configured index agent information. It is useful for the IAPI command returning the index agent status (see below).

Index Agent Statusapply,c,NULL,FTINDEX_AGENT_ADMIN,NAME,S,DOCBASE_ftindex_01,AGENT_INSTANCE_NAME,S, HOST._9200_IndexAgent,ACTION,S,status
next,c,q0
get,c,q0,status
close,c,q0

 

This script returns the Index Agent Status (Running, Stopped, and so on). Note that you have to replace the Indey Agent information in the first line by your Index Agent. You can get these information thanks to the DQL query above.

Manually Queue Index Requestqueue,c,09xxxxxxxxxxxxxx,dm_fulltext_index_user,dm_force_ftindex

 

This one simply puts an indexing request in the queue. You have to replace 09xxxxxxxxxxxxxxx by the r_object_id of the document you want to queue.

Display Dsearch Portcat $DOCUMENTUM/dsearch/admin/xplore.properties | grep port

 

For this one you have to go to the xPlore server, it shows the configured dsearch port.

2. Rendering (ADTS)

The following queries concern the rendition component. It regroups the rendition queue check and the way to manually ask a rendition through IAPI.

 

Manually Queue Rendering Requestqueue,c,09xxxxxxxxxxxxxx,dm_autorender_win31,rendition,0,F,,rendition_req_ps_pdf

 

As the indexing request, this one puts a PDF rendition request in the queue. It can be useful when scripting or in DFC programs.

Rendering By Stateselect task_state,count(*) from dmi_queue_item where name = 'dm_autorender_win31' group by task_state

 

This returns the rendition requests by state.

Rendering Queueselect * from dmi_queue_item where name ='dm_autorender_win31' order by date_sent desc

 

This query returns all documents present in the rendering queue. That means all document waiting for rendition.

Rendition Failedselect r_object_id,item_id,name,item_name,date_sent from dmi_queue_item where event ='DTS' order by date_sent desc

 

This Query returns the failed renditions. Be aware of the date_sent field, because this queue is not cleared. This means that if a rendition request failed 3 times in a row and succeed the last time, there will be 3 rows in the failed queue, but the rendition did succeed. So you should verify that the rendition did succeed and if so, you can delete the row form of the failed queue.

Check Rendition Successfulselect r_object_id from dm_document where object_name='DOCUMENT' and exists(select * from dmr_content where any parent_id=dm_document.r_object_id and full_format='pdf')

 

This query checks if a rendition is present for the given DOCUMENT name. If the pdf rendition exists, it returns its r_object_id. If no rendition is present for the given document, it returns nothing.

3. Audit Trail

 

Failed Login Since 1hselect user_name,count(*) as logon_failure from dm_audittrail where event_name='dm_logon_failure' and time_stamp > (date(now)-(60*1/24/60)) group by user_name order by 2

 

This query displays the number of failed logons in the docbase per user since 60 minutes. The parameter 60 can be changed.

Purge Logon FailureEXECUTE purge_audit WITH delete_mode='PREDICATE', dql_predicate='dm_audittrail where event_name=''dm_logon_failure'''

 

This statement purges the audit trail queue by deleting all logon failure entries. Be aware that it can take a while depending on the number of entries you have.

Number Of Logon Failureselect count(*) as logon_failure from dm_audittrail where event_name='dm_logon_failure'

 

This query simply shows the number of logon failures in the queue.

4. Miscellaneous

 

IAPI Purge Cachesflush,c,ddcache,dm_type
flush,c,ddcache,dmi_type_info
flush,c,ddcache,dm_aggr_domain
flush,c,ddcache,dm_domain
flush,c,ddcache,dm_dd_info
flush,c,ddcache,dm_nls_dd_info
flush,c,ddcache,dm_foreign_key
flush,c,persistentcache
publish_dd,c
reinit,c

 

This query flushes caches, it can be used when trying to install ADTS dars and fails due to version mismatch.

Check ADTS Installer Versionjava -cp adtsWinSuiteSetup.jar DiShowVersion
Multi-installer Suite 6.7.2000.42
Installer-Version: 6.7.2000.42 build 1
Installer-Build-Date: 1/11/13 12:28 AM

 

Go to the ADTS installer directory and issue this query. It shows the version of the installer.

Encrypt dm_bof_registry Passwordjava com.documentum.fc.tools.RegistryPasswordUtils PASSWORD

 

This one encrypts the dm_bof_registry password in order to use it in dfc.properties. Not that the encryption process is different on xPlore and ADTS but you can use it on the content server and all DFC related programs. Replace the PASSWORD in the query by your clear password.

SQL Server 2014: DBCC CHECKDB and ReFS support

Wed, 2014-08-06 21:50

Have you noticed an interesting behavior with SQL Server 2014 when you are running a dbcc checkdb command? If not, you should have a look at this blog post. I discovered it completely by accident during some tests. If you watch carefully, you will see "weird" files appear while the dbcc checkdb command is running.

Below is a sample of the file you will find near the corresponded database file:

 

blog_15_-_dbcc_checkdb_refs_-_1

 

The file immediately disappears after completion of the dbcc checkdb command. So the question is, what is this file? The type of this file is MDF_MSSQL_DBCC10 and we can easily guess that it concerns a dbcc checkdb command, but I must admit I have never seen it in the past...

To be sure, I decided to trace the file activity of the SQL Server process while the dbcc checkdb command is running. You can see the part of the result that concerns our investigation below:

 

blog_15_-_dbcc_checkdb_refs_-2_

 

So we can now say that these files concern the dbcc checkdb activity and Kevin Farlee (SQL Program Manager at Microsoft) confirmed that this new behavior is quite normal.

Here is the explanation:

Prior to SQL Server 2014, the files for dbcc checkdb were created as alternate data streams of the regular database files. This is why we don’t see them by default (alternate data streams can be showed by using the dir command with /R parameter). But with the release of Windows Server 2012 and the new ReFS file system, the story has changed because it does not provide any more alternate data streams capabilities, which is why it was not compatible with SQL Server prior to the SQL Server 2014 version (see KB2681562).

This is a good news, because as you certainly know, ReFS provides some enhancements over NTFS. This is a more resilient file system and it has a better support for extremely large amounts of data. As a reminder, with large databases, you may face the operating system error numbers 1450 and 665 when you run dbcc checkdb command due to a NTFS limitation. These errors are reported in Microsoft KB2002606 and some fixes or workarounds are fortunately available.

Time to switch to ReFS file system with SQL Server 2014? Your thought will be appreciated!

Oracle 12.1.0.2: Wait event histograms in μs

Mon, 2014-08-04 08:01

When an Oracle Database spends a high percentage of its DB time in User I/O, I usually check the wait event histograms in order to see if the storage system is working well. But today, with storage going to SSD, most I/O are less than 1 milliseconds and we have no details about those wait times.

Here is what is exposed by V$WAIT_EVENT_HISTOGRAM:

 

select event,wait_time_milli,wait_count from v$event_histogram where event like 'db file sequential read' order by event,wait_time_milli;
EVENT                          WAIT_TIME_MILLI WAIT_COUNT
------------------------------ --------------- ----------
db file sequential read                      1      27140
db file sequential read                      2          6
db file sequential read                      4          1

 

The latest Oracle 12c patchset, 12.1.0.2, besides changing the future of the database world with the In-Memory option, comes with a small new feature that helps us in our day-to-day tasks: the introduction of the V$EVENT_HISTOGRAM_MICRO view:

 

select event,wait_time_micro,wait_count,wait_time_format from v$event_histogram_micro where event like 'db file sequential read' order by event,wait_time_micro;
EVENT                          WAIT_TIME_MICRO WAIT_COUNT WAIT_TIME_FORMAT
------------------------------ --------------- ---------- ----------------
db file sequential read                      1        120   1 microsecond
db file sequential read                      2         24   2 microseconds
db file sequential read                      4         51   4 microseconds
db file sequential read                      8        212   8 microseconds
db file sequential read                     16      19600  16 microseconds
db file sequential read                     32       5958  32 microseconds
db file sequential read                     64        550  64 microseconds
db file sequential read                    128        492 128 microseconds
db file sequential read                    256         98 256 microseconds
db file sequential read                    512         14 512 microseconds
db file sequential read                   1024         21   1 millisecond
db file sequential read                   2048          6   2 milliseconds
db file sequential read                   4096          1   4 milliseconds

 

Here it is: the wait event are detailed up to microseconds. It's good for I/O when on SSD. It's good for In-Memory events as well.

Unfortunately, this has not been yet introduced in the AWR reports (I made an enhancement request for that).

Now, if you wonder which disk I'm using to get the microsecond i/o above...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

... here is how I created that database:

 

mkdir -p /mnt/ramdisk
mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk
$ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName RAMDB -sid RAMDB -sysPassword oracle -systemPassword oracle -storageType FS -characterSet AL32UTF8 -listeners LISTENER -sampleSchema  true -totalMemory 600 -databaseType MULTIPURPOSE -silent -datafileDestination /mnt/ramdisk

SQL Server: DBCC CHECKDB does not detect corruption

Mon, 2014-08-04 02:12

During my audits at customer places, it still happens very often to find SQL Server databases with page verification option configured to "none". I always alert my customers on this configuration point because it can have an impact on the overall integrity of their databases. One of my customer told me that the integrity task of its maintenance will detect the corruption anyway and alert him by email - but is it really the case?

Of course, my response to the customer is that it depends of the corruption type and that a dbcc checkdb is not an absolute guarantee in this case. I like to show this example to my customers to convince them to change the page verify option to another thing than "none".

Let me show this to you:

 

use [master]; go   -- create corrupt db if DB_ID('corrupt') is not null        drop database corrupt;        create database corrupt; go   -- force page verify option to none alter database corrupt set page_verify none; go   use corrupt; go   -- create two tables t1 and t2 create table dbo.t1( id int identity primary key, col1 varchar(50)); create table dbo.t2( id int identity primary key, col1 int, col2 int, col3 varchar(50));   -- insert some sample data declare @random int; declare @i int = 1;   while @i begin        set @random = 10000/(rand()* 1000);        if @random > 50 set @random = 50;          insert t1(col1) values (REPLICATE('t', 50));        insert t2(col1, col2, col3) values (@i, @i + 1, REPLICATE('t', @random));          set @i = @i + 1; end   -- add a foreign key on column id to table t2 that references the table t1 on column id alter table dbo.t2 add constraint FK_t2_id foreign key (id) references dbo.t1(id); go   -- create a nonclustered covered index on table t1 create nonclustered index idx_t2_col1 on dbo.t2( col1, col2) include ( col3 ); go

 

At this point we have two tables named t1 and t2. Table t2 has a foreign key constraint on the id column that references the table t1 on the column with the same name.

Now let’s corrupt a data page in the clustered index on the table t1. First, we will find the first data page in the clustered index of the table t1:

 

-- get the first data page on the t1 table clustered index dbcc ind('corrupt', 'dbo.t1', 1); go

 

blog_14_-_dbcc_checkb_corrupt_-_1

 

Then we will find the first row. The first row is stored in slot 0 which is located at offset 0x060.

 

-- Display dump page id = 15 dbcc traceon(3604); go dbcc page ('corrupt', 1, 15, 3); go

 

blog_14_-_dbcc_checkb_corrupt_-_2

 

Now it’s time to corrupt the id column (id = 1) located to the offset 0x4 in the row. That means we have to place to the offset 0x60 + 0x4 to corrupt this column.

We will use the DBCC WRITEPAGE undocumented command to corrupt our page (again, a big thanks to Paul Randal for showing us how to use this command for testing purposes).

 

-- corrupt the concerned page alter database corrupt set single_user; go   dbcc writepage('corrupt', 1, 15, 100, 1, 0x00, 1)   alter database corrupt set multi_user; go

 

Now if we take a look at the page id=15, we notice that the id column value is now changed from 1 to 0.

 

blog_14_-_dbcc_checkb_corrupt_-_3

 

Ok, let’s run a DBCC CHECKDB command:

 

-- perform an integrity check with dbcc checkdb dbcc checkdb('corrupt') with no_infomsgs, all_errormsgs; go

 

blog_14_-_dbcc_checkb_corrupt_-_4

 

As you can see, the dbcc checkdb command does not detect any corruption! Now, let’s run the following statements:

 

-- first query select t2.col2, t2.col3 from dbo.t2        join dbo.t1              on t1.id = t2.id where t2.col1 = 1

 

blog_14_-_dbcc_checkb_corrupt_-_5

 

Do you notice that reading the corrupted page does not trigger an error in this case?

 

-- second query select t2.col2, t2.col3, t1.col1 from dbo.t2        join dbo.t1              on t1.id = t2.id where t2.col1 = 1

 

blog_14_-_dbcc_checkb_corrupt_-_6

 

As you can notice, adding the t1.col1 column to the query will give it a different result between the both queries. Strange behavior isn’t it? In fact, the two queries above don’t use the same execution plan as the following below:

Query 1:

 

blog_14_-_dbcc_checkb_corrupt_-_7

 

Query 2:

 

blog_14_-_dbcc_checkb_corrupt_-_8

 

In the query 1, due to the foreign key constraint, the query execution engine doesn’t need to join t2 to t1 to retrieve data because we need only data already covered by the idx_t2_col1 index on table t2. However the story is not the same with the query 2. Indeed, we want to retrieve an additional value provided by the col1 column from the table t1. In this case SQL Server has to join t1 and t2 because the covered index idx_t2_col1 cannot provide all the data we need. But remember we had corrupt the id column of the primary key of the table t1 by changing the value from 1 to 0. This is why the query 2 doesn’t display any results.

The main question here is: why dbcc checkdb doesn’t detect the corruption? Well, in this case corruption has occurring directly on the data value and dbcc checkdb doesn’t have a verification mechanism to detect a corruption issue. Having a checksum value stored in the page would help dbcc checkdb operation in this case because it could compare a computed checksum while reading the page with the stored checksum stored on it.

Below the output provided by dbcc checkdb command if checksum page verify option was enabled for the database …

 

blog_14_-_dbcc_checkb_corrupt_-_9

 

… or when we ran the query used earlier:

 

blog_14_-_dbcc_checkb_corrupt_-_10

 

My conclusion:

Do not hesitate to change your page verify option value when it is configured to "none".

PostgreSQL for Oracle DBAs - an introduction

Sun, 2014-08-03 22:16

Having worked for several years as an Oracle DBA, I decided to have a look at the PostgreSQL database and see how it functions in comparison to the Oracle Database.

The "Enterprise DB" graphical installation of PostgreSQL 9.3 is quite easy and rather fast. Under Linux you run the graphical installer, dialog boxes lead you through the installation process. You enter the specific information of your system and at the end of the PostgreSQL installation, the Stack Builder package is invoked if you need to install applications, drivers, agents or utilities.

You can download the Enterprise DB utility using the following URL:

http://www.enterprisedb.com/downloads/postgres-postgresql-downloads

I have installed PostgreSQL 9.3 using Enterprise DB as described below:

 

pg1

 

Choose Next.

 

pg2

 

Specify the installation directory where PostgreSQL 9.3 will be installed.

 

pg3

 

Select the directory that will store the data.

 

pg4

 

Provide a password to the PostgreSQL database user.

 

pg5

 

Select a port number.

 

pg6

 

Choose the locale for the new database cluster.

 

pg7

 

PostgreSQL is now ready to be installed.

 

pg8

 

You can choose to launch or not the Stack Builder - if not, the installation process will begin.

If you encounter any problem during the installation phase, the log files are generated in /tmp.

Under Linux, a shell script named uninstall-postgresql is created in the PostgreSQL home directory to de-install the software.

The installation phase is very quick, your PostgreSQL cluster database is ready to use. Furthermore, the Enterprise DB installation creates the automatic startup file in /etc/init.d/postgresql-9.3 to start PostgreSQL in case of a server reboot.

Once the Enterprise DB installation is processed, a database storage area is initialized on disk (a database cluster). After the installation, this database cluster will contain a database named postgres and will be used by utilities or users:

 

postgres=# \list                                 List of databases   Name   | Owner   | Encoding | Collate   |   Ctype   |   Access privileges-----------+----------+----------+------------+------------+-------------postgres | postgres | UTF8     | en_US.utf8 | en_US.utf8 |template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres      +           |         |         |           |           | postgres=CTc/postgrestemplate1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres+           |         |         |           |           | =c/postgres

 

By default, a new database is created by cloning the system standard base named template1. The template0 allows you to create a database containing only pre-defined standard objects.

The sqlplus oracle equivalent command in PostgreSQL is psql. As you will see in the document, the PostgreSQL commands begin with the \ sign. The “\?” command lists every possibility.

For example, the following commands connects to the psi database:

 

-bash-3.2$ psql -d psi

Password:psql.bin (9.3.4)

Type "help" for help.No entry for terminal type "xterm";

using dumb terminal settings.

psi=# \q


If you do not want the system to ask for a password, you simply have to create a .pgpass file in the postgres home directory with the 0600 rights and the following syntax:

 

-bash-3.2$ more .pgpass

localhost:5432:PSI:postgres:password

 

-bash-3.2$ su - postgres

Password:

-bash-3.2$ psql -d psi

psql.bin (9.3.4)

Type "help" for help.

No entry for terminal type "xterm";

using dumb terminal settings.

psi=#

psi-# \q

 

At first you probably need to create a database. As an Oracle DBA, I was wondering about some typical problems such as character set or default tablespace. With PostgreSQL, it is quite easy to create a database.

As the locale en_US.utf8 has been chosen during the installation phase to be used by the cluster database, every database you will create will use it.

When you create a database you can specify a default tablespace and an owner. At first we create a tablespace:

 

postgres=# create tablespace psi location '/u01/postgres/data/psi';

CREATE TABLESPACE

 

The tablespace data is located in /u01/postgres/data/psi:

 

-bash-3.2$ ls

PG_9.3_201306121

-bash-3.2$ ls PG_9.3_201306121/

16526

-bash-3.2$ ls PG_9.3_201306121/16526/

12547     12587_vm  12624     12663     12728     12773

12547_fsm 12589     12625     12664     12728_fsm 12774

12664_vm  12730   12774_vm     12627     12666     12731     12776

 

Then we create the database:

 

postgres=# create database psi owner postgres tablespace psi;

CREATE DATABASE

 

We can list all databases with the \list command:

 

postgres=# \list                                

                 List of databases

   Name   | Owner   | Encoding | Collate   |   Ctype   |   Access privileges

-----------+----------+----------+------------+------------+-------------

postgres | postgres | UTF8     | en_US.utf8 | en_US.utf8 |

psi       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |

template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres        

|         |         |           |           | postgres=CTc/postgres

template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres+           |         |         |           |           | =c/postgres

 

Now, we can connect to the psi database and create objects, the syntax is quite similar to Oracle:

 

postgres=# \c psi

You are now connected to database "psi" as user "postgres".


We create a table and an index:

 

psi=# create table employe (name varchar);

CREATE TABLE

psi=# create index employe_ix on employe (name);

CREATE INDEX

 

We insert values in it:


psi=# insert into employe values ('bill');

INSERT 0 1

 

We reconnect to the psi database:


-bash-3.2$ psql -d psi

Password:

psql.bin (9.3.4)

Type "help" for help.

No entry for terminal type "xterm";

using dumb terminal settings.


The following command lists the tables:


psi=# \dt[+]                    

             List of relations

Schema | Name   | Type | Owner   | Size | Description

--------+---------+-------+----------+-------+-------------

public | employe | table | postgres | 16 kB |

(1 row)

psi=# select * from employe;

name

------

bill

(1 row)

 

The \d+ postgreSQL command is the equivalent of the Oracle desc command:


psi=# \d+ employe                            

                Table "public.employe"

Column |       Type       | Modifiers | Storage | Stats target | Description

--------+-------------------+-----------+----------+--------------+-------------

name   | character varying |          | extended |             |

Indexes:

   "employe_ix" btree (name)

Has OIDs: no


Obviously we also have the possibility to create a schema and create objects in this schema.

Let's create a schema:


psi=# create schema psi;

CREATE SCHEMA


Let's create a table, insert objects in it and create a view:


psi=# create table psi.salary (val integer);

CREATE TABLE

psi=# insert into psi.salary values (10000);

INSERT 0 1

psi=# select * from psi.salary;

val

-------

10000

psi=# create view psi.v_employe as select * from psi.salary;

CREATE VIEW

 

If we list the tables we can only see the public objects:


psi=# \d        

        List of relations

Schema | Name   | Type | Owner  

--------+---------+-------+----------

public | employe | table | postgres

(1 row)


If we modify the search path, all schemas are visible:


psi=# set search_path to psi,public;

SET

psi=# \d 

        List of relations

Schema | Name   | Type | Owner  

--------+---------+-------+----------

psi   | salary | table | postgres

public | employe | table | postgres


Oracle DBA’s are familiar with sql commands - e. g. to get the table list of a schema by typing select table_name, owner from user_tables, etc.

What is the equivalent query in postgreSQL?

PostgreSQL uses a schema named information_schema available in every database. The owner of this schema is the initial database user in the cluster. You can drop this schema, but the space saving is negligible.

You can easily query the tables of this schema to get precious informations about your database objects:

Here is a list of the schemas tables:


psi=# select table_name, table_schema from information_schema.tables where table_schema in ('public','psi');

table_name | table_schema

------------+--------------

employe   | public

salary     | psi


We can display the database character set:


psi=# select character_set_name from information_schema.character_sets;

character_set_name

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

UTF8

 

We can display schema views:


psi=# select table_name from information_schema.views where table_schema='psi';

table_name

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

v_employe


Using the information_schema schema helps us to display information about a lot of different database objects (tables, constraints, sequences, triggers, table_privileges …)

Like in Oracle you can run a query from the SQL or the UNIX prompt. For example, if you want to know the index name of the table employe, you shoud use the index.sql script:


select

t.relname as table_name,

i.relname as index_name,

a.attname as column_name

from

pg_class t,pg_class i,

pg_index ix,pg_attribute a

wheret.oid = ix.indrelid

and i.oid = ix.indexrelid

and a.attrelid = t.oid

and a.attnum = ANY(ix.indkey)

and t.relkind = 'r'

and t.relname = 'employe'

order byt.relname,i.relname;


If you want to display the employee index from the SQL prompt, you run:


psi=# \i index.sql

table_name | index_name | column_name

------------+------------+-------------

employe   | employe_ix | name


If you want to run the same query from the UNIX prompt:


-bash-3.2$ psql -d psi -a -f index.sql

Password:

table_name | index_name | column_name

------------+------------+-------------

employe   | employe_ix | name


However, typing an SQL request might be interesting, but - as many Oracle DBA - I like using an administration console because I think it increases efficiency.

I have discovered pgAdmin, an administration tool designed for Unix or Windows systems. pgAdmin is easy to install on a PostgreSQL environment and enables many operations for the administration of a cluster database.

pgAdmin3 is installed in the home directory of the user postgre - in my case in /opt/postgres/9.3.

To successfully enable pgAdmin3, it is necessary to correctly initialize the LD_LIBRARY_PATH variable:

 

export LD_LIBRARY_PATH=/opt/PostgreSQL/9.3/lib:/opt/PostgreSQL/9.3/pgAdmin3/lib

 

The pgadmin3 console:

 

pg9

 

As you can see, you can administer every database object (tables, functions, sequences, triggers, views…).

You can visualize the table creation scripts:

 

pg9

 

You can edit / change / modify the privileges of an object:

 

pg11

 

You also have the possibility to create scripst for the database creation:

 

pg12

 

Or even to backup the database:

 

pg13

 

This tool seems to be very powerful, even if for the moment, I did not find any performance tool available like in Cloud Control 12c.

 

Conclusion

Discovering PostgreSQL as an Oracle DBA, I realized how close the two products are. The PostgreSQL database has a lot of advantages such as the easy installation, the general usage and the price (because it’s free!).

For the processing of huge amounts of data, Oracle certainly has advantages, nevertheless the choice of a RDBMS always depends on what your application business needs are.

SQL Saturday in Paris on 12 -13 September

Mon, 2014-07-28 22:35

As you certainly know SQL Saturday events are very popular in SQL Server world community. This is the second time the event takes place in Paris (France), but this time, we have a new format with pre-conferences on Wednesday and classic sessions on Saturday. During pre-conferences, we will talk about a particular subject for a whole day.

This time, I have the opportunity to participate twice by giving two sessions (in French) with the following program:

  • Friday: Inside the SQL Server storage and backups

If you are interested in how the SQL Server storage works and how to deal with corruption as well as backups, this session might be interesting for you.

Be careful: the pre-conferences on Wednesday are fee-paying sessions (but not that expensive). You can still register at this address.

  • Saturday: SQL Server AlwaysOn deep dive

SQL Server AlwaysOn is a new great high-availability and disaster recovery feature provided by Microsoft. You can come take a look at this session if you are concerned by questions like:

  • How to configure my Windows failover cluster and quorum in my situation?
  • What exactly is a read-only secondary replica?
  • What are the built-in tools provided by Microsoft to monitor and troubleshoot this infrastructure?

Good news: the sessions on Saturday are free!

Take a look at the agenda if you want to attend to other interesting sessions. I hope there will be many attendees! Smile

PDB media failure may cause the whole CDB to crash

Mon, 2014-07-28 07:44

Do you remember last year, when 12c arrived with multitenant, David Hueber warned us about the fact that a single PDB can, under certain conditions, generate a complete system downtime? We are beta testers and opened a SR for that. Now one year later the first patchset is out and obviously I checked if the issue was fixed. It's a patchset afterall, which is expected to fix issues before than bringing new features.

So the issue was that when the SYSTEM tablespace is lost in a PDB, then we cannot restore it without shutting down the whole CDB. This is because we cannot take the SYSTEM tablespace offline, and we cannot close the PDB as a checkpoint cannot be done. There is no SHUTDOWN ABORT for a PDB that can force to it. Conclusion: if you loose one SYSTEM tablespace, either you accept to wait for a maintenance window before bring it back online, or you have to stop the whole CDB with a shutdown abort.

When I receive a new release, I like to check new parameters, even the undocumented ones. And in 12.1.0.2 there is a new underscore parameter _enable_pdb_close_abort which has the description 'Enable PDB shutdown abort (close abort)'. Great. It has a default value of false but maybe this is how the bug has been addressed.

Before trying that parameter, let's reproduce the case:

Here are my datafiles:

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB_SITE1

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               YES     /u01/app/oracle/oradata/CDB/system01.dbf
3    680      SYSAUX               NO      /u01/app/oracle/oradata/CDB/sysaux01.dbf
4    215      UNDOTBS1             YES     /u01/app/oracle/oradata/CDB/undotbs01.dbf
5    250      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
6    5        USERS                NO      /u01/app/oracle/oradata/CDB/users01.dbf
7    540      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
8    250      PDB1:SYSTEM          NO      /u01/app/oracle/oradata/CDB/PDB1/system01.dbf
9    570      PDB1:SYSAUX          NO      /u01/app/oracle/oradata/CDB/PDB1/sysaux01.dbf
10   5        PDB1:USERS           NO      /u01/app/oracle/oradata/CDB/PDB1/PDB1_users01.dbf
11   250      PDB2:SYSTEM          NO      /u01/app/oracle/oradata/CDB/PDB2/system01.dbf
12   570      PDB2:SYSAUX          NO      /u01/app/oracle/oradata/CDB/PDB2/sysaux01.dbf
13   5        PDB2:USERS           NO      /u01/app/oracle/oradata/CDB/PDB2/PDB2_users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    197      TEMP                 32767       /u01/app/oracle/oradata/CDB/temp01.dbf
2    100      PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/CDB/pdbseed/pdbseed_temp012014-06-15_09-46-11-PM.dbf
3    20       PDB1:TEMP            32767       /u01/app/oracle/oradata/CDB/PDB1/temp012014-06-15_09-46-11-PM.dbf
4    20       PDB2:TEMP            32767       /u01/app/oracle/oradata/CDB/PDB2/temp012014-06-15_09-46-11-PM.dbf

then I just remove the PDB2 SYSTEM datafile:

rm /u01/app/oracle/oradata/CDB/PDB2/system01.dbf 

And I go to sqlplus in order to check the state of my pdb. Remeber, I want to see if I can restore the datafile without doing a shutdown abort on my CDB instance.

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 27 20:31:45 2014

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

Connected to an idle instance.

SQL> select name,open_mode from v$pdbs;
select name,open_mode from v$pdbs
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

Oh... that's bad... Let's look at the alert.log:

Errors in file /u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ckpt_21620.trc:
ORA-01243: system tablespace file suffered media failure
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/u01/app/oracle/oradata/CDB/PDB2/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
USER (ospid: 21620): terminating the instance due to error 1243
System state dump requested by (instance=1, osid=21620 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_diag_21608_20140727202844.trc
2014-07-27 20:28:49.596000 +02:00
Instance terminated by USER, pid = 21620

The CKPT process has terminated the instance. The whole CDB is down.

That's worse. In 12.1.0.1 we had to bring down the instance, but at least we were able to choose the time and warn the users. Not here. In 12.1.0.2 it crashes immediately when a checkpoint occurs.

I've opened a bug for that (Bug 19001390 - PDB SYSTEM TABLESPACE MEDIA FAILURE CAUSES THE WHOLE CDB TO CRASH) which is expected to be fixed for the next release (12.2).

Ok the good news is that once the CDB is down, recovery is straightforward:

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 27 21:36:22 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup

Oracle instance started
database mounted
database opened

Total System Global Area     838860800 bytes

Fixed Size                     2929936 bytes
Variable Size                616565488 bytes
Database Buffers             213909504 bytes
Redo Buffers                   5455872 bytes


RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
3353       CRITICAL OPEN      27-JUL-14     System datafile 11: '/u01/app/oracle/oradata/CDB/PDB2/system01.dbf' is missing
245        HIGH     OPEN      27-JUL-14     One or more non-system datafiles need media recovery


RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
3353       CRITICAL OPEN      27-JUL-14     System datafile 11: '/u01/app/oracle/oradata/CDB/PDB2/system01.dbf' is missing
245        HIGH     OPEN      27-JUL-14     One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/CDB/PDB2/system01.dbf was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode
3. If you restored the wrong version of data file /u01/app/oracle/oradata/CDB/PDB2/sysaux01.dbf, then replace it with the correct one
4. If you restored the wrong version of data file /u01/app/oracle/oradata/CDB/PDB2/PDB2_users01.dbf, then replace it with the correct one

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 11; Recover datafile 12; Recover datafile 13
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/cdb/CDB/hm/reco_3711091289.hm


RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/cdb/CDB/hm/reco_3711091289.hm

contents of repair script:
   # restore and recover datafile
   restore ( datafile 11 );
   recover datafile 11;
   # recover datafile
   recover datafile 12, 13;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 27-JUL-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/CDB/PDB2/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB/FECFFDC5F6D31F5FE043D74EA8C0715F/backupset/2014_07_28/o1_mf_nnndf_TAG20140728T150921_9xdlw21n_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB/FECFFDC5F6D31F5FE043D74EA8C0715F/backupset/2014_07_28/o1_mf_nnndf_TAG20140728T150921_9xdlw21n_.bkp tag=TAG20140728T150921
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 27-JUL-14

Starting recover at 27-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 27-JUL-14

Starting recover at 27-JUL-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 27-JUL-14
repair failure complete


RMAN> alter pluggable database PDB2 open;

Statement processed

I whish that one day the PDB will have true isolation so that I can give DBA rights to the application owner on his PDB. But that means that, at least:

  • A PDB failure cannot crash the CDB instance.
  • A PDB admin cannot create datafiles anywhere on my server.
  • A PDB admin cannot run anything as the instance owner user (usually oracle)

SQL monitoring 12.1.0.2 shows adaptive plans

Fri, 2014-07-25 14:23

In a previous post, I have described Adaptive Plans. Even if I prefer to show plans with the SQL Monitor active html format, I had to stick with the dbms_xplan for that because SQL Monitoring did not show all information about adaptive plans.

This has been fixed in the Patchset 1 and I have run the same query to show the new feature.

First, an adaptive plan can be in two states: 'resolving' where all alternatives are possible and 'resolved' then the final plan has been choosen. It is resolved once the first execution statistics collector has made the decision about the inflection point. We can see the state in the SQL Monitor header:

 

CaptureSqlmonAPStatus.PNG

 

Here my plan is resolved because the first execution is finished.

The plan with rowsource statistics show only the current plan, but the 'Plan Note' shows that it is an adaptive plan:

 

CaptureSqlmonAP.PNG

 

Now we have to go to the 'Plan' tab which show the equivalent of dbms_xplan.display_cursor:

 

CaptureSqlmonAPFull.PNG

 

Here the format is equivalent to format=>'adaptive'. It's the 'Full' plan where all branches are shown but inactive part is grayed. We have here the Statistics Collector after reading DEPARTMENTS, and we have the inactive full table scan hash join of EMPLOYEES.

Just choose the 'Final' Plan (or 'Current' if it is not yet resolved) to get only the active part:

 

CaptureSqlmonAPFinal.PNG

 

I often prefer the tabular format to the graphical one:

 

CaptureSqlmonAPTabular.PNG

 

We have all information: the 7 rows from DEPARTMENTS have gone through STATISTICS COLLECTOR and NESTED LOOP with index access has been choosen. Note that it is different from the previous post where HASH JOIN with full table scan was choosen because the 7 rows were higher than the inflection point.

In my current example, because I have system statistics that costs full table scan higher:

 

DP: Found point of inflection for NLJ vs. HJ: card = 8.35

 

This is higher than ny 7 rows from DEPARTMENTS.

Here is the whole sqlmon report: sqlmon.zip and how I got it:

 

alter session set current_schema=HR;
select /*+ monitor */ distinct DEPARTMENT_NAME from DEPARTMENTS
 join EMPLOYEES using(DEPARTMENT_ID)
 where DEPARTMENT_NAME like '%ing' and SALARY>20000;

alter session set events='emx_control compress_xml=none';set pagesize 0 linesize 10000 trimspool on serveroutput off long 100000000 longc 100000000 echo off feedback off
spool sqlmon.htm
select dbms_sqltune.report_sql_monitor(report_level=>'all',type=>'html') from dual;
spool off
 

Note that I used the script exposed here and I used the emx_event to get the uncompressed xml, which I got from Tyler Muth:

@FranckPachot well, I asked @DBAKevlar, she asked the developer that owns it ;)

— tmuth (@tmuth) July 25, 2014

Beyond In-Memory, what's new in 12.1.0.2 ?

Tue, 2014-07-22 09:53

It's just a patchset. The delivery that is there to stabilize a release with all the bug fixes. But it comes with a lot of new features as well. And not only the one that has been advertised as the future of the database. It's a huge release.

Let's have a look at what's new.

First, it seems that it will be the only patchest for 12.1

Then, there is that In-Memory option awaited for a while. There has been some demo done by Larry Ellison on Exadata or even on the Oracle SPARC M6. Of course, if you have 32 TB of memory, we can understand the need for an In-Memory optimized storage. For a more real-life usage of that option, stay tune on our blog. We investigate the features in the context of our customer concerns, to fit their needs. For example, In-Memory addresses cases where some customers use Active Data Guard to offload reporting/real-time analytics to another server. But unfortunately In-Memory is not populated on a physical standby. We probably have to wait 12.2 for that.

In-Memory is an option, so available only in Enterprise Edition.

There are other new features related with large memory. There is a part of buffer cache dedicated to big tables (you just set the percentage) to be cached for In-Memory Parallel Query. And there is also a mode where all the database is in buffer cache. About performance and Parallel Query, a new transformation has been introduced to optimize the group by operation when joining a fact table to dimensions.

Second new feature is the range-partitioned hash cluster. Oracle CLUSTER segments is a very old feature but not widely used. Hash cluster is the fastest way to access to a row because the key can be directly transformed to a rowid. Unfortunately maintenance is not easy, especially when the volume increases. And we have partitioning which is the way to ease maintenance with growing tables but, until today, we can't partition a hash cluster. I mean, not in a supported way because Oracle uses it on SPARC for the TPC benchmarks - applying a specific patch (10374168) for it.

Well, the good news is that we can finally partition hash clusters with the simple syntax:

create cluster democ1 (sample_time timestamp,sample_id number)
hashkeys 3600 hash is sample_id size 8192
partition by range (sample_time) (
partition P12 values less than( timestamp'2014-04-26 12:00:00' )
... 

Another nice feature is Attribute Clustering. Lot of other RDBMS has the ability to arrange rows but Oracle puts any insert anywhere in a heap table, depending only on where some free space is left. The alternative is IOT of course. But it can be good to try to cluster rows on one or several columns. It's better for index access, it's better for cache efficiency, it's better for storage indexes (or in-memory min/max), for ILM compression, etc. We can finally do it and I'll blog soon about that. 

Attribute Clustering is not an option, but available only in Enterprise Edition.

 

I think those two features are my favorite ones. Because the best optimization we can do, without refactoring the application design, is to place data in the way it will be retreived.

 

The trend today is to store unstructured data as JSON. XML was nice, but it's verbose. JSON is easier to read and even PostgreSQL can store JSON in its latest version. So Oracle has it in 12.1.0.2: you can store and index it. Once again stay tuned on this blog to see how it works.

Something important was missing in Oracle SQL. How do you grant a read only user? You grant only select privilege? But that's too much because with a select privilege we can lock a table (with LOCK or SELECT FOR UPDATE). So we have now a READ privilege to prevent that. That's my favorite new feature for developers.

Then there are a few improvements on multitenant, such as the possibility to save the state of a pluggable database so that it can be automatically opened when the CDB startup. We already addressed that in 12.1.0.1 in our Database Management Kit. An undocumented parameter, _multiple_char_set_cdb, let us imagine that we will be able to have different characterset for the PDB - probably in the future. Currently it's set to false.

And once again as beta testing partners we have put the pressure to have a fix for what we consider as serious availability bug. The behaviour in 12.1.0.1 beta was even worse about CDB availability and I finally had a bug opened (Bug 19001390 - PDB SYSTEM TABLESPACE MEDIA FAILURE CAUSES THE WHOLE CDB TO CRASH) that should be fixed in 12.1

About fixes, some restrictions are now gone: we can finally use ILM with multitenant and we can have supplemental logging while using a move partition online. And you can have Flashback Data Archive in multitenant as well.

All that is good news, but remember, even if it's only the 4th digit that is increased in the version number, it's a brand new version with lot of new features. So, when do you plan to upgrade ? 11g is supported until January 2015. Extended support is free until January 2016 given that you are in the terminal patchset (11.2.0.4). So either you don't want to be in the latestet release and you will have to upgrade to 11.2.0.4 before the end of the year, waiting for 12.2 maybe in 2016. Or you want those new features and will probably go to 12.1.0.2 for 2015.

Talking about upgrade, there's a bad news. We thought that multitenancy can accelarate upgrade time. Because the data dictionary is shared, you just have to plug a PDB into a newer version CDB and it's upgraded. And we show that in our 12c new features workshop by applying a PSU. But we have tested the upgrade to 12.1.0.2 in the same way, and it's not that simple. Plugging is quick when you have only new patches that did not change the dictionary. It's still true for PSU when the dictionary changes are limited to the root container. But when you upgrade to 12.1.0.2 you have to synchronize all the PDB dictionaries (all that magic behind object links and metadata links) and that takes time. It takes the same time as upgrading a non-CDB. Conclusion: you don't save time when you do it by plug/unplug.

But I have good news as well for that because I've tested a 1 minute downtime migration from 12.1.0.1 to 12.1.0.2. Dbvisit replicate, the affordable replication solution, supports multitenant in it's latest version, both as source and target. If your application is compatible (which is easy to check with the 30 days trial) then it's a good way to migrate without stress and with minimal downtime. It's available for Standard Edition as well, but currently the 12.1.0.2 download can install only an Enterprise Edition.

Backup an SQL Server database from On-Premise to Azure

Sun, 2014-07-20 23:46

SQL Server database backup & restore from On-Premise to Azure is a feature introduced with SQL Server 2012 SP1 CU2. In the past, it could be used with these three tools:

  • Transact-SQL (T-SQL)
  • PowerShell
  • SQL Server Management Objects (SMO)

With SQL Server 2014, backup & restore can also be enabled via SQL Server Management Studio (SSMS).

Oracle EM agent 12c thread leak on RAC

Thu, 2014-07-17 22:24

In a previous post about nproc limit, I wrote that I had to investigate the nproc limit with the number of threads because my Oracle 12c EM agent was having thousands of threads. This post is a short feedback about this issue and the way I have found the root cause. It concerns the enterprise manager agent 12c on Grid Infrasctructure >= 11.2.0.2

 

NLWP

The issue was:

 

ps -o nlwp,pid,lwp,args -u oracle | sort -n
NLWP   PID   LWP COMMAND
   1  8444  8444 oracleOPRODP3 (LOCAL=NO)
   1  9397  9397 oracleOPRODP3 (LOCAL=NO)
   1  9542  9542 oracleOPRODP3 (LOCAL=NO)
   1  9803  9803 /u00/app/oracle/product/agent12c/core/12.1.0.3.0/perl/bin/perl /u00/app/oracle/product/agent12c/core/12.1.0.3.0/bin/emwd.pl agent /u00/app/oracle/product/agent12c/agent_inst/sysman/log/emagent.nohup
  19 11966 11966 /u00/app/11.2.0/grid/bin/oraagent.bin
1114  9963  9963 /u00/app/oracle/product/agent12c/core/12.1.0.3.0/jdk/bin/java ... emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain

 

By default ps has only one entry per process, but each processes can have several threads - implemented on linux as light-weight process (LWP). Here, the NLWP column shows that I have 1114 threads for my EM 12c agent - and it was increasing every day until it reached the limit and the node failed ('Resource temporarily unavailable').

The first thing to do is to know what those threads are. The ps entries do not have a lot of information, but I discovered jstack which every java developer should know, I presume. You probably know that java has very verbose (lengthy) stack traces. Jstack was able to show me thousands of them in only one command:

 

Jstack
$ jstack 9963
2014-06-03 13:29:04
Full thread dump Java HotSpot(TM) 64-Bit Server VM (20.14-b01 mixed mode):

"Attach Listener" daemon prio=10 tid=0x00007f3368002000 nid=0x4c9b waiting on condition [0x0000000000000000]
   java.lang.Thread.State: RUNNABLE

"CRSeOns" prio=10 tid=0x00007f32c80b6800 nid=0x3863 in Object.wait() [0x00007f31fe11f000]
   java.lang.Thread.State: TIMED_WAITING (on object monitor)
	at java.lang.Object.wait(Native Method)
	at oracle.eons.impl.NotificationQueue.internalDequeue(NotificationQueue.java:278)
	- locked  (a java.lang.Object)
	at oracle.eons.impl.NotificationQueue.dequeue(NotificationQueue.java:255)
	at oracle.eons.proxy.impl.client.base.SubscriberImpl.receive(SubscriberImpl.java:98)
	at oracle.eons.proxy.impl.client.base.SubscriberImpl.receive(SubscriberImpl.java:79)
	at oracle.eons.proxy.impl.client.ProxySubscriber.receive(ProxySubscriber.java:29)
	at oracle.sysman.db.receivelet.eons.EonsMetric.beginSubscription(EonsMetric.java:872)
	at oracle.sysman.db.receivelet.eons.EonsMetricWlm.run(EonsMetricWlm.java:139)
	at oracle.sysman.gcagent.target.interaction.execution.ReceiveletInteractionMgr$3$1.run(ReceiveletInteractionMgr.java:1401)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
	at oracle.sysman.gcagent.util.system.GCAThread$RunnableWrapper.run(GCAThread.java:184)
	at java.lang.Thread.run(Thread.java:662)
...
 
CRSeOns

I don't paste all of them here. We have the 'main', we have a few GCs and 'Gang workers' which are present in all JVMs and we have a few enterprise manager threads. And what was interesting was that I had thousands of "CRSeOns" that seemed to be increasing.

Some guesses: I'm on RAC, and I have a 'ons' resource and the EM agent tries to subscribe to it. Goggle search returned nothing, and that's the reason I put that in a blog post now. Then I searched MOS, and bingo, there is a note: Doc ID 1486626.1. It has nothing to do with my issue, but has an interesting comment in it:

In cluster version 11.2.0.2 and higher, the ora.eons resource functionality has been moved to EVM. Because of this the ora.eons resource no longer exists or is controlled by crsctl.

It also explains how to disable EM agent subscription:

emctl setproperty agent -name disableEonsRcvlet -value true

I'm in 11.2.0.3 and I have thousands of threads related to a functionality that doesn't exist anymore. And that leads to some failures in my 4 nodes cluster.

The solution was simple: disable it.

For a long time I have seen a lot of memory leaks or CPU usage leaks related to the enterprise manager agent. With this new issue, I discovered a thread leak and I also faced a SR leak when trying to get support for the 'Resource temporarily unavailable' error, going back and forth between OS, Database, Cluster and EM support teams...

SQL Server Perfmon does not start automatically

Mon, 2014-07-14 19:28

I have recently used perfmon (performance monitor) at a customer site. I created a Data Collector Set to monitor CPU, Memory, Disk, and Network during one day. Then, I ran the monitor and I received a "beautiful" error message…

Partial Join Evaluation in Oracle 12c

Sun, 2014-07-13 21:21

Do you think that it's better to write semi-join SQL statements with IN(), EXISTS(), or to do a JOIN? Usually, the optimizer will evaluate the cost and do the transformation for you. And in this area, one more transformation has been introduced in 12c which is the Partial Join Evaluation (PJE).

First, let's have a look at the 11g behaviour. For that example, I use the SCOTT schema, but I hire a lot more employees in departement 40:

 

SQL> alter table EMP modify empno number(10);
Table altered.
SQL> insert into EMP(empno,deptno) select rownum+10000,40 from EMP,(select * from dual connect by level

 

Why department 40? I'll explain it below, but I let you think about it before. In the default SCOTT schema, there is a department 40 in DEPT table, but which has no employees in EMP. And the new transformation is not useful in that case.

 

11g behaviour

Now, I'm running the following query to check all the departments that have at least one employee:

I can write it with IN:

 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  6y71msam9w32r, child number 0
-------------------------------------
select distinct deptno,dname from dept 
 where deptno in ( select deptno from emp)

Plan hash value: 1754319153

------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows | Buffers |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      4 |      15 |
|*  1 |  HASH JOIN SEMI    |      |      1 |      4 |      4 |      15 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |       7 |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |  15068 |    388 |       8 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"="DEPTNO")

 

or with EXISTS:

 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  cbpa3zjtzfzrn, child number 0
-------------------------------------
select distinct deptno,dname from dept 
 where exists ( select 1 from emp where emp.deptno=dept.deptno)

Plan hash value: 1754319153

------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows | Buffers |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      4 |      15 |
|*  1 |  HASH JOIN SEMI    |      |      1 |      4 |      4 |      15 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |       7 |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |  15068 |    388 |       8 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"="DEPTNO")

 

Both are good. We didn't have to read the whole EMP table. I have 15000 rows in my table, I do a full scan on it, but look at the A-Rows: only 388 rows were actually read.

The HASH JOIN first read the DEPT table in order to build the hash table. So it already knows that we cannot have more than 4 distinct departments.

Then we do the join to EMP just to check which of those departments have an employee. But we can stop as soon as we find the 4 departments. This is the reason why we have read only 388 rows here. And this is exactly what a Semi Join is: we don't need all the matching rows, we return at most one row per matching pair.

Ok. What if we write the join ourselves?

 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID  2xjj9jybqja87, child number 1
-------------------------------------
select distinct deptno,dname from dept join emp using(deptno)

Plan hash value: 2962452962

-------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows | Buffers |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      4 |     129 |
|   1 |  HASH UNIQUE        |      |      1 |  15068 |      4 |     129 |
|*  2 |   HASH JOIN         |      |      1 |  15068 |  14014 |     129 |
|   3 |    TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |       7 |
|   4 |    TABLE ACCESS FULL| EMP  |      1 |  15068 |  14014 |     122 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

 

Bad luck. We have to read all the rows. More rows and more buffers.

 

12c behaviour

Let's do the same in 12.1.0.1:

 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID  2xjj9jybqja87, child number 0
-------------------------------------
select distinct deptno,dname from dept join emp using(deptno)

Plan hash value: 1629510749

-------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows | Buffers |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      4 |      14 |
|   1 |  HASH UNIQUE        |      |      1 |      4 |      4 |      14 |
|*  2 |   HASH JOIN SEMI    |      |      1 |      4 |      4 |      14 |
|   3 |    TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |       7 |
|   4 |    TABLE ACCESS FULL| EMP  |      1 |  15068 |    388 |       7 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

 

Same plan but less rows have been read. If we check the outlines, we see the new feature:

 

      PARTIAL_JOIN(@"SEL$58A6D7F6" "EMP"@"SEL$1")

 

And here is what we see in the optimizer trace:

 

OPTIMIZER STATISTICS AND COMPUTATIONS
PJE: Checking validity of partial join eval on query block SEL$58A6D7F6 (#1)
PJE: Passed validity of partial join eval by query block SEL$58A6D7F6 (#1)
PJE: Partial join eval conversion for query block SEL$58A6D7F6 (#1).
PJE: Table marked for partial join eval: EMP[EMP]#1

 

The hints that controls the feature are PARTIAL_JOIN and NO_PARTIAL_JOIN and there are enabled by _optimizer_partial_join_eval which appeared in 12c.

But of course, the optimization is useful only when we have all the values at the beginning of the table. This is why I added at least one employee in department 40. If there are some rows in DEPT that have no matching row in EMP, then Oracle cannot know the result before reaching the end of the table.

Master Data Services installation for SQL Server 2012

Sun, 2014-07-13 20:32

This posting is a tutorial for installing Master Data Services on your Windows Server 2012. Microsoft SQL Server Master Data Services (MDS) is a Master Management product from Microsoft, code-named Bulldog. It is the rebranding of the Stratature MDM product, titled +EDM and acquired in June 2007 by Microsoft. Initially, it was integrated for the first time in Microsoft SQL Server 2008 as an additional installer. But since SQL Server 2012, Master Data Services is integrated as a feature within the SQL Server installer.

 

Introduction

Master Data Services is part of the Enterprise Information Management (EMI) technologies, provided by Microsoft, for managing information in an enterprise.

EMI technologies include:

  • Integration Services
  • Master Data Services
  • Data Quality Services

 

Components

Master Data Services covers five main components:

  • MDS Configuration Manager tool: used to configure Master Data Services
  • MDS Data Manager Web Application: used essentially to perform administrative tasks
  • MDS Web Service: used to extend or develop custom solutions
  • MDS Add-in for Excel: used to manage data, create new entities or attributes …

 

SQL Server Editions & Versions

Master Data Services can be installed only with the following SQL Server Editions & Versions:

  • SQL Server 2008 R2 edition: Datacenter or Enterprise versions
  • SQL Server 2012 or SQL Server 2014 editions: Enterprise or BI versions

 

Master Data Services prerequisites in SQL Server 2012

First, Master Data Services is based on an application web named Master Data Manager Web Application, in order to perform administrative task, for example. This web application is hosted by Internet Information Services (IIS), so it is a necessary prerequisite.

Furthermore, to be able to display the content from the web application, you need Internet Explorer 7 or later (Internet Explorer 6 is not supported) with Silverlight 5.

Moreover, if you planned to use Excel with Master Data Services, you also need to install Visual Studio 2010 Tools for Office Runtime, plus the Master Data Services Add-in for Microsoft Excel.

Finally, often forgotten, but PowerShell 2.0 is required for Master Data Services.

 Let’s resume the requirements for Master Data Services:

  • Internet Information Services (IIS)
  • Internet Explorer 7 or later
  • Silverlight 5
  •  PowerShell 2.0
  • Visual Studio 2010 Tools for Office Runtime and Excel Add-in for Microsoft Excel (only if you plan to use Excel with Master Data Services).

 

Configuration at the Windows Server level

In the Server Manager, you have to activate the Web Server (IIS) Server Roles to be able to host the Master Data Web Application, as well as the .Net 3.5 feature.

For the Server Roles, you have to select:

  • Web Server (IIS)

For the Server Features, you have to select:

- .NET Framework 3.5 Features
  - .NET Framework 3.5
  - HTTP Activation
- .NET Framework 4.5 features
  - .NET Framework 4.5
  - ASP.NET 4.5
  - WCF Services
    - HTTP Activation
    - TCP Port Sharing

 

MDS_features_selection_2.png

 

For the IIS features selection, you have to select:

- Web Server
  - Common HTTP Features
    - Default Document
    - Directory Browsing
    - HTTP Errors
    - Static Content
  - Health and Diagnostics
    - HTTP Logging
    - Request Monitor
  - Performance
    - Static Content Compression
  - Security
    - Request Filtering
    - Windows Authentication
  - Application Development
    - .NET Extensibility
    - .NET Extensibility 4.5
    - ASP.NET 3.5
    - ASP.NET 4.5
    - ISAPI Extensions
    - ISAPI Filters
  - Management Tools
    - IIS Management Console

 

MDS_features_selection.png

MDS_features_selection_3.png 

 

Installation of SQL Server 2012

Master Data Services stores its data on a SQL Server database, so you need a SQL Server Engine installed.

Of course, SQL Server Engine can be installed on a different Windows Server. So the Windows Server with Master Data Services installed is used as a Front Server.

Then, in order to personalize the roles of your Master Data Services, you also need to install Management Tools.

At the features installation step, you have to select:

  • Database Engine Services
  • Management Tools
  • Master Data Services

 

Conclusion

At this point, Master Data Services should be installed with all the needed prerequisites.


MDS_confirm_prerequisites.png

 

However, Master Data Services cannot be used without configuring it. Three main steps need to be performed through the MDS Configuration Manager:

  • First, you have to create a MDS database
  • Then, you have to create a MDS web application hosted in IIS
  • Finally, you have to link the MDS database with the MDS web application

SQL Server 2014: Are DENY 'SELECT ALL USERS SECURABLES' permissions sufficient for DBAs?

Wed, 2014-07-02 20:09

SQL Server 2014 improves the segregation of duties by implementing new server permissions. The most important is the SELECT ALL USERS SECURABLES permission that will help to restrict database administrators from viewing data in all databases.

My article is a complement to David Barbarin's article 'SQL Server 2014: SELECT ALL USERS SECURABLES & DB admins'.

Java Mission Control 5.2 (7u40) deserves your attention

Mon, 2014-06-30 20:38

Recently, some new versions of java were made available. Most people think Java updates are boring and only security-oriented. But one of the last updates (7u40) includes a feature which deserves attention. I mean Java Mission Control 5.2.

 

Hotspot incoming

If you know the Oracle JRockit JVM a little bit, you might have heard about JMC, which was called JRockit Mission Control in the past. In fact, it’s a tool suite embedded with the Hotspot JDK since this so called 7u40 release which allows to monitor and profile your JVM.

Previously, it was only available for JRockit JVMs and it has now been ported to the Hotspot. JMC is a way more accurate and complete than JConsole or other embedded tools. It does not affect JVM performances more than 1%.

 

B006---JMCView1.png

 

Mission Completed

JMC gathers low level information thanks to its Flight Recorder tool which listens and waits for internal events. It can then monitor, manage, profile, and eliminate memory leaks from the JVM.

The new version of JMC now has a new browser with subnodes for available server side services with their states. It is supported by Eclipse 3.8.2/4.2.2 and later, it allows deeper management of MBeans, especially setting values directly in the attribute tree. It converges with JRockit event management: All information provided by JRockit is now available in the Hotspot as well.

 

B006---JMCView2.png

 

To enable JMC, you will have to add the following arguments to the JVM:


-XX:+UnlockCommercialFeatures
-XX:+|-FlightRecorder
-XX:+|-StartFlightRecording
-XX:FlightRecorderOptions=parameter=value

 

Sources

Oracle: http://www.oracle.com/technetwork/java/javase/2col/jmc-relnotes-2004763.html