Oracle Question II
20) What is the location of init<SID>.ora file for windows system and for unix system?
The default location of init<SID>.ora file is %ORACLE_HOME%/database folder for Windows while it is $ORACLE_HOME/dbs for UNIX System.
21) What is the use of init<SID>.ora file in Oracle Database?
init<SID>.ora Files contains all the parameters which is used to configure the Oracle Instance.
For e.g
shared_pool_size parameter in init<SID>.ora decides the size of shared pool
large_pool parameter decides the size of large pool.
22) Can the parameter of init file be changed dynamically?
No, the parameters of the init<SID>.ora does not take effect on the fly when the database
is running. To change the parameter we need to modify the parameter in init<SID>.ora
and then stop the database and start the database with the modified pfile.
23) How can we start the database with pfile which is
located in the non default location?
To start the database with pfile located in the non default location
i.e (Not in %oracle_home%/database OR $ORACLE_HOME/ dbs) we can use the following command at SQL prompt
SQL> startup pfile=<location of pfile>
24) Which parameter determines the location control files in oracle database?
CONTROL_FILES parameter decides the location of control files in oracle database.
25) What are Instance specific parameters and how are they defined in init<SID>.ora?
In RAC (Real Application Cluster) which is a Oracle HA(High Availability) Feature we can have multiple oracle instance configured on different nodes which can access the same database located in shared storage area. These instances configured on different nodes can be configured differently by using these instance specific parameters. These instance specific parameters are defined in init<SID>.ora as
Letz say the database name is ORCL
Instance1 SID: ORCL1
Instance 2 SID:ORCL2
initORCL1.ora
ORCL1.undo_tablespace=UNDOTBS1
ORCL2.undo_tablespace=UNDOTBS2
26) What is spfile<SID>.ora?
In Oracle 9i, spfile<SID>.ora was introduced as a replacement for init<SID>.ora in which
some of databaseparameters can be changed dynamically
without shutting down the database.
Spfile<SID>.ora is a binary file and cannot be modified manually
27) How can we create spfile from pfile and vice versa?
In the server set oracle_sid environment variable to the SID name
Log in to the database with sys as sysdba user
and fire the following query at the SQL prompt
SQL> create spfile from pfile;
File created
SQL> create pfile from spfile;
File created
By firing the above command the spfile/pfile is created in the default location i.e %oracle_home%/database in Windows and
$ORACLE_HOME/dbs
28) How can we dynamically change the parameter with spfile<SID>.ora?
To dynamically change the database parameter your database must be started with spfile<SID>.ora
log into the datbase with sys as sysdba
and fire the following query
SQL> alter system set <parameter name>=<value> scope=both;
If the parameter cannot be modified on the fly use the following command
SQL> alter system set <parameter-name>=<value> scope=spfile;
29) When you give startup to start the database and if both spfile and pfile are present in
default location which file is used to start the database?
When the oracle starts the instance it looks the following files in the specified order
1)spfile<SID>.ora
2) spfile.ora
3)init<SID>.ora
4) init.ora
If it finds spfile<SID>.ora it will start the database using that but if it is not
present it will look for spfile.ora file. If that file is also not present it looks for init<SID>.ora
and so on.
30) how can we create pointer to other init/spfile?
We can use
IFILE= ’<location of the pfile>’
SPFILE=’<location of spfile>’
parameters to create pointer to init/spfile which are located at some other location.