Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: looking for export with dynamic compress script on VMS

RE: looking for export with dynamic compress script on VMS

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 10 May 2001 22:10:12 -0700
Message-ID: <F001.003006E7.20010510214521@fatcity.com>

Hi,

Don't know if the following will work with Oracle import / export but it was posted by one of the Rdb engineers on the Rdb mailing list.

Hope it helps.

Regards,
Bruce Reardon
mailto:bruce.reardon_at_comalco.riotinto.com.au

"

-----Original Message-----
Sent: Tuesday, 10 April 2001 7:59
To: oraclerdb
RMU/LOAD with no disk file

This talk of RMU/UNLOAD and RMU/LOAD reminded me of a trick that might be handy for you to pull out of a hat some day. The goal of this trick is to avoid having to sequentially UNLOAD and then LOAD data from one database to another (or, I suppose, even within a single database between tables).

Here's a draft copy of an article that I'm putting together for metalink...

        n

When moving large amounts of data between two Oracle Rdb databases, often the RMU /UNLOAD and RMU /LOAD utilities are used to extract the data from one database and to load the data into another. Using a disk file as the destination for the unload and the source for the load would mean that these two operations must be done sequentially. However, by using an OpenVMS mailbox, it is possible to perform the unload and the load in parallel. Parallel processing can help keep multiple CPUs and disk drives/controllers busy and avoids the need for a disk file for sequential staging.

The tricky part of this operation is to create a mailbox to be used as the 'pipe' between the RMU /UNLOAD and RMU /LOAD commands. The BLISS example program attached at the end of this article can be used to create an OpenVMS mailbox for this purpose. You will need to have a BLISS32 compiler installed on your system. The compiler is available (for free) on the OpenVMS Freeware CD or from the Compaq OpenVMS Web site. Compile and link the MBX program using the following two commands:

        $ BLISS MBX.B32 
        $ LINK MBX.OBJ

To use the MBX program to create a mailbox, you should create a foreign command symbol. You can then start the MBX program one or more times in separate processes (batch jobs work well for this) to create system-wide mailboxes with specific names. Note that the example program as shown creates a system-wide permanent mailbox (so that the logical name will be visible to all UIC groups). The mailbox is marked 'delete pending' (by deleting it as soon as it is created) so that when the last channel to the mailbox is deassigned, the mailbox will disappear. Ideally, the MBX program would remain running during the entire operation, though it could be terminated once another process had assigned a channel to the mailbox. The MBX program can be stopped via the STOP command or with CONTROL/Y and EXIT if needed.

Note that it would be possible to modify the example program to use a temporary mailbox instead. This mailbox would, by default, only be accessable to processes in the same UIC group as the process that created the mailbox. Temporary mailboxes also disappear when the last channel to the mailbox is deassigned.

Once a mailbox is available, the RMU /UNLOAD command can be executed specifying the mailbox logical name as the output destination. And the RMU /LOAD command be can simultaneously executed specifying the mailbox logical name as the input source. Note that the RMU /UNLOAD command will stall as the mailbox fills and the RMU /LOAD command will stall when the mailbox is empty.

After the load/unload jobs complete, terminate the process that is running the MBX program to delete the mailbox (STOP/ID or CONTROL/Y EXIT or whatever).

The following example command procedure shows one possible way to create a mailbox called "<tbl>_MBX" (where <tbl> is the name of the table being manipulated), wait for it to be created and then submit 2 batch jobs to do the load and unload for the table and wait for them to complete and finally delete the mailbox. This example will have to be modified for your environment and is intended only to be used as an example template.

!---------------------------- CUT HERE ---------------------------------


$! L O A D _ U N L O A D . C O M
$!
$! Parameter P1 = table name to move from one database to the other
$! Parameter P2 = routine to execute if not null
$!
    $ IF P2 .NES. "" THEN GOTO 'P2'         ! Dispach
    $ MBX :== $MBX.EXE                      ! Point to MBX.EXE
    $ PNAME = F$EXTRACT(0,15,"''P1'_MBX")   ! Name limited to 15 bytes
    $ SPAWN /NOTIFY /NOWAIT /INPUT=NL: /PROCESS='PNAME' MBX 'p1'_MBX

$!
$ WAIT_COUNT = 60 ! Up to 1 minute $LP1: ! Wait for it to be created $ IF .NOT. F$GETDVI("''p1'_MBX:", "EXISTS") ! Created? $ THEN $ WAIT_COUNT = WAIT_COUNT - 1 $ IF WAIT_COUNT .LEQ. 0 ! Waited too long? $ THEN ! Yes $ WRITE SYS$OUTPUT "Mailbox didn't seem to be created" $ EXIT 44 ! SS$_ABORT $ ENDIF $ WAIT 0:0:1 ! Wait one second $ GOTO LP ! Check again $ ENDIF

$!
$! Submit both the load and unload batch jobs. Note that the
$! default SYS$BATCH queue must be defined/created such that
$! both of these batch jobs execute on the current system.
$!

    $ SUBMIT 'F$ENVIRONMENT ("PROCEDURE")' /LOG /NOTIFY -         /NAME=LOAD_'P1' /PARAMETERS=('P1', "LOAD")     $ SUBMIT 'F$ENVIRONMENT ("PROCEDURE")' /LOG /NOTIFY -         /NAME=UNLOAD_'P1' /PARAMETERS=('P1', "UNLOAD")
$!
    $ SET NOON                              ! Do not exit on errors
    $ SYNCHRONIZE UNLOAD_'P1'               ! Wait for jobs to finish
    $ SYNCHRONIZE LOAD_'P1'

$!
$ STOP 'PNAME' ! Delete mailbox $ EXIT 1 ! Done
$!
$LOAD: ! Load table specified by P1 from mailbox $ RMU /LOAD /BUFFERS=500 /COMMIT=10000 /LOG /STATISTICS=ON_COMMIT - <db2> 'P1' 'P1'_MBX: $ EXIT
$!
$UNLOAD ! Unload table specified by P1 into mailbox $ RMU /UNLOAD /BUFFERS=500 /STATISTICS_INTERVAL=60 - <db1> 'P1' 'P1'_MBX:

    $ EXIT
!---------------------------- CUT HERE ---------------------------------

MODULE X (MAIN = MBX$MAIN) = ! M B X . B 3 2 BEGIN

! MBX - Create a system-wide permanent mailbox with a specified name.
! 
! This program creates a system-wide permanent mailbox, marks it
! delete-pending and then hibernates forever.  Intended to create a
! mailbox used for utility message passing between programs.  See the
! OpenVMS System Services Reference Manual and the OpenVMS RTL
! Library (LIB$) Manual for more information about the routines used
! by this program.
!
! Requires an OpenVMS BLISS32 (Alpha or VAX) compiler available from
! the OpenVMS Freeware CD.  Compile and link instructions:
!
!       $ BLISS MBX.B32 
!       $ LINK MBX.OBJ 
! 
! When run as a foreign command, pass a name to give to the mailbox,
! a system-wide logical name will be created with this value and all
! accessors of the mailbox should use the specified name.  When
! finished with the mailbox, either STOP/ID or CONTROL/Y and EXIT to
! terminate the program.

LIBRARY 'SYS$LIBRARY:STARLET';
EXTERNAL ROUTINE LIB$GET_FOREIGN, LIB$SYS_FAO, LIB$PUT_OUTPUT; MACRO $CHK (STS) =
    ( LOCAL $$STS : INITIAL (STS);
        IF NOT .$$STS THEN SIGNAL(.$$STS)) %; MACRO $WRTOUT (FAOCTL) [] =
    ( LIB$SYS_FAO (%ASCID FAOCTL, 0, DDSC, %REMAINING);         LIB$PUT_OUTPUT (DDSC)) %; GLOBAL ROUTINE MBX$MAIN =
BEGIN
LOCAL
    MCHN : INITIAL (0),
    DD : $BBLOCK [DSC$K_D_BLN];     $INIT_DYNDESC (DD);     $CHK (LIB$GET_FOREIGN (DD, $DESCRIPTOR ('ENTER MAILBOX NAME: ')));     IF .DD [DSC$W_LENGTH] EQLU 0 THEN SIGNAL (SS$_ABORT);

    $CHK ($CREMBX (                 ! CREATE THE MAILBOX
        CHAN   = MCHN,              ! CHANNEL
        PRMFLG = 1,                 ! PERM MAILBOX (SYSTEM-WIDE)
        MAXMSG = 65535,             ! MAXIMUM RECORD SIZE
        BUFQUO = 65535,             ! BUFFER QUOTA
        LOGNAM = DD));              ! LOGICAL NAME OF MAILBOX

    $CHK ($DELMBX (CHAN = .MCHN)); ! MARK DELETE-PENDING     $WRTOUT ('!%D CREATED MAILBOX !AS', 0, DD);     WHILE 1 DO $HIBER; ! WAIT FOREVER     RETURN SS$_NORMAL; ! SHOULD NEVER GET HERE END; END
ELUDOM

!---------------------------- CUT HERE ---------------------------------
===================================================================

-----Original Message-----
Sent: Thursday, 10 May 2001 8:06
To: Multiple recipients of list ORACLE-L

Export to NLA0: would do 100% compression. Nope, I'm afraid that you can not use pipes in VMS. You may be able to create a permanent mailbox and write a program that would compress it and forward it through the network, but it would
be an extremely complicated thing. My advice is to export it to a DLT, and then
import it from the tape. You can do that by specifying RECORDSIZE argument and
specifying MUA0: (or any othe MU device) as an export file. Be sure not to specify
an MT: device, those are not supported for the export.

-----Original Message-----
[mailto:Prasada.Gunda1_at_hartfordlife.com] Sent: Tuesday, May 08, 2001 3:56 PM
To: Multiple recipients of list ORACLE-L

Hi VMS & DBA gurus,

As part of migration process from Oracle v7 to v8i, we are trying to do the complete export of our database. Due to the lack of disk space, we can not land such a big export file.

Our v7 database is on VMS platform and I am looking for the VMS script, which does dynamic compression (like the way pipes work on Unix systems) while export is running.

If any one has such script and would like share it, please post it on the list.

I really appreciate for your help.

Thanks in advance,

Best Regards,
Prasad

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Prasada.Gunda1_at_hartfordlife.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri May 11 2001 - 00:10:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US