Our server got crash in server1 and we recovered the same DB to server2 using RMAN back up. But while running our scheduler for email alert we got the error message as ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: 'D:ORADATAKFDBTEMP01.DBF' Our temp file is already in E folder.
View 4 RepliesI'm using SAP ECC6.0, Oracle 10G, HPUX B11.23.Recently I had performed database restoration from my backup tape. However, during the process of bringing up the Oracle and SAP database, I observed that there could be some archive logs went missing. I had tried to check them from my backup tape, but could not find it.
In short, now my SAP database is up and running, but I'm having another problem when I executed a "CheckDB" job in DB13. The job is unable to complete successfully
DB13 job log:
30.08.2010 19:44:40 Job started
30.08.2010 19:44:40 Step 001 started (program RSDBAJOB, variant &0000000000061, user ID BASIS)
30.08.2010 19:44:40 Execute logical command BRCONNECT On host drqaecc
30.08.2010 19:44:40 Parameters: -u / -jid CHECK20100830194440 -c -f check
[code]...
i did everything writen but when i do *SQL>alter database recover managed standby database disconnect from session;*
i go and look in the standby database AlertLog file ,and thats whats writen
*ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: غير قادر على فتح الملف
O/S-Error: (OS 3) The system cannot find the path specified.
[code]....
strange thing that it realises the primary database in drive F and it goes to it but i dont understand what could be the reason of this ,although im doing this command while primary database is shutdown!
I have a database of branch A whose files are located in E: Drive. I want to download it in branch B.I placed all the files of Branch A in D: Drive of Branch B. When i start the database i was getting controlfle error. I made required corrections in initorcl.ora. Now when i start the Database,its mounted, but I am getting ORA-01157: Cannot identify datafile 1. I tried to rename the file, but the "alter database rename file1 to file2;" option is not working.
View 3 Replies View RelatedHow to find out the database TNSNAMES.ora file if there are multiple .ora files with different names in Oracle_home/network/admin directory ?
View 6 Replies View RelatedI'm rying to import schema's from a dump file that came from a different environment.
What I have is:
1. dump file
2. log file of the export
I'm trying to import the file(containing three schemas) with remap_schemas, and it fails, gives a lot of ORA-00959: tablespace 'string' does not exist.
Now, I've read in OTN:
[URL]
that what you need to do in that case is to use the REMAP_TABLESPACE option,to redirect the objects to a different tablespace.
I don't see a name of the tablespace I'm getting the error for in the export log.I don't know if I have more tablespaces I have to redirect with REMAP_TABLESPACE.
I don't want to perform this 3 times, have an error, by that find out what's the next tablespace needing redirection and only then starting over...
How can I know from the dump file and the log file,what is the tablespace names i need for the redirection to my names? Or its just that the tablespace giving me the error is the only one in the dump file?
we know we can see lock mode held in session can be analysed using LM column in v$lock.But i confused in seeing LM column it all shows in numbers from 0 to 6.
eg
0,'None(0)',
1,'Null(1)',
2,'Row Share(2)',
3,'Row Exclu(3)',
4,'Share(4)',
5,'Share Row Ex(5)',
6,'Exclusive(6)')
i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors
SQL> alter tablespace APPLSYSD READ ONLY;
alter tablespace APPLSYSD READ ONLY
*
ERROR at line 1:
ORA-01230: cannot make read only - file 636 is offline
ORA-01111: name for data file 636 is unknown - rename to correct file
ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636'
i have not this file on the OS
In which condition it is required to rebuild the index and how can identified it?
View 12 Replies View RelatedI have create one table emp under scott schema, and this schemas default tablespace is USERS and this tablespace has 3 datafiles.
Now how do I identify the object EMP is residing on which datafile in USERS tablespace?
modify this script to show the table name, rowidwhich is locked?
In addtion,output of script below. In particular, I am trying to understand if there anyactive locks.
set pagesize 85
col username format a10
col osuser format a15
col sid format 9999
[code]...
Just noticed MR(media recovery lock ) in our database,further check showd all datafiles had this lock since our database was shutdown abortly but was startup gracefully.
View 2 Replies View RelatedI have a RAC environment with 3 nodes and application using Oracle Form Reports that access the database. Every day the application cause many locks on database when execute insert, update and delete. I don't understand why so much locks. Are there a way to avoid this? Always I have to eliminate session causing locks.
View 27 Replies View RelatedI am facing following message with alertlog file. It appearing much frequently and filling alertlog file.
Transaction recovery: lock conflict caught and ignored
When i try to compile a package, im getting below error.
ERROR at line 1:ORA-04021: timeout occurred while waiting to lock object
i gave below query and found the sid of object.
select * from v$access where object='THINKING_PKG'
From v$session view,i found sid and serial# and at same time i queried dba_jobs and saw one jobs is running at this time.Now how to compile this object.
We are experiencing tx row lock wait time over hours. There is no blocking session and it seems that the application hangs. What is funny is that when we gather_stats on the tables, those tx row lock wait are being released.
View 8 Replies View RelatedI am having I/O issues if i create 20 GB DATAFILES on SMALL TABLE SPACE. guide me with the maximum size limit of data file that I can create in Windows 2003 32 bit server.
View 3 Replies View RelatedI have 2 schemas in my database with over 500 tables in each.i am trying to know which tables actually belong to which datafile in which tablespace.
View 2 Replies View Relatedi have a tablespace with a datafile of 20g. now by mistake i delete the datafile and then try to delete the tablespace from EM but i got an error which says that data file is not present to delete
Now initially after deleting the file physically so then i check space by applying df -ah at os lvl so it didn't reclaim the space now i try to delete the tablespace from em so it gives me the above error. This might be due to tablespace existence. so how can i reclaim the space.
I was wondering if there is any way to know in which Tablespace and Datafile my Table is located. I have exported a table and about to delete it as i am partitioning it.
View 9 Replies View RelatedI'm new into Oracle, consider the below scenario and respond it accordingly.
I want to add 500GB data file at primary database side, but doesn't have space at standby database side. My STANDBY_FILE_MANAGEMENT is set to Auto.
Which is the best possible way of configuring the Physical file structure in case of Data Warehouseing application?
I mean to ask you Multiplexing of the data file or control file is required. Or any best possible way to configure Physical file structure.
I am using Oracle 11g in my Windows machine. I wrote a multithreaded program that will be inserting into Oracle database so I was getting this exception before-
ORA-12519: TNS:no appropriate service handler found
So after going through various article on the internet. I found that the solution for above exception is to increase the number of processes- So I followed the below thing and it works fine for me after that.
cmd>sqlplus / as sysdba
sqlplus>alter system set processes=300 scope=spfile;
sqlplus>shut immediate;
sqlplus>startup
Now after some more time, I increased the processes to something like below
alter system set processes=1000000 scope=spfile;
And since then whenever I am starting up my Oracle database, it is giving me this exception below and it is happening since one day.
ORA-10997: another startup/shutdown operation of this instance inprogress
ORA-09968: unable to lock file
I am having enq: TX - row lock contention in top wait event. it is occurring between 10pm - 2am.
We are having sqlloader job running every one hour(conventional path). But for the specific period of time i am getting "Global Enqueue Services Deadlock detected". Between 10-5. I analyzed related trace file it is make me little confusion.I found there are four insert query culprit for this locking. out of four sql , tow of them are ran by same SID, other two insert ran by same id. I got confused because how same sid locking them self. trace file below. during this period oracle maintenance window is active.
Trace file:
*** 2012-10-09 03:40:31.135
user session for deadlock lock 0x15365e060
sid: 1104 ser: 22256 audsid: 8797820 user: 49/iurth flags: 0x45
pid: 71 O/S info: user: oracle, term: UNKNOWN, ospid: 8601
image: oracle@sgh0909
client details:
[code]....
I am trying to write a code to identify the delimiter in the file ( which is in the form of table(id, raw) in system)then I take this delimiter and pass it as a parameter to SP which perform cleaning of this file(table) and creates another clean table.
The problem I am facing is until now the file was coming with one fixed (TAB) delimiter, but now it has come with different (SPACE), now here I want to develop the code to identify the delimiter place it in a variable an pass this as parameter to cleaning SP.
--here i want to develop code to identify delimiter from hosts_equiv file which has data as below
select * from hosts_equiv where left(raw,1) not in ('#','*') and isnull(raw,'')<>''
Raw id
---------------- ---
hiper USER1 1
hiper2 USER1 2
APX user2 3
Need to identify delimiter between e.g. hiper USER1 and pass it as a parameter to the raw_parse sp
declare Tab varchar(10)
set Tab = char(9)
exec raw_Parse
'hosts_equiv', -- From table ( entire file content is stored as table with Id record no sequence generated)
'CleanedHosts_Equiv', -- To table name, when passed it will clean the from table and places the cleaned data in to_table
[code]....
How can be identify a particular archive-log file have backup or not from rman catalog?
View 2 Replies View RelatedCan we lock data in global temporary table?
View 4 Replies View RelatedHow to execute the SQLLDR, where Data File Reside in another Server?
View 1 Replies View RelatedI have a question ragarding undo tablespace. I want to ask that why only undo tablespace information we need to specify in parameter file. We do not specify any other tablespace information. Not even for temporary tablespace. Then why we need to give undo tablespace name while instance is creating.
View 5 Replies View RelatedI noticed my DB is generating a lot of "small" .arc files and I am usure why. As you can see from the v$log query my log file size is set to 50MB. But yet BLOCKS*BLOCK_SIZE never adds up to 50MB.
Is there anything else I can look into to see how to make the .arc files larger?
SQL> select group#, thread#, bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 2 52428800
4 2 52428800
select blocks, block_size, blocks*block_size from v$archived_log where sequence# between 63876 and 72851 and thread# = 1
BLOCKS BLOCK_SIZE BLOCKS*BLOCK_SIZE
---------- ---------- -----------------
28 512 14336
28 512 14336
28 512 14336
55 512 28160
[code]...