Ora 01119 error in creating database file

Cause:

The error creeps in ,when datafile or tempfile cant be added due to space crunch on asm diskgroup

kish@exdbx<>create temporary tablespace temp2 tempfile '+DATA' size 1G;
create temporary tablespace temp2 tempfile '+DATA' size 1G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted

Workaround:

Check the space for DATA diskgroup.We have 112 MB space on the diskgroup which has external redundancy.So there is no room for incoming datafile or tempfile to accomodate in DATA diskgroup

ASMCMD> lsdg --discovery
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      5760      112                0             112              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304      1440     1304                0            1304              0             N  DATA2/
MOUNTED  EXTERN  N         512   4096  4194304      5040     1784                0            1784              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      1440     1042                0            1042              0             Y  MGMT/

Check with your storage team to allocate the disks to the server and check disk header status in v$asm_disk

If it is exadata,then all the luns should be added as celldisks which in turn should be mapped to ASM griddisks.In my case, after storage team added 4 arrays(cell20,cell21,cell22,cell23),symbolic link should be created from the logical cylinders

[root@exceladm00 raw]# ls -lrt
total 0
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell23 -> /dev/sdad
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell22 -> /dev/sdac
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell21 -> /dev/sdab
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell20 -> /dev/sdaa

Use a shell script to generate symbolic link commands in one go

fdisk -l 2>/dev/null | grep "805 MB" | awk '{ printf "%s%02dn", "ln -s "$2" cell", NR }'|sed "s/://"

  392  ln -s /dev/sdaa cell20
  393  ln -s /dev/sdab cell21
  394  ln -s /dev/sdac cell22
  395  ln -s /dev/sdad cell23

In storage server,check if physical disk which were allocated reflect!

CellCLI> list physicaldisk

         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell20    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell20          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell21    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell21          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell22    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell22          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell23    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell23          normal

Create celldisk from the physical disk with size clause to allocate only specific size for capacity

CellCLI> create celldisk  all;
CellDisk FD_06_stocell successfully created
CellDisk FD_07_stocell successfully created
CellDisk FD_08_stocell successfully created
CellDisk CD_cell20_stocell successfully created
CellDisk CD_cell21_stocell successfully created
CellDisk CD_cell22_stocell successfully created
CellDisk CD_cell23_stocell successfully created

Next map your asm to celldisks with griddisk creation

CellCLI> create griddisk all harddisk prefix=DATA
Cell disks were skipped because they had no freespace for grid disks: CD_cell10_stocell, CD_cell11_stocell, CD_cell12_stocell, CD_cell13_stocell, CD_cell14_stocell, CD_cell15_stocell, CD_cell16_stocell, CD_cell17_stocell, CD_cell18_stocell.
GridDisk DATA_CD_cell20_stocell successfully created
GridDisk DATA_CD_cell21_stocell successfully created
GridDisk DATA_CD_cell22_stocell successfully created
GridDisk DATA_CD_cell23_stocell successfully created

Newly added disk header status should reflect as CANDIDATE disk.Below,there are 4 candidate disks which can be added to the DATA diskgroup for space.

SQL> select name,header_status,state from v$asm_disk;

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
DATA_CD_CELL07_STOCELL         MEMBER       NORMAL
MGMT_CD_CELL15_STOCELL         MEMBER       NORMAL
FRA_CD_CELL08_STOCELL          MEMBER       NORMAL
MGMT_CD_CELL16_STOCELL         MEMBER       NORMAL
DATA_CD_CELL19_STOCELL         MEMBER       NORMAL
FRA_CD_CELL12_STOCELL          MEMBER       NORMAL
FRA_CD_CELL11_STOCELL          MEMBER       NORMAL

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
DATA_CD_CELL09_STOCELL         MEMBER       NORMAL
FRA_CD_CELL09_STOCELL          MEMBER       NORMAL
DATA_CD_CELL05_STOCELL         MEMBER       NORMAL
DATA_CD_CELL06_STOCELL         MEMBER       NORMAL
FRA_CD_CELL14_STOCELL          MEMBER       NORMAL
FRA_CD_CELL10_STOCELL          MEMBER       NORMAL
DATA_CD_CELL03_STOCELL         MEMBER       NORMAL
FRA_CD_CELL13_STOCELL          MEMBER       NORMAL
DATA_CD_CELL04_STOCELL         MEMBER       NORMAL
DATA_CD_CELL01_STOCELL         MEMBER       NORMAL
DATA_CD_CELL02_STOCELL         MEMBER       NORMAL

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
DATA_CD_CELL08_STOCELL         MEMBER       NORMAL

23 rows selected.

Use the below command with rebalance power 9 to speed up the rebalance operation.Make sure there is sufficient CPU power on your host so that 9 parallel I/O’s can be performed by ARB process.

SQL> alter diskgroup DATA add disk 'o/192.168.56.33/DATA_CD_cell2*' reba                                      lance power 9


NOTE: GroupBlock outside rolling migration privileged region
NOTE: Assigning number (2,8) to disk (o/192.168.56.33/DATA_CD_cell21_stocell)
NOTE: requesting all-instance membership refresh for group=2
NOTE: initializing header on grp 2 disk DATA_CD_CELL21_STOCELL
NOTE: requesting all-instance disk validation for group=2
Thu Mar 25 22:01:18 2021
NOTE: skipping rediscovery for group 2/0x1f0821be (DATA) on local instance.
NOTE: initiating PST update: grp = 2
Thu Mar 25 22:01:20 2021
GMON updating group 2 at 13 for pid 29, osid 9120
NOTE: PST update grp = 2 completed successfully
NOTE: membership refresh pending for group 2/0x1f0821be (DATA)
GMON querying group 2 at 14 for pid 19, osid 4885
NOTE: cache opening disk 8 of grp 2: DATA_CD_CELL21_STOCELL path:o/192.168.56.33                                      /DATA_CD_cell21_stocell
GMON querying group 2 at 15 for pid 19, osid 4885
Thu Mar 25 22:01:34 2021
SUCCESS: refreshed membership for 2/0x1f0821be (DATA)
Thu Mar 25 22:01:36 2021
SUCCESS: alter diskgroup DATA add disk 'o/192.168.56.33/DATA_CD_cell21_stocell'                                       rebalance power 9
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
NOTE: starting rebalance of group 2/0x1f0821be (DATA) at power 9
Starting background process ARB0
Thu Mar 25 22:01:38 2021
ARB0 started with pid=36, OS id=9478
NOTE: assigning ARB0 to group 2/0x1f0821be (DATA) with 9 parallel I/Os

Disk are added to DATA diskgroup and datafile or tempfile to instance can be added

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  Y         512   4096  4194304      7920     2248                0            2248              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304      1440     1304                0            1304              0             N  DATA2/
MOUNTED  EXTERN  N         512   4096  4194304      5040     1752                0            1752              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      1440     1042                0            1042              0             Y  MGMT/

kish@exdbx<>create temporary tablespace temp2 tempfile '+DATA2' size 100M autoextend on next 50M maxsize 1G;

Tablespace created.

Elapsed: 00:00:04.26

Effect of Creating Tablespace / Datafile on Primary when Logical Standby in Place (ORA-01119)

SYMPTOMS

Dataguard environment with Logical standby. 

When we add a new datafile on primary and both primary and standby are in different file name directory, SQL APPLY from production database to Logical Standby database will fail with ORA-01119.

CAUSE

1. If databse is OMF on both primary and standby then no issues in creating datafile at logical standby side.

2. If no OMF then Apply terminate with (ORA-1119) below errors, Because logcal standby will not honour the db_file_name_convert. 

SOLUTION

• Use OMF as a proactive measure.

• If not run skip handler.

WORKOUT

On Production

• create a tablespace as below,

PRODDB>create tablespace tbstest2 datafile ‘/oradata1/PRODDB/tbstestt201.dbf’ size 1m autoextend on next 1m maxsize unlimited;

Tablespace created.

PRODDB>select name from v$tablespace where name =’TBSTEST2′;

NAME

——————————

TBSTEST2

On Logical Standby database

• Check whether the tablespace has replicated from primary site to logical standby site.

LOGSTDB-LOGICAL>select name from v$database where name=’TBSTEST2′;

no rows selected

• From the above result we can understand that, the tablespace created in primary site did not replicate to logical standby. The following steps has to be followed to replicate the tablespace to logical standby site.

• Stop the logical standby apply process.

LOGSTDB-LOGICAL>alter database stop logical standby apply;

Database altered.

• Execute the below procedure.

LOGSTDB-LOGICAL>CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (

OLD_STMT IN VARCHAR2,

STMT_TYP IN VARCHAR2,

SCHEMA IN VARCHAR2,

NAME IN VARCHAR2,

XIDUSN IN NUMBER,

XIDSLT IN NUMBER,

XIDSQN IN NUMBER,

ACTION OUT NUMBER,

NEW_STMT OUT VARCHAR2

) AS

BEGIN

NEW_STMT := REPLACE(OLD_STMT,’/oradata1/PRODDB’,’/oradata2/LOGSTDB’);

ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;

EXCEPTION

WHEN OTHERS THEN

ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;

NEW_STMT := NULL;

END HANDLE_TBS_DDL;

/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39  

Procedure created.

• After creating the procedure, Execute the below dbms statement.

LOGSTDB-LOGICAL>EXECUTE DBMS_LOGSTDBY.SKIP (stmt => ‘TABLESPACE’,proc_name => ‘sys.handle_tbs_ddl’);

PL/SQL procedure successfully completed.

• Start the logical standby apply process.

LOGSTDB-LOGICAL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

• Check whether the tablespace is replicated in logical standby site.

LOGSTDB-LOGICAL>select name from v$tablespace where name=’TBSTEST2′;

NAME

——————————

TBSTEST2