Make index invisible

If we want to test the impact of dropping the index,we can do it in an easy way by making the index invisible.Here I am going to execute a query which will utilize an existing index PRODUCTS_PROD_CAT_IX.


SQL> set autotrace on EXPLAIN

SQL> select prod_category,avg(amount_sold) from sh.sales s,sh.products p where p.prod_id=s.prod_id group by prod_category;

PROD_CATEGORY                                      AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other                                           34.1313997
Hardware                                                 1344.50776
Electronics                                              125.551667
Peripherals and Accessories                              108.824588
Photo                                                    188.064642


Execution Plan
----------------------------------------------------------
Plan hash value: 1197568639

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |     5 |   255 |   520   (8)| 00:00:07 |       |       |
|   1 |  HASH GROUP BY           |                      |     5 |   255 |   520   (8)| 00:00:07 |       |       |
|*  2 |   HASH JOIN              |                      |    72 |  3672 |   519   (7)| 00:00:07 |       |       |
|   3 |    VIEW                  | VW_GBC_5             |    72 |  2160 |   516   (7)| 00:00:07 |       |       |
|   4 |     HASH GROUP BY        |                      |    72 |   648 |   516   (7)| 00:00:07 |       |       |
|   5 |      PARTITION RANGE ALL |                      |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   6 |       TABLE ACCESS FULL  | SALES                |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   7 |    VIEW                  | index$_join$_002     |    72 |  1512 |     3  (34)| 00:00:01 |       |       |
|*  8 |     HASH JOIN            |                      |       |       |            |          |       |       |
|   9 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |     1   (0)| 00:00:01 |       |       |
|  10 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |     1   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("P"."PROD_ID"="ITEM_1")
   8 - access(ROWID=ROWID)




Now lets make the index invisible and compare the difference.

SQL> alter index SH.PRODUCTS_PROD_CAT_IX invisible;

Index altered.

SQL> set autotrace on EXPLAIN
SQL> select prod_category,avg(amount_sold) from sh.sales s,sh.products p where p.prod_id=s.prod_id group by prod_category;

PROD_CATEGORY                                      AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other                                           34.1313997
Hardware                                                 1344.50776
Electronics                                              125.551667
Photo                                                    188.064642
Peripherals and Accessories                              108.824588


Execution Plan
----------------------------------------------------------
Plan hash value: 504757596

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     5 |   255 |   520   (7)| 00:00:07 |       |       |
|   1 |  HASH GROUP BY          |          |     5 |   255 |   520   (7)| 00:00:07 |       |       |
|*  2 |   HASH JOIN             |          |    72 |  3672 |   519   (7)| 00:00:07 |       |       |
|   3 |    VIEW                 | VW_GBC_5 |    72 |  2160 |   516   (7)| 00:00:07 |       |       |
|   4 |     HASH GROUP BY       |          |    72 |   648 |   516   (7)| 00:00:07 |       |       |
|   5 |      PARTITION RANGE ALL|          |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   6 |       TABLE ACCESS FULL | SALES    |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   7 |    TABLE ACCESS FULL    | PRODUCTS |    72 |  1512 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------

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

   2 - access("P"."PROD_ID"="ITEM_1")

So if we compare the two explain plans ,in the second plan we are doing full table scan instead of index fast full scan and also the cost of the second explain plan is high.

RMAN Cloning issues

I saw this issue few days back , it was related to some issue in 10.2.0.3 version database.We were doing point in time rman cloning from production to test.In the middle of the cloning it errored out saying that it is not able to create the control file.We created the control file manually including all the datafiles in the script.Then we followed the oracle support doc id  360962.1 to resume the rest of the cloning steps.

We restarted the cloning following the step 5 of the doc.

$ rman target / auxiliary sys/oracle@

RMAN> run { 
   set new name for datafile n to 'X';
   set until scn 1412841;
   recover clone database;
   alter clone database open resetlogs;
}

At the end of the script it was throwing error RMAN-00600 .It was strange why it was throwing this error.After sometime we noticed that this error was because we have not given the "Set newname for  datafile n to [newname]" in the script.Note that we are using the parameter db_file_name_convert in the parameter file of the test database.Still we changed it in the script and ran it again.This time set newname was taking almost 20 mins on each file .Looking at the primary database I found that it was doing a full table scan on X$KCCLH

SELECT RECID , STAMP , THREAD# , SEQUENCE# , FIRST_CHANGE# LOW_SCN , FIRST_TIME LOW_TIME , NEXT_CHANGE# NEXT_SCN , RESETLOGS_CHANGE# , RESETLOGS_TIME
FROM V$LOG_HISTORY
WHERE RECID BETWEEN :1 AND :1 AND RESETLOGS_TIME IS NOT NULL AND STAMP >= :1 ORDER BY RECID 

At the end of the script it had thrown the error again.This time something different.


RMAN-03002: failure of recover command at XX/XX/20XX 01:07:22
ORA-19870: error reading backup piece 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
ORA-19563: datafile header validation failed for file +XXXXXXXXXXXXXXXXXXXXXXXXXX


There are 2 errors .First one is saying the "error  reading from the backuppiece"and the second one saying "File header mismatch " .We verified that the backuppiece was valid by RMAN validate command but the second issue was still a mystery.

In my opinion instead of  "Set newname for  datafile n to [newname]" I should have used " Set newname for  clone datafile n to [newname]".

I didn't get the opportunity to try that option as it was kind of urgent so  we manually restored and applied the archivelogs  using following and the database was ready.

RMAN>recover database until logseq X;
Alter database open resetlogs;

But it doesn't feel good if you spent so much time in  doing the right thing and  something so simple is the workaround.This issue will remain a mystery for me as long as I am not able to reproduce this issue in test .
If anyone reading this gets the opportunity to try the "clone" clause let me know if that worked for you .

Corruption in the data file blocks

Friends I have seen this issue today.It was related to corrupt blocks reported in the alert log file.It was like following entry in the alert log.

Reread of blocknum=2233910, file=+DG1/main/datafile/db01.76820. found same corrupt data

After initial investigation we found that there is an entry in the V$database block corruption

SQL> select * from gv$database_block_corruption;

   INST_ID      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

         1         36    2233910          1                  0 FRACTURED

         2         36    2233910          1                  0 FRACTURED

        

we decided to run the dbverify utility .

dbv userid=sys/password file=+DG1/main/datafile/db01.76820

it showed no block corrupted but still we had entry in  the v$database_block_corruption.

We then followed the support doc 336133.1.Following query showed that the object is an index but its size is more than 20GB .If we have to rebuild it then it will take lot of time.

 select segment_name, segment_type, owner
       from dba_extents
      where file_id =
        and  between block_id
            and block_id + blocks -1;

To ascertain that this is indeed a corruption we ran rman validate on the datafile.This populated the v$database_block_corruption again and guess what!!
it didn't show any corruption as I was already doubting about it.

Restore Database with Current Redo log corruption

Hi Friends.I am writing here because I had seen this scenario where current redo log has got corrupted .We all know that point in time recovery is the solution in this case but following points are to be noted

• Database size is almost 25 TB. 
• When alter system checkpoint is not working.
• We are ready to loose some of the data but not willing to go for the outage that big for point in time recovery.

I heard that somebody did something with the control file and without actual restore he was able to go past the time in current log corruption.I did some testing on my test database .It might not be 100% correct so your suggestions are invited but it worked in my case.To simulate the case we will perform corruption in the current redo log.

SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS

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

         1         24          1 YES INACTIVE

         2         23          1 YES INACTIVE

         3         25          1 NO  CURRENT

So 3 is the current redolog.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE

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

MEMBER

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

IS_

---

         3         ONLINE

/u01/app/oracle/oradata/orcl/redo03.log

NO

         2         ONLINE

/u01/app/oracle/oradata/orcl/redo02.log

NO

Here we want to corrupt the file /u01/app/oracle/oradata/orcl/redo03.log.Lets do the following.

[root@localhost ~]# dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo03.log count=1 seek=1 bs=8192

0+0 records in

0+0 records out

0 bytes (0 B) copied, 4.9343e-05 seconds, 0.0 kB/s

shutdown and startup the database

SQL> startup

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size                  1339824 bytes

Variable Size             545263184 bytes

Database Buffers          297795584 bytes

Redo Buffers                5132288 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 3523

Session ID: 1 Serial number: 5

select GROUP#,substr(member,1,60) from v$logfile;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#

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

         1          1         27          1 NO  CURRENT                1128975

         3          1         25          1 NO  INACTIVE               1127602

         2          1         26          1 NO  INACTIVE               1128972

So group 3 is not archived as seen from above

Now we want to create as control file manually

SQL> alter database backup controlfile to trace;

Database altered.

It looks like following

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/orcl/system01.dbf',

  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

  '/u01/app/oracle/oradata/orcl/users01.dbf',

  '/u01/app/oracle/oradata/orcl/example01.dbf'

CHARACTER SET WE8MSWIN1252

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_17/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_17/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

-- or if the last shutdown was not normal or immediate.

#RECOVER DATABASE

-- All logs need archiving and a log switch is needed.

#ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

#ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

Move both the control files to backup

[oracle@localhost orcl]$ mv control02.ctl control02.ctl_bkp

startup nomount the instance

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 17 03:30:37 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @control.sql

Control file created.

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

ALTER SYSTEM ARCHIVE LOG ALL

*

ERROR at line 1:

ORA-01649: operation not allowed with a backup control file

ALTER DATABASE OPEN

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

so the database has created the control files at the 2 locations described in the spfile.After opening the database with the resetlogs we have the database open for read and write.The questions is thet I have not performed point in time recovery and I am ready to loose the data in the redo log files.That will be a compromise but think about the time in restoring the 25TB database.How much outage it will cause.I still don't know is it is the right way to do it or not but still there is a way.If you know a better way or if this is not correct please let me know.