有时候不想装software，有个偷懒的方法。在另外一台server上将software tar出来，解开后按以下步骤：

1.oracle用户: relink all

2.root 用户:运行root.sh

源数据库：sql 2000

目的数据库：sql2008

要求：copy logins

一个不算错误的错误。把应用重启就可以了。原因是长连接session在运行一个重编译的包后出错。

select sid, serial#, context,
round(sofar/totalwork*100,2) "% Complete",
substr(to_char(sysdate,'yymmdd hh24:mi:ss'),1,15) "Time Now",
elapsed_seconds
from v$session_longops
where compnam = 'dbms_backup_restore'; --> for 8.0

For Oracle8i and Oracle9i:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK

RMAN uses two types of rows in V$SESSION_LONGOPS: detail and aggregate rows.
Detail rows describe the files being processed by one job step, while aggregate rows describe
the files processed by all job steps in an RMAN command. A job step is the creation or restore
of one backup set or datafile copy. Detail rows are updated with every buffer that is read or
written during the backup step, so their granularity of update is small. Aggregate rows are updated
when each job step completes, so their granularity of update is large.

在服务器还可以登录的情况下，可以用以下快速方法获得占CPU的session的信息。

1.在9i/10g手工启动statspack快照,收集信息。

9i:

exec perfstat.statspack.snap();

10g:

exec dbms_workload_repository.create_snapshot;

2.利用一些进程监控工具如procexp.exe获得占cpu高的线程号.

3.SELECT a.sql_hash_value FROM v$session a,v$process b
WHERE a.paddr=b.addr AND b.spid=6012 (spid为上一点所获得的线程号）

4.select sql_text from v$sqltext where hash_value='xxxx' order by piece;

其中hash_value为第3点中所获得的sql_hash_value.

至此拿到占cpu的sql.

还要花时间去看看在远程服务器都很难登陆的情况下在其他客户端能否用什么工具去获得占cpu的线程号。

1.儿子1岁1一个月零一天学会自己走路，不用辅助。

2.2009年圣诞节，有意识的叫爸爸。

3.2010年3月7日，会叫妈妈

老了，写几个字记录一下。

这是在数据库升级到10.2.0.4以后做分析时碰到的问题。

 查看全文

这篇文章，在于提醒不要用system,sys用户去创建其他schema的索引。

$oracle:/export/home/oracle$ lslpp -l LGT*
Fileset Level State Description
----------------------------------------------------------------------------
Path: /usr/lib/objrepos
LGTOnmo.rte 4.2.0.0 COMMITTED NetWorker Module for Oracle
LGTOnw.clnt.rte 7.1.3.0 COMMITTED NetWorker Client
(Backup/Recover)
LGTOnw.man.rte 7.1.3.0 COMMITTED NetWorker Man Pages
(Backup/Recover)

Path: /etc/objrepos
LGTOnmo.rte 4.2.0.0 COMMITTED NetWorker Module for Oracle

具体解决方法如下：

rman>list incarnation;

rman>reset database incanation to..;

利用AWR的awrddrpt.sql，我们可以比较不同日期的相同时间段的数据库性能。具体可以参考：

http://www.5ienet.com/note/html/ash_awr/oracle-create-awr-different-report.shtml

这里要说明的是，如果数据库设的是/3gb /PAE,就不会设db_cache_size这个参数，而是db_block_buffer*db_block_size,在这种情况下，oracle就不会认识这种格式。已经从metalink的反馈中得知这是一个bug,所报的错误是：

ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 9515
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 11074
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 475

具体的解决如下，用sys用户,执行以下命令：

1. Please update __db_cache_size in wrh$_parameter:
SQL>update wrh$_parameter set VALUE = 2097152000 where PARAMETER_HASH in
(select PARAMETER_HASH from wrh$_parameter_name where parameter_name like
'__db_cache_size');
SQL>commit;

就可以了。

用以下方法就可以在另一台机器将dump文件导出

--- in centmsq3 --- (target database)

CREATE DIRECTORY dump_dir01 AS '/data/oracle/centmsq3/';
GRANT READ,WRITE ON DIRECTORY dump_dir01 TO export;

grant create database link to export;

connect export/123 create database link centqprf_exp connect to export identified by export using 'centqprf';

command:

expdp export/export directory=dump_dir01 schemas=stddata_suplr dumpfile=prod_stddata_suplr.dmp logfile=prod_centmsq3_stddata_suplr.log network_link=centqprf_exp

在测试库上insert一张大表，报undo不够，加上空间不够，就像flashback回去，但shutdown immediate的时候又不想等那么长的时间，就abort了。结果发现：

ORA-38760: This database instance failed to turn on flashback database

以下是解决方法：

select distinct(b.file#), a.datafile_blocks*8192/1024/1024/1024 from RC_BACKUP_DATAFILE a,DF b
where a.db_name='DB_NAME'
and a.completion_time>=to_date('20090810','yyyymmdd')
and a.completion_time<to_date('20090811','yyyymmdd')
and a.creation_change#=b.create_scn order by b.file#

1.

rman连接，然后:

rman>list incarnation of database;

从结果中获取db_key以及db_id

2.

以rman用户连接rman catalog进入sqlplus：

sql>execute dbms_rcvcat.unregisterdatabase(db_key,db_id);

3.rman > connect target;

rman>connect catalog

rman>register database;

====================================

 查看全文

sqlnet.ora

TRACE_DIRECTORY_CLIENT = c:abc

#TNSPING.TRACE_DIRECTORY = c:abc

#TNSPING.TRACE_LEVEL = SUPPORT

SQLNET.AUTHENTICATION_SERVICES= (NTS)

TRACE_FILE_CLIENT = bb

TRACE_LEVEL_CLIENT = ADMIN

当并发session在300左右时，用auto sga没有什么问题。但auto sga有一个bug。

Bug 5201883.

以下是工作中对database flashback的一些设置经验：

主要遵从以下步骤：

Transportable Tablespaces Steps:
· Determine the source and target database oracle version( Source database version can not be more than target database version).
· Verification of endian compatibilities of source and target and if there is any conversions required .
· Check Database Character set and national Character set for compatibility between source and target.
· Determine if there are external objects (external tables ,directory structures) need to moved separately to target database.
· Determine the tablespaces needs to be transported.
· Check for any violations.
· Put the tablespaces in read only mode.
· Export the meta data information for the tablespaces.
· Determine the size on each file system for these tablespace datafiles.
· Generate the ftp or copy commands for the files to moved to target.
· FTP or copy the source files to target location.
· Make Tablespaces to Read Write mode on the source.
· Generate the import par file with new location of the files on the target.
· Create the user on the target if required.
· Import into the target Database using export file and Par file generated from source database.
· Recompile all the procedures and packages.
· Put tablepsaces on target to read write mode.