|Oracle® Database Storage Administrator's Guide
11g Release 1 (11.1)
|PDF · Mobi · ePub|
This appendix describes how to deploy Automatic Storage Management (ASM) using SQL*Plus under the following topics:
See Also:Oracle Database Administrator's Guide for information about using Oracle Managed Files (OMF)
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.
This example creates a database with the following ASM files:
SYSTEM tablespace datafile in disk group
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
dgroup2 (flash recovery area).
If automatic undo management mode is enabled, then an undo tablespace datafile in directory
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;
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.
For example, if there is a disk group named
dgroup3, you can create a tablespace
tblspace3 in that disk group with the following SQL statement:
CREATE TABLESPACE tblspace3 DATAFILE '+DGROUP3';
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;
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.
For example, if there is are disk groups named
dgroup4, you can create a log file with a member in each disk group with the following SQL statement:
ALTER DATABASE ADD LOGFILE ('+DGROUP3','+DGROUP4');
The following example creates a log file with a member in each of the disk groups
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;
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
When creating a control file, datafiles and log files that are stored in an ASM disk group should be given to the
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.
You can specify a disk group name (or any ASM file creation context) as part of the
CONTROL_FILES parameter prior to database creation to have the control files managed by ASM.
CONTROLFILE statement is generated by an
TRACE command for a database with datafiles and log files created on disk groups
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 ;
This example is a
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 ;
Disk groups can be specified as archive log destinations in the
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.
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
+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