POUWIEL|COM

JeroenPouwiel

Synology :: Docker

After a reboot of a container, I noticed the port mappings changed.
A quick edit of:

root@synonas:/usr/syno/etc/packages/Docker# cat ora12c001.config
{"cap_add":[],"cap_drop":[],"cmd":"","cpu_priority":0,"ddsm_bind_share":"","devices":[],"enable_publish_all_ports":false,"enable_restart_policy":false,"enabled":true,"env_variables":[],"exportin                                           g":false,"id":"fe1a6706865e0a7eaf735a8d17530b23805b5291aa4749424eac30dc459f1a63","image":"sath89/oracle-12c:latest","is_ddsm":false,"is_package":false,"links":[],"memory_limit":0,"name":"ora12c001","network":[{"driver":"","name":"bridge"}],"port_bindings":[{"container_port":1521,"fixed":false,"host_port":0,"type":"tcp"},{"container_port":8080,"fixed":false,"host_port":0,"type":"tcp"}],

put a stop to that.
Just put the desired value in place of the 0 in the “port_binding” section.

Checking for (in-)compatibility in charactersets

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

OraPKI :: wallets and certificates

[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

PL/SQL thingy for selecting and creating insert PL/SQL thingy

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

Codepages, charactersets and territorial settings….

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…

HYPER-V :: backup VM’s locally

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

Find and move files

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 on Solaris 11.2

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

Mailing from *nix

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

GRANT ANY ROLE without granting “GRANT ANY ROLE”

[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

Sym links in Windows CLI

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.

VI :: remove break lines with search/replace

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

Find and count files

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

RoR on remote Oracle db

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.

MySQL, triggers and the obscure disappearance of data when using RoR…

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…

HYPER-V | Working without the Local Security Policy snap-in

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!

## Update::
OR!..
You can just
cmd::> gpedit.msc /gpcomputer: remote-computer
powershell::> Invoke-Command -ComputerName FQDN-of-computer {gpupdate /force}

Also works very nice and isn’t that cumbersome.

MicroSoft Windows | DiskPart.exe

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.

RDP from Windows 8 to VM Ubuntu 13.10

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 and delete files older than today in current directory

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

or

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

find and do things

Attach to datapump job when jobname is in lowercase

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

FIND | SED

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'

Mailx

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

PuTTY tab Manager

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)

Creating raidz zpool with ZIL, mirrored cache and spares

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

Mirroring the rpool

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 »

To gather any statistics

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 »

Oracle Solaris 11 Express: change from nwam DHCP to ipadm static IP

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 Jan 2011 – 8836671 & OUI-67294

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

Unattended Export/Import

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 »

Display IRQ settings in OpenSolaris

Source: click

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

unable to qualify my own domain name

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 »

Mounting NTFS usb HDD on OpenSolaris

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.

Fixing the network configuration on my OpenSolaris box

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 »

Flagitious, guileful and malignant piece of code

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 »

OpenSolaris 2008.11 xVM Hypervisor

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 »

Stringing a datafile…

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 »

Backup controlfile

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

T-bit and S-bit on *nix

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 on Solaris

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

Oracle 7

Quick reminder:

$> svrmgrl 
sql> connect internal

Online Redolog corruption or a bug ?

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 »

Find and do things in *nix

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

Kill a nohup process

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

Oracle agent options

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

When db = down, but is still shutting down

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

Log in as root from other than /dev/console

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

Apache2 on Solaris 10

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.

Solaris + MD5

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 »

Oracle silent install

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

To change the DBid of an database:

Here’s how:

sql> shutdown immediate
sql> startup mount
sql> exit

Read the rest of this entry »

UFS: export a filesystem to a container,..

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

Counting lines on *nix

To count number of process occurrences on *nix:

ps -ef|grep search_attribute|wc -l

Tarring

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 »

Force agent to gather and upload info to grid

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

Upgrade Oracle

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

Remove an entire directory in *nix

Even when it’s used:

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

Disk Usage in *nix

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.

Mailing from unix

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.

Categories