Yann Neuhaus
Prochaine édition des 24 HOP 2017 francophone
La prochaine édition du 24 Hours of PASS 2017 edition francophone se déroulera les 29-30 juin prochain.
Pour rappel le format est simple: 24 webinars gratuits répartis sur 2 jours de 07:00 à 18h00 GMT et en Français. La seule obligation: s’inscrire aux sessions auxquelles vous assisterez. Cela vous permettra également de récupérer l’enregistrement vidéo si vous voulez la visionner à nouveau par la suite.
Cette année il y en aura encore pour tous les goûts. Du monitoring, de la performance, de l’Azure, de la BI, du BigData et machine learning, de la modélisation, de la haute disponibilité, de l’open source et des nouveautés concernant la prochaine version de SQL Server!
Pour ma part j’aurai le privilège de présenter une session concernant les nouvelles possibilités en terme de haute disponibilité avec SQL Server dans un monde mixte (Windows et Linux) et un monde “full Linux”.
Au plaisir de vous y retrouver!
Cet article Prochaine édition des 24 HOP 2017 francophone est apparu en premier sur Blog dbi services.
New release model for JUL17 (or Oracle 17.3): RU and RUR
When reading about new version numbering in SQL Developer, I took the risk to change the title and guess the future version number for Oracle Database: Oracle 17.3 for the July (Q3) of 2017. Of course, this is just a guess.
Confirming the previous guess, we start to see some bugs planned to be fixed in version 18.1 which is probably the January 2018 Release Update.
Oracle Database software comes in versions: 10g in 2004, 11g in 2007, 12c in 2013
In between, there are releases: 10gR2 in 2005, 11gR2 in 2009, 12cR2 in 2017
In between, there are Patch Sets: the latest 11gR2 is 11.2.0.4 (2 years after 11.2.0.3) and 12cR1 is 12.1.0.2 (one year after 12.1.0.1)
Those are full install: full Oracle Home. It can be in-place or into a new Oracle Home but it is a full install. There are a lot of changes in the system dictionary and you run catupgrd.sql to update it. It takes from 30 minutes to 1 hour on average depending on the components and the system.
Their primary goal is to release features. You should test them carefully. For example, the In-Memory option came in the first Patch Set of 12cR1
This will change in 2017 with annual feature releases. Well, this looks like a rename of Patch Set.
All releases are supported several years, with fixes (patches) provided for encountered issues: security, wrong result, hanging, crash, etc. Rather than installing one-off patches, and requesting merges for them, Oracle supplies some bundle patches: merged together, tested as a whole, cumulative, with a quarterly release frequency. Depending on the content and the platform, they are called Bundle Patches (in Windows), CPU (only security fixes), SPU (same as CPU but renamed to SPU), PSU (Patch Set Update), Proactive Bundle Patch (a bit more than in the PSU)…
The names have changed, the versioning number as well as they now include the date of release.
You apply patches into the Oracle Home with OPatch utility and into the database dictionary with the new datapatch utility.
Their primary goal is to get stability: fix issues with a low risk of regression. The minimum recommended is in the PSU, more fixes are in the ProactiveBP for known bugs.
This will change in 2017 with quarterly Release Updates. Well, this looks like a rename of PSU to RUR (Release Update Revision) and a rename of ProactiveBP as RU (Release Update).
The goal is to reduce the need to apply one-off patches on top of PSUs.
Here is all what I know about it, as presented by Andy Mendelsohn at DOAG Datenbank 2017 keynote:
It is not common to have new announcements in the last fiscal year quarter. Thanks DOAG for this keynote.
Cet article New release model for JUL17 (or Oracle 17.3): RU and RUR est apparu en premier sur Blog dbi services.
Oracle 12cR2: exchange partition deferred invalidation
In a previous post I introduced the new 12cR2 feature where some DDL operations can use the same rolling invalidation than what is done with dbms_stats. On tables, DDL deferred invalidation is available only for operations on partitions. Here is how it works for partition exchange.
Here is my session environment:
SQL> whenever sqlerror exit failure
SQL> alter session set nls_date_format='hh24:mi:ss';
Session altered.
SQL> alter session set session_cached_cursors=0;
Session altered.
SQL> alter session set optimizer_dynamic_sampling=0;
Session altered.
SQL> alter system set "_optimizer_invalidation_period"=5;
System SET altered.
SQL> show user
USER is "DEMO"
I create a partitioned table with one local index
SQL> create table DEMO (n number, p number) partition by list(p) (partition P1 values(1), partition P2 values(2));
Table DEMO created.
SQL> create index DEMO on DEMO(n) local;
Index DEMO created.
I create the table with same structure for exchange
SQL> create table DEMOX for exchange with table DEMO;
Table DEMOX created.
SQL> create index DEMOX on DEMOX(n);
Index DEMOX created.
The CREATE TABLE FOR EXCHANGE do not create the indexes, but for rolling invalidation we need them. Without the same indexes, immediate invalidation occurs.
In order observe invalidation, I run queries on the partitioned tables, involving or not the partition I’ll exchange. I also run a query on the table used for exchange.
SQL> SELECT * FROM DEMO partition (P1);
no rows selected
SQL> SELECT * FROM DEMO partition (P2);
no rows selected
SQL> SELECT * FROM DEMO;
no rows selected
SQL> SELECT * FROM DEMOX;
no rows selected
Here are the cursors and some execution plans:
SQL> select sql_id,sql_text,child_number,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%' order by sql_text;
SQL_ID SQL_TEXT CHILD_NUMBER INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------ -------- ------------ ------------- ----- ----------- ---------- --------------- -------------- ---------------- ------------------
dd3ajp6k49u1d SELECT * FROM DEMO 0 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:12 N
1ft329rx910sa SELECT * FROM DEMO partition (P1) 0 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:12 N
9pp3h276waqvm SELECT * FROM DEMO partition (P2) 0 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:12 N
by2m6mh16tpsz SELECT * FROM DEMOX 0 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:12 N
SQL> select * from table(dbms_xplan.display_cursor('1ft329rx910sa',0,'basic +partition'));
PLAN_TABLE_OUTPUT
-----------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM DEMO partition (P1)
Plan hash value: 3520634703
------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION LIST SINGLE| | 1 | 1 |
| 2 | TABLE ACCESS FULL | DEMO | 1 | 1 |
------------------------------------------------------
SQL> select * from table(dbms_xplan.display_cursor('dd3ajp6k49u1d',0,'basic +partition'));
PLAN_TABLE_OUTPUT
-----------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM DEMO
Plan hash value: 1180220521
---------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
---------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION LIST ALL| | 1 | 2 |
| 2 | TABLE ACCESS FULL| DEMO | 1 | 2 |
---------------------------------------------------
I exchange the partition P1 with the table DEMOX. I include indexes and add the DEFERRED INVALIDATION clause
SQL> alter table DEMO exchange partition P1 with table DEMOX including indexes without validation deferred invalidation;
Table DEMO altered.
If I do the same without the DEFERRED INVALIDATION clause, or without including indexes, or having different indexes, then I would see all cursors invalidated. Here only the select on the DEMOX table is invalidated:
SQL> select sql_text,child_number,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%' order by sql_text;
SQL_TEXT CHILD_NUMBER INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
-------- ------------ ------------- ----- ----------- ---------- --------------- -------------- ---------------- ------------------
SELECT * FROM DEMO 0 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:12 N
SELECT * FROM DEMO partition (P1) 0 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:12 N
SELECT * FROM DEMO partition (P2) 0 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:12 N
SELECT * FROM DEMOX 0 1 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:12 N
I expected to see the IS_ROLLING_INVALID flag changed to ‘Y’ as we can observe with other operations. I have opened an SR for that.
Rolling invalidation sets a timestamp at next execution:
SQL> SELECT * FROM DEMO partition (P1);
no rows selected
SQL> SELECT * FROM DEMO partition (P2);
no rows selected
SQL> SELECT * FROM DEMO;
no rows selected
SQL> SELECT * FROM DEMOX;
no rows selected
SQL> select sql_text,child_number,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%' order by sql_text;
SQL_TEXT CHILD_NUMBER INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
-------- ------------ ------------- ----- ----------- ---------- --------------- -------------- ---------------- ------------------
SELECT * FROM DEMO 0 0 1 2 2 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:14 N
SELECT * FROM DEMO partition (P1) 0 0 1 2 2 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:14 N
SELECT * FROM DEMO partition (P2) 0 0 1 2 2 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:14 N
SELECT * FROM DEMOX 0 1 2 1 1 2017-05-26/10:06:12 2017-05-26/10:06:14 10:06:14 N
I expected to see IS_ROLLING_INVALID going from ‘Y’ to ‘X’ here when the random time is set for invalidation.
By default, the random time is set within a 5 hours window, but I changed “_optimizer_invalidation_period” to 5 seconds instead and I wait for this time window to be sure that invalidation occurs. And then run my queries again.
SQL> host sleep 5
SQL> SELECT * FROM DEMO partition (P1);
no rows selected
SQL> SELECT * FROM DEMO partition (P2);
no rows selected
SQL> SELECT * FROM DEMO;
no rows selected
SQL> SELECT * FROM DEMOX;
no rows selected
Here are the new child cursors created for the ones that were marked for rolling invalidation. The IS_ROLLING_INVALID did not display anything, but it seems that it works as expected:
SQL> select sql_text,child_number,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%' order by sql_text;
SQL_TEXT CHILD_NUMBER INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
-------- ------------ ------------- ----- ----------- ---------- --------------- -------------- ---------------- ------------------
SELECT * FROM DEMO 0 0 1 2 2 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:14 N
SELECT * FROM DEMO 1 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:19 10:06:19 N
SELECT * FROM DEMO partition (P1) 1 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:19 10:06:19 N
SELECT * FROM DEMO partition (P1) 0 0 1 2 2 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:14 N
SELECT * FROM DEMO partition (P2) 1 0 1 1 1 2017-05-26/10:06:12 2017-05-26/10:06:19 10:06:19 N
SELECT * FROM DEMO partition (P2) 0 0 1 2 2 2017-05-26/10:06:12 2017-05-26/10:06:12 10:06:14 N
SELECT * FROM DEMOX 0 1 2 2 2 2017-05-26/10:06:12 2017-05-26/10:06:14 10:06:19 N
Here is the confirmation that those 3 cursors were not shared because they have passed the rolling invalidation window:
SQL> select sql_id,child_number,reason from v$sql_shared_cursor join v$sql using(sql_id, child_number) where sql_text like 'S%DEMO%';
SQL_ID CHILD_NUMBER REASON
------ ------------ ------
1ft329rx910sa 0 033Rolling Invalidate Window Exceeded(3)2x414957859751495785979
1ft329rx910sa 1
by2m6mh16tpsz 0
dd3ajp6k49u1d 0 033Rolling Invalidate Window Exceeded(3)2x414957859771495785979
dd3ajp6k49u1d 1
9pp3h276waqvm 0 033Rolling Invalidate Window Exceeded(3)2x414957859781495785979
9pp3h276waqvm 1
The first observation is that deferred invalidation works with partition exchange, despite the fact that the V$SQL.IS_ROLLING_INVALID flag is not updated. I was surprised to see that rolling invalidation occurs even for the cursors accessing to the partition which was exchanged. However, the rolling invalidation occurs only if the indexes are the same. If we do not exchange the indexes, then all cursors are invalidated immediately. This means that probably the cursor parsed is compatible to run after the exchange as the indexes are guaranteed to have same structure, type, compression,…
This is a very nice feature when exchange partition is used to keep the fact table when loading new data: you load into a table and then exchange it with the latest partition. The new values are now exposed immediately and this new feature avoids a hard parse peak.
Cet article Oracle 12cR2: exchange partition deferred invalidation est apparu en premier sur Blog dbi services.
Which privilege for CREATE PLUGGABLE DATABASE from DB LINK?
When cloning a PDB from a remote CDB you need to define a database link to be used in the CREATE PLUGGABLE DATABASE … FROM …@… command. The documentation is not completely clear about the privileges required on the source for the user defined in the database link, so here are the different possibilities.
Remote cloneHere is what the documentation says:
So you can connect to the CDB or to the PDB.
In order to connect to the CDB you need a common user with the CREATE SESSION system privilege:
SQL> create user C##DBA identified by oracle;
User C##DBA created.
SQL> grant create session to C##DBA container=current;
Grant succeeded.
No need for CONTAINER=ALL here because you connect only to the CDB$ROOT.
Then you need the CREATE PLUGGABLE DATABASE system privilege on the PDB. You can grant it from the CDB$ROOT with the CONTAINER=ALL but it is sufficient to grant it locally on the source PDB:
SQL> alter session set container=PDB1;
Session altered.
SQL> grant create pluggable database to C##DBA container=current;
Grant succeeded.
Note that, not documented, but the SYSOPER administrative privilege can replace the CREATE PLUGGABLE DATABASE so we can run the following instead of the previous one:
SQL> alter session set container=PDB1;
Session altered.
grant sysoper to C##DBA container=current;
Grant succeeded.
Both ways are usable for cloning, you create a database link to this common user, on the destination, and run the CLONE PLUGGABLE DATABASE:
SQL> create database link CDB1A connect to C##DBA identified by oracle using '//localhost/CDB1A';
Database link CDB1A created.
SQL> create pluggable database PDB1CLONE from PDB1@CDB1A file_name_convert=('CDB1A/PDB1','CDB2A/PDB1CLONE');
Pluggable database PDB1CLONE created.
SQL> alter pluggable database PDB1CLONE open;
Pluggable database PDB1CLONE altered.
This was using a common user but you can also define the user locally on the source PDB:
SQL> alter session set container=PDB1;
Session altered.
SQL> create user PDBDBA identified by oracle;
User PDBDBA created.
SQL> grant create session to PDBDBA container=current;
Grant succeeded.
SQL> grant create pluggable database to PDBDBA container=current;
Grant succeeded.
There again you have the alternative to use SYSOPER instead of CREATE PLUGGABLE DATABASE:
SQL> alter session set container=PDB1;
Session altered.
SQL> create user PDBDBA identified by oracle;
User PDBDBA created.
SQL> grant create session to PDBDBA container=current;
Grant succeeded.
SQL> grant sysoper to PDBDBA container=current;
Grant succeeded.
With one of those, you can clone from the target with a database link connecting to the local user only:
SQL> create database link CDB1A connect to PDBDBA identified by oracle using '//localhost/PDB1';
Database link CDB1A created.
SQL> create pluggable database PDB1CLONE from PDB1@CDB1A file_name_convert=('CDB1A/PDB1','CDB2A/PDB1CLONE');
Pluggable database PDB1CLONE created.
SQL> alter pluggable database PDB1CLONE open;
Pluggable database PDB1CLONE altered.
Then which alternative to use? The choice of the common or local user is up to you. I probably use a common user to do system administration, and cloning is one of them. But if you are in a PDBaaS environment where you are the PDB administrator, then you can clone your PDB to another CDB that you manage. This can mean cloning a PDB from the Cloud to a CDB on your laptop.
PDB RelocateThings are different with the RELOCATE option where you drop the source PDB and redirect the connection to the new one. This is definitely a system administration task to do at CDB level and requires a common user. Trying it from a database link connecting to a local user will raise the following error:
ORA-17628: Oracle error 65338 returned by remote Oracle server
65338, 00000, "unable to create pluggable database"
// *Cause: An attempt was made to relocate a pluggable database using a
// database link to the source pluggable database.
// *Action: Use a database link that points to the source multitenant container
// database root and retry the operation.
Here is what the documentation says:
So, we need to have a common user on the source CDB, with CREATE SESSION privilege, and it makes sense to use an administrative privilege:
SQL> create user C##DBA identified by oracle;
User C##DBA created.
SQL> grant create session to C##DBA container=current;
Grant succeeded.
SQL> alter session set container=PDB1;
Session altered.
grant sysoper to C##DBA container=current;
Grant succeeded.
The documentation mentions that you can use either SYSDBA or SYSOPER, but from my tests (and Deiby Gómez ones) only SYSOPER works without raising an ‘insufficient privileges’. The documentation mentions that CREATE PLUGGABLE DATABASE is also necessary. Actually, it is not. And, with a relocate, it cannot be an alternative to SYSOPER. The user must be a common user, the CREATE SESSION must be granted commonly, but the SYSOPER can be locally for the PDB we relocate.
In summaryTo clone a remote PDB you can use a common or local user, with SYSOPER or CREATE PLUGGABLE DATABASE privilege. To relocate a PDB you need a common user with SYSOPER.
Cet article Which privilege for CREATE PLUGGABLE DATABASE from DB LINK? est apparu en premier sur Blog dbi services.
OUD – Oracle Unified Directory 11.1.2.3, How to change the Java version of OUD 11.1.2.3 with Weblogic and ODSM
In this blog, I would like to show how to change the Java version used by OUD and WebLogic, in case you use the ODSM. The OUD (Oracle Unified Directory) is written purely in Java, not like OID for example, and so, it relies heavily on your installed Java version. From a security point of view, updating the Java version is a task that you might do frequently and you want to do it as easy as possible. E.g. by just changing a symbolic link. In my case, I would like to update the Java version from 1.7.0_131 to 1.7.0_141. The first time you do the change, you have a few steps of manual steps to do, but the next Java updates will be much easier, by just changing a symbolic link.
So … the first question that might popp up is, where can I get Java 7 downloads? You can find them
in the Java SE 7 Archive Downloads, however, the Java 1.7 is freely available only until version 1.7.0_80, and for OUD you have to use Java 1.7 because this is the only supported version. Here is the link to the Java 1.7 archive downloads.
http://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase7-521261.html
Hint: In case you use SSLv3, the following announcement might be of interest for you. Starting with the January 20, 2015 Critical Patch Update releases JDK 7u75 and above, the Java Runtime Environment has SSLv3 disabled by default. For more information check out the following web page.
http://www.oracle.com/technetwork/java/javase/documentation/cve-2014-3566-2342133.html
OK. So, where do I get the latest Java version which is 1.7.0_141 at the moment? Since July 2015, the updates for Java 7 are no longer available to the public. Oracle offers updates to Java 7 only for customers who have purchased Java support or have Oracle products that require Java 7. That means, you have to go to MOS and search for the following note:
Information Center: Installation & Configuration for Oracle Java SE (Doc ID 1412103.2)
There you will find a link which points you to the JDK/JRE 7 Update 141 and “Patch 13079846: Oracle JDK 1.7.0″.
OK. Now that we go the latest 1.7 version, we can start with the upgrade procedure which consists of 8 steps.
1. Check the current version
2. Install the new Java version
3. Stop WebLogic and the OUD
4. Adjust the symlink
5. Update OUD configuration files (java.properties)
6. Update WebLogic configuration files
7. Start WebLogic and the OUD
8. Check the new version
The Java version can be checked via “java -version”
[dbafmw@dbidg01 ~]$ echo $JAVA_HOME /u01/app/oracle/product/Middleware/11.1.2.3/jdk1.7.0_131 [dbafmw@dbidg01 bin]$ /u01/app/oracle/product/Middleware/11.1.2.3/jdk1.7.0_131/bin/java -version java version "1.7.0_131" Java(TM) SE Runtime Environment (build 1.7.0_131-b12) Java HotSpot(TM) 64-Bit Server VM (build 24.131-b12, mixed mode)
To check the Java vendor and Java version used by WebLogic Server, I use usually the Admin Console. Just go to Environment > Servers > Your Server > Monitoring > General
That page should have all the information that you need, like on the following screen shot:
To check the Java version used by OUD, simply login to the ODSM and on the Home page you will see it immediately.
Or use the status script from the command line.
[dbafmw@dbidg01 bin]$ /u01/app/oracle/product/Middleware/11.1.2.3/asinst_1/OUD/bin/status -D "cn=Directory Manager" -j ~/.oudpwd | grep "Java Version" Java Version: 1.7.0_1312. Install the new Java version
[dbafmw@dbidg01 ~]$ cd /u01/stage/ [dbafmw@dbidg01 stage]$ unzip p13079846_17000_Linux-x86-64.zip ... ... [dbafmw@dbidg01 stage]$ mv jdk-7u141-linux-x64.tar.gz /u01/app/oracle/product/Middleware/11.1.2.3/ [dbafmw@dbidg01 stage]$ cd /u01/app/oracle/product/Middleware/11.1.2.3/ [dbafmw@dbidg01 11.1.2.3]$ tar -xzvf jdk-7u141-linux-x64.tar.gz ... ... [dbafmw@dbidg01 11.1.2.3]$ rm jdk-7u141-linux-x64.tar.gz [dbafmw@dbidg01 11.1.2.3]$ ls -l | grep jdk lrwxrwxrwx 1 dbafmw oinstall 12 Mar 27 10:36 jdk -> jdk1.7.0_131 drwxr-x--- 8 dbafmw oinstall 4096 Dec 12 23:34 jdk1.7.0_131 drwxr-xr-x 8 dbafmw oinstall 4096 Mar 14 06:10 jdk1.7.0_1413. Stop WebLogic and the OUD
Stop WebLogic
[dbafmw@dbidg01 bin]$ . /u01/app/oracle/product/Middleware/11.1.2.3/wlserver_10.3/server/bin/setWLSEnv.sh [dbafmw@dbidg01 bin]$ java weblogic.version -verbose [dbafmw@dbidg01 bin]$ cd /u01/app/oracle/product/Middleware/11.1.2.3/user_projects/domains/base_domain/bin [dbafmw@dbidg01 bin]$ ./stopWebLogic.sh ... ...
Stop OUD
[dbafmw@dbidg01 bin]$ /u01/app/oracle/product/Middleware/11.1.2.3/asinst_1/OUD/bin/stop-ds ... ...4. Adjust the symlink
[dbafmw@dbidg01 11.1.2.3]$ rm jdk [dbafmw@dbidg01 11.1.2.3]$ ln -s jdk1.7.0_141 jdk [dbafmw@dbidg01 11.1.2.3]$ ls -l | grep jdk lrwxrwxrwx 1 dbafmw oinstall 12 May 29 14:10 jdk -> jdk1.7.0_141 drwxr-x--- 8 dbafmw oinstall 4096 Dec 12 23:34 jdk1.7.0_131 drwxr-xr-x 8 dbafmw oinstall 4096 Mar 14 06:10 jdk1.7.0_1415. Update OUD configuration files (java.properties)
The update of the OUD java.properties file is usually done in the INSTALL_HOME and the INSTANCE_HOME, and the activated via the dsjavaproperties script from the appropriate location. The dsjavaproperties script is quite good documented by Oracle. See the following link for more information.
http://docs.oracle.com/cd/E52734_01/oud/OUDAG/appendix_cli.htm#OUDAG01148
Ok. Let’s do it once for your INSTALL_HOME
[dbafmw@dbidg01 11.1.2.3]$ vi $MW_HOME/Oracle_OUD1/config/java.properties ... default.java-home=/u01/app/oracle/product/Middleware/11.1.2.3/jdk/jre
Now you can apply the new java configuration with dsjavaproperties shell script.
[dbafmw@dbidg01 11.1.2.3]$ cd $MW_HOME/ [dbafmw@dbidg01 11.1.2.3]$ cd Oracle_OUD1/bin [dbafmw@dbidg01 bin]$ ./dsjavaproperties The operation was successful. The server commands will use the java arguments and java home specified in the properties file located in /u01/app/oracle/product/Middleware/11.1.2.3/asinst_1/OUD/config/java.properties
And now for your INSTANCE_HOME
[dbafmw@dbidg01 11.1.2.3]$ vi $MW_HOME/asinst_1/OUD/config/java.properties ... default.java-home=/u01/app/oracle/product/Middleware/11.1.2.3/jdk/jre
And apply the new java configuration with dsjavaproperties shell script here as well.
[dbafmw@dbidg01 11.1.2.3]$ cd $MW_HOME/ [dbafmw@dbidg01 11.1.2.3]$ cd asinst_1/OUD/bin/ [dbafmw@dbidg01 bin]$ ./dsjavaproperties The operation was successful. The server commands will use the java arguments and java home specified in the properties file located in /u01/app/oracle/product/Middleware/11.1.2.3/asinst_1/OUD/config/java.properties6. Update Weblogic configuration files
Adjust the JAVA_HOME in the following files and point it now to the symlink which we have previously created. So, the new Java home should be:
JAVA_HOME=/u01/app/oracle/product/Middleware/11.1.2.3/jdk
These are the files where you typically need to adjust it:
~/.bash_profile # of the Application owner of the WebLogic/OUD installation $MW_HOME/wlserver_10.3/common/bin/commEnv.sh $MW_HOME/wlserver_10.3/samples/domains/wl_server/bin/setDomainEnv.sh $MW_HOME/wlserver_10.3/samples/domains/medrec/bin/setDomainEnv.sh $MW_HOME/wlserver_10.3/samples/domains/medrec-spring/bin/setDomainEnv.sh $MW_HOME/wlserver_10.3/common/nodemanager/nodemanager.properties $MW_HOME/utils/bsu/bsu.sh $MW_HOME/utils/uninstall/uninstall.sh $MW_HOME/utils/quickstart/quickstart.sh $MW_HOME/user_projects/domains/base_domain/bin/setDomainEnv.sh
To double check it, you might want to run the following find command, to see if you have somewhere else still the old 131 JDK.
[dbafmw@dbidg01 11.1.2.3]$ cd $MW_HOME
[dbafmw@dbidg01 11.1.2.3]$ find . -type f -name "*.sh" -exec grep -il jdk1.7.0_131 {} \;
7. Start WebLogic and the OUD
Start WebLogic
[dbafmw@dbidg01 bin]$ . /u01/app/oracle/product/Middleware/11.1.2.3/wlserver_10.3/server/bin/setWLSEnv.sh [dbafmw@dbidg01 bin]$ java weblogic.version -verbose [dbafmw@dbidg01 bin]$ cd /u01/app/oracle/product/Middleware/11.1.2.3/user_projects/domains/base_domain/bin [dbafmw@dbidg01 bin]$ nohup ./startWebLogic.sh & ... ...
Start OUD
[dbafmw@dbidg01 bin]$ /u01/app/oracle/product/Middleware/11.1.2.3/asinst_1/OUD/bin/start-ds ... ...8. Check the new version
[dbafmw@dbidg01 bin]$ echo $JAVA_HOME /u01/app/oracle/product/Middleware/11.1.2.3/jdk [dbafmw@dbidg01 bin]$ /u01/app/oracle/product/Middleware/11.1.2.3/jdk/bin/java -version java version "1.7.0_141" Java(TM) SE Runtime Environment (build 1.7.0_141-b11) Java HotSpot(TM) 64-Bit Server VM (build 24.141-b11, mixed mode)
To check the Java vendor and Java version used by WebLogic Server, use again the WebLogic Admin Console like previously mentioned.
And login to the ODSM.
Or use the status script from the command line.
[dbafmw@dbidg01 bin]$ /u01/app/oracle/product/Middleware/11.1.2.3/asinst_1/OUD/bin/status -D "cn=Directory Manager" -j ~/.oudpwd | grep "Java Version" Java Version: 1.7.0_141Conclusion
A few manual steps are involved, when you want to change the Java version the first time, however, the next time you do it, you simply need to stop WebLogic and OUD, install the new JDK and change the symbolic link and start WebLogic and OUD again.
Cet article OUD – Oracle Unified Directory 11.1.2.3, How to change the Java version of OUD 11.1.2.3 with Weblogic and ODSM est apparu en premier sur Blog dbi services.
12cR2 needs to connect with password for Cross-PDB DML
In a previous post, I explained that Cross-PDB DML, executing an update/delete/insert with the CONTAINERS() clause, seems to be implemented with implicit database links. Connecting through a database link requires a password and this blog post is about an error you may encounter: ORA-01017: invalid username/password; logon denied
This blog post also explains a consequence of this implementation, the big inconsistency of CONTAINERS() function because the implementation is completely different for queries (select) and for insert/delete/update, and you may finally write and read from different schemas.
We do not need Application Container for Cross-PDB DML and we don’t even need metadata link tables. Just tables with same columns. Here I have a DEMO table which is just a copy of DUAL, and it is created in CDB$ROOT and in PDB1 (CON_ID=3), owned by SYS.
I’m connecting to CDB$ROOT with user, password and service name:
SQL> connect sys/oracle@//localhost/CDB1A as sysdba
Connected.
I insert a row into the DEMO table in the PDB1, which is CON_ID=3:
SQL> insert into containers(DEMO) (con_id,dummy) values (3,'Y');
1 row created.
This works in 12.2, is documented, and is an alternative way to switching to the container.
But now, let’s try to do the same when connecting with ‘/ as sysdba':
SQL> connect / as sysdba
Connected.
SQL> insert into containers(DEMO) (con_id,dummy) values (3,'Y');
insert into containers(DEMO) (con_id,dummy) values (3,'Y')
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from PDB1
The first message mentions invalid user/password, and the second one mentions a database link having the same name as the container.
As I described in the previous post the CONTAINERS() opens an implicit database link when doing some modifications to another container. But a database link requires a connection and no user/password has been provided. It seems that it tries to connect with the same user and password as the one provided to connect to the root.
Then, I provide the user/password but with local connection (no service name):
SQL> connect sys/oracle as sysdba
Connected.
SQL> insert into containers(DEMO) (con_id,dummy) values (3,'Y');
insert into containers(DEMO) (con_id,dummy) values (3,'Y')
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
There is no mention of a database link here, but still impossible to connect. Then it seems that the session needs our connection string to find out how to connect to the PDB.
Explicit database linkThere is an alternative. You can create the database link explicitly and then it will be used by the container(), having all information required password and service. But the risk is that you define this database link to connect to another user.
Here I have also a DEMO table created in SCOTT:
SQL> create database link PDB1 connect to scott identified by tiger using '//localhost/PDB1';
Database link created.
SQL> select * from DEMO@PDB1;
D
-
X
From the root I insert with CONTAINERS() without mentioning the schema:
SQL> insert into containers(DEMO) (con_id,dummy) values (3,'S');
1 row created.
I have no errors here (I’m still connected / as sysdba) because I have a database link with the same name as the one it tries to use implicitly. So it works without any error or warning. But my database link does not connect to the same schema (SYS) but to SCOTT. And because a DEMO table was there with same columns, the row was actually inserted into the SCOTT schema:
SQL> select * from DEMO@PDB1;
D
-
X
S
The big problem here is that when doing a select through the same CONTAINER() function, a different mechanism is used, not using the database link but session switching to the other container, in same schema, so the row inserted through INSERT INTO CONTAINER() is not displayed by SELECT FROM CONTAINER():
SQL> select * from containers(DEMO);
D CON_ID
- ----------
X 1
X 3
Y 3
I don’t know if the first problem (invalid user/password) will be qualified as a bug but I hope the second one will. Cross-PDB DML will be an important component of Application Containers, and having a completely different implementation for SELECT and for INSERT/UPDATE/DELETE may be a source of problems. In my opinion, both should use container switch within the same session, but that means that a transaction should be able to write in multiple containers, which is not possible currently.
Cet article 12cR2 needs to connect with password for Cross-PDB DML est apparu en premier sur Blog dbi services.
Documentum – Setup WildFly in HTTPS
In a previous blog (See this one), I mentioned some differences between JBoss 7.1.1 (coming with CS 7.2 / xPlore 1.5) and WildFly 9.0.1 (coming with CS 7.3 / xPlore 1.6). In this blog, I will talk about what is needed to setup a WildFly instance in HTTPS only or HTTPS+HTTP. This blog applies to both Content Servers and Full Text Servers. On the Full Text Server side, you might be aware of the ConfigSSL.groovy script which can be used to setup the Dsearch and IndexAgents in HTTPS (see this blog for more information). But if you are using a Multi-node Full Text setup (which most likely involves a lot of CPS (Content Processing Service)) or if you are just installing additional CPS (or Remote CPS), then what can you do?
The script ConfigSSL.groovy can only be used to configure a PrimaryDsearch or an IndexAgent in SSL, it’s not able to configure a CPS in SSL. Also, the setup of a CPS in SSL isn’t described anywhere in the official Documentation of EMC (/OpenText) so that’s the main purpose of this blog: what is needed to setup a WildFly instance in HTTPS so you can use that for your CPS as well as for your JMS (Java Method Server).
As a prerequisite, you need to have a Java Keystore. There are plenty of documentation around that so I won’t describe this part. In this blog, I will use a Full Text Server and I will configure a CPS in HTTPS. If you want to do that on the Content Server, just adapt the few paths accordingly. As a first thing to do, I will setup two environment variables which will contain the passwords for the Java cacerts and my Java Keystore (JKS):
[xplore@full_text_server_01 ~]$ cd /tmp/certs/ [xplore@full_text_server_01 certs]$ read -s -p " ----> Please enter the Java cacerts password: " jca_pw [xplore@full_text_server_01 certs]$ read -s -p " ----> Please enter the JKS password: " jks_pw
When you enter the first read command, the prompt isn’t returned. Just write/paste the jca password and press enter. Then the prompt will be returned and you can execute the second read command in the same way to write/paste the jks password. Now you can update the Java cacerts and import your JKS. The second and third commands below (the ones with the “-delete”) will remove any entries with the alias mentioned (dbi_root_ca and dbi_int_ca). If you aren’t sure about what you are doing, don’t execute these two commands. If the alias already exists in the Java cacerts, you will saw an error while executing the commands. In such cases, just use another alias (or remove the existing one using the “-delete” commands…):
[xplore@full_text_server_01 certs]$ cp $JAVA_HOME/jre/lib/security/cacerts $JAVA_HOME/jre/lib/security/cacerts_bck_$(date +%Y%m%d)
[xplore@full_text_server_01 certs]$ $JAVA_HOME/bin/keytool -delete -noprompt -alias dbi_root_ca -keystore $JAVA_HOME/jre/lib/security/cacerts -storepass ${jca_pw} > /dev/null 2>&1
[xplore@full_text_server_01 certs]$ $JAVA_HOME/bin/keytool -delete -noprompt -alias dbi_int_ca -keystore $JAVA_HOME/jre/lib/security/cacerts -storepass ${jca_pw} > /dev/null 2>&1
[xplore@full_text_server_01 certs]$ $JAVA_HOME/bin/keytool -import -trustcacerts -noprompt -alias dbi_root_ca -keystore $JAVA_HOME/jre/lib/security/cacerts -file "/tmp/certs/dbi_root_certificate.cer" -storepass ${jca_pw}
[xplore@full_text_server_01 certs]$ $JAVA_HOME/bin/keytool -import -trustcacerts -noprompt -alias dbi_int_ca -keystore $JAVA_HOME/jre/lib/security/cacerts -file "/tmp/certs/dbi_int_certificate.cer" -storepass ${jca_pw}
In the commands above, you will need to customize at least the “-file” parameter. I used above one Root CA and one Intermediate CA. Depending on your needs, you might need to add the same thing (if you are using a trust chain with 2 CAs) or just import the SSL Certificate directly, aso…
When this is done, you need to stop your application. In this case, I will stop my CPS (named “Node2_CPS1″ here). To stay more general, I will define a new variable “server_name”. You can use this variable to keep the same commands on the different servers, just use the appropriate value: “MethodServer” (for a JMS), “PrimaryDsearch” (or the name of your Dsearch), “CPS” (or the name of your CPS) or anything else:
[xplore@full_text_server_01 certs]$ export server_name="Node2_CPS1"
[xplore@full_text_server_01 certs]$ $JBOSS_HOME/server/stop${server_name}.sh
In the command above, $JBOSS_HOME is the location of the JBoss/WildFly instance. For an xPlore 1.6 for example, it will “$XPLORE_HOME/wildfly9.0.1″. For a Content Server 7.3, it will be “$DOCUMENTUM_SHARED/wildfly9.0.1″. I kept the name “JBOSS_HOME” because even if it has been renamed, I think it will take time before everybody use WildFly, just like there are still a lot of people using “docbase” while this has been replaced with “repository” with the CS 7.0…
Then let’s move the JKS file to the right location:
[xplore@full_text_server_01 certs]$ mv /tmp/certs/full_text_server_01.jks $JBOSS_HOME/server/DctmServer_${server_name}/configuration/my.keystore
[xplore@full_text_server_01 certs]$ chmod 600 $JBOSS_HOME/server/DctmServer_${server_name}/configuration/my.keystore
Now you can configure the standalone.xml file for this application to handle the HTTPS communications because by default only HTTP is enabled:
[xplore@full_text_server_01 certs]$ cd $JBOSS_HOME/server/DctmServer_${server_name}/configuration/
[xplore@full_text_server_01 configuration]$ cp standalone.xml standalone.xml_bck_$(date +%Y%m%d)
[xplore@full_text_server_01 configuration]$
[xplore@full_text_server_01 configuration]$ sed -i 's/inet-address value="${jboss.bind.address.management:[^}]*}/inet-address value="${jboss.bind.address.management:127.0.0.1}/' standalone.xml
[xplore@full_text_server_01 configuration]$ sed -i '/<security-realm name="sslSecurityRealm"/,/<\/security-realm>/d' standalone.xml
[xplore@full_text_server_01 configuration]$ sed -i '/<https-listener.*name="default-https"/d' standalone.xml
[xplore@full_text_server_01 configuration]$ sed -i '/<security-realms>/a \ <security-realm name="sslSecurityRealm">\n <server-identities>\n <ssl>\n <keystore path="'$JBOSS_HOME'/server/DctmServer_'${server_name}'/configuration/my.keystore" keystore-password="'${jks_pw}'"/>\n </ssl>\n </server-identities>\n </security-realm>' standalone.xml
So what are the commands above doing?
- Line 2: Backup of the standalone.xml file
- Line 4: Changing the default IP on which WildFly is listening for the Management Interface (by default 0.0.0.0 which means no restriction) to 127.0.0.1 so it’s only accessible locally
- Line 5: Removing any existing Security Realm named “sslSecurityRealm”, if any (there isn’t any unless you already created one with this specific name…)
- Line 6: Removing any HTTPS listener, if any (there isn’t any unless you already setup this application in HTTPS…)
- Line 7: Creating a new Security Realm named “sslSecurityRealm” containing all the needed properties: keystore path and password. You can also define additional parameters like alias, aso… $JBOSS_HOME, ${server_name} and ${jks_pw} are surrounded with single quote so they are evaluated before being put in the xml file.
Once this has been done, you defined your security realm but it’s not yet used… So here comes the choice to either completely deactivate HTTP and keep only HTTPS or use both at the same time. From my point of view, it’s always better to keep only HTTPS but in DEV environments for example, you might have requests from the developer to enable the HTTP because it’s simpler for them to develop something (web services for example) using HTTP and then secure it. So it’s up to you!
1. HTTP and HTTPS
To keep both enabled, you just have to create a new listener in the standalone.xml file which will be dedicated to HTTPS. This is the command that can do it:
[xplore@full_text_server_01 configuration]$ sed -i '/<http-listener .*name="default".*/a \ <https-listener name="default-https" socket-binding="https" security-realm="sslSecurityRealm" enabled-cipher-suites="PUT_HERE_SSL_CIPHERS"/>' standalone.xml
In the above command, just replace “PUT_HERE_SSL_CIPHERS” with the SSL Ciphers that you want your application to be using. Depending on how you configure the SSL in your environment, the list of SSL Ciphers needed will change so I will let you fill that blank. Of course if the name of your http-listener isn’t “default”, then you will need to adapt the command above but it is the default name for all WildFly instances so unless you customized this already, this will do.
2. HTTPS Only
If you don’t want to allow HTTP communications, then you just need to remove the HTTP listener and replace it with the HTTPS listener. This is the command that can do it:
[xplore@full_text_server_01 configuration]$ sed -i 's,<http-listener .*name="default".*,<https-listener name="default-https" socket-binding="https" security-realm="sslSecurityRealm" enabled-cipher-suites="PUT_HERE_SSL_CIPHERS"/>,' standalone.xml
No matter if you choose HTTP+HTTPS or HTTPS only, you then have to start your application again using your prefered way. This is an example of command that will do it:
[xplore@full_text_server_01 configuration]$ sh -c "cd $JBOSS_HOME/server/; nohup ./start${server_name}.sh & sleep 5; mv nohup.out nohup-${server_name}.out"
When the application has been started, you can try to access the application URL. These are some examples using the most common ports:
- JMS => https://content_server_01:9082/DmMethods/servlet/DoMethod
- PrimaryDsearch => https://full_text_server_01:9302/dsearch
- IndexAgent => https://full_text_server_01:9202/IndexAgent
- CPS => https://full_text_server_01:9402/cps/ContentProcessingService?wsdl
Cet article Documentum – Setup WildFly in HTTPS est apparu en premier sur Blog dbi services.
Documentum – WildFly not able to start in a CS 7.3 / xPlore 1.6
As mentioned in previous blogs (like this one), we were doing some testing on the newly released versions of Documentum (CS 7.3, xPlore 1.6, aso…). On this blog, I will talk about something that can prevent the WildFly instances to start properly (RedHat renamed the JBoss Application Server to WildFly in 2013 starting with the version 8.0). As this issue is linked to WildFly, you can understand that this might happen on both Content Servers 7.3 and Full Text Servers 1.6 but in this blog, I will use a Content Server for the example.
So when I first installed a Content Server 7.3 (in full silent of course!), the Java Method Server wasn’t running properly. Actually worse than that, it wasn’t even starting completely. Here are the entries put in the JMS log file (server.log):
JAVA_OPTS already set in environment; overriding default settings with values: -XX:+UseParallelOldGC -XX:ReservedCodeCacheSize=128m -XX:MaxMetaspaceSize=256m -XX:NewRatio=4 -Xms4096m -Xmx4096m -XX:NewSize=256m -XX:-UseAdaptiveSizePolicy -XX:SurvivorRatio=8 -Xss256k -Djava.awt.headless=true -Djava.io.tmpdir=$DOCUMENTUM/temp/JMS_Temp -Djboss.server.base.dir=$DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer
=========================================================================
JBoss Bootstrap Environment
JBOSS_HOME: $DOCUMENTUM_SHARED/wildfly9.0.1
JAVA: $DOCUMENTUM_SHARED/java64/JAVA_LINK/bin/java
JAVA_OPTS: -server -XX:+UseCompressedOops -server -XX:+UseCompressedOops -XX:+UseParallelOldGC -XX:ReservedCodeCacheSize=128m -XX:MaxMetaspaceSize=256m -XX:NewRatio=4 -Xms4096m -Xmx4096m -XX:NewSize=256m -XX:-UseAdaptiveSizePolicy -XX:SurvivorRatio=8 -Xss256k -Djava.awt.headless=true -Djava.io.tmpdir=$DOCUMENTUM/temp/JMS_Temp -Djboss.server.base.dir=$DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer
=========================================================================
15:48:41,985 INFO [org.jboss.modules] (main) JBoss Modules version 1.4.3.Final
15:48:44,301 INFO [org.jboss.msc] (main) JBoss MSC version 1.2.6.Final
15:48:44,502 INFO [org.jboss.as] (MSC service thread 1-8) WFLYSRV0049: WildFly Full 9.0.1.Final (WildFly Core 1.0.1.Final) starting
15:48:50,818 INFO [org.jboss.as.controller.management-deprecated] (ServerService Thread Pool -- 28) WFLYCTL0028: Attribute 'enabled' in the resource at address '/subsystem=datasources/data-source=ExampleDS' is deprecated, and may be removed in future version. See the attribute description in the output of the read-resource-description operation to learn more about the deprecation.
15:48:50,819 INFO [org.jboss.as.controller.management-deprecated] (ServerService Thread Pool -- 27) WFLYCTL0028: Attribute 'job-repository-type' in the resource at address '/subsystem=batch' is deprecated, and may be removed in future version. See the attribute description in the output of the read-resource-description operation to learn more about the deprecation.
15:48:50,867 WARN [org.jboss.messaging] (ServerService Thread Pool -- 30) WFLYMSG0071: There is no resource matching the expiry-address jms.queue.ExpiryQueue for the address-settings #, expired messages from destinations matching this address-setting will be lost!
15:48:50,868 WARN [org.jboss.messaging] (ServerService Thread Pool -- 30) WFLYMSG0072: There is no resource matching the dead-letter-address jms.queue.DLQ for the address-settings #, undelivered messages from destinations matching this address-setting will be lost!
15:48:51,024 INFO [org.jboss.as.server.deployment.scanner] (DeploymentScanner-threads - 1) WFLYDS0004: Found acs.ear in deployment directory. To trigger deployment create a file called acs.ear.dodeploy
15:48:51,026 INFO [org.jboss.as.server.deployment.scanner] (DeploymentScanner-threads - 1) WFLYDS0004: Found ServerApps.ear in deployment directory. To trigger deployment create a file called ServerApps.ear.dodeploy
15:48:51,109 INFO [org.xnio] (MSC service thread 1-5) XNIO version 3.3.1.Final
15:48:51,113 INFO [org.jboss.as.server] (Controller Boot Thread) WFLYSRV0039: Creating http management service using socket-binding (management-http)
15:48:51,128 INFO [org.xnio.nio] (MSC service thread 1-5) XNIO NIO Implementation Version 3.3.1.Final
15:48:51,164 INFO [org.jboss.remoting] (MSC service thread 1-5) JBoss Remoting version 4.0.9.Final
15:48:51,325 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 39) WFLYCLINF0001: Activating Infinispan subsystem.
15:48:51,335 INFO [org.wildfly.extension.io] (ServerService Thread Pool -- 38) WFLYIO001: Worker 'default' has auto-configured to 8 core threads with 64 task threads based on your 4 available processors
15:48:51,374 INFO [org.jboss.as.naming] (ServerService Thread Pool -- 47) WFLYNAM0001: Activating Naming Subsystem
15:48:51,353 INFO [org.jboss.as.security] (ServerService Thread Pool -- 54) WFLYSEC0002: Activating Security Subsystem
15:48:51,359 WARN [org.jboss.as.txn] (ServerService Thread Pool -- 55) WFLYTX0013: Node identifier property is set to the default value. Please make sure it is unique.
15:48:51,387 INFO [org.jboss.as.webservices] (ServerService Thread Pool -- 57) WFLYWS0002: Activating WebServices Extension
15:48:51,410 INFO [org.jboss.as.jsf] (ServerService Thread Pool -- 45) WFLYJSF0007: Activated the following JSF Implementations: [main]
15:48:51,429 INFO [org.jboss.as.security] (MSC service thread 1-3) WFLYSEC0001: Current PicketBox version=4.9.2.Final
15:48:51,499 INFO [org.jboss.as.connector] (MSC service thread 1-7) WFLYJCA0009: Starting JCA Subsystem (IronJacamar 1.2.4.Final)
15:48:51,541 INFO [org.jboss.as.naming] (MSC service thread 1-4) WFLYNAM0003: Starting Naming Service
15:48:51,554 INFO [org.jboss.as.mail.extension] (MSC service thread 1-7) WFLYMAIL0001: Bound mail session
15:48:51,563 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 56) WFLYUT0003: Undertow 1.2.9.Final starting
15:48:51,564 INFO [org.wildfly.extension.undertow] (MSC service thread 1-8) WFLYUT0003: Undertow 1.2.9.Final starting
15:48:51,583 INFO [org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 34) WFLYJCA0004: Deploying JDBC-compliant driver class org.h2.Driver (version 1.3)
15:48:51,593 INFO [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-4) WFLYJCA0018: Started Driver service with driver-name = h2
15:48:51,931 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 56) WFLYUT0014: Creating file handler for path $DOCUMENTUM_SHARED/wildfly9.0.1/welcome-content
15:48:51,984 INFO [org.wildfly.extension.undertow] (MSC service thread 1-8) WFLYUT0012: Started server default-server.
15:48:52,049 INFO [org.wildfly.extension.undertow] (MSC service thread 1-3) WFLYUT0018: Host default-host starting
15:48:52,112 ERROR [org.jboss.msc.service.fail] (MSC service thread 1-8) MSC000001: Failed to start service jboss.undertow.listener.default: org.jboss.msc.service.StartException in service jboss.undertow.listener.default: Could not start http listener
at org.wildfly.extension.undertow.ListenerService.start(ListenerService.java:150)
at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1948)
at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1881)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.net.SocketException: Protocol family unavailable
at sun.nio.ch.Net.bind0(Native Method)
at sun.nio.ch.Net.bind(Net.java:433)
at sun.nio.ch.Net.bind(Net.java:425)
at sun.nio.ch.ServerSocketChannelImpl.bind(ServerSocketChannelImpl.java:223)
at sun.nio.ch.ServerSocketAdaptor.bind(ServerSocketAdaptor.java:74)
at sun.nio.ch.ServerSocketAdaptor.bind(ServerSocketAdaptor.java:67)
at org.xnio.nio.NioXnioWorker.createTcpConnectionServer(NioXnioWorker.java:182)
at org.xnio.XnioWorker.createStreamConnectionServer(XnioWorker.java:243)
at org.wildfly.extension.undertow.HttpListenerService.startListening(HttpListenerService.java:115)
at org.wildfly.extension.undertow.ListenerService.start(ListenerService.java:147)
... 5 more
...
15:49:37,585 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("add") failed - address: ([
("core-service" => "management"),
("management-interface" => "native-interface")
]) - failure description: {"WFLYCTL0080: Failed services" => {"jboss.remoting.server.management" => "org.jboss.msc.service.StartException in service jboss.remoting.server.management: WFLYRMT0005: Failed to start service
Caused by: java.net.SocketException: Protocol family unavailable"}}
15:49:37,589 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("add") failed - address: ([
("core-service" => "management"),
("management-interface" => "http-interface")
]) - failure description: {
"WFLYCTL0080: Failed services" => {"jboss.serverManagement.controller.management.http" => "org.jboss.msc.service.StartException in service jboss.serverManagement.controller.management.http: WFLYSRV0083: Failed to start the http-interface service
Caused by: java.lang.RuntimeException: java.net.SocketException: Protocol family unavailable
Caused by: java.net.SocketException: Protocol family unavailable"},
"WFLYCTL0288: One or more services were unable to start due to one or more indirect dependencies not being available." => {
"Services that were unable to start:" => ["jboss.serverManagement.controller.management.http.shutdown"],
"Services that may be the cause:" => ["jboss.remoting.remotingConnectorInfoService.http-remoting-connector"]
}
}
...
15:49:37,653 INFO [org.jboss.as.server] (ServerService Thread Pool -- 35) WFLYSRV0010: Deployed "acs.ear" (runtime-name : "acs.ear")
15:49:37,654 INFO [org.jboss.as.server] (ServerService Thread Pool -- 35) WFLYSRV0010: Deployed "ServerApps.ear" (runtime-name : "ServerApps.ear")
...
15:49:37,901 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0063: Http management interface is not enabled
15:49:37,902 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0054: Admin console is not enabled
15:49:37,903 ERROR [org.jboss.as] (Controller Boot Thread) WFLYSRV0026: WildFly Full 9.0.1.Final (WildFly Core 1.0.1.Final) started (with errors) in 57426ms - Started 225 of 424 services (16 services failed or missing dependencies, 225 services are lazy, passive or on-demand)
...
15:49:38,609 INFO [org.jboss.as.server] (DeploymentScanner-threads - 2) WFLYSRV0009: Undeployed "acs.ear" (runtime-name: "acs.ear")
15:49:38,610 INFO [org.jboss.as.server] (DeploymentScanner-threads - 2) WFLYSRV0009: Undeployed "ServerApps.ear" (runtime-name: "ServerApps.ear")
...
15:53:02,276 INFO [org.jboss.as] (MSC service thread 1-4) WFLYSRV0050: WildFly Full 9.0.1.Final (WildFly Core 1.0.1.Final) stopped in 124ms
After all these errors, the logs are showing that the Http Management interface isn’t enabled and then the WildFly is just shutting itself down. So what is this “Protocol family unavailable”? This is actually linked to one of the changes included in WildFly compared to the old version (JBoss 7.1.1): WildFly supports and use by default IPv4 and IPv6. Unfortunately if your OS isn’t configured to use IPv6, this error will appear and will prevent your WildFly to start.
Fortunately, it is very easy to correct this, you just have to force WildFly to only use IPv4. For that purpose, we will add a single JVM parameter in the start script of the JMS:
sed -i 's,^JAVA_OPTS="[^"]*,& -Djava.net.preferIPv4Stack=true,' $DOCUMENTUM_SHARED/wildfly9.0.1/server/startMethodServer.sh
This command will simply add ” -Djava.net.preferIPv4Stack=true” just before the ending double quote of the line that is starting with JAVA_OPTS= in the file $DOCUMENTUM_SHARED/wildfly9.0.1/server/startMethodServer.sh. As a side note, to do the same thing on a Full Text Server, just change the path and the name of startup script and it will do the same thing (E.g.: $XPLORE_HOME/wildfly9.0.1/server/startPrimaryDsearch.sh).
After that, just restart the JMS and it will be able to start without issue:
JAVA_OPTS already set in environment; overriding default settings with values: -XX:+UseParallelOldGC -XX:ReservedCodeCacheSize=128m -XX:MaxMetaspaceSize=256m -XX:NewRatio=4 -Xms4096m -Xmx4096m -XX:NewSize=256m -XX:-UseAdaptiveSizePolicy -XX:SurvivorRatio=8 -Xss256k -Djava.awt.headless=true -Djava.io.tmpdir=$DOCUMENTUM/temp/JMS_Temp -Djboss.server.base.dir=$DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer -Djava.net.preferIPv4Stack=true ========================================================================= JBoss Bootstrap Environment JBOSS_HOME: $DOCUMENTUM_SHARED/wildfly9.0.1 JAVA: $DOCUMENTUM_SHARED/java64/JAVA_LINK/bin/java JAVA_OPTS: -server -XX:+UseCompressedOops -server -XX:+UseCompressedOops -XX:+UseParallelOldGC -XX:ReservedCodeCacheSize=128m -XX:MaxMetaspaceSize=256m -XX:NewRatio=4 -Xms4096m -Xmx4096m -XX:NewSize=256m -XX:-UseAdaptiveSizePolicy -XX:SurvivorRatio=8 -Xss256k -Djava.awt.headless=true -Djava.io.tmpdir=$DOCUMENTUM/temp/JMS_Temp -Djboss.server.base.dir=$DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer -Djava.net.preferIPv4Stack=true ========================================================================= 16:10:22,285 INFO [org.jboss.modules] (main) JBoss Modules version 1.4.3.Final 16:10:22,601 INFO [org.jboss.msc] (main) JBoss MSC version 1.2.6.Final 16:10:22,711 INFO [org.jboss.as] (MSC service thread 1-7) WFLYSRV0049: WildFly Full 9.0.1.Final (WildFly Core 1.0.1.Final) starting 16:10:24,627 INFO [org.jboss.as.controller.management-deprecated] (ServerService Thread Pool -- 28) WFLYCTL0028: Attribute 'job-repository-type' in the resource at address '/subsystem=batch' is deprecated, and may be removed in future version. See the attribute description in the output of the read-resource-description operation to learn more about the deprecation. 16:10:24,645 INFO [org.jboss.as.controller.management-deprecated] (ServerService Thread Pool -- 27) WFLYCTL0028: Attribute 'enabled' in the resource at address '/subsystem=datasources/data-source=ExampleDS' is deprecated, and may be removed in future version. See the attribute description in the output of the read-resource-description operation to learn more about the deprecation. 16:10:24,664 INFO [org.jboss.as.server.deployment.scanner] (DeploymentScanner-threads - 1) WFLYDS0004: Found acs.ear in deployment directory. To trigger deployment create a file called acs.ear.dodeploy 16:10:24,665 INFO [org.jboss.as.server.deployment.scanner] (DeploymentScanner-threads - 1) WFLYDS0004: Found ServerApps.ear in deployment directory. To trigger deployment create a file called ServerApps.ear.dodeploy 16:10:24,701 WARN [org.jboss.messaging] (ServerService Thread Pool -- 30) WFLYMSG0071: There is no resource matching the expiry-address jms.queue.ExpiryQueue for the address-settings #, expired messages from destinations matching this address-setting will be lost! 16:10:24,702 WARN [org.jboss.messaging] (ServerService Thread Pool -- 30) WFLYMSG0072: There is no resource matching the dead-letter-address jms.queue.DLQ for the address-settings #, undelivered messages from destinations matching this address-setting will be lost! 16:10:24,728 INFO [org.xnio] (MSC service thread 1-2) XNIO version 3.3.1.Final 16:10:24,737 INFO [org.jboss.as.server] (Controller Boot Thread) WFLYSRV0039: Creating http management service using socket-binding (management-http) 16:10:24,745 INFO [org.xnio.nio] (MSC service thread 1-2) XNIO NIO Implementation Version 3.3.1.Final 16:10:24,808 INFO [org.jboss.remoting] (MSC service thread 1-2) JBoss Remoting version 4.0.9.Final 16:10:24,831 INFO [org.wildfly.extension.io] (ServerService Thread Pool -- 38) WFLYIO001: Worker 'default' has auto-configured to 8 core threads with 64 task threads based on your 4 available processors 16:10:24,852 INFO [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 39) WFLYCLINF0001: Activating Infinispan subsystem. 16:10:24,867 INFO [org.jboss.as.jsf] (ServerService Thread Pool -- 45) WFLYJSF0007: Activated the following JSF Implementations: [main] 16:10:24,890 WARN [org.jboss.as.txn] (ServerService Thread Pool -- 55) WFLYTX0013: Node identifier property is set to the default value. Please make sure it is unique. 16:10:24,899 INFO [org.jboss.as.security] (ServerService Thread Pool -- 54) WFLYSEC0002: Activating Security Subsystem 16:10:24,901 INFO [org.jboss.as.naming] (ServerService Thread Pool -- 47) WFLYNAM0001: Activating Naming Subsystem 16:10:24,916 INFO [org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 34) WFLYJCA0004: Deploying JDBC-compliant driver class org.h2.Driver (version 1.3) 16:10:24,939 INFO [org.wildfly.extension.undertow] (MSC service thread 1-6) WFLYUT0003: Undertow 1.2.9.Final starting 16:10:24,939 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 56) WFLYUT0003: Undertow 1.2.9.Final starting 16:10:24,959 INFO [org.jboss.as.security] (MSC service thread 1-2) WFLYSEC0001: Current PicketBox version=4.9.2.Final 16:10:24,967 INFO [org.jboss.as.webservices] (ServerService Thread Pool -- 57) WFLYWS0002: Activating WebServices Extension 16:10:24,977 INFO [org.jboss.as.connector] (MSC service thread 1-4) WFLYJCA0009: Starting JCA Subsystem (IronJacamar 1.2.4.Final) 16:10:25,034 INFO [org.jboss.as.naming] (MSC service thread 1-7) WFLYNAM0003: Starting Naming Service 16:10:25,035 INFO [org.jboss.as.mail.extension] (MSC service thread 1-7) WFLYMAIL0001: Bound mail session 16:10:25,044 INFO [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-6) WFLYJCA0018: Started Driver service with driver-name = h2 16:10:25,394 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 56) WFLYUT0014: Creating file handler for path $DOCUMENTUM_SHARED/wildfly9.0.1/welcome-content 16:10:25,414 INFO [org.wildfly.extension.undertow] (MSC service thread 1-3) WFLYUT0012: Started server default-server. 16:10:25,508 INFO [org.wildfly.extension.undertow] (MSC service thread 1-6) WFLYUT0018: Host default-host starting 16:10:25,581 INFO [org.wildfly.extension.undertow] (MSC service thread 1-3) WFLYUT0006: Undertow HTTP listener default listening on /0.0.0.0:9080 16:10:25,933 INFO [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-6) WFLYJCA0001: Bound data source 16:10:26,177 INFO [org.jboss.as.server.deployment.scanner] (MSC service thread 1-6) WFLYDS0013: Started FileSystemDeploymentService for directory $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer/deployments 16:10:26,198 INFO [org.jboss.as.server.deployment] (MSC service thread 1-7) WFLYSRV0027: Starting deployment of "ServerApps.ear" (runtime-name: "ServerApps.ear") 16:10:26,201 INFO [org.jboss.as.server.deployment] (MSC service thread 1-4) WFLYSRV0027: Starting deployment of "acs.ear" (runtime-name: "acs.ear") 16:10:26,223 INFO [org.jboss.as.remoting] (MSC service thread 1-5) WFLYRMT0001: Listening on 0.0.0.0:9084 ... 16:10:42,398 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 63) WFLYUT0021: Registered web context: /DmMethods 16:10:44,519 INFO [org.wildfly.extension.undertow] (ServerService Thread Pool -- 64) WFLYUT0021: Registered web context: /ACS 16:10:44,563 INFO [org.jboss.as.server] (ServerService Thread Pool -- 35) WFLYSRV0010: Deployed "acs.ear" (runtime-name : "acs.ear") 16:10:44,564 INFO [org.jboss.as.server] (ServerService Thread Pool -- 35) WFLYSRV0010: Deployed "ServerApps.ear" (runtime-name : "ServerApps.ear") 16:10:44,911 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0060: Http management interface listening on http://0.0.0.0:9085/management 16:10:44,912 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0051: Admin console listening on http://0.0.0.0:9085 16:10:44,912 INFO [org.jboss.as] (Controller Boot Thread) WFLYSRV0025: WildFly Full 9.0.1.Final (WildFly Core 1.0.1.Final) started in 23083ms - Started 686 of 905 services (298 services are lazy, passive or on-demand)
As shown above, the WildFly has been started successfully. In this example, I really took a default JMS setup with just a few custom JVM parameters (Xms, Xmx, aso…) but as you can see in the logs above, there are a few issues with the default setup like deprecated or missing parameters. So I would strongly suggest you to take a look at that and configure properly your JMS once it is up & running!
Earlier in this blog, I mentioned that there are a few things that changed between JBoss 7.1.1 (CS 7.2 / xPlore 1.5) and WildFly 9.0.1 (CS 7.3 / xPlore 1.6). In a future blog, I will talk about another change which is how to setup a WildFly instance in SSL.
Cet article Documentum – WildFly not able to start in a CS 7.3 / xPlore 1.6 est apparu en premier sur Blog dbi services.
Documentum – Not able to install IndexAgent with xPlore 1.6
In the last few months, we already did some tests with the Content Server 7.3, D2 4.7 and xPlore 1.6. As often, we found some funny/interesting behaviors and we faced some issues. In this blog I will talk about one issue I faced when I tried to install a new IndexAgent with xPlore 1.6. The Content Server used was a CS 7.3 (no patch available when I did this installation). This issue was present in both Silent and non-Silent installations.
So what was the issue? While trying to install a first IndexAgent, it looked like the installer wasn’t able to contact the Content Server. The GUI installer was stuck on the “Connection Broker Information”. After filling the information and clicking on “Next”, the Full Text Installer was stuck and nothing happened anymore.
Basically on this step of the installer, it is trying to connect to the docbroker using the information provided (docbroker hostname and port). Of course the docbroker and the docbase were running and responding properly.
To verify what was happening, I just checked the xPlore processes:
[xplore@full_text_server_01 ~]$ ps -ef | grep xplore xplore 2423 1 0 08:04 ? 00:00:00 /bin/sh ./startPrimaryDsearch.sh xplore 2425 2423 0 08:04 ? 00:00:00 /bin/sh $XPLORE_HOME/wildfly9.0.1/bin/standalone.sh xplore 2572 2425 5 08:04 ? 00:03:01 $XPLORE_HOME/java64/1.8.0_77/bin/java -D[Standalone] -server -XX:+UseCompressedOops -server -XX:+UseCompressedOops -XX:+UseCodeCacheFlushing -XX:ReservedCodeCacheSize=196m -Xms4096m -Xmx4096m -XX:MaxMetaspaceSize=512m -Xss1024k -XX:+UseCompressedOops -XX:+DisableExplicitGC -XX:-ReduceInitialCardMarks -Djava.awt.headless=true -Djboss.server.base.dir=$XPLORE_HOME/wildfly9.0.1/server/DctmServer_PrimaryDsearch -Djava.net.preferIPv4Stack=true -Dorg.jboss.boot.log.file=$XPLORE_HOME/wildfly9.0.1/server/DctmServer_PrimaryDsearch/log/server.log -Dlogging.configuration=file:$XPLORE_HOME/wildfly9.0.1/server/DctmServer_PrimaryDsearch/configuration/logging.properties -jar $XPLORE_HOME/wildfly9.0.1/jboss-modules.jar -mp $XPLORE_HOME/wildfly9.0.1/modules org.jboss.as.standalone -Djboss.home.dir=$XPLORE_HOME/wildfly9.0.1 -Djboss.server.base.dir=$XPLORE_HOME/wildfly9.0.1/server/DctmServer_PrimaryDsearch xplore 3977 2572 0 08:06 ? 00:00:00 $XPLORE_HOME/dsearch/cps/cps_daemon/bin/CPSDaemon $XPLORE_HOME/dsearch/cps/cps_daemon/PrimaryDsearch_local_configuration.xml Daemon0 9322 NORMAL xplore 4069 2572 0 08:06 ? 00:00:00 $XPLORE_HOME/dsearch/cps/cps_daemon/bin/CPSDaemon $XPLORE_HOME/dsearch/cps/cps_daemon/PrimaryDsearch_local_configuration.xml QDaemon0 9323 QUERY xplore 9591 9568 0 08:45 pts/3 00:00:00 /bin/sh ./configIndexagent.sh xplore 9592 9591 0 08:45 pts/3 00:00:08 $XPLORE_HOME/java64/1.8.0_77/jre/bin/java -Xmx262114000 -Xms262114000 com.zerog.lax.LAX /tmp/install.dir.9592/temp.lax /tmp/env.properties.9592 "-f" "config.properties" xplore 11099 9592 0 08:51 pts/3 00:00:02 $XPLORE_HOME/java64/1.8.0_77/jre/bin/java -cp /tmp/342186.tmp/testDocbrokerUtil.jar:$XPLORE_HOME/dfc/dfc.jar -Ddfc.properties.file=/tmp/install.dir.9592/dfc.properties com.documentum.install.shared.util.TestDocbrokerUtil content_server_01 1489 xplore 11928 9515 0 08:59 pts/1 00:00:00 ps -ef xplore 11929 9515 0 08:59 pts/1 00:00:00 grep xplore
As you can see above, the PrimaryDsearch is up & running so that’s good but there are two additional interesting processes:
xplore 9592 9591 0 08:45 pts/3 00:00:08 $XPLORE_HOME/java64/1.8.0_77/jre/bin/java -Xmx262114000 -Xms262114000 com.zerog.lax.LAX /tmp/install.dir.9592/temp.lax /tmp/env.properties.9592 "-f" "config.properties" xplore 11099 9592 0 08:51 pts/3 00:00:02 $XPLORE_HOME/java64/1.8.0_77/jre/bin/java -cp /tmp/342186.tmp/testDocbrokerUtil.jar:$XPLORE_HOME/dfc/dfc.jar -Ddfc.properties.file=/tmp/install.dir.9592/dfc.properties com.documentum.install.shared.util.TestDocbrokerUtil content_server_01 1489
The most interesting one is the second line: this is the command that the installer is starting to check that the docbroker is responding properly and this process is stuck. To do some testing, I just put the utilities and the needed elements to a test folder so I would be able to execute this command myself whenever I wanted. Then I killed the installer since it was stuck and I tried myself to contact the docbroker:
[xplore@full_text_server_01 ~]$ time $XPLORE_HOME/java64/1.8.0_77/jre/bin/java -cp /tmp/my.tmp/testDocbrokerUtil.jar:$XPLORE_HOME/dfc/dfc.jar -Ddfc.properties.file=/tmp/my.tmp/dfc.properties com.documentum.install.shared.util.TestDocbrokerUtil content_server_01 1489 ^C real 7m43.669s user 0m1.618s sys 0m0.153s
As you can see above, after 7min and 43 seconds, I just killed the process (^C) because it still didn’t return anything. Just to be sure, I checked our monitoring tool and it was able to connect to the docbase, remotely, without issue so the issue here was link somehow to the Full Text Server.
To go deeper, the only thing I could do was to perform some thread dumps of the java process to try to see where the bottleneck was. After doing that, it actually became quite clear that the server had an issue with the entropy generation because the threads were holding and waiting for the rsa libraries to get a random seed from the system. If you are running Documentum (or any other thing) on a Virtual Machine you will most probably want to change the default random generation because the entropy isn’t sufficient usually on VMs. We are always doing that for all our customer’s environments but since this was just a sandbox server to play with the newly released xPlore 1.6, this wasn’t setup at that time. With xPlore 1.5, this wouldn’t have caused any issue but it looks like xPlore 1.6 is much more demanding on the random seed generations. This has most probably something to do with some security improvements done in xPlore 1.6.
So to solve this issue, you just need to increase the entropy of your server. This can be done as follow (as root):
- Install the rng tools package
- Update the options in the /etc/sysconfig/rngd file
- Setup the service to start automatically at OS boot
You can basically use the following commands for that (I’m using a RHEL so it’s yum based):
[root@full_text_server_01 ~]# yum -y install rng-tools.x86_64 Loaded plugins: product-id, search-disabled-repos, security, subscription-manager Setting up Install Process Resolving Dependencies --> Running transaction check ... Transaction Test Succeeded Running Transaction Installing : rng-tools-5-2.el6_7.x86_64 1/1 Verifying : rng-tools-5-2.el6_7.x86_64 1/1 Installed: rng-tools.x86_64 0:5-2.el6_7 Complete! [root@full_text_server_01 ~]# rpm -qf /etc/sysconfig/rngd rng-tools-5-2.el6_7.x86_64 [root@full_text_server_01 ~]# [root@full_text_server_01 ~]# sed -i 's,EXTRAOPTIONS=.*,EXTRAOPTIONS=\"-r /dev/urandom -o /dev/random -t 0.1\",' /etc/sysconfig/rngd [root@full_text_server_01 ~]# cat /etc/sysconfig/rngd # Add extra options here EXTRAOPTIONS="-r /dev/urandom -o /dev/random -t 0.1" [root@full_text_server_01 ~]# [root@full_text_server_01 ~]# chkconfig --level 345 rngd on [root@full_text_server_01 ~]# chkconfig --list | grep rngd rngd 0:off 1:off 2:off 3:on 4:on 5:on 6:off [root@full_text_server_01 ~]# [root@full_text_server_01 ~]# service rngd start Starting rngd: [ OK ] [root@full_text_server_01 ~]#
When this is done, you need to force xPlore to use this new random generation mechanism. For that, just put in the profile of the xplore user one environment variable (as xplore):
[xplore@full_text_server_01 ~]$ echo 'export DEVRANDOM=/dev/urandom' >> ~/.bash_profile
When this has been done, you can just re-execute the java command and in something like 1 second the command should return a result like that:
[xplore@full_text_server_01 ~]$ $XPLORE_HOME/java64/1.8.0_77/jre/bin/java -cp /tmp/my.tmp/testDocbrokerUtil.jar:$XPLORE_HOME/dfc/dfc.jar -Ddfc.properties.file=/tmp/my.tmp/dfc.properties com.documentum.install.shared.util.TestDocbrokerUtil content_server_01 1489 0 [main] ERROR com.documentum.fc.common.impl.logging.LoggingConfigurator - Problem locating log4j configuration 1 [main] WARN com.documentum.fc.common.impl.logging.LoggingConfigurator - Using default log4j configuration [xplore@full_text_server_01 ~]$
These two messages aren’t important, they are just printed because there is no log4j properties file in the command line so it is using the default one. The only important thing here is that the command returns something so it is working! To confirm that, you can just run the installer once more and this time the screen shouldn’t be stuck on the Connection Broker Information.
Cet article Documentum – Not able to install IndexAgent with xPlore 1.6 est apparu en premier sur Blog dbi services.
12cR2 Cross-container DML – insert into container()
Multitenant has been introduced in 12.1.0.1 with the goal to share resources but isolate data. However, having all PDBs in the same root may be convenient to manipulate data in multiple PDBs. In the first patchset, 12.1.0.2, a way to query cross-container has been introduced for the CDB administrator to see data in other containers. In the second release, 12.2.0.1, this goes further with the introduction of Application Containers and cross-PDB DML. Currently, not all possibilities are documented and not all documented features are actually working. This will probably improve in next patchset. I’ll start here with something simple: insert from root into a table which is in a PDB.
Here is my CDB with two PDBs
22:48:13 SQL> connect sys/oracle@//localhost/CDB1A as sysdba
Connected.
22:48:13 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------ -------- ---- ---- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
I create a DEMO table in CDB$ROOT and do the same in PDB1 and PDB2
22:48:13 SQL> create table DEMO (n number primary key, text varchar2(90) );
Table DEMO created.
22:48:14 SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
22:48:14 SQL> create table DEMO (n number primary key, text varchar2(90) );
Table DEMO created.
22:48:14 SQL> connect sys/oracle@//localhost/PDB2 as sysdba
Connected.
22:48:14 SQL> create table DEMO (n number primary key, text varchar2(90) );
Table DEMO created.
I connect to CDB$ROOT and set a transaction name, then check all transactions
22:48:14 SQL> connect sys/oracle@//localhost/CDB1A as sysdba
Connected.
22:48:14 SQL> set transaction name 'Franck';
Transaction NAME succeeded.
22:48:14 SQL> select con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);
no rows selected
I’m alone here with no transactions.
CONTAINERS()Here is the cross-container syntax: using the CONTAINERS() and specifying the CON_ID column and value (CON_ID=3 for PDB1)
22:48:14 SQL> insert into containers(DEMO) (con_id,n,text) values (3,1,'Cross-container insert');
1 row inserted.
22:48:14 SQL> select con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);
CON_ID ADDR XIDUSN UBAFIL SES_ADDR PTX_XID NAME USED_UREC
------ ---- ------ ------ -------- ------- ---- ---------
1 0000000067BB19E8 7 0 000000006ADD2EA8 0000000000000000 Franck 1
3 000000006642AEB8 1 62 000000006AC99610 0000000000000000 2
The interesting thing is that I have two transactions: one on my current container, and one on the container CON_ID=3 specified in my insert.
I’m doing the same for PDB2 which is CON_ID=4
22:48:14 SQL> insert into containers(DEMO) (con_id,n,text) values (4,1,'Cross-container insert');
1 row inserted.
22:48:15 SQL> select addr,con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);
ADDR CON_ID ADDR XIDUSN UBAFIL SES_ADDR PTX_XID NAME USED_UREC
---- ------ ---- ------ ------ -------- ------- ---- ---------
0000000067BB19E8 1 0000000067BB19E8 7 0 000000006ADD2EA8 0000000000000000 Franck 1
000000006642AEB8 3 000000006642AEB8 1 62 000000006AC99610 0000000000000000 2
000000006644EA90 4 000000006644EA90 6 66 000000006B20F828 0000000000000000 2
looking at the transactions sessions, the ones on the PDBs looks like a database link connection:
22:48:15 SQL> select taddr,con_id,program,action,module from v$session where saddr in (select ses_addr from v$transaction);
TADDR CON_ID PROGRAM ACTION MODULE
----- ------ ------- ------ ------
000000006644EA90 4 oracle@VM104 (TNS V1-V3) oracle@VM104 (TNS V1-V3)
000000006642AEB8 3 oracle@VM104 (TNS V1-V3) oracle@VM104 (TNS V1-V3)
0000000067BB19E8 1 java@VM104 (TNS V1-V3) java@VM104 (TNS V1-V3)
It looks as database links, and we can actually see those open links in V$DBLINKS:
23:06:53 SQL> select * from v$dblink;
DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH CON_ID
------- -------- --------- ------------- -------- ------------ -------------- ----------- --------------------- ------
PDB1 0 YES YES UNKN 0 YES YES 1 1
PDB2 0 YES YES UNKN 0 YES YES 1 1
However, when using CONTAINERS() the session is not using the database links but something like parallel query switching to the containers. This means that it is not the same transaction and we don’t see the modifications:
22:48:15 SQL> select * from containers(DEMO);
no rows selected
Now, I commit:
22:48:15 SQL> commit;
Commit complete.
and all transactions are ended:
22:48:15 SQL> select taddr,con_id,program,action,module from v$session where saddr in (select ses_addr from v$transaction);
no rows selected
the links are still opened but not in a transaction anymore:
23:10:21 SQL> select * from v$dblink;
DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_STRENGTH CON_ID
------- -------- --------- ------------- -------- ------------ -------------- ----------- --------------------- ------
PDB1 0 YES YES UNKN 0 NO NO 1 1
PDB2 0 YES YES UNKN 0 NO NO 1 1
My inserts are now visible, either from the root with CONTAINER()
22:48:15 SQL> select * from containers(DEMO);
N TEXT CON_ID
- ---- ------
1 Cross-container insert 4
1 Cross-container insert 3
or from each PDB:
22:48:15 SQL> alter session set container=PDB1;
Session altered.
22:48:15 SQL> select * from DEMO;
N TEXT
- ----
1 Cross-container insert
This is a convenient way for the CDB administrator, or for the Application Root administrator, to do some DML on different containers, without having to create a database link. Of course, the common user can also switch to a PDB using the ‘alter session set container’ but this one does not allow to have a transaction that spans multiple containers. You can think of it as a shortcut to avoid creating database links from the root to its containers.
Cet article 12cR2 Cross-container DML – insert into container() est apparu en premier sur Blog dbi services.
Personal road trip – PowerShell Conference EU 2017
Started on April, 5th at 9:00 AM, I was immersed in a wonderful and amazing Open Ceremony among light effects and an epic music. Indeed, using PowerShell is always an epic moment…
Then, lights turned on and all the speakers stood up and come behind the scene. As you may see, they came from all around the worlds to bring us the best of PowerShell!
This Open Ceremony was followed by the Keynote “State of the Union” presented by Jeffrey SNOVER, also know as the “father” of Windows PowerShell. He really congratulated the PowerShell Team for their great work which now make us possible to use our favorite Shell on Linux environments. Moreover, he described this Transformational Change as a logical evolution of the tool.
Before the lunch, Will SCHROEDER presented how in the last years, Red Team and Blue Team played cat and mouse regarding the security. As a conclusion for this session, we can admit notable improvement in security was performed over the PowerShell versions.
If you’ve got a question, it was the place to go! 
In the afternoon, you had the choice between four different streams. I decided to learn more about PowerShell on Linux and to attend to “Hell freezing over: PowerShell on Linux and GitHub” presented by Bartosz Bielawski.
If you wanted to obtain a good overview of what are Windows Server Containers and how convenient it is, the session “Getting started with Windows Server container” presented by Flynn Bundy was great.
Then, the session “Look! Up in the sky! It’s a bird. It’s a plane. It’s Nanoman!” presented by Aleksandar Nikolic and Jan Egil Ring gave you key clues to connect and administrate a Windows Server Nano with PowerShell.
And finally, I discovered an interesting PowerShell module called Pester which automate code verification. So it can be used as a MSSQL instance policy check but with PowerShell. This session called “Green is bad Red is Good – Turning your Checklists into Pester Tests” was presented by Rob Sewell.
The second day, Bartosz Bielawski presented an other session about PowerShell on Linux called “Knock, knock, knock – Linux at your door”.
An interested session presented by Jason Yoder called “FASTER!!!! Make Your Code Run Faster!”, gave some key clues and approaches to optimize your PowerShell Code.
In the afternoon, Rob Sewell rocked again with “Using the SQL Server Provider”
Then I followed Mathias R Jessen’s session named “Regex 2.0: Full Coverage”.
During the last day, I wanted to be initiated to Visual Studio Code. So I went to David Wilson’s session “Advanced PowerShell Module Development with Visual Studio Code”.
However my favorite session was “Modern Database Administration using PowerShell and dbatools” presented by Chrissy LeMaire and Rob Sewell.
You can find dba tools on the official website. I highly recommend to take a look, it’s worth it!
I will finish my Road Trip with Luc Dekens’ session called “Visualising performance in VMware vSphere”.
Many interesting and pertinent sessions happened at this PowerShell Conference Europe 2017. If you want to learn more about this event, let’s take a look to the official website. And the slides and demos are on GitHub.
Cet article Personal road trip – PowerShell Conference EU 2017 est apparu en premier sur Blog dbi services.
APEX Connect 2017 – Day 3
For the last conference day, after the Keynote about “JavaScript: The next Language YOU should learn” by Dan McGhan , I decided to attend presentations on following topics:
– Upgrade your APEX app with zero downtime by using EBR
– Understand and make use of CSS(3)
– Best Practices for APEX Administrators
– APEX Version Control and Team Working
– Database Cloud Services with APEX
– Date, Time, Calendar and Co with APEX
I also got the chance to have a 1:1 talk with Marc Sewtz to expose some wishes about APEX and talk about some customer issue.
JavaScript programming language
JavaScript allows asynchronous programming: main thread can make calls to asynchronous API which return there result via an event/callback queue.
When the web browser parses the page to be rendered the DOM tree is built. That build process is stopped whenever JavaScript is found, until the JavaScript execution is completed. That means the JavaScript can only address the the element that where already put into the DOM tree, not the full page. That explains why people would want to put the JavaScripts at the bottom of the page definition.
Fortunately, there is the JQuery DOM manipulation library which allows developer to abstract from that constraint.
What you also need to keep about JavaScript is:
– functions are the first class
– functions provide scope and closure
In APEX you can make use of JavaScript for AJAX calls by using apex.server.process.
JavaScript is definitely a language that APEX developer should master beside PL/SQL to embrace customer requirements.
Upgrade your APEX app with zero downtime by using EBR:
EBR (Edition Based Redefinition) is a tool in the Oracle database to support online application upgrades.
You can find details about EBR in following Oracle documentation: Technet Edition Based Redefinition
EBR allows you to have your DB looking like 2 DBs, each identified in the normal way by a service (Just like 2 CDBs in the same CDB).
Hot rollover will be enabled by using a traffic director (load balancer).
This introduces end user session to application version affinity.
In APEX you need to create a copy of your application with the changes for the new version. The application version switch is managed with APEX_UTIL.SET_EDITION setting the value of the current application in the APPLICATION_PREFERENCES table.
Understand and make use of CSS(3):
CSS defines the layout design. The basic structure is: selector { attribute : value }
There are numerous selectors which can be combined. Also pseudo classes and elements can be used as reference.
You need to be careful about the cascading order, some strong rules are set to define them. The “!important” tag which overrules should be used as less as possible.
The definition of objects to be rendered is based on the Box Model (Margin – Border – Padding – Content).
There are 2 levels for the definitions:
– Block
– Inline (mainly for the text)
Positions also have multiple definition references:
– static
– relative
– fixed
– absolute
Media queries will allow the definitions for responsive design.
You can verify if some elements your want to use are supported by your web browser by checking the following web site:
http://www.caniuse.com/
I can also recommend you to visit W3C site: https://www.w3schools.com/css/
Best Practices for APEX Administrators:
Following best practice rules were presented based on presenter’s experience:
– Create a dedicated folder to store the APEX installation package (e.g. under the Oracle “product” folder)
– Create a dedicated tablespace for APEX
– Put every worspace on it’s own schema with dedicated tablespace
– Build your own workspace delivery system
– Restrict access workspace administration
– Use a version dedicated folder instead of /i/
– Rename ords.war based on application using it
– Setup automated export and backup of the workspaces and applications
– When patching also keep a copy of the full install package
– Manage downtimes during update operations (only required while sql is running)
– Set ACLs to give only required acces
APEX Version Control and Team Working:
There are different ways to manage team work and version control for APEX development, but none are identical to what is done for other programming languages due to the way APEX works.
What is common in the exposed ways is that the development cycle is about 3 weeks and there is always a Development, an integration, a test and a production environment. Code related to the database (DML, packages, …) is stored and managed with a version control system like GitHub and APEX application exported and pushed to the version control system on a daily basis.
Some people use CI (Continuous Integration) engine to generate a VM with the full test environment from the committed development work.
To manage the deployment of selective features developed you need to use conditional build.
There are different way to export/import APEX applications:
– ApexExport java class
– “apex export” and “apximp” in SQLcl
– “Application Archive” packaged application
– manual export/import
Oracle provides some white paper describing best practices to manage APEX development process:
http://www.oracle.com/technetwork/developer-tools/apex/learnmore/apex-life-cycle-management-wp-3030229.pdf
Database Cloud Services with APEX:
The requirements were about the setup of a private cloud to host about 200TB of data over 300000 schemas.
In order to be able to properly manage the infrastructure following tolls have been created in APEX:
– DB service portal (request management, password reset, reporting, cost tracking, approvals,…)
– DB metadata manager (reporting, interfacing, measurement of space, cpu, aso)
– DB service automation (order management, log management, messaging, maintenance management)
This allowed to raise customer satisfaction, enhance DBA efficiency and metadata maintenance.
Date, Time, Calendar and Co with APEX:
Dates can be represented in different ways in the Oracle DB:
– DATE
– TIMESTAMP
– TIMESTAMP WITH TIME ZONE
– TIMESTAMP WITH LOCAL TIME ZONE
If you dump those data types you will see the details of there implementation and how the Oracle DB stores them.
If you subtract a number to a DATE it will return a decimal number and if you add a number to a DATE you get a DATE.
There are function to manipulate dates like ADD_MONTHS (for DATE) and INTERVAL (for TIMESTAMP).
The EXTRACT function allows to get specific elements of a timestamp.
APEX items are always of VARCHAR2 type, so any date manipulation with implicitly use TO_CHAR and TO_DATE conversion.
The date format is to be set in the APEX application globalization parameters. Most of those parameters map to the DB NLS values by default.
In APEX 5.1 there were some additions to the Calendar like:
– ability to define Dynamic Actions
– JavaScript initialization on parameters
– …
I would recommend to play with the Calendar Sample application to see all new capabilities.
Enjoy APEX!
Cet article APEX Connect 2017 – Day 3 est apparu en premier sur Blog dbi services.
SQL Server 2017: Python server
After my article SQL Server 2017: first steps with Python Service, I write this article about my first test with the Python Server feature in SQL Server 2017.
The installation is very simple, in the features Selection window, you have in the Shared Features, you have a Machine Learning Server (Standalone) with R and Python.
I check Python to install the Python Server and here we go.
Like for the Python services, you need to accept “Consent to install Python”
Don’t forget to give access to Internet to your server. If not, the installation failed and you have this message:
With Internet, the installation is successful.
At the end of the installation, I open the summary file to analyze what is installed.
I find feature parameters for the script installation for the Python Server:
- SQL_SHARED_AA for the Machine Learning Server (Standalone) – AA is for ADVANCED ANALYTICS
- SQL_SHARED_MPY for Python
At this point, I have a Python Server but, now, what is a Python Server?
On my server, I open the search and tape Python as keyword…
No result found… Ok. Old method, go to the folder and search manually.
I go directly in the folder “C:\Program Files\Microsoft SQL Server\140” and I see a folder “PYTHON_SERVER”:
I open the folder “PYTHON_SERVER” and see three executables:
- Uninstall-Anaconda: to uninstall the python server
- Python: Command Console to execute Python code
- Pythonw: Command Console to execute Python code without Window
To Test the Python server, I run the executable python (to have a cmd window) and use the same example as my article for the Python service.
The Python query is:
Import pip Installed_packages = pip.get_installed_distributions() Installed_packages_list = sorted([“%s %s” % (i.key, i.version) for I in Installed_packages]) Print(Installed_packages_list) Len(Installed_packages_list)
As you can see in the picture, the same 125 packages as for the Python Service are installed on the Python Server.
Let yourself be tempted
Cet article SQL Server 2017: Python server est apparu en premier sur Blog dbi services.
APEX Connect 2017 – Day 2
After the Keynote about “Oracle Application Express – Ahead of it’s time. Leading the way forward” by Joel Kallmann (Director of Software Development for APEX) and some overall presentation about Development with APEX 5.1, I decided to attend presentations on following topics oriented towards tools to make developer’s life easier:
– APEX OOS Tools and helper
– Useful APEX plugins
– LESS is more
– JavaScript Debugging and Tuning
– Introduction to Oracle JET
Oracle Application Express – Ahead of it’s time. Leading the way forward:
In 2008 APEX was already matching the definition of cloud given by the NIST (National Institute of Standards and Technology) and even matching the definition of PaaS in 2002: APEX was ahead of it’s time!
APEX allows to increase productivity and consistency, reducing complexity due to it’s declarative framework setup. It’s therefore to be seen as lowcode development environment: https://apex.oracle.com/lowcode/
What is impressive about APEX is that a packaged app written in 2008 is still able to run on apex.oracle.com today.
It’s the most used development platform within Oracle for running there internal business.
There is now an online APEX curriculum available for free on Oracle academy: APEX on Oracle academy
Oracle database free services online will be launched, replacing apex.oracle.com.
Upcoming features in APEX 5.2:
The next version of APEX may contain features like:
– Blueprint wizard: allows to integrate features from existing packaged apps in your on application declaratively
– Improved packaged apps
– Update of JET and JQuery versions
– Enhancements in Page designer:
-* Dialogs/pop-overs
-* Client side dialogs
-* Adaptive UI based on preference options declaratively enabled/disabled
-* Removal of legacy component view
– Enhancements on JET Charts:
-* New Gantt chart
-* New pyramid report
-* New Box Plot
-* New interactive report and websheet charts
-* Removal of 32k limit of data visualization
– Improved interactive grids
-* Flexible row height
-* Group by view
-* Pivot view
-* Region button position
-* Printing
-* Subscription
-* Computation
-* Required filtering
-* Complex filtering and highlighting
-* No stretch columns
-* Copy down facility
-* API documentation
-* Migration for interactive reports
– Improved REST services
-* Declarative support of REST services
-* REST services as data source for APEX components
-* Simple access to Cloud services
-* ORDS remote SQL data access
-* SQL execution on remote DB
All very nice and promising features 
APEX OOS Tools and helper::
Oracle Open Source Tools and other useful Open Source projects can be found in GitHub within different repositories.
Most famous OOS tools for APEX developers are:
– oracle-db-tools
– oraclejet
– db-sample-schemas
– node-dboracle
– docker-images
Link to oracle GitHub: https://github.com/oracle
Beside that to Open Source community provides various other tools for APEX developers:
– oxar (automated installation of full development environment)https://github.com/OraOpenSource/OXAR
– docker images https://github.com/Dani3lSun/docker-db-apex-dev
– Logger https://github.com/OraOpenSource/Logger
– APEX Diff (comparison of application exports based on JSON using node.js and sqlcl) https://github.com/OraOpenSource/apex-diff
– APEX Client extension https://github.com/OraOpenSource/apex-frontend-boost
– PL/SQL libraries like Alexandria https://github.com/mortenbra/alexandria-plsql-utils
– APEX backup scripts https://github.com/OraOpenSource/apexbackup
There is a lot out there to make you life easier. Enjoy them!
Thanks a lot to all contributors!
Useful APEX plugins::
APEX is a very nice development framework but it sometimes needs more than what is provided by Oracle.
Fortunately APEX allows to write extensions to fill-in the gap: plug-ins
There are many to be found on apex.world
Here is an non exhaustive list of useful plug-ins that were presented at APEX connect:
– select2 https://select2.github.io/
– Date range (based on JS moment library) http://apex.pretius.com/apex/f?p=105:DATERANGE:::NO:::
– Help text tooltip http://apex.pretius.com/apex/f?p=105:HELPTEXT:::NO:::
– Dropzone (for multiple file upload) https://github.com/Dani3lSun/apex-plugin-dropzone
– Excel to collections http://www.apex-plugin.com/oracle-apex-plugins/process-type-plugin/excel2collections_271.html
– Enhanced notification http://apex.pretius.com/apex/f?p=105:NOTIFICATIONS:::NO:::
– Nested reports http://apex.pretius.com/apex/f?p=105:NESTED_REPORTS:::NO:::
Thanks to all developers who provide those plug-ins and make APEX be even more enjoyable!
LESS is more::
What is LESS?
LESS is a CSS pre-processor which allows to use variable, mix-ins and nested rules to facilitate the rules CSS management.
It’s already in use in APEXso that you can leverage thattool to adjust your application to your Corporate identity guidelines.
The Theme roller of APEX 5.x make use of it, but the core.css of APEX is not modified that way.
I would suggest you to visit following website if you are interested in who LESS works:
http://lesscss.org/
JavaScript Debugging and Tuning::
APEX makes use of JQuery.
In some cases you might run into performance issues while loading or using your application pages.
Here are some tips and tricks to optimize JavaScript in your application based on experience.
– Use the right selector to reducing searches in the DOM (from the most to the less selective: id, element, class, attribute, pseudo)
– Use loops the right way (arrays can help)
– Use variables to reduce DOM access
– Use detach function to have an “offline” DOM and reduce the cost of parsing and accessing the DOM
– In some case native JavaScript is faster than JQuery
There are tools to help you measure the performance of your page:
– jsperf.com
– ESBench.com
– Chrome Canary developer tools
When it comes about page load performance, the size of your JavaScript library file is key. This can be reduced using tools like uglifyJS.
For Debugging and Logging purpose you can make use of the client console.log or even better the APEX.debug wrapper on it. Unfortunately those logs are only visible on the client console during runtime. One option to centralize them would be to write the result of the logs into a DB table using AJAX. Also stacktrace.js is of help as it captures the user context which can then be put with the logs to better understand the issue.
Ultimately REST service could also be an alternative to send back the logs to the DB.
Introduction to Oracle JET:
Oracle JET: JavaScript Extension Toolkit
It supports multilingual and follows W3C standards.
A JET module is always made of at least 2 parts:
– JavaScript file (view Models)
– HTML file (views)
When using JET modules you always have to take care of the required libraries/modules (dependencies).
APEX only makes use of the JET Charts for now.
I can only recommend to visit the Oracle web page on that subject:
http://www.oracle.com/webfolder/technetwork/jet/index.html/
Cet article APEX Connect 2017 – Day 2 est apparu en premier sur Blog dbi services.
Upgrade to Oracle 12.2 with dbupgrade utility
Oracle 12.2 is released and we will have to upgrade. And in this blog we are going to talk about upgrading to oracle 12.2 using the dbupgrade utility.
Of course the dbua tool is still available and can be also used.
Oracle Database 12.1 introduced the Parallel Upgrade Utility, catctl.pl. This utility reduces the total amount of time it takes to perform an upgrade by loading the database dictionary in parallel, and by using multiple SQL processes to upgrade the database.
In Oracle 12.1 catcpl.pl had to be run using perl like
$ORACLE_HOME/perl/bin/perl catctl.pl -n 8 -l /home/oracle catupgrd.log
Starting with Oracle 12.2, we can use dbupgrade utility which starts up catctl.pl instead to run it from perl.
In this article we will describe the steps for upgrade using dbupgrade.
The first step is to run the preupgrade information tool preupgrade.jar tool which replaced the preupgrd.sql and utluppkg.sql scripts. This tool can be run from operating system command line. Remember that in 12.1, the Pre-Upgrade Information Tool was run within SQL*Plus as a SQL file.
Following environment variables must be set. Indeed ORACLE_HOME must point to the earlier release.
[oracle@serveroracle ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@serveroracle ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@serveroracle ~]$ export ORACLE_SID=ORCL
[oracle@serveroracle ~]$ export PATH=.:$ORACLE_HOME/bin:$PATH
The preupgrade.jar file is located to the oracle 12.2 $ORACLE_HOME/rdbms/admin. We can execute it using the TERMINAL or FILE options. The TERMINAL option will send output in the terminal and with FILE we will have the output in a file.
oracle@serveroracle:~[ORCL] $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0
Upgrade-To version: 12.2.0.1.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: ORCL
Container ID: 0
Version: 12.1.0.2.0
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
...
...
...
Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
oracle@serveroracle:~[ORCL]
As specified if we want the output in a file, we can use the option FILE instead of TERMINAL. The output directory is $ORACLE_BASE/cfgtoollogs/$ORACLE_SID
[oracle@serveroracle ~]$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT
Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
[oracle@serveroracle ~]$
Once the execution done, the tool will identify possible issues during the upgrade and will also generate 2 scripts
preupgrade_fixups.sql: fixes issues that an automated script can fix safely. This script must be run before the upgrade
postupgrade_fixups.sql: fixes the upgrade issues that can be automatically fixed after the upgrade.
For all other issues we have to manually fix them.
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> @preupgrade_fixups.sql
Once done we are ready to run the dbupgrade utility.
Prepare the environment
oracle@serveroracle:/home/oracle/ [rdbm12201] which dbupgrade
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/dbupgrade
oracle@serveroracle:/home/oracle/ [rdbm12201] echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0.1/dbhome_1
oracle@serveroracle:/home/oracle/ [rdbm12201] export ORACLE_SID=ORCL
Copy the parameter file to the oracle 12.2 ORACLE_HOME/dbs
oracle@serveroracle:/home/oracle/ [ORCL] cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileORCL.ora /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/
oracle@serveroracle:/home/oracle/ [ORCL]
Start the database in the new environment in an upgrade mode
oracle@serveroracle:/home/oracle/ [ORCL] which sqlplus
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/sqlplus
oracle@serveroracle:/home/oracle/ [ORCL] sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 4 16:24:22 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 394264576 bytes
Fixed Size 8621136 bytes
Variable Size 293602224 bytes
Database Buffers 83886080 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL>
And then run the dbupgrade utility
oracle@serveroracle:/home/oracle/upgrade/ [ORCL] dbupgrade -n 2 -l /home/oracle/upgrade
Argument list for [/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle/upgrade
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 2
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]
...
...
...
LOG FILES: (/home/oracle/upgrade/catupgrd*.log)
Upgrade Summary Report Located in:
/home/oracle/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:1h:38m:21s]
oracle@serveroracle:/home/oracle/upgrade/ [ORCL]
Once the upgraded finished without errors, we can run the postupgrade_fixups.sql.
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 4 23:17:10 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @postupgrade_fixups.sql
Hope that this article will help
Cet article Upgrade to Oracle 12.2 with dbupgrade utility est apparu en premier sur Blog dbi services.
SQL Server 2017: first steps with Python Service
Last year, I written blogs about SQL Server 2016 and his integration from the R language.
Just for information, you can find these blogs about SQL Server 2016 R Services here:
- SQL Server 2016: R Services
- SQL Server 2016 – R Services Tips: How to find installed packages using T-SQL?
Now, the next version of SQL Server, named officially SQL Server 2017 integrate also the language Python.
The first step is to install the Python Services during the installation of SQL Server.
In the Instance Features > Database Engine Services, you have a new structure named “Machine Learning Services (In-Database)” with R and Python.
I check the case “Python” and next button.
After the classical instance, server and database Engine Configurations, a window “Consent to install Python” need your acceptance to be validate and go to the next installation step.
After that, the installation begins and failed… 
The eternal question, WHY???…
Like a majority of peoples, I don’t read the text in the window “Consent to install Python”.
The text explains “By clicking Next, you will start the download and installation of these packages to your machine.”. This means that you need an access to Internet.
You can also go the installation log file to see the error message:
After activating the access to Internet to download all necessary packages to use Python, the installation was successful. 
I have the curiosity to have a look in the installation file and find the feature parameter for the script installation for Python. In addition of ADVANCEDANALYTICS, to install Python the parameter is SQL_INST_MPY.
Like for R, in the instance configuration, you need to set the ‘external script enabled’ to 1.
If you haven’t read my article on SQL Server 2016: R Services, I rewrite the method to search and configure the service:
Script to search the configuration:
SELECT * FROM sys.configurations WHERE name='external scripts enabled'
As you can see in the result, this configuration is not dynamic and need a restart of the engine.
After a restart, the configuration is enabled.
Time to test a query….
Like my article SQL Server 2016 – R Services Tips: How to find installed packages using T-SQL?, I will find all installed packages.
I have created the Query using packages pip and pandas:
- pip is a package management system used to install and manage software packages
- Pandas is a library for data manipulation and analysis
The query uses the stored procedure sp_execute_external_script and Python as parameter for the language:
exec sp_execute_external_script @language =N'Python',
@script=N'import pip
import pandas
installed_packages = pip.get_installed_distributions()
installed_packages_list = sorted(["%s %s" % (i.key,i.version)
for i in installed_packages])
OutputDataSet = pandas.DataFrame(installed_packages_list)'
You can see that you have 125 packages installed for Python with the CPT2 of SQL Server 2017.
Have fun with Python on SQL Server… 
Cet article SQL Server 2017: first steps with Python Service est apparu en premier sur Blog dbi services.
APEX Connect 2017 – Day 1
This year again the APEX connect conference spans over three days with mixed topics around APEX, like JavaScript, CSS, SQL and much more.
After the welcome speech and the Keynote about “Reconciling APEX and the Thick Database Paradigm” by Bryn Llewellyn I decided to attend presentations on following topics:
– Temporal Validity and Flashback Data Archive
– Universal Theme and Custom Themes in APEX 5.1
– Using REST Services in APEX applications the nice way
– Uncover the Truth (of APEX application)
– Browser Developer Tools for APEX developers
– Docker for Dummies
Thick Database Paradigm:
What was highlighted by Bryn Llewellyn is that the key to proper Database development, is to encapsulate your database through PL/SQL APIs.
When you work on DB development make your environment such as it can be easily rebuild from scratch with sripts so you have no fear in making anything wrong.
Your schema user should have as less rights as needed so you keep your environment safe.
If you build proper APIs, no mater what kind of client application uses your data (APEX, REST, Java Web App, …), it will be able to interface.
Temporal Validity and Flashback Data Archive:
There is an increasing demand on data history and audit.
Data history means not only keeping track of past data but also managing different versions of the same data over time (e.g. customer delivery address). This is managed by Temporal validity.
Oracle 12c allows to automatically manage such time dependent data by using “ADD PERIOD FOR” on a table.
When retrieving the data use “AS OF PERIOD FOR” in the select statement.
Details can be found on the Oracle website:
Implementing temporal validity
Audit can be managed using the well known trigger business with all issues it can generate but also automatically by using flashback archive.
In this second case data audit is written in a specified tablespace for which you define the data retention period. SYS_FBA tables get automatically created and information tracked is managed by setting context level. This is very powerful tool as it also takes in account DML changes.
Also very important for audit purpose, flashback data cannot be modified.
You can find further information on following Blog:
Oracle 12c Flashback Data Archive
Universal Theme and Custom Themes in APEX 5.1:
After a brief overview of Theme and Templates history in APEX, we were shown how easy (at least it seems) it is to create and manage custom Theme and Templates.
Template options introduced in APEX 5 aim to reduce the number of templates for a specific “object” type to a minimum in order to ease maintenance.
Live template options have been introduced with APEX 5.1 to have a preview of the changes at run time and facilitate their usage.
Theme subscription allows to distribute changes made to a master Theme which can now be defined at workspace level.
Theme styles allow you to have a dedicated CSS file on top of you application standard CSS and define user based styles from the Theme roller.
Note: Themes based on JQuery Mobile for mobile applications should no longer be used, rather use the Universal Theme responsive as JQuery UI wasn’t updated for long and might have issues with the new JQuery core version that might be used in future versions of APEX.
Using REST Services in APEX applications the nice way:
The definition of REST is based on 3 pillars:
– Resources
– Methods (GET, PUT, POST, DELETE, PATCH)
– Representation (JSON, HTML, CSV, …)
The new REST Client Assistant packaged application in APEX 5.1 will be on a great help for developer as it manages to generate the procedures required to parse JSON data returned by a given REST Data service URL as well as the underlying SQL query to display the data in report.
When the amount of data is becoming to large, REST data services can return them on a pagination fashion which needs to be supported on the client side. At this point only classic report can support that feature in APEX 5.1. Filtering on the data query to the service needs also to be managed. The REST Data Sample application is showing how to implement the different kind of interaction with REST Data services based on Oracle standards.
There will be improvements in supporting REST Data service in the upcoming version 5.2 of APEX, such as remote SQL execution.
Uncover the Truth (of APEX application):
When you have to modify an existing APEX application or take over from customer development you need to understand thee heart of the application which can be a challenge. To do so you need to identify it’s structure and how different elements are used and interact.
Various people are interested in this:
– DB developers
– APEX developers
– Cloud developers
– Project leaders
This is all about:
– Functionality (Page function, application logic, Interfaces)
– Complexity (APEX components, PL/SQL objects, JavaScript, CSS, DB objects)
– Transparency (who changed, when, Conditions, relations between pages)
There are already different tools in APEX allowing to see different aspects of those data:
– Searches
– History
– Application utilities
– Reports
– SQL Workshop
So it can be cumbersome to walk through all those.
We were presented a self developed toll which can be seen as a kind of “Dashboard” to analyze everything in one place base on all sorts of charts reading out the APEX metadata tables. I’m looking forward to seeing it released next summer.
Browser Developer Tools for APEX developers:
The IDE for APEX is the web browser, so it’s important to know about the developer tools provided in your web browser.
Each and every web browser has it’s own tools, some being better than others.
The most used browser has also the most complete tool set: Chrome (Firefox Developer Edition is also worth looking at)
As there are a lot of information to be displayed in the Developer tools, its highly recommended to detach the window from the browser to display it on a secondary screen when possible.
CSS usage is showing all level of the style Sheet to understand what is set where.
Specifications of the web page can be modified on the fly to understand their impact.
Debugging of JavaScript can be done with setting breakpoints.
Application panel allows to monitor cookies.
Device mode allows to emulate mobile devices and even set the network speed to have a better understanding of end user experience.
Even remote debugging can be used on attached devices.
Docker for Dummies
I already heard a lot about Docker but I never took some time to look into it, so I took the opportunity to have it shown today.
What is Docker? A light weight VM?
No, a container!
It allows to share resources and get ride of things like Hypervisor and full target OS (which are used for VMs), which makes it light.
Main characteristics are:
– You can put almost anything into it
– It stays locked
– It’s efficient for transport
– It’s small, light weight
– It’s scallable
Actually it can be seen more as a software delivery platform.
The basic component is an image:
– It contains File System and parameters
– It has no state
– Layers are read only
– Layers are shared
– Updates require only updated files to be downloaded
A container is a running instance of an image. It adds a R/W layer on top of the image.
Images are not cloned.
Persistence of the data used in the container is managed by mapping local folder into the container.
Also Docker is command line based, there is a GUI available called Kitematic.
Resources:
Docker website
Kitematic website
You can find further details on following blog:
Overview and installation
Cet article APEX Connect 2017 – Day 1 est apparu en premier sur Blog dbi services.
Grid Infrastructure Installation on SLES 12 SP1
This week I needed to install Oracle Grid Infrastructure 12c release 1 in a SLES 12 SP1 environment. Everything worked fine until I ran the root.sh at the end of the installation. Here’s a quick description of the problem and the workaround.
The root.sh script ran into error and the installation was completely unsuccessfull: oracle:/u00/app/grid/12.1.0.2 # /u00/app/grid/12.1.0.2/root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= grid ORACLE_HOME= /u00/app/grid/12.1.0.2 /u00/app/grid/12.1.0.2/install/utl/rootinstall.sh: line 39: [: -eq: unary operator expected /u00/app/grid/12.1.0.2/install/utl/rootinstall.sh: line 100: [: too many arguments Copying dbhome to /usr/local/bin ... /u00/app/grid/12.1.0.2/install/utl/rootinstall.sh: line 100: [: too many arguments Copying oraenv to /usr/local/bin ... /u00/app/grid/12.1.0.2/install/utl/rootinstall.sh: line 100: [: too many arguments Copying coraenv to /usr/local/bin ... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Using configuration parameter file: /u00/app/grid/12.1.0.2/crs/install/crsconfig_params LOCAL ADD MODE Creating OCR keys for user 'grid', privgrp 'oinstall'.. Operation successful. LOCAL ONLY MODE Successfully accumulated necessary OCR keys. Creating OCR keys for user 'root', privgrp 'root'.. Operation successful. CRS-4664: Node oracle_grid successfully pinned. 2017/03/31 09:56:43 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service' PRCR-1006 : Failed to add resource ora.ons for ons PRCR-1115 : Failed to find entities of type resource type that match filters (TYPE_NAME ends .type) and contain attributes CRS-0184 : Cannot communicate with the CRS daemon. 2017/03/31 09:57:04 CLSRSC-180: An error occurred while executing the command 'srvctl add ons' (error code 0) 2017/03/31 09:57:55 CLSRSC-115: Start of resource 'ora.evmd' failed 2017/03/31 09:57:55 CLSRSC-202: Failed to start EVM daemon The command '/u00/app/grid/12.1.0.2/perl/bin/perl -I/u00/app/grid/12.1.0.2/perl/lib -I/u00/app/grid/12.1.0.2/crs/install /u00/app/grid/12.1.0.2/crs/install/roothas.pl ' execution failed
When we run crsctl stat res –t :
grid@oracle_grid:/u00/app/grid/product/12.1.0.2/grid/bin> ./crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.cssd 1 OFFLINE OFFLINE STABLE ora.diskmon 1 OFFLINE OFFLINE STABLE ora.evmd 1 OFFLINE OFFLINE STABLE --------------------------------------------------------------------------------
After trying multiple times with other Oracle Grid Infrastructure versions from 11.2.0.4 to 12.2.0.1, I has to open a service request at Oracle, and they furnished me the following workaround:
Once rot.sh has failed, we do not close the GUI installer windows because we will use it to complete the installation after the root.sh is complete, at first we have to deconfigure the failed installation:
oracle_grid:/u00/app/grid/product/12.1.0.2/grid/crs/install # ./roothas.pl -verbose -deconfig –force oracle_grid:/u00/app/grid/product/12.1.0.2/grid/crs/install # . rootcrs.sh -deconfig –force
Then we modify the /etc/ld.so file by adding /lib64/noelision as first entry. The file should look like:
oracle@oracle_grid:/u00/app/oracle/product/12.1.0.2/dbhome_1/dbs/ [DORSTREA] cat /etc/ld.so.conf /lib64/noelision /usr/local/lib64 /usr/local/lib include /etc/ld.so.conf.d/*.conf
Finally we create a symbolic link between $GI_HOME/lib/libpthread.so.0 and /lib64/noelision/libpthread-2.19.so
lrwxrwxrwx 1 root root 35 Apr 11 15:56 libpthread.so.0 -> /lib64/noelision/libpthread-2.19.so
We only have to try to run the root.sh, and finally it works fine:
oracle_grid:/u00/app/grid/product/12.1.0.2/grid # . root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= grid ORACLE_HOME= /u00/app/grid/product/12.1.0.2/grid Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of "dbhome" have not changed. No need to overwrite. The contents of "oraenv" have not changed. No need to overwrite. The contents of "coraenv" have not changed. No need to overwrite. Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Using configuration parameter file: /u00/app/grid/product/12.1.0.2/grid/crs/install/crsconfig_params LOCAL ADD MODE Creating OCR keys for user 'grid', privgrp 'oinstall'.. Operation successful. LOCAL ONLY MODE Successfully accumulated necessary OCR keys. Creating OCR keys for user 'root', privgrp 'root'.. Operation successful. CRS-4664: Node oracle_grid successfully pinned. 2017/04/11 15:56:37 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service' oracle_grid /u00/app/grid/product/12.1.0.2/grid/cdata/oracle_grid/backup_20170411_155653.olr 0 CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oracle_grid' CRS-2673: Attempting to stop 'ora.evmd' on 'oracle_grid' CRS-2677: Stop of 'ora.evmd' on 'oracle_grid' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oracle_grid' has completed CRS-4133: Oracle High Availability Services has been stopped. CRS-4123: Oracle High Availability Services has been started. 2017/04/11 15:57:09 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
After the root.sh is successfully completed, we continue with the Oracle Installer, and everything is correctly configured for the Oracle Grid Infrastructure.
I will keep you informed of the bug evolution, and I will test ASAP the Oracle Grid Infrastructure installation under SLES 12 SP2 …
Cet article Grid Infrastructure Installation on SLES 12 SP1 est apparu en premier sur Blog dbi services.
What is in a transportable tablespace dumpfile?
On 31st of May in Düsseldorf, at DOAG Datenbank, I’ll talk about transportable tablespaces and pluggable databases. Both methods are transporting data physically, the difference is in the transport of the metadata, which can be more flexible when transported logically, as with TTS, but faster when transported physically with PDB. I have a lot of demos to show transportable tablespaces with RMAN, and the different cloning features available in 12cR2. If I have time I’ll show what is inside the dumpfile when using Data Pump to export the metadata. Here is the idea.
Here is how we export metadata with Data Pump for transportable tablespaces.
expdp system/oracle@//localhost/PDB1 directory=VAR_TMP dumpfile=expdat.tmp transport_tablespaces=USERS exclude=table_statistics,index_statistics;
...
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/oradata/tmp/expdat.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
/u01/oradata/CDB1/PDB1/users01.dbf
The metadata is exported into expdata.dmp and the data resides in the original datafile. The dumpfile is a binary file but there is a way to extract metadata as DDL using impdp
impdp sqlfileHere I run impdp with sqlfile to generate all DDL into this file. Nothing is imported and the datafiles are not read, reason why I’ve just put something wrong to transport_datafiles:
impdp system/oracle@//localhost/PDB1 directory=VAR_TMP transport_datafiles=blahblahblah sqlfile=sqlfile.sql ;
No error. Only the dumpfile has been read and here is an extract of the DDP in sqlfile.sql concerning the PK_DEPT and PK_EMP indexes:
-- new object type path: TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX (CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(SEG_FILE 26 SEG_BLOCK 138 OBJNO_REUSE 73197
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ) ENABLE;
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX (CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(SEG_FILE 26 SEG_BLOCK 154 OBJNO_REUSE 73205
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ) ENABLE;
-- new object type path: TRANSPORTABLE_EXPORT/INDEX_STATISTICS
-- new object type path: TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
-- new object type path: TRANSPORTABLE_EXPORT/TABLE_STATISTICS
-- new object type path: TRANSPORTABLE_EXPORT/STATISTICS/MARKER
-- new object type path: TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
This looks like the DDL used to re-create the same table except that we can see two storage attributes that are not documented:
- SEG_FILE and SEG_BLOCK
- OBJNO_REUSE
When you create an empty table, you just provide the tablespace name and Oracle will allocate the first extent, with the segment header. You don’t choose the data placement within the tablespace. But here we are in a different case: the extents already exist in the datafiles that we transport, and the DDL must just map to it. This is why in this case the segment header file number and block number is specified. The remaining extent allocation information is stored within the datafiles (Locally Managed Tablespace), only the segment header must be known by the dictionary.
As an example, when I look at the database where the export comes from, I can see that the attributes for PK_EMP (SEG_FILE 26 SEG_BLOCK 154) are the relative file number and header block number of the PK_EMP segment:
10:49:10 SQL> select owner,segment_name,header_file,header_block,blocks,extents,tablespace_name,relative_fno from dba_segments where owner='SCOTT';
OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS TABLESPACE_NAME RELATIVE_FNO
----- ------------ ----------- ------------ ------ ------- --------------- ------------
SCOTT DEPT 31 130 8 1 USERS 26
SCOTT EMP 31 146 8 1 USERS 26
SCOTT SALGRADE 31 162 8 1 USERS 26
SCOTT PK_DEPT 31 138 8 1 USERS 26
SCOTT PK_EMP 31 154 8 1 USERS 26
This file identifier is a relative file number within the tablespace, which means that there is no need to change it when a tablespace is transported.
You will see exactly the same information in the database where you import the tablespace (except for HEADER_FILE which is the absolute file number).
OBJNO_REUSEEach segment has a DATA_OBJECT_ID, which is referenced in each block, the ROWIDs. This must not change when we transport a tablespace because the goal is that nothing has to be modified in the datafiles. For this reason, the data object id is exported with the metadata, as we can see for this PK_EMP example (OBJNO_REUSE 73205), and set to the same in the target dictionary. Here are the data object IDs for the objects exported here:
10:49:20 SQL> select owner,object_name,object_type,object_id,data_object_id from dba_objects where owner='SCOTT';
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
----- ----------- ----------- --------- --------------
SCOTT DEPT TABLE 73196 73196
SCOTT PK_DEPT INDEX 73197 73197
SCOTT EMP TABLE 73198 73206
SCOTT PK_EMP INDEX 73199 73205
SCOTT BONUS TABLE 73200 73200
SCOTT SALGRADE TABLE 73201 73201
The OBJECT_ID will be different in the target, assigned in the same way as when we create an object, but this one is not referenced anywhere within the datafiles.
So what?Usually, the metadata precedes the data. With transportable tablespaces, it is the opposite: data is there and metadata is re-created to map the data. This metadata is what is stored into the dumpfile exported to transport tablespaces.
From what you have seen, you can understand now that the RELATIVE_FNO and the DATA_OBJECT_ID are not unique within a database, but only within a tablespace. You can understand also that Transportable Tablespace import duration does not depend on the size of data, but is proportional to the number of objects (metadata). This is where Pluggable Databases is more efficient: metadata is transported physically and import duration does not depend on the number of objects, especially when it does not involve an upgrade to new version and object recompilation.
Cet article What is in a transportable tablespace dumpfile? est apparu en premier sur Blog dbi services.
Oracle 12cR2 : Optimizer Statistics Advisor
The Optimizer Statistics Advisor is a new Advisor in Oracle 12.2.
The goal of this Advisor is to check the way you gather the statistics on your database, and depending on what is found, it will makes some recommendations on how you can improve the statistics gathering strategy in order to provide more efficient statistics to the CBO.
This Advisor is also able to generate remediation scripts to apply the statistics gathering “best practices”.
The recommendations are based on 23 predefined rules :
SQL> select rule_id, name, rule_type, description from v$stats_advisor_rules;
RULE_ID NAME RULE_TYPE DESCRIPTION
---------- ----------------------------------- --------- -------------------------------------------------------------------------------------
0 SYSTEM
1 UseAutoJob SYSTEM Use Auto Job for Statistics Collection
2 CompleteAutoJob SYSTEM Auto Statistics Gather Job should complete successfully
3 MaintainStatsHistory SYSTEM Maintain Statistics History
4 UseConcurrent SYSTEM Use Concurrent preference for Statistics Collection
5 UseDefaultPreference SYSTEM Use Default Preference for Stats Collection
6 TurnOnSQLPlanDirective SYSTEM SQL Plan Directives should not be disabled
7 AvoidSetProcedures OPERATION Avoid Set Statistics Procedures
8 UseDefaultParams OPERATION Use Default Parameters in Statistics Collection Procedures
9 UseGatherSchemaStats OPERATION Use gather_schema_stats procedure
10 AvoidInefficientStatsOprSeq OPERATION Avoid inefficient statistics operation sequences
11 AvoidUnnecessaryStatsCollection OBJECT Avoid unnecessary statistics collection
12 AvoidStaleStats OBJECT Avoid objects with stale or no statistics
13 GatherStatsAfterBulkDML OBJECT Do not gather statistics right before bulk DML
14 LockVolatileTable OBJECT Statistics for objects with volatile data should be locked
15 UnlockNonVolatileTable OBJECT Statistics for objects with non-volatile should not be locked
16 MaintainStatsConsistency OBJECT Statistics of dependent objects should be consistent
17 AvoidDropRecreate OBJECT Avoid drop and recreate object seqauences
18 UseIncremental OBJECT Statistics should be maintained incrementally when it is beneficial
19 NotUseIncremental OBJECT Statistics should not be maintained incrementally when it is not beneficial
20 AvoidOutOfRange OBJECT Avoid Out of Range Histogram endpoints
21 UseAutoDegree OBJECT Use Auto Degree for statistics collection
22 UseDefaultObjectPreference OBJECT Use Default Object Preference for statistics collection
23 AvoidAnalyzeTable OBJECT Avoid using analyze table commands for statistics collection
24 rows selected.
SQL>
You can have a look at this blog if you want a little bit more informations about these rules.
If you want to exclude some rules or some database objects of the Advisor’s recommandation, you can define multiple filters. (I will do that below.)
Well, let’s see how to use the Advisor. The first step is to create a task which will run it :
DECLARE
tname VARCHAR2(32767);
ret VARCHAR2(32767);
BEGIN
tname := 'stat_advisor_1';
ret := DBMS_STATS.CREATE_ADVISOR_TASK(tname);
END;
/
The task is created :
SQL> select task_name, advisor_name, created, status from dba_advisor_tasks where advisor_name = 'Statistics Advisor';
TASK_NAME ADVISOR_NAME CREATED STATUS
------------------------------ ------------------------------ ------------------- -----------
STAT_ADVISOR_1 Statistics Advisor 04.05.2017-11:19:25 INITIAL
SQL>
Now, I want to define some filters.
The first one will disable the Advisor for all objects, the 2nd will enable it only on a specific table and the 3th and 4th will exclude two rules :
DECLARE
filter1 CLOB;
filter2 CLOB;
filter3 CLOB;
filter4 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
task_name => 'STAT_ADVISOR_1',
stats_adv_opr_type => 'EXECUTE',
rule_name => NULL,
ownname => NULL,
tabname => NULL,
action => 'DISABLE' );
filter2 := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
task_name => 'STAT_ADVISOR_1',
stats_adv_opr_type => 'EXECUTE',
rule_name => NULL,
ownname => 'JOC',
tabname => 'T2',
action => 'ENABLE' );
filter3 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
task_name => 'STAT_ADVISOR_1',
stats_adv_opr_type => 'EXECUTE',
rule_name => 'AvoidDropRecreate',
action => 'DISABLE' );
filter4 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
task_name => 'STAT_ADVISOR_1',
stats_adv_opr_type => 'EXECUTE',
rule_name => 'UseGatherSchemaStats',
action => 'DISABLE' );
END;
/
All is ready, let’s run the task…
DECLARE
tname VARCHAR2(32767);
ret VARCHAR2(32767);
BEGIN
tname := 'stat_advisor_1';
ret := DBMS_STATS.EXECUTE_ADVISOR_TASK(tname);
END;
/
…and generate the report :
SQL> select dbms_stats.report_advisor_task('stat_advisor_1',null,'text','all','all') as report from dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
Task Name : STAT_ADVISOR_1
Execution Name : EXEC_2172
Created : 05-04-17 11:34:51
Last Modified : 05-04-17 11:35:10
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
For execution EXEC_2172 of task STAT_ADVISOR_1, the Statistics Advisor has no
findings.
-------------------------------------------------------------------------------
SQL>
Cool ! Nothing to report regarding statistics gathering on my the table JOC.T2 (see filter2 above).
But how does the Advisor reacts when I run it after having deleted the statistics on this table ?
SQL> exec dbms_stats.delete_table_stats(ownname=>'JOC',tabname=>'T2');
PL/SQL procedure successfully completed.
SQL> DECLARE
2 tname VARCHAR2(32767);
3 ret VARCHAR2(32767);
4 BEGIN
5 tname := 'stat_advisor_1';
6 ret := DBMS_STATS.EXECUTE_ADVISOR_TASK(tname);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> select dbms_stats.report_advisor_task('stat_advisor_1',null,'text','all','all') as report from dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
Task Name : STAT_ADVISOR_1
Execution Name : EXEC_2182
Created : 05-04-17 11:34:51
Last Modified : 05-04-17 11:44:22
-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
For execution EXEC_2182 of task STAT_ADVISOR_1, the Statistics Advisor has 1
finding(s). The findings are related to the following rules: AVOIDSTALESTATS.
Please refer to the finding section for detailed information.
-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
Rule Name: AvoidStaleStats
Rule Description: Avoid objects with stale or no statistics
Finding: There are 1 object(s) with no statistics.
Schema:
JOC
Objects:
T2
Recommendation: Gather Statistics on those objects with no statistics.
Example:
-- Gathering statistics for tables with stale or no statistics in schema, SH:
exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
Rationale: Stale statistics or no statistics will result in bad plans.
-------------------------------------------------------------------------------
It looks to work well. The Advisor detected that there is no stats on the table, and a rule were triggered.
And what about the remediation scripts ? Firstly, we have to generate them :
VARIABLE script CLOB
DECLARE
tname VARCHAR2(32767);
BEGIN
tname := 'stat_advisor_1';
:script := DBMS_STATS.SCRIPT_ADVISOR_TASK(tname);
END;
/
PL/SQL procedure successfully completed.
And then display them :
set linesize 3000
set long 500000
set pagesize 0
set longchunksize 100000
set serveroutput on
DECLARE
v_len NUMBER(10);
v_offset NUMBER(10) :=1;
v_amount NUMBER(10) :=10000;
BEGIN
v_len := DBMS_LOB.getlength(:script);
WHILE (v_offset < v_len)
LOOP
DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(:script,v_amount,v_offset));
v_offset := v_offset + v_amount;
END LOOP;
END;
13 /
-- Script generated for the recommendations from execution EXEC_2182
-- in the statistics advisor task STAT_ADVISOR_1
-- Script version 12.2
-- No scripts will be provided for the rule USEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule COMPLETEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule MAINTAINSTATSHISTORY.Please check the report for more details.
-- No scripts will be provided for the rule TURNONSQLPLANDIRECTIVE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDSETPROCEDURES.Please check the report for more details.
-- No scripts will be provided for the rule USEDEFAULTPARAMS.Please check the report for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION.Please check the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE.Please check the report for more details.
-- No scripts will be provided for the rule USEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule COMPLETEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule MAINTAINSTATSHISTORY.Please check the report for more details.
-- No scripts will be provided for the rule TURNONSQLPLANDIRECTIVE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDSETPROCEDURES.Please check the report for more details.
-- No scripts will be provided for the rule USEDEFAULTPARAMS.Please check the report for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION.Please check the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE.Please check the report for more details.
-- Scripts for rule USECONCURRENT
-- Rule Description: Use Concurrent preference for Statistics Collection
-- No scripts will be provided for the rule USEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule COMPLETEAUTOJOB.Please check the report for more details.
-- No scripts will be provided for the rule MAINTAINSTATSHISTORY.Please check the report for more details.
-- No scripts will be provided for the rule TURNONSQLPLANDIRECTIVE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDSETPROCEDURES.Please check the report for more details.
-- No scripts will be provided for the rule USEDEFAULTPARAMS.Please check the report for more details.
-- No scripts will be provided for the rule USEGATHERSCHEMASTATS.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDINEFFICIENTSTATSOPRSEQ.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDUNNECESSARYSTATSCOLLECTION.Please check the report for more details.
-- No scripts will be provided for the rule GATHERSTATSAFTERBULKDML.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDDROPRECREATE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDOUTOFRANGE.Please check the report for more details.
-- No scripts will be provided for the rule AVOIDANALYZETABLE.Please check the report for more details.
-- Scripts for rule USEDEFAULTPREFERENCE
-- Rule Description: Use Default Preference for Stats Collection
-- Set global preferenes to default values.
-- Scripts for rule USEDEFAULTOBJECTPREFERENCE
-- Rule Description: Use Default Object Preference for statistics collection
-- Setting object-level preferences to default values
-- setting CASCADE to default value for object level preference
-- setting ESTIMATE_PERCENT to default value for object level preference
-- setting METHOD_OPT to default value for object level preference
-- setting GRANULARITY to default value for object level preference
-- setting NO_INVALIDATE to default value for object level preference
-- Scripts for rule USEINCREMENTAL
-- Rule Description: Statistics should be maintained incrementally when it is beneficial
-- Turn on the incremental option for those objects for which using incremental is helpful.
-- Scripts for rule UNLOCKNONVOLATILETABLE
-- Rule Description: Statistics for objects with non-volatile should not be locked
-- Unlock statistics for objects that are not volatile.
-- Scripts for rule LOCKVOLATILETABLE
-- Rule Description: Statistics for objects with volatile data should be locked
-- Lock statistics for volatile objects.
-- Scripts for rule NOTUSEINCREMENTAL
-- Rule Description: Statistics should not be maintained incrementally when it is not beneficial
-- Turn off incremental option for those objects for which using incremental is not helpful.
-- Scripts for rule USEAUTODEGREE
-- Rule Description: Use Auto Degree for statistics collection
-- Turn on auto degree for those objects for which using auto degree is helpful.
-- Scripts for rule AVOIDSTALESTATS
-- Rule Description: Avoid objects with stale or no statistics
-- Gather statistics for those objcts that are missing or have no statistics.
-- Scripts for rule MAINTAINSTATSCONSISTENCY
-- Rule Description: Statistics of dependent objects should be consistent
-- Gather statistics for those objcts that are missing or have no statistics.
declare
obj_filter_list dbms_stats.ObjectTab;
obj_filter dbms_stats.ObjectElem;
obj_cnt number := 0;
begin
obj_filter_list := dbms_stats.ObjectTab();
obj_filter.ownname := 'JOC';
obj_filter.objtype := 'TABLE';
obj_filter.objname := 'T2';
obj_filter_list.extend();
obj_cnt := obj_cnt + 1;
obj_filter_list(obj_cnt) := obj_filter;
dbms_stats.gather_database_stats(
obj_filter_list=>obj_filter_list);
end;
/
PL/SQL procedure successfully completed.
SQL>
It was a very simple demo, but as you can see above, the Advisor provides a small script to adjust what is wrong or what is missing concerning the statistics of the table.
Conclusion :
Once you have upgraded your database to Oracle 12.2, don’t hesitate to set up the new Statistics Advisor. It is easy to deploy and can be fully personalized depending on what you want to check (which objects ? which rules ?). Moreover, it has been developped by the same team who develops and maintains the CBO. Therefore, they know which statistics the Optimizer needs !
Cet article Oracle 12cR2 : Optimizer Statistics Advisor est apparu en premier sur Blog dbi services.