admin – 第99页 – Database SOS

Disable/Enable Oracle Database Vault

一、Disable Oracle Database Vault

[oracle@node1 ~]$ sqlplus sys/xifenfei@ora11g as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Fri Nov 4 09:09:00 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> col parameter for a30

SQL> col value for a10

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER VALUE

—————————— ———-

Oracle Database Vault TRUE

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ emctl stop dbconsole

[oracle@node1 ~]$ lsnrctl stop

[oracle@node1 ~]$ cd $ORACLE_HOME/rdbms/lib

[oracle@node1 lib]$ pwd

/opt/oracle/product/11.2.0/db_1/rdbms/lib

[oracle@node1 lib]$ make -f ins_rdbms.mk dv_off ioracle

/usr/bin/ar d /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a kzvidv.o

/usr/bin/ar cr /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a /opt/oracle/product/11.2.0/db_1/rdbms/lib/kzvndv.o

chmod 755 /opt/oracle/product/11.2.0/db_1/bin

– Linking Oracle

rm -f /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle

gcc -o /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle -m64 -L/opt/oracle/product/11.2.0/db_1/rdbms/lib/ -L/opt/oracle/product/11.2.0/db_1/lib/ -L/opt/oracle/product/11.2.0/db_1/lib/stubs/ -Wl,-E /opt/oracle/product/11.2.0/db_1/rdbms/lib/opimai.o /opt/oracle/product/11.2.0/db_1/rdbms/lib/ssoraed.o /opt/oracle/product/11.2.0/db_1/rdbms/lib/ttcsoi.o -Wl,–whole-archive -lperfsrv11 -Wl,–no-whole-archive /opt/oracle/product/11.2.0/db_1/lib/nautab.o /opt/oracle/product/11.2.0/db_1/lib/naeet.o /opt/oracle/product/11.2.0/db_1/lib/naect.o /opt/oracle/product/11.2.0/db_1/lib/naedhs.o /opt/oracle/product/11.2.0/db_1/rdbms/lib/config.o -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11 -lrt -lplp11 -lserver11 -lclient11 -lvsn11 -lcommon11 -lgeneric11 `if [ -f /opt/oracle/product/11.2.0/db_1/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /opt/oracle/product/11.2.0/db_1/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11 -lrt -lplp11 -ljavavm11 -lserver11 -lwwg `cat /opt/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /opt/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lmm -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /opt/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /opt/oracle/product/11.2.0/db_1/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/opt/oracle/product/11.2.0/db_1/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio `cat /opt/oracle/product/11.2.0/db_1/lib/sysliblist` -Wl,-rpath,/opt/oracle/product/11.2.0/db_1/lib -lm `cat /opt/oracle/product/11.2.0/db_1/lib/sysliblist` -ldl -lm -L/opt/oracle/product/11.2.0/db_1/lib

test ! -f /opt/oracle/product/11.2.0/db_1/bin/oracle ||\

mv -f /opt/oracle/product/11.2.0/db_1/bin/oracle /opt/oracle/product/11.2.0/db_1/bin/oracleO

mv /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle /opt/oracle/product/11.2.0/db_1/bin/oracle

chmod 6751 /opt/oracle/product/11.2.0/db_1/bin/oracle

[oracle@node1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 4 09:21:39 2011

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2230072 bytes

Variable Size 1241516232 bytes

Database Buffers 889192448 bytes

Redo Buffers 4947968 bytes

Database mounted.

Database opened.

SQL> col parameter for a30

SQL> col value for a10

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER VALUE

—————————— ———-

Oracle Database Vault FALSE

二、Enable Oracle Database Vault

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 4 12:50:40 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

[oracle@node1 ~]$ cd $ORACLE_HOME/rdbms/lib

[oracle@node1 lib]$ make -f ins_rdbms.mk dv_on lbac_on ioracle

/usr/bin/ar d /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a kzvndv.o

/usr/bin/ar cr /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a /opt/oracle/product/11.2.0/db_1/rdbms/lib/kzvidv.o

/usr/bin/ar cr /opt/oracle/product/11.2.0/db_1/rdbms/lib/libknlopt.a /opt/oracle/product/11.2.0/db_1/rdbms/lib/kzlilbac.o

chmod 755 /opt/oracle/product/11.2.0/db_1/bin

– Linking Oracle

rm -f /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle

test ! -f /opt/oracle/product/11.2.0/db_1/bin/oracle ||\

mv -f /opt/oracle/product/11.2.0/db_1/bin/oracle /opt/oracle/product/11.2.0/db_1/bin/oracleO

mv /opt/oracle/product/11.2.0/db_1/rdbms/lib/oracle /opt/oracle/product/11.2.0/db_1/bin/oracle

chmod 6751 /opt/oracle/product/11.2.0/db_1/bin/oracle

[oracle@node1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 4 12:52:51 2011

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 622149632 bytes

Fixed Size 2230912 bytes

Variable Size 201328000 bytes

Database Buffers 411041792 bytes

Redo Buffers 7548928 bytes

Database mounted.

Database opened.

SQL> col parameter for a30

SQL> col value for a10

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER VALUE

—————————— ———-

Oracle Database Vault TRUE

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@node1 lib]$ lsnrctl start

[oracle@node1 lib]$ emctl start dbconsole

三、Oracle Database Vault启用关闭总机
1、关闭实例/EM/监听
2、修改Oracle Database Vault
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle –关闭
make -f ins_rdbms.mk dv_on lbac_on ioracle –开启
3、开启实例/EM/监听

Linux之pmap命令

观察数据库发现，ora_dbw进程占用内存非常高，感觉很奇怪，然后询问高手，通过pmap命令发现，该进程是几乎具有了整个sga的句柄，所以显示占用的内存非常大，其实自己私有的很小

[oracle@DB1 ~]$ top -c

top – 23:06:02 up 553 days, 21:46, 1 user, load average: 0.62, 0.58, 0.47

Tasks: 365 total, 1 running, 364 sleeping, 0 stopped, 0 zombie

Cpu(s): 0.7%us, 0.3%sy, 0.0%ni, 98.3%id, 0.5%wa, 0.0%hi, 0.2%si, 0.0%st

Mem: 24666528k total, 24504356k used, 162172k free, 9192k buffers

Swap: 24579440k total, 3864056k used, 20715384k free, 18728188k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

2270 oracle 15 -1 20.1g 1.1g 1.1g S 2.7 4.7 147:10.01 oracleora9i (LOCAL=NO)

4334 oracle 14 -1 20.1g 1.7g 1.7g S 1.3 7.4 3:08.38 oracleora9i (LOCAL=NO)

5491 oracle 15 -1 20.2g 14g 14g S 1.3 62.1 699:54.55 ora_dbw1_ora9i

9682 oracle 15 0 12868 1296 820 R 1.3 0.0 0:00.14 top -c

18552 oracle 14 -1 20.1g 1.7g 1.7g S 1.3 7.2 2:43.74 oracleora9i (LOCAL=NO)

29977 oracle 14 -1 20.1g 2.1g 2.1g S 1.3 9.1 4:50.87 oracleora9i (LOCAL=NO)

SQL> show parameter sga;

NAME TYPE VALUE

———————————— ———– ——————————

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 20G

sga_target big integer 20G

[oracle@DB1 ~]$ pmap

Usage: pmap [-x | -d] [-q] pid…

-x show details

-d show offset and device number

-q quiet; less header/footer info

-V show the version number

[oracle@DB1 ~]$ pmap -d 5491

5491: ora_dbw1_ora9i

Address Kbytes Mode Offset Device Mapping

0000000000400000 100412 r-x– 0000000000000000 008:00005 oracle

000000000680f000 544 rwx– 000000000620f000 008:00005 oracle

0000000006897000 148 rwx– 0000000006897000 000:00000 [ anon ]

000000001f2f7000 596 rwx– 000000001f2f7000 000:00000 [ anon ]

0000000060000000 20971520 rwxs- 0000000000000000 000:00009 [ shmid=0x0 ]

0000000580000000 2048 rwxs- 0000000000000000 000:00009 [ shmid=0x8001 ]

0000003b44a00000 112 r-x– 0000000000000000 008:00001 ld-2.5.so

……

00002b293c72b000 64 rwx– 0000000000000000 000:00011 zero

00002b293c73b000 64 rwx– 0000000000000000 000:00011 zero

00002b293c74b000 64 rwx– 0000000000000000 000:00011 zero

00002b293c75b000 3840 —– 0000000000b2a000 000:00011 zero

00007fff710f7000 84 rwx– 00007ffffffea000 000:00000 [ stack ]

ffffffffff600000 8192 —– 0000000000000000 000:00000 [ anon ]

mapped: 21145976K writeable/private: 19316K shared: 20973572K

[oracle@DB1 ~]$ pmap -x 5491

5491: ora_dbw1_ora9i

Address Kbytes RSS Anon Locked Mode Mapping

0000000000400000 100412 – – – r-x– oracle

000000000680f000 544 – – – rwx– oracle

0000000006897000 148 – – – rwx– [ anon ]

000000001f2f7000 596 – – – rwx– [ anon ]

0000000060000000 20971520 – – – rwxs- [ shmid=0x0 ]

0000000580000000 2048 – – – rwxs- [ shmid=0x8001 ]

……

00002b293c73b000 64 – – – rwx– zero

00002b293c74b000 64 – – – rwx– zero

00002b293c75b000 3840 – – – —– zero

00007fff710f7000 84 – – – rwx– [ stack ]

ffffffffff600000 8192 – – – —– [ anon ]

—————- —— —— —— ——

total kB 21145976 – – –

Address:进程所占的地址空间

Kbytes 该虚拟段的大小

RSS 设备号（主设备：次设备）

Anon 设备的节点号，0表示没有节点与内存相对应

Locked 是否允许swapped

Mode 权限：r=read, w=write, x=execute, s=shared, p=private(copy on write)

Mapping: bash 对应的映像文件名

10.2.0.x升级到10.2.0.5.5

一、数据库10.2.0.x升级到10.2.0.5.5步骤

1、升级数据库到10.2.0.5.0

执行8202632升级补丁中的./runInstaller

执行dbua

2、升级opatch

使用6880880补丁替换原$ORACLE_HOME/OPatch目录

3、升级数据库到10.2.0.5.5

使用补丁12827745，执行$ORACLE_HOME/OPatch/opatch apply ./12827745

执行数据库升级

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catbundle.sql psu apply

SQL> — Execute the next statement only if this is the first
PSU applied for 10.2.0.5 or this is the first PSU applied since 10.2.0.5.3.

SQL> @utlrp.sql

SQL> QUIT

二、升级过程中出现错误

1、opatch版本过低

严重:OPatch invoked as follows: 'apply ./ '

信息:

Oracle 主目录 : /opt/oracle/product/10.2.0/db_1

主产品清单: /opt/oracle/oraInventory

从 : /etc/oraInst.loc

OPatch 版本 : 10.2.0.4.9

OUI 版本 : 10.2.0.5.0

OUI 位置 : /opt/oracle/product/10.2.0/db_1/oui

日志文件位置 : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/
opatch/opatch2011-11-03_12-43-59下午.log

Patch 12827745 requires OPatch version 10.2.0.5.0.

The OPatch version being used (10.2.0.4.9) doesn't meet the minimum version
required by the patch(es). Please download latest OPatch from My Oracle Support.

信息:系统无任何变化, OPatch 不会尝试还原系统

[oracle@node1 12827745]$ $ORACLE_HOME/OPatch/opatch version

Invoking OPatch 10.2.0.4.9

OPatch Version: 10.2.0.4.9

OPatch succeeded.

处理办法：升级opatch

下载补丁p6880880_102000_Linux-x86-64，备份原来$ORACLE_HOME/OPatch，然后用下载的补丁替换

[oracle@node1 opatch]$ $ORACLE_HOME/OPatch/opatch version

Invoking OPatch 10.2.0.5.1

OPatch Version: 10.2.0.5.1

OPatch succeeded.

2、检测补丁是否冲突

[oracle@node1 12827745]$ $ORACLE_HOME/OPatch/opatch \
>prereq CheckConflictAgainstOHWithDetail -phBaseDir ./

Invoking OPatch 10.2.0.5.1

Oracle 中间补丁程序安装程序版本 10.2.0.5.1

PREREQ session

Oracle 主目录 : /opt/oracle/product/10.2.0/db_1

主产品清单: /opt/oracle/oraInventory

从 : /etc/oraInst.loc

OPatch 版本 : 10.2.0.5.1

OUI 版本 : 10.2.0.5.0

OUI 位置 : /opt/oracle/product/10.2.0/db_1/oui

日志文件位置 : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/
opatch/opatch2011-11-03_13-50-44下午.log

Patch history file: /opt/oracle/product/10.2.0/db_1/
cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

3、CheckActiveFilesAndExecutables failed

[oracle@node1 12827745]$ $ORACLE_HOME/OPatch/opatch apply

Invoking OPatch 10.2.0.5.1

Oracle 中间补丁程序安装程序版本 10.2.0.5.1

Oracle 主目录 : /opt/oracle/product/10.2.0/db_1

主产品清单: /opt/oracle/oraInventory

从 : /etc/oraInst.loc

OPatch 版本 : 10.2.0.5.1

OUI 版本 : 10.2.0.5.0

OUI 位置 : /opt/oracle/product/10.2.0/db_1/oui

日志文件位置 : /opt/oracle/product/10.2.0/db_1/
cfgtoollogs/opatch/opatch2011-11-03_13-39-44下午.log

Patch history file: /opt/oracle/product/10.2.0/db_1/
cfgtoollogs/opatch/opatch_history.txt

ApplySession 将中间补丁程序 '12827745' 应用到 OH '/opt/oracle/product/10.2.0/db_1'

Running prerequisite checks…

Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following executables are active :

/opt/oracle/product/10.2.0/db_1/bin/oracle

在先决条件检查期间 ApplySession 失败:
Prerequisite check "CheckActiveFilesAndExecutables" failed.

系统无任何变化, OPatch 不会尝试还原系统

OPatch failed with error code 74

解决办法：关闭数据库相关程序

[oracle@node1 12827745]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Thu Nov 3 13:52:31 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@node1 ~]$ lsnrctl stop

[oracle@node1 ~]$ emctl stop dbconsole

4、@catbundle.sql psu apply报错

ORA-04045: errors during recompilation/revalidation of IS1OGG.DDLREPLICATION

ORA-04064: not executed, invalidated

ORA-04064: not executed, invalidated package body "IS1OGG.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called: "IS1OGG.DDLREPLICATION"

ORA-06512: at line 870

ORA-04064: not executed, invalidated package body "IS1OGG.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called: "IS1OGG.DDLREPLICATION"

ORA-06512: at line 870

ORA-04045: errors during recompilation/revalidation of IS1OGG.DDLREPLICATION

ORA-04064: not executed, invalidated

ORA-04064: not executed, invalidated package body "IS1OGG.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called: "IS1OGG.DDLREPLICATION"

ORA-06512: at line 870

ORA-04064: not executed, invalidated package body "IS1OGG.DDLREPLICATION"

ORA-06508: PL/SQL: could not find program unit being called: "IS1OGG.DDLREPLICATION"

解决方法：关闭ogg的ddl捕获

因为ddl捕获触发器，导致这些操作失败

ddl_disable.sql

升级完成后，开启ddl捕获触发器

ddl_enable.sql

三、opatch主要命令解释

1、查看升级是否成功

[oracle@node1 ~]$ $ORACLE_HOME/OPatch/opatch lsinv -bugs_fixed|grep PSU

ENCAPSULATED BY EXCEPTION HANDLING

9952230 12827745 Thu Nov 03 13:55:42 CST 2011 DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)

10248542 12827745 Thu Nov 03 13:55:42 CST 2011 DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)

11724962 12827745 Thu Nov 03 13:55:42 CST 2011 DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)

12419392 12827745 Thu Nov 03 13:55:42 CST 2011 DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)

12827745 12827745 Thu Nov 03 13:55:42 CST 2011 DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011)

2、检测补丁是否冲突

[oracle@node1 12827745]$ $ORACLE_HOME/OPatch/opatch prereq \
>CheckConflictAgainstOHWithDetail -phBaseDir ./

Invoking OPatch 10.2.0.5.1

Oracle 中间补丁程序安装程序版本 10.2.0.5.1

PREREQ session

Oracle 主目录 : /opt/oracle/product/10.2.0/db_1

主产品清单: /opt/oracle/oraInventory

从 : /etc/oraInst.loc

OPatch 版本 : 10.2.0.5.1

OUI 版本 : 10.2.0.5.0

OUI 位置 : /opt/oracle/product/10.2.0/db_1/oui

日志文件位置 : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/
opatch2011-11-03_13-50-44下午.log

Patch history file: /opt/oracle/product/10.2.0/db_1/
cfgtoollogs/opatch/opatch_history.txt

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

3、预演补丁实施

opatch apply -report

4、卸载补丁

opatch rollback -id 8350262

块修改跟踪特性(Block Change Tracking)

有一服务器有1T左右的数据，备份策略是(1+2)*2（1全备，2增量备份，备份保留2周期）的备份策略，随便增量备份减少了备份的体积，但是增量备份的时间，基本上和全备无差别，都是要近6小时（包括压缩）。这里没有完全体现出增量备份的强大之处，因为没有开启块修改跟踪，无论是增量备份还是全备都需要扫描所有的数据块。虽然网上说开启块修改跟踪可能会会触发一些bug，但是我找了下，10.2.0.4以后块修改跟踪还是比较稳定的，所以决定开启块修改跟踪功能，节约增量备份时间，提高系统性能。

一、开启块修改跟踪

[oracle@node1 bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Thu Nov 3 11:13:54 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database enable block change tracking using file

2 '/opt/oracle/oradata/ecp/Oracle_change.trace';

Database altered.

SQL> set long 200

SQL> col filename for a50

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace 11599872

SQL> !/opt/oracle/oradata/ecp/Oracle_change.trace

/bin/bash: /opt/oracle/oradata/ecp/Oracle_change.trace: 权限不够

SQL> !

[oracle@node1 ~]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace

-rw-r—– 1 oracle oinstall 11600384 11-03 11:05 /opt/oracle/oradata/ecp/Oracle_change.trace

[oracle@node1 ~]$ ps -ef|grep ctwr|grep -v grep

oracle 2771 1 0 11:05 ? 00:00:00 ora_ctwr_ecp

[oracle@node1 bdump]$ tail -11 /opt/oracle/admin/ecp/bdump/alert_ecp.log

Thu Nov 03 11:05:47 CST 2011

alter database enable block change tracking using file

'/opt/oracle/oradata/ecp/Oracle_change.trace'

Thu Nov 03 11:05:47 CST 2011

Block change tracking file is current.

Starting background process CTWR

CTWR started with pid=18, OS id=2771

Block change tracking service is active.

Thu Nov 03 11:05:48 CST 2011

Completed: alter database enable block change tracking using file

'/opt/oracle/oradata/ecp/Oracle_change.trace'

通过这些都可以看出来，开启块修改跟踪功能，会启动CTWR进程，并且修改的块号会被记录到指定文件中

二、关闭块改变跟踪

SQL> alter database disable block change tracking;

Database altered.

SQL> col filename for a50

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

DISABLED

SQL> !

[oracle@node1 bdump]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace

ls: /opt/oracle/oradata/ecp/Oracle_change.trace: 没有那个文件或目录

[oracle@node1 bdump]$ ps -ef|grep ctwr|grep -v grep

[oracle@node1 bdump]$ tail -8 /opt/oracle/admin/ecp/bdump/alert_ecp.log

Thu Nov 03 11:14:06 CST 2011

alter database disable block change tracking

Thu Nov 03 11:14:07 CST 2011

Block change tracking service stopping.

Thu Nov 03 11:14:07 CST 2011

Stopping background process CTWR

Deleted file /opt/oracle/oradata/ecp/Oracle_change.trace

Completed: alter database disable block change tracking

通过这些都可以看出来，关闭块修改跟踪功能，会关闭CTWR进程，并且删除跟踪文件（Linux系统会删除，Window不会）

三、块修改跟踪文件重命名

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace 11599872

SQL> alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

2 to '/opt/oracle/oradata/ecp/Oracle_change.trace_new';

alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-19771: cannot rename change tracking file while database is open

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size 2096632 bytes

Variable Size 385876488 bytes

Database Buffers 1207959552 bytes

Redo Buffers 14680064 bytes

Database mounted.

SQL> alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

2 to '/opt/oracle/oradata/ecp/Oracle_change.trace_new';

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace_new 11599872

[oracle@node1 bdump]$ tail -100 /opt/oracle/admin/ecp/bdump/alert_ecp.log |more

Thu Nov 03 11:22:34 CST 2011

alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

to '/opt/oracle/oradata/ecp/Oracle_change.trace_new'

Completed: alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

to '/opt/oracle/oradata/ecp/Oracle_change.trace_new'

Thu Nov 03 11:22:40 CST 2011

alter database open

Thu Nov 03 11:22:40 CST 2011

CHANGE TRACKING is enabled for this database, but the

change tracking file can not be found. Recreating the file.

Change tracking file recreated.

Block change tracking file is current.

[oracle@node1 bdump]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace*

-rw-r—– 1 oracle oinstall 11600384 11-03 11:22 /opt/oracle/oradata/ecp/Oracle_change.trace

-rw-r—– 1 oracle oinstall 11600384 11-03 12:05 /opt/oracle/oradata/ecp/Oracle_change.trace_new

数据库在open状态下不能修改，所以必须把数据库重启至mount状态才能够修改，如果库不能重启，那么可以先关闭原块修改跟踪功能，再开启块修改跟踪功能。
两种方法比较：
1、都会重新建立一个跟踪文件
2、如果是重命名，老的跟踪文件不会自动被删除，需要人工删除
3、推荐使用关闭跟踪功能，然后在重新制定跟踪文件开启跟踪功能

同台服务器多版本数据库监听配置

在一台服务器上分别安装有Oracle 10g和Oracle 11g，现在需要对这两个数据库都配置对应的监听，使其能够正常工作，本文对配置方法做了总结，供参考学习

一、动态监听
Oracle 10g listener.ora配置

[oracle@node1 admin]$ more listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = node1.srtcloud.com)(PORT = 1521))

)

Oracle 11g listener.ora配置

[oracle@node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = node1.srtcloud.com)(PORT = 1522))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))

)

ADR_BASE_LISTENER = /opt/oracle

[oracle@node1 admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORA11G =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.140)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = ora11g)

)

SQL> show parameter local_lis

NAME TYPE VALUE

———————————— ———– ——————————

local_listener string ora11g

[oracle@node1 admin]$ ps -ef|grep tns|grep -v grep

oracle 24713 1 0 10:30 ? 00:00:00 /opt/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit

oracle 24720 1 0 10:30 ? 00:00:00 /opt/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

事项说明：
1、10g和11g两个数据库都采用动态监听，但是由于默认情况下，动态注册只会注册1521端口，所以其中一个数据库需要设置local_listener参数，用于监听其他端口
2、10g和11g两个数据库都采用动态监听，会启动两个互不干扰的监听进程

二、静态监听
1）使用一个监听

[oracle@node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = chf)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = chf)

)

(SID_DESC =

(GLOBAL_DBNAME = ecp)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = ecp)

)

(SID_DESC =

(GLOBAL_DBNAME = ora11g)

(ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)

(SID_NAME = ora11g)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =node1.srtcloud.com)(PORT = 1521))

)

[oracle@node1 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-NOV-2011 10:03:44

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start

Starting /opt/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 11.2.0.3.0 – Production

System parameter file is /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /opt/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.srtcloud.com)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-NOV-2011 10:03:45

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1521)))

Services Summary…

Service "chf" has 1 instance(s).

Instance "chf", status UNKNOWN, has 1 handler(s) for this service…

Service "ecp" has 1 instance(s).

Instance "ecp", status UNKNOWN, has 1 handler(s) for this service…

Service "ora11g" has 1 instance(s).

Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.srtcloud.com)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-NOV-2011 10:03:45

Uptime 0 days 0 hr. 3 min. 57 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1521)))

Services Summary…

Service "chf" has 2 instance(s).

Instance "chf", status UNKNOWN, has 1 handler(s) for this service…

Instance "chf", status READY, has 1 handler(s) for this service…

Service "chfXDB" has 1 instance(s).

Instance "chf", status READY, has 1 handler(s) for this service…

Service "chf_XPT" has 1 instance(s).

Instance "chf", status READY, has 1 handler(s) for this service…

Service "ecp" has 2 instance(s).

Instance "ecp", status UNKNOWN, has 1 handler(s) for this service…

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ecpXDB" has 1 instance(s).

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ecp_XPT" has 1 instance(s).

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ora11g" has 1 instance(s).

Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@node1 admin]$ ps -ef|grep tns|grep -v grep

oracle 23602 1 0 10:03 ? 00:00:00 /opt/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit

事项说明：
1、这种方法是采用一个监听实现监听所有实例，只会启动一个监听进程，只监听一个端口
2、实例中显示的为：11g一个实例，10g有两个实例的监听情况
3、这种监听方法可以在10g中配置，也可以在11g中配置，都能够正常工作

2）使用多个监听
Oracle 10g listener.ora配置

[oracle@node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = chf)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = chf)

)

(SID_DESC =

(GLOBAL_DBNAME = ecp)

(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)

(SID_NAME = ecp)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =node1.srtcloud.com)(PORT = 1521))

)

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 01-NOV-2011 10:35:12

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.srtcloud.com)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.4.0 – Production

Start Date 01-NOV-2011 10:30:34

Uptime 0 days 0 hr. 4 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /opt/oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1521)))

Services Summary…

Service "chf" has 2 instance(s).

Instance "chf", status UNKNOWN, has 1 handler(s) for this service…

Instance "chf", status READY, has 1 handler(s) for this service…

Service "chfXDB" has 1 instance(s).

Instance "chf", status READY, has 1 handler(s) for this service…

Service "chf_XPT" has 1 instance(s).

Instance "chf", status READY, has 1 handler(s) for this service…

Service "ecp" has 2 instance(s).

Instance "ecp", status UNKNOWN, has 1 handler(s) for this service…

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ecpXDB" has 1 instance(s).

Instance "ecp", status READY, has 1 handler(s) for this service…

Service "ecp_XPT" has 1 instance(s).

Instance "ecp", status READY, has 1 handler(s) for this service…

The command completed successfully

Oracle 11g listener.ora配置

[oracle@node1 admin]$ more listener.ora

# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ora11g)

(ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)

(SID_NAME = ora11g)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =node1.srtcloud.com)(PORT = 1522))

)

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-NOV-2011 10:37:04

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.srtcloud.com)(PORT=1522)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production

Start Date 01-NOV-2011 10:30:25

Uptime 0 days 0 hr. 6 min. 39 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /opt/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.srtcloud.com)(PORT=1522)))

Services Summary…

Service "ora11g" has 2 instance(s).

Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service…

Instance "ora11g", status READY, has 1 handler(s) for this service…

Service "ora11gXDB" has 1 instance(s).

Instance "ora11g", status READY, has 1 handler(s) for this service…

The command completed successfully

[oracle@node1 admin]$ ps -ef|grep tns|grep -v grep

oracle 24713 1 0 10:30 ? 00:00:00 /opt/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit

oracle 24720 1 0 10:30 ? 00:00:00 /opt/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit

事项说明：使用了两个监听，分别监听不同的端口，对应不同的数据库

SP2-1503 SP2-0152 错误解决

一、现场描述

服务器上有Oracle 10g环境变量分别为

Oracle 10g环境变量

export ORACLE_BASE=/opt/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin

export ORACLE_SID=ecp

export NLS_LANG=AMERICAN_AMERICA.zhs16gbk

export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

export ORACLE_TERM=xterm

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORA_NLS10=$ORACLE_HOME/nls/data

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export CLASSPATH=$ORACLE_HOME/JRE

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

export THREADS_FLAG=native

export TEMP=/tmp

export TMPDIR=/tmp

export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin:$ORACLE_BASE:$ORACLE_HOME

export PATH=${PATH}:$ORACLE_HOME/bin:$ORA_CRS_HOME:$ORA_CRS_HOME/bin

export PATH=${PATH}:$LD_LIBRARY_PATH:$CLASSPATH:$ORACLE_PATH

现在在上面Oracle 11g，在shell中执行下面命令修改环境变量

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=ora11g

然后安装数据库，安装过程一切顺利，安装完成，当使用sqlplus的时候报如下错误：

[oracle@node1 tmp]$ sqlplus /nolog

SP2-1503: Unable to initialize Oracle call interface

SP2-0152: ORACLE may not be functioning properly

二、问题原因

[oracle@node1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

[oracle@node1 ~]$ export ORACLE_SID=ora11g

[oracle@node1 ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 31 17:04:17 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ sqlplus / as sysdba

SP2-1503: Unable to initialize Oracle call interface

SP2-0152: ORACLE may not be functioning properly

因为系统的环境变量中的PATH没有修改过来，直接使用sqlplus的时候，还是会调用Oracle 10g中的sqlplus，而此时ORACLE_HOME与其不匹配，导致出现上面错误

三、证明猜想，试验如下

[oracle@node1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

[oracle@node1 ~]$ env|grep ORACLE

ORACLE_PATH=/opt/oracle/common/oracle/sql:.:/opt/oracle/product/10.2.0/db_1/rdbms/admin

ORACLE_SID=ecp

ORACLE_BASE=/opt/oracle

ORACLE_TERM=xterm

ORACLE_HOME=/opt/oracle/product/11.2.0/db_1

[oracle@node1 ~]$ sqlplus / as sysdba

SP2-1503: Unable to initialize Oracle call interface

SP2-0152: ORACLE may not be functioning properly

说明：修改ORACLE_HOME，指向Oracle 11g的目录，然后执行sqlplus(该程序属于10g)，报同样错误

四、补充说明
1、如果在一台服务器上安装不同版本数据库，建议使用不用用户安装，这样可以减少很多管理上没必要的麻烦，提高工作效率
2、sqlplus程序需要和ORACLE_HOME向对应，不然就会出现SP2-1503 SP2-0152错误
3、本次排错中学习到知识，如果要执行一个shell文件修改环境变量，可以使用source
4、如果必须使用同一个用户安装，那么建议新安装的oracle版本建一个shell文件，然后使用source shell_filename，修改环境变量（特别注意PATH)

Linux防火墙配置

一、Linux防火墙基础知识
1、常用命令
查看配置 iptables -L -n
修改配置 iptables -A INPUT -p tcp -s 192.168.1.12 –dport 22 -j ACCEPT
保存修改 /etc/init.d/iptables save
开启/关闭/重启/状态 /etc/init.d/iptables start/stop/restart/status
2、常用参数
–append -A chain Append to chain
–in-interface -i [!] input name[+] network interface name ([+] for wildcard)
–proto -p [!] proto protocol: by number or name, eg. `tcp’
–source -s [!] address[/mask] source specification
–destination -d [!] address[/mask] destination specification
–jump -j target target for rule (may load target extension)
–dport 目标端端口
–sport 源端端口
二、Liunx防火墙配置方法
1、setup配置

注意INPUT/FORWARD/OUTPUT设置
2、vi修改/etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
#表示进入服务器包，一般情况下修改为DROP
:INPUT ACCEPT [0:0]
#表示转发包，根据实际情况
:FORWARD ACCEPT [0:0]
#表示从服务器发出包，一般设置为ACCEPT
:OUTPUT ACCEPT [0:0]
#不清楚该含义
:RH-Firewall-1-INPUT – [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
#表示所有eth1/eth0网卡包都可以被允许
-A RH-Firewall-1-INPUT -i eth1 -j ACCEPT
-A RH-Firewall-1-INPUT -i eth0 -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp –icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp –dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp –dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp –dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 1521 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 25 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 80 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 21 -j ACCEPT
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 443 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT –reject-with icmp-host-prohibited
COMMIT
service iptables restart
3、iptables命令配置
#注意是否已经配置了相关策略，不然可能导致ssh不能访问
iptables -P INPUT DROP
iptables -P OUTPUT DROP
#慎重，要不你的ssh也失去链接了
iptables -P FORWARD DROP
#如果iptables -P OUTPUT DROP配置，一定需要类此这两条配合
iptables -A INPUT -p tcp –dport 22 -j ACCEPT
iptables -A OUTPUT -p tcp –sport 22 -j ACCEPT
iptables -A INPUT -p tcp -s 10.218.32.153 –dport 1521 -j ACCEPT
iptables -A OUTPUT -p tcp -d 10.218.32.153 –sport 1521 -j ACCEPT
/etc/init.d/iptables save
service iptables restart

Oracle中connect by…start with…的使用

一、语法
大致写法：select * from some_table [where 条件1] connect by [条件2] start with [条件3];
其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：
[where 条件1]是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤，不会考虑树的结构；
[条件2]指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；
[条件3]限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件；
示例：
假如有如下结构的表：some_table(id,p_id,name)，其中p_id保存父记录的id。
select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321 start with t.p_id=33 or t.p_id=66;
对prior的说明：
prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“start with [条件3]”的记录，不会在寻找这些记录的子节点。要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。
二、执行原理
connect by…start with…的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明：
/* 遍历表中的每条记录，对比是否满足start with后的条件，如果不满足则继续下一条，
如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点，
如此循环直到遍历完整个表的所有记录。*/
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
RECURSE(rec, rec.child);
end if;
end loop;
/* 寻找子节点的存储过程*/
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin
APPEND_RESULT_LIST(rec); /*把记录加入结果集合中*/
/*再次遍历表中的所有记录，对比是否满足connect by后的条件，如果不满足则继续下一条，
如果满足则再以该记录为根节点，然后调用RECURSE()继续递归寻找该节点下的子节点，
如此循环直到找至叶子节点。*/
for rec_recurse in (select * from some_table) loop
if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
RECURSE(rec_recurse,rec_recurse.child);
end if;
end loop;
end procedure RECURSE;
三、使用探讨
从上面的执行原理可以看到“connect by…start with…”构造树的方式是：（1）如果是自上而下方式，则把表中的每一条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。（2）如果是自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。
因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况。
来源：http://hi.baidu.com/haydo/blog/item/069298438e5c6d1073f05d46.html

ggsci: error while loading shared libraries

在部署goldengate过程中发现如下错误：
[oracle@localhost OGG]$ ggsci
ggsci: error while loading shared libraries: /opt/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1: cannot restore segment prot after reloc: Permission denied
查找资料，没有专门说ogg安装这个错误，是SELinux启用导致，但是有很多其他程序执行过程中报类此错误是有此导致，那么我抱着尝试的态度实验看看：
1、查看SELinux是否被关闭
[oracle@localhost tmp]$ more /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing – SELinux security policy is enforced.
# permissive – SELinux prints warnings instead of enforcing.
# disabled – SELinux is fully disabled.
SELINUX=disabled
# SELINUXTYPE= type of policy in use. Possible values are:
# targeted – Only targeted network daemons are protected.
# strict – Full SELinux protection.
SELINUXTYPE=targeted
说明已经关闭。那就奇怪了，既然已经闭关了那为什么还不行？于是我怀疑，是不是有人只是修改了SELINUX=disabled，没有重启系统或者使用命令使其生效导致。
2、查看SELINUX修改是否生效
[root@localhost ~]# getenforce
Enforcing
果然修改没有生效
3、使SELINUX生效
[root@localhost ~]# setenforce 0
再次查询，现在已经生效
[root@localhost ~]# getenforce
Permissive
4、然后启动ggsci
[oracle@localhost ~]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.1 OGGCORE_11.1.1.1.1_PLATFORMS_110729.1700
Linux, x64, 64bit (optimized), Oracle 10g on Jul 29 2011 19:43:29
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

数据文件重命名

最近一段时间，发现不少pub上不少新手都因为一时大意，添加数据文件名称不规范，然后想重命名该数据文件（或者想删除该数据文件然后重建），处理思路有些不妥，导致一些悲剧的发现，我这里通过实验提供一个自认为比较合理的处理思路：处理思路是数据文件离线重命名

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/test/archivelog
Oldest online log sequence     210
Next log sequence to archive   212
Current log sequence           212
--确认数据库是归档模式，使得数据库离线后，可以有归档日志恢复到在线状态
SQL> col name for a50
SQL> select name,file# from v$datafile;
NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10
10 rows selected.
SQL> alter tablespace xff add datafile '/opt/oracle/oradata/test/xifenfei02.chf'
   2    size 10m autoextend off;
Tablespace altered.
SQL>  select name,file# from v$datafile;
NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10
/opt/oracle/oradata/test/xifenfei02.chf                    11
11 rows selected.
SQL> create table chf.xff_test tablespace xff
  2  as
  3  select * from dba_objects;
select * from dba_objects
              *
ERROR at line 3:
ORA-01536: space quota exceeded for tablespace 'XFF'
SQL> alter user chf quota 100m on xff;
User altered.
SQL> create table chf.xff_test tablespace xff
  2  as
  3  select * from dba_objects;
Table created.
--需要重命名的数据文件内有数据，模拟数据库在生产环境中工作
SQL> alter database datafile 11 offline drop ;
Database altered.
--数据文件离线处理
SQL> !mv /opt/oracle/oradata/test/xifenfei02.chf /opt/oracle/oradata/test/xifenfei02.dbf
--系统级别把数据文件修改为正确名称
SQL> alter database rename file '/opt/oracle/oradata/test/xifenfei02.chf'
     2  to '/opt/oracle/oradata/test/xifenfei02.dbf';
Database altered.
--修改控制文件中数据文件名称
SQL> recover datafile 11;
Media recovery complete.
SQL> alter database datafile 11 online;
Database altered.
--恢复数据文件，并使其online
SQL>  select name,file# from v$datafile;
NAME                                                    FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf                       1
/opt/oracle/oradata/test/undotbs01.dbf                      2
/opt/oracle/oradata/test/sysaux01.dbf                       3
/opt/oracle/oradata/test/users01.dbf                        4
/opt/oracle/oradata/test/user32g.dbf                        5
/opt/oracle/oradata/test/xifenfei01.dbf                     6
/opt/oracle/oradata/test/user02.dbf                         7
/opt/oracle/oradata/test/odu02.dbf                          8
/opt/oracle/oradata/test/odu01.dbf                          9
/opt/oracle/oradata/test/odu03.dbf                         10
/opt/oracle/oradata/test/xifenfei02.dbf                    11
11 rows selected.

如果数据库满足以下条件，可以删除数据文件，重新添加：
1、The database must be open.
2、If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
3、You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.
4、You cannot drop datafiles in a read-only tablespace.
5、You cannot drop datafiles in the SYSTEM tablespace.
6、If a datafile in a locally managed tablespace is offline, it cannot be dropped.
7、db version >= 10g R2

SQL> alter tablespace xff drop datafile 11;
alter tablespace xff drop datafile 11
*
ERROR at line 1:
ORA-03262: the file is non-empty
SQL> alter tablespace xff add datafile '/opt/oracle/oradata/test/xifenfei03.chf' size 10m autoextend off;
Tablespace altered.
SQL> alter tablespace xff drop datafile '/opt/oracle/oradata/test/xifenfei03.chf';
Tablespace altered.

作者：admin