Install new database host and reuse existing ASM database…

10. June 2013 vogler

My Database-Host was gone (don’t ask why…), but my database files are hosted by ASM (data diskgroup with 3 iSCSI disks), so i was able to install a new Solaris-Host and build up my database server with the existing ASM diskgroup and database in the following way:

Install grid-software with option “software only”

Run the roothas.pl script from Grid_home using the following syntax:

as root user: /app/11.2.0/grid/perl/bin/perl -I /app/11.2.0/grid/perl/lib -I /app/11.2.0/grid/crs/install /app/11.2.0/grid/crs/install/roothas.pl

as oracle user from the grid-home /app/11.2.0/grid/oui> ./runInstaller -updateNodeList ORACLE_HOME=/app/11.2.0/grid -defaultHomeName CLUSTER_NODES= CRS=TRUE

orapwd file=$ORACLE_HOME/dbs/orapw+ASM password=Password123 entries=5

mkdir -p $ORACLE_BASE/admin/+ASM
cd $ORACLE_BASE/admin/+ASM
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

init+ASM.ora:
*.asm_power_limit=1
*.diagnostic_dest=’/app/oracle’
*.instance_type=’asm’
*.large_pool_size=12M
*.remote_login_passwordfile=’EXCLUSIVE’

srvctl add listener
srvctl start listener
srvctl add asm -l LISTENER -p $ORACLE_HOME/dbs/init+ASM.ora -d ‘/dev/rdsk/*’

# disks
chown oracle:oinstall /dev/rdsk/c9t2d0s0
chown oracle:oinstall /dev/rdsk/c9t3d0s0
chown oracle:oinstall /dev/rdsk/c9t4d0s0
chmod 660 /dev/rdsk/c9t2d0s0
chmod 660 /dev/rdsk/c9t3d0s0
chmod 660 /dev/rdsk/c9t4d0s0
asmcmd> lsdsk –discovery
asmcmd> lsdsk
asmcmd> mount DATA

alter system set asm_diskgroups=’DATA’ scope=spfile;

create pfile with “SPFILE=+DATA/DB/spfiledb.ora”
mkdir -p /app/oracle/admin/DB/adump
srvctl add database -d DB -o /app/oracle/product/11.2.0/dbhome_1

Oracle, Solaris

Setup Solaris 11.1 / 11.2 for Oracle Database with ASM

10. June 2013 vogler

# allow root ssh
1) Modify PermitRootLogin to yes in /etc/ssh/sshd_config.
2) Comment out the “CONSOLE=/dev/console” line in /etc/default/login.
3) Remove “type=role” from the root entry in /etc/user_attr.

# for ssh X11 forwarding
pkg install xauth
/etc/ssh/sshd_config:
# X11 tunneling options
X11Forwarding yes
X11DisplayOffset 10
X11UseLocalhost yes

svcadm restart svc:/network/ssh:default

# check network cards
dladm show-phys
dladm show-link
ipadm show-if
ipadm show-addr

# change profile to fixed
netadm enable -p ncp DefaultFixed

# setup network to public network
ipadm create-ip net0
ipadm create-addr -T static -a 192.168.1.19/24 net0/acme
svccfg -s network/dns/client setprop config/nameserver=net_address: “(192.168.1.1)”

root@solaris:~# svccfg
svc:> select dns/client

svc:/network/dns/client> select dns/client
svc:/network/dns/client> setprop config/nameserver=net_address: “(192.168.1.1)”
svc:/network/dns/client> refresh
svc:/network/dns/client> validate

svc:/network/dns/client> select name-service/switch
svc:/system/name-service/switch> setprop config/host = astring: “files dns”
svc:/system/name-service/switch> refresh
svc:/system/name-service/switch> validate

# set defaultrouter/nameserver:
echo “192.168.1.1” >> /etc/defaultrouter
echo “192.168.1.1” >> /etc/resolv.conf

# setup network to storage network
dladm set-linkprop -p mtu=9000 net1
ipadm create-ip net1
ipadm create-addr -T static -a 192.168.100.19/24 net1/acme

# user and groups
groupadd -g 1000 oinstall
groupadd -g 1001 dba
useradd -u 1000 -g oinstall -G dba -m oracle
mkdir -p /app/11.2.0/grid
mkdir -p /app/oracle
chown -R oracle:oinstall /app
chmod -R 775 /app/

# set swap to ram=swap size (for below 16g RAM, for more than 16g RAM set swap fix to 16g)
zfs set volsize=4g rpool/swap

# packages
pkg install SUNWarc
pkg install SUNWbtool
pkg install SUNWcsl
pkg install SUNWhea
pkg install SUNWlibC
pkg install SUNWlibm
pkg install SUNWlibms
pkg install SUNWsprot
pkg install SUNWtoo
pkg install SUNWi1cs
pkg install SUNWi15cs
pkg install motif

# 12.1.0.2
pkg install x11-info-clients

file-descriptor-limit:
add a line to /etc/system and reboot:
set rlim_fd_max=1024

# allow oracle user to use more than 25% of system memory (execute as root user!)
projadd -p 100 -c ‘Oracle Database’ -U oracle -G dba -K ‘project.max-shm-memory=(privileged,3G,deny)’ DB
usermod -K project=DB oracle

# iscsi static connect for asm disks
iscsiadm add static-config iqn.2013-06.nas.solarisvm.asm,192.168.100.8:3260
iscsiadm modify discovery –static enable

# prepare local disk for ASM
format
AVAILABLE DISK SELECTIONS:
0. c8t0d0 <IET-VIRTUAL-DISK-0001-40.00GB>
/pci@0,0/pci15ad,1976@10/sd@0,0
1. c8t1d0 <IET-VIRTUAL-DISK-0001 cyl 2046 alt 2 hd 128 sec 32>
/pci@0,0/pci15ad,1976@10/sd@1,0
2. c8t2d0 <IET-VIRTUAL-DISK-0001 cyl 5219 alt 2 hd 255 sec 63>
/pci@0,0/pci15ad,1976@10/sd@2,0
3. c8t3d0 <IET-VIRTUAL-DISK-0001 cyl 5219 alt 2 hd 255 sec 63>
/pci@0,0/pci15ad,1976@10/sd@3,0
4. c8t4d0 <IET-VIRTUAL-DISK-0001 cyl 5219 alt 2 hd 255 sec 63>
/pci@0,0/pci15ad,1976@10/sd@4,0

format> fdisk
format> label
format> partition
partition> p

Volume: ASM00D1
Current partition table (original):
Total disk cylinders available: 5218 + 2 (reserved cylinders)

Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 0 (0/0/0) 0
1 unassigned wm 0 0 (0/0/0) 0
2 backup wu 0 – 5217 39.97GB (5218/0/0) 83827170
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 0 0 (0/0/0) 0
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 0 0 (0/0/0) 0
7 unassigned wm 0 0 (0/0/0) 0
8 boot wu 0 – 0 7.84MB (1/0/0) 16065
9 unassigned wm 0 0 (0/0/0) 0

partition> 0

Enter partition id tag[unassigned]: usr
Enter partition permission flags[wm]:
Enter new starting cyl[1]:
Enter partition size[0b, 0c, 1e, 0.00mb, 0.00gb]: 5217c
partition> label
Ready to label disk, continue? y

# change owner and permission for disks
chown oracle:oinstall /dev/rdsk/c8t1d0s0
chmod 660 /dev/rdsk/c8t1d0s0

# add a new disk to an existing diskgroup:

> sqlplus / as sysasm

SQL> select name, path from v$asm_disk;

NAME
——————————
PATH
——————————————————————————–

/dev/rdsk/c8t6d0s0

DATA_0003
/dev/rdsk/c8t2d0s0

DATA_0001
/dev/rdsk/c8t3d0s0
NAME
——————————
PATH
——————————————————————————–
DATA_0002
/dev/rdsk/c8t4d0s0

DATA_0004
/dev/rdsk/c8t5d0p0
SQL> alter diskgroup data add disk ‘/dev/rdsk/c8t6d0s0’;

Oracle

Oracle database ASM migration….

4. June 2013 vogler

Successfully migrated an oracle database from filesystem to ASM. More or less it was easy…

shutdown immediate
startup mount
alter database archivelog;
startup;

[oracle@scada ~]$ rman target=/

Recovery Manager: Release 11.2.0.3.0 – Production on Mon Jun 3 19:35:59 2013

connected to target database: DB (DBID=1578985198)

RMAN> backup as copy incremental level 0 database format ‘+DATA’ tag ‘ORA_ASM_MIGRATION’;

RMAN> restore spfile to ‘+DATA/DB/spfiledb.ora’;
RMAN> shutdown immediate

mv $ORACLE_HOME/dbs/spfileDB.ora $ORACLE_HOME/dbs/spfileDB.ora.bak
echo “SPFILE=’+DATA/DB/spfiledb.ora’ > $ORACLE_HOME/dbs/initDB.ora

SQL> STARTUP FORCE NOMOUNT;
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST=’+DATA’ SID=’*’;

if ( FRA is used ) {
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID=’*’;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’+FRA’ SID=’*’;
}

SQL> ALTER SYSTEM SET CONTROL_FILES=’+DATA’,’+DATA’ SCOPE=SPFILE SID=’*’;
SQL> SHUTDOWN IMMEDIATE

RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM ‘/data/oradata/DB/control01.ctl’;
RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE;

SQL> alter database add logfile thread 1 size 100M;
SQL> alter database add logfile thread 1 size 100M;
SQL> alter database add logfile thread 1 size 100M;
SQL> select * from v$log;
SQL> select * from v$logfile;
SQL> alter database drop logfile group <nr>
SQL> alter database drop logfile member ‘/u01/oradata/DB/onlinelog/o1_mf_2_8tssrbd8_.log’;
SQL> ..
SQL> alter system switch logfile;
SQL> alter system checkpoint global;
SQL> alter tablespace temp add tempfile size 2G;
SQL> alter tablespace temp drop tempfile ‘/u00/oradata/DB/temp01.dbf’;

Allgemein, APEX, Oracle, WinCC OA

WinCC OA APEX jQuery mobile smartphone demo…

13. March 2013 vogler

Apex-Mobile-Demo

Simple mobile smartphone demo build with Oracle APEX connected to WinCC OA (www.etm.at).

It tooks me about 3h… and i had NO experience with APEX!

The map does not work on (my) android smartphone… maybe a known bug: http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-42-known-issues-1863578.html

Connectivity WinCC OA <=> Oracle:

Oracle-DB => PL/SQL => XMLRPC-Calls => WinCC OA (Control)

Currently the connectivity library is very basic, but if i will find some time i will extend it… and you will find it on sourceforge…

if you have a valid Oracle database license APEX is for free! if you don’t have one, you can use Oracle-XE, which you can get for free…. 🙂

Allgemein, APEX, Oracle, WinCC OA

Oracle and APEX meets WinCC OA…

13. March 2013 vogler

With a “few lines” of code Oracle is able to fetch WinCC OA values over XMLRPC webservice – current and history values (WinCC OA RDB-Manager is not needed).

WinCC OA values can be queried with “natural” Oracle SQL Statements

select t.dp, wccoa.dpGet(t.dp||'.int'), wccoa.dpGet(t.dp||'.int:_online.._stime') from dpnames t where dp like 'Example%'

So, it is also easy to create “WinCC OA” (Web)APEX Applications!

Allgemein, WinCC OA

Update C# WinCC Open Architecture Connector

10. February 2013 vogler

New “C# WinCC Open Architecture Connector” version.

2013.01.01: Added Proxy-Server with client/server TCP/IP connection for dp(Query)Connects and unsolicited data multicasting.

2012.12.23: Added a very simple C# WinForms application which displays two datapoints, waits
for value changes and values can also be set by a button out of the WinForms application.

2012.12.23: Added WaitDataMulti function which performs a dpWaitForValue. Can be used to wait
for a change of value of data points of a taglist.

Allgemein, Oracle

Windows TopThreads for Oracle…

9. February 2013 vogler

list top windows threads. useful when using oracle on windows, where oracle is one big process with many threads (and windows task manager doesn’t show threads of a process…). if an odbc connection string is passed then also some useful information about the corresponding oracle process/session is displayed (oracle user, sid, serial#, …)

http://sourceforge.net/projects/topthreads/?source=directory

Allgemein, WinCC OA

C# iPhone App for WinCC OA build with MonoTouch!

8. February 2013 vogler

http://www.youtube.com/watch?v=k_55xCzwWrQ&feature=player_embedded#!

Functions implemented in C# for WinCC OA communication can also be used to build Android Apps with Mono for Android from Xamarin – http://xamarin.com

WinCC OA

C# WinCC OA Connector…

20. January 2013 vogler

http://sourceforge.net/projects/roc-winccoa-lib/?source=directory

Communication can be done directly with XMLRPC or with the included proxy:

.net remoting is used for “commands” from the client to the proxy (dpConnect, dpQueryConnect).
tcp (xml over tcp) for sending unsolicited data/events from the proxy to the client.

The proxy also acts as a distributor – if clients have the same dp(Query)Connects,
the connects in the control are done once, and the proxy will forward it to the clients.

Added functions for dpConnects without using “Tag” Objects (the Tag objects where created for
mobile clients). Now it is easy to create for example a dpQueryConnect. The callback function
is just a C# delegate:

// create server and client objects
client = new WCCOAClient (ProxyHost, ProxyRemotePort, ProxyClientPort);

// start and connect
client.Start ();
client.Connect ();
client.DpQueryConnectSingle((object s, ArrayList a) => {
WCCOABase.PrintArrayList(a);
}, “SELECT ‘_online.._value’, ‘_online.._stime’ FROM ‘*.**'”);

The proxy (WCCOANetServer.exe) must be started on a server (can, but must not be, the WCCOA Server).

Works fine with MonoTouch and Mono for Android!

WinCC Open Architecture must run the included Ctrl-Script xmlnet_server.ctl

GPLv3 License is used for the public domain.
If you plan to use it in a CLOSED SOURCE project,
please get in touch with me – andres.vogler@gmail.com !

Allgemein