Home » RDBMS Server » Server Utilities » Data Pump expdp to ASM daily (OEL 5.5 , Oracle 11.2.0.2.0)
Data Pump expdp to ASM daily [message #512048] Thu, 16 June 2011 13:37 Go to next message
s197oo302
Messages: 50
Registered: January 2011
Location: seoul
Member

i succeeded to expdp to ASM diskgroup such as
create directory asmexpdir as '+RECO/FILTDB/EXPDP';
grant read,write on directory asmexpdir to oraasfs;
expdp oraasfs/oraasfs2301 directory=asmexpdir dumpfile=SBSR_EXP.dmp tables=TM_SFS_CUST_01 logfile=EXPDP_LOG:SBSR_EXP.log


SUCCESS MESSAGE
. . exported "ORAASFS"."TM_SFS_CUST_01"                  387.2 MB  817684 rows
Master table "ORAASFS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ORAASFS.SYS_EXPORT_TABLE_01 is:
  +RECO/filtdb/expdp/sbsr_exp.dmp
Job "ORAASFS"."SYS_EXPORT_TABLE_01" successfully completed at 03:34:59


And I like to run this daily and delete after 14 days.
but it show error, what can be the solution to run this script?
#!/bin/bash
#Script to Perform Datapump Export backup Every Day
################################################################
#Change History
#================
#DATE AUTHOR s970302@gmail.com LAST-CHANGE
#--------- ----------------------- --------------------------------- -----------
#16-JUN-2011 Michael Wu SCRIPT FOR FULL EXPORT filtdb 01-JUN-2011

################################################################
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=filtdb1
export PATH=$PATH:$ORACLE_HOME/bin;/u01/app/common/oracle/sql:.:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin
expdp oraasfs/oraasfs2301@FILTDB dumpfile=CUST-`date '+%d%m%Y_%H%M%S'`.dmp directory=asmexpdir logfile=EXPDP_LOG:CUST-`date '+%d%m%Y_%H%M%S'`.log EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=CUST_EXPDP_FILTDB tables=TM_SFS_CUST_01

############################################################################
#Removing 7 days old dump files
find +RECO/FILTDB/EXPDP -mtime +13 -exec rm {} \;
######################################################################################



ERROR MESSAGE
 sh expdp.sh
expdp.sh: line 16: /u01/app/common/oracle/sql:.:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin: No such file or directory

Export: Release 11.2.0.2.0 - Production on Fri Jun 17 03:31:41 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
Re: Data Pump expdp to ASM daily [message #512059 is a reply to message #512048] Thu, 16 June 2011 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68045
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what can be the solution to run this script?

Check why:
Quote:
Unable to open the log file


Regards
Michel
Re: Data Pump expdp to ASM daily [message #512065 is a reply to message #512048] Thu, 16 June 2011 14:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
same expdp parameters with asm works for me (just the expdp).
Just copied your post and changed.
Even from a cronjob.

the EXPDP_LOG directory should be outside ASM. I suppose, you have already did that as it worked before?
your environment variables are little messed, but it has nothing to do with the error you have.
Re: Data Pump expdp to ASM daily [message #512074 is a reply to message #512065] Thu, 16 June 2011 15:32 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
Hi - there is a semicolon on your PATH setting, that might cause some issues. And (unrelated to your reported problem) this

find +RECO/FILTDB/EXPDP -mtime +13 -exec rm {} \;

will not work, you cannot use file system utilities on an ASM device, because it doesn;t have a file system.
I would try a much simpler, fully qualified, log file name.

Hope this helps.
John.

[update: no, I just remembered, the log file does have to be in an Oracle directory, I think?]

[Updated on: Thu, 16 June 2011 15:37]

Report message to a moderator

Re: Data Pump expdp to ASM daily [message #512382 is a reply to message #512074] Mon, 20 June 2011 00:09 Go to previous messageGo to next message
s197oo302
Messages: 50
Registered: January 2011
Location: seoul
Member

I am succesful with DATA_PUMP_DIR, I don't know what's difference with EXPDP_LOG directory and I can't find any log saved in DATA_PUMP_DIR. something is wrong but I finish EXPDP with script.
chmod also same drwxrwxrwx 2 oracle oinstall 4096 Apr 5 02:23 log(DATA_PUMP_DIR), drwxrwxrwx 2 oracle oinstall 4096 Jun 17 03:50 EXPLOG(EXPDP_LOG)
I might utilize this script but I like to found out where is error.
Thank you.

SQL> select * from dba_directories 
  2  where upper(directory_name) = 'EXPDP_LOG';

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            EXPDP_LOG
/home/oracle/EXPDP/EXPLOG


SQL> select * from dba_directories 
  2  where upper(directory_name) = 'DATA_PUMP_DIR';

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            DATA_PUMP_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/

[Updated on: Mon, 20 June 2011 00:09]

Report message to a moderator

Re: Data Pump expdp to ASM daily [message #512389 is a reply to message #512382] Mon, 20 June 2011 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68045
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We only have what you post, you don't post anything that allows us to know where is the problem
because you only post what you check yourself and think it is enough to check. As you don't copy
and paste what you did you even don't know if your interpretation of what you see is correct.

So copy and paste the following.
At OS level:
cd /home/oracle/EXPDP/EXPLOG
ls -ld /home/oracle/EXPDP/EXPLOG
ls -l /home/oracle/EXPDP/EXPLOG
df .

At SQL level
select table_name, grantee, privilege
from dba_tab_privs
where table_name in ('EXPDP_LOG', 'DATA_PUMP_DIR')
/
select granted_role, default
froml dba_role_privs
where grantee=upper('oraasfs')
/
select priviege
from dba_sys_privs
where grantee=upper('oraasfs')
/


Regards
Michel
Re: Data Pump expdp to ASM daily [message #512396 is a reply to message #512389] Mon, 20 June 2011 02:01 Go to previous messageGo to next message
s197oo302
Messages: 50
Registered: January 2011
Location: seoul
Member

I want to use /home/oracle/EXPDP as my log directory as expdplog.
and the Result is


OS level:
[oracle@filtdb01 EXPDP]$ ls -ld /home/oracle/EXPDP/
drwxrwxrwx 2 oracle oinstall 4096 Jun 20 14:25 /home/oracle/EXPDP/
[oracle@filtdb01 EXPDP]$ ls -l /home/oracle/EXPDP/
total 0
[oracle@filtdb01 EXPDP]$ df .
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VGExaDb-LVDbSys1
                      30963708  14504340  14886504  50% /
SQL LEVEL:
SQL> select table_name, grantee, privilege
  2  from dba_tab_privs
  3  where table_name in ('EXPDPLOG', 'DATA_PUMP_DIR');

TABLE_NAME                     GRANTEE                        PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
DATA_PUMP_DIR                  EXP_FULL_DATABASE              READ
DATA_PUMP_DIR                  EXP_FULL_DATABASE              WRITE
DATA_PUMP_DIR                  IMP_FULL_DATABASE              READ
DATA_PUMP_DIR                  IMP_FULL_DATABASE              WRITE
EXPDPLOG                       ORAASFS                        READ
DATA_PUMP_DIR                  ORAASFS                        READ
EXPDPLOG                       ORAASFS                        WRITE
DATA_PUMP_DIR                  ORAASFS                        WRITE

SQL> select granted_role, default_role
  2  from dba_role_privs
  3  where grantee=upper('oraasfs')
  4  ;

GRANTED_ROLE                   DEF
------------------------------ ---
DATAPUMP_EXP_FULL_DATABASE     NO
EXP_FULL_DATABASE              NO
DBA                            YES
CONNECT                        YES
DATAPUMP_IMP_FULL_DATABASE     NO
RESOURCE                       YES
TT_CACHE_ADMIN_ROLE            YES

SQL> select privilege
  2  from dba_sys_privs
  3  where grantee=upper('oraasfs')
  4  ;

PRIVILEGE
----------------------------------------
CREATE ANY TRIGGER
SELECT ANY DICTIONARY
ALTER ANY OUTLINE
CREATE VIEW
CREATE ANY TABLE
CREATE TABLE
CREATE ANY PROCEDURE
UPDATE ANY TABLE
INSERT ANY TABLE
CREATE SESSION
EXECUTE ANY TYPE

PRIVILEGE
----------------------------------------
EXECUTE ANY PROCEDURE
SELECT ANY TABLE
MANAGE TABLESPACE
ALTER TABLESPACE
CREATE ANY TYPE
CREATE TRIGGER
ALTER ANY TABLE
UNLIMITED TABLESPACE
CREATE ANY OUTLINE
CREATE PROCEDURE
CREATE DATABASE LINK

PRIVILEGE
----------------------------------------
CREATE TABLESPACE



Thank You

[Updated on: Mon, 20 June 2011 02:16]

Report message to a moderator

Re: Data Pump expdp to ASM daily [message #512528 is a reply to message #512396] Mon, 20 June 2011 19:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>export PATH=$PATH:$ORACLE_HOME/bin;/u01/app/common/oracle/sql:.:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin
In my opinion there is a problem with the line above, but it does not seem to be impacting anything for now
There is a semicoln just to the right of "$ORACLE_HOME/bin".

>expdp oraasfs/oraasfs2301@FILTDB dumpfile=CUST-`date '+%d%m%Y_%H%M%S'`.dmp directory=asmexpdir
>logfile=EXPDP_LOG:CUST-`date '+%d%m%Y_%H%M%S'`.log
"EXPDP_LOG" is wrong here. It needs to be replaced with a valid OS file system pathname; not Oracle internal object_name.
Re: Data Pump expdp to ASM daily [message #512529 is a reply to message #512528] Mon, 20 June 2011 19:31 Go to previous messageGo to next message
s197oo302
Messages: 50
Registered: January 2011
Location: seoul
Member

I change PATH, but still the same error.
And with OS file system pathname, it said below error. I think directory name is right for here, but some privilege problem, currently I assumed.

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name / is invalid

export PATH=$PATH:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/11.2.0/grid/bin:/u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin:/bi n:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/u01/app/oracle/product/11.2.0/dbhome_1/OPatch:/u01/app/11.2. 0/grid/bin:/opt/oracle.SupportTools:/opt/oracle.SupportTools/ibdiagtools:/opt/oracle.SupportTools/onecommand:/usr/local/bin:/bin:/usr /bin:/usr/X11R6/bin:/usr/kerberos/bin:/u01/app/oracle/product/11.2.0/dbhome_1/lib:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin :/usr/bin:/root/bin:/bin:/usr/bin:/root/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/u01/app/oracle/product/11.2.0/dbhome_1/OPatc h:/u01/app/11.2.0/grid/bin:/opt/oracle.SupportTools:/opt/oracle.SupportTools/ibdiagtools:/opt/oracle.SupportTools/onecommand:/usr/loc al/bin:/bin:/usr/bin:/usr/X11R6/bin:/usr/kerberos/bin
Re: Data Pump expdp to ASM daily [message #512530 is a reply to message #512529] Mon, 20 June 2011 19:56 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
expdp dbadmin/admindb directory=LOG_FILE_DIR dumpfile=test.dmp logfile=test.log schemas=dbadmin

Command line above produced results below
bcm@bcm-laptop:~$ ls -ltr /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
total 540
-rw-r----- 1 oracle dba   1573 2010-05-07 11:19 ext_1v3.log
-rw-r----- 1 oracle dba 540672 2011-06-20 17:54 test.dmp
-rw-r--r-- 1 oracle dba   2942 2011-06-20 17:54 test.log


It is trivial to test the basics. First make it work; then make it fancy!
Previous Topic: SQL Loader Error Message
Next Topic: importing 9i dump into 10g
Goto Forum:
  


Current Time: Sun Dec 05 18:01:52 CST 2021