03_manager_oracle_instance

chapter 3

03_manager_oracle_instance

Initialization parameter files

spfiledb01.ora

pfile

spfile

v$parameter

pfile: initSID.ora

changes take effect on the next start up;

only opened during instance start up

$ORACLE_HOME/dbs

ORACLE_SID   is a site identifier

[oracle@wangjia ~]$ env |grep oracle

USER=oracle

LD_LIBRARY_PATH=:/opt/oracle/product/10.2.0.1/lib:/usr/lib:/usr/local/lib

ORACLE_BASE=/opt/oracle

MAIL=/var/spool/mail/oracle

PATH=.:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin:/opt/oracle/product/10.2.0.1/bin:/home/oracle/bin

PWD=/home/oracle

HOME=/home/oracle

LOGNAME=oracle

ORACLE_HOME=/opt/oracle/product/10.2.0.1

cp init.ora $ORACLE_HOME/dbs/initdb01.ora

spfile:

binary file

maintained by the oracle server

always resides on the server side

ability to make changes persisitent across shutdown and start up

can self-tune parameter values

can have RMAN support backing up to the initialization parameter file

根据spfile 查看pfile

strings spfileora10g.ora |more

modify parameters in spfile

alter system set parameter=value

comment 注释，可以增加一些注释在里面

[oracle@wangjia dbs]$ env|grep ORACLE

ORACLE_SID=ora10g

ORACLE_BASE=/opt/oracle

ORACLE_HOME=/opt/oracle/product/10.2.0.1

alter system reset undo_suppress_errors scope=both sid='*';

spfile is better than pfile!

create spfile from pfile;

startup :

spfileSID.ora

default spfile --> spfile.ora

initSID.ora

default pfile -->

startup pfile=$ORACLE_HOME/dbs/initDBA1.ora

who can startup the database?

startup nomount;    instance started

alter database db01 mount;   control file opened for this instance;

open; all files opened as descripe by the control file for this instance;

alter database db01 open read only;

restrict mode:

alter system enable restricted session;

alter system kill session '15,6';  (SID,serial#,从v$session中查出来）

read only mode:

startup mount 

alter database open read only;

query

disk sorts using LMT

Take data file offline,online,but not tablespace

perform recovery of offline data files and tablespace

shutdown database:

close a database

close redo log files and datafiles

unmounted a database

close controlfiles

shutdown an instance

removed SGA,stop process

shutdown immediate,normal,transactional,abort;

Diagnostic files:

contain information about significant events encountered

used to resolve problems

used to better manager the database on a day-to-day basic

Background Trace Files : SID_processname_PID.trc

wilson_qmn0_3783.trc

/opt/oracle/admin/ora10g//bdump

User Trace Files:   SID_ora_PID.trc

wilson_ora_3673.trc

USER_DUMP_DEST

/opt/oracle/admin/ora10g/udump

MAX_DUMP_FILE_SIZE

SQL_TRACE = TRUE;

有两种：session  :

alter session set sql_trace= true;

dbms_system.set_sql_trace_in_session

实例级别的

alter system set sql_trace =true;

Alert Log File:

alertSID.log file

BACKGROUND_DUMP_DEST

/opt/oracle/admin/ora10g//bdump

Summary:

initialization parameter

startup and shutdown

monitor and use diagnostic file