Solved

Oracle 11gR2 - ASM tablespace creation

Posted on 2012-03-12
5
1,436 Views
Last Modified: 2012-03-13
Hi,

Im trying to set up Oracle using ASM. Installed it like this:

Oracle  was installed using 11GR2 patterns.

Two users created:  grid for infrastructure and Oracle for the DB

-      Instance ASM: (Infrastructure).
ORACLE_SID=+ASM
ORACLE_BASE=/u01/app/grid
ORACLE_HOME=/u01/app/grid/product/11.2.0/grid

-      Instance DPDB:
ORACLE_SID=DPDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

-      Mount Points created in ASM:
Visible through the user GRID and the ASM tool.
NOTE: not usual UNIX filesystems
Eg.:
$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     25557    25505                0           25505              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576     66519    64931                0           64931              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     10197    10145                0           10145              0             N  REDO/

So now the problem:

I will run a script that will create a bunch of tablespaces - among other things - for DPDB (script wasnt set for ASM use).

So I have to run these TS manually.

Basically a appl will have to access the tables inside these tablespaces using the DPDB.. but Im not sure how to create then for ASM...

I read this:
'You need to create your diskgroups in ASM, then create your tablespaces in the ASM diskgroup.

If you setup the instance with db_create_file_dest = ‘+<DISKGROUP>’ then you can simply do “create tablespace <TBLSPACE_NAME>;” '

====

Would it be the right way to do it? How could I create these diskgroups in ASM? This variable is necessary?

Any suggestion?

Tks,
Joao
0
Comment
Question by:joaotelles
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
Bajwa earned 500 total points
ID: 37714495
You can do that from ASMCMD or from sqlplus or from asmca (gui utility) as grid user.
for asmcmd check
http://docs.oracle.com/cd/B19306_01/server.102/b14215/asm_util.htm

for sqlplus
1. logon as GRID user
2. set the environment for your ASM instance
3. sqlplus "/ as sysasm"
and then for external redundancy (meaning that SAN is taking care of striping and mirroring)

create diskgroup DG_NAME external redundnacy disk 'ORCL:DISK' attribute 'au_size' = '4m';

another good website
http://www.oracle-base.com/articles/11g/AsmEnhancements_11gR1.php

then you can create a tablespace as

1.  logon as oracle
2. set the environment and run
3. create tablespace my_ts_name datafile '+DG_NAME' size 4096m
0
 

Author Comment

by:joaotelles
ID: 37714658
So let me see if I get it:

the DG's are already created:
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     25557    25505                0           25505              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576     66519    64931                0           64931              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     10197    10145                0           10145              0             N  REDO/

So now I can login here:
-      Instance DPDB:
ORACLE_SID=DPDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

And create the tablespaces as this:
create tablespace my_ts_name datafile '+DG_NAME' size 4096m,
using the Oracle user.

Is this correct?
0
 
LVL 5

Assisted Solution

by:Bajwa
Bajwa earned 500 total points
ID: 37714942
Sure!! You have three  Disk Groups

ARCH
DATA
REDO

you can use any of the with
create tablespace my_ts_name datafile '+DATA' size 4096m;

This will create the tablespace.
0
 
LVL 5

Assisted Solution

by:Bajwa
Bajwa earned 500 total points
ID: 37714951
This is exactly how it should work.  If you are getting error from inside the database (DPDB) which creating the tablespace then you need to put oracle user (database os owner) in appropriate OS groups.
0
 

Author Closing Comment

by:joaotelles
ID: 37714993
Tks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now