A Deploying ASM Using SQL*Plus

This appendix describes how to deploy Automatic Storage Management (ASM) using SQL*Plus under the following topics:

Creating a Database that Uses ASM
Creating Tablespaces in ASM
Creating Redo Logs in ASM
Creating Control Files in ASM
Creating Archive Log Files in ASM

See Also:
Oracle Database Administrator's Guide for information about using Oracle Managed Files (OMF)

Creating a Database that Uses ASM

The recommended method of creating your database is to use the Database Configuration Assistant (DBCA). However, if you create your database manually using the CREATE DATABASE statement, then ASM enables you to create a database and all of its underlying files with a minimum of input.

The following section describes an example of using the CREATE DATABASE statement, where database files are created and managed automatically by ASM.

Creating a Database that Uses ASM: Example

This example creates a database with the following ASM files:

A SYSTEM tablespace datafile in disk group dgroup1.
A SYSAUX tablespace datafile in disk group dgroup1. The tablespace is locally managed with automatic segment-space management.
A multiplexed online redo log with two online log groups, one member of each in dgroup1 and dgroup2 (flash recovery area).
If automatic undo management mode is enabled, then an undo tablespace datafile in directory dgroup1.
If you do not specify a value for the CONTROL_FILES initialization parameter, then two control files, one in drgoup1 and another in dgroup2 (flash recovery area). The control file in dgroup1 is the primary control file.

The following initialization parameter settings are included in the initialization parameter file:

DB_CREATE_FILE_DEST = '+dgroup1'
DB_RECOVERY_FILE_DEST = '+dgroup2'
DB_RECOVERY_FILE_DEST_SIZE = 10G

The following statement is issued at the SQL prompt:

CREATE DATABASE sample;

Creating Tablespaces in ASM

When ASM creates a datafile for a permanent tablespace (or a temporary file for a temporary tablespace), the datafile is set to auto-extensible with an unlimited maximum size and 100 MB default size. You can use the AUTOEXTEND clause to override this default extensibility and the SIZE clause to override the default size.

ASM applies attributes to the datafile, as specified in the system default template for a datafile as shown in the table in "Managing Disk Group Templates". You can also create and specify your own template.

Files in a tablespace might be in both ASM files and non-ASM files as a result of the tablespace history. RMAN commands enable non-ASM files to be relocated to an ASM disk group and enable ASM files to be relocated as non-ASM files.

Creating a Tablespace in ASM: Out-of-the-Box Example

This example illustrates out-of-the-box usage of ASM. This example lets ASM create and manage the tablespace datafile for you, using Oracle supplied defaults that are adequate for most situations.

Assume the following initialization parameter setting:

DB_CREATE_FILE_DEST = '+dgroup2'

The following statement creates the tablespace and its datafile:

CREATE TABLESPACE tspace2;

Creating Redo Logs in ASM

Online redo logs can be created in multiple disk groups, either implicitly in an initialization parameter file or explicitly in an ALTER DATABASE...ADD LOGFILE statement. Each online log should have one log member in multiple disk groups. The filenames for log file members are automatically generated. All partially created redo log files, created as a result of a system error, are automatically deleted.

Adding New Redo Log Files: Example

The following example creates a log file with a member in each of the disk groups dgroup1 and dgroup2. The following parameter settings are included in the initialization parameter file:

DB_CREATE_ONLINE_LOG_DEST_1 = '+dgroup1'
DB_CREATE_ONLINE_LOG_DEST_2 = '+dgroup2'

The following statement is issued at the SQL prompt:

ALTER DATABASE ADD LOGFILE;

Creating Control Files in ASM

Control files can be explicitly created in multiple disk groups. The filenames for control files are automatically generated. If an attempt to create a control file fails, then Oracle deletes partially created control automatically.

If you need to specify a control file by name, then you can use alias filenames to reference ASM files and use easy to understand names. Using aliases in the specification of a control file during its creation enables you to later refer to the control file with a meaningful name. Furthermore, an alias can be specified as a control file name in the CONTROL_FILES initialization parameter. Control files that you create without aliases can be given alias names at a later time using the ALTER DISKGROUP...CREATE ALIAS statement.

When creating a control file, datafiles and log files that are stored in an ASM disk group should be given to the CREATE CONTROLFILE command using the file reference context form of their ASM filenames. However, the use of the RESETLOGS option requires the use of a file creation context form for the specification of the log files.

Creating Control Files in ASM: Example 1

The following CREATE CONTROLFILE statement is generated by an ALTER DATABASE BACKUP CONTROLFILE TO TRACE command for a database with datafiles and log files created on disk groups dgroup1 and dgroup2:

CREATE CONTROLFILE REUSE DATABASE "SAMPLE" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 (
    '+DGROUP1/db/onlinelog/group_1.258.541956457',
    '+DGROUP2/db/onlinelog/group_1.256.541956473'
  ) SIZE 100M,
  GROUP 2 (
    '+DGROUP1/db/onlinelog/group_2.257.541956477',
    '+DGROUP2/db/onlinelog/group_2.258.541956487'
  ) SIZE 100M
DATAFILE
  '+DGROUP1/db/datafile/system.260.541956497',
  '+DGROUP1/db/datafile/sysaux.259.541956511'
CHARACTER SET US7ASCII
;

Creating Control Files in ASM: Example 2

This example is a CREATE CONTROLFILE statement for a database with datafiles, but uses a RESETLOGS clause, and thus uses the creation context form for log files:

CREATE CONTROLFILE REUSE DATABASE "SAMPLE" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 (
    '+DGROUP1',
    '+DGROUP2'
  ) SIZE 100M,
  GROUP 2 (
    '+DGROUP1',
    '+DGROUP2'
  ) SIZE 100M
DATAFILE
  '+DGROUP1/db/datafile/system.260.541956497',
  '+DGROUP1/db/datafile/sysaux.259.541956511'
CHARACTER SET US7ASCII
;

Creating Archive Log Files in ASM

Disk groups can be specified as archive log destinations in the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n initialization parameters. When destinations are specified in this manner, the archive log filename will be unique, even if archived twice. All partially created archive files, created as a result of a system error, are automatically deleted.

If LOG_ARCHIVE_DEST is set to a disk group name, LOG_ARCHIVE_FORMAT is ignored. Unique filenames for archived logs are automatically created by the Oracle database. If LOG_ARCHIVE_DEST is set to a directory in a disk group, LOG_ARCHIVE_FORMAT has its normal semantics.

The following sample archive log names might be generated with DB_RECOVERY_FILE_DEST set to +dgroup2. SAMPLE is the value of the DB_UNIQUE_NAME parameter:

+DGROUP2/SAMPLE/ARCHIVELOG/2003_09_23/thread_1_seq_38.614.541956473
+DGROUP2/SAMPLE/ARCHIVELOG/2003_09_23/thread_4_seq_35.609.541956477
+DGROUP2/SAMPLE/ARCHIVELOG/2003_09_23/thread_2_seq_34.603.541956487
+DGROUP2/SAMPLE/ARCHIVELOG/2003_09_25/thread_3_seq_100.621.541956497
+DGROUP2/SAMPLE/ARCHIVELOG/2003_09_25/thread_1_seq_38.614.541956511