笔记

最新推荐文章于 2024-05-31 15:23:18 发布

congku8444

最新推荐文章于 2024-05-31 15:23:18 发布

阅读量516

点赞数

135.155 ./rvrd -store rvrd7500.store -listen 7500 -http 3600
4.155 ./rvrd -store rvrd7500.store -listen 7500 -http 3500
alter database rename global_name to WAPDB.EYGLE.COM;
oradim -NEW -SID payroll2 -STARTMODE manual
http://www.scribd.com/doc/320791/Install-Oracle-RAC-10g-on-Oracle-Enterprise-Linux-Using-VMware

exec dbms_stats.create_stat_table('modmespd','stat_xhl','mvdata');
exec dbms_stats.export_table_stats(ownname=>'modmespd',tabname=>'LOT_MV',stattab=>'STAT_XHL');

RAC
http://soft.zdnet.com.cn/software_zone/2007/0724/436211.shtml
srvctl管理RAC数据库

物化视图MV综合技术应用
http://xsb.itpub.net/post/419/58589
371801345518

CREATE INDEX PCBASFISM4.R_WIP_TRACKING_T_IDX06 ON PCBASFISM4.R_WIP_TRACKING_T

################################################################3

exec dbms_system.set_sql_trace_in_session(10,7835,true);

select spid, pid
from v$process
where addr = (
        select paddr
        from v$session
        where sid = 99
);
oradebug setorapid 29
or
oradebug setospid {the spid above}
then
oradebug flush         -- flushes any remaining data to the trace file
oradebug close_trace   -- closes the trace file.

'10079 trace name context forever, level 2';

connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10079 trace name context forever,level 2

oradebug event 10079 trace name context off
#####################
For example to enable event 10046 level 8 in a session with SID 9 and serial number 29 use

EXECUTE dbms_system.set_ev (9,29,10046,8,'');

To disable event 10046 in the same session use

EXECUTE dbms_system.set_ev (9,29,10046,0,'');

###################################################################

oading average 在 HP-UX 的定義是 : the average number of jobs in the run queue over the last 1, 5, and 15 minutes for the active processors.

即使系統沒有 run 任何的 ap, 因為有 OS 的 process 在執行, 所以 loading average 不會為 0

一般來說, 我們是透過觀察 loading average , 來衡量 CPU 是否 loading 過重. 如 loading average 為 CPU 個數的 2 倍以下, 則 CPU performance 佳, 如超過 3 倍, 即使還有 idle , 此時系統 performance 已經很糟了
11:53
loading Average 的定義

COMMON VERB
add  add a batabase or instance
config  list theconfiguration for the database or instance
getenv  list the environment variables in the srvm configuration
remove  remove the database or instance
setenv  set the environment variable in the SRVM configuration
status  status of the database or instance
stop  stop the databae or instance
unsetenv set the environment variable in the SRVM
   configuration to unspecified

Usage: srvctl verb noun [options]

srvctl add database -d database_name [-m domain_name] -o oracle_home [-s spfile]
srvctl add instance -d database_name -i instance_name -n node_name

The following are examples of using the add command.
To add a new database:
srvctl add database -d mydb -o /ora/ora9
To add named instances to a database:
srvctl add instance -d mydb -i mydb01 -n gm01
srvctl add instance -d mydb -i mydb02 -n gm02
srvctl add instance -d mydb -i mydb03 -n gm03

SRVCTL Start
srvctl start database -d database_name [-o start_options] [-c connect_string]
srvctl start instance -d database_name -i instance_name [,instance_name-list]
[-o start_options] [-c connect_string]

Administering the GSD
gsdctl start — To start the GSD service
gsdctl stop — To stop the GSD service
gsdctl stat — To obtain the status of the GSD service

Administratering RAC database using SQL and SQL*PLUS
ensure the Cluster Manager (CM) component is started on each node.

Starting Databases in Cluster Mode on UNIX

Backup and Recovery in Real Application Clusters

如果看到latch free对应的latch为library cache pin，看看是谁block了谁，

SELECT 'waitting object: ' as "OBJECT", kglnaobj "VALUE"
FROM x$kglob
WHERE kglhdadr IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library cache pin')
union all
SELECT 'holder session: ' as "OBJECT", a.SID||' '||a.username||' '||a.program "VALUE"
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library cache pin')
union
SELECT 'SQL text: ' as "OBJECT", sql_text "VALUE"
FROM v$sqlarea
WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
SELECT sql_address, sql_hash_value
FROM v$session
WHERE SID IN (
SELECT SID
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')))；

查詢誰在用UNDO:
select s.username, rn.name,     rs.extents
           ,rs.status, t.used_ublk, t.used_urec
           ,do.object_name
    from    v$transaction   t
        ,v$session       s
        ,v$rollname      rn
        ,v$rollstat      rs
        ,v$locked_object lo
        ,dba_objects     do
where t.addr        = s.taddr
and    t.xidusn      = rn.usn
and    rn.usn        = rs.usn
and    t.xidusn      = lo.xidusn(+)
and    do.object_id = lo.object_id

查詢一個MV_LOG上有多少個MV,以及最新刷新時間：

SELECT owner, NAME, snapshot_site,
TO_CHAR (current_snapshots, 'mm/dd/yyyy hh24:mi') current_snapshots
FROM dba_registered_snapshots, dba_snapshot_logs
WHERE dba_registered_snapshots.snapshot_id = dba_snapshot_logs.snapshot_id(+)
AND dba_snapshot_logs.MASTER = UPPER ('&table_name');

select username,
       v$lock.sid,
        trunc(id1/power(2,16)) rbs,
         bitand(id1,to_number('ffff','xxxx'))+0 slot,
        id2 seq,
        lmode,
         request
from v$lock, v$session
where v$lock.type = 'TX'
   and v$lock.sid = v$session.sid
   and v$session.username = USER
/

USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0

tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 46 160

select
           (select username from v$session where sid=a.sid) blocker,
      a.sid,
      ' is blocking ',
       (select username from v$session where sid=b.sid) blockee,
          b.sid
    from v$lock a, v$lock b
   where a.block = 1
     and b.request > 0
     and a.id1 = b.id1
     and a.id2 = b.id2

SHOWSQL.SQL
declare
     x number;
begin
     for x in
     ( select username||'('||sid||','||serial#||
                 ') spid = ' || process ||
                 ' program = ' || program username,
              to_char(LOGON_TIME,' Day HH24:MI') logon_time,
              to_char(sysdate,' Day HH24:MI') current_time,
              sql_address, LAST_CALL_ET
         from v$session
        where status = 'ACTIVE'
          and rawtohex(sql_address) <> '00'
          and username is not null order by last_call_et )
     loop
         for y in ( select max(decode(piece,0,sql_text,null)) ||
                           max(decode(piece,1,sql_text,null)) ||
                           max(decode(piece,2,sql_text,null)) ||
                           max(decode(piece,3,sql_text,null))
                                sql_text
                      from v$sqltext_with_newlines
                     where address = x.sql_address
                       and piece < 4)
         loop
             if ( y.sql_text not like '%listener.get_cmd%' and
                  y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
             then
                 dbms_output.put_line( '--------------------' );
                 dbms_output.put_line( x.username );
                 dbms_output.put_line( x.logon_time || ' ' ||
                                       x.current_time||
                                       ' last et = ' ||
                                       x.LAST_CALL_ET);
                 dbms_output.put_line(
                           substr( y.sql_text, 1, 250 ) );
             end if;
         end loop;
     end loop;
end;

殺大量進程
SELECT 'ALTER SYSTEM KILL SESSION'''|| SID||','|| SERIAL#||''';' FROM V$SESSION WHERE USERNAME = 'OQC'
alter system kill session '121,32081';

SQL>ALTER SESSION SET max_dump_file_size = UNLIMITED;

Session altered .

SQL>ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

alter session set events '10046 trace name context off';

Session altered .

SQL>ALTER SESSION SET EVENTS '942 trace name errorstack level 10';

Session altered .

#########################
查詢進程開始時間

#! /bin/bash

# starttime.sh

# -sakulagi

PID=$1
JIFFIES=`cat /proc/$PID/stat | cut -d" " -f22`
UPTIME=`grep btime /proc/stat | cut -d" " -f2`
START_SEC=$(( $UPTIME + $JIFFIES / 100 ))
START_TIME=`date -d "1970-1-1 UTC $START_SEC seconds"`
echo $START_TIME
############################################################

EXPORT

FILE="D:\ERE.DMP"
LOG="D:\exp_ERE.log"
TABLES=('WLMES.C_BATCH', 'WLMES.C_FMA', 'WLMES.C_HOLD', 'WLMES.C_MES_ERROR', 'WLMES.C_MESSAGE', 'WLMES.C_PACKINGMODE', 'WLMES.C_PKGEQP', 'WLMES.C_PRODUCTGRADEHISTORY', 'WLMES.C_PRODUCTJOB', 'WLMES.C_SAMPLING_CARTON', 'WLMES.C_SAMPLING_LOT', 'WLMES.C_SAMPLING_RULE', 'WLMES.C_SAMPLING_RULE_AREA', 'WLMES.C_SCRAPREASON', 'WLMES.C_SN', 'WLMES.C_VENDOR', 'WLMES.C_WOLOTNAME')
ROWS=N
GRANTS=N
FEEDBACK=1000

SELECT * FROM DBA_INDEXES WHERE WNER='WLMESTEMP' AND TABLE_NAME IN ('C_BATCH',
'C_FMA',
'C_HOLD',
'C_MES_ERROR',
'C_MESSAGE',
'C_PACKINGMODE',
'C_PKGEQP',
'C_PRODUCTGRADEHISTORY',
'C_PRODUCTJOB',
'C_SAMPLING_CARTON',
'C_SAMPLING_LOT',
'C_SAMPLING_RULE',
'C_SAMPLING_RULE_AREA',
'C_SCRAPREASON',
'C_SN',
'C_VENDOR',
'C_WOLOTNAME'
);

表空
ALTER TABLE tablename move TABLESPACE tablenspacename
SQL> CREATE MATERIALIZED VIEW SUBSCRIPTION_TAB
2 BUILD IMMEDIATE
3 REFRESH complete START WITH SYSDATE NEXT trunc( SYSDATE ) + 1
4 AS SELECT * FROM SUBSCRIPTION_TAB@SMGR ;
AS SELECT * FROM SUBSCRIPTION_TAB@SMGR
*
ERROR at line 4 :
ORA - 00942 : table or view does not exist

SELECT * FROM v$license; 系統達到的最大SESSION數

1.?
Physical standby on disk database structures must be identical to the primary database on a block-for-block basis, because a recovery operation applies changes block-for-block using the physical ROWID. The database schema, including indexes, must be the same, and the database cannot be opened (other than for read-only access). If opened, the physical standby database will have different ROWIDs, making continued recovery impossible.
2. create materialized view
a.log
b.dblink
c.create as select
d.refresh kind
3. veritas 備份服務器
host name +++inllhbk01
ip :10.142.4.196
更改hosts文件

4..Specifying the Commit Point Strength of a Node
COMMIT_POINT_STRENGTH = 200

Eliminating Migrated or Chained Rows in a Table
You can use the information in the CHAINED_ROWS table to reduce or eliminate migrated and chained rows in an existing table. Use the following procedure.

Use the ANALYZE statement to collect information about migrated and chained rows.
ANALYZE TABLE order_hist LIST CHAINED ROWS;

Query the output table:
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID          TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAA 04-MAR-96
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAB 04-MAR-96
SCOTT       ORDER_HIST       ... AAAAluAAHAAAAA1AAC 04-MAR-96

#########################################################################
#
#解決行遷移步驟
#
#########################################################################
The output lists all rows that are either migrated or chained.

If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows by continuing through the following steps:
Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:
CREATE TABLE int_order_hist
   AS SELECT *
      FROM order_hist
      WHERE ROWID IN
         (SELECT HEAD_ROWID
            FROM CHAINED_ROWS
            WHERE TABLE_NAME = 'ORDER_HIST');

Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist
   WHERE ROWID IN
      (SELECT HEAD_ROWID
         FROM CHAINED_ROWS
         WHERE TABLE_NAME = 'ORDER_HIST');

Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist
SELECT *
FROM int_order_hist;

Drop the intermediate table:
DROP TABLE int_order_history;

Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

Use the ANALYZE statement again, and query the output table.
Any rows that appear in the output table are chained. You can eliminate chained rows only by increasing your data block size. It might not be possible to avoid chaining in all situations. Chaining is often unavoidable with tables that have a LONG column or long CHAR or VARCHAR2 columns.

#########################################################################
#
# DELETE///\\\\\\\\TRUNCATE///\\\\\\\DROP
#
#########################################################################
DROP
dropping and re-creating a table or cluster, all associated indexes, integrity constraints, and triggers are also dropped, and all objects that depend on the dropped table or clustered table are invalidated. Also, all grants for the dropped table or clustered table are dropped.
CAN'T ROLLBACK
TRUNCATE
Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any rollback information and it commits immediately. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.
A hash cluster cannot be truncated, nor can tables within a hash or index cluster be individually truncated
CAN'T ROLLBACK
DELETE
CAN ROLLBACK

#########################################################################
#
# TRIGGER
#
#########################################################################
alter table talbe_name enable all triggers;
alter table table_name disable all triggers;
alter trigger trigger_name disable/enable;

Integrity Constraint States
Enable Novalidate

ARCHIVE_LAG_TARGET

/*1:Mins*/ sysdate + 1/(60*24)
###########################################################
通過SID查找SPID
############################################################

SELECT p.spid, s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.USER#, s.username,
         s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
         s.SCHEMA#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
         UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
         s.prev_sql_addr, s.prev_hash_value, s.module, s.module_hash,
         s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
         s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
         s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
         s.failover_type, s.failover_method, s.failed_over,
         s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
         s.current_queue_duration, s.client_identifier, p.program "OSProgram",
          p.pid
    FROM v$session s, v$process p
   WHERE (s.username = 'SCHMGR')
     AND (    (s.username IS NOT NULL)
          AND (NVL (s.osuser, 'x') <> 'SYSTEM')
          AND (s.TYPE <> 'BACKGROUND')
    AND s.schemaname='SCHMGR'
    AND S.TERMINAL='LHPC1175'
    AND S.OSUSER='Excel'
         )
     AND (s.module <> 'TOAD background query session')
     AND (p.addr(+) = s.paddr)
ORDER BY "PROGRAM", ownerid

SELECT * FROM V$PROCESS P,V$SESSION S
WHERE S.PADDR=P.ADDR
AND S.SID=25

#########################################################
#########################################################

insert /*+append */ into emp nologging select ******** 直接插入.

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

alter database rename file '))))))))" to '*********************';

alter session set nls_language=american;

% rman TARGET / CATALOG rman/cat@catdb CMDFILE b_l0.rcv LOG log.f

CONFIGURE CHANNEL 1 DEVICE TYPE sbt CONNECT = 'SYS/oracle@trgt1';
CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT = 'SYS/oracle@trgt2';
CONFIGURE CHANNEL 3 DEVICE TYPE sbt CONNECT = 'SYS/oracle@trgt3';

BACKUP DEVICE TYPE DISK COPIES 3 DATAFILE 7 FORMAT '/tmp/%U','?/oradata/%U','?/%U';

BACKUP INCREMENTAL LEVEL 2 CUMULATIVE SKIP INACCESSIBLE DATABASE;

Database Administrator Usernames

useradd [-c comment] [-d home_dir]
               [-e expire_date] [-f inactive_time]
               [-g initial_group] [-G group[,...]]
               [-m [-k skeleton_dir] | -M] [-n] [-o] [-p passwd] [-r]
               [-s shell] [-u uid] login

       useradd -D [-g default_group] [-b default_home]
               [-e default_expire_date] [-f default_inactive]
               [-s default_shell]

One step procedure (uses less disk space and is faster):

zcat lnx_920_disk1.cpio.gz | cpio -idmv
zcat lnx_920_disk2.cpio.gz | cpio -idmv
zcat lnx_920_disk3.cpio.gz | cpio -idmv
Two step procedure:

# Uncompress
gunzip lnx_920_disk1.cpio.gz lnx_920_disk2.cpio.gz lnx_920_disk3.cpio.gz Linux9i_Disk3.cpio.gz

# Unpack the downloaded files:
cpio -idmv < lnx_920_disk1.cpio
cpio -idmv < lnx_920_disk2.cpio
cpio -idmv < lnx_920_disk3.cpio

To check the memory, run:
grep MemTotal /proc/meminfo
To check the swap space, run:
cat /proc/swaps

TS_PITR_OBJECTS_TO_BE_DROPPED

SELECT OWNER, NAME, TABLESPACE_NAME, TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
FROM SYS.TS_PITR_OBJECTS_TO_BE_DROPPED
WHERE TABLESPACE_NAME IN ('SALES_1','SALES_2')
AND CREATION_TIME > TO_DATE('00-JUN-02:07:03:11','YY-MON-DD:HH24:MI:SS')
ORDER BY TABLESPACE_NAME, CREATION_TIME;

DB_NAME = prod1
CONTROL_FILES = /oracle/aux/cf1.f
LOCK_NAME_SPACE = aux
DB_FILE_NAME_CONVERT=("/oracle/dbs/","/oracle/aux/")

LOG_ARCHIVE_DEST_1 = 'LOCATION=/oracle/work/arc_dest/arc'
LOG_ARCHIVE_FORMAT = r_%t_%s.arc

run {
allocate channel ch1 type disk;
backup database
format '/data/xhlbackup/full_%t.dbf';
sql 'alter system switch logfile';
backup archivelog all
format '/data/xhlbackup/r_%s_%t.arc';
release channel ch1;
}

RESYNC CATALOG

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD ONLINE TABLESPACE IDX;' FROM DBA_INDEXES WHERE WNER='CELLRPT' AND TABLESPACE_NAME='CELLRPT';

備份日期:Thu Mar 23 16:45:53 CST 2006

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/23/2006 17:05:03
RMAN-03015: error occurred in stored script. Memory Script
ORA-19504: failed to create file "/opt/oracle/oradata/adm/system01.dbf"
ORA-27086: skgfglk: unable to lock file - already in use
Linux Error: 11: Resource temporarily unavailable
Additional information: 8

RMAN> run {
2> allocate channel ch1 type disk;
3> set until time "to_date('03/24/2006 10:57:05','MM/DD/YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> release channel ch1;
7> }

auxiliary 備份腳本
run{
allocate auxiliary channel ch1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NB_ORA_CLIENT=inllhqis)';
set until time "to_date('03/30/2006 08:00:00','MM/DD/YYYY HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO mix NOFILENAMECHECK
skip tablespace
INDX,TOOLS,USERS,HZHRM,AVSYSTEM,IQC,SCHDAT,IEDAT,OA,IMPROVEDAT,VENDDAT,EAIDAT,OQC,MISMS,MESDNDAT,TFTMPSDAT,MIS112,SECPRDDAT,PCBAEDU
LOGFILE
GROUP 1 ('/oradata/mix/redo01a.log',
'/oradata/mix/redo01b.log') size 10240k reuse,
GROUP 2 ('/oradata/mix/redo02a.log',
'/oradata/mix/redo02b.log') size 10240k reuse;
release channel ch1;
}

18:36 FULLBACKUP

SELECT 'SET newname FOR '||file_id||' to ' ||''''||file_name||''''||';'
FROM dba_data_files

18:40 EXAMPEL DEL
ABC
5 A BC
6ABC

Thu Mar 23 18:44:30 CST 2006

丟失數據文件,有創建數據文件以來所有的歸檔日誌....可用以下方法.....
alter database create datafile '/opt/oracle/oradata/dba/example02.dbf'
2 as '/opt/oracle/oradata/dba/example02.dbf' reuse;

備份設定:
Attempts to load the default media management library. The filename of the
default library is operating system specific. On UNIX, the library filename is
$ORACLE_HOME/lib/libobk.so,withtheextensionnamevaryingaccording
to platform.:.so,.sl,.a, and so forth. On Windows NT the library is named
%ORACLE_HOME%\bin\orasbt.dll.

To integrate the media manager on UNIX:
1. Ifanoldlibobk.sosymboliclinkalreadyexistsin$ORACLE_HOME/lib,then
remove itbefore installing the media manager. For example:
% rm $ORACLE_HOME/lib/libobk.so
2. After installation, check your media management vendor documentation to
determine where the media management library is installed. For example,
suppose that the library is installed as/vendor/lib/oracle_lib.so.
3. Either change the name of the installed media management library to
$ORACLE_HOME/lib/libobk.so, or created a symbolic link to the library
calledlibobk.so.Forexample,youcancreateasymboliclinktothelibraryas
follows:
% ln -s /vendor/lib/oracle_lib.so $ORACLE_HOME/lib/libobk.so
Alternatively, you can simply change the name of the library tolibobk.so.
For example:
% mv /vendor/lib/oracle_lib.so $ORACLE_HOME/lib/libobk.so

MAXPIECESIZE
CONFIGURE DEFAULT DEVICE TYPE CLEAR

CONFIGURE DEVICE TYPE sbt PARALLELISM 3;
CONFIGURE DEFAULT DEVICE TYPE to sbt;
CONFIGURE CHANNEL 1 DEVICE TYPE sbt CONNECT 'SYS/oracle@node1'
    PARMS 'ENV=(NSR_SERVER=bksvr1)'; # channel 1 is for first node
CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT 'SYS/oracle@node2'
    PARMS 'ENV=(NSR_SERVER=bksvr2)'; # channel 2 is for second node
CONFIGURE CHANNEL 3 DEVICE TYPE sbt CONNECT 'SYS/oracle@node3'
    PARMS 'ENV=(NSR_SERVER=bksvr3)'; # channel 3 is for third node

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '?/oradata/%F.bck'

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SET ARCHIVELOG DESTINATION TO

run {
ALLOCATE auxiliary CHANNEL ch1 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=ldp2-node1)';
set until time "to_date('03/20/2006 16:22:00','MM/DD/YYYY HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO LCDTEST NOFILENAMECHECK
LOGFILE
GROUP 1 ('/data/lcdtest/redo01a.log') size 10240k reuse,
GROUP 2 ('/data/lcdtest/redo02a.log') size 10240k reuse;
release channel ch1;
}

RMAN >>REAL APPLICATION CLUSTER
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE DEVICE TYPE sbt PARALLELISM 3;
CONFIGURE CHANNEL 1 DEVICE TYPE sbt CONNECT = 'SYS/oracle@node1';
CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT = 'SYS/oracle@node2';
CONFIGURE CHANNEL 3 DEVICE TYPE sbt CONNECT = 'SYS/oracle@node3';
If the instance to which one of the channels is connected does not have the database open, then the database must not be open by any instance. In other words, either all channels must be connected to open instances, or all channels must be connected to instances that are not open. For example, if the node1 instance has the database mounted while the node2 and node3 instances have the database open, then the backup fails.

29/Creating a Standby Database with Image Copies: Overview

31http://inllhdb02/rac.920/a96597/psscadtl.htm
http://inllhdb02/rac.920/a96596/oemadmin.htm
cellreport mespd cellmes note
mntpdmdb01

http://inllhdb02/server.920/a96533/optimops.htm#721
CBO Statistics in the Data Dictionary

2。通过unix 的 PID 寻找正在执行的SQL以及wait的信息

select se.username||' '||se.machine||' '||se.program as session_info,
se.sid||','||se.serial# as SID, sq.sql_text ,w.event,w.wait_time
from v$session se,v$sql sq,v$process p,v$session_wait w
where
se.sid = w.sid(+)
and se.sql_hash_value = sq.hash_value(+)
and se.paddr = p.addr
and p.spid = &unix_pid
/
INDEX 狀態分析
DBMS_STATS.gather_table_stats
(ownname => 'SchemaA',
tabname => 'tableA',
CASCADE => TRUE,
estimate_percent => NULL,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 100'
);

行鏈接處理方法
Eliminating Migrated or Chained Rows in a Table
You can use the information in the CHAINED_ROWS table to reduce or eliminate migrated and chained rows in an existing table. Use the following procedure.

Use the ANALYZE statement to collect information about migrated and chained rows.
ANALYZE TABLE order_hist LIST CHAINED ROWS;

Query the output table:
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

The output lists all rows that are either migrated or chained.

Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist
   WHERE ROWID IN
      (SELECT HEAD_ROWID
         FROM CHAINED_ROWS
         WHERE TABLE_NAME = 'ORDER_HIST');

Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist
SELECT *
FROM int_order_hist;

Drop the intermediate table:
DROP TABLE int_order_history;

Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

CBO
Features that Require the CBO
The following features require use of the CBO:

Partitioned tables and indexes
Index-organized tables
Reverse key indexes
Function-based indexes
SAMPLE clauses in a SELECT statement
Parallel query and parallel DML
Star transformations and star joins
Extensible optimizer
Query rewrite with materialized views
Enterprise Manager progress meter
Hash joins
Bitmap indexes and bitmap join indexes
Index skip scans

ORA-ERROR
Ioctl ASYNC_CONFIG error, errno = 1
/wlrpt/app/oracle/product/920/rdbms/log/g18wlrpt_ora_28068.trc
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /wlrpt/app/oracle/product/920
System name: HP-UX
Node name: g18wlpd2
Release: B.11.23
Version: U
Machine: ia64
Instance name: g18wlrpt
Redo thread mounted by this instance: 0
Oracle process number: 0

FIX
Oracle(for HP only) always uses async I/O regardless of the value of init
(disk_asynch_io).
That's why this parameter filesystemio_options=none doesn't work.

Oracle always opens /dev/async successfully only if the /dev/async HP-UX
device driver is properly configured for read and write. This is irrespective
of whether the DISK_ASYNC_IO parm is set to TRUE.

So to unactivate ASYNCH_IO with Oracle , workaround could be:

chown bin:bin /dev/async
chmod 660 /dev/async

METALINK DOC NO 302801.1

SQL執行計划
CREATE USER INLDBA
IDENTIFIED BY XHL
DEFAULTTABLESPACE TOOLS
TEMPORARY TABLESPACE TEMP;

GRANT CONNECT ,DBA TO INLDBA;

@utlxplan.sql

CREATE PUBLIC SYNONYM TOAD_PLAN_TABLE FOR INLDBA.PLAN_TABLE;
CREATE PUBLIC SYNONYM PLAN_TABLE FOR INLDBA.PLAN_TABLE;
CREATE PUBLIC SYNONYM SQLEXPERT_PLAN1 FOR INLDBA.PLAN_TABLE;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON INLDBA.PLAN_TABLE TO PUBLIC;

SESSION-LEVEL TRACING
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(191,57987,TRUE);
THE DBMS_SYSTEM PACKAGE IS CREATED WHEN THE CATPROC.SQL SCRIPT. IS RUN.
SQL> ALTER SESSION SET SQL_TRACE=TRUE;

SELECT    d.VALUE
       || '/'
       || LOWER (RTRIM (i.INSTANCE, CHR (0)))
       || '_ora_'
       || p.spid
       || '.trc' trace_file_name
FROM (SELECT p.spid
          FROM v$mystat m, v$session s, v$process p
         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
       (SELECT t.INSTANCE
          FROM v$thread t, v$parameter v
         WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
       (SELECT VALUE
          FROM v$parameter
         WHERE NAME = 'user_dump_dest') d

DBMS_STATS
ANALYZE

statpack snapshot
SELECT * FROM v$session_longops WHERE elapsed_seconds>6 ORDER BY start_time DESC

SELECT * FROM v$sqltext_with_newlines WHERE hash_value='2177760586'

KILL SESSION 查找死掉的進程。。。。
SELECT * FROM v$process WHERE addr IN(SELECT p.addr FROM v$process p WHERE pid <> 1 MINUS SELECT s.paddr FROM v$session s)

更改LINUX 啟動級別
修改/etc/inittab
init:3
Here is a short description of the different run levels that are used in Linux:
– 0 - Halt (Do not set initdefault to this or the server will immediately shut down after
finishing the boot process.)
– 1 - Single user mode
– 2 - Multi-user, without NFS (the same as 3, if you do not have networking)
– 3 - Full multi-user mode
– 4 - Unused
– 5 - X11
– 6 - Reboot (Do not set initdefault to this or the server machine will continuously reboot
at startup.)
To set the initial runlevel of a machine

Bug 4390716 - Linux: "CMCLI WARNING" messages after applying 9.2.0.6 / 7

描述：
After applying 9.2.0.6 or 9.2.0.7 Patch Set on Linux
platforms then RAC installations may start reporting
numerous errors to trace files of the form.:
CMCLI WARNING: CMInitContext: init ctx(0xae5c9a4)
CMCLI WARNING: CommonContextCleanup: closing comm port

This can lead to disk full and instance crash scenarios.

解决方法：
After installation of the Patch Set ensure that the
folowing steps are executed on ALL nodes of the RAC
cluster:
cd $ORACLE_HOME/rdbms/lib
Shut down all the instances in the OH
make -f ins_rdbms.mk rac_on ioracle

STATSPACK SNAPSHOT JOB

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => 'PERFSTAT.STATSPACK.SNAP;'
     ,next_date => TO_DATE('01-01-4000 00:00:00','dd/mm/yyyy hh24:mi:ss')
     ,INTERVAL => 'SYSDATE+1/24'
     ,no_parse => TRUE
    );
SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => TRUE);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(x));
END;
/

COMMIT;

query bad sql
/* Formatted on 2006/09/05 13:04 (Formatter Plus v4.8.6) */
SELECT   sql_text nl, 'Executions=' || executions nl,
         'Expected Response Time in Seconds = ',
         disk_reads / DECODE (executions, 0, 1, executions) / 300 "Response"
    FROM v$sql
   WHERE disk_reads / DECODE (executions, 0, 1, executions) / 300 > 10
     AND executions > 0
ORDER BY hash_value, child_number;

ORA-08120: Need to create SYS.IND_ONLINE$ table in order to (re)build index
Cause: Alter index Build/Rebuild online require existing of SYS.IND_ONLINE$ table.
Action: User/DBA needs to create sys.ind_online$ before alter the index /rdbms/admin/catcio.sql contains script. to create ind_online$.

ALTER INDEX BATCHNO_IDX_PD MONITORING USAGE;
ALTER INDEX CONTAINERNO_IDX_G MONITORING USAGE;
ALTER INDEX GR_NO_IDX_PUSHDATA MONITORING USAGE;
ALTER INDEX PK_G_SAPPUSHDATA MONITORING USAGE;
ALTER INDEX REFNO10_IDX_PD MONITORING USAGE;
ALTER INDEX SAPTRANSTYPE_IDX MONITORING USAGE;
ALTER INDEX TRTIME_INDEX_PUSH MONITORING USAGE;

備份轉移：
=>HKEY_LOCAL_MACHINE=>SOFTWARE=>VREITAS=>NETBACKUP=>CURRENTVERSION=>CONFIG=>SERVER更改为 inllhbk02 更改hosts 重起netbackup client service backupserver 更改hosts

查詢打開了多少個CURSOR

SELECT o.SID, osuser, machine, COUNT (*) num_curs
FROM v$open_cursor o, v$session s
WHERE USER_NAME NOT IN ('SYSTEM','SYS')
AND o.SID = s.SID
GROUP BY o.SID, osuser, machine
ORDER BY num_curs DESC;

查詢打開最多的CURSOR的SQL
select sid, sql_text,count(*) from v$open_cursor
group by sid, sql_text
order by 3 desc

EXPORT
export NLS_LANG=AMERICAN_AMERICA.ZHT16BIG5

exp system/password tables=('PCBASFISM4.R_WIP_LOG_T') file=R_WIP_LOG_T_01.dmp,R
_WIP_LOG_T_02.dmp,R_WIP_LOG_T_03.dmp,R_WIP_LOG_T_04.dmp,R_WIP_LOG_T_05.dmp,R_WIP
_LOG_T_06.dmp,R_WIP_LOG_T_07.dmp,R_WIP_LOG_T_08.dmp,R_WIP_LOG_T_09.dmp,R_WIP_LOG
_T_10.dmp,R_WIP_LOG_T_11.dmp,R_WIP_LOG_T_12.dmp,R_WIP_LOG_T_13.dmp,R_WIP_LOG_T_1
4.dmp,R_WIP_LOG_T_15.dmp filesize=2000m log="/data/expcba.log" buffer=52428800 i
ndexes=n GRANTS=Y ROWS=Y triggers=y FEEDBACK=10000

采樣前N條記錄查詢
SQL> select * from (
2 select * from employee
3 order by dbms_random.value )
4 where rownum <= 4;

select *from employees sample(20)

flash back query
SELECT * FROM HR.JOBS AS OF TIMESTAMP to_timestamp('20061018095600','YYYYMMDDHH24MISS');

Find which SQL statements perform. Full Table or Fast Full Index scans and
tune them to make sure these scans are necessary and not the result of a
suboptimal plan.

- Starting with Oracle9i the new view V$SQL_PLAN view can help:
    (ignore data dictionary SQL in the output of these queries)
    For Full Table scans:
         select sql_text from v$sqltext t, v$sql_plan p
         where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
           and p.options='FULL'
         order by p.hash_value, t.piece;
    For Fast Full Index scans:
         select sql_text from v$sqltext t, v$sql_plan p
         where t.hash_value=p.hash_value and p.operation='INDEX'
           and p.options='FULL SCAN'
         order by p.hash_value, t.piece;

DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system

找出表中使用了的BLOCK
SELECT COUNT (DISTINCT
         DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
         DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
    FROM xhl.com;

exec dbms_job.broken(,TRUE); commit;

CREATE TABLE TEST1 AS SELECT * FROM HR.TEST1@TTT

CREATE MATERIALIZED VIEW TEST1 ON PREBUILT TABLE WITH REDUCED PRECISION AS SELECT * FROM HR.TEST1@TTT

DROP MATERIALIZED VIEW TEST1

重復記錄刪除
delete from tbl where rowid in (select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2)

SQL查詢進度
SELECT * FROM (select
username,opname,sid,serial#,context,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete"
from v$session_longops)
WHERE "% Complete" != 100
/

SELECT * FROM (select
username,opname,sid,serial#,context,b.sql_text,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete",elapsed_seconds,sql_hash_value
from v$session_longops , v$sql b
where sql_hash_value=b.hash_value )
WHERE "% Complete" != 100

在线重定义表
exec dbms_redefinition.can_redef_table(user,'R1');
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER,'R1','RR');

select name,gets,misses*100/decode(gets,0,1,gets) misses,
spin_gets*100/decode(misses,0,1,misses) spins, immediate_gets igets
,immediate_misses*100/decode(immediate_gets,0,1,immediate_gets) imisses
from v$latch order by gets + immediate_gets

select a.name
      ,a.gets gets
      ,a.misses*100/decode(a.gets,0,1,a.gets) miss
      ,to_char(a.spin_gets*100/decode(a.misses,0,1
       ,a.misses),'990.9')||
       to_char(a.sleep6*100/decode(a.misses,0,1
       ,a.misses),'90.9') cspins
      ,to_char(a.sleep1*100/decode(a.misses,0,1
       ,a.misses),'90.9')||
       to_char(a.sleep7*100/decode(a.misses,0,1
       ,a.misses),'90.9') csleep1
      ,to_char(a.sleep2*100/decode(a.misses,0,1
       ,a.misses),'90.9')||
       to_char(a.sleep8*100/decode(a.misses,0,1
       ,a.misses),'90.9') csleep2
      ,to_char(a.sleep3*100/decode(a.misses,0,1
       ,a.misses),'90.9')||
       to_char(a.sleep9*100/decode(a.misses,0,1
       ,a.misses),'90.9') csleep3
      ,to_char(a.sleep4*100/decode(a.misses,0,1
       ,a.misses),'90.9')||
       to_char(a.sleep10*100/decode(a.misses,0,1
       ,a.misses),'90.9') csleep4
      ,to_char(a.sleep5*100/decode(a.misses,0,1
       ,a.misses),'90.9')||
       to_char(a.sleep11*100/decode(a.misses,0,1
       ,a.misses),'90.9') csleep5
from v$latch a
where a.misses <> 0
order by 2 desc

通過ORACLE 視圖查詢OS BLOCK SIZE
select lbsize from x$kccle;

查詢一個進程的所有等待事件
SELECT   a.event, a.total_waits, a.time_waited, a.average_wait,
         DECODE (a.time_waited, 0, 1,a.time_waited) *100/ s.sum_time as "pct"
    FROM v$session_event a , (SELECT    SUM (time_waited) sum_time
                               FROM v$session_event
                              WHERE SID = '&sid'
                          ) s
   WHERE SID = '&sid'
ORDER BY 5 desc;

查詢系統主要等待事件
SELECT   se.SID, se.serial#, pr.spid, se.username, se.status, se.terminal,
         se.program, se.module, se.sql_address, st.event, st.p1text,
         si.physical_reads, si.block_changes
    FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
   WHERE st.SID = se.SID
     AND st.SID = si.SID
     AND se.paddr = pr.addr
     AND se.SID > 10
     AND st.wait_time = 0
     AND st.event NOT LIKE '%SQL%'
ORDER BY si.physical_reads desc

SELECT   se.SID, se.serial#, pr.spid, se.username, se.status, se.terminal,
         se.program,se.sql_hash_value, st.event, st.p1text,
         si.physical_reads, si.block_changes
    FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
   WHERE st.SID = se.SID
     AND st.SID = si.SID
     AND se.paddr = pr.addr
     AND st.wait_time = 0
     AND st.event NOT LIKE '%SQL%'
AND se.program NOT LIKE '%(%)%'
ORDER BY si.physical_reads desc

SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;

捕捉語句
SELECT   sql_text
    FROM v$sqltext a
   WHERE a.hash_value = (SELECT sql_hash_value
                           FROM v$session b
                          WHERE b.SID = '&sid')
ORDER BY piece ASC

ORA-12838: cannot read/modify an object after modifying it in parallel

查詢從DB STARTUP到當前系統的TRANSACTIONS 量
select value/up_days/(24*60*60) as tx_per_seconde
    from (select sum(value) as value from v$sysstat
          where name in ('user commits','user rollbacks','user calls')),
         (select sysdate-startup_time as up_days from v$instance);

查詢誰鎖住誰
select (select username from v$session where sid=a.sid) blocker,
    a.sid,
    (select username from v$session where sid=b.sid) blockee, b.sid
    from (select sid, id1,id2 from v$lock where block = 1) a,
         (select sid, id1,id2 from v$lock where request > 0 ) b
    where a.id1 = b.id1 and a.id2 = b.id2;

查看正在运行的过程?
col name format a56
select name
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';

TRACE用戶
SQL> exec dbms_system.set_sql_trace_in_session(8,1877,TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(8,1877,FALSE);

从Oracle8i开始，Oracle在企业版中引入了Fast-Start Fault Recovery选项。
该选项包含三个主要增强:

1.Fast-Start CheckpoRinting.
2.Fast-Start On-Demand Rollback.
3.Fast-Start Parallel Rollback.

查詢沒有建INDEX的FK TABLE

SELECT OWNER, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM DBA_CONS_COLUMNS c
WHERE position=1 AND
   (OWNER, TABLE_NAME, COLUMN_NAME) IN
   (SELECT c.OWNER, c.TABLE_NAME,cc.COLUMN_NAME
      FROM DBA_CONSTRAINTS c, DBA_CONS_COLUMNS cc
     WHERE c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
       AND c.TABLE_NAME = cc.TABLE_NAME
       AND c.OWNER = cc.OWNER
       AND c.CONSTRAINT_TYPE = 'R'
       AND cc.POSITION       = 1
       AND c.OWNER LIKE UPPER('&vOwner')
       AND c.TABLE_NAME LIKE UPPER('&vTable')
     MINUS
    SELECT table_owner, table_name, column_name
      FROM DBA_IND_COLUMNS
     WHERE COLUMN_POSITION = 1
       AND TABLE_OWNER LIKE UPPER('&vOwner')
       AND TABLE_NAME LIKE UPPER('&vTable')
) ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME;

perl -MDBI -le 'print "@{[DBI->data_sources(grep /oracle/i, DBI->available_drivers)]}"'

##########################################
#partition table
##########################################
modrpt:
CREATE TABLESPACE PTS_LHSUM07Q3 DATAFILE
'/data/database/e4mrpt/PTS_LHSUM07Q301.dbf' SIZE 2000M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 50M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE PTS_LHSUM07Q3_IDX DATAFILE
'/idx/database/e4mrpt/PTS_LHSUM07Q3_IDX01.dbf' SIZE 3000M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 20M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO;

ADD PARTITION TABLE:

ALTER INDEX MODRPTDATA.IDX_LHSUMBASE_EVENTTIME MODIFY DEFAULT ATTRIBUTES
TABLESPACE PTS_LHSUM07Q3_IDX;

ALTER INDEX MODRPTDATA.IDX_LHSUMBASE_SUBLOTNAME MODIFY DEFAULT ATTRIBUTES
TABLESPACE PTS_LHSUM07Q3_IDX;

ALTER INDEX MODRPTDATA.PK_LHSUMBASE MODIFY DEFAULT ATTRIBUTES
TABLESPACE PTS_LHSUM07Q3_IDX;

ALTER TABLE lothistorysumbase
ADD PARTITION LHSUM070409 VALUES LESS THAN (TO_DATE(' 2007-04-09 07:30:00', 'YYYY-MM-DD HH24:MI:SS') )
TABLESPACE PTS_LHSUM07Q2;

REMOVE JOB / BROKEN JOB
exec sys.dbms_job.remove('1');
commit;

http://www.nextre.it/oracledocs/10gR2_CRS_SLES9x86.html

http://www.puschitz.com/InstallingOracle10gRAC.shtml

Please check the error as soon as possible on the server e3wms01 for further Information..

Fri Apr 27 17:30:11 2007
     Errors in file /opt/oracle/admin/e3wms/udump/e3wms_ora_3169.trc:
     ORA-00600: internal error code, arguments: [qerrmObnd1], [932], [ORA-00932: inconsistent datatypes: expected got
Fri Apr 27 17:30:46 2007
     Errors in file /opt/oracle/admin/e3wms/udump/e3wms_ora_3169.trc:
     ORA-07445: exception encountered: core dump [qerrmOcl()+151] [SIGSEGV] [Address not mapped to object] [0x000000028] [] []
     ORA-00600: internal error code, arguments: [qerrmObnd1], [932], [ORA-00932: inconsistent datatypes: expected got

alter system flush share_pool;

10G FLASHBACK
SHOW RECYCLEBIN;
FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
PURGE RECYCLEBIN;清空回收站
DROP TABLE TABLENAME PURGE;刪除表，不讓其進入RECYCLEBIN ;
PURGE TABLE TABLENAME,將TABLE 從RECYCLEBIN中清除;
PURGE TABLESPACE USERS;
PURGE TABLESPACE USERS USER SCOTT;
PURGE RECYCLEBIN; 一般用戶清除自己在回收站所有的OBJECTS
PURGE DBA_RECYCLEBIN;

    'INNOLUX\F3109677',
         'INNOLUX\F3111272',
         'INNOLUX\F3106677',
         'INNOLUX\F3111830',
         'INNOLUX\F3111831',
         'INNOLUX\F3112199',
         'INNOLUX\F3111975',
         'INNOLUX\F3107144',
      'INNOLUX\F3111806',
         'INNOLUX\F3106677',
         'INNOLUX\F3106678',
         'INNOLUX\F3113291',
         'INNOLUX\F3101529',
         'INNOLUX\F3111931

logminer

Select
SCN,TIMESTAMP,COMMIT_TIMESTAMP,SEG_OWNER,SEG_NAME,SESSION#,SERIAL#,USERNAME,OPERATION,
session# session_num,
ROLLBACK,sql_redo
From V$LOGMNR_CONTENTS
Order by 1

Install the DBMS_LOGMNR package. John installs the package by logging on to SQL*Plus as SYS and executing the $ORACLE_HOME/rdbms/admin/dbmslm.sql file.

Grant the role. John then grants the user who will do the mining—in this case, himself—the appropriate role to execute this package:

GRANT EXECUTE_CATALOG_ROLE TO JOHN;

Create the synonym. John creates a public synonym:

CREATE PUBLIC SYNONYM DBMS_LOGMNR FOR SYS.DBMS_LOGMNR;

These first three steps are required just once.

Specify the scope of the mining. Because the error may have happened just recently, it's possible that the change is still within the online redo log files. So, for the first pass, John decides to mine only the online redo log files. He identifies the files by running the following query:

SELECT distinct member LOGFILENAME FROM V$LOGFILE;

LOGFILENAME
________________________
/dev/vgredo01/rlog1a
/dev/vgredo01/rlog1b

These are the two redo log files for the database. John limits the scope of mining to only these files, by executing the following:

BEGIN
   DBMS_LOGMNR.ADD_LOGFILE
      ('/dev/vgredo01/rlog1a');
   DBMS_LOGMNR.ADD_LOGFILE
      ('/dev/vgredo01/rlog1b');
END;

Start the LogMiner session and specify a dictionary. John executes the following SQL to start the LogMiner session:

BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;

Using the OPTIONS parameter, he also specifies that Oracle Database read the dictionary information to convert the object names from the online catalog while starting LogMiner.

As previously mentioned, redo log entries are not written in clear text. LogMiner provides human-readable reporting from the redo log files, but some reported values may not look like the original objects. For example, the owner, table, and column names are not recorded in the online redo log in the same format users input; they are instead converted to hexadecimal values. For instance, the ACCOUNTS table may be recorded as OBJ#45C1. To make the redo log data more readable, John can ask LogMiner to convert these values to more recognizable ones by translating them from a dictionary.

Check contents. Starting the LogMiner session populates a view named V$LOGMNR_CONTENTS, which John can then query to find out what happened to Ellen Smith's account. The query looks for the users and times of any updates against the ACCOUNTS table. The query against V$LOGMNR_CONTENTS and

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/308563/viewspace-172026/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/308563/viewspace-172026/