How to use rlwrap to get a command history in sql*plus

1. get rlwrap

$ wget ftp://195.220.108.108/linux/epel/5/x86_64/rlwrap-0.30-1.el5.x86_64.rpm
–11:28:39–  ftp://195.220.108.108/linux/epel/5/x86_64/rlwrap-0.30-1.el5.x86_64.rpm
=> `rlwrap-0.30-1.el5.x86_64.rpm’
Connecting to 195.220.108.108:21… connected.
Logging in as anonymous … Logged in!
==> SYST … done.    ==> PWD … done.
==> TYPE I … done.  ==> CWD /linux/epel/5/x86_64 … done.
==> SIZE rlwrap-0.30-1.el5.x86_64.rpm … 49213
==> PASV … done.    ==> RETR rlwrap-0.30-1.el5.x86_64.rpm … done.
Length: 49213 (48K)

100%[===========================================================================================>] 49,213      92.8K/s   in 0.5s

11:28:41 (92.8 KB/s) – `rlwrap-0.30-1.el5.x86_64.rpm’ saved [49213]

{root@sachse2:~}
$ rpm -ivh rlwrap-0.30-1.el5.x86_64.rpm
warning: rlwrap-0.30-1.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 217521f6
Preparing…                ########################################### [100%]
1:rlwrap                 ########################################### [100%]

2.  Usage

{oradb@sachse2:~}
$ rlwrap sqlplus / as sysdba

That’s it.

Solution: Converting flac to mp3

Sometimes you need to convert a load of flac files to mp3’s, for example when wanting to listen them on your mp3 player. This solution contains a single line of bash that’ll convert all flac files in the current directory to mp3’s, keeping the flac files.

Attention: this post now has an advanced counterpart providing a nice script. See Solution: Converting flac to mp3 advanced (supports drag ‘n drop).

Note that you will need flac and lame for this to work. Run the following line in the directory where the flac files are:

[rechosen@localhost ~]$ for file in *.flac; do $(flac -cd “$file” | lame -h – “${file%.flac}.mp3″); done

This will output the mp3 files in the same directory as the flac files. If any of the mp3 files that’s being created already exists, it will be overwritten. When the conversion has finished, you can copy the mp3 files to an other location like this:

[rechosen@localhost ~]$ cp *.mp3 /media/sda1

Note that this will copy all mp3 files in the current directory, not just the ones converted from the flac files! Of course, you could replace “/media/sda1″ with any other directory. You could also replace “cp” by “mv” to move the mp3 files instead of copying them.

You can also make lame create the mp3 files somewhere else. Take the following line:

[rechosen@localhost ~]$ for file in *.flac; do $(flac -cd “$file” | lame -h – YOURLOCATION/”${file%.flac}.mp3″); done

And replace YOURLOCATION with the location you want the mp3 files to be created in. For example:

[rechosen@localhost ~]$ for file in *.flac; do $(flac -cd “$file” | lame -h – /tmp/”${file%.flac}.mp3″); done

The above line would output the mp3’s in the /tmp directory.

Create 11g rman catalog

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 – Production on Wed Jul 22 14:42:29 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user rman11107 cascade;

User dropped.

SQL> create user rman11107 identified by rman11107 DEFAULT TABLESPACE rmand QUOTA UNLIMITED ON rmand;

User created.

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman11107;

Grant succeeded.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ rman catalog rman11107/rman11107@rman

Recovery Manager: Release 11.1.0.7.0 – Production on Wed Jul 22 14:43:17 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

RMAN>

$ rman target / catalog rman11107/rman11107@rman11g

Recovery Manager: Release 10.2.0.4.0 – Production on Wed Jul 22 14:46:57 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PDOEM (DBID=382700901)
connected to recovery catalog database

RMAN>  REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> configure channge device type disk format ‘/rep/rman_disk_backup/pdoem/ora_df%t_s%s_s%p’;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> backup database plus archivelog;

RMAN> backup database plus archivelog DELETE ALL INPUT;

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

RMAN> delete noprompt obsolete;

Set apache user and password

htpasswd -c /opt/lampp/etc/haiyan_passwd henry

Add new SATA disk to Linux (OEL5.0)

1. Install the drive and powerup
2. parted /dev/sdc

mklabel msdos
print
mkpart primary 0 500G
mkpart extended 500G 1500G
mkpart logical 500G 1500

1. /sbin/mkfs -t ext3 /dev/sdc1
2. /sbin/mkfs -t ext3 /dev/sdc5
3. mkdir /backup2
4. mkdir /u04
5. mount /dev/sdc1 /u04
6. mount /dev/sdc5 /backup2

Oracle repository DB licensing – FREE

Page 1-5,  at “Oracle® Database Licensing Information / 10g Release 2 (10.2)” Part Number B14199-10

Infrastructure Repository Databases

A separate Oracle Database can be installed and used as a Recovery Manager (RMAN) repository without additional license requirements, provided that all the Oracle databases managed in this repository are correctly licensed. This repository database may also be used for the Oracle Enterprise Grid Control repository. It may not be used or deployed for other uses.

Oracle Database Vault and Oracle9i Database Release 2 (9.2.0.8):

A separate Oracle Database 10g Release 2 can be installed and used to administer Oracle Database Vault when installed with Oracle9i Database Release 2 (9.2.0.8), without additional license requirements. All 9.2.0.8 databases managed by Database Control in this manner must be correctly licensed with the Oracle Database Vault option. The installation of Oracle
Database 10g Release 2 deployed in this manner may not be used or deployed for other uses.

Diagnosing Library Cache Latch Contention: A Real Case Study

ref:

http://arup.blogspot.com/2008/08/diagnosing-library-cache-latch.html

215858.1

175006.1

sqlplus -prelim / as sysdba

oradebug setmypid

oradebug hanganalyze 12

RAC Cluster:

oradebug setmypid

oradebug setinst all

oradebug -g def hanganalyze 3

reallife_diagnosing-library-cache-latch

interpreting-hanganalyze-trace-files

IBM MQ v7 installation

1 Preparations on MQ server
1.1 User creation
Create following user with group name of “mqm”
mqm, crtmqm,strmqm
1.2 Create mqm under /opt and /var
create /opt/mqm, make it RW to mqm group and 600MB in space capacity

create /var/mqm, make it RW to mqm group and 300MB in space capacity
1.3 Kernel configuration
make following kernel changes (/etc/sysctl.conf ):
kernel.msgmni = 1024
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 500 256000 250 1024
fs.file-max = 32768
net.ipv4.tcp_keepalive_time = 300

To load these sysctl values immediately, enter the command sysctl -p.
If you do not issue the sysctl -p command, the new values are loaded when the system is rebooted.
1.4 Max open files
If the system is heavily loaded, you might need to increase the maximum possible number of open files. If your distribution supports the proc filesystem you can query the current limit by issuing the following command: cat /proc/sys/fs/file-max

If you are using a pluggable security module such as PAM (Pluggable Authentication Module), ensure that this does not unduly restrict the number of open files for the ’mqm’ user. For a standard WebSphere MQ queue manager, set the ’nofile’ value to 10240 or more for the ’mqm’ user. We suggest you add this command to a startup script in /etc/rc.d/…
1.5 Max process
A running WebSphere MQ queue manager consists of a number of thread programs, and each connected application will increase the number of threads running in the queue manager processes. You should ensure that the maximum number of processes which the mqm user is allowed to run is not unduly restricted by one of the pluggable security modules such as PAM. Set nproc for the mqm user to 4090 or more.

1.6 Root access privilege
this is needed when doing the MQ installation.

1.7 64bit consideration
Implications of a 64-bit queue manager Note: A 64-bit queue manager is available on the POWER, x86-64 and zSeries (s390x) platforms. When using the 64-bit queue manager, the use of the LIBPATH and LD_LIBRARY_PATH environment variable is not advised.

The recommended way of using WebSphere MQ commands and your applications is as follows:
· Unset LIBPATH and LD_LIBRARY_PATH and build your applications with a built in path to the appropriate WebSphere MQ libraries, this is detailed in the appropriate WebSphere MQ book for your type of WebSphere MQ application.
· If you need to set LIBPATH or LD_LIBRARY_PATH, consider not including /usr/lib in the path you specify in the variable. If you need to include /usr/lib in your LIBPATH or LD_LIBRARY_PATH then in order to avoid errors running 64-bit WebSphere MQ applications or WebSphere MQ commands, consider removing the symbolic links from /usr/lib to the 32-bit WebSphere MQ libraries using the dltmqlnk command. The symbolic links can be restored with the crtmqlnk command. You also need to build your applications with a built in path to the appropriate WebSphere MQ libraries.
Note that both the dltmqlnk command and the crtmqlnk command are scripts, and take no parameters.
· If you cannot use either of the first two options, run your applications in a different environment to the one which issues any WebSphere MQ commands.
Note: WebSphere MQ libraries are in the following locations: /opt/mqm/lib (32-bit libraries) and /opt/mqm/lib64 (64-bit libraries).
2 Installation
2.1 Install rpm
2.1.1 Log in as root.
And make sure all RPMs are in your current directory.
2.1.2 Run the mqlicense.sh script.
If you want to view a text-only version of the license, which can be read by a screen-reader, type: ./mqlicense.sh -text_only
The license is displayed.
If want to accept the license without it being displayed, you can run the mqlicense.sh script with the -accept option. ./mqlicense.sh -accept
You must accept the license agreement before you can proceed with the installation.
2.1.3 Install components
Use the rpm -ivh command to install each component that you require.
The minimum components you must install are:
· MQSeriesRuntime
· MQSeriesJRE
· MQSeriesServer
· MQSeriesJava
This example shows a minimum installation:
rpm -ivh MQSeriesRuntime-7.0.0-0.i386.rpm MQSeriesSDK-7.0.0-0.i386.rpm MQSeriesServer-7.0.0-0.i386.rpm MQSeriesJava-7.0.0-0.i386.rpm

for 64bit:
rpm -ivh MQSeriesRuntime-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesJava-7.0.0-0.x86_64.rpm MQSeriesJRE-7.0.0-0.x86_64.rpm MQSeriesServer-7.0.0-0.x86_64.rpm

You can reinstall the /bin/sh shell using RPM, or specify the RPM option –nodeps to disable dependency checking during installation of WebSphere MQ.

Install rest of the components:
rpm -ivh MQSeriesSDK-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesSamples-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesClient-7.0.0-0.x86_64.rpm
rpm -ivh gsk7bas-7.0-4.14.i386.rpm
rpm -ivh gsk7bas64-7.0-4.14.x86_64.rpm
rpm -ivh MQSeriesKeyMan-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesMan-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesTXClient-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesEclipseSDK33-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesConfig-7.0.0-0.x86_64.rpm

2.1.4 client install:
Install minimum components:
· MQSeriesRuntime
· MQSeriesJRE
· MQSeriesServer
· MQSeriesJava

for 64bit:
rpm -ivh MQSeriesRuntime-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesJava-7.0.0-0.x86_64.rpm MQSeriesJRE-7.0.0-0.x86_64.rpm MQSeriesServer-7.0.0-0.x86_64.rpm

Then:
rpm -ivh MQSeriesClient-7.0.0-0.x86_64.rpm
2.2 Install verification
2.2.1 Create a sample Queue
mqm@dev15>crtmqm -q venus.queue.manager
There are 90 days left in the trial period for this copy of WebSphere MQ.
WebSphere MQ queue manager created.
Creating or replacing default objects for venus.queue.manager.
Default objects statistics : 58 created. 0 replaced. 0 failed.
Completing setup.
Setup completed.

mqm@dev15>strmqm
There are 90 days left in the trial period for this copy of WebSphere MQ.
WebSphere MQ queue manager ‘venus.queue.manager’ starting.
5 log records accessed on queue manager ‘venus.queue.manager’ during the log replay phase.
Log replay for queue manager ‘venus.queue.manager’ complete.
Transaction manager state recovered for queue manager ‘venus.queue.manager’.
WebSphere MQ queue manager ‘venus.queue.manager’ started.

mqm@dev15>runmqsc
5724-H72 (C) Copyright IBM Corp. 1994, 2008. ALL RIGHTS RESERVED.
Starting MQSC for queue manager venus.queue.manager.

define qlocal (orange.queue)
1 : define qlocal (orange.queue)
AMQ8006: WebSphere MQ queue created.
end
2 : end
One MQSC command read.
No commands have a syntax error.
All valid MQSC commands were processed.
mqm@dev15>

2.2.2 Test the sample queue
mqm@dev15>cd /opt/mqm/samp/bin
mqm@dev15>./amqsput ORANGE.QUEUE
Sample AMQSPUT0 start
target queue is ORANGE.QUEUE
Sample AMQSPUT0 end

Sample AMQSPUT0 end
mqm@dev15>./amqsget ORANGE.QUEUE
Sample AMQSGET0 start
message
no more messages
Sample AMQSGET0 end

Tips for during the tutorial:
1. Use “runmqsc QUEUE_NAME” to switch between queue management
2. When set sender channel, use “IP(PORT)” for connection name if the management queue port is not 1414.
3. Running the amqsput and amqsputc samples
These programs each take 2 parameters:
· The name of the target queue (required)
· The name of the queue manager (optional)
If a queue manager is not specified, amqsput connects to the default queue manager and amqsputc connects to the queue manager identified by an environment variable or the client channel definition file. To run these programs, enter one of the following:
· amqsput myqueue qmanagername
· amqsputc myqueue qmanagername
where myqueue is the name of the queue on which the messages are going to be put, and qmanagername is the queue manager that owns myqueue.
4. Client-server testing – windows id (hliang) should be set in MCA ID and also a user on Linux OS with mqm as group.
· On window
MQSERVER=CLIENT.MQ_ORANGE/TCP/192.168.1.205(1416)
C:\Documents and Settings\hliang>set MQSERVER=CLIENT.QM_ORANGE/TCP/192.168.1.205(1416

C:\Documents and Settings\hliang>amqsputc Q1
Sample AMQSPUT0 start
target queue is Q1
dsfasdfhsa
fkdsajfhas

Sample AMQSPUT0 end
· On linux
export MQSERVER=CLIENT.QM_ORANGE/TCP/192.168.1.205
$ ./amqsput Q1 QM_ORANGE (./amqsput Q1)
Sample AMQSPUT0 start
target queue is Q1
qqqqqqqqqqqqqqq
wwwwwwwwwwwwwwww

Sample AMQSPUT0 end

Testing Oracle 11g Message Gateway with IBM websphere MQ v7

TestCase setup
· MQ server
o IP: 192.168.1.205
o MQ server V7 installed successfully
· Oracle server
o IP: 192.168.1.221
o Oracle database 11g freshly installed
o Database T1 created during the installation as “orcl”
1 Create test MQ objects on Websphere server
Queue manager: TESTQM
Local Queue: TQ, LOG_IN, LOG_OUT
Channel: TQCONN(server-connect), MCA user ID=oracle

From OS : Add oracle into mqm group
2 Install MQ client on Oracle server
2.1 Log in as root.
And make sure all RPMs are in your current directory.
2.2 User creation
Create following user with group name of “mqm”
mqm, crtmqm,strmqm
2.3 Create mqm under /opt and /var
create /opt/mqm, make it RW to mqm group and 600MB in space capacity
create /var/mqm, make it RW to mqm group and 300MB in space capacity
2.4 Run the mqlicense.sh script.
If you want to view a text-only version of the license, which can be read by a screen-reader, type: ./mqlicense.sh -text_only
The license is displayed.
If want to accept the license without it being displayed, you can run the mqlicense.sh script with the -accept option. ./mqlicense.sh -accept
You must accept the license agreement before you can proceed with the installation.
2.5 Client installation:
Install minimum components:
· MQSeriesRuntime
· MQSeriesJRE
· MQSeriesServer
· MQSeriesJava

for 64bit:
rpm -ivh MQSeriesRuntime-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesJava-7.0.0-0.x86_64.rpm MQSeriesJRE-7.0.0-0.x86_64.rpm MQSeriesServer-7.0.0-0.x86_64.rpm

Then:
rpm -ivh MQSeriesClient-7.0.0-0.x86_64.rpm
3 Loading Database Objects into the Database
Using SQL*Plus, run ORACLE_HOME/mgw/admin/catmgw.sql as user SYS as SYSDBA
4 Setting Up a mgw.ora Initialization File
$ cp sample_mgw.ora mgw.ora

vi mgw.ora
s/myOracleHome/u01\/app\/oracle\/product\/11.1.0\/db_1/g
5 Setup listener.ora/tnsnames.ora
5.1 Configure listener.ora
# Add a SID_DESC
SID_LIST_LISTENER= (SID_LIST=
(SID_DESC =
(SID_NAME= mgwextproc)
(ENVS=”LD_LIBRARY_PATH=JRE_HOME/lib/sparc/server:ORACLE_HOME/lib”)
(ORACLE_HOME=ORACLE_HOME)
(PROGRAM = extproc))

SID_NAME The SID that is specified in the net service name in tnsnames.ora. In the following example, the SID_NAME is mgwextproc. ENVS Set up the LD_LIBRARY_PATH environment needed for the external procedure to run. The LD_LIBRARY_PATH must contain the following paths:
JRE_HOME/lib/PLATFORM_TYPE/server
ORACLE_HOME/lib
It should also contain any additional libraries required by third-party messaging systems. See “Setting Up Non-Oracle Messaging Systems”.
ORACLE_HOME Your Oracle home directory. Using $ORACLE_HOME does not work. PROGRAM The name of the external procedure agent, which is extproc

For example:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC =
(SID_NAME=mgwextproc)
(ENVS=”LD_LIBRARY_PATH=/u01/app/oracle/product/11.1.0/db_1/jdk/jre/lib/amd64/server:/u01/app/oracle/product/11.1.0/db_1/lib”)
(ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1)
(PROGRAM=extproc)
))
5.2 Configure tnsnames.ora
MGW_AGENT =
(DESCRIPTION=
(ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC1521)))
(CONNECT_DATA= (SID=mgwextproc) (PRESENTATION=RO)))
Note: KEY=EXTPROC should match what’s in listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
6 Create username
create user mgwuser identified by mgwuser;
grant connect, resource to mgwuser;
grant execute on dbms_aq to mgwuser;
grant execute on dbms_aqadm to mgwuser;

grant connect, resource, MGW_AGENT_ROLE to mgwagent identified by mgwagent;
grant connect, resource, MGW_ADMINISTRATOR_ROLE to mgwadm identified by mgwadm;

7 Checkpoint one
7.1 Start MGW
connect mgwadm/mgwadm
exec dbms_mgwadm.db_connect_info(‘mgwagent’,'mgwagent’, ‘t1′);
exec dbms_mgwadm.startup;
7.2 Check MGW status
select * from MGW_GATEWAY;
If “ORA-28575: unable to open RPC connection to external procedure agent”
Then fix listener.ora and tnsnames.ora configurations
· tnsnames.ora must have a net service name entry named MGW_AGENT.
· The SID value specified for CONNECT_DATA of the MGW_AGENT net service name in tnsnames.ora must match the SID_NAME value of the SID_DESC entry in listener.ora.
· If the MGW_AGENT net service name is set up for an IPC connection, the KEY values for ADDRESS in tnsnames.ora and listener.ora must match.
· Other values in tnsnames.ora or listener.ora may be incorrect or not match.
Note: No log file will be generated in the scenario.
7.3 Check log file for error messages
7.4 Stop MGW
exec dbms_mgwadm.shutdown;
8 AQMQ testing with SYS.MGW_BASIC_MSG_T payload
8.1 Create test queues in oracle
connect mgwuser/mgwuser
set serveroutput on

– create queue tables and queues
begin
dbms_aqadm.create_queue_table(
queue_table => ‘mgw_basic_mc’,
queue_payload_type => ’sys.mgw_basic_msg_t’,
multiple_consumers => TRUE);
dbms_aqadm.create_queue(queue_name =>’mgw_basic_src’,
queue_table => ‘mgw_basic_mc’,
max_retries => 1000);
dbms_aqadm.start_queue(‘mgw_basic_src’);

dbms_aqadm.create_queue_table(
queue_table => ‘mgw_basic_sc’,
queue_payload_type => ’sys.mgw_basic_msg_t’,
multiple_consumers => FALSE);
dbms_aqadm.create_queue(queue_name =>’mgw_basic_dest’,
queue_table => ‘mgw_basic_sc’,
max_retries => 1000);
dbms_aqadm.start_queue(‘mgw_basic_dest’);
end;
/
8.2 Setup oracle OMG for MQ
connect mgwadm/mgwadm;

variable gv_database VARCHAR2(32);
variable gv_mq_queue_manager VARCHAR2(32);
variable gv_mq_queue_name VARCHAR2(32);
variable gv_mq_channel VARCHAR2(32);
variable gv_mq_inbound_log_queue VARCHAR2(32);
variable gv_mq_outbound_log_queue VARCHAR2(32);
variable gv_mq_username VARCHAR2(32);
variable gv_mq_password VARCHAR2(32);
variable gv_mq_host VARCHAR2(48);
variable gv_mq_port VARCHAR2(6);

–
– WARNING!!!! The values below must be set before this script is run.
–
begin
:gv_database := ‘t1′; — tnsname entry for local database
:gv_mq_queue_manager := ‘TESTQM’; — Queue manager
:gv_mq_queue_name := ‘TQ’; — local Queue
:gv_mq_channel := ‘TQCONN’; — “server-connect” channel in TESTQM
:gv_mq_host := ‘192.168.1.205′; — MQ server
:gv_mq_port := ‘1417′; — port for TESTQM
:gv_mq_username := NULL; — optional
:gv_mq_password := NULL; — optional
:gv_mq_inbound_log_queue := ‘LOG_IN’; — local Queue
:gv_mq_outbound_log_queue := ‘LOG_OUT’; — local Queue
end;
/
prompt
prompt Setting the gateway agent user
prompt

rem If we do not set the service_name argument it will default to the
rem ORACLE_SID of the MGW process (the listener process).
begin
dbms_mgwadm.db_connect_info(
username => ‘mgwagent’,
password => ‘mgwagent’,
database => RTRIM(:gv_database));
end;
/

prompt
prompt Creating MQSeries link
prompt

declare
v_options sys.mgw_properties;
v_prop sys.mgw_mqseries_properties;
begin
if (:gv_mq_queue_manager IS NULL) then
dbms_output.put_line(‘queue manager must be specified’);
end if;

— set options if desired

— set certain mqseries properties used for MQSeries
— NOTE: This information must be changed for your MQSeries setup.
v_prop := sys.mgw_mqseries_properties.construct();

v_prop.max_connections := 1;

— queue manager name (required)
v_prop.queue_manager := RTRIM(:gv_mq_queue_manager);

— hostname for queue manager host (required)
v_prop.hostname := RTRIM(:gv_mq_host);

— channel name (required)
v_prop.channel := RTRIM(:gv_mq_channel);

— port (required — 1414 is MQSeries default)
v_prop.port := RTRIM(:gv_mq_port);

— username given to queue manager
if (:gv_mq_username IS NOT NULL) then
v_prop.username := RTRIM(:gv_mq_username);
end if;

— password given to queue manager
if (:gv_mq_password IS NOT NULL) then
v_prop.password := RTRIM(:gv_mq_password);
end if;

— name of MQSeries queue to be used for MGW logging on outbound jobs
if (:gv_mq_outbound_log_queue IS NOT NULL) then
v_prop.outbound_log_queue := RTRIM(:gv_mq_outbound_log_queue);
end if;

— name of MQSeries queue to be used for MGW logging on inbound jobs
if (:gv_mq_inbound_log_queue IS NOT NULL) then
v_prop.inbound_log_queue := RTRIM(:gv_mq_inbound_log_queue);
end if;

dbms_mgwadm.create_msgsystem_link(
linkname =>’mqlink’, – link name
properties => v_prop, – mqseries driver properties
options => v_options ); – options

end;
/

prompt
prompt Registering foreign queue
prompt

declare
v_options sys.mgw_properties;
begin
— Set options for register foreign queue
— MQ_openOptions: 2066 is TODO
v_options := sys.mgw_properties(
sys.mgw_property(‘MQ_openOptions’, ‘2066′) );

dbms_mgwadm.register_foreign_queue(
name => ‘destq’, — MGW foreign queue name
linkname => ‘mqlink’, — name of link to use
provider_queue => RTRIM(:gv_mq_queue_name), — name of MQSeries queue
domain => dbms_mgwadm.DOMAIN_QUEUE, — single consumer queue
options => v_options );
end;
/

prompt
prompt Adding outbound subscriber.
prompt

begin
— queue_name should be an AQ queue
— destination should be a non-Oracle queue
— subscriber rule applies to AQ subscriber
— exception queue must be a NORMAL AQ queue with payload the same as
— the source queue
dbms_mgwadm.add_subscriber(
subscriber_id => ’sub_aq2mq’, – MGW subscriber name
propagation_type => dbms_mgwadm.outbound_propagation,
— AQ queue name
queue_name => ‘mgwuser.mgw_basic_src’,
— MGW foreign queue with link
destination => ‘destq@mqlink’);
— no selection rule used for this subscriber
— no transformation invoked on dequeue
— no exception queue
end;
/

prompt
prompt Adding inbound subscriber.
prompt

begin
— destination should be an AQ queue
— queue_name should be a non-Oracle queue
— exception queue must be a WebSphere MQ queue name of a queue that
— exists on the queue manager the link uses.
dbms_mgwadm.add_subscriber(
— MGW subscriber name
subscriber_id => ’sub_mq2aq’,
propagation_type => dbms_mgwadm.inbound_propagation,
— MGW foreign queue with link
queue_name => ‘destq@mqlink’,
— AQ queue name
destination => ‘mgwuser.mgw_basic_dest’);
— no selection rule can be used for this subscriber
— no transformation invoked on dequeue
— no exception queue
end;
/

prompt
prompt Scheduling propagation
prompt

begin
dbms_mgwadm.schedule_propagation(
— schedule name
schedule_id => ’sch_aq2mq’,
— outbound propagation
propagation_type => dbms_mgwadm.outbound_propagation,
— AQ queue name
source =>’mgwuser.mgw_basic_src’,
— MGW foreign queue with link
destination =>’destq@mqlink’);

— The remaining fields currently not used by MGW
end;
/

begin
dbms_mgwadm.schedule_propagation(
— schedule name
schedule_id => ’sch_mq2aq’,
— outbound propagation
propagation_type => dbms_mgwadm.inbound_propagation,
— MGW foreign queue with link
source => ‘destq@mqlink’,
— AQ queue name
destination =>’mgwuser.mgw_basic_dest’);

— The remaining fields currently not used by MGW
end;
/

Clean testing:
connect mgwadm/mgwadm

Rem Remove the subscribers.
begin
— Since this subscriber was for outbound propagation
— removing it will remove the underlying AQ subscriber.
dbms_mgwadm.remove_subscriber(subscriber_id => ’sub_aq2mq’,
force => dbms_mgwadm.NO_FORCE);
dbms_mgwadm.remove_subscriber(subscriber_id => ’sub_mq2aq’,
force => dbms_mgwadm.NO_FORCE);
end;
/

exec dbms_mgwadm.remove_subscriber(subscriber_id => ’sub_aq2mq’,force => dbms_mgwadm.FORCE);
exec dbms_mgwadm.remove_subscriber(subscriber_id => ’sub_mq2aq’,force => dbms_mgwadm.FORCE);

Rem Remove the schedules.
begin
dbms_mgwadm.unschedule_propagation(schedule_id => ’sch_aq2mq’);
dbms_mgwadm.unschedule_propagation(schedule_id => ’sch_mq2aq’);
end;
/

Rem Unregister the MQSeries queue.
begin
dbms_mgwadm.unregister_foreign_queue(name => ‘destq’, linkname => ‘mqlink’);
end;
/

Rem Finally, remove the link.
begin
dbms_mgwadm.remove_msgsystem_link(‘mqlink’);
end;
/
8.3 Checkpoint two
8.3.1 Start MGW
connect mgwadm/mgwadm
exec dbms_mgwadm.startup;
8.3.2 Check MGW status
select * from MGW_GATEWAY;
If “ORA-28575: unable to open RPC connection to external procedure agent”
Then fix listener.ora and tnsnames.ora configurations
Note: No log file will be generated in the scenario.
8.3.3 Check log file for error messages
8.3.4 Stop MGW
exec dbms_mgwadm.shutdown;
8.4 Testing
· Start MGW
· Enqueue
· Check : select count(*) from mgwuser.mgw_basic_sc; — the count should increase by 1.
· Dequeue
Enqueue scripts:

SQL> !more enq.sql
declare
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
msgid RAW(16);
payload sys.mgw_basic_msg_t;
header sys.mgw_name_value_array_t;
text_body sys.mgw_text_value_t;
msg varchar2(100);

BEGIN
— Create the header. This may normally be set to NULL.
— As an example, we set a couple of WebSphere MQ message properties
— via the built-in capabilities of MGW_BASIC_MSG_T. In most cases
— these properties will not need to be set.
header := sys.mgw_name_value_array_t(
sys.mgw_name_value_t.construct_integer(‘MGW_MQ_characterSet’, ‘1208′),
sys.mgw_name_value_t.construct_integer(‘MGW_MQ_priority’, ‘7′)
);

select ‘Sample Msg:’||to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) into msg from dual;

— Create the text body.
text_body := sys.mgw_text_value_t(msg, NULL);

— Construct the message from its constituent parts.
payload := sys.mgw_basic_msg_t(header, text_body, NULL);

— Enqueue the message.
dbms_aq.enqueue(queue_name => ‘mgwuser.mgw_basic_src’,
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => payload,
msgid => msgid);
commit;
END enqueue;
/

Dequeue script:
SQL> !more deq.sql
select count(*) from mgwuser.mgw_basic_sc;

set serveroutput on

declare
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
msgid RAW(16);
payload SYS.MGW_BASIC_MSG_T;
BEGIN
— FIRST_MESSAGE
dequeue_options.navigation := 1;
dequeue_options.wait := dbms_aq.NO_WAIT;

dbms_aq.dequeue(queue_name => ‘mgwuser.mgw_basic_dest’,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => payload,
msgid => msgid);
DBMS_OUTPUT.PUT_LINE (‘Message:’ || payload.text_body.small_value);
commit;
END dequeue;
/

select count(*) from mgwuser.mgw_basic_sc;

SQL> !more cnt.sql
prompt mgwuser.mgw_basic_sc
select count(*) from mgwuser.mgw_basic_sc;

prompt mgwuser.mgw_basic_mc
select count(*) from mgwuser.mgw_basic_mc;