Initialization Parameter Files:
简介:
Initialization Parameter Files(初始化参数文件)是Oracle数据库中用于配置和控制数据库实例行为的重要文件。它包含了一系列的参数设置,用于定义数据库实例的各种属性和行为,如内存分配、并发连接数、日志文件大小等。
Oracle数据库支持两种类型的初始化参数文件:
1. SPFILE(Server Parameter File):以二进制格式存储的参数文件,通常命名为"spfile<sid>.ora",其中"<sid>"是数据库实例的系统标识符。SPFILE是Oracle推荐使用的参数文件格式,它提供了动态修改参数的能力,无需重启数据库实例。
2. PFILE(Parameter File):以文本格式存储的参数文件,通常命名为"init<sid>.ora",其中"<sid>"是数据库实例的系统标识符。PFILE使用静态参数设置,必须在数据库启动之前手动修改,并要求重启数据库实例才能生效。
通过修改初始化参数文件中的参数设置,可以对数据库实例的各种行为进行调整和优化。对于SPFILE,可以使用ALTER SYSTEM语句动态修改参数值,并且修改将在数据库下次启动时保持。对于PFILE,必须手动编辑文件,并在数据库重新启动时生效。
通常,初始化参数文件位于数据库实例的ORACLE_HOME/dbs目录下。在启动数据库实例时,Oracle会优先寻找SPFILE文件,如果未找到,则尝试使用PFILE文件。如果同时存在SPFILE和PFILE文件,优先使用SPFILE文件。
总而言之,Initialization Parameter Files是用于配置和控制Oracle数据库实例行为的重要文件,它定义了数据库的各种属性和行为参数。使用合适的参数设置可以优化数据库性能和适应特定的应用需求。
desc v$parameter
SQL> desc v$parameter Name Null? Type ----------------------------------------- -------- ---------------------------- NUM NUMBER NAME VARCHAR2(80) TYPE NUMBER VALUE VARCHAR2(4000) DISPLAY_VALUE VARCHAR2(4000) ISDEFAULT VARCHAR2(9) ISSES_MODIFIABLE VARCHAR2(5) ISSYS_MODIFIABLE VARCHAR2(9) ISINSTANCE_MODIFIABLE VARCHAR2(5) ISMODIFIED VARCHAR2(10) ISADJUSTED VARCHAR2(5) ISDEPRECATED VARCHAR2(5) ISBASIC VARCHAR2(5) DESCRIPTION VARCHAR2(255) UPDATE_COMMENT VARCHAR2(255) HASH NUMBER
select name,value from v$parameter;
归档一下:
col name format a20
col value format a30
asm_diskgroups asm_power_limit 1 control_management_p DIAGNOSTIC+TUNING ack_access awr_snapshot_time_of 0 fset sqltune_category DEFAULT NAME VALUE -------------------- ------------------------------ diagnostic_dest /u01/app/oracle tracefile_identifier max_dump_file_size unlimited trace_enabled TRUE cell_offloadgroup_na me 352 rows selected.
select name,value from v$parameter where name='pga_aggregate_target' ;
SQL> select name,value from v$parameter where name='pga_aggregate_target' ; NAME VALUE -------------------- ------------------------------ pga_aggregate_target 0
一般常用;
show
for example:
show parameter sga;
SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 728M sga_target big integer 0
PFILE(Parameter File):
以文本格式存储的参数文件,通常命名为"init<sid>.ora",其中"<sid>"是数据库实例的系统标识符。PFILE使用静态参数设置,必须在数据库启动之前手动修改,并要求重启数据库实例才能生效。
SID:Site ldentifier:
注意:如果您不熟悉SID或ORACLE_SID这个术语,则需要一个完整的定义。SID是一个站点标识符。在UNIX中,它和ORACLE_HOME(安装Oracle软件的地方)被散列在一起,以创建一个唯一的键名来附加SGA。如果您的ORACLE_SID或ORACLE_HOME设置不正确,您将得到ORACLE not AVAILABLE错误,因为您不能附加到由此唯一键标识的共享内存段。在Windows上,共享内存的使用方式与UNIX不同,但是SID仍然很重要。在同一个ORACLE_HOME上可以有多个数据库,因此需要一种方法来唯一地标识每个数据库及其配置文件。
SPFILE(Server Parameter File):
以二进制格式存储的参数文件,通常命名为"spfile<sid>.ora",其中"<sid>"是数据库实例的系统标识符。SPFILE是Oracle推荐使用的参数文件格式,它提供了动态修改参数的能力,无需重启数据库实例。
总结:
用SPFILE比PFILE好用
STARTUP Command Behavior
Order of precedence(启动顺序)
优先:
—spfileSID.ora
—Default SPFILE ->spfile.ora
—initSID.ora
—Default PFILE -> ???
Specified PFILE can override precedence.
STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora
PFILE can indicate to use SPFILE.
SPFILE = /database/startup/spfileDBA1.ora(通过pfile找到spfile)
Starting Up a Database
MOUNT(limit )
NOMOUNT
SHUTDOWN
Instance started Control file
opened for the instanc
All files opened as described
by the control file for this instance
OPEN |
STARTUP FORECE STARTUP RESTRICT
ALTER SYSTEM ENABLE RESTRICTED SESSION;
[oracle@linux dbs]$ lsnrctl start
create user boobooke identified by bbk;
grant connect, resource to boobooke;
远端连接:
sqlplus boobooke/bbk@oracle9ivm(this is a describor)
select * from dual;
sqlplus system/oracle@oracle9ivm
查看所有连接数据库的用户:
select sid, serial#, username from v$session;
管理员杀掉用户:
alter system kill session ’15,6’;
Read-Only Mode
Opening a database in read-only mode;
startup mount;
alter database open read only;
—can be used to:
—Execute queries
—Execute disk sorts using locally managed tablespaces
—Take data files offline and online, but not tablespaces
—Perform recovery of offline data files and tablespaces
Shutting Down the Databases;
Close a database
When you close a database, Oracle Database writes all database data and recovery data in the SGA to the datafiles and redo log files, respectively; When you close a database, Oracle writes all database data and recovery data in the SGA to the datafiles and redo log files, respectively. Next, Oracle closes all online datafiles and redo log files. (Any offline datafiles of any offline tablespaces have been closed already. If you subsequently reopen the database, any tablespace that was offline and its datafiles remain offline and closed, respectively.) At this point, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed but still mounted.
Unmount a Database
After the database is closed, Oracle unmounts the database to disassociate it from the instance. At this point, the instance remains in the memory of your computer.
After a database is unmounted, Oracle closes the control files of the database
Shut Down an Instance
The final step in database shutdown is shutting down the instance. When you shut down an instance, the SGA is removed from memory and the background processes are terminated.
Shutting Down the Database
Shutdown Mode | A | I | T | N |
Allow new connection | NO | No | No | No
|
Wait until current session end | No | NO | NO | Yes
|
Wait until current tansaction end | No | NO | Yes | Yes |
Force a checkpoint and close files | No | Yes | Yes | Yes |
Shutdown mode:
A ABORT I = IMMEDIATE T TRANSACTIONAL N = NORMAL
startup:
Online redo log files used to reapply changes
Undo segments used to roll back uncommitted changes
Resources released
Diagnostic Files
—Contain information about significant events encountered
—Used to resolve problems
-Used to better manage the database on a day-to-day basis
Several types exist:
—altertSID.log file
—Background trace files
—User trace files
Enable/Disable User Tracing
Session level:
—Using the alter session command:
—alter session set sql_trace = true
—Executing DBMS procedure:
—dbms_system.SET_SQL_TEACE_IN_SESSION
—Instance level
—Setting the initialization parameter:
—SQL_TRACE = TRUE
(SHUTDOWN NORMAL OR SHUTDOWN TRANSACTIONAL OR SHUTDOWN IMMEDIATE), on these way down:
★ Database buffer cache written to the data files
★ Uncommitted changes rolled back
★ Resources released
on the way up No instance recovery
(shutdown abort or instance failure or startup force),on the vay Modified buffers are not written to the data files Uncommitted changes are not rollback.
on the way up:
Online redo log files used to reapply changes
Undo segments used to rollback uncommitted changes Resources released
日志文件存放地点:
background_dump_dest string /u01/admin/denver/bdump
注意;可以拷贝出里面的文件作为PFILE在通过PFILE构造SPFILE
show parameter dump
User Trace Files:
记录的是单个用户访问数据库时候遇到的错误
select username, account_status from dba_users;
alter user hr account unlock;
select username, account_status from dba_users;
alter user hr identified by hr;
select username, account_status from dba_users;
exit