Lunar的oracle实验室 - 世间所有相遇都是久别重逢

ORA-00604和ORA-04024错误的处理方法

发表于 2015 年 5 月 8 日由 Lunar

一般Oracle的bootstrap index（引导表的索引和一些核心对象）都可以尝试类似方法处理，比如下面查询语句中的I_OBJxxxxx。
.
测试环境 11.2.0.3数据库：

[oracle@lunarpri ~]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 27 19:12:57 2015

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 and Real Application Testing options
SYS@lunar>col OBJECT_NAME for a30
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         STATUS  LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS                            I_OBJ#                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ5                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ3                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ1                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ2                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ4                         INDEX               VALID   08-FEB-13
SYS                            I_OBJAUTH1                     INDEX               VALID   08-FEB-13
SYS                            I_OBJAUTH2                     INDEX               VALID   08-FEB-13
SYS                            I_OBJ#_INTCOL#                 INDEX               VALID   08-FEB-13
SYS                            I_OBJTYPE                      INDEX               VALID   08-FEB-13

10 rows selected.

Elapsed: 00:00:00.06
SYS@lunar>

在数据库中长开启时，不能操作一些bootstrap index，例如：
有些bootstrap索引在upgrade模式下也不可以修改，有些可以：

SYS@lunar>alter index SYS.I_OBJ5 unusable
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered


Elapsed: 00:00:00.01
SYS@lunar>alter index SYS.I_OBJ3 unusable
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered


Elapsed: 00:00:00.02
SYS@lunar>alter index SYS.I_OBJ1 unusable
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered


Elapsed: 00:00:00.01
SYS@lunar>

可以启动到升级模式，这模式数据库会自动增加一些类似屏蔽system trigger等的操作
可以执行部分bootstrage对象的操作，例如：

SYS@lunar>startup upgrade
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.
SYS@lunar>
SYS@lunar>alter index SYS.I_OBJAUTH2 unusable;
alter index SYS.I_OBJ#_INTCOL# unusable;
alter index SYS.I_OBJTYPE unusable;

Index altered.

Elapsed: 00:00:00.04
SYS@lunar>alter index SYS.I_OBJ# unusable

Index altered.

Elapsed: 00:00:00.20
SYS@lunar>
Index altered.

Elapsed: 00:00:00.03
SYS@lunar>
Index altered.

Elapsed: 00:00:00.03
SYS@lunar>

升级模式自动添加的参数如下：

Fri Mar 27 19:21:48 2015
MMNL started with pid=16, OS id=15218
ALTER SYSTEM enable restricted session;
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan '' not set
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
ALTER SYSTEM SET recyclebin='OFF' DEFERRED SCOPE=MEMORY;
Resource Manager disabled during database migration
replication_dependency_tracking turned off (no async multimaster replication found)

此时，数据字典因为遭到破坏，很多相关查询等功能都失效了：

SYS@lunar>col OBJECT_NAME for a30
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'
                                                               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state


Elapsed: 00:00:00.20
SYS@lunar>

且启动时会报错ORA-00604 ORA-04024:

SYS@lunar>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.
ORA-00604: error occurred at recursive SQL level 4
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07EF125E8


SYS@lunar>

正常关闭数据库也关闭不了，应该是需要执行某些核心递归SQL时遇到问题了，只能shutdown abort：

SYS@lunar>shutdown immediate
ORA-00604: error occurred at recursive SQL level 4
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07EF125E8
SYS@lunar>shutdown abort 
ORACLE instance shut down
SYS@lunar>

然后以升级模式启动数据库，修复这些索引：.

SYS@lunar>startup upgrade
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.
SYS@lunar>

SYS@lunar>show parameter NLS_LENGTH_SEMANTICS
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state


SYS@lunar>

执行迷你升级脚本进行修复：

SYS@lunar>ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE;

Session altered.

Elapsed: 00:00:00.00
SYS@lunar>@?/rdbms/admin/utlmmig.sql

View created.

Elapsed: 00:00:01.32

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.34

Commit complete.

Elapsed: 00:00:00.01

Table dropped.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.53

Index created.

Elapsed: 00:00:00.12

Index created.

Elapsed: 00:00:00.05

Index created.

Elapsed: 00:00:00.02

Index created.

Elapsed: 00:00:00.03

Index created.

Elapsed: 00:00:00.04

Table dropped.

Elapsed: 00:00:00.02

Table created.

Elapsed: 00:00:00.07

Index created.

Elapsed: 00:00:00.03

Index created.

Elapsed: 00:00:00.03

Table dropped.

Elapsed: 00:00:00.00

Table created.

Elapsed: 00:00:00.04

Table dropped.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.05
declare
*
ERROR at line 1:
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state
ORA-06512: at line 13
ORA-06512: at line 137


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options
[oracle@lunarpri ~]$

这里我们看到SYS.I_OBJ#_INTCOL#索引为unusable，不能在升级模式下被修复
但是，10g以后，这个索引是可以通过event 38003屏蔽的：

SYS@lunar>create pfile='/tmp/spfile.bak' from spfile;

File created.

Elapsed: 00:00:00.01
SYS@lunar>show parameter spfile 
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state

SYS@lunar>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@lunar>

[oracle@lunarpri ~]$ tail /tmp/spfile.bak 
*.db_recovery_file_dest_size=10485760000
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=153092096
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=629145600
*.undo_tablespace='UNDOTBS1'
*.EVENT="38003 trace name context forever, level 10"
[oracle@lunarpri ~]$ 

SYS@lunar>startup pfile=/tmp/spfile.bak  upgrade
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.
SYS@lunar>col OBJECT_NAME for a30
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';
select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%'
                                                               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_OBJ#_INTCOL#' or partition of such index is in unusable state


Elapsed: 00:00:00.08
SYS@lunar>alter index SYS.I_OBJ#_INTCOL# rebuild;

Index altered.

Elapsed: 00:00:00.46
SYS@lunar>

这里我们已经修复了损坏的索引。
在此基础上，我们重新执行迷你升级脚本：

SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         STATUS  LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS                            I_OBJ#                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ5                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ3                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ1                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ2                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ4                         INDEX               VALID   08-FEB-13
SYS                            I_OBJAUTH1                     INDEX               VALID   27-MAR-15
SYS                            I_OBJAUTH2                     INDEX               VALID   27-MAR-15
SYS                            I_OBJ#_INTCOL#                 INDEX               VALID   27-MAR-15
SYS                            I_OBJTYPE                      INDEX               VALID   27-MAR-15
SYS                            I_OBJ_MIG1                     INDEX               VALID   27-MAR-15
SYS                            I_OBJ_MIG2                     INDEX               VALID   27-MAR-15
SYS                            I_OBJ_MIG3                     INDEX               VALID   27-MAR-15
SYS                            I_OBJ_MIG4                     INDEX               VALID   27-MAR-15
SYS                            I_OBJ_MIG5                     INDEX               VALID   27-MAR-15

15 rows selected.

Elapsed: 00:00:00.49
SYS@lunar>@?/rdbms/admin/utlmmig.sql

View created.

Elapsed: 00:00:00.69

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Commit complete.

Elapsed: 00:00:00.00

Table dropped.

Elapsed: 00:00:00.28

Table created.

Elapsed: 00:00:00.08

Index created.

Elapsed: 00:00:00.09

Index created.

Elapsed: 00:00:00.03

Index created.

Elapsed: 00:00:00.02

Index created.

Elapsed: 00:00:00.03

Index created.

Elapsed: 00:00:00.03

Table dropped.

Elapsed: 00:00:00.12

Table created.

Elapsed: 00:00:00.08

Index created.

Elapsed: 00:00:00.02

Index created.

Elapsed: 00:00:00.03

Table dropped.

Elapsed: 00:00:00.08

Table created.

Elapsed: 00:00:00.03

Table dropped.

Elapsed: 00:00:00.06

Table created.

Elapsed: 00:00:00.03

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.30

49 rows created.

Elapsed: 00:00:00.03

60 rows created.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.16

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

10 rows deleted.

Elapsed: 00:00:00.03

Commit complete.

Elapsed: 00:00:00.00

10 rows created.

Elapsed: 00:00:00.01

Commit complete.

Elapsed: 00:00:00.00

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.82

        COUNT(*)
----------------
              60

Elapsed: 00:00:00.00

        COUNT(*)
----------------
              60

Elapsed: 00:00:00.00

        COUNT(*)
----------------
              60

Elapsed: 00:00:00.01

        COUNT(*)
----------------
              60

Elapsed: 00:00:00.01

        COUNT(*)
----------------
              60

Elapsed: 00:00:00.00

        COUNT(*)
----------------
              60

Elapsed: 00:00:00.00

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@lunar>

然后正常启动数据库：

SYS@lunar>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.
SYS@lunar>
SYS@lunar>col OBJECT_NAME for a30
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         STATUS  LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS                            I_OBJ#                         INDEX               VALID   08-FEB-13
SYS                            I_OBJ_MIG1                     INDEX               VALID   08-FEB-13
SYS                            I_OBJ_MIG2                     INDEX               VALID   08-FEB-13
SYS                            I_OBJ_MIG3                     INDEX               VALID   08-FEB-13
SYS                            I_OBJ_MIG4                     INDEX               VALID   08-FEB-13
SYS                            I_OBJ_MIG5                     INDEX               VALID   08-FEB-13
SYS                            I_OBJAUTH1                     INDEX               VALID   27-MAR-15
SYS                            I_OBJAUTH2                     INDEX               VALID   27-MAR-15
SYS                            I_OBJ#_INTCOL#                 INDEX               VALID   27-MAR-15
SYS                            I_OBJTYPE                      INDEX               VALID   27-MAR-15
SYS                            I_OBJ1                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ2                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ3                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ4                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ5                         INDEX               VALID   27-MAR-15

15 rows selected.

Elapsed: 00:00:00.14
SYS@lunar>

删除第一次执行迷你升级脚本因为异常而中断残留在数据库中的临时索引：

SYS@lunar>DROP INDEX SYS.I_OBJ_MIG1;

Index dropped.

Elapsed: 00:00:00.44
SYS@lunar>C/1/2          
  1* DROP INDEX SYS.I_OBJ_MIG2
SYS@lunar>/

Index dropped.

Elapsed: 00:00:00.06
SYS@lunar>C/2/3
  1* DROP INDEX SYS.I_OBJ_MIG3
SYS@lunar>/

Index dropped.

Elapsed: 00:00:00.05
SYS@lunar>C/3/4
  1* DROP INDEX SYS.I_OBJ_MIG4
SYS@lunar>/

Index dropped.

Elapsed: 00:00:00.08
SYS@lunar>C/4/5
  1* DROP INDEX SYS.I_OBJ_MIG5
SYS@lunar>/

Index dropped.

Elapsed: 00:00:00.05
SYS@lunar>
SYS@lunar>col OBJECT_NAME for a30
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         STATUS  LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS                            I_OBJ#                         INDEX               VALID   08-FEB-13
SYS                            I_OBJAUTH1                     INDEX               VALID   27-MAR-15
SYS                            I_OBJAUTH2                     INDEX               VALID   27-MAR-15
SYS                            I_OBJ#_INTCOL#                 INDEX               VALID   27-MAR-15
SYS                            I_OBJTYPE                      INDEX               VALID   27-MAR-15
SYS                            I_OBJ1                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ2                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ3                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ4                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ5                         INDEX               VALID   27-MAR-15

10 rows selected.

Elapsed: 00:00:00.07
SYS@lunar>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@lunar>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.
SYS@lunar>
SYS@lunar>col OBJECT_NAME for a30
SYS@lunar>select OWNER,OBJECT_NAME,OBJECT_TYPE,status,LAST_DDL_TIME from dba_objects where OBJECT_NAME like 'I_OBJ%';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         STATUS  LAST_DDL_
------------------------------ ------------------------------ ------------------- ------- ---------
SYS                            I_OBJ#                         INDEX               VALID   08-FEB-13
SYS                            I_OBJAUTH1                     INDEX               VALID   27-MAR-15
SYS                            I_OBJAUTH2                     INDEX               VALID   27-MAR-15
SYS                            I_OBJ#_INTCOL#                 INDEX               VALID   27-MAR-15
SYS                            I_OBJTYPE                      INDEX               VALID   27-MAR-15
SYS                            I_OBJ1                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ2                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ3                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ4                         INDEX               VALID   27-MAR-15
SYS                            I_OBJ5                         INDEX               VALID   27-MAR-15

10 rows selected.

Elapsed: 00:00:00.13
SYS@lunar>

至此，完美恢复！

发表在 ORA-600 or ORA-7445 | 标签为 ORA-00604 ORA-04024, ORA-00701 | 留下评论

Exadata X5-2服务器上扩内存或者更换内存的详细步骤

发表于 2015 年 5 月 8 日由 Lunar

Exadata X5-2服务器上扩内存或者更换内存的paper研究
Exadata上服务器内存条的使用限制：
1，服务器支持3种类型的内存条：
（1）32-GB quad-rank (QR) Load-Reduced DIMMs (LRDIMMs)
（2）8-GB single-rank (SR) 或者16-GB dual-rank (DR) Registered DIMMs (RDIMMs)
2，不能混用前面两种，即要么都是32-GB quad-rank (QR) Load-Reduced DIMMs (LRDIMMs)，要么都是 8-GB single-rank (SR) 或者16-GB dual-rank (DR) Registered DIMMs (RDIMMs)
3，根据文档，更换内存条的过程是：Within a memory channel, DIMMs must be populated in the black sockets first, then in the black sockets with white tabs, and then in the white sockets.（这个我理解就是更换时的先后顺序和操作注意事项，不懂硬件的Lunar无奈的看不懂了……）
4，如果混插8-GB single-rank (SR) 或者16-GB dual-rank (DR) Registered DIMMs (RDIMMs)内存条，那么首先要安装大的RDIMMs（比如现在安装16GB的，再安装8GB的）
.
更换内存的详细步骤如下：

1. Do not populate any DIMM sockets next to an empty processor socket. Each processor contains a separate memory controller.

2. The server supports 32-GB quad-rank (QR) Load-Reduced DIMMs (LRDIMMs) and 8-GB single-rank (SR) and 16-GB dual-rank
(DR) Registered DIMMs (RDIMMs).

3. Do not mix 32-GB LRDIMMs and 8-GB or 16-GB RDIMMs in the same server. If the server has 32-GB LRDIMMs installed, you
cannot install 8-GB or 16-GB RDIMMs and vice versa.

4. Within a memory channel, DIMMs must be populated in the black sockets first, then in the black sockets with white tabs, and
then in the white sockets.

Black Sockets D0/D11/D3/D8

Black Sockets with white tab : D1/D10/D4/D7

White Sockets : D2/D9/D5/D6

5. If you are installing a mix of RDIMM sizes (for example, 8-GB and 16-GB), install the larger size RDIMMs first, then the smaller
RDIMMs, until all of the RDIMMs are installed.

6. There are no restrictions on the minimum number of DIMM sockets that must be populated.

7.To confirm and/or identify the location of the failed DIMM press the Fault Remind button on the motherboard. This will light the
amber fault LED for the failed DIMM.

8. To remove the DIMM rotate both DIMM socket ejectors outward as far as they will go.

9. Carefully lift the DIMM straight up to remove it from the socket and set it aside on an antistatic mat.

10. Unpack the replacement DDR4 DIMM and place it on an antistatic mat.

11. Ensure that the replacement DDR4 DIMM matches the size it is replacing.

12. Install the DIMM into the motherboard by ensuring that the ejector tabs of the DIMM slots are in the open position.

13. Then align the notch in the DIMM with the key in the connector.

14. Push the DDR4 DIMM into the connector until the ejector tabs lock the DIMM in place.

C. Return the Server to operation

1. Replace the top cover

2. Remove any anti-static measures that were used.

3. Return the server to it's normal operating position within the rack.

4. Re-install the AC power cords and any data cables that were removed.

5. Power on server.

D. Check the ILOM to ensure that the fault has been cleared automatically and if not clear it manually.

1. login to the ILOM CLI and launch a Fault Management Shell session
-> start /SP/faultmgmt/shell
Are you sure you want to start /SP/faultmgmt/shell (y/n)? y
faultmgmtsp>

2. use the "fmadm faulty -a" command to list out all active faulty components

3. faultmgmtsp> fmadm faulty -a

4. If the DIMM that was just replaced is still listed as a fault then indicate that the DIMM has been replaced by using the command
"fmadm replaced <fru|cru|uuid>" for example:

faultmgmtsp> fmadm replaced /SYS/MB/P0/D2

5. confirm that the faults are cleared and then exit out of the fault management shell

faultmgmtsp> fmadm faulty -a
No faults found
faultmgmtsp> exit
->

发表在安装和升级 | 标签为 dimm, Exadata X5-2, 更换内存 | 留下评论

Exadata X5-2服务器上扩内存或者更换内存的paper研究

发表于 2015 年 5 月 8 日由 Lunar

根据Oracle白皮书的记录，Oracle Sun Server X5-2数据库服务器的配置如下：

X5-2 with Oracle Sun Server X5-2 Oracle Database Server:
Each database server is a component of the larger Machine and consists of the following major components:
		2 Intel Xeon E5-2699 18-Core processors (2.3GHz)
		256GB DDR4-2133 RAM (8x 32GB DIMMS; Memory Expansion kit provides up to 24x 32GB DIMMS in groups of 8)
		4x 600GB 10K RPM SAS2 disks pre-configured into a RAID5 volume with Linux and OVM pre-installed (choice made at install; dual-boot OS is not supported)
		Disk controller SAS3 HBA with 1GB write cache backed by ESM (LSI Aspen)
		Dual-port QDR (40Gb/s) InfiniBand Host Channel Adapter (HCA) PCIe Gen3 (qMirage CX3) supports Active/Active ports
		Dual-port 10 Gigabit Ethernet SFP+ ports (Intel Niantic)
		4 embedded 10 Gigabit Ethernet ports (copper) (1 is reserved for 1GbE management)
		1 Ethernet port for Sun Integrated Lights Out Manager (ILOM) for remote management
		Oracle Enterprise Linux pre-installed

注意上面：
256GB DDR4-2133 RAM (8x 32GB DIMMS; Memory Expansion kit provides up to 24x 32GB DIMMS in groups of 8)
也就是每台数据库服务器出厂缺省配置是256GB内存（8个32GB DIMM，具体型号是 DDR4-2133 RAM），可以扩展到最大24*32=768GB
LRDIMM是较新一代的内存设备，能够保证即使在单条内存32GB容量甚至更高的情况下，服务器上每个内存通道依然能够布满至3条内存条
这里的8组(P0：CHA,CHB,CHC,CHD P1：CHA,CHB,CHC,CHD)，也就是每组上面可以最多插入3根内存条，因此一共可插入24跟内存条，每个内存条32GB
具体如下图：

在服务器上如何查看当前内存的使用情况呢：
当前已经插入的内存条有8根，每根都是32GB：32*8=256GB

[root@dm01db01 ~]# dmidecode -t memory|grep "Size: 32 GB"
        Size: 32 GB
        Size: 32 GB
        Size: 32 GB
        Size: 32 GB
        Size: 32 GB
        Size: 32 GB
        Size: 32 GB
        Size: 32 GB
[root@dm01db01 ~]#

服务器上每个CPU支持的一对内存模块最多内存容量为192GB（6*32GB）：

[root@dm01db01 ~]# dmidecode -t memory|grep Maximum|sort|uniq
        Maximum Capacity: 192 GB
[root@dm01db01 ~]#

服务器上每个CPU支持的一对内存模块最多内存插槽为6个：

[root@dm01db01 ~]# dmidecode -t memory|grep Number|sort|uniq
        Number Of Devices: 6
        Part Number: M386A4G40DM0-CPB   
        Part Number: NO DIMM
        Serial Number: 02718F69
        Serial Number: 027190B4
        Serial Number: 02719149
        Serial Number: 02719157
        Serial Number: 02719160
        Serial Number: 027AA606
        Serial Number: 027AA60D
        Serial Number: 027AA611
        Serial Number: NO DIMM
[root@dm01db01 ~]#

24*32=768GB，也就是当前系统最大可以插入24根32GB的内存条，也就是最多支持768GB内存：

[root@dm01db01 ~]# dmidecode -t memory|grep "Memory Device"|wc -l
24
[root@dm01db01 ~]#

内存频率是2133：

[root@dm01db01 ~]# dmidecode -t memory|grep Speed|sort|uniq
        Configured Clock Speed: 2133 MHz
        Configured Clock Speed: Unknown
        Speed: 2133 MHz
        Speed: Unknown
[root@dm01db01 ~]#

全部内存信息如下：

[root@dm01db01 ~]# dmidecode -t memory
# dmidecode 2.12
SMBIOS 2.8 present.

Handle 0x0031, DMI type 16, 23 bytes
Physical Memory Array
        Location: System Board Or Motherboard
        Use: System Memory
        Error Correction Type: Multi-bit ECC
        Maximum Capacity: 192 GB      ------------------说明参见下面
        Error Information Handle: Not Provided
        Number Of Devices: 6     -----------说明参见下面
        
还是对比着上面的图，这里面我们计算一下：
LRDIMM是较新一代的内存设备，能够保证即使在单条内存32GB容量甚至更高的情况下，服务器上每个内存通道依然能够布满至3条内存条
.
X5-2数据库服务器一共两个物理CPU，每颗CPU左右两侧各有2组内存模块（每组模块上可以最多插入3个内存条），共8组内存模块
每个CPU支持的一对内存模块，每个模块可以最多插入3根内存条，因此每个CPU支持最多插槽数为6，因此，每个CPU支持最多6*32GB=192GB
.
一共应该有8个内存模块(P0：CHA,CHB,CHC,CHD   P1：CHA,CHB,CHC,CHD)，每个内存模块上可以插入3根内存条
因此总共可以插入24根内存条（参见上面的“dmidecode -t memory|grep "Memory Device"|wc -l”）
且服务器最大支持24*32DB=768GB内存
.
															
当前已经插入的内存条，32GB：					
Handle 0x0033, DMI type 17, 34 bytes
Memory Device
        Array Handle: 0x0031
        Error Information Handle: Not Provided
        Total Width: 72 bits
        Data Width: 64 bits
        Size: 32 GB          -------------当前已经插入的内存条，32GB
        Form Factor: DIMM
        Set: None
        Locator: D11
        Bank Locator: SYS/MB/P0
        Type: Other
        Type Detail: Synchronous
        Speed: 2133 MHz
        Manufacturer: Samsung
        Serial Number: 02719157
        Asset Tag: DIMM_A1_AssetTag
        Part Number: M386A4G40DM0-CPB   
        Rank: 4
        Configured Clock Speed: 2133 MHz
        
没插入内存条的空槽信息如下：
Handle 0x0044, DMI type 17, 34 bytes
Memory Device
        Array Handle: 0x003B
        Error Information Handle: Not Provided
        Total Width: Unknown
        Data Width: Unknown
        Size: No Module Installed         -------------当前空着的槽位
        Form Factor: DIMM
        Set: None
        Locator: D5
        Bank Locator: SYS/MB/P0
        Type: Other
        Type Detail: Synchronous
        Speed: Unknown
        Manufacturer: NO DIMM
        Serial Number: NO DIMM
        Asset Tag: NO DIMM
        Part Number: NO DIMM
        Rank: Unknown
        Configured Clock Speed: Unknown

。。。。。。。。。。。。
类似信息不赘述了

具体更换步骤参见《Oracle Exadata Database Machine Maintenance Guide》
Adding Memory Expansion Kit to Database Servers

Exadata X5-2服务器上扩内存或者更换内存的详细步骤

发表在安装和升级 | 标签为 dimm, dmidecode, exadata X5 | 留下评论

SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report

发表于 2015 年 5 月 5 日由 Lunar

SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set
SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report

1，查看当前STS中的SQL数量：

09:52:42 LUNAR@ lunardb> select count(*) from LUNAR.SQLSET_TAB_LUNAR;

  COUNT(*)
----------
    496641

Elapsed: 00:00:00.24
09:53:13 LUNAR@ lunardb>

删除一些没用的：

10:04:15 LUNAR@ lunardb> delete from LUNAR.SQLSET_TAB_LUNAR
10:07:33   2  where (PARSING_SCHEMA_NAME  in ('LUNAR', 'GGUSR','EXFSYS','SYS') )
10:07:33   3  or ( module  in ('PL/SQL Developer','SQL*Plus','sqlplus.exe','plsqldev.exe','DBMS_SCHEDULER') );

701 rows deleted.

Elapsed: 00:00:00.96
10:07:34 LUNAR@ lunardb> commit;

Commit complete.

Elapsed: 00:00:00.00
10:07:38 LUNAR@ lunardb>

2，在新库创建Lunar_11201STS_LUNAR SQLSET集

create user LUNARSPA identified by LUNARSPA;
grant connect,resource,dba to LUNARSPA;

10:24:41 LUNAR@ lunardb> 
10:24:41   2  from dba_users
10:24:41   3  where username in ('LUNAR','LUNARSPA')
10:24:41   4  order by 1,2;

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
LUNAR                          USERS                          TEMP
LUNARSPA                       USERS                          TEMP

Elapsed: 00:00:00.03
10:24:42 LUNAR@ lunardb>

—(2)使用LUNAR用户，创建STS：Lunar_11204STS_LUNAR

10:24:42 LUNAR@ lunardb> conn LUNARSPA/LUNARSPA
Connected.
10:25:33 LUNARSPA@ lunardb> exec DBMS_SQLTUNE.create_sqlset(sqlset_name => 'Lunar_11204STS_LUNAR');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
10:25:40 LUNARSPA@ lunardb>

—(2)使用LUNARSPA用户，将源库的LUNAR.Lunar_11201STS_LUNAR的SQL优化器映射到LUNARSPA.Lunar_11204STS_LUNAR

10:28:24 LUNARSPA@ lunardb> select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME                           OWNER                          CREATED             STATEMENT_COUNT
------------------------------ ------------------------------ ------------------- ---------------
Lunar_11204STS_LUNAR             LUNARSPA                       2015-04-19 10:25:40               0

Elapsed: 00:00:00.00


10:40:44 LUNARSPA@ lunardb> exec dbms_sqltune.remap_stgtab_sqlset(old_sqlset_name =>'Lunar_11201STS_LUNAR',old_sqlset_owner => 'LUNAR', new_sqlset_name => 'Lunar_11204STS_LUNAR',new_sqlset_owner => 'LUNARSPA', staging_table_name => 'SQLSET_TAB_LUNAR',staging_schema_owner => 'LUNAR');

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.39
10:41:06 LUNARSPA@ lunardb>

使用LUNARSPA用户执行remap：

BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(
sqlset_name => 'Lunar_11201STS_LUNAR',
sqlset_owner => 'SPA',
replace => TRUE,
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'SPA');
END;
/

11:21:16 LUNARSPA@ lunardb> select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME                           OWNER                          CREATED             STATEMENT_COUNT
------------------------------ ------------------------------ ------------------- ---------------
Lunar_11204STS_LUNAR             LUNARSPA                       2015-04-19 11:19:04            6005

Elapsed: 00:00:00.01
11:21:19 LUNARSPA@ lunardb>

至此，SPA在新库的数据已经准备完毕，可以开始生成SPA报告了。
常见报告的就提步骤如下：
1）创建SPA任务

11:33:10 LUNARSPA@ lunardb> exec :sname := 'Lunar_11204STS_GPS';
exec :tname := 'SPA_LUNARTEST1';
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
11:33:10 LUNARSPA@ lunardb> 

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
11:33:10 LUNARSPA@ lunardb> exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19
11:33:10 LUNARSPA@ lunardb>

2）生成11.2.0.1的SPA Trail,采用STS转化方式

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_LUNARTEST1',
execution_type => 'CONVERT SQLSET',
execution_name => 'CONVERT_11204G');
end;
/

3）在11.2.0.4中测试执行，从性能数据生成SPA trial
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_LUNARTEST1',
execution_type => 'TEST EXECUTE',
execution_name => 'EXEC_11204G');
end;
/

5 执行比较任务(一般取Elapsed Time、CPU Time、Buffer Get等指标)

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_LUNARTEST1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_11204G', 'execution_name2', 'EXEC_11204G', 'comparison_metric', 'elapsed_time') );
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_LUNARTEST1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_CPU_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_11204G', 'execution_name2', 'EXEC_11204G', 'comparison_metric', 'CPU_TIME') );
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_LUNARTEST1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_BUFFER_GETS_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_11204G', 'execution_name2', 'EXEC_11204G', 'comparison_metric', 'BUFFER_GETS') );
end;
/

6 生成SPA报告

spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time',top_sql=>500) FROM dual;
spool off;

spool spa_report_CPU_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'ALL','ALL', execution_name=>'Compare_CPU_time',top_sql=>500) FROM dual;
spool off;


spool spa_report_buffer_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1','HTML','ALL','ALL', execution_name=>'Compare_BUFFER_GETS_time',top_sql=>500) FROM dual;
spool off;

spool spa_report_elapsed_time_regressed.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'REGRESSED','ALL', execution_name=>'Compare_elapsed_time',top_sql=>500) FROM dual;
spool off;

spool spa_report_CPU_time_regressed.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'REGRESSED','ALL', execution_name=>'Compare_CPU_time',top_sql=>500) FROM dual;
spool off;


spool spa_report_buffer_time_regressed.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1','HTML','REGRESSED','ALL', execution_name=>'Compare_BUFFER_GETS_time',top_sql=>500) FROM dual;
spool off;


spool spa_report_errors.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'errors','summary') FROM dual;
spool off;


spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task('SPA_LUNARTEST1', 'HTML', 'unsupported','all') FROM dual;
spool off;

生成的报告一般如下：

-rwxrwxrwx 1 oracle oracle     1850 Apr 19 21:33 report_spa.sh
-rw-rw-r-- 1 oracle oracle  8498134 Apr 19 21:37 spa_report_elapsed_time.html
-rw-rw-r-- 1 oracle oracle  8954773 Apr 19 21:41 spa_report_CPU_time.html
-rw-rw-r-- 1 oracle oracle  7941640 Apr 19 21:44 spa_report_buffer_time.html
-rw-rw-r-- 1 oracle oracle    38933 Apr 19 21:44 spa_report_elapsed_time_regressed.html
-rw-rw-r-- 1 oracle oracle    61982 Apr 19 21:44 spa_report_CPU_time_regressed.html
-rw-rw-r-- 1 oracle oracle    28886 Apr 19 21:44 spa_report_buffer_time_regressed.html
-rw-rw-r-- 1 oracle oracle    15537 Apr 19 21:44 spa_report_errors.html
-rw-rw-r-- 1 oracle oracle    58703 Apr 19 21:44 spa_report_unsupport.html
-rw-rw-r-- 1 oracle oracle 18608938 Apr 19 21:44 report_spa.log
[oracle@lunardb tmp]$

发表在 Performence Tuning | 留下评论

SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set

发表于 2015 年 5 月 5 日由 Lunar

SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set
SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report
.
SPA(SQL Performance Analyzer , SQL 性能分析器)，是11g引入的新功能，主要用于预测潜在的更改对 SQL 查询工作量的性能影响。
一般有几种情况下，我们会建议做SPA:
1，OS版本发生变化
2，硬件发生变化
3，数据库版本的升级
4，实施某些优化建议
5, 收集统计信息
6，更改数据库参数
等等
.
SPA的主要实施步骤如下：
1, 在生产系统上捕捉SQL负载，并生成SQL Tuning Set；
2, 创建一个中转表，将SQL Tuning Set导入到中转表，导出中转表并传输到测试库；
3, 导入中转表，并解压中转表的数据到SQL Tuning Set；
4, 创建SPA任务，先生成10g的trail，然后在11g中再生成11g的trail；
5, 执行比较任务，再生成SPA报告；
6, 分析性能退化的SQL语句；
.
我这里的例子是，将一根数据库从10.2.0.1升级到11.2.0.4.
1，在源库创建spa用户：

create user LUNAR identified by LUNAR;
grant connect,resource,dba to LUNAR;

10:38:37 lunar@LUNAR>select username,default_tablespace,temporary_tablespace
10:41:41   2  from dba_users
10:41:41   3  where username in ('LUNAR','SPA')
10:41:41   4  order by 1,2;

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
LUNAR                          USERS                          TEMP

Elapsed: 00:00:00.27
10:41:41 lunar@LUNAR>

2，检查SYSAUX空间是否足够

09:26:38 sys@LUNAR>@ts

Name                           TS Type        All Size   Max Size  Free Size   Max Free Pct. Free Max Free%
------------------------------ ------------ ---------- ---------- ---------- ---------- --------- ---------
UNDOTBS1                       UNDO            148,433    221,521     19,467     92,555        13        42
LUNAR_IDX                        PERMANENT       352,256    352,256     84,272     84,272        24        24
LUNAR_DAT                        PERMANENT     1,048,576  1,048,576    258,728    258,728        25        25
LUNAR_TESTS                       PERMANENT       251,904    251,904    139,424    139,424        55        55
LUNAR_TESTS_IDX                   PERMANENT       329,728    329,728    196,351    196,351        60        60
USERS                          PERMANENT         4,096     32,768      2,582     31,254        63        95
SYSAUX                         PERMANENT         4,096     32,768      2,786     31,458        68        96
SYSTEM                         PERMANENT         4,096     32,768      2,882     31,554        70        96

8 rows selected.

Elapsed: 00:00:00.07
09:26:40 sys@LUNAR>

3，创建SQL优化器：

conn LUNAR/LUNAR
10:33:30 lunar@LUNAR>exec dbms_sqltune.create_sqlset('Lunar_11201STS_LUNAR');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11
10:34:25 lunar@LUNAR>

4，往SQL优化其中，加载优化集

1). 从AWR快照中加载
11:31:55 lunar@LUNAR>select INSTANCE_NUMBER ,min(snap_id),max(snap_id) from dba_hist_snapshot group by INSTANCE_NUMBER;

INSTANCE_NUMBER MIN(SNAP_ID) MAX(SNAP_ID)
--------------- ------------ ------------
              1        19355        19555

Elapsed: 00:00:00.01
11:32:12 lunar@LUNAR>

b).加载2个快照之间的所有查询（这一步大概执行了4分钟）

11:33:12 lunar@LUNAR>declare
11:33:14   2  own VARCHAR2(30) := 'LUNAR';
11:33:14   3  bid NUMBER := '&begin_snap';
11:33:14   4  eid NUMBER := '&end_snap';
11:33:14   5  stsname VARCHAR2(30) :='Lunar_11201STS_LUNAR';
11:33:14   6  sts_cur dbms_sqltune.sqlset_cursor;
11:33:14   7  begin
11:33:14   8  open sts_cur for
11:33:14   9  select value(P) from table(dbms_sqltune.select_workload_repository(bid,eid, null, null, null, null, null, 1, null, 'ALL')) P;
11:33:14  10  dbms_sqltune.load_sqlset(sqlset_name => stsname,populate_cursor => sts_cur,load_option => 'MERGE');
11:33:14  11  end;
11:33:14  12  /
Enter value for begin_snap: 19355
old   3: bid NUMBER := '&begin_snap';
new   3: bid NUMBER := '19355';
Enter value for end_snap: 19555
old   4: eid NUMBER := '&end_snap';
new   4: eid NUMBER := '19555';

PL/SQL procedure successfully completed.

Elapsed: 00:03:07.05
11:36:29 lunar@LUNAR>

c) 验证创建的SQL优化集

10:52:58 lunar@LUNAR>select NAME,OWNER,CREATED,STATEMENT_COUNT, LAST_MODIFIED FROM DBA_SQLSET;

NAME                           OWNER                          CREATED             STATEMENT_COUNT LAST_MODIFIED
------------------------------ ------------------------------ ------------------- --------------- -------------------
Lunar_11201STS_LUNAR                  LUNAR                          2015-04-18 10:34:25             921 2015-04-18 10:38:27

Elapsed: 00:00:00.06
10:53:03 lunar@LUNAR>

2). 如果需要，可以从AWR快照中加载指定sql_id和plan_hash_value的sql语句

12:06:31 lunar@LUNAR>SELECT sql_id, substr(sql_text, 1, 50) sql
12:06:32   2  FROM TABLE( DBMS_SQLTUNE.select_sqlset ('Lunar_11201STS_LUNAR')) 
12:06:32   3  where sql_id in ('34xbj7bv7suyk','gxsfh4gm276d3'); 

SQL_ID        SQL
------------- --------------------------------------------------
34xbj7bv7suyk UPDATE "LUNAR_PRD".MDRT_1472A$ set info= :1 where ro
gxsfh4gm276d3 update LUNARINFO t set TIME=:1, LUNARMARK=:2, LO

Elapsed: 00:00:01.14
12:06:34 lunar@LUNAR>

3). 从当前游标缓存中加载

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value(P) 
    FROM TABLE(dbms_sqltune.select_cursor_cache('parsing_schema_name <> ''SYS''',NULL,NULL,NULL,NULL,1,NULL,'ALL')) p;
    dbms_sqltune.load_sqlset('Lunar_11201STS_LUNAR', cur); 
  CLOSE cur;
END;
/

上述过程一般执行时间比较长，因此，通常放到后台执行。
这里我们看到加载的SQL明显增加了很多：

12:55:02 sys@LUNAR>select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME                           OWNER                          CREATED             STATEMENT_COUNT
------------------------------ ------------------------------ ------------------- ---------------
Lunar_11201STS_LUNAR             LUNAR                          2015-04-18 11:31:55           41928

12:57:11 sys@LUNAR>

执行完上述所有操作后，我们就可以将这个SQL TUNING SET迁移到新的环境，进行分析，具体过程如下：
1，在新库中创建SQL优化器用户

create user LUNAR identified by LUNAR;
grant connect,resource,dba to LUNAR;

2，检查SYSAUX空间是否足够

3，在源库上执行打包SQL TUNING SET的操作，然后exp/imp到新库上

[oracle@lunardb tmp]$ ss

SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 18 23:22:26 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

23:22:26 sys@GPS>conn LUNAR/LUNAR
Connected.
23:22:28 lunar@GPS>BEGIN
23:22:33   2  DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB_LUNAR',
23:22:34   3  schema_name => 'LUNAR',
23:22:34   4  tablespace_name => 'USERS');
23:22:34   5  END;
23:22:34   6  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.32
23:22:36 lunar@GPS>

3，打包SQL TUNING SET的操作，然后exp/imp到新库上

conn LUNAR/LUNAR

BEGIN
 DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'Lunar_11201STS_GPS',
 sqlset_owner => 'LUNAR',
 staging_table_name => 'SQLSET_TAB_LUNAR',
 staging_schema_owner => 'LUNAR');
 END;
/

执行过程中，我们可以监控一下：

[oracle@lunardb tmp]$ ss

SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 18 23:26:18 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

23:26:18 sys@GPS>select count(*) from LUNAR.SQLSET_TAB_LUNAR;

  COUNT(*)
----------
    496641

Elapsed: 00:00:00.57
23:28:04 sys@GPS>

exp LUNAR/LUNAR tables=SQLSET_TAB_LUNAR file=/u01/oradata/tmp/exp_SQLSET_TAB_LUNAR.dmp log=/u01/oradata/tmp/exp_SQLSET_TAB_LUNAR.log FEEDBACK=1000 BUFFER=5000000

4，在新库上执行导入SQL TUNING SET的表（LUNAR.SQLSET_TAB_LUNAR）
imp LUNAR/LUNAR fromuser=LUNAR touser=LUNAR file=/u01/oradata/tmp/exp_SQLSET_TAB_LUNAR.dmp feedback=1000 log=/u01/oradata/tmp/imp_SQLSET_TAB_LUNAR.log BUFFER=5000000

发表在 Performence Tuning | 标签为 SPA | Comments Off

11.2单机数据库转换为RAC-2-手工方式（与9i，10g，11.1的方法一样）

发表于 2015 年 5 月 4 日由 Lunar

单机数据库转换为RAC，方法很多，前面的已经讲过使用rconfig的方法（11.2以后Oracle推荐的方法）。
但是我还是喜欢手工转换，感觉还是简单省事的，整个过程15分钟左右（主要是启动和关闭数据库的时间）：
首先将单机数据库的pfile修改为RAC的pfile，具体如下：

*.audit_file_dest='/u01/app/oracle/admin/lunar/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.3.0'
*.control_files='+DATADG/lunar/control01.ctl','+DATADG/lunar/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=2000
*.db_name='lunar'
lunar1.instance_number=1
lunar2.instance_number=2
*.db_recovery_file_dest='+RECODG'
*.db_recovery_file_dest_size=536870912000
*.db_unique_name='lunar'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=lunarXDB)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lunar'
*.log_archive_dest_state_1='ENABLE'
*.open_cursors=200
*.pga_aggregate_target=7059552256
lunar1.local_listener=LISTENER_RAC1
lunar2.local_listener=LISTENER_RAC2
*.processes=2400
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.session_cached_cursors=200
*.sga_target=0
*.standby_file_management='AUTO'
lunar1.undo_tablespace='UNDOTBS1'
lunar2.undo_tablespace='UNDOTBS2'
*.undo_tablespace='UNDOTBS1'
*.db_cache_size=3g 
*.shared_pool_size=3g
*.streams_pool_size=128m
lunar1.instance_name=lunar1
lunar2.instance_name=lunar2
*.java_pool_size=200m
*.log_buffer=67108864
*.job_queue_processes=20
*.cluster_database=true
*.cluster_database_instances=2
*.undo_management=AUTO
*.db_create_file_dest='+DATADG'
*.db_create_online_log_dest_1='+RECODG'
lunar1.thread=1
lunar2.thread=2

然后使用这个pfile启动数据库：

08:26:59 @>startup pfile=/home/oracle/lunar/spfile.lunar.tmp
ORACLE instance started.

Total System Global Area       6881869824 bytes
Fixed Size                        2266064 bytes
Variable Size                  3573550128 bytes
Database Buffers               3221225472 bytes
Redo Buffers                     84828160 bytes
Database mounted.
Database opened.
08:27:30 @>

然后添加thread 2的redo log group：

08:27:30 @>alter database add logfile thread 2
08:28:16   2    group 17  ('+RECODG') size 1024m,
08:28:16   3    group 18  ('+RECODG') size 1024m,
08:28:16   4    group 19  ('+RECODG') size 1024m,
        group 20  ('+RECODG') size 1024m,
08:28:16   5  08:28:16   6      group 21  ('+RECODG') size 1024m,
08:28:16   7    group 22  ('+RECODG') size 1024m,
        group 23  ('+RECODG') size 1024m,
08:28:16   8  08:28:16   9      group 24  ('+RECODG') size 1024m,
        group 25  ('+RECODG') size 1024m,
08:28:16  10  08:28:16  11      group 26  ('+RECODG') size 1024m,
08:28:16  12    group 27  ('+RECODG') size 1024m,
        group 28  ('+RECODG') size 1024m,
08:28:16  13  08:28:16  14      group 29  ('+RECODG') size 1024m,
08:28:16  15    group 30  ('+RECODG') size 1024m,
08:28:16  16    group 31  ('+RECODG') size 1024m,
08:28:16  17    group 32  ('+RECODG') size 1024m
;08:28:16  18  

Database altered.

Elapsed: 00:00:28.51
08:28:46 @>

创建thread 2使用的UNDO TABLESPACE：

08:28:46 @>CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATADG' SIZE 10480m ;        

Tablespace created.

Elapsed: 00:00:09.87
08:29:11 @>

然后enable这个thread 2：

08:29:11 @>alter database enable public thread 2; 

Database altered.

Elapsed: 00:00:00.59
08:29:29 @>

最后，执行创建RAC的一些必要试图的脚本：
@?/rdbms/admin/catclust.sql
有些人说这个可以不执行，也有很多GV$的视图，但是实际上这个脚本是必须执行的，否则会缺少一部分GI需要视图
比如V$BH等等，具体可以查看catclust.sql脚本
都做完以后，就可以启动数据库了：

[oracle@dm01db02 ~]$ ss

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 3 08:47:51 2015

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

Connected to an idle instance.

SYS@lunar2>startup
ORACLE instance started.

Total System Global Area       6881869824 bytes
Fixed Size                        2266064 bytes
Variable Size                  3573550128 bytes
Database Buffers               3221225472 bytes
Redo Buffers                     84828160 bytes
Database mounted.
Database opened.
SYS@lunar2>select * from v$active_instances;

     INST_NUMBER INST_NAME
---------------- ------------------------------------------------------------------------------------------------------------------------
               1 lunar1.lunar.com:lunar1
               2 lunar2.lunar.com:lunar2

Elapsed: 00:00:00.00
SYS@lunar2>

这里看到已经是RAC数据库了，RAC中最好使用spfile文件，因此我们也创建spfile，例如：

SYS@lunar2>show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATADG/lunar/parameterfile/spf
                                                 ile.3296.878718931
SYS@lunar2>

然后把这数据库加入到CRS，就可以由CRS来管理了：

[oracle@dm01db01 ~]$ srvctl add database -d lunar -o /u01/app/oracle/product/11.2.0.4/dbhome_1
[oracle@dm01db01 ~]$ srvctl add instance -d lunar -n dm01db01 -i lunar1
[oracle@dm01db01 ~]$ srvctl add instance -d lunar -n dm01db02 -i lunar2
[oracle@dm01db01 ~]$ srvctl modify database -d lunar -n lunar
[oracle@dm01db01 ~]$ srvctl config database -d lunar
Database unique name: lunar
Database name: lunar
Oracle home: /u01/app/oracle/product/11.2.0.4/dbhome_1
Oracle user: oracle
Spfile: +DATADG/lunar/parameterfile/spfile.3296.878718931
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: lunar
Database instances: lunar1,lunar2
Disk Groups: DATADG,RECODG
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed
[oracle@dm01db01 ~]$

发表在 RAC | 留下评论

11.2单机数据库转换为RAC-1-使用rconfig完成转换

发表于 2015 年 5 月 4 日由 Lunar

将单机数据库转换为RAC数据库，有好几种方法：
1，使用DBCA
2，手工添加thread（这个方法最经典，从9i到10g，到11.1，到11.2都可以，不过11.2以后，Oracle不推荐使用这个方法）
3，使用rconfig
4，ODA上有专门的工具进行转换，号称无敌脚本，O(∩_∩)O哈哈~（本次我没有测试，因为上面的方法已经足够多选择了）
.
rconfig功能还是比较强大的，除了单机转换为RAC，还可以单机转换为Standalone，Standalone转换为RAC，RAC转换为Standalone等等。
.
这里我们使用Oracle 11.2推荐的方法，体验一下rconfig的强大之处。
首先，我们要从一个单机数据库备份，然后恢复到到RAC环境中：

backup database filesperset = 2  format '/home/oracle/backup/db_full_%U';
sql 'alter system archive log current';
backup archivelog from time 'sysdate-1' filesperset = 2  format '/home/oracle/backup/arch_bak_%U';
backup current controlfile format '/home/oracle/backup/ctl_%U';

然后将这个数据库恢复到已经安装了GI的环境中，就是普通的将数据文件恢复到ASM的操作，没什么难度，主要命令如下：

startup nomount pfile=xxxxx
restore controlfile from 'xxxxxxx';
set newname for database to '+DATADG1';
restore database;
switch datafile all;
catalog backuppiece '/home/oracle/test/arch_bak_22q2t3co_1_1';
catalog backuppiece '/home/oracle/test/arch_bak_21q2t3co_1_1';
recover database using backup controlfile until cancel;
ALTER DATABASE OPEN RESETLOGS;

.
在ORACLE_HOME下有一个xml脚本，通常我们只需要修改这个脚本，就可以完成转换：
即： $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml
1，我们先复制一个过来，进行修改：

$su – oracle
cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml lunar.xml

其中，Convert verify=”ONLY”表示只测试转换过程，不实际转换。
Convert verify=”YES”，表示真实的执行转换。
测试过程这里略过了，我们看一年他是如何转换的。
我这里xml的内容如下：

[oracle@lunar1 test]$ cat lunar.xml
<?xml version="1.0" encoding="UTF-8"?>
<n:RConfig xmlns:n="http://www.oracle.com/rconfig"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://www.oracle.com/rconfig rconfig.xsd">
    <n:ConvertToRAC>   
<!-- Verify does a precheck to ensure all pre-requisites are met, before the conversion is attempted. Allowable values are: YES|NO|ONLY -->
        <n:Convert verify="YES">
<!--Specify current OracleHome of non-rac database for SourceDBHome -->
              <n:SourceDBHome>/u01/app/oracle/product/11.2.0/db_1</n:SourceDBHome>
<!--Specify OracleHome where the rac database should be configured. It can be same as SourceDBHome -->
              <n:TargetDBHome>/u01/app/oracle/product/11.2.0/db_1</n:TargetDBHome>
<!--Specify SID of non-rac database and credential. User with sysdba role is required to perform conversion -->
              <n:SourceDBInfo SID="lunar1">
                <n:Credentials>
                  <n:User>sys</n:User>
                  <n:Password>oracle</n:Password>
                  <n:Role>sysdba</n:Role>
                </n:Credentials>
              </n:SourceDBInfo>
<!--Specify the list of nodes that should have rac instances running for the Admin Managed Cluster Database. LocalNode should be the first node in this nodelist. -->
              <n:NodeList>        
                <n:Node name="lunar1"/>
                <n:Node name="lunar2"/>
              </n:NodeList>
<!--Specify RacOneNode along with servicename to convert database to RACOne Node -->
              <!--n:RacOneNode  servicename="saleslunar1service"/-->
<!--Instance Prefix tag is optional starting with 11.2. If left empty, it is derived from db_unique_name.-->
              <n:InstancePrefix>lunar</n:InstancePrefix>
<!-- Listener details are no longer needed starting 11.2. Database is registered with default listener and SCAN listener running from Oracle Grid Infrastructure home. -->
<!--Specify the type of storage to be used by rac database. Allowable values are CFS|ASM. The non-rac database should have same storage type. ASM credentials are no needed for conversion. -->
              <n:SharedStorage type="ASM">
<!--Specify Database Area Location to be configured for rac database.If this field is left empty, current storage will be used for rac database. For CFS, this field will have directory path. -->
                <n:TargetDatabaseArea>+DATADG1</n:TargetDatabaseArea>
<!--Specify Fast Recovery Area to be configured for rac database. If this field is left empty, current recovery area of non-rac database will be configured for rac database. If current database is not using recovery Area, the resulting rac database will not have a recovery area. -->
                <n:TargetFlashRecoveryArea>+DATADG1</n:TargetFlashRecoveryArea>
              </n:SharedStorage>
        </n:Convert>
    </n:ConvertToRAC>
</n:RConfig>
[oracle@lunar1 test]$

具体的转换过程如下：

[oracle@lunar1 test]$ rconfig lunar.xml
Converting Database "lunar" to Cluster Database. Target Oracle Home: /u01/app/oracle/product/11.2.0/db_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Adding Trace files
Setting Fast Recovery Area
Updating Oratab
Creating Password file(s)
Configuring Listeners
Configuring related CRS resources
Starting Cluster Database
<?xml version="1.0" ?>
<RConfig version="1.1" >
<ConvertToRAC>
    <Convert>
      <Response>
        <Result code="0" >
          Operation Succeeded
        </Result>
      </Response>
      <ReturnValue type="object">
<Oracle_Home>
         /u01/app/oracle/product/11.2.0/db_1
       </Oracle_Home>
       <Database type="ADMIN_MANAGED"  >
         <InstanceList>
           <Instance SID="lunar1" Node="lunar1"  >
           </Instance>
           <Instance SID="lunar2" Node="lunar2"  >
           </Instance>
         </InstanceList>
       </Database>     </ReturnValue>
    </Convert>
  </ConvertToRAC></RConfig>
[oracle@lunar1 test]$

从这里我们清晰的看到，rconfig进行转换的时候，主要执行了如下步骤：

Setting Data Files and Control Files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Adding Trace files
Setting Fast Recovery Area
Updating Oratab
Creating Password file(s)
Configuring Listeners
Configuring related CRS resources
Starting Cluster Database

转换结果如下，lunar数据库已经是RAC数据库了，且已经在两个节点都启动了：

[root@lunar2 ~]# crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADG1.dg
               ONLINE  ONLINE       lunar1                                         
               ONLINE  ONLINE       lunar2                                         
ora.DATADG2.dg
               ONLINE  ONLINE       lunar1                                         
               ONLINE  ONLINE       lunar2                                         
ora.LISTENER.lsnr
               ONLINE  ONLINE       lunar1                                         
               ONLINE  ONLINE       lunar2                                         
ora.asm
               ONLINE  ONLINE       lunar1                     Started             
               ONLINE  ONLINE       lunar2                     Started             
ora.gsd
               OFFLINE OFFLINE      lunar1                                         
               OFFLINE OFFLINE      lunar2                                         
ora.net1.network
               ONLINE  ONLINE       lunar1                                         
               ONLINE  ONLINE       lunar2                                         
ora.ons
               ONLINE  INTERMEDIATE lunar1                     CHECK TIMED OUT     
               ONLINE  INTERMEDIATE lunar2                     CHECK TIMED OUT     
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       lunar2                                         
ora.cvu
      1        ONLINE  ONLINE       lunar2                                         
ora.lunar.db
      1        ONLINE  ONLINE       lunar1                     Open                
      2        ONLINE  ONLINE       lunar2                     Open                
ora.oc4j
      1        ONLINE  ONLINE       lunar1                                         
ora.lunar1.vip
      1        ONLINE  ONLINE       lunar1                                         
ora.lunar2.vip
      1        ONLINE  ONLINE       lunar2                                         
ora.scan1.vip
      1        ONLINE  ONLINE       lunar2                                         
[root@lunar2 ~]#

但是我个人并不喜欢rconfig的操作，原因是，他要进行backup as copy database的操作。
那么，就意味着，如果数据库巨大的话，需要的空间也会是双倍的
但是用传统的方法，手工添加第二个实例就不会有这个需求，比如我的数据库30T，怎么弄？

。。。。。。。。。。。。。。。。。。。
[Thread-81] [ 2013-06-24 08:12:56.090 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> backup as copy database to destination  '+ASMDATA';
[Thread-81] [ 2013-06-24 08:12:56.173 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Starting backup at 24-JUN-13
[Thread-81] [ 2013-06-24 08:12:56.793 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=allocated channel: ORA_DISK_1
[Thread-81] [ 2013-06-24 08:12:56.802 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: SID=19 instance=lunar1 device type=DISK
[Thread-81] [ 2013-06-24 08:12:59.169 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[Thread-81] [ 2013-06-24 08:12:59.430 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=input datafile file number=00004 name=+ASMDATA/lunar/datafile/users.316.818912513
[Thread-81] [ 2013-06-24 08:15:24.683 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=output file name=+ASMDATA/lunar/datafile/users.322.818928779 tag=TAG20130624T081258 RECID=1 STAMP=818928915
[Thread-81] [ 2013-06-24 08:15:24.689 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:25
[Thread-81] [ 2013-06-24 08:15:24.974 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[Thread-81] [ 2013-06-24 08:15:25.070 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=input datafile file number=00001 name=+ASMDATA/lunar/datafile/system.317.818912513
[Thread-81] [ 2013-06-24 08:16:10.130 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=output file name=+ASMDATA/lunar/datafile/system.323.818928925 tag=TAG20130624T081258 RECID=2 STAMP=818928965
[Thread-81] [ 2013-06-24 08:16:10.137 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
[Thread-81] [ 2013-06-24 08:16:10.694 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[Thread-81] [ 2013-06-24 08:16:11.100 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=input datafile file number=00002 name=+ASMDATA/lunar/datafile/sysaux.318.818912517
[Thread-81] [ 2013-06-24 08:16:36.203 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=output file name=+ASMDATA/lunar/datafile/sysaux.324.818928971 tag=TAG20130624T081258 RECID=3 STAMP=818928995
[Thread-81] [ 2013-06-24 08:16:36.210 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
[Thread-81] [ 2013-06-24 08:16:36.935 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: starting datafile copy
[Thread-81] [ 2013-06-24 08:16:37.311 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=input datafile file number=00003 name=+ASMDATA/lunar/datafile/undotbs1.319.818912523
[Thread-81] [ 2013-06-24 08:16:52.443 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=output file name=+ASMDATA/lunar/datafile/undotbs1.325.818928997 tag=TAG20130624T081258 RECID=4 STAMP=818929011
[Thread-81] [ 2013-06-24 08:16:52.445 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
[Thread-81] [ 2013-06-24 08:16:52.458 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Finished backup at 24-JUN-13
[Thread-81] [ 2013-06-24 08:16:56.649 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-81] [ 2013-06-24 08:16:56.691 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-81] [ 2013-06-24 08:16:56.712 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> set echo on
[Thread-81] [ 2013-06-24 08:16:56.715 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-81] [ 2013-06-24 08:16:56.718 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=echo set on
[Thread-81] [ 2013-06-24 08:16:56.718 CST ] [RMANEngine.readSqlOutput:945]  Notifying writer to proceed because m_bInterrupted=falsebNotify=truem_bReaderDone=falsem_bFatalErrorOccured=false
[Thread-81] [ 2013-06-24 08:16:56.719 CST ] [RMANEngine.run:729]  Return Value from readSqlOutput=echo set on
[main] [ 2013-06-24 08:16:56.722 CST ] [RMANEngine.executeImpl:1146]  Completed wait from reader
[main] [ 2013-06-24 08:16:56.723 CST ] [RMANEngine.executeImpl:1193]  DONE EXECUTING GIVEN COMMAND=backup as copy database to destination  '+ASMDATA';
[main] [ 2013-06-24 08:16:56.727 CST ] [RMANEngine.executeImpl:1098]  m_bExecQuery=false
[main] [ 2013-06-24 08:16:56.730 CST ] [RMANEngine.executeImpl:1106]  Command being written to rman process=backup as backupset reuse spfile format  '+ASMDATA/sp_lunar1_backup';
[main] [ 2013-06-24 08:16:56.733 CST ] [RMANEngine.executeImpl:1134]  Notify reader to start reading
[Thread-81] [ 2013-06-24 08:16:56.735 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-81] [ 2013-06-24 08:16:56.932 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> backup as backupset reuse spfile format  '+ASMDATA/sp_lunar1_backup';
[Thread-81] [ 2013-06-24 08:16:56.994 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Starting backup at 24-JUN-13
[Thread-81] [ 2013-06-24 08:16:57.278 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=using channel ORA_DISK_1
[Thread-81] [ 2013-06-24 08:16:57.782 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: starting full datafile backup set
[Thread-81] [ 2013-06-24 08:16:57.785 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: specifying datafile(s) in backup set
[Thread-81] [ 2013-06-24 08:16:57.849 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=including current SPFILE in backup set
[Thread-81] [ 2013-06-24 08:16:57.853 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: starting piece 1 at 24-JUN-13
[Thread-81] [ 2013-06-24 08:16:58.869 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: finished piece 1 at 24-JUN-13
[Thread-81] [ 2013-06-24 08:16:58.870 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=piece handle=+ASMDATA/sp_lunar1_backup tag=TAG20130624T081657 comment=NONE
[Thread-81] [ 2013-06-24 08:16:58.873 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
[Thread-81] [ 2013-06-24 08:16:58.879 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Finished backup at 24-JUN-13
[Thread-81] [ 2013-06-24 08:17:01.476 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-81] [ 2013-06-24 08:17:01.523 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-81] [ 2013-06-24 08:17:01.555 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> set echo on

。。。。。。。。。。。。。。。。。。。

[Thread-83] [ 2013-06-24 08:17:10.683 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:10.685 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> startup force nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initlunar1.ora';
[Thread-83] [ 2013-06-24 08:17:21.503 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Oracle instance started
[Thread-83] [ 2013-06-24 08:17:22.702 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:22.703 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Total System Global Area     626327552 bytes
[Thread-83] [ 2013-06-24 08:17:22.708 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:22.709 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Fixed Size                     2230952 bytes
[Thread-83] [ 2013-06-24 08:17:22.709 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Variable Size                247465304 bytes
[Thread-83] [ 2013-06-24 08:17:22.710 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Database Buffers             369098752 bytes
[Thread-83] [ 2013-06-24 08:17:22.710 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Redo Buffers                   7532544 bytes
[Thread-83] [ 2013-06-24 08:17:22.806 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:22.806 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:22.807 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> set echo on
[Thread-83] [ 2013-06-24 08:17:22.807 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:22.808 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=echo set on
[Thread-83] [ 2013-06-24 08:17:22.808 CST ] [RMANEngine.readSqlOutput:945]  Notifying writer to proceed because m_bInterrupted=falsebNotify=truem_bReaderDone=falsem_bFatalErrorOccured=false
[Thread-83] [ 2013-06-24 08:17:22.808 CST ] [RMANEngine.run:729]  Return Value from readSqlOutput=echo set on
[main] [ 2013-06-24 08:17:22.809 CST ] [RMANEngine.executeImpl:1146]  Completed wait from reader
[main] [ 2013-06-24 08:17:22.810 CST ] [RMANEngine.executeImpl:1193]  DONE EXECUTING GIVEN COMMAND=startup force nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initlunar1.ora';
[main] [ 2013-06-24 08:17:22.810 CST ] [Instance.setControlAutoBackupOff:519]  CONFIGURE CONTROLFILE AUTOBACKUP OFF 
[main] [ 2013-06-24 08:17:22.811 CST ] [RMANEngine.executeImpl:1098]  m_bExecQuery=false
[main] [ 2013-06-24 08:17:22.814 CST ] [RMANEngine.executeImpl:1106]  Command being written to rman process=set nocfau;
[main] [ 2013-06-24 08:17:22.815 CST ] [RMANEngine.executeImpl:1134]  Notify reader to start reading
[Thread-83] [ 2013-06-24 08:17:22.815 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:22.816 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> set nocfau;
[Thread-83] [ 2013-06-24 08:17:22.816 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=executing command: SET NOCFAU
[Thread-83] [ 2013-06-24 08:17:22.829 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=using target database control file instead of recovery catalog
[Thread-83] [ 2013-06-24 08:17:23.395 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:23.397 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:23.397 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> set echo on
[Thread-83] [ 2013-06-24 08:17:23.397 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:23.398 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=echo set on
[Thread-83] [ 2013-06-24 08:17:23.398 CST ] [RMANEngine.readSqlOutput:945]  Notifying writer to proceed because m_bInterrupted=falsebNotify=truem_bReaderDone=falsem_bFatalErrorOccured=false
[Thread-83] [ 2013-06-24 08:17:23.398 CST ] [RMANEngine.run:729]  Return Value from readSqlOutput=echo set on
[main] [ 2013-06-24 08:17:23.399 CST ] [RMANEngine.executeImpl:1146]  Completed wait from reader
[main] [ 2013-06-24 08:17:23.400 CST ] [RMANEngine.executeImpl:1193]  DONE EXECUTING GIVEN COMMAND=set nocfau;
[main] [ 2013-06-24 08:17:23.401 CST ] [RMANEngine.executeImpl:1098]  m_bExecQuery=false
[main] [ 2013-06-24 08:17:23.402 CST ] [RMANEngine.executeImpl:1106]  Command being written to rman process=restore spfile to  '+ASMDATA/spfilelunar1.ora'  from  '+ASMDATA/sp_lunar1_backup';
[main] [ 2013-06-24 08:17:23.402 CST ] [RMANEngine.executeImpl:1134]  Notify reader to start reading
[Thread-83] [ 2013-06-24 08:17:23.403 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:23.404 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> restore spfile to  '+ASMDATA/spfilelunar1.ora'  from  '+ASMDATA/sp_lunar1_backup';
[Thread-83] [ 2013-06-24 08:17:23.405 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Starting restore at 24-JUN-13
[Thread-83] [ 2013-06-24 08:17:23.546 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=allocated channel: ORA_DISK_1
[Thread-83] [ 2013-06-24 08:17:23.552 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: SID=18 instance=lunar1 device type=DISK
[Thread-83] [ 2013-06-24 08:17:23.906 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:24.331 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: restoring spfile from AUTOBACKUP +ASMDATA/sp_lunar1_backup
[Thread-83] [ 2013-06-24 08:17:25.358 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
[Thread-83] [ 2013-06-24 08:17:25.359 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Finished restore at 24-JUN-13
[Thread-83] [ 2013-06-24 08:17:25.440 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:25.441 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:25.441 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> set echo on
[Thread-83] [ 2013-06-24 08:17:25.441 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:25.441 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=echo set on
[Thread-83] [ 2013-06-24 08:17:25.442 CST ] [RMANEngine.readSqlOutput:945]  Notifying writer to proceed because m_bInterrupted=falsebNotify=truem_bReaderDone=falsem_bFatalErrorOccured=false
[Thread-83] [ 2013-06-24 08:17:25.442 CST ] [RMANEngine.run:729]  Return Value from readSqlOutput=echo set on
[main] [ 2013-06-24 08:17:25.443 CST ] [RMANEngine.executeImpl:1146]  Completed wait from reader
[main] [ 2013-06-24 08:17:25.443 CST ] [RMANEngine.executeImpl:1193]  DONE EXECUTING GIVEN COMMAND=restore spfile to  '+ASMDATA/spfilelunar1.ora'  from  '+ASMDATA/sp_lunar1_backup';
[main] [ 2013-06-24 08:17:25.444 CST ] [RMANEngine.executeImpl:1098]  m_bExecQuery=false
[main] [ 2013-06-24 08:17:25.444 CST ] [RMANEngine.executeImpl:1106]  Command being written to rman process=shutdown immediate;
[main] [ 2013-06-24 08:17:25.444 CST ] [RMANEngine.executeImpl:1134]  Notify reader to start reading
[Thread-83] [ 2013-06-24 08:17:25.445 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:25.445 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=RMAN> shutdown immediate;
[Thread-83] [ 2013-06-24 08:17:34.039 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=Oracle instance shut down
[Thread-83] [ 2013-06-24 08:17:34.040 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=
[Thread-83] [ 2013-06-24 08:17:34.041 CST ] [RMANEngine.readSqlOutput:801]  Log RMAN Output=

。。。。。。。。。。。。。。。。。。。。

发表在 RAC | 标签为 rconfig, 单机 rac | 留下评论

数据库升级和DDL trigger

发表于 2015 年 5 月 3 日由 Lunar

升级数据库时，比如从9i或者10g升级到11.2，官方文档强调需要禁用DDL trigger。
这里尤其要说的是OGG的DDL TRIGGER，如果升级前不禁用，很坑爹，具体参见《OGG DDL trigger造成升级数据库后大量SYS对象失效和数据库DDL失效》
那么什么是DDL trigger呢？
我们来做个测试：

21:07:17 @LUNAR>CREATE OR REPLACE TRIGGER Lunar_ddl_test
   BEFORE DROP ON DATABASE
21:08:54   2  21:08:54   3  BEGIN
   IF LOWER (ora_dict_obj_name ()) = 'test'
21:08:54   4  21:08:54   5     THEN
21:08:54   6        raise_application_error (num      => -20000,
21:08:54   7                                 msg      =>    'Lunar test '
21:08:54   8                                             || ora_dict_obj_name ()
21:08:54   9                                             || ' ?!!!!!'
21:08:54  10                                             || 'Lunar test.....'
21:08:54  11                                );
21:08:54  12     END IF;
21:08:54  13  END;
21:08:54  14  /       

Trigger created.

Elapsed: 00:00:00.01
21:09:26 @LUNAR>select OWNER,TRIGGER_NAME,TRIGGER_TYPE from dba_triggers where TRIGGER_NAME='LUNAR_DDL_TEST';

OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
SYS                            LUNAR_DDL_TEST                 BEFORE EVENT

Elapsed: 00:00:00.05
21:09:35 @LUNAR>

这里我们看到了，TRIGGER_TYPE为BEFORE EVENT，这个显然是DDL trigger。
那么还有哪些类似的呢，我们来查看一下：


20:59:14 @LUNAR>select distinct TRIGGER_TYPE from dba_triggers;

TRIGGER_TYPE
----------------
BEFORE STATEMENT
BEFORE EACH ROW
AFTER EACH ROW
BEFORE EVENT
AFTER STATEMENT
AFTER EVENT
INSTEAD OF

7 rows selected.

Elapsed: 00:00:00.00
20:59:19 @LUNAR>

上面trigger type，从名字也很容易看出，至少两种： BEFORE EVENT和AFTER EVENT。
如果查询trigger event，你会看到有明显的DDL字样的类型的trigger：

21:10:08 @LUNAR>select distinct TRIGGERING_EVENT from dba_triggers;

TRIGGERING_EVENT
----------------------------------------
UPDATE OR DELETE
DROP
ALTER OR RENAME
INSERT OR UPDATE OR DELETE
INSERT OR UPDATE
DELETE
UPDATE
DDL
TRUNCATE
RENAME
CREATE OR ALTER OR DROP OR RENAME
ALTER
CREATE
CREATE OR ALTER
INSERT
DROP OR TRUNCATE
STARTUP

17 rows selected.

Elapsed: 00:00:00.08
21:10:35 @LUNAR>

上述具体含义可以查询Oracle官方文档，大概解释如下：

事件				允许的时机	说明
STARTUP				AFTER		启动数据库实例之后触发
SHUTDOWN			BEFORE		关闭数据库实例之前触发（非正常关闭不触发）
SERVERERROR			AFTER		数据库服务器发生错误之后触发
LOGON				AFTER		成功登录连接到数据库后触发
LOGOFF				BEFORE		开始断开数据库连接之前触发
CREATE				BEFORE，AFTER	在执行CREATE语句创建数据库对象之前、之后触发
DROP				BEFORE，AFTER	在执行DROP语句删除数据库对象之前、之后触发
ALTER				BEFORE，AFTER	在执行ALTER语句更新数据库对象之前、之后触发
DDL				BEFORE，AFTER	在执行大多数DDL语句之前、之后触发
GRANT				BEFORE，AFTER	执行GRANT语句授予权限之前、之后触发
REVOKE				BEFORE，AFTER	执行REVOKE语句收权限之前、之后触犯发
RENAME				BEFORE，AFTER	执行RENAME语句更改数据库对象名称之前、之后触犯发
AUDIT / NOAUDIT	BEFORE，AFTER	执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

那么文档中所说的禁用DDL trigger到底是哪一种呢？下面我们测试一下，如果不禁用DDL trigger会是什么情况。
首先我们查询当前数据库中的trigger_type in (‘BEFORE EVENT’,’AFTER EVENT’)的DDL：

21:10:35 @LUNAR>drop TRIGGER Lunar_ddl_test;

Trigger dropped.

Elapsed: 00:00:00.00
21:11:06 @LUNAR>
21:17:50 @LUNAR>select OWNER,TRIGGER_NAME,TRIGGER_TYPE from dba_triggers where trigger_type in ('BEFORE EVENT','AFTER EVENT');

OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
SYS                            LOGMNRGGC_TRIGGER              BEFORE EVENT
SYS                            AW_TRUNC_TRG                   AFTER EVENT
SYS                            AW_REN_TRG                     AFTER EVENT
SYS                            AW_DROP_TRG                    AFTER EVENT
WMSYS                          NO_VM_DDL                      BEFORE EVENT
WMSYS                          NO_VM_DROP_A                   AFTER EVENT
SYS                            CDC_ALTER_CTABLE_BEFORE        BEFORE EVENT
SYS                            CDC_CREATE_CTABLE_AFTER        AFTER EVENT
SYS                            CDC_CREATE_CTABLE_BEFORE       BEFORE EVENT
SYS                            CDC_DROP_CTABLE_BEFORE         BEFORE EVENT
EXFSYS                         EXPFIL_RESTRICT_TYPEEVOLVE     BEFORE EVENT
EXFSYS                         EXPFIL_ALTEREXPTAB_MAINT       AFTER EVENT
SYS                            XDB_PI_TRIG                    BEFORE EVENT
EXFSYS                         EXPFIL_DROPOBJ_MAINT           BEFORE EVENT
EXFSYS                         EXPFIL_DROPUSR_MAINT           AFTER EVENT
EXFSYS                         RLMGR_TRUNCATE_MAINT           BEFORE EVENT
MDSYS                          SDO_DROP_USER                  AFTER EVENT
MDSYS                          SDO_ST_SYN_CREATE              BEFORE EVENT
MDSYS                          SDO_TOPO_DROP_FTBL             BEFORE EVENT
MDSYS                          SDO_GEOR_BDDL_TRIGGER          BEFORE EVENT
MDSYS                          SDO_GEOR_ADDL_TRIGGER          AFTER EVENT
MDSYS                          SDO_NETWORK_DROP_USER          AFTER EVENT
SYSMAN                         MGMT_STARTUP                   AFTER EVENT

23 rows selected.

Elapsed: 00:00:00.04

下面我们只把TRIGGERING_EVENT为DDL的trigger进行disable，然后执行数据库升级操作，之后来看看效果：

21:19:03 @LUNAR>select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,status from dba_triggers where  TRIGGERING_EVENT like 'DDL%';     
OWNER                          TRIGGER_NAME                   TRIGGER_TYPE     TRIGGERING_EVENT                    STATUS
------------------------------ ------------------------------ ---------------- ----------------------------------- --------
SYS                            GGS_DDL_TRIGGER_BEFORE         BEFORE EVENT     DDL                                 DISABLED
MDSYS                          SDO_GEOR_BDDL_TRIGGER          BEFORE EVENT     DDL                                 DISABLED
MDSYS                          SDO_GEOR_ADDL_TRIGGER          AFTER EVENT      DDL                                 DISABLED
SYS                            LOGMNRGGC_TRIGGER              BEFORE EVENT     DDL                                 DISABLED

Elapsed: 00:00:00.04
21:19:07 @LUNAR>

升级过程后续会单独写一个BLOG，这里不赘述。

升级过程没有任何报错，完美升级结束后，我们来检查一下数据库无效对象，发现确实有4个无效对象。
不过，数据库所有组件正如我们看到的升级过程一样，都是有效的，且已经升级成功：

19:04:56 sys@LUNAR>select owner,object_name,object_type,status from dba_objects where status != 'VALID' and owner in ('SYS','SYSTEM'); 

OWNER                          OBJECT_NAME                                        OBJECT_TYPE         STATUS
------------------------------ -------------------------------------------------- ------------------- -------
SYS                            DDLCTXINFO                                         PACKAGE BODY        INVALID

Elapsed: 00:00:00.03
19:04:56 sys@LUNAR>

19:05:07 sys@LUNAR>SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10) version, SUBSTR(comp_name,1,35) comp_name FROM dba_registry;

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

18 rows selected.

Elapsed: 00:00:00.01
19:05:07 sys@LUNAR>

19:05:07 sys@LUNAR>select count(*) from dba_objects where status='INVALID';

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

Elapsed: 00:00:00.04
19:05:24 sys@LUNAR>select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

OWNER                          OBJECT_TYPE                 COUNT(*)
------------------------------ ------------------- ----------------
SYS                            PACKAGE BODY                       1
OGG                            PACKAGE BODY                       1
OGG                            FUNCTION                           1
OGG                            PROCEDURE                          1

Elapsed: 00:00:00.03
19:05:28 sys@LUNAR>select owner, object_name,object_type from dba_objects where status ='INVALID';

OWNER                          OBJECT_NAME                                        OBJECT_TYPE
------------------------------ -------------------------------------------------- -------------------
SYS                            DDLCTXINFO                                         PACKAGE BODY
OGG                            DDLORA_GETLOBS                                     PROCEDURE
OGG                            FILTERDDL                                          FUNCTION
OGG                            DDLAUX                                             PACKAGE BODY

Elapsed: 00:00:00.02
19:05:33 sys@LUNAR>

上面看到有3个是OGG的，不用想，这个坑爹的OGG DDL功能造成的，另外一个SYS的DDLCTXINFO，这个比较眼生，检查它是什么来路：


20:02:47 sys@LUNAR>20:02:47 sys@LUNAR>select dbms_metadata.get_ddl('PACKAGE','DDLCTXINFO','SYS') from dual;


  CREATE OR REPLACE PACKAGE "SYS"."DDLCTXINFO" AS
    PROCEDURE setCtxInfo(objNum  IN NUMBER, baseObjNum IN NUMBER,
                         objUserId IN NUMBER, baseObjUserId IN NUMBER,
                         baseObjProperty IN NUMBER) ;
END DDLCtxInfo ;
CREATE OR REPLACE PACKAGE BODY "SYS"."DDLCTXINFO" AS
   PROCEDURE setCtxInfo(objNum  IN NUMBER, baseObjNum IN NUMBER,
                         objUserId IN NUMBER, baseObjUserId IN NUMBER,
                         baseObjProperty IN NUMBER) IS
   BEGIN
       "OGG".DDLReplication.setCtxInfo(objNum , baseObjNum ,
                         objUserId , baseObjUserId ,
                         baseObjProperty ) ;
   END;

END DDLCtxInfo;


Elapsed: 00:00:00.41
20:02:47 sys@LUNAR>

可见，还是OGG DDL的东西，于是清理上述4个无效对象：

20:02:51 sys@LUNAR>drop PACKAGE BODY SYS.DDLCTXINFO;

Package body dropped.

Elapsed: 00:00:00.03
20:02:52 sys@LUNAR>drop PROCEDURE OGG.DDLORA_GETLOBS;

Procedure dropped.

Elapsed: 00:00:00.00
20:02:57 sys@LUNAR>drop FUNCTION OGG.FILTERDDL;

Function dropped.

Elapsed: 00:00:00.01
20:02:58 sys@LUNAR>drop PACKAGE BODY OGG.DDLAUX;

Package body dropped.

Elapsed: 00:00:00.02
20:02:58 sys@LUNAR>

再次检查，已经没有无效对象，且数据库组件全部有效：

20:02:59 sys@LUNAR>select owner,object_name,object_type,status from dba_objects where status != 'VALID' and owner in ('SYS','SYSTEM'); 

no rows selected

Elapsed: 00:00:00.02
20:06:00 sys@LUNAR>select owner, object_name,object_type from dba_objects where status ='INVALID';

no rows selected

Elapsed: 00:00:00.01
20:06:01 sys@LUNAR>
[oracle@dm01db01 ~]$ ss

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 3 20:07:10 2015

Copyright (c) 1982, 2013, Oracle.  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

20:07:10 sys@LUNAR>col COMP_NAME for a35        
20:07:18 sys@LUNAR>SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10) version, SUBSTR(comp_name,1,35) comp_name FROM dba_registry; 

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

18 rows selected.

Elapsed: 00:00:00.00
20:07:25 sys@LUNAR>

总结：
1，数据库升级前，一定要检查OGG用户的DDL功能是否已经被禁用，如果没有，麻利儿禁用它
2，其他系统自带的DDL trigger一般来说，如果是11.2小版本之间的升级，个人感觉不禁用也问题不大
3，官方建议升级前禁用DDL trigger，因此，推荐升级前还是禁用吧。尤其是从9i或者10g升级到11.2.

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

ORA-12537: TNS:connection closed

发表于 2015 年 5 月 2 日由 Lunar

今天测试数据库的时候，忽然发现使用远程连接的方式会报错：

SQL> conn sys/oracle@lunar as sysdba
ERROR:
ORA-12537: TNS:connection closed


Warning: You are no longer connected to ORACLE.
SQL> conn sys/oracle@lunar as sysdba
ERROR:
ORA-12537: TNS:connection closed


SQL>

listener.log的日志如下：

24-JUN-2013 06:21:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=lunar)(CID=(PROGRAM=sqlplus)(HOST=lunar1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=48962)) * establish * lunar * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe

这个报错，只解决上应该跟OS的什么配置有关系，但是不清楚哪里的配置问题。
正常的系统，使用strace跟踪，相关log如下：

。。。。。。。。。。。。。。。。。
16123      0.001337 open("/etc/hosts", O_RDONLY) = 10
16123      0.001629 fcntl(10, F_GETFD)  = 0
16123      0.001044 fcntl(10, F_SETFD, FD_CLOEXEC) = 0
16123      0.001640 fstat(10, {st_mode=S_IFREG|0644, st_size=590, ...}) = 0
16123      0.000826 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f11f0d1c000
16123      0.001343 read(10, "# Do not remove the following li"..., 4096) = 590
16123      0.001433 close(10)           = 0
16123      0.001342 munmap(0x7f11f0d1c000, 4096) = 0
16123      0.001008 open("/etc/hostid", O_RDONLY) = -1 ENOENT (No such file or directory)
16123      0.000998 uname({sys="Linux", node="lunar1", ...}) = 0
16123      0.001304 open("/etc/hosts", O_RDONLY) = 10
16123      0.004079 fcntl(10, F_GETFD)  = 0
16123      0.000199 fcntl(10, F_SETFD, FD_CLOEXEC) = 0
16123      0.000167 fstat(10, {st_mode=S_IFREG|0644, st_size=590, ...}) = 0
16123      0.001394 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f11f0d1c000
16123      0.001537 read(10, "# Do not remove the following li"..., 4096) = 590
16123      0.001640 close(10)           = 0
16123      0.001891 munmap(0x7f11f0d1c000, 4096) = 0
16123      0.001114 gettimeofday({1372028588, 2119}, NULL) = 0
16123      0.000792 write(9, "\3\214\0\0\6\0\0\0\0\0\3s\3\376\377\377\377\377\377\377\377\t\0\0\0!\1\0\0\376\377\377"..., 908) = 908
16123      0.003459 read(9, "\6\315\0\0\6\0\0\0\0\0\10&\0\23\0\0\0\23AUTH_VERSION_S"..., 8208) = 1741
16123      0.005878 open("/u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb", O_RDONLY) = 10
16123      0.001656 fcntl(10, F_SETFD, FD_CLOEXEC) = 0
16123      0.001247 lseek(10, 0, SEEK_SET) = 0
16123      0.001266 read(10, "\25\23\"\1\23\3\t\t\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256
16123      0.001189 lseek(10, 512, SEEK_SET) = 512
16123      0.001447 read(10, "l\31\3013\276J\213hv{\316\210\200\227S\3113\373\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
16123      0.002147 lseek(10, 1024, SEEK_SET) = 1024
16123      0.001641 read(10, "\30\0$\0002\0;\0D\0Q\0[\0f\0o\0{\0\210\0\240\0\310\0\321\0\331\0\340\0"..., 512) = 512
16123      0.002218 lseek(10, 55808, SEEK_SET) = 55808
16123      0.000759 read(10, "\10\0e\5\0\0008\0f\5\0\0\232\0g\5\0\0\7\1w\5\0\0Z\1x\5\0\0o\1"..., 512) = 512
16123      0.002017 lseek(10, 512, SEEK_SET) = 512
16123      0.000381 read(10, "l\31\3013\276J\213hv{\316\210\200\227S\3113\373\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
16123      0.001120 lseek(10, 1024, SEEK_SET) = 1024
16123      0.000799 read(10, "\30\0$\0002\0;\0D\0Q\0[\0f\0o\0{\0\210\0\240\0\310\0\321\0\331\0\340\0"..., 512) = 512
16123      0.001908 lseek(10, 56320, SEEK_SET) = 56320
。。。。。。。。。。。。。。。。。。

这个有问题的系统使用strace跟踪，相关log如下：

。。。。。。。。。。。。。。。。。。
11647      0.002544 open("/etc/hosts", O_RDONLY) = 9
11647      0.000358 fcntl(9, F_GETFD)   = 0
11647      0.000122 fcntl(9, F_SETFD, FD_CLOEXEC) = 0
11647      0.000169 fstat(9, {st_mode=S_IFREG|0644, st_size=590, ...}) = 0
11647      0.000120 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f48f89b9000
11647      0.000157 read(9, "# Do not remove the following li"..., 4096) = 590
11647      0.000393 read(9, "", 4096)   = 0
11647      0.000107 close(9)            = 0
11647      0.000155 munmap(0x7f48f89b9000, 4096) = 0
11647      0.000178 open("/etc/hosts", O_RDONLY) = 9
11647      0.000129 fcntl(9, F_GETFD)   = 0
11647      0.000559 fcntl(9, F_SETFD, FD_CLOEXEC) = 0
11647      0.000204 fstat(9, {st_mode=S_IFREG|0644, st_size=590, ...}) = 0
11647      0.001389 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f48f89b9000
11647      0.000577 read(9, "# Do not remove the following li"..., 4096) = 590
11647      0.000127 close(9)            = 0
11647      0.000123 munmap(0x7f48f89b9000, 4096) = 0
11647      0.000300 lstat("/u01/app/oracle/diag/clients/user_oracle/host_918442181_80/alert/log.xml", {st_mode=S_IFREG|0640, st_size=132505, ...}) = 0
。。。。。。。。。。。。

上述较大的不同在于fcntl(9, F_GETFD)相关的内容，这个东西我也不懂，c语言都还给老师了…… ：（

于是查询官方文档，根据MOS介绍，逐一核对文件权限：

检查oracle用户的权限：
1，grid的是正确的：

[grid@lunar1 trace]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 grid asmadmin 203973009 May  3 12:42 /u01/11.2.0/grid/bin/oracle
[grid@lunar1 trace]$

oracle的是错误的：

[oracle@lunar1 ~]$ ls -l $ORACLE_HOME/bin/oracle
-rwxr-s--x 1 oracle asmadmin 232399431 May  3 15:11 /u01/app/oracle/product/11.2.0/db_1/bin/oracle
[oracle@lunar1 ~]$

使用root修改

[root@lunar1 ~]# chmod 6751 /u01/app/oracle/product/11.2.0/db_1/bin/oracle
[root@lunar1 ~]#

再次检查已经正确了：

[oracle@lunar1 ~]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 232399431 May  3 15:11 /u01/app/oracle/product/11.2.0/db_1/bin/oracle
[oracle@lunar1 ~]$

2，oracle用户的ORACLE_HOME所在文件系统不支持setuid/suid（也不支持 nosetuid/nosuid 设置），例如:

mount| grep <mount_point_of_ORACLE_HOME>
/home/oracle on /dev/dsk/diskoracle read/write/nosuid..

我这里正常的：

[oracle@lunar1 ~]$ mount| grep /u01
/dev/sdb1 on /u01 type ext3 (rw)
[oracle@lunar1 ~]$

再次测试还是连接不上：

SQL> conn sys/oracle@lunar as sysdba
ERROR:
ORA-12537: TNS:connection closed


Warning: You are no longer connected to ORACLE.
SQL>

3，检查oracle用户的$ORACLE_HOME应该为755（注意，不要带 -R ！！！）：

[oracle@lunar1 ~]$ chmod 755 $ORACLE_HOME
[oracle@lunar1 ~]$

这次可以了：

SQL> conn sys/oracle@lunar as sysdba
Connected.
SQL>

把节点2也修改了：

[oracle@RAC2 ~]$ chmod 755 $ORACLE_HOME
[oracle@RAC2 ~]$ 

[oracle@RAC2 ~]$ ls -l $ORACLE_HOME/bin/oracle
-rwxr-s--x 1 oracle asmadmin 232399431 May  3 15:16 /u01/app/oracle/product/11.2.0/db_1/bin/oracle
[oracle@RAC2 ~]$ exit
logout
[root@RAC2 ~]# chmod 6751 /u01/app/oracle/product/11.2.0/db_1/bin/oracle
[root@RAC2 ~]# su - oracle
[oracle@RAC2 ~]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 232399431 May  3 15:16 /u01/app/oracle/product/11.2.0/db_1/bin/oracle
[oracle@RAC2 ~]$ 

[grid@RAC2 ~]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 grid oinstall 203973009 May  3 12:46 /u01/11.2.0/grid/bin/oracle
[grid@RAC2 ~]$

发表在 network, troubleshoooting | 标签为 ORA-12537: TNS:connection closed | 留下评论

站内搜索

Search for:
Oracle证书
分类目录
- ASM (30)
- Database (86)
  - backup&recovery (21)
  - expdp/impdp (5)
  - Installation and Deinstall (31)
  - network (7)
  - ORA-600 or ORA-7445 (6)
  - Performence Tuning (13)
  - troubleshoooting (2)
- Dataguard (7)
- EBS (3)
  - EBS系统管理 (1)
  - 安装、克隆、迁移 (2)
- Exadata (120)
  - FAQ (19)
  - POC和性能调整 (11)
  - 体系架构 (19)
  - 内部机制 (22)
  - 安装和升级 (14)
  - 性能指标 (8)
    - Exadata V1 (1)
    - Exadata V2 (1)
    - Exadata X2-2 (2)
    - Exadata X3-2 (1)
    - Exadata X4-2 (1)
    - FAQ (1)
  - 故障诊断 (3)
  - 日常运维 (15)
  - 硬件配置 (43)
    - Exadata V1 (6)
    - Exadata V2 (6)
    - Exadata X2-2 (6)
    - Exadata X3-2 (8)
    - Exadata X4-2 (8)
    - FAQ (1)
- FAQ (16)
- Internal (21)
  - bbed (2)
  - DUL ODU (4)
- Linux (20)
- MYSQL (8)
  - FAQ (7)
  - 复制 (1)
  - 安装配置 (1)
- OGG (1)
- ORA-600/7445 (2)
- ORA-XXXXX (5)
- Oracle 11.1 & Oracle11.2 (6)
- ORACLE 12C (21)
- Oracle 8 & Oracle 8i (1)
- RAC (47)
- SAP (2)
- Scripts (6)
- 未分类 (1)
- 虚拟化 (1)
2024 年五月

S M T W T F S

« Nov

1 2 3 4

5 6 7 8 9 10 11

12 13 14 15 16 17 18

19 20 21 22 23 24 25

26 27 28 29 30 31
文章归档
文章归档
近期文章
近期评论
- tom 发表在《exadata巡检报告的模板》
- cyx 发表在《关于我》
- 李科胜发表在《EBS克隆–db和app分开在两个服务器上》
- xiao 发表在《exadata巡检报告的模板》
- Chris Sun 发表在《使用Oracle 11.2的DBMS_RESOURCE_MANAGER.CALIBRATE_IO对Exadata X5（HC）进行测试》

2024 年五月
S	M	T	W	T	F	S
« Nov
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

站内搜索

Oracle证书

分类目录

文章归档

近期文章

近期评论