Solved

Oracle 11gR2 - ASM tablespace creation

Posted on 2012-03-12
5
1,420 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Number Format 1 44
Oracle SQL Select Statement 19 58
Out of Sequence numbers for today 25 47
Oracle 12c patching 1 33
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

757 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

17 Experts available now in Live!

Get 1:1 Help Now