Posts tagged ‘oracle’
oracle export / import command
exp userid=user/password file=/path/database_$(date +%Y%m%d).dmp log=/path/database_$(date +%Y%m%d).log ROWS=Y
imp user/password file=database_20100914.dmp fromuser=user1 touser=user2 grants=n ignore=y feedback=1000000
Maximum Availability Architecture – Oracle Streams Configuration Best Practices
Oracle doc PDF URL : http://dn.monster.tw/my/docs/oracle/MAA_10gR2_Streams_Configuration.pdf
Oracle® Streams Advanced Queuing User’s Guide and Reference
10g Release 2 (10.2) – http://download.oracle.com/docs/cd/B19306_01/server.102/b14257/toc.htm
sample memo: http://www.monster.com.tw/archives/2565
Oracle Maximum Availability Architecture – Overview – http://www.oracle.com/technology/deploy/availability/htdocs/maaoverview.html
使用 mysql partition table sample
Mysql server version:
mysql> show variables like '%version%'; +--------------------------+------------------------------+ | Variable_name | Value | +--------------------------+------------------------------+ | protocol_version | 10 | | version | 5.1.46-community | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +--------------------------+------------------------------+ 9 rows in set (0.01 sec)
Check things…
mysql> show variables like '%inno%'; +-----------------------------------------+------------------------+ | Variable_name | Value | +-----------------------------------------+------------------------+ | have_innodb | YES | mysql> show variables like '%part%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES |
my schema:
-- CREATE TABLE `tbl_ts_ip` ( `xdate` int(8) NOT NULL default 0, `xhour` varchar(2) NOT NULL default '00', `tag1` varchar(100) NOT NULL default '', `tag2` varchar(300) NOT NULL default '', `val` int(10) unsigned NOT NULL default '0', KEY `ndx_xdate` (`xdate`), KEY `ndx_xhour` (`xhour`), KEY `ndx_tag1` (`tag1`), KEY `ndx_tag2` (`tag2`) ) ENGINE=InnoDB partition by range(xdate) ( partition p201003 values less than (20100400), partition p201004 values less than (20100500), partition p201005 values less than (20100600), partition p201006 values less than (20100700) );
testing
-- insert into tbl_ts_ip values (20100301,'01','tag1','tag2',1); insert into tbl_ts_ip values (20100401,'01','tag1','tag2',1); insert into tbl_ts_ip values (20100501,'01','tag1','tag2',1); insert into tbl_ts_ip values (20100601,'01','tag1','tag2',1); insert into tbl_ts_ip values (20100701,'01','tag1','tag2',1); --
result:
-- mysql> insert into tbl_ts_ip values (20100301,1,'tag1','tag2',1); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl_ts_ip values (20100401,1,'tag1','tag2',1); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl_ts_ip values (20100501,1,'tag1','tag2',1); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl_ts_ip values (20100601,1,'tag1','tag2',1); Query OK, 1 row affected (0.00 sec) mysql> insert into tbl_ts_ip values (20100701,1,'tag1','tag2',1); ERROR 1526 (HY000): Table has no partition for value 20100701 mysql> select * from tbl_ts_ip; +----------+-------+------+------+-----+ | xdate | xhour | tag1 | tag2 | val | +----------+-------+------+------+-----+ | 20100301 | 1 | tag1 | tag2 | 1 | | 20100401 | 1 | tag1 | tag2 | 1 | | 20100501 | 1 | tag1 | tag2 | 1 | | 20100601 | 1 | tag1 | tag2 | 1 | +----------+-------+------+------+-----+ 4 rows in set (0.00 sec) --
data files :
[root@ stat]# ls -l total 676 -rw-rw---- 1 mysql mysql 65 May 18 17:02 db.opt -rw-rw---- 1 mysql mysql 8682 May 18 17:07 tbl_ts_ip.frm -rw-rw---- 1 mysql mysql 52 May 18 17:07 tbl_ts_ip.par -rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201003.ibd -rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201004.ibd -rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201005.ibd -rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201006.ibd
add partitions:
-- alter table tbl_ts_ip add partition ( partition p201007 values less than (20100800), partition p201008 values less than (20100900), partition p201009 values less than (20101000), partition p201010 values less than (20101100), partition p201011 values less than (20101200), partition p201012 values less than (20110100) ); --
explain partitions:
-- mysql> explain partitions select * from tbl_ts_ip where xdate=20100501; +----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+ | 1 | SIMPLE | tbl_ts_ip | p201005 | ref | ndx_xdate | ndx_xdate | 4 | const | 1 | | +----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+ -- -- -- mysql> explain partitions select * from tbl_ts_ip where xdate>20100101 and xdate<20100501 and xhour=1 and tag1='x1' and tag2='x2'; +----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+ | 1 | SIMPLE | tbl_ts_ip | p201003,p201004,p201005 | ref | ndx_xdate,ndx_xhour,ndx_tag1,ndx_tag2 | ndx_xhour | 4 | const | 3 | Using where | +----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+ --
######################
sample schema:
create table t1 (
d date
) engine=innodb partition by range ( to_days(d) ) (
partition p1 values less than (to_days(’1999-01-01′)),
partition p2 values less than (to_days(’2000-01-01′))
);
– drop old partition
alter table tablename
drop partition p1;
######
-- CREATE TABLE `tbl_ts_ip` ( `xdate` int(8) NOT NULL default 0, `xhour` int(2) NOT NULL default 0, `tag1` varchar(100) NOT NULL default '', `tag2` varchar(300) NOT NULL default '', `val` int(10) unsigned NOT NULL default '0', KEY `ndx_xdate` (`xdate`), KEY `ndx_xhour` (`xhour`), KEY `ndx_tag1` (`tag1`), KEY `ndx_tag2` (`tag2`) ) ENGINE=InnoDB partition by range(xdate) ( partition p200901 values less than (20090200), partition p200902 values less than (20090300), partition p200903 values less than (20090400), partition p200904 values less than (20090500), partition p200905 values less than (20090600), partition p200906 values less than (20090700), partition p200907 values less than (20090800), partition p200908 values less than (20090900), partition p200909 values less than (20091000), partition p200910 values less than (20091100), partition p200911 values less than (20091200), partition p200912 values less than (20100100), partition p201001 values less than (20100200), partition p201002 values less than (20100300), partition p201003 values less than (20100400), partition p201004 values less than (20100500), partition p201005 values less than (20100600), partition p201006 values less than (20100700), partition p201007 values less than (20100800), partition p201008 values less than (20100900), partition p201009 values less than (20101000), partition p201010 values less than (20101100), partition p201011 values less than (20101200), partition p201012 values less than (20110100), partition p201101 values less than (20110200), partition p201102 values less than (20110300), partition p201103 values less than (20110400), partition p201104 values less than (20110500), partition p201105 values less than (20110600), partition p201106 values less than (20110700), partition p201107 values less than (20110800), partition p201108 values less than (20110900), partition p201109 values less than (20111000), partition p201110 values less than (20111100), partition p201111 values less than (20111200), partition p201112 values less than (20120100) ); --
###
一天一個 partition …
--
CREATE TABLE `tbl_ts_ip` (
`xdate` date NOT NULL DEFAULT '0000-00-00',
`xhour` int(2) NOT NULL DEFAULT '0',
`tag1` varchar(100) NOT NULL DEFAULT '',
`tag2` varchar(300) NOT NULL DEFAULT '',
`val` int(10) unsigned NOT NULL DEFAULT '0',
KEY `ndx_xdate` (`xdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( to_days(xdate) )
(PARTITION p0 VALUES LESS THAN ( to_days('2005-1-1') ) );
--
--
alter table tbl_ts_ip
add partition (
partition p20100401 values less than ( to_days('20100401')+1 )
);
--
###
產生 alter table … add partition 的 code
$start_date = mktime( 0,0,0,4,1,2010);
$day_count = 365;
for ( $i=0; $i < $day_count; $i++ ) {
echo "
alter table tbl_ts_ip
add partition (
partition p".date("Ymd",$start_date)." values less than ( to_days('".date("Ymd",$start_date)."')+1 )
);
";
echo "
alter table tbl_ts_member
add partition (
partition p".date("Ymd",$start_date)." values less than ( to_days('".date("Ymd",$start_date)."')+1 )
);
";
$start_date += 86400;
}
###
--
mysql> explain partitions select * from tbl_ts_ip where xdate>20100510 and xdate<20100517 and tag2='114.39.237.77'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_ts_ip
partitions: p0,p20100511,p20100512,p20100513,p20100514,p20100515,p20100516
type: ALL
possible_keys: ndx_xdate
key: NULL
key_len: NULL
ref: NULL
rows: 37706064
Extra: Using where
1 row in set (0.01 sec)
mysql> select * from tbl_ts_ip where xdate>20100510 and xdate<20100517 and tag2='114.39.237.77';
+------------+-------+--------------------+---------------+-----+
| xdate | xhour | tag1 | tag2 | val |
+------------+-------+--------------------+---------------+-----+
| 2010-05-16 | 23 | 37043600380A380C3E | 114.39.237.77 | 2 |
+------------+-------+--------------------+---------------+-----+
1 row in set (24.26 sec)
--
###
| Oracle partition table | MySQL partition table |
|
Oracle 說 partition 的好處是…
Partitioning is transparent to existing applications and standard DML statements run against partitioned tables. However, an application can be programmed to take advantage of partitioning by using partition-extended table or index names in DML. You can use SQL*Loader and the import and export utilities to load or unload data stored in partitioned tables. These utilities are all partition and subpartition aware. |
two main advantages are: Increased performance – during scan operations, the MySQL optimizer knows what partitions contain the data that will satisfy a particular query and will access only those necessary partitions during query execution. For example, a million row table may be broken up into ten different partitions in range style so that each partition contains 100,000 rows. If a query is issued that only needs data from one of the partitions, and a table scan operation is necessary, only 100,000 rows will be accessed instead of a million. Obviously, it is much quicker for MySQL to sample 100,000 rows than one million so the query will complete much sooner. The same benefit is derived should index access be possible as local partitioned indexes are created for partitioned tables. Finally, it is possible to stripe a partitioned table across different physical drives by specifying different file system/directory paths for specific partitions. This allows physical I/O contention to be reduced when multiple partitions are accessed at the same time.
|
| ref: MySQL partitions tutorial , http://www.slideshare.net/datacharmer/mysql-partitions-tutorial download: http://dn.monster.tw/my/docs/mysql/partitionstutorial-090426130303-phpapp02.pdf |
—
用 mysql partition table 要注意的事:
Check resources!
- InnoDB
check disk space (uses more than MyISAM)check CPU usage
- MyISAM
use 2 file handles per partitionIf you use more than one partitioned table, count total file handles
- Archive
check CPU usagecheck memory usage
更改 oracle 成 archivelog mode
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 171970372 bytes
Database Buffers 423624704 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
————
ARCHIVELOG
[note] install oracle 10g on ubuntu 910 steps
root@ubuntu910:~# uname -a
Linux ubuntu910 2.6.31-20-generic-pae #58-Ubuntu SMP Fri Mar 12 06:25:51 UTC 2010 i686 GNU/Linux
安裝這些
apt-get update apt-get upgrade apt-get install gcc make binutils libaio1 gawk ksh libc6-dev rpm libmotif3 alien lsb-rpm libtool
root@ubuntu910:~# whereis awk
awk: /usr/bin/awk /usr/lib/awk /usr/share/awk /usr/share/man/man1/awk.1.gz
root@ubuntu910:~# whereis rpm
rpm: /usr/bin/rpm /usr/lib/rpm /usr/share/man/man8/rpm.8.gz
root@ubuntu910:~# whereis basename
basename: /usr/bin/basename /usr/share/man/man1/basename.1.gz
修正這些 bin 的 path , oracle 10g
ln -s /usr/bin/awk /bin/awk ln -s /usr/bin/rpm /bin/rpm ln -s /usr/bin/basename /bin/basename
設定帳號 / group 及建目錄
addgroup oinstall addgroup dba addgroup nobody usermod -g nobody nobody useradd --create-home --home-dir /home/oracle --gid dba --shell /bin/bash oracle usermod --groups admin oracle chown -R oracle:dba /home/oracle mkdir -p /u01/app/oracle chown -R oracle:dba /u01 chmod -R 755 /u01 # 改一下 oracle 的 password passwd oracle
修改 LINUX OS 系統參數 /etc/sysctl.conf
fs.file-max = 65535 kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 1024 65535 net.core.rmem_default = 1048576 net.core.rmem_max = 1048576 net.core.wmem_default = 262144 net.core.wmem_max = 262144
及 /etc/security/limits.conf
oracle soft nproc 2047 oracle hard nproc 16383 oracle soft nofile 1023 oracle hard nofile 65535
vi ~oracle/.profile
# # export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 export ORACLE_SID=orcl # PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin export PATH # unset USERNAME # #
reboot ubuntu 讓 OS tunning 參數生效
用 oracle 帳號login
搞定 X window 或在 ubuntu 圖型界面下開始安裝 oracle
X window 參考: http://www.monster.com.tw/archives/1098
圖形界面參考: http://www.monster.com.tw/archives/1709
cd /database 後下:
./runInstaller -ignoreSysPrereqs
// ———————————————-
遇到這個問題
INFO: gcc: /usr/lib/libstdc++.so.5: No such file or directory
INFO: /u01/app/oracle/product/10.2.0/db_1/bin/genorasdksh: Failed to link liborasdkbase.so.10.2
INFO: make: *** [liborasdkbase] Error 1
cd /usr/lib ls -l libstdc++* lrwxrwxrwx 1 root root 19 2010-04-17 22:14 libstdc++.so.6 -> libstdc++.so.6.0.13 -rw-r--r-- 1 root root 962800 2010-01-10 23:53 libstdc++.so.6.0.13 # ln -sf libstdc++.so.6.0.13 libstdc++.so.5
遇到這個問題
/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole…. not found
.
.
.
oracle@ubuntu910:~$ netstat -an |grep LIST tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:3938 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:1158 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:5520 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:1521 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:33044 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN tcp6 0 0 ::1:6010 :::* LISTEN tcp6 0 0 :::22 :::* LISTEN tcp6 0 0 ::1:631 :::* LISTEN unix 2 [ ACC ] STREAM LISTENING 4621 /var/run/cups/cups.sock unix 2 [ ACC ] STREAM LISTENING 3998 @/var/run/hald/dbus-W4TPdf1r7s unix 2 [ ACC ] STREAM LISTENING 2677 @/com/ubuntu/upstart unix 2 [ ACC ] STREAM LISTENING 6110 /var/tmp/.oracle/sEXTPROC0 unix 2 [ ACC ] STREAM LISTENING 6108 /var/tmp/.oracle/s#1165.1 unix 2 [ ACC ] STREAM LISTENING 6112 /var/tmp/.oracle/s#1165.2 unix 2 [ ACC ] STREAM LISTENING 3610 @/var/run/hald/dbus-Empv97RMbr unix 2 [ ACC ] STREAM LISTENING 3564 /var/run/dbus/system_bus_socket
PS note:
- 開機時自動啟動 oracle database 及 listener – http://www.akadia.com/services/ora_linux_install_10g.html
- .
其他相關的檔案/設定:
monster@ubuntu910:~$ cat /etc/fstab # /dev/sdb1 none swap sw 0 0 /dev/sdc1 /u01 ext2 rw 0 0 monster@ubuntu910:~$ cat /etc/hosts 127.0.0.1 localhost 172.30.0.48 ubuntu910.monster.tw ubuntu910 #
default 有這幾個 tablespace
SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX TEMP USERS
