分类目录归档：Oracle

利用物化视图刷数据

发表于 2011 年 8 月 11 日由惜分飞

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(*)
———-
0

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结合，实现数据的跨版本迁移

发表在 Oracle | 评论关闭

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

发表于 2011 年 8 月 11 日由惜分飞

一、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

发表在 Oracle RAC | 评论关闭

RAC维护命令

发表于 2011 年 8 月 10 日由惜分飞

一、节点层（olsnodes）

rac1-> olsnodes -help

Usage: olsnodes [-n] [-p] [-i] [<node> | -l] [-g] [-v]

where

-n print node number with the node name

-p print private interconnect name with the node name

-i print virtual IP name with the node name

<node> print information for the specified node

-l print information for the local node

-g turn on logging

-v run in verbose mode

rac1-> olsnodes -p -n -i

rac1 1 rac1-priv rac1-vip

rac2 2 rac2-priv rac2-vip

二、网络层（oifcfg）

rac1-> oifcfg -help

Name:

oifcfg – Oracle Interface Configuration Tool.

Usage: oifcfg iflist [-p [-n]]

oifcfg setif {-node <nodename> | -global} {<if_name>/<subnet>:<if_type>}…

oifcfg getif [-node <nodename> | -global] [ -if <if_name>[/<subnet>] [-type <if_type>] ]

oifcfg delif [-node <nodename> | -global] [<if_name>[/<subnet>]]

oifcfg [-help]

<nodename> – name of the host, as known to a communications network

<if_name> - name by which the interface is configured in the system

<subnet> – subnet address of the interface

<if_type> - type of the interface { cluster_interconnect | public | storage }

rac1-> oifcfg iflist -n -p

eth0 10.10.10.0 PRIVATE 255.255.255.0

eth1 192.168.1.0 PRIVATE 255.255.255.0

rac1-> oifcfg getif

eth0 10.10.10.0 global cluster_interconnect