The effect of the OFM feature in ASM on the shadow data file name and the alias used for the data file in the SYSTEM tablespace

Source: Internet
Author: User

The customer has encountered a problem with DG. The storage uses ASM management and has multiple disk disks.
When a data file is created in the master database, the data files automatically created in the slave database are in the same disk group, and the data files created in the master database are specified to be similar **. the DBF name is changed to the numeric format of ASM in the standby database **. 266.123456
This is because the OMF feature is used.
OMF stands for Oracle_Managed Files, which is Oracle file management.
OMF can simplify administrator management without specifying the file name, size, and path. The file name, size, and path are automatically allocated by oracle. When deleting logs, data, and control files that are no longer in use, OMF can also automatically delete their corresponding OS files.

You can view the DB_CREATE_FILE_DEST parameter to check whether the OMF feature is enabled for Oracle databases.
If the value of DB_CREATE_FILE_DEST is null, The OMF function is not enabled. If it is set to the directory name, OMF is enabled.
Lab database version: 11.2.0.4.0,
1. Data File Creation when OMF is enabled 1. The master database creates a data file in the specified disk group, and the slave database creates a data file in the disk group specified by DB_CREATE_FILE_DEST = + DG1.Slave database Parameters
SQL> show parameter standby_file
NAME TYPE VALUE
-----------------------------------------------------------------------------
Standby_file_management string AUTO
SQL> show parameter DB_CREATE_FILE_DEST
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_create_file_dest string + DG1
SQL> col name for a50
SQL> set linesize 1000
SQL> select * from v $ dbfile;
FILE # NAME
------------------------------------------------------------
1 + DG1/dg2/datafile/system.258.852146097
2 + DG1/dg2/datafile/sysaux.257.852146099
3 + DG1/dg2/datafile/undotbs1.256.852146101
4 + DG1/dg2/datafile/users.259.852146107
5 + DG2/dg/datafile/test1.dbf

Create a tablespace for the master database in the DG2 disk group:
SQL> col name for a50
SQL> set linesize 1000
SQL> select * from v $ dbfile;
FILE # NAME
------------------------------------------------------------
1 + DG1/dg/datafile/system.260.852134271
2 + DG1/dg/datafile/sysaux.261.851_1_3
3 + DG1/dg/datafile/undotbs1.262.8520.4329
4 + DG1/dg/datafile/users.264.851_4361
5 + DG2/dg/datafile/test1.dbf
SQL> create tablespace test2 datafile '+ DG2/dg/datafile/test2.dbf' size 10 m;
Tablespace created.
SQL> alter system switch logfile;
System altered.
Query table space and Data File Creation in the backup database: create in the disk group + DG1 specified by DB_CREATE_FILE_DEST
SQL> select * from v $ dbfile;
FILE # NAME
------------------------------------------------------------
1 + DG1/dg2/datafile/system.258.852146097
2 + DG1/dg2/datafile/sysaux.257.852146099
3 + DG1/dg2/datafile/undotbs1.256.852146101
4 + DG1/dg2/datafile/users.259.852146107
5 + DG2/dg/datafile/test1.dbf
6 + DG1/dg2/datafile/test2.272.852147911

Modify the DB_CREATE_FILE_DEST parameter of the standby database to + DG2:
SQL> show parameter DB_CREATE_FILE_DEST

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_create_file_dest string + DG2

Create a tablespace in the master database:
SQL> create tablespace test3 datafile '+ DG1/dg/datafile/test3.dbf' size 10 m;
Tablespace created.
SQL> alter system switch logfile;
System altered.
Query in the slave database:
SQL> select * from v $ dbfile;
FILE # NAME
------------------------------------------------------------
1 + DG1/dg2/datafile/system.258.852146097
2 + DG1/dg2/datafile/sysaux.257.852146099
3 + DG1/dg2/datafile/undotbs1.256.852146101
4 + DG1/dg2/datafile/users.259.852146107
5 + DG2/dg/datafile/test1.dbf
6 + DG1/dg2/datafile/test2.272.852147911
7 + DG2/dg2/datafile/test3.260.852148535


Ii. Disable OMF featuresThe parameter DB_CREATE_FILE_DEST of the standby database is set to null to disable the OMF feature.-- Only the standby database's
SQL> show parameter DB_CREATE_FILE_DEST
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_create_file_dest string + DG2
SQL> alter system set DB_CREATE_FILE_DEST = '';
System altered.
SQL> show parameter DB_CREATE_FILE_DEST

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_create_file_dest string
The master database performs the following operations:
SQL> create tablespace test4 datafile '+ DG1/dg/datafile/test4.dbf' size 10 m;
Tablespace created.
SQL> create tablespace test5 datafile '+ DG2/dg/datafile/test5.dbf' size 10 m;
Tablespace created.
SQL> select * from v $ dbfile;
FILE # NAME
------------------------------------------------------------
1 + DG1/dg/datafile/system.260.852134271
2 + DG1/dg/datafile/sysaux.261.851_1_3
3 + DG1/dg/datafile/undotbs1.262.8520.4329
4 + DG1/dg/datafile/users.264.851_4361
5 + DG2/dg/datafile/test1.dbf
6 + DG2/dg/datafile/test2.dbf
7 + DG1/dg/datafile/test4.dbf
8 + DG2/dg/datafile/test5.dbf
Standby database query:
SQL> select * from v $ dbfile;
FILE # NAME
------------------------------------------------------------
1 + DG1/dg2/datafile/system.258.852146097
2 + DG1/dg2/datafile/sysaux.257.852146099
3 + DG1/dg2/datafile/undotbs1.256.852146101
4 + DG1/dg2/datafile/users.259.852146107
5 + DG2/dg/datafile/test1.dbf
6 + DG1/dg2/datafile/test2.272.852147911
7 + DG1/dg/datafile/test4.dbf

8 + DG2/dg/datafile/test5.dbf

3. Observe the storage format of data file aliases in the asm disk group.

ASMCMD> ls-al
WARNING: option 'A' is deprecated for 'LS'
Please use 'absolutepath'

Type Redund Striped Time Sys Name
Datafile unprot coarse jul 05 22:00:00 Y none => SYSAUX.257.852146099
Datafile unprot coarse jul 05 22:00:00 Y none => SYSTEM.258.852146097
Datafile unprot coarse jul 05 22:00:00 Y none => TEST2.272.852147911
Datafile unprot coarse jul 05 22:00:00 Y + DG1/dg/datafile/test4.dbf => TEST4.279.852157365
Datfile unprot coarse jul 05 22:00:00 Y none => UNDOTBS1.256.852146101
Datafile unprot coarse jul 05 22:00:00 Y none => USERS.259.852146107

ASMCMD> ls-al
WARNING: option 'A' is deprecated for 'LS'
Please use 'absolutepath'

Type Redund Striped Time Sys Name
Datafile unprot coarse jul 05 22:00:00 Y + DG2/dg/datafile/test1.dbf => TEST1.256.852146103
Datafile unprot coarse jul 05 22:00:00 Y + DG2/dg/datafile/test5.dbf => TEST5.260.852157413

4. Use aliases for SYSTEM tablespace in ASM

For example, the data file TEST4.DBF corresponding to the table space test4 created above is an alias. The real data file corresponds to the alias: + DG1/dg/datafile/test4.dbf => TEST4.279.852157365

However, (system, undotbs, sysaux, users) All correspond to real data files without aliases. To use aliases for these tablespaces, You need to recreate the control file. Example:

Alter diskgroup dg1 add alias '+ DG1/dg2/datafile/system1.dbf' FOR' + DG1/dg2/datafile/system.258.852146097 ';
View in ASMCMD:
ASMCMD> ls-al
WARNING: option 'A' is deprecated for 'LS'
Please use 'absolutepath'
Type Redund Striped Time Sys Name
Datafile unprot coarse jul 05 23:00:00 Y none => SYSAUX.257.852146099
Datafile unprot coarse jul 05 23:00:00 Y + DG1/DG2/DATAFILE/SYSTEM1.DBF => SYSTEM.258.852146097
N SYSTEM1.DBF => + DG1/DG2/DATAFILE/SYSTEM.258.852146097
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1365040 bytes
Variable Size 255855568 bytes
Database Buffers 155189248 bytes
Redo Buffers 6074368 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DG" RESETLOGS FORCE LOGGING ARCHIVELOG ............
After the rebuild control file is complete, the database is in the MOUNT State. In this case, query:
SQL> select * from v $ dbfile;
FILE # NAME
------------------------------------------------------------
8 + DG2/dg/datafile/test5.dbf
7 + DG1/dg/datafile/test4.dbf
6 + DG1/dg2/datafile/test2.272.852147911
5 + DG2/dg/datafile/test1.dbf
4 + DG1/dg2/datafile/users.259.852146107
3 + DG1/dg2/datafile/undotbs1.256.852146101
2 + DG1/dg2/datafile/sysaux.257.852146099
1 + DG1/dg2/datafile/system1.dbf


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.