?
Solved

Oracle 11gR2 - ASM tablespace creation

Posted on 2012-03-12
5
Medium Priority
?
1,661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
Bajwa earned 2000 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 2000 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 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