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)

一、创建测试表
SQL> create table t_rep as
2  select * from all_objects;
Table created.
SQL> select count(*) from  t_rep;
COUNT(*)
———-
49857
二、使用bbed修改数据块
三、错误现象
1、sqlplus窗口
SQL> select count(*) from  t_rep;
select count(*) from  t_rep
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 1477)
ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’
2、alert.log文件中
Sun Aug 14 22:01:14 2011
Hex dump of (file 6, block 1477) in trace file /opt/oracle/admin/test/udump/test_ora_10785.trc
Corrupt block relative dba: 0x018005c5 (file 6, block 1477)
Bad check value found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x018005c5
last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x28ef0602
check value in block header: 0x493
computed block checksum: 0x44b9
Reread of rdba: 0x018005c5 (file 6, block 1477) found same corrupted data
Sun Aug 14 22:01:15 2011
Corrupt Block Found
TSN = 6, TSNAME = XFF
RFN = 6, BLK = 1477, RDBA = 25167301
OBJN = 52727, OBJD = 52728, OBJECT = T_REP, SUBOBJECT =
SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
四、验证是否真的坏块
1、dbv验证
[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:08:37 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf
Page 1477 is marked corrupt
Corrupt block relative dba: 0x018005c5 (file 6, block 1477)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x018005c5
last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x28ef0602
check value in block header: 0x493
computed block checksum: 0x44b9
DBVERIFY – Verification complete
Total Pages Examined         : 2560
Total Pages Processed (Data) : 1371
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   : 1
Total Pages Influx           : 0
Highest block SCN            : 1256043 (0.1256043)
2、rman验证
RMAN> backup check logical validate datafile 6;
Starting backup at 2011-08-14 22:09:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
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:09:53
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:10:00 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;SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);
FILE#     BLOCK#     BLOCKS
———- ———- ———-
6       1477          1
五、跳过坏块读取其他数据
SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);
PL/SQL procedure successfully completed.
SQL> select skip_corrupt from dba_tables where table_name=’T_REP’;
SKIP_COR
——–
ENABLED
SQL> select count(*) from t_rep;
COUNT(*)
———-
49794
说明:数据发生丢失6号文件的1477块中的数据丢失

物化视图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破坏数据文件

一、安装bbed
[oracle@ECP-UC-DB1 bin]$ cd $ORACLE_HOME/rdbms/lib
[oracle@ECP-UC-DB1 lib]$ ls -al *bb*
-rw-r–r– 1 oracle oinstall 1863 Mar 11 2008 sbbdpt.o
-rw-r–r– 1 oracle oinstall 1191 Mar 11 2008 ssbbded.o
[oracle@ECP-UC-DB1 lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed
[oracle@ECP-UC-DB1 lib]$ mv bbed $ORACLE_HOME/bin
[oracle@ECP-UC-DB1 lib]$ cd ~
二、bben入门
[oracle@ECP-UC-DB1 ~]$ bbed
Password: blockedit(默认该密码)
BBED: Release 2.0.0.0.0 – Limited Production on Sun Aug 14 19:56:10 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> help all
SET DBA [ dba | file#, block# ]
SET FILENAME ‘filename’
SET FILE file#
SET BLOCK [+/-]block#
SET OFFSET [ [+/-]byte offset | symbol | *symbol ]
SET BLOCKSIZE bytes
SET LIST[FILE] ‘filename’
SET WIDTH character_count
SET COUNT bytes_to_display
SET IBASE [ HEX | OCT | DEC ]
SET OBASE [ HEX | OCT | DEC ]
SET MODE [ BROWSE | EDIT ]
SET SPOOL [ Y | N ]
SHOW [ <SET parameter> | ALL ]
INFO
MAP[/v] [ DBA | FILENAME | FILE | BLOCK ]
DUMP[/v] [ DBA | FILENAME | FILE | BLOCK | OFFSET | COUNT ]
PRINT[/x|d|u|o|c] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
EXAMINE[/Nuf] [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
</Nuf>:
N – a number which specifies a repeat count.
u – a letter which specifies a unit size:
b – b1, ub1 (byte)
h – b2, ub2 (half-word)
w – b4, ub4(word)
r – Oracle table/index row
f – a letter which specifies a display format:
x – hexadecimal
d – decimal
u – unsigned decimal
o – octal
c – character (native)
n – Oracle number
t – Oracle date
i – Oracle rowid
FIND[/x|d|u|o|c] numeric/character string [ TOP | CURR ]
COPY [ DBA | FILE | FILENAME | BLOCK ] TO [ DBA | FILE | FILENAME | BLOCK ]
MODIFY[/x|d|u|o|c] numeric/character string
[ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]
SUM [ DBA | FILE | FILENAME | BLOCK ] [ APPLY ]
PUSH [ DBA | FILE | FILENAME | BLOCK | OFFSET ]
POP [ALL]
REVERT [ DBA | FILE | FILENAME | BLOCK ]
UNDO
HELP [ <bbed command> | ALL ]
VERIFY [ DBA | FILE | FILENAME | BLOCK ]
CORRUPT [ DBA | FILE | FILENAME | BLOCK ]
三、创建测试表
[oracle@ECP-UC-DB1 ~]$ sqlplus chf/xifenfei
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 19:58:46 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> create table t_bbed (id number,name varchar2(100)) tablespace xff;
Table created.
SQL> insert into t_bbed values(1,’xifenfei’);
1 row created.
SQL> insert into t_bbed values(2,’xff’);
1 row created.
SQL> insert into t_bbed values(3,’chengfei’);
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter system checkpoint;
System altered.
SQL> conn chf/xifenfei
Connected.
SQL> set serveroutput on
SQL> declare
ridtype number;
objnum number;
relfno number;
blno number;
rowno number;
rid rowid;
begin
select rowid into rid from t_bbed where id=1;
dbms_rowid.rowid_info(rid,ridtype,objnum,relfno,blno,rowno,’SMALLFILE’);
dbms_output.put_line(‘Row Typ-‘||to_char(ridtype));
dbms_output.put_line(‘Obj No-‘||to_char(objnum));
dbms_output.put_line(‘RFNO-‘||to_char(relfno));
dbms_output.put_line(‘Block No-‘||to_char(blno));
dbms_output.put_line(‘Row No-‘||to_char(rowno));
end;–查看rowid对应的文件号,块号等信息
/
Row Typ-1
Obj No-52721
RFNO-6
Block No-780
Row No-1
PL/SQL procedure successfully completed.
四、bben准备工作
1、创建参数文件
[oracle@ECP-UC-DB1 ~]$ cat parfile.conf
blocksize=8192
listfile=list
mode=edit
2、创建数据文件列表
[oracle@ECP-UC-DB1 ~]$ cat list
1 /opt/oracle/oradata/test/xifenfei01.dbf 2097160192
五、修改数据块中内容
[oracle@ECP-UC-DB1 ~]$ bbed parfile=parfile.conf
Password:
BBED: Release 2.0.0.0.0 – Limited Production on Sun Aug 14 20:35:41 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
1、定位需要修改块
BBED> set dba 1,780
DBA 0x0040030c (4195084 1,780)
2、查看相关内容
BBED> find /c xifenfei
File: /opt/oracle/oradata/test/xifenfei01.dbf (1)
Block: 780 Offsets: 8180 to 8191 Dba:0x0040030c
————————————————————————
78696665 6e666569 01062bce
<32 bytes per line>
3、dump文件中内容
BBED> dump /v dba 1,780 offset 8180 count 32
File: /opt/oracle/oradata/test/xifenfei01.dbf (1)
Block: 780 Offsets: 8170 to 8191 Dba:0x0040030c
——————————————————-
7866662c 010202c1 02087869 66656e66 l xff,……xifenf
65690106 2bce l ei..+.
<16 bytes per line>
4、修改数据块内容
BBED> modify 500 dba 1,780
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/test/xifenfei01.dbf (1)
Block: 780 Offsets: 8180 to 8191 Dba:0x0040030c
————————————————————————
01f46665 6e666569 01062bce
<32 bytes per line>
BBED> find /c xienfei
BBED-00212: search string not found
BBED> exit
六、验证修改是否成功
1、dbv验证
[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 20:30:56 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf
Page 780 is marked corrupt
Corrupt block relative dba: 0x0180030c (file 6, block 780)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0180030c
last change scn: 0x0000.0012ce2b seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xce2b0601
check value in block header: 0x6382
computed block checksum: 0x1c
DBVERIFY – Verification complete
Total Pages Examined : 1280
Total Pages Processed (Data) : 690
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 31
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 558
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1232338 (0.1232338)
2、rman验证
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sun Aug 14 20:39:55 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906)
RMAN> backup check logical validate datafile 6;
Starting backup at 2011-08-14 20:40:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
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 20:40:08
RMAN> exit
Recovery Manager complete.
3、直接查询验证
[oracle@ECP-UC-DB1 ~]$ sqlplus chf/xifenfei
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 20:40:14 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;
FILE# BLOCK# BLOCKS
———- ———- ———-
6 780 1
SQL> conn / as sysdba
Connected.
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> select * from chf.t_bbed;
select * from chf.t_bbed
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 780)
ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’

oracle 10g rman自动创建数据文件

oracle官方建议,如果修改过数据库结构后,需要立即重新备份数据库,我想通过试验验证该知识点。
试验过程是使用rman备份数据库,然后添加表空间,添加数据文件,创建表在新表空间和新数据文件上,然后关闭数据库,删除新添加的数据文件,再使用rman备份来恢复数据库。操作过程如下:

1、当前数据库表空间已经数据文件情况
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
SQL> select file#,name from v$datafile;
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
2、rman备份数据库
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sat Aug 13 21:44:36 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906)
RMAN> backup database format ‘/tmp/%U’ plus archivelog delete input;
Starting backup at 2011-08-13 21:46:46
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=1 stamp=757977034
input archive log thread=1 sequence=4 recid=2 stamp=758152833
input archive log thread=1 sequence=5 recid=3 stamp=758320953
input archive log thread=1 sequence=6 recid=4 stamp=758321218
input archive log thread=1 sequence=7 recid=5 stamp=758412073
input archive log thread=1 sequence=8 recid=6 stamp=758574035
input archive log thread=1 sequence=9 recid=7 stamp=758665608
input archive log thread=1 sequence=10 recid=8 stamp=758757646
input archive log thread=1 sequence=11 recid=9 stamp=758844058
input archive log thread=1 sequence=12 recid=10 stamp=758930497
input archive log thread=1 sequence=13 recid=11 stamp=759027608
input archive log thread=1 sequence=14 recid=12 stamp=759102408
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:46:50
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:47:35
piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214648_74f02bg3_.bkp tag=TAG20110813T214648 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/opt/oracle/oradata/test/archivelog/1_3_757860476.dbf recid=1 stamp=757977034
archive log filename=/opt/oracle/oradata/test/archivelog/1_4_757860476.dbf recid=2 stamp=758152833
archive log filename=/opt/oracle/oradata/test/archivelog/1_5_757860476.dbf recid=3 stamp=758320953
archive log filename=/opt/oracle/oradata/test/archivelog/1_6_757860476.dbf recid=4 stamp=758321218
archive log filename=/opt/oracle/oradata/test/archivelog/1_7_757860476.dbf recid=5 stamp=758412073
archive log filename=/opt/oracle/oradata/test/archivelog/1_8_757860476.dbf recid=6 stamp=758574035
archive log filename=/opt/oracle/oradata/test/archivelog/1_9_757860476.dbf recid=7 stamp=758665608
archive log filename=/opt/oracle/oradata/test/archivelog/1_10_757860476.dbf recid=8 stamp=758757646
archive log filename=/opt/oracle/oradata/test/archivelog/1_11_757860476.dbf recid=9 stamp=758844058
archive log filename=/opt/oracle/oradata/test/archivelog/1_12_757860476.dbf recid=10 stamp=758930497
archive log filename=/opt/oracle/oradata/test/archivelog/1_13_757860476.dbf recid=11 stamp=759027608
archive log filename=/opt/oracle/oradata/test/archivelog/1_14_757860476.dbf recid=12 stamp=759102408
Finished backup at 2011-08-13 21:47:36
Starting backup at 2011-08-13 21:47:37
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=00001 name=/opt/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/test/sysaux01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/test/undotbs01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/test/user32g.dbf
input datafile fno=00004 name=/opt/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:47:37
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:12
piece handle=/tmp/06mjtuvp_1_1 tag=TAG20110813T214737 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:48:14
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:15
piece handle=/tmp/07mjtv0s_1_1 tag=TAG20110813T214737 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2011-08-13 21:48:15
Starting backup at 2011-08-13 21:48:15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=13 stamp=759102495
channel ORA_DISK_1: starting piece 1 at 2011-08-13 21:48:16
channel ORA_DISK_1: finished piece 1 at 2011-08-13 21:48:17
piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214815_74f050vl_.bkp tag=TAG20110813T214815 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/opt/oracle/oradata/test/archivelog/1_15_757860476.dbf recid=13 stamp=759102495
Finished backup at 2011-08-13 21:48:17
3、添加表空间/数据文件
SQL> create tablespace xff datafile ‘/opt/oracle/oradata/test/xifenfei01.dbf’ size 10m autoextend on next 10m maxsize 5G;
Tablespace created.
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user02.dbf’ size 10m autoextend on next 10m maxsize 5G;
Tablespace altered.
4、创建测试表
SQL> create table chf.t_1 tablespace xff
2 as
3 select * from all_objects;
Table created.
SQL> create table chf.t_2 tablespace users
2 as
3 select * from all_objects;
Table created.
SQL> select count(*) from chf.t_1;
COUNT(*)
———-
49855
SQL> select count(*) from chf.t_2;
COUNT(*)
———-
49856
SQL> select file#,name from v$datafile;
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
7 rows selected.
5、关闭数据库
SQL> shutdown abort
ORACLE instance shut down.
6、删除相关数据文件
[oracle@ECP-UC-DB1 ~]$ cd /opt/oracle/oradata/test/
[oracle@ECP-UC-DB1 test]$ ll
total 1066968
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:48 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:55 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:55 undotbs01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 user02.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 user32g.dbf
-rw-r—– 1 oracle oinstall 5251072 Aug 13 21:55 users01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 21:55 xifenfei01.dbf
[oracle@ECP-UC-DB1 test]$ rm xifenfei01.dbf
[oracle@ECP-UC-DB1 test]$ rm user*.dbf
[oracle@ECP-UC-DB1 test]$ ll
total 1031036
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 21:56 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:48 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:55 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:55 undotbs01.dbf
6、开启数据库
SQL> startup
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2082784 bytes
Variable Size 125831200 bytes
Database Buffers 75497472 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/opt/oracle/oradata/test/users01.dbf’
7、alert.log文件报错
Sat Aug 13 21:58:22 2011
ALTER DATABASE OPEN
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/opt/oracle/oradata/test/users01.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/opt/oracle/oradata/test/user32g.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 6 – see DBWR trace file
ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 13 21:58:22 2011
Errors in file /opt/oracle/admin/test/bdump/test_dbw0_25268.trc:
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘/opt/oracle/oradata/test/user02.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN…
8、登录rman
[oracle@ECP-UC-DB1 test]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sat Aug 13 22:00:03 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906, not open)
9、rman还原预览
RMAN> restore datafile 4,5,6,7 preview;
Starting restore at 2011-08-13 22:01:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
data file 6 will be created automatically during restore operation
data file 7 will be created automatically during restore operation
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— ——————-
4 Full 602.67M DISK 00:00:30 2011-08-13 21:47:38
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20110813T214737
Piece Name: /tmp/06mjtuvp_1_1
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——————- —-
4 Full 1177858 2011-08-13 21:47:37 /opt/oracle/oradata/test/users01.dbf
5 Full 1177858 2011-08-13 21:47:37 /opt/oracle/oradata/test/user32g.dbf
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
——- ———- ———– ———— ——————-
6 2.00K DISK 00:00:01 2011-08-13 21:48:16
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20110813T214815
Piece Name: /opt/oracle/flash_recovery_area/TEST/backupset/2011_08_13/o1_mf_annnn_TAG20110813T214815_74f050vl_.bkp
List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
—- ——- ———- ——————- ———- ———
1 15 1177835 2011-08-13 21:46:46 1177874 2011-08-13 21:48:15
Media recovery start SCN is 1177858
Recovery must be done beyond SCN 1178068 to clear data files fuzziness
Finished restore at 2011-08-13 22:01:30
10、还原数据文件
RMAN> restore datafile 4,5,6,7;
Starting restore at 2011-08-13 22:02:04
using channel ORA_DISK_1
creating datafile fno=6 name=/opt/oracle/oradata/test/xifenfei01.dbf
creating datafile fno=7 name=/opt/oracle/oradata/test/user02.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /opt/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/test/user32g.dbf
channel ORA_DISK_1: reading from backup piece /tmp/06mjtuvp_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/06mjtuvp_1_1 tag=TAG20110813T214737
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2011-08-13 22:02:08
11、查看恢复后的数据文件情况
[oracle@ECP-UC-DB1 ~]$ cd /opt/oracle/oradata/test
[oracle@ECP-UC-DB1 test]$ ll
total 1066968
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 21:48 archivelog
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control01.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control02.ctl
-rw-r—– 1 oracle oinstall 7061504 Aug 13 22:03 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:55 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:46 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Aug 13 21:48 redo03.log
-rw-r—– 1 oracle oinstall 335552512 Aug 13 21:58 sysaux01.dbf
-rw-r—– 1 oracle oinstall 513810432 Aug 13 21:58 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Aug 13 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 26222592 Aug 13 21:58 undotbs01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 user02.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 user32g.dbf
-rw-r—– 1 oracle oinstall 5251072 Aug 13 22:02 users01.dbf
-rw-r—– 1 oracle oinstall 10493952 Aug 13 22:02 xifenfei01.dbf
12、恢复数据文件
RMAN> recover datafile 4,5,6,7;
Starting recover at 2011-08-13 22:04:21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 2011-08-13 22:04:25
13、open数据库
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
14、验证rman恢复结果
[oracle@ECP-UC-DB1 test]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Aug 13 22:05:25 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#,name from v$datafile;
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
7 rows selected.
SQL> select count(*) from chf.t_1;
COUNT(*)
———-
49855
SQL> select count(*) from chf.t_2;
COUNT(*)
———-
49856
注:如果在rman的还原过程中,没有自动创建数据文件,需要用命令创建alter database datafile n或者alter database datafile ‘path’,然后进行恢复

在线重定义原理探讨

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";

RAC负载均衡配置

1、客户端均衡(Client-Side LB)
工作原理:当客户端发起连接时,会从地址列表中随机选取一个,再使用随机算法把连接请求分散到各个实例。
存在缺点:
1.1)分配连接时没有考虑每个节点的真实负载,最后分配不过不一定是平衡
1.2)随机算法需要长时间片,如果在短时间内同时发起多个连接,这些连接有可能被分配到一个节点上
1.3)有些情况下,连接可能被分配到故障节点上
配置方法:在tns中添加LOAD_BALANCE = YES条目
2、服务器端均衡(Server-Side LB)
工作原理:
2.1)该均衡实现是依赖于Listener收集的负载信息。在数据库运行过程中,PMON后台进程会收集数系统的负载信息,然后登记到Listener中。
2.2)PMON进程不仅会向本地的Listener注册,也会想其他节点上的Listener注册,但到底向何处注册,是由Remote_Listeners和Local_Listener这两个参数决定。Local_Listener不用设置,而Remote_Listeners需要设置,参数值有一个tnsnames项。
2.3)当收到客户端连接请求时,就会把连接转给负载最小的节点,这个节点可能是自己,也可能是其他节点,也就是Listener会转发客户端的连接请求。
配置方法:

SQL> show parameter listener;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
remote_listener                      string      LISTENERS_DEVDB
tnsnames.ora
LISTENERS_DEVDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
  )
listener.ora(除掉SID_LIST_LISTENER_NAME项)
LISTENER_RAC1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521)(IP = FIRST))
    )
  )

3、两者联合使用
Server-Side LB和Client-Side LB不是互斥的,两者可以一起工作,这个时候客户端的连接请求会先从地址列表中随机选择一个地址,然后向该地址的Listener发送请求;Listener接到请求后,根据各个节点负载情况从中挑选出最合适的节点转发连接请求。

RAC Failover三种方式

1、Client-Side Connect Time Failover
1.1)在用户端tnsname中配置了多个地址,用户发起连接请求时,会先尝试连接地址表中的第一个地址,如果这个连接尝试失败,则继续尝试使用第二个地址,直至连接成功或者遍历了所有的地址。
1.2)这种Failover的特点是:在建立连接那一时刻起作用,一旦连接建立之后,节点出现故障都不会作处理,从而客户端的表现就是会话断开,用户程序必须重新建立连接。
启用该方法:在客户端tnsname.ora中添加FAILOVER=ON条目,因为这个参数默认值就是为NO,所以即使客户端不加该条目,也有这种Failover功能。

XFF_F =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.22)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb)
    )
  )

2、TAF(Transparent Application Failover)
2.1)在连接建立以后、应用系统运行过程中,如果某个实例发生故障,连接到这个实例上的用户会被自动迁移到其他的健康的实例上。对于应用程序而言,这个迁移过程是透明的,不需要用户的介入,当然在迁移过程中,未提交的事物会回滚。
2.2)与Client-Side Connect Time Failover比较起来,就是多了FAILOVER_MODE这一配置项,该配置项包含4个子项目
2.2.1)METHOD:可选值有BASIC和PRECONNECT
BASIC是指在感知到节点故障时才创建到其他实例的连接
PRECONNECT是在最初建立连接时就同时建立到所有实例的连接,当发生故障时,立刻就可以切换到其他链路上。
2.2.2)TYPE:可选值有SESSION和SELECT
两者的区别在于对select语句的处理,select表示如果发生故障迁移,正在执行的select语句将在新的节点上继续返回后续结果集;而session表示重新执行该select查询返回全部的结果。
2.2.3)DELAY表示重试间隔时间
2.2.4)RETRIES表示重试次数

XFF_T =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.22)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

3、Server-Side TAF
3.1)Server-Side TAF具有TAF的所有特点
3.2)这种TAF是在服务器上配置,不需要在客户端进行相关配置,如果修改一个参数,不需要在所有的tns上修改,而只要修改服务器中的service即可
用户有两种角色可以选择
PREFERRED:首选实例,会优先选择拥有这个角色的实例提供服务
AVAILABLE:后备实例,当PREFERRED实例不可用时,才会转到AVAILABLE实例上

XFF_RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.22)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XFF)
    )
  )

利用物化视图刷数据

1、创建测试表
SQL> CREATE TABLE t_M
2 AS
3 SELECT * FROM all_objects;
Table created
2、查询测试表中记录
SQL> select count(*) from t_m;
COUNT(*)
———-
48941
3、创建中间表
SQL> create table t_m_n as
2 select * from t_m where 1=0;
Table created
4、查询中间表记录
SQL> select count(*) from t_m_n;
COUNT(*)
———-
5、创建刷新物化视图
SQL> CREATE MATERIALIZED VIEW t_m_n
on prebuilt TABLE WITH REDUCED PRECISION
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM t_m;
Materialized view created
6、执行物化视图刷新
SQL> exec dbms_mview.refresh(‘T_M_N’);
PL/SQL procedure successfully completed
7、查询物化视图中记录数
SQL> select count(*) from t_m_n;
COUNT(*)
———-
48941
8、删除物化视图
SQL> DROP MATERIALIZED VIEW T_M_N;
Materialized view dropped
9、查询中间表中条数
SQL> select count(*) from t_m_n;
COUNT(*)
———-
48941
10、后续可能操作
1)t_m和t_m_n相互重命名,实现在线修移动表的位置、改表结构、降低高水位等操作,同(shrink)
2)和dblink结合,实现数据的跨版本迁移

集群服务启动与关闭(10g)

一、crs开启和关闭
关闭crs
/etc/init.d/init.crs stop
开启crs
/etc/init.d/init.crs start
二、启动和关闭所有的集群服务
关闭
./crs_stop -all
启动
./crs_start -all
三、分步操作crs服务
1、关闭集群
srvctl stop service -d -s
srvctl stop database -d
srvctl stop asm -n
srvctl stop asm -n
srvctl stop nodeapps -n
srvctl stop nodeapps -n
2、关闭集群
srvctl start nodeapps -n
srvctl start nodeapps -n
srvctl start asm -n
srvctl start asm -n
srvctl start database -d
srvctl start service -d -s
3、测试
3.1)关闭
srvctl stop service -d devdb -s XFF
srvctl stop instance -d devdb -i devdb1,devdb2 -o immediate
(srvctl stop database -d devdb -o immediate)
srvctl stop asm -n rac1
srvctl stop asm -n rac2
srvctl stop nodeapps -n rac1
srvctl stop nodeapps -n rac2
3.2)启动
srvctl start nodeapps -n rac1
srvctl start nodeapps -n rac2
srvctl start asm -n rac1
srvctl start asm -n rac2
srvctl start database -d devdb
(srvctl start instance -n devdb -i devdb1,devdb2)
srvctl start service -d devdb -s XFF