Oracle : ORA-01186,ORA-01111, ORA-01110, ORA-01157 - MRP Terminated with error 1111 - SOLVED

Datafiles Named "UNNAMED%" 

The datafiles are incorrectly stored under $ORACLE_HOME/dbs location with name 'UNNAMED%' for different reasons. No space available to create the datafiles under the allotted datafile location or if the parameter "STANDBY_FILE_MANAGEMENT" is not set to AUTO

As per Oracle 

STANDBY_FILE_MANAGEMENT : enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. 

Precaution should be taken while using this parameter, Make sure the FILE NAME CONVERT parameter is set correctly. 

1st Case : If the allotted datafiles location is running short on space and a new datafile is added on Primary Database, then the datafile is stored under $ORACLE_HOME/dbs location. 

Fix : Get more space added to the mountpoint and perform the below steps to rename the datafile 

2nd Case : If a new datafile is added on Primary database and the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL, then Oracle Recovery Process(MRP) will get terminated due to wrong parameter setting and create the datafile under $ORACLE_HOME/dbs location.

Fix : Rename the Datafile to correct name and change the parameter STANDBY_FILE_MANAGEMENT

I had the 2nd Case on my Database

Errors From Alert Log file:-

Mon Oct 26 14:02:03 2020

Managed Standby Recovery starting Real Time Apply

Mon Oct 26 14:02:03 2020

Errors in file /usr/app/oracle/diag/rdbms/SDB1/SDB11/trace/SDB11_dbw0_2960.trc:

ORA-01186: file 997 failed verification tests

ORA-01157: cannot identify/lock data file 997 - see DBWR trace file

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

File 997 not verified due to error ORA-01157

Mon Oct 26 14:02:04 2020

MRP0: Background Media Recovery terminated with error 1111

Errors in file /usr/app/oracle/diag/rdbms/SDB1/SDB11/trace/SDB11_pr00_342871.trc:

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

ORA-01157: cannot identify/lock data file 997 - see DBWR trace file

ORA-01111: name for data file 997 is unknown - rename to correct file

ORA-01110: data file 997: '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997'

Managed Standby Recovery not using Real Time Apply

Fix:-

Check for the files needs to be recovered.

SQL> select file#,name,status from v$datafile where status not in ('ONLINE','SYSTEM');

     FILE# NAME                                                    STATUS

---------- ------------------------------------------------------- -------

       997 /usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997    RECOVER

Get the datafile from Primary Database

     FILE# NAME                                       STATUS

---------- --------------------------------------------------

       997 +DATA/PDB/datafile/tbs1.4268.1054563669    ONLINE

- Stop Recovery on Standby Database ( Most Likely it wont be running as Oracle will be terminating it)

SQL > RECOVER MANAGED STANDBY DATABASE CANCEL;

- Make sure STANDBY_FILE_MANAGEMENT is set to MANUAL, if not then run below

SQL> alter system set standby_file_management=MANUAL scope=both sid='*';

System altered.

SQL> alter database create datafile '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997' as '+DATA/SDB1/datafile/tbs1.4268.1054563669';

 *

 ERROR at line 1:

 ORA-01276: Cannot add file

 +DATA/SDB1/datafile/tbs1.4268.1054563669. File has an Oracle Managed Files file name.

 

This Failed for me as we were managing the ASM with OMF. With Oracle Managed Files, you specify file system directories in which the database automatically creates, names, and manages files at the database object level. For example, you need only specify that you want to create a tablespace; you do not need to specify the name and path of the tablespace's data file with the DATAFILE clause. This feature works well with a logical volume manager (LVM).

 

With OMF in place, its easier to rename the dummy datafile

SQL> alter database create datafile '/usr/app/oracle/product/11.2.0/home/dbs/UNNAMED00997' as new;

Database altered.

-- Set the Parameter back to AUTO

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

-- Start Recovery Process 

SQL> alter database recover managed standby database disconnect from session;

Database altered.

-- This fixed my issue and got the MRP started back and all Datafiles ONLINE.  Hope it resolves your Issue... 

ORA-12537: TNS:connection closed - Oracle RAC 11g and above

 Client was getting Below error while connecting to the RAC database

sqlplus tester@RACDB_1

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 22 11:34:07 2020

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:

ORA-12537: TNS:connection closed

Issue : 

The Oracle Binary permissions got changed and was not allowing the connections

Current Permissions under Oracle Home

[oracle@host1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwxr-sr-x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

It should be set to 6751 and should look like "-rwsr-s--x"

But doing chmod 6751 on oracle binary was not setting the correct permissions

[oracle@host1 bin]$ chmod 6751 oracle

[oracle@host1 bin]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-x--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Running below as RDBMS database Owner user helped , in this case "oracle" user

1) Stop the database instance where the permissions got changed

 srvctl stop instance -d RACDB -i RACDB1

 2) Run as Oracle Database owner, in this case its Oracle OS user.  

[oracle@host1 ~]$ $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle

3) The permissions got changed and resolved the connection issues

[oracle@host1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-s--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Hope this resolves your issue.. 

Oracle : Query to find ASM Freespace with Redundancy

Below Query will show how much space is available to use incase of High or Normal Redundancy

TOTAL_MB:- Refers to Total Capacity of the Diskgroup
FREE_MB :- Refers to raw Free Space Available in Diskgroup in MB.

FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))

REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.

USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Its calculated as :-

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy
USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy


Query to Run:

column total format 999,999 Heading "Total(G)"
column free format 999,999 Heading "Free (G)"
column Mirror_GB format 999,999 Heading "Space Used |for Mirroring(G)"
column Usable_GB format 999,999 Heading "Space Available |to Use(G)"
column pct format 999.0 Heading "% Free |in DG" 
column pct2 format 999.0 Heading "Real % Free |in DG" 
column type format a10
column name format a20
set linesize 200
set colsep '|'
prompt
Prompt "NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"
prompt
select name,type, TOTAL_MB/1024 total, FREE_MB/1024 free, REQUIRED_MIRROR_FREE_MB/1024 Mirror_GB, USABLE_FILE_MB/1024 Usable_GB ,100-((total_MB-FREE_MB)/total_mb)*100 pct, 100-((total_MB-USABLE_FILE_MB)/total_mb)*100 pct2  from v$asm_diskgroup;


Sample Output :

"NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"


                    |          |        |        |     Space Used |Space Available |% Free |Real % Free
NAME                |TYPE      |Total(G)|Free (G)|for Mirroring(G)|       to Use(G)|  in DG|       in DG
--------------------|----------|--------|--------|----------------|----------------|-------|------------
DATA1              |HIGH      | 260,496|  57,951|          14,472|          14,493|   22.2|         5.6
REDO1              |HIGH      |  65,124|  33,322|           3,618|           9,901|   51.2|        15.2

ORACLE RAC : TERMINATING THE INSTANCE DUE TO ERROR 304

After refreshing my QA database using RMAN DUPLICATE, my instance startup was failing with below error


USER (ospid: 60897): terminating the instance due to error 304
Instance terminated by USER, pid = 60897
Wed Mar 06 02:14:34 2019
Starting ORACLE instance (normal)

Looking into the spfile, I noticed the the database was pulling wrong instance_number and thread numbers even though the DB configuration was correct

$ srvctl config database -d oradb
Database unique name: oradb
Database name: oradb
Oracle home: /opt/app/oradb/oracle/product/11.2.0.4
Oracle user: oradb
Spfile: +oradb_DATA/oradb/spfileoradb.ora
Domain: db.abc.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: oradb
Database instances: oradb1,oradb2
Disk Groups: oradb_DATA,oradb_FRA,oradb_REDO1,oradb_REDO2
Mount point paths: 
Services: oradb_1_2.db.abc.com,oradb_2_1.db.abc.com
Type: RAC
Database is administrator managed

From the pfile I created from the current spfile I could see 

*.instance_number=2
*.thread=2

To resolve this, bring down the complete database and just start the failing instance, in our case instance 1

srvctl start instance -d oradb -i oradb1

Once the instance is started, login to SQL and run below

SQL> alter system set instance_number=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set thread=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set undo_tablespace='UNDO01' sid='oradb1';

System altered.

shutdown the instance and start the complete database

SQL> shutdown immediate

srvctl start database -d oradb


Hope this resolves your issue. 

Oracle RAC : Change VIP status from INTERMEDIATE state back to ONLINE state

Check current VIP status:

$ crsctl status resource ora.serv2.vip
NAME=ora.serv2.vip
TYPE=ora.cluster_vip_net1.type
TARGET=ONLINE
STATE=INTERMEDIATE on serv1

Stop the VIP resource:

$ crsctl stop resource ora.serv2.vip
CRS-2673: Attempting to stop 'ora.serv2.vip' on 'serv1'
CRS-2677: Stop of 'ora.serv2.vip' on 'serv1' succeeded

Start the VIP resource:

$ crsctl start resource ora.serv2.vip
CRS-2672: Attempting to start 'ora.serv2.vip' on 'serv2'
CRS-2676: Start of 'ora.serv2.vip' on 'serv2' succeeded

Check current VIP status:

$ crsctl status resource ora.serv2.vip
NAME=ora.serv2.vip
TYPE=ora.cluster_vip_net1.type
TARGET=ONLINE
STATE=ONLINE on serv2

Oracle RAC : ASM instance startup failing with "terminating the instance due to error 482" in alert log

ASM instance startup failing with "terminating the instance due to error 482" in alert log

ASM instance alert log shows below error while starting the ASM instance on second node

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1994] [PC:0x43EFF99, kjbmprlst()+1369] [flags: 0x0, count: 1]

Errors in file /opt/app/oragrid/orabase/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_39620.trc  (incident=224081):

ORA-07445: exception encountered: core dump [kjbmprlst()+1369] [SIGSEGV] [ADDR:0x1994] [PC:0x43EFF99] [Address not mapped to object] []

Incident details in: /opt/app/oragrid/orabase/diag/asm/+asm/+ASM2/incident/incdir_224081/+ASM2_lmd0_39620_i224081.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Dumping diagnostic data in directory=[cdmp_20180131051633], requested by (instance=2, osid=39620 (LMD0)), summary=[incident=224081].

PMON (ospid: 39577): terminating the instance due to error 482

Fix :

The cluster_database parameter was changed to FALSE which was resulting in the ASM startup failures

Please run the below from the running cluster node making change to the ASM instance

SQL> alter system set cluster_database=TRUE scope=spfile sid='+ASM2';

System altered.

This should start your ASM instance now. I hope this resolves your issue.