ssh tunnel - ssh 隧道

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/8/19
Subject: ssh 隧道 ssh tunnel
To: xiewenxiewen at gmail.com


ssh 隧道tunnel，也叫端口转发port forwarding
现举例说明使用ssh隧道实现端口转发，通信信息加密，socks代理服务器，突破防火墙

用法1. 通过不安全网络，加密通信内容，防止被窃听
比如，通过Internet互联网，我想访问一台服务器(mgt.s3lab.mot.com)的邮件服务，然而stmp邮件协议是不安全的，传输的内容有可能被其它人窃取
所以我要在我的机器和服务器之间建立起一条加密通道

图1
       --------
      |internet|
me ---|--------|---> mail server
 :25  |        |        :25
       --------

运行
plink s3op@mgt.s3lab.mot.com -L 25:localhost:25 -N

s3op@mgt.s3lab.mot.com 是ssh登录的用户名和地址
-L 表示Local port forwarding 本地端口转发
25:localhost:25 表示将我的pc机的25端口转发到服务器(mgt.s3lab.mot.com)上的localhost:25这个端口
-N 表示不需要交互操作，也就是说登录ssh后不启动shell
这样，访问本机的25端口就等于访问mgt服务器上的邮件服务了，而且是经过加密的

试发一封邮件给root，运行:
telnet localhost 25
ehlo helo
mail from:xiewen@earth.com
rcpt to:root
data
.
quit

发送成功



用法2. 穿过防火墙，访问防火墙后面的内网上的服务

比如，因为防火墙限制，只有MGT服务器对外提供ssh登录，无法直接访问防火墙后的内网的其它机器
但我想访问内网oracle数据库

运行
plink s3op@mgt.s3lab.mot.com -L 9901:QA-DB-1:1521 -L 9902:DEV-DB-1:1521 -N

将本机9901转发到内网QA-DB-1上oracle监听器端口1521，9902转发到DEV-DB-1的1521

图2
        firewall
            |
            |
me ---------|-----MGT------> QA-DB-1 :1521
      :9901 |       \
      :9902 |        -------> DEV-DB-1 :1521
            |

我就可以很方便的从本机直接登录内网数据库了
sqlplus system/manager@localhost:9901/screen3


sqlplus system/manager@localhost:9902/screen3



用法3: 穿过防火墙，让内网机器访问我的服务

前面举的例子的都是Local port forwarding本地端口转发，Remote port forwarding和它正好相反，是将远端端口转发到本地
比如，我在本机建立了一个http服务器，提供了文件共享服务

想从内网的访问到我共享的文件
运行
plink s3op@mgt.s3lab.mot.com -R 9080:localhost:80 -N

将mgt上的9080转发到本机的localhost:80上

图3
      firewall
         |
me <-----|-------- mgt <------- QA-DB-1
     :80 |        :9080

在内网mgt上，就可以访问共享文件了
wget http://localhost:9080/D17090GC30_ppt/index.ppt


要让内网其它机器也能访问，还要打开ssh的GatewayPorts参数
重启sshd后台服务进程，使修改的参数生效


从内网其它机器也能访问了



用法4. 建立SOCKS代理服务器
比如，我想从公司访问http://www.playboy.com
，可公司代理做了限制，不能访问


机房里有一台机器可以访问互联网，向外访问似乎没有限制，于是我打算将这台机器作为代理服务器
运行
plink s3op@mgt.s3lab.mot.com -D 1080 -N

-D 表示dynamic port forwarding, 将ssh作为SOCKS代理服务器，在本机监听1080端口

图4
     firewall
         |
         |                   --------
me ------|---- mgt -------> |internet|
   :1080 |                   --------
         |

然后我修改了firefox的网络连接设置，使用SOCKS代理服务器，再访问playboy

成功了！


用法5. 综合应用
综合运用远程+动态端口转发，突破公司防火墙，使用bittorrent,edonkey等p2p软件下载盗版软件、最新大片
没有条件，所以就不演示了




Xie wen（谢文）
Network & Operations,
Mobile Software Solutions (MDB)
MOTOROLA Inc.
No. 108 Jian Guo Road, Chao Yang District, Beijing
100022 P. R. China
e-mail wenxie at motorola.com



-fin-

recover from cold backup of control files - 恢复冷备份的控制文件

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/8/19
Subject: RE: 答复: 答复: test for lesson 14
To: wen xie <xiewenxiewen at googlemail.com>


1.丢失一个控制文件，实例将终止
>control03.ctl清空一个控制文件内容，模拟磁盘出错

[oracle@DEV-RPT-2 ~/app/oracle/oradata/test]$ ls -l *.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control01.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control02.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control03.ctl
[oracle@DEV-RPT-2 ~/app/oracle/oradata/test]$ >control01.ctl
[oracle@DEV-RPT-2 ~/app/oracle/oradata/test]$ ls -l *.ctl
-rw-r----- 1 oracle oinstall 65536 Jan 20 04:29 control01.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control02.ctl
-rw-r----- 1 oracle oinstall 9093120 Jan 20 04:29 control03.ctl
[oracle@DEV-RPT-2 ~/app/oracle/oradata/test]$

alter.log报错

Tue Jan 20 04:29:27 2009
Errors in file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_7105.trc:
ORA-00202: control file: '/home/oracle/app/oracle/oradata/test/control01.ctl'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 4
Additional information: 1
Tue Jan 20 04:29:27 2009
Errors in file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_7105.trc:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/home/oracle/app/oracle/oradata/test/control01.ctl'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 4
Additional information: 1
Tue Jan 20 04:29:27 2009
LGWR: terminating instance due to error 204
Instance terminated by LGWR, pid = 7105

实例终止了



2.archivelog数据库丢失所有控制文件的恢复

a.先关闭数据库，做个全库冷备份
cp -rp test test.bak

b.启动数据库，随便修改一些数据
conn / as sysdba
startup
conn a/a
create table tmp as select * from all_objects;
select count(*) from tmp;

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2083592 bytes
Variable Size 188744952 bytes
Database Buffers 117440512 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> conn a/a
Connected.
SQL> create table tmp as select * from all_objects;

Table created.

SQL> select count(*) from tmp;

COUNT(*)
----------
4562

SQL>

c.清空所有控制文件
>control01.ctl;>control02.ctl;>control03.ctl
alert.log报错，实例终止

Tue Jan 20 06:21:54 2009
Hex dump of (file 0, block 1) in trace file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_11683.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Completely zero block found during control file header read
Tue Jan 20 06:21:54 2009
Errors in file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_11683.trc:
ORA-00202: control file: '/home/oracle/app/oracle/oradata/test/control01.ctl'
Tue Jan 20 06:21:54 2009
Errors in file /home/oracle/app/oracle/admin/test/bdump/test_lgwr_11683.trc:
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: '/home/oracle/app/oracle/oradata/test/control01.ctl'
LGWR: terminating instance due to error 227
Instance terminated by LGWR, pid = 11683

d.恢复数据前，对坏了的数据库也冷备份一下，保证不会因为误操作破坏数据库
cp -rp test test.bad.bak

e.从冷备份中恢复旧的控制文件
cp -p test.bak/control0{1,2,3}.ctl test/

f.启动数据库
conn / as sysdba
startup

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2083592 bytes
Variable Size 188744952 bytes
Database Buffers 117440512 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/test/system01.dbf'
ORA-01207: file is more recent than control file - old control file


SQL>

报告控制文件是旧的，无法打开数据库

f.恢复数据库
recover database using backup controlfile;

SQL> recover database using backup controlfile;
ORA-00279: change 700297 generated at 01/20/2009 06:15:17 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/oradata/test/archive/1_658_675146492.arc
ORA-00280: change 700297 for thread 1 is in sequence #658


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

提示说需要一个归档日志

alter.log显示

Tue Jan 20 06:26:56 2009
ALTER DATABASE RECOVER database using backup controlfile
Tue Jan 20 06:26:56 2009
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
parallel recovery started with 3 processes
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile ...

因为没有这个归档，查询v$log,v$logfile，得知在线日志文件名
set pages 9999 line 130
col member for a50
select * from v$log;
select * from v$logfile;

SQL> set pages 9999 line 130
SQL> col member for a50
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 656 104857600 2 YES INACTIVE 699823 20-JAN-09
3 1 658 104857600 2 NO CURRENT 699844 20-JAN-09
2 1 657 104857600 2 YES INACTIVE 699840 20-JAN-09

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /home/oracle/app/oracle/oradata/test/redo01.rdo NO
2 ONLINE /home/oracle/app/oracle/oradata/test/redo02.rdo NO
3 ONLINE /home/oracle/app/oracle/oradata/test/redo03.rdo NO
1 ONLINE /home/oracle/app/oracle/oradata/test/redo01b.rdo NO
2 ONLINE /home/oracle/app/oracle/oradata/test/redo02b.rdo NO
3 ONLINE /home/oracle/app/oracle/oradata/test/redo03b.rdo NO

6 rows selected.

SQL>

所以输入对应的文件名

/home/oracle/app/oracle/oradata/test/redo03.rdo
Log applied.
Media recovery complete.
SQL>

恢复完成

alert.log显示

Tue Jan 20 06:30:22 2009
ALTER DATABASE RECOVER LOGFILE '/home/oracle/app/oracle/oradata/test/redo03.rdo'
Tue Jan 20 06:30:22 2009
Media Recovery Log /home/oracle/app/oracle/oradata/test/redo03.rdo
Tue Jan 20 06:30:23 2009
Media Recovery Complete (test)
Completed: ALTER DATABASE RECOVER LOGFILE '/home/oracle/app/oracle/oradata/test/redo03.rdo'

g.打开数据库
alter database open resetlogs;
查询select count(*) from a.tmp;
没问题
说明成功恢复了.


3.noarchivelog恢复所有控制文件
过程同2，也可以恢复

但是，如果在线日志中没有恢复所要用到的全部重做记录

SQL> recover database using backup controlfile;
ORA-00279: change 699614 generated at 01/20/2009 05:01:58 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/oradata/test/archive/1_655_675146492.arc
ORA-00280: change 699614 for thread 1 is in sequence #655


Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/test/redo03b.rdo
ORA-00326: log begins at change 700047, need earlier change 699614
ORA-00334: archived log: '/home/oracle/app/oracle/oradata/test/redo03b.rdo'

SQL>

提示说需要更早的重做日志

这种情况下就不能用备份的控制文件恢复了，必须重建控制文件

alter database backup controlfile to trace;
shutdown immediate

startup nomount
create controlfile reuse database test noresetlogs force logging noarchivelog
...
logfile
...
datafile
...
character set al32utf8
;

recover database;
alter database open;

所以noarchivelog非归档模式下,有时可以用备份的控制文件恢复, 有时不行. C说的不严谨, 所以不选C


P.S.
如果是重建控制文件，恢复完了还要手工给临时表空间加上临时文件

ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/app/oracle/oradata/test/temp01.dbf' REUSE;

P.P.S.
4.
19. Your database is configured in NOARCHIVELOG mode. All the control files have been lost due to a hard disk failure but the data files are not lost. You have the closed whole database backup available to you. Which two statements are true in this scenario? (Choose two.)
A) The instance aborts.
B) The database cannot be recovered.
C) The database can be recovered by restoring the control files from the backup.
D) The database remains opened and you have to shut it down with the ABORT option.
E) The database can be restored till the point of the last closed whole database backup.
A.E.


外部链接:

Restore Control File from Backup After Loss of All Current Control Files



-fin-

escaping special characters in Oracle - 转义Oracle的特殊字符

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/8/12
Subject: oracle特殊字符的转义 escaping special characters in oracle
To: xiewenxiewen at gmail.com


1.sqlplus中的&符号转义
sqlplus中, 默认的，&符号有特殊含义，&符号后面加上一个字符串名字表示引用用户定义的一个字符串变量，比如:
_USER是sqlplus默认定义的一个字符串变量，用&_USER表示引用这个变量.
变量a没有定义，会提示用户输入


如果想把&当成普通字符处理，比如就想查询出'&a'这个字符串，有几种方法
a. set define
修改默认的变量引用符号
show define
set define '$'
show define
select '&a' from dual;

变量引用符号改成了$，直接查询'&a'，结果就是'&a'

b. set define off
关闭引用变量功能
set define off

也可以直接查询出'&a'

c. set escape设置转义字符
set define '&'
show define
show escape
set escape '\'
show escape
select '\&a','\\'
from dual;

&前面加上了\转义字符，表示&不再带有特殊含义，而当成普通字符处理


d. 用||连接符号把&和后面的字符分开
select '&'||'a'
from dual;

单独一个&不具有特殊含义

或
select chr(38)||'a' from dual;

38是&符号的ascii编码


2.转义单引号''

a. 单引号本身就是转义符
select 'a''bc'
from dual;

ab之间连着写的两个单引号就代表一个单引号

单引号转义多用于pl/sql执行动态语句,如:
drop table t;
create table t(a varchar2(10), b varchar2(10));
declare
v_tab varchar2(30) := 't';
begin
execute immediate 'insert into '|| v_tab ||' values (''a'',''''''b'')';
end;
/
select * from t;


b. 10g以前，字符串只能放在两个单引号之间。10g新增了语法，q'?......?'，可以定义自己的引号符代替单引号
比如，用双引号或其他符号代替单引号
select q'"a'bc"' from dual;


select q'!a'bc!' from dual;
select q'xa'bcx' from dual;
select q'1a'bc1' from dual;
select q''a'bc'' from dual;
select q'谢a'bc谢' from dual;


用成对的括号也可以
select q'{a'bc}' from dual;
select q'[a'bc]' from dual;
select q'<a'bc>' from dual;
select q'(a'bc)' from dual;


c. chr函数和连接字符串
select ascii('''') from dual;
select 'a'||chr(39)||'bc' from dual;

39是单引号的ascii编码


3. 转义like查询中的通配符_和%
like查询有两种通配符, _下划线代表任意一个字符，%百分号代表任意个字符

比如，想查询含有_io_的参数
select name from v$parameter where name like '%_io_%' order by 1;

因为要查询的字段有通配符，'%_io_%' 表示含有?io?的字段, 所以operation,session什么的参数也查出来了，没有得到预想的结果

a. 加上escape转义符
select name from v$parameter where name like '%\_io\_%' escape '\' order by 1;

自定义\反斜杠为转义符，并对_下划线转义
查询结果正确

b.用instr
select name from v$parameter where instr(name,'_io_')>0 order by 1;


c.用translate或replace
用translate或replace函数将_下划线替换成别的字符，然后再比较
select name from v$parameter where translate(name,'_','#') like '%#io#%' order by 1;


或
select name from v$parameter where replace(name,'_','#') like '%#io#%' order by 1;



d.用正则表达式regular expression
10g下用regexp_like或regexp_instr:
select name from v$parameter where regexp_like(name,'.*_io_.*') order by 1;


或
select name from v$parameter where regexp_instr(name,'.*_io_.*')>0 order by 1;

'.*_io_.*'是正则表达式的模式pattern

9i sql语句中不支持正则表达式，没有regexp_like，得调用owa_pattern包
create or replace function my_match (p_str in varchar2, p_pat in varchar2)
return number
is
begin
if (owa_pattern.match(p_str, p_pat)) then
return 1;
else
return 0;
end if;
end;
/
show err
select name from v$parameter where my_match(name,'.*_io_.*')>0 order by 1;

速度很慢很慢


Xie wen（谢文）
Network & Operations, Mobile Software Solutions (MDB) MOTOROLA Inc.
No. 108 Jian Guo Road, Chao Yang District, Beijing 100022 P. R. China
e-mail wenxie at motorola.com



-fin-

generate sequence numbers - 用SQL语句生成序号

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/8/6
Subject: sql语句生成一组序号
To: xiewenxiewen at gmail.com


方法1. 建表

比如：
drop table t;
create table t(n number primary key) organization index;
exec for i in 1..10000 loop insert into t(n) values (i); end loop
commit;
select n from t where n between 15 and 22;

生成了15到22一组数字

这种方法效率低下，需要建表建数据，占用空间，浪费时间


方法2. pl/sql函数
create or replace
type numtab_type is table of number;
/
show err
create or replace function rowgen(p_a in number, p_b in number)
return numtab_type deterministic pipelined
as
begin
for i in p_a..p_b
loop
pipe row(i);
end loop;
return;
end rowgen;
/
show err
select column_value n from table(rowgen(15,22));

生成了15到22一组数据

8i不支持pipeline，函数得改成:
create or replace
function rowgen(p_a in number, p_b in number)
return numtab_type deterministic
as
v_numtab numtab_type;
begin
v_numtab := new numtab_type();
for i in p_a..p_b
loop
v_numtab.extend();
v_numtab(i-p_a+1) := i;
end loop;
return v_numtab;
end rowgen;
/
show err
select column_value from table(rowgen(15,22));




方法3. 用数据库中数据字典和rownum伪列
select rownum+15-1 n from all_objects where rownum <= 22-15+1;


如果想生成1000万条数据，一个all_objects不够用，则要交叉连接多个all_objects
如：

连接2个就够了
select rownum n from all_objects,all_objects where rownum <= 10000000;


方法4. 用connect by
select level+15-1 n from dual connect by level <= 22-15+1;


此方法速度很快：
set autot trace
select level+15-1 n from dual connect by level <= 22-15+1;

10g对dual表进行了优化，没有逻辑读和物理读
但是，8i下这么用有问题，不能用


方法5. group by cube
select rownum+15-1 n
from (select 1
from dual
group by cube(1,2,3))
where rownum<=22-15+1;

cube括号里有几个数字，子查询就会产生2^n条记录
select 1 from dual group by cube(0);
select 1 from dual group by cube(10,-1);



方法6. model
select integer_value n
from dual
where 0=1
model
dimension by ( 0 as key )
measures ( 0 as integer_value )
rules upsert ( integer_value[ for key from 15 to 22 increment 1 ] = cv(key) );


只有10g才能用


方法7. xmltable
select to_number(column_value) n from xmltable('15 to 23')


也是10g才能用


结论:
第四种connect by，又快又好，推荐使用


参考资料:
Oracle Row Generator Techniques
Generating an arbitrary amount of rows with Oracle SQL
Integer Series Generators
Can there be an infinite DUAL?



Xie wen（谢文）
Network & Operations,
Mobile Software Solutions (MDB) MOTOROLA Inc.
No. 108 Jian Guo Road, Chao Yang District, Beijing
100022 P. R. China
e-mail wenxie at motorola.com



-fin-

multitable insert - 多表插入语句

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/7/30
Subject: 多表插入 multitable insert
To: xiewenxiewen at gmail.com


多表插入是oracle 9i新增功能, 实现一条insert语句插入多个目标表，提高性能，简化代码

1.举例:
drop table a;
create table a(a int, b varchar2(50));
drop table b;
create table b(a int, b date);
insert all
into a values (object_id, object_name)
into b values (object_id, created)
select object_id, object_name,object_type,created
from all_objects
where object_name like 'USER_TABLE%';


select * from a;
select * from b;
rollback;

看到两个表都插入记录了



2.可以增加条件语句，有选择的插入记录
delete from a;
delete from b;
insert all
when otype = 'VIEW' and name ='USER_TABLES' then
into a (a,b) values(id,name)
when mod(id,2)=0 then
into b (a) values (id)
else
into b(a) values (id+10000)
select object_id id, object_name name ,object_type otype,created
from all_objects
where object_name like 'USER_TABLE%';


select * from a;
select * from b;



3.
insert all 的意思是，oracle依次判断每一个when条件是否满足，满足就插入记录，不满足的不插入
还有一种是insert first，意思是，依次判断每一个条件，不满足的不插入，只要发现第一个条件满足了就插入，然后跳过剩下的条件，不继续判断了

比如:
delete from a;
delete from b;
insert first
when otype = 'VIEW' and name ='USER_TABLES' then
into a (a,b) values(id,name)
when mod(id,2)=0 then
into b (a) values (id)
else
into b(a) values (id+10000)
select object_id id, object_name name ,object_type otype,created
from all_objects
where object_name like 'USER_TABLE%';


select * from a;
select * from b;



4.序列不能用在select里面
delete from a;
delete from b;
create sequence s;


insert all
into a(a) values (s)
into b(a) values (s)
select s.nextval s
from dual;


但可以用在into values后
insert all
into a(a) values (s.nextval)
into b(a) values (s.nextval)
select 1
from dual;


select * from a;
select * from b;

drop sequence s;



Xie wen（谢文）
Network & Operations, Mobile Software Solutions (MDB) MOTOROLA Inc.
No. 108 Jian Guo Road, Chao Yang District, Beijing 100022 P. R. China
e-mail wenxie at motorola.com




-fin-