| Oracle® Database System Administration Guide 10g Release 2 (10.2) for IBM z/OS (OS/390) Part Number B25398-01 |
|
|
View PDF |
| Oracle® Database System Administration Guide 10g Release 2 (10.2) for IBM z/OS (OS/390) Part Number B25398-01 |
|
|
View PDF |
This chapter describes z/OS-specific considerations for the files used by an Oracle database. In general, what Oracle product literature refers to as "files" will be data sets in your z/OS system. Some Oracle product features access files that are part of the hierarchical file system of UNIX System Services rather than z/OS data sets. However, the files that make up an Oracle database are all z/OS data sets.
Refer to Oracle Database Concepts and the Oracle Database Administrator's Guide to learn about the uses and relationships among Oracle databases, tablespaces, and operating system files. Refer to the Oracle Database User's Guide for IBM z/OS (OS/390) for a discussion of the interaction between Oracle database tools and z/OS data sets.
The following topics are included:
A number of files are used by an Oracle database instance. Some of these files are not an intrinsic part of the database. These include input parameter files, trace or diagnostic log files, and other similar files. Most of these will be sequential data sets, PDS members, or JES spool (SYSOUT) files on your z/OS system.
All of the files comprising an Oracle database are VSAM LDS clusters on z/OS. (For compatibility with past releases, VSAM Entry-Sequenced Data Sets with a control interval size of 4 KB are also accepted.) The Oracle server can create these data sets for you by internally invoking the z/OS IDCAMS utility and passing it DEFINE CLUSTER commands. This is done automatically during processing of SQL statements that create or add to the database structure, including CREATE DATABASE, CREATE TABLESPACE, and certain ALTER…ADD statements.
In most cases, it also is possible to pre-allocate Oracle database files by invoking IDCAMS yourself and issuing your own DEFINE CLUSTER commands prior to issuing SQL statements that add the files to the database. The main advantage to pre-allocation is that it gives you more control over physical data set placement.
If you omit file specifications on a CREATE DATABASE request, Oracle normally creates a minimal set of files using default data set names discussed in the following sections. This practice is not recommended for production databases. An optional facility called Oracle Managed Files (OMF) provides a different default naming scheme that is aimed at production database use. OMF is discussed in "Oracle Managed Files on z/OS".
The control file is a relatively small file used to record Oracle database instance control information. Control file availability is critical for overall database availability, and the Oracle database will therefore maintain multiple mirror-image copies of the control file if told to do so. Oracle Corporation recommends using at least two control file copies for any production database. The control file copies should be on separate physical storage devices and, if possible, on separate I/O paths in order to minimize the risk of losing both (or all) of them to media or path failure. You specify the data set name(s) of the control files in the init.ora parameter file that you supply to the Oracle database STARTUP command.
If you do not specify a name for the control file and you are not using OMF, the following default file name will be used:
"&ORAPREFD..&ORASRVN..DFLCNTL.DBF"
The system symbols &ORAPREFD and &ORASRVN are discussed in Appendix C, "Oracle Database for z/OS System Symbols".
The database files contain all the database data, both application tables, indexes and the like, and the Oracle database server's internal dictionary objects. Each tablespace in your database contains at least one database file. The SYSTEM tablespace, where the Oracle database keeps its internal dictionary structures and stored PL/SQL procedures, is created automatically when you issue CREATE DATABASE.
The names of one or more files to be used for the SYSTEM tablespace are supplied in the CREATE DATABASE statement or generated by the Oracle server using Oracle Managed Files. Normally, you will not store application data in the SYSTEM tablespace: after your database is created, you will create one or more additional tablespaces for application data. The CREATE TABLESPACE statement supplies the names of files to be used when adding a new tablespace, or the names can be generated by the Oracle server using Oracle Managed Files.
If you do not specify names for the database files and you are not using OMF, the following default file names will be used:
"&ORAPREFD..&ORASRVN..DFLDBS1.DBF" (default database file)
"&ORAPREFD..&ORASRVN..DFLTEMP.DBF" (default temporary tablespace file)
"&ORAPREFD..&ORASRVN..DFLDBU1.DBF" (default undo tablespace file)
The system symbols &ORAPREFD and &ORASRVN are discussed in Appendix C, "Oracle Database for z/OS System Symbols".
The redo log files record changes to database data (both application data and internal control data) and are critical for restart and recovery. An Oracle database instance must have at least two redo log files. Redo data is written to the log more or less sequentially from the beginning of the file to the end. Like control files, the Oracle server can write multiple mirror images of a log to reduce the risk of data loss due to media failure.
The term "log file group" refers to one or more mirror-image copies of a given log. When a log file (or group) is filled, the Oracle server automatically switches log writing to the next available log file or group. The frequency of this switching is a function of the size of the redo log files and the amount of database update activity.
The data set names of the log files can be supplied in the CREATE DATABASE statement, or generated by the Oracle server using Oracle Managed Files. In addition, log files can be added to a database later, using ALTER DATABASE…ADD LOGFILE.
If you do not specify names for the redo log files and you are not using OMF, the following default file names will be used:
"&ORAPREFD..&ORASRVN..DFLLOG1.DBF" (default redo log file # 1)
"&ORAPREFD..&ORASRVN..DFLLOG2.DBF" (default redo log file # 2)
The system symbols &ORAPREFD and &ORASRVN are discussed in Appendix C, "Oracle Database for z/OS System Symbols".
When you operate the database in ARCHIVELOG mode (normally the case for all production databases), the Oracle server copies filled logs to another (newly-created) file in order to make the filled log available for reuse. The new file that is created by such an operation is called an archive log. The data set names of archive logs are generated using a pattern that you specify in your init.ora file parameters. This name includes components that are distinct for each log, ensuring a unique data set name for each archive. This is one case where file pre-allocation is not possible: archive logs are always created by the Oracle server via internal IDCAMS call. Archive logs usually are required (read) during recovery, after restoring database files from backups. For more information, refer to Chapter 6, "Database Backup and Recovery".
When you create a tablespace, its initial size is the sum of the sizes of all datafiles you specify on the CREATE TABLESPACE statement. These are the actual sizes of the data sets, if you are using preallocated data sets or reusing existing files, or the amounts specified with the SIZE keyword if the server is creating the files. In the latter case, the Oracle server uses only the SIZE amount, and some unused space (overallocation) may be present due to VSAM allocation rounding. The unused space is never more than one cylinder per file, but you should be aware of this rounding when planning your Oracle server disk space needs.
A tablespace can be enlarged in two ways: by extending existing files of the tablespace or by adding new files. Existing files can be extended both automatically, on demand as data is added, and explicitly with a DDL SQL statement. Automatic file extension is enabled when the AUTOEXTEND clause is included in the file specification of CREATE or ALTER TABLESPACE. Automatic file extension is attempted when a database insert or update requires more free space than is currently available in the tablespace. This can be triggered by any user session; no special privileges are required. Explicit extension is requested with an ALTER DATABASE DATAFILE...RESIZE statement using SQL*Plus or a similar interface, and must be done from a session with ALTER DATABASE authority (normally a database administrator).
Adding new files to a tablespace is only done manually, by issuing an ALTER TABLESPACE ADD DATAFILE statement using SQL*Plus or a similar interface.
There are several z/OS-specific considerations with file extension and AUTOEXTEND. When a tablespace consists of multiple datafiles, all specified with AUTOEXTEND, you cannot control which data set the Oracle server extends in a given situation. Second, the amount by which to extend a file is governed by the NEXT amount from the AUTOEXTEND clause, never by a secondary space quantity in the data set's ICF catalog entry. (Secondary space is not included on server-generated DEFINE CLUSTER commands. Secondary space that you specify on the DEFINE CLUSTER for a preallocated database file is ignored except for the influence it exerts on CA size and rounding.)
VSAM space allocation always rounds an allocation amount to a Control Area (CA) multiple, and that can distort the way the AUTOEXTEND NEXT amount is handled. CA size is not specified explicitly but is derived from other DEFINE CLUSTER parameters: it is the lesser of the primary space quantity, the secondary space quantity (if any), and the device cylinder size, but never less than one whole track. When the Oracle server issues DEFINE CLUSTER it does not include a secondary space quantity, so CA size is one cylinder unless your SIZE is very small (less than a cylinder). You can control CA size on preallocated database files by specifying a secondary space amount that is less than a cylinder; although the secondary amount is not used by the Oracle server, it does determine CA size as described here.
The Oracle server is not aware of VSAM space rounding, so if your NEXT amount is less than one CA, the space added by CA rounding is not used. It does get used, however, when the file is extended again. In some cases, file extension is satisfied entirely with existing allocated but unused space, without invoking VSAM secondary allocation. Be aware of VSAM space rounding behavior when choosing a NEXT amount for a datafile.
Another consideration with file extension concerns Oracle database servers that are configured to run in multiple address spaces (where the DEFINE SERVICE command specifies a MAXAS number greater than one). The z/OS-specific logic for file extension requires establishing two concurrent "opens" to a file that is accessed from multiple server address spaces. This in turn requires that the VSAM cluster be defined with cross-region shareoption 3. The cross-system shareoption is irrelevant.
If you preallocate a data set for use as a tablespace file and you want to specify AUTOEXTEND for it, you must specify SHR(3,3) in your DEFINE CLUSTER command. If you let the server create the file for you, you must use the SHAREOPTION file management parameter to specify that cross-region shareoption 3 be included on the server's DEFINE CLUSTER command. (Server file management parameters are discussed in "Server File Management Parameters".) This is required only for Oracle servers whose MAXAS is greater than one, and is so even if only one address space is actually started. If your Oracle server is defined with MAXAS(1) (which is the default), AUTOEXTEND processing does not require SHR(3,3).
A bigfile tablespace is a tablespace with a single, but very large datafile (an ultra large datafile). Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. On z/OS, datafiles associated with a smallfile tablespace are limited to 4 GB; datafiles associated with bigfile tablespaces may be greater than 4 GB and can span multiple volumes.
On z/OS, ultra large datafiles are implemented as extended-format data sets with the Extended Addressabiltiy attribute set. Extended-format data sets must be system managed and are requested through the SMS data class DSNTYPE=EXT parameter and subparameter R (required). ISMF can be used to create such an SMS data class.
Oracle recommends that you pre-allocate ultra large datafiles. For more information, refer to the section "Special Considerations for Ultra Large Datafiles".
There are implicit methods for specifying an SMS data class at datafile creation time, whether pre-allocated or otherwise, which may be used at the installation's discretion. It is also possible to configure a file management parameter file group definition with the DATACLASS keyword to explicitly provide the data class for datafiles created by the database server. In this case, all datafiles governed by that file group would be allocated as extended-format data sets, including datafiles associated with a smallfile tablespace.
When specifying data sets or files to an Oracle database server on z/OS (in init.ora file parameters, SQL statements, PL/SQL package calls, or other) you use an extended version of the filespec syntax used by IBM C/C++ and LE. The IBM syntax encompasses data sets, DD statements (which can designate a variety of data set or file types), and POSIX HFS files. An extension provided by Oracle provides an easy way to specify spool (SYSOUT) data sets.
This section discusses a few filespec considerations that are specific to the Oracle database server. For more information on supported filespecs and syntax, refer to the Oracle Database User's Guide for IBM z/OS (OS/390). This guide also covers ambiguous filespecs which are those that cannot be distinguished as data set or HFS files by syntax alone. Such filespecs are interpreted based on the LE POSIX setting. If POSIX is ON, the filespec is treated as an HFS file. If POSIX is OFF it is treated as a data set. For example, the string "my.sql" is ambiguous: it could refer to an HFS file my.sql in the current working directory or it could refer to a data set named MY.SQL, perhaps with an implied userid prefix.
With some server features, interpretation of ambiguous filespecs is specific to the feature rather than to LE POSIX. For example, all of the files comprising the database store (such as control files, log files, and tablespace files) are VSAM LDS on z/OS. An ambiguous filespec supplied as such a filename, for example in CREATE TABLESPACE, is always processed as a data set filespec.
Conversely, some server features are limited to processing POSIX HFS files only and so always interpret ambiguous filespecs as HFS. This is true of the java.io package in Oracle Java.
Finally, some server features can access both data sets and HFS files. This is true of server PFILE processing (in CREATE SPFILE...FROM PFILE) and the UTL_FILE PL/SQL package. With these components, ambiguous filespecs are interpreted in the server as data set references.
Unambiguous syntax must be used when an HFS file is to be accessed. In contrast to client-side processing, there is no implied high-level prefix on data set names in the Oracle database server, even when a data set filespec does not use enclosing apostrophes. With database files, the system symbol &ORAPREFD is often used as the first qualifier of a database file name; it is translated to a value supplied as a database region parameter.
Similar to data sets, server references to HFS files are never path-relative, implying a current working directory. All server HFS references must use a complete path and file name.
A number of server file access features used by end users and applications use the DIRECTORY database object as a security control mechanism. On z/OS, a DIRECTORY object can specify either an HFS path or the high-level part of a data set name.
To create a new database or add files to an existing database, you issue a SQL CREATE or ALTER statement to the Oracle server. This statement allows you to specify whether a file is pre-allocated or, if not, the size with which the new file should be created by the server. There is no provision in Oracle SQL for supplying additional z/OS-specific parameters for creating the associated data set. Instead, you can supply file management parameters, using the ORA$FPS DD statement in the database service JCL procedure, to control most of the parameters in the IDCAMS DEFINE CLUSTER command that the Oracle server issues to create a new file.
File management parameters are also used for certain non-VSAM files, including the sequential backup data sets created and read with an RMAN External Data Mover (EDM). In the EDM case the parameters are read and used by the ORAEDM program running in a separate address space instead of the Oracle server.
In the Oracle server, the ORA$FPS file management parameters are meaningful mainly for files for which the Oracle server issues the IDCAMS DEFINE CLUSTER command. As discussed in the section "Oracle Database Files", you may have the option of pre-allocating database files by issuing your own DEFINE CLUSTER command. The DEFINE CLUSTER command is discussed in "Pre-allocating Database Files" . This command gives you complete control over most of the DEFINE parameters, which may be desirable when creating the permanent parts of a production database.
Oracle server archive log files cannot be pre-allocated, however. Assuming that you are running your database instance in ARCHIVELOG mode, these files are created by the Oracle server whenever an online log fills and is archived. They are created using a generated data set name whose pattern you control with init.ora file parameters. Because these files must be created by the server, the file management parameters for the DBAL group are critical for proper operation of the database.
Server file management parameters are read during service startup. You can change the parameters and cause the server to reread them without stopping and restarting the service, by using a MODIFY command. This is discussed in the section "Other Database Service Commands".
File management parameters are organized by file group, with each group having a distinct four-character name. The current file group names are as follows:
DBBA - database archive log backup piece (RMAN backup to disk) (VSAM)
DBBI - database incremental backup piece (RMAN backup to disk) (VSAM)
DBBK - database datafile backup piece (RMAN backup to disk) (VSAM)
DBCH - database change tracking file (created for ALTER DATABASE ENABLE BLOCK CHANGE TRACKING) (VSAM)
DBDB - database datafile (one that is part of a tablespace) (VSAM)
DBOS - database transferred file (output of the DBMS_FILE_TRANSFER package) (VSAM)
DBPM - database text parameter file (including PFILE) (non-VSAM)
In addition to the above, the file group name DFLT can be specified to supply default parameters. Default parameters are used for any group that you do not specify explicitly.
The External Data Mover (EDM), discussed in Chapter 6, "Database Backup and Recovery", executes in a separate address space from the Oracle database server and supports database file backup and restore operations under control of the RMAN utility. EDM uses file management parameters to control the creation of non-VSAM sequential backup data sets on disk or tape.
In this case, the ORA$FPS DD statement in the EDM JCL procedure supplies the parameters. The file groups for EDM backups all have names of the form Pnnn where nnn is the storage pool number from an RMAN BACKUP request. Certain parameters are specific to EDM and are ignored when specified for file groups used by the Oracle database server.
The ORA$FPS parameter file contains file group definitions which are specified using keyword(value) syntax. Each definition must start with the keyword FILE_GROUP(name) and continues until the next FILE_GROUP (name) keyword is encountered. Comments must start with an asterisk (*) and can begin in any column as long as comments (that are on the same line as keywords) are separated from the last keyword by at least one blank.
Keywords can be coded one per line or strung together on the same line separated by at least one blank, but a keyword(value) pair cannot be split across two lines. No defaults are defined for the parameters. If a keyword is not coded, then it will not be used on the DEFINE CLUSTER or dynamic allocation that is used to create data sets in the associated group. The only parameter that can be overridden or supplied from the SQL command line is SPACE, which the Oracle server supports via the SIZE keyword in SQL statements that specify files. The default file group (DFLT) supplies parameters for any file group that is completely omitted from the file management parameters. Keywords are described in the following list:
| Keyword | Description |
|---|---|
BUFNO(nnn) |
Specifies the number of I/O buffers to be allocated by EDM for use during conventional (non-proxy) backup and restore operations, where nnn is a decimal number from 1 to 255. Each buffer is equal in size to the block size of the associated backup data set. The buffers are allocated above the 16MB line.
BUFNO(3) is the default. |
CREATE_MODELDSN(dsn) |
Specifies a data set name to use as a MODEL in IDCAMS DEFINE CLUSTER commands. This value is mutually exclusive with the SMS class name parameters. CREATE_MODELDSN can be abbreviated MODEL. |
DATACLAS(classname) |
Specifies an SMS data class name to be specified on DEFINE CLUSTER or dynamic allocation requests to create new data sets. DATACLAS can be abbreviated DATACL. |
DEFAULT_SPACE(primary secondary) |
Specifies default primary and secondary space quantities for a data set that is being created. The primary quantity applies only in situations where the Oracle server has not indicated the desired file size. The secondary quantity is optional and is meaningful only for non-VSAM data sets created by the server, such as trace files. It is ignored for the VSAM LDS clusters comprising the database. Both values must be numbers and are expressed in kilobyte (1024-byte) units. DEFAULT_SPACE can be abbreviated SPA. |
DSS_COMPRESS(YES|NO) |
Specifies whether DFSMSdss should compress data sets dumped during a proxy backup. This parameter is applicable to proxy backup only; if specified for conventional backup, it is ignored. YES indicates that DFSMSdss will compress dumped data sets. NO indicates that DFSMSdss will not compress dumped data sets. The default is NO. |
EDM_TAPEDSN(dsname) |
Specifies the data set name to be used for allocation purposes during a proxy backup or restore operation. The data set is not cataloged but it is placed in the tape header. System symbols may be used to ensure uniqueness, thus preventing enqueue contention during concurrent proxy operations. This parameter is applicable to proxy backup only; if specified for conventional backup, it is ignored. The default value is ORACLE.EDMBKUP.Axxxx.D&&LYYMMDD..T&&LHHMMSS , where xxxx is the address space ID of the EDM. |
EXPIRATION_DATE (yyyyddd|yyddd) |
Specifies a data set expiration date to be used for allocation during EDM backup data set creation or restoration, where yyyyddd is a four-digit year and three-digit day of the year, and yyddd is a two-digit year and three-digit day of the year. EXPIRATION_DATE can be abbreviated EXPDT. |
FILE_GROUP(name) |
Specifies the file group to which the file management parameters belong, where name is one of the allowed 4-letter file group names. This ends any in-progress file group definition and begins a new one. FILE_GROUP can be abbreviated FILE. |
MGMTCLAS(classname) |
Specifies an SMS management class name to be specified on DEFINE CLUSTER or dynamic allocation requests to create new data sets. MGMTCLAS can be abbreviated MGMTCL. |
RECALL(ALL|NONE) |
Specifies whether migrated data sets may be recalled during backup data set allocation for an EDM restore operation. ALL indicates that the recall of migrated data sets is allowed during backup data set allocation. NONE indicates the recall of migrated data sets is not allowed during backup data set allocation. The default is RECALL(NONE). |
SHAREOPTION(n) |
Specifies the VSAM cross-region shareoption to be used on DEFINE CLUSTER.
|
STORCLAS(classname) |
Specifies an SMS storage class name to be specified on DEFINE CLUSTER or dynamic allocation requests to create new data sets. STORCLAS can be abbreviated STORCL. |
UNIT(unitname) |
Specifies an allocation unit name to use in dynamic allocation requests that create new non-VSAM data sets. This parameter is intended for future use when non-VSAM files use file management parameters. |
VOLUMES(volser) |
Specifies a volume serial number to use in IDCAMS DEFINE CLUSTER commands for VSAM data sets or in dynamic allocation requests that create non-VSAM data sets. EDM uses this parameter for conventional (non-proxy) backups, only.
Only a single volume serial can be specified. Because of this limitation, it is recommended that you use storage management class parameters instead of explicit volumes. |
VOLUME_COUNT(nnn) |
Specifies a volume count to be used for allocation during EDM backup data set creation, where nnn is a decimal number between 1 and 255. This parameter is normally used when the backup data set is to reside on tape. VOLUME_COUNT can be abbreviated COUNT. |
Storage Management Parameter Example
The following is an example of a storage management parameter:
* Oracle server file management parameters * Tablespace data files FILE_GROUP(DBDB) DEFAULT_SPACE(10000 ) * a comment CREATE_MODELDSN(ORBL.ORAV8.DB1) * Archive logs FILE_GROUP(DBAL) DEFAULT_SPACE(11100 9000) DATACLAS(OSDIDC2) MGMTCLAS(OSDIMC2) * 2 keywords on one line * Default for groups not specified FILE_GROUP(DFLT) DEFAULT_SPACE(10000 5000) UNIT(SYSDA) VOL(TEMP01)
If you choose to pre-allocate Oracle server control, log, or database files, then you execute the z/OS IDCAMS utility, and you supply one or more DEFINE CLUSTER commands. Alternatively, DEFINE CLUSTER can be issued directly in a TSO session. For details on the DEFINE CLUSTER command, refer to the IBM document DFSMS Access Method Services for Catalogs. This section discusses DEFINE CLUSTER requirements specific to Oracle database files.
You can give an Oracle database file any data set name that conforms to your installation's naming standards and/or security requirements. You will specify this name to the Oracle server later (in a SQL statement or, in the case of control files, in the init.ora parameter file) using the file name syntax discussed earlier in this chapter.
Note:
Oracle Corporation recommends using a consistent set of qualifiers for the left-hand portion of all data set names associated with a given database. Certain Oracle database features, particularly the standby database features, are usable only when all data sets comprising the database share a common set of leftmost data set name qualifiers.The amount of space to allocate to a file depends on how the file is used and on your requirements. Refer to the Oracle Database Administrator's Guide for discussion of database file sizing for each type of file. The IDCAMS DEFINE command can specify space in any of several different units. Choose the unit that is easiest for you. The Oracle server has no preference for space allocation units. Space can be specified in tracks, cylinders, megabytes, kilobytes, or records. Any secondary space quantity in your DEFINE is ignored by the Oracle server.
When you specify the pre-allocated file to the Oracle server in a SQL CREATE or ALTER statement, you must omit the SIZE keyword and specify REUSE, indicating that the file already exists. Except in the case of control files, the Oracle server will use all of the primary space that you pre-allocated. (With control files, the Oracle server uses exactly the amount of space it needs to contain the internal control structures, whose size depends on some of the other parameters of CREATE DATABASE. This might be less than the space that you pre-allocated.)
Oracle recommends that you pre-allocate ultra large datafiles, as in the following example:
DEFINE CLUSTER( -
NAME( ORACLE.V10G.BIGFILE ) -
LINEAR -
DATACLASS( ULDCLASS ) -
MEGABYTES( 6144 ) )
After pre-allocating the datafile, the tablespace can be created with the REUSE keyword, which causes the pre-allocated datafile to be used. For example:
CREATE BIGFILE TABLESPACE bigtbs
DATAFILE 'oracle.v10g.bigfile'
REUSE;
The DEFINE command must specify the LINEAR keyword, indicating that a VSAM LDS is being created. An LDS always has a control interval size of 4K and does not contain VSAM logical record structures. DEFINE parameters such as CONTROLINTERVALSIZE and RECORDSIZE are therefore not used. (If space is specified using RECORDS, then IDCAMS assumes that each record equates to one 4K CI.) The VSAM SHAREOPTIONS default of SHR(1,3) is recommended for all database files except when the server is configured for multiple address space (MAXAS is greater than one). In this case, if automatic file extension is desired (using the AUTOEXTEND clause of the CREATE/ALTER TABLESPACE command), SHR(3,3) is required.
Depending on the standards of your installation, you may need to specify VOLUMES or one or more of the SMS parameters (STORAGECLASS, MANAGEMENTCLASS, and DATACLASS) in the DEFINE command.
The following is an example DEFINE command for an Oracle database file:
DEFINE CLUSTER( - NAME(VSAM.QUALS.SYSTEM.DBF2)- LINEAR - STORAGECLASS(OSCM3A) - MANAGEMENTCLASS(OMCM3A) - MEGABYTES(150))
No other preparation, loading, or formatting is required before a pre-allocated file is added to the database. When you specify the new file in a SQL statement (such as ALTER DATABASE or CREATE TABLESPACE), the server will format all of the primary space of the data set. Adding a large file to the database will therefore incur a noticeable delay while formatting is done. (This is true whether files are pre-allocated or created by the server.)
The Oracle Managed Files (OMF) feature of Oracle Database on z/OS simplifies database administration by eliminating the need to specify the names of database files (control, log, and tablespace files) and to delete underlying files when the owning database element is logically dropped.
When you use OMF, you can omit the single-quoted filenames in the CREATE/ALTER DATABASE and CREATE/ALTER TABLESPACE statements, because the Oracle server generates unique names for each file. When you drop an OMF log file or a tablespace comprising OMF files, the Oracle server deletes the files. In the case of DROP TABLESPACE, you can omit the INCLUDING CONTENTS AND DATAFILES clause.
To use OMF, you must do the following:
Specify certain init.ora file parameters involved in name generation
Omit the single-quoted filenames from CREATE or ALTER SQL statements
The init.ora file parameters for OMF are DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n. On z/OS, these parameters supply the left-hand portion of a data set name (high-level qualifier and possibly other qualifiers), and they must end with a period. The OMF parameters can be reset or changed without shutting down, using ALTER SYSTEM or ALTER SESSION. For z/OS-specific details on the OMF parameters, see "Oracle Initialization Parameter Considerations". For general information about OMF, refer to Oracle Database Administrator's Guide.
Even when you specify OMF parameters, you can continue to specify explicit file names in CREATE and ALTER statements. In fact, it is necessary to do so when you want to use a preallocated file or reuse an existing file.
Oracle tablespace names can be up to 30 characters long. If you want to be able to associate an OMF-created data set with its owning tablespace, then you must use tablespace names that are distinct in the first eight characters.
The right-hand portion of an OMF-generated filename depends on the type of file and includes an encoded timestamp value for uniqueness. The complete data set name format for OMF files is shown in the following example:
control files: destOMC.Attttttt log files: destOMLnnn.Attttttt permanent tablespace files: destOMD.tsn.Attttttt datafile copy: destOMD.tsn.Attttttt temporary tablespace files: destOMT.tsn.Attttttt archive log files: destOMA.Tnnn.Attttttt datafile backup piece: destOMB.Lnnn.Attttttt datafile incremental backup piece: destOMB.Lnnn.Attttttt archive log backup piece: destOMB.Tnnn.Attttttt rman backup piece: destOMB.Lnnn.Attttttt rman autobackup piece: destOMX.xnnnnnnn.Attttttt block change tracking files: destOMR.Attttttt flash back log files: destOMF.Attttttt
In the previous example, the variables are defined as follows:
| Variable | Description |
|---|---|
|
dest |
is the destination string (_DEST) in the OMF parameter |
|
|
is a three-digit log group number |
|
|
is up to eight characters of the tablespace name |
|
ttttttt |
is the encoded timestamp (which looks like a random mix of letters and numerals) |
|
T |
is the letter "T" followed by a three-digit thread number |
|
Lnnn |
is the letter "L" followed by a three digit incremental level |
|
x |
is the letter "P" if the database has an SPFILE or the letter "T" if the database does not have an SPFILE |
|
nnnnnnn |
is a seven-byte time stamp |
Oracle allows underscores ("_") in a tablespace name, and any that are present are changed to "@" for use in the generated data set name.
Given the 44-character limit on z/OS data set names, the above data set name formats impose a limit of 29 characters on DB_CREATE_ONLINE_LOG_DEST_n and 23 characters on DB_CREATE_FILE_DEST (assuming a tablespace name of eight characters or more).
You can use Oracle-specific and z/OS system symbols in the OMF parameters. The destination string must end with a period after any symbol substitutions have been performed.
SQL statements that exploit OMF are generally the same as their non-OMF counterparts except that single-quoted filenames are missing. REUSE is not recognized for OMF and you can omit SIZE, which defaults to 100M for all types of files.
The following is an example of a CREATE DATABASE command that uses OMF for both log files and for the SYSTEM tablespace:
CREATE DATABASE W1O9 MAXINSTANCES 1 MAXDATAFILES 1000 MAXLOGFILES 10 MAXLOGMEMBERS 1 MAXLOGHISTORY 100 LOGFILE SIZE 40M, SIZE 40M, SIZE 40M DATAFILE SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE 256M;
The repeated "SIZE 40M" results in 3 log files of 40 megabytes each. You can leave the LOGFILE clause off completely and the Oracle server will create two log files (or log file groups) of the default 100 MB size. Similarly, the DATAFILE clause can be omitted if the 100M size is acceptable and no autoextension is required for the SYSTEM tablespace. In fact, if you accept all the defaults, it is possible to specify the CREATE DATABASE command as follows:
CREATE DATABASE W1O9;
Likewise, you can do the same with the CREATE TABLESPACE command.
OMF files are distinguished internally by the presence of ".OMC", ".OML", ".OMT", or ".OMD" in the data set name. To avoid conflict with OMF, avoid using data set names containing these qualifiers in non-OMF operations.
There are various circumstances in which you might want to copy and move database files. For example, you might want to create a standby database. Two different methods are described in the following examples:
Example 1 This method uses DFSMSdss to copy two tablespaces, system and rollback. It works only for standby when the two systems share DASD.
//STEP2 EXEC PGM=ADRDSSU
//SYSPRINT DD SYSOUT=*
//DASD DD UNIT=SYSDA, SPACE=(CYL, (300,100)),
// DISP=(NEW,DELETE,DELETE),DSN=&&TMPNAME
//SYSIN DD *
COPY TOL(ENQF) CATALOG -
OUTDDNAME (DASD) -
DS(INCLUDE( -
ORAF.V900.SYSTEM.DB1 -
ORAF.V900.SYSTEM.RBS -
)) -
RENAMEU( -
(ORAF.V900.SYSTEM.DB1,ORBN.V900.SYSTEM.DB1) -
(ORAF.V900.SYSTEM.RBS,ORBN.V900.SYSTEM.RBS) -
)
//*
Example 2 This method uses the IDCAMS Export/Import utility to copy the system tablespace. It is useful when the file needs to be transmitted to another system. You can use FTP to send the output file from Export to the remote system and then read it with Import to recreate the file. This method can be used for any database file (control files, log files, and tablespaces).
//EXPORTCL EXEC PGM=IDCAMS,REGION=1024K
//SYSPRINT DD SYSOUT=*
//DISKOUT DD DSN=EXPORT.VSAM,DISP=(,CATLG),
UNIT=SYSDA,SPACE=(CYL,(50,50),RLSE)
//SYSIN DD *
EXPORT -
ORAF.V900.SYSTEM.DB1 -
OUTFILE(DISKOUT) -
TEMPORARY
/*
Now transmit to the remote system the EXPORT.VSAM data set, using a method such as FTP.
//STEP01 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
IMPORT IDS(EXPORT.VSAM) -
ODS (ORBN.V900.SYSTEM.DB1) -
OBJECTS -
((ORAF.V900.SYSTEM.DB1 -
NEWNAME(ORBN.V900.SYSTEM.DB1) -
) -
(ORAF.V900.SYSTEM.DB1.DATA -
NEWNANE(ORBN.V900.SYSTEM.DB1.DATA) -
) -
)
