Category Archives: Oracle

Oracle RAC ologgerd process high cpu load…

The ologgerd is part of Oracle Cluster Health Monitor and is used by the Oracle Support to troubleshoot RAC problems. If ologgerd process is consuming a lot of CPU and writes a lot of data to disk, it can be stopped by executing on both nodes:

# crsctl stop resource ora.crf -init

to disable ologgerd permanently, execute:

# crsctl delete resource ora.crf -init

Oracle 11gR2 Solaris DBConsole did not start…

On my machine the dbconsole didn’t start.

The problem was the timezone didn’t match the timezone of the machine/host.

$ORACLE_HOME/solaris_DB/sysman/config$ vi emd.properties

agentTZRegion=+02:00

===============================================================

how to recreate DBConsole & repository:

export ORACLE_UNQNAME = XXX

emca -deconfig dbcontrol db -repos drop

emca -repos create

emca -config dbcontrol db

Solaris ZFS snapshot to save machine state…

Very nice feature of solaris is to use a zfs snapshots to protect the current state/configuration of a machine.

And it is very easy to use, all we have to do is:

> beadm list
BE Active Mountpoint Space Policy Created
-- ------ ---------- ----- ------ -------
solaris NR / 11.23G static 2013-07-04 16:45
solaris-backup-1 - - 155.0K static 2013-07-04 17:28

After the installation of solaris a snapshot was already created automatically.

After installation and configuration of various software we can create a new snapshot:

beadm create -e solaris solaris-backup-2

If something bad will happen afterwards we can just simple select the backup at the boot manager! and then activate our backup as the active snapshot:

beadm activate solaris-backup-2

I think the old/bad snapshot/base can be deleted afterwards, but i didn’t yet tried that, maybe it is a good idea to create a base snapshot and working snapshot, and set the working snapshot as the active one, so in the case of an error we can create a new snapshot based on the base snapshot, and afterwards we can remove the bad working snapshot.

Solaris is very smart 🙂

SmartHome Architecture…

SmartHome…. high availability architecture… but just for fun and to play with some technologies. The system is also able to run on a single system like the “Scada Local” system, and this is just an old Lenovo Netbook with 2GB RAM…

Mobile-Phone-App: http://www.rocworks.at/wordpress/rocvmobile/

Used software: Arduino, RaspberryPI, WinCC OA, VMWare ESXi, iSCSI-Target TGT, Oracle-Solaris, Oracle-Linux, Oracle-Database & ASM, Oracle-Apex…

Progamming the Arduino was pretty cool, this cheap micro controller is very powerful 🙂  Implemented a rough interface to WinCC OA (powerful SCADA software made by Siemens/ETM)…

SmartHomeArchitecture

Install new database host and reuse existing ASM database…

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

Setup Solaris 11.1 / 11.2 for Oracle Database with ASM

# 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 database ASM migration….

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

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

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