DG Creation failed while trying to create spfile - ERROR : Unable to create spfile ORA-01034: ORACLE not available (Doc ID 2988527.1)

Last updated on AUGUST 23, 2024

Applies to:

Symptoms

DG Creation failed while trying to create spfile from pfile on standby

dgrops_duplicate_db_service_2023-11-12_02:26:20.116679132046.log

2023-11-12 02:26:28.962510 - Output from cmd /bin/hostname -f run on localhost is:
2023-11-12 02:26:28.984151 - uk1lpvdbora102.mfil.local
2023-11-12 02:26:28.988246 - cmd took 0.0111761093139648 seconds
2023-11-12 02:26:28.991903 - Output from cmd /bin/su -- oracle -c '/bin/ssh -q -2 -o ServerAliveInterval=100 -o ConnectTimeout=5 -o StrictHostKeyChecking=no oracle@uk1lpvdbora202.mfil.local '"'"'/bin/grep '"'"'"'"'"'"'"'"'DBNAME_1:/u02/app/oracle/product/12.1.0/dbhome_1'"'"'"'"'"'"'"'"' /etc/oratab || echo '"'"'"'"'"'"'"'"'DBNAME_1:/u02/app/oracle/product/12.1.0/dbhome_1:N'"'"'"'"'"'"'"'"' >> /etc/oratab'"'"'' run on localhost is:
2023-11-12 02:26:29.356709 - cmd took 0.354856967926025 seconds
2023-11-12 02:26:29.360295 - INFO: NO of retries = 1

2023-11-12 02:26:29.364047 - INFO: exec_on_node end:

2023-11-12 02:26:29.370978 - pfile is : /var/opt/oracle/dbaas_acfs/DBNAME/DBNAME_1_pfile.ora
2023-11-12 02:26:29.374552 - Spfile is : +DATAC2/DBNAME_1/PARAMETERFILE/spfileDBNAME_1.ora
2023-11-12 02:26:29.378452 - Creating a spfile from pfile
2023-11-12 02:26:29.385665 - Output from sql $VAR1 = [
'startup nomount pfile=\'/var/opt/oracle/dbaas_acfs/DBNAME/DBNAME_1_pfile.ora\'',
'create spfile=\'+DATAC2/DBNAME_1/PARAMETERFILE/spfileDBNAME_1.ora\' from pfile=\'/var/opt/oracle/dbaas_acfs/DBNAME/DBNAME_1_pfile.ora\'',
'shutdown abort'
];
is:
2023-11-12 02:26:29.392074 - DEBUG: SQL Executing set hea off set pagesize 5000 set linesize 400 set newpage none set feedback off prompt =START= startup nomount pfile='/var/opt/oracle/dbaas_acfs/DBNAME/DBNAME_1_pfile.ora'; create spfile='+DATAC2/DBNAME_1/PARAMETERFILE/spfileDBNAME_1.ora' from pfile='/var/opt/oracle/dbaas_acfs/DBNAME/DBNAME_1_pfile.ora'; shutdown abort; quit
2023-11-12 02:26:47.998553 - ORACLE instance started.
Total System Global Area 8589934592 bytes
Fixed Size 2944952 bytes
Variable Size 1409286216 bytes
Database Buffers 6895435776 bytes
Redo Buffers 282267648 bytes
ORACLE instance shut down.
2023-11-12 02:26:48.004104 - Completed in 18.6191239356995 seconds
2023-11-12 02:26:48.007517 - sql execution failed:
ORA-01034: ORACLE not available2023-11-12 02:26:48.011279 - ERROR : Unable to create spfile ORA-01034: ORACLE not available
2023-11-12 02:26:48.015511 -
CDG-50104 : DataGuard Configuration failed for DBNAME
ERROR: Unable to create spfile using pfile

Command executing:
==============
startup nomount pfile=\'/var/opt/oracle/dbaas_acfs/DBNAME/DBNAME_1_pfile.ora\'',
'create spfile=\'+DATAC2/DBNAME_1/PARAMETERFILE/spfileDBNAME_1.ora\' from pfile=\'/var/opt/oracle/dbaas_acfs/DBNAME/DBNAME_1_pfile.ora\'',
'shutdown abort'

As per sequence spfile has to create before shut abort.

Command Output:
=============
Total System Global Area 8589934592 bytes
Fixed Size 2944952 bytes
Variable Size 1409286216 bytes
Database Buffers 6895435776 bytes
Redo Buffers 282267648 bytes
ORACLE instance shut down.

Based on the Error output, its shutting down the database before creating the spfile.

From standby Alert Log.
===================

Sun Nov 12 02:26:43 2023
The database resource spfile location is updated <------ its creating the spfile ..?
Shutting down instance (abort)
License high water mark = 3
USER (ospid: 133841): terminating the instance
Sun Nov 12 02:26:44 2023
Instance terminated by USER, pid = 133841
Sun Nov 12 02:26:44 2023
Instance shutdown complete
Sun Nov 12 02:26:44 2023
ORA-1092 : opitsk aborting process

But from alert log spfile is getting created, same time SPfile in ASM DG at that execution phase. 

Changes

glogin set on all the environment 

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.