New DB - How To Partition Raw Disks
Jan 10, 2012
I am a new Oracle DBA that has been tasked with creating some single node databases for our programmers to connect to. I have never built a database, only upgraded. I will be using a Dell 610 with 10.2.0.4 software. I have been reading the install guides but cannot find a definitive answer for how to create the raw partitions. I have a SAN that I can utilize and my current db is set up with RAID 6 like so:
Disk 2 300 GB 1 Extended 300 GB and 5 logical, 100MB, 30 MB, 100 GB, 100 GB, 100 GB
Disk 3 100GB 1 Extended 100 GB and 3 logical 100 MB, 30 MB, 100 GB
why the old DBA partitioned this way? I am not good with storage and know that I need to understand before I build new databases so I do not duplicate something that is wrong. I was reading articles that recommend SAME but would not know how to do that on the SAN.
I will be using data pump to export the database and import to the new server. I was looking at using transportable tablespaces to do this. Would you recommend that from the cluster to the standalone?
View 4 Replies
ADVERTISEMENT
Jun 27, 2012
We have Exadata -V2 quarter rack with High Performance Disks. We applied EHCC's various compression methods on some of the table's partitions.
Now we are setting up Exadata Expansion Rack - High Capacity Disks. Post implementation, we would be moving older partitions to new Expansion Rack wherein compressed partitions are also included.
In this case, would there be any impact on the compression ratio as the expansion rack is having high capacity disks.
And, moving partition method would be same as it is for non-exadata database i.e. "alter table <table_name> move partition
<partition_name> tablespace <tablespace_name>"
View 2 Replies
View Related
May 28, 2010
i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.
View 6 Replies
View Related
Oct 21, 2013
Is there any command to identify the disks are HC OR HP ? Indirectly i could find with the following exercise -
$ grep -i MACHINETYPES /opt/oracle.SupportTools/onecommand/databasemachine.xml <MACHINETYPES>X2-2 Half rack</MACHINETYPES> ASMCMD> lsdgState Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files NameMOUNTED NORMAL N 512 4096 4194304 18235392 10597264 2605056 3996104 0 N DATA/MOUNTED NORMAL N 512 4096 4194304 2087680 2085808 298240 893784 0 Y DBFS_DG/MOUNTED NORMAL N 512 4096 4194304 27240192 18871860 3891456 7490202 0 N RECO/ = 45TB >>
[URL] ....... >> Page 5 >> HP SAS disks
View 2 Replies
View Related
Jul 25, 2010
Regarding 11gR2 RAC, ASM, and NFS. Installing 11gR2 Grid Infrastructure and 11gR2 RAC on OEL 5.5 servers with storage supplied by NetApp NFS shares over TCP/Ethernet.
How do I mount NFS file systems as block devices so ASM will use them?
It is my understanding that Linux mounts NFS file systems as anonymous file systems rather than block devices. ASM requires block devices. The only way I know to have Linux treat an anonymous file system mounted over NFS as a block device is to subsequently mount it as a loop device.The Oracle 11gR2 documentation states, "ASM does not support loop devices". Without defining NFS mounts as loop devices, oracleasm createdisk complains the given file on the NFS mounted file system is not a block device when i run
/usr/sbin/oracleasm createdisk DATA /oradata/prod/data/disk01
where /oradata is an NFS mount from a NetApp volume defined in /etc/fstab and disk01 was created with the dd command.I followed the steps in section 3.3.1.2 'Creating Files on a NAS Device for Use with Oracle ASM' of the Oracle guide..
When I use asmca, it does not find any candidate disks with the search path set accordingly.If successfully implemented ASM using NFS shares for database disk groups from a NAS, Steps I followed are below.
In /etc/fstab:
server-ip-address:/vol/data /oradata nfs rw,bg,hard,nointr,tcp,nfsvers=3,timeo=600,rsize=32768,wsize=32768,actimeo=0 0 0
-- mount and verify. /oradata is mounted with specified options.
# mount -a
# mount
[code]...
Command fails with error message "File /oradata/prod/data/disk01 is not a block device." None of the OUIs (grid installer, asmca, or dbca) identify any disks as ASM candidate disks with the search string '/oradata/prod/data/*'. I also tried using '/*', '/oradata/*', '/oradata/prod/data/*' to no avail.
View 4 Replies
View Related
Jan 1, 2013
I am using oel 5.8, oracle 11.2.0.3. The raw disk permissions are getting changed when the node gets rebooted.I am trying to use udev rule to change the permission permanently but its not working.
CODE[root@VM-PAGRAC3 rules.d]# more 99-asm.rules
KERNEL=="sd", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p",*
RESULT=="SATA_VBOX_HARDDISK_VB7a29b7ae-de673b56_",
NAME+="oracleasm/disks/DSK1#" OWNER="grid"
GROUP="asmadmin" MODE="0660"
KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p",
RESULT=="SATA_VBOX_HARDDISK_VB36f5d134-4e0e4c43_",
[code].....
View 8 Replies
View Related
Aug 31, 2011
I have an Oracle database 11.1.0.6 with ASM storage.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
There is a disk group named +FRA which has only 1 disk (call it disk 0 for later references).
+FRA disk group is in type of EXTERNAL redundancy
I'd like to add another disk to +FRA disk group and copy all files stored in disk 0 to the new disk (call it disk 1 for ex.), then take off disk 0 (not use for +FRA disk group any more).
Expected result: there is only one disk 1 left in the +FRA disk group, with all the data from disk 0 and keep their original file names
View 5 Replies
View Related
Aug 27, 2011
I am trying to install 2-node 11gR2 RAC on CentOS 5.6 using VMWare Fusion. I'm in the middle of Grid Infrastructure installation and while choosing the ASM diskgroups I don't see any candidate disks but when I click on all disks i can see them but can't select them.
I tried installing the grid before and ended up with and issue running the root.sh on all the nodes so had to deconfigure the grid binaries using the rootcrs.pl and then deinstalled the grid software using the ./desintall utility.
when tried installing again ...I ran the cluvfy utility ..it doesn't show any errors.
View 2 Replies
View Related
Jan 20, 2013
We are in preparation for the purchase of equipment for two- node Oracle10g RAC (on Windows Server 2008) and I need to configure the disks on servers. We have an existing EMC VNX5500 storage and SAN 8Gb switches.
My question is with how many disks to configure servers?Originally we planned four SSD discs (two in RAID 1 for OS and two in RAID 1 for locally Oracle home) for each node/server, and everything else on storage disks (Shared Disks Storage).
Whether it is necessary to have more local disks and for what? Where should be redologs files, archivelogs files, backup files…
Whether it is better server to be configured with six SSD disks:
- two in RAID 1 for OS,
- four in RAID 10 (or RAID 1) for locally Oracle home, archivelogs files, or something else…?
View 6 Replies
View Related
Nov 12, 2010
ASM instance SID = +ASM and the database installation was under SID =rdbms112
Im just in the process of running dbca and have specified which ASM disks to use for data, but then on the next screen of the dbca, im asked for the ASMNMP password,
the password for 'oracle' user is 'oracle' but im getting this message:
"Could not validate ASMNP password due to the following error: "ORA-01031-insufficient privilages" Do you want to continue? If you contiue ASM will not be configured to be moitored by database controll "
when I was asked for privilaged operating system groups when setting up ASM
OSDBA, OSOPER and OSASM ... were all set to group 'dba'
the oracle user is in the 'dba' group
View 2 Replies
View Related
Mar 3, 2012
you have given 500gb of harddisk. how will u implement the size or how to assing the size for disks in the diskgroup.give me the best practice that is used in production
View 1 Replies
View Related
Jun 28, 2012
Normally ASMlib/oracleasm is used to prepare the disks for ASM. I just wonder, besides this tool, is there any other GUI based tool to use for preparing the ASM disks?
View 2 Replies
View Related
Mar 17, 2011
I Know we can create dynamic partitions on table in oracle 11g. Is it possible to create normal partition and sub partition both dynamically.I have to create Normal partition range on date and sub partition list on Batch ID (varchar).
View 3 Replies
View Related
Jun 26, 2010
I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
View 3 Replies
View Related
Apr 3, 2013
At present we have a non partitioned table.
Can we apply redefinition and create range partition and hash sub partition on it?
View 2 Replies
View Related
Jun 19, 2012
I have 11.1.0.7 instance running and got more 300G of storage available for the database. I would like to know what are the steps to add these disks/storage to the existing DATA asm disk group?
View 5 Replies
View Related
Jan 2, 2013
Can I add range sub partition to a hash partition table. Example like this.
CREATE TABLE test
(
test_id VARCHAR2(10 ) ,
test_TYPE VARCHAR2(5) ,
CREATE_DATE date
)
partition by hash (test_id, test_type)
Partitions 3
SUBPARTITION BY RANGE (CREATE_DATE);
When Tried, I am getting syntax error as invalid option.
View 8 Replies
View Related
Sep 22, 2011
I have two tables Activity and Activity1.
Activity Structure
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),
CHILD_REFERENCE_NO VARCHAR2 (19),
USER_ID VARCHAR2 (30) DEFAULT user NOT NULL,
TERMINAL VARCHAR2 (30) DEFAULT userenv ('TERMINAL') NOT NULL )
Activity1 Structure Which I have Done Partitioning When I insert data from Activity to Activity1 it gives that error ORA-14400: inserted partition key does not map to any partition what I am doing wrong
CREATE TABLE ACTIVITY1(
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),
[code]....
Insert Statement
insert into ACTIVITY1(ACTIVITY_TYPE,
ACTIVITY_DATE,
ACTIVITY_ON,
REFERENCE_NO,
CHILD_REFERENCE_NO,
[code]....
View 2 Replies
View Related
Jun 14, 2011
i want to create a new partition for version 2
existing table is as below
create table test
(
name varchar2(100),
version NUMBER(12)
)
[Code]....
View 15 Replies
View Related
Mar 3, 2011
I have partition based table one the basis of year month. And we have 8 local indexes on this table. Every month we have to create a new partition and load data into this partition and the volume of the data is around 14million and the load process is taking long time due to indexes. Is it possible to drop the indexes from particular partition?
View 8 Replies
View Related
Jul 10, 2012
I have two tables in which one is partitioned table with the following details.
CREATE TABLE "SCOTT"."TBL_MITTAL"
("ACCOUNT_NAME" VARCHAR2(50 BYTE),
"BILL_NO" VARCHAR2(50 BYTE),
"BILL_DATE" VARCHAR2(50 BYTE),
"CLI" VARCHAR2(50 BYTE),
"ANI" VARCHAR2(50 BYTE),
[code].....
When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error
SQL> insert into tbl_temp select * from tbl_mittal;
insert into tbl_temp select * from tbl_mittal
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.
ACCOUNT_NAMEBILL_NOBILL_DATECLIANICHARGE_START_DATEDURFROM_LOCATIONTO_LOCATIONINVOICE_IDCIRCLE
10000010357423128271095119301-Feb-111723006000931488182328-JAN-11 11.30.54.000000000 AM59.04CHANDIGARHJAIPUR271095119
10000011844187128348720198715-Jun-121409470011825531896615-MAY-12 09.10.36.000000000 AM28.03CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531927115-MAY-12 09.10.41.000000000 AM38.32CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531933015-MAY-12 09.10.46.000000000 AM28.81CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531930215-MAY-12 09.10.53.000000000 AM28.96CHANDIGARHBANTWAL348720198
[code].....
I also tried to upload the same data using sqlldr.
sqlldr log file contents is as follows:
Total logical records skipped: 0
Total logical records read: 1857532
Total logical records rejected: 801092
Total logical records discarded: 37
[code].....
So some sqlldr bad file contents is as follows.
100000118441871,283487201987,15-JUN-12,1723958000,9355115251,10-JUN-12 05.56.05.000000 PM,36.99,CHANDIGARH,AMBALA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7520533825,10-JUN-12 05.56.14.000000 PM,44.12,CHANDIGARH,AGRA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9356452151,10-JUN-12 05.56.17.000000 PM,116.83,CHANDIGARH,JALANDHAR,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9331223048,10-JUN-12 05.56.21.000000 PM,28.33,CHANDIGARH,KOLKATA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7827927893,10-JUN-12 05.56.24.000000 PM,3384.33,CHANDIGARH,DELHI,348720198,,
[code].....
View 2 Replies
View Related
Apr 12, 2010
needed in stored procedure to achieve this...how to get this..(stored procedure)
table:
studentid,sname,partitonid
901,x,null
902,y,null
903,z,null
904,p,null
905,q,null
906,a,null
907,b,null
908,d,null
909,f,null
910,m,null
For the above data set i need to divide into 5 partittions and need to updated the partitonid with the partition number for each partition set,like the below result set
studentid,sname,partitonid
901,x,p1
902,y,p1
903,z,p2
904,p,p2
905,q,p3
906,a,p3
907,b,p4
908,d,p4
909,f,p5
910,m,p5
View 3 Replies
View Related
Mar 28, 2013
write ALTER STATEMENT for adding new partition P1 and SUBPARTITION P1_201001 and P1_201002.
TABLE
=======
CREATE TABLE TEST
(
"REPORT_ID" NUMBER,
"MONTH_ID" NUMBER,
"GROUP_ID" NUMBER,
"AGE_GROUP_ID" NUMBER,
[code]...........
View 4 Replies
View Related
Jun 8, 2011
How to find the size pf a partition in a partition table?I guess we need to query views like dba_tab_partitions but I am not very sure. will running dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')
View 3 Replies
View Related
Nov 17, 2013
We're trying to utilize Oracle RAC 11.2. We want to have two servers in our grid. These two server are installed on a VM using Hyper-V software.
common storage for asm is also developed on a VM using FreeNAS software.Now, when we try to install Grid Infrastructure the disk that is recognized as a extra drive in both servers are disabled and we can't select it as ASM storage. The OS we're using is Windows Server 2008 R2.I have to say the IP requirements are considered and the servers have two IPs.
View 0 Replies
View Related
Mar 25, 2011
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
View 11 Replies
View Related
Jul 17, 2010
I have a partitioned table in which at the first level it is been partitioned based on date column(C1) and within in each partition it is again sub-partitioned based on a column(C1) which is a numeric value now at first level(C1 column level) there are 6 partitions and in the last partition i want to add another partition how can i do that.
View 8 Replies
View Related
May 5, 2011
I am trying to add partition to table without partition
with following code
ALTER TABLE ACC_LOC1_TAB
ADD PARTITION testpart BY RANGE (ALT_AUTHDT)
(PARTITION UPTO_2010 values less than (31-mar-2010),
PARTITION APR_JUN_10 VALUES less than (30-JUN-2010),
PARTITION JUL_SEP_10 VALUES less than (30-SEP-2010),
PARTITION OCT_DEC_10 VALUES less than (31-DEC-2010),
PARTITION JAN_MAR_11 VALUES less than (31-MAR-2011))
it will raise error ora-14020
View 4 Replies
View Related
Feb 21, 2013
I am using Oracle 11.2.0.1 Oracle Database.I have a table with 10 Million records and it's a Non Partitioned Table.
1) I would like to partition the table (with partition by range ) without creating new table . I should do it in the existing table itself (not sure DBMS_ REDEFINITION is the only option ) (or) can i use alter table ...?
2) Add one partition which will have data for the unspecified range.
View 5 Replies
View Related
Jun 19, 2012
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
View 1 Replies
View Related
