POUWIEL|COM

HOST echo %NLS_LANG% > Y:\temp\NLS_CHAR_SESSION.lst

DECLARE
  dbs_nls  varchar2(4000);
  ses_nls  varchar2(4000);
--
  file_contents VARCHAR2(32767) := '
@@Y:\temp\NLS_CHAR_SESSION.lst
';
--
BEGIN
--
  file_contents := TRIM(replace(replace(file_contents,CHR(13),''),CHR(10),' '));
  ses_nls := file_contents;
  SELECT a.value||'_'||b.value||'.'||c.value INTO dbs_nls
    FROM (select value from nls_database_parameters where parameter = 'NLS_LANGUAGE') a,
         (select value from nls_database_parameters where parameter = 'NLS_TERRITORY') b,
         (select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET') c;
--
  IF(NVL(dbs_nls,'X') != NVL(ses_nls,'Y')) THEN
    IF (UPPER(dbs_nls) = 'AMERICAN_AMERICA.WE8ISO8859P1' AND UPPER(ses_nls) = 'AMERICAN_AMERICA.WE8MSWIN1252') OR (UPPER(ses_nls) = 'AMERICAN_AMERICA.WE8ISO8859P1' AND UPPER(dbs_nls) = 'AMERICAN_AMERICA.WE8MSWIN1252')
      dbms_output.put_line('INFO: ');
      dbms_output.put_line('  CHARACTERSET WE8MSWIN1252 is a binary superset of WE8ISO8859P1')
      dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
    ELSE
      dbms_output.put_line('##### WARNING!!! ##############################################');
      dbms_output.put_line('  The two CHARACTERSET settings are not equal !!!');
      dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
    END IF;
  ELSE
    dbms_output.put_line('INFO: ');
    dbms_output.put_line('  The two CHARACTERSET settings are equal');
    dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
  END IF;
--
END;
/

[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

ascii dumps and converts to multilangual hexes.
US7ASCII is outdated, it slowely drives me insane.
Those are a few of my least favorite things…

Here we are, struggling again. *sigh…

The skinny:
Read through Oracle’s NLS_LANG faq. Again. Don’t argue, just do it. Yes, Again!
Check your codepage, I’ll assume your on Windows otherwise you wouldn’t be in this mess.
For arguments sake, let’s assume it’s 850.

Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation.  All rights reserved.

c:\Windows>chcp
Active code page: 850

Set your NLS_LANG to: american_america.we8pc850

Start SQLPlus.

Note to the weary:
When working remotely via Citrix, it’s at all good possible you’ll check your codepage, determine it’s that 850, set your NLS_LANG to american_america.we8pc850, run your script that’s located on a mounted share and still have garbage.
That.. that I can’t help you with.. Still need to figure that one out. I suppose it’s due to the fact that the scripts are compiled on another system, placed on the share and both environments may have different codepages than 850. Maybe another time, eh!?

Update ::
According to RedGrittyBrick on superuser.com, it seems that Notepad uses ANSI as another word for WE8MSWIN1252. Set your NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and be golden again…

[15:28:03_DBA_ORCL@TSERVER] TST 
SQL> create user sw_owner identified by sw_owner;
SQL>   grant create session, create table to sw_owner;
SQL>   grant create procedure to sw_owner;
SQL>   alter user sw_owner quota 5m on  users;
SQL>   grant grant any role to sw_owner;

SQL> create user sw_admin identified by sw_admin;
SQL>   grant create session to sw_admin;

SQL> create user sw_user1 identified by sw_user1;
SQL>   grant create session to sw_user1;

SQL> create table sw_owner.sw_table1 (a number);
SQL>   insert into sw_owner.sw_table1 (a) values (1);
SQL>   insert into sw_owner.sw_table1 (a) values (2);
SQL>   insert into sw_owner.sw_table1 (a) values (3);
SQL>   commit;

SQL> create role sw_role1;
SQL> grant select on sw_owner.sw_table1 to sw_role1;


SQL> CREATE OR REPLACE PACKAGE sw_owner.sw_package1
  2  IS
  3     PROCEDURE sw_procedure1 (p_grantee varchar2);
  4  END;
  5  /

SQL> CREATE OR REPLACE PACKAGE BODY sw_owner.sw_package1
  2  IS
  3  -- ---------------------------------------------------
  4     PROCEDURE sw_procedure1 (p_grantee  VARCHAR2)
  5     IS
  6     v_query       varchar2(200);
  7     BEGIN
  8       v_query := 'GRANT sw_role1 to '|| p_grantee;
  9       EXECUTE IMMEDIATE v_query;
 10     END;
 11  END;
 12  /
SQL> create public synonym sw_package1 for sw_owner.sw_package1;

SQL> grant execute on sw_owner.sw_package1 to sw_admin;

SQL> conn sw_admin/sw_admin@orcl
SQL> execute sw_package1.sw_procedure1('sw_user1');
SQL> conn sw_user1/sw_user1@orcl
SQL> select * from sw_owner.sw_table1;

         A
----------
         1
         2
         3

With a generous wink to my esteemed colleague Remi Visser, for your (but mostly mine) Ctrl-C / Ctrl-V benefit:

SQL> SELECT SUBSTR ( file_name, INSTR( file_name, '/', -1)) file_name, COUNT(*)
  2  FROM dba_data_files
  3  GROUP BY SUBSTR ( file_name,INSTR( file_name, '/', -1)) 
  4  HAVING COUNT(*) > 1
  5  /

FILE_NAME                               COUNT(*)
------------------------------------- ----------
/****p1i005ds10.dbf                            2
/****p1i005ds11.dbf                            2

SQL>

To retrieve the complete filename, as mentioned in dba_data_files/dba_temp_files:

SQL> SELECT *
  2  FROM dba_data_files
  3  WHERE SUBSTR ( file_name, INSTR( file_name, '/', -1)) IN (
  4     SELECT file_name FROM (
  5        SELECT SUBSTR ( file_name, INSTR( file_name, '/', -1)) file_name, 
  6        COUNT(*)
  7        FROM dba_data_files
  8        GROUP BY SUBSTR ( file_name,INSTR( file_name, '/', -1))
  9        HAVING COUNT(*) > 1 )
 10     )
 11  ORDER BY 3, 1;

FILE_NAME                                                               FILE_ID
---------------------------------------------------------------------- --------
/m021/oradata/********/********p1boff00106.dbf                              153
/m001/oradata/********/********p1boff00106.dbf                              154
/m005/oradata/********/********p1boff00209.dbf                               40
/m002/oradata/********/********p1boff00209.dbf                               39
/m006/oradata/********/********p1boffis08.dbf                                52
/m004/oradata/********/********p1boffis08.dbf                               163

6 rows selected.

Plain and simple, sweet as Π

exp \'/ as sysdba\'

Fill/alter in the required parameters and Ctrl-C/Ctrl-V:

DECLARE
  h1   number;
BEGIN
  h1 := dbms_datapump.open(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => '<JOB_NAME__MAKE_THIS_UPPERCASE!!!>', version => 'COMPATIBLE', remote_link=>'<DB_LINK_NAME>');
  dbms_datapump.set_parallel(handle => h1, degree => 1);
  dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
  dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''<SCHEMA_NAME>'')');
  dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'NOT IN(''<EXCLUDED_TABLE01>'',''<EXCLUDED_TABLE02>'',''<EXCLUDED_TABLE03>'')', object_type => 'TABLE');
  dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_TIME', value => 'TO_TIMESTAMP (to_char(sysdate,''dd.mm.yyyy HH24:MI:SS''),''dd.mm.yyyy HH24:MI:SS'')');
  dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
  dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
  dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
END;
/

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.

server01(database01):/home/oracle>expdp userid=dpuser ATTACH='\"dpjob1414698371\"'

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 06 February, 2013 14:25:31

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: dpjob1414698371
  Owner: DPUSER
  Operation: EXPORT
  Creator Privs: FALSE
  GUID: D421B4DB34AE6236E04400144F2ABBF8
  Start Time: Wednesday, 06 February, 2013 14:25:38
  Mode: SCHEMA
  Instance: database01
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     DATA_ACCESS_METHOD    AUTOMATIC
     ESTIMATE              BLOCKS
     INCLUDE_METADATA      1
     KEEP_MASTER           0
  State: IDLING
  Bytes Processed: 9,289,692,760
  Percent Done: 60
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /oradump/20130125_1414698371.dmp01
    bytes written: 9,977,094,144
  Dump File: /oradump/20130125_1414698371.dmp%u

Worker 1 Status:
  State: UNDEFINED
  Object Schema: SCHEMASOURCE
  Object Name: WORKFLOW_PROCESS
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 15
  Total Objects: 583
  Worker Parallelism: 1

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

Error when installing CPU, could Oracle be more cryptic ?!? 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 »

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 »

Quick reminder:

$> svrmgrl 
sql> connect internal

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 »

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.

In order to log on to DB console running on a Windows platform, the user with which you (want to) log onto the host must be granted the privilege to log on as a batch job.

* Go to control panel/administrative tools.
* click on “local security policy”.
* click on “local policies”.
* click on “user rights assignments”.
* double click on “log on as a batch job”.
* click on “add” and add the user that was entered in the “normal username” or “privileged username” section of the EM Console.

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

Whadda ya’ know, Solaris doesn’t come shipped with MD5 but (!) using digest, you can come a long way:
Read the rest of this entry »

To perform a silent (oracle-) install: Read the rest of this entry »

Here’s how:

sql> shutdown immediate
sql> startup mount
sql> exit

Read the rest of this entry »

– Shutdown immediate / exit
– adjust the oratab to suit the new $ORACLE_HOME
– adjust listener and reload
– startup upgrade
– spool /home/oracle/upgrade.log
– @?/rdbms/admin/catupgr.sql

Should run smoothly, if not: You’re on your own (i would be)