exp system/abc@c file=c:dmp_%date%.dmp log=c:log.txt owner=xyz
I run this script through batch file its working.Problem is dump file showing with this format C:dmp_wed.dmp.I want to date format like that C:dmp_18052011.dmp.
for date formatting.How I can add date format in a batch file.
i get flat file and i have set up a control M job so that it runs at a particular time.
initially my control file was as below:
LOAD DATA
INFILE 'DFILEabcd.dat'
BADFILE 'BDabcd.bad'
REPLACE
INTO TABLE abcd_table
(A position(01:09) CHAR,
B position(11:12) CHAR,
C position(14:33) CHAR,
D position(37:50) char)
this was working fine. control M did not send FAIL message.but later i had to change the ctl file due to requirement. i had to add a when clause.
my code after modification is:
INFILE 'DFILEabcd.dat'
BADFILE 'BDabcd.bad'
REPLACE
INTO TABLE abcd_table
when A<>'10'
(A position(01:09) CHAR,
B position(11:12) CHAR,
C position(14:33) CHAR,
D position(37:50)
now the control M is sending an erro message after it runs the job. error is Return 5. thats all it gives.
i think it is due to errorlevel 1. in log file it says zero records inserted due to data error. then what is causing control M to send fail message??
sqlloader is loading all the required records correctly.
I am trying to setup incremental backup on my windows OS based server using RMAN command in batch file. When I use batch file in OS scheduler it is working fine, when I am calling same batch file from my LOCAL desktop PC it throws errors as below.
D:> \3.193.211.19sgdba
mankp_acressit.bat
D:>rman catalog rman/******@acressit target / cmd
file=E:sgdba
mankp_arch.rcv log E:sgdba
mansit_arch_rman_backup.log
RMAN-00557: could not open MSGLOG "E:sgdba
[code]....
Both single and double quotes (' or ") are accepted for a quoted-string.
Quotes are not required unless the string contains embedded white-space.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00556: could not open CMDFILE "E:sgdba
mankp_arch.rcv"
[code]....
* on my DB server I am login using my administrator account, on my PC I dont have admin account.
* I have checked remote execution using local user as well as admin user.
* I have checked the permission and my local ID & EVERYONE has all permission in that folder.
execute a BATCH File through PLSQL.
BEGIN
dbms_scheduler.Create_schedule(schedule_name => 'RMAN_TICKER_STARTING',
repeat_interval => 'FREQ=DAILY;BYHOUR=9; BYMINUTE=15,30,45,59',
comments
=> 'schedule to run daily');
dbms_scheduler.Create_program (program_name => 'TICKER_PROGRAME',
[code]....
It was created successfully, but when I execute, it shows the error message.
BEGIN
dbms_scheduler.Run_job ('RMAN_TICKER_JOB', TRUE);
END;
begin
dbms_scheduler.run_job (
'RMAN_TICKER_JOB',TRUE);
end;
Error at line 1
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
[code]....
But I have the CMD file in the location - "F:FEEDLGRTOOLSfeedlgr.cmd".
I made small Inventory software for Medical store. Now I want daily base data in DMP file. How to make current date in DMP file don't need all.
I mean I have 30 tables in oracle sql . They are daily update with new entry and some table has date column and some not. Actually I want to send daily Data via mail.
Is there a way to know what kind of Backup (table/tablespace/full/schema) by looking at export dump file ? If yes, can you tell me the command ?
View 1 Replies View RelatedI have one doubt on Expdp & RMAN. Do EXPDP utilities does backup at block level as what RMAN is doing? Which one is faster, expdp or RMAN?
View 16 Replies View Relatedhow export is called as logical backup ? If export is logical backup then what is hot backup .Can hot backup be called as logical backup.
View 2 Replies View RelatedI am trying to have sqlldr running against a file:
C:oratest20080318
Is it possible I get SQL*Loader-500: Unable to open file and 503 file not found just because the file name does not have an extension?
I can see that any file name I try it looks after whateverFileName.dat! Is there a way to have sqlldr working with files that do not have extensions?
I am executing multiple PL/SQL files(.sql) with single batch file. The batch file sql.bat has got 3 sub sql sub-tasks to complete once its run. The sql.bat is show below
@Echo off
CD C:Report
echo Loadin tables from text file Report.txt
sqlplus security/password <c:Reportloader_security.sql
echo Creating Security table
sqlplus security/password <c:Reportcreating_security_final.sql
echo Inserting text file Security table
sqlplus security/password <c:Reportinsert_security_final.sql
PAUSE
The sql.bat runs perfectly if I double click on the sql.bat file separately. But if I call the sql.bat from a different batch file 'Final.bat' it throws the below error.
Error
-----------
Executing SQL commands and loading file into SQL tables
Loadin tables from text file Report.txt
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[code].....
The Final.bat file calls other bat files too. It is as show below.
CD C:ReportSecurity
echo Merging all Files
CALL merge.bat
CD C:ReportSecurity
echo Deleting old files
CALL del.bat
CD C:ReportSecurity
echo Executing SQL commands and loading file into SQL tables
CALL sql.bat
I have a .bat file in my client system,which will open a web page after executing(after double clicking on it).I want to execute the same batch file from my pl/sql block.So,after executing my pl/sql block that .bat file should execute,and it should open the same web page.
View 12 Replies View RelatedI am trying to create a batch file which will be executed with windows scheduled task. This batch file will have sqlplus script running Oracle query. I can run this query from the command prompt, no problem,
This is the sqlplus scipt to run query:
C:\Documents and Settings\testuser>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 1 19:54:25 2010
Copyright © 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: TEST1
Enter password:
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
TEST1@DB> spool testuserfile
here goes my query
TEST1@DB> spool off
TEST1@DB> exit
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production
C:\Documents and Settings\testuser>
How do I put this in a batch file. I was able to figure how to logon, but I get stuck at the spool command. My batch file starts like this:
cd C:\Documents and Settings\testuser
sqlplus TEST1/Password
I create a form with button....whenever i click button my batch file should be run..
My batch file code :
@ECHO OFF
cd
ipconfig /all
pause
When-button-pressed trigger :
======================================
client_host('D: est_ipa.bat');
When i double click .bat file, it will get proper info.But when i call this .bat file thru form, it will show blank screen..why this .bat file not running ?
I am trying to create a .bat file where I need to take inputs for below variables
DNAME
UNAME
PSWD
So I have done like this
SET /p DNAME="Enter the Database Name "
SET /p UName="Enter the Username "
SET /p Pswd="Enter the Password "
Now I need to validate whether I am able to establish the connection
sqlpus -s "%UName%/%Pswd%@%DNAME%"
if the above connection is valid one then perform certain action else need to echo user that Access is denied.
(ex: I am trying to say if cond is valid
stmt-1
else
echo message to user. )
I want to call .bat file from oracle froms 10g.
I tried given below code but failed.
---------------------------------
declare
v1 varchar2(200);
begin
v1 := 'D:FolderLogicalbackup.bat';
HOST('cmd /c start'||v1,no_screen);
--HOST(v1);
end;
-----------------------------
The Logicalbackup.bat file contains:-
Cd d:oracleproduct10.2.0db_1in
d:
exp mw6/mw6@mw file=d:mw6ackupackupRGLHR.dmp log=d:mw6ackupackuplogRGLHR.log
I am running Oracle Database 10g R2 on windows 2003, I want to create a batch file to check if the database is idle or not, and if it is idle shut it down and start it up.
View 13 Replies View RelatedI have a particular sql code which works perfectly fine on sql developer. But if I run the same sql code through a batch file it does not get executed. It does not throw an error too.
SQL code - clean_tables.sql
begin
execute immediate 'drop table external_tables';
execute immediate 'drop table security';
exception
when others then
null;
end;
Batch file - Clean.bat
set ORACLE_SID=orcl
set ORACLE_HOME=C:oracleproduct11.2.0dbhome_1
set PATH=C:oracleproduct11.2.0dbhome_1BIN
sqlplus security/password@orcl <c:Reportclean_tables.sql
pause
I have a backup of Oracle Database 10g (business_bk.dmp). Now I would like to import it into Oracle DB 9i.
View 9 Replies View RelatedWhen I am trying to get backup the following error occurred in a specific table.
Error Description:
ORA-00056 is means DDL lock on object 'string.string' is already held in an incompatible mode.
How can I resolve this.
Is the tablespace actually off-line when doing a user-managed hot backup? I know the data blocks are copied into a redo stream but I am not sure if that means it(tablespace) is actually on-line or off-line.
View 6 Replies View RelatedI have a batch file Menu.bat which should pass accept the input parameters from the end users and pass the entered values to a different batch file.
After entering option 2 the user should be prompted with an option to enter the path. E.g. /shared/folder.
Now '/shared/folder' should be passed to a different batch file named R.bat
Below is the Menu.bat file.
@echo off
color 00
title Security Audit Adaptor
:main
cls
echo //MAIN MENU\
echo.
echo 1. Got to Google
echo 2. Enter path
[code].......
From within a DOS batch file I make the following call:
sqlplus [User]/[password]@[server] @batch_script.sql [Server] [User]
Within the file batch_script.sql I have the following lines:
spool output_batch.lst
connect &&2/[password]@&&1;
@SomeOtherFile.sql
I want to change the last line to something like this:
if &&1 = 'Something' then
@SomeOtherFile1.sql
else
@SomeOtherFile2.sql
end if;
I get the following result
SQL> if &&1 = "something" then
SP2-0734: unknown command beginning "if &&1 = "..." - rest of line ignored.
I took a shot at using the "If" statement and missed.
How to execute the SQLLDR, where Data File Reside in another Server?
View 1 Replies View RelatedI have a requirement to read flat text file(around 15000 lines) residing at a client location from DB server and write into a table in One cell.
I tried UTL_FILE and DBMS_LOB but, i am not able to access client location to read the file as it reads path from Oracle Directory.
eg.
my client path is 198.168.1.1 and my DB server is in unix say 192.168.1.10.
file location is: \192.168.1.1shareabc.txt
So I created One Oracle directory as MY_DIR having DIRECTORY_PATH as '\192.168.1.1share'.
But both UTL_FILE and DBMS_LOB is not able to access the file.
Error Message:
-------------
Unable to process CLOB -22288 ~ ORA-22288: file or LOB operation FILEOPEN
failed
No such file or directory
Few Details for reference:
-------------------------
File Location: \192.168.1.1shareabc.txt
Unix DB Server location: 192.168.1.10
Table : Test (filename varchar2(30), Content CLOB)
Oracle Dir: MYDIR
Directory_Path: \192.168.1.1share
i am using a schema which i need to take a backup of meta data only i am using exp utility
exp shan/shan@shan file=/backup_dump/shan.dmp log=/backup_dump/shan.log owner=shan rows=n
but it will return me below error, i have only access to user shan our client cant allow me to use system or sysdba schema or any other required grants or privileges so is there any way to take metadata backup of user shan from user shan.
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
How to backup the tables in a schema? I need som halp on this subjekt. I hav Dev envrnment that I must to backup.
View 9 Replies View RelatedWe are doing daily cold backup. Due to lack of disk space,we couldn't Hot backup. We want our database to be up when doing backups. Since only export/import is possible in our scenario, clarify few queries:
1) Export was done during off period from the live server.
2) We have a development server, in which we have to update our database daily. Can i overwrite the Development server using IMPORT daily? Since this import might show lots of errors (Object already exist), what parameters can i use for import.
why datapump is faster than normal exp ? one ans that i know is dp use block mode and exp use byte mode . is there any other major reason? if say i have database of size 10g and want to take datapump backup, but condition is that i can take dumpfile of size 2g only. there is any way to take full backup of database in part wise .
View 1 Replies View RelatedI am trying to create a silent install as part of a batch file that does other things after. I'm running into the following issues
1) I need the batch file to wait until the install is done to continue. Trouble is that running setup with a response file opens up a new windows to run the install. I tried the wait command but doesn't work right either
2) Same issue with DBCA and a response file.
Any way to run the silent installs and have the batch file wait until the install is done and then continue? The less user intervention required, the better.
How to call a batch file existing in network path through oracle forms 6i. I am using following code in Forms 6i which is supposed to FTP some .text files to a UNIX box.
Host( '\guww.netdfs.root.Global Shared Data$(LEV)Leven Shared DataLeven Shared DataPublicCOMMONMatsEmpty Bottle ReportsSchedules Preliminaryftp_files_to_ddd.bat');
When I am running this batch file ftp_files_to_ddd.bat from local directory of my PC,it is working properly.