Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 15 hours 57 min ago

Windows Server – Service not starting with ‘Error 1067: The process terminated unexpectedly’ – again

Sun, 2018-07-01 14:10

Some months ago, I wrote a blog regarding a Windows Service not starting up with the error 1067. The first time I faced this issue, it was about an fmeAG Migration Center installation and as said in this previous blog, I’m not an expert in this software so I started to work on the Windows side of the issue and I found a workaround, which I explained in the above mentioned blog. A few weeks ago, I faced the exact same issue on another environment. Since I had a few hours available at that time and since I was much more familiar with the fmeAG Migration Center, I tried to really find the issue and not just apply a – kind of stupid – workaround. You guessed it, I found the issue, otherwise I wouldn’t have written this blog in the first place…

So first of all, the Windows Service that couldn’t start is the “Migration Center Job Server” which uses the wrapper.exe which can be found in the installation folder (E.g.: D:\fmeAG\migration-center Server Components 3.3\). In this folder, there is a wrapper.log as well as wrapper.conf so that looked like a good starting point. From the logs, this is what I could find (I cut most of the non-needed lines):

STATUS | wrapper  | 2018/06/14 12:46:47 | --> Wrapper Started as Service
STATUS | wrapper  | 2018/06/14 12:46:48 | Launching a JVM...
INFO   | jvm 1    | 2018/06/14 12:46:48 | Usage: java [-options] class [args...]
INFO   | jvm 1    | 2018/06/14 12:46:48 |            (to execute a class)
INFO   | jvm 1    | 2018/06/14 12:46:48 |    or  java [-options] -jar jarfile [args...]
INFO   | jvm 1    | 2018/06/14 12:46:48 |            (to execute a jar file)
INFO   | jvm 1    | 2018/06/14 12:46:48 | where options include:
INFO   | jvm 1    | 2018/06/14 12:46:48 |     -d32	  use a 32-bit data model if available
INFO   | jvm 1    | 2018/06/14 12:46:48 |     -d64	  use a 64-bit data model if available
...
INFO   | jvm 1    | 2018/06/14 12:46:48 | See http://www.oracle.com/technetwork/java/javase/documentation/index.html for more details.
ERROR  | wrapper  | 2018/06/14 12:46:48 | JVM exited while loading the application.
STATUS | wrapper  | 2018/06/14 12:46:52 | Launching a JVM...
...
ERROR  | wrapper  | 2018/06/14 12:46:53 | JVM exited while loading the application.
STATUS | wrapper  | 2018/06/14 12:46:57 | Launching a JVM...
...
ERROR  | wrapper  | 2018/06/14 12:46:58 | JVM exited while loading the application.
STATUS | wrapper  | 2018/06/14 12:47:02 | Launching a JVM...
...
ERROR  | wrapper  | 2018/06/14 12:47:02 | JVM exited while loading the application.
STATUS | wrapper  | 2018/06/14 12:47:07 | Launching a JVM...
...
ERROR  | wrapper  | 2018/06/14 12:47:07 | JVM exited while loading the application.
FATAL  | wrapper  | 2018/06/14 12:47:07 | There were 5 failed launches in a row, each lasting less than 300 seconds.  Giving up.
FATAL  | wrapper  | 2018/06/14 12:47:07 |   There may be a configuration problem: please check the logs.
STATUS | wrapper  | 2018/06/14 12:47:07 | <-- Wrapper Stopped

 

This actually looked quite interesting… The fact that when starting the JVM, the java “help” is displayed would tend to show that the start command isn’t correct or that there is something wrong with it. I was able to start a JVM using the Windows command line tools so it wasn’t an issue with Java. As a result, I checked the wrapper.conf which was shipped with the software. In our installation, we only slightly updated this configuration file to add custom JVM parameters but this wasn’t the issue (I still checked with the default file to be sure). There were no issue with the content of this file or with its formatting but there are still something useful in it: the possibility to change the log level. There are the relevant settings:

# Log Level for console output.  (See docs for log levels)
wrapper.console.loglevel=ERROR

# Log file to use for wrapper output logging.
wrapper.logfile=./wrapper.log

# Log Level for log file output.  (See docs for log levels)
wrapper.logfile.loglevel=INFO

 

So to see more information on the log file, you simply have to switch “wrapper.logfile.loglevel” from INFO to DEBUG for example. After doing that, the logs were clearer:

STATUS | wrapper  | 2018/06/14 12:53:10 | --> Wrapper Started as Service
DEBUG  | wrapper  | 2018/06/14 12:53:10 | Using tick timer.
DEBUG  | wrapperp | 2018/06/14 12:53:10 | server listening on port 32000.
STATUS | wrapper  | 2018/06/14 12:53:10 | Launching a JVM...
DEBUG  | wrapper  | 2018/06/14 12:53:10 | command: "D:\Java\jdk1.8.0_171\bin\java.exe" -Xss512k -DdocumentDirectory.home="%DOCUMENTDIRECTORY_HOME%" -Dclb.library.path=.\lib\mc-d2-importer\LockBox\lib\native\win_vc100_ia32 -Duser.timezone=UTC -Xms512m -Xmx1536m -Djava.library.path=".;./lib/mc-outlook-adaptor;./lib/mc-domino-scanner/lib;D:\DFC\DFC_7.3\Shared;D:\Oracle\instantclient_12_2;D:\Java\jdk1.8.0_171\bin;C:\windows\system32;C:\windows;C:\windows\System32\Wbem;C:\windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\1E\NomadBranch"" -classpath "wrappertest.jar;wrapper.jar;./lib/mc-common/mc-common-3.3.jar;./lib/mc-api/mc-api-3.3.jar;./lib/mc-api/ojdbc7.jar;./lib/mc-api/orai18n.jar;./lib/mc-api/runtime12.jar;./lib/mc-api/translator.jar;./lib/mc-server;./lib/mc-server/log4j-1.2.17.jar;./lib/mc-server/mc-server-3.3.jar;./lib/mc-dctm-adaptor;./lib/mc-dctm-adaptor/mc-dctm-adaptor-3.3.jar;./lib/mc-d2-importer;./lib/mc-d2-importer/C2-API.jar;./lib/mc-d2-importer/C6-Common-4.7.0.jar;./lib/mc-d2-importer/commons-collections-3.2.jar;./lib/mc-d2-importer/commons-compress-1.5.jar;./lib/mc-d2-importer/commons-io-1.4.jar;./lib/mc-d2-importer/commons-lang-2.4.jar;./lib/mc-d2-importer/D2-API-4.7.0.jar;./lib/mc-d2-importer/D2-BOCS-65.jar;./lib/mc-d2-importer/D2-Specifications-API.jar;./lib/mc-d2-importer/D2-Specifications.jar;./lib/mc-d2-importer/D2BofServices-4.7.0.jar;./lib/mc-d2-importer/D2FS-Generated-4.7.0.jar;./lib/mc-d2-importer/D2FS4DCTM-API-4.7.0.jar;./lib/mc-d2-importer/dfc.jar;./lib/mc-d2-importer/diff-0.4.2.jar;./lib/mc-d2-importer/ehcache-core-1.7.2.jar;./lib/mc-d2-importer/emc-dfs-rt.jar;./lib/mc-d2-importer/emc-dfs-services.jar;./lib/mc-d2-importer/gwt-servlet-2.5.1.jar;./lib/mc-d2-importer/logback-classic-0.9.18.jar;./lib/mc-d2-importer/logback-core-0.9.18.jar;./lib/mc-d2-importer/mail.jar;./lib/mc-d2-importer/mc-d2-importer-3.3.jar;./lib/mc-d2-importer/poi-3.6-20091214.jar;./lib/mc-d2-importer/poi-contrib-3.6-20091214.jar;./lib/mc-d2-importer/slf4j-api-1.5.10.jar;./lib/mc-dcm-importer;./lib/mc-dcm-importer/dcm.jar;./lib/mc-dcm-importer/dcmibof.jar;./lib/mc-dcm-importer/dcmproperties.jar;./lib/mc-dcm-importer/dcmresource.jar;./lib/mc-dcm-importer/DmcRecords.jar;./lib/mc-dcm-importer/mc-dcm-importer-3.3.jar;./lib/mc-dcm-importer/pss.jar;./lib/mc-otcs-common/activation.jar;./lib/mc-otcs-common/aspectjrt.jar;./lib/mc-otcs-common/commons-lang3-3.3.2.jar;./lib/mc-otcs-common/jaxb-api.jar;./lib/mc-otcs-common/jaxb-impl.jar;./lib/mc-otcs-common/jaxws-api.jar;./lib/mc-otcs-common/jaxws-rt.jar;./lib/mc-otcs-common/jsr173_api.jar;./lib/mc-otcs-common/jsr181-api.jar;./lib/mc-otcs-common/jsr250-api.jar;./lib/mc-otcs-common/mimepull.jar;./lib/mc-otcs-common/resolver.jar;./lib/mc-otcs-common/saaj-api.jar;./lib/mc-otcs-common/saaj-impl.jar;./lib/mc-otcs-common/stax-ex.jar;./lib/mc-otcs-common/streambuffer.jar;./lib/mc-d2-importer/LockBox;./lib/mc-firstdoc-importer;D:\Documentum\config;D:\DFC\DFC_7.3\dctm.jar" -Dwrapper.key="eFO3zr2BRv874Qb4" -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.debug="TRUE" -Dwrapper.pid=4832 -Dwrapper.version="3.2.3" -Dwrapper.native_library="wrapper" -Dwrapper.cpu.timeout="10" -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp de.fme.mc.server.Main
DEBUG  | wrapper  | 2018/06/14 12:53:10 | JVM started (PID=4756)
INFO   | jvm 1    | 2018/06/14 12:53:10 | Usage: java [-options] class [args...]
INFO   | jvm 1    | 2018/06/14 12:53:10 |            (to execute a class)
INFO   | jvm 1    | 2018/06/14 12:53:10 |    or  java [-options] -jar jarfile [args...]
INFO   | jvm 1    | 2018/06/14 12:53:10 |            (to execute a jar file)
INFO   | jvm 1    | 2018/06/14 12:53:10 | where options include:
INFO   | jvm 1    | 2018/06/14 12:53:10 |     -d32	  use a 32-bit data model if available
INFO   | jvm 1    | 2018/06/14 12:53:10 |     -d64	  use a 64-bit data model if available
...
INFO   | jvm 1    | 2018/06/14 12:53:10 | See http://www.oracle.com/technetwork/java/javase/documentation/index.html for more details.
ERROR  | wrapper  | 2018/06/14 12:53:10 | JVM exited while loading the application.
...
FATAL  | wrapper  | 2018/06/14 12:53:30 | There were 5 failed launches in a row, each lasting less than 300 seconds.  Giving up.
FATAL  | wrapper  | 2018/06/14 12:53:30 |   There may be a configuration problem: please check the logs.
STATUS | wrapper  | 2018/06/14 12:53:30 | <-- Wrapper Stopped

 

From the beginning, this looked like an issue with the java command executed so I took a close look at it and I could find that there were indeed something wrong. If you have good eyes (and took the time to scroll a little bit), you can see that at some point, there are two consecutive double quotes ( “” ) to close the “-Djava.library.path” JVM parameter. As a result, the following parameter which is -classpath isn’t taken properly and it just cause the whole command to be wrongly formatted…

The value to be used for the “-Djava.library.path” JVM parameter is coming from the wrapper.conf file as well: in this file, you can find the “wrapper.java.library.path.X=” lines where X is a number starting with 1 and each of these values are concatenated (separated with semicolon) to form the final value. By default, the last of these lines will have “%PATH%” as value so it will replace it at runtime with the actual value of this environment variable. Since it was identified that the issue is coming from the double quotes at the end of the “-Djava.library.path”, it is therefore safe to assume that the issue is inside %PATH% definition…

Checking its value using the command prompt didn’t show anything strange but through the Control Panel, it confirmed my suspicions: the declaration of the %PATH% environment variable for the current user ended with a double quote ( ” ) while there were no double quotes at the start of this declaration. After removing it, the Service was able to start successfully. After investigation on the root cause, it appeared that this double quote was actually coming from an issue with the Windows Server delivery tool that wrongly set this environment variable on the server delivery. This also explained why the workaround I described in the previous blog worked: it cleaned the environment variables for this user (there were no specific declaration on this user’s %PATH%, it was only the default Windows stuff).

 

 

Cet article Windows Server – Service not starting with ‘Error 1067: The process terminated unexpectedly’ – again est apparu en premier sur Blog dbi services.

Statspack installation scripts

Fri, 2018-06-29 14:24

When Diagnostic Pack is disabled, either because you don’t have Diagnostic Pack or you are in Standard Edition, I highly recommend to install Statspack. When you will need it, to investigate on an issue that occured in the past, you will be happy to have it already installed and gathering snapshots.

I order to be sure to have it installed correctly, there’s a bit more to do than just what is described in spcreate.doc and I detail that in a UKOUG Oracle Scene article Improving Statspack Experience.

For easy download, I’ve put the scripts on GitHub: https://github.com/FranckPachot/scripts/tree/master/statspack

You will find the following scripts for Statspack installation:

  • 01-install.sql to create the tablespace and call spcreate
  • 02-schedule.sql to schedule snap and purge jobs
  • 03-idle-events.sql to fix issue described at https://blog.dbi-services.com/statspack-idle-events/
  • 04-modify-level.sql to collect execution plans and segment statistics

And some additional scripts:

  • 08-create-delta-views.sql to create views easy to query
  • 11-comment-with-load.sql to add load information to snapshots without comments

You can leave feedback and comment on Twitter:

The scripts I use to install Statspack are on GitHub:https://t.co/qboDoX2pc9
- I'll be happy to have your feedback here -

— Franck Pachot (@FranckPachot) June 29, 2018

 

Cet article Statspack installation scripts est apparu en premier sur Blog dbi services.

#SwissPGDay is a place where you can meet developers of the PostgreSQL community

Fri, 2018-06-29 12:10

swisspgday
As the #SwissPGDay is located one hour south of  Zurich we traveled yesterday evening to Rapperswil, where is a nice little Swiss city. For the People coming one day before, Stephan Wagner organized a nice dinner in an excellent restaurant, before I forget the Red-wine was also excellent !

preconf-diner
The #SwissPGday is located in Rapperswil at the HSR(Hochschule für Technik) near the Zürisee, it’s a nice location and everything was nicely organized. Also the look-out from the HSR is very beautiful.

lake
After a short introduction from Stephan Keller (HSR) where the room of the plenum session was completely full, the presentations was splitted in two streams. For your information last year they had around 60 participants this year we are around 100 participants, so I would say, that PostgreSQL it’s the trend.

Screen Shot 2018-06-29 at 19.44.58

Today what I especially appreciated, is that many teacher’s of the event are also developers of the PostgreSQL community, thus we get the latest information of some development and it was possible to discuss about new features directly with them.

I will also give you feedback of 2 sessions from other partner which I really appreciated. For sure the session from my colleague Daniel Westermann was the best one :-), who presented the new features of PostgreSQL 11 with many demos.

Screen Shot 2018-06-29 at 22.20.47The first one was from Laurenz Albe(Cybertec), who presented a community Tool ORA_MIGRATOR and tips to migrate from Oracle to PostgreSQL.
The second one was from  Harald Armin Massa(2ndQuadrant), who presented the new PostgreSQL logical replication, which was developed by his company and is now partially available for the community edition.

As usual to finish an Apero was organized to drink a beer together and exchange with the other participants.

See you next year at the #SwissPGDay 2019.

 

Cet article #SwissPGDay is a place where you can meet developers of the PostgreSQL community est apparu en premier sur Blog dbi services.

DataGuard and Transparent Data Encryption

Fri, 2018-06-29 09:45

Setting up a DatagGard environment for a database with Transparent Data Encryption requires some tasks concerning the encryption keys. Otherwise the steps are the same than for an environment without TDE.
In this blog we will present the tasks we have to do on both primary and standby servers for the keys. We will not describe the procedure to build the standby database. We will just talk about tasks for the wallet and we will verify that data for encrypted tables are being replicated.
We are using oracle 12.2 and a non-container database.
Tasks on primary side
First on the primary server we have to configure the keystore location. This will be done by updating the sqlnet.ora with the directory whch will contain the keys.

[oracle@primaserver ~]$mkdir /u01/app/wallet

[oracle@primaserver admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

# For TDE
ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=file)
   (METHOD_DATA=
    (DIRECTORY=/u01/app/wallet)))
[oracle@primaserver admin]$

After on the primary we have to create the keystore.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/wallet' identified by root ;

keystore altered.

Next we have to open the keystore before creating the master key

SQL> ADMINISTER KEY MANAGEMENT set KEYSTORE open   identified by root ;

keystore altered.

And then we can create the master key.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY root WITH BACKUP;

keystore altered.

The wallet should be open before we can access to encrypted objects. So every time the database starts up, we have to manually open the wallet. To avoid this we can just create an auto_login wallet which will automatically opened at each database startup.

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/wallet' identified by root;

keystore altered.

Tasks on standby side
On the standby side we just have to copy files in the wallet and to update the sqlnet.ora file.

[oracle@primaserver wallet]$ pwd
/u01/app/wallet
[oracle@primaserver wallet]$ ls
cwallet.sso  ewallet_2018062707462646.p12  ewallet.p12
[oracle@primaserver wallet]$ scp * standserver1:$PWD
oracle@standserver1's password:
cwallet.sso                                   100% 3891     3.8KB/s   00:00
ewallet_2018062707462646.p12                  100% 2400     2.3KB/s   00:00
ewallet.p12                                   

And that’s all. We can now configure our standby database. Below our configuration

DGMGRL> show configuration;

Configuration - DGTDE

  Protection Mode: MaxPerformance
  Members:
  DGTDE_SITE1 - Primary database
    DGTDE_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 1 second ago)

DGMGRL>

Now let’s verify that encrypted data are being replicated. We have a table with an encrypted column

SQL> show user
USER is "SCOTT"
SQL> desc TEST_COL_ENC
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 DESIGNATION                                        VARCHAR2(30) ENCRYPT

SQL> select * from TEST_COL_ENC;

        ID DESIGNATION
---------- ------------------------------
         1 toto
         2 tito
         3 tata

SQL>

And let’s insert some data form the primary

SQL> insert into TEST_COL_ENC values (4,'titi');

1 row created.

SQL> insert into TEST_COL_ENC values (5,'teti');

1 row created.

SQL> commit;

Commit complete.

SQL>

From the standby let’s query the table

SQL> select db_unique_name,open_mode from v$database;

DB_UNIQUE_NAME                 OPEN_MODE
------------------------------ --------------------
DGTDE_SITE2                    READ ONLY WITH APPLY

SQL> select * from scott.TEST_COL_ENC;

        ID DESIGNATION
---------- ------------------------------
         4 titi
         5 teti
         1 toto
         2 tito
         3 tata

SQL>

To finish we will remind following notes about DataGuard and TDE (Oracle Documentation)

The database encryption wallet on a physical standby database must be replaced with a fresh copy of the database encryption wallet from the primary database whenever the TDE master encryption key is reset on the primary database.

For online tablespaces and databases, as of Oracle Database 12c Release 2 (12.2.0.1), you can encrypt, decrypt, and re-key both new and existing tablespaces, and existing databases within an Oracle Data Guard environment. This tasks will be automatically performed on the standby once done on the primary. Note that these online tasks cannot be done directly on the standby side.

In an offline conversion, the encryption or decryption must be performed manually on both the primary and standby. An offline conversion affects the data files on the particular primary or standby database only. Both the primary and physical standby should be kept at the same state. You can minimize downtime by encrypting (or decrypting) the tablespaces on the standby first, switching over to the primary, and then encrypting (or decrypting) the tablespaces on the primary.

 

Cet article DataGuard and Transparent Data Encryption est apparu en premier sur Blog dbi services.

PGIO, PG_STAT_ACTIVITY and PG_STAT_STATEMENTS

Thu, 2018-06-28 01:22

For an Oracle DBA, we are used to join active sessions (from V$SESSION where status=’ACTIVE) with active statements (from V$SQL where users_executing>0) on the SQL_ID. V$ACTIVE_SESSION_HISTORY also displays the TOP_LEVEL_SQL_ID to get the entrypoint of the usercall if we need it. With Postgres it is a bit more difficult because it seems that PG_STAT_ACTIVITY do not show the active statement but only the top-level one. But pg_stat_statement collects statistics for the final statements.

Here is an example where I’ve run pgio (the SLOB method for Postgres by Kevin Closson) in a Bitnami Postgres Compute service on the Oracle Cloud. pgio runs all statements from a PL/pgSQL function MYPGIO. This function runs the SELECT statements and some UPDATE statements depending on UPDATE_PCT. Most of the time is spend in those statements and very few in PL/pgSQL itself, which is the goal of pgio – measuring logical and physical I/O without the noise of other application components.

I have added the pg_stat_statements extension by setting the following in postgresql.conf and installing postgresql10-contrib

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

I’ve reset the statistics before running pgio:

select pg_stat_reset();
select pg_stat_statements_reset();

The I’ve run the runit.sh with 4 schemas and 20 threads.

pg_stat_activity

Here is what I can see in PG_STAT_ACTIVITY when it is running:

postgres=# select substr(query,1,100) query,backend_type,application_name,query_start,wait_event_type,wait_event,state from pg_stat_activity where datid=17021;
 
query | backend_type | application_name | query_start | wait_event_type | wait_event | state
------------------------------------------------------------------------------------------------------+-------------------+------------------------+-------------------------------+-----------------+---------------+--------
select query,backend_type,application_name,query_start,wait_event_type,wait_event,state from pg_stat | client backend | PostgreSQL JDBC Driver | 2018-06-27 20:15:11.578023+00 | Client | ClientRead | idle
autovacuum: VACUUM ANALYZE public.pgio3 | autovacuum worker | | 2018-06-27 20:01:19.785971+00 | | | active
autovacuum: VACUUM ANALYZE public.pgio4 | autovacuum worker | | 2018-06-27 20:02:19.811009+00 | | | active
autovacuum: VACUUM ANALYZE public.pgio1 | autovacuum worker | | 2018-06-27 20:08:19.864763+00 | | | active
SELECT * FROM mypgio('pgio1', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.915761+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio1', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.925117+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio1', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.934903+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio2', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.931038+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio1', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.9269+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio1', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.923288+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio2', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.95252+00 | LWLock | WALWriteLock | active
SELECT * FROM mypgio('pgio3', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.96237+00 | | | active
SELECT * FROM mypgio('pgio1', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.951347+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio1', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.962725+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio4', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.985567+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio1', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.98943+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio2', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.976483+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio4', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:56.032111+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio4', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:55.998013+00 | Lock | transactionid | active
SELECT * FROM mypgio('pgio4', 10, 300, 131072, 255, 8); | client backend | psql.bin | 2018-06-27 20:14:56.043566+00 | Lock | transactionid | active
(20 rows)

My client sessions are active on the MYPGIO function. This is the top-level statement which runs some SELECT and UPDATE and there is a very small chance to get samples with the session active on the controller procedural code rather than one of those statements. However, that’s the only thing I can see here.

pg_stat_statements

Now looking at PG_STAT_STATEMENTS:

postgres=# create extension pg_stat_statements;
CREATE EXTENSION
 
postgres=# select substr(query,1,100) query,calls,total_time/1e6 seconds,total_time/calls/1e6 "sec./call",rows from pg_stat_statements where dbid=17021 order by total_time desc;
query | calls | seconds | sec./call | rows
------------------------------------------------------------------------------------------------------+-------+---------------------+----------------------+-------
UPDATE pgio1 SET scratch = scratch + $1 WHERE mykey BETWEEN $2 AND $3 | 2081 | 0.583796520366999 | 0.000280536530690533 | 18729
UPDATE pgio4 SET scratch = scratch + $1 WHERE mykey BETWEEN $2 AND $3 | 2076 | 0.565067778457 | 0.000272190644728805 | 18684
UPDATE pgio2 SET scratch = scratch + $1 WHERE mykey BETWEEN $2 AND $3 | 2229 | 0.412313896512 | 0.000184977073356662 | 20061
SELECT sum(scratch) FROM pgio3 WHERE mykey BETWEEN $1 AND $2 | 19199 | 0.317597650247998 | 1.65424058673888e-05 | 19199
UPDATE pgio3 SET scratch = scratch + $1 WHERE mykey BETWEEN $2 AND $3 | 2202 | 0.304893281095001 | 0.000138461980515441 | 19818
SELECT sum(scratch) FROM pgio2 WHERE mykey BETWEEN $1 AND $2 | 19500 | 0.29686205481 | 1.52236951184615e-05 | 19500
SELECT sum(scratch) FROM pgio1 WHERE mykey BETWEEN $1 AND $2 | 17873 | 0.285481468151 | 1.59727783892464e-05 | 17873
SELECT sum(scratch) FROM pgio4 WHERE mykey BETWEEN $1 AND $2 | 17889 | 0.273855678533 | 1.53086074421711e-05 | 17889
SELECT pgio_get_random_number($6, v_scale - v_select_batch_size) | 83129 | 0.00134782461999997 | 1.62136513130192e-08 | 83129
select blks_read from pg_stat_database where datname = $1 | 1 | 1.0456142e-05 | 1.0456142e-05 | 1
create table pgio_audit_table ( c1 numeric , c2 numeric, c3 numeric , c4 numeric, c5 numeric, c6 | 1 | 7.636071e-06 | 7.636071e-06 | 0
CREATE OR REPLACE FUNCTION mypgio( +| 1 | 6.425742e-06 | 6.425742e-06 | 0
v_mytab varchar, +| | | |
v_pctupd int, +| | | |
v_runtime_secs bigint, +| | | |
v_scal | | | |
select substr(query,$1,$2) query,calls,total_time/$3 seconds,total_time/calls/$4 "sec./call",rows fr | 9 | 5.017752e-06 | 5.57528e-07 | 139
CREATE OR REPLACE FUNCTION pgio_get_random_number(BIGINT, BIGINT) RETURNS BIGINT AS $$ +| 1 | 4.217947e-06 | 4.217947e-06 | 0
DECLARE +| | | |
v | | | |
drop table pgio_audit_table | 1 | 3.337471e-06 | 3.337471e-06 | 0
DROP TYPE pgio_return CASCADE | 1 | 2.451694e-06 | 2.451694e-06 | 0
CREATE TYPE pgio_return AS ( +| 1 | 1.746512e-06 | 1.746512e-06 | 0
mypid int, +| | | |
loop_iterations bigint , +| | | |
sql_selects bigint, +| | | |
sql_updates | | | |
select query,backend_type,application_name,query_start,wait_event_type,wait_event,state from pg_stat | 3 | 1.312499e-06 | 4.37499666666667e-07 | 172
SELECT pg_backend_pid() | 80 | 6.10539e-07 | 7.6317375e-09 | 80
select pg_stat_statements_reset() | 1 | 1.8114e-07 | 1.8114e-07 | 1
select datname, blks_hit, blks_read,tup_returned,tup_fetched,tup_updated from pg_stat_database where | 1 | 1.60161e-07 | 1.60161e-07 | 1
select blks_hit from pg_stat_database where datname = $1 | 1 | 1.48597e-07 | 1.48597e-07 | 1
select pg_stat_reset() | 1 | 5.3776e-08 | 5.3776e-08 | 1
SET application_name = 'PostgreSQL JDBC Driver' | 2 | 5.2967e-08 | 2.64835e-08 | 0
SET extra_float_digits = 3 | 2 | 3.7514e-08 | 1.8757e-08 | 0
show shared_buffers--quiet | 1 | 2.1151e-08 | 2.1151e-08 | 0
(26 rows)

Here I can see that the main activity is on UPDATE and SELECT. There’s even no mention of the MYPGIO function except for its creation. I don’t even see the calls for it. We can see the same graphically from Orachrome Lighty for Postgres where only the top-level statement is displayed:
CapturePGIO

This makes it very difficult to match those two views. From PG_STAT_ACTIVITY I know that I spend time on MYPGIO function call and its system activity (here lot of locks waiting for vacuum sessions and some latch contention on WAL). We have sampling information, but nothing about database time and read/write measures. From PG_STAT_STATEMENT we have all measures, but with no link to the sampling activity, and then no link with the wait events. Coming from Oracle databases, the most important in Oracle tuning is the DB time which can be detailed from the system point of view (wait events, CPU, I/O) and from the user point of view (time model, SQL statements, Segments). And it can be a cumulative measure (each session measuring the time in a call or an operation) or a sampling one (counting the sessions active and on which operation). And ASH makes the link between all those dimensions. With this, we can directly address the user response time with our actions on the system, digging from the end-user perceived problem to the internal application or system root cause.

With current Postgres statistics, it seems that we can look at SQL statements on one side and at the system on the other side. But can we ensure that we really address the user problems? Especially when calling the statements from a function, which is the optimized way to avoid useless roundtrips and context switches? Postgres is evolving a lot from the community. I hope that more and more information will be available in PG_STAT_ACTIVITY to be sampled and link all dimensions related to the system and the users. Like with any analytic query, we need all dimensions at the same level of granularity.

 

Cet article PGIO, PG_STAT_ACTIVITY and PG_STAT_STATEMENTS est apparu en premier sur Blog dbi services.

Lighty for PostgreSQL

Wed, 2018-06-27 15:00

If you follow this blog, you should know how I like Orachrome Lighty for Oracle, for its efficiency to monitor database performance statistics. Today Orachrome released the beta version of Lighty for Postgres: https://orachrome.com/news/la-beta-de-lighty-for-postgresql-est-ouverte/
The Cloud is perfect to do short tests with more resources than my laptop, especially the predictability of performance, then I started a Bitnami Postgres Compute service on the Oracle Cloud and did some tests with pgbench and pgio.

The installation is easy:

  • unzip the front-end
  • add the licence key
  • install the sampling job in the server

In the beta I tested, the sampling job was running from cronjob in Linux and a service on Windows but as far as I know this will run as a service on Linux as well.
The sampling service takes a sample of pg_stat_activity every 10 seconds, and gathers more details about sessions and statements. It stores its data in a ‘lighty’ database created by the initialization script (you provide the tablespace name that you should create before) The overhead is very small.

In my Oracle Cloud Bitnami environment I’ve added the following to the script called by crontab:

export PGPASSWORD=myPostgresPassword
export PATH=$PATH:/opt/bitnami/postgresql/bin

CapturePGL001If you look at this ‘lighty’ database with the tool itself you may think that the overhead is important because you will see one active session average. This is just a side effect of sampling. At the time of sampling (every 10 seconds) this job will always be seen as active, which is right and which is the reason for the one active session. What you don’t see is that this job is inactive for 10 seconds in-between. And you don’t see this inactivity because you have no samples then. So, the one session active on average is just the consequence of the perfect synchronization which is specific to the tool looking at its own activity.
In my opinion, we should just ignore this activity and there’s an option to check “Exclude Lighty proc” to hide this from the ‘all databases’ view.

The main screen is the Activity Viewer where you can see graphically the samples from the postgres waits class and CPU as well as some details on the top level SQL statements, the wait events, the sessions,… We have to enable the pg_stat_statements extension for that:

# to add in postgresql.conf after installing postgresql10-contrib
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Here is how it looks like:
DgrxdisXcAErgZv

Another important screen is the ‘real time’ performance views displayed as graphs, with information from the database as well as from the host operating system. There’s one non-defautl setting to get the full picture:

# to add in postgresql.conf to get Block R/W time
track_io_timing=on

Here is how it looks like:
CapturePG020202

As with Lighty for Oracle, there’s also a bunch of reports and a multi-database view which shows the main performance indicators (transactions per seconds, Block I/O, Tuples in/out, CPU and OS load average) for several databases on the same screen.

Of course, if you compare it with Lighty for Oracle, you will see some limitations. Not because of the tool but because of the statistics provided. Oracle has detailed wait events. They are more limited in postgres. Oracle has all information about what the current sessions are doing, in V$SESSION, and have even more information on V$ACTIVE_SESSION_HISTORY if you have Diagnostic Pack. In Postgres, PG_STAT_ACTIVITY is much more limited. And Lighty shows only what is available by default (and with the pg_stat_statements extension).

 

Cet article Lighty for PostgreSQL est apparu en premier sur Blog dbi services.

Oracle Virtual Private Database

Tue, 2018-06-26 13:41

Oracle Virtual Private Database (VPD) enables you to dynamically add a WHERE clause in any SQL statement that a user executes. The WHERE clause filters the data the user is allowed to access, based on the identity of a user.
This feature restricts row and column level data access by creating a policy that enforces a WHERE clause for all SQL statements that query the database. The WHERE clause allows only users whose identity passes the security policy, and hence, have access to the data that you want to protect. You create and manage the VPD policy at the database table or view level, which means that you do not modify the applications that access the database.
In a multitenant environment, each Virtual Private Database policy applies only to the current pluggable database (PDB).
In this blog we are going to use this functionality in a pluggable database 12.1
We are going to setup a policy for the table emp of scott so that each user should be able to see only his own data. We present below contents of table emp of scott

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

The first step is to create two users jones and miller for example and let’s give them the required privileges

SQL> create user jones  identified by root default tablespace users ;

User created.

SQL> grant select on scott.emp to jones;

Grant succeeded.

SQL>


SQL> create user miller identified by root default tablespace users ;

User created.

SQL> grant select on scott.emp to miller;

Grant succeeded.

SQL> grant create session to jones;

Grant succeeded.

SQL> grant create session to miller;

Grant succeeded.

SQL>

Once users created, we have to create the function we will use to create the VPD policy

CREATE OR REPLACE FUNCTION emp_policy
     (user_name IN VARCHAR2,tab_name in varchar2)
    RETURN VARCHAR2 IS
      BEGIN
       RETURN 'upper(ename) = SYS_CONTEXT(''USERENV'', ''CURRENT_USER'')';
      END;
      /

Function created.

With this function Oracle will add for each query on the table emp the following clause

Where  upper(ename) = SYS_CONTEXT(’USERENV’, ‘CURRENT_USER’)

when the VPD policy is set
Now let’s create the VPD policy

SQL> show user
USER is "SYSTEM"
SQL>
BEGIN
      SYS.DBMS_RLS.ADD_POLICY(
        object_schema   => 'scott',
        object_name     => 'emp',
        policy_name     => 'emp_vpd_policy',
        function_schema => 'system',
        policy_function => 'emp_policy',
        statement_types => 'select'
     );
  END;
 11    /

PL/SQL procedure successfully completed.

And then we can test that each user can see only his own data

With user miller

[oracle@em13c wallet]$ rlwrap sqlplus miller/root@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 26 17:39:18 2018

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

Last Successful login time: Tue Jun 26 2018 17:34:57 +02:00

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> select * from scott.emp ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

With user jones

[oracle@em13c wallet]$ rlwrap sqlplus jones/root@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 26 17:39:25 2018

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

Last Successful login time: Tue Jun 26 2018 17:34:33 +02:00

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> select * from scott.emp ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

SQL>

But there is a problem now, because VPD is enforced by default for all users except those connected with SYSDBA privilege. This mean that if we connect for example with SYSTEM, we will not see any data

SQL> show user
USER is "SYSTEM"
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> select * from scott.emp;

no rows selected

SQL>

To exempt SYSTEM to VPD we have to grant him the EXEMPT ACCESS POLICY

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> grant EXEMPT ACCESS POLICY to system;

Grant succeeded.

SQL>

And then login again whith SYSTEM

SQL> show user
USER is "SYSTEM"
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10

14 rows selected.
SQL>
 

Cet article Oracle Virtual Private Database est apparu en premier sur Blog dbi services.

Kernel panic – not syncing: Out of memory and no killable processes

Tue, 2018-06-26 10:37

This is a quick post to give a solution (maybe not the best one as this was just quick troubleshooting) if, at boot, you see something like:
Trying to allocate 1041 pages for VMLINUZ [Linux=EFI, setup=0x111f, size=0x41108d01]
and then:
Kernel panic - not syncing: Out of memory and no killable processes
Pid: 1228 comm: kworker Not tainted 3.8.13-118.17.4.el6uek.x86_64 #2


IMG_E5543
If you do not see the messages, then you may need to remove the ‘quiet’ option of kernel and replace it by ‘nosplash’ – this is done from grub.

So, there’s not enough memory to start the kernel. I got this from a server that had been shut down to be moved to another data center, so at first, I checked that the RAM was ok. Actually, the problem was with the configuration of hugepages (vm.nr_hugepages in /etc/sysctl.conf) which was allocating nearly the whole RAM. Probably because the setting has been erroneously changed without checking /proc/meminfo.

So, without any clue about what the problem was, I started by forcing the kernel to use 4GB (instead of getting it from hardware info). This is done by pressing any key on the GRUB menu, ‘e’ to edit, add the ‘mem=4g’. Then the boot was ok. Of course, if the server tries to start the Oracle instances, the systems starts to swap so better change also the runlevel.

This is sufficient to check the huge pages allocation and ensure that you leave enough memory for at least the kernel (but also consider also the system, the PGA, …). So don’t forget that the mem= option, usually there to give a maximum limit, may be useful also to guarantee a minimum of RAM for the kernel.

 

Cet article Kernel panic – not syncing: Out of memory and no killable processes est apparu en premier sur Blog dbi services.

18c: No Active Data Guard required (and detected) when only CDB$ROOT and PDB$SEED are opened in read-only

Sun, 2018-06-24 14:32

When you clone a PDB on the primary CDB, you want that the same is automatically done on the standby ones. Unfortunately, for this operation, the CDB must be opened read-only. In 12.1 even the PDB needed to be opened read-only (Ludovico Caldara reported this in his blog). This, as far as I know, was fixed in 12.2 where MRP reads the files without the need to have the PDB opened. But another problem comes with online cloning, as reported by Alain Fuhrer in his blog, where the CDB needs to be opened read-only with real-time apply. This again requires the Active Data Guard option, which is then mandatory to use all power from the multitenant option. This brings the use of multitenant from x1.36 the price for this option to x1.61 for both, on the primary and the standby servers. Hopefully, this has been addressed in 18c where you don’t need the Active Data Guard to have the CDB opened in read-only with real-time apply, as long as you ensure that the user PDB are always closed when apply is on.

This test was done on the Oracle Cloud DBaaS because it is very easy to provision a Data Guard configuration. It is 18.1 because this is what is created and the patch apply for 18.2 is not so easy. But behaviour the same in 18.2

CDB in MOUNT do not requires ADG

The standby is not opened:

SQL> select dbid,name,open_mode,database_role from v$database;
 
DBID NAME OPEN_MODE DATABASE_ROLE
---------- --------- -------------------- ----------------
1507386248 ORCL MOUNTED PHYSICAL STANDBY
 
SQL> show pdbs
 
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
4 PDB1 MOUNTED

In the primary, we see that the standby destination is in real time apply, but not opened:

SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status;
 
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL MOUNTED-STANDBY MANAGED REAL TIME APPLY
3 LOG_ARCHIVE_DEST_3 INACTIVE LOCAL UNKNOWN IDLE
4 LOG_ARCHIVE_DEST_4 INACTIVE LOCAL UNKNOWN IDLE

This is the information that is logged into DBA_FEATURE_USAGE_STATISTICS. Here is the query that counts the usage of real-time query which is one of the features that require ADG option:

SQL> select count(*) from v$archive_dest_status where status = 'VALID' and recovery_mode like 'MANAGED%QUERY' and database_mode = 'OPEN_READ-ONLY';
 
COUNT(*)
----------
0

Here we have no ADG usage recorded because the database is not opened read-only. But if you try some PDB cloning on the primary you will quickly encounter the problems: the PDB on the standby has no datafiles.

CDB in READ and PDB closed do not require ADG in 18c

I’ll now open the standby read-only:

SQL> alter database open;
Database altered.
 
SQL> show pdbs;
 
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 READ ONLY NO

Unfortunately, this has also opened the PDB because the saved state was OPEN for the primary database and we cannot manage that properly on the standby (except with Grid Infrastructure or Oracle Restart or custom scripts or database triggers). I close it quickly before the ADG usage is recorded:

SQL> alter pluggable database pdb1 close;
Pluggable database altered.

So, my standby is opened read-only with real-time apply:

SQL> select dbid,name,open_mode,database_role from v$database;
 
DBID NAME OPEN_MODE DATABASE_ROLE
---------- --------- -------------------- ----------------
1507386248 ORCL READ ONLY WITH APPLY PHYSICAL STANDBY

but all the user PDBs are not opened:

SQL> show pdbs
 
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 MOUNTED

Here is what we see from the primary about this archive destination:

SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status;
 
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL OPEN_READ-ONLY MANAGED REAL TIME APPLY
3 LOG_ARCHIVE_DEST_3 INACTIVE LOCAL UNKNOWN IDLE
4 LOG_ARCHIVE_DEST_4 INACTIVE LOCAL UNKNOWN IDLE

This in 12.2 would have recorded the usage of real-time query usage because the standby is opened for queries and we are in managed recovery mode. Here was the 12.2 query to detect it (you can see it in catfusrg.sql):

SQL> select count(*) from v$archive_dest_status where status = 'VALID' and recovery_mode like 'MANAGED%' and database_mode = 'OPEN_READ-ONLY';
 
COUNT(*)
----------
1

But the pattern has been changed in 18c to include ‘QUERY':

SQL> select count(*) from v$archive_dest_status where status = 'VALID' and recovery_mode like 'MANAGED%QUERY' and database_mode = 'OPEN_READ-ONLY';
 
COUNT(*)
----------
0

and this ‘QUERY’ word is added only when some user pluggable databases are not in MOUNT but opened for queries.

I check quickly that a PDB clone on the primary:

SQL> create pluggable database PDB2 from PDB1 keystore identified by "Ach1z0#d";
Pluggable database created.
 
SQL> alter pluggable database PDB2 open;
Pluggable database altered.

has the clone created on the standby, in closed mode:

SQL> show pdbs
 
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 MOUNTED
5 PDB2 MOUNTED

So, finally, we have a good way to use multitenant features without the need for Active Data Guard. This is good for DBaaS where we want easy cloning, protect their availability with Data Guard, but with no need to query them on the standby.

CDB in READ and PDB opened requires ADG in 18c

Now, if I open the PDB:

SQL> select dbid,name,open_mode,database_role from v$database;
 
DBID NAME OPEN_MODE DATABASE_ROLE
---------- --------- -------------------- ----------------
1507386248 ORCL READ ONLY WITH APPLY PHYSICAL STANDBY
 
SQL> alter pluggable database pdb1 open;
 
Pluggable database PDB1 altered.
 
SQL> show pdbs
 
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 READ ONLY NO

From the primary, the recovery mode for this standby mentions ‘WITH QUERY’ for the managed real time apply:

SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status;
 
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL OPEN_READ-ONLY MANAGED REAL TIME APPLY WITH QUERY
3 LOG_ARCHIVE_DEST_3 INACTIVE LOCAL UNKNOWN IDLE
4 LOG_ARCHIVE_DEST_4 INACTIVE LOCAL UNKNOWN IDLE

And only in this case the ADG usage is recorded:

SQL> select count(*) from v$archive_dest_status where status = 'VALID' and recovery_mode like 'MANAGED%QUERY' and database_mode = 'OPEN_READ-ONLY';
 
COUNT(*)
----------
1

The proper way to open without activating ADG

In the previous test, I had the PDB opened for a short time when I opened the CDB because of the saved state. If you want to avoid this, the proper way is to ensure that the apply is off when you open it so that you do not have, at the same time, a PDB opened and real time apply.

I’m back to the state where the standby is in mount and apply on:

SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
 
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL MOUNTED-STANDBY MANAGED REAL TIME APPLY

I stop the apply in the standby:

DGMGRL> edit database "ORCL_02" set state=apply-off;
Succeeded.

I check that the primary sees that apply is off:

SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
 
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL MOUNTED-STANDBY IDLE

I open the standby CDB read-only and ensures that all PDB are closed:

SQL> alter database open;
Database altered.
 
SQL> alter pluggable database all close;
Pluggable database altered.
 
SQL> show pdbs
 
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 MOUNTED

The primary sees the open state:

SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
 
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL OPEN_READ-ONLY IDLE

I can now restart the apply:

DGMGRL> edit database "ORCL_02" set state=apply-on;
Succeeded.

Then the primary detects the managed recovery

SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
 
DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL OPEN_READ-ONLY MANAGED

Then once the gap is resolved, we are in real time apply:

SQL> select dest_id,dest_name,status,type,database_mode,recovery_mode from v$archive_dest_status where status='VALID';
  DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE
---------- -------------------- --------- ---------------- --------------- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE
2 LOG_ARCHIVE_DEST_2 VALID PHYSICAL OPEN_READ-ONLY MANAGED REAL TIME APPLY

But there is no mention of ‘WITH QUERY’ thanks to the closed PDBs, which means that no ADG usage is recorded. In summary, 18c has added two possible values for RECOVERY_MODE: ‘MANAGED WITH QUERY’ and ‘MANAGED REAL TIME APPLY WITH QUERY’, and those ones, in multitenant, are for standby CDB opened read-only when at least one user PDB is also opened read-only.

For DBaaS, you probably run with Grid Infrastructure (because Oracle Restart do not allow databases on ACFS). For single-tenant, this new feature can solve the problem of activating ADG option by mistake, at least if we have good control of PDB states depending on the role.

 

Cet article 18c: No Active Data Guard required (and detected) when only CDB$ROOT and PDB$SEED are opened in read-only est apparu en premier sur Blog dbi services.

Introduction to databases for {Power.Coders} with MySQL

Fri, 2018-06-22 15:04
    PC_IMG_5528
    This week I took some days off to do something related to my job but a bit different. I’ve given a course on Databases and SQL. But not for my usual customers. And not with the database I know the best. So, it is still in a domain that I know, but out of my comfort zone. And this is something that we should do more often because it gives a little apprehension and a big satisfaction.

    The little apprehension is because there were a lot of unknown parameters for me. I taught to students from Power.Coders, a coding academy for refugees. 18 young people with a very different background. Some already knew how to code. Some did some front-end stuff and website design in the past but had no idea about what is a server. Some others are doing all that for the first time, and have to learn what is a program. But there’s one thing that is common to everybody here: all are motivated to learn, understand, and acquire the knowledge and experience to start a career in IT. This is the good energy that makes everything possible.

    The big satisfaction is because, with everybody doing their best, things works and everyone gains confidence. It is out of the comfort zone that you can get your best, and that is for the students as well as the teacher and coaches. Because I wanted the course to stay consistent with what they learned in the curriculum, I did the database examples and exercises on MySQL and from PHP code. I never did that so I had to study as well. The big advantage I have, from experience, is that I know where to search on the internet. One of the students told me “don’t tell me to google for it, that’s like swimming in the ocean!”. When you are not used to it ‘googling’ for solutions is not easy. Experienced people do not always consider that and they answer in forums with a rude “RTFM” or “LMGTFY”. I’ve never felt obliged to answer on forums but when I do it, it is not to argue about the OP and his question, and whether he did his own research before. If I choose to answer, then my goal is to explain as clearly as possible. And I do not fear to repeat myself because the more I explain and the better understanding I have about what I explain. I remember my first answers on the dba-village forum. Some questions were recurrent. And each time I tried to answer with a shorter and clearer explanation.

    Google Slides

    PC_IMG_5523I’ve prepared slides and exercises for 3 days and here I share the content (however, there were a lot of whiteboard explanations so the slides may not be sufficient). I did the pages were with Google Sites and the presentation with Google Slides. Both of them were, again, new things for me, out of my .ppt comfort zone. It went very well. For correct presenter experience, I installed the “Google Slides Auto Resize Speaker Notes” Google Chrome extension. One things annoys me with Google Slides: readers cannot copy/paste from the slides. Please, comment here if you have a solution. My workaround was to copy the code to the presenter’s notes and tell the students to open them (with ‘s’ key) and copy from there. But I don’t like to duplicate the code.

    – Day 1 on Data Structures:
    Data Modeling, YAML, XML, JSON, CSV and introduction to relation tables.

    Exercise: load some OpenFlight data into MySQL

    – Day 2 on Introduction to Databases:
    RDBMS, create and load tables, normalization

    Exercise: a PHP page to query a simple table

    – Day 3 on Introduction to SQL:
    SELECT, INSERT, DELETE, UPDATE, ACID and transactions

    Exercise: a PHP page to list Flights from multi-creteria form

    In addition to the course, I also did some coaching for their PHP exercises. I discovered this language (which I do not like – meaningless error messages, improbable implicit conversion,…). But at least we were able to make some concepts more clear: what is a web server, sessions, cookies, access to the database… And the method is important. How to approach a code that doesn’t work, where nothing is displayed: change the connection parameters to wrong ones to see if we go to this part of code, add explicitly a syntax error in the SQL statement to see if errors are correctly trapped, echo some variables to see if they are set. Before learning magic IDEs, we must put the basics that will help everywhere. The main message is: you are never stuck with an error. There is always a possibility to trace more. And when you have all details, you can focus your google search better.

    Thanks

    Big thanks to SQLFiddle where it is easy to do some SQL without installing anything. However, being 20 people on a small Wi-Fi, using local resources is preferable. And we installed MAMP (see here how I discovered it and had to fix a bug at the same time). Big thanks to Chris Saxon ‘Database for Developers’ videos which will help the students to review all the concepts in an entertaining way. Thanks to w3schools for the easy learning content.

    Oh, and thanks to facebook sponsoring-privacy-intrusive-algorithms! Because this is how I heard about PowerCoders. For the first time of my life, I clicked on a sponsored link on a social media. This was for the WeMakeIt crowdfunding project for this powercoders curriculum in Lausanne. I’ve read about the project. I watched the video and that’s how I wanted to participate in this project. You should watch this Christian Hirsig TED talk as well. At a time where everybody is talking about autonomous self-driven cars, his accomplishment was to move from completely powerless to be back in the driver’s seat…

    IMG_5531
    And of course thanks to Powercoders organizers, students, teachers, coaches, mentors and the companies who propose internships to complete the curriculum (I was happy, and proud of my employer, when dbi-services was in immediately).
    Teaching to motivated people who want to learn as much as possible is a great experience, and not all days are like this in the professional life. And explaining topics that are aside of my comfort zone is lot of work, but also a rewarding experience. In this world where technology goes faster and faster, showing the approach and the method to adapt to new topics gives a lot of self-confidence.

     

    Cet article Introduction to databases for {Power.Coders} with MySQL est apparu en premier sur Blog dbi services.

MySQL 8.0 – Roles are finally there

Fri, 2018-06-22 05:29

Roles have been existing on many RDBMS for a long time by now. Starting from version 8.0, this functionality is finally there for MySQL.
The most important advantage is to define only once a role that includes a “set of permissions”, then assign it to each user, avoiding wasting time declaring them individually.

In MySQL, a role can be created like a user, but without the “identified by” clause and without login:

mysqld2-(root@localhost) [(none)]> CREATE ROLE 'r_sakila_read';
Query OK, 0 rows affected (0.03 sec)
mysqld2-(root@localhost) [(none)]> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| r_sakila_read    | %         |                                                                        |
| multi_admin      | localhost | $A$005$E?D/>efE+Rt12omzr.78VnfR3kxj8KLG.aP84gdPMxW7A/7uG3D80B          |
| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |
| root             | localhost | {u]E/m)qyn3YRk2u.JKdxj9/6Krd8uqNtHRzKA38cG5qyC3ts5                     |
+------------------+-----------+------------------------------------------------------------------------+

After that you can grant some privileges to this role, as you usually do for users:

mysqld2-(root@localhost) [(none)]> grant select on sakila.* to 'r_sakila_read';
Query OK, 0 rows affected (0.10 sec)
mysqld2-(root@localhost) [(none)]> show grants for r_sakila_read;
+---------------------------------------------------+
| Grants for r_sakila_read@%                        |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `r_sakila_read`@`%`         |
| GRANT SELECT ON `sakila`.* TO `r_sakila_read`@`%` |
+---------------------------------------------------+
2 rows in set (0.00 sec)

Now you can create your user:

mysqld2-(root@localhost) [(none)]> create user 'u_sakila1'@localhost identified by 'qwepoi123098';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

And yes, check your password policy because, starting from version 8.0, the new validate_password component replaces the old validate_password plugin and is now enabled by default and you don’t have to install it anymore.

mysqld2-(root@localhost) [(none)]> show variables like 'validate_password_%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
mysqld2-(root@localhost) [(none)]> create user 'u_sakila1'@localhost identified by 'QwePoi123098!';
Query OK, 0 rows affected (0.08 sec)

In my example I have by default a MEDIUM level for checking passwords which means “Length; numeric, lowercase/uppercase, and special characters” (I will better talk about validate_password component in an upcoming blog). Let’s go back to roles…

Grant the created role to your created user (as you usually grant a privilege):

mysqld2-(root@localhost) [(none)]> grant 'r_sakila_read' to 'u_sakila1'@localhost;
Query OK, 0 rows affected (0.01 sec)
mysqld2-(root@localhost) [(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)

At this point if you check privileges of your user through a USING clause, you will get information about the granted roles and also privileges associated with each role:

mysqld2-(root@localhost) [(none)]> show grants for 'u_sakila1'@localhost using 'r_sakila_read';
+-------------------------------------------------------+
| Grants for u_sakila1@localhost                        |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `u_sakila1`@`localhost`         |
| GRANT SELECT ON `sakila`.* TO `u_sakila1`@`localhost` |
| GRANT `r_sakila_read`@`%` TO `u_sakila1`@`localhost`  |
+-------------------------------------------------------+
3 rows in set (0.00 sec)

Now if you try to connect with your user and do a select of data on the database on which you have a read privilege, you will discover that something is still missing:

mysqld2-(root@localhost) [(none)]>  system mysql -u u_sakila1 -p
mysqld2-(u_sakila1@localhost) [(none)]> use sakila;
ERROR 1044 (42000): Access denied for user 'u_sakila1'@'localhost' to database 'sakila'
mysqld2-(u_sakila1@localhost) [(none)]> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

Why?
Because you have to define which roles will be active when the user authenticates. You you can do that by adding the “DEFAULT ROLE role” during the user creation (starting from version 8.0.3), or even later through the following statement:

mysqld2-(root@localhost) [(none)]> set default role r_sakila_read to 'u_sakila1'@localhost;
Query OK, 0 rows affected (0.08 sec)

Otherwise, starting from version 8.0.2, you can directly let the server activate by default all roles granted to each user, setting the activate_all_roles_on_login variable to ON:

mysqld2-(root@localhost) [(none)]> show variables like '%activate%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysqld2-(root@localhost) [(none)]> set global activate_all_roles_on_login=ON;
Query OK, 0 rows affected (0.00 sec)
mysqld2-(root@localhost) [(none)]> show variables like '%activate%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)

So if you check again, all works correctly:

mysqld2-(root@localhost) [mysql]> select * from role_edges;
+-----------+----------------+-----------+-----------+-------------------+
| FROM_HOST | FROM_USER      | TO_HOST   | TO_USER   | WITH_ADMIN_OPTION |
+-----------+----------------+-----------+-----------+-------------------+
| %         | r_sakila_read  | localhost | u_sakila1 | N                 |
+-----------+----------------+-----------+-----------+-------------------+
4 rows in set (0.00 sec)
mysqld2-(root@localhost) [(none)]>  system mysql -u u_sakila1 -p
mysqld2-(u_sakila1@localhost) [(none)]> use sakila
mysqld2-(u_sakila1@localhost) [sakila]> connect
Connection id:    29
Current database: sakila
mysqld2-(u_sakila1@localhost) [sakila]> select CURRENT_ROLE();
+---------------------+
| CURRENT_ROLE()      |
+---------------------+
| `r_sakila_read`@`%` |
+---------------------+
1 row in set (0.00 sec)

Enjoy your roles now! ;)

 

Cet article MySQL 8.0 – Roles are finally there est apparu en premier sur Blog dbi services.

What’s new in EDB EFM 3.1?

Fri, 2018-06-22 04:24

Beginning of this month EnterpriseDB announced a new version of its Failover Manager. Version 2.1 introduced controlled switchover operations, version 3.0 brought support for PostgreSQL 10 and now: What’s new in version 3.1? It might seem this is just a bugfix release but there is more and especially one enhancement I’ve waited for a long time.

As you might remember: When you stopped EFM (before version 3.1) the nodes.in file was always empty again. What we usually did is to create a backup of that file so we just could copy it back but this is somehow annoying. The current version comes with a new property in the efm.properties file to handle that better:

# When set to true, EFM will not rewrite the .nodes file whenever new nodes
# join or leave the cluster. This can help starting a cluster in the cases
# where it is expected for member addresses to be mostly static, and combined
# with 'auto.allow.hosts' makes startup easier when learning failover manager.
stable.nodes.file=true

When set to “true” the file will not be touched when you stop/restart EFM on a node:

root@:/etc/edb/efm/ [] cat efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
192.168.22.60:9998 192.168.22.61:9998 
root@:/etc/edb/efm/ [] systemctl stop efm-3.1.service
root@:/etc/edb/efm/ [] cat efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
192.168.22.60:9998 192.168.22.61:9998 
root@:/etc/edb/efm/ [] systemctl start efm-3.1.service
root@:/etc/edb/efm/ [] cat efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
192.168.22.60:9998 192.168.22.61:9998 

A small, but really nice improvement. At least with our deployments the amount of cluster nodes is rather static so this helps a lot. While this is a new property another property is gone:

root@:/etc/edb/efm/ [] grep efm.license efm.properties

This means you do not anymore need a license key to test EFM for more than 60 days, which is great as well. Another small improvement is that you now can see on which node the VIP is currently running on:

root@:/etc/edb/efm/ [] /usr/edb/efm/bin/efm cluster-status efm
Cluster Status: efm

	Agent Type  Address              Agent  DB       VIP
	-----------------------------------------------------------------------
	Master      192.168.22.60        UP     UP       192.168.22.63*
	Standby     192.168.22.61        UP     UP       192.168.22.63

Allowed node host list:
	192.168.22.60 192.168.22.61

Membership coordinator: 192.168.22.61

Standby priority host list:
	192.168.22.61

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.60        0/40006F0        
	Standby     192.168.22.61        0/40006F0        

	Standby database(s) in sync with master. It is safe to promote.

When it comes to the VIP there is another enhancement which is controlled by new property:

root@:/etc/edb/efm/ [] grep virtualIp.single efm.properties | tail -1
virtualIp.single=true

When this is set to “true” EFM will use the same address for the VIP after a failover on the new master. This was the default behavior before EFM 3.1. When you want to use another VIP on a new master you can now do that be switching that to false and provide a different VIP in the properties file on each node.

That’s the important ones for me. The full list is in the documentation.

 

Cet article What’s new in EDB EFM 3.1? est apparu en premier sur Blog dbi services.

utl_dbws causes ORA-29532 and bad_record_mac

Fri, 2018-06-22 03:27

After installing OJVM patch set update APR-2017 on a 11.2.0.4 database with PSU APR-2017 installed, first call of utl_dbws package was successful, but after a while utl_dbws calls failed always with ORA-29532 and bad_record_mac. All Java objects remained valid.
Also after trying procedures described in MOS document 2314363.1 utl_dbws worked first time, after that it always failed.
We could observe that after a while after restarting database m000 process ran and tried to recompile Java classes. When waiting till m000 finished utl_dbws always succeeded.
The m000 process start was caused by parameter setting JAVA_JIT_ENABLED to TRUE.

When setting JAVA_JIT_ENABLED to false, utl_dbws always worked fine. Probably locking of java classes by application prevented to recompile them properly.

 

Cet article utl_dbws causes ORA-29532 and bad_record_mac est apparu en premier sur Blog dbi services.

Dealing with automatic restart and SQL Docker containers

Wed, 2018-06-20 12:57

A couple of weeks ago, a customer asked me how to restart containers automatically after a reboot of the underlying host. In his context, it was not an insignificant question because some containers are concerned by SQL Server databases and he wanted to stay relaxed as long as possible even after a maintenance of the Linux host by sysadmins. The concerned (DEV) environment doesn’t include container orchestration like Swarm or Kubernetes.

blog 139 - 0 - banner

The interesting point is there are several ways to perform the job according to the context. Let’s say I was concerned by services outside Docker that are depend of the containerized database environment.

The first method is a purely sysadmin solution that includes systemd which is a Linux process manager that can be used to automatically restart services that fail with restarting policy values as no, on-success, on-failure, on-abnormal, on-watchdog, on-abort, or always. The latter fits well with my customer scenario.

Is there advantage to use this approach? Well, in my customer context some services outside docker are dependent of the SQL container and using systemd is a good way to control dependencies.

Below the service unit file used during my mission and I have to give credit to the SQL Server Customer Advisory team who published an example of this file included in their monitoring solution based on InfluxDB, Grafana and collectd. The template file includes unit specifiers that make it generic. I just had to change the name of the system unit file accordingly to which container I wanted to control.

[Unit]
Description=Docker Container %I
Requires=docker.service
After=docker.service

[Service]
TimeoutStartSec=0
Restart=always
ExecStart=/usr/bin/docker start -a %i
ExecStop=/usr/bin/docker stop -t 2 %i

[Install]
WantedBy=default.target

 

Let’s say I have one SQL Server container named sql. The next step will consist in copying the service template to /etc/systemd/system and changing the service name accordingly to the SQL container name. Thus, we may now benefit from the systemctl command capabilities

$ sudo cp ./service-template /etc/systemd/system/docker-container@sql.service
$ systemctl daemon-reload
$ sudo systemctl enable docker-container@sql

 

That’s it. I may get the status of my new service as following

$ sudo systemctl status docker-container@sql

 

blog 139 - 1 - systemctl status docker container

 

I can also stop and start my SQL docker container like this:

[clustadmin@docker3 ~]$ sudo systemctl stop docker-container@sql
[clustadmin@docker3 ~]$ docker ps -a
CONTAINER ID        IMAGE                                   COMMAND                  CREATED             STATUS                     PORTS               NAMES
9a8cad6f21f5        microsoft/mssql-server-linux:2017-CU7   "/opt/mssql/bin/sqls…"   About an hour ago   Exited (0) 7 seconds ago                       sql

[clustadmin@docker3 ~]$ sudo systemctl start docker-container@sql
[clustadmin@docker3 ~]$ docker ps
CONTAINER ID        IMAGE                                   COMMAND                  CREATED             STATUS              PORTS                    NAMES
9a8cad6f21f5        microsoft/mssql-server-linux:2017-CU7   "/opt/mssql/bin/sqls…"   About an hour ago   Up 5 seconds        0.0.0.0:1433->1433/tcp   sql

 

This method met my customer requirement but I found one drawback in a specific case when I stop my container from systemctl command and then I restart it by using docker start command. Thus the status is not reported correctly (Active = dead) and I have to run systemctl restart command against my container to go back to normal. I will probably update this post or to write another one after getting some information on this topic or just feel free to comments: I’m willing to hear about you!

 

The second method I also proposed to my customer for other SQL containers without any external dependencies was to rely on the Docker container restart policy capability. This is a powerful feature and very simple to implement with either docker run command or Dockerfile as follows:

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=P@$$w0rd1' -p 1433:1433 --restart=unless-stopped -d microsoft/mssql-server-linux:2017-CU7

 

Restart-policy values as Always and unless-stopped fit well with my customer scenario even if I prefer the latter option because it provides another level of control if you manually decide to stop the container for any reasons.

I will voluntary omit the third method that consist in installing systemd directly into the container because it is not recommended by Docker itself and not suitable with my customer case as well.

See you!

 

 

 

Cet article Dealing with automatic restart and SQL Docker containers est apparu en premier sur Blog dbi services.

Migrating from ASMLIB to ASMFD

Wed, 2018-06-20 12:33

Before Oracle 12.1 the methods used to configure ASM were
• udev
• asmlib
Oracle 12.1 comes with a new method called Oracle ASM Filter Driver (Oracle ASMFD).
In Oracle documentation we can find following:
Oracle ASM Filter Driver (Oracle ASMFD) is a kernel module that resides in the I/O path of the Oracle ASM disks. Oracle ASM uses the filter driver to validate write I/O requests to Oracle ASM disks.
The Oracle ASMFD simplifies the configuration and management of disk devices by eliminating the need to rebind disk devices used with Oracle ASM each time the system is restarted.
The Oracle ASM Filter Driver rejects any I/O requests that are invalid. This action eliminates accidental overwrites of Oracle ASM disks that would cause corruption in the disks and files within the disk group. For example, the Oracle ASM Filter Driver filters out all non-Oracle I/Os which could cause accidental overwrites.

In the following blog I am going to migrate from asmlib to asmfd. I am using a cluster 12.1 with 2 nodes.

Below we present our actual configuration.

[root@rac12a ~]# crsctl check cluster -all
**************************************************************
rac12a:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac12b:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@rac12a ~]#


[root@rac12a ~]# crsctl get cluster mode status
Cluster is running in "flex" mode
[root@rac12a ~]#

[root@rac12a ~]# ps -ef | grep pmon
grid      7217     1  0 11:20 ?        00:00:00 asm_pmon_+ASM1
grid      8070     1  0 11:21 ?        00:00:00 apx_pmon_+APX1
oracle    8721     1  0 11:22 ?        00:00:00 ora_pmon_mydb_1
root     14395  2404  0 11:32 pts/0    00:00:00 grep --color=auto pmon
[root@rac12a ~]#

First let’s get information about our ASM disks. We will use these outputs later to migrate the disks to ASMFD disks

[root@rac12a ~]# oracleasm listdisks | xargs oracleasm querydisk -p             
Disk "ASM_DATA" is a valid ASM disk
/dev/sdc1: LABEL="ASM_DATA" TYPE="oracleasm"
Disk "ASM_DIVERS" is a valid ASM disk
/dev/sdd1: LABEL="ASM_DIVERS" TYPE="oracleasm"
Disk "ASM_OCR1" is a valid ASM disk
/dev/sdg1: LABEL="ASM_OCR1" TYPE="oracleasm"
Disk "ASM_OCR2" is a valid ASM disk
/dev/sdi1: LABEL="ASM_OCR2" TYPE="oracleasm"
Disk "ASM_VOT1" is a valid ASM disk
/dev/sde1: LABEL="ASM_VOT1" TYPE="oracleasm"
Disk "ASM_VOT2" is a valid ASM disk
/dev/sdh1: LABEL="ASM_VOT2" TYPE="oracleasm"
Disk "ASM_VOT3" is a valid ASM disk
/dev/sdf1: LABEL="ASM_VOT3" TYPE="oracleasm"
[root@rac12a ~]#

To migrate to ASMFD, we first have to change the value of the parameter diskstring for the ASM instance. The actual value can be get by using

[grid@rac12a trace]$ asmcmd dsget
parameter:ORCL:*
profile:ORCL:*
[grid@rac12a trace]$

Let’s set the new value on both nodes

grid@rac12a trace]$ asmcmd dsset 'ORCL:*','AFD:*'

We can then verify

[grid@rac12a trace]$ asmcmd dsget
parameter:ORCL:*, AFD:*
profile:ORCL:*,AFD:*
[grid@rac12a trace]$

Once the new value of the diskstring set, let stop the cluster on both nodes

[root@rac12a ~]# crsctl stop cluster
[root@rac12b ~]# crsctl stop cluster

Once the cluster is stopped we have to disable and stop asmlib on both nodes

[root@rac12a ~]# systemctl disable oracleasm
Removed symlink /etc/systemd/system/multi-user.target.wants/oracleasm.service.

[root@rac12a ~]# oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

[root@rac12a ~]# oracleasm exit
Unmounting ASMlib driver filesystem: /dev/oracleasm
Unloading module "oracleasm": oracleasm
[root@rac12a ~]#

[root@rac12a ~]# ls -ltr /dev/oracleasm/
total 0
[root@rac12a ~]#

Now let’s remove all packages relative to ASMLIB on both nodes

[root@rac12a oracle]# rpm -e oracleasm-support-2.1.11-2.el7.x86_64 oracleasmlib-2.0.12-1.el7.x86_64
warning: /etc/sysconfig/oracleasm saved as /etc/sysconfig/oracleasm.rpmsave
[root@rac12a oracle]#

The next step is to stop acfsload on both nodes

[root@rac12a ~]# lsmod | grep acfs
oracleacfs           3343483  0
oracleoks             500109  2 oracleacfs,oracleadvm
[root@rac12a ~]#

[root@rac12a ~]# acfsload stop
[root@rac12a ~]# lsmod | grep acfs
[root@rac12a ~]#

As root, we can now configure Oracle ASMFD to filter at the node level. In my case steps were done on both nodes

[root@rac12a oracle]# asmcmd afd_configure
Connected to an idle instance.
AFD-627: AFD distribution files found.
AFD-636: Installing requested AFD software.
AFD-637: Loading installed AFD drivers.
AFD-9321: Creating udev for AFD.
AFD-9323: Creating module dependencies - this may take some time.
AFD-9154: Loading 'oracleafd.ko' driver.
AFD-649: Verifying AFD devices.
AFD-9156: Detecting control device '/dev/oracleafd/admin'.
AFD-638: AFD installation correctness verified.
[root@rac12a oracle]#

Once the configuration done, we can check AFD state on all nodes

[root@rac12a oracle]# asmcmd afd_state
Connected to an idle instance.
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'DISABLED' on host 'rac12a.localdomain'
[root@rac12a oracle]#

We can see that afd module is loaded but the filtering is disabled. We then have to edit the oracleafd.conf to enable the filtering

[root@rac12a etc]# cat oracleafd.conf
afd_diskstring='/dev/sd*1'

And then we have to run on both nodes

[root@rac12a etc]# asmcmd afd_filter -e
Connected to an idle instance.
[root@rac12a etc]#

[root@rac12b ~]#  asmcmd afd_filter -e
Connected to an idle instance.
[root@rac12b ~]#

Running again the afd_state command, we can confirm that the filtering is now enabled.

[root@rac12a etc]# asmcmd afd_state
Connected to an idle instance.
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'rac12a.localdomain'
[root@rac12a etc]#

Now we can migrate all asm disks.

[root@rac12a etc]# asmcmd afd_label ASM_DATA /dev/sdc1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_DIVERS /dev/sdd1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_OCR1 /dev/sdg1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_OCR2 /dev/sdi1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_VOT1 /dev/sde1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_VOT2 /dev/sdh1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_VOT3 /dev/sdf1 --migrate
Connected to an idle instance.
[root@rac12a etc]#

We can verify the ASMFD disks using the command

[root@rac12b ~]# asmcmd afd_lsdsk
Connected to an idle instance.
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
ASM_DATA                    ENABLED   /dev/sdc1
ASM_DIVERS                  ENABLED   /dev/sdd1
ASM_OCR1                    ENABLED   /dev/sdg1
ASM_OCR2                    ENABLED   /dev/sdi1
ASM_VOT1                    ENABLED   /dev/sde1
ASM_VOT2                    ENABLED   /dev/sdh1
ASM_VOT3                    ENABLED   /dev/sdf1
[root@rac12b ~]#

Let’s update the afd.conf so that ASMFD can mount ASMFD disks.

[root@rac12a etc]# cat afd.conf
afd_diskstring='/dev/sd*'
afd_filtering=enable

When the ASMFD disks are visible on both nodes, we can start acsfload on both nodes

[root@rac12a etc]# acfsload start
ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9392: Validating ADVM/ACFS installation files for operating system.
ACFS-9393: Verifying ASM Administrator setup.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9154: Loading 'oracleoks.ko' driver.
ACFS-9154: Loading 'oracleadvm.ko' driver.
ACFS-9154: Loading 'oracleacfs.ko' driver.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9322: completed
[root@rac12a etc]#

Now the conversion is done and we can start crs on both nodes

[root@rac12a ~]# crsctl start crs

[root@rac12b ~]# crsctl start crs

We can remove all asmlib references in the parameter diskstring

[grid@rac12a trace]$ asmcmd dsget
parameter:ORCL:*, AFD:*
profile:ORCL:*,AFD:*

[grid@rac12a trace]$ asmcmd dsset 'AFD:*'

[grid@rac12a trace]$ asmcmd dsget
parameter:AFD:*
profile:AFD:*
[grid@rac12a trace]$

Once the cluster started, we can verify the disk names

[grid@rac12a trace]$ asmcmd lsdsk
Path
AFD:ASM_DATA
AFD:ASM_DIVERS
AFD:ASM_OCR1
AFD:ASM_OCR2
AFD:ASM_VOT1
AFD:ASM_VOT2
AFD:ASM_VOT3
[grid@rac12a trace]$

We can also use following command to confirm that now ASMFD is being used

set linesize 300
col PATH for a20
set pages 20
col LIBRARY for a45
col NAME for a15
select inst_id,group_number grp_num,name,state,header_status header,mount_status mount,path, library
from gv$asm_disk order by inst_id,group_number,name;


   INST_ID    GRP_NUM NAME            STATE    HEADER       MOUNT   PATH                 LIBRARY
---------- ---------- --------------- -------- ------------ ------- -------------------- ---------------------------------------------
         1          1 ASM_DIVERS      NORMAL   MEMBER       CACHED  AFD:ASM_DIVERS       AFD Library - Generic , version 3 (KABI_V3)
         1          2 ASM_OCR1        NORMAL   MEMBER       CACHED  AFD:ASM_OCR1         AFD Library - Generic , version 3 (KABI_V3)
         1          2 ASM_OCR2        NORMAL   MEMBER       CACHED  AFD:ASM_OCR2         AFD Library - Generic , version 3 (KABI_V3)
         1          3 ASM_DATA        NORMAL   MEMBER       CACHED  AFD:ASM_DATA         AFD Library - Generic , version 3 (KABI_V3)
         1          4 ASM_VOT1        NORMAL   MEMBER       CACHED  AFD:ASM_VOT1         AFD Library - Generic , version 3 (KABI_V3)
         1          4 ASM_VOT2        NORMAL   MEMBER       CACHED  AFD:ASM_VOT2         AFD Library - Generic , version 3 (KABI_V3)
         1          4 ASM_VOT3        NORMAL   MEMBER       CACHED  AFD:ASM_VOT3         AFD Library - Generic , version 3 (KABI_V3)
         2          1 ASM_DIVERS      NORMAL   MEMBER       CACHED  AFD:ASM_DIVERS       AFD Library - Generic , version 3 (KABI_V3)
         2          2 ASM_OCR1        NORMAL   MEMBER       CACHED  AFD:ASM_OCR1         AFD Library - Generic , version 3 (KABI_V3)
         2          2 ASM_OCR2        NORMAL   MEMBER       CACHED  AFD:ASM_OCR2         AFD Library - Generic , version 3 (KABI_V3)
         2          3 ASM_DATA        NORMAL   MEMBER       CACHED  AFD:ASM_DATA         AFD Library - Generic , version 3 (KABI_V3)
         2          4 ASM_VOT1        NORMAL   MEMBER       CACHED  AFD:ASM_VOT1         AFD Library - Generic , version 3 (KABI_V3)
         2          4 ASM_VOT2        NORMAL   MEMBER       CACHED  AFD:ASM_VOT2         AFD Library - Generic , version 3 (KABI_V3)
         2          4 ASM_VOT3        NORMAL   MEMBER       CACHED  AFD:ASM_VOT3         AFD Library - Generic , version 3 (KABI_V3)

14 rows selected.
 

Cet article Migrating from ASMLIB to ASMFD est apparu en premier sur Blog dbi services.

Remote syslog from Linux and Solaris

Wed, 2018-06-20 10:47

Auditing operations with Oracle Database is very easy. The default configuration, where SYSDBA operations go to ‘audit_file_dest’ (the ‘adump’ directory) and other operations go to the database may be sufficient to log what is done but is definitely not a correct security audit method as both destinations can have their audit trail deleted by the DBA. If you want to secure your environment by auditing the most privileged accounts, you need to send the audit trail to another server.

This is easy as well and here is a short demo involving Linux and Solaris as the audited environments. I’ve created those 3 computer services in the Oracle Cloud:
CaptureSyslog000

So, I have an Ubuntu service where I’ll run the Oracle Database (XE 11g) and the hostname is ‘ubuntu’

root@ubuntu:~# grep PRETTY /etc/os-release
PRETTY_NAME="Ubuntu 16.04.4 LTS"

I have a Solaris service which will also run Oracle, and the hostname is ‘d17872′

root@d17872:~# cat /etc/release
Oracle Solaris 11.3 X86
Copyright (c) 1983, 2016, Oracle and/or its affiliates. All rights reserved.
Assembled 03 August 2016

I have an Oracle Enterprise Linux service which will be my audit server, collecting syslog messages from remote hosts, the hostname is ‘b5e501′ and the IP address in the PaaS network is 10.29.235.150

[root@b5e501 ~]# grep PRETTY /etc/os-release
PRETTY_NAME="Oracle Linux Server 7.5"

Testing local syslog

I start to ensure that syslog works correctly on my audit server:

[root@b5e501 ~]# jobs
[1]+ Running tail -f /var/log/messages &
[root@b5e501 ~]#
[root@b5e501 ~]# logger -p local1.info "hello from $HOSTNAME"
[root@b5e501 ~]# Jun 20 08:28:35 b5e501 bitnami: hello from b5e501

Remote setting

On the aduit server, I un-comment the lines about receiving syslog from TCP and UDP on port 514

[root@b5e501 ~]# grep -iE "TCP|UDP" /etc/rsyslog.conf
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514
# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514
# Remote Logging (we use TCP for reliable delivery)

I restart syslog service

[root@b5e501 ~]# systemctl restart rsyslog
Jun 20 08:36:47 b5e501 systemd: Stopping System Logging Service...
Jun 20 08:36:47 b5e501 rsyslogd: [origin software="rsyslogd" swVersion="8.24.0" x-pid="2769" x-info="http://www.rsyslog.com"] exiting on signal 15.
Jun 20 08:36:47 b5e501 systemd: Starting System Logging Service...
Jun 20 08:36:47 b5e501 rsyslogd: [origin software="rsyslogd" swVersion="8.24.0" x-pid="2786" x-info="http://www.rsyslog.com"] start
Jun 20 08:36:47 b5e501 systemd: Started System Logging Service.

I tail the /var/log/messages (which is my default destination for “*.info;mail.none;authpriv.none;cron.none”)

[root@b5e501 ~]# tail -f /var/log/messages &
[root@b5e501 ~]# jobs
[1]+ Running tail -f /var/log/messages &

I test with local1.info and check that the message is tailed even when logger is sending it though the network:

[root@b5e501 ~]# logger -n localhost -P 514 -p local1.info "hello from $HOSTNAME"
Jun 20 09:18:07 localhost bitnami: hello from b5e501

That’s perfect.

Now I can test the same from my Ubuntu host to ensure that the firewall settings allow for TCP and UDP on port 514


root@ubuntu:/tmp/Disk1# logger --udp -n 10.29.235.150 -P 514 -p local1.info "hello from $HOSTNAME in UDP"
root@ubuntu:/tmp/Disk1# logger --tcp -n 10.29.235.150 -P 514 -p local1.info "hello from $HOSTNAME in TCP"

Here are the correct messages received:

Jun 20 09:24:46 ubuntu bitnami hello from ubuntu in UDP
Jun 20 09:24:54 ubuntu bitnami hello from ubuntu in TCP

Destination setting for the audit

As I don’t want to have all messages into /var/log/messages, I’m now setting, in the audit server, a dedicated file for “local1″ facility and “info” level that I’ll use for my Oracle Database audit destination

[root@b5e501 ~]# touch "/var/log/audit.log"
[root@b5e501 ~]# echo "local1.info /var/log/audit.log" >> /etc/rsyslog.conf
[root@b5e501 ~]# systemctl restart rsyslog

After testing the same two ‘logger’ commands from the remote host I check the entries in my new file:

[root@b5e501 ~]# cat /var/log/audit.log
Jun 20 09:55:09 ubuntu bitnami hello from ubuntu in UDP
Jun 20 09:55:16 ubuntu bitnami hello from ubuntu in TCP

Remote logging

Now that I validated that remote syslog is working, I set automatic forwarding of syslog messages on my Ubuntu box to send all ‘local1.info to the audit server':

root@ubuntu:/tmp/Disk1# echo "local1.info @10.29.235.150:514" >> /etc/rsyslog.conf
root@ubuntu:/tmp/Disk1# systemctl restart rsyslog

This, with a single ‘@’ forwards in UDP. You can double the ‘@’ to forward using TCP.

Here I check with logger in local (no mention of the syslog host here):

root@ubuntu:/tmp/Disk1# logger -p local1.info "hello from $HOSTNAME with forwarding"

and I verify that the message is logged in the audit server into /var/log/audit.log

[root@b5e501 ~]# tail -1 /var/log/audit.log
Jun 20 12:00:25 ubuntu bitnami: hello from ubuntu with forwarding

Repeated messages

Note that when testing, you may add “$(date)” to your message in order to see it immediately because syslog keeps the message to avoid flooding when the message is repeated. This:

root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Then another one"

is logged as this:

Jun 20 12:43:12 ubuntu bitnami: message repeated 5 times: [ Always the same message] Jun 20 12:43:29 ubuntu bitnami: Then another one

I hope that one day this idea will be implemented by Oracle when flooding messages to the alert.log ;)

Oracle Instance

The last step is to get my Oracle instance sending audit message to the local syslog, with facility.level local1.info so that they will be automatically forwarded to my audit server. I have to set audit_syslog_level to ‘local1.info’ and the audit_trail to ‘OS':

oracle@ubuntu:~$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 20 11:48:00 2018
 
Copyright (c) 1982, 2011, Oracle. All rights reserved.
 
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
SQL> alter system set audit_syslog_level='local1.info' scope=spfile;
 
System altered.
 
SQL> alter system set audit_trail='OS' scope=spfile;
 
System altered.
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 1068937216 bytes
Fixed Size 2233344 bytes
Variable Size 616565760 bytes
Database Buffers 444596224 bytes
Redo Buffers 5541888 bytes
Database mounted.
Database opened.

It is very easy to check that it works as the SYSDBA and the STARTUP are automatically audited. Here is what I can see in my audit server /var/log/audit.log:

[root@b5e501 ~]# tail -f /var/log/audit.log
Jun 20 11:55:47 ubuntu Oracle Audit[27066]: LENGTH : '155' ACTION :[7] 'STARTUP' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[13] 'Not Available' STATUS:[1] '0' DBID:[0] ''
Jun 20 11:55:47 ubuntu Oracle Audit[27239]: LENGTH : '148' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[0] ''
Jun 20 11:55:51 ubuntu Oracle Audit[27419]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2860420539'

In the database server, I have no more files in the adump since this startup:

oracle@ubuntu:~/admin/XE/adump$ /bin/ls -alrt
total 84
drwxr-x--- 6 oracle dba 4096 Jun 20 11:42 ..
-rw-r----- 1 oracle dba 699 Jun 20 11:44 xe_ora_26487_1.aud
-rw-r----- 1 oracle dba 694 Jun 20 11:44 xe_ora_26515_1.aud
-rw-r----- 1 oracle dba 694 Jun 20 11:44 xe_ora_26519_1.aud
-rw-r----- 1 oracle dba 694 Jun 20 11:44 xe_ora_26523_1.aud
drwxr-x--- 2 oracle dba 4096 Jun 20 11:48 .
-rw-r----- 1 oracle dba 896 Jun 20 11:48 xe_ora_26574_1.aud

Solaris

I have also started a Solaris service:

opc@d17872:~$ pfexec su -
Password: solaris_opc
su: Password for user 'root' has expired
New Password: Cl0udP01nts
Re-enter new Password: Cl0udP01nts
su: password successfully changed for root
Oracle Corporation SunOS 5.11 11.3 June 2017
You have new mail.
root@d17872:~#

Here, I add the forwarding to /etc/syslog.conf (tab is a required separator which cannot be replaced with spaces) and restart the syslog service:

root@d17872:~# echo "local1.info\t@10.29.235.150" >> /etc/syslog.conf
root@d17872:~# svcadm restart system-log

Then logging a message locally

root@d17872:~# logger -p local1.info "hello from $HOSTNAME with forwarding"

Here is the messaged received from the audit server:

[root@b5e501 ~]# tail -f /var/log/audit.log
Jun 20 05:27:51 d17872.compute-a511644.oraclecloud.internal opc: [ID 702911 local1.info] hello from d17872 with forwarding

Here in Solaris I have the old ‘syslog’ with no syntax to change the UDP port. The default port is defined in /etc/services, which is the one I’ve configured to listen to on my audit server:

root@d17872:~# grep 514 /etc/services
shell 514/tcp cmd # no passwords used
syslog 514/udp

If you want more features, you can install syslog-ng or rsyslog on Solaris.

 

Cet article Remote syslog from Linux and Solaris est apparu en premier sur Blog dbi services.

How to fix your own SQL plan in Oracle ?

Mon, 2018-06-18 10:23

There is time when you have build an SQL plan and then you want to fix it for all next executions of the query by your application.

In this post I show how fix a plan you have created by yourself.

First we need to identified the query

SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 4159986352

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     3 |    57 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS   |     3 |    57 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ITEM_ORDER_IX |     3 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

In that example I’ll take an application query against the ORDER_ITEMS table. I find hat query too fast because it is using the index ITEM_ORDER_IX which is based on the primary key. So we are going to force that query to be executed by accessing the whole table without using any index. Here I’ll use the hint FULL to do the job.

SQL> select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 456270211

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |
---------------------------------------------------------------------------------

 

On both queries I added a comment to make it easier to retrieve information in the SQL views from Oracle. Now I can get the statistic about my queries:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh        4159986352                                        25         5199     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

Plan control

So my goal is to force the application query “8ms87fhrq01xh” to use the plan from my manual modified query “55x955b31npwq”. To do so, I’m going to use the  “SQL Plan Management” from Oracle which is embedded from the release 11 and can be used with the DBMS_SPM package.

First I need to load the plan from my application query into SPM baseline:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache( sql_id => '8ms87fhrq01xh' ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

 

I have now a new cursor in the SQL view with the SQL_PLAN_BASELINE identifier:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh        4159986352                                        25         5199     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
8ms87fhrq01xh        4159986352 SQL_PLAN_gt4cxn0aacz0j91520601          1        21703     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

I can now find the SPM content for my SQL:

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO

 

What we need to do now is to inject into the SPM baseline the plan from my modified query. To do so, I need the SQL_HANDLE of my application query and the couple of SQL_ID+PLAN_HASH_VALUE of the modified query to inject its plan into the plan baseline of my application query:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '55x955b31npwq',
    plan_hash_value => 456270211,
    sql_handle => 'SQL_fc919da014a67c11'
  ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

 

Now, let’s seen what’s in the baseline of our application query:

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   NO

 

A new plan called “SQL_PLAN_gt4cxn0aacz0jf91228bb” has been generated and I know want to be sure it is the only one that are goin gto be used. Thus we need to fix it:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.alter_sql_plan_baseline(
    sql_handle => 'SQL_fc919da014a67c11',
plan_name => 'SQL_PLAN_gt4cxn0aacz0jf91228bb',
    attribute_name => 'fixed',
    attribute_value => 'YES'
  ) ;
  dbms_output.put_line( 'plans modified: '||plans_loaded ) ;
end ;
/
plans modified: 1

PL/SQL procedure successfully completed.

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   YES

 

Then, my Full access plan is fixed and I can check if the index on the primary key is still in used:

SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 456270211

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |
---------------------------------------------------------------------------------

 

I can monitor the plan usage from the SQL view to check if the application has is executing the query with the new plan:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh         456270211 SQL_PLAN_gt4cxn0aacz0jf91228bb          6       145687     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

As I see that both queries are currently using the same plan, I know that my application is now using the new plan with the full access to the ORDER_ITEMS table.

I hope this demonstration may help and please do not hesitate to contact us if you have any further questions or observations.

Scripts used in this article:

-- script sql.sql
set lines 180 pages 500
col sql_id format a14
col sql_plan_baseline format a30
col plan_hash_value format 999999999999999
col exact_matching_signature format 99999999999999999999
col sql_text format a50
select sql_id,
plan_hash_value,
sql_plan_baseline,
executions,
elapsed_time,
exact_matching_signature,
substr(sql_text,0,50) sql_text
from v$sql
where parsing_schema_name != 'SYS'
and sql_text like '%_ACCESS%' ;
-- script spm.sql
set lines 200
set pages 500
col signature format 99999999999999999999
col sql_handle format a30
col plan_name format a30
col enabled format a5
col accepted format a5
col fixed format a5
select
signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where signature = '&signature.'
 

Cet article How to fix your own SQL plan in Oracle ? est apparu en premier sur Blog dbi services.

ADWC new OCI interface

Sun, 2018-06-17 14:51

A few things have changed about the Autonomous Data Warehouse Cloud service recently. And I’ve found the communication not so clear, so here is a short post about what I had to do to start the service again. The service has always been on the OCI data centers but was managed with the classic management interface. It has been recently migrated to the new interface:
CaptureADWCnew
Note that ADWC here is the name I’ve given for my service. It seems that the Autonomous Data Warehouse Cloud Service is now referred by the ADW acronym.

The service itself did not have any outage. The migration concerns only the interface. However, once the migration done, you cannot use the old interface. I went to the old interface with the URL I bookmarked, tried to start the service, and got a ‘last activity START_SERVICE failed’ error message without additional detail.
CaptureADWCfail

You can forget the old bookmark (such as https://psm-tenant.console.oraclecloud.com/psmui/faces/paasRunner.jspx?serviceType=ADWC) and you now have to use the new one (such as https://console.us-ashburn-1.oraclecloud.com/a/db/adws/ocid1.autonomousdwdatabase.oc1.iad.al-long-IAD-identifier)

So I logged to the console https://console.us-ashburn-1.oraclecloud.com (My service is in Ashburn-1 region). There I provided the tenant name (was the cloud account in the old interface) which can also be provided in the URL as https://console.us-ashburn-1.oraclecloud.com/?tenant=tenant. I selected oracleidentitycloudservice as the ‘identity provider’, my username and password and I am on the OCI console.

From the top-left menu, I can go to Autonomous Data Warehouse. I see nothing until I choose the compartement in the ‘list scope’. The ADWC service I had created when in the old interface is in the ‘tenant (root)’ compartment. Here I can start the service.

The previous PSM command line interface cannot be used anymore. We need to install the OCI CLI:

$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

You will need the Tenancy ID (Tenancy OCID:ocid1.tenancy.oc1..aaaaaaaa… that you find on the bottom of each page in the console), the User ID (User OCID ocid1.user.oc1..aaaaaaa… that you find in the ‘users’ menu). All those ‘OCID’ are documented in https://docs.us-phoenix-1.oraclecloud.com/Content/API/Concepts/apisigningkey.htm

If you used the REST API, they change completely. You will have to post to something like:

/20160918/autonomousDataWarehouses/ocid1.autonomousdwdatabase.oc1.iad.abuwcljrb.../actions/start

where the OCID is the database one that cou can copy from the console.

 

Cet article ADWC new OCI interface est apparu en premier sur Blog dbi services.

Convert a WebLogic Cluster from configured to dynamic

Fri, 2018-06-15 00:14

Unless the servers in cluster are not symmetric, which is not recommended anyway, dynamic cluster have many advantages against configured cluster:

  1. Ensure Cluster Member Uniformity
  2. Easily add new servers to manage more traffic
  3. Automatically adapt to load to add/remove managed servers
  4. Can still contain configured servers even if not recommended as for point 1
Server template

A server template defines a set of attributes. A change in a template will be propagated to all server depending on it. A dynamic cluster can be based on a server template.

Here is an overview of the configured to dynamic change:

  1. Write down all customized parameters of server’s member of the cluster.
  2. Create new dynamic cluster
  3. Report all settings. There are two specificities on dynamic cluster:
    1. Listen port and SSL port which can be either:
      1. Static, meaning all servers of the cluster will have same port. This is the best option when you have one server to one machine mapping
      2. Calculated, meaning each server will listen on a different port by step of 1. For example, if first port is set to 7000, server 1 will listen on 7001, server 2 on 7002, …
    2. Machine binding: Use of a specific (filtered) subset of machines from the cluster
  4. Create new server template
Procedure
  1. In left tree, go in Environment, Clusters, Server Templates
  2. Lock & Edit the configuration and click New
    1. Name the template:
      5 - Server Template Name
    2. In the server template list, select the newly created template to customize the parameters.
    3. In General tab, select the Cluster that needs to be converted:
      6 - Server Template Name
    4. Save
  3. In Environment, Clusters, Servers tab, set Server Template name:
    Cluster settings - servers tab
  4. Save and Activate the changes
  5. In Environment, Servers folder, dynamically created servers will be displayed (4 in your example):
    7 - Servers List
    Note that machine are distributed across available machines in round robin and also the Listen port and SSL are incrementing.
  6. Then, start these new servers, test application is running correctly.
  7. Finally, stop configured managed servers by selecting “Work when completes”:
    8 - stop when complete

Cluster is now dynamic and you can easily add or remove managed servers from cluster.

What Next ?

This was a quick overview of how to convert configured to dynamic cluster. As we saw, it still require manual intervention to add or remove servers from cluster.

Coming with 12.2.1, WebLogic introduce a new feature called “elasticity”. This feature allows to automatically scales the amount of managed servers in the cluster based on user defined policies.

Thanks to WebLogic Diagnostic Framework (WLDF) Policies, it is possible to monitor memory, CPU usage, threads and then trigger a scale up or down action.

 

Cet article Convert a WebLogic Cluster from configured to dynamic est apparu en premier sur Blog dbi services.

Is adding a column to a typed table in PostgreSQL instant?

Thu, 2018-06-14 06:26

Today at the SOUG Day I did some little demos and one of them was about creating typed tables. In the demo the two tables did not contain any rows and one of the questions was: When these tables contain a million of rows would adding a column be instant as well? Lets do a quick test.

Same setup as in the post referenced above: Two schemas, one type, two tables based on the type:

postgres=# select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
postgres=# create schema a;
CREATE SCHEMA
postgres=# create schema b;
CREATE SCHEMA
postgres=# create type typ1 as ( a int, b text );
CREATE TYPE
postgres=# create table a.t1 of typ1;
CREATE TABLE
postgres=# create table b.t1 of typ1;
CREATE TABLE
postgres=# insert into a.t1
postgres-# select a.*
postgres-#      , md5(a::text)
postgres-#   from generate_series(1,1000000) a;
INSERT 0 1000000
postgres=# insert into b.t1 select * from a.t1;
INSERT 0 1000000
postgres=# 

Both of the tables contain 1’000’000 rows so how much time would a modification of the type take?

postgres=# \timing
Timing is on.
postgres=# alter type typ1 add attribute c timestamp cascade;;
ALTER TYPE
Time: 9.338 ms
Time: 0.867 ms
postgres=# \d a.t1
                             Table "a.t1"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 a      | integer                     |           |          | 
 b      | text                        |           |          | 
 c      | timestamp without time zone |           |          | 
Typed table of type: typ1

postgres=# \d b.t1
                             Table "b.t1"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 a      | integer                     |           |          | 
 b      | text                        |           |          | 
 c      | timestamp without time zone |           |          | 
Typed table of type: typ1

Almost instant. Btw: Of course you can also remove an attribute from the type:

postgres=# alter type typ1 drop attribute c cascade;
ALTER TYPE
Time: 14.417 ms
postgres=# \d a.t1
                   Table "a.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    |           |          | 
Typed table of type: typ1

postgres=# \d b.t1
                   Table "b.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    |           |          | 
Typed table of type: typ1
 

Cet article Is adding a column to a typed table in PostgreSQL instant? est apparu en premier sur Blog dbi services.

Pages