ORA-00600[kfioTranslateIO03] 和ORA-00600[17090]的解决

发表于 2015 年 5 月 2 日 由 Lunar

今天玩VM，发现数据库不能启动了：

[oracle@lunar1 test]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 24 05:32:01 2013

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

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             184550744 bytes
Database Buffers          432013312 bytes
Redo Buffers                7532544 bytes
ORA-03113: end-of-file on communication channel
Process ID: 12878
Session ID: 125 Serial number: 3


SQL>

检查alert日志，报错如下：

Mon Jun 24 05:32:16 2013
ORACLE_BASE from environment = /u01/app/oracle
Mon Jun 24 05:32:16 2013
ALTER DATABASE   MOUNT
NOTE: Loaded library: System 
ORA-15025: could not open disk "/dev/asm-diske"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk "/dev/asm-diskf"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
SUCCESS: diskgroup ASMDATA was mounted
ERROR: failed to establish dependency between database lunar and diskgroup resource ora.ASMDATA.dg
Errors in file /u01/app/oracle/diag/rdbms/lunar/lunar1/trace/lunar1_ckpt_12857.trc  (incident=4945):
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/lunar/lunar1/incident/incdir_4945/lunar1_ckpt_12857_i4945.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Jun 24 05:32:19 2013
Sweep [inc][3746]: completed
Sweep [inc][3745]: completed
Sweep [inc2][3746]: completed
Sweep [inc2][3745]: completed
Dumping diagnostic data in directory=[cdmp_20130624053221], requested by (instance=1, osid=12857 (CKPT)), summary=[incident=4945].
Errors in file /u01/app/oracle/diag/rdbms/lunar/lunar1/trace/lunar1_ckpt_12857.trc  (incident=4946):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/lunar/lunar1/incident/incdir_4946/lunar1_ckpt_12857_i4946.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 12857 
Dumping diagnostic data in directory=[cdmp_20130624053223], requested by (instance=1, osid=12857 (CKPT)), summary=[incident=4946].
Mon Jun 24 05:32:24 2013
PMON (ospid: 12823): terminating the instance due to error 469
System state dump requested by (instance=1, osid=12823 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/lunar/lunar1/trace/lunar1_diag_12833.trc
Mon Jun 24 05:32:25 2013
Dumping diagnostic data in directory=[cdmp_20130624053224], requested by (instance=1, osid=12823 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 12823

很久没玩这个VM，隐约记得上次好像测试修改权限了
然后，根据报错查询官网：
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []
果然是权限问题，因此检查相关权限并修改。

[oracle@lunar1 ~]$ cd $ORACLE_HOME/bin/
[oracle@lunar1 bin]$ ll oracle
-rwsr-x--x 1 oracle asmadmin 222M May  3 15:11 oracle
[oracle@lunar1 bin]$ chmod 6751 oracle
[oracle@lunar1 bin]$ ll oracle
-rwsr-x--x 1 oracle asmadmin 222M May  3 15:11 oracle
[oracle@lunar1 bin]$

使用root：

[root@lunar1 bin]# chmod 6751 oracle
[root@lunar1 bin]# ll oracle
-rwsr-s--x 1 oracle asmadmin 232399431 May  3 15:11 oracle
[root@lunar1 bin]#

现在启动数据库就不报错了：

[oracle@lunar1 test]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 24 05:48:33 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             184550744 bytes
Database Buffers          432013312 bytes
Redo Buffers                7532544 bytes
Database mounted.
Database opened.
SQL>
发表在 ORA-600 or ORA-7445 | 标签为 , | 留下评论

在Double-E之间配置基于Infiniband的SDP连接（比如，两个Exadata，或者1个Exadata和1个Exaligic之间）

发表于 2015 年 4 月 27 日 由 Lunar

在Double-E之间（比如，两个Exadata，或者1个Exadata和1个Exaligic之间），我们可以使用IB来连接
比如两个Exadata之间互相做灾备（ADG）
再比如，Exalogic上有大量的空间，即使是1/4配置的Exalogic上也有几十T的容量空闲（没有部署应用的空间）。
有时候我们会见可以购买了Double-E的客户用这个空间来做Exadata上的数据库的ADG。
为了充分利用IB的传输速度块，低延迟和CPU消耗少等优点，Double-E之间的互联，一般建议通过SDP协议访问Exadata上的数据库
文档建议（Exadata X2和X3），Exadata和Exalogic使用8个IB线进行连接（4跟的话也可以，但是没有冗余，一般不建议）
Exadata 1/4 rack和Exalogic 1/4 rack上分别都有2个叶IB交换机（半配的会有第三个主IB交换机，从Exadata X3开始，出厂时缺省没有最下的主交换机了）
这4个交换机如下：
Exadata-IB1 Exadata–IB2
Exalogic-IB1 Exalogic–IB2

连接方式：
Exadata-IB1——-Exalogic–IB2
Exadata-IB1——-Exalogic–IB1
Exadata-IB2——-Exalogic–IB1
Exadata-IB2——-Exalogic–IB2

配置方式具体如下：
1、修改db node上的/etc/infiniband/openib.conf：

SDP_LOAD=yes

2、在/etc/ofed/libsdp.conf中添加：

use tcp server * *:*
use tcp client * *:*

3、在/etc/modprobe.conf都添加并且reboot节点：

options ib_sdp sdp_zcopy_thresh=0 recv_poll=0

配置Double-E（比如Exadata和ExalogicIB，或者Exadata和Exadata之间的）网络上配置监听：

192.168.10.21 dm01db01-ibvip.lunar.com dm01db01-ibvip
192.168.10.22 dm01db02-ibvip.lunar.com dm01db02-ibvip

添加network2（for IB）：

[root@dm01db01 ~]# srvctl add network -k 2 -S 192.168.8.0/255.255.252.0/bondib0 
[root@dm01db01 ~]#

验证添加结果：

[root@dm01db01 ~]# crsctl stat res -t | grep net
ora.net1.network
ora.net2.network 					-- Output indicating new Network resource
[root@dm01db01 ~]# 
 
[root@dm01db01 ~]# srvctl config network -k 2
Network exists: 2/192.168.8.0/255.255.252.0/bondib0, type static
[root@dm01db01 ~]#

添加vip：

[root@dm01db01 ~]# srvctl add vip -n dm01db01 -A dm01db01-ibvip/255.255.252.0/bondib0 -k 2
[root@dm01db01 ~]# srvctl add vip -n dm01db02 -A dm01db02-ibvip/255.255.252.0/bondib0 -k 2
[root@dm01db01 ~]#

检查vip是否可以正常启动：

[root@dm01db01 ~]# srvctl start vip -i dm01db01-ibvip
[root@dm01db01 ~]#

[root@dm01db02 ~]# srvctl start vip -i dm01db02-ibvip
[root@dm01db02 ~]#

添加这个IB网络的监听（LISTENER_IB）：

[root@dm01db01 ~]# srvctl add listener -l LISTENER_IB -k 2 -p TCP:1522,/SDP:1522
[root@dm01db01 ~]# srvctl start listener -l LISTENER_IB
[root@dm01db01 ~]#

[grid@dm01db01 ~]$ lsnrctl status listener_ib

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-DEC-2012 12:43:15

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_IB)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_IB
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                18-DEC-2012 12:41:03
Uptime                    0 days 0 hr. 2 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0.3/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dm01db01/listener_ib/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_IB)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.21)(PORT=1522)))
The listener supports no services
The command completed successfully
[grid@dm01db01 ~]$

配置tnsnames.ora

DBFS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbfs)
    )
  )

LUNARDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LUNARDB)
    )
  )


#the following are for IB
LISTENER_IBREMOTE=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01db02-ibvip.lunar.com)(PORT = 1522))
  )

LISTENER_IBLOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01db01-ibvip.lunar.com)(PORT = 1522))
    (ADDRESS = (PROTOCOL = SDP)(HOST = dm01db01-ibvip.lunar.com)(PORT = 1522))
  )


LISTENER_IPLOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm0101-vip.lunar.com)(PORT = 1521))
  )


LISTENER_IPREMOTE=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan.lunar.com)(PORT = 1521))
  )


LUNARDB_IB =
  (DESCRIPTION =
  (LOAD_BALANCE=on)
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01db01-ibvip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01db02-ibvip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LUNARDB)
    )
  )

修改数据库参数listener_networks：

SQL> alter system set listener_networks='((NAME=network2) (LOCAL_LISTENER=LISTENER_IBLOCAL)(REMOTE_LISTENER=LISTENER_IBREMOTE))', '((NAME=network1)(LOCAL_LISTENER=LISTENER_IPLOCAL)(REMOTE_LISTENER=LISTENER_IPREMOTE))' scope=both;

System altered.

SQL>

测试连接，使用oracle用户：

conn sys/oracle@LUNARDB
conn sys/oracle@LUNARDB_IB

在客户端（Exalogic或者其他需要连接db的主机上），需要配置 如下连接串：

LUNARDB_IB =
  (DESCRIPTION =
  (LOAD_BALANCE=on)
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01db01-ibvip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01db02-ibvip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LUNARDB)
    )
  )

好了，这样Exalogic就可以使用IB连接Exadata了。

发表在 Exadata, 日常运维 | 标签为 , | 留下评论

Exadata X5 上测试单进程impdp导入数据的效率

发表于 2015 年 4 月 23 日 由 Lunar

===========================================
单进程，每分钟： 16G（每小时960G）
7分钟，导完LUNAR_P201404_1
请注意下面的过程 parallel=1，表示单进程测试
（只能测试单进程，是因为跟我同事的导出方式有关系，明天测试多进程……）
===========================================

[oracle@dm01db01 lunar]$ nohup impdp LUNAR/passwd content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log &
[1] 26837
[oracle@dm01db01 lunar]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@dm01db01 lunar]$ jobs
[1]+  Running                 nohup impdp LUNAR/passwd content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log &
[oracle@dm01db01 lunar]$ jobs
[1]+  Running                 nohup impdp LUNAR/passwd content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log &
[oracle@dm01db01 lunar]$ tail -f nohup.out 
Import: Release 11.2.0.4.0 - Production on Thu Apr 23 00:05:53 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "LUNAR"."IMPDP_LUNAR_P201404_1" successfully loaded/unloaded
Starting "LUNAR"."IMPDP_LUNAR_P201404_1":  LUNAR/******** content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

导入过程中进行检测：
可以看到exadata聪明的选择了直接裸盘，没有走Flashcache，每秒钟吞吐量大概460MB左右（一起开始时候会有一个高峰大概有2倍的这个值，猜测是由于分配空间等等）

Current Time: Thu Apr 23 00:06:30 CST 2015               ONLY RECEIVED DATA FROM 4 NODES, INSTEAD OF 5 NODES!
               <---------------Disks------------------><---------------Flash------------------><--------CPU----------><-----------Memory------->
                MBRead Reads RSize MBWrit Writes WSize  MBRead Reads RSize MBWrit Writes WSize  User Sys Wait Irq Run  FreeMB SwapMB SwIn SwOut 
dm01cel01         0    38     3    221    913   248       0     0     0      0      0     0     5   4    0   0   3   49851      0    0     0 
dm01cel02         0    50     3    242    986   252       0     0     0      0      0     0     1   2    0   0   0   51249      0    0     0 
dm01cel03         0    39     3    245   1011   248       0     0     0      0      0     0     1   1    0   0   0   50961      0    0     0 
TotalIO: 708 MB/s;   DiskRead: 0 MB/s;  DiskWrite: 708 MB/s;   FlashRead: 0 MB/s;  FlashWrite: 0 MB/s;  Average CPU: 5%;
            <--------CPU----------><---------------Disks------------------><-----------Memory------->
             User Sys Wait Irq Run  MBRead Reads RSize MBWrit Writes WSize  FreeMB SwapMB SwIn SwOut 
dm01db01        2   1    0   0   1       3    21   153      0      8    26    2802      1    0     0 
Average CPU: 3%;

大概2~3分钟后，存储节点总的吞吐量稳定在每秒钟460MB：


Current Time: Thu Apr 23 00:08:24 CST 2015               ONLY RECEIVED DATA FROM 4 NODES, INSTEAD OF 5 NODES!
               <---------------Disks------------------><---------------Flash------------------><--------CPU----------><-----------Memory------->
                MBRead Reads RSize MBWrit Writes WSize  MBRead Reads RSize MBWrit Writes WSize  User Sys Wait Irq Run  FreeMB SwapMB SwIn SwOut 
dm01cel01         0    20     1    148    604   251       0     0     0      0      0     0     5   3    0   0   2   50039      0    0     0 
dm01cel02         0    26     1    152    627   248       0     0     0      0      0     0     1   0    0   0   2   51247      0    0     0 
dm01cel03         0    25     1    164    673   250       0     0     0      0      0     0     1   0    0   0   1   50958      0    0     0 
TotalIO: 464 MB/s;   DiskRead: 0 MB/s;  DiskWrite: 464 MB/s;   FlashRead: 0 MB/s;  FlashWrite: 0 MB/s;  Average CPU: 3%;
            <--------CPU----------><---------------Disks------------------><-----------Memory------->
             User Sys Wait Irq Run  MBRead Reads RSize MBWrit Writes WSize  FreeMB SwapMB SwIn SwOut 
dm01db01        1   1    0   0   2     124   502   253      0      5    69    1362      1    0     0 
Average CPU: 2%;

===========================================
测试结果：
在数据库中实测的数据（按照每分钟这个表的增长大小来计算）
单进程，导入速度每分钟： 16G
7分钟，导完LUNAR_P201404_1，该表大概309GB（按照这个测试，每小时大概2.5TB左右）
===========================================


SYS@lunar1>set timing on
SYS@lunar1>set time on
00:07:25 SYS@lunar1>col segment_name format a45 heading "Segment Name"
00:07:32 SYS@lunar1>select sum(bytes)/1024/1024/1024     "Size In GB"
00:07:32   2  from dba_segments
00:07:32   3  where owner in upper('LUNAR')
00:07:32   4  order by 1;

      Size In GB
----------------
  236.7705078125

Elapsed: 00:00:00.13

      Size In GB
----------------
  252.2080078125

Elapsed: 00:00:00.03
00:08:31 SYS@lunar1>
。。。。。
00:13:25 SYS@lunar1>/

      Size In GB
----------------
309.794738769531  --------------改表总共309GB

Elapsed: 00:00:00.03
00:13:27 SYS@lunar1>

[oracle@dm01db01 lunar]$ tail -f nohup.out 
Import: Release 11.2.0.4.0 - Production on Thu Apr 23 00:05:53 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "LUNAR"."IMPDP_LUNAR_P201404_1" successfully loaded/unloaded
Starting "LUNAR"."IMPDP_LUNAR_P201404_1":  LUNAR/******** content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_5"  25.19 GB 69742631 rows
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_1"      0 KB       0 rows
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_4"  25.11 GB 69877929 rows
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_3"  24.34 GB 67662725 rows
. . imported "LUNAR"."LUNAR_P201404_1":"LUNAR_P201404_2"  17.17 GB 47756673 rows
Job "LUNAR"."IMPDP_LUNAR_P201404_1" successfully completed at Thu Apr 23 00:12:55 2015 elapsed 0 00:07:02

^C
[1]+  Done                    nohup impdp LUNAR/passwd content=DATA_ONLY DIRECTORY=lunar JOB_NAME=impdp_LUNAR_P201404_1 parallel=1 TABLES=LUNAR.LUNAR DUMPFILE=LUNAR_P201404_01.dmp,LUNAR_P201404_02.dmp,LUNAR_P201404_03.dmp,LUNAR_P201404_04.dmp,LUNAR_P201404_05.dmp,LUNAR_P201404_06.dmp,LUNAR_P201404_07.dmp REMAP_TABLE=LUNAR.LUNAR:LUNAR_P201404_1 logfile=impdp_LUNAR_P201404_1.log
[oracle@dm01db01 lunar]$

===========================================
注意上面： elapsed 0 00:07:02 也就是说，7分钟导入数据309GB

发表在 POC和性能调整 | 标签为 , | 留下评论

配置Exadata上的万兆模块和测试万兆

发表于 2015 年 4 月 21 日 由 Lunar

Exadata上配置万兆非常简单，就是设置一下网卡，然后绑定即可
根据文档描述，万兆光口对应的接口如下：
Exadata Database Machine X5-2的eth4和eth5
不同的机器，可能接口名称不同，可以在机器上看，也可以核对文档来识别。
.
（1）Exadata上，有两个光口，是千兆万兆自适应的，但是10Gib模块需要单独购买。
（2）Exadata上，4个电口是百兆，千兆，万兆自适应的

1

下面的是一个例子：
例如，电口的eth0（用于连接内置的Cisco）：

[root@dm01db02 ~]# ethtool eth0
Settings for eth0:
        Supported ports: [ TP ]
        Supported link modes:   100baseT/Full 
                                1000baseT/Full 
                                10000baseT/Full 
        Supported pause frame use: No
        Supports auto-negotiation: Yes
        Advertised link modes:  100baseT/Full 
                                1000baseT/Full 
                                10000baseT/Full 
        Advertised pause frame use: No
        Advertised auto-negotiation: Yes
        Speed: 1000Mb/s
        Duplex: Full
        Port: Twisted Pair
        PHYAD: 0
        Transceiver: external
        Auto-negotiation: on
        MDI-X: Unknown
        Supports Wake-on: d
        Wake-on: d
        Current message level: 0x00000007 (7)
                               drv probe link
        Link detected: yes
[root@dm01db02 ~]#

连接了万兆的eth4：

[root@dm01db01 ~]# ethtool eth4
Settings for eth4:
        Supported ports: [ FIBRE ]
        Supported link modes:   10000baseT/Full 
        Supported pause frame use: No
        Supports auto-negotiation: No
        Advertised link modes:  10000baseT/Full 
        Advertised pause frame use: No
        Advertised auto-negotiation: No
        Speed: 10000Mb/s   ------------这里显示为万兆
        Duplex: Full   -----------全双工
        Port: Other
        PHYAD: 0
        Transceiver: external
        Auto-negotiation: off
        Supports Wake-on: d
        Wake-on: d
        Current message level: 0x00000007 (7)
                               drv probe link
        Link detected: yes  
[root@dm01db01 ~]#

然后设置一下IP，按照linux上绑定网卡的方法将eth4和eth5绑定即可。

万兆配置好了，测试了一下万兆的效率（还没时间做测试跨机房）：
下载了iperf安装在exadata上：

[root@dm01db01 tmp]# mkdir lunar
[root@dm01db01 tmp]# mv iperf-3.0.tar.gz lunar/
[root@dm01db01 tmp]# cd lunar/
[root@dm01db01 lunar]# ls
iperf-3.0.tar.gz
[root@dm01db01 lunar]# tar zxvf iperf-3.0.tar.gz 
。。。。
[root@dm01db01 lunar]# cd iperf
[root@dm01db01 iperf]# ./configure
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for gcc... gcc
checking for C compiler default output file name... a.out
..................
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
checking for ANSI C header files... (cached) yes
checking for library containing nanosleep... none required
checking for library containing hstrerror... none required
checking for an ANSI C-conforming const... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating src/Makefile
config.status: creating src/config.h
config.status: src/config.h is unchanged
config.status: executing depfiles commands
config.status: executing libtool commands
[root@dm01db01 iperf]# make && make install
Making all in src
make[1]: Entering directory `/tmp/lunar/iperf/src'
make  all-am
make[2]: Entering directory `/tmp/lunar/iperf/src'
gcc -DHAVE_CONFIG_H -I.     -g -O2 -MT cjson.o -MD -MP -MF .deps/cjson.Tpo -c -o cjson.o cjson.c
mv -f .deps/cjson.Tpo .deps/cjson.Po
gcc -DHAVE_CONFIG_H -I.     -g -O2 -MT iperf_api.o -MD -MP -MF .deps/iperf_api.Tpo -c -o iperf_api.o iperf_api.c
mv -f .deps/iperf_api.Tpo .deps/iperf_api.Po
gcc -DHAVE_CONFIG_H -I.     -g -O2 -MT iperf_error.o -MD -MP -MF .deps/iperf_error.Tpo -c -o iperf_error.o iperf_error.c
mv -f .deps/iperf_error.Tpo .deps/iperf_error.Po
gcc -DHAVE_CONFIG_H -I.     -g -O2 -MT iperf_client_api.o -MD -MP -MF .deps/iperf_client_api.Tpo -c -o iperf_client_api.o iperf_client_api.c
。。。。。。。。。。。。。。。。
 /usr/bin/install -c -m 644 'iperf_api.h' '/usr/local/include/iperf_api.h'
test -z "/usr/local/share/man/man1" || /bin/mkdir -p "/usr/local/share/man/man1"
 /usr/bin/install -c -m 644 './iperf3.1' '/usr/local/share/man/man1/iperf3.1'
test -z "/usr/local/share/man/man3" || /bin/mkdir -p "/usr/local/share/man/man3"
 /usr/bin/install -c -m 644 './libiperf.3' '/usr/local/share/man/man3/libiperf.3'
make[2]: Leaving directory `/tmp/lunar/iperf/src'
make[1]: Leaving directory `/tmp/lunar/iperf/src'
make[1]: Entering directory `/tmp/lunar/iperf'
make[2]: Entering directory `/tmp/lunar/iperf'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/lunar/iperf'
make[1]: Leaving directory `/tmp/lunar/iperf'
[root@dm01db01 iperf]# which iperf3
/usr/local/bin/iperf3
[root@dm01db01 iperf]#

测试了一下，每秒基本上可以达到7Gb(大概7Gb/8≈700MB/s)：

[root@dm01db01 modprobe.d]# iperf3 -c 10.10.10.12 -n 30G 
Connecting to host 10.10.10.12, port 5201
[  4] local 10.10.10.11 port 42491 connected to 10.10.10.12 port 5201
[ ID] Interval           Transfer     Bandwidth       Retransmits
[  4]   0.00-1.00   sec   619 MBytes  5.19 Gbits/sec   16         
[  4]   1.00-2.00   sec   696 MBytes  5.84 Gbits/sec   17         
[  4]   2.00-3.00   sec   680 MBytes  5.70 Gbits/sec   12         
[  4]   3.00-4.00   sec   746 MBytes  6.26 Gbits/sec    8         
[  4]   4.00-5.00   sec   741 MBytes  6.22 Gbits/sec    9         
[  4]   5.00-6.00   sec   750 MBytes  6.29 Gbits/sec    9         
[  4]   6.00-7.00   sec   783 MBytes  6.57 Gbits/sec    6         
[  4]   7.00-8.00   sec   838 MBytes  7.03 Gbits/sec    4         
[  4]   8.00-9.00   sec   838 MBytes  7.03 Gbits/sec    8         
[  4]   9.00-10.00  sec   838 MBytes  7.03 Gbits/sec    3         
[  4]  10.00-11.00  sec   827 MBytes  6.94 Gbits/sec    5         
[  4]  11.00-12.00  sec   898 MBytes  7.53 Gbits/sec    6         
[  4]  12.00-13.00  sec   905 MBytes  7.59 Gbits/sec    2         
[  4]  13.00-14.00  sec  1.01 GBytes  8.71 Gbits/sec    0         
[  4]  14.00-15.00  sec   967 MBytes  8.11 Gbits/sec    6         
[  4]  15.00-16.00  sec   933 MBytes  7.83 Gbits/sec    0         
[  4]  16.00-17.00  sec  1.01 GBytes  8.65 Gbits/sec    1         
[  4]  17.00-18.00  sec   913 MBytes  7.66 Gbits/sec    2         
[  4]  18.00-19.00  sec  1018 MBytes  8.54 Gbits/sec    1         
[  4]  19.00-20.00  sec   928 MBytes  7.79 Gbits/sec    1         
[  4]  20.00-21.00  sec  1.01 GBytes  8.66 Gbits/sec    2         
[  4]  21.00-22.00  sec  1.03 GBytes  8.81 Gbits/sec    0         
[  4]  22.00-23.00  sec  1.02 GBytes  8.73 Gbits/sec    3         
[  4]  23.00-24.00  sec  1.03 GBytes  8.82 Gbits/sec    0         
[  4]  24.00-25.00  sec   928 MBytes  7.78 Gbits/sec    3         
[  4]  25.00-26.00  sec   805 MBytes  6.75 Gbits/sec    6         
[  4]  26.00-27.00  sec   944 MBytes  7.92 Gbits/sec    2         
[  4]  27.00-28.00  sec   929 MBytes  7.79 Gbits/sec    1         
[  4]  28.00-29.00  sec  1.02 GBytes  8.74 Gbits/sec    0         
[  4]  29.00-30.00  sec  1020 MBytes  8.56 Gbits/sec    0         
[  4]  30.00-31.00  sec   926 MBytes  7.77 Gbits/sec    2         
[  4]  31.00-32.00  sec  1.02 GBytes  8.80 Gbits/sec    0         
[  4]  32.00-33.00  sec  1.01 GBytes  8.68 Gbits/sec    0         
[  4]  33.00-33.86  sec   879 MBytes  8.59 Gbits/sec    0         
- - - - - - - - - - - - - - - - - - - - - - - - -
[ ID] Interval           Transfer     Bandwidth       Retransmits
[  4]   0.00-33.86  sec  30.0 GBytes  7.61 Gbits/sec  135         sender
[  4]   0.00-33.86  sec  30.0 GBytes  7.61 Gbits/sec              receiver

iperf Done.
[root@dm01db01 modprobe.d]#
发表在 Database | 标签为 , , | 留下评论

Exadata上精彩的硬件告警（准确的说，应该是SUN ILOM的）

发表于 2015 年 4 月 21 日 由 Lunar

昨天Exadata上发出了一封靓丽的告警邮件，如下图：

1

2

3

没见过这么漂亮的邮件，清晰的告诉你是什么部件（可惜我还不认识那个大大的就是CPU……汗……）
又惊又喜的傻Lunar以为硬件坏了，高兴的是，SUN的ILOM的告警做的真棒啊
但是奇怪的是才用了没多久啊，而且比较爱惜的，怎么回事？
后来，在oracle官网搜索这个硬件, 发现是CPU边上的一个部件，貌似PCIe“故障”了。系统组的同事log了SR，等待回复。
晚上回家，按照以往的管理，我也到ILOM上搜索了一下，然后按照官方文档收集了ILOM的snapshot和一些必要信息传到SR上，SR的回复我没看懂：

8”

也就是说，33%的可能是是硬件损坏……
在oracle官网找到如下方法判断，下面的输出是ILOM 3.2以上的输出（老版本的输出本文后面有例子）

-> start /SP/faultmgmt/shell
Are you sure you want to start /SP/faultmgmt/shell (y/n)? y

faultmgmtsp> fmadm faulty
------------------- ------------------------------------ -------------- --------
Time                UUID                                 msgid          Severity
------------------- ------------------------------------ -------------- --------
2015-04-20/15:13:42 xxxxxxx-xxxxx-xxxx-xxx-xxxxxxxxx xxxX86A-xxxx-xxx Critical    

Problem Status    : open
Diag Engine       : fdd 1.0
System           
   Manufacturer   : Oracle Corporation
   Name           : Exadata X5-2        
   Part_Number    : Exadata X5-2


System Component 
   Manufacturer   : Oracle Corporation
   Name           : ORACLE SERVER X5-2


----------------------------------------
Suspect 1 of 3
   Fault class  : fault.io.intel.iio.pcie-fatal
   Certainty    : 33%
   Affects      : /SYS/MB/RISER2/PCIE2   ----------这个就是文档中说的那个拗口的“PCIe 竖隔板”，2表示槽位2
   Status       : faulted

   FRU                 
      Status            : faulty
      Location          : /SYS/MB/RISER2/PCIE2
      Chassis          
         Manufacturer   : Oracle Corporation
         Name           : ORACLE SERVER X5-2

----------------------------------------
Suspect 2 of 3
   Fault class  : fault.io.intel.iio.pcie-fatal
   Certainty    : 33%
   Affects      : /SYS/MB/P0   ------------这个表示是CPU
   Status       : faulted

   FRU                 
      Status            : faulty
      Location          : /SYS/MB/P0
      Name              : Intel(R) Xeon(R) CPU E5-2699 v3 @ 2.30GHz

      Chassis          
         Manufacturer   : Oracle Corporation
         Name           : ORACLE SERVER X5-2

----------------------------------------
Suspect 3 of 3
   Fault class  : fault.io.intel.iio.pcie-fatal
   Certainty    : 33%
   Affects      : /SYS/MB   -------------这个表示是主板
   Status       : faulted

   FRU                 
      Status            : faulty
      Location          : /SYS/MB
      Manufacturer      : MiTAC International Corporation
      Name              : ASM,MOTHERBOARD,1U

      Chassis          
         Manufacturer   : Oracle Corporation

Description : An integrated I/O (II0) fatal error in a downstream PCIE
              device has been detected.

Response    : The service-required LED on the chassis is illuminated.

Impact      : The server will reset, however the affected processor is not
              disabled to allow the host OS to boot up and operate in the
              presence of a faulty processor.

Action      : Please refer to the associated reference document at
              http://support.oracle.com/msg/SPX86A-8002-RK for the latest
              service procedures and policies regarding this diagnosis.

faultmgmtsp>

收集的信息，跟SR上老外的回复差不多，也是每个部件都有33%的可能性是硬件损坏……

8

按照以往的理解，猜测是“先兆损坏”，通常这种情况，工程师可能会把硬件进行一次插拔，如果没问题就算了，如果有问题，可能就是硬件损坏。
于是又到ILOM上检查，什么硬件，根据文档说明和ILOM的指示，大概知道是什么东西了（虽然每个东西长什么样子，我没见过……基本上从毕业后就没有拆过机箱，摸过螺丝的Lunar感觉很无奈……）：
.
/SYS/MB ————-这个表示是主板
/SYS/MB/P0 ————这个表示是CPU
/SYS/MB/RISER2/PCIE2 ———-这个就是文档中说的那个拗口的“PCIe 竖隔板”，2表示槽位2
.
找到硬件工程师问了一下，这个问题是因为我们插入万兆模块的时候，报的告警，不过奇怪的是为什么oracle文档中没有写明“更换万兆模块需要关机？”

5

6

行了，知道了不是硬件问题，clear即可，这个文档上有，照做即可：

-> start /SP/faultmgmt/shell
Are you sure you want to start /SP/faultmgmt/shell (y/n)? y

faultmgmtsp> fmadm repair xxxxxxxx-xxxx-xxxxxx-xxxxxxxxx
faultmgmtsp> fmadm repair /SYS/MB
faultmgmtsp>

检查清除结果：

faultmgmtsp> fmadm faulty
No faults found
faultmgmtsp>

9

顺便介绍一下老版本的ILOM输出（ILOM 3.2以前）：
—举例1： 输出类似 FRU : /SYS/FANBD/FM0，这个就指出了损坏的FRU是具体对应到主机上的哪一个物理组件（full physical path）
—“/SYS” 底架（chassis），我理解可能是主机架，就是拆开挡板后，所有东西都插在上面的那个主机箱的含义。
—“FANBD” 风扇模块 （fan board）
—“FM0” 具体哪一个风扇模块（Fan Module）
.
—举例2： 输出类似 FRU : /SYS/MB，这个就指出了损坏的FRU是具体对应到主机上的哪一个物理组件（full physical path）
—“/SYS” 底架（chassis），我理解可能是主机架，就是拆开挡板后，所有东西都插在上面的那个主机箱的含义。
—‘/MB’ 表示主板（ Mother Board）

发表在 Database | 标签为 , , , , | 留下评论

[FAQ]-使用duplicate克隆数据库

发表于 2015 年 4 月 16 日 由 Lunar

帮一个朋友整理的，顺便发到blog。
.
使用duplicate克隆数据库： 将宝宝数据库科隆为Lunar数据库
这部分东西不是新东西，clone在8i就是可以手工做的，在9i还是10g（忘记了），就被封装成duplicat命令了。
之前的测试参见无所不能的duplicate–克隆数据库
.
不过一般我更喜欢手工用rman做，每一步都透明，每一步都可控可回退可追查可修改。
duplicate自动化了，但是出问题后就是整体全部重来，万一有问题，比较耽误时间。
具体步骤如下：
在备库创建目录：

mkdir -p /u01/app/oracle/admin/lunar/adump
mkdir -p /u01/app/oracle/admin/lunar/bdump
mkdir -p /u01/app/oracle/admin/lunar/cdump
mkdir -p /u01/app/oracle/admin/lunar/udump
mkdir -p /u01/app/oracle/oradata/lunar
mkdir -p /u01/app/oracle/diag/rdbms/lunar/lunar
mkdir -p /u01/app/oracle/oradata/lunar

宝宝数据库的参数文件：

*.audit_file_dest='/u01/app/oracle/admin/lunar/adump'
*.audit_trail='none'
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/lunar/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='bb'
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=121634816
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=529145600
*.undo_tablespace='UNDOTBS1'
*.job_queue_processes=10
*.db_unique_name='bb'
*.log_archive_dest_1='location=/u01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lunar'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_max_processes=5

Lunar数据库使用的参数文件：

*.audit_file_dest='/u01/app/oracle/admin/lunar/adump'
*.audit_trail='none'
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/lunar/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='lunar'
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=121634816
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=529145600
*.undo_tablespace='UNDOTBS1'
*.job_queue_processes=10
*.db_unique_name='lunar'
*.log_archive_dest_1='location=/u01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lunar'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_max_processes=5

创建监听：

[oracle@lunar admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.66)(PORT = 1521))
    )
  )
  
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = bb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1)
      (SID_NAME = bb)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = lunar)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1)
      (SID_NAME = lunar)
    )
   )
  )

  

  
ADR_BASE_LISTENER = /u01/app/oracle

创建tnsnames.ora文件

[oracle@lunar admin]$ 

bb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.66)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bb)
      (SID_NAME = bb)
    )
  )

lunar =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.99)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lunar)
      (SID_NAME = lunar)
    )
  )

lunar =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.66)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lunar)
      (SID_NAME = lunar)
    )
  )

重启监听
测试连接：


[oracle@lunar lunardb]$ . ~/lunar.env 
[oracle@lunar lunardb]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 28 00:53:25 2013

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

Connected to an idle instance.

SQL> startup nomount pfile=/test/lunardb/pfilelunar.ora
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2230024 bytes
Variable Size             163580152 bytes
Database Buffers          356515840 bytes
Redo Buffers                7962624 bytes
SQL> 

[oracle@lunar ~]$ . bb.env 
[oracle@lunar ~]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 28 01:03:35 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect sys/oracle@bb as sysdba
Connected.
SQL> connect sys/oracle@lunar as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

创建口令文件：


[oracle@lunar admin]$ cd $ORACLE_HOME/dbs
[oracle@lunar dbs]$ ls
10o0goff_1_1  11o0gogi_1_1  hc_bb.dat  hc_lunar.dat  init.ora  lkBB  orapwbb  snapcf_bb.f  spfilebb.ora
[oracle@lunar dbs]$ cp orapwbb orapwlunar
[oracle@lunar dbs]$

开始duplicate：

[oracle@lunar lunar]$ . ~/lunar.env 
[oracle@lunar lunar]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 28 01:36:58 2013

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

Connected to an idle instance.

SQL> startup nomount pfile=/test/lunardb/pfilelunar.ora
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2230024 bytes
Variable Size             163580152 bytes
Database Buffers          356515840 bytes
Redo Buffers                7962624 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lunar lunar]$ rman target sys/oracle@bb auxiliary sys/oracle@lunar

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 28 01:37:25 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BB (DBID=2078073270)
connected to auxiliary database: lunar (not mounted)

RMAN> run{
2> set newname for datafile 1 to '/test/lunardb/SYSTEM01.DBF';
3> set newname for datafile 2 to '/test/lunardb/undotbs01.DBF';
4> set newname for datafile 3 to '/test/lunardb/sysaux01.DBF';
5> set newname for datafile 4 to '/test/lunardb/users01.DBF';
6> set newname for datafile 5 to '/test/lunardb/temp01.DBF';
7> duplicate target database to lunar nofilenamecheck logfile
8> '/test/lunardb/redo01.log' size 10m,
9> '/test/lunardb/redo02.log' size 10m,
10> '/test/lunardb/redo03.log' size 10m;
11> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 28-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     530288640 bytes

Fixed Size                     2230024 bytes
Variable Size                163580152 bytes
Database Buffers             356515840 bytes
Redo Buffers                   7962624 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''BB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''lunar'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''BB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''lunar'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     530288640 bytes

Fixed Size                     2230024 bytes
Variable Size                163580152 bytes
Database Buffers             356515840 bytes
Redo Buffers                   7962624 bytes

Starting restore at 28-JAN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/11o0gogi_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/11o0gogi_1_1 tag=TAG20130128T004847
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/lunar/control01.ctl
Finished restore at 28-JAN-13

database mounted

contents of Memory Script:
{
   set until scn  330514;
   set newname for datafile  1 to 
 "/test/lunardb/SYSTEM01.DBF";
   set newname for datafile  2 to 
 "/test/lunardb/undotbs01.DBF";
   set newname for datafile  3 to 
 "/test/lunardb/sysaux01.DBF";
   set newname for datafile  4 to 
 "/test/lunardb/users01.DBF";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-JAN-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /test/lunardb/SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to /test/lunardb/undotbs01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to /test/lunardb/sysaux01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to /test/lunardb/users01.DBF
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/10o0goff_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/10o0goff_1_1 tag=TAG20130128T004847
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 28-JAN-13

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=805858730 file name=/test/lunardb/SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=805858730 file name=/test/lunardb/undotbs01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=805858730 file name=/test/lunardb/sysaux01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=805858730 file name=/test/lunardb/users01.DBF

contents of Memory Script:
{
   set until scn  330514;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 28-JAN-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 43 is already on disk as file /u01/archive/1_43_805707062.dbf
archived log file name=/u01/archive/1_43_805707062.dbf thread=1 sequence=43
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-JAN-13
Oracle instance started

Total System Global Area     530288640 bytes

Fixed Size                     2230024 bytes
Variable Size                163580152 bytes
Database Buffers             356515840 bytes
Redo Buffers                   7962624 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''lunar'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''lunar'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     530288640 bytes

Fixed Size                     2230024 bytes
Variable Size                163580152 bytes
Database Buffers             356515840 bytes
Redo Buffers                   7962624 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "lunar" RESETLOGS ARCHIVELOG 
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 '/test/lunardb/redo01.log' SIZE 10 M ,
  GROUP   2 '/test/lunardb/redo02.log' SIZE 10 M ,
  GROUP   3 '/test/lunardb/redo03.log' SIZE 10 M 
 DATAFILE
  '/test/lunardb/SYSTEM01.DBF'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/bb/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/test/lunardb/undotbs01.DBF", 
 "/test/lunardb/sysaux01.DBF", 
 "/test/lunardb/users01.DBF";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/bb/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/test/lunardb/undotbs01.DBF RECID=1 STAMP=805858744
cataloged datafile copy
datafile copy file name=/test/lunardb/sysaux01.DBF RECID=2 STAMP=805858744
cataloged datafile copy
datafile copy file name=/test/lunardb/users01.DBF RECID=3 STAMP=805858744

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=805858744 file name=/test/lunardb/undotbs01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=805858744 file name=/test/lunardb/sysaux01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=805858744 file name=/test/lunardb/users01.DBF
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data
Executing: alter database force logging

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 28-JAN-13

RMAN>

克隆完成，创建临时文件：

[oracle@lunar lunar]$ . ~/lunar.env 
[oracle@lunar lunar]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 28 01:40:32 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select NAME,OPEN_MODE from v$database;

NAME               OPEN_MODE
------------------ ----------------------------------------
lunar             READ WRITE

SQL> 
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/bb/temp01.dbf' DROP  INCLUDING DATAFILES;

Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/test/lunardb/temp01.DBF' SIZE 10M;

Tablespace altered.

SQL>
发表在 backup&recovery | 标签为 , | 一条评论

[FAQ]-使用rman备份做同机克隆和异机恢复

发表于 2015 年 4 月 16 日 由 Lunar

帮一个朋友整理的，顺便发到blog。
.
数据库克隆操作文档
一．前提说明:
1. 本测试是在同一台机器上完成，而在同一台机器中ORACLE_SID不可相同，所以涉及到克隆库修改ORACLE_SID问题
2. 为了使得本次操作能够更好的让客户了解其过程，使用了传统的rman备份来实现,而没有使用duplicate相关命令实现
3. 如果在不同机器上使用rman备份做异机恢复，就不用修改SID，保持跟以前一样就可以（步骤差不多，比同机克隆更简单）
.
二．整体思路:
1. 关闭lunar
2，使用pfile.lunar.bak（DB_NAME=lunar）nomount database
3，恢复控制文件
4，恢复数据文件（SET NEWNAME）
5，shutdown abort（lunar）
6，export ORACLE_SID=lunar
7，使用pfile.lunar.bak（DB_NAME=lunar）nomount database
8，重建控制文件（SET DATABASE “lunar”）
9，catalog start with
10，recover database
11，alter database open resetlogs
.
三．详细步骤
1，备份数据库lunar

rman target /
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
run{
CONFIGURE DEVICE TYPE DISK PARALLELISM 12 BACKUP TYPE TO BACKUPSET;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/lunartest/databasefiles_%d_%U_%s' 
plus archivelog FORMAT '/lunartest/archivelogs_%d_%U_%s_%T' delete input;
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT '/lunartest/controlfile_%d_%U_%s';
}

2，创建lunar的spfile

[oracle@gg lunartest]$ cat pfile.lunar.bak
*.aq_tm_processes=0
*.audit_file_dest='/home/oracle/product/admin/lunar/adump'
*.background_dump_dest='/home/oracle/product/admin/lunar/bdump'
*.compatible='11.2.0.2.0'
*.control_files='/home/oracle/product/oradata/lunar/controlfile/o1_mf_3sfb1t6n_.ctl'
*.core_dump_dest='/home/oracle/product/admin/lunar/cdump'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/product/oradata'
*.db_domain='oracle.com'
*.db_file_multiblock_read_count=8
*.db_name='lunar'
*.db_recovery_file_dest='/home/oracle/product/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=0
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/product/admin/lunar/udump'
#db_name = "lunar"
#instance_name = lunar
#service_names = lunar

3，关闭lunar实例

[oracle@com lunartest]$ ps -ef|grep pmon
oracle    5070     1  0 13:14 ?        00:00:03 ora_pmon_lunar
oracle    5706  5656  0 22:18 pts/2    00:00:00 grep pmon
[oracle@com lunartest]$ export ORACLE_SID=lunar
[oracle@com lunartest]$
[oracle@com lunartest]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Sep 23 22:19:24 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

4，使用pfiel.lunar.ora把lunar实例nomount

mkdir -p /home/oracle/oracle/product/admin/lunar/adump
mkdir -p /home/oracle/oracle/product/admin/lunar/bdump
mkdir -p /home/oracle/oracle/product/oradata/lunar/controlfile/o1_mf_3sfb1t6n_.ctl
mkdir -p /home/oracle/oracle/product/admin/lunar/cdump
mkdir -p /home/oracle/oracle/product/oradata
mkdir -p /home/oracle/oracle/product/flash_recovery_area
mkdir -p /home/oracle/oracle/product/admin/lunar/udump

nomount数据库：


[oracle@com lunartest]$ ss

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Sep 23 22:36:22 2012

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

Connected to an idle instance.

SQL> startup nomount pfile=pfile.lunar.ora
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@com lunartest]$

5， 恢复控制文件

run{
allocate channel c1 type disk;
restore controlfile from '/lunartest/c-1172535738-20120921-01';
release channel c1;
}

6， 恢复数据文件

alter database mount;
run
{
allocate channel c1 type disk;
set newname for datafile 1 to '+DATA_DM01' ;
set newname for datafile 2 to '+DATA_DM01' ;
set newname for datafile 3 to '/home/oracle/product/oradata/lunar/datafile/sysaux01.dbf' ;
set newname for datafile 4 to '/home/oracle/product/oradata/lunar/datafile/users01.dbf' ;
set newname for datafile 5 to '/home/oracle/product/oradata/lunar/datafile/example01.dbf' ;
set newname for datafile 6 to '/home/oracle/product/oradata/lunar/datafile/inventory01.dbf' ;
set newname for datafile 7 to '/home/oracle/product/oradata/lunar/datafile/odi_default.dbf' ;
set newname for datafile 8 to '/home/oracle/product/oradata/lunar/datafile/test1-01.dbf' ;
set newname for datafile 9 to '/home/oracle/product/oradata/lunar/datafile/system1.dbf' ;
set newname for datafile 10 to '/home/oracle/product/oradata/lunar/datafile/rman01.dbf' ;
restore database;
switch datafile all;
release channel c1;
}

7， 修改pfile文件（这一步只有同机克隆采用，异机恢复不用）

[oracle@gg lunartest]$ cat pfile.lunar.bak
*.aq_tm_processes=0
*.audit_file_dest='/home/oracle/product/admin/lunar/adump'
*.background_dump_dest='/home/oracle/product/admin/lunar/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/product/oradata/lunar/controlfile/o1_mf_3sfb1t6n_.ctl'
*.core_dump_dest='/home/oracle/product/admin/lunar/cdump'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/product/oradata'
*.db_domain='oracle.com'
*.db_file_multiblock_read_count=8
*.db_name='lunar'
*.db_recovery_file_dest='/home/oracle/product/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=0
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/product/admin/lunar/udump'
db_name = "lunar"
instance_name = lunar
service_names = lunar

8， 重建控制文件

CREATE CONTROLFILE SET DATABASE "lunar" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/home/oracle/product/oradata/lunar/datafile/redo01.log'
  ) SIZE 50M,
  GROUP 2 (
   '/home/oracle/product/oradata/lunar/datafile/redo02.log'
  ) SIZE 50M,
  GROUP 3 (
  '/home/oracle/product/oradata/lunar/datafile/redo03.log'
  ) SIZE 50M
DATAFILE
  '/home/oracle/product/oradata/lunar/datafile/system01.dbf',
  '/home/oracle/product/oradata/lunar/datafile/undotbs01.dbf',
  '/home/oracle/product/oradata/lunar/datafile/sysaux01.dbf',
  '/home/oracle/product/oradata/lunar/datafile/users01.dbf',
  '/home/oracle/product/oradata/lunar/datafile/example01.dbf',
  '/home/oracle/product/oradata/lunar/datafile/inventory01.dbf',
  '/home/oracle/product/oradata/lunar/datafile/odi_default.dbf',
  '/home/oracle/product/oradata/lunar/datafile/test1-01.dbf',
  '/home/oracle/product/oradata/lunar/datafile/system1.dbf',
  '/home/oracle/product/oradata/lunar/datafile/rman01.dbf'
CHARACTER SET US7ASCII
;

9， 应用归档日志
catalog start with ‘/lunartest/’;
recover database;

10， 打开数据库
alter database open resetlogs;

11， 添加临时文件
alter tablespace temp add tempfile ‘/home/oracle/product/oradata/lunar/datafile/temp01.dbf’ size 10m autoextend on next 10m maxsize 10g;

发表在 backup&recovery | 标签为 , | 留下评论

OGG DDL trigger造成升级数据库后大量SYS对象失效和数据库DDL失效

发表于 2015 年 4 月 16 日 由 Lunar

OGG的DDL功能真是坑，今天在测试库尝试升级，被OGG的DDL给坑了（以前没摸过这个库，不知道有这个东西……）
以后，做任何大操作以前，一定要仔细检查数据库，是否曾经开启过OGG的DDL功能。
这个功能有很多隐患：
因为OGG的DDL是建立在SYS用户下面的， 与其他普通用户的trigger不同：
1，数据库文件offline后，如果有OGG的DDL曾经被开启，可能因此打不开数据库（先disable ogg ddl才行）
2，升级过程中，如果没有disable ddl trigger，可能出现一些升级后的异常
3，OGG用户删除后，并不能删除OGG的DDL trigger，因为他的属主是SYS，且属于internal trigger，会导致大量SYS用户的对象失效和DDL功能失效。
。。。。

升级数据库，从11.2.0.1到11.2.0.4，升级过程中，没有报错，但是数据库启动后，报错：

Errors in file /u01/oracle/app/diag/rdbms/gpsdb3/gpsdb3/trace/gpsdb3_ora_19939.trc:
ORA-04045: errors during recompilation/revalidation of SYS.OLAPISTARTUPTRIGGER
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06512: at line 904
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLR
Completed: ALTER DATABASE OPEN

尝试删除OGG用户，报错：

SQL> drop user GGUSR;      
drop user GGUSR
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 904
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"


SQL>

检查报错的OLAP过程，为INVALID状态：

SQL> select owner, object_type, status from all_objects where object_name='OLAPIHISTORYRETENTION';

OWNER                          OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
SYS                            PROCEDURE           INVALID

SQL>

但是升级后，检查所有数据库组件是VALID状态：

SQL> set pages 9999
SQL> set linesize 1000
SQL> col comp_id for a14
SQL> col comp_name for a36
SQL> col version for a14
SQL> select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;

COMP_ID        COMP_NAME                            VERSION        STATUS
-------------- ------------------------------------ -------------- ----------------------
OWB            OWB                                  11.2.0.1.0     VALID
APEX           Oracle Application Express           3.2.1.00.10    VALID
AMD            OLAP Catalog                         11.2.0.4.0     VALID
SDO            Spatial                              11.2.0.4.0     VALID
ORDIM          Oracle Multimedia                    11.2.0.4.0     VALID
XDB            Oracle XML Database                  11.2.0.4.0     VALID
CONTEXT        Oracle Text                          11.2.0.4.0     VALID
EXF            Oracle Expression Filter             11.2.0.4.0     VALID
RUL            Oracle Rules Manager                 11.2.0.4.0     VALID
OWM            Oracle Workspace Manager             11.2.0.4.0     VALID
CATALOG        Oracle Database Catalog Views        11.2.0.4.0     VALID
CATPROC        Oracle Database Packages and Types   11.2.0.4.0     VALID
JAVAVM         JServer JAVA Virtual Machine         11.2.0.4.0     VALID
XML            Oracle XDK                           11.2.0.4.0     VALID
CATJAVA        Oracle Database Java Packages        11.2.0.4.0     VALID
APS            OLAP Analytic Workspace              11.2.0.4.0     VALID
XOQ            Oracle OLAP API                      11.2.0.4.0     VALID

17 rows selected.

SQL>

理论上，升级后，组件都是正常的，为什么出现这种情况？

SQL> select owner, trigger_name, status from dba_triggers where trigger_name like '%OLAPI%';

OWNER                          TRIGGER_NAME                   STATUS
------------------------------ ------------------------------ --------
SYS                            OLAPISTARTUPTRIGGER            ENABLED
SYS                            OLAPISHUTDOWNTRIGGER           ENABLED

SQL>

尝试disable 这两个trigger，也报错：

SQL> ALTER TRIGGER SYS.OLAPISTARTUPTRIGGER DISABLE;
ALTER TRIGGER SYS.OLAPISTARTUPTRIGGER DISABLE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06512: at line 904
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called: "GGUSR.DDLREPLICATION"


SQL>

貌似所有的DDL都失效了：

SQL> create user lunar identified by lunar;
create user lunar identified by lunar
                                *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 904
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"


SQL>

再一查SYS的无效对象，800多个：

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
       814

SQL>

使用utlrp编译，也报错：

SQL> @?/rdbms/admin/utlrp

COMP_TIMESTAMP UTLRP_BGN  2015-04-16 00:36:07

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
DECLARE
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 904
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4



COMP_TIMESTAMP UTLRP_END  2015-04-16 00:36:07

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

                          0

CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 904
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"


DECLARE
*
ERROR at line 1:
ORA-00904: "LOCAL_ENQUOTE_NAME": invalid identifier
ORA-06512: at line 27


DROP function local_enquote_name
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 904
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-04045: errors during recompilation/revalidation of GGUSR.DDLREPLICATION
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06512: at line 1027
ORA-04067: not executed, package body "GGUSR.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGUSR.DDLREPLICATION"


FAILED CHECK FOR INDEXTYPE CONTEXT
Warning: XDB now invalid, could not find xdbconfig

PL/SQL procedure successfully completed.

SQL>

最后一行提示XDB组件有问题:

SQL> SELECT substrb(Comp_ID,1,10)Comp_ID,
  2  substrb(Status,1,8)Status,
  3  substrb(Version,1,12)Version,
  4  substrb(Comp_Name,1,35)Comp_Name
  5  FROM DBA_Registry
  6  WHERE comp_id = 'XDB';

COMP_ID    STATUS   VERSION      COMP_NAME
---------- -------- ------------ -----------------------------------
XDB        INVALID  11.2.0.4.0   Oracle XML Database

SQL>

diable OGG的trigger，然后删除ogg用户后，再次使用utlrp编译，已经正常了：

SQL> @?/rdbms/admin/utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2015-04-16 01:03:22

1 row selected.

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2015-04-16 01:04:47

1 row selected.

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

1 row selected.

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

1 row selected.


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>

且数据库已经没有无效对象：

SQL>  set heading off                                                                                                                        
SQL>  spool compileinvalid.sql                                                                                                               
SQL>  select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID';                 

no rows selected

SQL> 
SQL> 
SQL>

再次校验XDB的组件，正常了：

SQL> execute dbms_regxdb.validatexdb;

PL/SQL procedure successfully completed.

SQL> SELECT substrb(Comp_ID,1,10)Comp_ID,
substrb(Status,1,8)Status,
substrb(Version,1,12)Version,  2    3  
  4  substrb(Comp_Name,1,35)Comp_Name
FROM DBA_Registry  5  
  6  WHERE comp_id = 'XDB';

COMP_ID              STATUS          VERSION    COMP_NAME
-------------------- --------------- ---------- ----------------------------------------
XDB                  VALID           11.2.0.4.0 Oracle XML Database

SQL> alter trigger MDSYS.SDO_GEOR_ADDL_TRIGGER enable;
alter trigger MDSYS.SDO_GEOR_BDDL_TRIGGER enable;
Trigger altered.

SQL> 

Trigger altered.

SQL>

再次查询组建状态，都ok：

SQL> set pages 9999
SQL> set linesize 1000
SQL> col comp_id for a14
SQL> col comp_name for a36
SQL> col version for a14
SQL> select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;

COMP_ID        COMP_NAME                            VERSION        STATUS
-------------- ------------------------------------ -------------- ----------------------
OWB            OWB                                  11.2.0.1.0     VALID
APEX           Oracle Application Express           3.2.1.00.10    VALID
AMD            OLAP Catalog                         11.2.0.4.0     VALID
SDO            Spatial                              11.2.0.4.0     VALID
ORDIM          Oracle Multimedia                    11.2.0.4.0     VALID
XDB            Oracle XML Database                  11.2.0.4.0     VALID
CONTEXT        Oracle Text                          11.2.0.4.0     VALID
EXF            Oracle Expression Filter             11.2.0.4.0     VALID
RUL            Oracle Rules Manager                 11.2.0.4.0     VALID
OWM            Oracle Workspace Manager             11.2.0.4.0     VALID
CATALOG        Oracle Database Catalog Views        11.2.0.4.0     VALID
CATPROC        Oracle Database Packages and Types   11.2.0.4.0     VALID
JAVAVM         JServer JAVA Virtual Machine         11.2.0.4.0     VALID
XML            Oracle XDK                           11.2.0.4.0     VALID
CATJAVA        Oracle Database Java Packages        11.2.0.4.0     VALID
APS            OLAP Analytic Workspace              11.2.0.4.0     VALID
XOQ            Oracle OLAP API                      11.2.0.4.0     VALID

17 rows selected.

SQL>
发表在 OGG | 标签为 , , | 留下评论

ADG备库由于控制文件，归档损坏等原因，不能switchover，failover和active standby database时

发表于 2015 年 4 月 15 日 由 Lunar

昨天损坏的备库，今天准备激活，然后做升级测试。
备库由于归档损坏，已经不能用常规手段switchover和failover，甚至直接激活也不能：

[oracle@lunardb3 trace]$ ss

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 15 17:25:08 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

17:25:08 SYS@ lunardb3> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active


17:27:11 SYS@ lunardb3>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2048 change 6194107977662 time 04/13/2015 12:28:54
ORA-00334: archived log: '/u01/oradata/1_775046_804791836.arc'


Elapsed: 00:00:06.26
17:27:31 SYS@ lunardb3> alter database recover managed standby database finish skip standby logfile;
alter database recover managed standby database finish skip standby logfile
*
ERROR at line 1:
ORA-00274: illegal recovery option SKIP STANDBY LOGFILE is an invalid option on RECOVER FINISH


Elapsed: 00:00:00.00
17:27:59 SYS@ lunardb3> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Elapsed: 00:00:17.19
17:28:34 SYS@ lunardb3> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required


Elapsed: 00:00:00.01
17:28:37 SYS@ lunardb3> recover standby database until cancel;
ORA-00279: change 6194107977643 generated at 04/13/2015 12:28:54 needed for thread 1
ORA-00289: suggestion : /u01/oradata/arch/1_775046_804791836.arc
ORA-00280: change 6194107977643 for thread 1 is in sequence #775046


17:29:22 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oradata/lunar/system01.dbf'


17:29:37 SYS@ lunardb3> recover standby database until cancel;
ORA-00279: change 6194107977643 generated at 04/13/2015 12:28:54 needed for thread 1
ORA-00289: suggestion : /u01/oradata/arch/1_775046_804791836.arc
ORA-00280: change 6194107977643 for thread 1 is in sequence #775046


17:29:45 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2048 change 6194107977662 time 04/13/2015 12:28:54
ORA-00334: archived log: '/u01/oradata/arch/1_775046_804791836.arc'


ORA-01112: media recovery not started


17:29:48 SYS@ lunardb3> alter database activate standby database;
alter database activate standby database
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oradata/lunar/system01.dbf'


Elapsed: 00:00:02.14
17:30:00 SYS@ lunardb3>

重启数据库后，重建控制文件，然后open resetlogs：

17:30:00 SYS@ lunardb3> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
17:32:48 SYS@ lunardb3> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size                  2220200 bytes
Variable Size            2281705304 bytes
Database Buffers         1.4764E+10 bytes
Redo Buffers               55287808 bytes
17:32:57 SYS@ lunardb3> CREATE CONTROLFILE REUSE DATABASE "lunar" RESETLOGS FORCE LOGGING ARCHIVELOG
17:33:16   2      MAXLOGFILES 80
17:33:16   3      MAXLOGMEMBERS 3
17:33:16   4      MAXDATAFILES 2048
17:33:16   5      MAXINSTANCES 8
17:33:17   6      MAXLOGHISTORY 18688
17:33:17   7  LOGFILE
17:33:17   8    GROUP 1 '/u01/oradata/lunar/redo01.log'  SIZE 400M BLOCKSIZE 512,
17:33:17   9    GROUP 2 '/u01/oradata/lunar/redo02.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  10    GROUP 3 '/u01/oradata/lunar/redo03.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  11    GROUP 4 '/u01/oradata/lunar/redo04.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  12    GROUP 5 '/u01/oradata/lunar/redo05.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  13    GROUP 6 '/u01/oradata/lunar/redo06.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  14    GROUP 7 '/u01/oradata/lunar/redo07.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  15    GROUP 8 '/u01/oradata/lunar/redo08.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  16    GROUP 50 '/u01/oradata/lunar/redo50.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  17    GROUP 51 '/u01/oradata/lunar/redo51.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  18    GROUP 52 '/u01/oradata/lunar/redo52.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  19    GROUP 53 '/u01/oradata/lunar/redo53.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  20    GROUP 54 '/u01/oradata/lunar/redo54.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  21    GROUP 55 '/u01/oradata/lunar/redo55.log'  SIZE 400M BLOCKSIZE 512,
17:33:17  22    GROUP 56 '/u01/oradata/lunar/redo56.log'  SIZE 400M BLOCKSIZE 512,
17:33:18  23    GROUP 57 '/u01/oradata/lunar/redo57.log'  SIZE 400M BLOCKSIZE 512
17:33:18  24  -- STANDBY LOGFILE
17:33:18  25  --   GROUP 9 '/u01/oradata/lunar/stdb_redo01.log'  SIZE 400M BLOCKSIZE 512,
17:33:18  26  --   GROUP 10 '/u01/oradata/lunar/stdb_redo02.log'  SIZE 400M BLOCKSIZE 512,
17:33:18  27  --   GROUP 11 '/u01/oradata/lunar/stdb_redo03.log'  SIZE 400M BLOCKSIZE 512,
17:33:18  28  --   GROUP 12 '/u01/oradata/lunar/stdb_redo04.log'  SIZE 400M BLOCKSIZE 512,
17:33:18  29  --   GROUP 13 '/u01/oradata/lunar/stdb_redo05.log'  SIZE 400M BLOCKSIZE 512,
17:33:18  30  --   GROUP 14 '/u01/oradata/lunar/stdb_redo06.log'  SIZE 400M BLOCKSIZE 512,
17:33:18  31  --   GROUP 15 '/u01/oradata/lunar/stdb_redo07.log'  SIZE 400M BLOCKSIZE 512
17:33:18  32  DATAFILE
17:33:18  33    '/u01/oradata/lunar/system01.dbf',
17:33:18  34    '/u01/oradata/lunar/sysaux01.dbf',
17:33:18  35    '/u01/oradata/lunar/undotbs01.dbf',
17:33:18  36    '/u01/oradata/lunar/users01.dbf',
17:33:18  37    '/u01/oradata/lunar/undotbs02.dbf',
17:33:18  38    '/u01/oradata/lunar/undotbs03.dbf',
17:33:18  39    '/u01/oradata/lunar/undotbs04.dbf',
17:33:19  40    '/u01/oradata/lunar/lunar_dat_01.dbf',
17:33:19  41    '/u01/oradata/lunar/lunar_idx_01.dbf',
17:33:19  42    '/u01/oradata/lunar/lunar_dat_02.dbf',
17:33:19  43    '/u01/oradata/lunar/lunar_dat_03.dbf',
17:33:19  44    '/u01/oradata/lunar/lunar_dat_04.dbf',
17:33:19  45    '/u01/oradata/lunar/lunar_dat_05.dbf',
17:33:19  46    '/u01/oradata/lunar/lunar_dat_06.dbf',
17:33:19  47    '/u01/oradata/lunar/lunar_dat_07.dbf',
17:33:19  48    '/u01/oradata/lunar/lunar_dat_08.dbf',
17:33:19  49    '/u01/oradata/lunar/lunar_dat_09.dbf',
17:33:19  50    '/u01/oradata/lunar/lunar_dat_10.dbf',
17:33:19  51    '/u01/oradata/lunar/lunar_dat_11.dbf',
17:33:19  52    '/u01/oradata/lunar/lunar_dat_12.dbf',
17:33:19  53    '/u01/oradata/lunar/lunar_dat_13.dbf',
17:33:19  54    '/u01/oradata/lunar/lunar_dat_14.dbf',
17:33:19  55    '/u01/oradata/lunar/lunar_dat_15.dbf',
17:33:19  56    '/u01/oradata/lunar/lunar_dat_16.dbf',
17:33:20  57    '/u01/oradata/lunar/lunar_dat_17.dbf',
17:33:20  58    '/u01/oradata/lunar/lunar_dat_18.dbf',
17:33:20  59    '/u01/oradata/lunar/lunar_dat_19.dbf',
17:33:20  60    '/u01/oradata/lunar/lunar_dat_20.dbf',
17:33:20  61    '/u01/oradata/lunar/lunar_dat_21.dbf',
17:33:20  62    '/u01/oradata/lunar/lunar_dat_22.dbf',
17:33:20  63    '/u01/oradata/lunar/lunar_dat_23.dbf',
17:33:20  64    '/u01/oradata/lunar/lunar_dat_24.dbf',
17:33:20  65    '/u01/oradata/lunar/lunar_dat_25.dbf',
17:33:20  66    '/u01/oradata/lunar/lunar_dat_26.dbf',
17:33:20  67    '/u01/oradata/lunar/lunar_dat_27.dbf',
17:33:20  68    '/u01/oradata/lunar/lunar_dat_28.dbf',
17:33:20  69    '/u01/oradata/lunar/lunar_dat_29.dbf',
17:33:20  70    '/u01/oradata/lunar/lunar_dat_30.dbf',
17:33:20  71    '/u01/oradata/lunar/lunar_dat_31.dbf',
17:33:20  72    '/u01/oradata/lunar/lunar_dat_32.dbf',
17:33:20  73    '/u01/oradata/lunar/lunar_dat_33.dbf',
17:33:21  74    '/u01/oradata/lunar/lunar_dat_34.dbf',
17:33:21  75    '/u01/oradata/lunar/lunar_dat_35.dbf',
17:33:21  76    '/u01/oradata/lunar/lunar_dat_36.dbf',
17:33:21  77    '/u01/oradata/lunar/lunar_dat_37.dbf',
17:33:21  78    '/u01/oradata/lunar/lunar_dat_38.dbf',
17:33:21  79    '/u01/oradata/lunar/lunar_dat_39.dbf',
17:33:21  80    '/u01/oradata/lunar/lunar_dat_40.dbf',
17:33:21  81    '/u01/oradata/lunar/lunar_dat_41.dbf',
17:33:21  82    '/u01/oradata/lunar/lunar_dat_42.dbf',
17:33:21  83    '/u01/oradata/lunar/lunar_dat_43.dbf',
17:33:21  84    '/u01/oradata/lunar/lunar_dat_44.dbf',
17:33:21  85    '/u01/oradata/lunar/lunar_dat_45.dbf',
17:33:21  86    '/u01/oradata/lunar/lunar_dat_46.dbf',
17:33:21  87    '/u01/oradata/lunar/lunar_dat_47.dbf',
17:33:21  88    '/u01/oradata/lunar/lunar_dat_48.dbf',
17:33:21  89    '/u01/oradata/lunar/lunar_dat_49.dbf',
17:33:21  90    '/u01/oradata/lunar/lunar_dat_50.dbf',
17:33:22  91    '/u01/oradata/lunar/lunar_dat_51.dbf',
17:33:22  92    '/u01/oradata/lunar/lunar_dat_52.dbf',
17:33:22  93    '/u01/oradata/lunar/lunar_dat_53.dbf',
17:33:22  94    '/u01/oradata/lunar/lunar_dat_54.dbf',
17:33:22  95    '/u01/oradata/lunar/lunar_dat_55.dbf',
17:33:22  96    '/u01/oradata/lunar/lunar_dat_56.dbf',
17:33:22  97    '/u01/oradata/lunar/lunar_dat_57.dbf',
17:33:22  98    '/u01/oradata/lunar/lunar_dat_58.dbf',
17:33:22  99    '/u01/oradata/lunar/lunar_dat_59.dbf',
17:33:22 100    '/u01/oradata/lunar/lunar_dat_60.dbf',
17:33:22 101    '/u01/oradata/lunar/lunar_dat_61.dbf',
17:33:22 102    '/u01/oradata/lunar/lunar_dat_62.dbf',
17:33:22 103    '/u01/oradata/lunar/lunar_dat_63.dbf',
17:33:22 104    '/u01/oradata/lunar/lunar_dat_64.dbf',
17:33:22 105    '/u01/oradata/lunar/lunar_dat_65.dbf',
17:33:22 106    '/u01/oradata/lunar/lunar_dat_66.dbf',
17:33:22 107    '/u01/oradata/lunar/lunar_dat_67.dbf',
17:33:23 108    '/u01/oradata/lunar/lunar_dat_68.dbf',
17:33:23 109    '/u01/oradata/lunar/lunar_dat_69.dbf',
17:33:23 110    '/u01/oradata/lunar/lunar_dat_70.dbf',
17:33:23 111    '/u01/oradata/lunar/lunar_dat_71.dbf',
17:33:23 112    '/u01/oradata/lunar/lunar_dat_72.dbf',
17:33:23 113    '/u01/oradata/lunar/lunar_dat_73.dbf',
17:33:23 114    '/u01/oradata/lunar/lunar_dat_74.dbf',
17:33:23 115    '/u01/oradata/lunar/lunar_dat_75.dbf',
17:33:23 116    '/u01/oradata/lunar/lunar_dat_76.dbf',
17:33:23 117    '/u01/oradata/lunar/lunar_dat_77.dbf',
17:33:23 118    '/u01/oradata/lunar/lunar_dat_78.dbf',
17:33:23 119    '/u01/oradata/lunar/lunar_dat_79.dbf',
17:33:23 120    '/u01/oradata/lunar/lunar_dat_80.dbf',
17:33:23 121    '/u01/oradata/lunar/undotbs05.dbf',
17:33:23 122    '/u01/oradata/lunar/lunar_car_dat_01.dbf',
17:33:23 123    '/u01/oradata/lunar/lunar_car_dat_02.dbf',
17:33:23 124    '/u01/oradata/lunar/lunar_car_dat_03.dbf',
17:33:24 125    '/u01/oradata/lunar/lunar_car_dat_04.dbf',
17:33:24 126    '/u01/oradata/lunar/lunar_car_dat_05.dbf',
17:33:24 127    '/u01/oradata/lunar/lunar_car_idx_01.dbf',
17:33:24 128    '/u01/oradata/lunar/lunar_car_idx_02.dbf',
17:33:24 129    '/u01/oradata/lunar/lunar_car_idx_03.dbf',
17:33:24 130    '/u01/oradata/lunar/lunar_car_idx_04.dbf',
17:33:24 131    '/u01/oradata/lunar/lunar_car_idx_05.dbf',
17:33:24 132    '/u01/oradata/lunar/lunar_car_dat_06.dbf',
17:33:24 133    '/u01/oradata/lunar/lunar_car_dat_07.dbf',
17:33:24 134    '/u01/oradata/lunar/lunar_car_dat_08.dbf',
17:33:24 135    '/u01/oradata/lunar/lunar_car_dat_09.dbf',
17:33:24 136    '/u01/oradata/lunar/lunar_car_dat_10.dbf',
17:33:24 137    '/u01/oradata/lunar/lunar_car_idx_06.dbf',
17:33:24 138    '/u01/oradata/lunar/lunar_car_idx_07.dbf',
17:33:24 139    '/u01/oradata/lunar/lunar_car_idx_08.dbf',
17:33:24 140    '/u01/oradata/lunar/lunar_car_idx_09.dbf',
17:33:25 141    '/u01/oradata/lunar/lunar_car_idx_10.dbf',
17:33:25 142    '/u01/oradata/lunar/lunar_dat_81.dbf',
17:33:25 143    '/u01/oradata/lunar/lunar_dat_82.dbf',
17:33:25 144    '/u01/oradata/lunar/lunar_dat_83.dbf',
17:33:25 145    '/u01/oradata/lunar/lunar_dat_84.dbf',
17:33:25 146    '/u01/oradata/lunar/lunar_dat_85.dbf',
17:33:25 147    '/u01/oradata/lunar/lunar_dat_86.dbf',
17:33:25 148    '/u01/oradata/lunar/lunar_dat_87.dbf',
17:33:25 149    '/u01/oradata/lunar/lunar_dat_88.dbf',
17:33:25 150    '/u01/oradata/lunar/lunar_dat_89.dbf',
17:33:25 151    '/u01/oradata/lunar/lunar_dat_90.dbf',
17:33:25 152    '/u01/oradata/lunar/lunar_idx_02.dbf',
17:33:25 153    '/u01/oradata/lunar/lunar_idx_03.dbf',
17:33:25 154    '/u01/oradata/lunar/lunar_idx_04.dbf',
17:33:25 155    '/u01/oradata/lunar/lunar_idx_05.dbf',
17:33:25 156    '/u01/oradata/lunar/lunar_idx_06.dbf',
17:33:25 157    '/u01/oradata/lunar/lunar_idx_07.dbf',
17:33:26 158    '/u01/oradata/lunar/lunar_idx_08.dbf',
17:33:26 159    '/u01/oradata/lunar/lunar_idx_09.dbf',
17:33:26 160    '/u01/oradata/lunar/lunar_idx_10.dbf',
17:33:26 161    '/u01/oradata/lunar/lunar_dat_91.dbf',
17:33:26 162    '/u01/oradata/lunar/lunar_dat_92.dbf',
17:33:26 163    '/u01/oradata/lunar/lunar_dat_93.dbf',
17:33:26 164    '/u01/oradata/lunar/lunar_dat_94.dbf',
17:33:26 165    '/u01/oradata/lunar/lunar_dat_95.dbf',
17:33:26 166    '/u01/oradata/lunar/lunar_dat_96.dbf',
17:33:26 167    '/u01/oradata/lunar/lunar_dat_97.dbf',
17:33:26 168    '/u01/oradata/lunar/lunar_dat_98.dbf',
17:33:26 169    '/u01/oradata/lunar/lunar_dat_99.dbf',
17:33:26 170    '/u01/oradata/lunar/lunar_dat_100.dbf',
17:33:26 171    '/u01/oradata/lunar/lunar_dat_101.dbf',
17:33:26 172    '/u01/oradata/lunar/lunar_dat_102.dbf',
17:33:27 173    '/u01/oradata/lunar/lunar_dat_103.dbf',
17:33:27 174    '/u01/oradata/lunar/lunar_dat_104.dbf',
17:33:27 175    '/u01/oradata/lunar/lunar_dat_105.dbf',
17:33:27 176    '/u01/oradata/lunar/lunar_dat_106.dbf',
17:33:27 177    '/u01/oradata/lunar/lunar_dat_107.dbf',
17:33:27 178    '/u01/oradata/lunar/lunar_dat_108.dbf',
17:33:27 179    '/u01/oradata/lunar/lunar_dat_109.dbf',
17:33:27 180    '/u01/oradata/lunar/lunar_dat_110.dbf',
17:33:27 181    '/u01/oradata/lunar/undotbs06.dbf',
17:33:27 182    '/u01/oradata/lunar/undotbs07.dbf',
17:33:27 183    '/u01/oradata/lunar/lunar_dat_111.dbf',
17:33:27 184    '/u01/oradata/lunar/lunar_dat_112.dbf',
17:33:27 185    '/u01/oradata/lunar/lunar_dat_113.dbf',
17:33:27 186    '/u01/oradata/lunar/lunar_dat_114.dbf',
17:33:27 187    '/u01/oradata/lunar/lunar_dat_115.dbf',
17:33:27 188    '/u01/oradata/lunar/lunar_idx_11.dbf',
17:33:27 189    '/u01/oradata/lunar/lunar_idx_12.dbf',
17:33:28 190    '/u01/oradata/lunar/lunar_idx_13.dbf',
17:33:28 191    '/u01/oradata/lunar/lunar_idx_14.dbf',
17:33:28 192    '/u01/oradata/lunar/lunar_idx_15.dbf',
17:33:28 193    '/u01/oradata/lunar/lunar_dat_116.dbf',
17:33:28 194    '/u01/oradata/lunar/lunar_dat_117.dbf',
17:33:28 195    '/u01/oradata/lunar/lunar_dat_118.dbf',
17:33:28 196    '/u01/oradata/lunar/lunar_dat_119.dbf',
17:33:28 197    '/u01/oradata/lunar/lunar_dat_120.dbf',
17:33:28 198    '/u01/oradata/lunar/lunar_dat_121.dbf',
17:33:28 199    '/u01/oradata/lunar/lunar_dat_122.dbf',
17:33:28 200    '/u01/oradata/lunar/lunar_dat_123.dbf',
17:33:28 201    '/u01/oradata/lunar/lunar_dat_124.dbf',
17:33:28 202    '/u01/oradata/lunar/lunar_dat_125.dbf',
17:33:28 203    '/u01/oradata/lunar/lunar_idx_16.dbf',
17:33:28 204    '/u01/oradata/lunar/lunar_idx_17.dbf',
17:33:28 205    '/u01/oradata/lunar/lunar_idx_18.dbf',
17:33:29 206    '/u01/oradata/lunar/lunar_idx_19.dbf',
17:33:29 207    '/u01/oradata/lunar/lunar_idx_20.dbf',
17:33:29 208    '/u01/oradata/lunar/lunar_idx_21.dbf',
17:33:29 209    '/u01/oradata/lunar/lunar_idx_22.dbf',
17:33:29 210    '/u01/oradata/lunar/lunar_idx_23.dbf',
17:33:29 211    '/u01/oradata/lunar/lunar_idx_24.dbf',
17:33:29 212    '/u01/oradata/lunar/lunar_idx_25.dbf',
17:33:29 213    '/u01/oradata/lunar/lunar_dat_126.dbf',
17:33:29 214    '/u01/oradata/lunar/lunar_dat_127.dbf',
17:33:29 215    '/u01/oradata/lunar/lunar_dat_128.dbf',
17:33:29 216    '/u01/oradata/lunar/lunar_dat_129.dbf',
17:33:29 217    '/u01/oradata/lunar/lunar_dat_130.dbf',
17:33:29 218    '/u01/oradata/lunar/lunar_idx_26.dbf',
17:33:29 219    '/u01/oradata/lunar/lunar_idx_27.dbf',
17:33:29 220    '/u01/oradata/lunar/lunar_idx_28.dbf',
17:33:29 221    '/u01/oradata/lunar/lunar_idx_29.dbf',
17:33:29 222    '/u01/oradata/lunar/lunar_idx_30.dbf',
17:33:30 223    '/u01/oradata/lunar/lunar_dat_131.dbf',
17:33:30 224    '/u01/oradata/lunar/lunar_dat_132.dbf',
17:33:30 225    '/u01/oradata/lunar/lunar_dat_133.dbf',
17:33:30 226    '/u01/oradata/lunar/lunar_dat_134.dbf',
17:33:30 227    '/u01/oradata/lunar/lunar_dat_135.dbf',
17:33:30 228    '/u01/oradata/lunar/lunar_idx_31.dbf',
17:33:30 229    '/u01/oradata/lunar/lunar_idx_32.dbf',
17:33:30 230    '/u01/oradata/lunar/lunar_idx_33.dbf',
17:33:30 231    '/u01/oradata/lunar/lunar_idx_34.dbf',
17:33:30 232    '/u01/oradata/lunar/lunar_idx_35.dbf',
17:33:30 233    '/u01/oradata/lunar/lunar_dat_136.dbf',
17:33:30 234    '/u01/oradata/lunar/lunar_dat_137.dbf',
17:33:30 235    '/u01/oradata/lunar/lunar_dat_138.dbf',
17:33:30 236    '/u01/oradata/lunar/lunar_dat_139.dbf',
17:33:30 237    '/u01/oradata/lunar/lunar_idx_36.dbf',
17:33:30 238    '/u01/oradata/lunar/lunar_dat_140',
17:33:31 239    '/u01/oradata/lunar/lunar_dat_141',
17:33:31 240    '/u01/oradata/lunar/lunar_dat_142',
17:33:31 241    '/u01/oradata/lunar/lunar_dat_143',
17:33:31 242    '/u01/oradata/lunar/lunar_dat_144',
17:33:31 243    '/u01/oradata/lunar/lunar_dat_145',
17:33:31 244    '/u01/oradata/lunar/lunar_idx_37.dbf',
17:33:31 245    '/u01/oradata/lunar/lunar_idx_38.dbf',
17:33:31 246    '/u01/oradata/lunar/lunar_idx_39.dbf',
17:33:31 247    '/u01/oradata/lunar/lunar_idx_40.dbf',
17:33:31 248    '/u01/oradata/lunar/lunar_dat_146.dbf',
17:33:31 249    '/u01/oradata/lunar/lunar_dat_147.dbf',
17:33:31 250    '/u01/oradata/lunar/lunar_dat_148.dbf',
17:33:31 251    '/u01/oradata/lunar/lunar_dat_149.dbf',
17:33:31 252    '/u01/oradata/lunar/lunar_dat_150.dbf',
17:33:31 253    '/u01/oradata/lunar/lunar_dat_151.dbf',
17:33:31 254    '/u01/oradata/lunar/lunar_dat_152.dbf',
17:33:31 255    '/u01/oradata/lunar/lunar_dat_153.dbf',
17:33:32 256    '/u01/oradata/lunar/lunar_dat_154.dbf',
17:33:32 257    '/u01/oradata/lunar/lunar_dat_155.dbf',
17:33:32 258    '/u01/oradata/lunar/lunar_dat_156.dbf',
17:33:32 259    '/u01/oradata/lunar/lunar_dat_157.dbf',
17:33:32 260    '/u01/oradata/lunar/lunar_dat_158.dbf',
17:33:32 261    '/u01/oradata/lunar/lunar_dat_159.dbf',
17:33:32 262    '/u01/oradata/lunar/lunar_dat_160.dbf',
17:33:32 263    '/u01/oradata/lunar/lunar_idx_41.dbf',
17:33:32 264    '/u01/oradata/lunar/lunar_car_idx_11.dbf',
17:33:32 265    '/u01/oradata/lunar/lunar_idx_42.dbf',
17:33:32 266    '/u01/oradata/lunar/lunar_idx_43.dbf',
17:33:32 267    '/u01/oradata/lunar/undotbs08.dbf',
17:33:32 268    '/u01/oradata/lunar/lunar_car_dat_11.dbf',
17:33:32 269    '/u01/oradata/lunar/lunar_car_idx_12.dbf',
17:33:32 270    '/u01/oradata/lunar/lunar_car_idx_13.dbf',
17:33:32 271    '/u01/oradata/lunar/undotbs09.dbf',
17:33:33 272    '/u01/oradata/lunar/undotbs10.dbf',
17:33:33 273    '/u01/oradata/lunar/undotbs11.dbf',
17:33:33 274    '/u01/oradata/lunar/undotbs12.dbf',
17:33:33 275    '/u01/oradata/lunar/lunar_car_idx_14.dbf',
17:33:33 276    '/u01/oradata/lunar/lunar_car_idx_15.dbf',
17:33:33 277    '/u01/oradata/lunar/lunar_car_idx_16.dbf',
17:33:33 278    '/u01/oradata/lunar/lunar_car_dat_12.dbf',
17:33:33 279    '/u01/oradata/lunar/lunar_car_dat_13.dbf',
17:33:33 280    '/u01/oradata/lunar/lunar_car_dat_14.dbf',
17:33:33 281    '/u01/oradata/lunar/lunar_dat_161.dbf',
17:33:33 282    '/u01/oradata/lunar/lunar_dat_162.dbf',
17:33:33 283    '/u01/oradata/lunar/lunar_dat_163.dbf',
17:33:33 284    '/u01/oradata/lunar/lunar_dat_164.dbf',
17:33:33 285    '/u01/oradata/lunar/lunar_dat_165.dbf',
17:33:33 286    '/u01/oradata/lunar/lunar_dat_166.dbf',
17:33:33 287    '/u01/oradata/lunar/lunar_dat_167.dbf',
17:33:33 288    '/u01/oradata/lunar/lunar_dat_168.dbf',
17:33:34 289    '/u01/oradata/lunar/undotbs13.dbf',
17:33:34 290    '/u01/oradata/lunar/undotbs14.dbf',
17:33:34 291    '/u01/oradata/lunar/undotbs15.dbf',
17:33:34 292    '/u01/oradata/lunar/undotbs16.dbf',
17:33:34 293    '/u01/oradata/lunar/lunar_car_idx_17.dbf',
17:33:34 294    '/u01/oradata/lunar/lunar_car_idx_18.dbf',
17:33:34 295    '/u01/oradata/lunar/undotbs17.dbf',
17:33:34 296    '/u01/oradata/lunar/undotbs18.dbf',
17:33:34 297    '/u01/oradata/lunar/undotbs19.dbf',
17:33:34 298    '/u01/oradata/lunar/undotbs20.dbf',
17:33:34 299    '/u01/oradata/lunar/lunar_car_dat_15.dbf',
17:33:34 300    '/u01/oradata/lunar/lunar_car_dat_16.dbf',
17:33:34 301    '/u01/oradata/lunar/lunar_car_dat_17.dbf',
17:33:34 302    '/u01/oradata/lunar/lunar_car_idx_19.dbf',
17:33:34 303    '/u01/oradata/lunar/lunar_car_idx_20.dbf',
17:33:34 304    '/u01/oradata/lunar/lunar_car_idx_21.dbf',
17:33:34 305    '/u01/oradata/lunar/lunar_car_idx_22.dbf',
17:33:35 306    '/u01/oradata/lunar/lunar_car_idx_23.dbf',
17:33:35 307    '/u01/oradata/lunar/lunar_car_idx_24.dbf',
17:33:35 308    '/u01/oradata/lunar/lunar_car_idx_25.dbf',
17:33:35 309    '/u01/oradata/lunar/lunar_car_idx_26.dbf',
17:33:35 310    '/u01/oradata/lunar/lunar_car_idx_27.dbf',
17:33:35 311    '/u01/oradata/lunar/lunar_car_dat_18.dbf',
17:33:35 312    '/u01/oradata/lunar/lunar_car_dat_19.dbf',
17:33:35 313    '/u01/oradata/lunar/lunar_car_dat_20.dbf',
17:33:35 314    '/u01/oradata/lunar/lunar_car_dat_21.dbf',
17:33:35 315    '/u01/oradata/lunar/lunar_car_dat_22.dbf',
17:33:35 316    '/u01/oradata/lunar/lunar_idx_44.dbf',
17:33:35 317    '/u01/oradata/lunar/lunar_idx_45.dbf',
17:33:35 318    '/u01/oradata/lunar/lunar_dat_169.dbf',
17:33:35 319    '/u01/oradata/lunar/lunar_dat_170.dbf'
17:33:35 320  CHARACTER SET ZHS16GBK
17:33:35 321  ;

Control file created.

Elapsed: 00:00:01.35
17:33:43 SYS@ lunardb3>

激活数据库：

17:35:03 SYS@ lunardb3> RECOVER DATABASE USING BACKUP CONTROLFILE until cance;
ORA-00277: illegal option to the UNTIL recovery flag CANCE


17:35:14 SYS@ lunardb3> RECOVER DATABASE USING BACKUP CONTROLFILE until cancel;
ORA-00279: change 6194107977643 generated at  needed for thread 1


17:35:50 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
17:35:53 SYS@ lunardb3> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Elapsed: 00:00:06.56
17:36:05 SYS@ lunardb3>

最后在备库添加temp文件：

18:15:26 SYS@ lunardb3> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/lunar/temp01.dbf' REUSE;

Tablespace altered.

Elapsed: 00:00:00.08
18:16:19 SYS@ lunardb3>
18:26:26 SYS@ lunardb3> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/lunar/temp02.dbf' size 16G;

Tablespace altered.

Elapsed: 00:00:00.13
18:26:32 SYS@ lunardb3> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/lunar/temp03.dbf' size 16G;

Tablespace altered.

Elapsed: 00:00:00.07
18:26:46 SYS@ lunardb3> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/lunar/temp04.dbf' size 16G;

Tablespace altered.

Elapsed: 00:00:00.06
18:27:02 SYS@ lunardb3> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/lunar/temp05.dbf' size 16G;

Tablespace altered.

Elapsed: 00:00:00.06
18:27:07 SYS@ lunardb3> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIME              TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------------- ------------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ----------
NAME
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1       6.1941E+12 2015-04-15 18:16:19          3          1 ONLINE  READ WRITE 1.7180E+10    2097152   1.7180E+10       8192
/u01/oradata/lunar/temp01.dbf

         2       6.1941E+12 2015-04-15 18:26:32          3          2 ONLINE  READ WRITE 1.7180E+10    2097152   1.7180E+10       8192
/u01/oradata/lunar/temp02.dbf

         3       6.1941E+12 2015-04-15 18:26:39          3          3 ONLINE  READ WRITE 1.7180E+10    2097152   1.7180E+10       8192
/u01/oradata/lunar/temp03.dbf

         4       6.1941E+12 2015-04-15 18:27:02          3          4 ONLINE  READ WRITE 1.7180E+10    2097152   1.7180E+10       8192
/u01/oradata/lunar/temp04.dbf

         5       6.1941E+12 2015-04-15 18:27:07          3          5 ONLINE  READ WRITE 1.7180E+10    2097152   1.7180E+10       8192
/u01/oradata/lunar/temp05.dbf


Elapsed: 00:00:00.01
18:27:40 SYS@ lunardb3>
发表在 backup&recovery, Dataguard | 标签为 , , | 留下评论

OEL6.2 EXT4 filesystemio_options=SETALL造成archivelog坏块

发表于 2015 年 4 月 14 日 由 Lunar

又踩了个坑……

今天同事告诉我前天调整一个数据库的参数，重启后，备库总报错：

Errors in file /u01/oracle/app/diag/rdbms/lunar1db3/lunar1db3/trace/lunar1db3_rfs_26241.trc:
ORA-00272: error writing archive log /u01/oradata/arch/1_775047_804791836.arc
ORA-00354: corrupt redo log block header
RFS[3]: Possible network disconnect with primary database
Errors in file /u01/oracle/app/diag/rdbms/lunar1db3/lunar1db3/trace/lunar1db3_rfs_26237.trc:
ORA-00272: error writing archive log /u01/oradata/arch/1_775048_804791836.arc
ORA-00354: corrupt redo log block header
Errors in file /u01/oracle/app/diag/rdbms/lunar1db3/lunar1db3/trace/lunar1db3_rfs_26239.trc:
ORA-00272: error writing archive log /u01/oradata/arch/1_775046_804791836.arc
ORA-00354: corrupt redo log block header
RFS[1]: Possible network disconnect with primary database
RFS[2]: Possible network disconnect with primary database
Tue Apr 14 20:29:08 2015
RFS[4]: Assigned to RFS process 26243
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 21348
RFS[4]: Opened log for thread 1 sequence 775051 dbid 148164954 branch 804791836
Tue Apr 14 20:29:09 2015
RFS[5]: Assigned to RFS process 26245
RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 21354
Tue Apr 14 20:29:09 2015
RFS[6]: Assigned to RFS process 26247
RFS[6]: Identified database type as 'physical standby': Client is ARCH pid 21356
CORRUPTION DETECTED: In redo blocks starting at block 1count 2048 for thread 1 sequence 775051
RFS[5]: Opened log for thread 1 sequence 775049 dbid 148164954 branch 804791836
RFS[6]: Opened log for thread 1 sequence 775050 dbid 148164954 branch 804791836
CORRUPTION DETECTED: In redo blocks starting at block 1count 2048 for thread 1 sequence 775049
CORRUPTION DETECTED: In redo blocks starting at block 1count 2048 for thread 1 sequence 775050
Errors in file /u01/oracle/app/diag/rdbms/lunar1db3/lunar1db3/trace/lunar1db3_rfs_26243.trc:
ORA-00272: error writing archive log /u01/oradata/arch/1_775051_804791836.arc
ORA-00354: corrupt redo log block header
Errors in file /u01/oracle/app/diag/rdbms/lunar1db3/lunar1db3/trace/lunar1db3_rfs_26245.trc:
ORA-00272: error writing archive log /u01/oradata/arch/1_775049_804791836.arc
ORA-00354: corrupt redo log block header
Errors in file /u01/oracle/app/diag/rdbms/lunar1db3/lunar1db3/trace/lunar1db3_rfs_26247.trc:
ORA-00272: error writing archive log /u01/oradata/arch/1_775050_804791836.arc
ORA-00354: corrupt redo log block header
RFS[4]: Possible network disconnect with primary database
RFS[5]: Possible network disconnect with primary database
RFS[6]: Possible network disconnect with primary database
Tue Apr 14 20:29:12 2015
RFS[7]: Assigned to RFS process 26249
RFS[7]: Identified database type as 'physical standby': Client is ARCH pid 21348
RFS[7]: Opened log for thread 1 sequence 775052 dbid 148164954 branch 804791836
CORRUPTION DETECTED: In redo blocks starting at block 1count 2048 for thread 1 sequence 775052
Errors in file /u01/oracle/app/diag/rdbms/lunar1db3/lunar1db3/trace/lunar1db3_rfs_26249.trc:
ORA-00272: error writing archive log /u01/oradata/arch/1_775052_804791836.arc
ORA-00354: corrupt redo log block header
RFS[7]: Possible network disconnect with primary database

初分析，这个错误有点怪异，有redo 头损坏，有“Possible network disconnect with primary database”
尝试clear online redo log和standby redo log，没用
尝试在主库重建控制文件，然后再直接重启备库，还是上面的错误。
冷静下来，感觉不对劲，检查主库和备库，发现主库归档日志是按照sequence顺序生成
备库则是断断续续的，有的可以从主库传过来，有的传不过来
手工传过来，APPLY还是报错：

SYS@ lunar1db3> recover standby database;
ORA-00279: change 6194107977528 generated at 04/13/2015 12:28:54 needed for thread 1
ORA-00289: suggestion : /u01/oradata/arch/1_775046_804791836.arc
ORA-00280: change 6194107977528 for thread 1 is in sequence #775046


20:49:02 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/arch/1_775046_804791836.arc
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2048 change 6194107977662 time 04/13/2015 12:28:54
ORA-00334: archived log: '/u01/oradata/arch/1_775046_804791836.arc'


ORA-01112: media recovery not started


SYS@ lunar1db3>

感觉是arch异常了，而且貌似所有报错的，都是没有从主库传过来的
而所有没传过来的，都是损坏的，因此手工传过来也没用，因此抽取一个arch进行校验：

SYS@ lunar1db3> alter system dump logfile '/u01/oradata/arch/1_775046_804791836.arc' VALIDATE;
alter system dump logfile '/u01/oradata/arch/1_775046_804791836.arc' VALIDATE
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2048 change 6194107977662 time 04/13/2015 12:28:54
ORA-00334: archived log: '/u01/oradata/arch/1_775046_804791836.arc'


Elapsed: 00:00:01.08
SYS@ lunar1db3>

结果显示，确实arch损坏了。
.
根据问题发生的时间，想起来那天调整数据库参数的时候，有一个filesystemio_options=SETALL
也就是调整文件系统AIO方式的，去年在公司还给大家发邮件说起来“ext4上不要使用 filesystemio_options=SETALL”
否则会造成数据库坏块，没想到今年自己被坑了……（哇哇大哭啊……）
具体可以参见Oracle 文档：
ORA-1578 ORA-353 ORA-19599 Corrupt blocks with zeros when filesystemio_options=SETALL on ext4 file system using Linux (Doc ID 1487957.1)
.
大概是说，在下面的版本上，如果使用ext4，那么设置了filesystemio_options=SETALL可能会造成数据库坏块：

- for RHEL5
kernel-2.6.18-238.el5 - RHEL5.6 Errata RHSA-2011-0017 or later
( [fs] ext4: move aio completion after unwritten extent con (Eric Sandeen) [617690] )

- for RHEL6 you need to have
kernel-2.6.32-71 and later 
( [fs] ext4: move aio completion after unwritten extent conversion (Christoph Hellwig) [589985] )

检查了一下我们的系统，果然命中：

[oracle@lunardb1 ~]$ uname -a
Linux lunardb1.800best.com 2.6.32-300.3.1.el6uek.x86_64 #1 SMP Fri Dec 9 18:57:35 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
[oracle@lunardb1 ~]$ cat /etc/issue
Oracle Linux Server release 6.2
Kernel \r on an \m

[oracle@lunardb1 ~]$ mount
/dev/mapper/vg_lunar1adg01-lv_root on / type ext4 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw)
/dev/sda1 on /boot type ext4 (rw)
/dev/mapper/vg_lunar1adg01-LogVol02 on /u01 type ext4 (rw)
/dev/mapper/vg_lunar1data01-oradata on /u01/oradata type ext4 (rw)
/dev/mapper/vg_lunar1data01-tmpdata on /tmpdata type ext4 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
[oracle@lunardb1 ~]$

后悔呀，上周类似的一个系统同样的一套参数调整，怎么没问题呢？
查看了一下，因为是ext3…………

[oracle@lunardb2 ~]$ uname -a
Linux lunardb2.800best.com 2.6.18-308.el5 #1 SMP Fri Jan 27 17:17:51 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
[oracle@lunardb2 ~]$ cat /etc/issue
Red Hat Enterprise Linux Server release 5.8 (Tikanga)
Kernel \r on an \m

[oracle@lunardb2 ~]$ 
[oracle@lunardb2 ~]$ mount
/dev/sda1 on / type ext3 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw)
/dev/mapper/vg00-lv00 on /tmpdata type ext3 (rw)
/dev/mapper/vg00-lv01 on /hddata type ext3 (rw)
/dev/sda3 on /u01 type ext3 (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
[oracle@lunardb2 ~]$

没办法，申请业务，短期内，只能修改数据库参数回到filesystemio_options=none，然后重启主库
至于备库，只能重建了……
.
dd了两个好的归档日志和1个损坏的归档日志，对比了一下，发现有一个关键标示位，确实写错了：

无标题

无标题1

顺便说一下，这个bug在下面的版本已经被fixed了：
Updated kernel to version kernel-uek-2.6.39-200.29.3.el6uek

发表在 backup&recovery, Dataguard | 标签为 , , | 留下评论