凡所有相☯皆是虛妄: 二月 2010

2010年2月25日星期四

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.

記錄一下，備忘。

2010年2月22日星期一

openSolaris 0906 install GCC

1,像偉大的SUN默哀1 min

2,使用 open Solaris live CD 0906安裝一個測試環境。

運行gcc

root@opensolaris:~# gcc
-bash: gcc: command not found
root@opensolaris:~#

沒有找到gcc,安裝

圖形介面

System->Administration –> Package Manager

Search all package for “GCC” or “SUNWgcc”

Install /update packages

“installation completed successfully”

root@opensolaris:~# cd /usr/sfw/bin
root@opensolaris:/usr/sfw/bin# ls gcc
gcc
root@opensolaris:/usr/sfw/bin# ./gcc
gcc: no input files
root@opensolaris:/usr/sfw/bin# ./gcc -v
Reading specs from /usr/sfw/lib/gcc/i386-pc-solaris2.11/3.4.3/specs
Configured with: /builds2/sfwnv-111a/usr/src/cmd/gcc/gcc-3.4.3/configure --prefix=/usr/sfw --with-as=/usr/sfw/bin/gas --with-gnu-as --with-ld=/usr/ccs/bin/ld --without-gnu-ld --enable-languages=c,c++,f77,objc --enable-shared
Thread model: posix
gcc version 3.4.3 (csl-sol210-3_4-20050802)
root@opensolaris:/usr/sfw/bin#

記錄一下，備忘！

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> /

SQL> /

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

記錄一下，備忘

2010年2月19日星期五

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>國家字符集

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

春節後的第一貼！

订阅：博文 (Atom)

凡所有相☯皆是虛妄