Archive for June, 2008

How to make changes to spfile / pfile or generate spfile / pfile on a RAC database

Posted on June 30, 2008. Filed under: Configuration, Database, DBA/Admin Tips, RAC | Tags: , , , |

Not all init.ora parameters can be changed using ‘alter system set’ commands. For example audit_syslog_level parameter

SQL> alter system set audit_syslog_level=local0.warning scope=both;
alter system set audit_syslog_level=local0.warning scope=both
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

In this scenario you need to bounce the database after making the changes in init.ora file.

Here are the detailed steps to generate pfile from spfile and vice-versa.

1. Connect to a database instance (any instance of the rac database) as sysdba user
2. Generate pfile from spfile

sql> create pfile=’/tmp/initSID1.ora’ from spfile;

—Make sure you mention a location for the pfile else it will overwrite the initSID1.ora file under $ORACLE_HOME/dbs

3. Make necessary changes eg.


4. Shutdown instance 1
sql> shutdown immediate

5. Shutdown instance 2
sql> shutdown immediate

You can also use srvctl command to stop the database by issuing

srvctl stop database -d SID

6. Backup existing spfile.

In RAC architecture the spfile which is common to both the instances will be usually in a location (viz., nfs or nas filesystem ) which is accessible to both the instances. Its a good practice to maintain single copy of spfile for instances that are part of RAC database, instead of having individual copies of spfile.ora under $ORACLE_HOME/dbs.

When the spfile is in a common location for the instances, the spfileSID[instance_num].ora eg: spfileSID1.ora under $ORACLE_HOME/dbs will be a soft link to the common location where the file name would be spfileSID.ora

cd /common_location
mv spfileSID.ora spfileSID.ora.lastbutone

7. Startup the database on instance 1 with the new pfile
8. sqlplus ‘/ as sysdba’
sql> startup pfile=’/tmp/initSID1.ora’

9. After the instance comes up, generate spfile from this pfile.
sql> create spfile=’location of existing spfile.ora with filename’ from pfile=’/tmp/initSID1.ora’;

eg: create spfile=’/common_location/spfileSID.ora’ from pfile=’/tmp/initSID1.ora’;

10. Verify that spfile is created on the mentioned location.
11. After confirmation, shutdown the instance
sql> shutdown immediate

12. sqlplus ‘/ as sysdba’ on instance 1
sql> startup [ this startup will start the instance using the spfile]

13. You can also issue show parameter spfile to verify that the instance had been started with the spfile.
14. After instance 1 comes up, start instance 2.


Read Full Post | Make a Comment ( None so far )


Posted on June 26, 2008. Filed under: DBA/Admin Tips, R12, TroubleShooting | Tags: |

Never ever run this script when the instance is up and running. Wondering what will happen if you run … pretty simple … nobody can login to the instance including SYSADMIN user even with the correct password.

Oracle Ebusiness Suite security best practices document suggests to run this script to grant and revoke permissions to/from applsys user.

The best way to do this is to run Recreate Grants and Synonyms using adadmin.


Read Full Post | Make a Comment ( None so far )

Script to list profile option values for a specific user

Posted on June 26, 2008. Filed under: DBA/Admin Tips, R12, Scripts | Tags: , |

col profile_option_name for a35
col profile_option_value for a30
col profile_option_id for 9999

set lines 500

 10003,’Repsonsibiltiy’,10004,’User’,10005,’Server’,10006,’Organisation’),1,15) profile_level,
 substr(fu.user_name,1,12) user_name,
 fnd_profile_options p,
 fnd_profile_option_values v,
 fnd_user fu
 p.profile_option_id = v.profile_option_id
and  v.level_value = fu.user_id
and fu.user_name like upper(‘&USER_NAME’)

Read Full Post | Make a Comment ( None so far )

Liked it here?
Why not try sites on the blogroll...