Online DBA

<<Back to Oracle ASM Main Page ASMCMD-9512: failed to update AFD disk string in Oracle Local Repository. $ asmcmd afd_dsset '/dev/xvdd*' AFD managed disk '/dev/xvdc1' is not part of '/dev/xvdd*' AFD managed devices does not match '/dev/xvdd*' ASMCMD-9512: failed to update AFD disk string in Oracle Local Repository. Cause Trying to overwrite ASM_DISKSTRING parameter with the value which does not discovers ASM known disks on OS Solution Modify the command to include all ASM known disk strings. Find out the current ASM_DISKSTRING parameter value and include it in your command while overwriting the ASM_DISKSTRING value using  afd_dsset command $ asmcmd afd_dsget AFD discovery string: /dev/xvc $ asmcmd afd_dsset '/dev/xvc*','/dev/xvdd*' $asmcmd afd_dsget AFD discovery string: /dev/xvc,/dev/xvdd*

<<Back to Oracle DataGuard Main Page dgmgrl- Dataguard Broker Command Hanging for Forever Issue :- When ever I try to run any DGMGRL command it simply hangs. After waiting a long time I get time out message. Solution ; Restart the Broker Process in both Primary and Standby Database SQL> alter system set dg_broker_start=false sid='*'; System altered. SQL> alter system set dg_broker_start=true  sid='*'; System altered. Even If the restarting the broker processes does not solve the issue then you need to re-create the broker configuration. Follow Steps described  here to re-create the broker configuration

<<Back to Oracle DataGuard Main Page ORA-16541: database is not enabled Cause Dataguard Configuration is broken Solution : Re-create the broker configuration. If remove configuration is failing from primary try remove configuration from standby and vice verse.   Step1> Remove Configuration; DGMGRL> remove configuration Removed configuration Step2> Create Configuration from current Primary Database You will encounter  ORA-16584 if creating from standby At standby DGMGRL>  CREATE CONFIGURATION 'APX1P_CONF' AS PRIMARY DATABASE IS 'APX1PP' CONNECT IDENTIFIER IS 'APX1PP.oracle.com'; Error: ORA-16584: operation cannot be performed on a standby database At Primary DGMGRL>  CREATE CONFIGURATION 'APX1P_CONF' AS PRIMARY DATABASE IS 'APX1PP' CONNECT IDENTIFIER IS 'APX1PP.oracle.com'; Configuration "APX1P_CONF" created with primary database "APX1PP" Step3>

<<Back to Oracle ASM Main Page You can use afddriverstate to display detailed information on the current state of the Oracle ASM Filter Driver Syntax  afddriverstate [-h] [-orahome <home_path>] {installed | loaded | version | supported} [-s] $ afddriverstate -h AFD-651: Usage: afddriverstate [-h] [-orahome <home_path>] {installed | loaded | version | supported} [-s] How to Check ASM AFD Driver is Supported on Your Server $ afddriverstate supported AFD-9200: Supported How to Check ASM AFD Driver is Installed $ afddriverstate installed AFD-9203: AFD device driver installed status: 'true' How to Check ASM Which AFD Driver Version is Installed $ afddriverstate version AFD-9325:     Driver operating system kernel version = 4.1.12-32.el7uek.x86_64(x86_64). AFD-9326:     Driver Oracle version = 161110. AFD-9212:     Driver build version = 12.2.0.1 ().. How to Check ASM AFD Driver Loaded $ afddriverstate loaded AFD-9205: AFD device driver lo

<<Back to Oracle ASM Main Page How to Create Snapshots of an Oracle ACFS File System $ /sbin/acfsutil snap create mysnapshot_20180926 /acfsmounts/acfs1 acfsutil snap create: Snapshot operation is complete. $ ls -lrt /acfsmounts/acfs1/.ACFS/snaps/mysnapshot_20180926 How to Rename Snapshots of an Oracle ACFS File System $ /sbin/acfsutil snap rename mysnapshot_20180926 mysnapshot_20180926_L0 /acfsmounts/acfs1 acfsutil snap rename: Snapshot operation is complete. How to Find Oracle ACFS File System Snapshot Information $ /sbin/acfsutil snap info -t mysnapshot_20180926_L0 /acfsmounts/acfs1 mysnapshot_20180926_L0    RO<= read only How to Convert Oracle ACFS Snapshot from RO to RW $  /sbin/acfsutil snap convert -w mysnapshot_20180926_L0 /acfsmounts/acfs1 acfsutil snap convert: Snapshot operation is complete. $ /sbin/acfsutil snap info -t mysnapshot_20180926_L0 /acfsmounts/acfs1 mysnapshot_20180926_L0    RW How to Restore ACFS File System from Snapshot  The current

<<Back to Oracle ASM Main Page ORA-15493: target ADVM compatibility (12.1.0.0.0) exceeds ASM compatibility (11.2.0.2.0) SQL> alter diskgroup DG_TEST set attribute 'compatible.advm'='12.1'; alter diskgroup DG_TEST set attribute 'compatible.advm'='12.1' * ERROR at line 1: ORA-15032: not all alterations performed ORA-15242: could not set attribute compatible.advm ORA-15493: target ADVM compatibility (12.1.0.0.0) exceeds ASM compatibility (11.2.0.2.0) Solution : Increase the  compatible.asm minimum to a value you want  compatible.advm to be. SQL> select NAME,VALUE from v$asm_attribute where name like '%com%' and GROUP_NUMBER=5; NAME                                      VALUE ---------------------------------------- ---------------------------------------- compatible.asm                           11.2.0.2.0 compatible.rdbms                         10.1.0.0.0 compatible.advm                          11.2.0.2.0 SQL> alter d

<<Back to Oracle ASM Main Page How to Create Oracle ACFS File System Step1> Create an ADVM Volume ASMCMD> volcreate -G DG_TEST -s 10G vol3 Step2> Find Volume Info ASMCMD>  volinfo -G DG_TEST vol3 Diskgroup Name: DG_TEST          Volume Name: VOL3          Volume Device: /dev/asm/vol3-69          State: ENABLED          Size (MB): 10240          Resize Unit (MB): 64          Redundancy: UNPROT          Stripe Columns: 8          Stripe Width (K): 1024          Usage:          Mountpath: Step3> Fomat the volume using mkfs command $ /sbin/mkfs -t acfs /dev/asm/vol3-69 mkfs.acfs: version                   = 12.2.0.1.0 mkfs.acfs: on-disk version           = 39.0 mkfs.acfs: volume                    = /dev/asm/vol3-69 mkfs.acfs: volume size               = 10737418240  (  10.00 GB ) mkfs.acfs: Format complete. Step4> Mount the File System # mkdir -p /acfsmounts/acfs1 #/bin/mount -t acfs /dev/asm/vol3-69 /acfsmounts/acfs1 Step5> Chec

<<Back to Oracle ASM Main Page PRKO-2012 : filesystem object is not supported in Oracle Restart $ /sbin/acfsutil registry -a /dev/asm/vol3-69 /acfsmounts/acfs1 Usage: srvctl <command> <object> [<options>]     commands: enable|disable|start|stop|status|add|remove|modify|update|getenv|setenv|unsetenv|config|upgrade|downgrade     objects: database|service|asm|diskgroup|listener|home|ons|oraclehome For detailed help on each command and object and its options use:   srvctl <command> -help [-compatible] or   srvctl <command> <object> -help [-compatible] PRKO-2012 : filesystem object is not supported in Oracle Restart acfsutil registry: ACFS-09173: The srvctl command line "/u01/oragrid/12.2.0.1/grid/bin/srvctl add filesystem -device /dev/asm/vol3-69 -path /acfsmounts/acfs1" failed to execute. (0) acfsutil registry: ACFS-03111: unable to add ACFS mount /acfsmounts/acfs1 within Oracle Registry Cause  Starting with Oracle Database 12c,

<<Back to Oracle ASM Main Page Creating ASM ADVM Volume Using SQLPLUS SQL> alter diskgroup DG_TEST add volume vol1 size 2G; Diskgroup altered. SQL> SELECT DG.NAME,VO.VOLUME_NAME,VO.SIZE_MB,VO.VOLUME_NUMBER,VO.REDUNDANCY,VO.STATE,VO.VOLUME_DEVICE FROM V$ASM_DISKGROUP DG, V$ASM_VOLUME VO WHERE DG.GROUP_NUMBER=VO.GROUP_NUMBER; NAME       VOLUME_NAM    SIZE_MB VOLUME_NUMBER REDUND STATE    VOLUME_DEVICE ---------- ---------- ---------- ------------- ------ -------- ------------------------------ DG_TEST    VOL1             2048             1 UNPROT ENABLED  /dev/asm/vol1-69 Using ASMCMD ASMCMD> volcreate -G DG_TEST -s 10G vol2 ASMCMD> volinfo -G DG_TEST vol2 Diskgroup Name: DG_TEST          Volume Name: VOL2          Volume Device: /dev/asm/vol2-69          State: ENABLED          Size (MB): 10240          Resize Unit (MB): 64          Redundancy: UNPROT          Stripe Columns: 8          Stripe Width (K): 1024          Usage:          Mountpath:

<<Back to Oracle ASM Main Page How to Backup ASM Diskgroup Metadata ASM diskgroup metadata backup is performed using md_backup command. If no diskgroup specified ASM md_backup backs up all the mounted diskgroup metadata. Syntax :         md_backup <backup_file> [--acfs_sec_encr] [-G <diskgroups,...>] Backup All Mounted Diskgroup ASMCMD> md_backup /home/oragrid/dg_bkp Disk group metadata to be backed up: DG_TST2_DATA Disk group metadata to be backed up: DG_TST2_FRA Disk group metadata to be backed up: DG_TST_DATA Disk group metadata to be backed up: DG_TST_FRA Disk group metadata to be backed up: DG_FLEX_DATA .................................................... Current alias directory path: TST2T/764BD41B76B837D5E0530601A8C05A1A Current alias directory path: TST2T/TEMPFILE Current alias directory path: TST1T/769E1B25EFDA4E86E0530601A8C0A64C/TEMPFILE  Backup Specific Diskgroup ASMCMD> md_backup /home/oragrid/dg_bkp_DG_TST2_DATA -G DG_TST2_DAT

<<Back to Oracle ASM Main Page ASM File Name Alias Alias names, or aliases, are intended to provide a more user-friendly means of referring to Oracle ASM files, rather than using the system-generated file names Creating an Alias Name for an Oracle ASM File Name SQL> alter diskgroup DG_TST_DATA add alias '+DG_TST_DATA/TST1T/datafile1.dbf' for '+DG_TST_DATA/TST1T/DATAFILE/system.261.987251371'; Diskgroup altered. Renaming an Alias Name for an Oracle ASM File Name SQL> alter diskgroup DG_TST_DATA rename alias '+DG_TST_DATA/TST1T/datafile1.dbf' to '+DG_TST_DATA/TST2T/datafile1.dbf'; Diskgroup altered. Dropping an Alias Name for an Oracle ASM File Name SQL> ALTER DISKGROUP DG_TST_DATA DROP ALIAS '+DG_TST_DATA/TST2T/datafile1.dbf'; Diskgroup altered. Managing ASM Disk Group Directories Oracle ASM disk groups contain a system-generated hierarchical directory structure for storing Oracle ASM files. If you want you can crea

<<Back to Oracle ASM Main Page How ASM Names Files Every file created in Oracle ASM is provided a file name generated by Oracle Managed Files, known as a fully qualified file name (or system-generated file name). The fully qualified file name represents a complete path name in the Oracle ASM file system. A fully qualified file name has the following form in a non-multitenant environment: +diskgroup/dbname/filetype/filetypetag.file.incarnation With multitenant databases, a fully qualified file name has the following form +diskgroup/cdb/pdb_guid/filetype/filetypetag.file.incarnation filetype is the Oracle file type eg. CONTROLFILE, DATAFILE,ONLINELOG,ARCHIVELOG etc. file.incarnation is the file/incarnation pair, used to ensure uniqueness. cdb is the name of the CDB pdb_guid is the PDB GUID value Examples of fully qualified Oracle ASM file names are: SQL> SELECT NAME FROM V$DATAFILE; NAME -------------------------------------------------------------------

<<Back to Oracle ASM Main Page SQL> alter diskgroup DG_FLEX_DATA drop quotagroup QG_PDB1; alter diskgroup DG_FLEX_DATA drop quotagroup QG_PDB1 * ERROR at line 1: ORA-15032: not all alterations performed ORA-15438: Quota group QG_PDB1 has one or more file groups associated. Solution : $ oerr ora 15438 15438, 00000, "Quota group %s has one or more file groups associated." // *Cause:  One or more file groups were associated with the specified quota //          group and, therefore, the operation was not permitted. // *Action: Drop the file groups or move the file groups to a different quota //          group. Related Posts: Oracle ASM Flex Redundancy and Flex Disk Groups What is Oracle ASM File Groups What is ASM Quota Groups

<<Back to DB Administration Main Page SQL> create pluggable database PDB01 admin user pdb01 identified by pdb01 file_name_convert=( '+DG_FLEX_DATA','+DG_TST_DATA' ); create pluggable database PDB01 admin user pdb01 identified by pdb01 file_name_convert=('+DG_FLEX_DATA','+DG_TST_DATA') * ERROR at line 1: ORA-65005: missing or invalid file name pattern for file - +DG_TST_DATA/TST1T/7637E1C08DFB31DFE0530601A8C097C7/DATAFILE/system.262.98725137 7 Cause Issue in Create Pluggable Database statement. I made a mistake and supplied the diskgroup in incorrect order in file_name_convert parameter Solution SQL>  create pluggable database PDB01 admin user pdb01 identified by pdb01 file_name_convert=( '+DG_TST_DATA','+DG_FLEX_DATA' ); Pluggable database created. Also Add Temp file to the file_name_convert part of the Create Pluggable database statement 

<<Back to Oracle ASM Main Page Restricting Disk Space Usages for a Database in DiskGroup Can we restrict the usages of space in a diskgroup for a database to a defined size just like we define quota for a user in each tablespace? Yes you can if you are on version 12.2. Continue reading this article to see how Starting with oracle 12c Release 2 it is possible to restrict the space usage in diskgroup for a database using ASM QutoaGroup feature. An ASM quota Group is only possible with ASM FLEX diskgroup. To Know more about ASM Flex DiskGroup, ASM File Group and ASM Quota Group read the related posts Oracle ASM Flex Redundancy and Flex Disk Groups What is Oracle ASM File Groups What is ASM Quota Groups PreReq: To achieve space restriction for a database in a diskgroup  1> You must be using 12.2 2> You must have decided for FLEX Diskgroup.  Click Here to Create a Flex DiskGroup 3> You must have created a File group  Click Here to Create File Group. Once

<<Back to Oracle ASM Main Page   SQL> ALTER DISKGROUP DG_FLEX_DATA MODIFY QUOTAGROUP QG_PDB1 SET 'QUOTA' = '10g'; ALTER DISKGROUP DG_FLEX_DATA MODIFY QUOTAGROUP QG_PDB1 SET 'QUOTA' = '10G'                                                                      * ERROR at line 1: ORA-15432: missing or invalid quota Reason : Incorrect SQL Syntax Solution : Check and correct the syntax and rerun the command SQL> ALTER DISKGROUP DG_FLEX_DATA MODIFY QUOTAGROUP QG_PDB1 SET 'QUOTA' = 10G ; Diskgroup altered.

<<Back to Oracle ASM Main Page   ORA-15437: Not enough quota available in quota group QG_PDB1. SQL> create tablespace test datafile '+DG_FLEX_DATA' size 4G; create tablespace test datafile '+DG_FLEX_DATA' size 4G * ERROR at line 1: ORA-01119: error in creating database file '+DG_FLEX_DATA' ORA-17502: ksfdcre:4 Failed to create file +DG_FLEX_DATA ORA-15437: Not enough quota available in quota group QG_PDB1. Reason : You are using quota to limit the space usage for the database in the diskgroup and quota limit is exceeding Solution Increase the quota value for the database Investigation Login to ASM Instance and Find out the Quota for your Database  SQL> SELECT DG.NAME AS DG_NAME,FI.NAME ASM_FG,QO.NAME AS QUOTA_NAME,FI.CLIENT_NAME as DB_NAME,QO.USED_QUOTA_MB,QO.QUOTA_LIMIT_MB FROM V$ASM_FILEGROUP FI, V$ASM_QUOTAGROUP QO , V$ASM_DISKGROUP DG WHERE FI.CLIENT_NAME='PDB1' AND FI.QUOTAGROUP_NUMBER=QO.QUOTAGROUP_NUMBER AND FI

<<Back to Oracle ASM Main Page   What is ASM Quota Groups for Oracle ASM File Groups A quota group defines the quota allocated to a group of Oracle ASM File Groups. A file group belongs to only one quota group. A Quota Group on a  File Group applies limit on the on space a file group can use from the diskgroup with which this quota group is associated. Important notes about quota groups for file groups. 1> A file group can belong to only one quota group. 2> A quota group cannot span multiple disk groups. 3> A quota group describes an aggregate of space used by one file group or multiple file groups in the same disk group. 4> The quota is enforced when a file is created and resized. 5>  A quota is a physical space. 6>  Each quota group has two values: the limit and the current used space. If the limit value set below the current used space. This action prevents any additional space being allocated for files described by file groups associated with th

<<Back to Oracle ASM Main Page   What is Oracle ASM File Groups? An Oracle ASM file group is a group of files that share the same set of properties and characteristics. A major benefit of file groups is the ability to have a different redundancy for each database that shares the same disk group. In other words you can define the redundancy at file level and a diskgroup can store both mirrored as well as  unmirrored (unprotected) file at the same time Important notes about file groups. 1> A disk group contains at least one file group, the default file group. 2> A disk group must have FLEX or EXTENDED redundancy to contain a file group. 3> A disk group can store files belonging to multiple databases with each database having a separate file group. 4> A database can have only one file group in a disk group. 5> A database can span multiple disk groups with multiple file groups in different disk groups. 5> The file groups name of database in multiple disk groups