10g

Sqlplus HTML reports

I was working on setting up monitoring for application team to monitor AQ and wanted html formatting of table data. On searching I found this link from Ittichai Chammavanijakul  which makes use of css formatting to generate good visual reports.

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400 30

I used following code

set markup HTML ON HEAD "<style type='text/css'> -
body { -
font:10pt Arial,Helvetica,sans-serif; -
color:blue; background:white; } -
p { -
font:8pt Arial,sans-serif; -
color:grey; background:white; } -
table,tr,td { -
font:10pt Arial,Helvetica,sans-serif; -
text-align:right; -
color:Black; background:white; -
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -
th { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:#336699; -
background:#cccc99; -
padding:0px 0px 0px 0px;} -
h1 { -
font:16pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
border-bottom:1px solid #cccc99; -
margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 { -
font:bold 10pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
margin-top:4pt; margin-bottom:0pt;} -
a { -
font:9pt Arial,Helvetica,sans-serif; -
color:#663300; -
background:#ffffff; -
margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
.threshold-critical { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:red; } -
.threshold-warning { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:orange; } -
.threshold-ok { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:green; } -
</style> -
<title>SQL*Plus Report</title>" -
BODY "" - 
TABLE "border='1' width='90%' align='center'" -
ENTMAP OFF SPOOL ON

So you can execute above code in sqlplus and spool the output to file. This file can be send using uuencode/sendmail function . You can use below code (note content-type is text/html)

_send_email()
{
/usr/lib/sendmail -t << EOF
From: $FROMADDRESS
To: $EMAIL
Subject: $MESSAGE
X-Priority: 1
Content-type: text/html

`cat $LOGFILE |grep -v 'rows selected'`
EOF
}

Call the function _send_email in your script and ensure all variables are correctly set.

uuencode usage is also simple

uuencode emp.html emp.html|mailx [email protected]
28 June, 2012 by 10g 11g 1

10gR2 Silent Install with 11gr2 CRS fails

I was trying to perform a 10.2 silent install with 11gR2 CRS. While doing pre-checks installer failed with following error

Check complete: Failed <<<<
Problem: The 'active' version of Oracle Clusterware is not 10g Release 2 (10.2).
Recommendation: You must upgrade all nodes of the cluster to Oracle Clusterware 10g Release 2.  If you have upgraded some but not all of the nodes to use the 10g Release 2 version of Oracle Clusterware, then the 'active' version is still 10g Release 1 (10.1)  You must upgrade all nodes in the cluster to Oracle Clusterware 10g Release 2 before installing Oracle 10g Release 2 Real Application Clusters.

I tried “ignoreSysPrereqs” option with runInstaller but it also did not succeed. I checked My Oracle Support (formerly metalink..anyways I still refer to as metalink) and also searched for any known issues, but couldn’t find any document. I could find some issues on OTN but there was no solution. Finally I searched for the file reporting this error in Oracle software staging location.

$% grep -r "version of Oracle Clusterware is not 10g Release 2" *
stage/prereq/db/db_prereq.xml:

This was part of following code( I have removed Angle brackets with Square brackets as wordpress confuses it with html tags)

[PREREQUISITE NAME="Detect10.2CRS"
                EXTERNALNAME="Checking Oracle Clusterware version ..."
                EXTERNALNAMEID="[email protected]"
                SEVERITY="Error"]
        [DESCRIPTION TEXT="This is a prerequisite condition to test if all nodes in the cluster have had the Clusterware upgraded to 10g Release 2 (10.2)."
                TEXTID="S_CHECK_10.2_CRS_DESCRIPTION@oracle.install.prereqs.resources.PrereqRes"/]
        [RULESETREF NAME="CRS102Checks" RULE="CheckFor102CRS" FILE="db/refhost.xml"
                RESULTS_FILE="install_rule_results.xml"/]
        [PROBLEM TEXT="The 'active' version of Oracle Clusterware is not 10g Release 2 (10.2)."
                TEXTID="S_CHECK_10.2_CRS_ERROR@oracle.install.prereqs.resources.PrereqRes"]
        [/PROBLEM]

Checking “Detect10.2CRS” in My Oracle Support, got exact hit

Silent Install 10.2.0.1 Database Fails When Cluster Is 11.1.0.6 [ID 755345.1]

As per note, we need to change the following lines in (software location)\stage\prereq\db\db_prereq.xml file ( I have removed Angle brackets with Square brackets as wordpress confuses it with html tags)

[PREREQUISITESET NAME="clusterTests"]
[PREREQUISITEREF NAME="Detect10.2CRS" SEVERITY="Error"/]
[/PREREQUISITESET]

to :

[PREREQUISITESET NAME="clusterTests"]
[/PREREQUISITESET]

You would be required to do same change for similar file to any 10g patchset on top of it. In case of 10.2.0.4 patch I found it under (software_location)/stage/prereq/patch_prereqs.xml
Searching on the error messages in My Oracle Support did not return above document. Anyways documenting it so that Search engines can report it faster. Note that to use 10g DB software with 11gR2 CRS, you will have to pin the nodes

$GRID_HOME/bin/crsctl pin css -n node1 node2

olsnodes -t will report current status of the nodes i.e whether pinned or not

16 August, 2011 by 10g 0

Effect Of Multiple SHMMAX Settings

Last week I saw a warning message at database startup time saying:

WARNING: EINVAL creating segment of size 0x000000000f0020xx
fix shm parameters in /etc/system or equivalent

It is an Oracle 10204 database running on Solaris.

Searching MOS for exact meaning for this warning, it states that a new shared memory segment is getting created to accommodate SGA.

As the message indicated, I opened /etc/system file to verify the settings of SHMMAX parameter and found the SHMMAX value to be 4GB. I stopped at this point and closed the /etc/system file. Then the next thing to check is the number of oracle instances running on the server and the size of largest SGA.

There were two instances running on the server and the largest SGA was set to 1.8G and the other SGA size was 700M.

This setting shows that there is no need to create additional shared memory segment. Then I checked the /etc/system file again, but this time I used the following command :

<span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">$ cat /etc/system | grep shmmax</span></span>
<span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">set shmsys:shminfo_shmmax=4000000000  ==&gt;4GB</span></span>
<span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">set shmsys:shminfo_shmmax=500000000  ==&gt;500M</span></span><span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">

There were two different values set for SHMMAX parameter.

The cause of the above warning message came out to be:
As the files are read from bottom-to-top, server was taking SHMMAX value as 500M and ignoring the 4GB value.

After commenting SHMMAX value of 500M, the warning message disappeared.

28 August, 2010 by 10g 0

Physical Corruption: ORA-1578 part 3

Recently we encountered a block corruption issue in 10g database on Linux x86 64 bit and using ASM for storing database files.
Saurabh had earlier written articles on ora-1578.

http://askdba.org/weblog/2008/04/physical-corruption-ora-1578-part-1/
 http://askdba.org/weblog/2008/05/physical-corruption-ora-1578-part-2/

It was first time I was working on a corruption issue, so I followed above notes and found them quite useful. Good thing about ora-1578 error message is that it gives the problematic block and datafile number

ORA-01578: ORACLE data block corrupted (file # 699, block # 188654)
ORA-01110: data file 699:
'+DG_IDX/james/datafile/scott_emp_detail.381.705692005'

Querying dba_extents revealed that it was a Fact table and one partition was giving error.Since we had rman backup for this database we directly went for recovery using blockrecover command

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Aug 7 01:48:51 2010

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

connected to target database: james (DBID=1812247572)

RMAN> run {blockrecover datafile 699 block 188654;}

Starting blockrecover at 07-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2231 instance=james4 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=2190 instance=james4 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=2142 instance=james4 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=2186 instance=james4 devtype=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid=2170 instance=james4 devtype=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: sid=2203 instance=james4 devtype=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: sid=2240 instance=james4 devtype=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: sid=2195 instance=james4 devtype=DISK

channel ORA_DISK_2: restoring block(s)
channel ORA_DISK_2: specifying block(s) to restore from backup set
restoring blocks of datafile 00699
channel ORA_DISK_2: reading from backup piece /RMNGD_RMAN1/backup/james/29lk4ift_1_1

user interrupt received
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 08/07/2010 02:52:27
RMAN-03099: job cancelled at user request

We had to cancel the block recovery as even after 1 hour it could not locate the block from backup piece. Checking backup piece found that this backup piece was of 184G

ls -lh /RMNGD_RMAN1/backup/james/29lk4ift_1_1
-rw-r-----  1 oracle dba 184G Jul 31 18:17 /RMNGD_RMAN1/backup/james/29lk4ift_1_1

We had stopped the data load for this table but delaying further was not possible as it was impacting application. Finally we  decided to use dbms_repair table to allow DML to mark the block as corrupt .
This table was FACT table which could have been loaded again (lucky for us 🙂 ). Before marking it corrupt, we took a dump of block

ALTER SYSTEM DUMP DATAFILE 699 block 188654;
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => dbms_repair.repair_table,
     ACTION     => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;
/

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
     SCHEMA_NAME => 'SCOTT',
     OBJECT_NAME => 'EMP_DETAIL_FACT',
     PARTITION_NAME => 'SYS_P1140013',
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     CORRUPT_COUNT =>  num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION FROM REPAIR_TABLE;

  SET SERVEROUTPUT ON
  DECLARE num_fix INT;
  BEGIN
  num_fix := 0;
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
      SCHEMA_NAME => 'SCOTT',
      OBJECT_NAME => 'EMP_DETAIL_FACT',
     PARTITION_NAME => 'SYS_P1140013',
      OBJECT_TYPE => dbms_repair.table_object,
      REPAIR_TABLE_NAME => 'REPAIR_TABLE',
      FIX_COUNT=> num_fix);
  DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
  END;
  /

  execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SCOTT','EMP_DETAIL_FACT')

Refer 10g doc to get details on DBMS_REPAIR.

This allowed the application to load data again. Later we worked with application team and created a new segment and renamed the old one to *_bkp and then dropped it.
Next day we got a alert informing errors with our rman backup

ORA-19566: exceeded limit of 0 corrupt blocks for file +DG_IDX/james/datafile/scott_emp_detail.381.705692005
continuing other job steps, job failed will not be re-run
channel ORA_DISK_8: finished piece 1 at AUG-10-2010 01:56:45
piece handle=/RMNGD_RMAN3/backup/james/i2lkuv0q_1_1 tag=james_INC_20100810 comment=NONE
channel ORA_DISK_8: backup set complete, elapsed time: 00:26:43
channel ORA_DISK_1: finished piece 1 at AUG-10-2010 02:13:01
piece handle=/RMNGD_RMAN1/backup/james/hrlkuv0i_1_1 tag=james_INC_20100810 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:43:07
channel ORA_DISK_6: finished piece 1 at AUG-10-2010 02:21:26
piece handle=/RMNGD_RMAN2/backup/james/i0lkuv0n_1_1 tag=james_INC_20100810 comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:51:27
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

Backup job failed again as the table was still lying in recyclebin.

03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name=\'EMP_DETAIL_FACT_BKP\';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name=\'EMP_DETAIL_FACT_BKP\';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==03:16:47 SQL> select * from dba_recyclebin where original_name='EMP_DETAIL_FACT_BKP';

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12                                                                                                                                            NO        NO               4465594     4465594   4465594
      1088

SCOTT                       BIN$jXvLFhsgCOXgQA1M6jspCQ==$0 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896 EMP_DETAIL_FACT_BKP
DROP                            Table Partition
scott_emp_detail                                                                                                      2009-11-09:18:41:14
2010-08-10:09:40:39                                                    9.9476E+12
                                                                                                                                               NO        NO               4465594     4465594   4465594
     12896

So we decided to purge the recyclebin and resized datafile to ensure that we get rid of corrupt block. As a result maximum block for datafile was 117765, whereas we had error for block 188654. 

03:19:07 SQL> conn scott

Enter password: 

Connected.

03:19:20 SQL> purge recyclebin;

03:23:39 SQL> alter database datafile '+DG_IDX/james/datafile/scott_emp_detail.381.705692005' resize 3800m;

Database altered.

  1* select max(block_id) from dba_extents where file_id=699

03:24:25 SQL> /

MAX(BLOCK_ID)

-------------

       117765

We also ran rman backup validate and dbverify to ensure that there was no corruption

RMAN> backup validate datafile 699;

Starting backup at 11-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2171 instance=james4 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=2157 instance=james4 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00699 name=+DG_IDX/james/datafile/scott_emp_detail.381.705692005
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 11-AUG-10

Since this is ASM, dbv needs you to specify system password

dbv file=+DG_IDX/james/datafile/scott_emp_detail.381.705692005 userid=system/oracle123 blocksize=32768

DBVERIFY - Verification starting : FILE = +DG_IDX/james/datafile/scott_emp_detail.381.705692005
DBVERIFY - Verification complete

Total Pages Examined         : 118400
Total Pages Processed (Data) : 110979
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 7421
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 0 (0.0)

All this exercise made us re-look at rman backup. We realized that we need to reduce the size of RMAN backup piece as having smaller piece will help us in faster recovery of data blocks or datafile loss.
We had two options

1) Specify value of maxpiecesize so as to limit size of backup piece.
2) Specify filesperset parameter to limit the number of files in backup piece.

Both things meant we had more backup pieces but definitely it would result in improved recovery times. We modified the backup script to include filesperset parameter

BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE FILESPERSET=1

In our case we could reload the data so we dropped the old table. But in case you can’t do it and you have rman backups failing because of corrupt block issue, you can use following syntax in rman run clause to skip ‘n’ corrupt blocks

set maxcorrupt for datafile 699 to 1;
21 August, 2010 by 10g 3

Cloning RAC Database with RMAN fails with error

Steps for cloning a RAC database  with RMAN is similar to cloning a single instance database. But while using rman duplicate in 10g, you will get following errors

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/14/2010 00:09:42
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

RMAN> exit

This is due to Bug 4355382 ORA-38856: FAILED TO OPEN DATABASE WITH RESETLOGS WHEN USING RAC BACKUP

To avoid this we need to set the following initialization parameter on the auxiliary database; and restart the database in no mount mode

_no_recovery_through_resetlogs=TRUE

Unset this once rman duplicate command completes successfully. One more notable thing which I noticed while cloning this database was that if you use skip readonly clause to skip duplicating read only tablespace, entries for read only tablespace still remain and file names are marked as missing

/oracle/product/10.2/dbs/MISSING00048
OFFLINE READ ONLY

/oracle/product/10.2/dbs/MISSING00049
OFFLINE READ ONLY

This is normal behavior and not a bug. From docs

SKIP READONLY Excludes datafiles in read-only tablespaces from the duplicate database.
Note: A record for the skipped read-only tablespace still appears in DBA_TABLESPACES. By using this feature, you can activate the read-only tablespace later. For example, you can store the read-only tablespace data on a CD-ROM, then mount the CD-ROM later and view the data

If you don’t plan to make use of these tablespaces, you can drop them by simply using drop tablespace command .Meanwhile do you know that  tables can be dropped from read-only tablespace. To know more about it, you can read this entry from Tanel describing this behavior

15 August, 2010 by 10g 0

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers