1
1)system parameter file -- spfile<sid>,spfile (oracle 9i or later) is a binary file ,it cann't been edited by the text editor. you can change it through "alter system XXX";
in oracle 9.2.0,spfile is stored in the directory of ora92\datatase\spfile<sid>.ora.
2)init parameter file(pfile)--init<sid>.ora (early version) is a text file.it is stored in admin\<sid>\pfile.
2
Entries are specific to the instance being accessed;
there are two kinds of parameters:
explicit: having an entry in the file
implicit: no entry within the file,but assuming the oracle default values;
Multiple files can be used for a single datasbase to optimize performance in different situation.
static parameter file,pfile (init.ora)
persistent parameter file,spfile(spfile<sid>.ora);
3 order being used
first spfile<sid> then int<sid>.ora
select name,value from v$system_parameter
4 spfile spfilesid.ora
1)Binary file with the abiility to make changes persistent across shutdown and startup
2)Maintained by oracle server
3)Record parameter value change made with the alter system command
4)Can specify wether the change being made is tempory or persistent
5)value can be delete or reset to allow an instance to revert to the default value;
e.g
alter system set timed_statistics=false scope=memory;
alter system set timed_statistics=false scope=spfile;
startup force-- restart oracle
select name,value from v$system_parameter where name like 'time%';
alter system set timed_statistics=false scope=both;
6) not all entries can be modified.to see more ,you can lool up v$system_parameter.in this table the isses_modifiable and issys_modifiable will tell which one can be modified in session,and which one can be modified in sys, and which one cann't be modified.
e.g
select name,isses_modifiable from v$system_parameter where isses_modifiable='true';
alter session set timed_statistics=false;
e.g
select name,issys_modifiable from v$system_parameter where issys_modifiable<>'false';
.issys_modifiable may have two values immediate or deffered.
immediate means alter this entry will take effect on the current session ,while derrered will take effect on a new session.
5 pfile initsid.ora
.the pfile is a text file than can be modigied with an operation system editor.
.modifications to the file are made manually.
.changes to the file take effect on the next startup
6 Creating an spfile
spfile can be created from initsid.ora file using thee create spfile command,whick can be executed before or after instance startup.
create spfile from pfile. -- you must shutdown the instance befofe or you can use the fellow statement:
create spfile='d:\oracle\oracle\ora92\database\spfile.ora' from pfile;
yo caan backup the
7 Oracle Managed files(OMF)
1) OMF are created and deleted by the oracle server as directed by sql commands
2) OMF are established by parameters:
DB_CREATE_DEST: set to give the default location for data files
DB__CREATE_OMLINE_LOG_DEST_N: set to gieve the default locations for online redo logs and control files,up to a mazimum of 5 locations.
e.g 1
show parameter db_create
e.g 2
create tablespace test1;--report error,Do not konw where create the data file
alter system set db_create_file_dest='d:\oracle\oradb';
create tablespace test1; -- is ok;
e.g 3
alter database add logilfe group 6;
-- if you do not specify the db_create_online_log_dest_N ,the log file will create at --the location which is specified by the db_create_file_dest.
e.g 4
drop table test1; -- oracle alse remove it's physicial file;
alter database drop logfile group 6-- also romove the physical file.
e.g 5
create tablespace test1
datafile 'd:\oracle\oradb\test1.dbf' size 2m;
drop tablespace test1 -- if you create tablespace or others object in this way ,you --will cann't remove the physical file.
drop table test1 including comtents and datafiles;
8 Starting up a database mount
startup nomomount :instance started for this instance;create db.create controlfule
1) create alert_sid.log startup log which is stored in <oracle_home>\admin\<sid>\bdump
2) start instance include allocating memory and start bpprocess
select * from v$instance;
select * from v$bgprocess;
select * from V$sga;
alter database mount;
open control files for the instance;
select* from v$database;
select* from v$tablespace;
select* from datafile;
select* from log;
alter database open;
all files opened as describled by the control file for this instance;
9 start up
start pfile=...\initsid.ora; (alter session set nls_language=american)
alter database open read only; you can not change database to read only after the database has been created.
startup database restrict;
alter system enable restricted session;-- only people with restricted privilege can --access the database;
alter restricted session to kkk;
alter disable restricted session
10 opening a database in read only mode
a databse can be opened as read only database alter database open read only;
a read-only database can be used to :
execute queries
execute disk sorts using locally managed
take data files offline and online ,not tableespaces
perform recovery of offline data files and tablespace;
11Shutting down teh database
normal: wait until current sessions end,wait until current trasactions end ,force a checkpoint and close files.
tansactional:wail until current transaction end.force a checkpoint and close files
immediate :force a checkpoint and close the files
abort : do nothing ,just close the files. when startup ,need recovery.
12 Managing an instance by monitoring Diagnostic Files
Diagnostic files contain information about significant events encounted while the instance is operational.
.user to resolve problem or to better manager the database.
.server types of dignostic files exist:
alertsid.log --which location is specified by the background_dump_dest --entry in the initsid.ora.tje default value is --<ora_home>\admin\sid\bdump
background trace files -- the same as above;
user_tace files
13 alert log file
the alertsid.log file recored the command and result of major event while the database is operational.
.it is userd for day-to-day operational information or dignosing database errors
.ench entry has a time stamp associated with it.
.the dba manager the alertsid.log file.
.its location is defined by background_dump_dest.
14 enabling or disabling user tracing
. sessin level using the alter session
alter session set sql_trace=true;
session level by execcuting dbms