Starting an Oracle database without a spfile & pfile?

Posted on

Question :

How can I start an Oracle database if both the spfile and pfile are not present, and a backup is also not available?

Answer :

You can try it with a basic pfile file like this:

SID.__db_cache_size=180355072
SID.__java_pool_size=71303168
SID.__large_pool_size=4194304
SID.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SID.__pga_aggregate_target=335544320
SID.__sga_target=1001088640
SID.__shared_io_pool_size=0
SID.__shared_pool_size=419430400
SID.__streams_pool_size=50331648
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0'
*.control_files='/u01/oradata/SID/control01.ctl','/u01/oradata/SID/control02.ctl','/u01/oradata/SID/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/SID/cdump'
*.db_domain='your.domain'
*.db_name='SID'
*.db_recovery_file_dest='/u01/oradata/SID/flash_recovery_area'
*.db_recovery_file_dest_size=1000G
*.db_unique_name='SID02'
*.pga_aggregate_target=335544320
*.sga_max_size=1005306368
*.sga_target=1001088640
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTS02'

Change the values so that they reflect the values (at least replace SID with your SID) that you need.

You can start your database in nomount mode without parameter file, control file and datafile.

Steps:

  1. rman target /
  2. startup nomount force;

Oracle will use current $ORACLE_SID variable as SID and default parameters for parameter file.

In addition to @Marco answer, after creating manual pfile, you should create spfile from it using: CREATE SPFILE FROM PFILE = '/location/to/your/pfile'

Some information about FPIEL and SPFILE

PFILE

A PFILE is a static, client side text file. This file normally resides
on the server. However in case you would like to start oracle from a
remote machine, you will need a local copy of the PFILE in that
machine.

This file can be edited with a standard text editor like notepad or vi
editor. This file will be commonly referred as INIT.ORA file.

SPFILE

SPFILE (Server parameter file) is a persistent server side binary
file. SPFILE should only be modified through “ALTER SYSTEM SET”
command . Editing an SPFILE directly will corrupt the file and the
start up of the database or instance will not be possible.

As SPFILe is a server side binary file, local copy of the PFILE is
not required to start oracle from a remote machine.

Advantages of SPFILE compared to PFILE

  • A SPFILE doesn’t need a local copy of the pfile to start oracle
    from a remote machine. Thus eliminates configuration problems.
  • SPFILE is a binary file and modification to that can only be done
    through ALTER SYSTEM SET command.
  • As SPFILE is maintained by the server, human errors can be eliminated
    as the parameters are checked before modification in SPFILE
  • It is easy to locate SPFILE as it is stored in a central location
  • Changes to the parameters in SPFILE will take immediate effect
    without restart of the instance i.e. Dynamic change of parameters is
    possible
  • SPFILE can be backed up by RMAN

Reference:
https://sapbasisdurgaprasad.blogspot.com/2011/07/difference-between-pfile-and-spfile-in.html

Leave a Reply

Your email address will not be published. Required fields are marked *