Description:-
If you lost both spfile and pfile of the instance when it is up and running. Suppose you don’t have any type of rman or autobackup also. How can you restore that. Let’s see the below example.
Step 1: Once start the database verify it name and mode
SQL> startup
ORACLE instance started.
Total System Global Area 347340800 bytes
Fixed Size 1336456 bytes
Variable Size 285215608 bytes
Database Buffers 54525952 bytes
Redo Buffers 6262784 bytes
Database mounted.
Database opened.
SQL> Select name,open_mode from V$database;
NAME OPEN_MODE
————- ————
ORA12DB READ WRITE
Step 2: check the background dump test location
SQL>show parameter background_dump_dest;
NAME TYPE VALUE
——— ——– ———–
background_dump_dest string /u01/app/oracle/product/12.2.0.1/db_1/rdbms/log
Step 3: shut down the database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 4: Go to the spfile location
SQL> !
[oracle@trichydoyen dbs]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/ [oracle@trichydoyen dbs]$ ls [oracle@trichy dbs]$ ls0cvamqlf_1_1 c-2343286063-20200918-00 hc_jgso.dat lkJGSO_PITR_TEST orapwtest spfiletest.ora
0dvamqou_1_1 c-2343286063-20200918-01 hc_test.dat lkTEST snapcf_test.f
(Note: Here we have to remove spfile , if suppose we had a pfile for that database remove that file also )
[oracle@trichydoyen dbs]$ rm spfiletest.ora [oracle@trichy dbs]$ ls0cvamqlf_1_1 c-2343286063-20200918-00 hc_jgso.dat lkJGSO_PITR_TEST orapwtest
0dvamqou_1_1 c-2343286063-20200918-01 hc_test.dat lkTEST snapcf_test.f
[oracle@trichy dbs]$Step 5: now we are going to login as sysdba from dbs location
[oracle@trichy ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 9 02:02:11 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
Step 6: Now trying to start the database it through an error like below
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/12.2.0.1/db_1/dbs/inittest.ora’
SQL>
Step 7 : now we are going to background dump test location which we found it before background_dump_test parameter location
SQL> !
[oracle@trichydoyen dbs]$ cd /u01/app/oracle/diag/rdbms/ora12db/ora12db/trace [oracle@trichydoyen trace ]$ cat alert_test.log(the cat command will show all actions of database)
Step 8: since we don’t have pfile and spfile so we ‘ll get back into the alert log file select system parameter with non-default values
Using parameter settings in server-side spfile /u01/app/oracle/product/12.2.0.1/db_1/dbs/spfiletest.ora
System parameters with non-default values:
processes = 300
resource_limit = TRUE
nls_language = “AMERICAN”
nls_territory = “AMERICA”
sga_target = 1952M
control_files = “/u01/app/oracle/oradata/test/control01.ctl”
control_files = “/u01/app/oracle/oradata/test/control02.ctl”
db_block_size = 8192
compatible = “12.2.0”
undo_tablespace = “UNDOTBS1”
remote_login_passwordfile= “EXCLUSIVE”
dispatchers = “(PROTOCOL=TCP) (SERVICE=testXDB)”
audit_file_dest = “/u01/app/oracle/admin/test/adump”
audit_trail = “DB”
db_name = “test”
open_cursors = 300
pga_aggregate_target = 1941M
diagnostic_dest = “/u01/app/oracle”
Step 9: From trace location into go dbs location
[oracle@trichy trace]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/ [oracle@trichy dbs]$ ls0cvamqlf_1_1 c-2343286063-20200918-00 hc_jgso.dat lkJGSO_PITR_TEST orapwtest
0dvamqou_1_1 c-2343286063-20200918-01 hc_test.dat lkTEST snapcf_test.f
[oracle@trichy dbs]$Step 10: after went dbs location create a new file in the name of ‘initCA.ora’
[oracle@trichy dbs]$ vi inittest.ora(Note copy and paste line from alert log i.e step-8)
Step 11: once we done to create a pfile login from dbs location
[oracle@trichy dbs]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 9 02:12:13 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2046820352 bytes
Fixed Size 8622480 bytes
Variable Size 587206256 bytes
Database Buffers 1442840576 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL>
Step 12: check the name and mode of the database
SQL> Select name,open_mode from V$database;
NAME OPEN_MODE
——— ——————–
TEST READ WRITE
Step 13: create spfile into pfile
SQL> create spfile from pfile;
File created.
Step 14: check the parameter by using which file database parameter instance is working
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string
we found our database instance is working pfile s-18: shut down the database because we have spfile so start our database using spfile
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>!
[oracle@trichydoyen dbs]$ls [oracle@trichy dbs]$ ls0cvamqlf_1_1 c-2343286063-20200918-00 hc_jgso.dat inittest.ora lkTEST snapcf_test.f
0dvamqou_1_1 c-2343286063-20200918-01 hc_test.dat lkJGSO_PITR_TEST orapwtest spfiletest.ora
[oracle@trichy dbs]$
