很多新手在安装weblogic的时候，可能因为不知道weblogic各个组件的作用而不知道该选择安装哪些组件合适，weblogic包含的组件如下:
各个组件作用说明:http://www.xifenfei.com/wp-content/uploads/2012/03/WebLogic_component.pdf
通过修改col$.col#改变列展示顺序
有网友提出在表中新增加一列，然后让这个列在中间(大家都知道默认情况下，增加一列，这列会在其他列之后)，通过修改col$基表可以实现改变列的显示顺序(增加一个新列是小儿科的事情不再研究)。我这里通过建立一个表，有id和name列，现在要改变这两列的默认展示顺序
1.创建模拟表
SQL> create table chf.t_xff (id number,name varchar2(100));
Table created.
SQL> insert into chf.t_xff values(1,'xifenfei');
1 row created.
SQL> insert into chf.t_xff values(2,'www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> desc chf.t_xff;
Name Null? Type
----------------------------------------- -------- -------------------
ID NUMBER
NAME VARCHAR2(100)
SQL> col name for a30
SQL> select * from chf.t_xff;
ID NAME
---------- ------------------------------
1 xifenfei
2 www.xifenfei.com
2.修改col$.col#
SQL> select COLUMN_ID,COLUMN_NAME from dba_tab_cols
2 where table_name='T_XFF' AND OWNER='CHF';
COLUMN_ID COLUMN_NAME
---------- ------------------------------------------------------------
2 NAME
1 ID
SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T_XFF';
OBJECT_ID
----------
75598
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME COL#
------------------------------ ----------
NAME 2
ID 1
SQL> UPDATE COL$ SET COL#=2 WHERE OBJ#=75598 AND NAME='ID';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME COL#
------------------------------ ----------
NAME 2
ID 2
SQL> UPDATE COL$ SET COL#=1 WHERE OBJ#=75598 AND NAME='NAME';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME COL#
------------------------------ ----------
NAME 1
ID 2
SQL> COMMIT;
Commit complete.
3.验证结果
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 368263168 bytes Fixed Size 1345016 bytes Variable Size 306186760 bytes Database Buffers 54525952 bytes Redo Buffers 6205440 bytes Database mounted. Database opened. SQL> select * from chf.t_xff; NAME ID ------------------------------ ---------- xifenfei 1 www.xifenfei.com 2 SQL> desc chf.t_xff Name Null? Type ----------------------------------------- -------- --------------- NAME VARCHAR2(100) ID NUMBER
_allow_resetlogs_corruption和adjust_scn解决ORA-01190
一、模拟offline文件然后resetlogs操作
1.设置datafile 5数据文件offline
2.rman备份数据库
3.关闭原数据库，删除数据文件/当前日志和部分归档日志
4.执行不完全恢复，resetlogs打开数据库(如下面操作)
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database until cancel;
ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
ORA-00280: change 868870 for thread 1 is in sequence #29
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_STATUS TO_CHAR(CHANGE#,'999999999
---------- -------------- --------------------------
5 OFFLINE 868810
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(last_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
---------- -------------------------- --------------------------
1 868874
2 868874
3 868874
4 868874
5 868810 868874
--可以看到offline的数据文件，没有因为resetlogs操作而改变
--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(RESETLOGS_CHANGE#,'999999999999')
3 from v$datafile_header;
FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
1 868874 868871
2 868874 868871
3 868874 868871
4 868874 868871
5 868810 787897
二、隐含参数设置
SQL> create pfile='/tmp/pfile' from spfile; File created. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 在pfile中增加 _allow_resetlogs_corruption=true _allow_error_simulation=TRUE(10g及其以上版本需要)
三、打开数据库,online离线文件
SQL> startup pfile='/tmp/pfile' mount;
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1345016 bytes
Variable Size 293603848 bytes
Database Buffers 67108864 bytes
Redo Buffers 6205440 bytes
Database mounted.
--在mount状态下执行
SQL> alter session set events '10015 trace name adjust_scn level 2';
Session altered.
--[一定要]在mount状态下执行online操作
SQL> alter database datafile 5 online;
Database altered.
SQL> recover database until cancel;
ORA-00279: change 868810 generated at 03/13/2012 22:19:37 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_27_777766629.dbf
ORA-00280: change 868810 for thread 1 is in sequence #27
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
no rows selected
姊妹篇:bbed解决ORA-01190
bbed解决ORA-01190
当我们使用resetlogs方式打开数据库后，发现有数据文件处于offline状态，这个时候很可能就是悲剧降临的时候，很有可能这个文件文件在resetlogs之前就处于offline状态，然后你resetlogs之后，这个文件使用常规方法很难再online，会出现ORA-01190或者ORA-01189之类的错误。
一、模拟offline文件然后resetlogs操作
1.设置datafile 5数据文件offline
2.rman备份数据库
3.关闭原数据库，删除数据文件/当前日志和部分归档日志
4.执行不完全恢复，resetlogs打开数据库(如下面操作)
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> recover database until cancel;
ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1
ORA-00289: suggestion :
/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
ORA-00280: change 868870 for thread 1 is in sequence #29
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
FILE# ONLINE_STATUS TO_CHAR(CHANGE#,'999999999
---------- -------------- --------------------------
5 OFFLINE 868810
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01190: control file or data file 5 is from before the last RESETLOGS
ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(last_change#,'999999999999') from v$datafile;
FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
---------- -------------------------- --------------------------
1 868874
2 868874
3 868874
4 868874
5 868810 868874
--可以看到offline的数据文件，没有因为resetlogs操作而改变
--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(RESETLOGS_CHANGE#,'999999999999')
3 from v$datafile_header;
FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
1 868874 868871
2 868874 868871
3 868874 868871
4 868874 868871
5 868810 787897
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
二、bbed修改相关项
下面两项与resetlogs相关
kcvfhrlc表示 reset logs count
kcvfhrls表示 resetlogs scn
下面四项与数据库文件scn相关
kscnbas (at offset 140) – SCN of last change to the datafile.
kcvcptim (at offset 148) - Time of the last change to the datafile.
kcvfhcpc (at offset 176) – Checkpoint count.
kcvfhccc (at offset 184) – Unknown, but is always 1 less than thecheckpoint point count.
BBED> set filename '/u01/oracle/oradata/ora11g/system01.dbf'
FILENAME /u01/oracle/oradata/ora11g/system01.dbf
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x2e5eed37
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x000d4207
ub2 kscnwrp @120 0x0000
BBED> p kcvcpscn
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000d4495
ub2 kscnwrp @488 0x0000
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000078
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000077
BBED> set filename '/u01/oracle/oradata/ora11g/xifenfei01.dbf'
FILENAME /u01/oracle/oradata/ora11g/xifenfei01.dbf
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x2e5bc6e5
BBED> set mode edit
MODE Edit
BBED> m /x 37ed5e2e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
BBED> p kcvfhrlc
ub4 kcvfhrlc @112 0x2e5eed37
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x000c05b9
ub2 kscnwrp @120 0x0000
BBED> m /x 07420d00
BBED> p kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x000d4207
ub2 kscnwrp @120 0x0000
BBED> p kcvcpscn
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000d41ca
ub2 kscnwrp @488 0x0000
BBED> set offset 484
OFFSET 484
BBED> m /x 95440d00
BBED-00209: invalid number (95440d00)
BBED> m /x 9544
BBED> set offset +2
OFFSET 486
BBED> m /x 0d00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000003
BBED> m /x 78000000
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000078
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000002
BBED> m /x 77000000
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000077
BBED> sum
Check value for File 0, Block 1:
current = 0xe079, required = 0x5940
BBED> sum apply
Check value for File 0, Block 1:
current = 0x5940, required = 0x5940
三、数据文件online
[oracle@xifenfei ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:48:48 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1345016 bytes
Variable Size 301992456 bytes
Database Buffers 58720256 bytes
Redo Buffers 6205440 bytes
Database mounted.
Database opened.
SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(RESETLOGS_CHANGE#,'999999999999')
3 from v$datafile_header;
FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
---------- -------------------------- --------------------------
1 869528 868871
2 869528 868871
3 869528 868871
4 869528 868871
5 869525 868871
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
在RAC中lsnrctl和srvctl操作监听区别
朋友今天询问了一个问题RAC中使用srvctl 操作监听和lsnrctl 操作监听结果不一样，下面我通过实验说明问题
0.listener.ora文件内容
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))
)
)
SID_LIST_LISTENER_RAC1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
1.srvctl 启动监听
rac1-> srvctl start listener -n rac1 rac1-> lsnrctl status LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:09:34 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER_RAC1 Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 11-MAR-2012 22:07:21 Uptime 0 days 0 hr. 2 min. 13 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully rac1-> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.....XFF.cs application ONLINE ONLINE rac1 ora....db1.srv application ONLINE ONLINE rac2 ora.devdb.db application ONLINE ONLINE rac2 ora....b1.inst application ONLINE ONLINE rac1 ora....b2.inst application ONLINE ONLINE rac2 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2
srvctl操作监听，自动反馈到crs中
2.使用srvctl关闭监听
rac1-> srvctl stop listener -n rac1 rac1-> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.....XFF.cs application ONLINE ONLINE rac1 ora....db1.srv application ONLINE ONLINE rac2 ora.devdb.db application ONLINE ONLINE rac2 ora....b1.inst application ONLINE ONLINE rac1 ora....b2.inst application ONLINE ONLINE rac2 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application OFFLINE OFFLINE ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2
3.使用lsnrctl查看监听状态
rac1-> lsnrctl status LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:15:54 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) <--host为空 TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused rac1-> lsnrctl LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:16:55 Copyright (c) 1991, 2005, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> status listener_rac1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)(IP=FIRST))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.11)(PORT=1521)(IP=FIRST))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused
这里可以发现问题:
1)如果当前没有监听在运行，使用lsnrctl status的时候，会去检查默认的监听名称为listener的监听，如果该监听不存在不会使用hostname填充到hostname项中(注意下面的启动默认监听过程)
2)lsnrctl查看指定监听为listener_rac1，发现和listener.ora中配置相同
4.lsnrctl 关闭监听
rac1-> srvctl start listener -n rac1 rac1-> lsnrctl stop LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:43:14 Copyright (c) 1991, 2005, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) <--host为空 The command completed successfully rac1-> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.....XFF.cs application ONLINE ONLINE rac1 ora....db1.srv application ONLINE ONLINE rac2 ora.devdb.db application ONLINE ONLINE rac2 ora....b1.inst application ONLINE ONLINE rac1 ora....b2.inst application ONLINE ONLINE rac2 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application OFFLINE OFFLINE ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2
这里可以说明问题：
1)lsnrctl stop虽然是要停止掉默认监听，但是也会停止掉非默认监听
2)lsnrctl stop如果默认监听不存在，那么注册host也为空
5.使用lsnrctl启动默认监听
rac1-> lsnrctl start LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:17:37 Copyright (c) 1991, 2005, Oracle. All rights reserved. Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 - Production System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 11-MAR-2012 22:17:37 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521))) <--主机名 The listener supports no services The command completed successfully rac1-> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.....XFF.cs application ONLINE ONLINE rac1 ora....db1.srv application ONLINE ONLINE rac2 ora.devdb.db application ONLINE ONLINE rac2 ora....b1.inst application ONLINE ONLINE rac1 ora....b2.inst application ONLINE ONLINE rac2 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application OFFLINE OFFLINE ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2
这里发现问题：
1)监听的ip只有主机名的一个，和srvctl启动的监听不一样
2)虽然监听启动了，crs中依然显示为offline状态
6.使用lsnrctl启动listener_rac1监听
LSNRCTL> start listener_rac1 Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 - Production System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)(IP=FIRST))) STATUS of the LISTENER ------------------------ Alias listener_rac1 Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 11-MAR-2012 22:19:04 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully rac1-> crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.....XFF.cs application ONLINE ONLINE rac1 ora....db1.srv application ONLINE ONLINE rac2 ora.devdb.db application ONLINE ONLINE rac2 ora....b1.inst application ONLINE ONLINE rac1 ora....b2.inst application ONLINE ONLINE rac2 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2
这里可以说明两个问题:
1)使用lsnrctl启动监听和srvctl启动一样
2)启动listener_rac1后，crs中监听资源变成online
7.问题原因分析
rac1-> srvctl config listener -n rac1 rac1 LISTENER_RAC1
通过这里可以发现，其实srvctl操作的监听就是LISTENER_RAC1，所以当我使用lsnrctl 操作LISTENER_RAC1监听时候crs会自动offline或者online，而lsnrctl 操作默认监听时crs不会online
误删除dual表恢复
1.10G中删除dual表恢复
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select object_type,owner from dba_objects where object_name='DUAL';
OBJECT_TYPE OWNER
------------------- ------------------------------
TABLE SYS
SYNONYM PUBLIC
SQL> drop table sys.dual;
Table dropped.
SQL> select object_type from dba_objects where object_name='DUAL';
OBJECT_TYPE
-------------------
SYNONYM
SQL> SELECT SYSDATE FROM dual;
SELECT SYSDATE FROM dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
SQL> CREATE TABLE XFF AS SELECT * from dba_objects;
Table created.
SQL> drop table xff purge;
drop table xff purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
设置10046跟踪会话发现，在每次删除表操作的时候发现如下错误
select dummy from dual where ora_dict_obj_type = 'TABLE'
其实这里错误都很明显，是因为dual表不存在了，表对应的同义词还存在，当查询dual的时候，会去查询同义词，然后该同义词去找表，而表不存在，所以出现上述的ORA-01775: looping chain of synonyms错误
2.解决方法
SQL> CREATE TABLE "SYS"."DUAL"
2 ( "DUMMY" VARCHAR2(1)
3 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
4 STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6 TABLESPACE "SYSTEM" ;
Table created.
SQL> GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION;
Grant succeeded.
SQL> insert into dual values('X');
1 row created.
SQL> COMMIT;
Commit complete.
--编译对象
SQL> @?/rdbms/admin/utlrp.sql
3.测试结果
SQL> select sysdate from dual; SYSDATE ------------ 13-MAR-12 SQL> drop table xff purge; Table dropped.
rman 备份出现ORA-00245/RMAN-08132
rman备份出现如下错误
RMAN> backup full tag 'dbfull' format '/jfkdata1/rman_bak/full_%d_%u' database 2> include current controlfile plus archivelog format '/jfkdata1/rman_bak/arch_%d_%u'; Starting backup at 13-MAR-12 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=297 instance=ykcdb2 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=29 RECID=43 STAMP=777808820 input archived log thread=1 sequence=30 RECID=46 STAMP=777816035 input archived log thread=2 sequence=21 RECID=45 STAMP=777816033 channel ORA_DISK_1: starting piece 1 at 13-MAR-12 channel ORA_DISK_1: finished piece 1 at 13-MAR-12 piece handle=/jfkdata1/rman_bak/arch_YKCDB_01n5p1vf tag=DBFULL comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 Finished backup at 13-MAR-12 Starting backup at 13-MAR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/jfkdata1/oradata/ykcdb/sysaux01.dbf input datafile file number=00001 name=/jfkdata1/oradata/ykcdb/system01.dbf input datafile file number=00003 name=/jfkdata1/oradata/ykcdb/undotbs01.dbf input datafile file number=00005 name=/jfkdata1/oradata/ykcdb/undotbs02.dbf input datafile file number=00004 name=/jfkdata1/oradata/ykcdb/users01.dbf channel ORA_DISK_1: starting piece 1 at 13-MAR-12 channel ORA_DISK_1: finished piece 1 at 13-MAR-12 piece handle=/jfkdata1/rman_bak/full_YKCDB_02n5p209 tag=DBFULL comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 13-MAR-12 channel ORA_DISK_1: finished piece 1 at 13-MAR-12 piece handle=/jfkdata1/rman_bak/full_YKCDB_03n5p213 tag=DBFULL comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13-MAR-12 Starting backup at 13-MAR-12 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=2 sequence=22 RECID=48 STAMP=777816102 input archived log thread=1 sequence=31 RECID=47 STAMP=777816101 channel ORA_DISK_1: starting piece 1 at 13-MAR-12 channel ORA_DISK_1: finished piece 1 at 13-MAR-12 piece handle=/jfkdata1/rman_bak/arch_YKCDB_04n5p217 tag=DBFULL comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13-MAR-12 ORA-00245: control file backup operation failed RMAN-08132: WARNING: cannot update recovery area reclaimable file list RMAN>
从这里可以看出数据库文件，归档日志，spfile和controlfile都备份完成，后面又出现一个controlfile失败，应该是控制文件快照备份失败。
错误原因
RMAN creates a copy of the control file for read consistency, this is the snapshot controlfile. Due to the changes made to the controlfile backup mechanism in 11gR2 any instances in the cluster may write to the snapshot controlfile. Therefore, the snapshot controlfile file needs to be visible to all instances. The same happens when a backup of the controlfile is created directly from sqlplus any instance in the cluster may write to the backup controfile file. In 11gR2 onwards, the controlfile backup happens without holding the control file enqueue. For non-RAC database, this doesn't change anything. But, for RAC database, the snapshot controlfile location must be in a shared file system that will be accessible from all the nodes. The snapshot controlfile MUST be accessible by all nodes of a RAC database.
大致意思就是rman的snapshot controlfile必须放在共享存储之上。
解决方法
The snapshot controlfile MUST be accessible by all nodes of a RAC database, if the snapshot controlfile does not reside in on a shared device this error will raise. 1. Check the snapshot controlfile location: RMAN> show all; 2. Configure the snapshot controlfile to a shared disk: RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '<shared_disk>/snapcf_<DBNAME>.f';
TimesTen c/s模式配置
一、linux/unix环境配置
1.相关IP配置
客户端:192.168.1.105
服务端:192.168.1.10
2.服务端ODBC配置
$ more sys.odbc.ini [ODBC Data Sources] TT_1122=TimesTen 11.2.2 Driver [TT_1122] Driver=/u01/TimesTen/tt1122/lib/libtten.so DataStore=/u01/TimesTen/tt1122/info/TT_1122 DatabaseCharacterSet=ZHS16GBK PermSize=64 OracleNetServiceName=XFF
3.服务端timesten监听端口
$ ttstatus|grep "TimesTen server" TimesTen server pid 3792 started on port 53393
4.客户端配置
$ more sys.ttconnect.ini [tt_xifenfei_c_s] Network_Address=192.168.1.10 TCP_PORT=53393 $ more sys.odbc.ini [ODBC Data Sources] tt_xifenfei_c=TimesTen 11.2.2 Client Driver [tt_xifenfei_c] TTC_SERVER=tt_xifenfei_c_s TTC_SERVER_DSN=tt_1122 UID=chf PWD=xifenfei
参考:在另一节点配置TT客户端的简明方法
二、win环境配置
1.在odbc中选择增加timesten client
2.点击services，增加ip和端口配置
3.配置关闭services，配置其他选项
三、补充说明
这里的配置，基本上都是最基础配置，在实际生产环境中，需要考虑其他相关环境变量
gzexe加密shell脚本
很多时候，我们的脚本中涉及的用户名/密码/ip等敏感信息，我们需要使用一些加密方面来屏蔽这些信息，确保我们的系统安全(主要防菜鸟)，虽然shc可以实现强大的加密功能，但是他需要另外安装shc软件，比较麻烦，应对一般的加密，个人推荐直接喜用系统自带的gzexe(大部分Linux，unix都有)
[root@xifenfei tmp]# more xifenfei.sh
#!/usr/bin/ksh
echo 'xifenfei--'`date`
[root@xifenfei tmp]# ./xifenfei.sh
xifenfei--Tue Mar 6 13:31:35 CST 2012
[root@xifenfei tmp]# ls -l xifenfei.sh
-rwxr-xr-x 1 root root 40 Mar 6 13:30 xifenfei.sh
--加密操作
[root@xifenfei tmp]# gzexe xifenfei.sh
xifenfei.sh: -5.0%
[root@xifenfei tmp]# ls -l xifenfei.sh*
-rwxr-xr-x 1 root root 449 Mar 6 13:31 xifenfei.sh
-rwxr-xr-x 1 root root 40 Mar 6 13:30 xifenfei.sh~
--加密后文件内容
[root@xifenfei tmp]# strings xifenfei.sh
#!/bin/sh
skip=14
tmpdir=`/bin/mktemp -d ${TMPDIR:-/tmp}/gzexe.XXXXXXXXXX` || exit 1
prog="${tmpdir}/`echo \"$0\" | sed 's|^.*/||'`"
if /usr/bin/tail -n +$skip "$0" | "/bin"/gzip -cd > "$prog"; then
/bin/chmod 700 "$prog"
trap '/bin/rm -rf $tmpdir; exit $res' EXIT
"$prog" ${1+"$@"}; res=$?
else
echo "Cannot decompress $0"
/bin/rm -rf $tmpdir
exit 1
fi; exit $res
xifenfei.sh
UOHI,IM
这里的xifenfei.sh是加密后文件，xifenfei.sh~是源码文件(加密前文件)，只需要运行xifenfie.sh即可实现原脚本功能
优点:在大部分系统上均可运行，不用安装额外软件
缺点:破解容易,放君子不防小人
Multiple-table cache group配置
1.准备测试表和数据
SQL> CREATE TABLE customer
2 (cust_num NUMBER(6) NOT NULL PRIMARY KEY,
3 name VARCHAR2(50)
4 );
Table created.
SQL> CREATE TABLE orders
2 (ord_num NUMBER(10) NOT NULL PRIMARY KEY,
3 cust_num NUMBER(6) NOT NULL
4 );
Table created.
SQL> insert into customer values(1,'wwww.xifenfei.com1');
1 row created.
SQL> insert into customer values(2,'wwww.xifenfei.com2');
1 row created.
SQL> insert into customer values(3,'wwww.xifenfei.com3');
1 row created.
SQL> insert into customer values(4,'wwww.xifenfei.com4');
1 row created.
SQL> insert into orders(cust_num,ord_num) values(1,1);
1 row created.
SQL> insert into orders (cust_num,ord_num) values(1,2);
1 row created.
SQL> insert into orders (cust_num,ord_num) values(3,5);
1 row created.
SQL> insert into orders (cust_num,ord_num) values(3,6);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from customer;
CUST_NUM NAME
---------- --------------------------------------------------
1 wwww.xifenfei.com1
2 wwww.xifenfei.com2
3 wwww.xifenfei.com3
4 wwww.xifenfei.com4
SQL> select * from orders;
ORD_NUM CUST_NUM
---------- ----------
1 1
2 1
5 3
6 3
SQL> grant select on oratt.customer to cacheuser;
Grant succeeded.
SQL> grant select on oratt.orders to cacheuser;
Grant succeeded.
2.创建cache group
[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> drop cache group cacheuser.customer_orders;
Command> CREATE READONLY CACHE GROUP customer_orders
> AUTOREFRESH INTERVAL 5 SECONDS
> STATE ON
> FROM oratt.customer
> (cust_num NUMBER(6) NOT NULL,
> name VARCHAR2(50),
> PRIMARY KEY(cust_num)),
> oratt.orders
> (ord_num NUMBER(10) NOT NULL,
> cust_num NUMBER(6) NOT NULL,
> PRIMARY KEY(ord_num),
> FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));
Command> cachegroups;
Cache Group CACHEUSER.CUSTOMER_ORDERS:
Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds
Autorefresh Status: ok
Aging: No aging defined
Root Table: ORATT.CUSTOMER
Table Type: Read Only
Child Table: ORATT.ORDERS
Table Type: Read Only
1 cache groups found.
3.TT中表访问授权
[oracle@xifenfei ~]$ ttisql tt_1122 Command> grant select on oratt.customer to cacheuser; Command> grant select on oratt.orders to cacheuser;
4.测试数据初始化
[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> select * from oratt.customer; < 1, wwww.xifenfei.com1 > < 2, wwww.xifenfei.com2 > < 3, wwww.xifenfei.com3 > < 4, wwww.xifenfei.com4 > 4 rows found. Command> select * from oratt.orders; < 1, 1 > < 2, 1 > < 5, 3 > < 6, 3 > 4 rows found.
5.ORACLE修改数据
SQL> update customer set name='xifenfei' where cust_num=2; 1 row updated. SQL> insert into customer values(5,'wwww.xifenfei.com5'); 1 row created. SQL> delete from customer where cust_num=1; 1 row deleted. SQL> commit; Commit complete.
6.TT中验证数据
Command> select * from oratt.customer; < 2, xifenfei > < 3, wwww.xifenfei.com3 > < 4, wwww.xifenfei.com4 > < 5, wwww.xifenfei.com5 > 4 rows found. Command> select * from oratt.orders; < 5, 3 > < 6, 3 > 2 rows found.
7.补充说明
7.1)在oracle中需要授权cacheuser有访问oratt中相关表权限，不然创建cache group失败
7.2)自动刷新数据需要设置AUTOREFRESH STATE ON，其他方法初始化关联表的数据暂未知
7.3)在TT中，关联表删除是级联的