May 11 2012

ORA-10567: Redo is inconsistent with data block

Recovery interrupted!
Recovered data files to a consistent state at change 13507372
Fri May 11 20:11:09  2012
Errors in file e:\oracle\product\10.2.0\admin\ecc\bdump\eccstandby_mrp0_2724.trc:
ORA-12801: error signaled in parallel query server P011
ORA-00600: internal error code, arguments: [3020], [1], [56204], [4250508], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 56204)
ORA-10564:

I had an interesting scenario lately. In a dataguard configuration my primary database had block corruption. But instead of

failing over I decided to repair the database by doing point-in-time recovery by rolling back 5 minutes before the bloxk

corruption.

I used RMAN command like below on Primary

run {
set until time to_date(’12-May-2012 02:15:00′, ‘DD-MON-YYYY HH24:MI:SS’);
restore database;
recover database;
}

Now this recovered the primary and I was able to connect the Application. Now i wanted to check what was happeining on the

standby. The alert log on standby gave below error

ORA-10567: Redo is inconsistent with data block (file# 1, block# 56204)

I immediately knew my PITR on primary was the reason for this inconistency. This is called a ‘STUCK RECOVERY’.There is an

inconsistency between the information stored in the redo and the information stored in a database block being recovered.

The best way to overcome is to restore from a backup and doing a point-in-time recovery to a time PRIOR to the one implied

by the ORA-600[3020] error.

So all I had to do is rewind the standby database to one minute before the Primary restore time and let dataguard do its

magic.

run {
set until time to_date(’12-May-2012 02:14:00′, ‘DD-MON-YYYY HH24:MI:SS’);
restore database;
recover database;
}

Once I did this I checked the v$managed_standby view for MRP0 process and it had started applying the logs by then.

Dataguard brought the standby in sync with primary. Magical !

By doing it this way; I recovered my primary database quickly and avoided failover to standby site and rebuilding the

standby database from scratch. Please note this procedure was done on Oracle 10.2.0.5 database running on Windows Server

2003. Also I had a full backup from 2 days before both on Primary and Standby site. Also all archivelogs after that day

existed on disks of both Primary and Standby.

May 11 2012

Guaranteed Restore Points Oracle 11g

Guaranteed Restore Points are a life-saver when it comes to Application changes.It can ensure that you can rewind the database to a time without tradional point in time recovery. Guaranteed restore points are basically alias’es for SCN’s

A normal restore point assigns a restore point name to an SCN or specific point in time.The control file can maintain a record of thousands of normal restore points with no significant effect on database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

SQL> CREATE RESTORE POINT before_upgrade;

Creating a normal restore point eliminates manually recording an SCN in advance or determine the correct SCN after the fact by using features such as Flashback Query.

Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations. A principal difference is that guaranteed restore points never age out of the control file and must be explicitly dropped. In general, you can use a guaranteed restore point as an alias for an SCN with any command that works with a normal restore point. Except as noted, the information about where and how to use normal restore points applies to guaranteed restore points as well.

A guaranteed restore point ensures that you can use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled. If flashback logging is enabled, then a guaranteed restore point enforces the retention of flashback logs required for Flashback Database to any SCN after the earliest guaranteed restore point.

This example shows how to create a guaranteed restore point:

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Listing Restore Points
______________________

You can use the LIST command to list either a specific restore point or all restore points known to the RMAN repository. The variations of the command are as follows:

LIST RESTORE POINT restore_point_name;
LIST RESTORE POINT ALL;

RMAN indicates the SCN and time of the restore point, the type of restore point, and the name of the restore point. The following example shows sample output:

RMAN> LIST RESTORE POINT ALL;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
—————- ——— ———- ——— —-
341859           28-JUL-06            28-JUL-06 NORMAL_RS
343690           28-JUL-06 GUARANTEED 28-JUL-06 GUARANTEED_RS

To see a list of all currently defined restore points (normal and guaranteed), use the V$RESTORE_POINT control file view, by means of the following query:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT

Dropping Restore Points
_______________________

When you are satisfied that you do not need an existing restore point, or when you want to create a new restore point with the name of an existing restore point, you can drop the restore point, using the DROP RESTORE POINT SQL*Plus statement. For example:

SQL> DROP RESTORE POINT before_app_upgrade;
Restore point dropped.

The same statement is used to drop both normal and guaranteed restore points.

Note:
Normal restore points eventually age out of the control file, even if not explicitly dropped.
Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

Flashing back database till Restore Point
_________________________________________

Login to RMAN and bring the database to mount mode.

FLASHBACK DATABASE TO RESTORE POINT ‘before_upgrade’;

FLASHBACK DATABASE TO SCN 202381;

Open the database.

I normally create guaranteed retore points before doing a Dataguard switchover. It helps you assure you can go back to the state before you started (in case anything goes wrong).

May 11 2012

RMAN database restore until time

I had a block corruption of my primary database, though we had a standby build we wanted to recover the Primary instead of failover to the Standby.
This recovery is possible when you have say a full backup made of the database till 9th May and all archivelogs from 9th May till 12th MAY are present.

Put the database in mount mode and restore it till the time before the corruption happened.

run {
set until time to_date(’12-May-2012 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’);
restore database;
recover database;
}

 

Apr 30 2012

Flashback Table to Timestamp 11gR2

Flashback Technology provided by Oracle is one of the most useful in a production environment. It is a life saver. You can even rollback a query to a point in time without requiring traditional database restore. I will demonstrate below how to do flashback a table to a point in time. But first you have to enable flashback.

From 11gR2 onwards you don’t have to shutdown the database to enable Flashback. You can do it online. To enable flashback in 11gR2 do the below steps.

1.  alter system set db_recovery_file_dest=’+FRA’ scope=both sid=’*';

2. alter system set db_recovery_file_dest_size=’400G’ scope=both sid=’*';

3.  select flashback_on from v$database;

FLASHBACK_ON
——————
NO

4. alter database flashback on;

select flashback_on from v$database;

FLASHBACK_ON
——————
YES

_______________________________________________________

– For a table to be flash-backed you need to have row movement enabled on it. We have a table here called Employees in Schema HR

ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;

 

– Check current SCN and Database Time

SELECT current_scn, TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) FROM v$database;

4784136875    2012-04-30 8:30:00

 

– Check the current total no. of records in the table

SELECT COUNT(*) FROM HR.EMPLOYEES

Count(*)

126

 

– Check the current total no. of records in the table as it existed half an hour back at 8:00 AM

SELECT COUNT(*) FROM HR.EMPLOYEES AS OF TIMESTAMP TO_TIMESTAMP(’2012-04-30 8:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)

Count(*)

127

This means there is one record was deleted. We need to flashback the table as it existsed at 8:00AM

 

FLASHBACK TABLE HR.EMPLOYEES TO TIMESTAMP TO_TIMESTAMP(’2012-04-30 8:00:00′, ‘YYYY-MM-DD HH24:MI:SS’);

 

SELECT COUNT(*) FROM HR.EMPLOYEES

Count(*)

127

 

Now we can see that the record are displaying as 127. Which means table is back to the state before the record was deleted.

Apr 20 2012

ORA-00980: synonym translation is no longer valid

This error occurs when you have a public synonym defined and the object referencing or owning the synonym has been dropped or deleted. You can check for the invalid synonyms using below query.

select * from dba_synonyms s

where table_owner not in(‘SYSTEM’,'SYS’)

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);

 

Solution:

Replace the synonym with the name of the object it references or re- create the synonym so that it refers to a valid table, view, or synonym.

The Invalid synonyms must be dropped and recreated with the right owner. I normally do this using below query.(In this example the owner of the synonym was dropped and I am altering the synonym to reference the new owner)

DROP PUBLIC SYNONYM SYNONYM_NAME;
CREATE PUBLIC SYNONYM SYNONYM_NAME FOR NEW_OWNER.OBJECT_NAME;

Apr 10 2012

ORA-38753: Cannot flashback data file ; no flashback log data. ORA-01110: data file

CAUTION !!!! DO NOT DO THIS ON YOUR PRIMARY. IF YOU DO THEN YOU WOULD REQUIRE MEDIA RECOVERY ON THE DATAFILE YOU BROUGHT OFFLINE !!!

 

If your getting this error when you converting a Snapshot Standby Database (11gR2) back to Physical Standby Database.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

ERROR at line 1: ORA-38753: Cannot flashback data file 4; no flashback log data. ORA-01110: data file 4: ‘+DATA/swxstandby/datafile/swx.271.780160475′

When you get this error do as below :

SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# ;

TS# TS_NAME                        FLA ———- ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— ———- —————————— —          1 +DATA/swxstandby/datafile/system.270.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             0 SYSTEM                         YES          2 +DATA/swxstandby/datafile/sysaux.269.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             1 SYSAUX                         YES          3 +DATA/swxstandby/datafile/undotbs1.268.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           2 UNDOTBS1                       YES          5 +DATA/swxstandby/datafile/undotbs2.267.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           5 UNDOTBS2                       YES          4 +DATA/swxstandby/datafile/swx.271.780160475                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                7 SWX                            NO          6 +DATA/swxstandby/datafile/osms.266.780160477                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               9 OSMS                           YES

6 rows selected.

See which datafile has flashback_on colmn set as NO

SQL> alter database datafile 4 offline;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— RESTORE POINT ONLY

SQL> select name, flashback_on from v$tablespace ;

NAME                           FLA —————————— — SYSTEM                         YES SYSAUX                         YES UNDOTBS1                       YES SWX                            NO TEMP                           YES UNDOTBS2                       YES OSMS                           YES

SQL> alter tablespace SWX flashback on;

Tablespace altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes

Variable Size            1979719264 bytes

Database Buffers         4429185024 bytes

Redo Buffers                4521984 bytes

Database mounted.

SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount ORACLE instance started.

Total System Global Area 6415597568 bytes

Fixed Size                  2171296 bytes

Variable Size            1979719264 bytes

Database Buffers         4429185024 bytes

Redo Buffers                4521984 bytes Database mounted.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select flashback_on from v$database ;

FLASHBACK_ON —————— NO

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE            SWITCHOVER_STATUS    DATABASE_ROLE ——————– ——————– —————- MOUNTED              RECOVERY NEEDED      PHYSICAL STANDBY

Mar 30 2012

Check Dataguard Sync Status Oracle 10g 11g

On Primary:-

select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log
where first_time between (sysdate-1) and (sysdate+1)
group by thread# order by 1;

On Standby:-

select thread#, max(sequence#) “Last Standby Seq Received”
from v$archived_log
group by thread# order by 1;

select thread#, max(sequence#) “Last Standby Seq Applied”
from v$archived_log where applied=’YES’
group by thread# order by 1;

SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ASC;

select process, thread#, sequence#, status from v$managed_standby where process=’MRP0′;

#Status should be ‘APPLYING_LOG’

Mar 29 2012

Active Session History 11g

Even when sessions are inactive they can be executing SQL’s in the background. You have to identify the SQL’s which are executing.

Run this query as sysdba user. If you are using RAC then replace v$active_session_history with gv$active_session_history

select
ash.SQL_ID ,
sum(decode(ash.session_state,’ON CPU’,1,0)) “CPU”,
sum(decode(ash.session_state,’WAITING’,1,0)) -
sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “WAIT” ,
sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “IO” ,
sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL”
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id ;

You will see something like

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
8tfvwyvfm5cjn          0          1          0          1
1xpfbutrrzkph          0          1          0          1
aykvshm7zsabd          0          1          0          1
92f47aa2q2rmd          0          1          0          1
92b382ka0qgdt          0          4          0          4
1h50ks4ncswfn          0          3          1          4
f99a23s9aba0z          0          0          1          1
f6cz4n8y72xdc          0          0          1          1
cvn54b7yz0s8u          0          0          1          1
3s58mgk0uy2ws          0          1          0          1
fnk7155mk2jq6          0          1          0          1

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
c2p32r5mzv8hb          0          1          0          1
5p6a1yss527ap          0          1          0          1
6gvch1xu9ca3g          0          1          0          1

Now see which statement shows maximum value in Total column. Take the SQL ID  and run below query

SELECT sql_text FROM v$sqlarea WHERE sql_id = ’1h50ks4ncswfn’;

This will give you an SQL statement. See which table is it running on and what is it doing. Maybe it is a poorly written SQL which is locking the table or looping on the table. Do an explain plan for this statement.

SELECT * FROM table(dbms_xplan.display_awr(’1h50ks4ncswfn’));

This way you can identify the top consuming SQL’s running inside your Oracle database. This procedure was run on a 11gR2 database but is also valid for 10gR2.