Monday, February 12, 2024

RAC Database Spfile Or Pfile

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

1 comment:

  1. 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

Running SQL and O/S Commands Within RMAN

Running SQL and O/S Commands Within RMAN Sometimes you may want to run an SQL statement from within RMAN. Use RMAN’s sql command to do this....