Solved

to exportusing script

Posted on 2014-02-26
22
258 Views
Last Modified: 2014-11-26
Here is the script to export schema name is  RRI, my directory is /backup6
how will I modify this script for the schema RRI  and location /backup6

script:

 cat expdp_sch.sh*
#!/bin/sh

set -x
. /ldba/set_coreprod.sh

NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
export NLS_LANG

USER=$1
#USER=SPAPP

LDBAPSWD=/ldba/${SERVER_ABBREV}/bin/.ldbapswd
LDBAPSWD=${LDBAPSWD}.${ORACLE_SID}
PASSWORD=`grep -w SYSTEM $LDBAPSWD | awk '{print $2}'`

expdp SYSTEM/${PASSWORD} DIRECTORY=DUMP_EXADATA DUMPFILE=expdp_${ORACLE_SID}_${USER}_%U.dmp PARALLEL=4 FILESIZE=4G JOB_NAME=expdp_job_${USER} SCHEMAS=$USER LOGFILE=expdp_${ORACLE_SID}_${USER}.log EXCLUDE=STATISTICS

exit 0
0
Comment
Question by:thomasliju
  • 6
  • 6
  • 5
  • +2
22 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39889038
I do not understand the question:

Here is the script to export schema name is  RRI, my directory is /backup6
how will I modify this script for the schema RRI  and location /backup6

If that is the script for RRI and /backup6, then there is no need to modify it?
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 375 total points
ID: 39889054
You are currently, in this script, exporting a schema represented by the environment variable $USER:

>>SCHEMAS=$USER

The script is defined for $USER to be the first parameter when the script is called (that's what $1 is):

>>USER=$1

The destination for the export is an Oracle directory called DUMP_EXADATA

>>DIRECTORY=DUMP_EXADATA

From within SQLPLUS, check the defintion of that directory:

select * from dba_directories;

Where is DUMP_EXADATA pointed ?   To /backup6 ?  If so, you're good.  If not, create a new directory pointing there and modify the call to expdp accordingly.

create directory NEWDIR as '/backup6';
grant read, write on directory NEWDIR to SYSTEM;

(Since SYSTEM is who the script is exporting under).

Also:  Don't use EXPDP as your sole means of backup.  If this is an export for some other reason, then fine.  If it's meant as your primary means of backing up this data you may find yourself in trouble down the line, since you can't use an export for point in time recovery.  If this is some form of static data that never changes, then you'll probably be OK.  Just be aware of the limitation of exp/expdp as the primary means of backing up data.
0
 

Author Comment

by:thomasliju
ID: 39889075
SYS@sprefuat1> select * from dba_directories;


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            DUMP_FILES2
/backup/ora_export

SYS                            DATA_PUMP_DIR
/ora102_home/app/oracle/product/10.2.0/rdbms/log/

SYS                            DUMP_FILES
/ora_export


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            WORK_DIR
/ora102_home/app/oracle/product/10.2.0/work

SYS                            ARROWGB
/local/apps/cmp01/ext_link

SYS                            FDE_DS
/glpfeeds/input


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            SNP_MANUFACTURE
/glpfeeds/output

SYS                            GLP_MANUFACTURE
/glpfeeds/output

SYS                            GLP_DATA_LOAD
/glpfeeds/input


OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/ora_home/app/oracle/product/10.2.0/ccr/state

SYS                            TMP
/tmp


11 rows selected.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39889084
I see no directory named DUMP_EXADATA.

You need to create it pointing to /backup6.

create or replace directory DUMP_EXADATA as '/backup6';
0
 
LVL 23

Expert Comment

by:David
ID: 39889126
Thomas, it helps me to think of the expdp directory as an alias or variable, rather than a file system.  However, the file system that the alias resolves to, must have the appropriate read-write O/S permissions.  What Steve mentions in (39889054) about the read & write is internal to Oracle. You need both internal rights, and external.
0
 

Author Comment

by:thomasliju
ID: 39889134
These are the schemas to export..
 @/ldba/10gR2_migration_to_exadata/gen_export_schemas.sql
nohup expdp_sch.sh AUGEAS &
nohup expdp_sch.sh BLS &
nohup expdp_sch.sh CAPIQ &
nohup expdp_sch.sh CAPIQ_INCR &
nohup expdp_sch.sh CGS_ETL_USER &
nohup expdp_sch.sh CLARITAS &
nohup expdp_sch.sh COMPUSTAT &
nohup expdp_sch.sh COMUTIL &
nohup expdp_sch.sh COMUTIL_ARCHIVE &
nohup expdp_sch.sh COMUTIL_CDC &




If I want to export this CAPIQ schema..

What should be the modified script..
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39889140
Looks like you've already got this exporting CAPIQ:

>>nohup expdp_sch.sh CAPIQ &

All the "nohup" and "&" are doing is spawning the job to run in the background and allowing the initiating user to logoff if necessary without killing the job.

The "expdp_sch.sh CAPIQ" is what is doing the work.
0
 

Author Comment

by:thomasliju
ID: 39889158
So in order to export CAPIQ
I have to do
nohup expdp_sch.sh CAPIQ &

in command line
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 375 total points
ID: 39889172
Assuming you've created a directory inside Oracle pointing to the place you want the export sent to, granted read/write to the user running the export (according to the docs the user creating the directory has read/write on it, so if you create as system and export as system it should have those permissions) and assuming the oracle user at the operating system level has write permissions to /backup6 then you should be good to go.

The logfile of the job will be in /backup6 and will be called what's specified in the LOGFILE directive:

>>LOGFILE=expdp_${ORACLE_SID}_${USER}.log
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:thomasliju
ID: 39889199
so logout from sql
come to os
and
run the command
nohup expdp_sch.sh CAPIQ &
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39889253
Correct.
0
 

Author Comment

by:thomasliju
ID: 39889258
but you may see this.


nj09mhf0362[oracle:sprefuat1] 40: nohup expdp_sch.sh TULLETT &
[1] 15940
nj09mhf0362[oracle:sprefuat1] 41: + . /ldba/set_coreprod.sh
expdp_sch.sh: /ldba/set_coreprod.sh: not found
^Z
[1]    Exit 1               expdp_sch.sh TULLETT
nj09mhf0362[oracle:sprefuat1] 41: nohup expdp_sch.sh TULLETT &
[1] 18771
nj09mhf0362[oracle:sprefuat1] 42: + . /ldba/set_coreprod.sh
expdp_sch.sh: /ldba/set_coreprod.sh: not found

[1]    Exit 1               expdp_sch.sh TULLETT
nj09mhf0362[oracle:sprefuat1] 42: cd /ldba
/ldba
nj09mhf0362[oracle:sprefuat1] 43: ls
10gR2_migration_to_exadata/   fix.sql                       set_cgsods11.csh*             set_cgsodsui.csh*             set_sprefuat1.sh@
afiedt.buf                    fix.txt                       set_cgsods11.sh*              set_cgsodsui.sh*              set_sprefuat.csh*
analyzefinmaster.sql          foo                           set_cgsodspr.csh*             set_cgsodsut1.csh@            set_sprefuat.csh.before_rac
chk_all_invalid.log           libobk.so64.1@                set_cgsodspr.sh*              set_cgsodsut1.sh@             set_sprefuat.sh*
ck_obj.lst                    log/                          set_cgsodsqa1.csh@            set_cgsodsut.csh*             set_sprefuat.sh.before_rac
com/                          lost+found/                   set_cgsodsqa1.sh@             set_cgsodsut.sh*              shrink.sql
control.bkp                   mhf0362/                      set_cgsodsqa.csh*             set_rman_rman10g.sh*          shrink.sql.fin_master_ts
copy.txt                      null/                         set_cgsodsqa.sh*              set_sprefprd.csh              shrink.sql.tab
dead.letter                   oradiag_oracle/               set_cgsodsui1.csh@            set_sprefprd.sh               tmp/
exp_script/                   rc_syn.log                    set_cgsodsui1.sh@             set_sprefuat1.csh@
nj09mhf0362[oracle:sprefuat1] 44: cd 10gR2_migration_to_exadata/
/ldba/10gR2_migration_to_exadata
nj09mhf0362[oracle:sprefuat1] 45: ls
afiedt.buf                      create_public_db_links.exe      create_tablespaces.exe          idx_tablespaces.sql             recreate_dblink_with_pswd.exe
alter_user.exe                  create_public_db_links.sql      create_users.exe                impdp_sch.sh*                   role_grants.exe
alter_user.sql                  create_public_synonyms.exe      create_users.sql                job_sync.exe                    role_grants.sql
char_conv/                      create_public_synonyms.sql      exp_all_sch.sh*                 job_sync.sql                    sys_privs.exe
chg_idx_tablespace.sql          create_quotas.exe               exp_all_syns_sch.sh             logs/                           sys_privs.sql
chg_noparallel.sql              create_quotas.sql               expdp_pbc_tables.sh*            object_counts.lst.new           sys_tab_privs.exe
ck_obj.lst                      create_roles.exe                expdp_sch.sh*                   object_counts.sql               sys_tab_privs.sql
create_dblink.exe               create_roles.sql                extract_tablespaces.sql         object_counts.sql.new           system_tab_privs.exe
create_profiles.exe             create_schema_owners.exe        gen_export_schemas.sql          rc_dblink.sql                   system_tab_privs.sql
create_profiles.sql             create_schema_owners.sql        gen_export_syns_schemas.sql     rec_dblink.sql
nj09mhf0362[oracle:sprefuat1] 46:
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 39889273
>>expdp_sch.sh: /ldba/set_coreprod.sh: not found

OK, from the ls command, that doesn't exist. We cannot help you with that since that is not a default Oracle script.

>>nohup expdp_sch.sh TULLETT &

For testing don't use nohup and &

Just use:
expdp_sch.sh TULLETT
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 375 total points
ID: 39889276
For a start, your script "./ldba/set_coreprod.sh" seems to be missing.

The script is assuming that from the OS level directory you're running expdp_sch.sh from, there's a directory there called ldba and within that directory is another script called set_coreprod.sh

You should make sure that exists.  That's what your script is complaining about at the moment.

I guess it begs the question, where did you get the script expdp_sch.sh from ?  Is it something  you've grabbed from the internet or something that was already on the machine.

We can tell you that it's missing, but have no idea what it does since it appears to be a custom script.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40374145
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40374146
thomasliju is under suspension for multiple accounts and has been instructed to come back and close ALL his open questions in ALL accounts before the suspension is lifted.
0
 

Author Comment

by:thomasliju
ID: 40383676
nohup expdp_sch.sh CAPIQ &

yes nohup is helped.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40384036
thomasliju,

The original question was:
how will I modify this script for the schema RRI  and location /backup6

I fail to see how "nohup expdp_sch.sh CAPIQ &"  answered the question asked.

Please close your questions properly.  I will have a Moderator take a look at this question.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40398131
I feel the asker just wanted to modify an existing script to add a new schema to it and to a new location.  Then it morphed into a different question where they didn't understand the environment (missing files and such).

Based on that I feel the following posts would be enough to answer it:
http:#a39889054
http:#a39889172
http:#a39889273
http:#a39889276

Given the history of this particular member, I want them to come back and answer it properly and not have Cleanup or Moderators answer it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now