1. 2016-11-14 - Drop pluggable databases in Oracle DB; Tags: Drop pluggable databases in Oracle DB
    Loading...

    Drop pluggable databases in Oracle DB

    Why use PDBs? In a multitenant environment, each customer has their own PDB which is isolated from other customers’ PDBs. A clean (domain) separation. This post covers how to get rid of one. A quick example how to drop PDBs (pluggable databases) in the multi-database architecture of Oracle DB (since v12c).

    When you drop a PDB, the control file of the multitenant container database (CDB) is modified to remove all references to the dropped PDB and its data files. Archived logs and backups associated with the dropped PDB are not deleted.

    DROP PLUGGABLE DATABASE

    SQL> DROP PLUGGABLE DATABASE fraud INCLUDING DATAFILES
    *
    FEHLER in Zeile 1:
    ORA-65025: Integrierbare Datenbank FRAUD ist nicht auf allen Instanzen
    geschlossen.
    

    Close the pdb to resolve ORA-65025.

    SQL> ALTER PLUGGABLE DATABASE fraud CLOSE;
    Integrierbare Datenbank geΣndert.
    

    After this the drop works.

    SQL> DROP PLUGGABLE DATABASE fraud INCLUDING DATAFILES;
    Integrierbare Datenbank gel÷scht.
    
  2. 2015-11-04 - Add Oracle DB datasource to JBoss; Tags: Add Oracle DB datasource to JBoss
    Loading...

    Add Oracle DB datasource to JBoss

    This post demonstrates how to establish a data-source configuration for JBoss EAP with the Oracle DB.

    Dump the contents of the help command to help.txt

    [standalone@localhost:9999 /] data-source --help --properties > help.txt
    

    The contents

     --name                 - Required argument in commands which identifies the
                              instance to execute the command against.
     --share-prepared-statements  - (BOOLEAN,read-write) Whether to share prepared
                                    statements, i.e. whether asking for same
                            statement twice without closing uses the same underlying
                            prepared statement
     --driver-class         - (STRING,read-write) The fully qualified name of the
                              JDBC driver class
     --prepared-statements-cache-size  - (LONG,read-write) The number of prepared
                                         statements per connection in an LRU cache
     --spy                  - (BOOLEAN,read-write) Enable spying of SQL statements
     --password             - (STRING,read-write) Specifies the password used when
                              creating a new connection
     --max-pool-size        - (INT,read-write) The max-pool-size element specifies
                              the maximum number of connections for a pool. No more
                            connections will be created in each sub-pool
     --set-tx-query-timeout  - (BOOLEAN,read-write) Whether to set the query timeout
                               based on the time remaining until transaction
                            timeout. Any configured query timeout will be used if
                            there is no transaction
     --jndi-name            - (STRING,read-write) Specifies the JNDI name for the
                              datasource
     --url-delimiter        - (STRING,read-write) Specifies the delimiter for URLs
                              in connection-url for HA datasources
     --enabled              - (BOOLEAN,read-write) Specifies if the datasource
                              should be enabled. Note this attribute will not be
                            supported runtime in next versions.
     --connectable          - (BOOLEAN,read-write) Enable the use of CMR. This
                              feature means that a local resource can reliably
                            participate in an XA transaction.
     --blocking-timeout-wait-millis  - (LONG,read-write) The blocking-timeout-millis
                                       element specifies the maximum time, in
                            milliseconds, to block while waiting for a connection
                            before throwing an exception. Note that this blocks only
                            while waiting for locking a connection, and will never
                            throw an exception if creating a new connection takes an
                            inordinately long time
     --statistics-enabled   - (BOOLEAN,read-write) define if runtime statistics is
                              enabled or not.
     --pool-use-strict-min  - (BOOLEAN,read-write) Specifies if the min-pool-size
                              should be considered strictly
     --validate-on-match    - (BOOLEAN,read-write) The validate-on-match element
                              specifies if connection validation should be done when
                            a connection factory attempts to match a managed
                            connection. This is typically exclusive to the use of
                            background validation
     --transaction-isolation  - (STRING,read-write) Set the java.sql.Connection
                                transaction isolation level. Valid values are:
                            TRANSACTION_READ_UNCOMMITTED,
                            TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ,
                            TRANSACTION_SERIALIZABLE and TRANSACTION_NONE
     --jta                  - (BOOLEAN,read-write) Enable JTA integration
     --valid-connection-checker-class-name  - (STRING,read-write) An
                                              org.jboss.jca.adapters.jdbc.ValidConne
                            ctionChecker that provides an
                            isValidConnection(Connection) method to validate a
                            connection. If an exception is returned that means the
                            connection is invalid. This overrides the
                            check-valid-connection-sql element
     --allocation-retry-wait-millis  - (LONG,read-write) The allocation retry wait
                                       millis element specifies the amount of time,
                            in milliseconds, to wait between retrying to allocate a
                            connection
     --exception-sorter-properties  - (OBJECT,read-write) The exception sorter
                                      properties
     --background-validation-millis  - (LONG,read-write) The
                                       background-validation-millis element
                            specifies the amount of time, in milliseconds, that
                            background validation will run. Changing this value can
                            be done only on disabled datasource,  requires a server
                            restart otherwise
     --track-statements     - (STRING,read-write) Whether to check for unclosed
                              statements when a connection is returned to the pool,
                            result sets are closed, a statement is closed or return
                            to the prepared statement cache. Valid values are:
                            "false" - do not track statements, "true" - track
                            statements and result sets and warn when they are not
                            closed, "nowarn" - track statements but do not warn
                            about them being unclosed
     --use-fast-fail        - (BOOLEAN,read-write) Whether to fail a connection
                              allocation on the first try if it is invalid (true) or
                            keep trying until the pool is exhausted of all potential
                            connections (false)
     --flush-strategy       - (STRING,read-write) Specifies how the pool should be
                              flush in case of an error. Valid values are:
                            FailingConnectionOnly (default), IdleConnections and
                            EntirePool
     --stale-connection-checker-class-name  - (STRING,read-write) An
                                              org.jboss.jca.adapters.jdbc.StaleConne
                            ctionChecker that provides an
                            isStaleConnection(SQLException) method which if it
                            returns true will wrap the exception in an
                            org.jboss.jca.adapters.jdbc.StaleConnectionException
     --exception-sorter-class-name  - (STRING,read-write) An
                                      org.jboss.jca.adapters.jdbc.ExceptionSorter
                            that provides an isExceptionFatal(SQLException) method
                            to validate if an exception should broadcast an error
     --background-validation  - (BOOLEAN,read-write) An element to specify that
                                connections should be validated on a background
                            thread versus being validated prior to use. Changing
                            this value can be done only on disabled datasource,
                            requires a server restart otherwise.
     --check-valid-connection-sql  - (STRING,read-write) Specify an SQL statement to
                                     check validity of a pool connection. This may
                            be called when managed connection is obtained from the
                            pool
     --reauth-plugin-class-name  - (STRING,read-write) The fully qualified class
                                   name of the reauthentication plugin
                            implementation
     --allow-multiple-users  - (BOOLEAN,read-write) Specifies if multiple users will
                               access the datasource through the getConnection(user,
                            password) method and hence if the internal pool type
                            should account for that
     --url-selector-strategy-class-name  - (STRING,read-write) A class that
                                           implements
                            org.jboss.jca.adapters.jdbc.URLSelectorStrategy
     --user-name            - (STRING,read-write) Specify the user name used when
                              creating a new connection
     --use-ccm              - (BOOLEAN,read-write) Enable the use of a cached
                              connection manager
     --reauth-plugin-properties  - (OBJECT,read-write) The properties for the
                                   reauthentication plugin
     --driver-name          - (STRING,read-write) Defines the JDBC driver the
                              datasource should use. It is a symbolic name matching
                            the the name of installed driver. In case the driver is
                            deployed as jar, the name is the name of deployment unit
     --stale-connection-checker-properties  - (OBJECT,read-write) The stale
                                              connection checker properties
     --datasource-class     - (STRING,read-write) The fully qualified name of the
                              JDBC datasource class
     --idle-timeout-minutes  - (LONG,read-write) The idle-timeout-minutes elements
                               specifies the maximum time, in minutes, a connection
                            may be idle before being closed. The actual maximum time
                            depends also on the IdleRemover scan time, which is half
                            of the smallest idle-timeout-minutes value of any pool.
                            Changing this value can be done only on disabled
                            datasource, requires a server restart otherwise.
     --query-timeout        - (LONG,read-write) Any configured query timeout in
                              seconds. If not provided no timeout will be set
     --use-java-context     - (BOOLEAN,read-write) Setting this to false will bind
                              the datasource into global JNDI
     --valid-connection-checker-properties  - (OBJECT,read-write) The valid
                                              connection checker properties
     --min-pool-size        - (INT,read-write) The min-pool-size element specifies
                              the minimum number of connections for a pool
     --allocation-retry     - (INT,read-write) The allocation retry element
                              indicates the number of times that allocating a
                            connection should be tried before throwing an exception
     --security-domain      - (STRING,read-write) Specifies the security domain
                              which defines the javax.security.auth.Subject that are
                            used to distinguish connections in the pool
     --new-connection-sql   - (STRING,read-write) Specifies an SQL statement to
                              execute whenever a connection is added to the
                            connection pool
     --connection-url       - (STRING,read-write) The JDBC driver connection URL
     --use-try-lock         - (LONG,read-write) Any configured timeout for internal
                              locks on the resource adapter objects in seconds
     --pool-prefill         - (BOOLEAN,read-write) Should the pool be prefilled.
                              Changing this value can be done only on disabled
                            datasource, requires a server restart otherwise.
    

    Add driver modules

    #    _     _ _
    #   (_) __| | |__   ___
    #   | |/ _` | '_ \ / __|
    #   | | (_| | |_) | (__
    #  _/ |\__,_|_.__/ \___|
    # |__/
    #
    # install jdbc driver with i18n
    # module remove --name=com.oracle.jdbc6
    # module remove --name=com.oracle.orai18n
    module add --name=com.oracle.jdbc6 \
        --resources=${deploy.dir}/deploy/jdbc/ojdbc6-11203.jar \
        --dependencies=javax.api,javax.transaction.api
    module add --name=com.oracle.orai18n \
        --resources=${deploy.dir}/deploy/jdbc/orai18n-11203.jar
        --dependencies=com.oracle.jdbc6,javax.api,javax.transaction.api
    

    Add datasource

    #      _       _
    #   __| | __ _| |_ __ _ ___  ___  _   _ _ __ ___ ___
    #  / _` |/ _` | __/ _` / __|/ _ \| | | | '__/ __/ _ \
    # | (_| | (_| | || (_| \__ \ (_) | |_| | | | (_|  __/
    #  \__,_|\__,_|\__\__,_|___/\___/ \__,_|_|  \___\___|
    #
    /subsystem=datasources/jdbc-driver=oracle:add( \
        driver-name=oracle, \
        driver-module-name=com.oracle.jdbc6, \
        driver-xa-datasource-class-name=oracle.jdbc.driver.OracleDriver)
    # pool-name will be demo
    /subsystem=datasources/data-source=demo:add( \
        jta=true, \
        jndi-name=java:jboss/datasources/demo, \
        enabled=true, \
        use-java-context=true, \
        use-ccm=true, \
        connection-url=${jdbc.url}, \
        driver-name=oracle, \
        transaction-isolation=TRANSACTION_READ_COMMITTED, \
        min-pool-size=1, \
        max-pool-size=10, \
        pool-prefill=true, \
        flush-strategy=FailingConnectionOnly, \
        user-name=scott, \
        password=tiger, \
        check-valid-connection-sql="SELECT 1 FROM DUAL")