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

使用VBox 安装 Oracle Database 12c Flex Cluster for OEL 5.8—第4部分开始安装

发表于 2013 年 8 月 28 日由 Lunar

1. On Oracle Linux 6.3, Select Servers on the left hand side of the screen, and System administration tools on the right hand side of the screen (options may vary between releases).
The Packages in System Tools window opens.
2. Select the Oracle Pre-Install RPM or Oracle Validated package box from the package list. For example, for Oracle 6, select a package similar to the following:
oracle-rdbms-server-12cR1-preinstall-1.0-1.el6.x86_64.rpm
具体见：使用VBox 安装 Oracle Database 12c Flex Cluster for OEL 5.8—第4部分开始安装

发表在 Installation and Deinstall, ORACLE 12C, RAC | 标签为 12c, flex cluster, rac | 留下评论

使用VBox 安装 Oracle Database 12c Flex Cluster for OEL 5.8—第3部分准备网络环境

发表于 2013 年 8 月 28 日由 Lunar

配置DHCP DNS：

[root@lunar1 ~]# rpm -qa dhcp
dhcp-3.0.5-31.el5
[root@lunar1 ~]# 
[root@lunar1 ~]# yum install bind
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
ULN support will be disabled.
Setting up Install Process
Package 30:bind-9.3.6-20.P1.el5.x86_64 already installed and latest version
Nothing to do
[root@lunar1 ~]# 
[root@lunar1 ~]# yum install caching-nameserver
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
ULN support will be disabled.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package caching-nameserver.x86_64 30:9.3.6-20.P1.el5 set to be updated
--> Finished Dependency Resolution

具体见：使用VBox 安装 Oracle Database 12c Flex Cluster for OEL 5.8—第3部分准备网络环境

发表在 Installation and Deinstall, ORACLE 12C, RAC | 标签为 12c, flex cluster, rac | 留下评论

使用VBox 安装 Oracle Database 12c Flex Cluster for OEL 5.8—第2部分前期准备工作

发表于 2013 年 8 月 28 日由 Lunar

关于OS，文档有如下介绍：
Oracle Linux 6:

# yum install oracle-rdbms-server-12cR1-preinstall

Oracle Linux 5 or Oracle Linux 4:

# yum install oracle-validated

You should see output indicating that you have subscribed to the Oracle Linux channel, and that packages are being installed. For example:

el5_u6_i386_base
el5_u6_x86_64_patch

Check the RPM log file to review the system configuration changes. For example:
Oracle Linux 6:

/var/log/oracle-rdbms-server-12cR1-preinstall/results/orakernel.log
1
Oracle Linux 5:
1/var/log/oracle-validated/results/orakernel.log

具体见：使用VBox 安装 Oracle Database 12c Flex Cluster for OEL 5.8—第2部分前期准备工作

发表在 Installation and Deinstall, ORACLE 12C, RAC | 标签为 12c, flex cluster, rac | 留下评论

使用VBox 安装 Oracle Database 12c Flex Cluster for OEL 5.8—第1部分环境介绍

发表于 2013 年 8 月 28 日由 Lunar

由于Oracle Database 12c的 Flex Cluster需要使用DNS来解析GNS，因此必须配置DNS server。然后需要使用GNS动态分配SCAN和VIP，因此需要配置DHCP server。
Flex Cluster内置Flex ASM，因此，需要配置共享存储。
好了，我们需要的大致工作如下：
1，安装（或者复制）2个VBox虚拟机，建议使用 OEL 6.3以上版本，具体参见文档（参考支持版本的说明，OEL 5，OEL6都可以）
2，配置共享盘，配置yum安装需要的package
3，配置DHCP SERVER, DNS SERVER, GNS
4，规划网络，确定具体IP。12c只需要在host中指定Public和Private IP即可，至于VIP和SCAN都是由GNS来分配的，而GNS需要在DNS中解析。
5，安装GI
6，调整asm的sga，建议每个asm的sga256M足以，然后重启crs
7，安装DB（推荐DBCA建库，注意建库时指定sga的分配采用全手工方式，既非AMM亦非ASMM，这样经过测试一个db只需要230M到300M就可以跑的很好了，没办法，穷人，你懂的…………）
具体见:使用VBox 安装 Oracle Database 12c Flex Cluster for OEL 5.8—第1部分环境介绍

发表在 Installation and Deinstall, ORACLE 12C, RAC | 标签为 12c, flex cluster, rac | 留下评论

贫民电脑（8G mem）玩12c standalone需要的配置多大内存的vm呢？

发表于 2013 年 8 月 25 日由 Lunar

12c官方文档要求，对于单机的数据库，要求如下：
Server Memory Minimum Requirements
Ensure that your system meets the following memory requirements:
Minimum: 1 GB of RAM
Recommended: 2 GB of RAM or more

这说明，我们完全可以使用很小的memory来玩12c的。。。。。。

看下当前的配置（使用了ASMM）：

shared_pool_size=160M
db_cache_size=30M
log_buffer=6594560
MEMORY_MAX_TARGET=0
MEMORY_TARGET=0
sga_max_size=330M
sga_target=3300M

关于9i开始的sga介绍，请参考： http://blog.csdn.net/lunar2000/article/details/49437

启动一下数据库：

[oracle@lunar ~]$ ss

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 24 22:43:04 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SYS% lunarbb> show sga          

Total System Global Area  346562560 bytes         这里看到sga确实是最大330M
Fixed Size                  2288240 bytes
Variable Size             188745104 bytes
Database Buffers          146800640 bytes					但是这里并不是我设置的30M，而是140M
Redo Buffers                8728576 bytes
SYS% lunarbb>
SYS% lunarbb> show parameter cache_size   

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 32M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
SYS% lunarbb> 

SYS% lunarbb> col KSPPINM for a30
SYS% lunarbb> col ksppstvl format a15
SYS% lunarbb> col KSPPDESC for a55
SYS% lunarbb> select ksppinm, ksppstvl, KSPPDESC 
  2  from x$ksppi pi, x$ksppcv cv 
  3  where cv.indx=pi.indx and pi.ksppinm like '\_%' escape '\' 
  4   and pi.ksppinm like '%cache%';

KSPPINM                        KSPPSTVL        KSPPDESC
------------------------------ --------------- -------------------------------------------------------
_number_cached_attributes      10              maximum number of cached attributes per instance
_number_cached_group_membershi 32              maximum number of cached group membershipsps
_number_group_memberships_per_ 3               maximum number of group memberships per cache linecache_line
_blocking_sess_graph_cache_siz                 blocking session graph cache size in bytese
_hang_delay_resolution_for_lib TRUE            Hang Management delays hang resolution for library cach
cache                                          e

_lm_cache_res_cleanup          25              percentage of cached resources should be cleanup
_lm_cache_allocated_res_ratio  50              ratio of cached over allocated resources
_lm_cache_res_skip_cleanup     20              multiple of iniital res cache below which cleanup is skipped
_lm_cache_res_cleanup_tries    10              max number of batches of cached resources to free per c
                                               leanup
_lm_cache_res_type             TMHWHVDI        cache resource: string of lock types(s)
_lm_cache_lvl0_cleanup         0               how often to cleanup level 0 cache res (in sec)
_lm_cache_res_options          0               ges resource cache options
_blocks_per_cache_server       16              number of consecutive blocks per global cache server
_db_block_cache_protect        FALSE           protect database blocks (true only when debugging)
_db_block_cache_protect_intern 0               protect database blocks (for strictly internal use only
al                                             )

_db_block_cache_num_umap       0               number of unmapped buffers (for tracking swap calls on blocks)
__db_cache_size                134217728       Actual size of DEFAULT buffer pool for standard block size buffers     注意这里，这个就是ASMM中，会自动设置的buffer cache的尺寸，也就是sga动态调整后的值，他会被记录到alert中
_db_percpu_create_cachesize    2               size of cache created per cpu in deferred cache create
_db_initial_cachesize_create_m 256             size of cache created at startupb


......

过几分钟再次查询，发现buffer cache 已经被动态调整：

SYS% lunarbb> col KSPPINM for a30
SYS% lunarbb> col ksppstvl format a15
SYS% lunarbb> col KSPPDESC for a55
SYS% lunarbb> select ksppinm, ksppstvl, KSPPDESC 
  2  from x$ksppi pi, x$ksppcv cv 
  3  where cv.indx=pi.indx and pi.ksppinm like '\_%' escape '\' 
  4   and pi.ksppinm like '%db_cache_size%';

KSPPINM                        KSPPSTVL        KSPPDESC
------------------------------ --------------- -------------------------------------------------------
__db_cache_size                125829120       Actual size of DEFAULT buffer pool for standard block s                 动态调整后为125M
                                               ize buffers


Elapsed: 00:00:00.05
SYS% lunarbb>

select COMPONENT,CURRENT_SIZE,
GRANULE_SIZE
from V$SGA_DYNAMIC_COMPONENTS;

SYS% lunarbb> select COMPONENT,CURRENT_SIZE,
  2  GRANULE_SIZE
  3  from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                                                        CURRENT_SIZE GRANULE_SIZE
---------------------------------------------------------------- ------------ ------------
shared pool                                                         184549376      4194304
large pool                                                            8388608      4194304
java pool                                                             4194304      4194304
streams pool                                                                0      4194304
DEFAULT buffer cache                                                125829120      4194304
KEEP buffer cache                                                           0      4194304
RECYCLE buffer cache                                                        0      4194304
DEFAULT 2K buffer cache                                                     0      4194304
DEFAULT 4K buffer cache                                                     0      4194304
DEFAULT 8K buffer cache                                                     0      4194304
DEFAULT 16K buffer cache                                                    0      4194304
DEFAULT 32K buffer cache                                                    0      4194304
Shared IO Pool                                                       12582912      4194304
Data Transfer Cache                                                         0      4194304
ASM Buffer Cache                                                            0      4194304

15 rows selected.

Elapsed: 00:00:00.02
SYS% lunarbb> 
SYS% lunarbb> select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE,GRANULE_SIZE from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------
shared pool                                                         184549376  167772160  184549376      4194304
large pool                                                            8388608    8388608  125829120      4194304
java pool                                                             4194304    4194304    4194304      4194304
streams pool                                                                0          0          0      4194304
DEFAULT buffer cache                                                125829120   37748736  155189248      4194304
KEEP buffer cache                                                           0          0          0      4194304
RECYCLE buffer cache                                                        0          0          0      4194304
DEFAULT 2K buffer cache                                                     0          0          0      4194304
DEFAULT 4K buffer cache                                                     0          0          0      4194304
DEFAULT 8K buffer cache                                                     0          0          0      4194304
DEFAULT 16K buffer cache                                                    0          0          0      4194304
DEFAULT 32K buffer cache                                                    0          0          0      4194304
Shared IO Pool                                                       12582912          0   12582912      4194304
Data Transfer Cache                                                         0          0          0      4194304
ASM Buffer Cache                                                            0          0          0      4194304

15 rows selected.

Elapsed: 00:00:00.01
SYS% lunarbb> 

再次手工设置buffer cache的值来看看：
SYS% lunarbb> alter system set "__db_cache_size"=30M scope=spfile;

System altered.

Elapsed: 00:00:00.27
SYS% lunarbb>
SYS% lunarbb> shutdown abort
ORACLE instance shut down.
SYS% lunarbb> startup
ORACLE instance started.

Total System Global Area  346562560 bytes
Fixed Size                  2288240 bytes
Variable Size             297797008 bytes
Database Buffers           37748736 bytes
Redo Buffers                8728576 bytes
Database mounted.
Database opened.
SYS% lunarbb> !free
             total       used       free     shared    buffers     cached
Mem:       1495512    1402960      92552          0       2208     879872             我的vm目前给了这个oracle 12c standalone （asm+db）共1.4G内存，目前free的是92M
-/+ buffers/cache:     520880     974632
Swap:      4095992     183452    3912540

SYS% lunarbb> show parameter cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 32M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
SYS% lunarbb> 
SYS% lunarbb> select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE,GRANULE_SIZE from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------
shared pool                                                         176160768  167772160  176160768      4194304
large pool                                                            8388608    8388608  125829120      4194304
java pool                                                             4194304    4194304    4194304      4194304
streams pool                                                                0          0          0      4194304
DEFAULT buffer cache                                                146800640   37748736  155189248      4194304               我们看到这里还是140M
KEEP buffer cache                                                           0          0          0      4194304
RECYCLE buffer cache                                                        0          0          0      4194304
DEFAULT 2K buffer cache                                                     0          0          0      4194304
DEFAULT 4K buffer cache                                                     0          0          0      4194304
DEFAULT 8K buffer cache                                                     0          0          0      4194304
DEFAULT 16K buffer cache                                                    0          0          0      4194304
DEFAULT 32K buffer cache                                                    0          0          0      4194304
Shared IO Pool                                                              0          0          0      4194304
Data Transfer Cache                                                         0          0          0      4194304
ASM Buffer Cache                                                            0          0          0      4194304

15 rows selected.

Elapsed: 00:00:00.09
SYS% lunarbb> show parameter cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 32M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
SYS% lunarbb> show sga

Total System Global Area  346562560 bytes
Fixed Size                  2288240 bytes
Variable Size             188745104 bytes
Database Buffers          146800640 bytes
Redo Buffers                8728576 bytes
SYS% lunarbb>

好吧，我现在手工设置sga，ASMM和AMM都不用了，回归到8i的sga的设置方法，o(∩_∩)o 哈哈

shared_pool_size=160M
db_cache_size=25M
java_pool_size=5M
large_pool_size=10M
log_buffer=6594560
streams_pool_size=5M
MEMORY_MAX_TARGET=0
MEMORY_TARGET=0
sga_max_size=0
sga_target=0

再次启动数据库：

[oracle@lunar ~]$ ss

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 24 23:36:32 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SYS% lunarbb> startup
ORACLE instance started.

Total System Global Area  238055424 bytes           这次太平了，sga只有230M左右了，我想如果你有耐心，还可以再次调整，不过share pool不建议太小，从11.2开始，如果share pool太小，数据库跑一会就报ORA-4031错误了
Fixed Size                  2286840 bytes
Variable Size             197135112 bytes
Database Buffers           29360128 bytes           buffer cache只有我设置的25M，由于自己测试，用到25M buffer cache的时候也不算多，因此，我感觉目前的配置够用了，o(∩_∩)o 哈哈
Redo Buffers                9273344 bytes
Database mounted.
Database opened.
SYS% lunarbb> 
SYS% lunarbb> select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE,GRANULE_SIZE from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------
shared pool                                                         167772160  167772160  167772160      4194304
large pool                                                           12582912   12582912   12582912      4194304
java pool                                                             8388608    8388608    8388608      4194304
streams pool                                                          8388608    8388608    8388608      4194304
DEFAULT buffer cache                                                 29360128   29360128   29360128      4194304
KEEP buffer cache                                                           0          0          0      4194304
RECYCLE buffer cache                                                        0          0          0      4194304
DEFAULT 2K buffer cache                                                     0          0          0      4194304
DEFAULT 4K buffer cache                                                     0          0          0      4194304
DEFAULT 8K buffer cache                                                     0          0          0      4194304
DEFAULT 16K buffer cache                                                    0          0          0      4194304
DEFAULT 32K buffer cache                                                    0          0          0      4194304
Shared IO Pool                                                              0          0          0      4194304
Data Transfer Cache                                                         0          0          0      4194304
ASM Buffer Cache                                                            0          0          0      4194304

15 rows selected.

Elapsed: 00:00:00.04
SYS% lunarbb> 

现在看下asm：
[grid@lunar ~]$ ss

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 24 23:55:07 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> 
SQL> startup
ASM instance started

Total System Global Area  313159680 bytes             300M就可以了（以后会不会遇到性能问题或者其他限制，还不知道，o(∩_∩)o 哈哈）
Fixed Size                  2287856 bytes
Variable Size             285706000 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL>

关于12c中asm的内存的设置请参考 http://www.lunar2013.com/2013/08/ora-00443-background-process-mmnl-did-not-start.html

可以了，环境基本ready，db的sga只有230M，asm的sga 只有300M（从11.2以后，oracle对asm实例的最低大小是256M）.

后面可以跟小伙伴儿们一起在简陋的小本本上玩那些个 12c flex NF了，o(∩_∩)o 哈哈

发表在 ASM, ORACLE 12C, RAC | 标签为 12c | 留下评论

ORA-00443 background process MMNL did not start

发表于 2013 年 8 月 24 日由 Lunar

启动asm报ORA-00443

[root@lunar ~]# srvctl start asm
PRCR-1079 : Failed to start resource ora.asm
CRS-5017: The resource action "ora.asm start" encountered the following error: 
ORA-00443: background process "MMNL" did not start
. For details refer to "(:CLSN00107:)" in "/u01/app/12.1/grid/log/lunar/agent/ohasd/oraagent_grid/oraagent_grid.log".

CRS-2674: Start of 'ora.asm' on 'lunar' failed

先看下ORA-00443的含义：

[grid@lunar ~]$ oerr ora 00443
00443, 00000, "background process \"%s\" did not start"
// *Cause:  The specified process did not start.
// *Action: Ensure that the executable image is in the correct place with
//          the correct protections, and that there is enough memory.
[grid@lunar ~]$

感觉是内存不足…………

再看下/u01/app/12.1/grid/log/lunar/agent/ohasd/oraagent_grid/oraagent_grid.log：

2013-08-24 15:54:54.330: [ora.asm][1092012352] {0:0:2} [start] clsnUtils::error Exception type=2 string=
CRS-5017: The resource action "ora.asm start" encountered the following error:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/12.1/grid/log/lunar/agent/ohasd/oraagent_grid/oraagent_grid.log".

2013-08-24 15:54:54.330: [    AGFW][1092012352] {0:0:2} sending status msg [CRS-5017: The resource action "ora.asm start" encountered the following error:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/12.1/grid/log/lunar/agent/ohasd/oraagent_grid/oraagent_grid.log".
] for start for resource: ora.asm lunar 1


2013-08-24 16:19:43.750: [ USRTHRD][1114593600] {0:0:2} InstConnection::connectInt (2) Exception OCIException
2013-08-24 16:19:43.750: [ USRTHRD][1114593600] {0:0:2} InstConnection:connect:excp OCIException OCI error 1034
2013-08-24 16:19:43.750: [ USRTHRD][1114593600] {0:0:2} AsmCommonAgent DedicatedThread Exception OCIException
2013-08-24 16:19:43.750: [ USRTHRD][1114593600] {0:0:2} ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

我们发现，ohasd进程尝试多次重启ASM都是报上述错误，貌似什么原因造成ASM起不来。。。。

接着检查下ASM的日志发现如下信息：

Starting up:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option.
ORACLE_HOME = /u01/app/12.1/grid
System name:    Linux
Node name:      lunar
Release:        2.6.32-300.10.1.el5uek
Version:        #1 SMP Wed Feb 22 17:37:40 EST 2012
Machine:        x86_64
Using parameter settings in server-side spfile +DATA/ASM/ASMPARAMETERFILE/registry.253.818242245
System parameters with non-default values:
  large_pool_size          = 12M
  remote_login_passwordfile= "EXCLUSIVE"
IMODE=BR
ILAT =0
LICENSE_MAX_USERS = 0
SYS auditing is disabled
NOTE: remote asm mode is local (mode 0x301; from cluster type)
Starting up:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option.
ORACLE_HOME = /u01/app/12.1/grid
System name:    Linux
Node name:      lunar
Release:        2.6.32-300.10.1.el5uek
Version:        #1 SMP Wed Feb 22 17:37:40 EST 2012
Machine:        x86_64
Using parameter settings in server-side spfile +DATA/ASM/ASMPARAMETERFILE/registry.253.818242245
System parameters with non-default values:
  large_pool_size          = 12M
  remote_login_passwordfile= "EXCLUSIVE"
  asm_diskgroups           = "RECO"
  asm_power_limit          = 1
NOTE: remote asm mode is local (mode 0x301; from cluster type)
Sat Aug 24 15:54:18 2013
NOTE: PatchLevel of this instance 0
Starting background process PMON
Sat Aug 24 15:54:19 2013
PMON started with pid=2, OS id=3521
Starting background process PSP0
Sat Aug 24 15:54:19 2013
PSP0 started with pid=3, OS id=3525
Starting background process VKTM
Sat Aug 24 15:54:20 2013
VKTM started with pid=4, OS id=3529 at elevated priority
Starting background process GEN0
Sat Aug 24 15:54:20 2013
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sat Aug 24 15:54:20 2013
GEN0 started with pid=5, OS id=3535
Starting background process MMAN
Sat Aug 24 15:54:20 2013
MMAN started with pid=6, OS id=3539
Starting background process DIAG
Sat Aug 24 15:54:21 2013
DIAG started with pid=8, OS id=3547
Starting background process DIA0
Sat Aug 24 15:54:21 2013
DIA0 started with pid=9, OS id=3551
Starting background process DBW0
Sat Aug 24 15:54:21 2013
DBW0 started with pid=10, OS id=3555
Starting background process LGWR
Sat Aug 24 15:54:21 2013
LGWR started with pid=11, OS id=3559
Starting background process CKPT
Sat Aug 24 15:54:21 2013
CKPT started with pid=12, OS id=3563
Starting background process SMON
Sat Aug 24 15:54:21 2013
SMON started with pid=13, OS id=3567
Starting background process LREG
Sat Aug 24 15:54:21 2013
LREG started with pid=14, OS id=3571
Starting background process RBAL
Sat Aug 24 15:54:21 2013
RBAL started with pid=15, OS id=3575
Starting background process GMON
Sat Aug 24 15:54:22 2013
Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x9F6AC008] [PC:0xA6B0D9C, dbgtTrcData_int()+380] [flags: 0x0, count: 1]
Sat Aug 24 15:54:22 2013
Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x9F67A010] [PC:0xA6B0D9C, dbgtTrcData_int()+380] [flags: 0x0, count: 1]
Errors in file /u01/app/grid/diag/asm/+asm/+ASM/trace/+ASM_psp0_3525.trc  (incident=27225):
ORA-07445: exception encountered: core dump [dbgtTrcData_int()+380] [SIGBUS] [ADDR:0x9F67A010] [PC:0xA6B0D9C] [Non-existent physical address] []
Errors in file /u01/app/grid/diag/asm/+asm/+ASM/trace/+ASM_gmon_3579.trc  (incident=28801):
ORA-07445: exception encountered: core dump [dbgtTrcData_int()+380] [SIGBUS] [ADDR:0x9F6AC008] [PC:0xA6B0D9C] [Non-existent physical address] []
Incident details in: /u01/app/grid/diag/asm/+asm/+ASM/incident/incdir_27225/+ASM_psp0_3525_i27225.trc
Incident details in: /u01/app/grid/diag/asm/+asm/+ASM/incident/incdir_28801/+ASM_gmon_3579_i28801.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sat Aug 24 15:54:24 2013
Using default pga_aggregate_limit of 2048 MB
Sat Aug 24 15:54:26 2013
Dumping diagnostic data in directory=[cdmp_20130824155426], requested by (instance=1, osid=3525 (PSP0)), summary=[incident=27225].
Process GMON died, see its trace file
Sat Aug 24 15:54:27 2013
USER (ospid: 3474): terminating the instance due to error 443
Sat Aug 24 15:54:28 2013
Instance terminated by USER, pid = 3474

这里可以发现，实际上ASM的进程已经启动了pmon, smon,ckpt，dbwr…等重要进程，但是后来被GMON进程终止了。
这里简单说下，GMON和PSP0进程都是ORACLE 10.2 ASM中就有的进程，其中：
GMON（ASM Disk Group Monitor Process）是10.2 asm引入的一个新的进程，该进程ASM instace启动以后监控diskgroup的元数据信息，并跟ocssd进程进行交互，
GMON负责将ASM实例的Diskgroup信息发送给ocssd，这样，其他数据库实例通过跟ocssd交互并获得ASM磁盘组的信息，再之后，数据库实例就可以打开磁盘组，对其进读写的操作。
文档中是这样描述的：

GMON
	ASM Disk Group Monitor Process
	Monitors all mounted ASM disk groups
	GMON monitors all the disk groups mounted in an ASM instance and is responsible for maintaining consistent disk membership and status information. 
Membership changes result from adding and dropping disks, whereas disk status changes result from taking disks offline or bringing them online.

而PSP0进程的主要作用是创建新的进程，文档描述如下：

PSP0
	Process Spawner Process
	Spawns Oracle background processes after initial instance startup

我们来分析一下这两个进程的trace。

检查GMON的trace，发现确实系统当时空闲内存很少：

========= Dump for incident 27225 (ORA 7445 [dbgtTrcData_int]) ========
Dump continued from file: /u01/app/grid/diag/asm/+asm/+ASM/trace/+ASM_gmon_3579.trc
ORA-07445: exception encountered: core dump [dbgtTrcData_int()+380] [SIGBUS] [ADDR:0x9F6AC008] [PC:0xA6B0D9C] [Non-existent physical address] []

========= Dump for incident 28801 (ORA 7445 [dbgtTrcData_int]) ========
----- Beginning of Customized Incident Dump(s) -----
Dumping swap information
Memory (Avail / Total) = 75.04M / 1164.46M
Swap (Avail / Total) = 3999.99M /  3999.99M
Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x9F6AC008] [PC:0xA6B0D9C, dbgtTrcData_int()+380] [flags: 0x0, count: 1]
Registers:
%rax: 0x0000000000000001 %rbx: 0x000000009f6abfd0 %rcx: 0xffffffff0000ffff
%rdx: 0x0000000000000000 %rdi: 0x000000009f6aff80 %rsi: 0x0000000000000000
%rsp: 0x00007fff8433f580 %rbp: 0x00007fff8433f790  %r8: 0x000000009f6abfd0
 %r9: 0x000000009f6abfd8 %r10: 0x0000000000010000 %r11: 0x000000000000000b

检查PSP0的trace，发现确实系统当时空闲内存很少：

Dump continued from file: /u01/app/grid/diag/asm/+asm/+ASM/trace/+ASM_psp0_3525.trc
ORA-07445: exception encountered: core dump [dbgtTrcData_int()+380] [SIGBUS] [ADDR:0x9F67A010] [PC:0xA6B0D9C] [Non-existent physical address] []

========= Dump for incident 27225 (ORA 7445 [dbgtTrcData_int]) ========
----- Beginning of Customized Incident Dump(s) -----
Dumping swap information
Memory (Avail / Total) = 75.04M / 1164.46M
Swap (Avail / Total) = 3999.99M /  3999.99M
Exception [type: SIGBUS, Non-existent physical address] [ADDR:0x9F67A010] [PC:0xA6B0D9C, dbgtTrcData_int()+380] [flags: 0x0, count: 1]
Registers:
%rax: 0x0000000000000001 %rbx: 0x000000009f679fd8 %rcx: 0xffffffff0000ffff
%rdx: 0x000000000000004b %rdi: 0x000000009f67bf80 %rsi: 0x0000000000000000
%rsp: 0x00007fff83b440e0 %rbp: 0x00007fff83b442f0  %r8: 0x000000009f679fd8
 %r9: 0x000000009f679fe0 %r10: 0x0000000000010000 %r11: 0x0000000000000008

已经差不多定位了，系统内存不足，因此，关闭VM，增加VM的内存，然后重启，一切ok了
现在看下asm中sga的参数配置：

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 1076M					居然是1G
memory_target                        big integer 1076M
pga_aggregate_target                 big integer 0
sga_target                           big integer 0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
sga_max_size                         big integer 1088M
sga_target                           big integer 0
unified_audit_sga_queue_size         integer     1048576
SQL>

查询了Oracle 10.2中，Oracle Database Administrator’s Guide “Using Automatic Storage Management”这一章节对于ASM的实例有如下的简单说明：

ASM Instance Memory Requirements
ASM instances are smaller than database instances. 
A 64 MB SGA should be sufficient for all but the largest ASM installations.
Total memory footprint for a typical ASM instance is approximately 100 MB.

也就是说，在10.2的环境中，该ASM实例设置为100M足够了。

从11.2以后，ASM单独有一个doc来讲： Oracle Automatic Storage Management Administrator’s Guide：
Automatic memory management automatically manages the memory-related parameters for both Oracle ASM and database instances with the MEMORY_TARGET parameter.
Automatic memory management is enabled by default on an Oracle ASM instance, even when the MEMORY_TARGET parameter is not explicitly set.
The default value used for MEMORY_TARGET is acceptable for most environments.
This is the only parameter that you must set for complete Oracle ASM memory management.
Oracle strongly recommends that you use automatic memory management for Oracle ASM. ————》Oracle强烈推荐使用AMM方式管理ASM实例

If you do not set a value for MEMORY_TARGET, but you do set values for other memory related parameters, Oracle internally calculates the optimum value for MEMORY_TARGET based on those memory parameter values.
You can also increase MEMORY_TARGET dynamically, up to the value of the MEMORY_MAX_TARGET parameter, just as you can do for the database instance.

Although it is not recommended, you can disable automatic memory management by either setting the value for MEMORY_TARGET to 0 in the Oracle ASM parameter file or by running an ALTER SYSTEM SET MEMORY_TARGET=0 statement. When you disable automatic memory management, Oracle reverts to auto shared memory management and automatic PGA memory management. To revert to Oracle Database 10g release 2 (10.2) functionality to manually manage Oracle ASM SGA memory, also run the ALTER SYSTEM SET SGA_TARGET=0 statement. You can then manually manage Oracle ASM memory using the information in “Oracle ASM Parameter Setting Recommendations”, that discusses Oracle ASM memory-based parameter settings. Unless specified, the behaviors of the automatic memory management parameters in Oracle ASM instances behave the same as in Oracle Database instances.

Notes:
For a Linux environment, automatic memory management cannot work if /dev/shm is not available or is undersized.
For more information, see Oracle Database Administrator’s Reference for Linux and UNIX-Based Operating Systems.
For information about platforms that support automatic memory management, see Oracle Database Administrator’s Guide.

The minimum MEMORY_TARGET for Oracle ASM is 256 MB. If you set MEMORY_TARGET to 100 MB, then Oracle increases the value for MEMORY_TARGET to 256 MB automatically.

也就是说，从11.2开始，Oracle强烈推荐使用AMM方式管理ASM实例，并且最小值是256M。

我们看下12c（12.1）的文档中除了11.2的上述描述外，增加了一个内容，无他：
In an Oracle Exadata environment, the recommended settings for managing memory are SGA_TARGET = 1250MB, PGA_AGGREGATE_TARGET = 400MB, MEMORY_TARGET = 0, and MEMORY_MAX_TARGET = 0.

在Exadata的ASM环境，缺省配置是这样的：

		在Exadata Version	11.2.2.2.0：
		---------------------------------------
		memory_max_target = 1073741824 
		memory_target = 1073741824
		pga_aggregate_target = 104857600 
		sga_max_size = 943718400 
		sga_target = 943718400
		shared_pool_size = 0 
		sort_area_size = 65536
		large_pool_size = 12582912

		从Exadata Version	11.2.3.1.1到11.2.3.2.1：
		---------------------------------------
		memory_max_target = 0 
		memory_target = 0 
		pga_aggregate_target = 419430400 
		sga_max_size = 1325400064 
		sga_target = 1325400064
		sort_area_size = 65536 
		large_pool_size = 16777216

居然用 sort_area_size ，不知道这里面有什么玄机………………

发表在 ORA-XXXXX | 标签为 mmnl, ORA-00443 | 留下评论

浅谈SCN_2–_kcmgas_函数

发表于 2013 年 8 月 22 日由 Lunar

从oracle10g开始，我们可以通过查询v$database.current_scn来获取当前数据库的scn，这个是通过调用”kcmgas”函数来完成的，这是一个oracle intance的永久内存结构体，我们可以查询 v$syssta来观察该函数的调用情况：
先看对v$database.current_scn的查询来获取scn的方式：

alter system checkpoint;
select * from v$sysstat where name like '%kcmgas';
select current_scn from v$database;
select * from v$sysstat where name like '%kcmgas';
select current_scn from v$database;
select * from v$sysstat where name like '%kcmgas';
select current_scn from v$database;
select * from v$sysstat where name like '%kcmgas';
select current_scn from v$database;
select * from v$sysstat where name like '%kcmgas';
select current_scn from v$database;
select * from v$sysstat where name like '%kcmgas';
select current_scn from v$database;
select * from v$sysstat where name like '%kcmgas';

详细描述请见：浅谈SCN_2-_kcmgas_函数
姊妹篇见:浅谈SCN_1–从oracle7至今，如何获取scn

发表在 Internal | 标签为 kcmgas, scn | 留下评论

浅谈SCN_1–从oracle7至今，如何获取scn

发表于 2013 年 8 月 22 日由 Lunar

SCN (System Change Number) 是Oracle数据库中保持数据一致性的主要机制。数据库内部的scn有好几种，会在后面的blog中慢慢细数。今天主要我们如何获取数据库的scn？

SCN是一个很大的数字，Oracle使用6 Bytes记录SCN，也就是48bit(一个byte是8bit，每个bit存储0或者1)，其最大值是其格式貌似由两部分组成： wrap.base
其中前面16bit的十进制数表示wrap，后面32bit的十进制数表示base，当base达到4 billion（4G），wrap就会增加1。
这是因为Oracle使用c语言写的，在c语言里面 long 类型是一个32bit整数，即最大是 4G（4294967296，2 power 32），因此，scn若在自增的时候采用long类型的整数，正好是4字节，因此，当scn base（ktuxescnb ）增加到4G的时候，就需要扩充，于是就有了scn wrap （ktuxescnw），这个表示每满一个 4G（ktuxescnb）则该值被重置为0，然后再次开始递增1。
详细描述请见:浅谈SCN_1-_从oracle7至今，如何获取scn

发表在 Internal | 标签为 scn | 留下评论

Linux误删除文件并且数据库crash后恢复

发表于 2013 年 8 月 21 日由 Lunar

我们都知道误删除文件后，如果没有其他操作，且数据库没有crash（句柄还在），那么是可以通过fd找到文件进行数据库恢复的，具体可以参考以前的文章：linux 误删除文件恢复
那么，如果句柄已经释放（比如数据库crash了），且客户重启了数据库，并执行了一些“恢复”尝试，然后怎么办？

我们测试下，这里我们要借助一个小工具：ext3grep
该工具可以在下面的网址下载最新版：
http://code.google.com/p/ext3grep/downloads/list
系统必须要有e2fsprogs-libs，否则安装ext3grep的时可能会有问题。

[root@lunar tmp]# uname -a
Linux lunar 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
[root@lunar tmp]# rpm -qa |grep e2fs
e2fsprogs-libs-1.39-33.el5
e2fsprogs-1.39-33.el5
e2fsprogs-devel-1.39-33.el5
e2fsprogs-libs-1.39-33.el5
[root@lunar tmp]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       35G  3.3G   30G  10% /
/dev/sda1              99M   23M   71M  25% /boot
tmpfs                 583M     0  583M   0% /dev/shm
/dev/sdb1              40G  4.3G   34G  12% /u01
/dev/sdc1              40G  1.9G   36G   5% /oradata
/dev/sdd1              40G  203M   38G   1% /other
[root@lunar tmp]#

如果你下载了rpm包，那么安装so easy:

[root@lunar tmp]# ls
ext3grep-0.10.2-1.el5.rf.x86_64.rpm  mapping-root  scim-panel-socket:0-root  spfile.bak
[root@lunar tmp]# rpm -ivh ext3grep-0.10.2-1.el5.rf.x86_64.rpm 
warning: ext3grep-0.10.2-1.el5.rf.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 6b8d79e6
Preparing...                ########################################### [100%]
   1:ext3grep               ########################################### [100%]
[root@lunar tmp]# which ext3grep
/usr/bin/ext3grep
[root@lunar tmp]#

如果你下载了src的源码，那么可以如下方式安装：

[root@localhost src]# tar xfvz ext3grep-0.6.0.tar.gz 
[root@localhost ext3grep-0.6.0]# ./configure
[root@localhost ext3grep-0.6.0]# make install 
[root@localhost ext3grep-0.6.0]# ext3grep
Running ext3grep version 0.6.0

我们看下他的帮助，还是很强大的：

[root@lunar ~]# ext3grep
Running ext3grep version 0.10.2
No action specified; implying --superblock.

Usage: ext3grep [options] [--] device-file
Options:
  --version, -[vV]       Print version and exit successfully.
  --help,                Print this help and exit successfully.
  --superblock           Print contents of superblock in addition to the rest.
                         If no action is specified then this option is implied.
  --print                Print content of block or inode, if any.
  --ls                   Print directories with only one line per entry.
                         This option is often needed to turn on filtering.
  --accept filen         Accept 'filen' as a legal filename. Can be used multi-
                         ple times. If you change any --accept you must remove
                         BOTH stage* files!
  --accept-all           Simply accept everything as filename.
  --journal              Show content of journal.
  --show-path-inodes     Show the inode of each directory component in paths.
Filters:
  --group grp            Only process group 'grp'.
  --directory            Only process directory inodes.
  --after dtime          Only entries deleted on or after 'dtime'.
  --before dtime         Only entries deleted before 'dtime'.
  --deleted              Only show/process deleted entries.
  --allocated            Only show/process allocated inodes/blocks.
  --unallocated          Only show/process unallocated inodes/blocks.
  --reallocated          Do not suppress entries with reallocated inodes.
                         Inodes are considered 'reallocated' if the entry
                         is deleted but the inode is allocated, but also when
                         the file type in the dir entry and the inode are
                         different.
  --zeroed-inodes        Do not suppress entries with zeroed inodes. Linked
                         entries are always shown, regardless of this option.
  --depth depth          Process directories recursively up till a depth
                         of 'depth'.
Actions:
  --inode-to-block ino   Print the block that contains inode 'ino'.
  --inode ino            Show info on inode 'ino'.
                         If --ls is used and the inode is a directory, then
                         the filters apply to the entries of the directory.
                         If you do not use --ls then --print is implied.
  --block blk            Show info on block 'blk'.
                         If --ls is used and the block is the first block
                         of a directory, then the filters apply to entries
                         of the directory.
                         If you do not use --ls then --print is implied.
  --histogram=[atime|ctime|mtime|dtime|group]
                         Generate a histogram based on the given specs.
                         Using atime, ctime or mtime will change the
                         meaning of --after and --before to those times.
  --journal-block jblk   Show info on journal block 'jblk'.
  --journal-transaction seq
                         Show info on transaction with sequence number 'seq'.
  --dump-names           Write the path of files to stdout.
                         This implies --ls but suppresses it's output.
  --search-start str     Find blocks that start with the fixed string 'str'.
  --search str           Find blocks that contain the fixed string 'str'.
  --search-inode blk     Find inodes that refer to block 'blk'.
  --search-zeroed-inodes Return allocated inode table entries that are zeroed.
  --inode-dirblock-table dir
                         Print a table for directory path 'dir' of directory
                         block numbers found and the inodes used for each file.
  --show-journal-inodes ino
                         Show copies of inode 'ino' still in the journal.
  --restore-inode ino[@seqnr][,ino[@seqnr],...]
                         Restore the file(s) with known inode number 'ino'.
                         The restored files are created in ./RESTORED_FILES/
                         with their inode number as extension (ie, inode.12345).
                         If '@seqnr' is provided then (only) the journal entry
                         with that sequence number is used, otherwise the latest
                         entry is used (if any). You can use that in the case a
                         a file was overwritten or truncated, rather than deleted.
  --restore-file 'path' [--restore-file 'path' ...]
                         Will restore file 'path'. 'path' is relative to the
                         root of the partition and does not start with a '/' (it
                         must be one of the paths returned by --dump-names).
                         The restored directory, file or symbolic link is
                         created in the current directory as 'RESTORED_FILES/path'.
  --restore-all          As --restore-file but attempts to restore everything.
                         The use of --after is highly recommended because the
                         attempt to restore very old files will only result in
                         them being hard linked to a more recently deleted file
                         and as such polute the output.
  --show-hardlinks       Show all inodes that are shared by two or more files.
[root@lunar ~]# 

[root@lunar ~]# ext3grep version
Running ext3grep version 0.10.2
No action specified; implying --superblock.

ext3grep: stat "version": No such file or directory
[root@lunar ~]#

模拟数据库文件被误删除，且数据库crash：

SYS% orcl> select name from v$datafile;

NAME
-----------------------------------------------------------
/oradata/orcl/system01.dbf
/oradata/orcl/sysaux01.dbf
/oradata/orcl/undotbs01.dbf
/oradata/orcl/users01.dbf

Elapsed: 00:00:00.01
SYS% orcl> 

[root@lunar oradata]# cd orcl
[root@lunar orcl]# ll
total 1758684
-rw-r----- 1 oracle oinstall   9912320 Aug 17 22:56 control01.ctl
-rw-r----- 1 oracle oinstall   9912320 Aug 17 22:56 control02.ctl
-rw-r----- 1 oracle oinstall  52429312 Aug 17 22:56 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Aug 17 22:39 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Aug 17 22:54 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Apr  4 19:56 sby_redo04.log
-rw-r----- 1 oracle oinstall  52429312 Apr  4 19:56 sby_redo05.log
-rw-r----- 1 oracle oinstall  52429312 Apr  4 19:56 sby_redo06.log
-rw-r----- 1 oracle oinstall  52429312 Apr  4 19:56 sby_redo07.log
-rw-r----- 1 oracle oinstall 576724992 Aug 17 22:54 sysaux01.dbf
-rw-r----- 1 oracle oinstall 754982912 Aug 17 22:54 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Aug 17 11:27 temp01.dbf
-rw-r----- 1 oracle oinstall  73408512 Aug 17 22:54 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Aug 17 22:54 users01.dbf
[root@lunar orcl]# rm -rf /oradata/orcl/users01.dbf
[root@lunar orcl]# ls /oradata/orcl/users01.dbf
ls: /oradata/orcl/users01.dbf: No such file or directory
[root@lunar orcl]# 

SYS% orcl> alter system switch logfile;

System altered.

Elapsed: 00:00:00.08
SYS% orcl> 
SYS% orcl> shutdown abort
ORACLE instance shut down.
SYS% orcl> 
SYS% orcl> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             205522264 bytes
Database Buffers          411041792 bytes
Redo Buffers                7532544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oradata/orcl/users01.dbf'

数据库启动报错，丢失了文件 ‘/oradata/orcl/users01.dbf’。

[root@lunar orcl]# ls /oradata/orcl/users01.dbf
ls: /oradata/orcl/users01.dbf: No such file or directory
[root@lunar orcl]#

现在使用ext3grep进行扫描和恢复：
ext3grep是针对ext3文件系统的（ext2单有自己的扫描恢复工具），确认丢失的文件是ext3文件体系:

[root@lunar ~]# cat /etc/fstab
/dev/VolGroup00/LogVol00 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/VolGroup00/LogVol01 swap                    swap    defaults        0 0
/dev/sdb1               /u01                    ext3    defaults        0 0
/dev/sdc1               /oradata                ext3    defaults        0 0
/dev/sdd1               /other                  ext3    defaults        0 0
[root@lunar ~]# 

SYS% orcl> shutdown abort
ORACLE instance shut down.
SYS% orcl> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lunar ~]$ ps -ef|grep dbw
oracle   32639 32418  0 23:07 pts/1    00:00:00 grep dbw
[oracle@lunar ~]$ ps -ef|grep ora_
oracle   32641 32418  0 23:07 pts/1    00:00:00 grep ora_
[oracle@lunar ~]$ ps -ef|grep pmon
oracle   32643 32418  0 23:07 pts/1    00:00:00 grep pmon
[oracle@lunar ~]$

这里，我的数据文件都在/oradata，是设备 /dev/sdc1 ：

[root@lunar orcl]# ext3grep /dev/sdc1 --ls --inode 2     （注意，必须使用inode 2，表示'..'，即，上一级目录)
Running ext3grep version 0.10.2
WARNING: EXT3_FEATURE_INCOMPAT_RECOVER is set. This either means that your partition is still mounted, and/or the file system is in an unclean state.
Number of groups: 320
Loading group metadata... done
Minimum / maximum journal block: 1545 / 35886
Loading journal descriptors... sorting... done
The oldest inode block that is still in the journal, appears to be from 1376706423 = Sat Aug 17 10:27:03 2013
Number of descriptors in journal: 13954; min / max sequence numbers: 3147 / 10086
Inode is Allocated
Finding all blocks that might be directories.
D: block containing directory start, d: block containing more directory entries.
Each plus represents a directory start that references the same inode as a directory start that we found previously.

Searching group 0: DDD
Searching group 1: 
Searching group 2: 
Searching group 3: 
Searching group 4: 
Searching group 5: 
Searching group 6: 
Searching group 7: 
。。。。。。。。。。。。。。。。。。。。
Searching group 314: 
Searching group 315: 
Searching group 316: 
Searching group 317: 
Searching group 318: 
Searching group 319: 
Writing analysis so far to 'sdc1.ext3grep.stage1'. Delete that file if you want to do this stage again.
Result of stage one:
  3 inodes are referenced by one or more directory blocks, 3 of those inodes are still allocated.
  1 inodes are referenced by more than one directory block, 1 of those inodes is still allocated.
  0 blocks contain an extended directory.
Result of stage two:
  3 of those inodes could be resolved because they are still allocated.
All directory inodes are accounted for!


Writing analysis so far to 'sdc1.ext3grep.stage2'. Delete that file if you want to do this stage again.
The first block of the directory is 1539.
Inode 2 is directory "".
Directory block 1539:
          .-- File type in dir_entry (r=regular file, d=directory, l=symlink)
          |          .-- D: Deleted ; R: Reallocated
Indx Next |  Inode   | Deletion time                        Mode        File name
==========+==========+----------------data-from-inode------+-----------+=========
   0    1 d       2                                         drwxrwxr-x  .
   1    2 d       2                                         drwxrwxr-x  ..
   2    3 d      11                                         drwxrwxr-x  lost+found
   3  end d 4358145                                         drwxr-x---  orcl   
[root@lunar orcl]# 
[root@lunar orcl]#

注意这里，我的数据库目录的inode是 4358145 ，下面我们开始从这个inode继续查找：

[root@lunar orcl]# ext3grep /dev/sdc1 --ls --inode 4358145
Running ext3grep version 0.10.2
WARNING: EXT3_FEATURE_INCOMPAT_RECOVER is set. This either means that your partition is still mounted, and/or the file system is in an unclean state.
Number of groups: 320
Minimum / maximum journal block: 1545 / 35886
Loading journal descriptors... sorting... done
The oldest inode block that is still in the journal, appears to be from 1376706423 = Sat Aug 17 10:27:03 2013
Number of descriptors in journal: 13960; min / max sequence numbers: 3147 / 10087
Inode is Allocated
Loading sdc1.ext3grep.stage2... done
The first block of the directory is 8742912.
Inode 4358145 is directory "orcl".
Directory block 8742912:
          .-- File type in dir_entry (r=regular file, d=directory, l=symlink)
          |          .-- D: Deleted ; R: Reallocated
Indx Next |  Inode   | Deletion time                        Mode        File name
==========+==========+----------------data-from-inode------+-----------+=========
   0    1 d 4358145                                         drwxr-x---  .
   1    2 d       2                                         drwxrwxr-x  ..
   2    3 r 4358146                                         rrw-r-----  system01.dbf
   3    4 r 4358147                                         rrw-r-----  sysaux01.dbf
   4    6 r 4358148                                         rrw-r-----  undotbs01.dbf
   6    7 r 4358150                                         rrw-r-----  control01.ctl
   7    8 r 4358151                                         rrw-r-----  control02.ctl
   8    9 r 4358152                                         rrw-r-----  redo01.log
   9   10 r 4358153                                         rrw-r-----  redo02.log
  10   11 r 4358154                                         rrw-r-----  redo03.log
  11   12 r 4358155                                         rrw-r-----  temp01.dbf
  12   13 r 4358156                                         rrw-r-----  sby_redo04.log
  13   14 r 4358157                                         rrw-r-----  sby_redo05.log
  14   15 r 4358158                                         rrw-r-----  sby_redo06.log
  15   16 r 4358159                                         rrw-r-----  sby_redo07.log
  16   17 r 4358149                                         rrw-r--r--  sdc1.ext3grep.stage1
  17  end r 4358160                                         rrw-r--r--  sdc1.ext3grep.stage2    这里可以看到，users01.dbf
[root@lunar orcl]#

文件的inode已经被覆盖了

这里根据两个两个信息进行恢复文件的操作：
（1）数据库报错告诉我们需要恢复的文件名称：/oradata/orcl/users01.dbf
（2）ext3grep的提示信息告诉我们了从哪里开始写文件： Inode 4358145 is directory “orcl”.

恢复过程如下：

[root@lunar orcl]# ext3grep /dev/sdc1 --restore-file orcl/users01.dbf
Running ext3grep version 0.10.2
WARNING: EXT3_FEATURE_INCOMPAT_RECOVER is set. This either means that your partition is still mounted, and/or the file system is in an unclean state.
Number of groups: 320
Minimum / maximum journal block: 1545 / 35886
Loading journal descriptors... sorting... done
The oldest inode block that is still in the journal, appears to be from 1376706423 = Sat Aug 17 10:27:03 2013
Number of descriptors in journal: 13962; min / max sequence numbers: 3147 / 10088
Writing output to directory RESTORED_FILES/
Loading sdc1.ext3grep.stage2... done
Cannot find an inode number for file "orcl/users01.dbf".   注意这里，比如是 orcl开始的，表示从这个inode开始的，不能写成"/oradata/orcl/users01.dbf或者“/orcl/users01.dbf”
[root@lunar orcl]#

从上面提示我们看到了文件已经恢复出来了，放在 orcl/RESTORED_FILES 下面：

[root@lunar RESTORED_FILES]# pwd
/oradata/orcl/RESTORED_FILES
[root@lunar RESTORED_FILES]# ls -lrt
total 0
[root@lunar RESTORED_FILES]# cd ..
[root@lunar orcl]# ls -lrt
total 1753556
-rw-r----- 1 oracle oinstall  52429312 Apr  4 19:56 sby_redo07.log
-rw-r----- 1 oracle oinstall  52429312 Apr  4 19:56 sby_redo06.log
-rw-r----- 1 oracle oinstall  52429312 Apr  4 19:56 sby_redo05.log
-rw-r----- 1 oracle oinstall  52429312 Apr  4 19:56 sby_redo04.log
-rw-r----- 1 oracle oinstall  30416896 Aug 17 11:27 temp01.dbf
-rw-r----- 1 oracle oinstall  52429312 Aug 17 22:54 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Aug 17 22:57 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Aug 17 22:57 redo01.log
-rw-r----- 1 oracle oinstall  73408512 Aug 17 23:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 754982912 Aug 17 23:05 system01.dbf
-rw-r----- 1 oracle oinstall 576724992 Aug 17 23:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall   9912320 Aug 17 23:06 control02.ctl
-rw-r----- 1 oracle oinstall   9912320 Aug 17 23:06 control01.ctl
-rw-r--r-- 1 root   root           203 Aug 17 23:10 sdc1.ext3grep.stage1
-rw-r--r-- 1 root   root           157 Aug 17 23:10 sdc1.ext3grep.stage2
drwxr-xr-x 2 root   root          4096 Aug 17 23:30 RESTORED_FILES
[root@lunar orcl]# 
[root@lunar orcl]#

完了不行了，被覆盖了。。。否则这一步就会在当前执行ext3grep的目录下找到一个RESTORED_FILES目录，里面就是我们的user01.dbf文件，再之后，你懂的。。
把他copy到/oradata/orcl/users01.dbf，然后执行recover datafile ‘/oradata/orcl/users01.dbf’，在open，就ok了。。。

我们再测试另一个工具extundelete（感觉原理跟ext3grep一样），看看他是不是强大一些，o(∩_∩)o 哈哈：

[root@lunar tmp]# bunzip2 extundelete-0.2.4.tar.bz2 
[root@lunar tmp]# ls
ext3grep-0.10.2-1.el5.rf.x86_64.rpm  extundelete-0.2.4.tar  mapping-root  scim-panel-socket:0-root  spfile.bak
[root@lunar tmp]# tar xvf extundelete-0.2.4.tar 
extundelete-0.2.4/
extundelete-0.2.4/acinclude.m4
extundelete-0.2.4/missing
extundelete-0.2.4/autogen.sh
extundelete-0.2.4/aclocal.m4
extundelete-0.2.4/configure
extundelete-0.2.4/LICENSE
extundelete-0.2.4/README
extundelete-0.2.4/install-sh
extundelete-0.2.4/config.h.in
extundelete-0.2.4/src/
extundelete-0.2.4/src/extundelete.cc
extundelete-0.2.4/src/block.h
extundelete-0.2.4/src/kernel-jbd.h
extundelete-0.2.4/src/insertionops.cc
extundelete-0.2.4/src/block.c
extundelete-0.2.4/src/cli.cc
extundelete-0.2.4/src/extundelete-priv.h
extundelete-0.2.4/src/extundelete.h
extundelete-0.2.4/src/jfs_compat.h
extundelete-0.2.4/src/Makefile.in
extundelete-0.2.4/src/Makefile.am
extundelete-0.2.4/configure.ac
extundelete-0.2.4/depcomp
extundelete-0.2.4/Makefile.in
extundelete-0.2.4/Makefile.am
[root@lunar tmp]# ls
ext3grep-0.10.2-1.el5.rf.x86_64.rpm  extundelete-0.2.4.tar  scim-panel-socket:0-root
extundelete-0.2.4                    mapping-root           spfile.bak
[root@lunar tmp]# cd extundelete-0.2.4
[root@lunar extundelete-0.2.4]# ls
acinclude.m4  autogen.sh   configure     depcomp     LICENSE      Makefile.in  README
aclocal.m4    config.h.in  configure.ac  install-sh  Makefile.am  missing      src
[root@lunar extundelete-0.2.4]# ./configure
Configuring extundelete 0.2.4
Writing generated files to disk
[root@lunar extundelete-0.2.4]# 

[root@lunar extundelete-0.2.4]# extundelete /dev/sdc1  --restore-file /oradata/orcl/user01.dbf
WARNING: EXT3_FEATURE_INCOMPAT_RECOVER is set.
The partition should be unmounted to undelete any files without further data loss.
If the partition is not currently mounted, this message indicates 
it was improperly unmounted, and you should run fsck before continuing.
If you decide to continue, extundelete may overwrite some of the deleted
files and make recovering those files impossible.  You should unmount the
file system and check it with fsck before using extundelete.
Would you like to continue? (y/n) 
y
Loading filesystem metadata ... 320 groups loaded.
Loading journal descriptors ... 14916 descriptors loaded.
Failed to restore file /oradata/orcl/user01.dbf
Could not find correct inode number past inode 2.
Try altering the filename to one of the entries listed below.
File name                                       | Inode number | Deleted status
.                                                 2
..                                                2
lost+found                                        11
orcl                                              4358145
extundelete: Operation not permitted while restoring file.
extundelete: Operation not permitted when trying to examine filesystem
[root@lunar extundelete-0.2.4]# 
[root@lunar extundelete-0.2.4]#

单独恢复文件也没戏。。。。

结论：
经过测试，如果使用恢复全部命令，很多时候可以抢救更多数据，因为extundelete有可能会把恢复的文件改名，并放到其它目录中。
而且，遇到上面的错误后，也可以使用all来试试运气，例如：

[root@lunar /]# extundelete /dev/sdb1 --restore-directory /stage/lunar
Loading filesystem metadata ... 28 groups loaded.
Loading journal descriptors ... 11376 descriptors loaded.
Failed to restore file /stage/lunar
Could not find correct inode number past inode 2.
Try altering the filename to one of the entries listed below.
File name | Inode number | Deleted status
. 2
.. 2
travel 32705 Deleted
backup 327041
bbed 408801 Deleted
dul 179873 Deleted
odu 147169 Deleted
ff 425153 Deleted
lunar 32705
roger 245281 Deleted
fast_recovery_area 261633
lost+found 11
extundelete: Operation not permitted while restoring directory.
extundelete: Operation not permitted when trying to examine filesystem
[root@lunar /]#

[root@lunar /]# extundelete /dev/sdb1 --restore-all
Loading filesystem metadata ... 28 groups loaded.
Loading journal descriptors ... 11376 descriptors loaded.
Searching for recoverable inodes in directory / ...
23 recoverable inodes found.
Looking through the directory structure for deleted files ...
Unable to restore inode 261634 (fast_recovery_area/TRAVEL): Space has been reallocated.
Block 532480 is allocated.
Unable to restore inode 261646 (fast_recovery_area/LUNAR/archivelog/2015_01_13/o1_mf_1_94_bbp9r02b_.arc): Space has been reallocated.
Unable to restore inode 261647 (fast_recovery_area/LUNAR/archivelog/2015_01_13/o1_mf_1_95_bbp9xtxs_.arc): Space has been reallocated.
Unable to restore inode 261648 (fast_recovery_area/LUNAR/archivelog/2015_01_13/o1_mf_1_96_bbp9ydr0_.arc): Space has been reallocated.
Unable to restore inode 261649 (fast_recovery_area/LUNAR/archivelog/2015_01_13/o1_mf_1_97_bbp9z68q_.arc): Space has been reallocated.
9 recoverable inodes still lost.
Block 325403 is allocated.
Unable to restore inode 261635 (file.261635): Space has been reallocated.
Unable to restore inode 261636 (file.261636): Space has been reallocated.
Unable to restore inode 261637 (file.261637): Space has been reallocated.
Block 533126 is allocated.
Unable to restore inode 261640 (file.261640): Space has been reallocated.
Unable to restore inode 261641 (file.261641): Space has been reallocated.
[root@lunar /]#

[root@lunar /]# ls
bin dev home lib lost+found misc opt proc root selinux stage tftpboot u01 var
boot etc kfed lib64 media mnt other RECOVERED_FILES sbin srv sys tmp usr
[root@lunar /]# mv RECOVERED_FILES/ /other
[root@lunar /]#

[root@lunar other]# ls
lost+found RECOVERED_FILES undelete
[root@lunar other]# cd RECOVERED_FILES/
[root@lunar RECOVERED_FILES]# ls
fast_recovery_area file.261638 file.261639 file.32716
[root@lunar RECOVERED_FILES]# ll
total 139896
drwxr-xr-x 3 root root 4096 Jan 13 08:43 fast_recovery_area
-rw-r--r-- 1 root root 46579712 Jan 13 08:50 file.261638
-rw-r--r-- 1 root root 46578688 Jan 13 08:50 file.261639
-rw-r--r-- 1 root root 104865792 Jan 13 08:50 file.32716
drwxr-xr-x 2 root root 4096 Jan 13 08:50 lunar
[root@lunar RECOVERED_FILES]#
[root@lunar lunar]# ll
total 3024
-rw-r--r-- 1 root root 31465472 Jan 13 08:43 temp01.dbf
-rw-r--r-- 1 root root 31465472 Jan 13 08:50 temp01.dbf.v1
[root@lunar lunar]#

看，我这里已经通过恢复全部命令的方式找到了删除的temp01.dbf。

发表在 backup&recovery | 标签为 asm, ext3grep, extundelete, linux, 恢复, 误删除数据 | 留下评论

坑爹的11.2.0.2的新特性： Large partition extent

发表于 2013 年 8 月 12 日由 Lunar

_partition_large_extents 参数从11.2.0.2开始缺省为ture，你不修改为false测试下结果，保证吓你一跳，坑爹的新特性，哼

创建表空间

CREATE SMALLFILE TABLESPACE sp_2008 DATAFILE '/oradata/orcl/sp_2008.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2009 DATAFILE '/oradata/orcl/sp_2009.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2010 DATAFILE '/oradata/orcl/sp_2010.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2011 DATAFILE '/oradata/orcl/sp_2011.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2012 DATAFILE '/oradata/orcl/sp_2012.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2013 DATAFILE '/oradata/orcl/sp_2013.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2014 DATAFILE '/oradata/orcl/sp_2014.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2015 DATAFILE '/oradata/orcl/sp_2015.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2016 DATAFILE '/oradata/orcl/sp_2016.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2017 DATAFILE '/oradata/orcl/sp_2017.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2018 DATAFILE '/oradata/orcl/sp_2018.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2019 DATAFILE '/oradata/orcl/sp_2019.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2020 DATAFILE '/oradata/orcl/sp_2020.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2021 DATAFILE '/oradata/orcl/sp_2021.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2022 DATAFILE '/oradata/orcl/sp_2022.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2023 DATAFILE '/oradata/orcl/sp_2023.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2024 DATAFILE '/oradata/orcl/sp_2024.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_2025 DATAFILE '/oradata/orcl/sp_2025.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE SMALLFILE TABLESPACE sp_max DATAFILE '/oradata/orcl/sp_max.dbf' SIZE 2M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

设置参数值

SQL> alter system set "_partition_large_extents"=false;

System altered.

SQL> alter system set deferred_segment_creation=false;

System altered.

创建表

SQL> create table test_com_partition_1
  2  (
  3  name varchar2(4000) not null,
  4  aaaaa number not null,
  5  bbbbb varchar2(180) not null,
  6  ccccc varchar2(4000),
  7  constraint pk_test_com_partition_1 primary key(name)
  8  )
  9  partition by range(aaaaa) interval (1)
 10  --第二级分区按年度划分
 11  subpartition by range (bbbbb)
 12  subpartition template
 13  (
 14  subpartition sp_2008 values less than ('2009') tablespace sp_2008,
 15  subpartition sp_2009 values less than ('2010') tablespace sp_2009,
 16  subpartition sp_2010 values less than ('2011') tablespace sp_2010,
 17  subpartition sp_2011 values less than ('2012') tablespace sp_2011,
 18  subpartition sp_2012 values less than ('2013') tablespace sp_2012,
 19  subpartition sp_2013 values less than ('2014') tablespace sp_2013,
 20  subpartition sp_2014 values less than ('2015') tablespace sp_2014,
 21  subpartition sp_2015 values less than ('2016') tablespace sp_2015,
 22  subpartition sp_2016 values less than ('2017') tablespace sp_2016,
 23  subpartition sp_2017 values less than ('2018') tablespace sp_2017,
 24  subpartition sp_2018 values less than ('2019') tablespace sp_2018,
 25  subpartition sp_2019 values less than ('2020') tablespace sp_2019,
 26  subpartition sp_2020 values less than ('2021') tablespace sp_2020,
 27  subpartition sp_2021 values less than ('2022') tablespace sp_2021,
 28  subpartition sp_2022 values less than ('2023') tablespace sp_2022,
 29  subpartition sp_2023 values less than ('2024') tablespace sp_2023,
 30  subpartition sp_2024 values less than ('2025') tablespace sp_2024,
 31  subpartition sp_2025 values less than ('2026') tablespace sp_2025,
 32  subpartition sp_max values less than (maxvalue) tablespace sp_max
 33  )
 34  (partition part_init values less than (1))
 35  enable row movement;

Table created.

SQL> select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_segments  where tablespace_name like 'SP%' group by TABLESPACE_NAME;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
SP_2018                                       .0625
SP_2022                                       .0625
SP_2021                                       .0625
SP_2025                                       .0625
SP_2011                                       .0625
SP_2008                                       .0625
SP_2020                                       .0625
SP_2012                                       .0625
SP_2010                                       .0625
SP_MAX                                        .0625
SP_2024                                       .0625
SP_2019                                       .0625
SP_2015                                       .0625
SP_2014                                       .0625
SP_2013                                       .0625
SP_2023                                       .0625
SP_2017                                       .0625
SP_2016                                       .0625
SP_2009                                       .0625

19 rows selected.


+------------------------------------------------------------------------+
| SEGMENT INFORMATION                                                    |
+------------------------------------------------------------------------+

Segment Name         Partition Name       Segment Type                   Num Rows              Bytes Tablespace           TABLESPACE_BYTES Last Analyzed
-------------------- -------------------- -------------------- ------------------ ------------------ -------------------- ---------------- -----------------------
TEST_COM_PARTITION_1 PART_INIT_SP_MAX     TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     PART_INIT_SP_2025    TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     PART_INIT_SP_2024    TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     PART_INIT_SP_2023    TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     PART_INIT_SP_2022    TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     PART_INIT_SP_2021    TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     PART_INIT_SP_2020    TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     PART_INIT_SP_2019    TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     PART_INIT_SP_2018    TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     PART_INIT_SP_2017    TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     PART_INIT_SP_2016    TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     PART_INIT_SP_2015    TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     PART_INIT_SP_2014    TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     PART_INIT_SP_2013    TABLE SUBPARTITION                                  65,536 SP_2013                      52428800
                     PART_INIT_SP_2012    TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     PART_INIT_SP_2011    TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     PART_INIT_SP_2010    TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     PART_INIT_SP_2009    TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     PART_INIT_SP_2008    TABLE SUBPARTITION                                  65,536 SP_2008                       2097152

19 rows selected.

加载数据

SQL> begin      
  2  for i in 1000 .. 3000 loop
  3  insert into test_com_partition_1 values (to_char(i)||lpad('济南',3900,'*'),mod(i,5),'2013',rpad('济南',4000,'*'));
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.


+------------------------------------------------------------------------+
| SEGMENT INFORMATION                                                    |
+------------------------------------------------------------------------+

Segment Name         Partition Name       Segment Type                   Num Rows              Bytes Tablespace           TABLESPACE_BYTES Last Analyzed
-------------------- -------------------- -------------------- ------------------ ------------------ -------------------- ---------------- -----------------------
TEST_COM_PARTITION_1 PART_INIT_SP_2008    TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     SYS_SUBP261          TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     SYS_SUBP281          TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     SYS_SUBP301          TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     SYS_SUBP321          TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     PART_INIT_SP_2009    TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     SYS_SUBP262          TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     SYS_SUBP282          TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     SYS_SUBP302          TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     SYS_SUBP322          TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     PART_INIT_SP_2010    TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     SYS_SUBP263          TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     SYS_SUBP283          TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     SYS_SUBP303          TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     SYS_SUBP323          TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     PART_INIT_SP_2011    TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     SYS_SUBP264          TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     SYS_SUBP284          TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     SYS_SUBP304          TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     SYS_SUBP324          TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     PART_INIT_SP_2012    TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     SYS_SUBP265          TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     SYS_SUBP285          TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     SYS_SUBP305          TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     SYS_SUBP325          TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
<strong>                     PART_INIT_SP_2013    TABLE SUBPARTITION                               4,194,304 SP_2013                      52428800
                     SYS_SUBP266          TABLE SUBPARTITION                               4,194,304 SP_2013                      52428800
                     SYS_SUBP286          TABLE SUBPARTITION                               4,194,304 SP_2013                      52428800
                     SYS_SUBP306          TABLE SUBPARTITION                               4,194,304 SP_2013                      52428800
                     SYS_SUBP326          TABLE SUBPARTITION                               4,194,304 SP_2013    </strong>                  52428800
                     PART_INIT_SP_2014    TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     SYS_SUBP267          TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     SYS_SUBP287          TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     SYS_SUBP307          TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     SYS_SUBP327          TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     PART_INIT_SP_2015    TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     SYS_SUBP268          TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     SYS_SUBP288          TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     SYS_SUBP308          TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     SYS_SUBP328          TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     PART_INIT_SP_2016    TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     SYS_SUBP269          TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     SYS_SUBP289          TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     SYS_SUBP309          TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     SYS_SUBP329          TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     PART_INIT_SP_2017    TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     SYS_SUBP270          TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     SYS_SUBP290          TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     SYS_SUBP310          TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     SYS_SUBP330          TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     PART_INIT_SP_2018    TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     SYS_SUBP271          TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     SYS_SUBP291          TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     SYS_SUBP311          TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     SYS_SUBP331          TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     PART_INIT_SP_2019    TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     SYS_SUBP272          TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     SYS_SUBP292          TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     SYS_SUBP312          TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     SYS_SUBP332          TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     PART_INIT_SP_2020    TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     SYS_SUBP273          TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     SYS_SUBP293          TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     SYS_SUBP313          TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     SYS_SUBP333          TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     PART_INIT_SP_2021    TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     SYS_SUBP274          TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     SYS_SUBP294          TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     SYS_SUBP314          TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     SYS_SUBP334          TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     PART_INIT_SP_2022    TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     SYS_SUBP275          TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     SYS_SUBP295          TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     SYS_SUBP315          TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     SYS_SUBP335          TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     PART_INIT_SP_2023    TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     SYS_SUBP276          TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     SYS_SUBP296          TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     SYS_SUBP316          TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     SYS_SUBP336          TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     PART_INIT_SP_2024    TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     SYS_SUBP277          TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     SYS_SUBP297          TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     SYS_SUBP317          TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     SYS_SUBP337          TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     PART_INIT_SP_2025    TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     SYS_SUBP278          TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     SYS_SUBP298          TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     SYS_SUBP318          TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     SYS_SUBP338          TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     PART_INIT_SP_MAX     TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     SYS_SUBP279          TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     SYS_SUBP299          TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     SYS_SUBP319          TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     SYS_SUBP339          TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152

95 rows selected.


SQL> begin      
  2  for i in 3001 .. 4000 loop
  3  insert into test_com_partition_1 values (to_char(i)||lpad('济南',3900,'*'),mod(i,5),'2013',rpad('济南',4000,'*'));
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> @part_seg

+------------------------------------------------------------------------+
| Report   : PARTITION Table Information                                 |
| Instance : orcl                                                        |
+------------------------------------------------------------------------+

Enter table owner : lunar
Enter table name  : test_com_partition_1

+------------------------------------------------------------------------+
| SEGMENT INFORMATION                                                    |
+------------------------------------------------------------------------+

Segment Name         Partition Name       Segment Type                   Num Rows              Bytes Tablespace           TABLESPACE_BYTES Last Analyzed
-------------------- -------------------- -------------------- ------------------ ------------------ -------------------- ---------------- -----------------------
TEST_COM_PARTITION_1 PART_INIT_SP_2008    TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     SYS_SUBP261          TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     SYS_SUBP281          TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     SYS_SUBP301          TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     SYS_SUBP321          TABLE SUBPARTITION                                  65,536 SP_2008                       2097152
                     PART_INIT_SP_2009    TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     SYS_SUBP262          TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     SYS_SUBP282          TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     SYS_SUBP302          TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     SYS_SUBP322          TABLE SUBPARTITION                                  65,536 SP_2009                       2097152
                     PART_INIT_SP_2010    TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     SYS_SUBP263          TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     SYS_SUBP283          TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     SYS_SUBP303          TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     SYS_SUBP323          TABLE SUBPARTITION                                  65,536 SP_2010                       2097152
                     PART_INIT_SP_2011    TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     SYS_SUBP264          TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     SYS_SUBP284          TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     SYS_SUBP304          TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     SYS_SUBP324          TABLE SUBPARTITION                                  65,536 SP_2011                       2097152
                     PART_INIT_SP_2012    TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     SYS_SUBP265          TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     SYS_SUBP285          TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     SYS_SUBP305          TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
                     SYS_SUBP325          TABLE SUBPARTITION                                  65,536 SP_2012                       2097152
<strong>                     PART_INIT_SP_2013    TABLE SUBPARTITION                               5,242,880 SP_2013                      52428800
                     SYS_SUBP266          TABLE SUBPARTITION                               5,242,880 SP_2013                      52428800
                     SYS_SUBP286          TABLE SUBPARTITION                               5,242,880 SP_2013                      52428800
                     SYS_SUBP306          TABLE SUBPARTITION                               5,242,880 SP_2013                      52428800
                     SYS_SUBP326          TABLE SUBPARTITION                               5,242,880 SP_2013                      52428800</strong>
                     PART_INIT_SP_2014    TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     SYS_SUBP267          TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     SYS_SUBP287          TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     SYS_SUBP307          TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     SYS_SUBP327          TABLE SUBPARTITION                                  65,536 SP_2014                       2097152
                     PART_INIT_SP_2015    TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     SYS_SUBP268          TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     SYS_SUBP288          TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     SYS_SUBP308          TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     SYS_SUBP328          TABLE SUBPARTITION                                  65,536 SP_2015                       2097152
                     PART_INIT_SP_2016    TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     SYS_SUBP269          TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     SYS_SUBP289          TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     SYS_SUBP309          TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     SYS_SUBP329          TABLE SUBPARTITION                                  65,536 SP_2016                       2097152
                     PART_INIT_SP_2017    TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     SYS_SUBP270          TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     SYS_SUBP290          TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     SYS_SUBP310          TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     SYS_SUBP330          TABLE SUBPARTITION                                  65,536 SP_2017                       2097152
                     PART_INIT_SP_2018    TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     SYS_SUBP271          TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     SYS_SUBP291          TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     SYS_SUBP311          TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     SYS_SUBP331          TABLE SUBPARTITION                                  65,536 SP_2018                       2097152
                     PART_INIT_SP_2019    TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     SYS_SUBP272          TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     SYS_SUBP292          TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     SYS_SUBP312          TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     SYS_SUBP332          TABLE SUBPARTITION                                  65,536 SP_2019                       2097152
                     PART_INIT_SP_2020    TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     SYS_SUBP273          TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     SYS_SUBP293          TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     SYS_SUBP313          TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     SYS_SUBP333          TABLE SUBPARTITION                                  65,536 SP_2020                       2097152
                     PART_INIT_SP_2021    TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     SYS_SUBP274          TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     SYS_SUBP294          TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     SYS_SUBP314          TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     SYS_SUBP334          TABLE SUBPARTITION                                  65,536 SP_2021                       2097152
                     PART_INIT_SP_2022    TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     SYS_SUBP275          TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     SYS_SUBP295          TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     SYS_SUBP315          TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     SYS_SUBP335          TABLE SUBPARTITION                                  65,536 SP_2022                       2097152
                     PART_INIT_SP_2023    TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     SYS_SUBP276          TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     SYS_SUBP296          TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     SYS_SUBP316          TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     SYS_SUBP336          TABLE SUBPARTITION                                  65,536 SP_2023                       2097152
                     PART_INIT_SP_2024    TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     SYS_SUBP277          TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     SYS_SUBP297          TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     SYS_SUBP317          TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     SYS_SUBP337          TABLE SUBPARTITION                                  65,536 SP_2024                       2097152
                     PART_INIT_SP_2025    TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     SYS_SUBP278          TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     SYS_SUBP298          TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     SYS_SUBP318          TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     SYS_SUBP338          TABLE SUBPARTITION                                  65,536 SP_2025                       2097152
                     PART_INIT_SP_MAX     TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     SYS_SUBP279          TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     SYS_SUBP299          TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     SYS_SUBP319          TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152
                     SYS_SUBP339          TABLE SUBPARTITION                                  65,536 SP_MAX                        2097152

95 rows selected.

SQL>

查看os file 大小

SQL> !ls -lhtr /oradata/orcl/sp*
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_max.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2025.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2024.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2023.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2022.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2021.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2020.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2019.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2018.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2017.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2016.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2015.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2014.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2012.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2011.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2010.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2009.dbf
-rw-r----- 1 oracle oinstall 2.1M Aug 12 20:50 /oradata/orcl/sp_2008.dbf
-rw-r----- 1 oracle oinstall  51M Aug 12 20:50 /oradata/orcl/sp_2013.dbf --注意文件大小

发表在 Oracle 11.1 & Oracle11.2 | 标签为 Large partition extent | 留下评论

站内搜索

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
文章归档
文章归档
近期文章
近期评论
- 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

站内搜索

Oracle证书

分类目录

文章归档

近期文章

近期评论