Mallik034: 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 ~]$ 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 ~]$ 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 ~]$ 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

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> 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 ~]$ 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]$ 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

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]$ 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

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

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

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

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:

Ellie BrownFebruary 14, 2024 at 7:50 PM
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.

Mallik034

Monday, February 12, 2024

RAC Database Spfile Or Pfile

1 comment:

Automation Script | Archivelog Generation Hourly Monitoring

Social Plugin

Blog Archive