Vryjdfkk

ENVIRONMENT INFO:

[oragrid@condor ~]$ uname -a  

Linux condor 3.10.0-862.14.4.el7.x86_64 #1 SMP Wed Sep 26 15:12:11 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

New installation of Oracle Database 11gR2 Enterprise Edition running in an Oracle Restart environment.

First installed Oracle Grid for Standalone Server, then Oracle Database 11gR2 software only, followed by creation of the database using DBCA.

PROBLEM DEFINED:

The database does not come online after Oracle HAS (ohasd.bin) is started. All other resources, including disk groups, are brought up online automatically. Attempting to start the database manually though Oracle Restart results in a failure with several error codes. Though an internet search of the error codes suggests a possible issue with the values of ORACLE_HOME or ORACLE_SID, I don't believe that is the case for three reasons. First, I checked them. Second, because I am able to start the database through SQLplus. And third, because the log file identified in the error message appears to reveal the system is trying to start the database using a PFILE I have never created or seen. (Nor have I ever attempted to start the database with a PFILE.) I specified an ASM-stored SPFILE when I added the database to the Oracle Restart configuration.

So my questions are:

Why is the system attempting to use a PFILE to initialize the database rather than the SPFILE that I specified in the database's Oracle Restart configuration?

And what could be over-riding the SPFILE specified in the Oracle Restart registry?

The following provides more detail as well as troubleshooting process.

BACKGROUND INFO:

[oragrid@condor ~]$ crsctl start has

CRS-4123: Oracle High Availability Services has been started.



[oragrid@condor ~]$ crs_stat -t

Name           Type           Target    State     Host        



ora.DATA.dg    ora....up.type ONLINE    ONLINE    condor      

ora.FRA.dg     ora....up.type ONLINE    ONLINE    condor      

ora....ER.lsnr ora....er.type ONLINE    ONLINE    condor      

ora.asm        ora.asm.type   ONLINE    ONLINE    condor      

ora.cssd       ora.cssd.type  ONLINE    ONLINE    condor      

ora.diskmon    ora....on.type ONLINE    ONLINE    condor      

ora.oraprd1.db ora....se.type ONLINE    OFFLINE



[oragrid@condor ~]$ srvctl start database -d oraprd1

PRCR-1079 : Failed to start resource ora.oraprd1.db

CRS-5011: Check of resource "oraprd1" failed: details at "(:CLSN00007:)"

in "/u01/app/oragrid/product/11.2.0/grid/log/condor/agent/ohasd/oraagent_oragrid/oraagent_oragrid.log"

CRS-2674: Start of 'ora.oraprd1.db' on 'condor' failed

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

.

[oradb@condor ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 12 09:55:10 2018

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect sys as sysdba

Enter password:



Connected to an idle instance.

SQL> startup

ORACLE instance started.



Total System Global Area 3240239104 bytes

Fixed Size                  2217712 bytes

Variable Size            1811941648 bytes

Database Buffers         1409286144 bytes

Redo Buffers               16793600 bytes

Database mounted.

Database opened.

SQL> shutdown                                         

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> disconnect

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



SQL> exit

[oradb@condor ~]$ srvctl start database -d oraprd1

PRCR-1079 : Failed to start resource ora.oraprd1.db

CRS-5011: Check of resource "oraprd1" failed: details at "(:CLSN00007:)"

in "/u01/app/oragrid/product/11.2.0/grid/log/condor/agent/ohasd/oraagent_oragrid/oraagent_oragrid.log"

CRS-2674: Start of 'ora.oraprd1.db' on 'condor' failed

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

Examination of the referenced log file, oraagent_oragrid.log, reveals that the error, "Linux-x86_64 Error: 2: No such file or directory", arises from an attempt by the system to access/open a PFILE named /u01/app/oradb/product/11.2.0/db-home1/dbs/ initoraprd1.ora.new.condor as shown below.

[oragrid@condor oraagent_oragrid]$ cat oraagent_oragrid.log | grep "Failed to open"

2018-12-07 12:51:26.128: [ora.oraprd1.db][3210733312] [start] ConfigFile::update 

Failed to open file /u01/app/oradb/product/11.2.0/db-home1/dbs/initoraprd1.ora.new.condor

I don't know why this attempt was made because I did not create a database PFILE, not with this name or any other name. The Oracle Restart registration for the database includes the SPILE, +DATA/oraprd1/spfileoraprd1.ora as revealed by listing the full Restart configuration for the database.

[oragrid@condor ~]$ crsctl status resource ora.oraprd1.db -f

NAME=ora.oraprd1.db

TYPE=ora.database.type

STATE=OFFLINE

TARGET=ONLINE

ACL=owner:oragrid:--x,pgrp:asmdba:--x,other::r--,group:oinstall:r-,user:oradb:rwx

ACTIVE_PLACEMENT=1

AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%

AUTO_START=always

CARDINALITY=1

CARDINALITY_ID=0

CHECK_INTERVAL=1

CHECK_TIMEOUT=600

CLUSTER_DATABASE=false

CREATION_SEED=33

CURRENT_RCOUNT=0

DB_UNIQUE_NAME=oraprd1

DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= 

CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)

DEGREE=1

DESCRIPTION=Oracle Database resource

ENABLED=1

FAILOVER_DELAY=0

FAILURE_COUNT=0

FAILURE_INTERVAL=60

FAILURE_THRESHOLD=1

ID=ora.oraprd1.db

INCARNATION=0

INSTANCE_FAILOVER=0

LAST_FAULT=0

LAST_RESTART=0

LOAD=1

LOGGING_LEVEL=1

MANAGEMENT_POLICY=AUTOMATIC

OFFLINE_CHECK_INTERVAL=0

ORACLE_HOME=/u01/app/oradb/product/11.2.0/db-home1

PLACEMENT=balanced

RESTART_ATTEMPTS=2

ROLE=primary

SCRIPT_TIMEOUT=60

SPFILE=+DATA/oraprd1/spfileoraprd1

START_DEPENDENCIES=hard(ora.DATA.dg,ora.FRA.dg)weak(type:ora.listener.type,uniform:ora.ons,uniform:ora.eons)pullup(ora.DATA.dg,ora.FRA.dg)

START_TIMEOUT=600

STATE_CHANGE_VERS=0

STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg,shutdown:ora.FRA.dg)



STOP_TIMEOUT=600

UPTIME_THRESHOLD=1h

USR_ORA_INST_NAME=oraprd1

USR_ORA_OPEN_MODE=open

USR_ORA_OPI=false

USR_ORA_STOP_MODE=transactional

VERSION=11.2.0.1.0

Also the Oracle Restart local registry, /u01/app/oragrid/product/11.2.0/grid/cdata/localhost/condor.olr further confirms the registration as shown below. In addition, during the database's creation it appears the DBCA created, in the default location (ORACLE_HOME/dbs/), a PFILE that simply points to the SPFILE on ASM storage. Strangely, while the DBCA created both the PFILE and the SPFILE, it embedded the database name in the SPFILE name but the database SID in the PFILE name. Is that an indicator of an issue?

[oradb@condor ~]$ cat $ORACLE_HOME/dbs/initoraprd1s.ora

SPFILE='+DATA/oraprd1/spfileoraprd1.ora'



ASMCMD> ls -l +DATA/oraprd1/spfileoraprd1.ora



Type....Redund....Striped....Time....Sys....Name



                                      N     spfileoraprd1.ora => +DATA/ORAPRD1/PARAMETERFILE/spfile.266.993223141



[oragrid@condor ~]$ strings /u01/app/oragrid/product/11.2.0/grid/cdata/localhost/condor.olr | grep PFILE



INALITY=1~CHECK_INTERVAL=1~CHECK_TIMEOUT=600~CLUSTER_DATABASE=false

~DB_UNIQUE_NAME=oraprd1~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database)

PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .))

ELEMENT(INSTANCE_NAME=%GEN_USR_ORA_INST_NAME%)~DEGREE=1~DESCRIPTION=Oracle Database resource

~ENABLED=1~FAILOVER_DELAY=0~FAILURE_INTERVAL=60~FAILURE_THRESHOLD=1

~GEN_AUDIT_FILE_DEST=~GEN_USR_ORA_INST_NAME=~HOSTING_MEMBERS=~INSTANCE_FAILOVER=0

~LOAD=1~LOGGING_LEVEL=1~MANAGEMENT_POLICY=AUTOMATIC~NAME=ora.oraprd1.db

~NLS_LANG=~NOT_RESTARTING_TEMPLATE=~OFFLINE_CHECK_INTERVAL=0~ORACLE_HOME=/u01/app/oradb/product/11.2.0/db-home1

~PLACEMENT=balanced~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=2~ROLE=PRIMARY~SCRIPT_TIMEOUT=60

~SERVER_POOLS=

~**SPFILE**=+DATA/oraprd1/spfileoraprd1.ora

~START_DEPENDENCIES=hard(ora.DATA.dg,ora.FRA.dg) weak(type:ora.listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg,ora.FRA.dg)

~START_TIMEOUT=600~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg,shutdown:ora.FRA.dg)

~STOP_TIMEOUT=600~TYPE=ora.database.type~TYPE_ACL=owner:oradb:rwx,pgrp:oinstall:rwx,other::r--

~UPTIME_THRESHOLD=1h~USR_ORA_DB_NAME=~USR_ORA_DOMAIN=orogen.com~USR_ORA_ENV=~USR_ORA_FLAGS=

~USR_ORA_INST_NAME=oraprd1~USR_ORA_OPEN_MODE=open~USR_ORA_OPI=false

~USR_ORA_STOP_MODE=transactional~VERSION=11.2.0.1.0

~uSPFILET=600

~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)

~DEGREE=1~DESCRIPTION=Oracle ASM resource   ~ENABLED=1

~GEN_USR_ORA_INST_NAME=+ASM~LOAD=1~LOGGING_LEVEL=1~NAME=ora.asm~NLS_LANG=~NOT_RESTARTING_TEMPLATE=

~OFFLINE_CHECK_INTERVAL=0~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=5~SCRIPT_TIMEOUT=60

~SPFILE=/u01/app/oragrid/product/11.2.0/grid/dbs/spfile+ASM.ora

~START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)

~START_TIMEOUT=900~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(ora.cssd)

~STOP_TIMEOUT=600~TYPE=ora.asm.type~TYPE_ACL=owner:oragrid:rwx,pgrp:oinstall:rwx,other::r--

~UPTIME_THRESHOLD=1d~USR_ORA_ENV=~USR_ORA_INST_NAME=+ASM~USR_ORA_OPEN_MODE=mount

~USR_ORA_OPI=false~USR_ORA_STOP_MODE=immediate~VERSION=11.2.0.1.0

~SPFILEINALITY=1

~CHECK_INTERVAL=1~CHECK_TIMEOUT=600~CLUSTER_DATABASE=false~DB_UNIQUE_NAME=oraprd1

~DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)~DEGREE=1~DESCRIPTION=Oracle Database resource

~ENABLED=1~FAILOVER_DELAY=0~FAILURE_INTERVAL=60~FAILURE_THRESHOLD=1~GEN_AUDIT_FILE_DEST=

~GEN_USR_ORA_INST_NAME=~HOSTING_MEMBERS=~INSTANCE_FAILOVER=0~LOAD=1~LOGGING_LEVEL=1

~MANAGEMENT_POLICY=AUTOMATIC~NAME=ora.oraprd1.db~NLS_LANG=~NOT_RESTARTING_TEMPLATE=

~OFFLINE_CHECK_INTERVAL=0~ORACLE_HOME=/u01/app/oradb/product/11.2.0/db-home1/~PLACEMENT=balanced

~PROFILE_CHANGE_TEMPLATE=~RESTART_ATTEMPTS=2~ROLE=PRIMARY~SCRIPT_TIMEOUT=60~SERVER_POOLS=

~**SPFILE**=+DATA/oraprd1/spfileoraprd1.ora

~START_DEPENDENCIES=hard(ora.DATA.dg,ora.FRA.dg) weak(type:ora.listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg,ora.FRA.dg)

~START_TIMEOUT=600~STATE_CHANGE_TEMPLATE=~STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg,shutdown:ora.FRA.dg)

~STOP_TIMEOUT=600~TYPE=ora.database.type~TYPE_ACL=owner:oradb:rwx,pgrp:oinstall:rwx,other::r--

~UPTIME_THRESHOLD=1h~USR_ORA_DB_NAME=~USR_ORA_DOMAIN=orogen.com~USR_ORA_ENV=~USR_ORA_FLAGS=

~USR_ORA_INST_NAME=oraprd1~USR_ORA_OPEN_MODE=open~USR_ORA_OPI=false

~USR_ORA_STOP_MODE=transactional~VERSION=11.2.0.1.0~

ATTEMPTED RESOLUTIONS:

1) After removing and then re-registering the database resource from/with Oracle Restart and then attempting to start the database via srvctl the same error occurred.

[oradb@condor ~]$ srvctl remove database -d oraprd1

Remove the database oraprd1? (y/[n]) y



[oradb@condor ~]$ srvctl add database -d oraprd1 -o /u01/app/oradb/product/11.2.0/db-home1 -p +DATA/oraprd1/spfileoraprd1.ora -r primary -s open -t transactional -y AUTOMATIC -a data,fra



[oradb@condor ~]$ srvctl start database -d oraprd1

PRCR-1079 : Failed to start resource ora.oraprd1.db

CRS-5011: Check of resource "oraprd1" failed: details at "(:CLSN00007:)"

in "/u01/app/oragrid/product/11.2.0/grid/log/condor/agent/ohasd/oraagent_oragrid/oraagent_oragrid.log"

CRS-2674: Start of 'ora.oraprd1.db' on 'condor' failed

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

2) Restarted the database again via SQLPLUS and created an SPFILE from memory in the default location. Then, after modifying the Oracle Restart configuration for the database and specifying the SPFILE in $ORACLE_HOME/dbs (not Grid home) and attempting to start the database again (with the srvctl command) the same exact error message was returned. (Also created the PFILE from memory.)

The following shows the contents of the PFILE created from memory.

oraprd1s.__db_cache_size=1426063360

oraprd1s.__java_pool_size=33554432

oraprd1s.__large_pool_size=16777216

oraprd1s.__oracle_base='/u01/app/oradb'#ORACLE_BASE set from environment

oraprd1s.__pga_aggregate_target=1308622848

oraprd1s.__sga_target=1946157056

oraprd1s.__shared_io_pool_size=0

oraprd1s.__shared_pool_size=436207616

oraprd1s.__streams_pool_size=0

*.audit_file_dest='/u01/app/oradb/admin/oraprd1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='+DATA/oraprd1/controlfile/current.260.993222835','+FRA/oraprd1/controlfile/current.256.993222837'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain='orogen.com'

*.db_name='oraprd1'

*.db_recovery_file_dest='+FRA'

*.db_recovery_file_dest_size=31457280000

*.diagnostic_dest='/u01/app/oradb'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraprd1sXDB)'

*.log_archive_dest_1='LOCATION=/u01/app/oradb/oradata/oraprd1/arc-A'

*.log_archive_dest_2='LOCATION=/u02/app/oradb/oradata/oraprd1/arc-B'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=3246391296

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

3) Checked online redo logs and archive logs to make sure they are OK and not the cause of ORA-27101.

SQL> select group#,thread#,sequence#,members,archived,status from v$log;



GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS

     1          1         10          2 YES INACTIVE

     2          1         11          2 YES INACTIVE

     3          1         12          2 NO  CURRENT



SQL> alter system switch logfile;



System altered.



SQL> select group#,thread#,sequence#,members,archived,status from v$log;



GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS

     1          1         13          2 NO  CURRENT

     2          1         11          2 YES INACTIVE

     3          1         12          2 YES ACTIVE

4) Checked that the TNS listener is running an functional.

[oradb@condor ~]$ ps -aux | grep LISTENER

oragrid   6795  0.0  0.1 232976 13208 ?        Ssl  09:14   0:01 /u01/app/oragrid/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit



[oradb@condor ~]$ tnsping condor



TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-DEC-2018 13:16:56



Copyright (c) 1997, 2009, Oracle.  All rights reserved.



Used parameter files:

/u01/app/oragrid/product/11.2.0/grid/network/admin/sqlnet.ora



Used EZCONNECT adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.170)(PORT=1521)))

OK (0 msec)