How to use opatch to apply Oracle Critical Patch Update(CPU)

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to apply the latest CPU for Oracle Windows
Environment: Windows 2003 server SP2, Oracle 10.2.0.4 32bit


Steps:

1. backup database (rman, expdp/impdp and code backup if possible)
2. backup oracle home and c:\program files\oracle
3. stop listener, oracle service, zabbix agent and netbackup client service
4. sqlora32.dll cannot be used by other process, use process explorer from systeminternal to check.
5. list number of invalid objects and views
6. select * from registry$history for patching history
7. opatch version requirement, if higher version is required, download the latest opatch
8. opatch lsinventory to list inventory
9. opatch apply to apply patch
10. compile all views again , compare the number of invalid views and objects.

How to use sqldeveloper with OS authentication

Jephe Wu  - http://linuxtechres.blogspot.com

Objective: use OS authentication with sql developer on Windows server itself
Environment: Windows 2003 server SP2 32bit, Oracle 10g R2 standard edition 32bit


Steps:

1. download sqldeveloper 2.1.1.64(build MAIN-64.45) for 32bit Windows with JDK included
2. configure sqldeveloper to use OCI/Thick driver
tools-preferences-database-advanced- tick 'use OCI/Thick driver'
3. download Oracle instance client 11.1.0.7.1 for 32bit Windows
extract ocijdbc11.dll from the zip file, copy into Oracle home directory (e.g. d:\oracle\10.2.0\db-1\bin\)
There should be a ocijdbc10.dll there too.
4. configure connection as follows:

connection name: server_name_sys
role: sysdba
connection type: basic
hostname: localhost
port: 1521
sid: get it from sqlplus 'select instance_name from v$instance'
tick 'OS authentication'

How to solve common DG broker issues?

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to make dgmgrl to work, show configuration should display 'SUCCESS'.
Environment:  RHEL4.8 64bit, Oracle 10.2.0.3 64bit (RAC+Dataguard, RAC as primary, standalone DB as standby).

Error messages:
When running dgmgrl, connect /, show configuration, Get the following errors:
1.   In the alert log: /u01/app/oracle/admin/LIVEDB/bdump/drcLIVEDB1.log

DG 2011-07-22-16:55:32        0 2 756506724 DMON: Automatic health check timed out
DG 2011-07-22-16:55:32        0 2 756506724 Operation CTL_GET_STATUS continuing with warning, status = ORA-16619
DG 2011-07-22-16:55:32        0 2 0 INSV: cancelling message publish for opcode CTL_GET_STATUS
DG 2011-07-22-16:55:32        0 2 0       request ID = 1.2.756506724

2  When run 'show configuration', got 'Warning: DMON: health check timeout'

3. in the dataguard log file on one of RAC DB server, got error:
DG 2011-07-22-15:35:07        0 2 0 DMON: found read, but unconsumed message
DG 2011-07-22-15:35:07        0 2 0 DMON: req_id = 1.2.756506586



Solution:
1. On one of the RAC DB, run command 'ALTER SYSTEM SET DG_BROKER_START=FALSE' to shutdown dg broker for RAC, then run 'ALTER SYSTEM SET DG_BROKER_START=TRUE' to start it again.


Other useful commands:
select * from v$archive_gap;
show parameter dg_broker_start;  # should be true on dataguard
http://www.ritzyblogs.com/OraTalk/PostID/105/How-to-setup-DGMGRL-broker-with-example 
SQL> set line 32767;
SQL> select * from v$logfile;

Startup standby db and redo apply:
sql>STARTUP MOUNT;
sql> alter database recover managed standby database cancel; 
sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
or
sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT FROM SESSION;
note: cancel first like above, otherwise, you will get error such as "an incompatible media recovery is active".

How to install zabbix agent on Windows and backup zabbix database configuration

Jephe Wu - http://linuxtechres.blogspot.com


steps:

1. refer to doc at http://www.zabbix.com/documentation/1.8/manual/processes/zabbix_agentd_win

2. copy 32bit or 64bit unzipped files to c:\Program Files\Zabbix Agent\ (create this directory first)

3. copy configuration file from zabbix source to above directory as zabbix_agentd.conf

An example configuration file is available in Zabbix source archive as misc/conf/zabbix_agentd.win.conf.

EnableRemoteCommands=0
Server=172.16.1.1,172.16.1.2
Hostname=JEPHE
ListenPort=10050
ServerPort=10051
StartAgents=5

4. install it
go to cmd,
cd "Program Files\Zabbix Agent"
zabbix_agentd.exe -c "c:\Program Files\Zabbix Agent\zabbix_agentd.conf" -i

5. go to services.msc, start zabbix agent.

6. test
from agent host, telnet zabbixserver 10051
from server host, telnet zabbixagent 10050

7. add agent host into zabbix monitored server list

========
backup zabbix configuration shell script:

root@db01:~/bin/ # more zabbix_config_backup.sh
#!/bin/sh
DAY=`date +%w`
HOSTNAME=`hostname`
TABLES="acknowledges actions applications autoreg_host conditions config dchecks dhosts drules dservices escalations expressions functions globalmacro graph_theme graphs graphs_items groups help_items hostmacro hosts hosts_groups hosts_profiles hosts_prof
les_ext hosts_templates housekeeper httpstep httpstepitem httptest httptestitem ids images items items_applications maintenances maintenances_groups maintenances_hosts maintenances_windows mappings media media_type nodes opconditions operations opmediatypes profiles proxy_autoreg_host regexps rights screens screens_items scripts services services_links sessions slides slideshows sysmaps sysmaps_elements sysmaps_link_triggers sysmaps_links timeperiods trigger_depends triggers users users_backup users_groups usrgrp valuemaps"
COMMAND="mysqldump -uzabbix -pzabbix zabbix --add-drop-table --add-locks --extended-insert --single-transaction --quick"
DIR="/mysql/config_backup"
DATATABLES="alerts auditlog events history history_log history_str history_str_sync history_sync history_text history_uint history_uint_sync node_cksum proxy_dhistory proxy_history service_alarms services_times trends trends_uint"

# program starts here
$COMMAND --tables $TABLES | gzip > $DIR/zabbix_config_db.$DAY.gz

(echo "From: `hostname`<root@`hostname`>";echo "Subject: Finished daily Zabbix config backup"; echo "To: jwu@domain.com"; echo "";echo "According to Zabbix
.8 network monitoring book page 377, the following tables are data tables, all others are configuration tables"; echo "";echo "$DATATABLES";echo "";echo "running command $COMMAND --tables $TABLES | gzip > $DIR/zabbix_config_db.$DAY.gz"; echo ""; echo "`ls
-lat /srv/mysql/config_backup`") | /usr/sbin/sendmail jwu@domain.com