Pfile & SPfile Recovery Without rman or any Backup

S-1: login as sysdba user and start the database

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.

S-2: Once start the database verify it name and mode

SQL> Select name,open_mode from V$database;

 

NAME             OPEN_MODE

-------------     ------------

ORA12DB               READ WRITE

S-3: 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

S-4: shut down the database

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

S-5: 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]$ ls

0cvamqlf_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]$ ls

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

 

S-7: now we are going to login as sysdba from dbs location

[oracle@trichy ~]$ sqlplus / as sysdba

 

SQL*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.

 

.S-8: 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>

s-9: 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/test/test/trace

[oracle@trichydoyen trace ]$ cat alert_test.log

(the cat command will show all actions of database)

S-10: 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"

S-11: 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]$ ls

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

S-12: 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 s-10)

S-13: once we done to create a pfile login from dbs location

[oracle@trichy dbs]$ sqlplus / as sysdba

SQL*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>

 

S-14: check the name and mode of the database

SQL> Select name,open_mode from V$database;

NAME   OPEN_MODE

--------- --------------------

TEST   READ WRITE

 

S-15: create spfile into pfile

SQL> create spfile from pfile;

 

File created.

S-16: 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]$ ls

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

(Note : here spfile and pfile have been shown above then start our database and use s-17 )