Oracle参数及参数文件spfile/pfile深入解析

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:2441

Oracle参数及参数文件spfile/pfile深入解析

1、参数文件v$parameter
SQL> desc v$parameter
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(512)
DISPLAY_VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISINSTANCE_MODIFIABLE VARCHAR2(5)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
ISDEPRECATED VARCHAR2(5)
DESCRIPTION VARCHAR2(255)
UPDATE_COMMENT VARCHAR2(255)
HASH NUMBER

其结构为:
从以下结果中看到,v$parameter结构是由GV$PARAMETER创建,而GV$PARAMETER则由X$创建,从下面可以看出GV$parameter来源于x$ksppi、x$ksppcv
SQL> select view_definition from v$fixed_view_definition a where a.view_name='V$PARAMETER';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIAB
LE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH from GV$PARAMETER where inst_id = USERENV
('Instance')

Execution Plan
----------------------------------------------------------
Plan hash value: 1020564687
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2058 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 2058 | 0 (0)| 00:00:01 |
|* 2 | FIXED TABLE FULL | X$KQFVI | 1 | 43 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KQFVT (ind:2) | 1 | 2015 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select view_definition from v$fixed_view_definition a where a.view_name='GV$PARAMETER';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf, decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'
), decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decode(bitand(ksppiflg/65536,3), 0, 'FALSE',
'TRUE')), decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'), decode(bitand(ksppstvf,2),2,'TRUE','FALSE
'), decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash from x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and ((translate(ksppinm,'_','#') not like '##%') and ((translate(ksppinm,'_','#') not like
'#%') or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0)))

Execution Plan
----------------------------------------------------------
Plan hash value: 1020564687
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2058 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 2058 | 0 (0)| 00:00:01 |
|* 2 | FIXED TABLE FULL | X$KQFVI | 1 | 43 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KQFVT (ind:2) | 1 | 2015 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
processes参数在启动时,会先为processes分配内存地址空间,并向shared pool注册,默认的每个进程会在共享池中分配4 Bytes大小的注册空间;

如下:

SQL> select name,value from v$parameter where name in('processes','sessions');
NAME VALUE
-------------------- --------------------
processes 150
sessions 170

SQL> select * from v$sgastat where name='processes';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool processes 600
如果更改processes大小,如更改为:200,则在共享池中应该为800Bytes的注册空间;;
SQL> select * from v$sgastat where name='processes';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool processes 800
SQL> select name,value from v$parameter where name in('processes');
NAME VALUE
-------------------- --------------------
processes 200

2、初始化参数的跟踪
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/source/udump/source_ora_19471.trc
SQL> alter session set sql_trace=true;
Session altered.
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 260M
sga_target big integer 260M
SQL> alter session set sql_trace=false;
我们看看show parameter sga后台主要做了什么操作,在trace文件找到如下语句:
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,
DECODE(TYPE,
1,
'boolean',
2,
'string',
3,
'integer',
4,
'file',
5,
'number',
6,
'big integer',
'unknown') TYPE,
DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM V$PARAMETER
WHERE UPPER(NAME) LIKE UPPER('%sga%')
ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM

其中UPPER是指可以忽略大小写。

3、关于glogin.sql脚本的说明

在启动sqlplus 时,会自动调用$ORACLE_HOME/sqlplus/admin/glogin.sql文件执行一系列的参数设置,可以通过修改这个参数来变更一些sqlplus登录后的显示信息。
glogin.sql内容如下:
注意里面格式的内容:
[oracle@source admin]$ more glogin.sql
--
-- Copyright (c) 1988, 2004, Oracle Corporation. All Rights Reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command
--
-- USAGE
-- This script. is automatically run
--
-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15
-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR FORMAT A65 WORD_WRAPPED
-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24
COLUMN units_col_plus_show_sga FORMAT a15
-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE
-- Defaults for SHOW RECYCLEBIN
COLUMN origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME'
COLUMN objectname_plus_show_recyc FORMAT a30 HEADING 'RECYCLEBIN NAME'
COLUMN objtype_plus_show_recyc FORMAT a12 HEADING 'OBJECT TYPE'
COLUMN droptime_plus_show_recyc FORMAT a19 HEADING 'DROP TIME'
-- Defaults for SET AUTOTRACE EXPLAIN report
-- These column definitions are only used when SQL*Plus
-- is connected to Oracle 9.2 or earlier.
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44
-- Default for XQUERY
COLUMN result_plus_xquery HEADING 'Result Sequence'
最常见的是在启动时候增加用户名和数据库提示,可以在该文件中增加一行:
set sqlprompt "mailto:_user@_connect_identifier]_user@_connect_identifier>"
如我在glogin.sql中设置以上参数后,登录sqlplus "/as sysdba"后,
由默认的SQL> 更改为:mailto:SYS@source]SYS@source>

常用的设置还有:
set sqlprompt "&_user>"
set sqlprompt "_user_privilege>"
mailto:SYS@_connect_identified]define">SYS@_connect_identified>define
DEFINE _DATE = "2009-12-02 06:56:27" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "source" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)

4、v$spparameter参数
这个视图记录了spfile文件中设置的初始化参数:
select sid,name,value from v$spparameter where value is not null;
mailto:SYS@source]SYS@source> /
SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* processes 200
* sga_max_size 271859200
* nls_language SIMPLIFIED CHINESE
* nls_territory CHINA
* sga_target 271859200
* control_files /oracle/oradata/source/control01.ctl
* control_files /oracle/oradata/source/control02.ctl
* control_files /oracle/oradata/source/control03.ctl
* db_file_name_convert /oracle/oradata/csfmis
* db_file_name_convert /oracle/oradata/source
* log_file_name_convert /oracle/oradata/csfmis
SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* log_file_name_convert /oracle/oradata/source
* db_block_size 16384
* compatible 10.2.0.1.0
* db_file_multiblock_read_count 16
* db_recovery_file_dest /oracle/flash_recovery_area
* db_recovery_file_dest_size 2147483648
* undo_management AUTO
* undo_tablespace UNDOTBS1
* remote_login_passwordfile EXCLUSIVE
* job_queue_processes 10
* background_dump_dest /oracle/admin/source/bdump
SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* user_dump_dest /oracle/admin/source/udump
* core_dump_dest /oracle/product/10.2.0/admin/source/cdump
* audit_file_dest /oracle/admin/source/adump
* db_name source
* open_cursors 300
* pga_aggregate_target 50683392
28 rows selected.
*的参数则是指对RAC所有实例都生效,如果指定实例名,则只对单独的实例生效。
通过跟踪v$spparameter视图,则是建在X$KSPSPFILE数据字典之上。
除了v$parameter、v$spparameter、v$system_parameter外,还有一个v$spparameter2视图;
v$spparameter:用于记录spfile文件中设置的初始化参数;
v$system_parameter:用于显示当前实例级别生效的参数文件,可认为是系统system级别的参数设置,同时存在v$system_parameter2视图;
v$spparameter2:与v$parameter相比,对于存在多行的数据,v$spparameter2则以多行显示,如下:
mailto:SYS@source]SYS@source> select name,value from v$parameter where name='control_files';
NAME VALUE
------------------------------ --------------------------------------------------
control_files /oracle/oradata/source/control01.ctl, /oracle/orad
ata/source/control02.ctl, /oracle/oradata/source/c
ontrol03.ctl

mailto:SYS@source]SYS@source> select name,value from v$parameter2 where name='control_files';
NAME VALUE
------------------------------ --------------------------------------------------
control_files /oracle/oradata/source/control01.ctl
control_files /oracle/oradata/source/control02.ctl
control_files /oracle/oradata/source/control03.ctl
从11g开始,show spparameter用于显示v$spparameter视图中的参数设置,这是11g新特性的一个增强;
5、重置spfile中设置的参数
如,目前我的processes为200
SQL> select name,value from v$parameter where name in('processes');
NAME VALUE
-------------------- --------------------
processes 200
重置processes参数:
alter system reset processes scope=spfile sid='*';
再通过strings spfilesource.ora |grep process就可以看到参数已不存在了。
重启后实例后,processes值还原为默认的40。
6、spfile参数的备份与恢复
主要介绍rman:
1)设置控制文件自动备份:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
这个参数设置后,在数据库中可以通过如下方式查询到:
mailto:SYS@source]SYS@source> desc v$rman_configuration;
Name Null? Type
----------------------------------------- -------- ----------------------------
CONF# NUMBER
NAME VARCHAR2(65)
VALUE VARCHAR2(1025)

mailto:SYS@source]SYS@source> select * from v$rman_configuration;
CONF# NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 CONTROLFILE AUTOBACKUP ON

2)更改自动备份的位置

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/bak/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/bak/%F';
new RMAN configuration parameters are successfully stored
3)检查自动备份

mailto:SYS@source]SYS@source> alter tablespace fmismain add datafile '/oracle/oradata/source/FMISMAIN2.ORA' size 10m autoextend off;
Tablespace altered.
mailto:SYS@source]SYS@source> !ls -ls /oracle/bak
total 7312
7312 -rw-r----- 1 oracle oinstall 7471104 Dec 2 09:46 c-2847860684-20091202-00
mailto:SYS@source]SYS@source> desc v$backup_spfile;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
RECID NUMBER
STAMP NUMBER
SET_STAMP NUMBER
SET_COUNT NUMBER
MODIFICATION_TIME DATE
BYTES NUMBER
COMPLETION_TIME DATE
mailto:SYS@source]SYS@source> select * from v$backup_spfile;
RECID STAMP SET_STAMP SET_COUNT MODIFICATION_TIME BYTES COMPLETION_TIME
---------- ---------- ---------- ---------- ------------------- ---------- -------------------
1 736681616 736681616 1 2009-12-02 09:44:47 2 2009-12-02 09:46:56

通过RMAN可以列出这些备份集:
RMAN> list backup of spfile;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Full 7.11M DISK 00:00:02 2009-12-02 09:46:58
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20091202T094656
Piece Name: /oracle/bak/c-2847860684-20091202-00
SPFILE Included: Modification time: 2009-12-02 09:44:47

4) 记录数据库变化(通过更改表空间信息、备份表空间等)
5)测试恢复。
RMAN> restore spfile to '/oracle/spfile.ora' from autobackup;
Starting restore at 2009-12-02 09:55:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=18 devtype=DISK
recovery area destination: /oracle/flash_recovery_area
database name (or database unique name) used for search: SOURCE
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20091202
channel ORA_DISK_1: autobackup found: /oracle/bak/c-2847860684-20091202-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 2009-12-02 09:55:10
如果数据库无法mount,是不能使用上面的方式来恢复控制文件及数据文件的。
此时,oracle需要提供数据库的dbid,才能找到相应的自动备份用以恢复,如果无法得知DBID,那么可以直接指定
自动备份集来进行恢复。
如:
RMAN> restore spfile to '/oracle/spfile3.ora' from autobackup;
Starting restore at 2009-12-02 10:30:37
using channel ORA_DISK_1
recovery area destination: /oracle/flash_recovery_area
database name (or database unique name) used for search: SOURCE
channel ORA_DISK_1: no autobackups found in the recovery area
autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/02/2009 10:30:38
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
RMAN> restore spfile to '/oracle/spfile2.ora' from '/oracle/bak/c-2847860684-20091202-00';
Starting restore at 2009-12-02 10:30:54
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /oracle/bak/c-2847860684-20091202-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 2009-12-02 10:30:56
如果通过DBID来恢复,如下:
RMAN> set dbid=2847860684
executing command: SET DBID
RMAN> startup nomount;
Oracle instance started
Total System Global Area 272629760 bytes
Fixed Size 1218944 bytes
Variable Size 83887744 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
RMAN> set controlfile autobackup format for device type disk to '/oracle/bak/%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN> restore spfile to '/oracle/spfile3.ora' from autobackup;
Starting restore at 2009-12-02 10:35:18
using channel ORA_DISK_1
recovery area destination: /oracle/flash_recovery_area
database name (or database unique name) used for search: SOURCE
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20091202
channel ORA_DISK_1: autobackup found: /oracle/bak/c-2847860684-20091202-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 2009-12-02 10:35:22
如果所有的备份参数文件都删除了,则手工创建一个spfile文件,再把数据库默认的参数写回去即可,如下:
set linsize 100
set pagesize 999
set heading off
set feedback off
spool /oracle/inittmp.ora
select '*.'||name||'='|| value from v$parameter where isdefault='FALSE';
spool off
结果如下:
*.processes=150
*.sga_max_size=272629760
*.nls_language=SIMPLIFIED CHINESE
*.nls_territory=CHINA
*.sga_target=272629760
*.control_files=/oracle/oradata/source/control01.ctl, /oracle/oradata/source/control02.ctl, /oracle/oradata/source/control03.ctl
*.db_file_name_convert=/oracle/oradata/csfmis, /oracle/oradata/source
*.log_file_name_convert=/oracle/oradata/csfmis, /oracle/oradata/source
*.db_block_size=16384
*.compatible=10.2.0.1.0
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest=/oracle/flash_recovery_area
*.db_recovery_file_dest_size=2147483648
*.undo_management=AUTO
*.undo_tablespace=UNDOTBS1
*.remote_login_passwordfile=EXCLUSIVE
*.db_domain=
*.job_queue_processes=10
*.background_dump_dest=/oracle/admin/source/bdump
*.user_dump_dest=/oracle/admin/source/udump
*.core_dump_dest=/oracle/product/10.2.0/admin/source/cdump
*.audit_file_dest=/oracle/admin/source/adump
*.db_name=source
*.open_cursors=300
*.pga_aggregate_target=50683392

7、使用BBED获得数据库的DBID
用BBED打开控制文件,可以清晰的看到数据库的DBID:
[oracle@source lib]$ more par.bbd
blocksize=16384
listfile=file.lst
mode=edit
[oracle@source lib]$ more file.lst
1 /oracle/oradata/source/control01.ctl
[oracle@source lib]$ ./bbed parfile=par.bbd
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Dec 2 11:19:11 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> dump
File: /oracle/oradata/source/control01.ctl (1)
Block: 1 Offsets: 0 to 511 Dba:0x00400001
------------------------------------------------------------------------
15c20000 01000000 00000000 00000104 6a350000 00000000 0001200a cce7bea9
534f5552 43450000 df050000 c2010000 00400000 00000100 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
abae2baa 8d497b2b 2e1c1f00 00000000 d3eee82b 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 08000000 08000000 08000000 00000000 00000000 00000000
01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
在offsets(29-32)时,发现cce7bea9,高位与低位交换为a9bee7cc,再由十六进制转换为十进制,正好是DBID:2847860684
如果需要修改BBED,方法如下:
set offset 28
modify /x 9b87
set offset 30
modify /x fb03
sum apply

8、使用dump datafile的方法获得DBID
none> conn /as sysdba;
Connected to an idle instance.
none> startup nomount;
ORACLE instance started.
Total System Global Area 272629760 bytes
Fixed Size 1218944 bytes
Variable Size 83887744 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
none> oradebug setmypid
Statement processed.
none> oradebug tracefile_name
Statement processed.
none> alter system dump datafile '/oracle/oradata/source/SYSTEM01.DBF' block 1;
System altered.
none> oradebug tracefile_name
/oracle/admin/source/udump/source_ora_20368.trc
[oracle@source bdump]$ more /oracle/admin/source/udump/source_ora_20368.trc
/oracle/admin/source/udump/source_ora_20368.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name: Linux
Node name: source
Release: 2.6.9-42.0.0.0.1.ELsmp
Version: #1 SMP Sun Oct 15 14:02:40 PDT 2006
Machine: i686
Instance name: source
Redo thread mounted by this instance: 0
Oracle process number: 13
Unix process pid: 20368, image: mailto:oracle@source]oracle@source (TNS V1-V3)
*** 2009-12-02 11:02:24.903
*** SERVICE NAME:() 2009-12-02 11:02:24.902
*** SESSION ID:(159.1) 2009-12-02 11:02:24.902
Start dump data block from file /oracle/oradata/source/SYSTEM01.DBF minblk 1 maxblk 1
V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=2847860684=0xa9bee7cc, Db Name='SOURCE'
Activation ID=0=0x0
Control Seq=1500=0x5dc, File size=33920=0x8480
File Number=1, Blksiz=16384, File Type=3 DATA
Dump all the blocks in range:
End dump data block from file /oracle/oradata/source/SYSTEM01.DBF minblk 2 maxblk 1
从上可以看到DBID为2847860684(0xa9bee7cc)
9、oracle 11g spfile文件的创建
使用当前参数设置创建一个spfile文件:
create spfile='/tmp/spfile.ora' from memory;

10、event事件的设置与取消
以下是修改spfile文件,查看evnet事件:
alter system set event='10841 trace name context forever' scope=spfile;
startup force;
show parameter event
如果想取消event事件,同样可以用参数reset的方法:
alter system reset event scope=spfile sid='*';

11、以v$parameter为例说明如何通过同义词定位对象
可以通过10046事件跟踪,再去看trace文件,如下:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/source/udump/source_ora_20696.trc
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> select count(*) from v$parameter;
COUNT(*)
----------
258

Session altered.
SQL> create table v$parameter as select * from xtdw;
Table created.
SQL> select count(*) from v$parameter;
SQL> alter session set events '10046 trace name context off';
跟踪部分内容如下:
FETCH #1:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1261022358232331
WAIT #1: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=47127 tim=1261022358232410
WAIT #1: nam='SQL*Net message from client' ela= 1686 driver id=1650815232 #bytes=1 p3=0 obj#=47127 tim=1261022358234155
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=3 pr=0 pw=0 time=152 us)'
看一下obj#=47127,如下:
SQL>select object_name,object_id,object_type from dba_objects where object_id=-1
OBJECT_NAME OBJECT_ID OBJECT_TYPE
--------------- -------------- -------------------
XTDW 47127 TABLE

看到了吧,刚才创建的表v$parameter

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】