調整redo log file 的一個script

在8i（8.1.7.4）上用到增加一個redo log file group 成員的例子

 

db03-oracle $cat alterredo.sql
alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 1;
alter database add logfile group 1 ('/u01/oradata/databasesid/redo1.log','/u04/oradata/databasesid/redo1.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 2;
alter database add logfile group 2 ('/u01/oradata/databasesid/redo2.log','/u04/oradata/databasesid/redo2.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 3;
alter database add logfile group 3 ('/u01/oradata/databasesid/redo3.log','/u04/oradata/databasesid/redo3.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 4;
alter database add logfile group 4 ('/u01/oradata/databasesid/redo4.log','/u04/oradata/databasesid/redo4.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 5;
alter database add logfile group 5 ('/u01/oradata/databasesid/redo5.log','/u04/oradata/databasesid/redo5.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 6;
alter database add logfile group 6 ('/u01/oradata/databasesid/redo6.log','/u04/oradata/databasesid/redo6.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 7;
alter database add logfile group 7 ('/u01/oradata/databasesid/redo7.log','/u04/oradata/databasesid/redo7.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 8;
alter database add logfile group 8 ('/u01/oradata/databasesid/redo8.log','/u04/oradata/databasesid/redo8.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 9;
alter database add logfile group 9 ('/u01/oradata/databasesid/redo9.log','/u04/oradata/databasesid/redo9.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 10;
alter database add logfile group 10 ('/u01/oradata/databasesid/redo10.log','/u04/oradata/databasesid/redo10.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 11;
alter database add logfile group 11 ('/u01/oradata/databasesid/redo11.log','/u04/oradata/databasesid/redo11.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 12;
alter database add logfile group 12 ('/u01/oradata/databasesid/redo12.log','/u04/oradata/databasesid/redo12.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 13;
alter database add logfile group 13 ('/u01/oradata/databasesid/redo13.log','/u04/oradata/databasesid/redo13.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 14;
alter database add logfile group 14 ('/u01/oradata/databasesid/redo14.log','/u04/oradata/databasesid/redo14.log') size 6291456 reuse;

alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 15;
alter database add logfile group 15 ('/u01/oradata/databasesid/redo15.log','/u04/oradata/databasesid/redo15.log') size 6291456 reuse;

alter system switch logfile;
db03-oracle $

記錄一下。

遭遇 ORA-03232: unable to allocate an extent of 40 blocks from tablespace 5

對Oracle database 進行 exp imp操作的時候，跑到最後發現報錯了

…

MP-00017: following statement failed with ORACLE error 604:
"ALTER TABLE "abc_USER_AUTOPAY_PLAN" ENABLE CONSTRAINT "FK_AUTOP_PLAN_abc""
IMP-00003: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-03232: unable to allocate an extent of 40 blocks from tablespace 5

…

解決步驟：

1 tablespace 5是什麼

SQL> select * from v$tablespace;
       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 TOOLS
         2 OEM_REPOSITORY
         3 RBS
         4 TEMP
        5 APP_TEMP
         6 USERS
         7 ABC_DATA
         8 INDX
         9 ABC_INDX
        10 DRSYS
11 rows selected.

增大 tablespace size 無效

alter database datafile '/u04/oradata/crprod/apptemp01.dbf' resize 2000m;

google一下發現可能是 dba_tablespace的 next_extent的問題

SQL> select next_extent from dba_tablespaces where tablespace_name = 'APP_TEMP';
NEXT_EXTENT
-----------
      65536

 

SQL> alter tablespace app_temp default storage (next 1m);
Tablespace altered.
SQL> select next_extent from dba_tablespaces where tablespace_name = 'APP_TEMP';
NEXT_EXTENT
-----------
    1048576

手工運行-成功：

SQL> ALTER TABLE ABC_USER_AUTOPAY_PLAN ENABLE CONSTRAINT FK_AUTOP_PLAN_UPROF_ABC;
Table altered.

記錄一下備忘！

ORACLE 11g RAC ons offline 處理

同事報生產環境的ons offline.但是神奇的是rac的node並沒有影響，記錄一下解決過程

[root@oracle02 trace]# which crs_start
/u01/app/11.1.0/crs/bin/crs_start
[root@oracle02 trace]# which crs_stop
/u01/app/11.1.0/crs/bin/crs_stop
這個的ora.oracle02.ons 可以從ora....e02.ons 和 host
[root@oracle02 trace]# crs_stop ora.oracle02.ons （Name 和Host)這兩個地方找到
Target set to OFFLINE for `ora.oracle02.ons`
[root@oracle02 trace]# crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....b5.inst application    ONLINE    OFFLINE
ora.crpb5.db   application    ONLINE    OFFLINE
ora....d1.inst application    ONLINE    ONLINE    oracle01
ora....d2.inst application    ONLINE    ONLINE    oracle02
ora.crprod.db  application    ONLINE    ONLINE    oracle01
ora....SM1.asm application    ONLINE    ONLINE    oracle01
ora....01.lsnr application    ONLINE    ONLINE    oracle01
ora....e01.gsd application    ONLINE    ONLINE    oracle01
ora....e01.ons application    ONLINE    ONLINE    oracle01
ora....e01.vip application    ONLINE    ONLINE    oracle01
ora....SM2.asm application    ONLINE    ONLINE    oracle02
ora....02.lsnr application    ONLINE    ONLINE    oracle02
ora....e02.gsd application    ONLINE    ONLINE    oracle02
ora....e02.ons application    OFFLINE   OFFLINE
ora....e02.vip application    ONLINE    ONLINE    oracle02
[root@oracle02 trace]# crs_start ora.oracle02.ons
Attempting to start `ora.oracle02.ons` on member `oracle02`
Start of `ora.oracle02.ons` on member `oracle02` succeeded.
[root@oracle02 trace]# crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....b5.inst application    ONLINE    OFFLINE
ora.crpb5.db   application    ONLINE    OFFLINE
ora....d1.inst application    ONLINE    ONLINE    oracle01
ora....d2.inst application    ONLINE    ONLINE    oracle02
ora.crprod.db  application    ONLINE    ONLINE    oracle01
ora....SM1.asm application    ONLINE    ONLINE    oracle01
ora....01.lsnr application    ONLINE    ONLINE    oracle01
ora....e01.gsd application    ONLINE    ONLINE    oracle01
ora....e01.ons application    ONLINE    ONLINE    oracle01
ora....e01.vip application    ONLINE    ONLINE    oracle01
ora....SM2.asm application    ONLINE    ONLINE    oracle02
ora....02.lsnr application    ONLINE    ONLINE    oracle02
ora....e02.gsd application    ONLINE    ONLINE    oracle02
ora....e02.ons application    ONLINE    ONLINE    oracle02
ora....e02.vip application    ONLINE    ONLINE    oracle02
[root@oracle02 trace]#
之前嘗試 onsctl stop |start 沒有什麼效果，原因待查
[root@oracle02 trace]# onsctl help
usage: /u01/app/oracle/product/11.1.0/bin/onsctl start|stop|ping|reconfig|debug
start                            - Start opmn only.
stop                             - Stop ons daemon
ping                             - Test to see if ons daemon is running
debug                            - Display debug information for the ons daemon
reconfig                         - Reload the ons configuration
help                             - Print a short syntax description (this).
detailed                         - Print a verbose syntax description.
[root@oracle02 trace]#
ref:  http://blog.roodo.com/mywork/archives/11858971.html

關於ons,好像是跟jdbc連接有關係，我們application使用的是dot net.所以沒有看到影響。得空研究一下。

如何查找redo file?

備忘一下

SQL> select group#, members, status from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          2 INACTIVE
         2          2 CURRENT
         3          2 INACTIVE
         4          2 CURRENT

 

SQL> desc v$logfile
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GROUP#                                             NUMBER
STATUS                                             VARCHAR2(7)
TYPE                                               VARCHAR2(7)
MEMBER                                             VARCHAR2(513)
IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> col member for a30
SQL> select * from v$logfile;

 

SQL> set linesize 300
SQL> /

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         1         ONLINE  +CRPROD_DATA/crprod/onlinelog/ NO
                           group_1.257.686193451

         1         ONLINE  +FLASH_RECOVERY_AREA/crprod/on YES
                           linelog/group_1.257.686193459

         2         ONLINE  +CRPROD_DATA/crprod/onlinelog/ NO
                           group_2.258.686193481

         2         ONLINE  +FLASH_RECOVERY_AREA/crprod/on YES
                           linelog/group_2.258.686193491

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---

         3         ONLINE  +CRPROD_DATA/crprod/onlinelog/ NO
                           group_3.265.686198773

         3         ONLINE  +FLASH_RECOVERY_AREA/crprod/on YES
                           linelog/group_3.259.686198787

         4         ONLINE  +CRPROD_DATA/crprod/onlinelog/ NO
                           group_4.266.686198801

         4         ONLINE  +FLASH_RECOVERY_AREA/crprod/on YES

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
                           linelog/group_4.260.686198809

8 rows selected.

How to Delete a node from RAC test

在測試環境中做一個一個RAC delete node的實驗記錄一下

環境是vmware

OS: OEL 5.2

DB: oracle 11gR1

 

http://docs.google.com/Doc?docid=0AbicX7azhQtmZGs3NzQ2Ml85NjBmbWMyZjRjZw&hl=en

BBED complie

發現一個有用的工具BBED,處理壞塊使用的， 根據網上提供的方法，自己做了一個實驗：

我的環境是openSolaris 其他的*nix也是類似

1，源文件在哪裡？

cd $ORACLE_HOME

 

oracle@opensolaris:/opt/oracle/product/10.2.0/db_1$ find . -name ins_rdbms.mk
./rdbms/lib/ins_rdbms.mk

2,編譯：

oracle@opensolaris:/opt/oracle/product/10.2.0/db_1/rdbms/lib$ make -f ins_rdbms.mk /opt/oracle/product/10.2.0/db_1/rdbms/lib/bbed

Linking BBED utility (bbed)
rm -f /opt/oracle/product/10.2.0/db_1/rdbms/lib/bbed
/usr/ccs/bin/ld -64 -o /opt/oracle/product/10.2.0/db_1/rdbms/lib/bbed -L/opt/oracle/product/10.2.0/db_1/rdbms/lib/ -L/opt/oracle/product/10.2.0/db_1/lib/ -dy /opt/oracle/product/10.2.0/db_1/lib/prod/lib/amd64/crti.o /opt/oracle/product/10.2.0/db_1/lib/prod/lib/amd64/crt1x.o /opt/oracle/product/10.2.0/db_1/lib/s0main.o /opt/oracle/product/10.2.0/db_1/rdbms/lib/ssbbded.o /opt/oracle/product/10.2.0/db_1/rdbms/lib/sbbdpt.o `cat /opt/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 /opt/oracle/product/10.2.0/db_1/rdbms/lib/defopt.o  -ldbtools10 -lclntsh  `cat /opt/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /opt/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10  -lvsn10  -lcommon10 -lgeneric10 -lmm -lsnls10 -lnls10  -lcore10 -lsnls10  -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10  -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat /opt/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat /opt/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10  -lvsn10  -lcommon10 -lgeneric10   -lsnls10 -lnls10  -lcore10 -lsnls10  -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10  -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10  -lclient10 -lnnetd10  -lvsn10  -lcommon10 -lgeneric10 -lsnls10 -lnls10  -lcore10 -lsnls10  -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10  -lcore10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10   `cat /opt/oracle/product/10.2.0/db_1/lib/sysliblist` -R /opt/SUNWcluster/lib:/opt/oracle/product/10.2.0/db_1/lib:/opt/ORCLcluster/lib/  -Y P,:/opt/SUNWcluster/lib:/opt/ORCLcluster/lib/:/usr/ccs/lib/amd64:/usr/lib/amd64  -Qy -lc  -lrt  -laio -lposix4 -lkstat -lm  /opt/oracle/product/10.2.0/db_1/lib/prod/lib/amd64/crtn.o

驗證一下：

oracle@opensolaris:/opt/oracle/product/10.2.0/db_1/rdbms/lib$ ls -al bbed
-rwxr-xr-x 1 oracle oinstall 862016 2010-03-03 10:06 bbed

執行

oracle@opensolaris:/opt/oracle/product/10.2.0/db_1/rdbms/lib$ ./bbed
Password:（blockedit 為default password)

BBED: Release 2.0.0.0.0 - Limited Production on Wed Mar 3 10:07:15 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED>

這個工具是內部人員使用的哦

BBED> help all
SET DBA [ dba | file#, block# ]
SET FILENAME 'filename'
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] 'filename'
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE  [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ <SET parameter> | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
</Nuf>:
N - a number which specifies a repeat count.
u - a letter which specifies a unit size:
  b - b1, ub1 (byte)
  h - b2, ub2 (half-word)
  w - b4, ub4(word)
  r - Oracle table/index row
f - a letter which specifies a display format:
  x - hexadecimal
  d - decimal
  u - unsigned decimal
  o - octal
  c - character (native)
  n - Oracle number
  t - Oracle date
  i - Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
      [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ <bbed command> | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]

BBED>

記錄一下，備忘，繼續學習！

ref:

http://www.databasejournal.com/features/oracle/article.php/3835546/Installing-Oracle-Block-Browser-and-Editor-tool-bbed.htm

http://www.eygle.com/archives/2005/09/how_to_compile.html

oracle exp parfile mode

發現一個oracle exp的方法不錯，就是用file方式。記錄一下備查

 

oracle@opensolaris:~$ cat experfstat.par
userid=perfstat/perfstat
owner=perfstat
DIRECT=y
CONSISTENT=y
file=/export/home/oracle/perfstat.dmp
grants=y
indexes=y
oracle@opensolaris:~$

oracle@opensolaris:~$ exp parfile=experfstat.par
Export: Release 10.2.0.1.0 - Production on Thu Feb 25 17:55:39 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user PERFSTAT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user PERFSTAT
About to export PERFSTAT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export PERFSTAT's tables via Direct Path ...
. . exporting table         STATS$BG_EVENT_SUMMARY        308 rows exported
. . exporting table          STATS$BUFFERED_QUEUES          0 rows exported
. . exporting table     STATS$BUFFERED_SUBSCRIBERS          0 rows exported
. . exporting table   STATS$BUFFER_POOL_STATISTICS         14 rows exported
. . exporting table          STATS$CR_BLOCK_SERVER          0 rows exported
. . exporting table     STATS$CURRENT_BLOCK_SERVER          0 rows exported
. . exporting table        STATS$DATABASE_INSTANCE          1 rows exported
. . exporting table          STATS$DB_CACHE_ADVICE        294 rows exported
. . exporting table                 STATS$DLM_MISC          0 rows exported
. . exporting table   STATS$DYNAMIC_REMASTER_STATS          0 rows exported
. . exporting table       STATS$ENQUEUE_STATISTICS        826 rows exported
. . exporting table          STATS$EVENT_HISTOGRAM       6692 rows exported
. . exporting table               STATS$FILESTATXS         70 rows exported
. . exporting table           STATS$FILE_HISTOGRAM        350 rows exported
. . exporting table               STATS$IDLE_EVENT         70 rows exported
. . exporting table  STATS$INSTANCE_CACHE_TRANSFER          0 rows exported
. . exporting table        STATS$INSTANCE_RECOVERY         14 rows exported
. . exporting table         STATS$JAVA_POOL_ADVICE         28 rows exported
. . exporting table                    STATS$LATCH       5348 rows exported
. . exporting table           STATS$LATCH_CHILDREN          0 rows exported
. . exporting table     STATS$LATCH_MISSES_SUMMARY         14 rows exported
. . exporting table             STATS$LATCH_PARENT          0 rows exported
. . exporting table        STATS$LEVEL_DESCRIPTION          5 rows exported
. . exporting table             STATS$LIBRARYCACHE        154 rows exported
. . exporting table              STATS$MUTEX_SLEEP          0 rows exported
. . exporting table                   STATS$OSSTAT        238 rows exported
. . exporting table               STATS$OSSTATNAME         17 rows exported
. . exporting table                STATS$PARAMETER       3696 rows exported
. . exporting table                  STATS$PGASTAT        196 rows exported
. . exporting table        STATS$PGA_TARGET_ADVICE        196 rows exported
. . exporting table    STATS$PROCESS_MEMORY_ROLLUP        376 rows exported
. . exporting table           STATS$PROCESS_ROLLUP        111 rows exported
. . exporting table     STATS$PROPAGATION_RECEIVER          0 rows exported
. . exporting table       STATS$PROPAGATION_SENDER          0 rows exported
. . exporting table           STATS$RESOURCE_LIMIT         70 rows exported
. . exporting table                 STATS$ROLLSTAT        154 rows exported
. . exporting table         STATS$ROWCACHE_SUMMARY        532 rows exported
. . exporting table                 STATS$RULE_SET         14 rows exported
. . exporting table                 STATS$SEG_STAT          0 rows exported
. . exporting table             STATS$SEG_STAT_OBJ          4 rows exported
. . exporting table            STATS$SESSION_EVENT          0 rows exported
. . exporting table                  STATS$SESSTAT          0 rows exported
. . exporting table          STATS$SESS_TIME_MODEL          0 rows exported
. . exporting table                      STATS$SGA         56 rows exported
. . exporting table                  STATS$SGASTAT        318 rows exported
. . exporting table        STATS$SGA_TARGET_ADVICE        112 rows exported
. . exporting table       STATS$SHARED_POOL_ADVICE        196 rows exported
. . exporting table                 STATS$SNAPSHOT         14 rows exported
. . exporting table                  STATS$SQLTEXT        362 rows exported
. . exporting table                 STATS$SQL_PLAN        330 rows exported
. . exporting table           STATS$SQL_PLAN_USAGE          0 rows exported
. . exporting table           STATS$SQL_STATISTICS         14 rows exported
. . exporting table              STATS$SQL_SUMMARY        885 rows exported
. . exporting table   STATS$SQL_WORKAREA_HISTOGRAM         67 rows exported
. . exporting table      STATS$STATSPACK_PARAMETER          1 rows exported
. . exporting table        STATS$STREAMS_APPLY_SUM          0 rows exported
. . exporting table          STATS$STREAMS_CAPTURE          0 rows exported
. . exporting table      STATS$STREAMS_POOL_ADVICE          0 rows exported
. . exporting table                  STATS$SYSSTAT       5180 rows exported
. . exporting table             STATS$SYSTEM_EVENT        798 rows exported
. . exporting table           STATS$SYS_TIME_MODEL        266 rows exported
. . exporting table               STATS$TEMPSTATXS         14 rows exported
. . exporting table           STATS$TEMP_HISTOGRAM         14 rows exported
. . exporting table                   STATS$THREAD         14 rows exported
. . exporting table      STATS$TIME_MODEL_STATNAME         19 rows exported
. . exporting table                 STATS$UNDOSTAT          8 rows exported
. . exporting table                 STATS$WAITSTAT        252 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
oracle@opensolaris:~$

dbms_job實驗

做statspack實驗的時候發現一個問題，就是設定dbms_job後，不自動進行刷新，記錄一下，備忘

在oracle的$ORACLE_HOME/rdbms/admin下有一個腳本spauto.sql看名字就知道是自動做statspack的。其中有一段腳本可以直接來用

--
--  Schedule a snapshot to be run on this instance every hour, on the hour
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;
/

創建一個user job

SQL>
SQL>
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);tno);
  commit;
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL>

SQL> select job, last_date, this_date, next_date from user_jobs;
       JOB LAST_DATE           THIS_DATE           NEXT_DATE
---------- ------------------- ------------------- -------------------
        21                                         2010-02-25 16:00:00
1 row selected.
SQL>

因為是實驗所以將間隔設定的為1min一次

SQL> exec dbms_job.interval(21, 'sysdate+1/(24*60)');
PL/SQL procedure successfully completed.
SQL>

SQL> commit;
Commit complete.

SQL> col interval for a30
SQL> select job, last_date, this_date, next_date , interval from user_jobs;
       JOB LAST_DATE           THIS_DATE           NEXT_DATE           INTERVAL
---------- ------------------- ------------------- ------------------- ------------------------------
        21                                         2010-02-25 16:00:00 sysdate+1/(24*60)
1 row selected.
SQL>

可以看到已經設置成功了。

但是等了1min發現沒有新的snap記錄

SQL> select count(*) from stats$snapshot;
  COUNT(*)
----------
         3
1 row selected.

感覺應該是next_date這個字段有影響

執行

SQL> exec dbms_job.next_date(21, to_date('20100225 15:45:01', 'yyyymmdd hh24:mi:ss'));
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

 

SQL> select sysdate from dual;
SYSDATE
-------------------
2010-02-25 15:45:10
1 row selected.

 

SQL> select job, last_date, this_date, next_date , interval from user_jobs;
       JOB LAST_DATE           THIS_DATE           NEXT_DATE           INTERVAL
---------- ------------------- ------------------- ------------------- ------------------------------
        21 2010-02-25 15:45:05                     2010-02-25 15:46:05 sysdate+1/(24*60)
1 row selected.

注意已經發生變化了

SQL> select count(*) from stats$snapshot;
  COUNT(*)
----------
        4
1 row selected.

等1min左右

SQL> select count(*) from stats$snapshot;
  COUNT(*)
----------
        5
1 row selected.

記錄一下，備忘。

Oracle scn相關

如何查看Oracle當前的SCN呢？

SQL> select current_scn,scn_to_timestamp(current_scn) ,timestamp_to_scn(sysdate)
from v$database  ;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                      T                                                                                                                               IMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------                                                                                                                               ------- -------------------------
    4170959 03-MAR-10 09.30.34.000000000 AM                                                                                                                                                                     4170958

SQL> l
  1  select current_scn,scn_to_timestamp(current_scn) ,timestamp_to_scn(sysdate)
  2* from v$database
SQL> /

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                               TIMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------------- -------------------------
    4170969 03-MAR-10 09.31.01.000000000 AM                                                               4170968

SQL> /

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                               TIMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------------- -------------------------
    4170983 03-MAR-10 09.31.22.000000000 AM                                                               4170982

SQL> /

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                               TIMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------------- -------------------------
    4170986 03-MAR-10 09.31.28.000000000 AM                                                               4170985

SQL> /

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                               TIMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------------- -------------------------
    4170988 03-MAR-10 09.31.31.000000000 AM                                                               4170987

SQL> /

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                               TIMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------------- -------------------------
    4170991 03-MAR-10 09.31.37.000000000 AM                                                               4170990

 

SQL> /

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                               TIMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------------- -------------------------
    4171182 03-MAR-10 09.34.25.000000000 AM                                                               4171177

SQL> /

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                               TIMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------------- -------------------------
   4171184 03-MAR-10 09.34.31.000000000 AM                                                               4171184

SQL> /

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)                                               TIMESTAMP_TO_SCN(SYSDATE)
----------- --------------------------------------------------------------------------- -------------------------
    4171187 03-MAR-10 09.34.34.000000000 AM                                                               4171186

current_scn與 convert以後的sysdate，不一定對應的。

繼續研究

Oracle archive log mode 相關

發現自己搭建的RAC環境（vmware +OEL linux 5 + oracle 11g),總是包空間不夠，想了一下，應該是archive log mode 因為不管它了，瘋漲log了

如何發現是archive log mode呢？

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

archive log 放在那個地方呢？

show parameter archive

SQL> show parameter archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
standby_archive_dest                 string      ?/dbs/arch
SQL>

沒有顯示出來，如何解決呢？

用v$archive_dest這個view

SQL> desc v$archive_dest;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DEST_ID                                            NUMBER
DEST_NAME                                          VARCHAR2(256)
STATUS                                             VARCHAR2(9)
BINDING                                            VARCHAR2(9)
NAME_SPACE                                         VARCHAR2(7)
TARGET                                             VARCHAR2(7)
ARCHIVER                                           VARCHAR2(10)
SCHEDULE                                           VARCHAR2(8)
DESTINATION                                        VARCHAR2(256)
LOG_SEQUENCE                                       NUMBER
REOPEN_SECS                                        NUMBER
DELAY_MINS                                         NUMBER
MAX_CONNECTIONS                                    NUMBER
NET_TIMEOUT                                        NUMBER
PROCESS                                            VARCHAR2(10)
REGISTER                                           VARCHAR2(3)
FAIL_DATE                                          DATE
FAIL_SEQUENCE                                      NUMBER
FAIL_BLOCK                                         NUMBER
FAILURE_COUNT                                      NUMBER
MAX_FAILURE                                        NUMBER
ERROR                                              VARCHAR2(256)
ALTERNATE                                          VARCHAR2(256)
DEPENDENCY                                         VARCHAR2(256)
REMOTE_TEMPLATE                                    VARCHAR2(256)
QUOTA_SIZE                                         NUMBER
QUOTA_USED                                         NUMBER
MOUNTID                                            NUMBER
TRANSMIT_MODE                                      VARCHAR2(12)
ASYNC_BLOCKS                                       NUMBER
AFFIRM                                             VARCHAR2(3)
TYPE                                               VARCHAR2(7)
VALID_NOW                                          VARCHAR2(16)
VALID_TYPE                                         VARCHAR2(15)
VALID_ROLE                                         VARCHAR2(12)
DB_UNIQUE_NAME                                     VARCHAR2(30)
VERIFY                                             VARCHAR2(3)
COMPRESSION                                        VARCHAR2(7)

 

SQL> col dest_name for a50
SQL> col destination for a50
SQL> select dest_name, status, destination from v$archive_dest;

DEST_NAME                                          STATUS    DESTINATION
-------------------------------------------------- --------- --------------------------------------------------
LOG_ARCHIVE_DEST_1                                 VALID     /u01/app/oracle/product/11.1.0/db_1/dbs/arch
LOG_ARCHIVE_DEST_2                                 INACTIVE
LOG_ARCHIVE_DEST_3                                 INACTIVE
LOG_ARCHIVE_DEST_4                                 INACTIVE
LOG_ARCHIVE_DEST_5                                 INACTIVE
LOG_ARCHIVE_DEST_6                                 INACTIVE
LOG_ARCHIVE_DEST_7                                 INACTIVE
LOG_ARCHIVE_DEST_8                                 INACTIVE
LOG_ARCHIVE_DEST_9                                 INACTIVE
LOG_ARCHIVE_DEST_10                                VALID     USE_DB_RECOVERY_FILE_DEST

10 rows selected.

SQL>

到相應的目錄下找到時間比較久遠的文件，delete,空間就釋放了，

因為我這裡是測試環境，所以沒有考慮那麼複雜

[oracle@croracle02 dbs]$ df -lh
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       18G  8.8G  7.8G  54% /
/dev/sda1              99M   12M   82M  13% /boot
tmpfs                1006M  667M  340M  67% /dev/shm
/dev/sdd1             2.0G  843M  1.2G  42% /u02

 

記錄一下，備忘

expdp實驗

仰慕expdp,impdp很久，一直沒有動手做過，記錄一下expdp的實驗的內容：

[oracle@localhost ~]$ expdp scott/tiger DIRECTORY=dump_dir dumpfile=emp.dmp tables=emp
Export: Release 10.2.0.1.0 - 64bit Production on 星期六, 27 2月, 2010 7:12:40
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_DIR is invalid

報錯了

[oracle@localhost ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 2月 27 07:18:57 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory dump_dir as '/u01';
create directory dump_dir as '/u01'
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn / as sysdba;
Connected.
SQL> create directory dump_dir as '/u01/dump';
Directory created.
SQL> grant read, write on directory dump_dir to scott;
Grant succeeded.
SQL>

 

[oracle@localhost ~]$ expdp scott/tiger DIRECTORY=dump_dir dumpfile=emp.dmp tables=emp
Export: Release 10.2.0.1.0 - 64bit Production on 星期六, 27 2月, 2010 7:22:27
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** DIRECTORY=dump_dir dumpfile=emp.dmp tables=emp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/dump/emp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 07:22:49
[oracle@localhost ~]$

 

[oracle@localhost dump]$ ls
emp.dmp  export.log
[oracle@localhost dump]$
[oracle@localhost dump]$ strings  emp.dmp |less
<CHARSET>WE8ISO8859P1</CHARSET> //字符集

<NCHARSET
>AL16UTF16</NCHARSET>國家字符集

 

這個表的數據不大，體現不出來優勢，不過基本上跑過了，呵呵!

春節後的第一貼！

CRS-0184: Cannot communicate with the CRS daemon. 的處理

有一段時間沒有用RAC的測試環境了，重新啟動後，發現報錯了

[oracle@oracle01 ~]$ crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

我記得itpub上有人處理過類似的問題，今天終於有機會實踐一把了

在/var/tmp/有一個.oracle目錄

將這個目錄中的東西清空

rm –rf *

然後在root用戶下啟動crs

crsctl start crs

等待一段時間發現還是沒有辦法帶動起來crs全部的services

[oracle@oracle01 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    ONLINE    OFFLINE
ora....01.lsnr application    ONLINE    OFFLINE
ora....e01.gsd application    ONLINE    OFFLINE
ora....e01.ons application    ONLINE    OFFLINE
ora....e01.vip application    ONLINE    OFFLINE
ora....SM2.asm application    ONLINE    ONLINE    croracle02
ora....02.lsnr application    ONLINE    ONLINE    croracle02
ora....02.lsnr application    ONLINE    OFFLINE
ora....e02.gsd application    ONLINE    OFFLINE
ora....e02.ons application    ONLINE    ONLINE    croracle02
ora....e02.vip application    ONLINE    ONLINE    croracle02
ora....SM3.asm application    ONLINE    OFFLINE
ora....03.lsnr application    ONLINE    OFFLINE
ora....e03.gsd application    ONLINE    OFFLINE
ora....e03.ons application    ONLINE    OFFLINE
ora....e03.vip application    ONLINE    OFFLINE
ora....SM4.asm application    ONLINE    OFFLINE
ora....04.lsnr application    ONLINE    OFFLINE
ora....e04.gsd application    ONLINE    OFFLINE
ora....e04.ons application    ONLINE    OFFLINE
ora....e04.vip application    ONLINE    OFFLINE
ora....02.inst application    ONLINE    OFFLINE
ora....d1.inst application    ONLINE    OFFLINE
ora....d3.inst application    ONLINE    OFFLINE
ora....d4.inst application    ONLINE    OFFLINE
ora.crprod.db  application    ONLINE    OFFLINE

看到之後oracle02 這個node的服務啟動了

沒有辦法重新啟動服務器（我的是vmware 呵呵）

reboot後正常

 

[oracle@oracle01 ~]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    croracle01
ora....01.lsnr application    ONLINE    ONLINE    croracle01
ora....e01.gsd application    ONLINE    ONLINE    croracle01
ora....e01.ons application    ONLINE    ONLINE    croracle01
ora....e01.vip application    ONLINE    ONLINE    croracle01
ora....SM2.asm application    ONLINE    ONLINE    croracle02
ora....02.lsnr application    ONLINE    ONLINE    croracle02
ora....02.lsnr application    ONLINE    OFFLINE
ora....e02.gsd application    ONLINE    ONLINE    croracle02
ora....e02.ons application    ONLINE    ONLINE    croracle02
ora....e02.vip application    ONLINE    ONLINE    croracle02
ora....SM3.asm application    ONLINE    OFFLINE
ora....03.lsnr application    ONLINE    OFFLINE
ora....e03.gsd application    ONLINE    OFFLINE
ora....e03.ons application    ONLINE    OFFLINE
ora....e03.vip application    ONLINE    ONLINE    croracle01
ora....SM4.asm application    ONLINE    OFFLINE
ora....04.lsnr application    ONLINE    OFFLINE
ora....e04.gsd application    ONLINE    OFFLINE
ora....e04.ons application    ONLINE    OFFLINE
ora....e04.vip application    ONLINE    ONLINE    croracle02
ora....02.inst application    ONLINE    ONLINE    croracle02
ora....d1.inst application    ONLINE    ONLINE    croracle01
ora....d3.inst application    ONLINE    OFFLINE
ora....d4.inst application    ONLINE    OFFLINE
ora.crprod.db  application    ONLINE    ONLINE    croracle02
[oracle@croracle01 ~]$

 

[oracle@oracle01 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Feb 3 01:50:41 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select * from v$active_instances;

INST_NUMBER
-----------
INST_NAME
--------------------------------------------------------------------------------
          1
croracle01.localdomain:crprod1

          2
croracle02.localdomain:croracle02

SQL>

 

看來reboot對oracle也是好使的！

Oracle tuning goals and techniques

oracle 9i tuning 教程总结的，不错，记录一下

tuning goals（主要是data buffer cache部分的） :
--servers find data in memory //数据尽量放在内存中
-- no waits on the buffer cache  //buffer cache上不要发生wait events
. Diagnostic measures
--wait events  //等待事件
--Cache hit ratio  //命中率
-- the v$db_cache_advice view  //data buffer的建议
.  tuning techniques
--reduce the number of blocks requires by sql  //sql尽量做到精确
--increase buffer cache size  // buffer cache的大小很重要
--use multiple buffer pools   //为不同性质的sql使用不同的buffer pool
-- cache tables //将小表放在内存中
-- bypass the cache for sorting and paralled reads. //绕过排序和并发读

Oracle - statspack報告中的 log file sync是意義？

官方文檔中：

redo synch time

The elapsed time of all redo sync writes calls in 10s of milliseconds.

redo sync writes

Usually, redo that is generated and copied into the log buffer need not be flushed out to disk immediately. The log buffer is a circular buffer that LGWR periodically flushes. Redo sync writes increments when changes being applied must be written out to disk due to a commit.

參考eygle的文章：

----------------------------------------------------------------------------

.等待事件

Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
---------------------------- ------------ ---------- ----------- ------ ------
log file sync                      14,466          2       4,150      3    1.0
db file sequential read            17,202          0       2,869      2    1.2
latch free                         24,841     13,489       2,072      1    1.7 
direct path write                     121          0       1,455    120    0.0
db file parallel write              1,314          0       1,383     11    0.1
log file sequential read            1,540          0          63      0    0.1
....
log file switch completion              1          0           3     30    0.0
refresh controlfile command            23          0           1      0    0.0
LGWR wait for redo copy                46          0           0      0    0.0
....
log file single write                   4          0           0      0    0.0
       

我们看到，这里log file sync和db file parallel write等待同时出现了.

显然log file sync在等待db file parallel write的完成.

这里磁盘IO肯定存在了瓶颈，实际用户的redo和数据文件同时存放在Raid的磁盘上，存在性能问题.

需要调整.

 

---------------------------------------------------

我猜測的是redo log file 所在的physical disk 有問題,或者io的問題,大體上沒有猜錯。

但是看了一下eygle的文章2004年的，差距啊

準備仔細研究一下statspack!

參考：

http://www.eygle.com/statspack/statspack14-LogFileSync.htm

SP2-0618: Cannot find the Session Identifier. 问题

在oracle11g下面做一个实验的时候发现包这个错：

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

google 发现当前用户权限不够

SQL> grant all on plan_table to u2;

Grant succeeded.

 

SQL> grant select any dictionary to u2;

Grant succeeded.

再次执行，ok

SQL> set autotrace on

SQL> set timing on
SQL> select owner, count(*) from my_all_objects group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
WKSYS                                 840
MDSYS                                4896
WK_TEST                                36
U2                                    192
PUBLIC                             160218
CTXSYS                                534
OLAPSYS                              1056
SYSTEM                                 54
EXFSYS                                480
ORDSYS                              12606
ORDPLUGINS                             30

OWNER                            COUNT(*)
------------------------------ ----------
XDB                                  1212
FLOWS_030000                          942
SYS                                139512
WMSYS                                 702

15 rows selected.

Elapsed: 00:00:00.22

Execution Plan
----------------------------------------------------------
Plan hash value: 2509106709

--------------------------------------------------------------------------------
------------------

| Id  | Operation           | Name                       | Rows  | Bytes | Cost
(%CPU)| Time     |

--------------------------------------------------------------------------------
------------------

|   0 | SELECT STATEMENT    |                            |    15 |    90 |  1371
   (3)| 00:00:17 |

|   1 |  RESULT CACHE       | 27vtdg9w24wgcb8d23j5h07m2v |       |       |
      |          |

|   2 |   HASH GROUP BY     |                            |    15 |    90 |  1371
   (3)| 00:00:17 |

|   3 |    TABLE ACCESS FULL| MY_ALL_OBJECTS             |   323K|  1894K|  1348
   (1)| 00:00:17 |

--------------------------------------------------------------------------------
------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(U2.MY_ALL_OBJECTS); parameters=(nls); name=
"select owner, count(*) from my_all_objects group by owner"

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4869  consistent gets
          0  physical reads
        116  redo size
        863  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL>

看来在oracle 11g中不用再使用($ORACLE_HOME/sqlplus/admin/plustrce.sql)脚本创建 plustrace了

记录一下。

diagcollection.pl –collect Mandatory argument 'crshome' is missing. 問題處理

學習使用RAC  diagcollection tools報錯了,我的環境是oracle11g R1

[oracle@croracle01 bin]$ ./diagcollection.pl -collect
Production Copyright 2004, 2007, Oracle.  All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
Mandatory argument 'crshome' is missing.

[oracle@croracle01 bin]$ su
Password:
[root@croracle01 bin]# ./diagcollection.pl -collect -crshome=/u01/app/crs
Production Copyright 2004, 2007, Oracle.  All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
The following CRS diagnostic archives will be created in the local directory.
crsData_croracle01.tar.gz -> logs,traces and cores from CRS home. Note: core files will be packaged only with the -core option.
ocrData_croracle01.tar.gz -> ocrdump, ocrcheck etc
coreData_croracle01.tar.gz -> contents of CRS core files in text format

Collecting crs data
/bin/tar: Removing leading `/' from member names

Collecting OCR data
Collecting information from core files
No corefiles found
The following Oracle Home diagnostic archives will be created in the local directory.
oraData_croracle01.tar.gz -> logs, traces and cores from Oracle Home
Collecting oracle home data
/bin/tar: Removing leading `/' from member names
[root@croracle01 bin]#

 

[root@croracle01 bin]# ll *.gz
-rw-r--r-- 1 root root 1559181 Nov 26 23:39 crsData_croracle01.tar.gz
-rw-r--r-- 1 root root   11791 Nov 26 23:40 ocrData_croracle01.tar.gz
-rw-r--r-- 1 root root   35400 Nov 26 23:40 oraData_croracle01.tar.gz

其實-help中說的很清楚

[root@croracle01 bin]# ./diagcollection.pl -help
Production Copyright 2004, 2007, Oracle.  All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
diagcollection
    --collect
             [--crs] For collecting crs diag information
             [--oh]  For collecting oracle home diag information
             [--adr] For collecting diag information for ADR
             [--all] Default.For collecting all diag information.
             [--core] UNIX only. Package core files with CRS data
             [--afterdate] UNIX only. Collects archives from the specified date. Specify in mm/dd/yyyy format
             [--aftertime] Supported with -adr option. Collects archives after the specified time. Specify in YYYYMMDDHHMISS24 format
             [--beforetime] Supported with -adr option. Collects archives before the specified date. Specify in YYYYMMDDHHMISS24 format
             [--crshome] Argument that specifies the CRS Home location
             NOTE:
             1. You can also do the following
                ./diagcollection.pl --collect --crs --oh
             2. ORACLE_HOME env variable needs to be set.

     --clean        cleans up the diagnosability
                    information gathered by this script

     --coreanalyze  UNIX only. Extracts information from core files
                    and stores it in a text file
diagcollection
    --collect
             [--crs] For collecting crs diag information
             [--oh]  For collecting oracle home diag information
             [--adr] For collecting diag information for ADR
             [--all] Default.For collecting all diag information.
             [--core] UNIX only. Package core files with CRS data
             [--afterdate] UNIX only. Collects archives from the specified date. Specify in mm/dd/yyyy format
             [--aftertime] Supported with -adr option. Collects archives after the specified time. Specify in YYYYMMDDHHMISS24 format
             [--beforetime] Supported with -adr option. Collects archives before the specified date. Specify in YYYYMMDDHHMISS24 format
             [--crshome] Argument that specifies the CRS Home location
             NOTE:
             1. You can also do the following
                ./diagcollection.pl --collect --crs --oh
             2. ORACLE_HOME env variable needs to be set.

     --clean        cleans up the diagnosability
                    information gathered by this script

     --coreanalyze  UNIX only. Extracts information from core files
                    and stores it in a text file
[root@croracle01 bin]#

記錄一下備忘!