1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Oracle 9i New Feature - Setting Persistent Parameters


Oracle has introduced 'Server Parameter File' (spfile) feature.
Oracle has introduced restricted dynamism in modifying some initialization parameters at session and system level. But these changes are not persistent over the startup-shutdown cycles of the database at the database level unless the initialization parameter file is modified.
Defining spfile before creating database in the initialization parameter file
This parameter set in the initialization parameter file shall create spfile at the given destination with the given name.
spfile=d:\ora901\database\spfiletest.ora
Creation of SPFile
The following command is to be issued if the spfile is not created at the time of creating the database.
create spfile='M:\oracle\ora91\database\SPFILEKNOW9I.ORA'
from pfile='H:\Oracle\admin\know9i\pfile\init.ora';
Operation 187 succeeded.
This output indicates that
create spfile='M:\oracle\ora91\database\SPFILEKNOW9I.ORA'
from pfile='h:\oracle\admin\know9i\pfile\init.ora';
File created.
This output indicates that
After creating the spfile shutdown the database and restart the instance using the spfile.
shutdown immediate
startup pfile= M:\oracle\ora91\database\SPFILEKNOW9I.ORA
Default Location of SPFile
The server parameter file is created by default under the Oracle_Home\database in case of Windows NT/2000/XP and Oracle_Home/dbs in case of UNIX operating system
Setting up of Persistent Parameters
A new clause to the 'alter system' called 'scope' has been introduced to enable the DBA to indicate how Oracle is to interpret the desired persistence of the change. The scope has been defined at three levels. Basically there are only two levels and the third level is a put together of both the 2 levels.
First Level spfile - This indicates that the spfile will also be modified. But changes will be
effective from the next startup or instance is recycled using the spfile.
This is the only way to modify STATIC parameters of the database.
Second Level memory - This indicates that the spfile will not be updated for the database and the change is for the instance for the current instance. This can not be used for the STATIC parameters. And the modification is not persistent.
Third Level both - This indicates that the change to the parameter is not only for the current instance but also for the spfile of the database and thus shall persist through the future cycles of the database.
Another new clause to the 'alter system' called 'comment' is also introduced to enable to record the
comments made at the time of modifying the parameter can be recorded in the spfile.
When the spfile is created after the database is created only those comments that are in the same line of the parameter survive and all others do not appear. Stand alone and header comments do not survive.
A sub-clause for the 'memory' is introduced to 'defer' the implementation of modifications/changes
for future sessions that connect to the database during the life of that instance.
ex : changing the parameters for the future sessions of the current instance
alter system set query_rewrite_enabled=true comment='change on 07/21/2002' scope=memory
deferred;
ex: changing parameters for both
alter system set query_rewrite_enabled=true comment='change on 07/21/2002' scope=both;
ex: changing more than one parameters in a single statement
alter system set shared_pool_size=100m, comment='change on 07/21/2002' scope=both,
aq_tm_processes=5, comment='change on 07/21/2002' scope=both;
Note:
Problems on NT and Windows 2000
On NT / W2Kyou have for every database a dedicated service. This services is created
with oradim.exe and this program doesn't support spfile with Version 9.0.1
There are Workarounds for this problem.
1. You can remove the pfile entry ORA_<SID>_PFILE from the registry.
2. In your init.ora you can add an entry spfile=<spfile_name>.

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy