Oracle Learning

Tuesday, October 30, 2012

OCR recovery from CRS internal backup and export backup

Recovery case of OCR disk from ocr own backup.

$ crs_stat -t

Name Type Target State Host

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

ora.ibm.db application ONLINE ONLINE labtest02

ora....m1.inst application ONLINE ONLINE labtest01

ora....m2.inst application ONLINE ONLINE labtest02

ora....SM1.asm application ONLINE ONLINE labtest01

ora....01.lsnr application ONLINE ONLINE labtest01

ora....t01.gsd application ONLINE ONLINE labtest01

ora....t01.ons application ONLINE ONLINE labtest01

ora....t01.vip application ONLINE ONLINE labtest01

ora....SM2.asm application ONLINE ONLINE labtest02

ora....02.lsnr application ONLINE ONLINE labtest02

ora....t02.gsd application ONLINE ONLINE labtest02

ora....t02.ons application ONLINE ONLINE labtest02

ora....t02.vip application ONLINE ONLINE labtest02

$ ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 307112

Used space (kbytes) : 3812

Available space (kbytes) : 303300

ID : 992671071

Device/File Name : /dev/rhdiskpower1

Device/File integrity check succeeded

Device/File Name : /dev/rhdiskpower2

Device/File integrity check succeeded

Cluster registry integrity check succeeded

$ orcconfig -showbackup

ksh: orcconfig: not found

$ ocrconfig -showbackup

labtest02 2010/02/06 11:40:44 /u01/app/oracle/product/10.2.0/crs/cdata/crs

labtest02 2010/02/06 07:40:44 /u01/app/oracle/product/10.2.0/crs/cdata/crs

labtest02 2010/02/06 03:40:43 /u01/app/oracle/product/10.2.0/crs/cdata/crs

labtest02 2010/02/05 07:40:41 /u01/app/oracle/product/10.2.0/crs/cdata/crs

labtest02 2010/01/26 04:53:33 /u01/app/oracle/product/10.2.0/crs/cdata/crs

##################

Now we format ocr disk...

$ sudo su -

Password:

# pwd

# dd if=/dev/zero of=/dev/rhdiskpower1 bs=8192 count=25000

25000+0 records in

25000+0 records out

# dd if=/dev/zero of=/dev/rhdiskpower1 bs=8192 count=25000

25000+0 records in

25000+0 records out

# dd if=/dev/zero of=/dev/rhdiskpower2 bs=8192 count=25000

25000+0 records in

25000+0 records out

######################

$ crs_stat -t

Name Type Target State Host

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

ora.ibm.db application ONLINE ONLINE labtest02

ora....m1.inst application ONLINE ONLINE labtest01

ora....m2.inst application ONLINE ONLINE labtest02

ora....SM1.asm application ONLINE ONLINE labtest01

ora....01.lsnr application ONLINE ONLINE labtest01

ora....t01.gsd application ONLINE ONLINE labtest01

ora....t01.ons application ONLINE ONLINE labtest01

ora....t01.vip application ONLINE ONLINE labtest01

ora....SM2.asm application ONLINE ONLINE labtest02

ora....02.lsnr application ONLINE ONLINE labtest02

ora....t02.gsd application ONLINE ONLINE labtest02

ora....t02.ons application ONLINE ONLINE labtest02

ora....t02.vip application ONLINE ONLINE labtest02

$ uname -n

labtest01

$ srvctl stop listener -n labtest01

PRKH-1010 : Unable to communicate with CRS services.

[OCR Error(Native: prsr_initCLSS:[21])]

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ ocrconfig -restore /u01/app/oracle/product/10.2.0/crs/cdata/crs/day.ocr

PROT-20: Insufficient permission to proceed. Require privileged user

###################

with root privilage:-

# cd /u01/app/oracle/product/10.2.0/crs

# cd bin

# ./ocrconfig -restore /u01/app/oracle/product/10.2.0/crs/cdata/crs/day.ocr

# echo $?

# ./crsctl start crs

Attempting to start CRS stack

The CRS stack will be started shortly

######################

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

Name Type Target State Host

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

ora.debs.db application ONLINE ONLINE labtest02

ora....debs.cs application ONLINE ONLINE labtest01

ora....bs1.srv application ONLINE OFFLINE

ora....s1.inst application ONLINE OFFLINE

ora....s2.inst application ONLINE OFFLINE

ora....SM1.asm application ONLINE ONLINE labtest01

ora....01.lsnr application ONLINE ONLINE labtest01

ora....t01.gsd application ONLINE ONLINE labtest01

ora....t01.ons application ONLINE ONLINE labtest01

ora....t01.vip application ONLINE ONLINE labtest01

ora....SM2.asm application ONLINE ONLINE labtest02

ora....02.lsnr application ONLINE ONLINE labtest02

ora....t02.gsd application ONLINE ONLINE labtest02

ora....t02.ons application ONLINE ONLINE labtest02

ora....t02.vip application ONLINE ONLINE labtest02

ora....andh.db application ONLINE ONLINE labtest01

ora....h1.inst application ONLINE OFFLINE

ora....h2.inst application ONLINE OFFLINE

$ ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 307112

Used space (kbytes) : 5128

Available space (kbytes) : 301984

ID : 992671071

Device/File Name : /dev/rhdiskpower1

Device/File integrity check succeeded

Device/File Name : /dev/rhdiskpower2

Device/File integrity check succeeded

Cluster registry integrity check succeeded

$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 6 14:44:27 2012

Enter user-name: / as sysdba

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

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

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

SQL>

********************************************************************

From OCR export backup:

$ crs_stat -t

Name Type Target State Host

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

ora.debs.db application OFFLINE OFFLINE

ora....debs.cs application OFFLINE OFFLINE

ora....bs1.srv application ONLINE OFFLINE

ora....s1.inst application ONLINE OFFLINE

ora....s2.inst application ONLINE OFFLINE

ora....SM1.asm application ONLINE ONLINE labtest01

ora....01.lsnr application ONLINE ONLINE labtest01

ora....t01.gsd application ONLINE ONLINE labtest01

ora....t01.ons application ONLINE ONLINE labtest01

ora....t01.vip application ONLINE ONLINE labtest01

ora....SM2.asm application ONLINE ONLINE labtest02

ora....02.lsnr application ONLINE ONLINE labtest02

ora....t02.gsd application ONLINE ONLINE labtest02

ora....t02.ons application ONLINE ONLINE labtest02

ora....t02.vip application ONLINE ONLINE labtest02

ora....andh.db application OFFLINE OFFLINE

ora....h1.inst application ONLINE OFFLINE

ora....h2.inst application ONLINE OFFLINE

$ ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 2

Total space (kbytes) : 307112

Used space (kbytes) : 5136

Available space (kbytes) : 301976

ID : 992671071

Device/File Name : /dev/rhdiskpower1

Device/File integrity check succeeded

Device/File Name : /dev/rhdiskpower2

Device/File integrity check succeeded

Cluster registry integrity check succeeded

$ ls -lt /u01

total 0

drwxr-xr-x 2 oracle oinstall 256 Jan 20 16:20 cron_script

drwxrwx--- 3 oracle oinstall 256 Jan 12 16:17 app

drwxr-xr-x 2 root system 256 Jan 7 16:10 lost+found

$ cd /u01/app

$ ls

oracle

$ mkdir ocr

$ ls

ocr oracle

$ ls -lt

total 0

drwxr-xr-x 2 oracle oinstall 256 Feb 6 15:09 ocr

drwxrwx--- 3 oracle oinstall 256 Jan 12 16:17 oracle

$ cd ocr

$ pwd

/u01/app/ocr

$ ocrconfig -export /u01/app/ocr/ocr-export.dmp

PROT-20: Insufficient permission to proceed. Require privileged user

######################

# pwd

/u01/app/oracle/product/10.2.0/crs/bin

# ./ocrconfig -export /u01/app/ocr/ocr-export.dmp

# dd if=/dev/zero of=/dev/rhdiskpower1 bs=8192 count=25000

25000+0 records in

25000+0 records out

# dd if=/dev/zero of=/dev/rhdiskpower2 bs=8192 count=25000

25000+0 records in

25000+0 records out

# ./ocrconfig -import /u01/app/ocr/ocr-export.dmp

# ./crsctl start crs

Attempting to start CRS stack

The CRS stack will be started shortly

#################

$ crs_stat -t

CRS-0184: Cannot communicate with the CRS daemon.

$ crs_stat -t

Name Type Target State Host

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

ora.debs.db application OFFLINE OFFLINE

ora....debs.cs application OFFLINE OFFLINE

ora....bs1.srv application ONLINE OFFLINE

ora....s1.inst application ONLINE OFFLINE

ora....s2.inst application ONLINE OFFLINE

ora....SM1.asm application ONLINE ONLINE labtest01

ora....01.lsnr application ONLINE ONLINE labtest01

ora....t01.gsd application ONLINE ONLINE labtest01

ora....t01.ons application ONLINE ONLINE labtest01

ora....t01.vip application ONLINE ONLINE labtest01

ora....SM2.asm application ONLINE ONLINE labtest02

ora....02.lsnr application ONLINE ONLINE labtest02

ora....t02.gsd application ONLINE ONLINE labtest02

ora....t02.ons application ONLINE ONLINE labtest02

ora....t02.vip application ONLINE ONLINE labtest02

ora....andh.db application OFFLINE OFFLINE

ora....h1.inst application ONLINE OFFLINE

ora....h2.inst application ONLINE OFFLINE

Thursday, September 20, 2012

to check if oracle database is upgrade from 10g to 11g

Below script can the checked to see the database up gradation life cycle.

SQL> l

1* select * from DBA_REGISTRY_HISTORY

SQL> /

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS

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

16-NOV-09 04.22.18.257083 PM UPGRADE SERVER 11.1.0.7.0 Upgraded from 11.1.0.6.0

06-JUN-10 01.36.36.363629 PM APPLY SERVER 11.1.0.7 2 PSU 11.1.0.7.2

06-JUN-10 01.40.09.606668 PM CPU 6452863 view recompilation

25-MAY-11 05.59.56.069514 PM VIEW INVALIDATE 8289601 view invalidation

25-MAY-11 06.00.22.484425 PM UPGRADE SERVER 11.2.0.2.0 Upgraded from 11.1.0.7.0

25-MAY-11 07.45.23.622169 PM VIEW INVALIDATE 8289601 view invalidation

25-MAY-11 07.45.39.435819 PM UPGRADE SERVER 11.2.0.2.0 Upgraded from 11.1.0.7.0

7 rows selected.

SQL> SELECT version, prv_version,banner FROM sys.registry$ ;

VERSION PRV_VERSION BANNER

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

11.2.0.2.0 11.1.0.6.0 Oracle Database Catalog Views Release 11.2.0.2.0 - 64bit Production

11.2.0.2.0 11.1.0.7.0 Oracle Database Packages and Types Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Workspace Manager Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 JServer JAVA Virtual Machine Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle XDK Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Database Java Packages Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Expression Filter Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Text Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle XML Database Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Rules Manager Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle Multimedia Release 11.2.0.2.0 - Development

VERSION PRV_VERSION BANNER

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

11.2.0.2.0 11.1.0.7.0 OLAP Analytic Workspace Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Oracle OLAP API Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 OLAP Catalog Release 11.2.0.2.0 - Development

11.2.0.2.0 11.1.0.7.0 Spatial Release 11.2.0.2.0 - Development

11.2.0.2.0 Oracle Enterprise Manager Release 11.2.0.2.0 - Development

3.2.1.00.12 3.0.1.00.12 Oracle Application Express Release 3.2.1.00.12 - Development

11.1.0.6.0 OWB Release 11.1.0.6.0 - Production

18 rows selected.

SQL>

Monday, September 10, 2012

RMAN DUPLICATE COMMAND PITFALL

· RMAN DUPLICATE: CONTROL FILE IS RESTORED NOT CREATED.

In Oracle Database 11.2, the RMAN 'Duplicate' command now RESTORES the backup controlfile to the auxiliary host rather than CREATE it. So we must ensure that there is a controlfile backup available to meet the duplicate time. Otherwise we may get the bellow error:

RMAN-03002: failure of Duplicate Db command at 07/23/2012 10:09:08

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06026: some targets not found - aborting restore

RMAN-06024: no backup or copy of the control file found to restore

Here's test to run before starting your duplication, to verify whether you have a valid controlfile backup:

RMAN> run {

set until time "to_date('2012 SEP 03 07:37','YYYY MON DD HH24:MI')";

restore controlfile preview;}

Note: change the 'SET UNTIL TIME' to meet your duplicate requirements.

· BCT(Block change tracking) issue when duplicating DB.

If BCT enable at production DB, we may get 'ORA-19755 Using RMAN Duplicate, Tries Open The Block Change Tracking File of Source DB'.

Workaround is to create a dummy file in same location as on Production to meet this requirement. We may also disable BCT for the time, duplicate process is active. Also check DOC ID: 1098638.1.

RMAN backup optimization

RMAN backup optimization is another feature to minimize backup size. Enabling this feature, RMAN BACKUP will skip backup of files that comes in certain condition or exactly identical to the file already backup up.

By Default it is set to OFF:

RMAN> SHOW BACKUP OPTIMIZATION;

RMAN configuration parameters for database with db_unique_name ORACLE are:

CONFIGURE BACKUP OPTIMIZATION OFF; # default

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

RMAN>

Below are the criteria when RMAN check which files can be skipped from the backup.

In case of datafile RMAN compares the DBID, checkpoint SCN, creation SCN, and RESETLOGS SCN and time as a datafile already in a backup. If they are identical then skip to take backup again.

In case of archived redo log RMAN compares thread, sequence number, and RESETLOGS SCN and time.

In case of backupset RMAN compares recid and stamp.

Backup Optimization only applies to below rman commands:

BACKUP DATABASE

BACKUP ARCHIVELOG with ALL or LIKE options

BACKUP BACKUPSET ALL

Optimization feature can be override anytime by using FORCE option with backup command.

BACKUP DATABASE FORCE;

Log:

Starting backup at 08-SEP-12

current log archived

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3074.294.793279165;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3075.307.793284119;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3076.298.793286989;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3077.297.793289297;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3078.382.793291555;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3079.395.793293577;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3080.381.793295619;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3081.299.793297653;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3082.403.793299621;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3083.408.793301609;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3084.362.793303497;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3085.355.793305483;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3086.364.793307311;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3087.306.793309275;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3088.292.793311117;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3089.283.793312945;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3090.365.793314877;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3091.356.793316867;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3092.410.793317693;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3093.281.793319779;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_06/thread_1_seq_3094.358.793322287;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3095.368.793325083;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3096.386.793326137;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3097.290.793328655;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3098.411.793332451;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3099.360.793338931;

skipping archived log file +ASMFRA/tiger/archivelog/2012_09_07/thread_1_seq_3100.407.793343249;

Saturday, September 8, 2012

log evidence of master node in RAC environment

Another log evidence of master node in RAC environment

2012-09-06 18:27:20.452: [ CRSRES][1090042176][ALERT] Remote start for `******.******1.inst` failed on member `*******`

2012-09-06 18:27:20.453: [ CRSD][1092143424] SM: recovery in process: 8

2012-09-06 18:27:20.453: [ CRSEVT][1092143424] Do failover for: ********

2012-09-06 18:27:20.457: [ OCRMSG][1092143424]prom_rpc: CLSC recv failure..ret code 7

2012-09-06 18:27:20.457: [ OCRMSG][1092143424]prom_rpc: possible OCR retry scenario

2012-09-06 18:27:20.458: [ OCRMAS][1085839680]th_master:13: I AM THE NEW OCR MASTER at incar 14. Node Number 2

Thursday, September 6, 2012

ORA-20005: object statistics are locked

Today with an import script, we had received below error.

IMP-00017: following statement failed with ORACLE error 20005: IMP-00003: ORACLE error 20005 encountered IMP-00017: following statement failed with ORACLE error 20005: IMP-00003: ORACLE error 20005 encountered IMP-00017

When checking we find statistics are locked for those objects.

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked = 'ALL';

Solution is either to unlock the statistics the objects using dbms_stats package.

exec dbms_stats.unlock_schema_stats('schema_owner');

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Else to prevent import (imp) from locking the table's statistics when importing a table without the rows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

Friday, July 6, 2012

using bad backup by hidden parameter _allow_resetlogs_corruption

Case: how to use bad backup

To learn this, first we need to take a bad backup that we will use later to recover the database. Let’s checking the database.

C:\Users\jayva>echo %ORACLE_SID%

target

C:\Users\jayva>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 6 19:16:14 2012

Enter user-name: / as sysdba

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> select * from v$backup

2 ;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

SQL> exit

So we have 4 datafiles and all in open status. We will prefer to use user manage backup and do we will do without taking datafilesin backup mode. Also same time, I will execute a pl/sql block that will push incremental data in a table. This I have done to keep changing the SCN number with respect of datafiles.

So what we will do is we will keep database up and running with executing the block i said above and copy the datafiles to backup location. Once done with datafiles copy we will also backup the controlfile.

C:\app\JAYVA\oradata\target>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 6 19:26:32 2012

Enter user-name: / as sysdba

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> alter system archive log current;

System altered.

SQL> alter database backup controlfile to 'C:\app\JAYVA\backup\control.ctl';

Database altered.

Once done will the backup, let shutdown the database.

SQL> shu abort

ORACLE instance shut down.

SQL>

Now what we need to is to delete all the datafiles and restore the files from what backup that we have taken previously. Once done with restoring the bakcup, let us try to start the database.

SQL> startup mount

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1375004 bytes

Variable Size 322962660 bytes

Database Buffers 88080384 bytes

Redo Buffers 6066176 bytes

Database mounted.

SQL> alter database open;

alter database open

ERROR at line 1:

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

Now we know these files we need recovery, so trying that.

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 6610452 generated at 07/06/2012 19:22:46 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_108

_7ZFVMWOZ_.ARC

ORA-00280: change 6610452 for thread 1 is in sequence #108

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

7_7ZFVFL8O_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6672502 generated at 07/06/2012 19:26:09 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_109

_7ZFVNZJ0_.ARC

ORA-00280: change 6672502 for thread 1 is in sequence #109

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

8_7ZFVMWOZ_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6751368 generated at 07/06/2012 19:26:46 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_110

_7ZFVO6QJ_.ARC

ORA-00280: change 6751368 for thread 1 is in sequence #110

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_10

9_7ZFVNZJ0_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6768172 generated at 07/06/2012 19:26:53 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_111

_7ZFVOL8M_.ARC

ORA-00280: change 6768172 for thread 1 is in sequence #111

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

0_7ZFVO6QJ_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6850266 generated at 07/06/2012 19:27:05 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_112

_7ZFVPC9X_.ARC

ORA-00280: change 6850266 for thread 1 is in sequence #112

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

1_7ZFVOL8M_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 6931017 generated at 07/06/2012 19:27:30 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_113

_7ZFVPSLT_.ARC

ORA-00280: change 6931017 for thread 1 is in sequence #113

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

2_7ZFVPC9X_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7012625 generated at 07/06/2012 19:27:45 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_114

_7ZFVQ5WF_.ARC

ORA-00280: change 7012625 for thread 1 is in sequence #114

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

3_7ZFVPSLT_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7094066 generated at 07/06/2012 19:27:57 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115

_7ZFVQKP3_.ARC

ORA-00280: change 7094066 for thread 1 is in sequence #115

ORA-00278: log file

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

4_7ZFVQ5WF_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7115299 time 07/06/2012

19:28:02

ORA-00334: archived log:

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

5_7ZFVQKP3_.ARC'

ORA-01112: media recovery not started

Once done with the recovery, let try to open the database.

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 resetlogs;

alter database open resetlogs

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'C:\APP\JAYVA\ORADATA\TARGET\SYSTEM01.DBF'

SQL> alter database open noresetlogs;

alter database open noresetlogs

ERROR at line 1:

ORA-01588: must use RESETLOGS option for database open

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 7111011 generated at 07/06/2012 19:27:57 needed for thread 1

ORA-00289: suggestion :

C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115

_7ZFVQKP3_.ARC

ORA-00280: change 7111011 for thread 1 is in sequence #115

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012

19:28:02

ORA-00334: archived log:

'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_11

5_7ZFVQKP3_.ARC'

ORA-01112: media recovery not started

SQL>

We tried to open the database with all the option avalliable but none works. Also checking the alert log files, we is what we get the error.

In alert log +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Media Recovery Start

started logmerger process

Fri Jul 06 21:42:19 2012

WARNING! Recovering data file 1 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 2 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 3 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 4 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

Parallel Media Recovery started with 2 slaves

ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel using backup controlfile ...

ALTER DATABASE RECOVER CONTINUE DEFAULT

Media Recovery Log C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_pr00_17832.trc (incident=62563):

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Incident details in: c:\app\jayva\diag\rdbms\target\target\incident\incdir_62563\target_pr00_17832_i62563.trc

Recovery interrupted!

Fri Jul 06 21:42:24 2012

Trace dumping is performing id=[cdmp_20120706214224]

Recovered data files to a consistent state at change 7111011

Media Recovery failed with error 354

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_pr00_17832.trc:

ORA-00283: recovery session canceled due to errors

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 24576 change 7111704 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Fri Jul 06 21:42:25 2012

Sweep [inc][62563]: completed

ORA-283 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...

ALTER DATABASE RECOVER CANCEL

ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...

Errors in file c:\app\jayva\diag\rdbms\target\target\trace\target_m000_14772.trc (incident=62603):

ORA-00353: log corruption near block 24576 change 7115299 time 07/06/2012 19:28:02

ORA-00334: archived log: 'C:\APP\JAYVA\FLASH_RECOVERY_AREA\TARGET\TARGET\ARCHIVELOG\2012_07_06\O1_MF_1_115_7ZFVQKP3_.ARC'

Incident details in: c:\app\jayva\diag\rdbms\target\target\incident\incdir_62603\target_m000_14772_i62603.trc

Trace dumping is performing id=[cdmp_20120706214227]

Fri Jul 06 21:42:49 2012

Sweep [inc][62603]: completed

Sweep [inc2][62603]: completed

Sweep [inc2][62563]: completed

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

So alert log confirms we are using the bad backup. So now we will use hidden parameter _allow_resetlogs_corrution=yes in pfile and try to open the database with that.

C:\app\JAYVA\oradata\target>type pfile.ora

target.__db_cache_size=92274688

target.__java_pool_size=4194304

target.__large_pool_size=4194304

target.__oracle_base='C:\app\JAYVA'#ORACLE_BASE set from environment

target.__pga_aggregate_target=146800640

target.__sga_target=272629760

target.__shared_io_pool_size=0

target.__shared_pool_size=155189248

target.__streams_pool_size=8388608

*.audit_file_dest='C:\app\JAYVA\admin\target\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='C:\app\JAYVA\oradata\target\control01.ctl','C:\app\JAYVA\oradat

a\target\control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='C:\APP\JAYVA\ORADATA\TARGET'

*.db_domain=''

*.db_name='target'

*.db_recovery_file_dest_size=21474836480

*.db_recovery_file_dest='C:\app\JAYVA\flash_recovery_area\target'

*.diagnostic_dest='C:\app\JAYVA'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=targetXDB)'

*.local_listener='LISTENER_TARGET'

*.memory_target=419430400

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.result_cache_max_size=10485760

*.sga_target=0

*.shared_pool_size=104857600

*.undo_tablespace='UNDOTBS1'

*._allow_resetlogs_corruption=yes

C:\app\JAYVA\oradata\target>

SQL> startup mount pfile=C:\app\JAYVA\oradata\target\pfile.ora

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1375004 bytes

Variable Size 318768356 bytes

Database Buffers 92274688 bytes

Redo Buffers 6066176 bytes

Database mounted.

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 resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

So now we have open the database, lets checking the data.

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

MAGIC1 TABLE

SALGRADE TABLE

TESTTABLE1 TABLE

6 rows selected.

SQL> select count(1) from TESTTABLE1;

COUNT(1)

----------

450927

SQL>

We have recovered the data now.

It resolved our issue and database was up and running without any issue.

_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated. As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all.Using this hidden should only used when we have confirm statement form Oracle Suppport.

Oracle Learning

Tuesday, October 30, 2012

OCR recovery from CRS internal backup and export backup

Thursday, September 20, 2012

to check if oracle database is upgrade from 10g to 11g

Monday, September 10, 2012

RMAN DUPLICATE COMMAND PITFALL

RMAN backup optimization

Saturday, September 8, 2012

log evidence of master node in RAC environment

Thursday, September 6, 2012

ORA-20005: object statistics are locked

Friday, July 6, 2012

using bad backup by hidden parameter _allow_resetlogs_corruption

About Me

Blog Archive