Tuesday, August 7, 2012

Creating Oracle database with ASM storage mechanism using dbca


Creating Oracle database with ASM using Oracle database configuration assistant (dbca).
1. Run dbca:
./dbca
2. Until step 6 leave the default choices and press the “Next” button.
3. Database Configuration Assistant, Step 6 of 16: Storage Options.
Select the Automatic Storage Management (ASM) mechanism for the database you’re creating:
Creating Oracle database with ASM storage mechanism using dbca
4. At the step 7 select ASM disk groups you’d like to use as storage for the database you’re creating.
This dialog allows you to create new asm disk groups or add disks to an existing disk group.
We’re using external redundancy, because our asm files are placed on a storage with RAID 5 / RAID 10 redundancy mechanism implemented.
Creating Oracle database with ASM storage mechanism using dbca
5. Next step select database file locations.
Choose the ASM disk group for the database files to be created.
Creating Oracle database with ASM storage mechanism using dbca  Creating Oracle database with ASM storage mechanism using dbca
6. Specify recovery options for the database.
For production databases both options are obligatory.
For example, you can place flash recovery area on +FLASHBACK ASM diskgroup and archivelogs on +ARCHIVELOG ASM diskgroup.
Creating Oracle database with ASM storage mechanism using dbca
7. The next few steps are straightforward. Just set desirable memory_max_target/sga_target, character set, security defaults for your database to be created.
8. Finally your database will be created in 10-20 minutes.
Creating Oracle database with ASM storage mechanism using dbca
9. Connect to the database and run some queries to verify everything is Ok.
[oracle@london ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 -
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
london           OPEN
SQL> set lines 1200
SQL> select file_name, tablespace_name, bytes, status from dba_data_files;
FILE_NAME                                                                                                                        TABLESPACE_NAME                      BYTES STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- ---------
+DATA/london/datafile/users.259.762604973                                                                                        USERS                      5242880 AVAILABLE
+DATA/london/datafile/undotbs1.258.762604973                                                                                     UNDOTBS1                          78643200 AVAILABLE
+DATA/london/datafile/sysaux.257.762604973                                                                                       SYSAUX                   614203392 AVAILABLE
+DATA/london/datafile/system.256.762604971                                                                                       SYSTEM                   723517440 AVAILABLE
SQL>
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                                                                                   IS_
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
         3         ONLINE  +DATA/london/onlinelog/group_3.266.762605165                                                             NO
         3         ONLINE  +DATA/london/onlinelog/group_3.267.762605167                                                             YES
         2         ONLINE  +DATA/london/onlinelog/group_2.264.762605159                                                             NO
         2         ONLINE  +DATA/london/onlinelog/group_2.265.762605163                                                             YES
         1         ONLINE  +DATA/london/onlinelog/group_1.262.762605155                                                             NO
         1         ONLINE  +DATA/london/onlinelog/group_1.263.762605157                                                             YES
6 rows selected.
SQL> create table TESTTABLE (a number(10), b number(20)) tablespace TESTTB;
Table created.
SQL> insert into TESTTABLE values(10, 12);
1 row created.
SQL> commit;
SQL>  select TABLE_NAME, TABLESPACE_NAME from dba_tables where table_name like '%TESTT%';
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TESTTABLE                      TESTTB
SQL>
[oracle@london ~]$ cat .do_asm
ORACLE_BASE=/u01/app/oracle/product/11.1.0
ORACLE_HOME=$ORACLE_BASE/asm
ORACLE_SID=+ASM
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH
[oracle@london ~]$ . .do_asm
[oracle@london ~]$ asmcmd
ASMCMD> ls
ARCHLOG/
DATA/
REDOLOGS/
ASMCMD> ls ./*/*/*
+DATA/LONDON/CONTROLFILE/:
Current.260.762605153
Current.261.762605153
+DATA/LONDON/DATAFILE/:
SYSAUX.257.762604973
SYSTEM.256.762604971
TESTTB.269.762606991
UNDOTBS1.258.762604973
USERS.259.762604973
+DATA/LONDON/ONLINELOG/:
group_1.262.762605155
group_1.263.762605157
group_2.264.762605159
group_2.265.762605163
group_3.266.762605165
group_3.267.762605167
+ARCHLOG/LONDON/PARAMETERFILE/:
spfile.256.762605605
+DATA/LONDON/TEMPFILE/:
TEMP.268.762605203
spfilelondon.ora
ASMCMD>

No comments: