四、补充说明
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.
清除离线数据文件记录
测试前提:数据文件离线,系统上删除了该文件,需要删除在数据字典中,关于这条离线数据文件记录
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 file_name for a40 SQL> select file_id,file_name,bytes from dba_data_files order by 1; FILE_ID FILE_NAME BYTES ---------- ---------------------------------------- ---------- 1 /opt/oracle/oradata/test/system01.dbf 524288000 2 /opt/oracle/oradata/test/undotbs01.dbf 1289748480 3 /opt/oracle/oradata/test/sysaux01.dbf 377487360 4 /opt/oracle/oradata/test/users01.dbf 5242880 5 /opt/oracle/oradata/test/user32g.dbf 10485760 6 /opt/oracle/oradata/test/xifenfei01.dbf 20971520 7 /opt/oracle/oradata/test/user02.dbf 10485760 8 /opt/oracle/oradata/test/odu02.dbf 1.1283E+10 9 /opt/oracle/oradata/test/odu01.dbf 104857600 10 /opt/oracle/oradata/test/odu03.chf 10 rows selected. SQL> col error for a20 SQL> select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1; FILE# ONLINE_ ERROR CHANGE# ---------- ------- -------------------- ---------- 10 OFFLINE FILE NOT FOUND 0 SQL> !ls /opt/oracle/oradata/test/odu03.chf ls: /opt/oracle/oradata/test/odu03.chf: No such file or directory --说明该数据文件已经从硬盘上删除 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 209715200 bytes Fixed Size 2082784 bytes Variable Size 130025504 bytes Database Buffers 71303168 bytes Redo Buffers 6303744 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/opt/oracle/oradata/test/redo01.log' SIZE 50M, 9 GROUP 2 '/opt/oracle/oradata/test/redo02.log' SIZE 50M, 10 GROUP 3 '/opt/oracle/oradata/test/redo03.log' SIZE 50M 11 DATAFILE 12 '/opt/oracle/oradata/test/system01.dbf', 13 '/opt/oracle/oradata/test/undotbs01.dbf', 14 '/opt/oracle/oradata/test/sysaux01.dbf', 15 '/opt/oracle/oradata/test/users01.dbf', 16 '/opt/oracle/oradata/test/user32g.dbf', 17 '/opt/oracle/oradata/test/xifenfei01.dbf', 18 '/opt/oracle/oradata/test/user02.dbf', 19 '/opt/oracle/oradata/test/odu02.dbf', 20 '/opt/oracle/oradata/test/odu01.dbf' ,'/opt/oracle/oradata/test/odu03.chf' --文件不存在,创建控制文件这条记录需要除掉 21 CHARACTER SET ZHS16GBK 22 ; Control file created. SQL> alter database open; Database altered. SQL> select file_id,file_name,bytes from dba_data_files order by 1; FILE_ID FILE_NAME BYTES ---------- ---------------------------------------- ---------- 1 /opt/oracle/oradata/test/system01.dbf 524288000 2 /opt/oracle/oradata/test/undotbs01.dbf 1289748480 3 /opt/oracle/oradata/test/sysaux01.dbf 377487360 4 /opt/oracle/oradata/test/users01.dbf 5242880 5 /opt/oracle/oradata/test/user32g.dbf 10485760 6 /opt/oracle/oradata/test/xifenfei01.dbf 20971520 7 /opt/oracle/oradata/test/user02.dbf 10485760 8 /opt/oracle/oradata/test/odu02.dbf 1.1283E+10 9 /opt/oracle/oradata/test/odu01.dbf 104857600 10 /opt/oracle/product/10.2.0/db_1/dbs/MISSING00010 --系统默认创建了自定义的数据文件名称 10 rows selected. SQL> select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1; FILE# ONLINE_ ERROR CHANGE# ---------- ------- -------------------- ---------- 10 OFFLINE FILE MISSING 0 --提示该文件是离线状态,需要恢复,结果同开始时候状态 SQL> select file#,STATUS$,TS#,RELFILE# from file$ order by 1; FILE# STATUS$ TS# RELFILE# ---------- ---------- ---------- ---------- 1 2 0 1 2 2 1 2 3 2 2 3 4 2 4 4 5 2 4 5 6 2 6 6 7 2 4 7 8 2 7 9 9 2 7 6 10 2 7 10 11 1 11 rows selected. SQL> delete from file$ where file#=10; ---重要的就是这个操作 1 row deleted. SQL> select file#,STATUS$,TS#,RELFILE# from file$ order by 1; FILE# STATUS$ TS# RELFILE# ---------- ---------- ---------- ---------- 1 2 0 1 2 2 1 2 3 2 2 3 4 2 4 4 5 2 4 5 6 2 6 6 7 2 4 7 8 2 7 9 9 2 7 6 11 1 10 rows selected. SQL> col name for a40 SQL> select * from v$dbfile order by 1; FILE# NAME ---------- ---------------------------------------- 1 /opt/oracle/oradata/test/system01.dbf 2 /opt/oracle/oradata/test/undotbs01.dbf 3 /opt/oracle/oradata/test/sysaux01.dbf 4 /opt/oracle/oradata/test/users01.dbf 5 /opt/oracle/oradata/test/user32g.dbf 6 /opt/oracle/oradata/test/xifenfei01.dbf 7 /opt/oracle/oradata/test/user02.dbf 8 /opt/oracle/oradata/test/odu02.dbf 9 /opt/oracle/oradata/test/odu01.dbf 10 /opt/oracle/product/10.2.0/db_1/dbs/MISSING00010 10 rows selected. --需要重建控制文件,删除不存在的数据文件 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP NOMOUNT ORACLE instance started. Total System Global Area 209715200 bytes Fixed Size 2082784 bytes Variable Size 130025504 bytes Database Buffers 71303168 bytes Redo Buffers 6303744 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/opt/oracle/oradata/test/redo01.log' SIZE 50M, 9 GROUP 2 '/opt/oracle/oradata/test/redo02.log' SIZE 50M, 10 GROUP 3 '/opt/oracle/oradata/test/redo03.log' SIZE 50M 11 DATAFILE 12 '/opt/oracle/oradata/test/system01.dbf', 13 '/opt/oracle/oradata/test/undotbs01.dbf', 14 '/opt/oracle/oradata/test/sysaux01.dbf', 15 '/opt/oracle/oradata/test/users01.dbf', 16 '/opt/oracle/oradata/test/user32g.dbf', 17 '/opt/oracle/oradata/test/xifenfei01.dbf', 18 '/opt/oracle/oradata/test/user02.dbf', 19 '/opt/oracle/oradata/test/odu02.dbf', 20 '/opt/oracle/oradata/test/odu01.dbf' 21 CHARACTER SET ZHS16GBK 22 ; Control file created. SQL> alter database open; Database altered. SQL> select file_id,file_name,bytes from dba_data_files order by 1; FILE_ID FILE_NAME BYTES ---------- ---------------------------------------- ---------- 1 /opt/oracle/oradata/test/system01.dbf 524288000 2 /opt/oracle/oradata/test/undotbs01.dbf 1289748480 3 /opt/oracle/oradata/test/sysaux01.dbf 377487360 4 /opt/oracle/oradata/test/users01.dbf 5242880 5 /opt/oracle/oradata/test/user32g.dbf 10485760 6 /opt/oracle/oradata/test/xifenfei01.dbf 20971520 7 /opt/oracle/oradata/test/user02.dbf 10485760 8 /opt/oracle/oradata/test/odu02.dbf 1.1283E+10 9 /opt/oracle/oradata/test/odu01.dbf 104857600 9 rows selected. SQL> select * from v$dbfile order by 1; FILE# NAME ---------- ---------------------------------------- 1 /opt/oracle/oradata/test/system01.dbf 2 /opt/oracle/oradata/test/undotbs01.dbf 3 /opt/oracle/oradata/test/sysaux01.dbf 4 /opt/oracle/oradata/test/users01.dbf 5 /opt/oracle/oradata/test/user32g.dbf 6 /opt/oracle/oradata/test/xifenfei01.dbf 7 /opt/oracle/oradata/test/user02.dbf 8 /opt/oracle/oradata/test/odu02.dbf 9 /opt/oracle/oradata/test/odu01.dbf 9 rows selected.
补充说明:非归档模式下,NOARCHIVELOG创建控制文件,其他无太大区别
测试来源:itpub:数据文件物理性删除相关问题疑惑?
参考blog:roger:如何彻底删除已经不存在的数据文件?
设置oracle 含特殊字符密码
oracle 修改设置密码复杂度渐增方法:
1、修改为常见密码(无特殊字符)
SQL> alter user chf identified by xifenfei;
User altered.
2、修改含一般特殊字符(如:$, %等)
SQL>
SQL> alter user chf identified by “xi%,fenfei”;
User altered.
SQL> conn chf/xi%,fenfei
Connected.
3、修改含”的特殊字符
3.1)修改制定用户密码(sys用户操作)
SQL> password chf
Changing password for chf
New password:
Retype new password:
Password changed
SQL> conn chf/aa””bb
Connected.
3.2)修改当前用户密码(需要有修改密码权限)
SQL> password
Changing password for CHF
Old password:
New password:
Retype new password:
Password changed
SQL>
注:因为一般特殊字符可以使用双引号处理,但是如果密码中含有双引号,就不能用双引号处理,可以直接使用password修改密码
sys用户密码含$ sqlplus登录数据库诡异事件分析
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:37:51 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter user sys identified by "ab$"; User altered. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$ as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:38:53 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string test db_unique_name string test global_names boolean FALSE instance_name string test lock_name_space string log_file_name_convert string service_names string test SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$abc as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:39:05 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$@abc as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:40:06 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string test db_unique_name string test global_names boolean FALSE instance_name string test lock_name_space string log_file_name_convert string service_names string test SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ECP-UC-DB1 ~]$ sqlplus sys/ab$@abc11 as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:44:11 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string test db_unique_name string test global_names boolean FALSE instance_name string test lock_name_space string log_file_name_convert string service_names string test SQL>
通过以上sql发现,把sys的密码改为ab$后,无论是什么tns都可以登录数据库,而且都是本地数据库,是不是感觉很诡异,其实你仔细观察发现,密码中有了$,使得$@的操作都变成了无效的,其实就是sqlplus sys/123(随意) as sysdba方式登录本地数据库
[oracle@ECP-UC-DB1 ~]$ sqlplus sys/123 as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 17 23:48:23 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
Oracle 小升级 opatch apply使用
这里记录的是Oracle 升级到10.2.0.4后,出现EM不能正常启动情况下,Oracle提示需要打上Patch 8350262,下面是记录打补丁过程
[oracle@ocp ~]$ unzip ~/p8350262_10204_Generic.zip Archive: /home/oracle/p8350262_10204_Generic.zip creating: 8350262/ inflating: 8350262/killDBConsole creating: 8350262/files/ creating: 8350262/files/sysman/ creating: 8350262/files/sysman/jlib/ creating: 8350262/files/sysman/jlib/emCORE.jar/ creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/ creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/ creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/ creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/ creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/ inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/ inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/RootCert.class inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/SecConstants.class creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/ inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class creating: 8350262/files/sysman/jlib/emd_java.jar/ creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/ creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/ creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/ creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/ creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/ inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/ inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/RootCert.class inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/SecConstants.class creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/ inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class inflating: 8350262/killDBConsole.pl inflating: 8350262/README.txt creating: 8350262/etc/ creating: 8350262/etc/xml/ inflating: 8350262/etc/xml/ShiphomeDirectoryStructure.xml inflating: 8350262/etc/xml/GenericActions.xml creating: 8350262/etc/config/ inflating: 8350262/etc/config/inventory inflating: 8350262/etc/config/actions [oracle@ocp ~]cd 8350262 [oracle@ocp 8350262]$ $ORACLE_HOME/OPatch/opatch apply --Note:$ORACLE_HOME/OPatch/opatch路径 Invoking OPatch 10.2.0.4.2 Oracle Interim Patch Installer version 10.2.0.4.2 Copyright (c) 2007, Oracle Corporation. All rights reserved. Oracle Home : /opt/oracle/app/10.2.0/db_1 Central Inventory : /opt/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.4.2 OUI version : 10.2.0.4.0 OUI location : /opt/oracle/app/10.2.0/db_1/oui Log file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-17_18-11-40PM.log ApplySession applying interim patch '8350262' to OH '/opt/oracle/app/10.2.0/db_1' Running prerequisite checks... OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Backing up files and inventory (not for auto-rollback) for the Oracle Home Backing up files affected by the patch '8350262' for restore. This might take a while... Backing up files affected by the patch '8350262' for rollback. This might take a while... Patching component oracle.sysman.agent.core, 10.2.0.4.0a... Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class" Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class" Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/RootCert.class" Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/SecConstants.class" Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class" Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class" Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/RootCert.class" Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/SecConstants.class" ApplySession adding interim patch '8350262' to inventory Verifying the update... Inventory check OK: Patch ID 8350262 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 8350262 are present in Oracle Home. OPatch succeeded. [oracle@ocp 8350262]$ ../opatch lsinventory --Note检测是否安装成功 Invoking OPatch 10.2.0.4.2 Oracle Interim Patch Installer version 10.2.0.4.2 Copyright (c) 2007, Oracle Corporation. All rights reserved. Oracle Home : /opt/oracle/app/10.2.0/db_1 Central Inventory : /opt/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.4.2 OUI version : 10.2.0.4.0 OUI location : /opt/oracle/app/10.2.0/db_1/oui Log file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-17_18-18-34PM.log Lsinventory Output file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2011-10-17_18-18-34PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (2): Oracle Database 10g 10.2.0.1.0 Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0 There are 2 products installed in this Oracle Home. Interim patches (1) : Patch 8350262 : applied on Mon Oct 17 18:17:28 CST 2011 Created on 14 Sep 2010, 04:59:44 hrs PST8PDT Bugs fixed: 8350262 -------------------------------------------------------------------------------- OPatch succeeded.
Oracle常见后台进程及其功能汇总
今天参照oracle官方文档和metalink文档,对Oracle常见后台进程及其功能汇总进行了一次汇总
Oracle常见后台进程及其功能汇总
http://www.xifenfei.com/wp-content/uploads/2011/10/Oracle_Process.pdf