| Data Pump expdp to ASM daily [message #512048] |
Thu, 16 June 2011 13:37  |
 |
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 #512074 is a reply to message #512065] |
Thu, 16 June 2011 15:32  |
John Watson
Messages: 8962
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 |
|
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 #512396 is a reply to message #512389] |
Mon, 20 June 2011 02:01 |
|
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 #512530 is a reply to message #512529] |
Mon, 20 June 2011 19:56 |
 |
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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
