SQL> CREATE MATERIALIZED VIEW mv_vcard_last_hlrlist refresh fast as
select vcard.username,
vcard.nick_name,vcard.pcode,hlrlist.region,
vcard.birth_year, vcard.icon, vcard.descr, last.state,
vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY
from hlrlist ,last ,vcard
where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id
and vcard.status = 0 and last.state='Online' ;
2 3 4 5 6 7 8 and vcard.status = 0 and last.state='Online'
*
ERROR at line 8:
ORA-12052: cannot fast refresh materialized view IMPS.MV_VCARD_LAST_HLRLIST
begin
DBMS_MVIEW.EXPLAIN_MVIEW('select vcard.username,vcard.nick_name,vcard.pcode,hlrlist.region,vcard.birth_year, vcard.icon, vcard.descr, last.state,vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY from hlrlist ,last ,vcard where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id');
END;
SELECT CAPABILITY_NAME, POSSIBLE, MSGTXT
FROM MV_CAPABILITIES_TABLE
WHERE CAPABILITY_NAME LIKE 'REFRESH%';
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_COMPLETE Y
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N
the SELECT list does not have the rowids of all the detail tables
CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_FAST_AFTER_ONETAB_DML N
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N
PCT is not possible on any of the detail tables in the materialized view
发现是因为没有包括各个表的rowid
于是改为:
CREATE MATERIALIZED VIEW mv_vcard_last_hlrlist refresh fast as
select hlrlist.rowid arowid,last.rowid browid,vcard.rowid crowid, vcard.username,
vcard.nick_name,vcard.pcode,hlrlist.region,
vcard.birth_year, vcard.icon, vcard.descr, last.state,
vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY
from hlrlist ,last ,vcard
where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id and last.state='Online' ;
成功建立该物化视图
fast刷新的限制
所有类型的快速刷新物化视图都必须满足的条件：
1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；
2.物化视图不能包含对LONG和LONG RAW数据类型的引用。
只包含连接的物化视图：
1.必须满足所有快速刷新物化视图都满足的条件；
2.不能包括GROUP BY语句或聚集操作；
3.如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上；
4.如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作。
5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。
6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。
查看全文昨天一个朋友给我讲他的DB一直报ora-3136，带来的后果就是客户不能登陆，发现他安装了10g 10.2.0.1 for linux，没有用连接池，连接数也不是很多，process是200，并没有报连接溢出错误，但进程数一直在200以上，查metalink：
*Note: 345197.1
1. set INBOUND_CONNECT_TIMEOUT_<listener name> = 0 in listener.ora
2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
3. stop and start both listener and data base.
4. Re-run the Workflow Configuration Assistant
看了一下，因为是10g，在network/admin/目录下什么文件都没有，手工帮他创建了listener.ora与sqlnet.ora，然后把INBOUND_CONNECT_TIMEOUT_<listener name>与SQLNET.INBOUND_CONNECT_TIMEOUT 都设置为60，问题解决。
我先查看该进程的进程号:
ps -ef|grep lgwr
daizy 27680 1 0 09:24 ? 00:00:00 ora_lgwr_dzy
进程号为27680
然后用top来查看nice值:
top -p27680
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27680 daizy 33 0 384m 15m 14m S 0.0 1.6 0:00.53 oracle
可以看到NI值为:0
再用renice来修改:
renice 18 27680
再用top来看:
top -p27680
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27680 daizy 33 18 384m 15m 14m S 0.0 1.6 0:00.53 oracle
这时候NI变成了18
这个修改nice值是看了eygle的一个"LGWR与AIX上的进程优先级","""用户的事务及提交非常频繁，大约每秒要执行20000个事务，每个事务都需要独立提交。
在压力测试情况下，用户发现最终的数据库瓶颈出现在LGWR上。
最显著的等待是LOG FILE SYNC，最终发现问题出在LGWR进程，由于该进程活动过于频繁，该进程在系统上的nice值被调至68，AIX上缺省进程nice值为60，nice值越高表示优先级越低。
经过renice之后，系统性能恢复正常。"(http://www.eygle.com/archives/2007/03/)
这个还是比较有用,可以在日志写忙不过来的时候让日志优先排后
查看全文启动实例hang的一例的诊断
数据库被人无意断开电源。
1、启动数据库时HANG住：ALERT日志停在以下的行。
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Sun May 27 11:22:28 2007
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sun May 27 11:22:28 2007
ALTER DATABASE MOUNT
Sun May 27 11:22:35 2007
Successful mount of redo thread 1, with mount id 2423313207.
Sun May 27 11:22:35 2007
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Sun May 27 11:22:35 2007
ALTER DATABASE OPEN
Sun May 27 11:22:35 2007
Beginning crash recovery of 1 threads
Sun May 27 11:22:35 2007
Started first pass scan
Sun May 27 11:22:36 2007
Completed first pass scan
0 redo blocks read, 0 data blocks need recovery
Sun May 27 11:22:36 2007
Started recovery at
Thread 1: logseq 78153, block 3, scn 2110.630842439
Recovery of Online Redo Log: Thread 1 Group 3 Seq 78153 Reading mem 0
Mem# 0 errs 0: /dev/ora/redolog3
Sun May 27 11:22:36 2007
Ended recovery at
Thread 1: logseq 78153, block 3, scn 2110.630862440
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Sun May 27 11:22:36 2007
Thread 1 advanced to log sequence 78154
Thread 1 opened at log sequence 78154
Current log# 2 seq# 78154 mem# 0: /dev/ora/redolog2
Successful open of redo thread 1.
Sun May 27 11:22:36 2007
LOG_CHECKPOINT_INTERVAL was set when MTTR advisory was switched on.
Sun May 27 11:22:36 2007
SMON: enabling cache recovery
启动到mount模式下，
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /disk1/ora9i/920/admin/ora9i/udump/ora9i_ora_1202.trc
SQL>
trace文件内容如下：
*** SESSION ID:(12.4) 2007-05-27 10:34:21.904
*** 2007-05-27 10:34:21.903
==============
HANG ANALYSIS:
==============
Open chains found:
Other chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/3/1/0xb7a8fc98/957/LGWR wait for redo copy>
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/8/1/0xb7a915c0/967/wakeup time manager>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/9/3/0xb7a924d8/989/No Wait>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/12/4/0xb7a92ee8/1202/No Wait>
Extra information that will be dumped at higher levels:
[level 5] : 4 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 10] : 7 node dumps -- [IGN]
可以看到这里有一个等待：LGWR wait for redo copy，诊断应与undo有关。
于是设置events 10015,对于该events的解释：Undo Segment Recovery
event="10015 trace name context forever,level 10"
5. 在生成的TRACE 文件内容如下：
KCRA: buffers claimed = 0/0, eliminated = 0
Acquiring rollback segment SYSTEM
Recovering rollback segment _SYSSMU5$
Recovering rollback segment _SYSSMU6$
Recovering rollback segment _SYSSMU7$
Recovering rollback segment _SYSSMU8$
Recovering rollback segment _SYSSMU9$
Recovering rollback segment _SYSSMU10$
这里可以发现从_SYSSMU5$在做recover时hang住了。
那么可以将此undo segment offline,于是我们使用隐含参数来打开数据库，当然这个过程log需要reset
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_OFFLINE_ROLLBACK_SEGMENTS=(_SYSSMU5$)
再打开数据库，OK。
ref: http://cc59.itpub.net/post/1845/293645
又发现一个不走索引的语句,一个长的sql条件很简单type=4
但是开发人员在写的时候没有看这个type在表里的类型是char(1),结果该语句在sp报表的SQL ordered by Reads里就有所表现:
CPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,308,056 7 186,865.1 91.5 190.04 1222.70 1623835478
让开发改为type='4'就好了
通过sqlplus "/as sysdba"登录信息我们可以知道，sqlplus认为oracle数据库并没有关闭。
oracle的进程都不存在了，但sqlplus为什么认为oracle没有关闭呢？它是根据什么来判断的？
4、查看此时的共享内存段情况
[oracle@rep ~]$ ipcs -as
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x91c11414 491531 oracle 640 142606336 10
------ Semaphore Arrays --------
key semid owner perms nsems
0xeddca300 360448 oracle 640 154
------ Message Queues --------
key msqid owner perms used-bytes messages
从上面的信息可以看出，oracle之前占用的共享内存段和信号段都还在，而sqlplus是根据这些信息来判断数据库是否关闭的。
我们虽然kill了oracle的进程，但是并没有清除其对应的共享内存段和信号段。
原因找到的，下面就是清除共享内存段和信号段了。
清除共享内存段：
要清除oracle共享内存段，必须保证没有任何连接连接到oracle数据库上，否则清除共享内存段会失败。
我们用暴力方法强制杀死连接到oracle的进程：
[root@rep ~]# ps -ef|grep ora|awk '{print $2}'|xargs kill -9
[root@rep ~]# su - oracle
[oracle@rep ~]$ ipcrm -m 491531
清除信号段：
[oracle@rep ~]$ ipcrm -s 360448
查看此时共享内存等信息：
[oracle@rep ~]$ ipcs -as
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
------ Semaphore Arrays --------
key semid owner perms nsems
------ Message Queues --------
key msqid owner perms used-bytes messages
从上面信息看到，共享内存段和信号段已经被清除。
5、启动oracle
清除完信号段后，我们再用sys登录数据库，就会发现，sqlplus已经认为数据库是关闭的了。
此时我们可以正常启动数据库：
[oracle@rep ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on D??úáù 7?? 28 14:17:46 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 135337420 bytes
Fixed Size 452044 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
其实这种情况下，我们只需要清除信号段就可以正常启动数据库，但为了更彻底一点，我们选择了把共享内存也一并清除。
一般遇到这种情况有两种解决方法：
1、直接重启OS
2、清除共享内存和信号量
最后简单总结一下：
1、oracle进程终止不代表数据库关闭
2、oracle进程异常中止时要及时清除对应的共享内存段和信号段
3、sqlplus是根据信号段来判断oracle数据库是否是关闭的
ref: http://space6212.itpub.net/post/12157/317871
一次调整优化过程总结
平台：windows 2000 server sp3
数据库：Oracle 9.0.1.1.1
1. 在高峰时段，系统高负载的情况下用statspack一个小时做一次系统的快照。对statspack产生的报告注意其中的Top 5 Wait Events。具体可以参考eygle写的statspace使用指南。本例的等待事件主要是db file scattered read、buffer busy waits和db file sequential read。
2. 对于db file scattered read主要是由于全表扫描造成的，db file sequential read说明当前系统负载很高或者cbo使用了不合适的索引。通过定时（2天一次在晚上12点执行dbms_stats.gather_schema_stats('')过程）统计schema信息并用过程
dbms_support.start_trace_in_session
dbms_support.stop_trace_in_session
跟踪客户端的会话，用tkprof分析跟踪文件：
tkprof sys=no sort=prsdsk，exedsk，fchdsk
通过分析输出文件并获得其中效率低下的sql语句，然后对这些sql语句进行调整。
3. 一般可以通过在表上加上必要的索引来消除全表扫描，并注意调整初始化参数db_file_multiblock_read_count和optimizer_index_cost_adj。这两个参数影响CBO选择的执行计划。如果希望CBO更倾向于使用索引，可以加提示/*+first_rows*/。
4. 经上述调整后，客户仍然抱怨在应用程序中查询某个报表的时候非常慢，需要2个小时以上。对sql语句分析后，发现该报表的sql语句是查询一个嵌套视图，结构大致如下：
create or replace view vw_v1 as select c1,c2,c3 from t1;
create or replace view vw_v2 as select c1,sum(c2),sum(c3) from vw_v1 group by c2,c3;
create or replace view vw_v3 as select *from vw_v2 union select *from vw_other;
客户端查询的时候是select *from vw_v3 where c1 = :b1;
其中t1表是个分区表，大概有4千万行，每个月增长1千万行。对sql语句分析后认为性能问题主要是由于视图vw_v2中对t1的group by c2,c3造成的，因而调整如下：
create or replace view vw_v2 as select c1,sum(c2),sum(c3) from vw_v1 where c1 = pg_arg.sf_get_c1() group by c2,c3;
在group by前加入where c1 = pg_arg.sf_get_c1()，pg_arg是pl/sql包，在执行该查询前需要执行pg_arg.sp_set_c1(c1)过程设置c1参数。通过这样的调整和改变可以把查询的耗时减少到10分钟以内。
5. 同时用户也在抱怨倒盘（主要是插入、删除操作）时非常缓慢，200人的单位需要2个小时才能导入到数据库中。该操作主要是大表（分区表，每个月增加1000万条记录，总行数现时在4千万行左右）的插入删除操作，在sqlplus里面执行插入操作，高峰时候更是达到了30秒才能插入一条记录。通过alter table modify partition freelists ;增大空闲列表的数目后对性能的提高帮助不大；同时在插入的时候加/*+append*/提示，对性能的提高也是很有限（原来两个小时的操作在一个小时五十分钟完成，基本上没多大的作用）。对大表结构分析后，发现在表上建立了全局分区索引，把该全局分区索引删除并建立普通索引后INSERT语句可以即时响应。另外在插入数据的时候用批量插入的方式进行插入：
/*如下所示:
先定义数组变量
然后把数据一次性fetch出来
最后用forall一次性插入到表中*/
TYPE t_jzny IS TABLE OF char(6);
b_jzny t_jzny;
v_rowcount NUMBER;
CURSOR cur_bulk
IS
SELECT ny
FROM
WHERE ROWNUM <= 6;
…………………
-- 初始化数组
OPEN cur_bulk;
FETCH cur_bulk
BULK COLLECT INTO b_jzny;
CLOSE cur_bulk;
………………………
FORALL v_i IN 1 .. v_rowcount
Insert into
……………..
values
(
………….
B_jzny(v_i);
…………..
)
经以上调整后，200人左右的倒盘操作可以从原来的2个小时提高到10分钟以内。收到立竿见影的效果。
6. 小结：性能调整一般是通过statspack，产生分析跟踪文件来定位效率低下的sql语句。然后通过各种手段，比如可以加入合适的索引，添加合适的hint来调整查询语句；用alter index monitoring usage;跟踪没有使用的索引并把这些索引删除、调整效率低下的索引（尤其使对大表上的索引）、加/*+append*/提示、批量插入等手段调整DML语句。
http://www.itpub.net/adv/banner.php?areaid=2&posid=2
查看全文
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and UTF8 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ORCL
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ORCL
About to export ORCL's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 4021 encountered
ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at "SYS.DBMS_METADATA", line 1511
ORA-06512: at "SYS.DBMS_METADATA", line 1548
ORA-06512: at "SYS.DBMS_METADATA", line 1864
ORA-06512: at "SYS.DBMS_METADATA", line 3707
ORA-06512: at "SYS.DBMS_METADATA", line 3689
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
先用sys执行下面脚本：
@?/rdbms/admin/catmeta.sql
然后执行：
select owner,object_name,object_type,status from dba_objects where status='invalid';
看看有没有无效的，如果没有则再倒出
如果有，则按步骤执行下面的
1. sql> conn / as sysdba
2. sql> startup migrate
3. sql> @/oraclehome/rdbms/admin/catpatch.sql
4. sql> select comp_id, comp_name, version, status from dba_registry;
这个时候应该没有问题了
今天查询一个运营库的user_jobs,发现job都停止了,未执行,修改了一些这个参数,job又可以执行了!
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;
等2~3分钟，重新设置
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 5;
此时PMON会重起CJQ0进程
ref: http://www.eygle.com/archives/2004/11/job_can_not_execute_auto.html
但是uptime看
1:38pm up 361 day(s)
这个服务器启动才361天,未到eygle说的497天,但是job进程却挺了,有点奇怪呀!
这个问题也有可能是归档日志的目标满了!
接上:
我的逻辑standby配置:
http://wayw009.spaces.live.com/blog/cns!73fba3701ddca
otn上logical standby: http://blog.oracle.com.cn/html/11/t-26311.html
1. 验证是否存在表没有主键
select owner,table_name from dba_logstdby_not_unique
where (owner,table_name) not in
(select distinct owner,table_name from dba_logstdby_unsupported)
and bad_column='Y';
2. 验证
SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,
SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG
FROM V$DATABASE;
PK_ UI_
--- ---
NO NO
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
再验证
SELECT SUPPLEMENTAL_LOG_DATA_PK AS PK_LOG,
SUPPLEMENTAL_LOG_DATA_UI AS UI_LOG
FROM V$DATABASE;
PK_LOG UI_LOG
------ ------
YES YES
3. 为primary database添加部分参数
alter system set log_archive_dest_3='location=/opt/oracle/standby_redo valid_for=(standby_logfiles,standby_role) db_unique_name=primary' scope=both;
alter system set log_archive_dest_state_3=enable scope=both;
alter system set UNDO_RETENTION=3600 scope=both;
4. alter database recover managed standby database cancel;
(这个应该在备份数据库里面执行)
(记住:在build之前一定要先cancel 物理standby,否则执行"alter database recover to logical standby cncimps"时会总是没有返回)
5. 在primary database上建立logminer字典
exec dbms_logstdby.build;
6. alter database recover to logical standby cncimps;
(这个应该在备份数据库里面执行)
我这里原来db_name就是cncimps,所以这样后db_name没变,也就不用再重新修改pasword文件了(如果和原来的不一致,那么就需要重建password文件)
7. standby也这么修改:
alter system set log_archive_dest_3='location=/opt/oracle/standby_redo valid_for=(standby_logfiles,standby_role) db_unique_name=standby' scope=both;
alter system set log_archive_dest_state_3=enable scope=both;
alter system set UNDO_RETENTION=3600 scope=both;
8. shutdown immediate (shutdown standby)
9. startup mount
alter database open resetlogs;
数据库已更改
(11) 开始应用redo
如果之前没有使用standby redo log file,这里会出错
SQL> alter database start logical standby apply immediate;
alter database start logical standby apply immediate
*第1行出现错误
ORA-16239: 由于没有备用重做日志, IMMEDIATE选项不可用
10. 添加redo log
alter database add standby logfile group 4 '/opt/oracle/oradata/cncimps/standbyredo04.log' size
alter database add standby logfile group 5 '/opt/oracle/oradata/cncimps/standbyredo05.log' size
alter database add standby logfile group 6 '/opt/oracle/oradata/cncimps/standbyredo06.log' size
alter database add standby logfile group 7 '/opt/oracle/oradata/cncimps/standbyredo07.log' size
11. 添加temp数据文件
alter session disable guard;
alter tablespace temp add tempfile '/opt/oracle/oradata/cncimps/temp03.dbf' size
alter session enable guard;
12. 测试
可以用这个来归档当前日志
alter system archive log current;
在测试的时候,好像要花费很长时间来应用日志
如果应用日志,备库里的alert文件会有下面的相关信息
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ have been marked unusable
Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable
LSP2 started with pid=29, OS id=18066
Wed Jul 25 09:57:26 2007
LOGMINER: Begin mining logfile: /opt/oracle/standby_redo/1_386_607103080.dbf
Wed Jul 25 09:57:26 2007
LOGMINER: Turning ON Log Auto Delete
LOGSTDBY Apply process P005 started with pid=32 OS id=18072
LOGSTDBY Apply process P004 started with pid=31 OS id=18070
LOGSTDBY Apply process P006 started with pid=33 OS id=18074
LOGSTDBY Apply process P007 started with pid=34 OS id=18076
LOGSTDBY Apply process P008 started with pid=35 OS id=18078
LOGSTDBY Analyzer process P003 started with pid=30 OS id=18068
Wed Jul 25 09:57:28 2007
LOGMINER: End mining logfile: /opt/oracle/standby_redo/1_386_607103080.dbf
Wed Jul 25 09:57:28 2007
LOGMINER: Begin mining logfile: /opt/oracle/standby_redo/1_387_607103080.dbf
Wed Jul 25 09:57:28 2007
LOGMINER: End mining logfile: /opt/oracle/standby_redo/1_387_607103080.dbf
Wed Jul 25 09:57:28 2007
LOGMINER: Begin mining logfile: /opt/oracle/standby_redo/1_388_607103080.dbf
Wed Jul 25 09:57:28 2007
LOGMINER: End mining logfile: /opt/oracle/standby_redo/1_388_607103080.dbf
Wed Jul 25 09:57:28 2007
LOGMINER: Begin mining logfile: /opt/oracle/archivelog/1_389_607103080.dbf
Wed Jul 25 09:57:28 2007
LOGMINER: End mining logfile: /opt/oracle/archivelog/1_389_607103080.dbf
Wed Jul 25 09:57:28 2007
LOGMINER: Begin mining logfile: /opt/oracle/oradata/cncimps/standbyredo05.log
Wed Jul 25 09:57:28 2007
Thread 1 advanced to log sequence 3
Current log# 2 seq# 3 mem# 0: /opt/oracle/oradata/cncimps/redo02.log
建立物理standby可以用rman,也可以用冷备份,使用冷备份可以参照http://www.itpub.net/showthread.php?s=&threadid=666617
我的这个实验是用rman来建立standby
我主要是参考下面个文档进行的
http://hi.baidu.com/space6212/blog/item/249d5b230cfa4a4c9358071e.html
http://blog.tom.com/blog/read.php?bloggerid=133149&blogid=61256
一一定要主库和备库都安装好后再启动listener
并且tnsname中的service看初始化参数的server_names
我就写一下,我在安装physical standby的一些步骤
环境:
Sid_name_ cncimps
Cnc17: primary
Cnc18: standby
1. 当然是要让数据库处在force logging状态
Alter database force logging;
2. 当然要让数据库处在archive状态
Startup mount
Alter database archivelog
3. 在主库创建standby redo log在主库上建立standby log，大小与主库联机日志大小一样，组数至少大1（这个是可选，是为了角色切换方便）
alter database add standby logfile group 4 '/opt/oracle/oradata/cncimps/standbyredo04.log' size 50m;
alter database add standby logfile group 5 '/opt/oracle/oradata/cncimps/standbyredo05.log' size 50m;
alter database add standby logfile group 6 '/opt/oracle/oradata/cncimps/standbyredo06.log' size 50m;
alter database add standby logfile group 7 '/opt/oracle/oradata/cncimps/standbyredo07.log' size 50m;
这些日志查询v$log是看不到状态的,需要查询v$logfile才能发现
orapwd file=orapwcncimps password=oracle entries=10
4. 初始化参数:
cncimps.__db_cache_size=771751936
cncimps.__java_pool_size=16777216
cncimps.__large_pool_size=16777216
cncimps.__shared_pool_size=251658240
cncimps.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/cncimps/adump'
*.background_dump_dest='/opt/oracle/admin/cncimps/bdump'
*.COMPATIBLE='10.2.0.1'
*.control_files='/opt/oracle/oradata/cncimps/control01.ctl','/opt/oracle/oradata/cncimps/control02.ctl','/opt/oracle/oradata/cncimps/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/cncimps/cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=8
*.db_recovery_file_dest_size=21474836480
*.dispatchers='(protocol=TCP)','(ADDRESS=(PROTOCOL=TCP)(DISPATCHERS=2))'
*.job_queue_processes=10*.open_cursors=600
*.pga_aggregate_target=254803968
*.processes=500
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.sessions=555
*.sga_max_size=1610612736
*.sga_target=1073741824
*.shared_servers=6
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/cncimps/udump'
*.local_listener='PRIMARY'
*.db_domain=''
*.FAL_CLIENT='primary'
*.FAL_SERVER='standby'
*.DB_UNIQUE_NAME='primary'
*.SERVICE_NAMES=primary ##指定了这个service_names,那么在tns里指定的server_name就只能是它,否则连接不上,当然也可以不指定,缺省是db_name+db_domain
*.db_name='cncimps'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
##primary和standby是指主备里面指定的db_unique_name,如果有多台可以指定多个
*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
##注意这里的service是tnsnames.ora里面定义的能到达standby的tns配置
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=4
*.log_file_name_convert='/opt/oracle/oradata/cncimps/','/opt/oracle/oradata/cncimps/'
这些粉红色的部分都是做standby特别需要注意的地方
5. rman备份数据库
$ORACLE_HOME/bin/rman target /
backup database format='/home/oracle/backup/%U_%s.bak';
sql "Alter System Archive Log Current";
Backup filesperset 10 ArchiveLog all format='/home/oracle/backup/arc%U_%s.bak';
在做rman备份之前最好删除过期的备份
crosscheck backupset
delete obsolete
delete expired backup;
change archivelog all crosscheck ;
delete archivelog all;
delete expired archivelog all;
6. 获取standby的控制文件
用sys用户进入数据库
alter database create standby controlfile as '/home/oracle/backup/standby.ctl';
7. 将控制文件复制到需要的位置
[oracle@cnc18 backup]$ cp standby.ctl /opt/oracle/oradata/cncimps/control01.ctl
[oracle@cnc18 backup]$ cp standby.ctl /opt/oracle/oradata/cncimps/control02.ctl
[oracle@cnc18 backup]$ cp standby.ctl /opt/oracle/oradata/cncimps/control03.ctl
8. 修改standby的init文件
*.DB_UNIQUE_NAME='standby' ##在从的指定从的unique name
*.SERVICE_NAMES=standby
*.FAL_CLIENT='standby'
*.FAL_SERVER='primary' ##这2个参数和主库的相反
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/opt/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT =AUTO
*.log_file_name_convert='/opt/oracle/oradata/cncimps/','/opt/oracle/oradata/cncimps/'
9. 建立备库密码文件
orapwd file=orapwcncimps password=oracle entries=10
注意 : 这一步这个password一定要和主库的password一致,否则容易出现ora-12154的问题,如果主库的记不住,主库也可以重建,方法一样的
10. 然后创建相应的文件夹
mkdir -p /u01/oracle/admin/primary/{adump,bdump,cdump,udump}
11. 恢复数据库到备库里
启动备库到nomount状态
Sqlplus / as sysdba
Startup nomount
Alter database mount standby database
Rman恢复数据库
restore datatase;
restore archivelog all;
recover database; ##这步需要做,否则在alter database open的时候不能打开数据库,说system.dbf有问题
(
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/opt/oracle/oradata/cncimps/system01.dbf'
)
12. 分别在主备库里面修改tnsnames.ora
cat $ORACLE_HOME/network/admin/tnsnames.ora
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 61.135.171.17)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cncimps)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 61.135.171.19)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
13. 启动备库
startup nomount;
alter database mount standby database;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
也可以按照下面的方式来打开备库来进行数据查询,查询后按上面的方式启动备库,就又可以恢复了
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database open;
14. 检查物理standby情况
1）在备库检查当前的archivelog
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
15. 然后可以在主库里插入数据来测试
16. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN;
今天在10.2.0.1 for linux上exp时出现下面错误:
EXP-00091: Exporting questionable statistics.
这个问题可以在EXP的参数中加参数STATISTICS=none就可以解决
今天运行一条sql语句,20多分钟了还是没有返回,数据也没有什么特殊的地方,于是我把这个用户下的各个表重新分析了一下,再执行该语句,发现很快获得了结果!!
在这里记录一下
dbms_utility.analyze_schema(schema=>'SCOTT',method=>'ESTIMATE',estimate_percent=>10,method_opt=>'FOR ALL INDEXES');
ORA-01779: cannot modify a column which maps to a non-key-preserved table
例如，使用以下的更新查询就会出现该错误。
CREATE TABLE test1 ( id integer primary key, num integer );这个错误的意思是，子查询的结果中，更新数据源(test2)的内容不唯一，导致被更新对象(test1)中的一行可能对应数据源(test2)中的多行。 本例中，test2表的id不唯一，因此test2表中可能存在id相同但是num不相同的数据，这种数据是无法用来更新 test1 的。
解决方法就是保证数据源的唯一性，例如本例中可以为test2.id创建一个唯一索引：
CREATE UNIQUE INDEX test2_idx_001 ON test2 (id);之后上面的更新就可以执行了。
另外也可以强制 Oracle 执行，方法是加上 BYPASS_UJVC 注释。
UPDATEBYPASS_UJVC的作用是跳过Oracle的键检查。 这样虽然能够执行了，但是如果test2中存在不唯一的数据，test1就会被更新多次而导致意想不到的结果。
环境：
AIX5L+HACMP+ORACLE9I
分析alert，没有发现错误：
Fri Apr 27 17:08:05 2007
Thread 1 advanced to log sequence 612
Current log# 2 seq# 612 mem# 0: /oracle2/oracle/oradata/mssdb2/redo02.log
Fri Apr 27 17:08:05 2007
ARC0: Evaluating archive log 1 thread 1 sequence 611
ARC0: Beginning to archive log 1 thread 1 sequence 611
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle2/oracle/oradata/oraclearchivelog/1_611.dbf'
ARC0: Completed archiving log 1 thread 1 sequence 611
Fri Apr 27 17:18:52 2007
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 117
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Fri Apr 27 17:20:49 2007
ALTER DATABASE CLOSE NORMAL
Fri Apr 27 17:20:49 2007
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri Apr 27 17:20:49 2007
Shutting down archive processes
Archiving is disabled
Fri Apr 27 17:20:49 2007
ARCH shutting down
Fri Apr 27 17:20:49 2007
ARCH shutting down
Fri Apr 27 17:20:49 2007
ARC1: Archival stopped
Fri Apr 27 17:20:49 2007
ARC0: Archival stopped
Fri Apr 27 17:20:49 2007
Thread 1 closed at log sequence 612
Successful close of redo thread 1.
Fri Apr 27 17:20:50 2007
Completed: ALTER DATABASE CLOSE NORMAL
Fri Apr 27 17:20:50 2007
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Fri Apr 27 17:22:39 2007
检查hacmp的日志，发现出错时刻hacmp正在执行切换：
hacmp的out文件：
:server_restart[144] :server_restart[144] odmget -qmonitor=ora2_db and name=NOTIFY_METHOD HACMPmonitor
:server_restart[144] cut -d" -f2
:server_restart[144] grep value =
NOTIFY=
:server_restart[146] :server_restart[146] cut -d -f1
:server_restart[146] echo
NOTIFY_SCRIPT=
:server_restart[146] [[ -n ]]
:server_restart[162] :server_restart[162] odmget -qmonitor=ora2_db and name=CLEANUP_METHOD HACMPmonitor
:server_restart[162] cut -d" -f2
:server_restart[162] egrep value =
CLEANUP=/hacmpscript/ora2stop-db.sh
:server_restart[163] :server_restart[163] cut -d -f1
:server_restart[163] echo /hacmpscript/ora2stop-db.sh
CLEANUP_SCRIPT=/hacmpscript/ora2stop-db.sh
:server_restart[163] [[ -n /hacmpscript/ora2stop-db.sh ]]
:server_restart[163] [[ -x /hacmpscript/ora2stop-db.sh ]]
:server_restart[167] dspmsg scripts.cat 9334 server_restart: Calling user specified cleanup method /hacmpscript/ora2stop-db.shn server_restart /hacmpscript/ora2stop-db.sh
server_restart: Calling user specified cleanup method /hacmpscript/ora2stop-db.sh
:server_restart[168] /hacmpscript/ora2stop-db.sh
:server_restart[168] ODMDIR=/etc/objrepos
SQL*Plus: Release 9.2.0.1.0 - Production on Wed May 9 16:26:13 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> Connected.
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
检查hacmp的test脚本，没有发现问题：
test-db-mssdb2.sh的内容：
#!/bin/sh
#
# Copyright (c) MDCL Inc. All Rights Reserved.
# Created by Li Han Guang 2006.01.16
#
# Should return 0 if the service was start succesfully; otherwise,
# return non-zero
#
# check the following process:
# ora_smon_$sid
# ora_pmon_$sid
# ora_dbw0_$sid
# ora_lgwr_$sid
# ora_reco_$sid
# database connection
ORACLE_SID="mssdb2"
ORACLE_HOME=/oracle2/oracle/product/9.2.0
ORA_USER="oracle2"
#ISQL=svrmgrl
ISQL="sqlplus /nolog"
LOG_FILE="/tmp/test_mssdb2.log"
#
# log messages, need var $LOG_FILE
#
log()
{
echo [`date +"%Y-%m-%d %H:%M:%S"`] $1 >> $LOG_FILE
echo [`date +"%Y-%m-%d %H:%M:%S"`] $1
}
get_status()
{
str=$1
# ps -ef | cut -c52- | grep $str | grep -v grep > /dev/null 2>&1
ps -ef | grep $str | grep -v grep > /dev/null 2>&1
STATUS=$?
case $STATUS in
0) return 0;;
*) return 1;;
esac
}
#
#checks for smon process
#
check_smon()
{
sid=$1
get_status ora_smon_$sid
return $?
}
#
#checks for pmon process
#
check_pmon()
{
sid=$1
get_status ora_pmon_$sid
return $?
}
#
#checks for dbw0 process
#
check_dbw0()
{
sid=$1
get_status ora_dbw0_$sid
return $?
}
#checks for lgwr process
#
check_lgwr()
{
sid=$1
get_status ora_lgwr_$sid
status=$?
return ${status}
}
#
#checks for reco process
#
check_reco()
{
sid=$1
get_status ora_reco_$sid
return $?
}
####################################
# start to check oracle processes
####################################
#checks for database connection
#
su - $ORA_USER -c "${ISQL} > /tmp/$$.out 2> /tmp/$$.err " <
select sysdate from dual;
exit;
EOF
grep SYSDATE /tmp/$$.out >/dev/null 2>&1
if test "$?" -ne 0
then
# cat /tmp/$$.*
log "ORACLE: Cannot connect to database for ORACLE_SID=${ORACLE_SID}."
exit 1
else
log "Oracle instance ${ORACLE_SID} can be connected"
fi
rm -f /tmp/$$.*
check_smon ${ORACLE_SID}
if test "$?" -ne 0
then
log "ORACLE: ora_smon_${ORACLE_SID} is NOT running."
exit 1
else
log "ORACLE: ora_smon_${ORACLE_SID} is running"
fi
check_pmon ${ORACLE_SID}
if test "$?" -ne 0
then
log "ORACLE: ora_pmon_${ORACLE_SID} is NOT running."
exit 1
else
log "ORACLE: ora_pmon_${ORACLE_SID} is running"
fi
check_dbw0 ${ORACLE_SID}
if test "$?" -ne 0
then
log "ORACLE: ora_dbw0_${ORACLE_SID} is NOT running."
exit 1
else
log "ORACLE: ora_dbw0_${ORACLE_SID} is running"
fi
check_lgwr ${ORACLE_SID}
if test "$?" -ne 0
then
log "ORACLE: ora_lgwr_${ORACLE_SID} is NOT running."
exit 1
else
log "ORACLE: ora_lgwr_${ORACLE_SID} is running"
fi
check_reco ${ORACLE_SID}
if test "$?" -ne 0
then
log "ORACLE: ora_reco_${ORACLE_SID} is NOT running."
exit 1
else
log "ORACLE: ora_reco_${ORACLE_SID} is running"
fi
exit 0
根据hacmp的test脚本检查test脚本对应的日志778500.out发现连接数据库存在问题：
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 27 17:12:32 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> ERROR:
ORA-12540: TNS:internal limit restriction exceeded
SQL> SP2-0640: Not connected
SQL>
继续检查ORACLE数据库的监听日志LISTERNER.LOG，发现如下错误：
27-APR-2007 17:10:33 * (CONNECT_DATA=(SID=mssdb2)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.192.139.194)(PORT=40707)) * establish * mssdb2 * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded
IBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable
27-APR-2007 17:12:25 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.192.139.202)(PORT=1521)))
27-APR-2007 17:14:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=Unicom_ora2)(USER=oracle2))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153092352)) * stop * 0
TNSLSNR for IBM/AIX RISC System/6000: Version 9.2.0.1.0 - Production on 27-APR-2007 17:22:47
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
最后分析该问题引起的可能原因：
一、 AIX操作系统参数maxuproc：
执行相应的检查，发现该参数使用的默认值，需要调整：
$ lsattr -El sys0|grep maxuproc
maxuproc 128 Maximum number of PROCESSES allowed per user True
二、 ORACLE数据库的参数PROCESSES和PGA_AGGREGATE_TARGET
执行相应的检查,发现该2个参数都为默认值，需要调整：
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 25165824
汇总建议，提交客户。