'2015/09/08'에 해당되는 글 4건

exec dbms_fga.drop_policy(

object_schema => 'SCOTT',

object_name => 'EMP',

policy_name => 'CONA_ACCESS_FGA'

);

begin

dbms_fga.add_policy (

object_schema => 'SCOTT',

object_name => 'EMP',

policy_name => 'EMP_ACCESS_FGA',

audit_column => 'ENAME',

audit_condition => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') NOT IN (''127.0.0.1'',''10.103.90.1'',' ||

'''10.103.90.2'',''10.103.90.3'',''10.103.90.4''' ||

',''10.103.90.12'',''10.103.90.13'',''10.103.90.22'',''10.103.90.23''' ||

',''10.103.90.32'',''10.103.90.33'',''10.103.90.42'',''10.103.90.43'')' ,

statement_types => 'INSERT,UPDATE,DELETE,SELECT',

audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );

end;

/

select ENAME from scott.emp;

select *from scott.emp;

select *from DBA_FGA_AUDIT_TRAIL;

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE

l_source VARCHAR2(32767);

l_wrap VARCHAR2(32767);

BEGIN

l_source := 'CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS' ||

'BEGIN ' ||

'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ' ||

'END get_date_string;';

l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source);

DBMS_OUTPUT.put_line(l_wrap);

END;

/

CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS BEGIN RETURN TO_CHAR(SYSDATE, 'DD-MON-YYYY'); END get_date_string; /

wrap iname=get_date_string.sql oname=get_date_string_wrap.sql

ORA-27061: waiting for async I/Os failed

Linux-x86_64 Error: 5: Input/output error

Additional information: -1

Additional information: 4194304

WARNING: Read Failed. group:1 disk:4 AU:626 offset:0 size:4194304

WARNING: failed to read mirror side 1 of virtual extent 1977 logical extent 0 of file 261 in group [1.103449180] from disk DATA_0004 allocation unit 626 reason error; if possible, will try another mirror side

NOTE: successfully read mirror side 2 of virtual extent 1977 logical extent 1 of file 261 in group [1.103449180] from disk HDD_E0_S01_975274559P1 allocation unit 4776

Tue Apr 28 03:10:41 2015

Hex dump of (file 4, block 1028608) in trace file /u01/app/oracle/diag/rdbms/SID/SID1/trace/SID1_ora_21436.trc

Corrupt block relative dba: 0x010fb200 (file 4, block 1028608)

Completely zero block found during backing up datafile

Trying mirror side HDD_E1_S06_1135637411P1.

Reread of blocknum=1028608, file=+DATA/datafile/undotbs2.261.794895089. found same corrupt data

Reread of blocknum=1028608, file=+DATA/datafile/undotbs2.261.794895089. found valid data

Hex dump of (file 4, block 1028609) in trace file /u01/app/oracle/diag/rdbms/SID/SID1/trace/SID_ora_21436.trc

Corrupt block relative dba: 0x010fb201 (file 4, block 1028609)

Completely zero block found during backing up datafile

….

..

Corrupt block relative dba: 0x00800201 (file 2, block 513)

Bad header found during backing up datafile

Data in bad block:

 type: 1 format: 2 rdba: 0x00003102

 last change scn: 0xb408.00000003 seq: 0x8 flg: 0x01

 spare1: 0x8 spare2: 0x1 spare3: 0x0

 consistency value in tail: 0x00000000

 check value in block header: 0x9

 block checksum disabled

ksfdrfms:Mirror Read file=+DATA/wind/datafile/sysaux.257.797856311 fob=0x3cb15a548 bufp=0x7f1f74501000 blkno=513 nbytes=8192

ksfdrfms: Read success from mirror side=1 logical extent number=0 disk=DATA_0002 path=/dev/asmdisks/oracle_ocr02p1

Mirror I/O done from ASM disk /dev/asmdisks/oracle_ocr02p1 

Trying mirror side DATA_0002.

Reread of blocknum=513, file=+DATA/wind/datafile/sysaux.257.797856311. found same corrupt data

ksfdrnms:Mirror Read file=+DATA/wind/datafile/sysaux.257.797856311 fob=0x3cb15a548 bufp=0x7f1f74501000 nbytes=8192

ksfdrnms: Read success from mirror side=2 logical extent number=1 disk=DATA_0003 path=/dev/asmdisks/oracle_recovery01p1

Mirror I/O done from ASM disk /dev/asmdisks/oracle_recovery01p1 

Reread of blocknum=513, file=+DATA/wind/datafile/sysaux.257.797856311. found valid data

1) Take a full backup of the database and archivelogs

2) Shutdown the database and startup mount the database

3) Restore and recovery as follows:

RMAN > run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

restore datafile < list all the affected files separated by commas >;

recover database;

sql 'alter database open';

}

Item 1 this will backup the database if the block is corrupt on the primary side RMAN will get the good block from the mirror. So once you have a good backup restoring the datafile will restore with good blocks.