Monday, August 25, 2008

Using mknod during export

Scenario

Every Friday we have taken the full export backup of one our database.
This database is used for reporting purpose.
Export dump size is around 25 GB. Backup mount point total space is 32 GB. Every weekend I have taken the backup and transfer the export dump (25 GB) to bridge server.

In Bridge server I have zipped the dump file and again transfer the zip file to Backup server. System support group taken the backup into tape.
During this activity takes more time. So I have planned to zip the export dump file on same server.
I have escalated mail to concern person to allocate the more space to Backup mount point.System support group couldn't allocate the space due to insufficient space in disk.At that time my lead told to me “raja use the mknod”. I am not awareness about the “mknod”.


Now we see the export backup using “mknod”.



File Name: exppipe.sh
#!/bin/sh
. $HOME/.bash_profile
cd /home/oracle/dbatest/raja/mknode/
mknod exp_pipe p
gzip -cNf exp_data.dmp.gz &
exp demo/demo file=exp_pipe log=exp_data.log owner=demo statistics=none
rm -f exp_pipe p
fi


File Name: imppipe.sh

#!/bin/sh
. $HOME/.bash_profile
cd /home/oracle/dbatest/raja/mknode/
mknod import_pipe p
gunzip -c exp_data.dmp.gz > import_pipe &
imp testpipe/testpipe file=import_pipe log=imp_data.log fromuser=demo touser=testpipe statistics=none commit=y
rm -f import_pipe p
fi

Note: . $HOME/.bash_profile is environment variable file


Instead of doing the export and zip separately, creating an interim dump file (or doing unzip and import), unix has the ability to pipe the output from one program (such as exp) as input to another program (such as gzip) as they both run in parallel, without having to run the programs sequentially and without having to create interim files.

I Hope this article helped you to export using mknod.Suggestions are welcome.

Best Regards
RajaBaskar Thangaraj

17 comments:

  1. hi raj, your blog is very informative ..

    please get me more details about MKNOD and role of it during exp/imp operation..

    thanks for doing nice kinda effort

    ReplyDelete
  2. hi rajbasker,

    very glad to be part of your blog which is very most informative for us. and very thanks for sharing your
    valuable time with us..

    kindly get me the list of most known error you see in alert log file ..
    and then detailed information about mknod utility

    ReplyDelete
  3. Dear Basker,

    Once again i got a good information from your blog. Nice yarr. Keep it up.

    naresh.

    ReplyDelete
  4. Babu/sundar

    I have updated the "USING MKNOD DURING EXPORT" article.

    Thanks & Regards
    Rajabaskar

    ReplyDelete
  5. Dear Raja, If you can add comments for every line of scripts, like what it does, that would be more explanatory and informative.

    Eg:

    exp un/pw file=eee log=iii # command for export.

    zip fname.dmp # zipping/compressing files.

    ReplyDelete
  6. Raja,

    Thanks for the nice article.

    When I tried to test this script, it hanged.

    What could be the reason?

    Thanks,
    Rajesh

    ReplyDelete
  7. Hi Rajesh,

    Thanks for your visit..

    please try the below script


    cat exp.sh
    mknod exp_pipe p
    gunzip < schema_1817.dmp.gz > imp_pipe &
    exp \'/ as sysdba\' file=exp_pipe buffer=100000000 owner=test log=exp_schema09182009.log feedback=10000 statistics=none

    ------------------------------------------------

    cat imp.sh
    mknod imp_pipe p
    gunzip < schema_1817.dmp.gz > imp_pipe &
    imp \'/ as sysdba\' file=imp_pipe buffer=100000000 fromuser=test touser=test1 log=imp_schema09182009.log feedback=10000 ignore=n commit=y



    ------------------------------------------------
    Regards
    Rajabaskar Thangaraj

    ReplyDelete
  8. Hi Raj,

    This info was really helpful as beginer.
    Thanks so much for thi ....

    Regards,
    Das

    ReplyDelete
  9. hi brother!
    its nice info...
    really superb......

    ReplyDelete
  10. Hello ,

    I have a query here.
    When we use the pipe method for performing the import. Is there any restriction or limit on the size ?
    Earlier I had used pipe for importing from a dmp.z file which was huge , it caused an error
    IMP 00008, today with some other schema also, IMP failed with the same error. It had worked earlier for this schema but failed today.
    What seems to me is that there is some size limit which restrcits it.

    Can you comment on this ?

    ReplyDelete
  11. Hi,

    Please post your commands..


    Regards
    Rajabaskar Thangaraj

    ReplyDelete
  12. Hi,

    Please try this..

    please try the below script


    cat exp.sh
    mknod exp_pipe p
    gunzip < schema_1817.dmp.gz > imp_pipe &
    exp \'/ as sysdba\' file=exp_pipe buffer=100000000 owner=test log=exp_schema09182009.log feedback=10000 statistics=none

    ------------------------------------------------

    cat imp.sh
    mknod imp_pipe p
    gunzip < schema_1817.dmp.gz > imp_pipe &
    imp \'/ as sysdba\' file=imp_pipe buffer=100000000 fromuser=test touser=test1 log=imp_schema09182009.log feedback=10000 ignore=n commit=y


    Regards
    Rajabaskar Thangaraj

    ReplyDelete
  13. Hi,

    I agree with your point but will it be possible in EXPDP. Is there is any feature for taking backup with parallel compression of dump file.I know it is possible in exp but i want to know is it be possible in expdp.I have googled and found that there is some feature in 11g but it is costly.

    ReplyDelete
  14. Hi Vino,

    Thanks For writing in..In expdp, we have use both parallel and Compression parameter in oracle 11g.

    Thanks
    Rajabaskar Thangaraj

    ReplyDelete
  15. hi raja,
    after creating the the .sh file .. how to execute the file.

    ReplyDelete
  16. Hi,
    can we use datapump using mknode .
    how to execute after creating the bash file;

    ReplyDelete
    Replies
    1. Hi Raf,

      Thanks for writing in..

      can we use datapump using mknode?

      No. We can't..

      how to execute after creating the bash file?

      In .bash_profiile,we have set the environment variable for particular instance.

      instead of bash profile, you can set below variable and run the shell script..

      export ORACLE_HOME=/app/oracle/product/11.1.0
      export PATH=$ORACLE_HOME/bin:$PATH
      export ORACLE_SID=test

      Regards
      Rajabaskar

      Delete