Oracle Cheatsheet

Wednesday, February 26, 2020

How to Resolve “ORA-29548: Java system class reported”

After applying Jan 14th 2020 CPU patch, one of the java program in the database getting error.

ORA-29548: Java system class reported: joxcsys: release mismatch, 
12.2.0.1.180717 1.8 in database (classes.bin) vs 12.2.0.1.0 1.8 in executable

Run this in sqlplus to resolve the issue.

@?/javavm/install/update_javavm_db.sql

Thursday, November 7, 2019

Tuning Tomcat 9 for APEX/ORDS in Production

Logging

Configuration File: $CATALINA_HOME/conf/logging.properties
Change all occurrences of FINE and INFO to WARNING or SEVERE

JVM Options

Run tomcat9w.exe
Change Initial Memory pool and Maximum memory pool to 4096.

Main Tomcat Server Configurations

Configuration File: $CATALINA_HOME/conf/server.xml

<Connector port="443" protocol="org.apache.coyote.http11.Http11AprProtocol" 
  SSLEnabled="true" scheme="https" secure="true"
  maxHttpHeaderSize="32767"
  compression="on"
  disableUploadTimeout="true">
  <UpgradeProtocol overheadDataThreshold="0" compression="on" className="org.apache.coyote.http2.Http2Protocol" />

...

</Connector>

Close to the end of server.xml

<Host name="localhost" appBase="webapps"
            unpackWARs="true" autoDeploy="true">

        <Context path="/ords" reloadable="false" />

...

</Host>

Oracle REST Data Service

Configuration File: defaults.xml
Change the limits

<entry key="cache.metadata.enabled">true</entry>
<entry key="jdbc.InitialLimit">20</entry>
<entry key="jdbc.MinLimit">20</entry>
<entry key="jdbc.MaxLimit">50</entry>
<entry key="jdbc.MaxStatementsLimit">20</entry>

Wednesday, September 11, 2019

UNDO tablespace growing non-stop Part 2

This the original post and afterward we find out the culprit is the Flashback Data Archive (FDA).

It is a simple fix. We just need to bounce the FDA and within a few hours, oracle should release all the spaces.

BEGIN
  dbms_flashback_archive.disable_application(application_name => 'PARIS_AUDIT');
  dbms_flashback_archive.enable_application(application_name => 'PARIS_AUDIT');
END;
/

Thursday, July 11, 2019

Oracle sql statement audit for an user

Enable Audit for an user

CREATE AUDIT POLICY penweb_api_audit_policy
ACTIONS DELETE, INSERT, UPDATE, SELECT
WHEN    'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''PENWEB_API'''
EVALUATE PER SESSION;

AUDIT POLICY penweb_api_audit_policy;

SELECT object_schema,
object_name,
object_type,
audit_option,
condition_eval_opt,
audit_condition
FROM   audit_unified_policies
WHERE  policy_name = 'PENWEB_API_AUDIT_POLICY';

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

Check the Audit trail

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

SELECT *
FROM   unified_audit_trail
WHERE  dbusername = 'PENWEB_API'
ORDER BY event_timestamp desc;

Remove Audit

NOAUDIT POLICY penweb_api_audit_policy;
DROP AUDIT POLICY penweb_api_audit_policy;

Monday, June 17, 2019

Remote Desktop Services Manager Command Line

From time to time, we cannot RDP into servers because the maximum # of users limit has been reached.
Here is what we do to kill the orphan sessions to make room.

List of Sessions

C:\Users\CyberArkDBA>qwinsta /server:parisdb02p
SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
services                                    0  Disc
console                                     1  Conn
CyberArkDBA                                 9  Disc
31c5ce94259d4...                        65536  Listen
rdp-tcp                                 65537  Listen

Reset a Session

Look for the ID that you want to reset. In this case, it is ID #9.

C:\Users\CyberArkDBA>rwinsta 9 /server:parisdb02p /V

List of Sessions again

C:\Users\CyberArkDBA>qwinsta /server:parisdb02p
SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
services                                    0  Disc
console                                     1  Conn
31c5ce94259d4...                        65536  Listen
rdp-tcp                                 65537  Listen

Tuesday, May 14, 2019

ORDS OAuth 2 Client Credentials

Setup Auto REST for one table with OAuth2

BEGIN
  oauth.create_client(p_name => 'OAuth2 Dynamics Document Client', p_grant_type => 'client_credentials', p_owner => 'LEAVEMEALONE', 
    p_description => 'A client for Dynamics Document', 
    p_support_email   => 'no-reply@leavemealone.com', 
    p_privilege_names => 'oracle.dbtools.autorest.privilege.LEAVEMEALONE');
  ords.enable_object(p_schema => 'LEAVEMEALONE', p_object => 'AD_USER', p_object_alias => 'ad_user', p_auto_rest_auth => true);
  oauth.grant_client_role(p_client_name => 'OAuth2 Dynamics Document Client', p_role_name => 'oracle.dbtools.role.autorest.LEAVEMEALONE');
  oauth.grant_client_role(p_client_name => 'OAuth2 Dynamics Document Client', p_role_name => 'oracle.dbtools.role.autorest.LEAVEMEALONE.AD_USER');
  COMMIT;
END;
/

Find out the CLIENT_ID and CLIENT_SECRET to retrieve the ACCESS_TOKEN

SELECT
*
FROM   user_ords_clients;

        ID NAME                            CLIENT_ID                        CLIENT_SECRET                   
---------- ------------------------------- -------------------------------- --------------------------------       
     10130 OAuth2 Dynamics Document Client K5DGLFxUHTbQ_yCaSc1y3A..         SCtjsrfxag2DWcQ35TjFuw..

SELECT
  client_name,
  role_name
FROM
  user_ords_client_roles;

CLIENT_NAME                     ROLE_NAME
------------------------------- -------------------------------------------------
OAuth2 Dynamics Document Client oracle.dbtools.role.autorest.LEAVEMEALONE
OAuth2 Dynamics Document Client oracle.dbtools.role.autorest.LEAVEMEALONE.ADUSER

Retrieve ACCESS_TOKEN

curl --request POST \
--url https://tst-opptools.leavemealone.com/ords/uat12c/opt/oauth/token \
--header 'Accept: */*' \
--header 'Authorization: Basic N1V1MHVtRy13bnBxWWg3WWtHOUtCQS4uOnptTjE4VnVhQlBpX2FpVEJCcnJncXcuLg==' \
--header 'Cache-Control: no-cache' \
--header 'Connection: keep-alive' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Host: tst-opptools.leavemealone.com' \
--header 'accept-encoding: gzip, deflate' \
--header 'cache-control: no-cache' \
--header 'content-length: 29' \
--data grant_type=client_credentials

Response

{
    "access_token": "-YDi4OaEGgN-mgjGcUfYrg",
    "token_type": "bearer",
    "expires_in": 3600
}

Use the ACCESS_TOKEN to access table

curl --request GET \
--url https://tst-opptools.leavemealone.com/ords/uat12c/opt/ad_user/ \
--header 'Accept: */*' \
--header 'Authorization: Bearer -YDi4OaEGgN-mgjGcUfYrg' \
--header 'Cache-Control: no-cache' \
--header 'Connection: keep-alive' \
--header 'Host: tst-opptools.leavemealone.com' \
--header 'accept-encoding: gzip, deflate' \
--header 'cache-control: no-cache'

Thursday, March 14, 2019

Google Analytics with Oracle

1. Use Query Explorer to generate the list

https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A187243477&start-date=2019-01-01&end-date=2019-03-14&metrics=ga%3Ausers&dimensions=ga%3AdeviceCategory%2Cga%3AscreenResolution&sort=-ga%3Ausers&include-empty-rows=false&max-results=10

2. JSON Response

{
   "kind":"analytics#gaData",
   "id":"https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&dimensions=ga:deviceCategory,ga:screenResolution&metrics=ga:users&sort=-ga:users&start-date=2019-01-01&end-date=2019-03-14&max-results=10",
   "query":{
      "start-date":"2019-01-01",
      "end-date":"2019-03-14",
      "ids":"ga:187243477",
      "dimensions":"ga:deviceCategory,ga:screenResolution",
      "metrics":[
         "ga:users"
      ],
      "sort":[
         "-ga:users"
      ],
      "start-index":1,
      "max-results":10
   },
   "itemsPerPage":10,
   "totalResults":39,
   "selfLink":"https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&dimensions=ga:deviceCategory,ga:screenResolution&metrics=ga:users&sort=-ga:users&start-date=2019-01-01&end-date=2019-03-14&max-results=10",
   "nextLink":"https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&dimensions=ga:deviceCategory,ga:screenResolution&metrics=ga:users&sort=-ga:users&start-date=2019-01-01&end-date=2019-03-14&start-index=11&max-results=10",
   "profileInfo":{
      "profileId":"187243477",
      "accountId":"131677935",
      "webPropertyId":"UA-131677935-1",
      "internalWebPropertyId":"191281733",
      "profileName":"All Web Site Data",
      "tableId":"ga:187243477"
   },
   "containsSampledData":false,
   "columnHeaders":[
      {
         "name":"ga:deviceCategory",
         "columnType":"DIMENSION",
         "dataType":"STRING"
      },
      {
         "name":"ga:screenResolution",
         "columnType":"DIMENSION",
         "dataType":"STRING"
      },
      {
         "name":"ga:users",
         "columnType":"METRIC",
         "dataType":"INTEGER"
      }
   ],
   "totalsForAllResults":{
      "ga:users":"130"
   },
   "rows":[
      [
         "desktop",
         "1920x1080",
         "34"
      ],
      [
         "desktop",
         "800x400",
         "32"
      ],
      [
         "desktop",
         "1600x900",
         "6"
      ],
      [
         "tablet",
         "1920x1080",
         "6"
      ],
      [
         "desktop",
         "1280x720",
         "4"
      ],
      [
         "desktop",
         "1368x912",
         "4"
      ],
      [
         "desktop",
         "1688x906",
         "2"
      ],
      [
         "desktop",
         "1824x1216",
         "2"
      ],
      [
         "desktop",
         "1829x1029",
         "2"
      ],
      [
         "desktop",
         "2057x1157",
         "2"
      ]
   ]
}

3. Parse the response into table records. We can now use the table records in report, grid, chart etc...

SELECT
    jt.*
FROM
        JSON_TABLE ( <<response data from above>>, '$'
            COLUMNS (
                "kind" VARCHAR2 ( 255 ) PATH '$.kind',
                "totalsforallresults" NUMBER PATH '$.totalsForAllResults."ga:users"',
                NESTED PATH '$.rows[*]'
                    COLUMNS (
                        "series" VARCHAR2 ( 255 ) PATH '$[0]' NULL ON ERROR,
                        "label" VARCHAR2 ( 255 ) PATH '$[1]' NULL ON ERROR,
                        "value" NUMBER PATH '$[2]' NULL ON ERROR
                    )
            )
        )
    jt

Sunday, January 27, 2019

Google Analytics authentication using OAuth 2.0 for Server to Server Applications

1. In Google API Console, search for Google Analytics and enable it

2. Create credential, pick service account key, pick project owner and use json key type. Google will download the JSON private key file to your computer

Sample JSON private key file

{
  "type": "service_account",
  "project_id": "penweb",
  "private_key_id": "3ec4cfc55497e888886099ded8c0c4321be4034b",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCv6/lV5nqGEDSb\nwTqoQxedjtChDsfSnX8QqAxSbWJK4z6foQ/DxjYdL+uH557eA+ftN1iQ+nf9RfvM\nxo0pbSj7hF/9MRZucsHsPTs5/AlegqiBBwQLKhPao1fUvoENLoSe9Vm/sohCm7eR\nuJiU5QBJLDuOk4msGtVi/CmVZQkmiTJpkh312345GQsMLRhh9brG5jAoYFFVgQoV\n6Pf8gjLkbC0Y10IICnhV/YSB3ygGsQc1WxZFfyBQVmTK4USghm5WBoci3MBavPaS\nGsjrNtg3DbY1H+UVrmc9GQ6JVLMpP6K5smIbVEJremSM1gMbHoGap/qpH2pHtSgj\nDVP0Fv3dAgMBAAECggEAJtLpw5CaDSCh75bBCnXGDi5Asi6YIE3ER0mC8KT1uoe0\noBd0KnVBp4bWELfn6mDLzCD5AYPZO7K4a3yNFi2iTAGuimrNI8JdNDTeL4GFEs+8\nfr8s4Abg+1EtlsSVJ/LSb18/t4vfwO1rIXRu7fss6TkjCqfaS/pfCdmTFsaFsrRa\nBIzAJufy6MHc3e692kOruavxD0kZi4aESsNgjL1LL7TxltGvTPj0sYXrDXxYfVRL\nHbGDcmWpxsarimKY+sTxNUPRj778uT9RWtZqw2WAgBXMmhQo6kNCsmxKKJzJC9ez\nWO1KATt23bJ7A3Ak09876a6G8mqeHAeGin85lfN2+QKBgQDm4Zn61ybijeqAZqkm\nSwWYlPWu7Zdw+WOeGr1aGeSjPLwa32r17Fh17BYzl9e2FBiBZDOahn/z65HyT7Gl\nSjSqry1k9W0kuIR2L/Ovx/k7E49vyaQqo24IH643wpN3eZzgSbbYAgTyj7bywELa\nZzeH1LCc/3kRGYJ0JYh3aIOKGQKBgQDDD6qQIzIw7aIe3+otDqHCDSEZvy2XtBHZ\nZdULdapqyAup4wY6Wi5HRqpOoM5L66J5vKoF57/IN4kvsY28YqK0+GewLwcyLDfh\nVAKQREA/n5d8R17gmsE/BqM7+B3k+cyJfoHpE61lDSdYHikPth+Hve3nJhENc+O5\na11391HSZQKBgQDEO796Gd/s8x37IEZoS59n/kOK+FmdJfpraOLDb/q54321aHMq\nrkTJhW2hUE+BbDcf0qChl+usz+3t+5pLmHSHFfkJNCgd6855/WurXdYRRiDFrKah\nruJYeUTEE9bu1yhk4YJMufqmh0vBq3Om2c+y3S13YxPtWrcsLLJ+exBCEQKBgGFX\ncxWlCsoR/IKN2W4MzDprlOYlWppHGVU1FUE3wgixtehcy+HY2RlmsVy1sN2ARL+d\nn/d8dF91H3f2kjW9v1ayVAQ/I8cs++Htq63U8X8OpGk3sKhDckaCiHlH05tN8X13\n7VqkIDa6frThsDoP9+IkrRFaFM5y0+TXKHjSqjllAoGATMQScip6IwgPoXRvMPvZ\nB6CDEZc2+xxrpCLqVVc9hj3Qx1X01UhpoZ6POS9ALHDTiMxp4+O8gjqz/lQ8GVIt\nSq8zYlhJjBiXGkdtNiyFHqQO7HgAWGPEVgBxB3yznOw0ASHlvK6SmAq+nqBM8K1v\nYAPDle9crXVN+SelID+xUs8=\n-----END PRIVATE KEY-----\n",
  "client_email": "apex-39@penweb.iam.gserviceaccount.com",
  "client_id": "116219189999913805651",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/apex-39%40penweb.iam.gserviceaccount.com"
}

3. Add a new user to the Google Analytics Dashboard. When ask for email, use the client_email from the JSON file. Give the new user permission for read.

4. Build the JWT file for authentication. I use https://jwt.io
Algorithm : RS256
Payload Data, change iat to current epoch time, exp to 1 hour later, https://www.epochconverter.com/

{
  "iss":"apex-39@penweb.iam.gserviceaccount.com",
  "scope":"https://www.googleapis.com/auth/analytics.readonly",
  "aud":"https://www.googleapis.com/oauth2/v4/token",
  "exp":1548635913,
  "iat":1548632524
}

5. Generate Signature
You should find two text boxes. Paste the private key in the JSON private key file into the private key box without the \n

It will say invalid signature because we didn't provide the public key, but all we need is the encoded signature.

6. Server to Server Authentication
Put the whole encoded signature from jwt.io in the assertion parameter.

Request

curl --request POST \
  --url https://www.googleapis.com/oauth2/v4/token \
  --header 'Content-Type: application/x-www-form-urlencoded' \
  --header 'cache-control: no-cache' \
  --data 'grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJhcGV4LTM5QHBlbndlYi5pYW0uZ3NlcnZpY2VhY2NvdW50LmNvbSIsInNjb3BlIjoiaHR0cHM6Ly93d3cuZ29vZ2xlYXBpcy5jb20vYXV0aC9hbmFseXRpY3MucmVhZG9ubHkiLCJhdWQiOiJodHRwczovL3d3dy5nb29nbGVhcGlzLmNvbS9vYXV0aDIvdjQvdG9rZW4iLCJleHAiOjE1NDg2MzU5MTMsImlhdCI6MTU0ODYzMjUyNH0.JtE0Q8oC6uaOqCvn59z_UKZPq7qHSLAVqJUFRDDP5nD22S_XL27HgA3mIEZJ9wsxrKngreeXfDqWIyVasdYVPdqowkK5ZB2r3BbAyyOrYnRpLEDiNLW82vYYtdzWKNpJFyhUO46CbuFy53Ntgov03jacnar3aMrd49NYNPZqP4Aiajk1Fivv4164CWbkWcZAB-YfEq1iCgbO_ZESkJaHP5ga8emmgErr3EU4DeiucrFgpqffwpxdr906VnImMlgcbhYcPhw8YmmJK1NhKxmChjjRBmodLncW-iy8H8AL2qeC6eX3MQMzOGEkJcd4wh8RDf7sW57_pW-zTF1Y8NvL5A'

Response

{
    "access_token": "ya29.c.ElmfBt9mjZ4nQRqRC92Z4uNMXgL9PugkbtKocv5DR-lAN-SNOOQwsTBkM5AUEP0GJGwUud6cBTk6Vfmmw0L0zmYslrogWVHAQdn1wPTwI4FZc9sL-2d7zD3c5A",
    "expires_in": 3600,
    "token_type": "Bearer"
}

7. Try to get some data from Google Analytics
You can find the ids from Google Analytics dashboard, admin->view settings (far right)->View ID (187243477)

Request

curl --request GET \
  --url 'https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&start-date=30daysAgo&end-date=yesterday&metrics=ga%3Ausers&dimensions=ga%3AuserType' \
  --header 'Authorization: Bearer ya29.c.ElmfBt9mjZ4nQRqRC92Z4uNMXgL9PugkbtKocv5DR-lAN-SNOOQwsTBkM5AUEP0GJGwUud6cBTk6Vfmmw0L0zmYslrogWVHAQdn1wPTwI4FZc9sL-2d7zD3c5A' \
  --header 'cache-control: no-cache'

Response

{
    "kind": "analytics#gaData",
    "id": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&dimensions=ga:userType&metrics=ga:users&start-date=30daysAgo&end-date=yesterday",
    "query": {
        "start-date": "30daysAgo",
        "end-date": "yesterday",
        "ids": "ga:187243477",
        "dimensions": "ga:userType",
        "metrics": [
            "ga:users"
        ],
        "start-index": 1,
        "max-results": 1000
    },
    "itemsPerPage": 1000,
    "totalResults": 2,
    "selfLink": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:187243477&dimensions=ga:userType&metrics=ga:users&start-date=30daysAgo&end-date=yesterday",
    "profileInfo": {
        "profileId": "187243477",
        "accountId": "131677935",
        "webPropertyId": "UA-131677935-1",
        "internalWebPropertyId": "191281733",
        "profileName": "All Web Site Data",
        "tableId": "ga:187243477"
    },
    "containsSampledData": false,
    "columnHeaders": [
        {
            "name": "ga:userType",
            "columnType": "DIMENSION",
            "dataType": "STRING"
        },
        {
            "name": "ga:users",
            "columnType": "METRIC",
            "dataType": "INTEGER"
        }
    ],
    "totalsForAllResults": {
        "ga:users": "55"
    },
    "rows": [
        [
            "New Visitor",
            "32"
        ],
        [
            "Returning Visitor",
            "23"
        ]
    ]
}

8. The access token is good for one hour. After that, we need to start from step #4-6 again

Thursday, January 3, 2019

Oracle Statistics History

Check the current retention value.

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

For alter this retention period to 90 days

execute dbms_stats.alter_stats_history_retention(90);

For check the old record present in database (upto you restore the stats)

select dbms_stats.get_stats_history_availability from dual;

Check out the statistics history

SELECT owner, table_name, stats_update_time
FROM dba_tab_stats_history
WHERE owner='OWNER_APP'
AND table_name='ORDERS'
ORDER BY stats_update_time;

Check out the differences

select * from table(dbms_stats.diff_table_stats_in_history(
ownname => 'OWNER_APP',
tabname => 'ORDERS',
time1 => systimestamp,
time2 => systimestamp-30,
pctthreshold => 0));

Restore the previous statistics for the whole database

execute dbms_stats.restore_database_stats(sysdate-1);

Restore the previous statistics for a schema

execute dbms_stats.restore_schema_stats (ownname=>'OWNER_APP',AS_OF_TIMESTAMP=>sysdate-1);

Restore the previous statistics for a table

execute dbms_stats.restore_table_stats (ownname=>'OWNER_APP', tabname=>'ORDERS', AS_OF_TIMESTAMP=>sysdate-1);

Flush the shared pool to see the new execute plan after restore

alter system flush shared_pool

Saturday, December 22, 2018

UNDO tablespace growing non-stop

Recently I found that the UNDO tablespace keep on growing, even after a database restart, it is still holding up the space. Creating a new UNDOTBS2 tablespace doesn't help either. 120GB "active" undo space never released.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
alter system set undo_tablespace=UNDOTBS2 scope=both;

Check the UNDO tablespace usage

SELECT
  COUNT(segment_name),
  SUM(bytes / 1024 / 1024),
  status,
  tablespace_name
FROM
  dba_undo_extents
GROUP BY
  tablespace_name,
  status;

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME               
------------------- -------------------- --------- ------------------------------
291                              23.8125 UNEXPIRED UNDOTBS1                      
93                               378.375 UNEXPIRED UNDOTBS2                      
545                             750.9375 EXPIRED   UNDOTBS1                      
1349                           10969.625 EXPIRED   UNDOTBS2                      
1                                     64 ACTIVE    UNDOTBS2                      
18130                         121075.375 ACTIVE    UNDOTBS1                      

6 rows selected.

Checking which active transaction is holding up those spaces tells me there is none.

SELECT s.username,s.sid, t.xidusn, t.ubafil,
    t.ubablk, t.used_ublk
    FROM v$session s, v$transaction t
   WHERE s.saddr = t.ses_addr;

USERNAME           SID     XIDUSN     UBAFIL     UBABLK  USED_UBLK
----------- ---------- ---------- ---------- ---------- ----------
                    14        308          0          0          1

Rollback Segment indicated there are problem with some segments

SELECT segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS               TABLESPACE_NAME
------------------------------ -------------------- ------------------
SYSTEM                         ONLINE               SYSTEM
_SYSSMU1_3780397527$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU2_2232571081$           OFFLINE              UNDOTBS1
_SYSSMU3_2097677531$           OFFLINE              UNDOTBS1
_SYSSMU4_1152005954$           OFFLINE              UNDOTBS1
_SYSSMU5_1527469038$           OFFLINE              UNDOTBS1
_SYSSMU6_2443381498$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU7_3286610060$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU8_2012382730$           OFFLINE              UNDOTBS1
_SYSSMU9_1424341975$           PARTLY AVAILABLE     UNDOTBS1
_SYSSMU10_3550978943$          OFFLINE              UNDOTBS1
_SYSSMU11_3715213117$          ONLINE               UNDOTBS2
_SYSSMU12_3563522984$          ONLINE               UNDOTBS2
_SYSSMU13_1284218525$          ONLINE               UNDOTBS2
_SYSSMU14_1624181735$          ONLINE               UNDOTBS2
_SYSSMU15_1102482685$          ONLINE               UNDOTBS2
_SYSSMU16_1909532494$          ONLINE               UNDOTBS2
_SYSSMU17_3453924897$          ONLINE               UNDOTBS2
_SYSSMU18_4116382225$          ONLINE               UNDOTBS2
_SYSSMU19_606215510$           ONLINE               UNDOTBS2
_SYSSMU20_1963701883$          ONLINE               UNDOTBS2

21 rows selected.

SELECT
    LISTAGG(''''||segment_name||'''', ',') WITHIN GROUP(
        ORDER BY
            segment_name
    )
FROM
    dba_rollback_segs
WHERE
    status NOT IN (
        'ONLINE',
        'OFFLINE'
    );

SELECT '"drop rollback segment "'||segment_name||'";'
FROM
    dba_rollback_segs
WHERE
    status NOT IN (
        'ONLINE',
        'OFFLINE'
    );

Let's kill the problem segments

create pfile from spfile

Add this to the end of the pfile
paris._offline_rollback_segments=('_SYSSMU1_3780397527$','_SYSSMU6_2443381498$','_SYSSMU7_3286610060$','_SYSSMU9_1424341975$')

shutdown immediate;
startup pfile='E:\oracle\db_home\product\12.2.0\dbhome_1\database\INITPARIS.ORA'

drop rollback segment "_SYSSMU1_3780397527$";
drop rollback segment "_SYSSMU6_2443381498$";
drop rollback segment "_SYSSMU7_3286610060$";
drop rollback segment "_SYSSMU9_1424341975$";
drop tablespace UNDOTBS1 including contents and datafiles;

shutdown immediate;
startup;

Check the UNDO tablespace usage again

SELECT
  COUNT(segment_name),
  SUM(bytes / 1024 / 1024),
  status,
  tablespace_name
FROM
  dba_undo_extents
GROUP BY
  tablespace_name,
  status;

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS    TABLESPACE_NAME               
------------------- -------------------- --------- ------------------------------
                162                  678 UNEXPIRED UNDOTBS2                      
                319               2840.5 EXPIRED   UNDOTBS2

Tuesday, October 23, 2018

Oracle Database 18c XE and Transparent Data Encryption

TDE Setup

The process is slightly different than 12c.

Create pfile

create pfile from spfile;
shutdown immediate;

Add this to /opt/oracle/product/18c/dbhomeXE/dbs/initXE.ora

*.wallet_root=/opt/encrypted_wallet

Create spfile from pfile with WALLET_ROOT

create spfile from pfile;
startup;

Configure the Software Keystore Type

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;
shutdown immediate;
startup;

Setup auto-login Wallet

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/encrypted_wallet/tde/' IDENTIFIED BY "wve6Wq54IUhg39XY";
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/encrypted_wallet/tde/' IDENTIFIED BY "wve6Wq54IUhg39XY";

Create the master key in all containers

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wve6Wq54IUhg39XY" CONTAINER=ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "wve6Wq54IUhg39XY" WITH BACKUP CONTAINER=ALL;

Checkout Setting

select * FROM V_$ENCRYPTION_WALLET;
select * FROM V_$ENCRYPTION_KEYS;
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name;

Migrate existing tablespaces to encrypted tablespace

ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE users ENCRYPTION OFFLINE ENCRYPT;
ALTER TABLESPACE users ONLINE;

Checkout tablespace encryption setting

select * from V$ENCRYPTED_TABLESPACES;

Setting Future Tablespaces to be Encrypted

ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS;

RMAN Encrypted Backup

RMAN Configuration

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';

RCV file

show all;
set encryption on;
crosscheck backupset;
crosscheck archivelog all;
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
run {  
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
list backupset;
exit

Datapump Encrypted Backup

Add these options to expdp

ENCRYPTION=ALL
ENCRYPTION_MODE=TRANSPARENT 
ENCRYPTION_ALGORITHM=AES256