POUWIEL|COM

[14:32:28_JEROEN_db_name@SERVER1] TST
SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                    Status                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- -------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE                 /ora/admin/db_name/wallet        OPEN_NO_MASTER_KEY             UNKNOWN              SINGLE    UNDEFINED          0
 
server1.pouwiel.com(db_name):/home/oracle>mkstore -wrl "/ora/admin/db_name/wallet/" -create --passwd:*****
server1.pouwiel.com(db_name):/home/oracle>orapki wallet add -wallet /ora/admin/db_name/wallet -cert /tmp/server2_trusted_cert.crt -trusted_cert -pwd "*****"
server1.pouwiel.com(db_name):/home/oracle>orapki wallet add -wallet /ora/admin/db_name/wallet -cert /tmp/server3_trusted_cert.crt -trusted_cert -pwd "*****"
server1.pouwiel.com(db_name):/home/oracle>orapki wallet display -wallet /ora/admin/db_name/wallet -pwd "*****"
server1.pouwiel.com(db_name):/home/oracle>orapki wallet remove -wallet "/ora/admin/db_name/wallet/" -dn 'CN=db_name,C=NL' -user_cert -pwd "*****"
server1.pouwiel.com(db_name):/home/oracle>orapki wallet remove -wallet "/ora/admin/db_name/wallet/" -dn 'CN=db_name,C=NL' -trusted_cert -pwd "*****"
server1.pouwiel.com(db_name):/home/oracle>orapki wallet remove -wallet /ora/admin/db_name/wallet -dn 'CN=db_name,C=NL' -cert_req -pwd "*****"


server1.pouwiel.com(db_name):/home/oracle>orapki wallet add -wallet /ora/admin/d/wallet -dn 'CN=db_name,C=NL' -keysize 2048 -self_signed -validity 3650 -pwd "*****"                                                                <
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

server1.pouwiel.com(db_name):/home/oracle>orapki wallet display -wallet /ora/admin/db_name/wallet -pwd "*****"
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=db_name,C=NL
Trusted Certificates:
Subject:        CN=server3.pouwiel.com,OU=Apx,O=AH,L=Amsterdam,ST=Netherlands,C=NL
Subject:        CN=db_name,C=NL
Subject:        CN=server2.pouwiel.com,OU=Apx,O=AH,L=Amsterdam,ST=Netherlands,C=NL
server1.pouwiel.com(db_name):/home/oracle>orapki wallet export -wallet /ora/admin/db_name/wallet -dn 'CN=db_name,C=NL' -cert /tmp/db_name.txt -pwd "*****"
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

server1.pouwiel.com(db_name):/home/oracle>lr /tmp/db_name.txt
-rw-------. 1 oinstall 990 Apr  6 14:28 /tmp/db_name.txt
server1.pouwiel.com(db_name):/home/oracle>cat /tmp/db_name.txt
-----BEGIN CERTIFICATE-----
MIICsDCCAZgCAQAwDQYJKoZIhvcNAQEEBQAwHjELMAkGA1UEBhMCTkwxDzANBgNVBAMTBmFwYWNo
ZTAeFw0xNzA0MDYxMjI3MzBaFw0yNzA0MDQxMjI3MzBaMB4xCzAJBgNVBAYTAk5MMQ8wDQYDVQQD
EwZhcGFjaG%wggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCZVNvKVj5pNfZQ9GAOILdg
dVTEgQf74+vyCKA7bq8uE3ooKFWIF+0z67zYScvgl0U6B4q02&h7Zj3I1/Z4E750Ss7H1i5Y9LgY
6kMF9+lhlctWmkiWTcco3yd47Xl+exmKVb1CLEG1234567890ZVoGG8Xvc6pSdm7/jInBnshUdTb
KJLDGcv4LAaS3s4mStr4at_aBcRK+s7sbcqBXf7vXLb9GBEeip8UZK5DgqrO++i7wzbT5AW4yOXP
CfeYwHbgdsyMO2eRrRvAcEULN4YTa6yOxz1FkxjrCI9UEFnuUM5/0NFmLu+Zff0iOCinDpjf6lCE
5Q1xt6P1cJHIfzr/AgMBAAEwDQYJKoZIhvcNAQEEBQADggEBAJczBLirU2m7tjAIkUgg8gDAKlgn
uabXCRjmaCBg18QOHWEF7W:EFINsejCq74NJmj45jMayrdsy+Tpa71+p80U6ojFPuO7EjqPWTFab
aCWOWNKAkE0h2402Bq8ysw4DE/GDdHbjGAxu2m3WvFPoRo3yd47XlNLuBAxu2m3WvFPoRaLXZ9Al
72pJXwrq7e4?K/zpDAQ976JT+7x6xsM9GODtNYrrJwHzH0ocSuFWNs5SvHdVBe#Y6fJPMyEH7qQR
D6Oh634ZBHtybrSeU6BAYq/U9kevrr5CP7+TkZA4EAhYu@Ifvh6b/yN2do0WwEjTWrGyERU6EVSD
tU0UZBbewCI=
-----END CERTIFICATE-----

bb_name

DECLARE
  var_mod number;
  CURSOR c1 IS
    SELECT tablespace_name, ROUND(SUM(bytes)/(1024*1024),0) AS mb
      FROM dba_data_files
     WHERE tablespace_name IN (SELECT DISTINCT tablespace_name
                                 FROM dba_segments
                                WHERE owner IN ('<schema_name>','<schema_name>','<schema_name>','<schema_name>'))
    GROUP BY tablespace_name
    ORDER BY 1;
BEGIN
   FOR tblspc IN c1
   LOOP
     var_mod := FLOOR(tblspc.mb/9216);
     IF tblspc.mb <= 9216 THEN
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE '||tblspc.mb||'M AUTOEXTEND ON MAXSIZE 9G;');
     ELSIF REMAINDER(tblspc.mb, 9216) = 0 THEN
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       FOR i IN 1..var_mod LOOP
         var_mod := var_mod - 1;
         DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE '||tblspc.tablespace_name||' ADD DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       END LOOP;
     ELSE
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       FOR i IN 1..var_mod LOOP
         var_mod := var_mod - 1;
         DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE '||tblspc.tablespace_name||' ADD DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       END LOOP;
     END IF;
     var_mod :=0;
   END LOOP;
END;
/

This piece of code will gather all used tablespaces by given schema's and output create statements for those tablespaces broken down into separate files of max. 9G.
Use at your own leisure and discretion... As always: No support and no responsibility

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘<user>’, ‘<table_name>’, estimate_percent => NULL, cascade => TRUE);

Instead of estimating the statistics using a given percentage, this will hit all rows.

One of my databases started to spew tracefiles as if it’s life depended on it:

Unix process pid: 22450, image: oracle@server1 (MMON)


*** 2013-02-15 16:40:05.655
*** SESSION ID:(1873.1) 2013-02-15 16:40:05.655
*** CLIENT ID:() 2013-02-15 16:40:05.655
*** SERVICE NAME:() 2013-02-15 16:40:05.655
*** MODULE NAME:() 2013-02-15 16:40:05.655
*** ACTION NAME:() 2013-02-15 16:40:05.655

minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

*** 2013-02-15 16:45:08.169
minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

*** 2013-02-15 16:50:09.519
minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000
minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

*** 2013-02-15 17:00:12.288
minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

*** 2013-02-15 17:05:13.881
minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000
minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

*** 2013-02-15 17:15:16.582
minact-scn master-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

One of my astute colleagues pointed my attention to the following MOS document:
Minact-Scn Master-Status: Grec-Scn Messages In Trace File [ID 1361567.1] which refers to
MINACT-SCN Errors in Alert and Trace Files Generated by MMON [ID 1297450.1] which refers to
How to Purge a Distributed Transaction from a Database [ID 159377.1]

It went a little like this:

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID
----------------------
GLOBAL_TRAN_ID
--------------------------------------------------------------------------------
TO_CHAR(FAIL_TIME,'D STATE MIX
-------------------- ---------------- ---
8.19.321913
1463898948.0000013CDE8005110000000179F68840A089FFFEE644B640AED6B02438B2F39D9665F
AFB
15-feb-2013 16:37:41 prepared no


SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID IN_ I
---------------------- --- -
DATABASE
--------------------------------------------------------------------------------
8.19.321913 in N
jdbc_11


SQL> rollback force '8.19.321913';

Rollback complete.

SQL> execute dbms_transaction.purge_lost_db_entry('8.19.321913');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;

no rows selected

Be aware that the support document 159377.1 does not speak of the rolback force. If you don’t execute that particular command it will not work.

On any schema, you’d need an any-privilege…
Which so happens to be: ANALYZE ANY DICTIONARY and ANALYZE ANY

So, give these to user you want to call the dbms_stats package and all will be fine…

In this case, I created this tiny script: Read the rest of this entry »

Error when installing CPU, could Oracle be more cryptic ?!? Read the rest of this entry »

Use FGA to simulate a parallel export in Oracle8: Read the rest of this entry »

For this setup, I create a folder “./out” and a folder “./log” on the OS and the becessary pipes.
In the source database, I create as many users I need to, each of these user has at least the privilege to export the entire database.
If you add (and I did), FGA policies on tables of a specific user, you can simulate a parallel export in database version that do not support this natively. Read the rest of this entry »

Again, a query for retrieving the number of open cursors per SID for the copy/paste-minded dba’s out there: Read the rest of this entry »

aka Oracle 10g agent

Today we received a sev2 ticket regarding an agent that was down. Don’t know why, seeing as the daylight savings time issue occurred on the 29th of March. Below the transcript of starting the agent: Read the rest of this entry »

After reading this article in the Oracle Magazine, i was eager to try the following part myself:

To confirm encryption, you can insert a record with a value “David” for FIRST_NAME and search the datafile you created for the enc128_ts tablespace for that value: Read the rest of this entry »

You add a datafile, you backup your controlfile. It’s that simple…. Read the rest of this entry »

I’ve been busy modifying my script which display all sessions in the database. I wanted to have the names of the background processes, not difficult i know. The trick is to get them in every (version of) database displayed correctly. Eventually i had it working: Read the rest of this entry »

We regularly get the request for a refresh of an OTA database.
Usually, the end-users would like for their settings to survive the refresh.

We use a couple of scripts to save these setting (eg. passwords and such), however, the database links didn’t survive last time.
Probably because the passwords aren’t visible anymore in 10g.
Update!: Not true, just had a peek in SYS.LINK$ ver 10.1.05 and column AUTHPWD still had (unencrypted!) values.
Update2!: Somewhat true, just had a peek in SYS.LINK$ ver 10.2.0.1 & 10.2.0.2 and column PASSWORDX has encrypted values.

It is possible to retrieve the passwords using dbms_metadata.get_ddl.
More after the jump:
Read the rest of this entry »

I found out this afternoon that a transaction waiting to receive all required latches is subordinate to a ddl statement. Regard the following:

[ZZXJFF@OCP04:W2ZZXJFF02]
SQL> select * from blaat;

A          B
- ----------
b          1
b          2

Read the rest of this entry »

One of our databases went over the fritz yesterday. We regurarly receive a request from the functional administrators to kill a session (batch-user) in the database. Often the session doesn’t end quick enough to the admin’s likings.
Read the rest of this entry »

Got the request to reorder a table’s column order. It was/is a simple enough table: 4 columns where the last two needed to be the first two.
Look after the jump to see how it went down:

Updated! Read the rest of this entry »

Nasty buggers,

Just came across a mview that didn’t gave results when it should’ve.
Most likely, it was in the wrong refresh_group, below the query to check in which refresh_group the mview is: Read the rest of this entry »

To retrieve or correct (mostly user-) data using Logminer:
Read the rest of this entry »

To alter the maxextents of a LOB, try the next statement:
ALTER TABLE . MOVE LOB () STORE AS
(STORAGE (MAXEXTENTS UNLIMITED));
Read the rest of this entry »

source:

USE_SHARED_SOCKET
You can set parameter USE_SHARED_SOCKET to true to enable use of shared sockets. If this parameter is set to true, the network listener passes the socket descriptor for client connections to the database thread. As a result, the client does not need to establish a new connection to the database thread and database connection time improves. Also, all database connections share the port number used by the network listener, which can be useful if you are setting up third-party proxy servers.
This parameter only works in dedicated server mode in a TCP/IP environment.
If this parameter is set, you cannot use the 9.0 listener to spawn Oracle7 release 7.x databases.
To spawn a dedicated server for an Oracle database not associated with the same Oracle home as the listener and have shared socket enabled, you must also set parameter USE_SHARED_SOCKET for both Oracle homes.

USE_SHARED_SOCKET could be a performance bottleneck if you have more connections to the database. The load on using the same socket on Windows has been very weak with respect to performance.
So the answer would depend on how many simultaneous connections will hit the listener at a point of time.
I don’t have any benchmark values for this ( there was never a need for this ).
But we decided against the use of this with just 50 connections.

Below the options for the oracle agent: Read the rest of this entry »

When db = down, but is still shutting down Read the rest of this entry »

To determine when the statistics have gathered for a certain table:
Note: to speed things up, you’d might consider: Read the rest of this entry »

Here’s how:

sql> shutdown immediate
sql> startup mount
sql> exit

Read the rest of this entry »

To alter the size of the next extent:

Alter table table_name storage (next 5M);

To force the agent to gather and upload information concerning a specific database, use the following command:
Read the rest of this entry »

Below a conventional way of naming triggers:

trigger_name_|B|S|I
|A|R|U
|D

B before
A after
S statement
R row
I insert
U update
D delete

Here’s how:
Doc ID: Note:175258.1
Oracle Metalink

Instead of deleting the alert log, just copy “nothing” into it:

&>  > alert***.log