1、创建表
SQL> create table t(id number,name varchar2(20));
表已创建。
2、常规方式插入
SQL> insert into t values(1,’a&b’);
输入 b 的值: a&b
原值 1: insert into t values(1,’a&b’)
新值 1: insert into t values(1,’aa&b’)
已创建 1 行。
3、chr(38)插入
SQL> insert into t values(2,’c’||chr(38)||’d’);
已创建 1 行。
4、set define
SQL> set define `
SQL> insert into t values(3,’e&f’);
已创建 1 行。
5、查看结果
SQL> select * from t;
ID NAME
———- ——————–
1 aa&b
2 c&d
3 e&f
6、说明
第一种方法不能得到正常的,我想要的结果,而且第一种方法在应用程序中也比较难实现
第二种比较适合应用程序实现
第三种比较适合sqlplus进行批量处理数据
DRBD安装配置说明
一、安装前说明
DRBD(Distributed Replicated Block Device),分布式复制块设备,是一种通过TCP/IP网络实现块设备数据实时镜像的方案。利用这种方案,单一主节点模式(single primary mode)双机系统能够实时地将业务数据保存在主备节点的磁盘中,正常情况下两个节点的数据是一模一样的。
根据官方的说明文档,如果系统内核(linux)版本低于2.6.33,在安装软件之前需要加载DRBD模块,如果高于(或等于)2.6.33,则只安装客户端软件。
如果没有安装DRBD模块,会在启动drbd时出现如下错误:
[root@node1 tmp]# /etc/init.d/drbd start
Starting DRBD resources: Can not load the drbd module.
其实就是安装一个kmod-drbd包即可
二、安装前环境设定
主机名 IP地址 DRBD使用磁盘
node1 10.10.10.1 /dev/sdb1
node2 10.10.10.2 /dev/sdb1
版本信息
[root@node1 /]# uname -a
Linux node1 2.6.9-89.0.0.0.1.ELsmp #1 SMP Tue May 19 04:23:49 EDT 2009 i686 i686 i386 GNU/Linux
[root@node2 test]# uname -a
Linux node2 2.6.9-89.0.0.0.1.ELsmp #1 SMP Tue May 19 04:23:49 EDT 2009 i686 i686 i386 GNU/Linux
挂载点:/drbd
三、安装步骤(两个节点相同)
1、安装kmod-drbd包
[root@node1 ~]# rpm -ivh /tmp/kmod-drbd83-smp-8.3.8-1.el4_8.i686.rpm
警告:/tmp/kmod-drbd83-smp-8.3.8-1.el4_8.i686.rpm: V3 DSA 簽章:NOKEY, key ID 443e1821
準備中… ########################################### [100%]
1:kmod-drbd83-smp ########################################### [100%]
2、安装drbd包
[root@node1 ~]# rpm -ivh /tmp/drbd83-8.3.8-1.el4_8.i386.rpm
警告:/tmp/drbd83-8.3.8-1.el4_8.i386.rpm: V3 DSA 簽章:NOKEY, key ID 443e1821
準備中… ########################################### [100%]
1:drbd83 ########################################### [100%]
四、fdisk磁盘(两个节点均要)
[root@node1 ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.
The number of cylinders for this disk is set to 2610.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-2610, default 2610): 200
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
五、/etc/drbd.conf配置(两个节点一样)
global { usage-count yes; } common { syncer { rate 10M; } } resource r0 { protocol C; disk { on-io-error detach; } net { after-sb-0pri disconnect; after-sb-1pri disconnect; after-sb-2pri disconnect; rr-conflict disconnect; } on node1 { device /dev/drbd0; disk /dev/sdb1; address 10.10.10.1:7788; meta-disk internal; } on node2 { device /dev/drbd0; disk /dev/sdb1; address 10.10.10.2:7788; meta-disk internal; } }
六、创建DRBD资源
[root@node1 tmp]# drbdadm create-md r0 //r0为配置文件中定义的资源名
[root@node2 tmp]# drbdadm create-md r0
七、DRBD的启动、关闭以及测试
1、启动DRBD
[root@node1 ~]# /etc/init.d/drbd start
[root@node2 ~]# /etc/init.d/drbd start
2、查看状态(两种方法均可)
[root@node1 tmp]# cat /proc/drbd
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
0: cs:Connected ro:Secondary/Secondary ds:Inconsistent/Inconsistent C r—-
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:b oos:1606380
[root@node2 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
m:res cs ro ds p mounted fstype
0:r0 Connected Secondary/Secondary Inconsistent/Inconsistent C
3、设置主机
[root@node1 tmp]# drbdsetup /dev/drbd0 primary -o
4、再查看状态
[root@node1 tmp]# cat /proc/drbd
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r—-
ns:15520 nr:0 dw:0 dr:15520 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:b oos:1590860
[>………………..] sync’ed: 1.3% (1590860/1606380)K delay_probe: 0
finish: 0:03:23 speed: 7,760 (7,760) K/sec
[root@node2 ~]# /etc/init.d/drbd status //表示数据初始化已经完成
drbd driver loaded OK; device status:
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
m:res cs ro ds p mounted fstype
0:r0 Connected Secondary/Primary UpToDate/UpToDate C
5、主节点磁盘操作
[root@node1 tmp]# mkfs.ext3 /dev/drbd0
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
200928 inodes, 401595 blocks
20079 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=415236096
13 block groups
32768 blocks per group, 32768 fragments per group
15456 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
[root@node1 tmp]# mkdir /drbd
[root@node1 tmp]# mount /dev/drbd0 /drbd
[root@node1 tmp]# df -h
Filesystem 容量 已用 可用 已用% 挂载点
/dev/sda2 18G 9.1G 7.7G 55% /
/dev/sda1 99M 13M 82M 14% /boot
none 395M 0 395M 0% /dev/shm
/dev/drbd0 1.6G 35M 1.4G 3% /drbd
[root@node1 tmp]# cd /drbd/
[root@node1 drbd]# ll
总用量 16
drwx—— 2 root root 16384 7月 14 15:01 lost+found
[root@node1 drbd]# mkdir test
[root@node1 drbd]# mkdir test
[root@node1 drbd]# cd test
[root@node1 test]# vi xifenfei.cf
drbd 测试 //vi 写入xifenfei.cf中数据
6、启用备机磁盘
[root@node1 /]# umount /drbd/
[root@node1 /]# drbdsetup 0 secondary
[root@node2 ~]# drbdadm primary r0
[root@node2 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
m:res cs ro ds p mounted fstype
0:r0 Connected Primary/Secondary UpToDate/UpToDate C
[root@node2 ~]# mkdir /drbd
[root@node2 ~]# mount /dev/drbd0 /drbd
[root@node2 ~]# df -h
Filesystem 容量 已用 可用 已用% 挂载点
/dev/sda2 18G 9.1G 7.7G 55% /
/dev/sda1 99M 13M 82M 14% /boot
none 395M 0 395M 0% /dev/shm
/dev/drbd0 1.6G 35M 1.4G 3% /drbd
[root@node2 ~]# cd /drbd
[root@node2 drbd]# ll
总用量 20
drwx—— 2 root root 16384 7月 14 15:01 lost+found
drwxr-xr-x 2 root root 4096 7月 14 15:05 test
[root@node2 drbd]# cd test/
[root@node2 test]# ll
总用量 4
-rw-r–r– 1 root root 10 7月 14 15:05 chengfei.cf
[root@node2 test]# more xifenfei.cf
drbd 测试 //主节点中 vi 写入xifenfei.cf中数据
heartbeat中ha.cf参数说明
#debugfile /var/log/ha-debug
说明:调试日志文件文件,取默认值
logfile /var/log/ha-log
说明:系统运行日志文件,取默认值
#logfacility local0
说明:用于syslog()/logger的设备
keepalive 2
说明:心跳频率,自己设定。1:表示1秒;200ms:表示200毫秒
deadtime 30
说明:节点死亡时间阀值,就是从节点在过了10后还没有收到心跳就认为主节点死亡,自己设定
warntime 10
说明:发出警告时间,自己设定
udpport 694
说明:心跳信息传递的udp端口,自己设定
#bcast eth0 # Linux
说明:采用udp广播播来通知心跳,建议在副节点不只一台时使用
ucast eth0 172.30.31.68
说明:采用网卡eth0的udp单播来通知心跳,eth0的IP
#mcast eth0 225.0.0.1 694 1 0
说明:采用udp多播播来通知心跳,建议在副节点不只一台时使用
auto_failback off
说明:主节点重启成功后,资源是自动拿回到主节点还是等到副节点down调后拿回资源
node heartbeat1
说明:主节点名称,与uname –n保持一致。排在第一的默认为主节点,所以不要搞措顺序
node heartbeat2
说明:副节点名称,与uname –n保持一致
watchdog /dev/watchdog
说明:看门狗。如果本节点在超过一分钟后还没有发出心跳,那么本节点自动重启
以上这些是我个人认为必配项,下面这些是可选项
stonith baytech /etc/ha.d/conf/stonith.baytech
说明:主/副等所有节点的一种校验。
respawn userid /path/name/to/run
说明:和heartbeat必须一起启动的本地服务
ping 10.10.10.254
说明:伪节点IP,伪节点就是其失效时主/副节点不会正常工作但本身不是主/副节点之一。
respawn hacluster /usr/lib/heartbeat/ipfail
说明:与ping选项一起使用,取默认值。
baud 19200
说明:串口波特率,与serial一起使用。
serial /dev/ttyS0 # Linux
说明:采用串口来传递心跳信息。
Heartbeat安装及简单配置
ORA-01578坏块解决(2)
ORA-01578坏块解决(1)续集
如果在坏块之前,有rman备份,可以使用rman的备份来进行恢复,确保数据不会被丢失
1、使用rman进行恢复
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sun Aug 14 22:21:13 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906)
RMAN> blockrecover datafile 6 block 1477;
Starting blockrecover at 2011-08-14 22:21:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /tmp/0fmk0ii5_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/tmp/0fmk0ii5_1_1 tag=TAG20110814T213357
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 2011-08-14 22:21:23
2、检查坏块是否被恢复
RMAN> backup check logical validate datafile 6;
Starting backup at 2011-08-14 22:22:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:22:12
RMAN> exit
Recovery Manager complete.
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:17 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> select file#,block#,blocks from v$database_block_corruption;
no rows selected
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 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:38 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 1372
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1140
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1256690 (0.1256690)
3、验证数据是否正确
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:34:18 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> select count(*) from t_rep;
COUNT(*)
———-
49857
和ORA-01578坏块解决(1)中的模拟环境比较,数据恢复正确,坏块问题解决
ORA-01578坏块解决(1)
物化视图on prebuilt table
1、ORA-12059
执行语句:
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
AS
SELECT * FROM SCOTT.emp
错误提示:
ORA-12059: prebuilt table “CHF”.”MV_T2″ does not exist
错误原因:
物化视图对应的表不存在(物化视图需要和表同名,结构相同)
解决方法:
CREATE TABLE mv_t2 AS
SELECT * FROM scott.emp WHERE 1=0;
2、ORA-23413
执行语句:
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on demand
AS
SELECT * FROM SCOTT.emp
错误提示:
ORA-23413: table “SCOTT”.”EMP” does not have a materialized view log
错误原因:
使用fast模式刷新物化视图,需要有物化视图日志
解决方法:
1)创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON scott.emp ;
2)刷新模式改为force,其实实质是采用了complete刷新模式
3、ORA-12058
执行语句(mv log是基于rowid,表无主键)
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
WITH ROWID
AS
SELECT * FROM SCOTT.emp
错误提示:
ORA-12058: materialized view cannot use prebuilt table
错误原因:
fast刷新模式不能基于rowid进行
解决方法:
1)删除mv log,表添加主键,采用基于主键模式重新建mv log和物化视图
DROP MATERIALIZED VIEW mv_t1;
ALTER TABLE mv_t2 ADD PRIMARY KEY (EMPNO);
CREATE MATERIALIZED VIEW LOG ON scott.emp;
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
–WITH ROWID
AS
SELECT * FROM SCOTT.emp;
2)刷新模式改为force,其实实质是采用了complete刷新模式
4、总结
1)在执行创建物化视图之前,需要先创建同名、同结构表
2)如果使用fast模式刷新(疑惑在线重定义可以通过rowid实现,为什么直接通过物化视图就不可以),物化视图同表需要主键,需要物化视图日志
3)通过drop mv,发现同名表还存在,和on prebuilt table本质区别
bbed破坏数据文件
oracle 10g rman自动创建数据文件
oracle官方建议,如果修改过数据库结构后,需要立即重新备份数据库,我想通过试验验证该知识点。
试验过程是使用rman备份数据库,然后添加表空间,添加数据文件,创建表在新表空间和新数据文件上,然后关闭数据库,删除新添加的数据文件,再使用rman备份来恢复数据库。操作过程如下:
在线重定义原理探讨
1、准备测试表
SQL> create table t_d as select * from all_objects; Table created SQL> alter table t_d add primary key(object_id); Table altered SQL> create table t_d_t as select * from t_d where 1=0; Table created SQL> alter table t_d_t add primary key(object_id); Table altered SQL> select count(*) from T_D; COUNT(*) ---------- 48945 SQL> select count(*) from T_D_T; COUNT(*) ---------- 0
2、执行在线同步
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T_D', DBMS_REDEFINITION.CONS_USE_PK); PL/SQL procedure successfully completed SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_D', 'T_D_T'); PL/SQL procedure successfully completed
3、查询相关物化视图
SQL> select owner,mview_name from user_mviews; OWNER MVIEW_NAME ------------------------------ ------------------------------ CHF T_D_T SQL> select log_owner,master,log_table from user_mview_logs; LOG_OWNER MASTER LOG_TABLE ------------------------------ ------------------------------ ------------------------------ CHF T_D MLOG$_T_D CREATE MATERIALIZED VIEW T_D_T ON PREBUILT TABLE REFRESH FAST ON DEMAND AS SELECT "T_D"."OWNER" "OWNER","T_D"."OBJECT_NAME" "OBJECT_NAME", "T_D"."SUBOBJECT_NAME" "SUBOBJECT_NAME","T_D"."OBJECT_ID" "OBJECT_ID", "T_D"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T_D"."OBJECT_TYPE" "OBJECT_TYPE", "T_D"."CREATED" "CREATED","T_D"."LAST_DDL_TIME" "LAST_DDL_TIME", "T_D"."TIMESTAMP" "TIMESTAMP","T_D"."STATUS" "STATUS", "T_D"."TEMPORARY" "TEMPORARY","T_D"."GENERATED" "GENERATED", "T_D"."SECONDARY" "SECONDARY" FROM "CHF"."T_D" "T_D";
4、结束物化视图
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T_D', 'T_D_T'); PL/SQL procedure successfully completed
5、继续查询相关物化视图
SQL> select log_owner,master,log_table from user_mview_logs; SQL> select * from user_mviews; SQL> select owner,mview_name from user_mviews; OWNER MVIEW_NAME ------------------------------ ------------------------------ SQL> select log_owner,master,log_table from user_mview_logs; LOG_OWNER MASTER LOG_TABLE ------------------------------ ------------------------------ ------------------------ SQL> select count(*) from T_D_T; COUNT(*) ---------- 48945
6、由试验可以得出
6.1)在线重定义本质就是利用物化视图刷新实现数据迁移
6.2)DBMS_REDEFINITION.START_REDEF_TABLE实现功能:
6.2.1)创建含on prebuilt table的物化视图和物化视图日志
6.2.2)实现通过物化视图刷新当前表中数据进入中间表
6.3)dbms_redefinition.sync_interim_table实现物化视图刷新在执行5.2)操作过程中变化数据
6.4)DBMS_REDEFINITION.FINISH_REDEF_TABLE将锁定原表,防止表上的DML,物化视图执行刷新,完成刷新后,将删除物化视图和对应的日志,将中间表rename成目标表
7、如果表无主键时,区别有
7.1)执行在线定义语句,具体见表在线重定义(无主键)
7.2)创建物化视图语句上
create materialized view T_D_T on prebuilt table refresh fast on demand with rowid as select OWNER OWNER, OBJECT_NAME OBJECT_NAME, SUBOBJECT_NAME SUBOBJECT_NAME, OBJECT_ID OBJECT_ID, DATA_OBJECT_ID DATA_OBJECT_ID, OBJECT_TYPE OBJECT_TYPE, CREATED CREATED, LAST_DDL_TIME LAST_DDL_TIME, TIMESTAMP TIMESTAMP, STATUS STATUS, TEMPORARY TEMPORARY, GENERATED GENERATED, SECONDARY SECONDARY from "CHF"."T_D" "T_D";