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;
/

CREATE TABLE ballage2 ( X NUMBER(10), Y VARCHAR2(24 CHAR), Z DATE )
/

DECLARE
  lStart            number := 1 ;
  CODE_ARRAY        dbms_sql.number_table ;
  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;
  COMPLETION_ARRAY  dbms_sql.date_table ;
BEGIN

    CODE_ARRAY(1) := 101 ;
    DESCRIPTION_ARRAY(1) := 'WRITING' ;
    COMPLETION_ARRAY(1) := TO_DATE('2017/02/27 14:00:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(2) := 102 ;
    DESCRIPTION_ARRAY(2) := 'PROCESSING' ;
    COMPLETION_ARRAY(2) := TO_DATE('2017/03/14 15:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(3) := 103 ;
    DESCRIPTION_ARRAY(3) := 'SEEDING' ;
    COMPLETION_ARRAY(3) := TO_DATE('2017/05/08 11:28:01', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(4) := 104 ;
    DESCRIPTION_ARRAY(4) := 'TRANSFORMING' ;
    COMPLETION_ARRAY(4) := TO_DATE('2018/07/07 17:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(5) := 105 ;
    DESCRIPTION_ARRAY(5) := 'BAWLING' ;
    COMPLETION_ARRAY(5) := '' ;
    LOOP
    BEGIN
        FORALL i IN lStart..CODE_ARRAY.COUNT
                INSERT INTO ballage2 VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;
        EXIT ;
    EXCEPTION WHEN OTHERS THEN
        lStart := lStart + sql%ROWCOUNT + 1 ;
    END ;
  END LOOP ;
END ;
/

CREATE OR REPLACE PACKAGE PNL_BALLAGE2 IS 
  type cg$row_type is record
    ( x                      ballage2.x%type
    , y                      ballage2.y%type
    , z                      ballage2.z%type
    ) ;
--------------------------------------------------------------------------------
  procedure ins( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure upd( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure del( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure lck( p_ballage2_old_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  function slct( p_x	    	in ballage2.x%type )
  return cg$row_type ;
--
  cg$tmp_rec cg$row_type ;
--
  type t_ballage2_tabtype is table of cg$row_type index by binary_integer ;
end PNL_BALLAGE2 ;
/

CREATE OR REPLACE PACKAGE BODY PNL_BALLAGE2 IS
  PROCEDURE ins( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    INSERT INTO ballage2
      ( x
      , y
      , z
      )
      VALUES
      (       p_ballage2_rec.x
      , NVL ( p_ballage2_rec.y , 'Initiële actie' )
      ,       TO_DATE(p_ballage2_rec.z, 'YYYY/MM/DD HH24:MI:SS') )
      ;
  --
  END ins ;
  --------------------------------------------------------------------------------
  PROCEDURE upd( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    UPDATE ballage2
    SET    y						= p_ballage2_rec.y
    ,      z            = p_ballage2_rec.z
    WHERE  x						= p_ballage2_rec.x
    ;
  --
  END upd ;
  --------------------------------------------------------------------------------
  PROCEDURE del( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    DELETE FROM ballage2
    WHERE  x						= p_ballage2_rec.x
    AND    y						= p_ballage2_rec.y
    AND    z						= p_ballage2_rec.z
    ;
  --
  END del ;
  --------------------------------------------------------------------------------
  PROCEDURE lck( p_ballage2_old_rec in cg$row_type )
  IS
  BEGIN
    SELECT x
    ,      y
    ,      z
    INTO   cg$tmp_rec.x
    ,      cg$tmp_rec.y
    ,      cg$tmp_rec.z
    FROM   ballage2
    WHERE  x						= p_ballage2_old_rec.x
    AND    y						= p_ballage2_old_rec.y
    AND    z						= p_ballage2_old_rec.z
    FOR UPDATE
    ;
  --
  END lck ;
  --------------------------------------------------------------------------------
  FUNCTION slct(p_x in ballage2.x%type)
  RETURN cg$row_type
  IS
    r_ballage2_rec cg$row_type DEFAULT NULL;
  BEGIN
    SELECT x
    ,      y
    ,      z
    INTO   r_ballage2_rec.x
    ,      r_ballage2_rec.y
    ,      r_ballage2_rec.z
    FROM   ballage2
    WHERE  x            = p_x
    ;
  --
    RETURN r_ballage2_rec ;
  --
  END slct;
END PNL_BALLAGE2;
/

DECLARE 
  my_data ballage2%rowtype;
BEGIN 
  my_data.x := 106; 
  my_data.y := 'JACKING';
  my_data.z := TO_DATE('2017/06/19 15:00:01', 'YYYY/MM/DD HH24:MI:SS');

  pnl_ballage2.ins( my_data ); 
END; 
/

DECLARE
  CURSOR my_data_cur
    IS
    SELECT x, y, z
      FROM ballage2
     WHERE x=102;

  my_data my_data_cur%ROWTYPE;
BEGIN
  OPEN my_data_cur;
  FETCH my_data_cur INTO my_data;
  my_data.y := 'altered via upd';
  CLOSE my_data_cur;
  commit;
  pnl_ballage2.upd( my_data );
END;
/

DECLARE
  my_data ballage2%ROWTYPE;
BEGIN
  SELECT *
   INTO my_data
   FROM ballage2
  WHERE x = 102;

  my_data.y := 'altered again via upd';

  pnl_ballage2.upd( my_data );
END;
/


DECLARE
  my_data ballage2%ROWTYPE;
BEGIN
  SELECT *
   INTO my_data
   FROM ballage2
  WHERE x = 103;

  pnl_ballage2.del( my_data );
END;
/

DECLARE
my_data ballage2%ROWTYPE;
p1      varchar2(16);
BEGIN
SELECT *
    INTO my_data
    FROM ballage2
   WHERE x = 102;

pnl_ballage2.lck( my_data );
my_data.y := 'altered using lck';
dbms_lock.sleep(20);
pnl_ballage2.upd( my_data );
commit;
END;
/


DECLARE
  v_ret ballage2%ROWTYPE;
BEGIN
  v_ret:=pnl_ballage2.slct(104);
  DBMS_OUTPUT.PUT_LINE(v_ret.x||', '||v_ret.y||', '||v_ret.z);
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

Just like this, sure it can be extended with some checks and other nifty things..:

DECLARE
  counter     number  := 1 ;
  -- declare a varchar variable to contain the
  -- TO_CHAR string, as DATE type is actually 
  -- a number
  v_date2char varchar2(50) ;
  --
  CURSOR c_data_ballage
    IS
    SELECT code
    ,      description
    ,      TO_CHAR(completion, 'YYYY/MM/DD HH24:MI:SS') AS completion
      FROM ballage ;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000) ;
	
  DBMS_OUTPUT.PUT_LINE('DECLARE') ;
  DBMS_OUTPUT.PUT_LINE('  lStart            number := 1 ;') ;
  DBMS_OUTPUT.PUT_LINE('  CODE_ARRAY        dbms_sql.number_table ;') ;
  DBMS_OUTPUT.PUT_LINE('  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;') ;
  DBMS_OUTPUT.PUT_LINE('  COMPLETION_ARRAY  dbms_sql.date_table ;') ;
  DBMS_OUTPUT.PUT_LINE('BEGIN') ;
  DBMS_OUTPUT.PUT_LINE(' ') ;
	
  FOR data_record IN c_data_ballage LOOP
    -- As moment of writing Oracle database considers NULL and empty string ('')
    -- as equal, as show in sample below:
    -- SQL> SELECT NVL(NULL,'This returns NULL') A,NVL('','This will too') B FROM DUAL;  
    -- A                 B  
    -- ----------------- -------------
    -- This returns NULL This will too
		
    IF data_record.completion IS NOT NULL THEN
      v_date2char := 'TO_DATE('''||data_record.completion||''', '''||'YYYY/MM/DD HH24:MI:SS'||''')' ;  
    ELSE
      v_date2char := ''''||data_record.completion||'''' ;
    END IF ;
		
    DBMS_OUTPUT.PUT_LINE('    CODE_ARRAY('||counter||') := '  || data_record.code ||' ;') ;
    DBMS_OUTPUT.PUT_LINE('    DESCRIPTION_ARRAY('||counter||') := '''|| data_record.description ||''' ;') ;
    DBMS_OUTPUT.PUT_LINE('    COMPLETION_ARRAY('||counter||') := '|| v_date2char ||' ;') ;
    counter     := counter + 1 ;
    v_date2char := NULL ;
  END LOOP ;
	
  DBMS_OUTPUT.PUT_LINE('    LOOP') ;
  DBMS_OUTPUT.PUT_LINE('    BEGIN') ;
  DBMS_OUTPUT.PUT_LINE('    	FORALL i IN lStart..CODE_ARRAY.COUNT') ;
  DBMS_OUTPUT.PUT_LINE('    		INSERT INTO ballage VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;') ;
  DBMS_OUTPUT.PUT_LINE('    	EXIT ;') ;
  DBMS_OUTPUT.PUT_LINE('    EXCEPTION WHEN OTHERS THEN') ;
  DBMS_OUTPUT.PUT_LINE('    	lStart := lStart + sql%ROWCOUNT + 1 ;') ;
  DBMS_OUTPUT.PUT_LINE('    END ;') ;
  DBMS_OUTPUT.PUT_LINE('  END LOOP ;') ;
  DBMS_OUTPUT.PUT_LINE('END ;') ;
  DBMS_OUTPUT.PUT_LINE('/') ;
END ;
/

This will generate the following output (for my “ballage” table that is):

DECLARE
  lStart            number := 1 ;
  CODE_ARRAY        dbms_sql.number_table ;
  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;
  COMPLETION_ARRAY  dbms_sql.date_table ;
BEGIN

    CODE_ARRAY(1) := 101 ;
    DESCRIPTION_ARRAY(1) := 'WRITING' ;
    COMPLETION_ARRAY(1) := TO_DATE('2017/02/27 14:00:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(2) := 102 ;
    DESCRIPTION_ARRAY(2) := 'PROCESSING' ;
    COMPLETION_ARRAY(2) := TO_DATE('2017/03/14 15:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(3) := 103 ;
    DESCRIPTION_ARRAY(3) := 'SEEDING' ;
    COMPLETION_ARRAY(3) := TO_DATE('2017/05/08 11:28:01', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(4) := 104 ;
    DESCRIPTION_ARRAY(4) := 'TRANSFORMING' ;
    COMPLETION_ARRAY(4) := TO_DATE('2018/07/07 17:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(5) := 105 ;
    DESCRIPTION_ARRAY(5) := 'BAWLING' ;
    COMPLETION_ARRAY(5) := '' ;
    LOOP
    BEGIN
        FORALL i IN lStart..CODE_ARRAY.COUNT
                INSERT INTO ballage2 VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;
        EXIT ;
    EXCEPTION WHEN OTHERS THEN
        lStart := lStart + sql%ROWCOUNT + 1 ;
    END ;
  END LOOP ;
END ;
/

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…

Somehow, from somewhere, my virtual console stopped connecting. The java console showed the following messages:
Missing Application-Name manifest attribute for: https://***.***.***.***:443/software/avctKVM.jar
jan 18, 2017 3:41:48 PM java.util.prefs.WindowsPreferences
WARNING: Could not open/create prefs root node Software\JavaSoft\Prefs at root 0x80000002. Windows RegCreateKeyEx(…) returned error code 5.
01/18/2017 03:41:49:004: Starting client.
====setPowerMenuStatus: (##2)
01/18/2017 03:41:51:018: Connection failed.

Pretty cryptic, huh!? And good luck trying to find a solution through google…
Of course, mine was the case: “..but it worked last week!”
Than I remembered, that I had installed HitmanPro.Alert. Could it be… No! surely not.

Well, of course it was and don’t call me Shirley!

Using the advanced interface, under “Risk reduction”, click the icon that looks like fusion between the CMD and network share icon. This is the “Network lockdown – command-and-control” option. When this is active, connection to/via the virtual console is prohibited.

Running Windows Server 2012 R2 HYPER-V Core but what to do regarding backups..!?

Easy.

Install the Windows Server Backup feature. As I am using HYPER-V Core, I can install the feature using server manager on my laptop.
Than, create a shared folder on my HYPER-V server with:

net share BCK=C:\BCK /GRANT:Everyone,FULL

And finally, backup the virtual machine using wbadmin.exe, like so:

WBADMIN START BACKUP -backupTarget:\\<servername>\BCK -hyperv:<vm_name>

Because I’ve got Swiss cheese for memory, I created a little script to do this for me:

@ECHO OFF

:HOUSEKEEPING
ECHO.

:CHECKFORSWITCHES
IF -%1-==-- (
    ECHO.:: ##########!! WARNING !!##############################################################
    ECHO.:: ## No parameters were passed. Please look through the Usage section
    ECHO.:: ##  & GOTO DISPINFO
) ELSE IF '%1'=='/H' (
    GOTO DISPINFO
) ELSE IF '%1'=='/h' (
    GOTO DISPINFO
) ELSE IF '%1'=='/?' (
    GOTO DISPINFO
) ELSE IF EXIST "C:\VMS\%1" (
    GOTO MAIN
) ELSE (
    ECHO.:: ##########!! WARNING !!##############################################################
    ECHO.:: ## Provided parameter was not recognized/valid. Please look through the Usage section
    ECHO.:: ##  & GOTO DISPINFO    
)

:DISPINFO
ECHO.:: ##########~~ INFORMATION ~~##########################################################
ECHO.:: ## BACKUP
ECHO.::
ECHO.:: Description:
ECHO.::     Backs up a single Virtual Machine to the local shared folder BCK @ \\<servername>\BCK
ECHO.::     The command used is: "WBADMIN START BACKUP -backupTarget:\\<servername>\BCK -hyperv:{vm_name}"
ECHO.::
ECHO.:: Usage:
ECHO.::     c:\Users\Administrator\Documents\backup.bat {vm_name}
ECHO.::
ECHO.:: Parameter List:
ECHO.::     /?             Displays this help message.
ECHO.::     vm_name        When given a vm_name, it will be passed as argument to the wbadmin program
ECHO.::
ECHO.:: Resources:
ECHO.::     WBADMIN        @ https://blogs.msdn.microsoft.com/virtual_pc_guy/2013/02/25/backing-up-hyper-v-virtual-machines-from-the-command-line/
ECHO.::                    @ https://virtualizationreview.com/blogs/virtual-insider/2013/02/back-up-hyper-v-vms.aspx
ECHO.::
ECHO.::     BATCH script   @ http://stackoverflow.com/questions/26551/how-to-pass-command-line-parameters-to-a-batch-file
ECHO.::                    @ http://stackoverflow.com/questions/13684259/show-help-message-if-any-command-line-argument-equals
ECHO.
ECHO.
GOTO :END

:MAIN
set arg1=%1
echo starting "WBADMIN START BACKUP -backupTarget:\\<servername>1\BCK -hyperv:%arg1%"
WBADMIN START BACKUP -backupTarget:\\<servername>\BCK -hyperv:%arg1%
:END

May not be the most elegant of code or robust, but it gets the job done.

//JP

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.

find . -type f -mtime -1 -exec mv ‘{}’ /ora/bin \;

This will move all files found that were created that day to the directory /ora/bin.

useradd -u 200 -d /export/home/oracle -g oinstall -G dbadmin -m -s /bin/bash -c “RDBMS install user” oracle

Now, what does it mean?
-u = userid (it is convenient to keep this the same on multiple systems)
-d = home directory
-m = make the home directory if it doesn’t exist (believe me, it’s quite frustrating if you don’t (know) use this one.)
-s = which shell to use
-c = comment about the user

and at the end the user’s username.

source http://docs.sun.com/

UPDATE::
I noticed that the order in which the flags are given are important on version 11.2:

source https://docs.oracle.com/ & Jeff Taylor’s Weblog

And let’s add some X11 related packages in the mix so we can connect via an X-terminal:
pkg install xauth
pkg install x11/diagnostic/x11-info-clients
pkg install xclock
pkg install library/motif

So, a couple of updates since last or last-er:

Using mailx to send a mail with attachment:

mailx -m -r sender_user@example.org -s "Subject header" -a /file/to/attach.log receiver_user@example.org

With uuencode to attach a file:

uuencode <source_file> <target_file> | mailx -m -r sender_user@example.org -s "Subject header" receiver_user@example.org

With mutt to attach a file:

echo "This is the message body" | mutt -a "/file/to/attach.log" -s "Subject header" -- receiver_user@example.org

[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.

I was just playing on the windows cli, trying to create a shortcut to a mysql binary directory, where I noticed that a shortcut/link to a directory didn’t behave as I initially expected it to do. Enter Jon Coombs on superuser.com:

:\Users\user5\Desktop>mklink /D mysymlink  "C:\ProgramData\Microsoft\Windows\S
tart Menu\Programs\G"
symbolic link created for mysymlink <<===>> C:\ProgramData\Microsoft\Windows\Sta
rt Menu\Programs\G

C:\Users\user5\Desktop>cd mysymlink

C:\Users\user5\Desktop\mysymlink>

Because I created my symlink outside of my user directory, I had to create it using administrator privileges and added privileges to my user via advanced security settings.

source: tech-recipes
To remove the carriage-return character from a windows originating file in vi:

:%s/<Ctrl-V><Ctrl-M>/g

or
:%s/\r//g

In UNIX, you can escape a control character by preceeding it with a CONTROL-V. The :%s is a basic search and replace command in vi. It tells vi to replace the regular expression between the first and second slashes (^M) with the text between the second and third slashes (nothing in this case). The g at the end directs vi to search and replace globally (all occurrences).

Plain and simple, sweet as Π

exp \'/ as sysdba\'

Find and count files categorized per file extension:

find . \( -name . -o -prune \) -type f | sed -n 's/..*\.//p' | sort -f | uniq -c

find and do things

Here we come

After several mysterious data disappearances in MySQL, I finally had enough and went on to configure RoR to use a remote Oracle db installation.
Here’s what’s what:

Versions:
Rails 4.0.5
ruby 2.0.0p481 (2014-05-08 revision 45883) [x86_64-linux]

Gemfile:
gem 'ruby-oci8', '~> 2.1.5'
gem "activerecord-oracle_enhanced-adapter", "~> 1.5.0"

database.yml
development:
  adapter: oracle_enhanced
  encoding: utf8
  database: 
  username: 
  password: 

.bashrc
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export TNS_ADMIN=/usr/lib/oracle/12.1/client64/tns
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
export PATH="$PATH:$HOME/.rvm/bin:$LD_LIBRARY_PATH:$ORACLE_HOME/bin:$TNS_ADMIN" # Add RVM to PATH for scripting
[[ -s "$HOME/.rvm/scripts/rvm" ]] && source "$HOME/.rvm/scripts/rvm"

After several tries to catch the culprit, I was not able to figure out what was going on, it even got to the point where I created a fresh user after a

rake db:refresh

and after a quick bite to eat my user was gone!.

Troubleshooting:
First things first: Can you connect to the db via sqlplus!?
Can Ruby connect to the database?:

ruby -rubygems -e "require 'oci8'; OCI8.new('','','').exec('select * from dual') do |r| puts r.join(','); end"

When you have set the NLS_LANG parameter, you should just get ‘X’ as output.

Having a server grade motherboard has its downsides as well, more specifically, getting the thing to boot from an installation disk when it just won’t use your DVD drive at all…

Luckily, good things come to those who search relentlessly!

In this case, it comes in the form of a FAQ entry. Just in case it may move and I need it again, here’s what what:

Please keep dvd-drive connect to SATA 5 or 6.

Change following BIOS setting:
Advanced --> PCIe/PCI/PnP configuration --> Launch Storage OpROM policy --> Legacy only
Advanced --> Chipset configuration --> Integrated IO configuration --> Detect Non-compliance device --> enable
Advanced --> Boot feature --> INT19 Trap response --> Postponed

Working through the excellent rails tutorial by Michael Hartl, somewhere around the end chapter 9 I came across some unusual behavior of (what I suspect) Ruby/Rails. My data, which is committed to a MySQL database, kept disappearing.
Whenever a change was made to any of the spec or controller files, all my users would be gone 🙁
To get a grip on who the culprit is, I went off to create some triggers who would log what happened and when:

CREATE TABLE logger
( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, logger_event      VARCHAR(50)
, logger_table      VARCHAR(50)
, logger_instring   VARCHAR(100)
, logger_outstring  VARCHAR(100)
, creation_date     DATETIME
, last_update_date  DATETIME) ENGINE=MyISAM;

DELIMITER $$
CREATE TRIGGER trg_users_insert
AFTER INSERT ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'insert'
           ,'users'
           , new.email
           , null
           , new.created_at
           , null );
END$$

CREATE TRIGGER trg_users_update
AFTER update ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'update'
           ,'users'
           , old.email
           , new.email
           , old.updated_at
           , new.updated_at );	
END$$

CREATE TRIGGER trg_users_delete
before delete ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'delete'
           ,'users'
           , old.email
           , null
           , null
           , current_timestamp );	
END$$

Not the most sophisticated piece of code ever, but let’s see if it does the trick…

When working with Windows Server products any security setting can be altered by using the local security policy.
Enter HYPER-V server core.

Because I’m just meddling around with it, I definitely want to be able to use it on a as much as TeleTubby level as possible. Queue Josip Medved. Following his post:
Run this on a server where the configuration already meets your requirements or where you can edit the file created in the code below:
secedit /export /cfg X:\new.cfg

The task has completed successfully.
See log %windir%\security\logs\scesrv.log for detail info.

When needed, edit the part below:
[System Access]
MinimumPasswordAge = 0
MaximumPasswordAge = 39
MinimumPasswordLength = 0
PasswordComplexity = 1
PasswordHistorySize = 0
...
In this case you’d want to set the PasswordComplexity to 0.
Move the file to your HyperV server and run the following code:
secedit /configure /db C:\Windows\security\new.sdb /cfg X:\new.cfg /areas SECURITYPOLICY

The task has completed successfully.
See log %windir%\security\logs\scesrv.log for detail info.

Worked for me!

Ran too many times into issues with GUI Disk Management when trying to repartition a non-Windows partitioned thumb-drive:
1.Connect your disk.
2.Run cmd as an Administrator.
3.Run diskpart.exe. Use ? if you need help in this program.
4.list disk
5.Find the disk that corresponds to your USB disk. select disk n where n is the number of the disk. Confirm that you’re using the right disk with detail disk.
6.clean (Warning: This command erases the disk’s partition information)
7.create partition primary. No size is needed if you want to use the whole disk
8.active. Optional. Marks the partition as potentially bootable.
9.format fs=fat32 quick. You can choose NTFS or exFAT instead of FAT32 if you want.
10.assign. Assigns the disk a drive letter.
11.exit to quit.

url

Took a while to find a working solution for Ubuntu 13.10, found at the link above.
Use it at your own discretion…

find . -mtime +0 -exec rm -f {} \;

or

find /any/directory/you/may/have/ -type f -mtime +15 -exec rm -f {} +

find and do things

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

Just a quick bite:
find and pre-/append for formatting datafiles to use in create controlfile script:

find /m*/oradata/${SID} -type f | sed "s/^/'/;s/$/'/"

Result:

'/m001/oradata/database01/database01pdtdc00101.dbf'
'/m001/oradata/database01/database01posdl01.dbf'
'/m001/oradata/database01/database01posdm03.dbf'
'/m001/oradata/database01/database01sysaux01.dbf'
'/m002/oradata/database01/database01pserrc00201.dbf'
'/m002/oradata/database01/database01phvcf00101.dbf'

Read the rest of this entry »

When sending mail with attachement from the command line, I usually use:
uuencode | mailx -r sender_user@example.org -s “Subject header” receiver_user@example.org

On HP-ux, this does not work. As stated on the webs, this rips the guts out of the file and puts it in the body of the message. This has something to do with the MIME headers being added which triggers the SMTP relay agent to do it’s grizzly work.
So add the “-m” flag and all is well, this is confirmed to work on HP-UX B.11.31:

uuencode | mailx -m -r sender_user@example.org -s “Subject header” receiver_user@example.org

In this particular case, I was trying to export from a 10.2.0.4 db with exp from 8.1.7.4.
The schema in question had (several) lobsegments, which would trigger Oracle bug 3784697.

Summary Bug report:

Bug 3784697: EXPORT OF LOB FROM 9205 WITH PRE-9205 EXP GIVES EXP-3 (NO STORAGE DEF FOUND) Read the rest of this entry »

To run just one module:
rspec spec/models/user_spec.rb -e “should have an encrypted password attribute”

Very nifty:
https://sites.google.com/site/macdsite/utilidades/puttytabmanager

Better yet:
http://www.windowstipspage.com/2010/06/configure-putty-connection-manager.html (first bullet = Download link)

Generally I use:

:%s/search/replace/g

This page shows the means of inserting a character at the beginning of each line:

:14,20s/^/#/

Creating raidz zpool with ZIL, mirrored cache and spares == {Pretty straightforward}: Read the rest of this entry »

As you may notice below, this is a VM with plenty of disks, the intention is to have a mirrored rpool with a ZFS ZIL. There are two 16Gb disks for the rpool, one 10Gb disk for the ZIL and ten (10) 5Gb disks for a RAIDZ2 zpool. This post will show how to mirror the rpool on Solaris Express 11, it is a shameless ctrl-c/ctrl-v of this blogpost. But it works none-the-less… Read the rest of this entry »

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 »

For the life of me, I can never seem to remember this stuff… Read the rest of this entry »

So, way back when I wrote this post, I thought I was done with it.

Seems the procedure described then, isn’t any good now. At least, come Hell or high water, I could not get a static IP and enjoy it…
Till this pdf crossed my digital path, and sure enough… I got a static IP which worked (skip to page 17: “Configuring Network Resources”).
Below the transcript: Read the rest of this entry »

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

Now this is how you exercise a Disaster/Recovery test

source: click

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 »

Source: click

Essentially it comes down to this, as root: Read the rest of this entry »

All rights belong to their respective owners, etc., etc.

Received sendmail errors, looked it up on internet. Turns out I didn’t fill out my /etc/hosts file correctly.
Here’s what’s what: Read the rest of this entry »

Just a quick note: this page@developer center contains an index of all (?) built-in functions that SQL Server 2008 has.

Put it to good use…

Also found a ‘best practices’ link.

Not sure if I ever posted this before. Couldn’t find it none the less, so here goes:

To be able to mount NTFS and/or EXT2fs usb HDD, read this post on OpenSolaris forums. It will give a link to genunix where you can download 2 packages and a txt file with instructions.

Works every time, wish I could say the same for the functionality of smb/server.

If you have read this post, then you would have known i have this motherboard and that it has two nic’s. Now during the installation, i just went with the default and that means it is automatically configured via nwam (see this pdf -page 11- or this page, they’re practically the same!). Of course, i can’t have that…

So, here’s howto configure it manually, just the way you like it:
First, see what the status is of – and disable nwam: Read the rest of this entry »

Howto Microsoft loopback adapter
Howto RSS Feed from a Oracle database
Howto create SSO in APEX

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 »

So, here i am. Trying to setup a Solaris xVM hypervisor….
Installed OpenSolaris using the ‘install’ icon on the desktop of the livecd.

According to the package manager, SUNWxvmdom, SUNWxvmipa and SUNWxvmpv were already installed, so i downloaded the rest (available via the package repository from opensolaris.org): SUNWvirtinst, SUNWvirt-manager and SUNWxvm. Those 3 were prox. 8 Mb in size. 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 »

When catching up on my L-/RPADding for graphical histograms on the sql*plus prompt, i ran across this link on Tom’s blog.

Should come in handy, when building reports in PHP that query (Oracle) databases and saves the results in Excel. Nice.

Read the rest of this entry »

Beware of parsing, beware of I/O, beware of the accent.
Just beware…
Read the rest of this entry »

Ran into a offline backup that took too long and went outside of the blackout.
A little querieng against the V$SESSION_LONGOPS told me it would take prox. another 3.8 hrs to finish.

Not something you want to wait for @ 02:00 am.

This is a 9i database (9.2.08) in archivelog mode, mounted an’ all.
All it took was a: “alter database open” and the offline backup transformed into a online version. 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 »

Click here to go to the Progress ESD page.

+ 2 fora concerning Progress database administration:
*PSDN
*ProgressTalk.com

Better not shout @ your disks, they will lose confidence but will gain latency:

Read the rest of this entry »

Just came across a directory with these permissions: drwxr-sr-x

Here’s howto set it:

chmod 2755 

Here are the sources: example man

useradd -u 200 -d /export/home/oracle -g oinstall -G dbadmin -m -s /bin/bash -c “RDBMS install user” oracle

Now, what does it mean?
-u = userid (it is convenient to keep this the same on multiple systems)
-d = home directory
-m = make the home directory if it doesn’t exist (believe me, it’s quite frustrating if you don’t (know) use this one.)
-s = which shell to use
-c = comment about the user

and at the end the user’s username.

source http://docs.sun.com/

UPDATE::
I noticed that the order in which the flags are given are important on version 11.2:
/usr/sbin/useradd -u 54321 -m -d /export/home/oracle -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba -s /bin/bash -c “RDBMS install user” oracle
/usr/sbin/useradd -u 54322 -m -d /export/home/grid -g oinstall -G asmadmin,asmdba,dba -s /bin/bash -c “INFRASTRUCTURE install user” grid

source https://docs.oracle.com/ & Jeff Taylor’s Weblog

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 »

Quite a mouthfull to refer to something that takes privileges away or adds them.
I needed/wanted to take the “System shutdown” privileges away from my kids on my computer, once in a while i might be downloading something that might otherwise take unnecassary longer.
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 »

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 »

Just began reading this book, in aid of my (short-term) memory, here are (the first of) a few excerpts from that book, after the jump:
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 »

Read the rest of this entry »

Find stuff in *nix:

find /file_system/file_system -name file_name -type f -exec rm {} ;
What this does is, find files (-type f) with the name file_name in given file_system and executes rm

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

Just a mental note for myself for using explain plan:
Read the rest of this entry »

Just ran into a hidden carriage retun inside the requested data. I wanted to spool to a .csv file and got garbled data.
Fix like this: Read the rest of this entry »

Of course you can use kill -9 pid, but sometimes you just can’t find the pid.
In that case try: fg.

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.

And how to roll ’em back:

Stop Spine/MQ and query dba_2pc_pending and/or dba_pending_transactions.

As sysdba :

rollback force 'local_tran_id#';
exec dbms_transaction.purge_lost_db_entry('local_tran_id#');
commit;

Et voila…

Sources: Admin_guide DBA_2PC_NEIGHBORS DBA_2PC_PENDING DBA_PENDING_TRANSACTIONS

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

Appearantly Nero Scout mucks about in the browser history, causing the “delete all” to hang.
I’m using IE7 in combination with Nero8, here’s how to solve it:

* Start
* All Programs
* Nero 8
* Nero Toolkit
* Nero Scout -> clear the “enable” check box

Problem solved !

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.

Not for n00bs
How on Earth do you return the column definitions of a table as the names for the start- and endtags ?

On Adobe.com® there is a tutorial for Adobe Flex® where the result of a query to a MySQL® database is returned via PHP as XML. The column names retrieved in this query are looped through and put in the start- and endtags in the XML-document.
Of course, MySQL® isn’t “good” enough >;-) and this is altered to query an Oracle® database….

click for source code.
click for xml result.

The easiest thing you will find on the net for spooling a resultset from Oracle to an Excel format via php, will probably be that you need to add: 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 »

Edit /etc/default/login with your editor (probably vi). Navigate to the line which reads
Read the rest of this entry »

‘This is a perfect howto for enabling Apache2 on a Solaris machine.
Click here if you want SAMP. The article refered to on Blastwave, is here (link doesn’t work)
Read this to configure your system for DNS. Or this one…
This one is nice too, it’s an overview of a lot of manuals/howto’s.

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 »

SQL> conn user_name@(description=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domain)(PORT=portnumber))(CONNECT_DATA=(SID=sid)))
Enter password: ********
Connected.

pretty straightforward

Here’s how:

sql> shutdown immediate
sql> startup mount
sql> exit

Read the rest of this entry »

UFS: export a filesystem to a container and remove the filesystem from the configuration:
Read the rest of this entry »

To count number of process occurrences on *nix:

ps -ef|grep search_attribute|wc -l

Create TAR file:
tar -cvf archive_file.tar file_to_archice.doc

Append a file to a TAR file:
tar -uvf archive_file.tar file_to_archice.doc

Extract a TAR file:
tar -xvf archive_file.tar directory_to_extract

List the contents of a TAR file:
tar -tvf archive_file.tar

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

– 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)

Even when it’s used:

rm -rf /file_system/file_system/fil…..

du -h /file_system | sort -n | tail -10 grep -v parameter

What this does is, give a result with the items in a given file system that sorted according to their size and only the top ten.
The grep -v excludes the parameter given.

Below a couple of queries to give you an idea about how your database is performing: Read the rest of this entry »

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

If you regard mailing from unix as quite a hassel, see the following:
uuencode old_file_name new_file_name | mailx -r reply_address -s “subject” to_address

In this manner, the file that you send with mailx, will be attached via uuencode.
If you need to send more files, tar hem.

For anyone working with the wonderfull product Progress from OpenEdge and especially version 91D, here is the Documentation site of Progress OpenEdge.

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

&>  > alert***.log

Ever wanted to know what that ORA-600 is about ?:
Oracle Metalink