Oracle日常

1、更改字符集
# echo "LANG="en_US.UTF-8" >> /etc/sysconfig/i18n
# source /etc/sysconfig/i18n
# xclock

2、创建Oracle用户
# mkdir -p /export/home
# groupadd -g 5000 dba
# groupadd -g 5001 oinstall
# useradd -g dba -G oinstall -u 502 -d /export/home/oracle -m -s /bin/bash oracle
# passwd oracle

3、创建安装目录
# mkdir -p /export/home/oracle/oracle/product/11.2.0
# chown -R oracle:oinstall /export/home/oracle
# mkdir /var/opt/oracle
# chown -R oracle:dba /var/opt/oracle
# chmod 755 /var/opt/oracle

4、内核优化
# uname -a
# Linux VI 命令/etc/sysctl.conf

### sysctl.conf 添加 ####
# For 11g, recommended value for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
# For 11g, recommended value for wmem_max is 1048576
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
# For 11g, recommended value for ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500

# Linux VI 命令/etc/security/limits.conf

### limits.conf 添加
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 65536
oracle hard nproc 65536
# End of file

# echo "250 32000 100 128" > /proc/sys/kernel/sem
# Linux VI 命令/etc/rc.local

## rc.local 添加
echo "250 32000 100 128" > /proc/sys/kernel/sem

5、添加oracle用户环境变量
oracle$ Linux VI 命令 .bash_profile

## .bash_profile添加
export LD_ASSUME_KERNEL=2.6.32
export ORACLE_BASE=/export/home/oracle/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=ctvit
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_TERM=xterm
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

______________________________________________________

读写分离
多主
队列缓冲
平衡系统间的速度差

负载均衡
会话保持

最终一致性
数据追赶
cap

核心思想

1、多数据副本 前端读写分离
2、数据库的复制
3、galera 同步多主复制
4、NoSQL

perl 连接库

eXtraDB
couchBase DB

筛选1、2个开源路线

jdbc-to-jdbc
透明化代理

———————————————————————
ORA-00257错误解决


cd /u01/app/oracle/product/11.2.0/db_1/dbs/arch
find ./ -mtime +2 -name "*.dbf" -exec rm -rf {} \;

_______________________________________________________

create user xxx identified by xxx default tablespace xxx ;
grant connect,resource,dba to xxx;


create tablespace test
datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8M
autoextend on
next 5M
maxsize 10M;


create user cctv identified by cctv default tablespace xxx ;
grant connect,resource,dba to xxx;



exp userid=system/manager owner=username1 file=expfile.dmp

imp userid=system/manager fromuser=username1 touser=username2 ignore=y file=expfile.dmp full=y
————————————————————————

cd ogg
./ggsci

> info all
> start mgr
> start *

————————————————————————
******* expdp.txt *********
userid=system/sys123
directory=my_dir
dumpfile=smsuser_20130130.dmp
logfile=smsuser_20130130.log
schemas=SMSUSER

*****************************

$ expdp parfile=expdp2.txt


******* impdp.txt *********
userid=system/sys123
directory=my_dir
dumpfile=smsuser_20130130.dmp
logfile=smsuser_20130130.log
content=all



*****************************

$ impdp parfile=impdp.txt
$ Linux VI 命令 impdp.txt
$ impdp parfile=impdp.txt flashback_scn=1651021 &

____________________________________________

expdp system/sys123 directory=DPDIR dumpfile=db2013.dmp logfile-=db2014.log Full=y


empdp system/sys123 DIRECTORY=dpdir dumpfile=cctv.dump schemas=cctv remap_tablespace=system:ts_data-01

CREATE DIRECTORY dump dir AS ‘DUMP’;
GRANT READ, WIRTE ON DIRECTORY dpdir TO xinhuashe;

create or replace directory dpumpdir as '/home/oracle/data';
grant read ,write on directory dpumpdir to SYS;