maintain

坏块处理一例 (1)

May 31, 2012 maintain, oracle 2 comments

测试AIX从linux导入数据时 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS阶段停留了很久:

ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”PRO_MOBILE_CHARGE_REFOUND” created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”PRO_USER_FINISH_GRF_BATCH” created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”PRO_USER_FINISH_GRF” created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”SERIES_COUPON_CANCELORDERZERO2″ created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:”USER_DATA2″.”SERIES_COUPON_CANCELNOTZERO2″ created with compilation warnings
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_AREA” created with compilation warnings
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_CITY” created with compilation warnings
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_CITY” created with compilation warnings
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_COUNTY” created with compilation warnings
ORA-39082: Object type TRIGGER:”USER_DATA2″.”TRIG_DML_COUNTY” created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

发现坏块 来记录一下操作过程:

Import> status

Job: SYS_IMPORT_FULL_01
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 154,504,859,144
Percent Done: 99
Current Parallelism: 1
Job Error Count: 0
Dump File: /data1/full.dmp

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Type: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Completed Objects: 80
Worker Parallelism: 1

Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

数据已经导入完成 只是在imp TABLE_STATISTICS时候hang住 直接kill掉该job

SQL> select count(*) from MON_MODS$;
select count(*) from MON_MODS$
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 3186)
ORA-01110: data file 1: ‘/data1/oracle/oradata/testdb/system01.dbf’

block 3186 标记为坏块:

sys@testdb> select
2 object_id||’-‘||segment_name||’/’||partition_name segment_name,
segment_type,
RELATIVE_FNO
from dba_extents a
,dba_objects b
where file_id = ‘1’
and 3186 between block_id and block_id + blocks – 1
and a.SEGMENT_NAME = b.OBJECT_NAME
and (a.PARTITION_NAME = b.SUBOBJECT_NAME or a.PARTITION_NAME is null) 3 4 5 6 7 8 9 10 ;

SEGMENT_NAME SEGMENT_TYPE RELATIVE_FNO
——————————————————————————————————————————————————— —————— ————
461-MON_MODS$/ TABLE 1

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
1 3186 1 0 FRACTURED

确定segment为 MON_MODS$ 对于这个DB来说 无备份 无法recover 这个block块

对于这个表的解释为统计信息收集记录,关闭自动统计信息收集这个表也失去了功效


— Create table
create table MON_MODS$
(
obj# NUMBER,
inserts NUMBER,
updates NUMBER,
deletes NUMBER,
timestamp DATE,
flags NUMBER,
drop_segments NUMBER
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 200K
next 104K
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create unique index I_MON_MODS$_OBJ on MON_MODS$ (OBJ#)
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

OTN 对于这个case 也给出过解决方法 eg:

rename to ; (preferently log in with owner)
where: sys.mon_mods$ sys.mon_mods$_corrupted

–> In the same session:
begin
SYS.DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘SYS’,’MON_MOD$_CORRUPTED’);
end;

–> after finish, try (remember that run in the same session):

create table SYS.MON_MODS$ as
select * from SYS.MON_MODS$_CORRUPTED

对于这个case 我们直接跳过这个block

SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’MON_MODS$’);

PL/SQL procedure successfully completed.

sys@testdb> select count(*) from MON_MODS$;

COUNT(*)
———-
684

dd了这个block 期待惜分飞同学的repair测试

Scripts: report the index fragmentation status

May 14, 2012 maintain, oracle No comments

/* ************************************************************* */
/* Index Fragmentation Status (idsfrag.sql): */
/* */
/* This script will report the index fragmentation status */
/* for a schema. */
/* */
/* Note: – Do not run this scrip during peak processing hours!!! */
/* – This script will fail for locked tables. */
/* */
/* ************************************************************* */


prompt — Drop and create temporary table to hold stats…
drop table my_index_stats
/
create table my_index_stats (
index_name varchar2(30),
height number(8),
del_lf_rows number(8),
if_rows number(8),
distinct_keys number(8),
rows_per_key number(10,2),
blks_gets_per_access number(10,2)
)
/

prompt — Save script which we will later use to populate the above table…
insert into my_index_stats
select NAME, HEIGHT, DEL_LF_ROWS,LF_ROWS, DISTINCT_KEYS, ROWS_PER_KEY,
BLKS_GETS_PER_ACCESS
from INDEX_STATS
— Note this open line…

save /tmp/save_index_stats.sql replace

prompt
prompt — Spool listing with validate commands…
col line1 newline
col line2 newline
col line3 newline
set pagesize 0
set echo off
set termout off
set trimspool on
set feed off
set linesize 200
spool /tmp/validate_indexes.sql
select ‘prompt Process table ‘||owner||’.’||table_name||
‘, index ‘||index_name||’…’ line1,
‘validate index ‘||owner||’.’||index_name||’;’ line2,
‘@/tmp/save_index_stats.sql’ line3
from sys.dba_indexes where owner = ‘SCOTT’
order by table_name, index_name
/
spool off
set termout on
set feed on

prompt
prompt — Run script to validate indexes…
@/tmp/validate_indexes.sql

prompt — Print nice report…
set pagesize 50000
set trimspool on
col height format 99999
col del_rows format 9999999
col rows/key format 999999.9
spool idxfrag.lst
select INDEX_NAME, HEIGHT, DEL_LF_ROWS “DEL_ROWS”,LF_ROWS, DISTINCT_KEYS “DIST KEYS”,
ROWS_PER_KEY “ROWS/KEY”,
BLKS_GETS_PER_ACCESS “BLKS/ACCESS”
from MY_INDEX_STATS
/
spool off

— Cleanup
drop table my_index_stats
/
! rm /tmp/validate_indexes.sql
! rm /tmp/save_index_stats.sql

prompt
prompt Report is in idxfrag.lst
prompt Done!!!


An index should be considered for rebuilding under any of the following conditions:

The percentage of deleted rows exceeds 30% of the total, i.e. if
1.del_lf_rows / lf_rows > 0.3.
2.If the ‘HEIGHT’ is greater than 4.
3.If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large 4.number of deletes, indicating that the index should be rebuilt.

11gR2 rac ora.asm error (ASM)

April 26, 2012 maintain, oracle, RAC No comments ,

ASM – ora.asm error

11.2.0.3 RAC CRS 无法启动

[ohasd(25286)]CRS-2112:The OLR service started on node db-41.
2012-04-25 14:46:13.120
[ohasd(25286)]CRS-1301:Oracle High Availability Service started on node db-41.
2012-04-25 14:46:13.120
[ohasd(25286)]CRS-8017:location: /etc/oracle/lastgasp has 2 reboot advisory log files, 0 were announced and 0 errors occurred
2012-04-25 14:46:14.181
[/data/11.2.0/grid/bin/orarootagent.bin(25332)]CRS-5016:Process “/data/11.2.0/grid/bin/acfsload” spawned by agent “/data/11.2.0/grid/bin/orarootagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/ohasd/orarootagent_root/orarootagent_root.log”
2012-04-25 14:46:17.031
[gpnpd(25510)]CRS-2328:GPNPD started on node db-41.
2012-04-25 14:46:20.794
[cssd(25593)]CRS-1713:CSSD daemon is started in clustered mode
2012-04-25 14:46:22.520
[ohasd(25286)]CRS-2767:Resource state recovery not attempted for ‘ora.diskmon’ as its target state is OFFLINE
2012-04-25 14:46:40.715
[cssd(25593)]CRS-1707:Lease acquisition for node db-41 number 1 completed
2012-04-25 14:46:42.003
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote03; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:42.008
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote02; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:42.015
[cssd(25593)]CRS-1605:CSSD voting file is online: /dev/mapper/Vote01; details in /data/11.2.0/grid/log/db-41/cssd/ocssd.log.
2012-04-25 14:46:52.173
[cssd(25593)]CRS-1601:CSSD Reconfiguration complete. Active nodes are db-41 .
2012-04-25 14:46:54.177
[ctssd(25738)]CRS-2407:The new Cluster Time Synchronization Service reference node is host db-41.
2012-04-25 14:46:54.177
[ctssd(25738)]CRS-2401:The Cluster Time Synchronization Service started on host db-41.
[client(25843)]CRS-10001:25-Apr-12 14:46 ACFS-9391: Checking for existing ADVM/ACFS installation.
[client(25848)]CRS-10001:25-Apr-12 14:46 ACFS-9392: Validating ADVM/ACFS installation files for operating system.
[client(25850)]CRS-10001:25-Apr-12 14:46 ACFS-9393: Verifying ASM Administrator setup.
[client(25853)]CRS-10001:25-Apr-12 14:46 ACFS-9308: Loading installed ADVM/ACFS drivers.
[client(25856)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading ‘oracleoks.ko’ driver.
[client(25913)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading ‘oracleadvm.ko’ driver.
[client(25974)]CRS-10001:25-Apr-12 14:46 ACFS-9154: Loading ‘oracleacfs.ko’ driver.
[client(26099)]CRS-10001:25-Apr-12 14:46 ACFS-9327: Verifying ADVM/ACFS devices.
[client(26103)]CRS-10001:25-Apr-12 14:46 ACFS-9156: Detecting control device ‘/dev/asm/.asm_ctl_spec’.
[client(26107)]CRS-10001:25-Apr-12 14:46 ACFS-9156: Detecting control device ‘/dev/ofsctl’.
[client(26113)]CRS-10001:25-Apr-12 14:46 ACFS-9322: completed
2012-04-25 14:47:06.975
[ohasd(25286)]CRS-2807:Resource ‘ora.asm’ failed to start automatically.

[ctssd(25738)]CRS-2405:The Cluster Time Synchronization Service on host db-41 is shutdown by user
2012-04-25 14:51:25.852
[/data/11.2.0/grid/bin/orarootagent.bin(25523)]CRS-5016:Process “/data/11.2.0/grid/bin/acfsload” spawned by agent “/data/11.2.0/grid/bin/orarootagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/ohasd/orarootagent_root/orarootagent_root.log”
2012-04-25 14:51:35.716
[cssd(25593)]CRS-1603:CSSD on node db-41 shutdown by user.
2012-04-25 14:51:35.725
[ohasd(25286)]CRS-2767:Resource state recovery not attempted for ‘ora.cssdmonitor’ as its target state is OFFLINE
2012-04-25 14:51:36.224
[cssd(25593)]CRS-1660:The CSS daemon shutdown has completed
2012-04-25 14:51:38.144
[gpnpd(25510)]CRS-2329:GPNPD on node db-41 shutdown.

重建spfile :


+ASM.asm_diskgroups=’DATA’,’ARCH’
*.asm_diskstring=’/dev/mapper/*’
*.asm_power_limit=1
*.diagnostic_dest=’/data/app/oracle’
*.instance_type=’asm’
*.large_pool_size=12M
#*.local_listener=’LISTENER_+ASM’
*.remote_login_passwordfile=’EXCLUSIVE’

eg: pfile 拉起 ASM instance mount data group

SQL>create spfile=’+DATA’ from pfile=’xxx’;

[grid@db-41 ~]$ sqlplus ‘/as sysasm’

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 26 15:21:33 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter spfile;

NAME TYPE
———————————— ———————————
VALUE
——————————
spfile string
+DATA/yhd-cluster/asmparameter
file/registry.253.781624261
SQL>

重新启动CRS OK

[client(2162)]CRS-10001:26-Apr-12 14:47 ACFS-9392: Validating ADVM/ACFS installation files for operating system.
[client(2164)]CRS-10001:26-Apr-12 14:47 ACFS-9393: Verifying ASM Administrator setup.
[client(2167)]CRS-10001:26-Apr-12 14:47 ACFS-9308: Loading installed ADVM/ACFS drivers.
[client(2170)]CRS-10001:26-Apr-12 14:47 ACFS-9154: Loading ‘oracleoks.ko’ driver.
[client(2227)]CRS-10001:26-Apr-12 14:47 ACFS-9154: Loading ‘oracleadvm.ko’ driver.
[client(2286)]CRS-10001:26-Apr-12 14:47 ACFS-9154: Loading ‘oracleacfs.ko’ driver.
[client(2415)]CRS-10001:26-Apr-12 14:47 ACFS-9327: Verifying ADVM/ACFS devices.
[client(2418)]CRS-10001:26-Apr-12 14:47 ACFS-9156: Detecting control device ‘/dev/asm/.asm_ctl_spec’.
[client(2422)]CRS-10001:26-Apr-12 14:47 ACFS-9156: Detecting control device ‘/dev/ofsctl’.
[client(2428)]CRS-10001:26-Apr-12 14:47 ACFS-9322: completed
2012-04-26 14:47:44.001
[crsd(2755)]CRS-1012:The OCR service started on node db-41.
2012-04-26 14:47:44.472
[evmd(2146)]CRS-1401:EVMD started on node db-41.
2012-04-26 14:47:46.297
[crsd(2755)]CRS-1201:CRSD started on node db-41.
2012-04-26 14:47:47.586
[/data/11.2.0/grid/bin/oraagent.bin(2880)]CRS-5016:Process “/data/11.2.0/grid/bin/lsnrctl” spawned by agent “/data/11.2.0/grid/bin/oraagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/crsd/oraagent_grid/oraagent_grid.log”
2012-04-26 14:47:47.592
[/data/11.2.0/grid/bin/oraagent.bin(2880)]CRS-5016:Process “/data/11.2.0/grid/bin/lsnrctl” spawned by agent “/data/11.2.0/grid/bin/oraagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/crsd/oraagent_grid/oraagent_grid.log”
2012-04-26 14:47:47.685
[/data/11.2.0/grid/bin/oraagent.bin(2880)]CRS-5016:Process “/data/11.2.0/grid/opmn/bin/onsctli” spawned by agent “/data/11.2.0/grid/bin/oraagent.bin” for action “check” failed: details at “(:CLSN00010:)” in “/data/11.2.0/grid/log/db-41/agent/crsd/oraagent_grid/oraagent_grid.log”
2012-04-26 14:47:47.832
[crsd(2755)]CRS-2772:Server ‘db-41’ has been assigned to pool ‘Generic’.
2012-04-26 14:47:47.833
[crsd(2755)]CRS-2772:Server ‘db-41’ has been assigned to pool ‘ora.user’.
2012-04-26 14:51:13.534

针对这个问题 可以提出这样的疑问 CRS 如何在 datagroup 还没有mount的时候 读取 spfile 而且CRS如何知道在哪去读 spfile 下面这篇精彩的文章 很详细的介绍了process读取的整个过程:

Oracle 11gR2 have many new futures in Grid Computing area. One of them is Oracle Local Repository (OLR), this repository designed to store information and profiles for local resources, resources that dedicated to particular node. It improves the performance of accessing local resources profile information, redundancy and manageability. In Grid Infrastructure RAC configuration there is usual one global shared OCR and OLR’s on each node. In Oracle Restart environment there is only OLR repository. In 11g R2 there is also new feature, Grid Plug and Play (GPNP), as the name implies, it helps to automate and simplify some of the aspects of grid administration. GPNP maintains profile, it is XML file that stores the configuration information of some components maintained by GPNP, for example vips and interconnect information is stored here.

But in this post, I wont to discuss another new feature of 11g R2, it is the possibility to store the server parameter file (spfile) on the ASM disks, also for ASM instance itself! And there is some reasonable questions arise:

1.How process can read the spfile from ASM disks, if they are not mounted yet
2.How process knows where to get spfile if there is no init.ora file in $ORACLE_HOME/dbs with spfile=[path to spfile] parameter
3.What about asm_diskstring parameter?

Third point is important for ASM instance because, before reading spfile from ASM disks, we need to identify them, to identify them we need spfile to get asm_diskstring parameter!

To make clear all this points I will use system calls tracing tools, to trace oracle processes. We can use tools like Dtrace, strace, truss and so on, it depends on platform, I will use strace because I am on Linux.

My environment is Oracle Grid Infrastructure 11g R2 (11.2.0.1.0) in Oracle Restart mode. I simulated ASM disks using loop devices, /dev/loop*, I have 5 disks

bash> /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5

SQL> conn / as sysasm
Connected.
SQL> col path format a15

SQL> select a.name,a.state,b.name,b.path from v$asm_diskgroup a, v$asm_disk b where a.group_number=b.group_number order by b.name;

NAME STATE NAME PATH
—————————— ———– —————————— —————
DGROUP1 MOUNTED DISK1 ORCL:DISK1
DGROUP1 MOUNTED DISK2 ORCL:DISK2
DGROUP2 MOUNTED DISK3 ORCL:DISK3
DGROUP2 MOUNTED DISK4 ORCL:DISK4
DGROUP2 MOUNTED DISK5 ORCL:DISK5

As seen from the listing, I have two disk groups DGROUP1 and DGROUP2. My spfile is located on DGROUP1

SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———– —————————————————-
spfile string +DGROUP1/asm/asmparameterfile/registry.253.740659373

Lets make some tests

SQL> conn / as sysasm
Connected.

SQL> startup
ASM instance started

Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>
SQL> show parameter power

NAME TYPE VALUE
———————————— ———– ——————————
asm_power_limit integer 1
SQL> alter system set asm_power_limit=3;

System altered.

SQL> show parameter power

NAME TYPE VALUE
———————————— ———– ——————————
asm_power_limit integer 3

SQL>–We set asm_power_limit to 3 in spfile

SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown

SQL> !crs_stat -t
Name Type Target State Host
————————————————————
ora.DGROUP1.dg ora….up.type OFFLINE OFFLINE
ora.DGROUP2.dg ora….up.type OFFLINE OFFLINE
ora….ER.lsnr ora….er.type ONLINE ONLINE testdb03
ora.asm ora.asm.type OFFLINE OFFLINE
ora.cssd ora.cssd.type ONLINE ONLINE testdb03
ora.diskmon ora….on.type ONLINE ONLINE testdb03

SQL>– Lets start instance in nomount mode, it will not mount the diskgroups

SQL> startup nomount;
ASM instance started

Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes
ASM Cache 25165824 bytes

SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———– —————————————————-
spfile string +DGROUP1/asm/asmparameterfile/registry.253.740659373

SQL> show parameter power

NAME TYPE VALUE
———————————— ———– ——————————
asm_power_limit integer 3

SQL> select * from v$spparameter;
select * from v$spparameter
*
ERROR at line 1:
ORA-15001: diskgroup “DGROUP1” does not exist or is not mounted

SQL> alter system set asm_power_limit=10;
alter system set asm_power_limit=10
*
ERROR at line 1:
ORA-32000: write to SPFILE requested but SPFILE is not modifiable

SQL> !asmcmd
ASMCMD> ls
ASMCMD> exit

SQL> !crs_stat -t
Name Type Target State Host
————————————————————
ora.DGROUP1.dg ora….up.type OFFLINE OFFLINE
ora.DGROUP2.dg ora….up.type OFFLINE OFFLINE
ora….ER.lsnr ora….er.type ONLINE ONLINE testdb03
ora.asm ora.asm.type ONLINE ONLINE testdb03
ora.cssd ora.cssd.type ONLINE ONLINE testdb03
ora.diskmon ora….on.type ONLINE ONLINE testdb03

So, what we see, we were able to start ASM instance without mounting the disks, our process picked up correct spfile. The command SHOW PARAMETER reads the parameter values from the memory, but if we try to read directly from spfile (select from v$spparameter) or write to it (alter system set) we will get errors, because diskgroups not mounted yet. It means that our process was read spfile directly from ASM disks. The design about from which disk to read it made based on information from disk header. I use ASMlib, and my asm_diskstring parameter is equal to default value null, ASM instance by default scans the /dev/oracleasm/disks/* on Linux, that is why process found my disks properly. But what if we use HP-UX and our disks is multipathing disks in /dev/rdisk/*, ASM will not scan them by default, we need to use asm_diskstring parameter, how our process will read asm_diskstring parameter before accessing spfile?

Lets start sqlplus, after we will connect as sysasm it will initialize server process, this process will do all job, that is why I will trace this server process

bash> sqlplus /nolog
SQL> conn / as sysasm
Connected.
SQL> !ps -aefH
.
.
oracle 15505 4255 0 13:26 pts/1 00:00:00 sqlplus
oracle 15507 15505 0 13:26 ? 00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
.
.
SQL>

Our sserver process number is 15507, now I will use strace to trace it


bash> strace -o userproc.out -p 15507

start the instance with nomount option, OCSSD must be running, or instace will not start

SQL> startup nomount;

Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes
ASM Cache 25165824 bytes

SQL>

Now we can analyze the contents of the userproc.out, I will not list all contents, just useful parts of it

First row is

read(9, “001\6\3\212\6\376\377\377\377\1\376\377\377\377\376\377\377\377″…, 8208) = 49

it is our “startup nomount” command transmitted to server process from sqlplus by socket

connect(6, {sa_family=AF_FILE, path=”/var/tmp/.oracle/sOCSSD_LL_testhost_”…}, 110) = 0
open(“/u01/oracle/product/grid11g/auth/css/testdb03/A6857753/84b0b692”, O_WRONLY|O_CREAT|O_EXCL, 0644) = 13
write(13, “\nS\336[“, 4) = 4
close(13)

from this lines we can see that our process connects to socket file of OCSSD /var/tmp/.oracle/sOCSSD_LL_testhost_ to communicate with it and authenticates itself. That is why it impossible to start instance without OCSSD, if process could not connect to this socket, it would fail. Then it establishes communication with OHASD (Oracle High Availability Services Deamon) through the socket file /var/tmp/.oracle/sCRSD_UI_SOCKET, after exchange messages with OHASD it will get information about the location of spfile and asm_diskstring parameter

access(“/var/tmp/.oracle/sCRSD_UI_SOCKET”, F_OK) = 0
connect(14, {sa_family=AF_FILE, path=”/var/tmp/.oracle/sCRSD_UI_SOCKET”…}, 110) = 0
open(“/u01/oracle/product/grid11g/auth/ohasd/testdb03/A4972914/5140f6df”, O_WRONLY|O_CREAT|O_EXCL, 0644) = 15
write(15, “\20\7\3519”, 4) = 4
close(15)
write(14, “4\2\2\1\1\1\1\3\1T\235\376\t”…, 52) = 52
write(14, “8\3\2\1\1\1\1\4\1T\235\376\t”…, 56) = 56
read(14, “8\3\2\1\1\1\1\3\1T\235\376\t”…, 32768) = 56
write(14, “\212\1PC\v\2\2\5\1T\235\376\t”…, 394) = 394
read(14, “\366\nPC\v\2\2\4\1T\235\376\t”…, 32768) = 2806
write(14, “0\2\20\1\1T\235\376\t”…, 48) = 48

write(3, “kggpnpSIHAGetItem 1 = +dgroup1/ASM/asmparameterfile/registry.253.740659373″…, 75) = 7
write(3, “kfspFileNameGet name=+dgroup1/ASSM/asmparameterfile/registry.253.740659373″…, 78) = 78

write(3, “kggpnpSIHAGetItem 2 = “, 23) = 23
write(3, “kgfspb_shallow_discover dscstr=\”\””…, 33) = 33

As we can see our process got spfile location (+dgroup1/ASM/asmparameterfile/registry.253.740659373) and asm_diskstring=”” which is null by default from OHASD. Now, lets see where OHASD itself gets this information. To identify that I traced the OHASD process, and I found this lines

open(“/etc/oracle/olr.loc”, O_RDONLY) = 4
read(4, “olrconfig_loc=/u01/oracle/produc”…, 4096) = 108
read(4, “”, 4096) = 0
close(4) = 0
open(“/u01/oracle/product/grid11g/cdata/localhost/testhost.olr”, O_RDONLY|O_SYNC|O_LARGEFILE) = 4
pread64(4, “\1\202VD\31\4\3OCR\226\361nA”…, 4096, 102400) = 4096
pread64(4, “\1\202\300I#\4\3OCR\226\361nA”…, 4096, 143360) = 4096
pread64(4, “\1\202VD\31\4\3OCR\226\361nA”…, 4096, 102400) = 4096
pread64(4, “\1\202hq\33\4\3OCR\226\361nA”…, 4096, 110592) = 4096
pread64(4, “\1\202\271\311#\4\20\3OCR\226\361nA”…, 4096, 4337664) = 4096
pread64(4, “\1\202\276\262$\4\20\3OCR\226\361nA”…, 4096, 4341760) = 4096
pread64(4, “\1\202VD\31\4\3OCR\226\361nA”…, 4096, 102400) = 4096
pread64(4, “\1\202hq\33\4\3OCR\226\361nA”…, 4096, 110592) = 4096
pread64(4, “\1\202\271\311#\4\20\3OCR\226\361nA”…, 4096, 4337664) = 4096
pread64(4, “\1\202\276\262$\4\20\3OCR\226\361nA”…, 4096, 4341760) = 4096
pread64(4, “\1\202\236\363%\4\2\3OCR\226\361nA”…, 4096, 4345856) = 4096
pread64(4, “\1\202\334\n&\4\2\3OCR\226\361nA”…, 4096, 4349952) = 4096
pread64(4, “\1\202\325\357-\4\2\3OCR\226\361nA”…, 4096, 4378624) = 4096
pread64(4, “\1\202VD\31\4\3OCR\226\361nA”…, 4096, 102400) = 4096
pread64(4, “\1\202hq\33\4\3OCR\226\361nA”…, 4096, 110592) = 4096
pread64(4, “\1\202\271\311#\4\20\3OCR\226\361nA”…, 4096, 4337664) = 4096
pread64(4, “\1\202\276\262$\4\20\3OCR\226\361nA”…, 4096, 4341760) = 4096
pread64(4, “\1\202\325\357-\4\2\3OCR\226\361nA”…, 4096, 4378624) = 4096

As one would expect OHASD reads this information from OLR, the path to it, it gets from /etc/oracle/olr.loc. I want to note that it is true for Oracle Restart mode, in Oracle RAC environment information is stored in GPNP profile and there is GPNPD process that maintains and manages this profile information. In Oracle Restart, as we can see OHASD process executes this role, and because there is no PGNP profile it stores information in OLR file. So, what next? Our process starts to scan all disks to identify ASM headers, after identifying them, it identifies which disks belongs to which diskgroups by information from header. There is many other metadata in the ASM diskheader that it reads, including pointers to spfile and votig disk file, it is kfdhdb.spfile, kfdhdb.spfflg (first block and number of blocks) and kfdhdb.vfstart, kfdhdb.vfend (begin block and end block). It is possible to read disk header using kfed utility from $ORACLE_HOME/bin directory. For example, lets read disk header of the /dev/loop1 which corresponds to ORCL:DISK1, spfile is on this disk.

shell> kfed read /dev/loop1 | more
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD –Indicates that this is ASM disk header
.
kfdhdb.grptyp: 2 ; 0x026: KFDGTP_NORMAL –Indicates mirroring level, in my case it is NORMAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER –Indicates that disk is the member disk of diskgroup
.
kfdhdb.dskname: DISK1 ; 0x028: length=5 –Disk name
kfdhdb.grpname: DGROUP1 ; 0x048: length=7 –Disk group name, to which this disk belongs
kfdhdb.fgname: DISK1 ; 0x068: length=5 –To which failure group this disk belongs
.
kfdhdb.secsize: 512 ; 0x0b8: 0x0200 –Disk sector size
kfdhdb.blksize: 4096 ; 0x0ba: 0x1000 –Disk block size
kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000 –Allocation Unit size, by default 1M
.
kfdhdb.vfstart: 0 ; 0x0ec: 0x00000000 –Begin block address of voting disk file
kfdhdb.vfend: 0 ; 0x0f0: 0x00000000 –End block address of voting disk file
kfdhdb.spfile: 59 ; 0x0f4: 0x0000003b –Begin block address of spfile
kfdhdb.spfflg: 1 ; 0x0f8: 0x00000001 –Number of blocks containing spfile

Now, lets see what will be next steps of our process

open(“/opt/oracle/extapi/32/asm/orcl/1/libasm.so”, O_RDONLY) = 17
read(17, “\177ELF\1\1\1\3\3\1000\v004″…, 512) = 512
close(17) = 0
open(“/dev/oracleasm/.query_version”, O_RDWR|O_LARGEFILE) = 17
write(17, “MSA\2\1\20”, 16) = 16
read(17, “MSA\2\1\20”, 16) = 16
close(17) = 0
open(“/dev/oracleasm/.get_iid”, O_RDWR|O_LARGEFILE) = 17
write(17, “MSA\2\2\30”, 24) = 24
read(17, “MSA\2\2\30\3”, 24) = 24
close(17) = 0
open(“/dev/oracleasm/.check_iid”, O_RDWR|O_LARGEFILE) = 17
write(17, “MSA\2\3\30\3”, 24) = 24
read(17, “MSA\2\3\30\3”, 24) = 24
close(17) = 0
open(“/dev/oracleasm/iid/0000000000000003”, O_RDWR|O_CREAT|O_LARGEFILE, 0770) = 17

open(“/dev/oracleasm/disks/DISK1”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200-%\310”, 32) = 32
close(18) = 0
read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
open(“/dev/oracleasm/disks/DISK2”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200+%\310”, 32) = 32
close(18) = 0
read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
open(“/dev/oracleasm/disks/DISK3”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200!%\310”, 32) = 32
close(18) = 0
read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
open(“/dev/oracleasm/disks/DISK4”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200#%\310”, 32) = 32
close(18) = 0
read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
open(“/dev/oracleasm/disks/DISK5”, O_RDONLY|O_LARGEFILE) = 18
read(17, “MSA\2\5 \22T/v\17\200)%\310”, 32) = 32
close(18) = 0

read(17, “MSA\2\7P@\364\311\20\320\301\225\277″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
read(17, “MSA\2\7P@\364\311\20″…, 80) = 80
.
.

open(“/u01/oracle/diag/asm/+asm/+ASM/trace/alert_+ASM.log”, O_WRONLY|O_CREAT|O_APPEND|O_LARGEFILE, 0660) = 16
write(16, “Tue Jan 18 17:45:13 2011\n”, 25) = 25
write(16, “Starting ORACLE instance (normal”…, 33) = 33
write(16, “\n”, 1) = 1
close(16)
.
.

It opens /opt/oracle/extapi/32/asm/orcl/1/libasm.so library, reads it. Then it opens special files /dev/oracleasm/.query_version, /dev/oracleasm/.get_iid and /dev/oracleasm/.check_iid, this files is interfaces to ASM device manager. First one is used to get managers version, second one is used to get identifier of the ASM disk device manager instance (not ASM instance) and third for verifying of this instance identifier. In our case ASM device managers instance identifier is 0000000000000003. Then, our process opens /dev/oracleasm/iid/0000000000000003, in other words it establishes connection to the ASM device manager instance. Process will use this interface to read and write to ASM disks. Then it checks all disks from DISK1 to DISK5. After it gets all information about disks, i.e. groups, mirroring level, AU size, failure group and so on it starts reading ASM disk through established interface. I think in this step it reads the spfile. After it gets initialization parameters it starts to allocate memory structures and starting necessary background processes.

Now lets see what is stored in OLR regarding ora.asm resource

shell> cd $ORACLE_HOME/cdata/localhost
shell> strings testhost.olr | grep dgroup1/ASM/asmparameterfile/registry | sed ‘s/~/\n/g’
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_USR_ORA_INST_NAME=+ASM
LOAD=1
LOGGING_LEVEL=1
NAME=ora.asm
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SPFILE=+dgroup1/ASM/asmparameterfile/registry.253.740659373
START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)
START_TIMEOUT=900
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(ora.cssd)
STOP_TIMEOUT=600
TYPE=ora.asm.type
TYPE_ACL=owner:oracle:rwx,pgrp:dba:rwx,other::r–
UPTIME_THRESHOLD=1d
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM
USR_ORA_OPEN_MODE=mount
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.1.0

We can found there information about spfile “SPFILE=+dgroup1/ASM/asmparameterfile/registry.253.740659373”, we also can see that ora.cssd is hard dependency resource, because OCSSD is responsible for synchronization between ASM and database instance. But what about asm_diskstring parameter, where is it?

shell> strings testhost.olr | grep ASM_DISKSTRING | sed ‘s/~/\n/g’

nothing, it is because my asm_diskstring parameter equals to null, default value, lets change it

SQL> alter system set asm_diskstring=’ORCL:DISK1,ORCL:DISK2′ scope=spfile;

System altered.

check OLR file again

strings testdb03.olr | grep ASM_DISKSTRING | sed ‘s/~/\n/g’
bASM_DISKSTRING
ACL=owner:oracle:rwx,pgrp:dba:rwx,other::r–
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALIAS_NAME=
ASM_DISKSTRING=ORCL:DISK1,ORCL:DISK2
AUTO_START=restore
BASE_TYPE=ora.local_resource.type

Now we can see that information about asm_diskstring is also stored in OLR profile. And next time, at ASM instance startup it will scan only specified disk strings. If we specify disks on which spfile not present, our instance will not start


SQL> alter system set asm_diskstring=’ORCL:DISK3,ORCL:DISK4,ORCL:DISK5′ scope=spfile;

System altered.

SQL> startup nomount force;
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘+DGROUP1/asm/asmparameterfile/registry.253.740659373’
ORA-17503: ksfdopn:2 Failed to open file +DGROUP1/asm/asmparameterfile/registry.253.740659373
SQL>

You can also use the ocrdump utility to extract this entries on any platform in XML format:

shell> ocrdump -local -keyname SYSTEM.OHASD.RESOURCES.ora\!asm.CONFIG -xml -noheader
shell> more OCRDUMPFILE


SYSTEM.OHASD.RESOURCES.ora!asm.CONFIG
ORATEXT

PROCR_ALL_ACCESS
PROCR_NONE
PROCR_NONE
oracle
dba

同样 我们在11.2.0.3的OLR 文件中可以找到SPFILE 的位置:


SPFile=”+DATA/yhd-cluster/asmparameterfile/registry.253.781624261″/>

这里需要说明一点 11.2.0.3 修改diskstring OLR 文件并没有出现对应的修改记录

另外针对 ASM_DISKGROUPS 需要一点说明:

SQL> create pfile=’/tmp/tmp.ctl’ from spfile=’+DATA/yhd-cluster/asmparameterfile/registry.253.781624261′;

File created.

[grid@db-41 ~]$ cat /tmp/tmp.ctl
+ASM1.__oracle_base=’/data/11.2.0’#ORACLE_BASE set from in memory value
+ASM.asm_diskgroups=’DATA’,’ARCH’
+ASM1.asm_diskgroups=’ARCH’#Manual Mount
*.asm_diskgroups=’ARCH’,’DATA’
*.asm_diskstring=’/dev/mapper/*’
*.asm_power_limit=1
*.diagnostic_dest=’/data/app/oracle’
*.instance_type=’asm’
*.large_pool_size=12M
*.remote_login_passwordfile=’EXCLUSIVE’

SQL> alter system set asm_diskgroups=’ARCH’,’DATA’ scope=both sid=’+ASM1′;

System altered.

SQL> create pfile=’/tmp/tmp.ctl’ from spfile=’+DATA/yhd-cluster/asmparameterfile/registry.253.781624261′;

File created.

[grid@db-41 ~]$ cat /tmp/tmp.ctl
+ASM1.__oracle_base=’/data/11.2.0’#ORACLE_BASE set from in memory value
+ASM.asm_diskgroups=’DATA’,’ARCH’
*.asm_diskgroups=’ARCH’,’DATA’
+ASM1.asm_diskgroups=’ARCH’,’DATA’
*.asm_diskstring=’/dev/mapper/*’
*.asm_power_limit=1
*.diagnostic_dest=’/data/app/oracle’
*.instance_type=’asm’
*.large_pool_size=12M
*.remote_login_passwordfile=’EXCLUSIVE’

针对这个问题 这篇文章也做过说明:http://comtmr.blogspot.com/2011/01/11gr2-mysteriously-changing.html

Reference:https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1059856.1

Create a database link with the new host:port/service syntax

March 8, 2012 11g, maintain, oracle 1 comment

Create a database link with the new host:port/service syntax

Submitted by tanelpoder on Mon, 2012-03-05 15:57

Share
I just noticed that (finally) in 11.2 this syntax is supported:

SQL> CREATE DATABASE LINK demo_x2
2 CONNECT TO tanel IDENTIFIED BY password
3 USING ‘exadb03:1521/DEMO’;
Database link created.
This just makes life a bit easier as there’s no need to use the long TNS format entry (or a tnsnames.ora/LDAP alias). It might work in 11.1 too (haven’t tested) but it didn’t work on 10.2.0.4 …

Update: This feature works for dblinks in 10.2 onwards – when I tested it on my 10.2, I got an error initially, but it was because the hostname I used didn’t resolve to an IP. Thanks to Randolf Geist for pointing this out.

In case you didn’t know, the sqlplus supports such an easy connect method since 10g:

tanel@mac02:~$ sqlplus tanel/password@exadb03/DEMO
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 5 09:51:27 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
If you omit the port from the syntax, it defaults to 1521.

eg:MY test

SQL> create database link test11g connect to user_data2 identified by xxxxxx using ‘10.0.0.42:1521/user’;

Database link created.

SQL> select * from global_name@test11g;

GLOBAL_NAME
——————————————————————————–
USER

创建index之前如何确定其大小

March 8, 2012 Internals, maintain, oracle 1 comment

昨天看了roger的一篇文章,如何在创建index 之前估算它的大小,这里有一种更简单的方法实现:

主要思路就是采用了dbms_space.create_index_cost这个包

–create a testing table named t
SQL> create table t as select * from dba_objects;

Table created.

SQL> declare
begin
for i in 1..10 loop
insert /*+ APPEND */ into t select * from t;
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

COUNT(*)
———-
38171648

1 row selected.

–As told by Oracle docs, you have to have updated statistics in order to allow CREATE_INDEX_COST to produce accurate estimation
SQL> exec dbms_stats.gather_table_stats(‘LIU’,’T’);

PL/SQL procedure successfully completed.

SQL> variable used_bytes number
SQL> variable allocated_bytes number
SQL> set autoprint on
SQL> exec dbms_space.create_index_cost( ‘create index t_idx on t(object_id,object_name,object_type) tablespace PURCHASE’, :used_bytes, :allocated_bytes );

PL/SQL procedure successfully completed.

ALLOCATED_BYTES
—————
1946157056

USED_BYTES
———-
1259664384

Verification
SQL> create index t_idx on t(object_id,object_name,object_type);

Index created.

SQL> select segment_name ,sum(bytes) from dba_segments where owner=’LIU’ and segment_name=’T_IDX’ group by segment_name;

SEGMENT_NAME SUM(BYTES)
——————————————————————————— ———-
T_IDX 1930428416

1 row selected.

可以看到预计空间为 1946157056byte 实际空间为 1930428416byte 结果极其的接近。

按照roger的方法 我们再计算一次:

对于8k的block:

block_size(8192)= = kcbh + ktbbh + kdxle + kd_off + DATA + block_size * Pctfree + 4
= 20 + 72 + 32 + 2 * Y + Y * ( 20 + 5 + 8 + rowid ) + 8192 * 0.1 + 4
8192 = 92 + 32 + 45Y + 819.2 + 4

SQL> SELECT (8192-92-32-819.2-4)/45 FROM dual;

(8192-92-32-819.2-4)/45
———————–
160.995

SQL> SELECT 38171648/((8192-92-32-819.2-4)/45) *8192 from dual;

38171648/((8192-92-32-819.2-4)/45)*8192
————————————————-
1942249319.354037

可以看到如果单用leaf block计算 同样很接近真实值,当然如果对一个very big的index而言 仅仅用leaf block去计算 还是会出现一定的偏差

Reference:http://www.killdb.com/2012/02/26/how-to-calculate-indexes-size-when-you-create-index.html?wpc=dlc#comment-805

Scripts:sess_uncommited_transactions.sql

March 2, 2012 maintain, oracle No comments

This script is monitoring sessions that are not commited :

— |—————————————————————————-|
— | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
— |—————————————————————————-|
— | DATABASE : Oracle |
— | FILE : sess_uncommited_transactions.sql |
— | CLASS : Session Management |
— | PURPOSE : Query all users with uncommited transactions. |
— | NOTE : As with any code, ensure to test this script in a development |
— | environment before attempting to run it in production. |
— +—————————————————————————-+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid FORMAT 99999 HEADING ‘SID’
COLUMN serial_id FORMAT 99999999 HEADING ‘Serial ID’
COLUMN session_status FORMAT a9 HEADING ‘Status’ JUSTIFY right
COLUMN oracle_username FORMAT a14 HEADING ‘Oracle User’ JUSTIFY right
COLUMN os_username FORMAT a12 HEADING ‘O/S User’ JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING ‘O/S PID’ JUSTIFY right
COLUMN session_program FORMAT a18 HEADING ‘Session Program’ TRUNC
COLUMN session_machine FORMAT a15 HEADING ‘Machine’ JUSTIFY right
COLUMN number_of_undo_records FORMAT 999,999,999,999 HEADING “# Undo Records”
COLUMN used_undo_size FORMAT 999,999,999,999 HEADING “Used Undo Size”

SELECT
s.sid sid
, lpad(s.status,9) session_status
, lpad(s.username,14) oracle_username
, lpad(s.osuser,12) os_username
, lpad(p.spid,7) os_pid
, b.used_urec number_of_undo_records
, b.used_ublk * d.value used_undo_size
, s.program session_program
, lpad(s.machine,15) session_machine
FROM
v$process p
, v$session s
, v$transaction b
, v$parameter d
WHERE
b.ses_addr = s.saddr
AND p.addr (+) = s.paddr
AND s.audsid <> userenv(‘SESSIONID’)
AND d.name = ‘db_block_size’;

11.2.0.3 datapump got ORA-38500

February 23, 2012 maintain, oracle No comments

从10.2.0.5的库导入一张表的增量数据到11.2.0.3 impdp报出如下错误,对比两边表结构一致,tablespace有剩余空间 报错如下:


[oracle@localhost ~]$ impdp \’\/ as sysdba \’ directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append

Import: Release 11.2.0.3.0 – Production on Thu Feb 23 09:44:49 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: “/******** AS SYSDBA” directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “USER_DATA2″.”SO” exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
ORA-38500: Unsupported operation: Oracle XML DB not present

reference:

Data Pump Import Fails With ORA-942 ORA-06512 And ORA-38500 [ID 1350414.1]

Master Note for Oracle XML Database (XDB) Install / Deinstall [ID 1292089.1]

11.2 – XDB Removal and Reinstall

XDB Removal

The catnoqm.sql script drops XDB.

SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;

XDB Installation

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using
Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D

For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

SQL> select owner, object_name, object_type, status
from dba_objects
where status = ‘INVALID’
and owner = ‘XDB’; 2 3 4

no rows selected

SQL> select comp_name, version, status
from dba_registry
where comp_id = ‘XDB’; 2 3

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ———————————
Oracle XML Database
11.2.0.3.0 VALID

1 row selected.

SQL> !

重新导入:

[oracle@localhost ~]$ impdp \’\/ as sysdba \’ directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append

Import: Release 11.2.0.3.0 – Production on Thu Feb 23 09:44:49 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: “/******** AS SYSDBA” directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “USER_DATA2″.”SO” exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “USER_DATA2″.”SO” 225.1 MB 428745 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_IMPORT_FULL_01″ successfully completed at 10:23:05

Scripts:show_space

January 16, 2012 maintain, oracle No comments

修改过的show_space 支持ASSM


create or replace
procedure show_space
( p_segname_1 in varchar2,
p_space in varchar2 default ‘MANUAL’,
p_type_1 in varchar2 default ‘TABLE’ ,
p_freespace in varchar2 default ‘N’,
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,’.’) ||
p_num );
end;
begin
p_segname := upper(p_segname_1); — rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;

if (p_type_1 = ‘i’ or p_type_1 = ‘I’) then –rainy changed
p_type := ‘INDEX’;
end if;

if (p_type_1 = ‘t’ or p_type_1 = ‘T’) then –rainy changed
p_type := ‘TABLE’;
end if;

if (p_type_1 = ‘c’ or p_type_1 = ‘C’) then –rainy changed
p_type := ‘CLUSTER’;
end if;
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
if p_space = ‘MANUAL’ or (p_space <> ‘auto’ and p_space <> ‘AUTO’) then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
p( ‘Free Blocks’, l_free_blks );
end if;
p( ‘Total Blocks’, l_total_blocks );
p( ‘Total Bytes’, l_total_bytes );
p( ‘Unused Blocks’, l_unused_blocks );
p( ‘Unused Bytes’, l_unused_bytes );
p( ‘Last Used Ext FileId’, l_LastUsedExtFileId );
p( ‘Last Used Ext BlockId’, l_LastUsedExtBlockId );
p( ‘Last Used Block’, l_LAST_USED_BLOCK );
if p_freespace = ‘Y’ then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(‘ ‘,50,’*’));
p( ‘0% — 25% free space blocks’, l_fs1_blocks);
p( ‘0% — 25% free space bytes’, l_fs1_bytes);
p( ‘25% — 50% free space blocks’, l_fs2_blocks);
p( ‘25% — 50% free space bytes’, l_fs2_bytes);
p( ‘50% — 75% free space blocks’, l_fs3_blocks);
p( ‘50% — 75% free space bytes’, l_fs3_bytes);
p( ‘75% — 100% free space blocks’, l_fs4_blocks);
p( ‘75% — 100% free space bytes’, l_fs4_bytes);
p( ‘Unused Blocks’, l_unformatted_blocks );
p( ‘Unused Bytes’, l_unformatted_bytes );
p( ‘Total Blocks’, l_full_blocks);
p( ‘Total bytes’, l_full_bytes);

end if;

end;

EXAMPLE:


SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

36729 rows created.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1;

73458 rows created.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1;

146916 rows created.

SQL> commit;

SQL> alter system checkpoint;

System altered.

SQL> exec show_space(‘T1′,’AUTO’,’T’,’Y’);
Total Blocks……………………….4096
Total Bytes………………………..33554432
Unused Blocks………………………0
Unused Bytes……………………….0
Last Used Ext FileId………………..10
Last Used Ext BlockId……………….1104905
Last Used Block…………………….128
*************************************************
0% — 25% free space blocks………….2
0% — 25% free space bytes…………..16384
25% — 50% free space blocks…………0
25% — 50% free space bytes………….0
50% — 75% free space blocks…………0
50% — 75% free space bytes………….0
75% — 100% free space blocks………..2
75% — 100% free space bytes…………16384
Unused Blocks………………………0
Unused Bytes……………………….0
Total Blocks……………………….4020
Total bytes………………………..32931840

PL/SQL procedure successfully completed.

SQL>

Scripts:xplan_extended_display_cursor

January 9, 2012 maintain, oracle No comments

Alter session set sql_trace=true;
Alter session set STATISTICS_LEVEL = ALL;

set echo off verify off termout off
set doc off
doc
— ———————————————————————————————-

— Script: xplan_extended_display_cursor.sql

— Version: 0.9
— December 2011

— Author: Randolf Geist
— oracle-randolf.blogspot.com

— Description: A free-standing SQL wrapper over DBMS_XPLAN. Provides access to the
— DBMS_XPLAN.DISPLAY_CURSOR pipelined function for a given SQL_ID and CHILD_NUMBER

— This is a prototype for an extended analysis of the data provided by the
— Runtime Profile (aka. Rowsource Statistics enabled via
— SQL_TRACE = TRUE, STATISTICS_LEVEL = ALL or GATHER_PLAN_STATISTICS hint)
— and reported via the ALLSTATS/MEMSTATS/IOSTATS formatting option of
— DBMS_XPLAN.DISPLAY_CURSOR

— Versions: This utility will work for all versions of 10g and upwards.

— Required: The same access as DBMS_XPLAN.DISPLAY_CURSOR requires. See the documentation
— of DISPLAY_CURSOR for your Oracle version for more information

— The script directly queries
— 1) V$SESSION
— 2) V$SQL_PLAN_STATISTICS_ALL

— Credits: Based on the original XPLAN implementation by Adrian Billington (http://www.oracle-developer.net/utilities.php
— resp. http://www.oracle-developer.net/content/utilities/xplan.zip)
— and inspired by Kyle Hailey’s TCF query (http://dboptimizer.com/2011/09/20/display_cursor/)

— Features: In addition to the PID (The PARENT_ID) and ORD (The order of execution, note that this doesn’t account for the special cases so it might be wrong)
— columns added by Adrian’s wrapper the following additional columns over ALLSTATS are provided:

— A_TIME_SELF : The time taken by the operation itself – this is the operation’s cumulative time minus the direct descendants operation’s cumulative time
— LIO_SELF : The LIOs done by the operation itself – this is the operation’s cumulative LIOs minus the direct descendants operation’s cumulative LIOs
— READS_SELF : The reads performed the operation itself – this is the operation’s cumulative reads minus the direct descendants operation’s cumulative reads
— WRITES_SELF : The writes performed the operation itself – this is the operation’s cumulative writes minus the direct descendants operation’s cumulative writes
— A_TIME_SELF_GRAPH : A graphical representation of A_TIME_SELF relative to the total A_TIME
— LIO_SELF_GRAPH : A graphical representation of LIO_SELF relative to the total LIO
— READS_SELF_GRAPH : A graphical representation of READS_SELF relative to the total READS
— WRITES_SELF_GRAPH : A graphical representation of WRITES_SELF relative to the total WRITES
— LIO_RATIO : Ratio of LIOs per row generated by the row source – the higher this ratio the more likely there could be a more efficient way to generate those rows (be aware of aggregation steps though)
— TCF_GRAPH : Each “+”/”-” sign represents one order of magnitude based on ratio between E_ROWS_TIMES_START and A-ROWS. Note that this will be misleading with Parallel Execution (see E_ROWS_TIMES_START)
— E_ROWS_TIMES_START : The E_ROWS multiplied by STARTS – this is useful for understanding the actual cardinality estimate for related combine child operations getting executed multiple times. Note that this will be misleading with Parallel Execution

— More information including demos can be found online at http://oracle-randolf.blogspot.com/2011/12/extended-displaycursor-with-rowsource.html

— Usage: @xplan_extended_display_cursor.sql [sql_id] [cursor_child_number] [format_option]

— If both the SQL_ID and CHILD_NUMBER are omitted the previously executed SQL_ID and CHILD_NUMBER of the session will be used
— If the SQL_ID is specified but the CHILD_NUMBER is omitted then CHILD_NUMBER 0 is assumed

— This prototype does not support processing multiple child cursors like DISPLAY_CURSOR is capable of
— when passing NULL as CHILD_NUMBER to DISPLAY_CURSOR. Hence a CHILD_NUMBER is mandatory, either
— implicitly generated (see above) or explicitly passed

— The default formatting option for the call to DBMS_XPLAN.DISPLAY_CURSOR is ALLSTATS LAST – extending this output is the primary purpose of this script

— Note: You need a veeery wide terminal setting for this prototype, something like linesize 400 should suffice

— This tool is free but comes with no warranty at all – use at your own risk

#

col plan_table_output format a400
set linesize 400 pagesize 0 tab off

/* ALLSTATS LAST is assumed as the default formatting option for DBMS_XPLAN.DISPLAY_CURSOR */
define default_fo = “ALLSTATS LAST”

column prev_sql_id new_value prev_sql_id
column prev_child_number new_value prev_cn

/* Get the previous command as default
if no SQL_ID / CHILD_NUMBER is passed */
select
prev_sql_id
, prev_child_number
from
v$session
where
sid = userenv(‘sid’)
;

— The following is a hack to use default
— values for defines
column 1 new_value 1
column 2 new_value 2
column 3 new_value 3

select
” as “1”
, ” as “2”
, ” as “3”
from
dual
where
rownum = 0;

column si new_value si
column cn new_value cn
column fo new_value fo

/* Use passed parameters else refer to previous SQL_ID / CHILD_NUMBER
ALLSTATS LAST is default formatting option */
select
nvl(‘&1’, ‘&prev_sql_id’) as si
, coalesce(‘&2’, ‘&prev_cn’, ‘0’) as cn
, nvl(‘&3’, ‘&default_fo’) as fo
from
dual
;

column last new_value last

/* Last or all execution */
select
case
when instr(‘&fo’, ‘LAST’) > 0
then ‘last_’
end as last
from
dual
;

set termout on

with
— The next three queries are based on the original XPLAN wrapper by Adrian Billington
— to determine the PID and ORD information, only slightly modified to deal with
— the 10g special case that V$SQL_PLAN_STATISTICS_ALL doesn’t include the ID = 0 operation
— and starts with 1 instead for Rowsource Statistics
sql_plan_data as
(
select
id
, parent_id
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
),
hierarchy_data as
(
select
id
, parent_id
from
sql_plan_data
start with
id in
(
select
id
from
sql_plan_data p1
where
not exists
(
select
null
from
sql_plan_data p2
where
p2.id = p1.parent_id
)
)
connect by
prior id = parent_id
order siblings by
id desc
),
ordered_hierarchy_data as
(
select
id
, parent_id as pid
, row_number() over (order by rownum desc) as oid
, max(id) over () as maxid
, min(id) over () as minid
from
hierarchy_data
),
— The following query uses the MAX values
— rather than taking the values of PLAN OPERATION_ID = 0 (or 1 for 10g V$SQL_PLAN_STATISTICS_ALL)
— for determining the grand totals

— This is because queries that get cancelled do not
— necessarily have yet sensible values in the root plan operation

— Furthermore with Parallel Execution the elapsed time accumulated
— with the ALLSTATS option for operations performed in parallel
— will be greater than the wallclock elapsed time shown for the Query Coordinator

— Note that if you use GATHER_PLAN_STATISTICS with the default
— row sampling frequency the (LAST_)ELAPSED_TIME will be very likely
— wrong and hence the time-based graphs and self-statistics will be misleading

— Similar things might happen when cancelling queries

— For queries running with STATISTICS_LEVEL = ALL (or sample frequency set to 1)
— the A-TIME is pretty reliable
totals as
(
select
max(&last.cu_buffer_gets + &last.cr_buffer_gets) as total_lio
, max(&last.elapsed_time) as total_elapsed
, max(&last.disk_reads) as total_reads
, max(&last.disk_writes) as total_writes
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
),
— The totals for the direct descendants of an operation
— These are required for calculating the work performed
— by a (parent) operation itself
— Basically this is the SUM grouped by PARENT_ID
direct_desc_totals as
(
select
sum(&last.cu_buffer_gets + &last.cr_buffer_gets) as lio
, sum(&last.elapsed_time) as elapsed
, sum(&last.disk_reads) as reads
, sum(&last.disk_writes) as writes
, parent_id
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
group by
parent_id
),
— Putting the three together
— The statistics, direct descendant totals plus totals
extended_stats as
(
select
stats.id
, stats.parent_id
, stats.&last.elapsed_time as elapsed
, (stats.&last.cu_buffer_gets + stats.&last.cr_buffer_gets) as lio
, stats.&last.starts as starts
, stats.&last.output_rows as a_rows
, stats.cardinality as e_rows
, stats.&last.disk_reads as reads
, stats.&last.disk_writes as writes
, ddt.elapsed as ddt_elapsed
, ddt.lio as ddt_lio
, ddt.reads as ddt_reads
, ddt.writes as ddt_writes
, t.total_elapsed
, t.total_lio
, t.total_reads
, t.total_writes
from
v$sql_plan_statistics_all stats
, direct_desc_totals ddt
, totals t
where
stats.sql_id=’&si’
and stats.child_number = &cn
and ddt.parent_id (+) = stats.id
),
— Further information derived from above
derived_stats as
(
select
id
, greatest(elapsed – nvl(ddt_elapsed , 0), 0) as elapsed_self
, greatest(lio – nvl(ddt_lio, 0), 0) as lio_self
, trunc((greatest(lio – nvl(ddt_lio, 0), 0)) / nullif(a_rows, 0)) as lio_ratio
, greatest(reads – nvl(ddt_reads, 0), 0) as reads_self
, greatest(writes – nvl(ddt_writes,0) ,0) as writes_self
, total_elapsed
, total_lio
, total_reads
, total_writes
, trunc(log(10, nullif(starts * e_rows / nullif(a_rows, 0), 0))) as tcf_ratio
, starts * e_rows as e_rows_times_start
from
extended_stats
),
/* Format the data as required */
formatted_data1 as
(
select
id
, lio_ratio
, total_elapsed
, total_lio
, total_reads
, total_writes
, to_char(numtodsinterval(round(elapsed_self / 10000) * 10000 / 1000000, ‘SECOND’)) as e_time_interval
/* Imitate the DBMS_XPLAN number formatting */
, case
when lio_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when lio_self >= 10000000000000000000 then to_char(lio_self/1000000000000000000, ‘FM99999’) || ‘E’
when lio_self >= 10000000000000000 then to_char(lio_self/1000000000000000, ‘FM99999’) || ‘P’
when lio_self >= 10000000000000 then to_char(lio_self/1000000000000, ‘FM99999’) || ‘T’
when lio_self >= 10000000000 then to_char(lio_self/1000000000, ‘FM99999’) || ‘G’
when lio_self >= 10000000 then to_char(lio_self/1000000, ‘FM99999’) || ‘M’
when lio_self >= 100000 then to_char(lio_self/1000, ‘FM99999’) || ‘K’
else to_char(lio_self, ‘FM99999’) || ‘ ‘
end as lio_self_format
, case
when reads_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when reads_self >= 10000000000000000000 then to_char(reads_self/1000000000000000000, ‘FM99999’) || ‘E’
when reads_self >= 10000000000000000 then to_char(reads_self/1000000000000000, ‘FM99999’) || ‘P’
when reads_self >= 10000000000000 then to_char(reads_self/1000000000000, ‘FM99999’) || ‘T’
when reads_self >= 10000000000 then to_char(reads_self/1000000000, ‘FM99999’) || ‘G’
when reads_self >= 10000000 then to_char(reads_self/1000000, ‘FM99999’) || ‘M’
when reads_self >= 100000 then to_char(reads_self/1000, ‘FM99999’) || ‘K’
else to_char(reads_self, ‘FM99999’) || ‘ ‘
end as reads_self_format
, case
when writes_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when writes_self >= 10000000000000000000 then to_char(writes_self/1000000000000000000, ‘FM99999’) || ‘E’
when writes_self >= 10000000000000000 then to_char(writes_self/1000000000000000, ‘FM99999’) || ‘P’
when writes_self >= 10000000000000 then to_char(writes_self/1000000000000, ‘FM99999’) || ‘T’
when writes_self >= 10000000000 then to_char(writes_self/1000000000, ‘FM99999’) || ‘G’
when writes_self >= 10000000 then to_char(writes_self/1000000, ‘FM99999’) || ‘M’
when writes_self >= 100000 then to_char(writes_self/1000, ‘FM99999’) || ‘K’
else to_char(writes_self, ‘FM99999’) || ‘ ‘
end as writes_self_format
, case
when e_rows_times_start >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when e_rows_times_start >= 10000000000000000000 then to_char(e_rows_times_start/1000000000000000000, ‘FM99999’) || ‘E’
when e_rows_times_start >= 10000000000000000 then to_char(e_rows_times_start/1000000000000000, ‘FM99999’) || ‘P’
when e_rows_times_start >= 10000000000000 then to_char(e_rows_times_start/1000000000000, ‘FM99999’) || ‘T’
when e_rows_times_start >= 10000000000 then to_char(e_rows_times_start/1000000000, ‘FM99999’) || ‘G’
when e_rows_times_start >= 10000000 then to_char(e_rows_times_start/1000000, ‘FM99999’) || ‘M’
when e_rows_times_start >= 100000 then to_char(e_rows_times_start/1000, ‘FM99999’) || ‘K’
else to_char(e_rows_times_start, ‘FM99999’) || ‘ ‘
end as e_rows_times_start_format
, rpad(‘ ‘, nvl(round(elapsed_self / nullif(total_elapsed, 0) * 12), 0) + 1, ‘@’) as elapsed_self_graph
, rpad(‘ ‘, nvl(round(lio_self / nullif(total_lio, 0) * 12), 0) + 1, ‘@’) as lio_self_graph
, rpad(‘ ‘, nvl(round(reads_self / nullif(total_reads, 0) * 12), 0) + 1, ‘@’) as reads_self_graph
, rpad(‘ ‘, nvl(round(writes_self / nullif(total_writes, 0) * 12), 0) + 1, ‘@’) as writes_self_graph
, ‘ ‘ ||
case
when tcf_ratio > 0
then rpad(‘-‘, tcf_ratio, ‘-‘)
else rpad(‘+’, tcf_ratio * -1, ‘+’)
end as tcf_graph
from
derived_stats
),
/* The final formatted data */
formatted_data as
(
select
/*+ Convert the INTERVAL representation to the A-TIME representation used by DBMS_XPLAN
by turning the days into hours */
to_char(to_number(substr(e_time_interval, 2, 9)) * 24 + to_number(substr(e_time_interval, 12, 2)), ‘FM900’) ||
substr(e_time_interval, 14, 9)
as a_time_self
, a.*
from
formatted_data1 a
),
/* Combine the information with the original DBMS_XPLAN output */
xplan_data as (
select
x.plan_table_output
, o.id
, o.pid
, o.oid
, o.maxid
, o.minid
, a.a_time_self
, a.lio_self_format
, a.reads_self_format
, a.writes_self_format
, a.elapsed_self_graph
, a.lio_self_graph
, a.reads_self_graph
, a.writes_self_graph
, a.lio_ratio
, a.tcf_graph
, a.total_elapsed
, a.total_lio
, a.total_reads
, a.total_writes
, a.e_rows_times_start_format
, x.rn
from
(
select /* Take advantage of 11g table function dynamic sampling */
/*+ dynamic_sampling(dc, 2) */
/* This ROWNUM determines the order of the output/processing */
rownum as rn
, plan_table_output
from
table(dbms_xplan.display_cursor(‘&si’,&cn, ‘&fo’)) dc
) x
, ordered_hierarchy_data o
, formatted_data a
where
o.id (+) = case
when regexp_like(x.plan_table_output, ‘^\|[\* 0-9]+\|’)
then to_number(regexp_substr(x.plan_table_output, ‘[0-9]+’))
end
and a.id (+) = case
when regexp_like(x.plan_table_output, ‘^\|[\* 0-9]+\|’)
then to_number(regexp_substr(x.plan_table_output, ‘[0-9]+’))
end
)
/* Inject the additional data into the original DBMS_XPLAN output
by using the MODEL clause */
select
plan_table_output
from
xplan_data
model
dimension by (rn as r)
measures
(
cast(plan_table_output as varchar2(4000)) as plan_table_output
, id
, maxid
, minid
, pid
, oid
, a_time_self
, lio_self_format
, reads_self_format
, writes_self_format
, e_rows_times_start_format
, elapsed_self_graph
, lio_self_graph
, reads_self_graph
, writes_self_graph
, lio_ratio
, tcf_graph
, total_elapsed
, total_lio
, total_reads
, total_writes
, greatest(max(length(maxid)) over () + 3, 6) as csize
, cast(null as varchar2(128)) as inject
, cast(null as varchar2(4000)) as inject2
)
rules sequential order
(
/* Prepare the injection of the OID / PID info */
inject[r] = case
/* MINID/MAXID are the same for all rows
so it doesn’t really matter
which offset we refer to */
when id[cv(r)+1] = minid[cv(r)+1]
or id[cv(r)+3] = minid[cv(r)+3]
or id[cv(r)-1] = maxid[cv(r)-1]
then rpad(‘-‘, csize[cv()]*2, ‘-‘)
when id[cv(r)+2] = minid[cv(r)+2]
then ‘|’ || lpad(‘Pid |’, csize[cv()]) || lpad(‘Ord |’, csize[cv()])
when id[cv()] is not null
then ‘|’ || lpad(pid[cv()] || ‘ |’, csize[cv()]) || lpad(oid[cv()] || ‘ |’, csize[cv()])
end
/* Prepare the injection of the remaining info */
, inject2[r] = case
when id[cv(r)+1] = minid[cv(r)+1]
or id[cv(r)+3] = minid[cv(r)+3]
or id[cv(r)-1] = maxid[cv(r)-1]
then rpad(‘-‘,
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
14 else 0 end /* A_TIME_SELF */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
11 else 0 end /* LIO_SELF */ +
case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then
11 else 0 end /* READS_SELF */ +
case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then
11 else 0 end /* WRITES_SELF */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
14 else 0 end /* A_TIME_SELF_GRAPH */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
14 else 0 end /* LIO_SELF_GRAPH */ +
case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then
14 else 0 end /* READS_SELF_GRAPH */ +
case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then
14 else 0 end /* WRITES_SELF_GRAPH */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
11 else 0 end /* LIO_RATIO */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
11 else 0 end /* TCF_GRAPH */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
11 else 0 end /* E_ROWS_TIMES_START */
, ‘-‘)
when id[cv(r)+2] = minid[cv(r)+2]
then case when total_elapsed[cv(r)+2] > 0 then
lpad(‘A-Time Self |’ , 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘Bufs Self |’ , 11) end ||
case when total_reads[cv(r)+2] > 0 then
lpad(‘Reads Self|’ , 11) end ||
case when total_writes[cv(r)+2] > 0 then
lpad(‘Write Self|’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘A-Ti S-Graph |’, 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘Bufs S-Graph |’, 14) end ||
case when total_reads[cv(r)+2] > 0 then
lpad(‘Reads S-Graph|’, 14) end ||
case when total_writes[cv(r)+2] > 0 then
lpad(‘Write S-Graph|’, 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘LIO Ratio |’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘TCF Graph |’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘E-Rows*Sta|’ , 11) end
when id[cv()] is not null
then case when total_elapsed[cv()] > 0 then
lpad(a_time_self[cv()] || ‘ |’, 14) end ||
case when total_lio[cv()] > 0 then
lpad(lio_self_format[cv()] || ‘|’, 11) end ||
case when total_reads[cv()] > 0 then
lpad(reads_self_format[cv()] || ‘|’, 11) end ||
case when total_writes[cv()] > 0 then
lpad(writes_self_format[cv()] || ‘|’, 11) end ||
case when total_elapsed[cv()] > 0 then
rpad(elapsed_self_graph[cv()], 13) || ‘|’ end ||
case when total_lio[cv()] > 0 then
rpad(lio_self_graph[cv()], 13) || ‘|’ end ||
case when total_reads[cv()] > 0 then
rpad(reads_self_graph[cv()], 13) || ‘|’ end ||
case when total_writes[cv()] > 0 then
rpad(writes_self_graph[cv()], 13) || ‘|’ end ||
case when total_lio[cv()] > 0 then
lpad(lio_ratio[cv()] || ‘ |’, 11) end ||
case when total_elapsed[cv()] > 0 then
rpad(tcf_graph[cv()], 9) || ‘ |’ end ||
case when total_elapsed[cv()] > 0 then
lpad(e_rows_times_start_format[cv()] || ‘|’, 11) end
end
/* Putting it all together */
, plan_table_output[r] = case
when inject[cv()] like ‘—%’
then inject[cv()] || plan_table_output[cv()] || inject2[cv()]
when inject[cv()] is present
then regexp_replace(plan_table_output[cv()], ‘\|’, inject[cv()], 1, 2) || inject2[cv()]
else plan_table_output[cv()]
end
)
order by
r
;

undefine default_fo
undefine prev_sql_id
undefine prev_cn
undefine last
undefine si
undefine cn
undefine fo
undefine 1
undefine 2
undefine 3

col plan_table_output clear
col prev_sql_id clear
col prev_child_number clear
col si clear
col cn clear
col fo clear
col last clear

EXAMPLE:

[oracle@testdb2 ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.5.0 – Production on Mon Jan 9 12:58:57 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

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

SQL> Alter session set sql_trace=true;
Alter session set STATISTICS_LEVEL = ALL;
Session altered.

SQL>

Session altered.

SQL>
SQL> Alter session set sql_trace=true;

Session altered.

SQL> select count (*) from dba_objects;

COUNT(*)
———-
31094

SQL> @ xplan_extended_display_cursor 2jf3kh52jy9fd 0
SQL_ID 2jf3kh52jy9fd, child number 0
————————————-
select count (*) from dba_objects

Plan hash value: 2476103605

————————————————————————————————————————————————————————————————————————————–
| Id | Pid | Ord | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | A-Time Self |Bufs Self |A-Ti S-Graph |Bufs S-Graph |LIO Ratio |TCF Graph |E-Rows*Sta|
————————————————————————————————————————————————————————————————————————————–
| 0 | | 13 | SELECT STATEMENT | | 1 | | 1 |00:00:00.77 | 12166 | | | | 00:00:00.00 | 0 | | | 0 | | |
| 1 | 0 | 12 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.77 | 12166 | | | | 00:00:00.02 | 0 | | | 0 | | 1 |
| 2 | 1 | 11 | VIEW | DBA_OBJECTS | 1 | 25931 | 31094 |00:00:00.75 | 12166 | | | | 00:00:00.09 | 0 | @ | | 0 | | 25931 |
| 3 | 2 | 10 | UNION-ALL | | 1 | | 31094 |00:00:00.66 | 12166 | | | | 00:00:00.12 | 0 | @@ | | 0 | | |
|* 4 | 3 | 6 | FILTER | | 1 | | 31078 |00:00:00.53 | 12159 | | | | 00:00:00.08 | 0 | @ | | 0 | | |
|* 5 | 4 | 3 | HASH JOIN | | 1 | 32255 | 32291 |00:00:00.36 | 443 | 1593K| 1593K| 1477K (0)| 00:00:00.25 | 0 | @@@@ | | 0 | | 32255 |
| 6 | 5 | 1 | TABLE ACCESS FULL | USER$ | 1 | 65 | 66 |00:00:00.01 | 6 | | | | 00:00:00.00 | 6 | | | 0 | | 65 |
|* 7 | 5 | 2 | TABLE ACCESS FULL | OBJ$ | 1 | 32255 | 32291 |00:00:00.11 | 437 | | | | 00:00:00.11 | 437 | @@ | | 0 | | 32255 |
|* 8 | 4 | 5 | TABLE ACCESS BY INDEX ROWID| IND$ | 5855 | 1 | 5184 |00:00:00.10 | 11716 | | | | 00:00:00.06 | 5859 | @ | @@@@@@ | 1 | | 5855 |
|* 9 | 8 | 4 | INDEX UNIQUE SCAN | I_IND1 | 5855 | 1 | 5855 |00:00:00.04 | 5857 | | | | 00:00:00.04 | 5857 | @ | @@@@@@ | 1 | | 5855 |
|* 10 | 3 | 9 | HASH JOIN | | 1 | 16 | 16 |00:00:00.01 | 7 | 1517K| 1517K| 606K (0)| 00:00:00.00 | 0 | | | 0 | | 16 |
| 11 | 10 | 7 | INDEX FULL SCAN | I_LINK1 | 1 | 16 | 16 |00:00:00.01 | 1 | | | | 00:00:00.00 | 1 | | | 0 | | 16 |
| 12 | 10 | 8 | TABLE ACCESS FULL | USER$ | 1 | 65 | 66 |00:00:00.01 | 6 | | | | 00:00:00.00 | 6 | | | 0 | | 65 |
————————————————————————————————————————————————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

4 – filter(((“O”.”TYPE#”<>1 AND “O”.”TYPE#”<>10) OR (“O”.”TYPE#”=1 AND =1)))
5 – access(“O”.”OWNER#”=”U”.”USER#”)
7 – filter((“O”.”LINKNAME” IS NULL AND BITAND(“O”.”FLAGS”,128)=0 AND “O”.”NAME”<>‘_NEXT_OBJECT’ AND
“O”.”NAME”<>‘_default_auditing_options_’))
8 – filter((“I”.”TYPE#”=1 OR “I”.”TYPE#”=2 OR “I”.”TYPE#”=3 OR “I”.”TYPE#”=4 OR “I”.”TYPE#”=6 OR “I”.”TYPE#”=7 OR
“I”.”TYPE#”=9))
9 – access(“I”.”OBJ#”=:B1)
10 – access(“L”.”OWNER#”=”U”.”USER#”)

36 rows selected.

Oracle 坏块处理(final)

January 8, 2012 maintain, oracle 2 comments

收到开发人员报告,oracle出现坏块,信息如下:

Hex dump of (file 7, block 1407500) in trace file /data/oracle/admin/orcl/bdump/orcl_p004_7383.trc
Corrupt block relative dba: 0x01d57a0c (file 7, block 1407500)
Fractured block found during crash/instance recovery
Data in bad block:
type: 6 format: 2 rdba: 0x01d57a0c
last change scn: 0x0000.e02e5f2d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x88150601
check value in block header: 0x462f
computed block checksum: 0xd738
Reread of rdba: 0x01d57a0c (file 7, block 1407500) found same corrupted data

至此,我们确定该坏块是 7月28号上午9:45分数据库server crash导致的。

解决思路:
首先确认该坏块所属对象

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

SQL> set pagesize 100
SQL> set linesize 144
SQL> col segment_name format a50

SQL> select
object_id||’-‘||segment_name||’/’||partition_name segment_name,
segment_type,
RELATIVE_FNO
from dba_extents a
,dba_objects b
where file_id = ‘7’
and 1407500 between block_id and block_id + blocks – 1
and a.SEGMENT_NAME = b.OBJECT_NAME
and (a.PARTITION_NAME = b.SUBOBJECT_NAME or a.PARTITION_NAME is null)
and a.owner = b.owner

SEGMENT_NAME SEGMENT_TYPE RELATIVE_FNO
—————— ————
159761-IDX_D_D_PRODUCT_ID/ INDEX 7

确认该坏块属于索引IDX_D_D_PRODUCT_ID。先重建索引暂时解决问题:

SQL> alter index IDX_D_D_PRODUCT_ID rebuild online tablespace pur_index ;
Index altered.


使用dbv检查:

[oracle@rac1 ~]$ dbv file=/data/oracle/oradata/orcl/purchase03.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 – Production on Thu Jul 29 10:53:48 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /data/oracle/oradata/orcl/purchase03.dbf
DBV-00200: Block, dba 30767628, already marked corrupted
DBVERIFY – Verification complete
Total Pages Examined : 4194302
Total Pages Processed (Data) : 3508146
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 605887
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75876
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 3781089739 (0.3781089739)

我们也可以使用如下过程确定坏块属于哪个block:
SQL> select dbms_utility.data_block_address_file(30767628) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(30767628)

7
Elapsed: 00:00:00.23
SQL> select dbms_utility.data_block_address_block(30767628) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(30767628)

1407500

其次要确认坏块类型是物理坏块还是逻辑坏块
for physical corruption check: backup validate datafile ‘filename’;
for logical corruption check: backup check logical validate datafile ‘filename’
then find out whether there is corruption from dynamic view V$DATABASE_BLOCK_CORRUPTION
先进行物理坏块检测
RMAN> backup validate datafile 7;
Starting backup at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1736 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/data/oracle/oradata/orcl/purchase03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 29-JUL-10
sys@std01> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- —————— ———
7 1407500 1 0 CORRUPT

确认为物理坏块
最后也是最重要的一步,进行坏块恢复,


RMAN> BLOCKRECOVER CORRUPTION LIST;
Starting blockrecover at 29-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=455 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /backup/rman/rac1_ORCL_20100729_eoljvn41_1_1.bak

channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/rman/rac1_ORCL_20100729_eoljvn41_1_1.bak tag=TAG20100729T050504
channel ORA_DISK_1: block restore complete, elapsed time: 00:57:56
failover to previous backup
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /backup/rman/rac1_ORCL_20100728_eiljt2ju_1_1.bak

channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/rman/rac1_ORCL_20100728_eiljt2ju_1_1.bak tag=TAG20100728T050253
channel ORA_DISK_1: block restore complete, elapsed time: 00:58:36
starting media recovery
media recovery complete, elapsed time: 00:12:15
Finished blockrecover at 29-JUL-10


命令完成,分别使用DBV和backup validate命令进行验证


[oracle@rac1 ~]$ dbv file=/data/oracle/oradata/orcl/purchase03.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 – Production on Thu Jul 29 19:04:45 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /data/oracle/oradata/orcl/purchase03.dbf

DBVERIFY – Verification complete
Total Pages Examined : 4194302
Total Pages Processed (Data) : 3507791
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 606239
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75879
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3785553739 (0.3785553739)

使用 backup validate datafile 进行验证:


验证前:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- —————— ———
7 1407500 1 0 CORRUPT
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Thu Jul 29 21:17:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1185342296)
RMAN> backup validate datafile 7;
Starting backup at 29-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=455 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/data/oracle/oradata/orcl/purchase03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
Finished backup at 29-JUL-10
执行完上述命令,坏块从数据字典里消失:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected

问题得到解决。

—————————————————————————-
下面模拟一种特殊情况,CORRUPTION BLOCKS上面还不存在object 这时候用BLOCKRECOVER CORRUPTION LIST是恢复不了的,我们提供下面一种方法。
首先查找已经format的块,也就是所谓的存在object的块:

SQL>create tablespace block datafile ‘/u01/app/oracle/oradata/axx/block01.dbf’ size 100M;

tablespace created

SQL> select file_id from dba_data_files where tablespace_name=’BLOCK’;

FILE_ID
———-
6

SQL> select OWNER,SEGMENT_NAME from dba_extents a where file_id=6 and 500 between a.block_id and a.block_id+a.blocks;

no rows selected

SQL> create table tmp_1 (id int) tablespace block;

Table created.

找出BLOCK中已经使用的最大块:

SQL> select max(BLOCK_ID+blocks) from dba_extents a where tablespace_name=’BLOCK’;

MAX(BLOCK_ID+BLOCKS)
——————–
489

下面模拟损坏datafile 6 的 第 500个block;

[oracle@liu ~]$ dd of=/u01/app/oracle/oradata/axx/block01.dbf bs=8192 seek=500 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out

RMAN> backup validate datafile 6;

Starting backup at 08-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=132 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u01/app/oracle/oradata/axx/block01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 08-JAN-12

[oracle@liu ~]$ dbv file=/u01/app/oracle/oradata/axx/block01.dbf blocksize=8192;

DBVERIFY: Release 10.2.0.1.0 – Production on Sun Jan 8 20:17:19 2012

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

DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/axx/block01.dbf
Page 500 is marked corrupt
Corrupt block relative dba: 0x018001f4 (file 6, block 500)
Completely zero block found during dbv:

DBVERIFY – Verification complete

Total Pages Examined : 12800
Total Pages Processed (Data) : 613
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 61
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12125
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1178159 (0.1178159)
[oracle@liu ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Jan 8 20:17:30 2012

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
6 500 1 0 ALL ZERO

SQL> alter system switch logfile;

System altered.

可以看到第500个block已经被标记为 Corrupt,下面我们通过创建object format这个block;

SQL> create table tmp_2 (id int) tablespace block;

Table created.

创建一个monitor trigger 达到第500个block后自动终止操作,由于这次只是模拟,datafile很小,在实际环境中可能出现Corrupt block在datafile的尾部接近max block,这时候可以
通过创建一张temp table allocate extent达到接近这个max block 然后再创建填充数据的table.


SQL> create or replace trigger trigger_monitor after insert on tmp_2 for each row
2 declare
3 check_block_id number;
4 begin
5 select dbms_rowid.rowid_block_number(:new.rowid) into check_block_id from dual;
6 if check_block_id=500 then
7 raise_application_error(-20201,’curroption block has been format’);
8 end if;
9 end;
10
11 /

Trigger created

SQL>
SQL>
SQL> begin
2 for i in 1..1000000 loop
3 insert into tmp_2 values (i);
4 commit;
5 end loop;
6 end;
7 /

begin
for i in 1..1000000 loop
insert into tmp_2 values (i);
commit;
end loop;
end;

ORA-20201: curroption block has been format
ORA-06512: at “LIU.TRIGGER_MONITOR”, line 6
ORA-04088: error during execution of trigger ‘LIU.TRIGGER_MONITOR’
ORA-06512: at line 4

第500个block已经被填充,下面我们来验证结果:


[oracle@liu ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Jan 8 20:06:25 2012

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

connected to target database: AXX (DBID=665578413)

RMAN> backup validate datafile 6;

Starting backup at 08-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u01/app/oracle/oradata/axx/block01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 08-JAN-12

RMAN> exit

Recovery Manager complete.
[oracle@liu ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Jan 8 20:07:11 2012

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

no rows selected

SQL> !
[oracle@liu ~]$ dbv file=/u01/app/oracle/oradata/axx/block01.dbf blocksize=8192;

DBVERIFY: Release 10.2.0.1.0 – Production on Sun Jan 8 20:09:07 2012

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

DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/axx/block01.dbf

DBVERIFY – Verification complete

Total Pages Examined : 12800
Total Pages Processed (Data) : 614
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 61
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12125
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1178159 (0.1178159)

OK 已经恢复了,如果这种情况下再采用rman 的blockrecover 是恢复不了这个block的 我们来测试下:
制造坏块,同时确保max block 小于500;

SQL> select max(BLOCK_ID+blocks) from dba_extents a where tablespace_name=’BLOCK’;

MAX(BLOCK_ID+BLOCKS)
——————–
489

RMAN> backup database ;

Starting backup at 08-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=132 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/axx/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/axx/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/axx/tbs_stream01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/axx/block01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/axx/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/axx/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/07n09aa7_1_1 tag=TAG20120108T202335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 08-JAN-12
channel ORA_DISK_1: finished piece 1 at 08-JAN-12
piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/08n09ac8_1_1 tag=TAG20120108T202335 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 08-JAN-12

RMAN> exit

[oracle@liu ~]$ dd of=/u01/app/oracle/oradata/axx/block01.dbf bs=8192 seek=500 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out

[oracle@liu ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Jan 8 20:23:01 2012

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

connected to target database: AXX (DBID=665578413)

RMAN> backup validate datafile 6;

Starting backup at 08-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/u01/app/oracle/oradata/axx/block01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-12

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
6 500 1 0 ALL ZERO

RMAN> blockrecover corruption list;

Starting blockrecover at 08-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished blockrecover at 08-JAN-12

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
6 500 1 0 ALL ZERO

[oracle@liu ~]$ dbv file=/u01/app/oracle/oradata/axx/block01.dbf blocksize=8192;

DBVERIFY: Release 10.2.0.1.0 – Production on Sun Jan 8 20:26:51 2012

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

DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/axx/block01.dbf
Page 500 is marked corrupt
Corrupt block relative dba: 0x018001f4 (file 6, block 500)
Completely zero block found during dbv:

DBVERIFY – Verification complete

Total Pages Examined : 12800
Total Pages Processed (Data) : 613
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 61
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12125
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 1189579 (0.1189579)

可以看到坏块依然存在