Cloning Oracle Management Agent OEM 11g

Environment
AIX 5.3.0.0
Oracle Enterprise Manager (OEM) Grid Control 11.1

Agent Installation
Create oracle user, groups and prepare .profile in UNIX.
Install agent by any method, e.g. agentDownload script.

Download file agentDownload.aix from OMS to target host.
wget http://omshost:4889/agent_download/11.1.0.1.0/aix/agentDownload.aix

Optionally change file agentDownload.aix:
doDiscovery=FALSE
startAgent=FALSE

Install agent:
./agentDownload.aix -b /oracle/oem -r 4889
Port number 4889 is necessary otherwise it will try to use HTTPS and port 1159, which didn’t work in my case.

Run root.sh script

Patch oracle agent
1. install latest OPatch (6880880)
2. apply proper TZ patch (6672979 for Australia)
3. apply recommended patches (PSU and others)

If you are going to use agent on this host then configure agent
agentca –f
Otherwise we don’t need to configure or start agent.

Prepare master archive file
It can be ZIP or TAR.GZ (or something similar)

Advantage of ZIP: it can be used as source file by Grid Control Cloning Wizard (in GC click Deployments > Install Agent > Clone Agent)
Disadvantage of ZIP: unzip cannot read from pipe or standard input, so zip file is copied to target host and then unzipped by Wizard or manual method. It takes bit extra space (1GB) and time.
Advantage of tar.gz: it can be uncompressed directly from SSH or WGET, see examples below
Disadvantage of tar.gz: it cannot be used by Grid Control Cloning Wizard

Prepare exclude list
# CLONE is any temp location
export CLONE=/oracle/software/clones
cd $ORACLE_HOME
find . -name "*.log" >$CLONE/ExcludeList.txt
find . -name "*.err" >>$CLONE/ExcludeList.txt
vi $CLONE/ExcludeList.txt
Add the following to the exclude list. It is taken from OMS host $OH/sysman/prov/agentpush/resources/scripts/exclude.lst
./sysman/upload/*
./sysman/emd/state/*
./sysman/emd/*
./sysman/log/*
./sysman/j2ee/*
./ccr/hosts/*
./ccr/state/*
./sysman/emd/*/*
./sysman/emd/*/*/*
./sysman/emd/*/*/*/*
./ccr/hosts/*/*
./ccr/hosts/*/*/*
./ccr/hosts/*/*/*/*
./bin/nmo
./bin/nmhs
./bin/nmb

Create tar.gz archive
cd $ORACLE_HOME
tar -X $CLONE/ExcludeList.txt -cvf - . | gzip -c >$CLONE/agent11g_home_11.1.0.1.5.tar.gz

Create zip archive
Attention: when you create the ZIP file, create it from the parent directory of the Oracle home (that’s GC wizard requirement)
cd $ORACLE_HOME
cd ..
zip -ry $CLONE/agent11g_home_11.1.0.1.5.zip agent11g -x@$CLONE/ExcludeList.txt 

Copy archived home (zip or tar.gz) to OMS host, e.g.
$ORACLE_HOME/sysman/agent_download/11.1.0.1.0/clones
You can see the files in Web browser
http://omshost:4889/agent_download/11.1.0.1.0/clones/

Deploying agent from tar.gz
Create oracle user on target host
Prepare .profile

Using SSH (method 1):
cd $ORACLE_HOME
ssh oracle@omshost 'cat /oracle/middleware/oms11g/sysman/agent_download/11.1.0.1.0/clones/agent11g_home_11.1.0.1.5.tar.gz' | gunzip | tar xvf –

Using WGET (method 2):
cd $ORACLE_HOME
wget -q -O- http://omshost:4889/agent_download/11.1.0.1.0/clones/agent11g_home_11.1.0.1.5.tar.gz | gunzip | tar xvf -

Run Installer:
cd $ORACLE_HOME/oui/bin
./runInstaller -clone -forceClone ORACLE_HOME=/oracle/oem/agent11g ORACLE_HOME_NAME=OH_agent11g -noconfig -silent OMS_HOST=omshost OMS_PORT=4889

As root
/oracle/oem/agent11g/root.sh

Continue as oracle user
Configure the Management Agent by running the following script:
$ORACLE_HOME/bin/agentca –f

Deploying agent from zip manually
You cannot pipe into unzip. So you have to download zip file to target and then unzip.
wget http://omshost:4889/agent_download/11.1.0.1.0/clones/agent11g_home_11.1.0.1.5.zip
Then run Installer and etc. as in example above.

Deploying agent from zip by Grid Control Wizard
Attention: in this method Oracle Home Name is assigned automatically; it looks something like AGT3561027

In Grid Control click Deployments > Install Agent > Clone Agent
Well, the rest is trivial.

Editors handy commands

Insert TEXT at beginning of a line
Notepad++
Search mode – regular expression
Find:   
^(.)
Replace with:   
TEXT\1

Vi
:%s/^/TEXT/

Append TEXT to end of a line
Notepad++
Search mode – regular expression
Find:
(.)$
Replace with:   
\1TEXT

Vi
:%s/$/TEXT/

Delete blank lines
Notepad++
Menu: TextFX -> TextFX Edit -> Delete blank lines

vi
:g/^$/d
Blank line with spaces (there is a space after backslash):
:g/^\ *$/d

Find “db.world” and replace with “db, db.world”
Notepad++
Search mode – regular expression
Find:   
^(\w*).world
Replace with:   
\1, \1.world

Time Zone DST patch for Oracle grid control agent 11g on AIX

Patch 6672979 has to be applied. Product: CORE (not agent!).
This patch installs TZ DST version 7, which includes Australian TZ changes in 2008.
Grid control agent 11.1.0.1.0 has Core Files 11.1.0.7.0

To see Core files for agent run:
opatch lsinventory -detail | grep "Oracle Core Required Support Files"

To see the current time zone info:
head $ORACLE_HOME/oracore/zoneinfo/readme.txt

Proper $TZ for AIX in NSW is:
echo $TZ
EET-10EETDT,M10.1.0/02:00,M4.1.0/03:00

To validate TZ:
echo $ORACLE_HOME
export EMDROOT=$ORACLE_HOME
export EMSTATE=$ORACLE_HOME
emdctl validateTZ agent Australia/Sydney

With TZ DST versions 10 and 14, I had the following errors:
emdctl validateTZ agent Australia/Sydney
tzOffset for Australia/Sydney is 0(min), but agent is runnning with tzOffset 660(min)
trying again after waiting for 1 sec to account for daylight transition
tzOffset for Australia/Sydney is 0(min), but agent is runnning with tzOffset 660(min)

Check the TZ settings in the Operating System environment:
emctl config agent getTZ

Reset time zone:
emctl resetTZ agent
grep TZ $ORACLE_HOME/sysman/config/emd.properties

sqlplus SYSMAN@OMS_repository
exec mgmt_target.set_agent_tzrgn('agenthost.gov.au:3872','Australia/Sydney');
commit;


Start agent
emctl start agent

Useful documents:
DST: Is your Grid Control ready for the next Daylight Saving Time - second half 2010? [ID 739116.1]
Grid Agent Configuration: How to Troubleshoot Timezone Issues in Enterprise Manager Grid Agent Setup? [ID 330737.1]

Recommended patches for agent 11.1.0.1.0 on AIX on 2011-10-26
1. 6880880 - OPatch version: 11.1.0.8.5
2. 6672979 - CORE: Patch DST CHANGES FOR SOME AUSTRALIAN REGIONS IN 2008 - NEED DATABASE FIX (this is TZ DST version 7)
3. 9345921 - ENTERPRISE MANAGER AGENT 11.1.0.1.5 PSU
4. 12424139 - PATCH BUNDLE REQUEST FOR MW MANAGEMENT CUMULATIVE MLR-APRIL 2011 - AGENT

Oracle Goldengate SQLEXEC output parameters

Environment
AIX 5.3.0.0
Oracle database 10.2.0.5
Goldengate 11.1.1.1

Problem
Replicat failed and the report file contains:
ERROR OGG-00353 Could not find stored procedure param (parameter does not exist in sp (seqnumout))

Solution
When using SQLEXEC with SELECT statement, make sure that alias in SELECT statement is the same as output parameter in GETVAL. It is seqnumout in the example below.

MAP HOU.HOUSEHOLD_PERS1, TARGET STG.HOUSEHOLD_PERS1,
SQLEXEC (ID seqnum, QUERY "select STG.HOUSEHOLD_PERS1_SEQ.nextval seqnumout from dual", NOPARAMS),
COLMAP (USEDEFAULTS,
GG_OPERATION = @GETENV ("GGHEADER","OPTYPE"),
GG_BEFORE_AFTER = @GETENV ("GGHEADER","BEFOREAFTERINDICATOR"),
GG_COMMITTIME = @GETENV ("GGHEADER","COMMITTIMESTAMP"),
SEQ# = @GETVAL(seqnum.seqnumout),
TKN_SCN = @TOKEN ("TKN_SCN"),
TKN_RSN = @TOKEN ("TKN_RSN")
);

Oracle Goldengate installation on AIX

Environment
AIX 5.3.0.0 (command oslevel)
Oracle database 10.2.0.5
GoldenGate 11.1.1.1

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
AIX 5L, ppc, 64bit (optimized), Oracle 10.2 on Apr 22 2011 02:17:48

Problem
After GoldenGate installation, ggsci cannot start.

ggsci
exec(): 0509-036 Cannot load program ggsci because of the following errors:
        0509-150   Dependent module libclntsh.a(shr.o) could not be loaded.
        0509-022 Cannot load module libclntsh.a(shr.o).
        0509-026 System error: A file or directory in the path name does not exist.
 
pwd
/oracle/goldengate/11.1.1
ldd ggsci
ggsci needs:
         /usr/lib/libc.a(shr_64.o)
         /usr/lib/libC.a(shr_64.o)
         /usr/lib/libC.a(shr2_64.o)
         /usr/lib/libC.a(ansi_64.o)
         /usr/lib/libpthreads.a(shr_xpg5_64.o)
         /oracle/goldengate/11.1.1/libicuuc38.a(libicuuc38.1.so)
         /oracle/goldengate/11.1.1/libicui18n38.a(libicui18n38.1.so)
Cannot find      /oracle/goldengate/11.1.1/libxerces-c.a
         /unix
         /usr/lib/libcrypt.a(shr_64.o)
         /usr/lib/libC.a(ansicore_64.o)
         /usr/lib/libC.a(shrcore_64.o)
         /usr/lib/libC.a(shr3_64.o)
         /oracle/goldengate/11.1.1/libicudata38.a(libicudata38.1.so)
         /usr/lib/libpthread.a(shr_xpg5_64.o)
         /usr/lib/libc_r.a(shr_64.o)
 
ls -l *a
-r-xr-xr-x    1 orax122  oinstall   11360230 Mar 29 10:42 libicudata38.a
-r-xr-xr-x    1 orax122  oinstall    9302329 Mar 29 10:42 libicui18n38.a
-r-xr-xr-x    1 orax122  oinstall    6302602 Mar 29 10:42 libicuuc38.a
-r-xr-xr-x    1 orax122  oinstall    9209080 Mar 29 10:42 libxerces-c.a
 
Solution
echo $LIBPATH
/oracle/goldengate/11.1.1

$LIBPATH must include $ORACLE_HOME/lib

export LIBPATH=${OGG_HOME}:$ORACLE_HOME/lib
echo $LIBPATH
/oracle/goldengate/11.1.1:/oracle/rdbms/10.2.0/orax122/lib

Database Character Set Conversion

Environment
Oracle 10.2.0.3, AIX 5.3

Requirements
Change database character set from AL32UTF8 to WE8ISO8859P1.

To see the current set:
select * from database_properties where property_name='NLS_CHARACTERSET';
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

Prerequisites
1. Install CSSCAN (Oracle support note ID 745809.1)
csscan \"sys as sysdba\" full=y
CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.

sqlplus / as sysdba
SQL> @?/rdbms/admin/csminst.sql

2. Apply Oracle patch 5879179 (to fix bug 5879179, specific to version 10.2.0.3 64bit)
It should ok for versions higher than 10.2.0.3
Before patching CSSCAN returns multiple errors:
ORA-22925: operation would exceed maximum size allowed for a LOB value

Solution
Character set conversion is done using Oracle utilities CSSCAN and CSALTER.
The main steps are:
1.    Clean the database
2.    CSSCAN
3.    Database backup
4.    Dealing with Truncation and Lossy data
5.    Dealing with Convertible data
6.    CSSCAN
7.    Conversion
8.    Post conversion steps

1. Clean the database. Delete unneeded schemas and tables. Purge database recycle bin.
PURGE DBA_RECYCLEBIN;

2. Run CSSCAN as SYSDBA user.
csscan \"sys as sysdba\" full=y

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Current database character set is AL32UTF8.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..32): 1 > 4

Enumerating tables to scan...

The output goes into 3 files: scan.err, scan.out, scan.txt
Detailed description can be found in Oracle Support document "Csscan output explained [ID 444701.1]".

3. Backup database if it has not been done yet.

4. Dealing with Truncation and Lossy data
Check file scan.txt
With some lossy data, I had the following summary:
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
Some character type application data are not convertible to the new character set

There was no Truncation data in this DB.
Lossy data were found in one table (TOAD_PLAN_TABLE) and I’ve just dropped it.
More info about dealing with Lossy and Transaction data can be found in Oracle document " Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1]"

5. Dealing with Convertible data
"Convertible" Data Dictionary CLOB data is handled by CSALTER. No action required.
"Convertible" Application Data has to be exported and deleted!
When there are application Convertible data without Truncation/Lossy data, scan summary in scan.txt looks like:
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data are convertible to the new character set

In this case there were two application tables with convertible data.
[Application Data Conversion Summary]
Datatype         Changeless  Convertible   Truncation  Lossy
---------- ---------------- ------------ ------------ ------
VARCHAR2     15,045,735,123          280            0      0
CHAR                      0            0            0      0
LONG                      0            0            0      0
CLOB                      0            0            0      0
VARRAY                    0            0            0      0
---------- ---------------- ------------ ------------ ------

[Distribution of Convertible, Truncated and Lossy Data by Table]
USER.TABLE                Convertible  Truncation  Lossy
------------------------ ------------ ----------- ------
EDWSTG_HM.JOBS                     21           0      0
EDWSTG_HM.TRANSACTIONS            259           0      0

Export and truncate
expdp \"/ as sysdba\" DUMPFILE=t01_%u.dmp LOGFILE=t01.log tables=EDWSTG_HM.JOBS,EDWSTG_HM.TRANSACTIONS parallel=4

SQL> truncate table EDWSTG_HM.JOBS;
SQL> truncate table EDWSTG_HM.TRANSACTIONS;

6. Run CSSCAN again.
csscan \"sys as sysdba\" full=y

To run CSALTER the following output of CSSCAN (scan.txt) is required:

[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data remain the same in the new character set

[Data Dictionary Conversion Summary]
…
…
The data dictionary can be safely migrated using the CSALTER script

7. Conversion
Shutdown listener and make sure nothing else connects to the database.

sqlplus / as sysdba
show parameter job_queue_processes
show parameter aq_tm_processes
-- assuming spfile is in use
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
shutdown immediate;

startup restrict
spool convert.log
@?/rdbms/admin/csalter.plb

Restart the database and listener.

8. Post conversion steps
Restore altered parameters
alter system set job_queue_processes=10;
alter system set aq_tm_processes=1;

Import exported tables
impdp \"/ as sysdba\" dumpfile=t01_%u.dmp logfile=t01.imp.log table_exists_action=append parallel=4

Useful Oracle support notes
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) [ID 745809.1]
Csscan output explained [ID 444701.1]
Changing the Database Character Set ( NLS_CHARACTERSET ) [ID 225912.1]
Changing the Database Character Set - Frequently Asked Questions [ID 227337.1]