Python connect to oracle

Oracle 9i Bug 5888835/5508574

How I Simplified Oracle Database 12c and 11g Installations on Oracle Linux 6

有趣!!!

OracleValidated Configurations

Oracle's V$ Views

http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

每月統計oracle table count

以前寫的一個store procedure ，起因是某個離職的主管要我每個月report DB內某一個USER下所有TABLE
row count的差異量，開玩笑不是10個或是20 table，是將近1000個，用excel要做到名國幾年阿。

先建立一個統計表

CREATE TABLE DB_RPT1
(
NAM_TABLE VARCHAR2(50) NOT NULL,
NAM_YEAR VARCHAR2(4),
MOON_1 VARCHAR2(30),
MOON_2 VARCHAR2(30),
MOON_3 VARCHAR2(30),
MOON_4 VARCHAR2(30),
MOON_5 VARCHAR2(30),
MOON_6 VARCHAR2(30),
MOON_7 VARCHAR2(30),
MOON_8 VARCHAR2(30),
MOON_9 VARCHAR2(30),
MOON_10 VARCHAR2(30),
MOON_11 VARCHAR2(30),
MOON_12 VARCHAR2(30),
PRIMARY KEY(NAM_TABLE,NAM_YEAR )
);




再建立store procedure


CREATE OR REPLACE procedure DB_RPT is

v_empty_flag varchar2(1);
V_CNT varchar2(30);
sql_stmt varchar2(1000);
SQL_STR VARCHAR2(1000);
SQL_STR1 VARCHAR2(1000);
V_NAM_MOON VARCHAR2(2);
V_NAM_MOON_FLG VARCHAR2(6);
V_NAM_YEAR VARCHAR2(4);

CURSOR C1 is
select A.TABLE_NAME from all_tables A where owner='ERPUSER';

BEGIN
V_NAM_MOON :=TO_CHAR(SYSDATE,'MM');
V_NAM_YEAR :=TO_CHAR(SYSDATE,'YYYY') ;
FOR c1_rec IN C1 loop
sql_stmt := 'select count(*) from '||c1_rec.table_name;
EXECUTE IMMEDIATE sql_stmt into V_CNT;
BEGIN
CASE V_NAM_MOON
WHEN '01' THEN
V_NAM_MOON_FLG:='MOON_1';
WHEN '02' THEN
V_NAM_MOON_FLG:='MOON_2';
WHEN '03' THEN
V_NAM_MOON_FLG:='MOON_3';
WHEN '04' THEN
V_NAM_MOON_FLG:='MOON_4';
WHEN '05' THEN
V_NAM_MOON_FLG:='MOON_5';
WHEN '06' THEN
V_NAM_MOON_FLG:='MOON_6';
WHEN '07' THEN
V_NAM_MOON_FLG:='MOON_7';
WHEN '08' THEN
V_NAM_MOON_FLG:='MOON_8';
WHEN '09' THEN
V_NAM_MOON_FLG:='MOON_9';
WHEN '10' THEN
V_NAM_MOON_FLG:='MOON_10';
WHEN '11' THEN
V_NAM_MOON_FLG:='MOON_11';
WHEN '12' THEN
V_NAM_MOON_FLG:='MOON_12';
END CASE;

SQL_STR :='UPDATE DB_RPT1 SET '||V_NAM_MOON_FLG||'='''||V_CNT||''' WHERE NAM_TABLE='''||c1_rec.table_name ||''' AND NAM_YEAR='''||V_NAM_YEAR||'''';
EXECUTE IMMEDIATE SQL_STR;
if(SQL%NOTFOUND)THEN
--ROLLBACK;
SQL_STR1:= 'INSERT INTO DB_RPT1( NAM_TABLE,NAM_YEAR,'||V_NAM_MOON_FLG||') VALUES('''|| c1_rec.table_name||''','''||V_NAM_YEAR||''','''||v_cnt||''')';
EXECUTE IMMEDIATE SQL_STR1;
END IF;
COMMIT;
END;
END LOOP;
COMMIT;
--exception
--when others then
-- rollback;
-- p_msg := to_char(sqlcode);
end;
/


再去產生一個job 看是每月的那一天執行，要看的時候就用SQL查詢出來吧！！
管你是要看今年還是明年 .....select 就對了

clean log for ORACLE LSNRCTL


#!/bin/sh
#

ORACLE_USER=oracle
TMP_FILE="/tmp/lsnrcmd"
###############
# use Yes or Y ,NO or N
ROTATE_FILE=y
###############
ROTATE=3

###########################################
EXEC_USER=`who -m |awk '{print $1}'`

CLEAN_LOG(){

if [ $EXEC_USER = "root" ]; then

su - $ORACLE_USER -c $ORACLE_HOME/bin/lsnrctl < $TMP_FILE

elif [ $EXEC_USER = "$ORACLE_USER" ]; then

$ORACLE_HOME/bin/lsnrctl < $TMP_FILE

fi
}

ROTATE_LOG(){
if [ -e /tmp/lsntmp ] ; then
NUM=`cat /tmp/lsntmp`
if [ $ROTATE -eq $NUM ] ;then
NUM="0"
echo $NUM > /tmp/lsntmp
else
NUM=`echo "$NUM + 1" |bc`
echo $NUM > /tmp/lsntmp
fi
else
echo "0" > /tmp/lsntmp
NUM="0"
fi
}

case $ROTATE_FILE in
[Nn]|[Nn][Oo])
echo "set current_listener LISTENER" > $TMP_FILE
echo "set log_file listener" >> $TMP_FILE
echo "exit" >> $TMP_FILE
CLEAN_LOG
;;
[Yy]|[Yy][Ee][Ss])
ROTATE_LOG
echo "set current_listener LISTENER" > $TMP_FILE
echo "set log_file listener.$NUM" >> $TMP_FILE
echo "exit" >> $TMP_FILE
CLEAN_LOG
;;
esac


Oracle Enterprise Manager 按鈕中文方塊字處理

有2個方法處理這問題，一個是直接使用英文介面，另一個就是下列的方式
網路上搜尋結果都是這樣說，那就是試試看果然是可以的。
以前使用OEM從來沒想過要處理這問題因為有英文介面可以用

1.先停止OEM
emctl stop dbconsole

2.替換字型以下3個目錄中的字型
cd ＄ORACLE_HOME/jdk/jre/lib
cp font.properties.zh_TW.Redhat font.properties

cd ＄ORACLE_HOME/jre/1.4.2/lib
cp font.properties.zh_TW.Redhat font.properties

cd ＄ORACLE_HOME/javavm/lib/ojvmfonts
cp font.properties.zh_TW.Redhat font.properties

3.清除OEM之前的檔案
cd $ORACLE_HOME/oc4j/j2ee/oc4j_applications/applications/em/em/cabo/images/cache/zht
rm -f *

4.重新啟動OEM
emctl start dbconsole

ORACLE ASM 問題

環境
2.6.9-78.ELsmp

asm 套件
oracleasm-2.6.9-78.EL-2.0.5-1.el4.i686.rpm
oracleasm-2.6.9-78.EL-debuginfo-2.0.5-1.el4.i686.rpm
oracleasm-2.6.9-78.ELsmp-2.0.5-1.el4.i686.rpm
oracleasmlib-2.0.4-1.el4.i386.rpm
oracleasm-support-2.1.3-1.el4.i386.rpm

執行 /etc/rc.d/init.d/oracleasm configure 發生一個錯誤
Initializing the OracleAsmlib driver: [FAILED]
在log中發現 FATAL: Module oracleasm not found
我就想說又來了！！每次做ASM都是碰到版本問題，剛好在OTN說明中看到oracleasm update-driver這參數
使用後！！搞定！

oracle 常用指令

轉貼：文章來源 http://www.strongd.net/blog/show/316

oracle里的常用命令

第一章：日志管理

1.forcing log switches
sql> alter system switch logfile;

2.forcing checkpoints
sql> alter system checkpoint;

3.adding online redo log groups
sql> alter database add logfile [group 4]
sql> (`/disk3/log4a.rdo`,`/disk4/log4b.rdo`) size 1m;

4.adding online redo log members
sql> alter database add logfile member
sql> `/disk3/log1b.rdo` to group 1,
sql> `/disk4/log2b.rdo` to group 2;

5.changes the name of the online redo logfile
sql> alter database rename file `c:/oracle/oradata/oradb/redo01.log`
sql> to `c:/oracle/oradata/redo01.log`;

6.drop online redo log groups
sql> alter database drop logfile group 3;

7.drop online redo log members
sql> alter database drop logfile member `c:/oracle/oradata/redo01.log`;

8.clearing online redo log files
sql> alter database clear [unarchived] logfile `c:/oracle/log2a.rdo`;

9.using logminer analyzing redo logfiles

a. in the init.ora specify utl_file_dir = ` `
b. sql> execute dbms_logmnr_d.build(`oradb.ora`,`c:\oracle\oradb\log`);
c. sql> execute dbms_logmnr_add_logfile(`c:\oracle\oradata\oradb\redo01.log`,
sql> dbms_logmnr.new);
d. sql> execute dbms_logmnr.add_logfile(`c:\oracle\oradata\oradb\redo02.log`,
sql> dbms_logmnr.addfile);
e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>`c:\oracle\oradb\log\oradb.ora`);
f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
sql> v$logmnr_logs);
g. sql> execute dbms_logmnr.end_logmnr;

第二章：表空间管理
1.create tablespaces
sql> create tablespace tablespace_name datafile `c:\oracle\oradata\file1.dbf` size 100m,
sql> `c:\oracle\oradata\file2.dbf` size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql> [online/offline] [permanent/temporary] [extent_management_clause]

2.locally managed tablespace
sql> create tablespace user_data datafile `c:\oracle\oradata\user_data01.dbf`
sql> size 500m extent management local uniform size 10m;

3.temporary tablespace
sql> create temporary tablespace temp tempfile `c:\oracle\oradata\temp01.dbf`
sql> size 500m extent management local uniform size 10m;

4.change the storage setting
sql> alter tablespace app_data minimum extent 2m;
sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);

5.taking tablespace offline or online
sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;

6.read_only tablespace
sql> alter tablespace app_data read only|write;

7.droping tablespace
sql> drop tablespace app_data including contents;

8.enableing automatic extension of data files
sql> alter tablespace app_data add datafile `c:\oracle\oradata\app_data01.dbf` size 200m
sql> autoextend on next 10m maxsize 500m;

9.change the size fo data files manually
sql> alter database datafile `c:\oracle\oradata\app_data.dbf` resize 200m;

10.Moving data files: alter tablespace
sql> alter tablespace app_data rename datafile `c:\oracle\oradata\app_data.dbf`
sql> to `c:\oracle\app_data.dbf`;

11.moving data files:alter database
sql> alter database rename file `c:\oracle\oradata\app_data.dbf`
sql> to `c:\oracle\app_data.dbf`;

第三章：表

1.create a table
sql> create table table_name (column datatype,column datatype]....)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [initrans integer] [maxtrans integer]
sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql> [logging|nologging] [cache|nocache]

2.copy an existing table
sql> create table table_name [logging|nologging] as subquery

3.create temporary table
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows

4.pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)

5.change storage and block utilization parameter
sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);

6.manually allocating extents
sql> alter table table_name allocate extent(size 500k datafile `c:/oracle/data.dbf`);

7.move tablespace
sql> alter table employee move tablespace users;

8.deallocate of unused space
sql> alter table table_name deallocate unused [keep integer]

9.truncate a table
sql> truncate table table_name;

10.drop a table
sql> drop table table_name [cascade constraints];

11.drop a column
sql> alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;

12.mark a column as unused
sql> alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint 1000;
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs

第四章：索引

1.creating function-based indexes
sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);

2.create a B-tree index
sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace
sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]
sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0
sql> maxextents 50);

3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows

4.creating reverse key indexes
sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k
sql> next 200k pctincrease 0 maxextents 50) tablespace indx;

5.create bitmap index
sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k
sql> pctincrease 0 maxextents 50) tablespace indx;

6.change storage parameter of index
sql> alter index xay_id storage (next 400k maxextents 100);

7.allocating index space
sql> alter index xay_id allocate extent(size 200k datafile `c:/oracle/index.dbf`);

8.alter index xay_id deallocate unused;

第五章：约束

1.define constraints as immediate or deferred
sql> alter session set constraint[s] = immediate/deferred/default;
set constraint[s] constraint_name/all immediate/deferred;

2. sql> drop table table_name cascade constraints
sql> drop tablespace tablespace_name including contents cascade constraints

3. define constraints while create a table
sql> create table xay(id number(7) constraint xay_id primary key deferrable
sql> using index storage(initial 100k next 100k) tablespace indx);
primary key/unique/references table(column)/check

4.enable constraints
sql> alter table xay enable novalidate constraint xay_id;

5.enable constraints
sql> alter table xay enable validate constraint xay_id;

第六章：LOAD数据

1.loading data using direct_load insert
sql> insert /*+append */ into emp nologging
sql> select * from emp_old;

2.parallel direct-load insert
sql> alter session enable parallel dml;
sql> insert /*+parallel(emp,2) */ into emp nologging
sql> select * from emp_old;

3.using sql*loader
sql> sqlldr scott/tiger \
sql> control = ulcase6.ctl \
sql> log = ulcase6.log direct=true

第七章：reorganizing data

1.using expoty
$exp scott/tiger tables(dept,emp) file=c:\emp.dmp log=exp.log compress=n direct=y

2.using import
$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y

3.transporting a tablespace
sql>alter tablespace sales_ts read only;
$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts
triggers=n constraints=n
$copy datafile
$imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2
/sles02.dbf)
sql> alter tablespace sales_ts read write;

4.checking transport set
sql> DBMS_tts.transport_set_check(ts_list =>`sales_ts` ..,incl_constraints=>true);
在表transport_set_violations 中查看
sql> dbms_tts.isselfcontained 为true 是， 表示自包含

第八章： managing password security and resources

1.controlling account lock and password
sql> alter user juncky identified by oracle account unlock;

2.user_provided password function
sql> function_name(userid in varchar2(30),password in varchar2(30),
old_password in varchar2(30)) return boolean

3.create a profile : password setting
sql> create profile grace_5 limit failed_login_attempts 3
sql> password_lock_time unlimited password_life_time 30
sql>password_reuse_time 30 password_verify_function verify_function
sql> password_grace_time 5;

4.altering a profile
sql> alter profile default failed_login_attempts 3
sql> password_life_time 60 password_grace_time 10;

5.drop a profile
sql> drop profile grace_5 [cascade];

6.create a profile : resource limit
sql> create profile developer_prof limit sessions_per_user 2
sql> cpu_per_session 10000 idle_time 60 connect_time 480;

7. view => resource_cost : alter resource cost
dba_Users,dba_profiles

8. enable resource limits
sql> alter system set resource_limit=true;

第九章：Managing users

1.create a user: database authentication
sql> create user juncky identified by oracle default tablespace users
sql> temporary tablespace temp quota 10m on data password expire
sql> [account lock|unlock] [profile profilename|default];

2.change user quota on tablespace
sql> alter user juncky quota 0 on users;

3.drop a user
sql> drop user juncky [cascade];

4. monitor user
view: dba_users , dba_ts_quotas

第十章：managing privileges

1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs

2.grant system privilege
sql> grant create session,create table to managers;
sql> grant create session to scott with admin option;
with admin option can grant or revoke privilege from any user or role;

3.sysdba and sysoper privileges:
sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,
alter tablespace begin/end backup,recover database
alter database archivelog,restricted session
sysdba: sysoper privileges with admin option,create database,recover database until

4.password file members: view:=> v$pwfile_users

5.O7_dictionary_accessibility =true restriction access to view or tables in other schema

6.revoke system privilege
sql> revoke create table from karen;
sql> revoke create session from scott;

7.grant object privilege
sql> grant execute on dbms_pipe to public;
sql> grant update(first_name,salary) on employee to karen with grant option;

8.display object privilege : view => dba_tab_privs, dba_col_privs

9.revoke object privilege
sql> revoke execute on dbms_pipe from scott [cascade constraints];

10.audit record view :=> sys.aud$

11. protecting the audit trail
sql> audit delete on sys.aud$ by access;

12.statement auditing
sql> audit user;

13.privilege auditing
sql> audit select any table by summit by access;

14.schema object auditing
sql> audit lock on summit.employee by access whenever successful;

15.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts

16.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement

第十一章： manager role

1.create roles
sql> create role sales_clerk;
sql> create role hr_clerk identified by bonus;
sql> create role hr_manager identified externally;

2.modify role
sql> alter role sales_clerk identified by commission;
sql> alter role hr_clerk identified externally;
sql> alter role hr_manager not identified;

3.assigning roles
sql> grant sales_clerk to scott;
sql> grant hr_clerk to hr_manager;
sql> grant hr_manager to scott with admin option;

4.establish default role
sql> alter user scott default role hr_clerk,sales_clerk;
sql> alter user scott default role all;
sql> alter user scott default role all except hr_clerk;
sql> alter user scott default role none;

5.enable and disable roles
sql> set role hr_clerk;
sql> set role sales_clerk identified by commission;
sql> set role all except sales_clerk;
sql> set role none;

6.remove role from user
sql> revoke sales_clerk from scott;
sql> revoke hr_manager from public;

7.remove role
sql> drop role hr_manager;

8.display role information
view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles

第十二章: BACKUP and RECOVERY

1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat

2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size

3. Monitoring Parallel Rollback
> v$fast_start_servers , v$fast_start_transactions

4.perform a closed database backup (noarchivelog)
> shutdown immediate
> cp files /backup/
> startup

5.restore to a different location
> connect system/manager as sysdba
> startup mount
> alter database rename file `/disk1/../user.dbf` to `/disk2/../user.dbf`;
> alter database open;

6.recover syntax
--recover a mounted database
>recover database;
>recover datafile `/disk1/data/df2.dbf`;
>alter database recover database;
--recover an opened database
>recover tablespace user_data;
>recover datafile 2;
>alter database recover datafile 2;

7.how to apply redo log files automatically
>set autorecovery on
>recover automatic datafile 4;

8.complete recovery:
--method 1(mounted databae)
>copy c:\backup\user.dbf c:\oradata\user.dbf
>startup mount
>recover datafile `c:\oradata\user.dbf;
>alter database open;
--method 2(opened database,initially opened,not system or rollback datafile)
>copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline)
>recover datafile `c:\oradata\user.dbf` or
>recover tablespace user_data;
>alter database datafile `c:\oradata\user.dbf` online or
>alter tablespace user_data online;
--method 3(opened database,initially closed not system or rollback datafile)
>startup mount
>alter database datafile `c:\oradata\user.dbf` offline;
>alter database open
>copy c:\backup\user.dbf d:\oradata\user.dbf
>alter database rename file `c:\oradata\user.dbf` to `d:\oradata\user.dbf`
>recover datafile `e:\oradata\user.dbf` or recover tablespace user_data;
>alter tablespace user_data online;
--method 4(loss of data file with no backup and have all archive log)
>alter tablespace user_data offline immediate;
>alter database create datafile `d:\oradata\user.dbf` as `c:\oradata\user.dbf``
>recover tablespace user_data;
>alter tablespace user_data online
5.perform an open database backup
> alter tablespace user_data begin backup;
> copy files /backup/
> alter database datafile `/c:/../data.dbf` end backup;
> alter system switch logfile;
6.backup a control file
> alter database backup controlfile to `control1.bkp`;
> alter database backup controlfile to trace;
7.recovery (noarchivelog mode)
> shutdown abort
> cp files
> startup
8.recovery of file in backup mode
>alter database datafile 2 end backup;

9.clearing redo log file
>alter database clear unarchived logfile group 1;
>alter database clear unarchived logfile group 1 unrecoverable datafile;

10.redo log recovery
>alter database add logfile group 3 `c:\oradata\redo03.log` size 1000k;
>alter database drop logfile group 1;
>alter database open;
or >cp c:\oradata\redo02.log` c:\oradata\redo01.log
>alter database clear logfile `c:\oradata\log01.log`;

oracle 10G install for Redhat AS4

oracle 10G install
#### OS Redhat AS4 update1####
uname -a

Linux dbtest 2.6.9-11.ELsmp #1 SMP Fri May 20 18:26:27 EDT 2005 i686 i686 i386 GNU/Linux

############ check sofware packages

binutils-2.15.92.0.2-10.EL4
compat-db-4.1.25-9
control-center-2.8.0-12
gcc-3.4.3-9.EL4
gcc-c++-3.4.3-9.EL4
glibc-2.3.4-2
glibc-common-2.3.4-2
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.3-9.EL4
libstdc++-devel-3.4.3-9.EL4
make-3.80-5
pdksh-5.2.14-30
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.2


rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' \
binutils compat-db control-center gcc gcc-c++ glibc glibc-common gnome-libs \
libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio

####### kernal parameter
vi /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem=250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range=1024 65000
net.core.rmem_max = 1048576
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

/sbin/sysctl -p

#### create user

groupadd dba
groupadd oinstall
useradd -g oinstall -G dba -d /oracle oracle
passwd oracle

chown -R oracle.oinstall /oracle
##############
vi /etc/security/limits.conf

* soft nproc 2047
* hade nproc 16384
* soft nofile 1024
* hade nofile 65536

vi /etc/pam.d/login

session required /lib/security/pam_limits.so
session required pam_limits.so

vi /etc/profile
if [ $USER = "oracle" ];then
if [ $SHELL = "/bin/bash" ];then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

vi /oracle/.bash_profile

umask 022
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2
export ORACLE_SID=ora10g
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

source ./.bash_profile


######### install ASM
rpm -Uvh oracleasm-support-2.0.3-1.i386.rpm
rpm -Uvh oracleasm-2.6.9-11.EL-2.0.3-1.i686.rpm
rpm -Uvh oracleasmlib-2.0.2-1.i386.rpm
rpm -Uvh oracleasm-2.6.9-11.ELsmp-2.0.3-1.i686.rpm


########## config ASM
/etc/rc.d/init.d/oracleasm configure



Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y



#### create ASM disk

/etc/rc.d/init.d/oracleasm createdisk VL01 /dev/hda10
Marking disk "/dev/hda10" as an ASM disk: [ OK ]
/etc/rc.d/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
/etc/rc.d/init.d/oracleasm listdisks
VL01

################## install oracle
unzip 10201_database_linux32.zip
cd /database

./runInstaller





############ ASM managment

ORACLE_SID=+ASM
export $ORACLE_SID
asmcmd
ASMCMD> help
asmcmd [-p] [command]

The environment variables ORACLE_HOME and ORACLE_SID determine the
instance to which the program connects, and ASMCMD establishes a
bequeath connection to it, in the same manner as a SQLPLUS / AS
SYSDBA. The user must be a member of the SYSDBA group.

Specifying the -p option allows the current directory to be displayed
in the command prompt, like so:

ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >

[command] specifies one of the following commands, along with its
parameters.

Type "help [command]" to get help on a specific ASMCMD command.

commands:
--------
cd
du
find
help
ls
lsct
lsdg
mkalias
mkdir
pwd
rm
rmalias
ASMCMD>



#### db console
emctl start console


http://x.x.x.x:1158/em

ANALYZ TABLE

以前寫的的！！當然還有其他的語法可以執行analyz table

CREATE OR REPLACE PROCEDURE DB_RPT_ANALYZ_TABLE

AS

V_TABLE_NAME VARCHAR2(100);

SQL_STR VARCHAR2(500);

CURSOR USER_TABLE1 IS

SELECT TABLE_NAME FROM USER_TABLES;

BEGIN

OPEN USER_TABLE1;

LOOP

FETCH USER_TABLE1 INTO V_TABLE_NAME;

EXIT WHEN USER_TABLE1%NOTFOUND;

SQL_STR:= 'ANALYZE TABLE '''''V_TABLE_NAME''''' compute statistics';

EXECUTE IMMEDIATE SQL_STR;

END LOOP;

CLOSE USER_TABLE1;

END;

Oracle Patch Set SOP

這是別人整理好的＠＠
Oracle Database Install Patch Set Software

1.rman full backup database

2.Shut Down Oracle Databases
SQL>shutdown immediate;

3. Stop All Processes

$lsnrctl stop

4. Back Up the /oracle mount point

v890-1 & v890-2
$tar -cvf oracle_9206.tar /oracle

5. Install patch set 9.2.0.8
$ patchset_directory/Disk1
$ ./runInstaller

6. Upgrade the Database
Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
$ sqlplus "/ as sysdba"
SQL> STARTUP MIGRATE;
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

7. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.

This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catpatch.sql script after correcting any problems.

8. Restart the database:

SQL> SHUTDOWN
SQL> STARTUP


9. Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.

SQL> @?/rdbms/admin/utlrp.sql

確認資料褲版本
select instance_name,status,version,host_name from v$instance;

10. Start Stop All Processes

$lsnrctl start

select instance_name,status,version,host_name from v$instance;

11.AP test

?
Oracle Database Removing the Patch Set Software

Starting in 9.2.0.7.0, there is a new script called catbkout.sql. This script enables the user to restore the Oracle9i installation of the database back to the original 9.2 patch release (as long as it is 9.2.0.4 or greater) that the user backed up before applying the patch set. Perform the following steps for backing out from the 9.2.0.8 patch release:

1. Before restoring the Oracle home to the original release, perform the following steps:

1. Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:

$ sqlplus "/ as sysdba"

2. Exit SQL*Plus and shut down the database:

SQL> SHUTDOWN
SQL> STARTUP MIGRATE
SQL> SPOOL catbkout.out
SQL> @?/rdbms/admin/catbkout.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE


2. Restore the Oracle9i installation and Inventory (see /var/opt/oracle/oraInst.loc) that you backed up before applying the patch set.
After restoring the original Oracle home, perform the following steps:

$ sqlplus /nolog
SQL> CONNECT SYS/SYS_password AS SYSDBA
SQL> STARTUP MIGRATE
SQL> SPOOL catpatch.out
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

3. Shut down the database:

SQL> SHUTDOWN IMMEDIATE


4. Start the database and recompile the remaining invalid objects:
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql


5. Review catbkout.out and catpatch.out for errors.

6. Restart the database:

SQL> SHUTDOWN
SQL> STARTUP

oracle error

###dbca #####
dbca:line 124:3373 segmentation fault $JRE_DIR/bun/jre -DORACLE_HOME=$OH_DJDBC_PROTOCOL=thin _mx64m_ classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

solution:
export THREADS_FLAG=native

####
oracle/jre/1.1.8/bin/../lib/i686/green_threads/libzip.so: symbol errno, version GLIBC_2.0 not defined in file libc.so.6 with link time reference (libzip.so) Unable to initialize threads: cannot find class java/lang/Thread Could not create Java VM

solution:
export LD_ASSUME_KERNEL=2.4.1

查詢oracle characterset

SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';

oracle 計算 table spaces

SELECT a.tablespace_name, a.file_name,(a.bytes/1024/1024) "allocated(MB)", ((a.bytes-b.free_bytes)/1024/1024) "Used Space(MB)",(b.free_bytes/1024/1024) "Free(MB)",round(((b.free_bytes/a.bytes)*100),3) '%' "Free Percentage"FROM dba_data_files a,(SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) bWHERE a.file_id=b.file_idORDER BY a.tablespace_name;

How to change password in Oracle

alter user 'user_name' identified by 'password';

oracle /PHP4.42 install

bzip: bzip2-1.0.3.tar.gz(http://www.bzip.org/1.0.3/bzip2-1.0.3.tar.gz)
make -f Makefile-libbz2_somake clean
make install
############################
libpng-1.2.18
http://www.libpng.org/pub/png/libpng.html
cp scripts/makefile.linux makefile make test make install ###################################
libjpeg: jpegsrc.v6b.tar.gz(http://www.ijg.org/files/jpegsrc.v6b.tar.gz)http://www.ijg.org/
Try doing ./configure make make testIf that doesn't complain, do make install #################################
gettext: gettext-0.13.tar.gz
(ftp://ftp.nctu.edu.tw/UNIX/gnu/gettext/gettext-0.16.tar.gz)http://tw.php.net/manual/en/ref.gettext.phphttp://www.gnu.org/software/gettext/
./configuremake make install
###################
freetype2: freetype-2.1.10.tar.gz
(ftp:// sourceforge.nchc.org.tw/f/fr/freetype/freetype-2.1.10.tar.gz)http://www.freetype.org/index2.html
./configuremake make install
###########################
libxml: libxml2-2.6.23.tar.gz
(http://xmlsoft.org/sources/libxml2-2.6.23.tar.gz)http://tw.php.net/manual/en/ref.libxml.phphttp://xmlsoft.org/
./configuremake make install
#############################
libcurl: curl-7.15.1.tar.gz
(http://curl.haxx.se/download/curl-7.15.1.tar.gz)
http://tw2.php.net/curlhttp://curl.haxx.se/

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

openssl

./config --prefix=/usr/local --openssldir=/usr/local/opensslmake make install
###########################
mysql
./configure --prefix=/usr/local/mysql5 --with-charset=big5 --with-extra-charsets=all
make && make install
useradd -s /bin/false mysql
cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql5bin/
mysql_install_db --user=mysql/usr/local/mysql5/bin/mysqladmin -u root password 'ssssss' chown -Rf mysql var; chgrp -Rf
#start mysql
mysql /usr/local/mysql5/bin/mysqld_safe --user=mysql &
#########################
httpd
./configure --prefix=/usr/local/httpd-2.2.4 \--with-mpm=prefork \--enable-cgi \--enable-dav \--enable-module=so \--enable-vhost-alias \--enable-so \--enable-ssl \--with-ssl=/usr/local/openssl \--with-z=/usr/local \--enable-http \--enable-rewrite \--enable-modules \--enable-logio \--enable-static-rotatelogs \--enable-static-logresolve \--enable-log-config \--enable-log-forensic \--enable-cache \--enable-headers \--enable-mime-magic \--enable-cern-meta \--enable-suexec \--enable-unique-id \--enable-deflate \--enable-usertrack #############################
OCI
rpm -ivh

oracle-instantclient-devel-10.2.0.3-1.i386.rpmoracle-instantclient-basic-10.2.0.3-1.i386.rpm oracle-instantclient-sqlplus-10.2.0.3-1.i386.rpm
############################
ORACLE_HOME=/oracle/product/9.2.0 ;export $ORACLE_HOMELD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/usr/lib/oracle/10.1.0.3/client/lib ;export $LD_LIBRARY_PATH

rm -rf autom4te.cache config.cache
./buildconf --force
cp *.h ../../main/cp *.c ../../main/
vi oci.h
#define OCI_NLS_CHARSET_ID 0

./configure --prefix=/usr/local/php4 \--with-apxs2=/usr/local/httpd-2.2.4/bin/apxs \--with-zlib \--with-mysql=/usr/local/mysql5 \--with-oci8=/oracle/product/9.2.0 \--with-oci8-instant-client=/usr/lib/oracle/10.1.0.3/client/lib \--enable-sigchild \--with-config-file-path=/usr/local/httpd-2.2.4/conf/ \


make;make install
cp php.ini-dist /usr/local/httpd-2.2.4/conf/php.ini

joe /usr/local/httpd-2.2.4/bin/apachectl LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/10.2.0.3/client/lib ;export LD_LIBRARY_PATHTNS_ADMIN=/oracle/product/9.2.0/network/admin/ ;export TNS_ADMINNLS_LANG=AMERICAN_AMERICA.ZHT16BIG5 ;export $NLS_LANG

centOS 安裝oracle

vi /etc/redhat-release


CentOS release 4.3 (Final) 修改成 redhat-3

kernel parameter for Tru64

vi /etc/syscontigtab

ipc:
shm-max=471859200
sem-mni=32
sem-msl=50
sem-opm=20
sem-ume=20
sem-vmx=327670
sem-aem=81920
sem-seg=128
sem-threshold=67108864
sem-min=1
semni=100

proc:
max-proc-per-user=2048
maxusers=1024