Dontcheff

Archive for the ‘DBA’ Category

Artificial stupidity as a DBA limitation of artificial intelligence

In Data, Database tuning, Databases, DBA on December 6, 2017 at 07:47

“Artificial intelligence is no match for natural stupidity” ― Albert Einstein

What about introducing Artificial Intelligence into the database to an extent it tunes itself into all possible dimensions?

You have probably either seen the question above or have already asked yourself if that was at all possible. On Ask Tom, John from Guildford wrote the following:

As for Artificial Intelligence, well Artificial Stupidity is more likely to be true. Humanity is not privy to the algorithm for intelligence. Anyone who’s had the pleasure of dealing with machine generated code knows that software is no more capable of writing a cohesive system than it is of becoming self-aware.

Provided you’re not trying to be a cheap alternative to an automaton you just need to think. That one function alone differentiates us from computers, so do more of it. The most sublime software on the planet has an IQ of zero, so outdoing it shouldn’t be all that hard.

Stephen Hawking thinks computers may surpass human intelligence and take over the world. Fear artificial stupidity, not artificial intelligence!

Einstein is credited with saying (but it was probably Alexandre Dumas or Elbert Hubbard who deserve the recognition): “The difference between genius and stupidity is that genius has its limits.”

Explore artificial stupidity (AS) and/or read Charles Wheelan’s book Naked Statistics to understand this kind of AI danger. By the way, according to Woody Allen, 94.5% of all statistics are made up!

So what are the limitations of AI? Jay Liebowitz argues that “if intelligence and stupidity naturally exist, and if AI is said to exist, then is there something that might be called “artificial stupidity?” According to him three of these limitations are:

  • Ability to possess and use common sense
  • Development of deep reasoning systems
  • Ability to easily acquire and update knowledge
  • But does artificial intelligence use a database in order to be an artificial intelligence? Few very interesting answers to that question are give by Douglas Green, Jordan Miller and Ramon Morales, here is a summary:

    Although AI could be built without a database, it would probably be more powerful if a database were added. AI and databases are currently not very well integrated. The database is just a standard tool that the AI uses. However, as AI becomes more advanced, it may become more a part of the database itself.

    I don’t believe you can have an effective Artificial Intelligence without a database or memory structure of some kind.

    While it is theoretically possible to have an artificial intelligence without using a database, it makes things a LOT easier if you can store what the AI knows somewhere convenient.

    As Demystifying Artificial Intelligence explains, AI hass been embedded into some of the most fundamental aspects of data management, making those critical data-driven processes more celeritous and manageable.

    Amazon Mechanical Turk is worth looking into and Oracle are also ready for business with AI.

    Matt Johnson, a Navy pilot turned AI researcher, at a conference this simmer by saying that one of the places we are not making a lot of advances is in that teaming, in that interaction (of humans and AI) – Artificial Stupidity: When Artificial Intelligence + Human = Disaster

    Bottom line: if AI uses a database, then the intelligent database should be at least autonomous and have most tasks automated but not relying on artificial stupidity as a DBA limitation of artificial intelligence. Whatever it means… I do not want to curb your enthusiasm but we need to first fill in the skills gap: we need data engineers who understand databases and data warehouses, infrastructure and tools that span data cleaning, ingestion, security, predictions. And in this aspect Cloud is critical and a big differentiator.

    P.S. Is Artificial Intelligence Progress a Bubble? was published 4 days after this blog post.

    Advertisements

    Blockchain for DBAs

    In Data, Databases, DBA on October 30, 2017 at 09:25

    “Instead of putting the taxi driver out of a job, blockhchain puts Uber out of a job and lets the taxi driver work with the customer directly.” – Vitalik Buterin

    A blockchain database consists of two kinds of records: transactions and blocks. Blocks contain the lists of the transactions that are hashed and encoded into a hash (Merkle) tree. The linked blocks form a chain as every block holds the hash pointer to the previous block.

    The blockchain can be stored in a flat file or in a database. For example, the Bitcoin core client stores the blockchain metadata using LevelDB (based on Google’s Bigtable database system).

    The diagram above can be used to create the schema in PostgreSQL. “As far as what DBMS you should put it in”, says Ali Razeghi, “that’s up to your use case. If you want to analyze the transactions/wallet IDs to see some patterns or do BI work I would recommend a relational DB. If you want to setup a live ingest with multiple cryptocoins I would recommend something that doesn’t need the transaction log so a MongoDB solution would be good.”

    If you want to setup a MySQL database: here are 8 easy steps.

    But what is the structure of the block, what does it look like?

    The block has 4 fields:

    1. Block Size: The size of the block in bytes
    2. Block Header: Six fields in the block header
    3. Transaction Counter: How many transactions follow
    4. Transactions: The transactions recorded in this block

    The block header has 6 fields:

    1. Version: A version number to track software/protocol upgrades
    2. Previous Block Hash: A reference to the hash of the previous (parent) block in the chain
    3. Merkle Root: A hash of the root of the merkle tree of this block’s transactions
    4. Timestamp: The approximate creation time of this block (seconds from Unix Epoch)
    5. Difficulty Target: The proof-of-work algorithm difficulty target for this block
    6. Nonce: A counter used for the proof-of-work algorithm

    More details, like for example details on block header hash and block height, can be found here.

    But how about blockchain vs. relational database: Which is right for your application? As you can see, because the term “blockchain” is not clearly defined, you could argue that almost any IT project could be described as using a blockchain.

    It is worth reading Guy Harrison’s article Sealing MongoDB documents on the blockchain. Here is a nice quote: “As a database administrator in the early 1990s, I remember the shock I felt when I realized that the contents of the database files were plain text; I’d just assumed they were encrypted and could only be modified by the database engine acting on behalf of a validated user.”

    The Blockchain technology is a very special kind of a distributed database. Sebastien Meunier’s post cocludes that ironically, there is no consensus on the definition of what blockchain technology is.

    I particularly, like his last question: Is a private blockchain without token really more efficient than a centralized system? And I would add: private blockchain, really?

    But once more, what is blockchain? Rockford Lhotka gives a very good DBA-friendly definition/characteristics of blockchain:

    1. A linked list where each node contains data
    2. Immutable:
    – Each new node is cryptographically linked to the previous node
    – The list and the data in each node is therefore immutable, tampering breaks the cryptography
    3. Append-only
    – New nodes can be added to the list, though existing nodes can’t be altered
    4. Persistent
    – Hence it is a data store – the list and nodes of data are persisted
    5. Distributed
    – Copies of the list exist on many physical devices/servers
    – Failure of 1+ physical devices has no impact on the integrity of the data
    – The physical devices form a type of networked cluster and work together
    – New nodes are only appended to the list if some quorum of physical devices agree with the cryptography and validity of the node via consistent algorithms running on all devices.

    Kevin Ford’s reply is a good one to conclude with: “Based on this description (above) it really sounds like your (Rockford Lhotka’s) earlier comparison to the hype around XML is spot on. It sounds like in and of itself it isn’t particularly anything except a low level technology until you structure it to meet a particular problem.”

    The nature of blockchain technology makes it difficult to work with high transnational volumes.

    But DBAs can have a look at (1) BigchainDB, a database with several blockchain characteristics added: high-transaction, decentralized database, immutability & native support for assets and (2) at Chainfrog if interested in connecting legacy databases together. As far as I know, they support as of now at least MySQL and SQL Server.

    DBA 3.0 – Database Administration in the Cloud

    In Cloud, DBA, OOW, Oracle database on September 23, 2017 at 10:35

    “The interesting thing about cloud computing is that we’ve redefined cloud computing to include everything that we already do … The computer industry is the only industry that is more fashion-driven than women’s fashion.” – Larry Ellison, CTO, Oracle

    DBA 1.0 -> DBA 2.0 -> DBA 3.0: Definitely, the versioning of DBAs is falling behind the database versions of Oracle, Microsoft, IBM, etc. Mainframe, client-server, internet, grid computing, cloud computing…

    The topic on the DBA profession and how it changes, how it evolves and how it expands has been of interest among top experts in the industry:

    Penny Arvil, VP of Oracle Database Product Development, stated that DBAs are being asked to understand what businesses do with data rather than just the mechanics of keeping the database healthy and running.

    Kellyn Pot’Vin-Gorman claims that DBAs with advanced skills will have plenty of work to keep them busy and if Larry is successful with the bid to rid companies of their DBAs for a period of time, they’ll be very busy cleaning up the mess afterwards.

    Tim Hall said that for pragmatic DBAs the role has evolved so much over the years, and will continue to do so. Such DBAs have to continue to adapt or die.

    Megan Elphingstone concluded that DBA skills would be helpful, but not required in a DBaaS environment.

    Jim Donahoe hosted a discussion about the state of the DBA as the cloud continues to increase in popularity.

    First time I heard about DBA 2.0 was about 10 years ago. At Oracle OpenWorld 2017 (next week or so), I will be listening to what DBA 3.0 is: How the life of a Database Administrator has changed! If you google for DBA 3.0 most likely you will find information about how to play De Bellis Antiquitatis DBA 3.0. Different story…

    But if I can also donate something to the discussion is probably the fact that ever since a database vendor automated something in the database, it only generated more work for DBAs in the future. More DBAs are needed now as ever. Growing size and complexity of IT systems is definitely contributing to that need.

    These DBA sessions in San Francisco are quite relevant to the DBA profession (last one on the list will be delivered by me):

    – Advance from DBA to Cloud Administrator: Wednesday, Oct 04, 2:00 p.m. – 2:45 p.m. | Moscone West – Room 3022
    – Navigating Your DBA Career in the Oracle Cloud: Monday, Oct 02, 1:15 p.m. – 2:00 p.m. | Moscone West – Room 3005
    – Security in Oracle Database Cloud Service: Sunday, Oct 01, 3:45 p.m. – 4:30 p.m. | Moscone South – Room 159
    – How to Eliminate the Storm When Moving to the Cloud: Sunday, Oct 01, 1:45 p.m. – 2:30 p.m. | Moscone South – Room 160
    – War of the Worlds: DBAs Versus Developers: Wednesday, Oct 04, 1:00 p.m. – 1:45 p.m. | Moscone West – Room 3014
    – DBA Types: Sunday, Oct 01, 1:45 p.m. – 2:30 p.m. | Marriott Marquis (Yerba Buena Level) – Nob Hill A/B

    And finally, a couple of quotes about databases:

    – “Database Management System [Origin: Data + Latin basus “low, mean, vile, menial, degrading, ounterfeit.”] A complex set of interrelational data structures allowing data to be lost in many convenient sequences while retaining a complete record of the logical relations between the missing items. — From The Devil’s DP Dictionary” ― Stan Kelly Bootle
    – “I’m an oracle of the past. I can accurately predict up to 1 minute in the future, by thoroughly investigating the last 2 years of your life. Also, I look like an old database – flat and full of useless info.” ― Will Advise, Nothing is here…

    DBA Statements

    In DBA, Oracle database, PL/SQL, SQL on September 5, 2017 at 11:51

    “A statement is persuasive and credible either because it is directly self-evident or because it appears to be proved from other statements that are so.” Aristotle

    In Oracle 12.2, there is a new view called DBA_STATEMENTS. It can helps us understand better what SQL we have within our PL/SQL functions, procedures and packages.

    There is too little on the Internet and nothing on Metalink about this new view:

    PL/Scope was introduced with Oracle 11.1 and covered only PL/SQL. In 12.2, PL/Scope was enhanced by Oracle in order to report on the occurrences of static and dynamic SQL call sites in PL/SQL units.

    PL/Scope can help you answer questions such as:
    – Where and how a column x in table y is used in the PL/SQL code?
    – Is the SQL in my application PL/SQL code compatible with TimesTen?
    – What are the constants, variables and exceptions in my application that are declared but never used?
    – Is my code at risk for SQL injection and what are the SQL statements with an optimizer hint coded in the application?
    – Which SQL has a BULK COLLECT or EXECUTE IMMEDIATE clause?

    Details can be found in the PL/Scope Database Development Guide or at Philipp Salvisberg’s blog.

    Here is an example: how to find all “execute immediate” statements and all hints used in my PL/SQL units? If needed, you can limit the query to only RULE hints (for example).

    1. You need to set the PLSCOPE_SETTINGS parameter and ensure SYSAUX has enough space:

    
    SQL> SELECT SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS 
    WHERE OCCUPANT_NAME='PL/SCOPE';
    
    SPACE_USAGE_KBYTES
    ------------------
                  1984
    
    SQL> show parameter PLSCOPE_SETTINGS
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------------
    plscope_settings                     string      IDENTIFIERS:NONE
    
    SQL> alter system set plscope_settings='STATEMENTS:ALL' scope=both;
    
    System altered.
    
    SQL> show parameter PLSCOPE_SETTINGS
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------------
    plscope_settings                     string      STATEMENTS:ALL
    
    

    2. You must compile the PL/SQL units with the PLSCOPE_SETTINGS=’STATEMENTS:ALL’ to collect the metadata. SQL statement types that PL/Scope collects are: SELECT, UPDATE, INSERT, DELETE, MERGE, EXECUTE IMMEDIATE, SET TRANSACTION, LOCK TABLE, COMMIT, SAVEPOINT, ROLLBACK, OPEN, CLOSE and FETCH.

    
    -- start
    SQL> select TYPE, OBJECT_NAME, OBJECT_TYPE, HAS_HINT, 
    SUBSTR(TEXT,1,LENGTH(TEXT)-INSTR(REVERSE(TEXT), '/*') +2 ) as "HINT" 
    from DBA_STATEMENTS
    where TYPE='EXECUTE IMMEDIATE' or HAS_HINT='YES';
    
    TYPE              OBJECT_NAME   OBJECT_TYPE  HAS HINT
    ----------------- ------------- ------------ --- ------------------
    EXECUTE IMMEDIATE LASKE_KAIKKI  PROCEDURE    NO  
    SELECT            LASKE_KAIKKI  PROCEDURE    YES SELECT /*+ RULE */
    
    -- end
    

    Check also the DBA_STATEMENTS and ALL_STATEMENTS documentation. And the blog post by Jeff Smith entitled PL/Scope in Oracle Database 12c Release 2 and Oracle SQL Developer.

    But finally, here is a way how to regenerate the SQL statements without the hints:

    
    -- start
    SQL> select TEXT from DBA_STATEMENTS where HAS_HINT='YES';
    
    TEXT
    ------------------------------------------------------------
    SELECT /*+ RULE */ NULL FROM DUAL WHERE SYSDATE = SYSDATE
    
    SQL> select 'SELECT '||
    TRIM(SUBSTR(TEXT, LENGTH(TEXT) - INSTR(REVERSE(TEXT), '/*') + 2))
    as "SQL without HINT"
    from DBA_STATEMENTS where HAS_HINT='YES';
    
    SQL without HINT
    -------------------------------------------------------------
    SELECT NULL FROM DUAL WHERE SYSDATE = SYSDATE
    
    -- end
    

    Can you use Oracle RAC on Third-Party Clouds?

    In DBA on July 11, 2017 at 14:40

    Q: Can you use Oracle RAC on Third-Party Clouds?
    A: No.

    The Licensing Oracle Software in the Cloud Computing Environment document states:

    “This policy applies to cloud computing environments from the following vendors: Amazon Web Services – Amazon Elastic Compute Cloud (EC2), Amazon Relational Database Service (RDS) and Microsoft Azure Platform (collectively, the ‘Authorized Cloud Environments’). This policy applies to these Oracle programs.”

    The document that lists “these Oracle Programs” does not include RAC (or Multitenant or In-Memory DB).

    For more details check Markus Michalewicz’s (Oracle RAC Product Manager) white paper entitled: How to Use Oracle RAC in a Cloud? – A Support Question. The image about is slide 45/50 from the same paper.

    And here is how to use Oracle Real Application Clusters (RAC) in the Oracle Database Cloud Service.

    An interesting blog post by Brian Peasland entitled Oracle RAC on Third-Party Clouds concludes: “But if I were looking to move my company’s RAC database infrastructure to the cloud, I would seriously investigate the claims in this Oracle white paper before committing to the AWS solution. That last sentence is the entire point of this blog post.”

    For business and mission critical applications I would by all means recommend Oracle Real Application Clusters on Oracle Bare Metal Cloud.

    We should not forget that something works and something being supported are two totally different things. Even for the Oracle Cloud check the Known issues for Oracle Database Cloud Service document: Updating the cloud tooling on a deployment hosting Oracle RAC requires manual update of the Oracle Database Cloud Backup Module.

    Conclusion: Oracle RAC can NOT be licensed (and consequently not be used) in the above mentioned cloud environments although such claims were published even yesterday in the internet (July 10, 2017).

    Twelve new features for Cyber Security DBAs

    In Cloud, Data, DBA, Security and auditing on June 2, 2017 at 08:32

    In the early years of Oracle, Larry Ellison was asked if clients ever ask for their money back. “Nobody’s asked for their money back yet – he replied – a few have asked for their data back though!

    A relatively new Wells Fargo Insurance Cyber Security study shows that companies are more concerned with private data loss than with hackers:

    Thus, one of the main roles of the cyber security DBA is to protect and secure the data.

    Here is what the latest Oracle release 12cR2 is offering us:

    1. A Fully Encrypted Database

    To encrypt an entire database, you must encrypt all the tablespaces within this database, including the Oracle-supplied SYSTEM, SYSAUX, UNDO, and TEMP tablespaces (which is now possible in 12.2). For a temporary tablespace, drop it and then recreate it as encrypted – do not specify an algorithm. Oracle recommends that you encrypt the Oracle-supplied tablespaces by using the default tablespace encryption algorithm, AES128. Here is how you do it:

    ALTER TABLESPACE system ENCRYPTION ONLINE ENCRYPT 
    FILE_NAME_CONVERT=('system01.dbf','system01_enc.dbf'); 
    

    2. TDE Tablespace Live Conversion

    You can now encrypt, decrypt, and rekey existing tablespaces with Transparent Data Encryption (TDE) tablespace live conversion. The feature performs initial cryptographic migration for TDE tablespace encryption on the tablespace data in the background so that the tablespace can continue servicing SQL and DML statements like insert, delete, select, merge, and so on. Ensure that you have enough auxiliary space to complete the encryption and run (for example):

    ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES192' ENCRYPT 
    FILE_NAME_CONVERT = ('users.dbf', 'users_enc.dbf'); 
    

    3. Support for ARIA, SEED, and GOST algorithms

    By default, Transparent Data Encryption (TDE) Column encryption uses the Advanced Encryption Standard with a 192-bit length cipher key (AES192), and tablespace and database encryption use the 128–bit length cipher key (AES128). 12.2 provides advanced security Transparent Data Encryption (TDE) support for these encryption algorithms:

    – SEED (Korea Information Security Agency (KISA) for South Korea
    – ARIA (Academia, Research Institute, and Agency) for South Korea
    – GOST (GOsudarstvennyy STandart) for Russia

    ALTER TABLE clients REKEY USING 'GOST256'; 
    

    4. TDE Tablespace Offline Conversion

    12.2 introduces new SQL commands to encrypt tablespace files in place with no storage overhead. You can do this on multiple instances across multiple cores. Using this feature requires downtime, because you must take the tablespace temporarily offline. With Data Guard configurations, you can either encrypt the physical standby first and switchover, or encrypt the primary database, one tablespace at a time. This feature provides fast offline conversion of existing clear data to TDE encrypted tablespaces. Use the following syntax:

    ALTER TABLESPACE users ENCRYPTION OFFLINE ENCRYPT; 
    

    5. Setting Future Tablespaces to be Encrypted

    ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = CLOUD_ONLY; 
    

    CLOUD_ONLY transparently encrypts the tablespace in the Cloud using the AES128 algorithm if you do not specify the ENCRYPTION clause of the CREATE TABLESPACE SQL statement: it applies only to an Oracle Cloud environment. ALWAYS automatically encrypts the tablespace using the AES128 algorithm if you omit the ENCRYPTION clause of CREATE TABLESPACE, for both the Cloud and premises scenarios.

    6. Role-Based Conditional Auditing

    Role-based conditional auditing provides the ability to define unified audit policies that conditionally audit users based on a role in addition to the current capability to audit by users. This feature enables more powerful policy-based conditional auditing by using database roles as the condition for auditing. For example, auditing for new users with the DBA role would begin automatically when they are granted the role:

    CREATE AUDIT POLICY role_dba_audit_pol ROLES DBA CONTAINER = ALL; 
    AUDIT POLICY role_dba_audit_pol;
    

    7. Strong Password Verifiers by Default and Minimum Authentication Protocols

    The newer verifiers use salted hashes, modern SHA-1 and SHA-2 hashing algorithms, and mixed-case passwords.

    The allowed_logon_version_server in the sqlnet.ora file is used to specify the minimum authentication protocol allowed when connecting to Oracle Database instances. 
    Oracle notes that the term “version” in the allowed_logon_version_server parameter name refers to the version of the authentication protocol.  It does NOT refer to the Oracle release version.

    – SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 generates all three password versions 10g, 11g, and 12c
    – SQLNET.ALLOWED_LOGON_VERSION_SERVER=12 generates both 11g and 12c password versions, and removes the 10g password version
    – SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a generates only the 12c password version

    8. New init.ora parametercalled OUTBOUND_DBLINK_PROTOCOLS

    Due to direct SQL*Net Access Over Oracle Cloud, existing applications can now use Oracle Cloud without any code changes. We can easily control the outbound database link options:

    – OUTBOUND_DBLINK_PROTOCOLS specifies the allowed network protocols for outbound database link connections: this can be used to restrict database links to use secure protocols
    – ALL_GLOBAL_DBLINKS allows or disallow global database links, which look up LDAP by default

    9. SYSRAC – Separation of Duty in a RAC

    SYSRAC is a new role for Oracle Real Application Clusters (Oracle RAC) management. This administrative privilege is the default mode for connecting to the database by the clusterware agent on behalf of the Oracle RAC utilities such as srvctl. For example, we can now create a named administrative account and grant only the administrative privileges needed such as SYSRAC and SYSDG to manage both Oracle RAC and Oracle Data Guard configurations.

    10. Automatic Locking of Inactive User Accounts

    CREATE PROFILE time_limit LIMIT INACTIVE_ACCOUNT_TIME 60;
    

    Within a user profile, the INACTIVE_ACCOUNT_TIME parameter controls the maximum time that an account can remain unused. The account is automatically locked if a log in does not occur in the specified number of days. Locking inactive user accounts prevents attackers from using them to gain access to the database. The minimum setting is 15 and the maximum is 24855. The default for INACTIVE_ACCOUNT_TIME is UNLIMITED.

    11. Kerberos-Based Authentication for Direct NFS

    Oracle Database now supports Kerberos implementation with Direct NFS communication. This feature solves the problem of authentication, message integrity, and optional encryption over unsecured networks for data exchange between Oracle Database and NFS servers using Direct NFS protocols.

    12. Lockdown Profiles

    Lockdown profile is a mechanism used to restrict operations that can be performed by connections to a given PDB for both cloud and non-cloud.

    There are three functionalities that you can disable:

    Feature: it lets us enable or disable database features for say junior DBAs (or cowboy DBAs)
    Option: for now, the two options we can enable/disable are “DATABASE QUEUING” and “PARTITIONING”
    Statement: we can either enable or disable the statements “ALTER DATABASE”, “ALTER PLUGGABLE DATABASE”, “ALTER SESSION”, and “ALTER SYSTEM”. In addition, we can specify granular options along with these statements. Example:

    ALTER LOCKDOWN PROFILE junior_dba_prof STATEMENT = ('ALTER SYSTEM') 
    CLAUSE = ('SET')  OPTION= ('OPTIMIZER_INDEX_COST_ADJ');
    

    But .. the most secure database is the database with no users connected to it.

    GDPR for DBAs

    In DBA, Security and auditing on May 25, 2017 at 07:10

    Exactly one year from now, from May 25th 2018, all businesses that handle personal data will have to comply with the new General Data Protection Regulation (GDPR) legislation.

    At 260 pages in length, with 99 Articles and over 100 pages of explanatory notes known as ‘Annexes’, the GDPR is roughly three times the length of the Data Protection Act 1998 it is replacing.

    The requirements for databases are:

    – Discovery
    – Classification
    – Masking
    – Monitoring
    – Audit reporting
    – Incident response and notification

    The maximum penalty for non-compliance is 4% of annual revenue or €20 million, whichever is higher. Lower fines of up to 2% are possible for administrative breaches, such as not carrying out impact assessments or notifying the authorities or individuals in the event of a data breach. This puts data protection penalties into same category as anti-corruption or competition compliance.

    What DBAs should start with now is account and identify 100% of the private data located in all databases!

    There are 4 major categories where DBAa will be involved. The details can be found in the appendix on page 19/23 entitled Mapping of Oracle Database Security Products to GDPR.

    1. Assess (Article 35 and Recital 84)
    2. Prevent (Articles 5,6,29,32 and Recitals 26,28,64,83)
    3. Detect (Articles 30,33,34)
    4. Maximum protection (Articles 25,32)

    Article 25 is about data minimization, user access limits and limit period of storage and accessibility.
    Article 32 is about pseudonymization and encryption, ongoing protection and regular testing and verification.
    Article 33 and 34 are about data breach notification: there is 72 hour notification following discovery of data breach.
    Article 35 is about the data protection impact assessment.
    Article 44 treats data transfers to third country or international organizations where the allowed transfers are only to entities in compliance with the regulation.

    As you can see, DBA job ads include nowadays the GDPR skills and responsibilities:

    The main lawful bases for data processing are consent and necessity. Data can be recognized as a necessity if it:

    • Relates to the performance of a contract
    • Illustrates compliance with a legal obligation
    • Protects the vital interests of the data subject or another person
    • Relates to a task that’s in the public interest
    • Is used for purposes of legitimate interests pursued by the controller or a third party (expect where overridden by the rights of the data subject)

    Data subjects’ requests for access should be responded to within a month and without charge. This is new legislation within the GDPR and the same one month time frame applies to rectifying inaccurate data.

    Breach notifications should be made within 72 hours of becoming aware. If this time frame isn’t met, a fine of 10M€, or 2% of global turnover, can be issued as a penalty. A breach is any failure of security leading to the destruction, loss, alteration, unauthorized disclosure of/access to personal data. Supervisory authorities must be notified if a breach results in a risk to the rights and freedoms of individuals.

    Data held in an encrypted or pseudonymized form isn’t deemed to be personal data and falls outside of the scope of these new rules altogether. Despite this, data that’s encrypted and considered secure using today’s technology may become readable in the future. Therefore it’s worth considering format preserving encryption/pseudonymization which renders anonymous but stills allows selected processing of that data.

    Here are few interesting articles meant mostly for DBAs:

    Accelerate Your Response to the EU General Data Protection Regulation (GDPR)

    Data Privacy and Protection GDPR Compliance for Databases

    European Union GDPR compliance for the DBA

    SQL Server 2016 – Always Encrypted and the GDPR

    How Oracle Security Solutions Can Help the EU GDPR

    Top 10 operational impacts of the GDPR

    Cloud Nine

    In Cloud, DBA, IaaS, Oracle database on May 3, 2017 at 16:58

    “Get happiness out of your work or you may never know what happiness is.” — Elbert Hubbard

    According to Amazon and quoted by Fortune Magazine in a recent article entitled “Amazon Data Center Chief schools Oracle CEO on Cloud claims“, AWS executive Andy Jassy said (at AWS Re:Invent two years ago) that every database customer he talks to is unhappy with their vendor: “I haven’t met a database customer that is not looking to flee their vendor.”

    Another interesting article by James Hamilton entitled “How many Data Centers needed world-wide” discusses more or less the same topic. Reading the comments after it is worthwhile. Also consider reading these extensive performance results about Cloud performance and TCO of the Oracle database.

    A young and extremely smart analyst from my company asked me last week: “Why is the Oracle database better than MySQL or MongoDB?”. Tough question, right? You may ask the same about DB2 or SQL Server. All databases have their pros and cons. And we as people have our preferences, based on experience, knowledge and prejudices.

    If you try to find out the explanation of the quote statement on top, you might very like end up with this one: “You have to spend most of your life working, so if you’re unhappy at your work you’re likely to always be unhappy”.

    So, I have been happy (if that is the right word) working with the Oracle database. Unlike DB2, you have all the tools, options and automation to tune it. With about couple of hundred MySQL databases at Nokia, we spent more time (thank you Google!) investigating issues than with more than one thousand Oracle databases. SQL Server: if you prefer using the mouse instead of the keyboard, then this is the right database for you! Teradata compared to Exadata: let me not start…

    As Forrester say, In-Memory Databases are driving next-generation workloads and use cases. Check out this recent comparison of all vendors.

    But back to Cloud. Have a look at what speed and what features Oracle is embedding into its Cloud. By far the best Cloud for Oracle workloads! All these are new additions to the Oracle IaaS:

    What’s New for Oracle Compute Cloud Service (IaaS)

    – Compute Service: 8 and 16 OCPU Virtual Machines

    CentOS, Ubuntu OS Images

    RHEL via BYOI OS Image

    Multipart Upload: Multipart upload enables uploading an object in parts, enhancing speed of upload and accommodating larger objects

    Audit Service: This new service automatically records calls to all supported BMCS public API endpoints as log events

    Search Domain DHCP

    Terraform Provider: The BMCS Terraform provider is now available. Terraform is an open source infrastructure automation and management software tool

    Developer Tools Enhancements: New versions of BMCS developer tools are now available, including Ruby, Python, and Java SDKs, HDFS Connector, and CLI

    New Instance Shapes

    – Windows BYOL: It is now possible to Bring Your Own License (BYOL) for Windows Server

    – NVMe Storage: You can now use NVMe SSD disks as ephemeral data disks attached to your instances

    – SSD Block Storage: These high-performance volumes can be used for persistent block storage or for bootable volumes

    New Web UI: This new interface can be used to perform basic operations against Storage Cloud resources

    HSM Cloud Service Integration

    Oracle Exadata Cloud Machine Q&A

    In Cloud, Database options, DBA, Exadata, IaaS, Oracle database, Oracle Engineered Systems on March 28, 2017 at 07:25

    “The computer industry is the only industry that is more fashion-driven than women’s fashion.” Larry Ellison

    Amazon have no on-premise presence, it is cloud only. Microsoft have the Azure Stack but the Azure Stack Technical Preview 3 is being made available as a Proof of Concept (POC) and must not be used as a production environment and should only be used for testing, evaluation, and demonstration.

    Oracle Database Exadata Public Cloud Machine, or Exadata Cloud Machine, or ExaCM in short, is a cloud-based Oracle database subscription service available on Oracle Exadata, and deployed in the customer or partner data center behind their firewall. This allows customers to subscribe to fully functional Oracle databases on Exadata, on an OPEX driven consumption model, using agile cloud-based provisioning, while the associated Exadata infrastructure is maintained by Oracle.

    This blog post contains the top 10 (5 commercial and 5 technical) facts about the Exadata Cloud Machine:

    1. On-premise licenses cannot be transferred to ExaCM.

    2. The minimum commitment to both the ExaCM and OCM is 4 years and the minimum configuration is Eighth Rack.

    3. The subscription price for Oracle Database Exadata Cloud Machine X6 Eighth Rack is $40,000 per month (= $2,500 X 16) and that includes all DB options/features, Exadata Software and OEM DB Packs.

    4. Standalone products such as Oracle Secure Backup and Oracle GoldenGate are not included in the ExaCM subscription. Only the database options (such as RAC, In-Memory, Partitioning, Active Data Guard, etc.), the database OEM packs and the Exadata storage server software are included.

    5. ExaCM requires Oracle Cloud Machine to deploy Exadata Cloud Control Plane (separate subscription). OCM subscription requires similar minimum term commitment as ExaCM. If a customer already has an OCM, that can be leveraged to deploy Exadata Control Plane at no extra cost. One OCM Model 288 can manage 6 ExaCM Full Racks (i.e. 24 ExaCM Quarter Racks or 12 ExaCM Half Racks). Theoretically one OCM can support a much larger number of ExaCM full racks: about 50.

    6. The 1/8th rack SKU is very similar to the on-premises 1/8th rack – i.e. minimum configuration of 16 OCPUs (cores), 240 GB RAM per database server, 144 TB raw storage (42 TB usable), 19.2TB of Flash. Compared to the Quarter Rack, it ships with less RAM, disk storage and flash. Those will be field installed if the customer chooses to go for the 1/8th to Quarter Rack upgrade. Note that this 1/8th rack enables customers to have an entry level configuration that is similar to what exists in Exadata Cloud Service.

    7. Hourly Online Compute Bursting is supported with ExaCM. The commercial terms are the same as in Exadata Cloud Service – i.e. 25% premium over the Metered rate, calculated on an hourly basis. Customers can scale up or down, dynamically. Bursting does not kick in automatically based on load. Bursting of OCPUs needs to be configured by customers as needed. Once customer initiates bursting, the OCPU update is done dynamically without downtime. Customers will be billed later on the hours of bursting usage. Price: $8.401 per OCPU per hour.

    8. If Cloud Control Plane is down, it doesn’t affect the availability of steady state runtime operations. However, cloud-based management (e.g. selfservice UI and REST API access) will be impacted.

    9. Access: the Exadata Cloud Machine compute nodes are each configured with a Virtual Machine (VM). You have root privilege for the Exadata compute node VMs, so you can load and run additional software on the Exadata compute nodes. However, you do not have administrative access to the Exadata infrastructure components, including the physical compute node hardware, network switches, power distribution units (PDUs), integrated lights-out management (ILOM) interfaces, or the Exadata Storage Servers, which are all administered by Oracle.

    10. Patching and backups: you can produce a list of available patches using the exadbcpatchmulti command as follows

    # /var/opt/oracle/exapatch/exadbcpatchmulti -list_patches 
    -sshkey=/home/opc/.ssh/id_rsa 
    -oh=hostname1:/u01/app/oracle/product/12.2.0.1/dbhome_1
    

    When you create a database deployment on Exadata Cloud Machine, you must choose from the following automatic backup configuration options:

    – Remote Storage Only: uses remote NFS storage to store periodic full (RMAN level 0) backups and daily incremental backups, with a seven day cycle between full backups and an overall retention period of thirty days.
    – None: no automatic backups are configured. Automatic backups cannot be configured later if you select the None option when you create a database deployment.

    Useful links:

    Oracle Exadata Cloud Machine Documentation
    Oracle Database Exadata Cloud Machine Data Sheet
    Features and Benefits of Oracle ExaCM
    Oracle Database Exadata Cloud Machine Pricing
    Creating an Exadata Cloud Machine Instance
    Known Issues for Oracle Exadata Cloud Machine
    Oracle SVP, Juan Loaiza, describes Oracle Database Exadata Cloud Machine

    DBA Productivity and Oracle Database 12.2

    In Cloud, DBA, Oracle database on February 9, 2017 at 15:15

    “Technology can be our best friend, and technology can also be the biggest party pooper of our lives. It interrupts our own story, interrupts our ability to have a thought or a daydream, to imagine something wonderful, because we’re too busy bridging the walk from the cafeteria back to the office on the cell phone.” Steven Spielberg

    busy

    The DBA profession was recently rated as #6 among the Best Technology Jobs. Good for all of us who are in this line of business. But notice the stress level: Above Average!

    DBAs are often busy people. Is that good or bad? Is “busy the new stupid”?

    Automation is not a luxury for the DBAs but it is a way in which DBAs execute their job. Of course, there is one thing that cannot be automated and that is quality but the best DBAs automate almost everything.

    Automating the database is a Win-Win for DBAs and DevOps. The mindset of the Enterprise DBA should be focused on harnessing the power of automation.

    The following data shows what tasks are mostly and least automated:

    dba_automation

    Look at the last row above. I still wonder why Automatic SQL Tuning is so underestimated. It was so powerfully helping the DBA team of Nokia…

    Oracle Database 12cR2 is out. And 12.2 comes with yet another new set of database automation related features:

    – Oracle Data Guard now supports multiple failover targets in a fast-start failover configuration. Previous functionality allowed for only a single fast-start failover target. Multiple failover targets increase high availability by making an automatic failover more likely to occur if there is a primary outage.

    – Oracle automatically synchronizes password files in Data Guard configurations: when the passwords of SYS, SYSDG, and so on, are changed, the password file at the primary database is updated and then the changes are propagated to all standby databases in the configuration.

    – Online table move: nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.

    – Automatic deployment of Oracle Data Guard: deployment is automatic for Oracle Data Guard physical replication between shards with Oracle Data Guard fast-start failover (automatic database failover): automatic database failover provides high availability for server, database, network, and site outages.

    – Automatically set user tablespaces to read-only during upgrade: the new -T option for the parallel upgrade utility (catctl.pl) can be used to automatically set user tablespaces to read-only during an upgrade, and then back to read/write after the upgrade.

    – The Oracle Trace File Analyzer (TFA) collector provides the option to automatically collect diagnostic information when TFA detects an incident.

    – Oracle Data Guard support for Oracle Diagnostics Pack: this enables you to capture the performance data to the Automatic Workload Repository (AWR) for an Active Data Guard standby database and to run Automatic Database Diagnostic Monitor (ADDM) analysis on the AWR data.

    – Automatic Workload Repository (AWR) support for pluggable databases: the AWR can be used in a PDB. This enables the capture and storage of performance data in the SYSAUX tablespace of the PDB.

    – The new ENABLE_AUTOMATIC_MAINTENANCE_PDB initialization parameter can be used to enable or disable the running of automated maintenance tasks for all the pluggable databases (PDBs) in a multitenant container database (CDB) or for individual PDBs in a CDB.

    – Automatic Data Optimization Support for In-Memory Column Store: Automatic Data Optimization (ADO) enables the automation of Information Lifecycle Management (ILM) tasks. The automated capability of ADO depends on the Heat Map feature that tracks access at the row level (aggregated to block-level statistics) and at the segment level.

    – Automatic Provisioning of Kerberos Keytab for Oracle Databases: the new okcreate utility automates the registering of an Oracle database as a Kerberos service principal, creating a keytab for it, and securely copying the keytab to the database for use in Kerberos authentication.

    – Role-Based Conditional Auditing: auditing for new users with the DBA role would begin automatically when they are granted the role.

    – Automatic Locking of Inactive User Accounts: within a user profile, the new INACTIVE_ACCOUNT_TIME parameter controls the maximum time that an account can remain unused. The account is automatically locked if a log in does not occur in the specified number of days.

    12_2_automation