Tag Archives: ora-06512

Fractured block in Oracle

Fractured Block in Oracle

Fractured block is a block in which header and footer are not consistent. If your database has any fractured block then you RMAN backup is failed.

1. For checking the Oracle Database from corruption/Fractured block.


From RMAN:
Rman> backup validate check logical database;

From OS:
dbv file='E:\oracle\oradata\sysauz01.dbf'

2. Following block show the block id,file id, type of corruption after RMAN command

Select * from v$database_block_corruption;

3. Find the object_name,object_type from following view:

select * from dba_extents where file_id = &DATA_FILE_ID and &CORRUPTED_BLOCK_ID between block_id AND block_id + blocks - 1;

Note: IF no object is part of corrupted/Fractured block upper query will return no rows. We have following query to verify it.

Note: Not need to specify the blockid and fileid in following query:

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id = c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id = c.block#
order by file#, corr_start_block#;

4. If you find fractured block in free space then you follow oracle(Doc ID 336133.1)
According to this doc id, for remove the fractured block in free space. you need
to write on that block then it will automatically fixed the issue.

1. After run the RMAN validate command.

Rman> backup validate check logical database;

2. Having fractured block in sysaux file.

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 FAILED 0 23534 85762 353506490

File Name: E:\ORACLE\ORADATA\IC\SYSAUX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 18166
Index 1 16146
Other 0 27914

3. On checking with above query 3 found data block is empty/freelist

4. create a table with Scott user
Note: Not create with sys or system users

create table s(
n number,
c varchar2(4000)
) nologging tablespace sysaux pctfree 99;
Table created.
Note: Mentioned tablespace which having fractured block

5. Create trigger with Scott user
Note: enter the corrupted block id and file id

CREATE OR REPLACE TRIGGER corrupt_trigger
AFTER INSERT ON scott.s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
END;
/
Enter value for blocknumber: 77810
old 8: IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
new 8: IF (dbms_rowid.rowid_block_number(:new_p.rowid)=77810)
Enter value for filenumber: 2
old 9: and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
new 9: and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=2) THEN

Trigger created.

6. Start file the tablespace having fractured block

BEGIN
FOR i IN 1..1000000000 LOOP
INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;
/

Following error occurred if you overwrite the corrupted block otherwise start repeat the insert operation until you got following error, Its depend upon size of tablespace

BEGIN
*
ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at "SCOTT.CORRUPT_TRIGGER", line 10
ORA-04088: error during execution of trigger 'SCOTT.CORRUPT_TRIGGER'
ORA-06512: at line 3

7. After getting error rerun the validate command for rman for verify

Rman> backup validate check logical database;

Advertisements

Stats for Table and Table Partitions

Backup, Restore, Copy, lock & unlock Stats for Table and Table Partitions

Stats will help the optimizer to choose the better execution plan for SQL Queries. Sometime change in stats may cause the performance issue in Database. You need to take backup of stats before any major task like Upgrade,patching, application deployment etc.

Using procedures in DBMS_STATS package you can backup and restore stats for table.

Assumes the user SCOTT already has access to execute DBMS_STATS

GRANT EXECUTE ON dbms_stats TO scott;

Note: SYS user give grant for execute the DBMS_STATS package to Scott user

Backup and restore for table stats

1. Following command will create table for backup the stats

execute dbms_stats.create_stat_table(ownname=> 'scott', stattab=> 'backup_stats');

2. Procedure to export statistics for a table

exec dbms_stats.export_table_stats(ownname=>'scott', tabname=>'test', statown=>'scott', stattab=>'backup_stats', cascade=>true);
Cascade is true means it will export table plus index stats.
 
3. Import table stats (Eg TEST to TEST1 table)

SQL> exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'test1', statown=>'scott', stattab=>'backup_stats', cascade=>true);

4. Drop statistics table

SQL> execute dbms_stats.drop_stat_table(ownname=> 'scott', stattab=> 'backup_stats');

Copy stats for a table partition to another partition
Example: Suppose i have test table having 5 partition, my SQL query is running fine in P4 partition but when it access P5 partition it used different execution plan rather than P4 plan. So in some case we need to copy the stats of one partition to next partition and lock the stats

exec dbms_stats.copy_table_stats('SCOTT', 'TEST',srcpartname=>'p4', dstpartname=>'p5');

Lock and Unlock the Stats for the Table
You can also locked and unlocked the stats manually, so that automatic job do not change the stats of the table

1. Check the lock stats status for the table
 
SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';
STATT
—–
ALL

2. Lock the stats for the table

exec dbms_stats.lock_table_stats('scott', 'test');
PL/SQL procedure successfully completed.

3. Unlock the stats for the table

SQL> exec dbms_stats.unlock_table_stats('scott', 'test');
PL/SQL procedure successfully completed.

4. Error occurs if stats for table is locked,then you need to unlocked table stats

— gather statistics on locked table
SQL> exec dbms_stats.gather_table_stats('scott', 'test');

ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10640
ORA-06512: at "SYS.DBMS_STATS", line 10664
ORA-06512: at line 1

– gather statistics on the index using analyze
SQL> analyze index scott.test_idx compute statistics;

ERROR at line 1:
ORA-38029: object statistics are locked

ORA-39002: invalid operation in Expdp Oracle

ORA-39002: invalid operation in Expdp

Error:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation

Error occurred in Oracle EXPDB backup because directory path in not exists in Windows / Linux

Solution:

Step 1: Please check the directory which you used in expdp directory parameter.

expdp directory=dbbackup

Step 2: Login with sysdba and check the directory path.

Select * from dba_directories where directory_name = 'DBBACKUP';

Step 3: Check the path shown in above queries and check in Windows / Linux is path is correct.

ORA-23421: job number xxx is not a job in the job queue

ORA-23421: job number xxx is not a job in the job queue

Oracle job scheduler has the error when tried to remove it.

SQL> exec dbms_job.remove(24);
BEGIN dbms_job.remove(24); END;
*
ERROR at line 1:
ORA-23421: job number 24 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 770
ORA-06512: at "SYS.DBMS_JOB", line 180
ORA-06512: at line 1

Option 1
1) Find the Owner of the job from dbms_job table

select job, schema_user from dba_jobs;
JOB Schema_User
--- -----------
24 HR

2) Log in as schema user and then remove the job.

Conn hr
Enter password:
connected.
select job,schema_user from user_jobs;
JOB Schema_User
--------- -----------
24 HR

exec dbms_job.remove(24);
PL/SQL procedure successfully completed.

Option 2
1) dbms_ijob : Oracle provide this package to handle other users jobs.It manage jobs which scheduled in DBA_JOBS.
SYS/SYSTEM user can use this for managing other user jobs.

SQL> exec dbms_ijob.remove(24);
PL/SQL procedure successfully completed.