Archive for the ‘RMAN’ Category

11gR2 RAC recovery test

Posted: December 10, 2013 in RAC, RMAN

I was working on a recovery guide for a RAC cluster I built so I thought I would share the recovery of the DATA diskgroup. The set-up is a 2 node RAC cluster running 11.2.0.3 standard edition ASM with 2 disk groups DATA and FLASH. The OCR and voting disk are in the DATA diskgroup along with the database datafiles.

The test is to illustrate the failure in the +DATA diskgroup which will result in loss of the OCR, Voting disk and database files

Pre-recovery Steps

1. OCR & Voting Disk backup

As root

root@dbrac01mgt)# . oraenv
ORACLE_SID = [+ASM1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

root@dbrac01mgt)# ocrconfig -showbackup
PROT-24: Auto backups for the Oracle Cluster Registry are not available
PROT-25: Manual backups for the Oracle Cluster Registry are not available

root@dbrac01mgt)# ocrconfig -manualbackup

dbrac01mgt 2013/12/09 13:35:34 /u01/app/11.2.3/grid/cdata/dbracmgt-scan/backup_20131209_133534.ocr
root@dbrac01mgt)# ocrconfig -showbackup
PROT-24: Auto backups for the Oracle Cluster Registry are not available

dbrac01mgt 2013/12/09 13:35:34 /u01/app/11.2.3/grid/cdata/dbracmgt-scan/backup_20131209_133534.ocr

2. Database Backup

oracle@dbrac01mgt /export/home/oracle $ . oraenv
ORACLE_SID = [+ASM1] ? RACTEST1
The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbrac01mgt /export/home/oracle $ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 9 15:35:09 2013

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

connected to target database: RACTEST (DBID=3362036407)

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/backup/df_%d_%s_%p.bak';
RMAN>backup as compressed backupset database plus archivelog;

Starting backup at 09-DEC-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 instance=RACTEST1 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=49 RECID=2 STAMP=833722208
input archived log thread=2 sequence=1 RECID=1 STAMP=833721815
input archived log thread=2 sequence=2 RECID=3 STAMP=833722209
input archived log thread=1 sequence=50 RECID=5 STAMP=833722364
input archived log thread=2 sequence=3 RECID=4 STAMP=833722362
input archived log thread=2 sequence=4 RECID=7 STAMP=833722440
input archived log thread=1 sequence=51 RECID=6 STAMP=833722437
channel ORA_DISK_1: starting piece 1 at 09-DEC-13
channel ORA_DISK_1: finished piece 1 at 09-DEC-13
piece handle=/u01/backup/df_RACTEST_4_1.bak tag=TAG20131209T133403 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 09-DEC-13

Starting backup at 09-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ractest/datafile/system.259.833718845
input datafile file number=00002 name=+DATA/ractest/datafile/sysaux.260.833718851
input datafile file number=00003 name=+DATA/ractest/datafile/undotbs1.261.833718855
input datafile file number=00004 name=+DATA/ractest/datafile/undotbs2.263.833718873
input datafile file number=00006 name=+DATA/ractest/datafile/test.268.833721985
input datafile file number=00005 name=+DATA/ractest/datafile/users.264.833718875
channel ORA_DISK_1: starting piece 1 at 09-DEC-13
channel ORA_DISK_1: finished piece 1 at 09-DEC-13
piece handle=/u01/backup/df_RACTEST_5_1.bak tag=TAG20131209T133411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 09-DEC-13
channel ORA_DISK_1: finished piece 1 at 09-DEC-13
piece handle=/u01/backup/df_RACTEST_6_1.bak tag=TAG20131209T133411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-DEC-13

Starting backup at 09-DEC-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=52 RECID=8 STAMP=833722559
input archived log thread=2 sequence=5 RECID=9 STAMP=833722560
channel ORA_DISK_1: starting piece 1 at 09-DEC-13
channel ORA_DISK_1: finished piece 1 at 09-DEC-13
piece handle=/u01/backup/df_RACTEST_7_1.bak tag=TAG20131209T133603 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-DEC-13

Simulate a error

Now we have the backups we need its time to corrupt the +DATA diskgroup, the DATA diskgroup has one disk /dev/oracleasm

oracle@dbrac01mgt /export/home/oracle/sql $ dd if=/dev/zero of=/dev/oracleasm/disk1 bs=1024 count=1000000
1000000+0 records in
1000000+0 records out
1024000000 bytes (1.0 GB) copied, 5.08769 s, 201 MB/s

root@dbrac01mgt)# ocrcheck
PROT-601: Failed to initialize ocrcheck
PROC-22: The OCR backend has an invalid format

Error from the clusterlogs

2013-12-09 13:55:38.099
[client(23076)]CRS-1006:The OCR location +DATA is inaccessible. Details in /u01/app/11.2.3/grid/log/dbrac01mgt/client/ocrcheck_23076.log.

Error from the alert log

ORA-00202: control file: '+DATA/ractest/controlfile/current.256.833718843'
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_lmon_19978.trc (incident=2489):
ORA-00227: corrupt block detected in control file: (block 35, # blocks 1)
ORA-00202: control file: '+DATA/ractest/controlfile/current.256.833718843'

Steps to recover the cluster

1. Stop cluster on each node (Root user).

DBRAC01MGT
root@dbrac01mgt)# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.crsd' on 'dbrac01mgt'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.oc4j' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.cvu' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.dbrac01mgt.vip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.scan3.vip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.scan2.vip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.dbrac01mgt.vip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.dbrac01mgt.vip' on 'dbrac02mgt'
CRS-2676: Start of 'ora.scan3.vip' on 'dbrac02mgt' succeeded
CRS-2676: Start of 'ora.scan2.vip' on 'dbrac02mgt' succeeded
CRS-2677: Stop of 'ora.FLASH.dg' on 'dbrac01mgt' succeeded
CRS-2676: Start of 'ora.dbrac01mgt.vip' on 'dbrac02mgt' succeeded
CRS-4549: Unexpected disconnect while executing shutdown request.
CRS-2675: Stop of 'ora.crsd' on 'dbrac01mgt' failed
CRS-2679: Attempting to clean 'ora.crsd' on 'dbrac01mgt'
CRS-2681: Clean of 'ora.crsd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.evmd' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.asm' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.evmd' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.asm' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.cssd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.crf' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.gipcd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.gpnpd' on 'dbrac01mgt' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dbrac01mgt' has completed
CRS-4133: Oracle High Availability Services has been stopped.

DBRAC02MGT
root@dbrac02mgt)# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dbrac02mgt'
CRS-2673: Attempting to stop 'ora.ctssd' on 'dbrac02mgt'
CRS-2673: Attempting to stop 'ora.evmd' on 'dbrac02mgt'
CRS-2673: Attempting to stop 'ora.asm' on 'dbrac02mgt'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.mdnsd' on 'dbrac02mgt' succeeded
CRS-2677: Stop of 'ora.evmd' on 'dbrac02mgt' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'dbrac02mgt' succeeded
CRS-2677: Stop of 'ora.asm' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.cssd' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.crf' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.gipcd' on 'dbrac02mgt' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dbrac02mgt'
CRS-2677: Stop of 'ora.gpnpd' on 'dbrac02mgt' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dbrac02mgt' has completed
CRS-4133: Oracle High Availability Services has been stopped.

2. Start the cluster in the excusive mode (Root user)
As root start GI in exclusive mode on one node only:

root@dbrac01mgt)# crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'dbrac01mgt'
CRS-2676: Start of 'ora.mdnsd' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'dbrac01mgt'
CRS-2676: Start of 'ora.gpnpd' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'dbrac01mgt'
CRS-2672: Attempting to start 'ora.gipcd' on 'dbrac01mgt'
CRS-2676: Start of 'ora.cssdmonitor' on 'dbrac01mgt' succeeded
CRS-2676: Start of 'ora.gipcd' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'dbrac01mgt'
CRS-2672: Attempting to start 'ora.diskmon' on 'dbrac01mgt'
CRS-2676: Start of 'ora.diskmon' on 'dbrac01mgt' succeeded
CRS-2676: Start of 'ora.cssd' on 'dbrac01mgt' succeeded
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'dbrac01mgt'
CRS-2672: Attempting to start 'ora.ctssd' on 'dbrac01mgt'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'dbrac01mgt'
CRS-2676: Start of 'ora.ctssd' on 'dbrac01mgt' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'dbrac01mgt' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'dbrac01mgt'
CRS-2676: Start of 'ora.asm' on 'dbrac01mgt' succeeded

3. Re-create DATA disk group

oracle@dbrac01mgt /export/home/oracle/sql $ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 14:06:53 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SYS@+ASM1 > select PATH, HEADER_STATUS, MODE_STATUS, STATE from v$asm_disk;

PATH
------------------------------------------------------------------------------------------------------------------------------------------------------
HEADER_STATU MODE_ST STATE
------------ ------- --------
/dev/oracleasm/disk1
CANDIDATE ONLINE NORMAL

/dev/oracleasm/disk3
CANDIDATE ONLINE NORMAL

/dev/oracleasm/disk4
CANDIDATE ONLINE NORMAL

/dev/oracleasm/disk2
MEMBER ONLINE NORMAL

SYS@+ASM1 > select name, type, total_mb, free_mb from v$asm_diskgroup;

NAME TYPE TOTAL_MB FREE_MB
------------------------------ ------ ---------- ----------
FLASH 0 0

SYS@+ASM1 > create diskgroup DATA external redundancy disk '/dev/oracleasm/disk1' attribute 'COMPATIBLE.ASM' = '11.2';

Diskgroup created.

4. Restore the OCR

root@dbrac01mgt)# ocrconfig -showbackup
PROT-26: Oracle Cluster Registry backup locations were retrieved from a local copy
PROT-24: Auto backups for the Oracle Cluster Registry are not available
PROT-25: Manual backups for the Oracle Cluster Registry are not available

root@dbrac01mgt)# ocrconfig -restore /u01/app/11.2.3/grid/cdata/dbracmgt-scan/backup_20131209_133534.ocr

root@dbrac01mgt)# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2936
Available space (kbytes) : 259184
ID : 1387583758
Device/File Name : +DATA
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

5. Recreate spfile for ASM

Create a temp init.ora and then recreate the spfile

oracle@dbrac01mgt /export/home/oracle/sql $ cat /tmp/init.ora
*.asm_power_limit=1
*.diagnostic_dest='/u01/app/oracle'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'
*.asm_diskstring = '/dev/oracleasm/*'

SYS@+ASM1 > create spfile='+DATA' from pfile='/tmp/init.ora';

File created.

6. Recreate the voting disk

root@dbrac01mgt)# crsctl replace votedisk +DATA
CRS-4602: Failed 27 to add voting file 050fdada770b4f45bf3a2c24adc6a7bd.
Failed to replace voting disk group with +DATA.
CRS-4000: Command Replace failed, or completed with errors.

This failed due to the diskgroup setting in ASM being unset, so we need to set it

SYS@+ASM1 > alter system set asm_diskstring='/dev/oracleasm/*';

System altered.

root@dbrac01mgt)# crsctl replace votedisk +DATA
Successful addition of voting disk 1b2343bf1ff94f2abf11bcdbf39254cd.
Successfully replaced voting disk group with +DATA.
CRS-4266: Voting file(s) successfully replaced

7. Stop/start the cluster

root@dbrac01mgt)# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.crsd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.crsd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.ctssd' on 'dbrac01mgt'
CRS-2673: Attempting to stop 'ora.asm' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.mdnsd' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.asm' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dbrac01mgt' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.cssd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.gipcd' on 'dbrac01mgt' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'dbrac01mgt'
CRS-2677: Stop of 'ora.gpnpd' on 'dbrac01mgt' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dbrac01mgt' has completed
CRS-4133: Oracle High Availability Services has been stopped.

root@dbrac01mgt)# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

8. Restore the spfile

So we now need to restore the spfile from the backup took earlier but first we need to create the directory +DATA/ractest in the asm diskgroup +DATA

oracle@dbrac01mgt /export/home/oracle $ . oraenv
ORACLE_SID = [RACTEST1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbrac01mgt /export/home/oracle $ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 14:41:47 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup DATA add directory '+DATA/ractest';

oracle@dbrac01mgt /export/home/oracle $ . oraenv
ORACLE_SID = [+ASM1] ? RACTEST1
The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbrac01mgt /export/home/oracle/sql $ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 9 14:36:31 2013

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

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/RACTEST/spfileRACTEST.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/RACTEST/spfileRACTEST.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DATA/ractest/spfileractest.ora
ORA-15173: entry 'ractest' does not exist in directory '/'
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2226456 bytes
Variable Size 104859368 bytes
Database Buffers 46137344 bytes
Redo Buffers 5439488 bytes

RMAN> restore spfile from '/u01/backup/df_RACTEST_6_1.bak';

Starting restore at 09-DEC-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/df_RACTEST_6_1.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 09-DEC-13

9. Restore the controlfile

RMAN> startup force nomount

Oracle instance started

Total System Global Area 1043886080 bytes

Fixed Size 2234960 bytes
Variable Size 339740080 bytes
Database Buffers 696254464 bytes
Redo Buffers 5656576 bytes

RMAN> restore controlfile from '/u01/backup/df_RACTEST_6_1.bak';

Starting restore at 09-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 instance=RACTEST1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/ractest/controlfile/current.257.833726749
output file name=+FLASH/ractest/controlfile/current.256.833718843
Finished restore at 09-DEC-13

10. Restore and recover the database

RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 09-DEC-13
Starting implicit crosscheck backup at 09-DEC-13
allocated channel: ORA_DISK_1
Crosschecked 3 objects
Finished implicit crosscheck backup at 09-DEC-13

Starting implicit crosscheck copy at 09-DEC-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-DEC-13

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

List of Cataloged Files
=======================
File Name: +flash/RACTEST/ARCHIVELOG/2013_12_09/thread_1_seq_52.268.833722559
File Name: +flash/RACTEST/ARCHIVELOG/2013_12_09/thread_2_seq_5.269.833722561

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/ractest/datafile/system.259.833718845
channel ORA_DISK_1: restoring datafile 00002 to +DATA/ractest/datafile/sysaux.260.833718851
channel ORA_DISK_1: restoring datafile 00003 to +DATA/ractest/datafile/undotbs1.261.833718855
channel ORA_DISK_1: restoring datafile 00004 to +DATA/ractest/datafile/undotbs2.263.833718873
channel ORA_DISK_1: restoring datafile 00005 to +DATA/ractest/datafile/users.264.833718875
channel ORA_DISK_1: restoring datafile 00006 to +DATA/ractest/datafile/test.268.833721985
channel ORA_DISK_1: reading from backup piece /u01/backup/df_RACTEST_5_1.bak
channel ORA_DISK_1: piece handle=/u01/backup/df_RACTEST_5_1.bak tag=TAG20131209T133411
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 09-DEC-13

RMAN> recover database;

Starting recover at 09-DEC-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 52 is already on disk as file +FLASH/ractest/archivelog/2013_12_09/thread_1_seq_52.268.833722559
archived log for thread 1 with sequence 53 is already on disk as file +FLASH/ractest/onlinelog/group_1.257.833718845
archived log for thread 2 with sequence 5 is already on disk as file +FLASH/ractest/archivelog/2013_12_09/thread_2_seq_5.269.833722561
archived log for thread 2 with sequence 6 is already on disk as file +FLASH/ractest/onlinelog/group_4.260.833721811
archived log file name=+FLASH/ractest/archivelog/2013_12_09/thread_1_seq_52.268.833722559 thread=1 sequence=52
archived log file name=+FLASH/ractest/archivelog/2013_12_09/thread_2_seq_5.269.833722561 thread=2 sequence=5
archived log file name=+FLASH/ractest/onlinelog/group_1.257.833718845 thread=1 sequence=53
archived log file name=+FLASH/ractest/onlinelog/group_4.260.833721811 thread=2 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-DEC-13

RMAN> alter database open resetlogs;

database opened

11. Start the cluster service on the second node

root@dbrac02mgt)# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

Oracle 11.2.0.3 RAC DB backup failing with the following error:

Error

Mon Oct 07 01:03:28 2013
Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_ora_25286.trc:
ORA-00245: control file backup failed; target is likely on a local file system

Cause

This is down to the snapshot controlfile being on a local disk and it needs to be on a shared disk for all of the cluster in our case we need to put it on our +FLASH ASM diskgroup

FIX

So to fix the issue we need to configure the SNAPSHOT CONTROLFILE to the +FLASH ASM diskgroup in RMAN, this is done as follows:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/dbs/snapcf_TESTDB1.f'; # default

RMAN> configure snapshot controlfile name to '+FLASH/snapcf_TESTDB1.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FLASH/snapcf_TESTDB1.f';
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> show all ;

RMAN configuration parameters for database with db_unique_name TESTDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FLASH/snapcf_TESTDB1.f';

Run a backup and check the file is created in the +FLASH diskgroup using asmcmd

oracle@testdb01tst /export/home/oracle $ asmcmd
ASMCMD> ls
DATA/
FLASH/
ASMCMD> cd FLASH/
ASMCMD> ls -l
Type         Redund  Striped  Time             Sys  Name
Y    TESTDB/
Y    TESTDBTST/
N    snapcf_testdb1.f => +FLASH/TESTDB/CONTROLFILE/Backup.302.828871329

Useful RMAN Commands

Posted: April 18, 2012 in RMAN

Show all of the RMAN parameters for the database

RMAN> show all;

Configure the disk channel to the format /backup/ora_df%t_s%s_s%p’

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;

Configure the disk channel to the format +FLASHDG

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘+FLASHDG’;

Backup the database using the standard RMAN parameters

RMAN> backup database;

List all of the current backups for the database

RMAN> list backup;

 

List Backup summary

RMAN> list summary;

 

Crosscheck backups to see if they still exist

RMAN> crosscheck backup;

 

Crosscheck archive logs to see if they still exist

RMAN> crosscheck archivelog all;

 

Remove any expired backup

RMAN> delete expired backup;

Remove any expired archivelos

RMAN> delete expired archivelog;

Backup the database and archivelogs using the standard RMAN parameters and delete the archivelogs after the backup

RMAN> backup archivelog all delete input;

And

RMAN> run

2> {

3> backup database;

4> backup  archivelog all delete input;

5> }

Report of files that need backup due to unrecoverable operations

RMAN> report unrecoverable;

Report the obsolete backups and archivelogs

RMAN> report obsolete;

 

Delete the obsolete backups and archivelogs

RMAN> delete obsolete;

 

List all of the archivelogs

RMAN> list archivelog all;

Delete a specifed backupset

RMAN> delete backupset 1344

Sample backup command

RMAN> run

2> {

3> backup database;

4> sql “alter system switch logfile”;

5> backup archivelog all delete input;

6> sql “alter database backup controlfile to trace”;

7> }

Backup archive logs from scn 680

RMAN> run

2> {

3> ALLOCATE CHANNEL d1 DEVICE TYPE disk

4> FORMAT = ‘/app/oracle/admin/TEST/backup/df_%d_%s_%p.bak’;

5> backup as compressed backupset archivelog from sequence 680;

6> }

Backup archive logs and delete them up to scn 679

RMAN> run

2> {

3> ALLOCATE CHANNEL d1 DEVICE TYPE disk

4> FORMAT = ‘/app/oracle/admin/TEST/backup/df_%d_%s_%p.bak’;

5> backup as compressed backupset archivelog high sequence 679 delete input;

6> }

 

Point in time recovery

RMAN> run{

2> set until time “to_date(’05/07/11 09:00:00′ , ‘dd/mm/yy hh24:mi:ss’)”;

3> restore database;

4> recover database;

5> alter database open resetlogs;}

Delete archive logs up to scn 650

RMAN> delete archivelog high sequence 650;

list archivelogs from yesterday.

RMAN> list archivelog time between “sysdate -1” and “sysdate”

Delete archivelogs backed up 1 time to disk

RMAN> delete archive log all backed up 1 times to disk;

Delete backups older than 7 days

RMAN> delete backup completed before ‘sysdate -7’;

Restore archivelog between to scn numbers

RMAN> restore archivelog scn between 547484587 and 547662805;

Check For Logical And Physical Block Corruption

RMAN> backup check logical validate database;

RMAN Restore Validate

Posted: March 29, 2012 in RMAN

The RMAN restore validate command can be very useful to test the integrity of your backups on tape or disk; it is sometimes not feasible to test a restore so we can at least run the restore validate as a test. We can check for database, controlfile, spfile or archivelogs.

Restore database test

RMAN> restore database validate;
Starting restore at 28-03-2012 14:12:13
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_02_22/o1
_mf_nnnd0_TAG20120222T102840_7n9jtv3m_.bkp
channel ORA_DISK_1: piece handle=/wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_02_22/o1_mf_nnnd0_TAG20120222T102840_7n9jtv3m_.bkp tag=TAG20120222T102840
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_02_27/o1_mf_nnnd1_TAG20120227T210012_7nqvpykl_.bkp
channel ORA_DISK_1: piece handle=/wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_02_27/o1_mf_nnnd1_TAG20120227T210012_7nqvpykl_.bkp tag=TAG20120227T210012
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
Finished restore at 28-03-2012 14:13:02

Restore selected archivelogs

RMAN> restore archivelog from logseq 290 until logseq 300 validate;
Starting restore at 28-03-2012 14:15:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece /wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_03_06/o1_mf_annnn_TAG20120306T210209_7odytpz4_.bkp
channel ORA_DISK_1: piece handle=/wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_03_06/o1_mf_annnn_TAG20120306T210209_7odytpz4_.bkp tag=TAG20120306T210209
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 28-03-2012 14:15:53
RMAN>

Restore Controlfile test

RMAN> restore controlfile validate;
Starting restore at 28-03-2012 14:13:12
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /wload/test1/app/oracle/flash_recovery_area/TESTDB1/autobackup/2012_03_06/o1_mf_s_777243776_7odyw21k_.bkp
channel ORA_DISK_1: piece handle=/wload/test1/app/oracle/flash_recovery_area/TESTDB1/autobackup/2012_03_06/o1_mf_s_777243776_7odyw21k_.bkp tag=TAG20120306T210256
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 28-03-2012 14:13:13

Restore spfile test

RMAN> restore spfile validate;
Starting restore at 28-03-2012 14:13:19
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /wload/test1/app/oracle/flash_recovery_area/TESTDB1/autobackup/2012_03_06/o1_mf_s_777243776_7odyw21k_.bkp
channel ORA_DISK_1: piece handle=/wload/test1/app/oracle/flash_recovery_area/TESTDB1/autobackup/2012_03_06/o1_mf_s_777243776_7odyw21k_.bkp tag=TAG20120306T210256
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 28-03-2012 14:13:20

Point in time recovery test

RMAN> run {
2> set until time “to_date(’28/03/12 13:00:00′ , ‘dd/mm/yy hh24:mi:ss’)”;
3> restore database validate;
4> }

executing command: SET until clause

Starting restore at 28-03-2012 14:06:15

using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_02_22/o1_mf_nnnd0_TAG20120222T102840_7n9jtv3m_.bkp
channel ORA_DISK_1: piece handle=/wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_02_22/o1_mf_nnnd0_TAG20120222T102840_7n9jtv3m_.bkp tag=TAG20120222T102840
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_02_27/o1_mf_nnnd1_TAG20120227T210012_7nqvpykl_.bkp
channel ORA_DISK_1: piece handle=/wload/test1/app/oracle/flash_recovery_area/TESTDB1/backupset/2012_02_27/o1_mf_nnnd1_TAG20120227T210012_7nqvpykl_.bkp tag=TAG20120227T210012
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
Finished restore at 28-03-2012 14:07:04

RMAN>


Running an RMAN backup or backup validate will check for logical and physical corruption on the data blocks, with 11g we get more output back from the run listing if there are any blocks marked corrupt.

Output from checking a test database for corruptions, in the test run we have no issues

RMAN> backup check logical validate database;

Starting backup at 28-03-2012 13:44:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/wload/test1/app/oracle/u01/oradata/testdb1/system.dbf
input datafile file number=00002 name=/wload/test1/app/oracle/u01/oradata/testdb1/sysaux.dbf
input datafile file number=00003 name=/wload/test1/app/oracle/u01/oradata/testdb1/undo01.dbf
input datafile file number=00004 name=/wload/test1/app/oracle/u01/oradata/testdb1/users.dbf
input datafile file number=00005 name=/wload/test1/app/oracle/u01/oradata/testdb1/tools.dbf
input datafile file number=00006 name=/wload/test1/app/oracle/u04/oradata/testdb1/COLLECT_DATA.dbf
input datafile file number=00007 name=/wload/test1/app/oracle/u04/oradata/testdb1/COLLECT_INDX.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:06
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
1 OK 0 85266 131072 654296
File Name: /wload/test1/app/oracle/u01/oradata/testdb1/system.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 35469
Index 0 7169
Other 0 3168

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
2 OK 0 120677 131072 654298
File Name: /wload/test1/app/oracle/u01/oradata/testdb1/sysaux.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 3174
Index 0 2624
Other 0 4597

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
3 OK 0 81999 131072 654298
File Name: /wload/test1/app/oracle/u01/oradata/testdb1/undo01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 49073

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 OK 0 4521 54176 647709
File Name: /wload/test1/app/oracle/u01/oradata/testdb1/users.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 49079
Index 0 0
Other 0 576

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 OK 0 25473 25600 18997
File Name: /wload/test1/app/oracle/u01/oradata/testdb1/tools.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 127

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
6 OK 0 6273 6400 350188
File Name: /wload/test1/app/oracle/u04/oradata/testdb1/COLLECT_DATA.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 127

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
7 OK 0 6273 6400 350467
File Name: /wload/test1/app/oracle/u04/oradata/testdb1/COLLECT_INDX.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 127

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
———— —— ————– —————
SPFILE OK 0 2
Control File OK 0 752
Finished backup at 28-03-2012 13:46:28

RMAN>