June 26, 2004
Oracle诊断案例-Sql_trace之一
作者:eygle
出处:http://blog.eygle.com
问题描述:
这是帮助一个公司的诊断案例.
应用是一个后台新闻发布系统.
症状是,通过连接访问新闻页是极其缓慢
通常需要十数秒才能返回.
这种性能是用户不能忍受的.
操作系统:SunOS 5.8
数据库版本:8.1.7
诊断时是晚上,无用户访问
在前台点击相关页面,同时进行进程跟踪
查询v$session视图,获取进程信息
|
SQL> select sid,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1 1
2 1
3 1
4 1
5 1
6 1
7 284 IFLOW
11 214 IFLOW
12 164 SYS
16 1042 IFLOW
10 rows selected.
|
启用相关进程sql_trace
|
SQL> exec dbms_system.set_sql_trace_in_session(7,284,true)
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(11,214,true)
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(16,1042,true)
PL/SQL procedure successfully completed.
SQL> select sid,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1 1
2 1
3 1
4 1
5 1
6 1
7 284 IFLOW
11 214 IFLOW
12 164 SYS
16 1042 IFLOW
10 rows selected.
|
等候一段时间,关闭sql_trace
|
SQL> exec dbms_system.set_sql_trace_in_session(7,284,false) PL/SQL procedure successfully completed. SQL> exec dbms_system.set_sql_trace_in_session(11,214,false) PL/SQL procedure successfully completed. SQL> exec dbms_system.set_sql_trace_in_session(16,1042,false) PL/SQL procedure successfully completed. |
2.检查trace文件
检查发现以下语句是可疑的
|
******************************************************************************** select auditstatus,categoryid,auditlevel from categoryarticleassign a,category b where b.id=a.categoryid and articleId= 20030700400141 and auditstatus>0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.81 0.81 0 3892 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.81 0.81 0 3892 0 1 ******************************************************************************** |
这里显然是根据articleId进行新闻读取的.
很可疑的是query读取有3892
这个内容引起了我的注意.
如果遇到过类似的问题，大家在这里就应该知道是怎么回事情了.
如果没有遇到过的朋友，可以在这里思考一下再往下看.
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 41
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
2 INDEX RANGE SCAN (object id 25062)
1 TABLE ACCESS BY INDEX ROWID CATEGORY
2 INDEX UNIQUE SCAN (object id 25057)
********************************************************************************
select auditstatus,categoryid
from
categoryarticleassign where articleId=20030700400138 and categoryId in ('63',
'138','139','140','141','142','143','144','168','213','292','341','346',
'347','348','349','350','351','352','353','354','355','356','357','358',
'359','360','361','362','363','364','365','366','367','368','369','370',
'371','372','383','460','461','462','463','621','622','626','629','631',
'634','636','643','802','837','838','849','850','851','852','853','854',
'858','859','860','861','862','863','-1')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 4.91 4.91 0 2835 7 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 4.91 4.91 0 2835 7 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 41
Rows Row Source Operation
------- ---------------------------------------------------
1 'TABLE ACCESS FULL CATEGORYARTICLEASSIGN'
我们注意到，这里有一个全表扫描存在
********************************************************************************
|
3.登陆数据库,检查相应表结构
|
|
我们注意到,IDX_ARTICLEID索引在以上查询中都没有被用到.
检查表结构:
|
SQL> desc categoryarticleassign Name Null? Type ----------------------------------------- -------- ---------------------------- CATEGORYID NOT NULL NUMBER ARTICLEID NOT NULL VARCHAR2(14) ASSIGNTYPE NOT NULL VARCHAR2(1) AUDITSTATUS NOT NULL NUMBER SORTID NOT NULL NUMBER UNPASS VARCHAR2(255) |
问题发现:
因为ARTICLEID是个字符型数据,查询中给入的articleId= 20030700400141 是一个数字值
Oracle发生潜在的数据类型转换,从而导致了索引失效
|
SQL> select auditstatus,categoryid
2 from
3 categoryarticleassign where articleId=20030700400132;
AUDITSTATUS CATEGORYID
----------- ----------
9 94
0 383
0 695
Elapsed: 00:00:02.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=110 Card=2 Bytes=38)
1 0 TABLE ACCESS (FULL) OF 'CATEGORYARTICLEASSIGN' (Cost=110 Card=2 Bytes=38)
|
4.解决方法
简单的在参数两侧各增加一个',既可解决这个问题.
对于类似的查询,我们发现Query模式读取降低为2
几乎不需要花费CPU时间了
********************************************************************************
select unpass
from
categoryarticleassign where articleid='20030320000682' and categoryid='113'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID CATEGORYARTICLEASSIGN
1 INDEX RANGE SCAN (object id 3080)
********************************************************************************
|
至此,这个问题得到了完满的解决.
Posted by eygle at 11:43 PM | Comments (1)
Oracle诊断案例-Sql_trace之二
作者:eygle
出处:http://blog.eygle.com
问题说明:
很多时候
在我们进行数据库操作时
比如drop user,drop table等，经常会遇到这样的错误
ORA-00604: error occurred at recursive SQL level 1 .
这样的提示，很多时候是没有丝毫用处的.
本案例就这一类问题提供一个思路及方法供大家参考.
1. drop user出现问题
报出以下错误后退出
|
|
关于 recursive SQL 错误
我们有必要做个简单说明.
我们知道，当我们发出一条简单的命令以后
Oracle数据库要在后台解析这条命令，并转换为Oracle数据库的一系列后台操作.
这些后台操作统称为递归sql
比如create table这样一条简单的DDL命令
Oracle数据库在后台，实际上要把这个命令转换为
对于obj$,tab$,col$等底层表的插入操作.
Oracle所作的工作可能比我们有时候想的要复杂的多.
2.跟踪问题
我们知道Oracle提供sql_trace的功能
可以用于跟踪Oracle数据库的后台递归操作.
通过跟踪文件，我们可以找到问题的所在
以下是格式化(tkprof)后的输出:
|
|
Oracle把错误信息首先呈现出来
我们看到ORA-00942错误是由于SDO_GEOM_METADATA_TABLE表/视图不存在所致
问题由此可以定位
对于这一类的错误，定位问题以后解决的方法就要依据具体问题原因而定了。
3.问题定位
对于本案例，通过Metalink获得以下解释:
Problem Description
-------------------
The Oracle Spatial Option has been installed and you are encountering
the following errors while trying to drop a user, who has no spatial tables,
connected as SYSTEM:
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
A 942 error trace shows the failing SQL statement as:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = '<user>'
Solution Description
--------------------
(1) Create a synonym for SDO_GEOM_METADATA_TABLE under SYSTEM which points to
MDSYS.SDO_GEOM_METADATA_TABLE.
对于本例，为MDSYS.SDO_GEOM_METADATA_TABLE创建一个同义词即可解决.
是相对简单的情况.
(2) Now the user can be dropped connected as SYSTEM.
Related Documents
-----------------
<Note.159776.1> ORA-604 and ORA-942 Reported During DROP USER CASCA
4.实际处理
MDSYS.SDO_GEOM_METADATA_TABLE为Spatial对象
如果未使用Spatial选项，可以删除
|
SQL> connect / as sysdba SQL> select * from dba_sdo_geom_metadata order by owner; SQL> select object_name from dba_objects where object_name like '%SDO%'; OBJECT_NAME 88 rows selected. SQL> drop user MDSYS cascade; User dropped. SQL> select owner,type_name from dba_types where type_name like 'SDO%'; no rows selected SQL> SQL> alter session set sql_trace=true; Session altered. SQL> drop user wapcomm; User dropped. SQL> alter session set sql_trace=false; Session altered. SQL> exit |
这时用户得以顺利drop
5.一点总结
使用sql_trace可以跟踪数据库的很多后台操作
有利于我们发现问题的所在
很多时候，我们想要研究Oracle的内部活动或后台操作
也可以通过sql_trace跟踪
sql_trace/10046 是Oracle提供的最为有效的诊断工具之一.
Posted by eygle at 11:37 PM | Comments (0)
Oracle诊断案例-Spfile案例一则
作者:eygle
出处:http://blog.eygle.com
情况说明:系统：SUN Solaris8
数据库版本:9203
问题描述：工程人员报告，数据库在重新启动时无法正常启动.检查发现UNDO表空间丢失.
问题诊断及解决过程如下:
1. 登陆系统检查alert.log文件
检查alert.log文件是通常是我们诊断数据库问题的第一步
SunOS 5.8 login: root "alert_gzhs.log" 7438 lines, 283262 characters ............. |
2. 尝试重新启动数据库
bash-2.03$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4月 1 11:43:52 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 已连接到空闲例程。 SQL> startup Total System Global Area 4364148184 bytes ............. |
工程人员报告的问题重现.
3. 检查数据文件
bash-2.03$ cd /u01/ oradata/gzhs |
发现存在文件UNDOTBS2.dbf
4. mount数据库，检查系统参数
bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4月 1 11:46:20 2004 SQL> SQL> show parameter spfile |
发现系统没有使用spfile,而初始化参数设置的undo表空间为UNDOTBS1
5. 检查参数文件
发现设置的UNDO表空间正是UNDOTBS1
undo_management=AUTO |
这个设置是极其可疑的.
怀疑参数文件和实际数据库设置不符.
6. 再次检查alert文件
查找对于UNDO表空间的操作
第一部分，创建数据库时的信息:
Sat Feb 7 20:30:12 2004 |
注意，这也是OCP教材上提到的两种创建UNDO表空间的方式之一
第二部分,发现创建UNDOTBS2的记录信息:
Wed Mar 24 20:20:58 2004 Successfully onlined Undo Tablespace 15. |
第三部分，新的UNDO表空间被应用
Wed Mar 24 20:24:25 2004
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=MEMORY;
我们发现问题就在这里,创建了新的UNDO表空间以后，因为使用的是pfile文件，修改的只对当前实例生效，操作人员忘记了修改pfile文件.
如果使用spfile，缺省的修改范围是both,会同时修改spfile文件，就可以避免以上问题的出现.
第四部分，删除了UNDOTBS1的信息
Wed Mar 24 20:25:01 2004 |
这样再次重新启动数据库的时候，问题出现了，pfile中定义的UNDOTBS1找不到了，而且操作实在很久以前，没人能回忆起来，甚至无法得知是什么人的操作。
7. 更改pfile，启动数据库
修改undo表空间
########################################### .... bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4月 1 11:55:11 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> select * from v$version; BANNER SQL> exit |
在这里我们可以看到，使用spfile可以免去手工修改pfile文件的麻烦，减少了犯错的可能。
既然Oracle9i给我们提供了这个新特性，就值得我们学习使用它.
Posted by eygle at 11:15 PM | Comments (2)
10g新特性之-expdp与传统exp的速度比较
作者:eygle
出处:http://blog.eygle.com
测试环境:
System Configuration: Sun Microsystems sun4u Sun Enterprise 450 (4 X UltraSPARC-II 296MHz)
System clock frequency: 99 MHz
Memory size: 2048 Megabytes
数据量:
单表测试,1363292行记录，116.6 MB左右数据
1.使用并行 expdp
花费时间:
Tue Apr 27 10:21:54 CST 2004 - Tue Apr 27 10:21:10 CST 2004 = 43秒
$ cat bak.sh
date
expdp eygle/eygle dumpfile=big_big_table.dmp directory=dpdata tables=big_big_table job_name=exptab parallel=4
date
$ ./bak.sh
Tue Apr 27 10:21:10 CST 2004Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 10:21
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "EYGLE"."EXPTAB": eygle/******** dumpfile=big_big_table.dmp directory=dpdata tables=big_big_table job_name=exptab parallel=4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 248 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows
Master table "EYGLE"."EXPTAB" successfully loaded/unloaded
******************************************************************************
Dump file set for EYGLE.EXPTAB is:
/opt/oracle/dpdata/big_big_table.dmp
Job "EYGLE"."EXPTAB" successfully completed at 10:21Tue Apr 27 10:21:54 CST 2004
2.使用正常 expdp
花费时间:
Tue Apr 27 10:23:36 CST 2004 - Tue Apr 27 10:23:02 CST 2004 = 34 秒
看来并行的差异需要更大的数据量的测试
$ cat bak2.sh date expdp eygle/eygle dumpfile=big_big_table2.dmp directory=dpdata tables=big_big_table job_name=exptab date$ ./bak2.sh
Tue Apr 27 10:23:02 CST 2004Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 10:23
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "EYGLE"."EXPTAB": eygle/******** dumpfile=big_big_table2.dmp directory=dpdata tables=big_big_table job_name=exptab
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 248 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows
Master table "EYGLE"."EXPTAB" successfully loaded/unloaded
******************************************************************************
Dump file set for EYGLE.EXPTAB is:
/opt/oracle/dpdata/big_big_table2.dmp
Job "EYGLE"."EXPTAB" successfully completed at 10:23Tue Apr 27 10:23:36 CST 2004
3.常规路径exp
花费时间:Tue Apr 27 10:27:00 CST 2004 - Tue Apr 27 10:24:54 CST 2004 = 2:06
这是花费时间最长的.
是 126/34 = 370.58823529411764705882352941176%
expdp明显快于exp
$ cat bak3.sh date exp eygle/eygle file=big_big_table3.dmp tables=big_big_table date$ ./bak3.sh
Tue Apr 27 10:24:54 CST 2004Export: Release 10.1.0.2.0 - Production on Tue Apr 27 10:24:54 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path ...
. . exporting table BIG_BIG_TABLE 1363292 rows exported
Export terminated successfully without warnings.
Tue Apr 27 10:27:00 CST 2004
4.直接路径exp
花费时间:
Tue Apr 27 10:52:09 CST 2004 - Tue Apr 27 10:50:58 CST 2004 = 1.11
是 71/34 = 208.82352941176470588235294117647%
直接路径导出快于常规路径导出，但是仍然不敌expdp
$ cat bak4.sh date exp eygle/eygle file=big_big_table3.dmp tables=big_big_table direct=y date$ ./bak4.sh
Tue Apr 27 10:50:58 CST 2004Export: Release 10.1.0.2.0 - Production on Tue Apr 27 10:50:58 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Direct Path ...
. . exporting table BIG_BIG_TABLE 1363292 rows exported
Export terminated successfully without warnings.
Tue Apr 27 10:52:09 CST 2004
5.文件大小
传统方式exp文件要大于expdp的文件大小
大约大出10M左右.
$ ls -al
total 741566
drwxr-xr-x 2 oracle dba 512 Apr 27 10:50 .
drwxr-xr-x 23 oracle dba 1024 Apr 26 22:53 ..
-rwxr-xr-x 1 oracle dba 120 Apr 27 10:21 bak.sh
-rwxr-xr-x 1 oracle dba 111 Apr 27 10:22 bak2.sh
-rwxr-xr-x 1 oracle dba 71 Apr 27 10:24 bak3.sh
-rwxr-xr-x 1 oracle dba 80 Apr 27 10:50 bak4.sh
-rw-r----- 1 oracle dba 122413056 Apr 27 10:21 big_big_table.dmp
-rw-r----- 1 oracle dba 122417152 Apr 27 10:23 big_big_table2.dmp
-rw-r--r-- 1 oracle dba 134604800 Apr 27 10:52 big_big_table3.dmp
-rw-r--r-- 1 oracle dba 965 Apr 27 10:23 export.log
以下是导入测试:
6. imp测试
花费时间: Tue Apr 27 11:15:11 CST 2004 - Tue Apr 27 11:08:24 CST 2004 = 6:47s
$ cat rev2.sh date imp eygle/eygle file=big_big_table3.dmp tables=big_big_table date$ ./rev2.sh
Tue Apr 27 11:08:24 CST 2004Import: Release 10.1.0.2.0 - Production on Tue Apr 27 11:08:24 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.01.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing EYGLE's objects into EYGLE
. . importing table "BIG_BIG_TABLE" 1363292 rows imported
Import terminated successfully without warnings.
Tue Apr 27 11:15:11 CST 2004
$
7. impdp
花费时间: Tue Apr 27 11:07:06 CST 2004 - Tue Apr 27 11:06:40 CST 2004 = 26s
与imp相比这个速度实在是惊人.
407/26 = 1565.3846153846153846153846153846%
无怪乎Oracle说impdp才是data pump真正杰出的地方(really stands out)
$ ./rev.sh Tue Apr 27 11:06:40 CST 2004Import: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 11:06
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "EYGLE"."IMPTAB" successfully loaded/unloaded
Starting "EYGLE"."IMPTAB": eygle/******** dumpfile=big_big_table.dmp tables=big_big_table directory=dpdata job_name=imptab
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
. . imported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows
Job "EYGLE"."IMPTAB" successfully completed at 11:07Tue Apr 27 11:07:06 CST 2004
-The End-
Posted by eygle at 8:34 PM | Comments (0)
10g新特性之-跨平台传输表空间
作者:eygle
出处:http://blog.eygle.com
1.准备工作:
查询源数据库平台信息
SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (64-bit) Big
查询目标数据库平台信息
SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
查询Oracle10g支持的平台转换
SQL> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
2.创建一个独立的自包含表空间
用于测试
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:04:08 2004
Copyright (c) 1982, 2004, Oracle.?All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/eygle/system01.dbf
/opt/oracle/oradata/eygle/undotbs01.dbf
/opt/oracle/oradata/eygle/sysaux01.dbf
/opt/oracle/oradata/eygle/users01.dbf
/data1/oradata/systemfile/eygle01.dbf
/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
7 rows selected.
SQL> create tablespace trans
2?datafile '/data1/oradata/systemfile/trans01.dbf'
3?size 10M;
Tablespace created.
SQL> create user trans identified by trans
2?default tablespace trans;
User created.
SQL> grant connect,resource to trans;
Grant succeeded.
SQL> connect trans/trans
Connected.
SQL> create table test as select * from user_objects;
Table created.
SQL> select?count(*) from test;
COUNT(*)
----------
1
SQL> select * from test;
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME?OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED?LAST_DDL_TIM TIMESTAMP?STATUS?T G S
------------ ------------ ------------------- ------- - - -
TEST
15604?15604 TABLE
27-APR-04?27-APR-04?2004-04-27:14:05:42 VALID?N N N
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data
3.导出要传输的表空间
$ pwd
/opt/oracle
$ cd dpdata
$ ls
$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_tablespace=trans
LRM-00101: unknown parameter name 'transport_tablespace'
$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:07
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'TRANS' is not read only
Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 14:08
注意:传输表空间必须置为只读状态
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:08:13 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter tablespace trans read only;
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:08
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EYGLE.SYS_EXPORT_TRANSPORTABLE_01 is:
/opt/oracle/dpdata/trans.dmp
Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:09
4.使用rman转换文件格式
$ rman target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: EYGLE (DBID=1337390772)
RMAN> convert tablespace trans
2> to platform 'Microsoft Windows IA (32-bit)'
3> Format '/tmp/%U';
Starting backup at 27-APR-04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00008 name=/data1/oradata/systemfile/trans01.dbf
converted datafile=/tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 27-APR-04
RMAN> exit
Recovery Manager complete.
5.确认导出文件已生成
$ ls /tmp/data*
/tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ ls -l /tmp/data*
-rw-r----- 1 oracle dba 10493952 Apr 27 14:12 /tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ chmod 777 /tmp/data*
$ chmod 777 /opt/oracle/dpdata/*
6.通过ftp传输文件至目标主机
220 billing-center.hurray.com.cn FTP server (SunOS 5.8) ready.
User (172.16.33.32none)): gqgai
331 Password required for gqgai.
Password:
230 User gqgai logged in.
ftp> bin
200 Type set to I.
ftp> cd /tmp
250 CWD command successful.
ftp> mget data*
200 Type set to I.
mget data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg? y
200 PORT command successful.
150 Binary data connection for data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg (172.16.32.65,2885) (10493952 bytes).
226 Binary Transfer complete.
ftp: 10493952 bytes received in 15.90Seconds 659.87Kbytes/sec.
ftp> cd /opt/oracle/dpdata
250 CWD command successful.
ftp> ls
200 PORT command successful.
150 ASCII data connection for /bin/ls (172.16.32.65,2889) (0 bytes).
export.log
trans.dmp
226 ASCII Transfer complete.
ftp: 23 bytes received in 0.01Seconds 2.30Kbytes/sec.
ftp> bin
200 Type set to I.
ftp> mget trans.dmp
200 Type set to I.
mget trans.dmp? y
200 PORT command successful.
150 Binary data connection for trans.dmp (172.16.32.65,2893) (73728 bytes).
226 Binary Transfer complete.
ftp: 73728 bytes received in 0.03Seconds 2457.60Kbytes/sec.
ftp> bye
221 Goodbye.
7.使用rman在目标数据库转换文件
E:\Oracle\oradata\eygle\dpdata>rman target /
恢复管理器: 版本10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
连接到目标数据库: EYGLE (DBID=1587222708)
RMAN> CONVERT DATAFILE 'E:\Oracle\oradata\eygle\dpdata\data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg'
2> DB_FILE_NAME_CONVERT
3> 'E:\Oracle\oradata\eygle\dpdata\data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg','E:\Oracle\oradata\eygle\EYGLE\DATAFILE\trans01.dbf';
启动 backup 于 27-4月 -04
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输出文件名=E:\ORACLE\ORADATA\EYGLE\DPDATA\DATA_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01FK92HG
已转换的数据文件 = E:\ORACLE\ORADATA\EYGLE\EYGLE\DATAFILE\TRANS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04
完成 backup 于 27-4月 -04
RMAN>
8.在目标数据库plugin数据文件
注意目标数据库中的目标用户必须存在,否则会报错.
E:\Oracle\oradata\eygle\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
ata\eygle\EYGLE\DATAFILE\TRANS01.DBF'
Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:02
Copyright (c) 2003, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\
Oracle\oradata\eygle\EYGLE\DATAFILE\TRANS01.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-29342: 数据库中不存在用户 TRANS
作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:02 停止
E:\Oracle\oradata\eygle\dpdata>sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:03 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user trans identified by trans;
用户已创建。
SQL> grant connect,resource to trans;
授权成功。
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
E:\Oracle\oradata\eygle\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
ata\eygle\EYGLE\DATAFILE\TRANS01.DBF'
Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:03
Copyright (c) 2003, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\
Oracle\oradata\eygle\EYGLE\DATAFILE\TRANS01.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:03 成功完成
9.检查数据
E:\Oracle\oradata\eygle\dpdata>sqlplus trans/trans
SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:50 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select count(*) from test;
COUNT(*)
----------
1
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
E:\Oracle\oradata\eygle\dpdata>
可以选择把表空间更改为读写
SQL> alter tablespace trans read write;
表空间已更改。
10.总结
10g的表空间跨平台迁移，较9i就是增加了一个使用Rman进行的文件格式转换的过程.
实际上也就是转换了数据文件头的格式信息而已.
然而这一简单改进带来的方便之处是显而易见的.
-The End-
Posted by eygle at 7:37 PM | Comments (14)
Oracle中password file的作用及说明
作者:eygle
出处:http://blog.eygle.com
在数据库没有启动之前，数据库内建用户是无法通过数据库来验证身份的
口令文件中存放sysdba/sysoper用户的用户名及口令
允许用户通过口令文件验证，在数据库未启动之前登陆
从而启动数据库
如果没有口令文件，在数据库未启动之前就只能通过操作系统认证.
使用Rman，很多时候需要在nomount,mount等状态对数据库进行处理
所以通常要求sysdba权限如果属于本地DBA组，可以通过操作系统认证登陆
如果是远程sysdba登陆，需要通过passwordfile认证.
1.remote_login_passwordfile = NONE
此时停用口令文件验证，Oracle数据库不允许远程SYSDBA/SYSOPER身份登录
无法通过远程进行数据库起停等操作管理
local:
|
[oracle@jumper oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Thu Apr 15 09:58:45 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production SQL> alter user sys identified by oracle; User altered. SQL> show parameter pass NAME TYPE VALUE --------------------- ----------- ------------------------------ remote_login_passwordfile string NONE |
remote:
|
E:\Oracle\ora92\bin>sqlplus /nolog |
此处实际上是无法通过口令文件验证
2.remote_login_passwordfile = exclusive
|
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile; System altered. SQL> startup force; ORACLE instance started. Total System Global Area 131142648 bytes Fixed Size 451576 bytes Variable Size 104857600 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> show parameter pass NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE SQL> alter user sys identified by oracle; User altered. |
remote:
|
E:\Oracle\ora92\bin>sqlplus /nolog |
这实际上就是通过口令文件验证登录的
3.进一步测试
如果此时我们删除passwdfile,sysdba/sysoper将无法认证，也就无法登陆数据库
Server:
|
SQL> ! |
Remote:
|
E:\Oracle\ora92\bin>sqlplus /nolog |
这实际上就是无法通过口令文件验证身份
4.如果丢失了passwdfile
如果使用passwdfile却意外丢失，此时将不能启动数据库
|
|
此时可以通过orapwd重建口令文件来解决
此处我们恢复口令文件既可
|
SQL> ! |
大致就是如此.
5. remote_login_passwordfile = shared
我们看一下Oracle9i文档中的说明:
SHARED
More than one database can use a password file. However, the only user recognized by the password file is SYS.
意思是说多个数据库可以共享一个口令文件,但是只可以识别一个用户:SYS
|
SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SQL> grant sysdba to eygle; SQL> show parameter password NAME TYPE VALUE |
我们看到,此时的口令文件中是不能添加用户的.
很多人的疑问在于:口令文件的缺省名称是orapw<sid>,怎么能够共享?
实际上是这样的: Oracle数据库在启动时,首先查找的是orapw<sid>的口令文件,如果该文件不存在,则开始查找,orapw的口令文件
如果口令文件命名为orapw,多个数据库就可以共享.
我们看一下测试:
|
[oracle@jumper dbs]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Tue Jul 6 09:40:34 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> ! [oracle@jumper dbs]$ ls hsjf initdw.ora inithsjf.ora init.ora lkHSJF orapwhsjf spfilehsjf.ora [oracle@jumper dbs]$ mv orapwhsjf orapwhsjf.bak [oracle@jumper dbs]$ exit exit SQL> startup ORACLE instance started. Total System Global Area 235999908 bytes Fixed Size 451236 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes ORA-01990: error opening password file '/opt/oracle/product/9.2.0/dbs/orapw'--这是最后查找的文件 ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 |
我们建立orapw口令文件,这时候可以打开数据库.
SQL> ! [oracle@jumper dbs]$ ls hsjf initdw.ora inithsjf.ora init.ora lkHSJF orapwhsjf.bak spfilehsjf.ora [oracle@jumper dbs]$ cp orapwhsjf.bak orapw [oracle@jumper dbs]$ exit exit SQL> alter database open; Database altered. SQL> show parameter passw NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string SHARED SQL>
|
那么你可能会有这样的疑问,多个Exclusive的数据库是否可以共享一个口令文件(orapw)呢?
我们继续这个实验:
SQL> show parameter password NAME TYPE VALUE [oracle@jumper dbs]$ strings orapw |
注意这里仅记录着INTERNAL/SYS的口令
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 时
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile; System altered. SQL> startup force; Total System Global Area 235999908 bytes |
注意这里,以EXCLUSIVE 方式启动以后,实例名称信息被写入口令文件.
此时如果有其他实例以Exclusive模式启动仍然可以使用这个口令文件,口令文件中的实例名称同时被改写.
也就是说,数据库只在启动过程中才读取口令文件,数据库运行过程中并不锁定该文件,类似于pfile/spfile文件.
SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SQL> grant sysdba to eygle; Grant succeeded. SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SQL> ! |
注意此时可以增加SYSDBA用户，并且这些信息可以被写入到口令文件.
一旦口令文件中增加了其他SYSDBA用户,此文件不再能够被其他Exclusive的实例共享.
实际上，口令文件对于其他用户来说就是启到了一个 sudo 的作用.
6.重建口令文件
如果口令文件丢失,可以使用orapwd可以重建口令文件，语法如下:
[oracle@jumper oracle]$ orapwd where
|
Posted by eygle at 11:59 AM | Comments (4)