I am trying to upload a database backup on a machine. Since the structure of files is different from server, how can i start the database.
Do i need to create a new control file? If yes, how can i run the sql command "Alter database backup controlfile to trace" in nomount mode.
I have 780(12*65) csv files generated from 65 databases.Now I have to load this 780 csv files into 12 tables created in my database for some monitoring and reporting purpose.to call the sql loader I am plannig to create 780 lines like below.
sqlldr abc@tns/pwd control='E:htmlctlhtml_broken_jobs_rpt.ctl' log='E:htmlreportloghtml_broken_jobs_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_db_size_rpt.ctl' log='E:htmlreportloghtml_db_size_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_fragmentation_rpt.ctl' log='E:htmlreportloghtml_fragmentation_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_index_stats_rpt.ctl' log='E:htmlreportloghtml_index_stats_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_invalid_object_rpt.ctl' log='E:htmlreportloghtml_invalid_object_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_long_running_queries_rpt.ctl' log='E:htmlreportloghtml_long_running_queries_rpt.log'
we know creating 780 control files is the difficult task.So I have created only 12 control files. is there any mechanism to pass a varible (planning to declare it in the sqlldr line) to the infile clause like below in sql loader?
infile "E:htmlreportoutput&a_html_broken_jobs_rpt.csv"
here a is the variable name. it will change every 12 csv files once.
or
is there anyother way to achive this?
database administration , we are planning to use amazon cloud database , this database does not allow us to login to server machine , unfortunately amazon don't provide ssh to this machine , in general for doing any of adminstration task on the database will there be need to log into the machine ? we can always log in through toad or any other sql client but we cannot do ssh to server..general can this limitation effect administration ?
View 9 Replies View RelatedWe have quite a number of sessions in database MES (production) coming from another machine.
From v$session, the program is oracle@WID27 (TNS V1-V3). This WID27 (hostname) consists of quite a number of development databases inside. We have to trace which jobs are actually triggering this, as WID27 are not suppose to connect to production databases.
How can we tell whether the sessions came in is from dblink or from the machine itself?
I want to load data into more tables from many files ,based on first column value,which is FILLER field.i am trying to test this scenario with two oracle tables with similar definition. and load one record on each table using WHEN/POSITION keywords. for this , i added first column as reference column in the data which i have in ctl file itself.
1st table loaded with 1st record. But, 2nd record not loading.if i missed anything with WHEN/POSITION keyword ?
This is the error in log file for 2nd table(WD1):
Record 2: Rejected - Error on table WD1, column TAB.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Table WD1:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
1 Row not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
[code]....
can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?
View 15 Replies View RelatedI have installed oracle 11g Release 11.1.0.1 on windows server 2008.I access this server from a client machine running on winXP through SQL PLUS. I wanted to shutdown and start DB server from this client machine. Accordingly i issued shutdown immediate command from SQLPLUS.
Now when i tried to start the DB with "STARTUP NOMOUNT" command i received error 12514. I understand this error means that the listener received a request to establish connections to a database but since DB services are not up and running, hence the error. So my first question is how do I resolve this error.
Continuing further, i tried to re-connect to the database as sys, and i received another error ORA-01041. So, my second question is why did i receive this error and again how can i resolve it
may be you can point me to relevant reference in Oracle documentation) how can i start and stop oracle from a client machine using SQL interface in CLI mode and windows environment and avoid this error.
I have already installed oracle 10g version in my Desktop. Now i want to install APPLICATION SERVER 10g version.
Should I Un install the Normal Database.
Or else can I have both in a single machine.
I have many backup sets of my database.
I need to recover a especific set of data, but where these data are. I thought of install oracle in a new machine and recover the backups one by one.
Send me the procedure for cloning a database on the same machine / server .
View 1 Replies View RelatedI want to read all the files present in a directory of client's machine.
However I have achieved this by creating java class in database but the problem is it accesses the directory of machine on which database is installed not the client's machine.
I have oracle client and I tried to access local directory of client's machine to read all the files in that directory but it didn't work.
However, I achieved this by creating one java class for ex. test.class and I run it locally through command prompt but I want to achieve it using Pl-sql.
This morning i feel my db server very slow to response. When i check, i saw db server got high load but low process.
View 5 Replies View RelatedI have client and database on same machine, when I will connect to the instance , it will use IPC, suppose If I will use connection string then which process will handle this request?
View 2 Replies View RelatedThe steps to move OMF files in ASM. I tried the following and was not successful.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/system.357.809972853"
datafile 2 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/sysaux.363.809972837"
datafile 3 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/undotbs1.365.809972737"
datafile 4 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/users.361.809972859"
datafile 5 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/undotbs2.360.809972761"
datafile 6 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/undotbs3.359.809972787"
datafile 7 switched to datafile copy "+DATA01/pa01pod_im1l059p/datafile/undotbs4.358.809972811"
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/13/2013 16:30:05
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
RMAN> alter database open;
so switch worked reset logs says can't use it there so I just try to open and it just hangs.
From the view v$flash_recovery_area_usage, below output shows total no of files are 24+4=28.
FILE_TYPE NUMBER_OF_FILES
------------ ---------------
CONTROLFILE 0
ONLINELOG 0
ARCHIVELOG 24
BACKUPPIECE 4
IMAGECOPY 0
FLASHBACKLOG 0
From the view v$recovery_file_dest, it shows the no of files are 49.
NAME NUMBER_OF_FILES
------------ ----------------
/dba/backup 49
Why there is discrimination?
I have a forms that generate a file from database into database server and copy it to client machine by the follow process:
IF webutil_file_transfer.Is_AS_readable(V_server_way || V_file_name) THEN
l_success := webutil_file_transfer.AS_to_Client
(clientFile => :V_screen_way
[code]...
But is showing the erro ora-105100 in one client machine
My problem is only on Some Saturday's my Oracle server's Load average goes high (more than 300 ).
-- oracle version 11.2.0.1.0
-- runs on Sun solaris 10
-- Sun fire V 440
-- Sun storEdge 3315 connected to the server.
Same setup is working find with higher volumes without any problem but only on saturday's, that too not on all saturdays, some specific saturdays the load average goes high.
At the time nothing will be processed from the application side and the cpu utilisation goes high upto 95 %.
I am sending necessary information as follows.
vmstat 6 10
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s3 s4 in sy cs us sy id
3 0 0 28660024 6513408 285 212 2044 2 2 0 0 0 17 0 42 830 3469 1380 22 5 74
125 0 0 29027480 6156256 2 6 34 0 0 0 0 0 5 0 10 791 53770 30278 83 17 0
125 0 0 29027680 6157496 29 140 21 1 1 0 0 0 5 0 9 786 52756 30309 83 17 0
116 0 0 29031600 6159896 24 125 0 0 0 0 0 7 3 0 5 819 54081 31069 83 17 0
[code]....
that are my biggest tables
Users use front end (called ESS Console) and when they try to open one of those tables they wait very long (really bad performance). Sometimes the GUI even hanging without displaying results.
Does Partitioned Tables feature works for better performance?
In our development database, we have created 5 dimensions and a cube with 2 measures in OLAP. All these are mapped to relational tables. When we try to load this cube (MAINTAIN CUBE option from AWM) we are getting this below error:
An error has occurred on the server
Error class: Express Failure
Server error descriptions:
INI: error creating a definition manager, Generic at TxsOqConnection::generic<BuildProcess>
INI: XOQ-01600: OLAP DML error "ORA-35571: The maximum number of load errors has occured." while executing DML
[code].....
What are the database parameter i should look for as a DBA ?
I want to know the file format of .dbf file.I want to open it in hex editor and want to read contents of that file from there.
View 4 Replies View RelatedI have some doubts about redo log files,
1) Can we fetch 'select statements' from redo log files through the use of log miner utility or any other?
(I think redo log file contains only insert,update,delete and DDL/DCL commands only)
2) If "No" to the above answer then how can i fetch all select statements fired on the system for a day or particular time.
(setting of sql_trace may be the one of them, but can it be possible for system level)
I have got backup pieces in ASM, I guess about more than 100 files. Now, I need to copy all of them from ASM to FileSystem, there are 2 methods still now:
1- Copy from ASM to FileSystem using cp command.
2- Copy from ASM to FileSystem using DBMS_FILE_TRANSFER.
But:
In the first method, when I copy one file, I took more than 1 minute, so the following script would take me more than 1 days (I guess so).
#!/bin/ksh
#
# This script copies files from FRA on ASM to local disk
#
ORACLE_SID=+ASM2
ASMLS=/vasgatedb/app/vsgbkp/asm_ls.txt ##{ASM files list}
[Code]...
The second method, DBMS_FILE_TRANSFER took me less than 1 second to copy one file completely.
sys@VSGDB> set timing on
sys@VSGDB> exec dbms_file_transfer.COPY_FILE('asm_dir','level_0_vsgdb_9998_813844797.bkp','fs_dir','level_0_vsgdb_9998_813844797.bkp');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.38
Of course, I wish I used the second method as soon as possible, however, said I below, I've got about ~200 files, and I could not copy one by one file.
We have a tablespace with one 30GB datafile. We would like to add 5 more datafiles then re-size the original to make six 5GB datafiles.
View 6 Replies View RelatedI have got oracle 11g release 1, I need recomendation, how can I delete safely content from the folder C:oraclediag dbmsinstancenameinstancenameincident?
can i delete files manually which are not useful?
we are getting below error:
ora-00257 archiver error. connect internal only until freed
when we tried to remove the unwanted arc files thro ASMCMD,we are getting below error:
ASMCMD> rm -ef 2011_04_05/
Unknown option: e
usage: rm [-rf] <name1 name2 . . .>
ASMCMD> rm -rf 2011_04_05/
ORA-15032: not all alterations performed
ORA-15028: ASM file '+XCOM_BACKUP_DG/TXCOM/ARCHIVELOG/2011_04_05/thread_2_seq_27215.1143.747641143' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15028: ASM file '+XCOM_BACKUP_DG/TXCOM/ARCHIVELOG/2011_04_05/thread_3_seq_21762.826.747641143' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)
further we checked FRA size:
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 3.19 0 38
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
and checked any arc processing holding lock on arc files:
> ps -ef | grep -i ora_arc*
oracle 6989 1 0 15:07 ? 00:00:00 ora_arc0_TXCOM1
oracle 6991 1 0 15:07 ? 00:00:00 ora_arc1_TXCOM1
oracle 12246 12164 0 15:17 pts/4 00:00:00 grep -i ora_arc*
oracle 13452 1 0 Mar23 ? 00:01:07 ora_arc0_TWEBAPPS1
oracle 13454 1 0 Mar23 ? 00:00:30 ora_arc1_TWEBAPPS1
oracle 15402 1 0 Mar23 ? 00:00:50 ora_arc0_SXCOM1
[Code] ........
but we were not able to remove those .arc files from that folder. finally we have down all the instances and deleted those files manually.
We have Oracle 10g(10.2.0.4) RAC on AIX 5.3, there are 3 RAC instances on each node.From Oct 9th, we found one of the instance in node 1 generated a large size trace files in udump. The largest size of the trace file take up 3G. But there's only about 15G $ORACLE_BASE direcotry. After some time, we should delete some trace files for releasing the space.
Here is a part of alert log when this issue happen:
Sun Oct 9 23:18:15 2011
Errors in file /oracle/app/oracle/admin/bzywk/udump/bzywk1_ora_3166258.trc:
ORA-00600: internal error code, arguments: [17087], [0x70000010DF9F580], [], [], [], [], [], []
Sun Oct 9 23:18:16 2011
Trace dumping is performing id=[cdmp_20111009231816]
[code]....
I checked with some trace files, I found all these files contains a huge info of processes.
I'm facing problem with archive log file size, Archive logs are generated with only of 90m or 92m or 94m(Variable sizes of less than 100m), Although i had set 100m for each of my redo log file. Here i'm providing my create db script for your reference. I want to know why the log switches before it reaches 100m.Is there any connection of intial 10m for my .dbf files.
create database mydev
maxlogmembers 3
maxloghistory 100
maxdatafiles 50
maxinstances 1
logfile
[Code]....
I am trying to install Oracle 11g Release 2 on Redhat Enterprise Linux 5.2 (vmware image) but after 50% installation it through errors while copying files on the server. I also tried on Windows 2003 Server R2 (physical m/c as well as vmware image) and faced same problems. I did installation of Oracle 11g Release 1 and didn't find any problems.
View 15 Replies View Relatedi want to load jpg into my oracle database 10g throw sqlloader
i did folloing steps
step 1)
Create the table as follows
CREATE TABLE image_table (file_id NUMBER(5),
file_name VARCHAR2(30),file_data BLOB);
step 2 )
Create control file as follows
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
[code].....
step 3)
Then i have run this command
F:oracleproduct10.2.0db_1in>sqlldr control=F:practicecontrol.ctl
Username:system
Password
so i got this error
SQL*Loader: Release 10.2.0.5.0 - Production on Wed Jun 8 13:47:27 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-404: Column FILE_ID present more than once in IMAGE_TABLE's INTO TABL
E block.
I am facing problem in user_dump_dest directory...I have noticed that there are a lot of trace files with huge size in MBs.I clean it and after 4 days there are 40G of size..
View 1 Replies View Related