I am creating the below table.but why oracle is not taking next extent value that I mentioned in the storage clause?
create table acs (
timestamp varchar2(19),
source varchar2(19),
message varchar2(35),
sev varchar2(10),
[Code]....
Suppose tablespace allocation_type is system then how oracle determines the initial extent and max extent size?
View 4 Replies View RelatedMy organisation is currently discussing different storage options for the database storage. Our production database is nearly 2TB and we do not want to continue with the existing NetApp storage (we use a 2 node RAC running 11.2.02 with nfs filesystem from NetApp filer).
We were looking at different options and came across Nimble Storage, they are very fast growing company aiming mid-range storage customers. The initial talks and demonstration looked very promising in terms of IO performance (they claim 40,000 - 60,000 IOPs for their CS400 series Nimble Storage array) and other options they are providing but we understand that majority of their customers are using it for VDI and other infrastructures.
They have demonstrated us using if for Oracle database with ASM storage over iSCSI LUNs. We are yet to do the POCs and benchmarking.
Has anyone come across Nimble Storage for running Oracle databases?
I have tables in production which has got huge no of partitions(say more than 100), but I would like to extract table definiation along with mentioned few partitions(say 10 partitions) alone. How to do that, which way is the best to extract DDL with right format.
because when I use metadata package the format for the extraction is not good, is there a way to extract table definition with mentioned partition names.
Does RMAN backupset (backup to ) on DBFS is supported. I can find ACFS supported but is not mentioned for DBFS. My current customer is thinking backup to DBFS then copy to tape as the interim solution before getting ZFS next year.
View 5 Replies View RelatedMy oracle database version is 11.2.0.3.0 where i am having one schema in that schema i am having 3 same tables with same structure same data but with different name.
but problem is in first table when i perform select query it takes 5 sec, in another table it is taking 0 sec and in third table it is taking 10 sec.
The below query is taking high CPU almost 98% and longer time to execute.
SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
[code]....
Some shops run Oracle RAC using "conventional" storage, like emc, netapp, etc. Some shops run Oracle RAC using JBOD, just bunch of disks, all redundancy is managed by ASM.
How would you distinct these two approaches? JBOD approach sounds cheaper, so why serious enterprises are still using traditional conventional storage, like netapp and emc?
I am new to oracle which command do I use to set the file storage in oracle? I learnt the we have the option of specifying the storage pattern as per our queries.
View 2 Replies View RelatedI am doing a Trade Study on Automatic Storage Management vs Veritas Storage Foundation for Oracle RAC on Solaris. I am trying to get feedback from individuals on which file system is best for a high OLTP 11gR2 system. I am really concerned about how the to file systems stack-up when compared on their overall performance. Our system admin doesn't trust ASM and is a firm believer in Veritas. I am trying to be objective and find the best solution for our projected upgrade system. We are upgrading from Solaris 890s to T4-2s, 11gR2, Solaris 10 or 11, and EMC storage. I was also concerned that Oracle doesn't have the ASMLIB package for Solaris. We have other system on Linux that use ASMLIB.
View 2 Replies View RelatedI wish to install 11g r1 RAC on my laptop having linux 4 as platform (on vmware) , for that i prepare 4 partition for that (on node1)
/dev/sdb1 - for ocr
/dev/sdb2 - for voting disk
/dev/sdb3 - for asmdisk group
/dev/sda5 - fro asmdisk group
by assuming external redundancy for ocr and voting disk i kept only one disk
and i configured following in /etc/sysconfig/rawdevices
/dev/raw/raw1 /dev/sdb1 -- ocr
/dev/raw/raw2 /dev/sdb2 -- voting disk
/dev/raw/raw3 /dev/sdb3 -- asmdisk group
/dev/raw/raw4 /dev/sdb5 -- asmdisk group
and my question is how node2 can understat these raw device as shared storage?
I have a Linux box with the below configurations:-
Linux blrulvremoradb02 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
Last time my UNIX administrator have done a partition in this box. I have 100GB lun allocated to this box which is spilitted in two mount points.
/u01 is having 30GB and /u02 is having 70GB. I want to build a ASM in this box and install Oracle 11.2.0.1 and for that I need raw disk.
Is there any useful links or can you guide me to achieve this... My UNIX admin is on leave for new year and I want to do this by my own..
Oracle 9i instalation problems in Windows Storage Server 2003.
I got the error "ORA-12546- TNS:Permission denied" when trying to install ORacle 9i in Windows Storage Server 2003. This error occurs at the time of Creating database.
I cannot start ASM on my Oracle Database Appliance.
crsctl status resource -t says:
ora.asm
ONLINE ONLINE node1 Started
ONLINE ONLINE node2 Started
however, if I try to access the ASMCMD it says:
[grid@node1 ~]$ asmcmd
Connected to an idle instance.
ASMCMD> startup
ORA-00304: requested INSTANCE_NUMBER is busy
Connected to an idle instance.
I am using 11.2.0.1.0 on RHEL 5.4ASM is properly working on my machine. database is running from ASM. I want to configure additional oracleasm devices.command fails saying:
$ oracleasm createdisk ASMDISK08 /dev/xvd8
Writing disk header: done
Instantiating disk: failed
Clearing disk header: done
I am creating asm diskgroup on loopback devicesHere are the configured loopback devices.
[root@host1 ~]# ls -l /dev/loop[1-9]
brw-rw-rw- 1 oracle oinstall 7, 1 Oct 25 14:42 /dev/loop1
brw-rw-rw- 1 oracle oinstall 7, 2 Oct 25 14:42 /dev/loop2
brw-rw-rw- 1 oracle oinstall 7, 3 Oct 25 14:42 /dev/loop3
brw-rw-rw- 1 oracle oinstall 7, 4 Oct 25 14:42 /dev/loop4
[code]...
I am unable to understand where is the problem.
what type of storage systems used for saving archive files and backup files "especially for oracle databases"
View 5 Replies View Related, I have a few doubts in Normal,High and External redundancy levels in ASM concept. External - No failure group. No mirroring. Normal - Two Way Mirroring. One failure group. High - Three Way Mirroring. Two failure group. 1.
above mentioned 3 types in correct.2. My main question is what is the minimum number of disks needed in Disk group creation in Normal and External redundancy??
How to get the row ids of the first and last row in every extent by dba_extents?
View 13 Replies View RelatedI've read the documentation that describes the storage management.I create a tablespace as:
CREATE TABLESPACE MY_TABLESPACE_NAME
DATAFILE 'path/filename1.dbf' SIZE 3000M AUTOEXTEND ON NEXT 200M MAXSIZE 4000M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8k
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
As the extent management is local, does it mean that any storage clause of the objects (tables, indexes etc.) placed in it isn't taken into consideration? I mean in a case of placing a table in the mentioned tablespace that has a storege parameters defined as follows:
CREATE TABLE MY_TABLE(
...
)
TABLESPACE MY_TABLESPACE_NAME
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 100M
NEXT 20M
MINEXTENTS 1
MAXEXTENTS 50
BUFFER_POOL DEFAULT
)
1. what will be the initial extent size? 1M or 100M?
2. what will be the next extent size? 1M or 20M?
3. will the maxextents parameter be taken into consideration?
4. when i'm sure the tablespace is dedicated to keep only one object [MY_TABLE], what should be the relation between the initial datafile size [filename1.dbf] and the initial extent size? Should they be iqual or doesn't matter?
5. as the SEGMENT SPACE MANAGEMENT is AUTO, the PCTFREE param doesn't make sense, right?
I did some google searches about large number of extents and ASSM. I see bits and pieces on the web. This is something I need to look at while testing an application. Not looking to go into 'why' I would use smaller extents, I just want to make sure I have what I need to look for during testing..Issues with massive numbers of extents:
1. DBA_EXTENTS query is really slow.
2. issues truncating tables (due to having to read lots of extents)
3. issues splitting maxvalue partitions and with dropping partitions.
4. if I stay away from ASSM, would this reduce these issues? Are there any other performance issues or other issues I need to know about to check when I do tests?
Any issues with query or insert wait time? The tables that would get smaller events would have thousands of partitions/sub-partitions . Most of these sub-partitions will be rather smaller.I just want to test for a variety of different cases. The 'why' will come out during testing.
I am having 2 data centers within 1/2 km distance. And we kept each vote disk in once site. In case of site failure, the cluster needs a 3rd set. We don't have 3rd site.
Last open world, Oracle announced that oracle storage on cloud as announced. If I get the small space on the cloud, can i put my 3rd vote disk on the cloud?
I have a partitioned table (one partition per month). Every month there are added about 1GB data. What extent size should I set? 1GB will be ok?
What if data will be greater than 1GB, adding new 1GB extent takes probably a lot of time and clients may see delays while they're inserting in this time? (it's OLTP system)
When new extent is allocated? Exact in time of lacking space in existing extent or before? Partitions are dropped after one year so free space isn't a problem.
i try to install oracle grid infrastructure 11r2 on cent OS 4.8 on vmware workstation 8.0.2..ASM disk is not showing ,i had also change disk discovery path to 'ORCL:*'i had already configure ASM disk using /etc/init.d/oracleasm createdisk...successfully..
View 8 Replies View RelatedI am getting error 'ORA-03113: end-of-file on communication channel' in Oracle 11g R2 version when I ran the below query.
WITH tab1 AS (SELECT C_NUM as col1 FROM test123_v)
SELECT COUNT (distinct col1) num_assignments FROM tab1
UNION ALL
SELECT COUNT (distinct col1) num_assignments FROM tab1
The same query is working fine in 10g.Here test123_v is view created using table test123, which has one column c_num.When I use the table instead of view in the with clause I am getting the output in 11g. The below query gives the output.
WITH tab1 AS (SELECT C_NUM as col1 FROM test123)
SELECT COUNT (distinct col1) num_assignments FROM tab1
UNION ALL
SELECT COUNT (distinct col1) num_assignments FROM tab1
How to estimate next extent size for very large table? What should I take into account? Is there any formula for that?
View 4 Replies View RelatedWhen ever error occurred as "Unable to extend extent", we do add either datafiles or increase the size (autoextend on).But in a interview, i was asked to handle the error without increasing size/adding new datafiles.
how can i handle this error without increasing size?
1.2 million chained rows, 1.7 million blocks, etc. Initial extent for this table is 64k and next 1 mb. I would try to calculate this out better for efficiency and performance. This will not be efficient as it stands. calculate the size.
View 14 Replies View RelatedI need a clarification on oracle WITH CLAUSE. When the query is build using oracle WITH clause and under lying table data gets changed, does oracle update data in temprorary table as well.I have the below query
WITH channel_summary AS (SELECT channels.channel_desc, SUM(amount_sold)
AS channel_total FROM sales, channels
WHERE sales.channel_id = channels.channel_id GROUP BY channels.channel_desc)
when i fire the query on it
SELECT channel_desc, channel_total
FROM channel_summary WHERE channel_total > (SELECT SUM(channel_total) * 1/3
FROM channel_summary);
this will give always same result or it will change the records as and when the tables goes under changes in case of DML operation.
ORA-01658: unable to create INITIAL extent for segment in tablespace
I am getting above error when a batch job is running.Its insterting data in tables. I checked free space i dont know y its not using avalable space.
Database Version: 11202
select sum(bytes)/1024/1024/1024, sum(maxbytes)/1024/1024/1024 from dba_data_files where tablespace_name='test_data';
sum(bytes) sum(maxbytes)
---------------- -------------
60 0
select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name='test_data';
sum(bytes/1024/1024/1024)
----------------------
50
There was a tablespace of size 100MB.That Tablespace is filled and hence throwing an error like "unable to extent". But verified that there is around 20MB freespace present in the tablespace.what would be the reason behind this error?
View 2 Replies View Related