Oracle DBA Tips and Techniques

By Arjun Raja

Archive for the ‘High Availability’ Category

ORACLE RESTART ON STANDALONE SERVER

Posted by Arjun Raja on October 4, 2013

Oracle restart feature is available for databases of 11.2 and greater.

This feature can be installed without the need to configure ASM as a pre-req.

To enable this on a server with just a simple 11.2 oracle database running these are the steps.

1. Download GRID INFRASTRUCTURE software from J:\DtaDbase\Software\Oracle 11g\Oracle 11g Rel 2 AIX\AIX_11.2.0.3_Database_and_gridinfrastructure

Unzip the files in a staging area on server:

Ex:

cd /u02/oradata/stage/AIX_11.2.0.3_Database_and_gridinfrastructure/grid directory once all files are unzipped.

export DISPLAY

./runInstaller

Follow prompts.

2 important choices

1. Choose install Oracle Grid Infrastucture Software only

2. Make sure grid infrastructure home is installed in a separate home to the existing database home :

Ex – new GI home is – /u01/oracle/product/11.2.0.3/gridinfra

Once install is complete :

As root user run

1. root.sh

2. /u01/oracle/product/11.2.0.3/gridinfra/perl/bin/perl -I/u01/oracle/product/11.2.0.3/gridinfra/perl/lib -I/u01/oracle/product/11.2.0.3/gridinfra/crs/install

/u01/oracle/product/11.2.0.3/gridinfra/crs/install/roothas.pl

The second command above is the command that configures Oracle Grid Infrastructure for a Standalone Server.

This automatically ensures availability of oracle restart.

To check

export ORACLE_HOME to point to new GI home

export PATH as necessary too.

ORACLE_HOME=/u01/oracle/product/11.2.0.3/gridinfra; export ORACLE_HOME

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/u01/oracle/bin:/usr/bin/X11:/sbin:/usr/java6:/usr/java6/bin:usr/java6/jre/bin:/u01/oracle:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java5/jre/bin:/usr/java5/bin:/u01/oracle/product/11.2.0.3/gridinfra/bin; export PATH

cd $ORACLE_HOME/bin

Check CRS

cpstest:/u01/oracle/product/11.2.0.3/gridinfra/bin> crsctl check has

CRS-4638: Oracle High Availability Services is online

cpstest:/u01/oracle/product/11.2.0.3/gridinfra/bin>

 

Check resources managed by oracle HAS.

cpstest:/u01/oracle/product/11.2.0.3/gridinfra/bin> crsctl status resource -t

——————————————————————————–

NAME TARGET STATE SERVER STATE_DETAILS

——————————————————————————–

Local Resources

——————————————————————————–

ora.ons

OFFLINE OFFLINE p520test1

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.cssd

1 ONLINE ONLINE p520test1

ora.diskmon

1 OFFLINE OFFLINE

ora.evmd

1 ONLINE ONLINE p520test1

cpstest:/u01/oracle/product/11.2.0.3/gridinfra/bin>

No database yet being monitored.

Add

database to list of processes controlled by Oracle Restart

Run this command from ORACLE_HOME – not GRID INFRASTRUCTURE HOME

cpstest:/u01/oracle> . oraenv

ORACLE_SID = [cpstest] ? test11g

test11g:/u01/oracle> which srvctl

/u01/oracle/product/11.2.0.3/db_1/bin/srvctl – Points to database home.

Add existing database to list monitored by Oracle Restart

test11g:/u01/oracle> srvctl add database -d test11g -o /u01/oracle/product/11.2.0.3/db_1

Check if the addition was successful :

test11g:/u01/oracle> srvctl config database -d test11g

Database unique name: test11g

Database name:

Oracle home: /u01/oracle/product/11.2.0.3/db_1

Oracle user: oracle

Spfile:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Database instance: test11g

Disk Groups:

Services:

test11g:/u01/oracle>

Now test if ORACLE RESTART is working –

srvctl status database -d test11g

Database is running.

test11g:/u01/oracle>

Kill pmon process for test11g database.

test11g:/u01/oracle> ps -ef |grep pmon

oracle 20971724 1 0 13:12:29 – 0:00 ora_pmon_test11g – note time of start – 13:12

test11g:/u01/oracle> kill -9 20971724

After five seconds –

test11g:/u01/oracle> ps -ef|grep pmon

oracle 21627036 1 0 13:16:27 – 0:00 ora_pmon_test11g

Note time of start – 13:16 – So Oracle restart works !

From now on – if a new database is created, it is automatically configured with Oracle restart :

I create a new database called testhas.

test11g:/u01/oracle/product/11.2.0.3/db_1/bin> ps -ef |grep pmon

oracle 21627036 1 0 13:16:27 – 0:00 ora_pmon_test11g

oracle 26738744 1 0 13:55:24 – 0:00 ora_pmon_testhas

test11g:/u01/oracle/product/11.2.0.3/db_1/bin>

Check configuration for Oracle restart :

test11g:/u01/oracle/product/11.2.0.3/db_1/bin> srvctl status database -d testhas

Database is running.

test11g:/u01/oracle/product/11.2.0.3/db_1/bin> srvctl config database -d testhas

Database unique name: testhas

Database name: testhas

Oracle home: /u01/oracle/product/11.2.0.3/db_1

Oracle user: oracle

Spfile:

Domain: xxxxxxx.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Database instance: testhas

Disk Groups:

Services:

Test Oracle restart works for new database :

ps -ef |grep pmon

oracle 26738744 1 0 13:55:24 – 0:00 ora_pmon_testhas

kill -9 26738744

test11g:/u01/oracle/product/11.2.0.3/db_1/bin> kill -9 26738744

No testhas pmon if I check within one second….

test11g:/u01/oracle/product/11.2.0.3/db_1/bin> ps -ef |grep pmon

oracle 21627036 1 0 13:16:27 – 0:00 ora_pmon_test11g

But after 5 seconds I check again :

test11g:/u01/oracle/product/11.2.0.3/db_1/bin> ps -ef |grep pmon

oracle 21627036 1 0 13:16:27 – 0:00 ora_pmon_test11g

oracle 26542206 1 2 14:00:49 – 0:00 ora_pmon_testhas

Check resources monitored by Oracle restart:

test11g:/u01/oracle/product/11.2.0.3/db_1/bin> crsctl status resource -t

——————————————————————————–

NAME TARGET STATE SERVER STATE_DETAILS

——————————————————————————–

Local Resources

——————————————————————————–

ora.ons

OFFLINE OFFLINE p520test1

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.cssd

1 ONLINE ONLINE p520test1

ora.diskmon

1 OFFLINE OFFLINE

ora.evmd

1 ONLINE ONLINE p520test1

ora.test11g.db

1 ONLINE ONLINE p520test1 Open

ora.testhas.db

1 ONLINE ONLINE p520test1 Open

test11g:/u01/oracle/product/11.2.0.3/db_1/bin>

Posted in High Availability | Tagged: | Leave a Comment »

CREATION OF STANDBY DATABASE 11g Rel 2 ON SAME HOST.

Posted by Arjun Raja on August 3, 2010

Create a standby database on the same host, using RMAN with a backup from an active database .

PRIMARY DATABASE – ORCL ON TEST010 BOX

STANDBY DATABASE – STBY ON TEST010 BOX.

In PRIMARY – orcl database >

export ORACLE_SID=orcl

export ORACLE_HOME=/u01/oracle/product/11.2

sql > Alter database force logging;

Create password file for the primary and standby database.

cd $ORACLE_HOME/dbs

orapwd file=orapworcl entries=10 password=temp force=y

cp orapworcl orapwstby – Do not create separate password file for standby – use cp .

Try a test connection –

$ sqlplus sys/temp@orcl as sysdba – should connect to primary.

ON PRIMARY – ORCL database.

alter system set LOG_ARCHIVE_DEST_1=’location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL’;

alter system set LOG_ARCHIVE_DEST_2=’SERVICE=STBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY’;

alter system set FAL_SERVER=STBY;

alter system set FAL_CLIENT=ORCL;

alter system set DB_UNIQUE_NAME=’ORCL’ scope=spfile;

alter system set log_archive_config=’dg_config=(orcl,stby)’;

STANDBY DATABASE ON SAME BOX.

Copy initorcl.ora and edit to reflect all stby entries.

vi initstby.ora

stby.__db_cache_size=155189248
stby.__java_pool_size=4194304
stby.__large_pool_size=4194304
stby.__oracle_base=’/u01/ofsad2’#ORACLE_BASE set from environment
stby.__pga_aggregate_target=159383552
stby.__sga_target=423624704
stby.__shared_io_pool_size=0
stby.__shared_pool_size=243269632
stby.__streams_pool_size=4194304
*.audit_file_dest=’/u01/ofsad2/admin/stby/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u02/oradata/stby/control01.ctl’,’/u02/oradata/stby/control02.ctl’
*.db_block_size=8192
*.db_file_name_convert=’/u02/oradata/orcl/’,’/u02/oradata/stby/’
*.db_name=’orcl’
*.db_recovery_file_dest=’/u02/oradata/flash_recovery_area’
*.db_recovery_file_dest_size=7340032000
*.db_unique_name=’stby’
*.diagnostic_dest=’/u01/ofsad2′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)’
*.fal_client=’STBY’
*.fal_server=’ORCL’
*.log_archive_config=’dg_config=(orcl,stby)’
*.LOG_ARCHIVE_DEST_1=’location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY’
*.log_archive_dest_2=’SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl’
*.log_archive_dest_state_2=’ENABLE’
*.log_file_name_convert=’/u02/oradata/orcl/’,’/u02/oradata/stby/’
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=423624704
*.standby_file_management=’auto’
*.undo_tablespace=’UNDOTBS1′

. setstby

sql > create spfile from pfile;

Configure Listener for Static service registration

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = apex4)
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = apex4)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = orcl)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = stby)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
)

Configure service name in tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

STARTUP NOMOUNT STANDBY DATABASE –

sql > startup nomount;

Now run rman command to create standby database –

stby:/u01/oracle/product/11.2/dbs> rman target sys/temp@orcl auxiliary /

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Jul 27 11:55:10 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1246075258)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover from active database ;

Starting Duplicate Db at 27/JUL/10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/27/2010 12:00:53
RMAN-06217: not connected to auxiliary database with a net service name

Therefore connect to auxiliary using connect string –

rman target sys/temp@orcl auxiliary sys/temp@stby

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Jul 27 12:02:22 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1246075258)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover from active database ;

Starting Duplicate Db at 27/JUL/10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/oracle/product/11.2/dbs/orapworcl’ auxiliary format
‘/u01/oracle/product/11.2/dbs/orapwstby’ ;
}
executing Memory Script

Starting backup at 27/JUL/10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
Finished backup at 27/JUL/10

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u02/oradata/stby/control01.ctl’;
restore clone controlfile to ‘/u02/oradata/stby/control02.ctl’ from
‘/u02/oradata/stby/control01.ctl’;
}
executing Memory Script

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/oracle/product/11.2/dbs/snapcf_orcl.f tag=TAG20100727T121951 RECID=4 STAMP=725458793
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27/JUL/10

Starting restore at 27/JUL/10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 27/JUL/10

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
“/u02/oradata/stby/temp01.dbf”;
switch clone tempfile all;
“/u02/oradata/stby/fla_tbs1.dbf” ;
and more …
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/stby/temp01.dbf in control file

executing command: SET NEWNAME

And more…

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/orcl/sysaux01.dbf
And more….
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27/JUL/10

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like “/u02/oradata/flash_recovery_area/ORCL/archivelog/2010_07_27/o1_mf_1_221_64wqpxq8_.arc” auxiliary format
“/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_%u_.arc” ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=221 RECID=180 STAMP=725458909
output file name=/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 27/JUL/10

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc

datafile 1 switched to datafile copy
And more…
input datafile copy RECID=10 STAMP=725458912 file name=/u02/oradata/stby/fla_tbs1.dbf

contents of Memory Script:
{
set until scn 5284605;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27/JUL/10
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 221 is already on disk as file /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc
archived log file name=/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc thread=1 sequence=221
media recovery complete, elapsed time: 00:00:01
Finished recover at 27/JUL/10
Finished Duplicate Db at 27/JUL/10

RMAN>

CREATE STANDBY LOGS –

Run on both instances –

ON PRIMARY

alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo03.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo02.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo01.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo04.rdo’ size 52428800;

ON STANDBY

alter database add standby logfile ‘/u02/oradata/stby/stdby_redo01.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo02.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo03.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo04.rdo’ size 52428800;

On STANDBY – START MEDIA RECOVERY.

sql > Recover managed standby database disconnect;

Tail –f alert logs in standy site to check if all is well…

Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_269_65dll3bx_.arc
Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_270_65dll45g_.arc
Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_271_65dll3z5_.arc
Media Recovery Waiting for thread 1 sequence 272 (in transit)

Also confirm with this sql that all scripts are in place…

ON PRIMARY –

select thread#, sequence#, archived, applied from v$archived_log where dest_id=2 and sequence# in ( select max(sequence#) from v$archived_log);

THREAD# SEQUENCE# ARC APPLIED
———- ———- — ———
1 274 YES YES
SQL>

select
LOG_ARCHIVED_ON_PRIMARY, LOG_APPLIED_ON_STANDBY, LOG_ARCHIVED_ON_PRIMARY-LOG_APPLIED_ON_STANDBY LOG_GAP
from
(select sequence# LOG_ARCHIVED_ON_PRIMARY from v$archived_log where dest_id=1 and archived=’YES’ and completion_time=(select max(completion_time) from v$archived_log where dest_id=1)) primary,
(select sequence# LOG_APPLIED_ON_STANDBY from v$archived_log where dest_id=2 and applied=’YES’ and completion_time=(select max(completion_time) from v$archived_log where dest_id=2))
/

LOG_ARCHIVED_ON_PRIMARY LOG_APPLIED_ON_STANDBY LOG_GAP

274 274 0

Posted in High Availability, Oracle 11g | Tagged: , | Leave a Comment »

AUTOMATIC BLOCK REPAIR IN 11G RELEASE 2

Posted by Arjun Raja on December 29, 2009

SET UP ACTIVE DATAGUARD AND TEST AUTOMATIC BLOCK CORRUPTION REPAIR.

Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination.

In addition, RECOVER BLOCK is enhanced to restore blocks from a physical standby database. The physical standby database must be in real-time query mode – Also called active dataguard.

1 – SETUP ACTIVE DATAGUARD –

Firstly ON STANDBY DATABASE –

alter database recover managed standby database cancel;

alter database open read only;

alter database recover managed standby database using current logfile disconnect;

Confirm that standby database is open –

SQL> select file_name from dba_data_files;

FILE_NAME

FILE_NAME
——————————————————————————–
/u02/oradata/bozo/users01.dbf
/u02/oradata/bozo/undotbs01.dbf
/u02/oradata/bozo/sysaux01.dbf
/u02/oradata/bozo/system01.dbf

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ ONLY WITH APPLY

SQL>

Try to create a table in the standby database –

SQL> create table test (col_1 number(10));
create table test (col_1 number(10))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Check for table test on STANDBY –

SQL> desc test ;
ERROR:
ORA-04043: object test does not exist

Check for table test on PRIMARY

SQL> desc test;
ERROR:
ORA-04043: object test does not exist

Create table test on PRIMARY –

SQL> create table test (col_1 number(10));

Table created.

SQL>

Now desc test on both primary and standby –

Primary and standby –

Desc test;

SQL> Desc test;
Name Null? Type
—————————————– ——– —————————-
COL_1 NUMBER(10)

SQL>

So while the standby database is open in READ ONLY mode, media recovery is still in progress.

2 – BLOCK CORRUPTION – AUTOMATIC MEDIA RECOVERY USING BLOCK FROM STANDBY SITE.

Simulate a block corruption –

Firstly check the file users01.dbf for any corruption –

cd /u02/oradata/arjun

[oracle@redhat346 arjun]$ dbv file=users01.dbf

DBVERIFY: Release 11.2.0.1.0 – Production on Wed Dec 23 11:03:47 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – Verification starting : FILE = /u02/oradata/arjun/users01.dbf

DBVERIFY – Verification complete

Total Pages Examined : 1280
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1153
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1303084 (0.1303084)
[oracle@redhat346 arjun]$

Run following command to corrupt datafile – /u02/oradata/arjun/users01.dbf

vi corrupt.sh

dd if=/dev/zero of=/u02/oradata/arjun/users01.dbf bs=8192 seek=139 count=2 conv=notrunc < backup validate tablespace users format ‘/u02/oradat/arjun/%U%s%d%t’;

Starting backup at 24-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u02/oradata/arjun/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 FAILED 0 1134 1280 1374339
File Name: /u02/oradata/arjun/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 11
Index 0 0
Other 2 135

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc for details
Finished backup at 24-DEC-09

Backup validate proves block corruption.

Also can be confirmed via this view –

SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 139 2 0 ALL ZERO

SQL>

Now since this database is a primary database in a dataguard configuration with active dataguard in place, AUTO BMR( Block Media Recovery) will kick in on the primary database box and fetch a non-corrupt block from the standby database and restore and recover this block.

Check of alert log on PRIMARY database –

Thu Dec 24 10:30:52 2009
Hex dump of (file 5, block 139) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc
Corrupt block relative dba: 0x0140008b (file 5, block 139)
Completely zero block found during validation
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Hex dump of (file 5, block 140) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc
Corrupt block relative dba: 0x0140008c (file 5, block 140)
Completely zero block found during validation
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data

Hex dump of (file 5, block 139) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_19540.trc
Corrupt block relative dba: 0x0140008b (file 5, block 139)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u02/oradata/arjun/arjun01.dbf’ for corruption at rdba: 0x0140008b (file 5, block 139)
Reread (file 5, block 139) found same corrupt data
Starting background process ABMR
Thu Dec 24 11:17:55 2009
ABMR started with pid=36, OS id=19550
Auto BMR service is active.
Requesting Auto BMR for (file# 5, block# 139)
Hex dump of (file 5, block 140) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_19540.trc
Corrupt block relative dba: 0x0140008c (file 5, block 140)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u02/oradata/arjun/arjun01.dbf’ for corruption at rdba: 0x0140008c (file 5, block 140)
Reread (file 5, block 140) found same corrupt data
Requesting Auto BMR for (file# 5, block# 140)
Waiting Auto BMR response for (file# 5, block# 139)
Auto BMR successful
Waiting Auto BMR response for (file# 5, block# 140)
Auto BMR response already received
Auto BMR successful

Now check for corrupt blocks –

cd /u02/oradata/arjun

dbv file=users01.dbf

INCASE THE DATAFILE HEADER ITSELF IS CORRUPT, THEN AUTOMATIC BLOCK MEDIA RECOVERY IS NOT POSSIBLE.

Starting background process ABMR
Wed Dec 23 11:17:08 2009
ABMR started with pid=31, OS id=5840
Auto BMR service is active.
Requesting Auto BMR for (file# 5, block# 3)
Waiting Auto BMR response for (file# 5, block# 3)
Wed Dec 23 11:17:09 2009
Read of datafile ‘/u02/oradata/arjun/arjun01.dbf’ (fno 5) header failed with ORA-01210
Hex dump of (file 5, block 1) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc
Corrupt block relative dba: 0x01400001 (file 5, block 1)
Completely zero block found during datafile header read
Rereading datafile 5 header failed with ORA-01210
Hex dump of (file 5, block 1) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc
Corrupt block relative dba: 0x01400001 (file 5, block 1)
Completely zero block found during datafile header read
Errors in file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: ‘/u02/oradata/arjun/users01.dbf’
ORA-01210: data file header is media corrupt
Auto BMR failed for (file# 5, block# 3); error=Invalid file header
Wed Dec 23 11:17:10 2009
Corrupt Block Found
TSN = 6, TSNAME = ARJUN
RFN = 5, BLK = 3, RDBA = 20971523
OBJN = 4, OBJD = -1, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
Wed Dec 23 11:17:10 2009
Checker run found 1 new persistent data failures

In this case – restoration of the datafile from a backup is required –

OFCOURSE DATABASE BLOCK CORRUPTIONS CAN BE RECOVERED USING RMAN – again RMAN gets the good blocks from the standby site incase active dataguard is running.

SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 139 2 0 ALL ZERO

SQL>

[oracle@redhat346 trace]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Dec 24 11:11:08 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ARJUN (DBID=3469830310)

RMAN> RECOVER CORRUPTION LIST;

RMAN> recover corruption list;

Starting recover at 24-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
finished standby search, restored 2 blocks

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 24-DEC-09

RMAN>

SQL > SQL> select * from v$database_block_corruption;

no rows selected

SQL> SQL>

Posted in High Availability, Oracle 11g | Leave a Comment »

RMAN WILL NOT DELETE ARCHIVELOG NEEDED FOR RECOVERY ON STANDBY DATABASE

Posted by Arjun Raja on December 1, 2009

RMAN command issued to backup and delete all archivelogs on PRIMARY database.

RMAN > BACKUP ARCHIVELOG ALL DELETE INPUT;

archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_22/o1_mf_1_5182_5fzsdwgv_.arc recid=10340 stamp=700923294
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_23/o1_mf_1_5183_5g3b7jt1_.arc recid=10342 stamp=701038835
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_25/o1_mf_1_5184_5g6pzq0g_.arc recid=10344 stamp=701150202
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_26/o1_mf_1_5185_5gc5c5kw_.arc recid=10346 stamp=701295975
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_10_27/o1_mf_1_5186_5gdp4vvn_.arc recid=10348 stamp=701345949
RMAN-08137: WARNING: archive log not deleted as it is still needed archive log
filename=/u02/oradata/flash_recovery_area/Test/archivelog/2009_10_27/o1_mf_1_5187_5gdpby1j_.arc thread=1 sequence=5187
channel ORA_SBT_TAPE_1: deleting archive log(s)
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_08_25/o1_mf_1_5128_596rhksp_.arc recid=10232 stamp=695822449
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_08_26/o1_mf_1_5129_59bdjhbv_.arc recid=10234 stamp=695941266
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_08_28/o1_mf_1_5130_59frckcs_.arc recid=10236 stamp=696051699
archive log
filename=/u02/oradata/flash_recovery_area/TEST/archivelog/2009_08_29/o1_mf_1_5131_59jkd348_.arc recid=10238 stamp=696142854

In this case, archivelog sequence 5187 was not yet applied on standby database – hence RMAN will not delete it from the PRIMARY database disk.

This is a safety measure to make sure primary and standby databases can remain in sync

Posted in High Availability | Tagged: , | Leave a Comment »

REMOVE BACKUPS FROM FRA BEFORE RECOVERY

Posted by Arjun Raja on November 30, 2009

Before attempting to restore and recover a database to a point before the current resetlogs, make sure to delete all files in the ‘backup’ area of the FRA – RMAN catalogs files in the ‘backup’ area in the FRA and if it finds a controlfile backup of time after the resetlogs it will use that backup.

Situation –

Database TEST restored and recovered from old backup – point in time recovery and database opened with RESET LOGS .

Backups run on new database and controlfile autobackup to FRA.

Database blown away later that day.

Same night another restore and recovery attempted to same time as previous recovery –

Restore completes but Recovery fails with following error –

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20003: target
database incarnation not found in recovery catalog

A check of the restore and recovery log –

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name:
/u03/oradata/flash_recovery_area/OFSAP/autobackup/2009_09_02/o1_mf_s_696491414_59v5rqrc_.bkp

RMAN catalogs the files in the FRA and if there is a controlfile autobackup
in the FRA it uses that file – and resets the incarnation of the database –
since the file in the FRA belonged to the previous database which ran on
this box after a RESETLOGS.

The alert log shows this when the RESTORE and RECOVER UNTIL TIME command is
issued –

Thu Sep 3 23:05:31 2009
New incarnation branch detected in Backup, filename
/u03/oradata/flash_recovery_area/OFSAP/autobackup/2009_09_02/o1_mf_s_696491414_59v5rqrc_.bkp
Inspection of file changed rdi from 2 to 3
Setting recovery target incarnation to 3
Thu Sep 3 23:05:31 2009
Setting recovery target incarnation to 3
Thu Sep 3 23:38:08 2009
Full restore complete of datafile 5 /u02/oradata/ofsap/ofsa_data03.dbf.
Elapsed time: 0:29:53
checkpoint is 24996620767
Fri Sep 4 00:10:04 2009

The way out is to RESET the incarnation of the database to the previous
incarnation – when the backup was taken and catalog updated .

List incarnation of database –

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Sep 4 05:02:43
2009

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

connected to target database: TEST (DBID=2764499561, not open)

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 TEST 2764499561 PARENT 738795154 30/APR/04
2 2 TEST 2764499561 ORPHAN 23856965510 03/AUG/09
3 3 TEST 2764499561 CURRENT 25008195933 02/SEP/09

RMAN> RESET DATABASE INCARNATION TO 2;

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 TEST 2764499561 PARENT 738795154 30/APR/04
2 2 TEST 2764499561 CURRENT 23856965510 03/AUG/09
3 3 TEST 2764499561 ORPHAN 25008195933 02/SEP/09

Remove all backups from FRA.

Now restart the Restore and recovery – And it will work.

Posted in High Availability | Tagged: , , | Leave a Comment »

Flashback database to scn

Posted by Arjun Raja on September 9, 2009

FLASHBACK DATABASE TO SCN –

Time to FLASHBACK DATABASE TO – 06:30 a.m on 31 July.

Get oldest flashback time –

Col myscn format 99999999999
SELECT OLDEST_FLASHBACK_SCN MYSCN ,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH24:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG;

If there are logs up until 06:30 on 31 Jul, you can flashback.

Get the SCN number to flashback to –

col first_change# format 99999999999
select first_change# ,to_char(first_time,’DD-MON-YYYY HH24:MI:SS’),sequence# from V$LOG_HISTORY ORDER BY 1 ;

23856899540 31-JUL-2009 04:30:09 119695
23856965501 31-JUL-2009 06:30:10 119696
23857031498 31-JUL-2009 08:30:09 119697
23859233947 31-JUL-2009 10:00:29 119704
23859242647 31-JUL-2009 10:31:34 119705

Note down the SCN at the time you want to flashback to which is the FIRST_CHANGE# in the V$LOG_HISTORY view –

Restore a few archivelogs prior to and after the flashback time – the log sequence is in the last column above….

Ex – to restore archivelogs from 119695 to 119702

rman target / catalog rman/rman@rman

RMAN > restore archivelog from sequence 119695 until sequence 119705;

Once the logs are restored make sure no automatic RMAN backup job of archivelogs run which delete old archivelogs from disk –

Next flashback the database –

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

FLASHBACK DATABASE TO SCN 23856965501;

Once FLASHBACK is complete-

Alter database open read only –

If satisfied with the data –

Shutdown immediate;

Startup mount;

Alter database open resetlogs;

To get rid of old flashback logs –

Shutdown immediate;

Startup mount;

Alter database flashback off ; – Oracle automatically deletes old flashback logs.

Alter database flashback on – restart flashback.

ALTER DATABASE OPEN;

Posted in High Availability | Tagged: | Leave a Comment »

TESTING FLASHBACK

Posted by Arjun Raja on August 25, 2009

SQL> select count(*) from test;

COUNT(*)
———-
50000

At 09:02

SQL> @loop
18 /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

COUNT(*)
———-
100000

At 09:10

SQL> select name from v$database;

NAME
———
BOZO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 478150656 bytes
Fixed Size 2084944 bytes
Variable Size 436211632 bytes
Database Buffers 33554432 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp(‘2009-01-15 09:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

Flashback complete.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> ALTER DATABASE OPEN NORESETLOGS;
ALTER DATABASE OPEN NORESETLOGS
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> select count(*) from test;

COUNT(*)
———-
50000

SQL> exit

_______________________________________________________________________________ Unencrypted electronic mail is not secure and may not be authentic. If you have any doubts as to the contents please telephone to confirm.

This electronic transmission including any attachments is intended only for those to whom it is addressed. It may contain copyright material or information that is confidential, privileged or exempt from disclosure by law. Any claim to privilege is not waived or lost by reason of mistaken transmission of this information. If you are not the intended recipient you must not distribute or copy this transmission and should please notify the sender. Your costs for doing this will be reimbursed by the sender.

We do not accept liability in connection with computer virus, data corruption, delay, interruption, unauthorised access or unauthorised amendment. _______________________________________________________________________________

______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

Posted in High Availability | Leave a Comment »

ORA-19809: limit exceeded for recovery files

Posted by Arjun Raja on August 24, 2009

ALERT LOG ERROR  – ORA-19809

If the archivelog desintation as suggested by the parameter db_recovery_file_dest has used all the capacity as indicated in the db_recovery_file_dest_size parameter , then Oracle will not archive the next log until space is added or freed up.

The following error will appear in the alert log if you try to shutdown a database or switch a log file –

ORACLE Instance flash – Archival Error
ORA-16038: log 1 sequence# 43 cannot be archived
ORA-19809: limit exceeded for recovery files

To overcome this error after you have issued the Shutdown Immediate command –

1.  Shutdown abort;

2.  Startup mount;

3.

SQL>

set lines 100

col name format a60

select  name,  (space_limit/1048576),(space_used/1048576)from  v$recovery_file_dest;

NAME                                                         (SPACE_LIMIT/1048576)
———————————————————— ———————
(SPACE_USED/1048576)
——————–
/u02/oradata/flash_recovery_area                                              2048
            2184.979

 SQL> alter system set db_recovery_file_dest_size=4096m scope=both;

SQL> alter database open;

Database altered.

SQL>

Now shutdown immediate;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Posted in Administration, High Availability | Tagged: , , | 1 Comment »

FLASHBACK DATABASE TO SCN

Posted by Arjun Raja on August 4, 2009

FLASHBACK DATABASE TO SCN

Time to FLASHBACK DATABASE TO – 06:30 a.m on 31 July.

Get oldest flashback time –

SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$FLASHBACK_DATABASE_LOG;

If there are logs up until 06:30 on 31 Jul, you can flashback.

Get the SCN number to flashback to –

col first_change# format 99999999999
select first_change# ,to_char(first_time,'DD-MON-YYYY HH24:MI:SS'),sequence# from V$LOG_HISTORY ORDER BY 1 ;

23856899540 31-JUL-2009 04:30:09 119695
23856965501 31-JUL-2009 06:30:10 119696
23857031498 31-JUL-2009 08:30:09 119697
23859233947 31-JUL-2009 10:00:29 119704
23859242647 31-JUL-2009 10:31:34 119705

Note down the SCN at the time you want to flashback to which is the FIRST_CHANGE# in the V$LOG_HISTORY view –

Example here the SCN is 23856965501 at 06:30 – the time you want to flashback to.

Restore a few archivelogs prior to and after the flashback time – the log sequence is in the last column above….

Ex – to restore archivelogs from 119695 to 119702

rman target / catalog rman/rman@rman

RMAN > restore archivelog from sequence 119695 until sequence 119705;

Once the logs are restored make sure no automatic RMAN backup job of archivelogs run which delete old archivelogs from disk –

Next flashback the database –

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

FLASHBACK DATABASE TO SCN 23856965501;

Once FLASHBACK is complete-

Verify the data before opening with a resetlogs –

ALTER DATABASE OPEN READ ONLY;

If satisfied with the data –

Shutdown immediate;
Startup mount ;
Alter database open resetlogs;

Posted in High Availability | Tagged: | Leave a Comment »

Hello world!

Posted by Arjun Raja on July 16, 2009

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Posted in High Availability | 1 Comment »