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 Replieswe 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)')
Due to improper documentations of a certain project, I need to drop a DEFAULT tablespace of a newly created instance including it's associated datafiles by using this command:
"DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;"
The default tablespace name is QWER (qwer01.dbf) and I added 2 datafiles in it, re: OPD_SML01.dbf & EXYT_SML01.dbf.
Do I have to do it online or offline?
Can we add a datafile mapped to a shared network drive in windows server.
View 1 Replies View RelatedWhat is best practice to change small disk D:? I am beginner with Oracle. 10g on W2008. 5 datafiles (all indexes,second data file, 2 undotabs)*.dbf (34;30;1;34;12 GB) is on D:. Part of tablespaces (1 data, 1 undo)has files on c:.
I.
1.Shutdown 2008 server.
2.Copy D: image with GHOST to USB, network.
3.Connect new D, create RAID.
4.Restore image to D.
5.Start 2008 server.
II.
1.Stop application.
2.CONNECT AS SYSDBA
3.SHUTDOWN NORMAL or (IMMEDIATE)?
4.Copy files *.dbf at OS level from d: to ... USB disk, network.
5.Shutdown 2008 server.
6.Change disks, create RAID in BIOS.
7.Start W2008.
Is Oracle at this moment in SHUTDOWN mode?
8.Copy back *.dbf to new D: (with directory structure).
9.STARTUP Oracle.
i encoutered lately on such statement:
Table D-7 shows a hierarchical file mapping of a sample OFA-compliant installation with two Oracle home directories and two databases. The database files are distributed across three mount points, /u02, /u03, and /u04. URL....
why three mount points (/u02, /u03, and /u04) for datafiles? don' understant this. isn't it better to store all datafiles in one mountpoint?
I have checked the space of my tablespaces/datafiles in my database. I have 8 GB space left in my database server. I cant add more hard-disk as there is no slot left. We r planning to buy a new server with latest config.
My question is, how can we know upto what size our database can increase and when a datafile need to be added in advance. Sometimes even though datafiles have space left,it shows errors abt extents cannot be extended. We have coalesce the tablespaces and added a new datafile.
I am facing an issue while rebuilding a DR (Physical Standby) site using RMAN.Rman is failing to restore all the datafiles to the stanby site.
There are 403 datafiles that should be restored among which after restoring 50-60 datafiles the channel is abruptly getting terminated.Please note this exercise is not new I have done this many times (once a year after a DR testing) and the same script is being used.The rcv file is as follows
/u03/oracle/admin/mpr/scripts/dataguard/configure_dr.rcv
connect auxiliary sys/XXXX@YYY
run {
duplicate target database for standby nofilenamecheck dorecover;
}
exit
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
configure_dr.rcv: END
[code]....
The trace file says "krso_proc_add: Detected dead process 13828326; subsuming V$MANAGED_STANDBY slot
INFO: krsr_rfs_atc: RFS processes are temporarily disallowed"
I have also started RMAN debug tracing and the trace is also scanned as it is huge.I have seen the following in the trace error 3015 signalled during compilation..I do not find any specific oracle error to check on .
The only change that happened is that the Primary production server has been moved to a new datacentre and after its move all the connections seems to be working fine.
We have two tablespace xyz_ts & pqr_ts having 167 datafiles . We have to reduce this number of files. These tablespace having 580 GB size .
View 1 Replies View Related would want to check which tables located in which datafiles.
Tried with this command:
select B.FILE_NAME from dba_segments a, dba_data_files b
where a.header_file=b.file_ID and segment_name='TABLE_NAME'
Seems like it only shows 1 datafile. Some tables are large and i suppose it's resided in more than 1 datafile.
what's the difference between having BIG datafile (32GB) vs many small datafiles (4GB) replacing a Big one.
View 3 Replies View RelatedWe have lost some datafiles and some archivelogs. We have backup of whole database taken on Dec 20 and archivelogs until Dec 23.So we dont have 1 day worth of archivelogs which can lead to incomplete recovery.
Now question is do we need to restore all datafiles or only lost datafiles for incomplete recovery since we dont have all archivelogs ? lost datafiles belong to different tablespaces.
Datafiles which are still intact are with current SCN and we restore lost datafiles only and recover unitl our last available archivelog it will have different SCN. Will we be able to open database or we need to resotre whole database for this scenario?
I am looking for a script to find the tbs usage including datafiles. Tablespaces Datafile Size Used Free Used% Free% Max MB
Need all the data in MB and all the datafile space in every tablespace.I have searched internet,but couldn't find this type of format.
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]...
I 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
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 Replies View RelatedWhen 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 RelatedWhen I list the parameters with "show parameter", I get only numeric value for hash_area_size with no unit, what is the unit for that value and conversion of it to MegaBytes.
View 3 Replies View RelatedHow to distribute the datafiles among various disks of a server, so that I/O operations are improved.
I mean is there any area we need to focus while distributing the disk like:
1)Particular types of datafiles should be placed in one disk.
2)Temp/Undo/Rollback tablespaces in another disk.
3)Indexes in another disk
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
View 16 Replies View RelatedI have dump file of 17 GB,which i want to import in my database db1 in user amol ;so i created new user under db1 as below ,before this i have created tablespace so that i can import my data only to that tablespace only. My steps are as below.
CREATE TABLESPACE ptaxold1 DATAFILE '/home/oracle/oracle/product/10.2.0/oradata/cvsdbm/ptaxold1.dbf' SIZE 6024M AUTOEXTEND ON;
create user amol identified by amol default tablespace ptaxold1 temporary tablespace tem; imp amol/amol then i mentioned my dump file but after importing it does not show any table under user amol.
I have a tablespace PLUS_IX with 5 datafiles PLUS_IX_01, etc.
PLUS_IX_04 is 85% full, up from 78% last month
PLUS_IX_05 is 22% full.
PLUS_IX_01 to _03 are 73% full.
Should I be concerned that PLUS_IX_04 is getting full? If so, what actions are recommended?
ALTER INDEX ..... REBUILD has been run on all the indices.
I read this article:
[URL]......
Does it work with copying datafiles from 10g2 to 11g2? I want to move one huge tablespace (which contains one table) from 10g2 to 11g2, what is the best method to do that?
Basically , we have a large index tablespace , whose data files already spans multiple mount points , and has been extended with a new data file on a wrong mount point by mistake , Can we relocate this last datafile extension , to a different mount point while the DB is online ? Is there any special consideration for index datafiles (If such classification is correct ) or is it just like any other Oracle non-system datafile ?
What will be the effect on the normal data tables which are using this index if they are being accessed during the exercise? This is a 2 node Oracle 9i 9.2.0.8.0 RAC on Veritas Cluster .
How to move the datafile from e-drive to d-drive in oracle 11g?
View 7 Replies View RelatedI am looking for a way to monitor the creation of new datafiles / tempfiles.Let's say I am creating a new datafile of 8GB in size, and it takes a few minutes to create.
I have looked at v$session_longops, and several other system views, but cannot find any that indicate how the file creation is progressing.
I want to be able to display something simple, such as "72% complete".
i want to delete one datafile from tablespace witch contain 2 datafiles
i did
ALTER DATABASE DATAFILE 'ton_fichier' OFFLINE DROP;
and delete the file physiquly but i have already my datafile witch i have delete in my tablespace
11.2.0.2 on Solaris..I have such a large post on a very basic space calculation.
We have several tablespaces starting with WLMCS in our DB..I just wanted to calculate the total space consumed in the disk by all these tablespace combined .When I queried DBA_DATA_FILES.MAXBYTES and DBA_DATA_FILES.USER_BYTES , I've noticed that ,
When AUTOEXTEND is NO: MAXBYTES is 0 for these datafiles . But USER_BYTES won't be 0 for these files
When AUTOEXTEND is YES: MAXBYTES will be a non-zero value for these datafiles . USER_BYTES won't be 0 either for these files-- Not including datafile names for better readability.
SYS > select tablespace_name, maxbytes/1024/1024, user_bytes/1024/1024, autoextensible from dba_data_files where tablespace_name like 'WLMCS%';
TABLESPACE_NAME MAXBYTES/1024/1024 USER_BYTES/1024/1024 AUT
------------------------- ------------------ -------------------- ---
WLMCS_DATA 32767.9844 32766.9375 YES
WLMCS_DATA 32767.9844 31615.875 YES
WLMCS_DATA 32767.9844 16419 YES
WLMCS_OAT_DATA 32767.9844 32766.9375 YES
[code]....
11 rows selected.To calculate the space consumed , I made 2 assumptions.Are the below 2 assumptions right?
Assumption 1. Whenever MAXBYTES = 0 , USER_BYTES should be considered for the space calculation.
Assumption 2. Whenever you have non-zero values for both MAXBYTES and USER_BYTES , MAXBYTES should be considered for the space calcuation.I did the calculation (adding up of) based on the above assumptions. Is this calcualtion Correct ?
-- Not including datafile names for better readability.
TABLESPACE_NAME MAXBYTES/1024/1024 USER_BYTES/1024/1024
------------------------- ------------------ --------------------
WLMCS_DATA 32767.9844 32766.9375
WLMCS_DATA 32767.9844 31615.875
WLMCS_DATA 32767.9844 16419
WLMCS_OAT_DATA 32767.9844 32766.9375
[code]....