Oracle RAC modifies the parameter file location

Purpose:

Change the parameter file location from +DATA/cjcdb/spfilecjcdb.ora to +DATA/cjcdb/spfile/spfilecjcdb.ora

The procedure is as follows:

One: temporary location to generate pfile Note: RAC environment is strictly prohibited to use create pfile from spfile; will overwrite the default location pfile content. SQL> create pfile ='/tmp/pfilexxx.ora' from spfile; 2: regenerate spfile from new location Note: both pfile and spfile need to specify directory, SQL> create spfile ='+ DATA/cjcdb/spfile/spfilecjcdb.ora' from pfile ='/tmp/pfilexxx.ora'; 3: modify spfile location in pfile [oracle@rac01dbs]$ vim initcjcdb1.ora ##SPFILE ='+ DATA/cjcdb/spfilecjcdb.ora'SPFILE ='+ DATA/cjcdb/spfile/spfilecjcdb.ora'Modify the spfile location in OCR [oracle@rac01 dbs]$ srvctl modify database -d cjcdb -p '+DATA/cjcdb/spfile/spfilecjcdb.ora' V: Restart database takes effect. Restart node by node or restart at the same time. Determine [oracle@rac01 dbs]$ srvctl stop database -d cjcdb[oracle@rac01 dbs]$ srvctl start database -d cjcdb

The procedure is as follows:

Node 1, Node 2

View current spfile location

SQL> show parameter spfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DATA/cjcdb/spfilecjcdb.ora

View current spfile location

[oracle@rac01 ~]$ srvctl config database -d cjcdb -aDatabase unique name: cjcdbDatabase name: cjcdbOracle home: /u01/app/oracle/product/11.2.0/db_1Oracle user: oracleSpfile: +DATA/cjcdb/spfilecjcdb.oraDomain: Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: cjcdbDatabase instances: cjcdb1,cjcdb2Disk Groups: DATAMount point paths: Services: Type: RACDatabase is enabledDatabase is administrator managed

create a new location

[root@rac01 ~]# su - grid[grid@rac01 ~]$ asmcmdASMCMD> lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files NameMOUNTED EXTERN N 512 4096 1048576 10240 9974 0 9974 0 N ARCH/MOUNTED EXTERN N 512 4096 1048576 20480 18689 0 18689 0 N DATA/MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCR/ASMCMD> cd data/cjcdbASMCMD> mkdir spfileASMCMD> cd spfileASMCMD> pwd+data/cjcdb/spfile

New location regenerate spfile

SQL> create pfile='/tmp/pfilexxx.ora' from spfile;SQL> create spfile='+DATA/cjcdb/spfile/spfilecjcdb.ora' from pfile='/tmp/pfilexxx.ora';

Change spfile in pfile to new location (node 1 and node 2)

[oracle@rac01 ~]$ cd $ORACLE_HOME/dbs[oracle@rac01 dbs]$ cp initcjcdb1.ora initcjcdb1.ora.bak[oracle@rac01 dbs]$ vim initcjcdb1.ora###SPFILE='+DATA/cjcdb/spfilecjcdb.ora'SPFILE='+DATA/cjcdb/spfile/spfilecjcdb.ora'[oracle@rac02 ~]$ cd $ORACLE_HOME/dbs[oracle@rac02 dbs]$ cp initcjcdb2.ora initcjcdb2.ora.bak[oracle@rac02 dbs]$ vim initcjcdb2.ora###SPFILE='+DATA/cjcdb/spfilecjcdb.ora'SPFILE='+DATA/cjcdb/spfile/spfilecjcdb.ora'

Modify spfile location recorded in OCR

[oracle@rac01 dbs]$ srvctl config database -d cjcdb -a|grep spfileSpfile: +DATA/cjcdb/spfilecjcdb.ora[oracle@rac01 dbs]$ srvctl modify database -d cjcdb -p '+DATA/cjcdb/spfile/spfilecjcdb.ora'[oracle@rac01 dbs]$ srvctl config database -d cjcdb -a|grep spfileSpfile: +DATA/cjcdb/spfile/spfilecjcdb.ora

restart the database

[oracle@rac01 dbs]$ srvctl stop database -d cjcdb[oracle@rac01 dbs]$ srvctl start database -d cjcdb

View spfile parameter values

[oracle@rac01 dbs]$ srvctl config database -d cjcdb -a|grep spfileSpfile: +DATA/cjcdb/spfile/spfilecjcdb.oraSQL> show parameter spfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DATA/cjcdb/spfile/spfilecjcdb.ora

See question:

Problem 1: srvctl modify needs to be executed to modify the parameter file location in ocr, otherwise the parameter file location remains unchanged after restarting the database through srvctl. For example: Restart database node 1 and node 2 SQL> Shutdown immeidateSQL> startup

View spfile location successfully by show parameter

SQL> show parameter spfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DATA/cjcdb/spfile/spfilecjcdb.ora

Failed to view parameter file location modification via srvctl config command

[oracle@rac01 dbs]$ srvctl config database -d cjcdb -aDatabase unique name: cjcdbDatabase name: cjcdbOracle home: /u01/app/oracle/product/11.2.0/db_1Oracle user: oracleSpfile: +DATA/cjcdb/spfilecjcdb.oraDomain: Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: cjcdbDatabase instances: cjcdb1,cjcdb2Disk Groups: DATAMount point paths: Services: Type: RACDatabase is enabledDatabase is administrator managed

Restart database via srvctl

[root@rac01 bin]# ./ srvctl stop database -d cjcdb[root@rac01 bin]# ./ srvctl start database -d cjcdb

Parameter file location completely changed to original value

SQL> show parameter spfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DATA/cjcdb/spfilecjcdb.ora

Check that the modified value in pfile also changes back to the original value

[oracle@rac01 dbs]$ cat initcjcdb1.ora###SPFILE='+DATA/cjcdb/spfilecjcdb.ora'SPFILE='+DATA/cjcdb/spfilecjcdb.ora'# line added by Agent

Problem 2: The two node spfile positions are inconsistent

For example, instance 1 spfile is in ASM disk group, instance 2 spfile is on local file system ($ORACLE_HOME/dbs), you can create a new pfile on instance 2, write spfile location on ASM to new pfile, restart instance. [oracle@rac02 dbs]$ vim initcjcdb2.ora###SPFILE='+DATA/cjcdb/spfilecjcdb.ora'SPFILE='+DATA/cjcdb/spfile/spfilecjcdb.ora'

Welcome to pay attention to my Weixin Official Accounts "IT Little Chen," learn together and grow together!!