Monthly Archives: 十二月 2011

创建视图提示ORA-01031

Posted on by

1、重现问题

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select table_name from user_tables where table_name in('X_T','DEPT');

TABLE_NAME
------------------------------
X_T

SQL> create view  v_xff1
  2  as 
  3  select * from X_T;

View created.


SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;
SELECT * FROM SCOTT.DEPT
                    *
ERROR at line 2:
ORA-01031: insufficient privileges


SQL> SELECT COUNT(*) FROM SCOTT.DEPT;

  COUNT(*)
----------
         4

通过上面的试验证明：
1）在同一个schema下，有查询权限，就可以创建视图
2）在不同schema下，即使有了查询权限，创建视图，还是会提示ORA-01031

2、查看相关权限情况

SQL> select * from SESSION_PRIVS where 
  2  PRIVILEGE in('SELECT ANY TABLE','CREATE ANY VIEW','CREATE VIEW');

PRIVILEGE
----------------------------------------
SELECT ANY TABLE
CREATE VIEW
CREATE ANY VIEW

SQL> SELECT * FROM SESSION_PRIVS WHERE PRIVILEGE NOT IN(
  2  SELECT PRIVILEGE
  3    FROM ROLE_SYS_PRIVS
  4   WHERE ROLE IN(SELECT * FROM SESSION_ROLES));

no rows selected

通过上面权限查询得出：用户所具有的select 其他用户表的权限是用过role授权

3、单独授于select权限

SQL> conn / as sysdba
Connected.
SQL> grant select on SCOTT.DEPT to xff;

Grant succeeded.

SQL> conn xff/xifenfei
Connected.
SQL> create view  V_XFF AS
  2  SELECT * FROM SCOTT.DEPT;

View created.

SQL> select view_name from user_views;

VIEW_NAME
------------------------------
V_XFF
V_XFF1

4、产生问题原因
In order to create a view in a schema, that schema must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The view owner must be granted these privileges directly, rather than through a role. The reason is that privileges granted to roles cannot be inherited via objects
ORA-1031 While Creating A View On A Table On Which The Select Privilege Is Granted Via A Role

删除Oracle数据文件/临时文件

Posted on by

有些时候，想删除一个数据文件(临时文件),在10g之前的版本，要删除一个数据文件，必须删除该数据文件所属的表空间(特殊处理方法除外)。不太懂数据库的朋友直接os级别删除数据文件，导致数据库不能正常启动;稍微等点数据库的朋友，会先offline数据文件，然后os级别删除，但是这条数据文件的记录还保留在数据字典中，不爽(特殊处理办法见:清除离线数据文件记录)。在10g及其以后版本中，oracle提供了alter tablespace talbespace_name drop datafile/tempfile path/file_id进行删除某个数据文件

一、删除数据文件

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 8 11:22:38 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> col tablespace_name for a20
SQL> col file_name for a50
SQL> set line 120
SQL> select file_id,file_name,tablespace_name 
   2 from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /opt/oracle/oradata/test/odu01.dbf                 ODU
        10 /opt/oracle/oradata/test/odu03.dbf                 ODU
         8 /opt/oracle/oradata/test/odu02.dbf                 ODU
         3 /opt/oracle/oradata/test/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/test/system01.dbf              SYSTEM
         2 /opt/oracle/oradata/test/undotbs01.dbf             UNDOTBS1
         5 /opt/oracle/oradata/test/user32g.dbf               USERS
         7 /opt/oracle/oradata/test/user02.dbf                USERS
         4 /opt/oracle/oradata/test/users01.dbf               USERS
         6 /opt/oracle/oradata/test/xifenfei01.dbf            XFF
        11 /opt/oracle/oradata/test/xifenfei03.dbf            XFF

11 rows selected.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 00:05 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 00:05 /opt/oracle/oradata/test/xifenfei01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/xifenfei03.dbf

SQL> alter tablespace xff drop datafile 11;
alter tablespace xff drop datafile 11
*
ERROR at line 1:
ORA-03262: the file is non-empty

SQL> col segment_name for a20
SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=11;

OWNER                          SEGMENT_NAME            FILE_ID     BLOCKS
------------------------------ -------------------- ---------- ----------
CHF                            XFF_TEST                     11          8
CHF                            XFF_TEST                     11        128
CHF                            XFF_TEST                     11        128
CHF                            T_XFF                        11        128
CHF                            T_XFF                        11        128

SQL> alter table chf.xff_test move tablespace users;

Table altered.

SQL> alter table chf.t_xff move tablespace users;

Table altered.

SQL> select owner,SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents
  2  where file_id=11;

no rows selected

SQL> alter tablespace xff drop datafile 11;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name 
  2  from dba_data_files order by tablespace_name;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         9 /opt/oracle/oradata/test/odu01.dbf                 ODU
        10 /opt/oracle/oradata/test/odu03.dbf                 ODU
         8 /opt/oracle/oradata/test/odu02.dbf                 ODU
         3 /opt/oracle/oradata/test/sysaux01.dbf              SYSAUX
         1 /opt/oracle/oradata/test/system01.dbf              SYSTEM
         2 /opt/oracle/oradata/test/undotbs01.dbf             UNDOTBS1
         4 /opt/oracle/oradata/test/users01.dbf               USERS
         7 /opt/oracle/oradata/test/user02.dbf                USERS
         5 /opt/oracle/oradata/test/user32g.dbf               USERS
         6 /opt/oracle/oradata/test/xifenfei01.dbf            XFF

10 rows selected.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 12:36 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:45 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:45 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:52 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:52 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:52 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:52 /opt/oracle/oradata/test/xifenfei01.dbf

二、删除临时表空间文件

SQL> alter  tablespace temp add tempfile '/opt/oracle/oradata/test/xff_temp02.dbf' size 10m;

Tablespace altered.

SQL> select file_id,file_name,tablespace_name from dba_temp_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         2 /opt/oracle/oradata/test/xff_temp02.dbf            TEMP
         1 /opt/oracle/oradata/test/temp01.dbf                TEMP

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 13:00 /opt/oracle/oradata/test/xff_temp02.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf

SQL> alter tablespace temp drop tempfile 2;

Tablespace altered.

SQL> !ls -l /opt/oracle/oradata/test/*.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  8 00:05 /opt/oracle/oradata/test/odu01.dbf
-rw-r----- 1 oracle oinstall 11282685952 Dec  8 00:05 /opt/oracle/oradata/test/odu02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 00:05 /opt/oracle/oradata/test/odu03.dbf
-rw-r----- 1 oracle oinstall   387981312 Dec  8 13:00 /opt/oracle/oradata/test/sysaux01.dbf
-rw-r----- 1 oracle oinstall   534781952 Dec  8 12:58 /opt/oracle/oradata/test/system01.dbf
-rw-r----- 1 oracle oinstall   104865792 Dec  7 22:01 /opt/oracle/oradata/test/temp01.dbf
-rw-r----- 1 oracle oinstall  1289756672 Dec  8 12:57 /opt/oracle/oradata/test/undotbs01.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user02.dbf
-rw-r----- 1 oracle oinstall    10493952 Dec  8 12:57 /opt/oracle/oradata/test/user32g.dbf
-rw-r----- 1 oracle oinstall     5251072 Dec  8 12:57 /opt/oracle/oradata/test/users01.dbf
-rw-r----- 1 oracle oinstall    20979712 Dec  8 12:57 /opt/oracle/oradata/test/xifenfei01.dbf

三、注意事项
1、Oracle Database 10g Release 2及其以上版本
2、删除数据文件从数据库和系统
3、数据文件必须为空
4、数据文件不是对应表空间的最后一个
5、不能删除只读表空间中数据文件
6、不能删除system中数据文件
7、不能删除本地管理的offline表空间数据文件

TNS-12525 TNS-12535 TNS-12606

Posted on by

一、发现TNS-12525错误
检查公司服务器listener日志中周期性发现如下错误提示：

08-DEC-2011 02:25:34 * (CONNECT_DATA=(SID=ora9i)) * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4877)) * establish * ora9i * 0
08-DEC-2011 02:25:35 * (CONNECT_DATA=(SID=ora9i)) * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4879)) * establish * ora9i * 0
08-DEC-2011 02:25:43 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=2486)) * establish * <unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred
08-DEC-2011 02:26:02 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.236.234)(PORT=4876)) * establish * <unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred

继续检查日志发现，只有211.155.236.234是外网ip，其他都是通过内网ip访问，都未报类此错误。初步怀疑是公司和运营商网络之间的防火墙导致

二、数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

三、MOS说明

Symptoms
At regular intervals (every three hours), you see new connections rejected with TNS-12525 "listener has not received client's request in time allowed".
The listener log shows the following error stack :
10-APR-2008 17:39:15 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=10.221.50.31)(PORT=4184)) * establish * <unknown sid> * 12525 
  TNS-12525: TNS:listener has not received client's request in time allowed 
    TNS-12535: TNS:operation timed out 
      TNS-12606: TNS: Application timeout occurred

At the same time the firewall log reports :
"TCP packet out of state: First packet isn't SYN; tcp_flags: PUSH-ACK"

Wireshark (Ethereal) traces show the TCP 3-way handshake never completes for those connections.

Changes
Value of idle timer was recently changed at firewall level.

Cause
Firewall closes ports not allowing the 3-way handshake to complete at TCP level.

Solution
Increase the firewall's idle timeout interval.

New Connections Intermittently Rejected with TNS-12525

异常断电导致current redo损坏处理

Posted on by

一、数据库启动异常
网友因异常断电导致数据库不能正常启动，处理过程记录
1、sqlplus中提示

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 12:57:02 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'

2、alert日志文件

ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc  (incident=214262):
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214262\hzyl_ora_2960_i214262.trc
Aborting crash recovery due to error 368
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 12014 更改 9743799889 时间 12/05/2011 09:21:11
ORA-00312: 联机日志 3 线程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
ORA-368 signalled during: ALTER DATABASE OPEN...
Wed Dec 07 12:57:17 2011
Trace dumping is performing id=[cdmp_20111207125717]
Wed Dec 07 12:57:17 2011
Sweep Incident[214262]: completed
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_m000_2400.trc  (incident=214326):
ORA-00353: log corruption near block 12014 change 9743799889 time 12/05/2011 09:21:11
ORA-00312: online log 3 thread 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214326\hzyl_m000_2400_i214326.trc
Trace dumping is performing id=[cdmp_20111207125718]

二、查询是损坏日志组

SQL> col member for a35
SQL> select a.group#,a.status,b.member from v$log a,v$logfile b 
   2 where a.group#=b.group#;

    GROUP# STATUS           MEMBER
---------- ---------------- -----------------------------------
         3 CURRENT          R:\ORADATA\HZYL\REDO03.LOG
         2 INACTIVE         R:\ORADATA\HZYL\REDO02.LOG
         1 INACTIVE         R:\ORADATA\HZYL\REDO01.LOG

确定是因为断电导致当前redo 损坏，需要使用隐含参数+不完全恢复处理(经验)

三、不完全恢复

SQL> recover database until cancel;
ORA-00279: 更改 9743796718 (在 12/05/2011 09:07:40 生成) 对于线程 1 是必需的
ORA-00289: 建议: S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC
ORA-00280: 更改 9743796718 (用于线程 1) 在序列 #15114 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: 无法打开归档日志
'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


ORA-00308: 无法打开归档日志
'S:\ORAFLASH\HZYL\ARCHIVELOG\2011_12_07\O1_MF_1_15114_%U_.ARC'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'R:\ORADATA\HZYL\SYSTEM01.DBF'

四、启用隐含参数，尝试打开数据库

SQL> create pfile='d:/1.txt' from spfile;

文件已创建。

#############添加隐含参数##################
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE
##########################################

SQL> shutdown immediate
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile ='d:\1.txt';
ORACLE 例程已经启动。

Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项


SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00603: ORACLE 服务器会话因致命错误而终止
进程 ID: 3388
会话 ID: 335 序列号: 5

五、查看alert日志

Wed Dec 07 13:02:49 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216664):
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216664\hzyl_ora_3388_i216664.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 3388): terminating the instance due to error 600
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmnl_3884.trc  (incident=216640):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216640\hzyl_mmnl_3884_i216640.trc
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_diag_4020.trc  (incident=216552):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216552\hzyl_diag_4020_i216552.trc
Wed Dec 07 13:02:50 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dia0_2896.trc  (incident=216584):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216584\hzyl_dia0_2896_i216584.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_pmon_2964.trc  (incident=216536):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216536\hzyl_pmon_2964_i216536.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mmon_3764.trc  (incident=216632):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216632\hzyl_mmon_3764_i216632.trc
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbrm_2060.trc  (incident=216560):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:51 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_psp0_1476.trc  (incident=216568):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:52 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_mman_3040.trc  (incident=216576):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Wed Dec 07 13:02:52 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_dbw0_2116.trc  (incident=216592):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_lgwr_2624.trc  (incident=216600):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_smon_592.trc  (incident=216616):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ckpt_3052.trc  (incident=216608):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
Instance terminated by USER, pid = 3388
ORA-1092 signalled during: alter database open resetlogs...
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216665):
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=216666):
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Wed Dec 07 13:02:53 2011
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc  (incident=218920):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_218920\hzyl_ora_3388_i218920.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []

这里看到熟悉的ora-600 [2662]，处理方法增加scn，参考ORA-00600 [2662]

六、增加SCN，打开数据库

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断
开

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on 星期三 12月 7 13:04:51 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> startup pfile ='d:\1.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1581916160 bytes
Fixed Size                  1336060 bytes
Variable Size             964693252 bytes
Database Buffers          603979776 bytes
Redo Buffers               11907072 bytes
数据库装载完毕。
SQL> alter session set events '10015 trace name adjust_scn level 1';

会话已更改。


SQL> alter database open;

数据库已更改。

SQL> select status from v$instance;

STATUS
------------
OPEN

七、启动过程中，alert日志现ORA-00600[2256]

Completed: ALTER DATABASE   MOUNT
Wed Dec 07 13:06:07 2011
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 1 redo blocks read, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 2, scn 9743796722
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: R:\ORADATA\HZYL\REDO01.LOG
Completed redo application
Completed crash recovery at
 Thread 1: logseq 1, block 3, scn 9743816724
 0 data blocks read, 0 data blocks written, 1 redo blocks read
Wed Dec 07 13:06:08 2011
Thread 1 advanced to log sequence 2
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: R:\ORADATA\HZYL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 07 13:06:08 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3096.trc  (incident=219072):
ORA-00600: 内部错误代码, 参数: [2256], [0], [1073741824], [2], [1153882138], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_219072\hzyl_ora_3096_i219072.trc
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Wed Dec 07 13:06:09 2011
Trace dumping is performing id=[cdmp_20111207130609]
Opening with internal Resource Manager plan
Wed Dec 07 13:06:10 2011
Sweep Incident[219072]: completed
Starting background process FBDA
Starting background process SMCO
Wed Dec 07 13:06:10 2011
FBDA started with pid=26, OS id=2436 
Wed Dec 07 13:06:10 2011
SMCO started with pid=27, OS id=2876 
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Dec 07 13:06:11 2011
QMNC started with pid=28, OS id=2812 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Wed Dec 07 13:06:15 2011
db_recovery_file_dest_size of 10000 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Dec 07 13:06:25 2011
Completed: alter database open

打开过程中，出现ORA-00600[2256]，但是数据库还是正常打开，具体原因待研究

利用impdp结合network_link+FLASHBACK_TIME初始化ogg同步数据

Posted on by

部署ogg，需要两边初始化数据，但是因为业务不能停止。所以考虑到使用ogg init功能或者impdp。考虑到数据量比较大，自己对ogg init不是很有信心，所以选择了使用impdp结合network_link+FLASHBACK_TIME处理
一、处理思路
1、选择时间点(尚未达到时间点)
选择一个时间点，用于重设ogg同步时间点，impdp导入时间点
要求:ogg尚未加载到该时间点(在该时间点之前停止ogg相关ext/pump/replicat进程)

2、开始导入指定时间点数据(该时间点已经达到后)

[oracle@srtcreen OGG]$ impdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 13:03:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\"

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 06 December, 2011 13:04:34

Copyright (c) 2003, 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
Starting "SRT_CREEN"."SYS_IMPORT_TABLE_01":  srt_creen/******** directory=DATA_PUMP_DIR network_link=OGG_TYKF REMAP_SCHEMA=CSCNEW:SRT_CREEN REMAP_TABLESPACE=CSC_TAB_1:SRTCREEN tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME="to_timestamp('2011-12-06 13:03:00','yyyy-mm-dd hh24:mi:ss')" 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 824.5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
    . . imported "SRT_CREEN"."TAB_CS_CALLLOG"               3289293 rows
    . . imported "SRT_CREEN"."TAB_CS_CALL_COMING"           1218843 rows
 . . imported "SRT_CREEN"."TAB_CS_CALL_PICKUP"           1092937 rows
. . imported "SRT_CREEN"."TAB_CS_QUEUES"                   4614 rows
. . imported "SRT_CREEN"."TAB_CS_USER"                      458 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SRT_CREEN"."SYS_IMPORT_TABLE_01" successfully completed at 13:12:37

3、重设同步时间点，开启进程

alter ext_1,begin 2011-12-06 13:03:00
alter ext_2,begin 2011-12-06 13:03:00
alter ext_3,begin 2011-12-06 13:03:00
alter ext_4,begin 2011-12-06 13:03:00
alter ext_5,begin 2011-12-06 13:03:00

alter p_1,begin 2011-12-06 13:03:00
alter p_2,begin 2011-12-06 13:03:00
alter p_3,begin 2011-12-06 13:03:00
alter p_4,begin 2011-12-06 13:03:00
alter p_5,begin 2011-12-06 13:03:00
start *

alter r_1,begin 2011-12-06 13:03:00
alter r_2,begin 2011-12-06 13:03:00
alter r_3,begin 2011-12-06 13:03:00
alter r_4,begin 2011-12-06 13:03:00
alter r_5,begin 2011-12-06 13:03:00
start *

4、验证同步情况

--源端
GGSCI (tykf) 23> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT_1       00:00:00      00:00:05    
EXTRACT     RUNNING     EXT_2       00:00:00      00:00:05    
EXTRACT     RUNNING     EXT_3       00:00:00      00:00:07    
EXTRACT     RUNNING     EXT_4       00:00:00      00:00:07    
EXTRACT     RUNNING     EXT_5       00:00:00      00:00:07    
EXTRACT     RUNNING     P_1         00:00:00      00:00:06    
EXTRACT     RUNNING     P_2         00:00:00      00:00:06    
EXTRACT     RUNNING     P_3         00:00:00      00:00:06    
EXTRACT     RUNNING     P_4         00:00:00      00:00:06    
EXTRACT     RUNNING     P_5         00:00:00      00:00:06    

GGSCI (tykf) 24> stats p_3

Sending STATS request to EXTRACT P_3 ...

Start of Statistics at 2011-12-06 13:15:46.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                   0.00
        Mapped operations                            0.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00

Output to /opt/OGG/dirdat/U3:

Extracting from CSCNEW.TAB_CS_CALL_PICKUP to CSCNEW.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Daily statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Hourly statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Latest statistics since 2011-12-06 13:13:01 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

End of Statistics.



--目标端
GGSCI (srtcreen) 11> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     R_1         00:00:00      00:00:06    
REPLICAT    RUNNING     R_2         00:00:00      00:00:06    
REPLICAT    RUNNING     R_3         00:00:00      00:00:08    
REPLICAT    RUNNING     R_4         00:00:00      00:00:04    
REPLICAT    RUNNING     R_5         00:00:00      00:00:02    


GGSCI (srtcreen) 12> stats r_3

Sending STATS request to REPLICAT R_3 ...

Start of Statistics at 2011-12-06 13:15:35.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   0.00
        Mapped operations                            0.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00
        Errors                                       0.00
        Retried errors                               0.00
        Discarded errors                             0.00
        Ignored errors                               0.00

Replicating from CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Daily statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Hourly statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

*** Latest statistics since 2011-12-06 13:13:10 ***
        Total inserts                                8.00
        Total updates                               10.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                            18.00

End of Statistics.

二、配置过程遇到问题
1、impdp报无权限

[oracle@srtcreen OGG]$ expdp srt_creen/a69UiBOB1gk directory=DATA_PUMP_DIR network_link=OGG_TYKF tables=CSCNEW.TAB_CS_USER,CSCNEW.TAB_CS_QUEUES,CSCNEW.TAB_CS_CALL_PICKUP,CSCNEW.TAB_CS_CALL_COMING,CSCNEW.TAB_CS_CALLLOG FLASHBACK_TIME=\"to_timestamp\(\'2011-12-06 11:00:00\',\'yyyy-mm-dd hh24:mi:ss\'\)\" dumpfile=a.dmp

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 06 December, 2011 11:15:04

Copyright (c) 2003, 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
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

srt_creen用户需要有imp_full_database权限
dblink中用户需要exp_full_database权限

2、目标端进程报OGG-01003

Opened trail file /opt/OGG/dirdat/U3000002 at 2011-12-06 12:34:47

Switching to next trail file /opt/OGG/dirdat/U3000003 at 2011-12-06 12:35:21 due to EOF, with current RBA 1028
Opened trail file /opt/OGG/dirdat/U3000003 at 2011-12-06 12:35:21

Processed extract process graceful restart record at seq 3, rba 993.
Processed extract process graceful restart record at seq 3, rba 1051.

MAP resolved (entry CSCNEW.TAB_CS_CALL_PICKUP):
  MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
Using following columns in default map by name:
  PICKUP_ID, CALL_ID, CALL_SERIAL, USER_ID, PICKUP_TIME, CALL_RESULT, 
  FAIL_REASON, CALL_TIME, CALL_DURA, END_TIME

Using the following key columns for target table SRT_CREEN.TAB_CS_CALL_PICKUP: CALL_ID.


2011-12-06 12:35:22  WARNING OGG-00869  OCI Error ORA-01407: cannot update ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDAT
E "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = :a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CAL
L_TIME" = :a7,"CALL_DURA" = :a8,"END_T>.

2011-12-06 12:35:23  WARNING OGG-01004  Aborted grouped transaction on 'SRT_CREEN.TAB_CS_CALL_PICKUP', Database error 1407 (OCI Error ORA-01407: cannot updat
e ("SRT_CREEN"."TAB_CS_CALL_PICKUP"."PICKUP_ID") to NULL (status = 1407), SQL <UPDATE "SRT_CREEN"."TAB_CS_CALL_PICKUP" SET "PICKUP_ID" = :a0,"CALL_SERIAL" = 
:a2,"USER_ID" = :a3,"PICKUP_TIME" = :a4,"CALL_RESULT" = :a5,"FAIL_REASON" = :a6,"CALL_TIME" = :a7,"CALL_DURA" = :a8,"END_T>).

2011-12-06 12:35:23  WARNING OGG-01003  Repositioning to rba 1111 in seqno 3.

部署ogg时配置relicat错误:
非MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (CALL_ID);
而是MAP CSCNEW.TAB_CS_CALL_PICKUP, TARGET SRT_CREEN.TAB_CS_CALL_PICKUP, KEYCOLS (PICKUP_ID);

3、update同步失败

GGSCI (srtcreen) 12> stats r_3

Sending STATS request to REPLICAT R_3 ...

Start of Statistics at 2011-12-06 12:37:49.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                   0.00
        Mapped operations                            0.00
        Unmapped operations                          0.00
        Other operations                             0.00
        Excluded operations                          0.00
        Errors                                       0.00
        Retried errors                               0.00
        Discarded errors                             0.00
        Ignored errors                               0.00

Replicating from CSCNEW.TAB_CS_CALL_PICKUP to SRT_CREEN.TAB_CS_CALL_PICKUP:

*** Total statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Daily statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Hourly statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

*** Latest statistics since 2011-12-06 12:37:43 ***
        Total inserts                                2.00
        Total updates                                6.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             8.00
        Total update collisions                      6.00

End of Statistics.

源端忘记执行add trandata 同步表

add trandata CSCNEW.TAB_CS_USER
add trandata  CSCNEW.TAB_CS_QUEUES
add trandata  CSCNEW.TAB_CS_CALL_PICKUP
add trandata CSCNEW.TAB_CS_CALL_COMING
add trandata CSCNEW.TAB_CS_CALLLOG