Citrix Linux Client 64bit (AMD64) for Ubuntu

Solution is here:
http://www.madox.net/blog/2009/05/04/citrix-linux-client-64bit-amd64-for-ubuntu-jaunty-904/

I've done it on Ubuntu 10.04 Lucid Lynx with
Citrix Linux Client version 11.1
linuxx86-11.100.158406.tar
libmotif3_2.2.3-4_i386.deb

Training environment refresh (database flashback)

Environment
Oracle 10.2.0.4, Solaris 10

Requirements
Create regular refresh of training database.

Solution
1.    Check that the following parameters are set as you want and there is sufficient disk space.
db_recovery_file_dest
db_recovery_file_dest_size
log_archive_format
log_archive_dest_1
2.    Make clean shutdown and mount database.
shutdown immediate;
startup mount;
3.    Enable archivelog and disable flashback.
When flashback is disabled, database can be flashed back only to a guaranteed restore point. When data block is modified multiple times, only original block image is stored in flashback logs, so you can not flash back to another SCN, but you have less disk utilization compared to enabled flashback.
alter database archivelog;
alter database flashback off;
4.    Check if you have any restore points and drop them if they are not needed.
select * from v$restore_point;
drop restore point ;
5.    Create guaranteed restore point.
create restore point GRP1 guarantee flashback database;
6.    Open database.
alter database open;

7.    Schedule the following script to refresh database at required time.
You can delete all archive logs. I keep 4 days just in case. Actually if even recent archive logs are deleted (created between resetlogs and current moment), database flashback is successful. But you cannot switch to noarchivelog if there are guaranteed restore points.

#!/usr/bin/ksh
cd
. ./.profile
# stop dependant applications if needed
#
sqlplus "/as sysdba" << EOF
shutdown immediate;
startup force mount;
flashback database to restore point GRP1;
alter database open resetlogs;
EOF

# start dependant applications if needed
#
rman target / << EOF
delete force noprompt archivelog all completed before 'sysdate-4';
EOF
FORCE option is necessary here. When database is opened with RESETLOGS option, FGRD archives redo logs before logs are cleared. So some logs will be archived twice by ARCH and FGRD, and control file will have two records of one log. You can see it in V$ARCHIVED_LOG. When logs are deleted, second DELETE of the same log will return error that file doesn’t exist.
For example:
RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Archivelog      /oracle/archive/dhmerr04/disk/dhmerr04_0000000001_1_729369092.arc
RMAN-06214: Archivelog      /oracle/archive/dhmerr04/disk/dhmerr04_0000000002_1_729369092.arc

Useful info
http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14192/rpfbdb001.htm

Restoring 10g database from tape to different location

Environment

Oracle 10.2.0.4, UNIX AIX 5.3.0.0, IBM Tivoli Storage Manager (TSM)

Purpose

Restore database to different location.

Create test database from tape backup of production database.

RMAN DUPLICATE can not be used as connection to source (target) database is not available.

Destination database already exists.

Solution

All operations are done on destination host, assuming that source host and database are not available.

1. TSM specific: If don’t have proper tdpo.opt file, then create one. Make sure you have correct (source) host name in this file.
2. Set environment variables.

export ORACLE_SID=destdb

export ORACLE_SID_SOURCE=sourcedb

export TDPO_OPTFILE=/oracle/RMAN/etc/tdpo.opt

echo $ORACLE_SID

echo $ORACLE_SID_SOURCE

echo $TDPO_OPTFILE

3. If spfile is used, create pfile.
4. Shut down database and listener

lsnrctl stop listener_10g_$ORACLE_SID

sqlplus "/ as sysdba"

shutdown abort;

exit;

5. Delete old data files, redo logs and clean admin directories
6. Create temporary pfile, as db_name must be source database name.

cd $ORACLE_HOME/dbs

sed -e "s/db_name='$ORACLE_SID'/db_name='$ORACLE_SID_SOURCE'/1" init$ORACLE_SID.ora > init$ORACLE_SID.duplicate.ora
diff init$ORACLE_SID.ora init$ORACLE_SID.duplicate.ora

7. Start oracle instance and test that RMAN can connect to TSM.

rman target / <<EOF

startup nomount pfile=init$ORACLE_SID.duplicate.ora

run {

allocate channel c1 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';

}

EOF

8. Get appropriate name of control file backup, update following script, and restore control file

rman target / <<EOF

run {

allocate channel c1 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';

restore controlfile from 'c-1811786000-20100601-10-sourcedb';

}

EOF

rman target / <<EOF

alter database mount;

EOF

9. Prepare SQL script create_rman_duplicate_script.sql to generate RMAN script. In this example TEMP files are dropped. You can keep them and/or rename them similar to redo logs.

Set any directory for your scripts:

cd /oracle/admin/$ORACLE_SID/create

cat >create_rman_duplicate_script.sql <<EOF

set heading off

set timing off

set flush off

set feedback off

set echo off

SET PAGESIZE 0

spool rman_duplicate.rcv

prompt run {;

prompt ALLOCATE CHANNEL ch1 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';;

prompt ALLOCATE CHANNEL ch2 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';;

select 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '''||replace(name,'$ORACLE_SID_SOURCE','$ORACLE_SID')||''';' from V\$DBFILE order by FILE#;

select 'SET NEWNAME FOR TEMPFILE '||FILE#||' TO '''||replace(name,'$ORACLE_SID_SOURCE','$ORACLE_SID')||''';' from V\$TEMPFILE order by FILE#;

prompt restore database;;

prompt SWITCH DATAFILE ALL;;

prompt };

select 'sql "alter database rename FILE '''''|| MEMBER ||''''' TO '''''||replace(MEMBER,'$ORACLE_SID_SOURCE','$ORACLE_SID')||'''''";' from v\$logfile;

select 'sql "alter database tempfile '||FILE#||' drop";' from v\$TEMPFILE;

prompt exit;

spool off

exit

EOF

cat create_rman_duplicate_script.sql

10. prepare RMAN script

sqlplus "/ as sysdba" @create_rman_duplicate_script.sql

cat rman_duplicate.rcv

11. restore database

rman target / @rman_duplicate.rcv

12. Recover database if you restored it from hot backup, otherwise go to next step

rman target / <<EOF

sql "alter system set LOG_ARCHIVE_DEST_1=''LOCATION=/oracle/export''";

run {

allocate channel c1 device type sbt_tape parms='ENV=(TDPO_OPTFILE=$TDPO_OPTFILE)';

recover database;

}

EOF

13. open database

sqlplus "/ as sysdba"

alter database open resetlogs;

alter database backup controlfile to trace;

shutdown immediate;

14. To rename database, prepare script to create controlfile. Pay attention to “REUSE SET DATABASE”, SID and NOARCHIVELOG. Pick up script from udump directory. Instance will start with default spfile or pfile. Parameter db_name must be set to new database name.

sqlplus "/ as sysdba"

@create_control_file.sql

alter database open resetlogs;

exit;

15. Add files to TEMP tablespace.
16. Startup listener

Control files deleted

Environment
Oracle 10.2.0.4, Solaris 10

Problem
Oracle control files were accidentally deleted when database was open.
No backups were available. Database is in noarchivelog mode.

Solution
Luckily redo logs were not overwritten since database startup so media recovery is possible.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;

Prepare SQL statement to create control file with NORESETLOGS option, e.g.
CREATE CONTROLFILE REUSE DATABASE "DHMERT01" NORESETLOGS  NOARCHIVELOG ...

SQL> @create_controlfile.sql

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/01/DHMERT01/system01.dbf'

SQL> alter database recover database;
Database altered.

SQL> alter database open;
Database altered.

Clone Oracle Application Server

Environment
Solaris 10, OAS 10.1.2.3, client database Oracle 10.2.0.4,
no OAS infrastructure, only OC4J, HTTP server and Web Cache.

Cloning
1. prepare UNIX user profile and environment variables on destination host or environment

2. shutdown source OAS (however documentation does not require it)
$ORACLE_HOME/opmn/bin/opmnctl stopall
$ORACLE_HOME/bin/emctl stop iasconsole

3. prepare clone (source environment)
cd $ORACLE_HOME/clone/bin
perl prepare_clone.pl

4. archive Oracle home
cd $ORACLE_HOME
tar cf - * | gzip > /oracle/stage/cloneOAS/OAShome.tar.gz

5. copy compressed file to another host if needed

6. go to destination host and/or environment
cd $ORACLE_HOME
gunzip < /oracle/stage/cloneOAS/OAShome.tar.gz | tar xvf -

7. Start up all other Oracle services to keep ports busy, so cloning operation will automatically assign available ports. If you want to assign ports manually, prepare $ORACLE_HOME/staticports.ini file (destination home) and update cs.properties file.
vi $ORACLE_HOME/clone/ias/config/cs.properties
Update line
clone_command_line = -silent
Put proper location of staticports.ini, in this example /tmp
(It is one line. There is a space after silent)
clone_command_line = -silent oracle.iappserver.iapptop:szl_PortListSelect="{\"YES\",\"/tmp/staticports.ini\"}"

8. Back up your Oracle Inventory directory. OAS Installer cannot detach Oracle home.

9.    Start cloning process.
Attention! If the cloning failed and you are restarting the process
•    Delete latest XML files from temp directory /tmp
ls -lrt /tmp/*xml
•    Make sure there are no UNIX processes left (e.g. opmn)
ps -fu $LOGNAME
•    Restore Oracle Inventory (or detach Oracle home with 10.2 Installer if available)

Run clone script. It will prompt for old and new ias_admin passwords.
cd $ORACLE_HOME/clone/bin
perl clone.pl ORACLE_HOME=/oracle/ias/10.1.2/ora10R4 ORACLE_HOME_NAME=ora10R4_10gAS -instance ora10R4_10gAS

10. run root.sh
cd $ORACLE_HOME
./root.sh

11. Check configuration files and change (host names, ports, etc.) if necessary. e.g. tnsnames.ora, httpd.conf, dads.conf, application config files, etc.

12. Re-deploy Java applications if necessary.

Useful info
Oracle documentation

Clone oracle 10g database software on Solaris

Environment
Oracle 10.2.0.4, Solaris 10

Cloning
1. Install oracle software, recommended patches and CPU on source host.
2. Oracle recommends to shutdown any databases, listeners, agents etc before copying files.
3. prepare exception list
export CLONE=/oracle/stage/clone10.2.0.4
cd $ORACLE_HOME
find . -name "*.log" >$CLONE/ExcludeList.txt
find . -name "*.err" >>$CLONE/ExcludeList.txt
# network/admin can be useful for next installation
#find ./network/admin >>$CLONE/ExcludeList.txt
find ./oc4j/j2ee/OC4J_DBConsole_* >>$CLONE/ExcludeList.txt
find `hostname |cut -d "." -f 1`*_* >>$CLONE/ExcludeList.txt
# DBCA templates, If u have them
find ./assistants/dbca/templates >>$CLONE/ExcludeList.txt
cat $CLONE/ExcludeList.txt

4. archive source Oracle home
cd $ORACLE_HOME
tar -cvfX - $CLONE/ExcludeList.txt . | gzip -c >$CLONE/oracle_home.tar.gz

5. copy tar.gz file to target host
6. extract oracle home as root to preserve permissions
# cd $ORACLE_HOME
cd /oracle/rdbms/10.2.0/ora10P1
gunzip < /oracle/stage/clone10.2.0.4/oracle_home.tar.gz | tar xvf -
# if you need to change owner of installation. Owner group is the same.
find . -user ora10T4 -exec chown ora10P1 {} \;

7. on target host as oracle user
cd $ORACLE_HOME/rdbms/lib
mv config.o config.o_backup

8. Solaris specific. To change group (dba) that owns the installation modify
vi $ORACLE_HOME/rdbms/lib/config.s
otherwise go to next step

9. Solaris specific
IGNORE make -f ins_rdbms.mk config.o It returns: make: Fatal error: Command failed for target `config.o'
make -f ins_rdbms.mk ioracle

10. run Installer
cd $ORACLE_HOME/oui/bin
detach oracle home if it exists
./runInstaller -detachHome ORACLE_HOME="/oracle/rdbms/10.2.0/ora10P1"
./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/oracle/rdbms/10.2.0/ora10P1" ORACLE_HOME_NAME="db_home_10g2_1"

and run as root

$ORACLE_HOME/root.sh

11. (optional) Let other users use Oracle Home.
cd $ORACLE_HOME/install
./changePerm.sh

Useful links

1. Metalink DocID 300062.1 How To Clone An Existing RDBMS Installation Using OUI
2. Metalink DocID 558478.1 Cloning A Database Home And Changing The User/Group That Owns It
3. Oracle Software Cloning Using Oracle Universal Installer

Speed up index creation

To speed up CREATE INDEX for large table or to adjust PGA per session.
Environment
Solaris 10, Oracle database 10.2.0.4

Solution
The most obvious way is to use NOLOGGING and PARALLEL, e.g.

CREATE INDEX index_name ON table_name
(column1, column2)
TABLESPACE ts_name
PARALLEL (DEGREE 6) NOLOGGING;

If it is not fast enough, you need to increase sort area size. Using WORKAREA_SIZE_POLICY = MANUAL and increasing SORT_AREA_SIZE didn't give a good result in my case. PGA was still quite small.

Another option is to play with hidden parameters
_pga_max_size Maximum size of the PGA memory for one process
_smm_max_size maximum work area size in auto mode (serial)
_smm_px_max_size maximum work area size in auto mode (global)

To see your current values log in as SYS and run following query:
(_smm* parameters are already in KiB)

select substr(ksppinm,1,20) param, ksppstvl/1024 "size_KiB", substr(ksppdesc,1,70) description
from   x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and KSPPINM in ('_pga_max_size', 'pga_aggregate_target')
union all
select substr(ksppinm,1,20), to_number(ksppstvl), substr(ksppdesc,1,70)
from   x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and KSPPINM in ('_smm_max_size', '_smm_px_max_size');

for 10.2.0.4 it looks like Oracle automatically sets values as
_pga_max_size = 0.2 * pga_aggregate_target
_smm_max_size = 0.5 * _pga_max_size (it automatically changes when you change _pga_max_size)
_smm_px_max_size = 0.5 * pga_aggregate_target

So by changing pga_aggregate_target you can change PGA size limit for session. You also can change these hidden parameters dynamically and monitor PGA usage, e.g.

alter system set pga_aggregate_target=6G;
alter system set "_pga_max_size"=2G;

These changes affect all sessions.

Solaris cheat sheet for DBA

There are some handy Unix commands for DBA. It was done on Solaris 10.

System Configuration

prtconf

Find physical memory

prtconf | grep Memory

Information about CPU
Physical processors

psrinfo -p

psrinfo -pv

Virtual processors

psrinfo

psrinfo -v

Information about swap
List all swap areas

swap -l

Show swap usage

swap -s

Swapping activity (3 seconds 5 samples)

vmstat -S 3 5

Performance Monitoring

top

prstat

top processes with totals per user

prstat -a

sorted by memory and totals per user (shared memory shown).

prstat -a -s rss

Archiving
zip (-r) recursively and (-y) with symbolic links

zip -vry file.zip target_dir

tar and compress (tar stores symbolic links)

tar -cvf - target_dir | gzip -c > destination_dir/file.tar.gz

Use RELATIVE path for target directory, unless you want to restore files to the same location.
Unpacking tar.gz

cd destination_dir

gunzip < file.tar.gz | tar xvf -

Checksum and digest
digest -va md5 file_name
digest -va sha1 file_name
cksum file_name

32 or 64?
Is my Solaris 64-bit or 32-bit?
/usr/bin/isainfo -kv
Is my Oracle 64-bit or 32-bit?
cd $ORACLE_HOME/bin
file oracle

Oracle files
cat /var/opt/oracle/oratab
cat /var/opt/oracle/oraInst.loc

Solaris files
/etc/vfstab File system mount table
/var/adm/loginlog Log of login attempts
/var/adm/messages Syslog location

Lack of swap

Environment:
Oracle 10g (10.2.0.4), Solaris 10
Problem:
alert.log:
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5
Mon Mar  8 14:07:46 2010
Process J007 died, see its trace file
Mon Mar  8 14:07:46 2010
kkjcre1p: unable to spawn jobq slave process

Solution:
It happened to be lack of swap. For Solaris use:

swap -s

swap -l

Useful info:
For other status number of ORA-27300 check Metalink note ID 579365.1

Generate tnsnames from OEM repository

Environment: Oracle 10g, 10.2
To get tnsnames from OEM, connect to OEM repository and run the following SQL.
p3.property_value and t.target_name can be the same, or one of them can include domain name. You can choose one or both.
Script:

select p3.property_value||', '||t.target_name||' = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '||p1.property_value||')(PORT = '||p2.property_value||'))) (CONNECT_DATA = (service_name = '||p3.property_value||')))'
from SYSMAN.MGMT_TARGETS t, SYSMAN.MGMT_TARGET_PROPERTIES p1, SYSMAN.MGMT_TARGET_PROPERTIES p2, SYSMAN.MGMT_TARGET_PROPERTIES p3
where upper(t.TARGET_NAME) like 'DH%' -- filter targets if you don't want all of them
and t.target_type='oracle_database'
and t.target_guid = p1.target_guid
and t.target_guid = p2.target_guid
and t.target_guid = p3.target_guid(+)
and p1.property_name='MachineName'
and p2.property_name='Port'
and p3.property_name(+)='ServiceName'
order by 1;

SQL Plus profile

When you start SQL Plus or connect, it runs the glogin.sql site profile script and then runs the login.sql user profile script.
The global site profile is $ORACLE_HOME/sqlplus/admin/glogin.sql
The user profile script is login.sql. SQL Plus looks for login.sql in the current directory, and then in the directories specified by the SQLPATH environment variable.
e.g. Following script sets up date format, SQL prompt and shows where you are connected to.

set head off feedback off
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd HH24:MI:SS';
SELECT
'user: '||SYS_CONTEXT('USERENV','CURRENT_USER')||
' inst: '|| SYS_CONTEXT('USERENV','INSTANCE_NAME')||
' db: '|| SYS_CONTEXT('USERENV','DB_NAME')||
' host: '|| SYS_CONTEXT('USERENV','SERVER_HOST') c1
FROM DUAL;
set sqlprompt "_user'@'_connect_identifier> "

set head on feedback on
prompt ___________________________________________________________________
set serveroutput on size 20000 line 200 

Oracle documentation (SQL*Plus® User's Guide and Reference Release 10.2)

Send mail from Windows

When we create backup scripts sometimes we need to email notifications of success or failure.
There are few ways to do it. One of them is to use blat. Another one is VBScript. Here is example of such vbs:

Set oShell = CreateObject( "WScript.Shell" )
user=oShell.ExpandEnvironmentStrings("%UserName%")
comp=oShell.ExpandEnvironmentStrings("%ComputerName%")
Set objEmail = CreateObject("CDO.Message")
objEmail.From = user+"@"+comp
objEmail.To = "your-name@your-email.com"
objEmail.Subject = comp + " RMAN backup failed"
objEmail.Textbody = "Please check RMAN log file"
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
        "your.smtp.server.com"
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send

Database performance monitoring

Some handy commands to help with database performance monitoring and troubleshooting.
Environment: Oracle 10.2.0.4 on Solaris

1. PGA usage, top consumers
   select
   round(p.pga_alloc_mem/1024/1024) "pga_alloc_MiB",
   round(p.pga_used_mem/1024/1024) "pga_used_MiB",
   p.spid "OS_pid",
   s.sid, s.serial#, s.username, s.status, s.client_info, s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from v$process p, v$session s
   where p.addr=s.paddr
   order by p.pga_alloc_mem desc;
   
2. UNDO usage, top consumers
   select round(t.used_ublk*(select block_size from dba_tablespaces where tablespace_name=(select value from v$parameter where name = 'undo_tablespace'))/1024/1024) "undo_used_MiB",
   t.used_ublk used_blocks,
   t.START_DATE trn_started,
   s.sid, s.serial#, s.username, s.status,s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from V$TRANSACTION t, v$session S
   where t.SES_ADDR=s.saddr
   order by used_ublk desc;
   
3. TEMP usage, top consumers
   select t.tablespace, t.blocks,
   round((t.blocks * ts.block_size)/1024/1024) "Size_MiB", t.segtype,
   s.sid, s.serial#, s.username, s.status,s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from v$session s, v$tempseg_usage t, dba_tablespaces ts
   where t.tablespace=ts.tablespace_name and s.saddr = t.session_addr
   order by t.blocks desc;
   
4.  CPU usage
   
   • UNIX top command, it can be sorted by CPU or memory (SIZE or RES). Press “h” for help.
   • In OEM you can look at Performance page of host.
   • UNIX Solaris command prstat
     sorted by CPU usage:
     
     
     prstat
     processes sorted by CPU and totals per user
     prstat -a
     sorted by memory and totals per user. Careful! It is different from top command as it shows shared memory as well.
     prstat -a -s rss
     
     
     
   
   
5. Free memory
   UNIX top command and OEM host performance page can be used. They show the same result.
    
6.  Paging and Swapping
   In OEM:
   Go to target host, "Performance" tab. Click "Swap Utilization" number to see a graph or click "Paging Activity" to see more metrics.
   Unix commands:
   vmstat -S 3 12
   Where 3 is the number of seconds between statistics samplings and 12 is the number of samples to take. Watch "si" and "so", swap in/out columns, and scan rate "sr". If scan rate is non-zero for an extended period of time, you need more RAM.
   Note that first line of output includes all activity since the last vmstat report.

Video problem in Skype on Linux

Environment:
Ubuntu 9.10 Karmic Koala 64-bit, Skype 2.1.0.47 (deb package downloaded from skype.com)
Problem:
test video is just a green screen.
Solution:
You need to use correct v4l1compat.so library.
az@az1:~$ locate v4l1compat.so
/usr/lib/libv4l/v4l1compat.so
/usr/lib32/libv4l/v4l1compat.so

Second one works ok for 64-bit linux. Try from shell:
LD_PRELOAD=/usr/lib32/libv4l/v4l1compat.so skype &

To make it permanent go to (using Gnome)
System > Preferences > Main Menu
and change properties of Skype shortcut to

env LD_PRELOAD=/usr/lib32/libv4l/v4l1compat.so skype