[LINUX] I tried to restore because the Oracle database initialization parameter setting failed.

Make a note so as not to forget it.

What happened

I didn't notice that I set the initialization parameter incorrectly from sqlplus, and when I did shutdown with momentum, it never started again. If you don't fix the initialization parameters, it won't start! It seemed to be said, so I managed to do it

environment

• Oracle Cloud
• Oracle Linux 7.8
• Hostname: odgb
• Oracle Database 19c (DBCS)
• Unique name: odgb_nrt1ws
• CDB：odgb

Term notes

• pfile ... Initialization parameter file. The database is started by reading the initialization parameters written in this file.
• spfile ... Server parameter file. A file that stores the initialization parameter settings. Can be created from pfile.

What i did

• Confirm unique name of database

$ sudo su - oracle
$ ls /u01/app/oracle/diag/rdbms/
odgb_nrt1ws

• Check spfile

$ sudo su - grid
$ asmcmd ls -l +DATA/odgb_nrt1ws/PARAMETERFILE/
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   OCT 03 16:00:00  Y    spfile.286.1048251613

• Copy spfile to hand

$ sudo su - grid
$ asmcmd cp +DATA/ODGB_NRT1WS/PARAMETERFILE/spfile.286.1048251613 /tmp
copying +DATA/ODGB_NRT1WS/PARAMETERFILE/spfile.286.1048251613 -> /tmp/spfile.286.1048251613

• Create pfile

$ sudo su - oracle
$ sqlplus / as sydba
sql> create pfile='/tmp/tmp.pfile' from spfile='/tmp/spfile.286.1048251613';
File created.
　
sql> exit
$ ls -l /tmp
-rw-r--r-- 1 oracle asmadmin 3705 Oct  3 16:35 tmp.pfile

• Edit pfile Edit what was set by mistake when setting the initialization parameters with vim When editing, pay attention to the contents of "Priority of description contents" on the following site Those without "*." Were deleted once. https://techlab.sixsquare.co.jp/archives/158

Corrected the value of "sga_max_size" that failed this time "Sga_target" has also changed, so fix it for the time being

$ vim /tmp/tmp.pfile
~
*.sga_max_size=20401094656
*.sga_target=14495514624
~
⬇︎
~
*.sga_max_size=6G
*.sga_target=3G
~

• Create a database with the created "pfile" I made a mistake in the correction value. Correct the same as the error content

$ sudo su - oracle
$ sqlplus / as sysdba
sql> startup mount pfile='/tmp/tmp.pfile';
ORA-00821: Specified value of sga_target 3072M is too small, needs to be at least 4656M
ORA-01078:Failed to process system parameters

After the correction, it starts properly this time. ..

SQL> startup mount pfile='/tmp/tmp.pfile';
The ORACLE instance has started.

Total System Global Area 6442449872 bytes
Fixed Size                  9149392 bytes
Variable Size            4848615424 bytes
Database Buffers         1560281088 bytes
Redo Buffers               24403968 bytes
The database has been mounted.
SQL> 

• Create "spfile" from the recreated "pfile" Once created, stop the database once

sql> create spfile='+DATA' from pfile='/tmp/tmp.pfile';

The file has been created.
sql> shutdown  

The database has been dismounted.
The ORACLE instance has been shut down.

• Check the new "spfile" You can see that there is one more

$ sudo su - grid
$ asmcmd ls -l +DATA/ODGB_NRT1WS/PARAMETERFILE/
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   OCT 03 17:00:00  Y    spfile.285.1052845549
PARAMETERFILE  UNPROT  COARSE   OCT 03 16:00:00  Y    spfile.286.1048251613

• Start the database with the new "spfile"

$ sudo su - oracle
$ srvctl modify database -db odgb_nrt1ws -spfile +DATA/odgb_nrt1ws/PARAMETERFILE/spfile.285.1052845549
$ srvctl start database -db odgb_nrt1ws
$ srvctl status database -db odgb_nrt1ws
 Instance odgb is running on node odgb
$ sqlplus / as sysdba

racle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
Connected to.

Somehow recovered. .. ..

Now it works normally. I realized that it would be dangerous if I made a mistake when playing with the initialization parameters, so be careful. .. ..