Check spfile location:
======================
srvctl config database -d TEST
srvctl status database -d TEST
sqlplus / as sysdba
show parameter spfile
create pfile:
create pfile from spfile;
create spfile:
create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' pfile;
Logs:
=====
[root@hostnode1 ~]# ps -ef|grep smon
root 7495 1 1 23:50 ? 00:00:05 /u01/app/19.0.0.0/grid/bin/osysmond.bin
oracle 8372 1 0 23:51 ? 00:00:00 asm_smon_+ASM1
oracle 11425 1 0 23:52 ? 00:00:00 ora_smon_TEST1
root 30091 17862 0 23:56 pts/0 00:00:00 grep --color=auto smon
[root@hostnode1 ~]# olsnodes
hostnode1
hostnode2
[root@hostnode1 ~]#
[root@hostnode1 ~]# su - oracle
Last login: Sun Feb 11 23:47:04 IST 2024
[oracle@hostnode1 ~]$ . oraenv
ORACLE_SID = [oracle] ? TEST1
The Oracle base has been set to /u01/app/oracle
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$ env |grep ORA
ORACLE_SID=TEST1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$ srvctl config database -d TEST
Database unique name: TEST
Database name: TEST
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/TEST/PARAMETERFILE/spfileTEST.ora
Password file: +DATA/TEST/PASSWORD/pwdtest.293.1160546127
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oinstall
Database instances: TEST1,TEST2
Configured nodes: hostnode1,hostnode2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$ srvctl status database -d TEST
Instance TEST1 is running on node hostnode1
Instance TEST2 is running on node hostnode2
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 11 23:57:25 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/TEST/PARAMETERFILE/spfil
etest.ora
SQL>
SQL>
SQL> create pfile from spfile;
File created.
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$ srvctl stop database -d TEST
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$
[oracle@hostnode1 ~]$ cd $ORACLE_HOME
[oracle@hostnode1 dbhome_1]$ cd dbs
[oracle@hostnode1 dbs]$ ls -ltrh
total 50M
-rw-r--r--. 1 oracle oinstall 3.1K May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 19M Jul 28 2023 snapcf_TESTCDB1.f
-rw-r-----. 1 oracle oinstall 874 Aug 5 2023 initTEST1.ora.929202384520
-rw-r-----. 1 oracle oinstall 19M Oct 21 23:44 snapcf_DEVCDB1.f
-rw-r--r--. 1 oracle oinstall 1.7K Oct 29 08:19 initDEV1.ora_backup
-rw-r-----. 1 oracle oinstall 923 Oct 29 09:51 initTEST1.ora.110202410641
-rw-r--r--. 1 oracle oinstall 2.1K Jan 27 00:37 initDEVDB1.ora
-rw-rw----. 1 oracle oinstall 1.6K Feb 3 09:43 hc_DEVDB1.dat
-rw-r-----. 1 oracle oinstall 2.0M Feb 3 09:43 id_DEVDB1.dat
-rw-r-----. 1 oracle oinstall 2.0M Feb 4 07:32 id_DEVCDB1.dat
-rw-rw----. 1 oracle oinstall 1.6K Feb 4 07:32 hc_DEVCDB1.dat
-rw-r-----. 1 oracle oinstall 2.0M Feb 10 05:41 id_TESTCDB1.dat
-rw-rw----. 1 oracle oinstall 1.6K Feb 10 05:41 hc_TESTCDB1.dat
-rw-r--r--. 1 oracle oinstall 1.6K Feb 10 09:35 initDEV1.ora
-rw-rw----. 1 oracle oinstall 1.6K Feb 11 23:30 hc_UAT1.dat
-rw-r-----. 1 oracle oinstall 2.0M Feb 11 23:30 id_UAT1.dat
-rw-r-----. 1 oracle oinstall 2.0M Feb 11 23:30 id_DEV1.dat
-rw-rw----. 1 oracle oinstall 1.6K Feb 11 23:30 hc_DEV1.dat
-rw-r--r--. 1 oracle oinstall 1.7K Feb 11 23:57 initTEST1.ora
-rw-r-----. 1 oracle oinstall 2.0M Feb 11 23:58 id_TEST1.dat
-rw-rw----. 1 oracle oinstall 1.6K Feb 11 23:58 hc_TEST1.dat
[oracle@hostnode1 dbs]$ ls -ltr *TEST*
-rw-r-----. 1 oracle oinstall 19709952 Jul 28 2023 snapcf_TESTCDB1.f
-rw-r-----. 1 oracle oinstall 874 Aug 5 2023 initTEST1.ora.929202384520
-rw-r-----. 1 oracle oinstall 923 Oct 29 09:51 initTEST1.ora.110202410641
-rw-r-----. 1 oracle oinstall 2097152 Feb 10 05:41 id_TESTCDB1.dat
-rw-rw----. 1 oracle oinstall 1544 Feb 10 05:41 hc_TESTCDB1.dat
-rw-r--r--. 1 oracle oinstall 1650 Feb 11 23:57 initTEST1.ora
-rw-r-----. 1 oracle oinstall 2097152 Feb 11 23:58 id_TEST1.dat
-rw-rw----. 1 oracle oinstall 1544 Feb 11 23:58 hc_TEST1.dat
[oracle@hostnode1 dbs]$ mv initTEST1.ora initTEST1.ora_backup
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$ srvctl start database -d TEST
PRCR-1079 : Failed to start resource ora.test.db
CRS-5017: The resource action "ora.test.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initTEST1.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/hostnode1/crs/trace/crsd_oraagent_oracle.trc".
CRS-5017: The resource action "ora.test.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initTEST2.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/hostnode2/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.test.db' on 'hostnode1' failed
CRS-2632: There are no more servers to try to place resource 'ora.test.db' on that would satisfy its placement policy
CRS-2674: Start of 'ora.test.db' on 'hostnode2' failed
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$ ls -ltr *TEST*
-rw-r-----. 1 oracle oinstall 19709952 Jul 28 2023 snapcf_TESTCDB1.f
-rw-r-----. 1 oracle oinstall 874 Aug 5 2023 initTEST1.ora.929202384520
-rw-r-----. 1 oracle oinstall 923 Oct 29 09:51 initTEST1.ora.110202410641
-rw-r-----. 1 oracle oinstall 2097152 Feb 10 05:41 id_TESTCDB1.dat
-rw-rw----. 1 oracle oinstall 1544 Feb 10 05:41 hc_TESTCDB1.dat
-rw-r--r--. 1 oracle oinstall 1650 Feb 11 23:57 initTEST1.ora_backup
-rw-r-----. 1 oracle oinstall 2097152 Feb 11 23:58 id_TEST1.dat
-rw-rw----. 1 oracle oinstall 1544 Feb 11 23:58 hc_TEST1.dat
[oracle@hostnode1 dbs]$ mv initTEST1.ora_backup initTEST1.ora
[oracle@hostnode1 dbs]$ srvctl start database -d TEST
PRCR-1079 : Failed to start resource ora.test.db
CRS-5017: The resource action "ora.test.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initTEST2.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/hostnode2/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.test.db' on 'hostnode2' failed
CRS-2632: There are no more servers to try to place resource 'ora.test.db' on that would satisfy its placement policy
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$ srvctl status database -d TEST
Instance TEST1 is running on node hostnode1
Instance TEST2 is not running on node hostnode2
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 12 00:01:47 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$ srvctl config database -d TEST
Database unique name: TEST
Database name: TEST
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/TEST/PARAMETERFILE/spfileTEST.ora
Password file: +DATA/TEST/PASSWORD/pwdtest.293.1160546127
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oinstall
Database instances: TEST1,TEST2
Configured nodes: hostnode1,hostnode2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 12 00:02:42 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
SQL> create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' pfile;
create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' pfile
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' from pfile;
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$ srvctl status database -d TEST
Instance TEST1 is not running on node hostnode1
Instance TEST2 is not running on node hostnode2
[oracle@hostnode1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 12 00:04:17 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' from pfile;
create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' from pfile
*
ERROR at line 1:
ORA-17502: ksfdcre:4 Failed to create file
+DATA/TEST/PARAMETERFILE/spfileTEST.ora
ORA-15056: additional error message
ORA-06512: at line 4
ORA-17502: ksfdcre:4 Failed to create file
+DATA/TEST/PARAMETERFILE/spfiletest.ora
ORA-15173: entry 'PARAMETERFILE' does not exist in directory 'TEST'
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 449
ORA-06512: at line 2
SQL> create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' from pfile;
File created.
SQL> exit
Disconnected
[oracle@hostnode1 dbs]$
[oracle@hostnode1 dbs]$ srvctl start database -d TEST
[oracle@hostnode1 dbs]$ srvctl status database -d TEST
Instance TEST1 is running on node hostnode1
Instance TEST2 is running on node hostnode2
[oracle@hostnode1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 12 00:07:14 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/TEST/PARAMETERFILE/spfil
etest.ora
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@hostnode1 dbs]$
[root@hostnode2 ~]# ps -ef|grep smon
oracle 9688 1 0 23:53 ? 00:00:00 asm_smon_+ASM2
oracle 11671 1 0 23:53 ? 00:00:00 ora_smon_TEST2
root 16924 31455 0 23:56 pts/0 00:00:00 grep --color=auto smon
root 21309 1 1 23:52 ? 00:00:03 /u01/app/19.0.0.0/grid/bin/osysmond.bin
[root@hostnode2 ~]# olsnodes
hostnode1
hostnode2
[root@hostnode2 ~]# su - oracle
Last login: Sun Feb 11 23:54:21 IST 2024
[oracle@hostnode2 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASm2
ORACLE_HOME = [/home/oracle] ? ^C
[oracle@hostnode2 ~]$
[oracle@hostnode2 ~]$ . oraenv
ORACLE_SID = [+ASm2] ? +ASM2
The Oracle base has been set to /u01/app/oracle
[oracle@hostnode2 ~]$
[oracle@hostnode2 ~]$ asmcmd -p
ASMCMD [+] > cd +DATA/TEST/PARAMETERFILE/
ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE FEB 11 23:00:00 Y spfile.313.1160563609
PARAMETERFILE UNPROT COARSE FEB 11 23:00:00 N spfileTEST.ora => +DATA/TEST/PARAMETERFILE/spfile.313.1160563609
ASMCMD [+DATA/TEST/PARAMETERFILE] > rm spfileTEST.ora
ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l
ASMCMD-8002: entry 'PARAMETERFILE' does not exist in directory '+DATA/TEST/'
ASMCMD [+DATA/TEST/PARAMETERFILE] > cd +DATA/TEST/PARAMETERFILE/
ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE FEB 12 00:00:00 Y spfile.313.1160697793
PARAMETERFILE UNPROT COARSE FEB 12 00:00:00 N spfileTEST.ora => +DATA/TEST/PARAMETERFILE/spfile.313.1160697793
ASMCMD [+DATA/TEST/PARAMETERFILE] > rm spfileTEST.ora
ASMCMD [+DATA/TEST/PARAMETERFILE] >
ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l
ASMCMD-8002: entry 'PARAMETERFILE' does not exist in directory '+DATA/TEST/'
ASMCMD [+DATA/TEST/PARAMETERFILE] > cd +DATA/TEST
ASMCMD [+DATA/TEST] > ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PASSWORD/
Y TEMPFILE/
ASMCMD [+DATA/TEST] > mkdir PARAMETERFILE
ASMCMD [+DATA/TEST] > ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
N PARAMETERFILE/
Y PASSWORD/
Y TEMPFILE/
ASMCMD [+DATA/TEST] > cd PARAMETERFILE/
ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l
ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE FEB 12 00:00:00 N spfiletest.ora => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.313.1160697929
ASMCMD [+DATA/TEST/PARAMETERFILE] >
Regards,
Mallikarjun
Thanks for sharing this informative post! Understanding the differences between RAC database SPFILE and PFILE configurations is essential for database administrators. Speaking of technical support, businesses in Hertfordshire can rely on Total Group for reliable IT support services. With their team of skilled professionals, Total Group provides comprehensive IT solutions tailored to the specific needs of businesses. From managing databases to resolving technical issues, https://totalgroup.co.uk/ ensures that businesses have a robust IT infrastructure to support their operations effectively. Their expertise in IT support helps businesses in Hertfordshire stay competitive in today's dynamic business environment.
ReplyDelete