Wednesday, 26 March 2014

Installing and configuring Swingbench 2.5 on RedHat Linux


There are a few things worth mentioning here...

I´m skipping the download and unzip part. That should be straight forward.

1. Make sure the user ulimits are ok. Every user in Swingbench is an os process. Make sure that ulimits are fine:

$ ulimit -u

Check for max user processes. The default in linux is 1024. This means that you will not be able to run more than 1024 swingbench database users.

If you need to adjust user limits, check this article. It depends a little on your OS-release, but I had to adjust /etc/security/limits.conf and /etc/security/limits.d/90-nproc.conf.

2. Create the tablespace you want to use and the user before running the oewizard!!

Oewizard is the tool for generating your database schema with your sample database that is used by Swingbench.

I have not been able to get oewizard to generate a successfull schema without first creating the user and granting it the right privileges. Oewizard should be able to do this correctly since it ask for the sys user and password, but it does not create the user with the right privileges.

I have not found out exactly what it needs but these works for me:

create user SOE identified by SOE;
grant DBA, RESOURCE to SOE;
grant execute on DBMS_LOCK to SOE;

3. Run oewizard

Make sure that java is in your path. When running oewizard (2.5) you need java 1.6. I found that it did not work with java 1.7.

./oewizard

Be really carefull with the tablespace options. By default it will create a datafile under $ORACLE_HOME. If you have a RAC-database, it gets corrupted. Make sure you select a pre created tablespace or change the parameter to point to ASM.

Make sure the create database script runs successfully.

4. Check parameters in your database

Make sure you have a large SGA.
Check your processes settings.
Create a service for Swingbench, example tpcc.
++

5. Run swingbench

./swingbench

Connection string might need port, I use jdbc thin with ip:port/service_name.

Having trouble with connecting? Check that you are able to reach your database server on the listener port:

telenet dbserver port, example:

telnet myserver.com 1521







Tuesday, 24 January 2012

Convert Standalone DB to RAC One Node

Convert Standalone DB to RACONENODE
================================

### Overview

# The convertion steps will be from Single DB -> RAC and from RAC -> RAC One.

# The database environment consists of three nodes configured as follows:
# Node1 - RAC One Node 1
# Node2 - RAC One Node 2
# Node3 - Standby Server configured with Data Guard Broker

## Description

# Convertion from RAC -> RAC One node can easilly be done with srvctl convert, but you need to manually stop and remove all rac instances exept one, since RAC One Node only permits one instance running. Before this can be done you need to convert your stand alone DB to RAC. An easy way to do this is with rconfig. Your database will stop and start several times and since RAC adds a prefix number on your instance, the instance name will change in the process.

# We will also add a service for use by the applications in this process. This service will only run when the primary databases is running on the RAC One Nodes. When the primary is on the standby server (node3), these srvices will be down and the connections from the applications will sucessfully failover.

# Summary of the steps involved

1. Switchover from node3 to node1
2. Convertion from Standalone DB -> RAC (rconfig)
3. Convertion from RAC -> RAC One (srvctl)
4. Relocate the databases between node1 and node2 (this will ensure that you get the right instance names)
5. Test of data guard switchover (recommended)

### An Example

## 1. Switchover from node3 to node1

# Switchover from node3 to node1
# - a) First take a switch logfile (check alertlog for applied)
# - b) Switchover with broker (dgmgrl>switchover to )

## 2. Convertion from Standalone DB -> RAC (rconfig)
# Prepare for rconfig
bash-3.2$ cd $ORACLE_HOME/bin
bash-3.2$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/bin
bash-3.2$ env |grep ORA
ORACLE_SID=bm2test
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1

# Important!
# Edit xml file, make sure dbfiles and flashback files is blank! We will not convert any files!

./rconfig /u01/app/oracle/product/11.2.0.3/db_1/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged_bm2test.xml

# Expected Output:
Converting Database "bm2test" to Cluster Database. Target Oracle Home: /u01/app/oracle/product/11.2.0.3/db_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Adding Trace files
Setting Fast Recovery Area
Updating Oratab
Creating Password file(s)
Configuring Listeners
Configuring related CRS resources
Starting Cluster Database

# Check of data guard on node3
# Expected output:
DGMGRL> show configuration

Configuration - dataguard_bm2test

Protection Mode: MaxPerformance
Databases:
bm2test - Primary database
DGbm2test - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

# Add a service for applications
bash-3.2$ srvctl add service -d bm2test -s bm2test_srv -r bm2test1 -a bm2test2
bash-3.2$ srvctl start service -d bm2test -s bm2test_srv

## 3. Convertion from RAC -> RAC One (srvctl)
# Stop instance2 to be able to remove it
bash-3.2$ srvctl stop instance -d bm2test -i bm2test2
# Remove instance from cluster
bash-3.2$ srvctl remove instance -d bm2test -i bm2test2
Remove instance from the database bm2test? (y/[n]) y
# Convert DB
srvctl convert database -d bm2test -c RACONENODE -i bm2test

## 4. Relocate the databases between node1 and node2 (this will ensure that you get the right instance names)

# Get the correct instance names by relocating to node2 and back to node1
srvctl relocate database -d bm2test -n dap-bm2-ora02
# Check the instance on node2, notice the instance name has changed!
oracle@dap-bm2-ora02 /home/oracle > bash
bash-3.2$ ps -ef |grep pmon
oracle 7536688 1 0 18 jan - 0:58 asm_pmon_+ASM2
oracle 11599938 1 0 13:22:22 - 0:00 ora_pmon_bm2test_2
# Relocate back to node1
srvctl relocate database -d bm2test -n dap-bm2-ora01
# Check the instance on node1, notice the instance name has changed!
bash-3.2$ ps -ef |grep pmon
oracle 8716330 1 0 18 jan - 1:03 asm_pmon_+ASM1
oracle 21954792 1 0 13:24:43 - 0:00 ora_pmon_bm2test_1

## Check the data guard
# Check the listener setup on node1, check that the new instance is registered under dbname, dbname_DGMGRL and dbname_DGB
. .profile_grid
lsnrctl status
# Expected output
...
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "bm2test" has 2 instance(s).
Instance "bm2test", status UNKNOWN, has 1 handler(s) for this service...
Instance "bm2test_1", status READY, has 1 handler(s) for this service...
Service "bm2testXDB" has 1 instance(s).
Instance "bm2test_1", status READY, has 1 handler(s) for this service...
Service "bm2test_DGB" has 2 instance(s).
Instance "bm2test", status UNKNOWN, has 1 handler(s) for this service...
Instance "bm2test_1", status READY, has 1 handler(s) for this service...
Service "bm2test_DGMGRL" has 1 instance(s).
Instance "bm2test", status UNKNOWN, has 1 handler(s) for this service...
Service "bm2test_srv" has 1 instance(s).
Instance "bm2test_1", status READY, has 1 handler(s) for this service...

# Check data guard config on node1
DGMGRL> show configuration

Configuration - dataguard_bm2test

Protection Mode: MaxPerformance
Databases:
bm2test - Primary database
DGbm2test - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

# Check for transport lag, should be 0
show database verbose 'DGbm2test'
# Expected output
Database - DGbm2test

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
DGbm2test

# Check data guard configuration on node 3 (this was done after a switchover test)
# Notice that all 4 instances is registered.
DGMGRL> show database verbose bm2test

Database - bm2test

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
bm2test1
bm2test2
bm2test_1 (apply instance)
bm2test_2

# Consider recreating the broker configuration!
# Check the static connection identifier (port 1521 should be changed)

## 5. Test of data guard switchover (recommended)

# Recommended post steps to check that everything is 100% working and that you are protected!
# Performe switchover to node3
# Performe switchover to node1


!NB remember to have the right instance in the LISTENER.ORA for Data Guard... (not a problem?)

Thursday, 15 July 2010

Hugepages (Linux x64)

If your running Oracle on Linux, there is a lot of nice tweeks that can boost your perfomance. My favorite for years has been Hugepages. I have used it since I startet working on 64-bits Linux, mostly RedHat and Suse Linux. Hugepages will not only boost the memory performance but can also solve troublesome swapping issues I have seen with using total SGA's above 10GB.

How to do it?

It's easy!

This is an example for 16GB Hugepages setup (it all depends on your servers total RAM and the relationship between users space and oracle sga space). Just send me an email if you need help in getting the right settings for your environment.

$cat /etc/sysctl.conf
# Hugepages for Oracle SGA, usually RAM/2
vm.nr_hugepages = 8000

$cat /etc/security/limits.conf
…
oracle soft memlock 16384000
oracle hard memlock 16384000






Memlock is in KB and Hugepages is the number of pages * hugepage size (8000 * 2048K = 16G)

Thursday, 3 December 2009

Oracle Grid Infrastructure Install with Iptables...


The new 11gR2 install is full of pre-checks, comes with a fix-it script utility and is much easier to use both a reinstall and for new installs. But, its still not completele problem free out in the real world ;)

The popularity of linux is not questionable, but there is seldom an installation that is just a default setup.

Iptables (OS firewall) is a common feature of linux which I find very usefull. This is of course something you need to take in account for all software installs. But, usually you get away by opening a port or two.

This is not the case with 11gR2 Grid Infrastructure install for RAC. It uses random ports to copy the software between nodes! At first I thougt it was enough to enable all traffic for the private nic, but unfortunately thats not true. It also communicates on the public nic!! From a security point of view, this is quite astonishing.

The only way I got it working was by stopping iptables during the installation. If anyone have a better idea I would really like to here about it!
Stop iptables on both nodes:
orarac06# service iptables stop
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
Now you can try your Oracle Grid Infrastructure Install again... (hopefully you saved a responsfile...)

Tuesday, 1 December 2009

Debug Tip for Oracle Data Guard

How to debug Oracle Data Guard

When you have problems with Oracle Data Guard, you have two scripts created by Oracle to check if everything is set up correctly. They are available on metalink. You just copy the scripts to your server and it will generate a logfile with information on both what to look for and what your current settings is. I think its a fast way to do a generic health check of Data Guard.

The scripts:

Script to Collect Data Guard Primary Site Diagnostic Information
[ID 241374.1]


Script to Collect Data Guard Physical Standby Diagnostic Information
[ID 241438.1]

This might be usefull to ensure you have a healthy setup, to resolve gaps or archiver hung problems.

Thursday, 10 September 2009

Block Change Tracking

We have a problem with backup taking too much time to finish. Now I'm gonna try if Oracle's 10G new feature, Block Change Tracing, will help me speed up the incremental backup level 1.

I just did this in my database:

alter database enable block change tracking using file '/u02/oradata/mydb/bctf01.log';

What it does, is that rman will look into that file for changed blocks instead of cheching all the blocks in the database for updated blocks.

Hopefully, thats enough :).

Tuesday, 7 July 2009

Renaming Datafiles in Oracle

Sometimes I need to move datafiles physically to a new disk device or another filsystem. As I'm sure you are aware of, the controlfile needs to be updated. I have a small script that is quite handy when moving several files, or all the files in the database. This I got from a very handsome college of mine ;). Thx Magnus!

Script:

select 'alter database rename file '''||file_name||''' TO '''|| REPLACE(file_name,'C:\ORADATA\MYDB','E:\ORADATA\MYDB')||''';' from dba_data_files
union
select 'alter database rename file '''||member||''' TO '''||
REPLACE(member,'C:\ORADATA\MYDB','E:\ORADATA\MYDB')||''';' from v$logfile;



The output generated is all you need to move all your files from the C drive to the E drive:

alter database rename file 'C:\ORADATA\MYDB\DATA2OHIST001.DBF' TO 'E:\ORADATA\MYDB\DATA2OHIST001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATA2OHIST002.DBF' TO 'E:\ORADATA\MYDB\DATA2OHIST002.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATA2OHIST003.DBF' TO 'E:\ORADATA\MYDB\DATA2OHIST003.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATADEF001.DBF' TO 'E:\ORADATA\MYDB\DATADEF001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATAOHIST001.DBF' TO 'E:\ORADATA\MYDB\DATAOHIST001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATAOHIST002.DBF' TO 'E:\ORADATA\MYDB\DATAOHIST002.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATAOHIST003.DBF' TO 'E:\ORADATA\MYDB\DATAOHIST003.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATASMALL001.DBF' TO 'E:\ORADATA\MYDB\DATASMALL001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATASTD001.DBF' TO 'E:\ORADATA\MYDB\DATASTD001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATASTD002.DBF' TO 'E:\ORADATA\MYDB\DATASTD002.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATATRANS001.DBF' TO 'E:\ORADATA\MYDB\DATATRANS001.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATATRANS002.DBF' TO 'E:\ORADATA\MYDB\DATATRANS002.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATATRANS003.DBF' TO 'E:\ORADATA\MYDB\DATATRANS003.DBF';

alter database rename file 'C:\ORADATA\MYDB\DATATRANS004.DBF' TO 'E:\ORADATA\MYDB\DATATRANS004.DBF';
...

Monday, 6 July 2009

JDBC connection for RAC Databases

An example of a JDBC URL, unsing JDBC thin driver

jdbc:oracle:thin:@
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2-vip)(PORT = 1521))
(LOAD_BALANCE = on)
(FAILOVER = on)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db-service)
)
)

Load Balance will ensure that the clinet will do a round robin choose of ip address.
Failover will ensure that the other ip is tried if the first one failes.

SRVCTL Syntax Short (RAC tool)

A short list of syntax examples of a very nice little clustering tool called server control (srvctl):

The most important tip first. If your having a problem with the syntax,
try ending your command and arguments with '-h'...

srvctl add service -h

Usage: srvctl add service -d < name > -s < service_name > -r " < preferred_list > " [-a " < available_list > "] [-P < TAF_policy > ]
-d < name > Unique name for the database
-s < service > Service name
-r " < pref_list > " List of preferred instances
-a " < avail_list > " List of available instances
-P < TAF_policy > TAF policy (NONE, BASIC, or PRECONNECT)
Usage: srvctl add service -d < name > -s < service_name > -u {-r " < new_pref_inst > " | -a " < new_avail_inst > "}
-d < name > Unique name for the database
-s < service > Service name
-u Add a new instance to service configuration
-r < new_pref_inst > Name of new preferred instance
-a < new_avail_inst > Name of new available instance
-h Print usage



Status of node applications (basic cluster components)

srvctl status nodeapps -n

Srvctl status nodeapps –n raclinux1

VIP is running on node: raclinux1
GSD is running on node: raclinux1
Listener is running on node: raclinux1
ONS daemon is running on node: raclinux1


Other commands

srvctl add database -d < DB_NAME > -o < ORACLE_HOME >
srvctl add instance -d < DB_NAME > -i < INSTANCE_NAME > -n < NODE_NAME >
srvctl add asm -n < NODENAME > -i < INSTANCE_NAME > -o ORACLE_HOME
srvctl remove asm -n < NODENAME > -i < INSTANCE_NAME >

srvctl add service -d < DB_NAME > -s < SERVICE_NAME > -r "INST1,INST2,INST3"

srvctl modify service -d < DB_NAME > -i < INSTANCE > -s < SERVICE_NAME > -r (Setting preferred instance)

srvctl modify service -d < DB_NAME > -s < SERVICE_NAME > -n -i "INST1,INST2,INST3,INST4" (Addning instances to services)

srvctl config database -d < DB_NAME > -a (Check if your database is registered with OCR)

raclinux1$ srvctl status database -d gendb
Instance gendb1 is running on node raclinux1
Instance gendb2 is running on node raclinux2

The full helplist

orarac-01$ srvctl -h
Usage: srvctl [-V]
Usage: srvctl add database -d < name > -o < oracle_home > [-m < domain_name > ] [-p < spfile > ] [-A < name|ip > /netmask] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s < start_options > ] [-n < db_name > ] [-y {AUTOMATIC | MANUAL}]
Usage: srvctl add instance -d < name > -i < inst_name > -n < node_name >
Usage: srvctl add service -d < name > -s < service_name > -r " < preferred_list > " [-a " < available_list > "] [-P < TAF_policy > ]
Usage: srvctl add service -d < name > -s < service_name > -u {-r " < new_pref_inst > " | -a " < new_avail_inst > "}
Usage: srvctl add nodeapps -n < node_name > -o < oracle_home > -A < name|ip > /netmask[/if1[|if2|...]]
Usage: srvctl add asm -n < node_name > -i < asm_inst_name > -o < oracle_home > [-p < spfile > ]
Usage: srvctl config database
Usage: srvctl config database -d < name > [-a] [-t]
Usage: srvctl config service -d < name > [-s < service_name > ] [-a] [-S < level > ]
Usage: srvctl config nodeapps -n < node_name > [-a] [-g] [-o] [-s] [-l]
Usage: srvctl config asm -n < node_name >
Usage: srvctl config listener -n < node_name >
Usage: srvctl disable database -d < name >
Usage: srvctl disable instance -d < name > -i " < inst_name_list > "
Usage: srvctl disable service -d < name > -s " < service_name_list > " [-i < inst_name > ]
Usage: srvctl disable asm -n < node_name > [-i < inst_name > ]
Usage: srvctl enable database -d < name >
Usage: srvctl enable instance -d < name > -i " < inst_name_list > "
Usage: srvctl enable service -d < name > -s " < service_name_list > " [-i < inst_name > ]
Usage: srvctl enable asm -n < node_name > [-i < inst_name > ]
Usage: srvctl getenv database -d < name > [-t " < name_list > "]
Usage: srvctl getenv instance -d < name > -i < inst_name > [-t " < name_list > "]
Usage: srvctl getenv service -d < name > -s < service_name > [-t " < name_list > "]
Usage: srvctl getenv nodeapps -n < node_name > [-t " < name_list > "]
Usage: srvctl modify database -d < name > [-n < db_name] [-o < ohome > ] [-m < domain > ] [-p < spfile > ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s < start_options > ] [-y {AUTOMATIC | MANUAL}]
Usage: srvctl modify instance -d < name > -i < inst_name > -n < node_name >
Usage: srvctl modify instance -d < name > -i < inst_name > {-s < asm_inst_name > | -r}
Usage: srvctl modify service -d < name > -s < service_name > -i < old_inst_name > -t < new_inst_name > [-f]
Usage: srvctl modify service -d < name > -s < service_name > -i < avail_inst_name > -r [-f]
Usage: srvctl modify service -d < name > -s < service_name > -n -i < prefered_inst > [-a < available_list > ] [-f]
Usage: srvctl modify asm -n < node_name > -i < asm_inst_name > [-o < oracle_home > ] [-p < spfile > ]
Usage: srvctl relocate service -d < name > -s < service_name > -i < old_inst_name > -t < new_inst_name > [-f]
Usage: srvctl remove database -d < name > [-f]
Usage: srvctl remove instance -d < name > -i < inst_name > [-f]
Usage: srvctl remove service -d < name > -s < service_name > [-i < inst_name > ] [-f]
Usage: srvctl remove nodeapps -n " < node_name_list > " [-f]
Usage: srvctl remove asm -n < node_name > [-i < asm_inst_name > ] [-f]
Usage: srvctl setenv database -d < name > {-t < name > = < val > [, < name > = < val > ,...] | -T < name > = < val > }
Usage: srvctl setenv instance -d < name > [-i < inst_name > ] {-t " < name > = < val > [, < name > = < val > ,...]" | -T " < name > = < val > "}
Usage: srvctl setenv service -d < name > [-s < service_name > ] {-t " < name > = < val > [, < name > = < val > ,...]" | -T " < name > = < val > "}
Usage: srvctl setenv nodeapps -n < node_name > {-t " < name > = < val > [, < name > = < val > ,...]" | -T " < name > = < val > "}
Usage: srvctl start database -d < name > [-o < start_options > ]
Usage: srvctl start instance -d < name > -i " < inst_name_list > " [-o < start_options > ]
Usage: srvctl start service -d < name > [-s " < service_name_list > " [-i < inst_name > ]] [-o < start_options > ]
Usage: srvctl start nodeapps -n < node_name >
Usage: srvctl start asm -n < node_name > [-i < asm_inst_name > ] [-o < start_options > ]
Usage: srvctl start listener -n < node_name > [-l < lsnr_name_list > ]
Usage: srvctl status database -d < name > [-f] [-v] [-S < level > ]
Usage: srvctl status instance -d < name > -i " < inst_name_list > " [-f] [-v] [-S < level > ]
Usage: srvctl status service -d < name > [-s " < service_name_list > "] [-f] [-v] [-S < level > ]
Usage: srvctl status nodeapps -n < node_name >
Usage: srvctl status asm -n < node_name >
Usage: srvctl stop database -d < name > [-o < stop_options > ]
Usage: srvctl stop instance -d < name > -i " < inst_name_list > " [-o < stop_options > ]
Usage: srvctl stop service -d < name > [-s " < service_name_list > " [-i < inst_name > ]] [-f]
Usage: srvctl stop nodeapps -n < node_name > [-r]
Usage: srvctl stop asm -n < node_name > [-i < asm_inst_name > ] [-o < stop_options > ]
Usage: srvctl stop listener -n < node_name > [-l < lsnr_name_list > ]
Usage: srvctl unsetenv database -d < name > -t " < name_list > "
Usage: srvctl unsetenv instance -d < name > [-i < inst_name > ] -t " < name_list > "
Usage: srvctl unsetenv service -d < name > [-s < service_name > ] -t " < name_list > "
Usage: srvctl unsetenv nodeapps -n < node_name > -t " < name_list > "

Changing Grid Control Management Console to HTTPS/Port 443



How to change the management port of Oracle Enterprise Manager Central Console to 443

Documentation Reference

http://docs.huihoo.com/oracle/docs/B16240_01/doc/em.102/b16242/postinstall_config.htm#i1025742


http://download-west.oracle.com/docs/cd/B14099_19/core.1012/b13995/ports.htm#BABHCHGA




Ports in use after a default installation

To check which ports are in use by Enterprise Manager check:
$ORACLE_HOME/install/portlist.ini

It will probably look like this:

[Ports]
Oracle HTTP Server port = 7777
Oracle HTTP Server Listen port = 7778
Oracle HTTP Server SSL port = 8250
Oracle HTTP Server Listen (SSL) port = 4444
Java Object Cache port = 7000
Oracle Notification Server Request port = 6003
Oracle Notification Server Local port = 6101
Oracle Notification Server Remote port = 6201
ASG port = 7890
Application Server Control RMI port = 1850
Oracle HTTP Server Diagnostic port = 7200
Log Loader port = 44000
DCM Discovery port = 7100
Oracle Management Agent Port = 1157
Application Server Control port = 1156
Web Cache HTTP Listen port = 7777
Web Cache HTTP Listen (SSL) port = 8250
Web Cache Administration port = 9400
Web Cache Invalidation port = 9401
Web Cache Statistics port = 9402
Enterprise Manager Central Console Port = 4889
Enterprise Manager Central Console Secure Port = 1159




Start by setting your environment right...

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/oms10g
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/oms10g/lib


Changeing from 1159 to 443...

In a unix environment there is only root that are able to run services on ports below portnumber 1024. So we need to grant root some access rights on apache to be abel to use 443.


oracle01# cd /u01/app/oracle/product/10.2.0/oms10g/Apache/Apache/bin
oracle01# chown root .apachectl
oracle01# chmod 6750 .apachectl
oracle01# ls -la |grep apache
-rwx------ 1 oracle oinstall 12743 Jun 25 12:14 apachectl
-rwsr-s--- 1 root oinstall 1716966 Aug 5 2005 .apachectl
-rwx------ 1 oracle oinstall 12218 Jun 23 2005 apachectl.tmp

Check the port currently in use

oracle01# cat /u01/app/oracle/product/10.2.0/oms10g/install/portlist.ini |grep "Secure Port"
Enterprise Manager Central Console Secure Port = 1159

oracle01$ netstat -lntp|grep 1159
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:1159 0.0.0.0:* LISTEN 6479/httpd



Stop oms (Oracle Management Service)

oracle01$ emctl stop oms
Oracle Enterprise Manager 10g Release 3 Grid Control
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Oracle Management Server is Down

Stop Oracle Application Server Web Services

oracle01$ $ORACLE_HOME/opmn/bin/opmnctl stopall
opmnctl: stopping opmn and all managed processes...

Use your favorite text editor and replace all instances of 1159 with 443 in ”$ORACLE_HOME/sysman/config/httpd_em.conf”.


Update your config

oracle01$ /u01/app/oracle/product/10.2.0/oms10g/dcm/bin/dcmctl updateconfig -ct ohs


Use your favorite text editor and replace all instances of 1159 with 443 in ”$ORACLE_HOME/sysman/config/emoms.properties”


Change the secure port

oracle01$ type emctl
emctl is hashed (/u01/app/oracle/product/10.2.0/oms10g/bin/emctl)
oracle01$ emctl secure oms -secure_port 443
Oracle Enterprise Manager 10g Release 3 Grid Control
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root Password :
Enter Agent Registration password :
OPMN processes successfully stopped... Done.
Securing central oms... Started.
Checking Repository... Done.
Checking Em Key... Done.
Checking Repository for an existing Enterprise Manager Root Key... Done.
Fetching Root Certificate from the Repository... Done.
Generating Registration Password Verifier in the Repository... Done.
Generating Oracle Wallet Password for Enterprise Manager OMS... Done.
Generating Oracle Wallet for Enterprise Manager OMS... Done.
Generating Oracle Wallet for iAS HTTP Server... Done.
Updating HTTPS port in emoms.properties file... Done.
Generating HTTPS Virtual Host for Enterprise Manager OMS... Done.
Securing central oms... Ended.
OPMN processes successfully restarted... Done.

(Enterprise Manager Root Password er det same som passordet som Agent Registration Password.)

Update your config

oracle01$ /u01/app/oracle/product/10.2.0/oms10g/dcm/bin/dcmctl updateconfig -ct ohs

Start oms

oracle01$ emctl start oms
Oracle Enterprise Manager 10g Release 3 Grid Control
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
opmnctl: opmn is already running
Starting HTTP Server ...
Oracle Management Server Already Started.
oracle01$ emctl status oms
Oracle Enterprise Manager 10g Release 3 Grid Control
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Oracle Management Server is Up.

Check that the new port 443 is in use

oracle01$ netstat -lntp |grep 443
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN -

Check your Management Agent

oracle01$ /u01/app/oracle/product/10.2.0/agent10g/bin/emctl status agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.5.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.3.0
Agent Home : /u01/app/oracle/product/10.2.0/agent10g
Agent binaries : /u01/app/oracle/product/10.2.0/agent10g
Agent Process ID : 2184
Parent Process ID : 2158
Agent URL : https://oracle01.myserv.com:3872/emd/main/
Repository URL : https://oracle01.myserv.com:1159/em/upload
Started at : 2009-06-25 14:51:08
Started by user : oracle
Last Reload : 2009-06-25 15:16:16
Last successful upload : 2009-06-30 13:23:43
Total Megabytes of XML files uploaded so far : 214.06
Number of XML files pending upload : 22
Size of XML files pending upload(MB) : 0.32
Available disk space on upload filesystem : 90.33%
Last attempted heartbeat to OMS : 2009-06-30 13:35:34
Last successful heartbeat to OMS : 2009-06-30 13:24:04
---------------------------------------------------------------
Agent is Running and Ready

This is still using the old port 1159. So we need to change it...


Stop the agent

oracle01$ /u01/app/oracle/product/10.2.0/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

Use your favorite text editor and replace all instances of 1159 with 443 in ” $ORACLE_HOME/sysman/config/emd.properties”

$ORACLE_HOME is your agent_home (usually something like:
/u01/app/oracle/product/10.2.0/agent10g)

Delete old logs and upload files

oracle01$ rm /u01/app/oracle/product/10.2.0/agent10g/sysman/emd/upload/*
oracle01$ rm /u01/app/oracle/product/10.2.0/agent10g/sysman/emd/state/*

rm: cannot remove `/u01/app/oracle/product/10.2.0/agent10g/sysman/emd/state/storage': Is a directory

Start agent

oracle01$ /u01/app/oracle/product/10.2.0/agent10g/bin/emctl start agent Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Starting agent ..... started.
oracle01$ /u01/app/oracle/product/10.2.0/agent10g/bin/emctl status agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.5.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.3.0
Agent Home : /u01/app/oracle/product/10.2.0/agent10g
Agent binaries : /u01/app/oracle/product/10.2.0/agent10g
Agent Process ID : 8864
Parent Process ID : 8838
Agent URL : https://oracle01.myserv.com:3872/emd/main/
Repository URL : https://oracle01.myserv.com:443/em/upload
Started at : 2009-06-30 13:38:14
Started by user : oracle
Last Reload : 2009-06-30 13:38:14
Last successful upload : 2009-06-30 13:38:24
Total Megabytes of XML files uploaded so far : 6.00
Number of XML files pending upload : 7
Size of XML files pending upload(MB) : 5.24
Available disk space on upload filesystem : 90.33%
Last successful heartbeat to OMS : 2009-06-30 13:38:17
---------------------------------------------------------------
Agent is Running and Ready


Test communication between agent and server by doing a manual upload of the agent

oracle01$ /u01/app/oracle/product/10.2.0/agent10g/bin/emctl upload agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Problem setting up Recovery Catalog in Oracle Enterprise Manager Grid Control

One of my customers discovered a problem on a freshly installed 10.2.0.3 database and 10.2.0.3 Grid Control. After some metalink searching, I found that it was a bug. Seems to be related to both 10.2.0.2 and 10.2.0.3. The error message is as follows:

"RMAN-06035: wrong version of recover.bsq, expecting 10.2.0.0, found 10.2.0.3"

There is an easy workaround:

Edit the recovery.bsq file in the AGENT_HOME (ORACLE_HOME for the OEM Agent). Modify the line:

library '10.2.0.3'

with

library '10.2.0.0'

References Bug ID: 5648668

Oracle Enterprise Manager Agent Installation Issues

Product: Oracle Enterprise Manager Agent
Version: 10.1.*
10.2.*
(Last version checked is 10.2.0.3)

Agent installation can be quite tricky. I have had a lot of problems installing the mass deployment agent, and are now only using the GUI installation found in ..agent/runInstaller included in the zip-file. Still there are some issues you might run into. I have listet up some problems I've run into and my solution to the problem...



Problem

You are seeing this error message:

The agentTZRegion value in /u01/app/oracle/product/10.2.0/agent10g/sysman/config/emd.properties is not in agreement with what agent thinks it should be.Please verify your environment to make sure that TZ setting has not changed since the last start of the agent.
If you modified the timezone setting in the environment, please stop the agent and exectute 'emctl resetTZ agent' and also execute the script mgmt_target.set_agent_tzrgn(, ) to get the value propagated to repository. Consult the log files in: /u01/app/oracle/product/10.2.0/agent10g/sysman/log

Solution


Edit $AGENT_HOME/sysman/config/emd.properties and set timezone to GMT (bottom of the file)

agentTZRegion=GMT

Restart the agent (emctl start agent), do not use emctl resetTZ agent!!


Problem

You are having problems with host discovery in Enterprise Manager Grid Control.

You might see this in the logfiles:

“The config assistant was running into problems because of an PERL5LIB setting
@ in the system environment. Removing this setting and rerunning the install
@ fixes the issue. “

Solution

Check if the OS user has the PERL5LIB environment set. If it does, unset it!!


Problem

Installation was broken or did not complete, agent is not started and is not abel to upload.

Solution

Sometimes there is a problem with securing the agent or with the first upload to OMS. It
often helps to do a clearstate of the agent.

Clearstate

Stop the agenten and remove these files from $AGENT_HOME:

$ rm -r sysman/emd/state/*
$ rm -r sysman/emd/upload/*
$ rm sysman/emd/agntstmp.txt
$ rm sysman/emd/lastupld.xml
$ rm sysman/emd/protocol.ini

Start the agent.

Run:
emctl clearstate agent
emctl upload agent


Problem

The agent is not discovering all targets or discovers old targets that where deleted.

Solution

Discovery in grid control checks targets.xml file from the agent and all Sqlnet files like
listener.ora,tnsnames.ora. It also checks /etc/oratab. Check these files for errors or old
information.

Problem

You get this error messages when discovering a target:

"...java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource ORA-06512:
at "SYSMAN.EM_TARGET", line 1681 ORA-06512: at "SYSMAN.EM_TARGET", line 1926 ORA-06512: at
"SYSMAN.MGMT_TARGET",line 2834 ORA-06512: at line 1 - ORA-00060: deadlock detected while
waiting for resource ORA-06512:

Solution

The installation might have used an ip address instead of hostname.
You can solve this by setting this environment variable before installing:

ORACLE_HOSTNAME=hostname


Problem

Agent will not start after boot.


Solution

Check the existence of these files:
/etc/rc.d/rc3.d/S99gcstartup
/etc/rc.d/rc5.d/S99gcstartup

Check the file:
$AGENT_HOME/install/unix/scripts/agentstup

If the file has "user=null" change this to "user=oracle". (user for agent installation)

Problem

I cant install the agent, several reinstalls have done a mess with the repository

Solution

1.Remove agent and associated targets from within Grid Control.
2.Uninstall agent.
3.Log into the repository DB as sysman, query the MGMT_TARGETS_DELETE table
for the GUID of the removed agent. Use the GUID from the MGMT_TARGETS_DELETE table to identify
the corresponding targets and delete
them from the MGMT_EMD_PING, MGMT_EMD_PING_CHECK, and the MGMT_TARGETS_DELETE tables.
4. Reinstall the agent


Problem

AGENT do not autodicsover duplicated databases

Solution

1. Edit $ORACLE_HOME/sysman/emd/targets.xml
change the values for MachineName, OracleHome, Port,SID etc.,
Set the dbsnmp password in clean text and change ENCRYPTED="FALSE"
and bounce the agent.

or,

emctl config agent addtarget


2. Use GC itself

Targets > Databases > Add Button
Select appropriate host and Continue. Auto discovery will occur.


Problem

Problems with uploading agent

Solution

If the agent is successfully installed, up and running and you still get error messages
when running emctl upload agent, try to do a new secure of the agent:

emctl secure agent

I have experienced several times that some agents need the manual secure job to be abel to
work after install.



Some emctl examples

emctl config agent addtarget /home/oracle/labs/asm1_target_RDBB.xml
emctl secure agent welcome (welcome er passordet til oms)
emctl clearstate agent (after this you need to do emctl upload agent)
emctl upload agent
emctl secure status agent

Tuesday, 9 June 2009

Exporting and importing a schema without data with datapump

The old exp/imp had a great option called row=n. This made you import a schema with everything but the actual rows. I have to do this all the time and here is how you do the exact same thing with Datapump.

The magic word is content=metadata_only!

expdp system directory=data_pump_dir dumpfile=myschema.exp logfile=myschema_exp.log schemas=myshema content=metadata_only

impdp system directory=data_pump_dir dumpfile=mydump.exp schemas=myschema content=metadata_only

Monday, 8 June 2009

Estimating dump size with datapump

With Oracle Datapump you can easily estimate the size of an export. This is usefull if you want to make sure that you will not run out of disk space during the export. Or, you might just be interested in measuring the size of a database or a schema.

1. SQL> create directory ho_pump as '/home/oracle/ho/pump';
2. SQL> grant read, write on directory ho_pump to public;
3. $ expdp system directory=ho_pump schemas=myschema estimate_only=y

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 08 June, 2009 15:21:05

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
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=ho_pump schemas=myschema estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
...
Total estimation using BLOCKS method: 8.598 GB
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:22:42

Monday, 2 June 2008

TNS_ADMIN

TNS_ADMIN is often used as a user environment variable on database servers. If you are using this variable in a RAC environment, you need to set this in the cluser respository (OCR). Do this by using the following command:

servctl setenv database -d db_name -t TNS_ADMIN=tns_admin_path

Wednesday, 7 November 2007

Oracle 11g on Ubuntu 7.10 link!

http://www.dizwell.com/prod/node/1046

Very nice link with a script to prepare your ubuntu for Oracle 11 ;).

There is something missing if you are installing 64-bits Oracle on 64-bits Ubuntu. There are some problems with 32-bit libraries that are not there. I managed to get it working by copying the library missing from a 32-bit installation. I might write something on that later...

SQL Developer on Ubuntu (7.10 Gutsy Gibbon)

About one year ago I fell in love with Ubuntu, Linux for Human Beings. I installed it on my laptop and for the first time in my life, I did not have to configure the X86config file to get the right resolution. I didn't even have to do anything to get wireless working. I was amazed. After a couple of days I managed to get Cisco VPN working, my work email account, calender sync, network printing etc..

Now I have recently installed Ubuntu 7.10 and it is even better. Everything just works!

So, now I need to set up my favorite SQL tool, Oracle SQL Developer. It is completely free of charge and is downloadable from http://otn.oracle.com. I'm sure you will find it.

Installation is very easy. You just unzip the package to a directory where you want SQL Developer to be installed, and you run sqldeveloper.sh. But, make sure you have a proper JDK on your system!

To install Sun JDK 5 do this:

Make sure your /etc/apt/sources.list have these lines in it:

deb http://us.archive.ubuntu.com/ubuntu dapper main restricted
deb http://us.archive.ubuntu.com/ubuntu dapper universe multiverse

After this have apt updates its repository:

sudo apt-get update

Then install java jdk!

sudo apt-get install sun-java5-jdk

Tuesday, 24 July 2007

DGS

Dataguard Syntax Short


This little list has proven to be quite usefull for me. Especially since they made the commands so loooong. Its all there in the manual, but with a lot more stuff around it ;).

Be Aware! The lines break like the wave Maveric in this blog!

Physical standby database commands

Goal
Mount a standby database
Command
SQL> alter database mount standby database;

Goal
Start Redo Apply (Normal Recovery Process)
Command
SQL> alter database recover managed standby database disconnect;

Goal
Start Real Time Apply
Command
SQL> alter database recover managed standby database using current logfile disconnect;

Goal
Stop Apply (Redo or Real Time)
Command
SQL> alter database recover managed standby database cancel;

Goal
Run the recovery process in paralell (Number of CPU*2)
Command
alter database recover managed standby database using current logfile disconnect paralell 16;

Logical standby database commands

Goal

Start SQL Apply
Command
SQL> alter database start logical standby apply;

Goal
Start SQL Apply with Real Time Apply
Command
SQL> alter database start logical standby apply immediate;

Goal
Stop SQL Apply
Command
SQL> alter database stop logical standby apply;

Switchover

Goal
Check if you database is ready to switch over
Command
SQL> select switchover_status from v$database;
Expected result
SWITCHOVER_STATUS
-----------------
TO PRIMARY



Other commands

Goal
List information about archivelogs (I just had to put it in here, cause its cute)
Command
SQL> archive log list;

QHPDIAAAS

Qlogic HP driver in an Active/Active Setting


If you have an IO bound system you might want to try to use dual fibercards in an active/active setting. This is usually done by adding some SAN software like EMC powerpath or Securepath or Multipath. If you are using standard Qlogic cards you might be abel to set this up in an active/active configuration as well. This way we should be abel to push twice as much IO through each node. Please see this article by HP:

http://h71028.www7.hp.com/ERC/downloads/4AA0-8102ENW.pdf



1. Install HP Proliant Support Pack (PSP)

2. Modify your /etc/modprobe.conf:
…

options qla2xxx ql2xmaxqdepth=16 qlport_down_retry=30 ql2xloginretrycount=30 ql2xfailover=1 ql2xlbType=2 ql2xautorestore=0x20

…

3. Create a new initrd

/opt/hp/src/hp_qla2x00src/make_initrd

4. Edit you driver (/etc/hp_qla2x00.conf)

qdepth = 16
port_down_retry_count = 30
login_retry_count = 30
failover = 1
load_balancing = 2
auto_restore = 0x20

5. Boot the server/servers

The parameter ql2xlbType defines which method of load balancing you want to use:
0 (None) - Expose luns on the first active path and make them the preferred path or the first active optimize path and make them the preferred path (storages: MSA A/A and EVA A/A).

1 (Static load balancing) - Distribute and expose the LUNs across the active optimize port(s) or active un-optimize port(s) and HBA(s).

2 (Least outstanding I/O) - send command to the path with the lowest I/O count.
3 (Least Service time) - Send request to the path with the shortest execution time. Default: 0

- Endret /etc/hp_qla2x00.conf:
qdepth = 16
port_down_retry_count = 30
login_retry_count = 30
failover = 1
load_balancing = 2
auto_restore = 0x20

GTASM

Going to ASM...

Converting non-ASM databases to ASM is quite easy...if you know what to do ;).

Actually I had to convert a non-ASM standby database to a two instance RAC ASM-database as part of a catastropical test. Why doing it this way, is really a good question. I will just say I'm trying to convince my customer to use Dataguard as the product is intended. No luck yet ;).

Here we go...

The tool you really need to know is RMAN! You don't get anywhere without RMAN working with ASM. This is not entirely true, but that's another story.

So basically you have a backup set of your non-ASM database. The files are typically stored on "/u02/oradata/SID/..." and so on... You ned RMAN to restore these files to ASM instead of "/u02/oradata/SID/...". To do this I use something called "set newname". The syntax is:

set newname for datafile 1 to '+DATA'

The reference "+DATA" is your diskgroup in ASM.

So, at some point you need to know how many datafiles you have, and probably how many tempfiles you have. You will typically have to write a little script like this:

connect / as sysdba
whenever sqlerror exit sql.sqlcode
set space 0
set pages 0
set heading off
set echo off
set trimspool on
set verify off
set feedback off
spool rman_newname_restore.rcv
select 'configure DEVICE TYPE DISK parallelism 4;' from dual;
select 'run' from dual;
select '{' from dual;
select 'set newname for datafile ' file# ' to ''' name '.dbf'';' from
v$datafile;
select 'restore database;' from dual;
select '}' from dual;
spool off;

Then you use the rman_newname_restore.rcv for the restore script. Rman will then restore your files to the new location set by newname.

There might be better solutions than mine, so I'm looking forward to reading your replays.