Validate and recover database irrecoverable data loss (Incomplete recovery)

December 18 2011 by egfh in Uncategorized |Comments Off on Validate and recover database irrecoverable data loss (Incomplete recovery)

On this scenario, we’ve been very bad guys and have no good controlfile backup from a recent archive backup, and also had lost some archivelog (let’s guess that the archivelogs lived on the FRA and all the diskgroups where corrupted). That means we have lost some data in time, but on some recovery scenarios, that means better than having nothing, for instance, on non production environments.

This database doesn’t fit on a tipical development environment, though, as it’s on archivelog mode. Anyway, it’s a perfectly valid example as the only valid database backup is an old full backup, and that’s definitely a damn development scenario. The SAN storage is quite old, did something weird and completely corrupted all development data, and all we have is an old last week’s full backup…WTH…

How we get to know this? After recovering the hardware outage, the database starts, but the rman validate command finds data corruption:

[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 14 21:36:47 2011

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

connected to target database: RACORA (DBID=4104435745)

RMAN> validate database;

Starting validate at 14-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 instance=RACORA2 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 12/14/2011 21:37:21
ORA-01135: file 3 accessed for DML/query is offline
ORA-01110: data file 3: '+DATA/racora/datafile/undotbs1.269.729858657'
RMAN-06031: could not translate database keyword

RMAN> 

Recovery Manager complete.

Good! Then, we’ll see what excatly means, as the database started and open with no erros…

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 14 21:37:44 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select FILE_NAME,STATUS from DBA_DATA_FILES;

FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
+DATA/racora/datafile/users.268.729858659
AVAILABLE

+DATA/racora/datafile/undotbs1.269.729858657
AVAILABLE

+DATA/racora/datafile/sysaux.261.729858653
AVAILABLE


FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
+DATA/racora/datafile/system.260.729858643
AVAILABLE

+DATA/racora/datafile/example.263.729858985
AVAILABLE

+DATA/racora/datafile/undotbs2.262.729859525
AVAILABLE


6 rows selected.

The DBA_DATAF_FILES table reports that all datafiles are online and available… what’s going on here?

SQL> select * from V$DATAFILE;
select * from V$DATAFILE
              *
ERROR at line 1:
ORA-01135: file 3 accessed for DML/query is offline
ORA-01110: data file 3: '+DATA/racora/datafile/undotbs1.269.729858657'

Now we see some light (or to day better, darkness). We had some data corruption on the SAN storage outage…

We try to restore only that datafile with RMAN:

SQL> alter database datafile 3 OFFLINE;

Database altered.

SQL> recover datafile 3;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 3: '+DATA/racora/datafile/undotbs1.269.729858657'
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '+DATA/racora/datafile/undotbs1.269.729858657'
ORA-01210: data file header is media corrupt

Ouch! OK, this is a controlled error, we can bypass it by removing the datafile manually and then performing the restore:

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 14 21:42:01 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediatE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; 
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size		    2214936 bytes
Variable Size		  289407976 bytes
Database Buffers	  226492416 bytes
Redo Buffers		    3821568 bytes
Database mounted.

[oracle@rac2 ~]$ export ORACLE_SID=+ASM2
[oracle@rac2 ~]$ export ORACLE_HOME="/u01/app/11.2.0/grid"
[oracle@rac2 ~]$ asmcmd rm '+DATA/racora/datafile/undotbs1.269.729858657'

And we’re ready to keep on the restore and recovery procedure:

[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 14 22:15:55 2011

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

connected to target database: RACORA (DBID=4104435745, not open)

RMAN> restore database;

Starting restore at 14-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=RACORA2 device type=DISK

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/racora/datafile/system.260.729858643
channel ORA_DISK_1: restoring datafile 00002 to +DATA/racora/datafile/sysaux.261.729858653
channel ORA_DISK_1: restoring datafile 00003 to +DATA/racora/datafile/undotbs1.269.729858657
channel ORA_DISK_1: restoring datafile 00004 to +DATA/racora/datafile/users.268.729858659
channel ORA_DISK_1: restoring datafile 00005 to +DATA/racora/datafile/example.263.729858985
channel ORA_DISK_1: restoring datafile 00006 to +DATA/racora/datafile/undotbs2.262.729859525
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backups/datafiles/RACORA_20110708_s4_s1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backups/datafiles/RACORA_20110708_s4_s1 tag=TAG20110708T164017
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:57:31
Finished restore at 14-DEC-11

RMAN> recover database;

Starting recover at 15-DEC-11
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 45 is already on disk as file +DATA/racora/archivelog/1_45_729858920.dbf
archived log for thread 1 with sequence 46 is already on disk as file +DATA/racora/archivelog/1_46_729858920.dbf
archived log for thread 1 with sequence 47 is already on disk as file +DATA/racora/archivelog/1_47_729858920.dbf
archived log for thread 1 with sequence 48 is already on disk as file +DATA/racora/archivelog/1_48_729858920.dbf
archived log for thread 1 with sequence 49 is already on disk as file +DATA/racora/archivelog/1_49_729858920.dbf
archived log for thread 1 with sequence 50 is already on disk as file +DATA/racora/archivelog/1_50_729858920.dbf
archived log for thread 1 with sequence 51 is already on disk as file +DATA/racora/archivelog/1_51_729858920.dbf
archived log for thread 1 with sequence 52 is already on disk as file +DATA/racora/archivelog/1_52_729858920.dbf
archived log for thread 1 with sequence 53 is already on disk as file +DATA/racora/archivelog/1_53_729858920.dbf
archived log for thread 1 with sequence 54 is already on disk as file +DATA/racora/archivelog/1_54_729858920.dbf
archived log for thread 1 with sequence 55 is already on disk as file +DATA/racora/archivelog/1_55_729858920.dbf
archived log for thread 1 with sequence 56 is already on disk as file +DATA/racora/archivelog/1_56_729858920.dbf
archived log for thread 1 with sequence 57 is already on disk as file +DATA/racora/archivelog/1_57_729858920.dbf
archived log for thread 1 with sequence 58 is already on disk as file +DATA/racora/archivelog/1_58_729858920.dbf
archived log for thread 1 with sequence 59 is already on disk as file +DATA/racora/archivelog/1_59_729858920.dbf
archived log for thread 1 with sequence 60 is already on disk as file +DATA/racora/archivelog/1_60_729858920.dbf
archived log for thread 1 with sequence 61 is already on disk as file +DATA/racora/archivelog/1_61_729858920.dbf
archived log for thread 1 with sequence 62 is already on disk as file +DATA/racora/archivelog/1_62_729858920.dbf
archived log for thread 1 with sequence 63 is already on disk as file +DATA/racora/archivelog/1_63_729858920.dbf
archived log for thread 1 with sequence 64 is already on disk as file +DATA/racora/archivelog/1_64_729858920.dbf
archived log for thread 1 with sequence 65 is already on disk as file +DATA/racora/archivelog/1_65_729858920.dbf
archived log for thread 1 with sequence 66 is already on disk as file +DATA/racora/archivelog/1_66_729858920.dbf
archived log for thread 1 with sequence 67 is already on disk as file +DATA/racora/archivelog/1_67_729858920.dbf
archived log for thread 1 with sequence 68 is already on disk as file +DATA/racora/archivelog/1_68_729858920.dbf
archived log for thread 1 with sequence 69 is already on disk as file +DATA/racora/archivelog/1_69_729858920.dbf
archived log for thread 2 with sequence 24 is already on disk as file +DATA/racora/archivelog/2_24_729858920.dbf
archived log for thread 2 with sequence 25 is already on disk as file +DATA/racora/archivelog/2_25_729858920.dbf
archived log for thread 2 with sequence 26 is already on disk as file +DATA/racora/archivelog/2_26_729858920.dbf
archived log for thread 2 with sequence 27 is already on disk as file +DATA/racora/archivelog/2_27_729858920.dbf
archived log for thread 2 with sequence 28 is already on disk as file +DATA/racora/archivelog/2_28_729858920.dbf
archived log for thread 2 with sequence 29 is already on disk as file +DATA/racora/archivelog/2_29_729858920.dbf
archived log for thread 2 with sequence 30 is already on disk as file +DATA/racora/archivelog/2_30_729858920.dbf
archived log for thread 2 with sequence 31 is already on disk as file +DATA/racora/archivelog/2_31_729858920.dbf
archived log for thread 2 with sequence 32 is already on disk as file +DATA/racora/archivelog/2_32_729858920.dbf
archived log for thread 2 with sequence 33 is already on disk as file +DATA/racora/archivelog/2_33_729858920.dbf
archived log for thread 2 with sequence 34 is already on disk as file +DATA/racora/archivelog/2_34_729858920.dbf
archived log for thread 2 with sequence 35 is already on disk as file +DATA/racora/archivelog/2_35_729858920.dbf
archived log for thread 2 with sequence 36 is already on disk as file +DATA/racora/archivelog/2_36_729858920.dbf
archived log for thread 2 with sequence 37 is already on disk as file +DATA/racora/archivelog/2_37_729858920.dbf
archived log for thread 2 with sequence 38 is already on disk as file +DATA/racora/archivelog/2_38_729858920.dbf
archived log for thread 2 with sequence 39 is already on disk as file +DATA/racora/archivelog/2_39_729858920.dbf
archived log for thread 2 with sequence 40 is already on disk as file +DATA/racora/archivelog/2_40_729858920.dbf
archived log for thread 2 with sequence 41 is already on disk as file +DATA/racora/archivelog/2_41_729858920.dbf
archived log for thread 2 with sequence 42 is already on disk as file +DATA/racora/archivelog/2_42_729858920.dbf
archived log for thread 2 with sequence 43 is already on disk as file +DATA/racora/archivelog/2_43_729858920.dbf
archived log for thread 2 with sequence 44 is already on disk as file +DATA/racora/archivelog/2_44_729858920.dbf
archived log for thread 2 with sequence 45 is already on disk as file /u02/oradata/ORASTB/archivelogs/2_45_729858920.dbf
archived log for thread 2 with sequence 46 is already on disk as file +DATA/racora/archivelog/2_46_729858920.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=32
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backups/datafiles/RACORA_20110708_s5_s1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backups/datafiles/RACORA_20110708_s5_s1 tag=TAG20110708T164226
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u02/oradata/ORASTB/archivelogs/1_32_729858920.dbf thread=1 sequence=32

If after all of this, your database still doesn’t opens, saying that datafile 1 (System tablespace datafile) needs recovery, then we’ll have to dismiss all data from last controlfile backup.

As we said, all we have is a full backup including controlfile. We’ll restore the controlfile from that backup and recover the database to that controlfile’s SCN.

First of all, we recover the controlfile with the database started in “nomount” mode:

[oracle@rac1 ~]$ ls -l /u01/app/oracle/backups/controlspfile/
total 54424
-rw-r----- 1 oracle oinstall 18579456 jul  8 16:37 c-4104435745-20110708-00
-rw-r----- 1 oracle oinstall 18579456 jul  8 16:42 c-4104435745-20110708-01
-rw-r----- 1 oracle oinstall 18497536 jul  8 16:42 snapcf_RACORA1.f
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 18 13:26:05 2011

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

connected to target database: RACORA (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/backups/controlspfile/c-4104435745-20110708-01';

Starting restore at 18-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 instance=RACORA1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/racora/controlfile/current.267.729858913
Finished restore at 18-DEC-11

Then, we restore and recover the database:

RMAN> mount database;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 18-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 instance=RACORA1 device type=DISK

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/racora/datafile/system.260.729858643
channel ORA_DISK_1: restoring datafile 00002 to +DATA/racora/datafile/sysaux.261.729858653
channel ORA_DISK_1: restoring datafile 00003 to +DATA/racora/datafile/undotbs1.269.729858657
channel ORA_DISK_1: restoring datafile 00004 to +DATA/racora/datafile/users.268.729858659
channel ORA_DISK_1: restoring datafile 00005 to +DATA/racora/datafile/example.263.729858985
channel ORA_DISK_1: restoring datafile 00006 to +DATA/racora/datafile/undotbs2.262.729859525
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backups/datafiles/RACORA_20110708_s4_s1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backups/datafiles/RACORA_20110708_s4_s1 tag=TAG20110708T164017
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:46
Finished restore at 18-DEC-11

RMAN> 

Recovery Manager complete.
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 18 13:39:26 2011

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

connected to target database: RACORA (DBID=4104435745, not open)

RMAN> recover database;

Starting recover at 18-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 instance=RACORA1 device type=DISK

starting media recovery

archived log for thread 1 with sequence 32 is already on disk as file +DATA/racora/archivelog/1_32_729858920.dbf
archived log for thread 1 with sequence 68 is already on disk as file +DATA/racora/onlinelog/group_2.265.729858931
archived log for thread 1 with sequence 69 is already on disk as file +DATA/racora/onlinelog/group_1.266.729858921
archived log for thread 2 with sequence 46 is already on disk as file +DATA/racora/onlinelog/group_4.258.729859647
archived log for thread 2 with sequence 47 is already on disk as file +DATA/racora/onlinelog/group_3.259.729859639
archived log file name=+DATA/racora/archivelog/1_32_729858920.dbf thread=1 sequence=32
archived log file name=+DATA/racora/archivelog/1_33_729858920.dbf thread=1 sequence=33
archived log file name=+DATA/racora/archivelog/1_34_729858920.dbf thread=1 sequence=34
archived log file name=+DATA/racora/archivelog/2_14_729858920.dbf thread=2 sequence=14
archived log file name=+DATA/racora/archivelog/2_15_729858920.dbf thread=2 sequence=15
archived log file name=+DATA/racora/archivelog/1_35_729858920.dbf thread=1 sequence=35
archived log file name=+DATA/racora/archivelog/2_16_729858920.dbf thread=2 sequence=16
archived log file name=+DATA/racora/archivelog/2_17_729858920.dbf thread=2 sequence=17
archived log file name=+DATA/racora/archivelog/1_36_729858920.dbf thread=1 sequence=36
archived log file name=+DATA/racora/archivelog/2_18_729858920.dbf thread=2 sequence=18
archived log file name=+DATA/racora/archivelog/1_37_729858920.dbf thread=1 sequence=37
archived log file name=+DATA/racora/archivelog/2_19_729858920.dbf thread=2 sequence=19
archived log file name=+DATA/racora/archivelog/2_20_729858920.dbf thread=2 sequence=20
archived log file name=+DATA/racora/archivelog/1_38_729858920.dbf thread=1 sequence=38
archived log file name=+DATA/racora/archivelog/2_21_729858920.dbf thread=2 sequence=21
archived log file name=+DATA/racora/archivelog/1_39_729858920.dbf thread=1 sequence=39
archived log file name=+DATA/racora/archivelog/1_40_729858920.dbf thread=1 sequence=40
archived log file name=+DATA/racora/archivelog/2_22_729858920.dbf thread=2 sequence=22
archived log file name=+DATA/racora/archivelog/2_23_729858920.dbf thread=2 sequence=23
archived log file name=+DATA/racora/archivelog/1_41_729858920.dbf thread=1 sequence=41
archived log file name=+DATA/racora/archivelog/1_42_729858920.dbf thread=1 sequence=42
archived log file name=+DATA/racora/archivelog/1_43_729858920.dbf thread=1 sequence=43
archived log file name=+DATA/racora/archivelog/1_44_729858920.dbf thread=1 sequence=44
archived log file name=+DATA/racora/archivelog/2_24_729858920.dbf thread=2 sequence=24
archived log file name=+DATA/racora/archivelog/1_45_729858920.dbf thread=1 sequence=45
archived log file name=+DATA/racora/archivelog/2_25_729858920.dbf thread=2 sequence=25
archived log file name=+DATA/racora/archivelog/1_46_729858920.dbf thread=1 sequence=46
archived log file name=+DATA/racora/archivelog/2_26_729858920.dbf thread=2 sequence=26
archived log file name=+DATA/racora/archivelog/1_47_729858920.dbf thread=1 sequence=47
archived log file name=+DATA/racora/archivelog/2_27_729858920.dbf thread=2 sequence=27
archived log file name=+DATA/racora/archivelog/1_48_729858920.dbf thread=1 sequence=48
archived log file name=+DATA/racora/archivelog/1_49_729858920.dbf thread=1 sequence=49
archived log file name=+DATA/racora/archivelog/2_28_729858920.dbf thread=2 sequence=28
archived log file name=+DATA/racora/archivelog/1_50_729858920.dbf thread=1 sequence=50
archived log file name=+DATA/racora/archivelog/2_29_729858920.dbf thread=2 sequence=29
archived log file name=+DATA/racora/archivelog/1_51_729858920.dbf thread=1 sequence=51
archived log file name=+DATA/racora/archivelog/2_30_729858920.dbf thread=2 sequence=30
archived log file name=+DATA/racora/archivelog/1_52_729858920.dbf thread=1 sequence=52
archived log file name=+DATA/racora/archivelog/2_31_729858920.dbf thread=2 sequence=31
archived log file name=+DATA/racora/archivelog/2_32_729858920.dbf thread=2 sequence=32
archived log file name=+DATA/racora/archivelog/1_53_729858920.dbf thread=1 sequence=53
archived log file name=+DATA/racora/archivelog/2_33_729858920.dbf thread=2 sequence=33
archived log file name=+DATA/racora/archivelog/1_54_729858920.dbf thread=1 sequence=54
archived log file name=+DATA/racora/archivelog/2_34_729858920.dbf thread=2 sequence=34
archived log file name=+DATA/racora/archivelog/1_55_729858920.dbf thread=1 sequence=55
archived log file name=+DATA/racora/archivelog/2_35_729858920.dbf thread=2 sequence=35
archived log file name=+DATA/racora/archivelog/1_56_729858920.dbf thread=1 sequence=56
archived log file name=+DATA/racora/archivelog/1_57_729858920.dbf thread=1 sequence=57
archived log file name=+DATA/racora/archivelog/2_36_729858920.dbf thread=2 sequence=36
archived log file name=+DATA/racora/archivelog/2_37_729858920.dbf thread=2 sequence=37
archived log file name=+DATA/racora/archivelog/1_58_729858920.dbf thread=1 sequence=58
archived log file name=+DATA/racora/archivelog/2_38_729858920.dbf thread=2 sequence=38
archived log file name=+DATA/racora/archivelog/2_39_729858920.dbf thread=2 sequence=39
archived log file name=+DATA/racora/archivelog/1_59_729858920.dbf thread=1 sequence=59
archived log file name=+DATA/racora/archivelog/1_60_729858920.dbf thread=1 sequence=60
archived log file name=+DATA/racora/archivelog/2_40_729858920.dbf thread=2 sequence=40
archived log file name=+DATA/racora/archivelog/1_61_729858920.dbf thread=1 sequence=61
archived log file name=+DATA/racora/archivelog/1_62_729858920.dbf thread=1 sequence=62
archived log file name=+DATA/racora/archivelog/2_41_729858920.dbf thread=2 sequence=41
archived log file name=+DATA/racora/archivelog/1_63_729858920.dbf thread=1 sequence=63
archived log file name=+DATA/racora/archivelog/2_42_729858920.dbf thread=2 sequence=42
archived log file name=+DATA/racora/archivelog/2_43_729858920.dbf thread=2 sequence=43
archived log file name=+DATA/racora/archivelog/1_64_729858920.dbf thread=1 sequence=64
archived log file name=+DATA/racora/archivelog/1_65_729858920.dbf thread=1 sequence=65
archived log file name=+DATA/racora/archivelog/1_66_729858920.dbf thread=1 sequence=66
archived log file name=+DATA/racora/archivelog/2_44_729858920.dbf thread=2 sequence=44
archived log file name=+DATA/racora/archivelog/2_45_729858920.dbf thread=2 sequence=45
archived log file name=+DATA/racora/archivelog/1_67_729858920.dbf thread=1 sequence=67
archived log file name=+DATA/racora/onlinelog/group_2.265.729858931 thread=1 sequence=68
archived log file name=+DATA/racora/onlinelog/group_4.258.729859647 thread=2 sequence=46
archived log file name=+DATA/racora/onlinelog/group_1.266.729858921 thread=1 sequence=69
archived log file name=+DATA/racora/onlinelog/group_3.259.729859639 thread=2 sequence=47
media recovery complete, elapsed time: 00:07:31
Finished recover at 18-DEC-11

RMAN>

And now we open the database with the resetlogs option:

[oracle@rac1 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 18 13:48:17 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter database open resetlogs;

Database altered.

Update Oracle Enterprise linux with the Oracle public Yum repositories

October 31 2011 by egfh in Uncategorized |Comments Off on Update Oracle Enterprise linux with the Oracle public Yum repositories

To make a successfull yum update, you must enable ALL versions in the .repo file in the /etc/yum.repos.d directory, otherwise, you’ll get “Dependency Missing” problems when updating.

It is not mandatory (I guess, have not tryed) but I whouldn’t recommend update straing from a low release (eg. 5.1) straight to another version. I consider it’s safer to update to the last update in the version before updating to the next version (but as I say, it’s just my opinion).

The Oracle public Yum repositories are available for download and instructions to use them at: http://public-yum.oracle.com/

Create your own RMAN backup scripts from the scratch

July 8 2011 by egfh in Backup and Recovery |2 Comments

This guide is intended for Oracle Database medium/high level administrators to have a reference for creating RMAN scripts from the scratch, based on retention policies for physical backups on databases in archive log mode.

[note]If we’re planning to backup an Oracle RAC database, we’d like to have the scripts we’ll create replicated in all the RAC nodes and set the backup storage location to a shared ocfs2/ASMFS disk so we can have “High Availability Oracle RAC RMAN Backups”.[/note]

First of all, if not already, we’ll “change the database into archive log mode”.

Then, we connect using RMAN to the Oracle database instance that we are going to use for the backup:
 

[oracle@rac1 ~]$ rman target / nocatalog 

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 5 13:35:50 2011 

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

connected to target database: RACORA (DBID=4104435745) 
using target database control file instead of recovery catalog 

RMAN> 

RMAN> show all; 

RMAN configuration parameters for database with db_unique_name RACORA are: 

CONFIGURE BACKUP OPTIMIZATION OFF; # default 
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/db_1/dbs/snapcf_RACORA1.f'; # default

From which we’ll keep the following lines for making our scripts configuration file:
 

# how many days we want to keep backups on disk before RMAN deletes them
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default 

# enable/disable automatic backup of spfile and controlfile
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default 

# string with the destination of the automatic backup of the spfile and controlfile
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default 

# Define parallelism degree by an integer.(Only usable on Enterprise Edition)
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default 

# Set the compression program. (Why not to use bz2 for compression? ;-)
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default 

# Set the destination of the snapshot control file to be kept on the backups. 
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_RACORA1.f'; # default

Now we’ll choose a backup destination for datafile backups, archived logs backups and the scripts and configuration file.

We really want the backup script outside our $ORACLE_HOME for if we need to reinstall our Oracle binaries (aka. In case of a downgrade needed) won’t have to worry or remember about them.

But it really can be a directory on our $ORACLE_BASE, so we’ll choose “$ORACLE_BASE/backups/” for the backup root path.

[warning]The paths as well the scripts themselves have to be owned by the system’s oracle user.[/warning]

[oracle@rac1 ~]$ mkdir $ORACLE_BASE/backups
[oracle@rac1 ~]$ mkdir /u01/app/oracle/backups/
[oracle@rac1 ~]$ mkdir $ORACLE_BASE/backups/archivelogs
[oracle@rac1 ~]$ mkdir $ORACLE_BASE/backups/datafiles
[oracle@rac1 ~]$ mkdir $ORACLE_BASE/backups/controlspfile

On the other hand, we really want the backup scripts to be placed on or near our backup destination, and that’s cause the disk filesystem backup will keep the copy to tape of the backup sets as well as the copy of the version of the scripts on the moment the backup is launched. This can be very useful to revert failures on the scripts after a backups fail cause of unwanted changes on the backup scripts.
 

[oracle@rac1 ~]$ mkdir $ORACLE_BASE/backups/scripts
[oracle@rac1 ~]$ mkdir $ORACLE_BASE/backups/scripts/conf

Then, we create a configuration file for the script pasting the rman parameters as we collected them before, but changing the parameters with the information we already know or have decided:
 

[oracle@rac1 ~]$ vi /u01/app/oracle/backups/scripts/conf/rmanbackup.cfg

# how many days we want to keep backups on disk before RMAN deletes them
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; 

# enable/disable automatic backup of spfile and controlfile
CONFIGURE CONTROLFILE AUTOBACKUP ON; 

# string with the destination of the automatic backup of the spfile and controlfile
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$ORACLE_BASE/backups/controlspfile
/%F'; 

# Define parallelism degree by an integer (only usable on Enterprise Edition), and if the backupset is going to be compressed.
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET; 

# Set the compression program. (Why not to use bz2 for compression? ;-)
CONFIGURE COMPRESSION ALGORITHM 'BASIC'; 
# Set the destination of the snapshot control file to be kept on the backups. 
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$ORACLE_BASE/backups/controlspfile
/snapcf_RACORA1.f';

Now, we need to add some extra parameters to the configuration file to add the destination of the backupsets for which RMAN will launch the channels we set up and the maximum size of each backup piece on the backupset, which we’ll set to 4GB, as it’s a handy file size for a decent system:
 

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$ORACLE_BASE/backups/datafiles
/%d_%T_s%s_s%p' MAXPIECESIZE 4G;

[warning]If your backup destination is a vfat(fat32) partition (WindowsXP-Windows95) the MAXPIECESIZE should be set to 2GB or the backup pieces will get corrupted.[/warning]

Where the parameter strings in the format stands for:
 

%d  The name of the database. 
%T  The year, month, and day (YYYYMMDD)
%s  The backup set number.
%p  The piece number within the backup set.

Now we have our configuration file ready, but it contains RMAN commands, that set the options once running the RMAN tool, and we don’t really want commands running on our configuration file, do we? So we’ll save the “CONFIGURE” lines on the backup script and change them to fancy fashion variable names and their values.
 

[oracle@rac1 ~]$ cat /u01/app/oracle/backups/scripts/conf/rmanbackup.cfg > /u01/app/oracle/backups/scripts/rmanbackup.sh

The result would be something like this:
 

# how many days we want to keep backups on disk before RMAN deletes them
REDUNDANCY=1

# enable/disable automatic backup of spfile and controlfile
CONTROLFILE_AUTOBACKUP="ON"

# string with the destination of the automatic backup of the spfile and controlfile
CONTROLFILE_AUTOBACKUP_FORMAT="$ORACLE_BASE/backups/controlspfile/%F"

# Define parallelism degree by an integer (only usable on Enterprise Edition)
PARALLELISM=1 

# and if the backupset is going to be compressed.
BACKUPSET_TYPE="COMPRESSED BACKUPSET"

# Set the compression program. (Why not to use bz2 for compression? ;-)
COMPRESSION_ALGORITHM="BASIC"

# Set the destination of the snapshot control file to be kept on the backups. 
SNAPSHOT_CONTROLFILE="$ORACLE_BASE/backups/controlspfile/snapcf_RACORA1.f"

DATAFILES_DEST="$ORACLE_BASE/backups/datafiles/%d_%T_s%s_s%p" 
ARCHIVELOGS_DEST="$ORACLE_BASE/backups/archivelogs/%d_%T_s%s_s%p" 

MAXPIECESIZE="4G"

Now, we open the script we just saved before with the “CONFIGURE” lines and swap the parameter with the variable names in the configuration file:
 

CONFIGURE RETENTION POLICY TO REDUNDANCY $REDUNDANCY;
CONFIGURE CONTROLFILE AUTOBACKUP $CONTROLFILE_AUTOBACKUP;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$CONTROLFILE_AUTOBACKUP_FORMAT';       
CONFIGURE DEVICE TYPE DISK PARALLELISM $PARALLELISM BACKUP TYPE TO $BACKUPSET_TYPE;
CONFIGURE COMPRESSION ALGORITHM '$COMPRESSION_ALGORITHM';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$SNAPSHOT_CONTROLFILE';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$DATAFILES_DEST' MAXPIECESIZE $MAXPIECESIZE;

We test the script and configuration file. To do that, first we need to complete the script loading the configuration file and connecting to RMAN so we add the following at the beginning of the script:
 

#!/bin/bash

source ~/.bash_profile # $ORACLE_HOME and $ORACLE_SID should be exported here 

source $ORACLE_BASE/backups/scripts/conf/rmanbackup.cfg

rman target / nocatalog <<EOF

...
EOF

Notice that new parameters have appeared, so we’ll need to add them to our configuration file and substitute them as needed.

Now we’ve got the script and configuration file ready to test them. This is how they look as they are:
 

[oracle@rac1 ~]$ cat /u01/app/oracle/backups/scripts/conf/rmanbackup.cfg
# how many days we want to keep backups on disk before RMAN deletes them
REDUNDANCY=1

# enable/disable automatic backup of spfile and controlfile
CONTROLFILE_AUTOBACKUP="ON"

# string with the destination of the automatic backup of the spfile and controlfile
CONTROLFILE_AUTOBACKUP_FORMAT="$ORACLE_BASE/backups/controlspfile/%F"

# Define parallelism degree by an integer (only usable on Enterprise Edition)
PARALLELISM=1 

# and if the backupset is going to be compressed.
BACKUPSET_TYPE="COMPRESSED BACKUPSET"

# Set the compression program. (Why not to use bz2 for compression? ;-)
COMPRESSION_ALGORITHM="BASIC"

# Set the destination of the snapshot control file to be kept on the backups. 
SNAPSHOT_CONTROLFILE="$ORACLE_BASE/backups/controlspfile/snapcf_RACORA1.f"

DATAFILES_DEST="$ORACLE_BASE/backups/datafiles/%d_%T_s%s_s%p" 
ARCHIVELOGS_DEST="$ORACLE_BASE/backups/archivelogs/%d_%T_s%s_s%p" 

MAXPIECESIZE="4G"

[oracle@rac1 ~]$ cat /u01/app/oracle/backups/scripts/rmanbackup.sh
#!/bin/bash

source ~/.bash_profile # $ORACLE_HOME and $ORACLE_SID should be exported here

source $ORACLE_BASE/backups/scripts/conf/rmanbackup.cfg

rman target / nocatalog <<EOF
CONFIGURE RETENTION POLICY TO REDUNDANCY $REDUNDANCY; 
CONFIGURE CONTROLFILE AUTOBACKUP $CONTROLFILE_AUTOBACKUP; 
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$CONTROLFILE_AUTOBACKUP_FORMAT'; 
CONFIGURE DEVICE TYPE DISK PARALLELISM $PARALLELISM BACKUP TYPE TO $BACKUPSET_TYPE; 
CONFIGURE COMPRESSION ALGORITHM '$COMPRESSION_ALGORITHM'; 
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$SNAPSHOT_CONTROLFILE'; 
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$DATAFILES_DEST' MAXPIECESIZE $MAXPIECESIZE;
exit;
EOF
exit

We set the script as executable and launch the script to test it:
 

[oracle@rac1 ~]$ chmod +x /u01/app/oracle/backups/scripts/rmanbackup.sh
[oracle@rac1 ~]$ /u01/app/oracle/backups/scripts/rmanbackup.sh


Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 8 14:24:24 2011

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

connected to target database: RACORA (DBID=4104435745)
using target database control file instead of recovery catalog

RMAN> 
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backups/controlspfile/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backups/controlspfile/%F';
new RMAN configuration parameters are successfully stored

RMAN> 

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> 
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backups/controlspfile/snapcf_RACORA1.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backups/controlspfile/snapcf_RACORA1.f';
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4 G;
new RMAN configuration parameters are successfully stored

RMAN> 

Recovery Manager complete.

Then, we can add the backup commands, one for the full backup. The backup commands are, as shown on “my Oracle Cookbook”:
 

backup database plus archivelog;
backup archivelog all delete input;

But we want to decide on the same script if we’re going to backup the full database (and the archivelogs to recover the datafiles up to the backup’s SCN) or just the archivelogs. So we’ll have to parse the option to the script as an argument.

The final resulting script will be:
 

#!/bin/bash

source ~/.bash_profile # $ORACLE_HOME and $ORACLE_SID should be exported here

source $ORACLE_BASE/backups/scripts/conf/rmanbackup.cfg

usage() {
        echo `basename $0`: ERROR: $* 1>&2
        echo usage: `basename $0` '[-[af]]' 1>&2
        exit 1
}

case "$1" in
        -a) BACKUP_TYPE="archivelog all format '$ARCHIVELOGS_DEST' delete input";;
        -f) BACKUP_TYPE="database plus archivelog";;
        *) usage ;; 
esac

rman target / nocatalog <<EOF
CONFIGURE RETENTION POLICY TO REDUNDANCY $REDUNDANCY; 
CONFIGURE CONTROLFILE AUTOBACKUP $CONTROLFILE_AUTOBACKUP; 
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$CONTROLFILE_AUTOBACKUP_FORMAT'; 
CONFIGURE DEVICE TYPE DISK PARALLELISM $PARALLELISM BACKUP TYPE TO $BACKUPSET_TYPE; 
CONFIGURE COMPRESSION ALGORITHM '$COMPRESSION_ALGORITHM'; 
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$SNAPSHOT_CONTROLFILE'; 
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$DATAFILES_DEST' MAXPIECESIZE $MAXPIECESIZE;
backup $BACKUP_TYPE;
exit;
EOF
exit

With the configuration file as follows:
 

[oracle@rac1 ~]$ cat /u01/app/oracle/backups/scripts/conf/rmanbackup.cfg
# how many days we want to keep backups on disk before RMAN deletes them
REDUNDANCY=1

# enable/disable automatic backup of spfile and controlfile
CONTROLFILE_AUTOBACKUP="ON"

# string with the destination of the automatic backup of the spfile and controlfile
CONTROLFILE_AUTOBACKUP_FORMAT="$ORACLE_BASE/backups/controlspfile/%F"

# Define parallelism degree by an integer (only usable on Enterprise Edition)
PARALLELISM=1 

# and if the backupset is going to be compressed.
BACKUPSET_TYPE="COMPRESSED BACKUPSET"

# Set the compression program. (Why not to use bz2 for compression? ;-)
COMPRESSION_ALGORITHM="BASIC"

# Set the destination of the snapshot control file to be kept on the backups. 
SNAPSHOT_CONTROLFILE="$ORACLE_BASE/backups/controlspfile/snapcf_RACORA1.f"

DATAFILES_DEST="$ORACLE_BASE/backups/datafiles/%d_%T_s%s_s%p" 
ARCHIVELOGS_DEST="$ORACLE_BASE/backups/archivelogs/%d_%T_s%s_s%p" 

MAXPIECESIZE="4G"

And it’s done! If you have a multi-instance RAC, you’d need to add the paramaters $ORACLE_HOME and $ORACLE_SID to the logic of the script as well for configuring RMAN to connect to a catalog database, but we’ll keep that for another posts ^_^.

Let’s test the scripts:
 

[oracle@rac1 ~]$ /u01/app/oracle/backups/scripts/rmanbackup.sh
rmanbackup.sh: ERROR:
usage: rmanbackup.sh [-[af]]

With “-a” for archivelog backup:
 

[oracle@rac1 ~]$ /u01/app/oracle/backups/scripts/rmanbackup.sh -a

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 8 16:36:42 2011

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

connected to target database: RACORA (DBID=4104435745)
using target database control file instead of recovery catalog

RMAN> 
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backups/controlspfile/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backups/controlspfile/%F';
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backups/controlspfile/snapcf_RACORA1.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backups/controlspfile/snapcf_RACORA1.f';
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4 G;
new RMAN configuration parameters are successfully stored

RMAN> 
Starting backup at 08-JUL-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=RACORA1 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=27 RECID=2 STAMP=755700601
input archived log thread=2 sequence=13 RECID=1 STAMP=755696179
input archived log thread=1 sequence=28 RECID=3 STAMP=755780576
input archived log thread=1 sequence=29 RECID=4 STAMP=755955454
input archived log thread=1 sequence=30 RECID=5 STAMP=755973413
channel ORA_DISK_1: starting piece 1 at 08-JUL-11
channel ORA_DISK_1: finished piece 1 at 08-JUL-11
piece handle=/u01/app/oracle/backups/archivelogs/RACORA_20110708_s1_s1 tag=TAG20110708T163654 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+DATA/racora/archivelog/1_27_729858920.dbf RECID=2 STAMP=755700601
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_13_729858920.dbf RECID=1 STAMP=755696179
archived log file name=+DATA/racora/archivelog/1_28_729858920.dbf RECID=3 STAMP=755780576
archived log file name=+DATA/racora/archivelog/1_29_729858920.dbf RECID=4 STAMP=755955454
archived log file name=+DATA/racora/archivelog/1_30_729858920.dbf RECID=5 STAMP=755973413
Finished backup at 08-JUL-11

Starting Control File and SPFILE Autobackup at 08-JUL-11
piece handle=/u01/app/oracle/backups/controlspfile/c-4104435745-20110708-00 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JUL-11

RMAN> 
RMAN> 

Recovery Manager complete.

And with “-f” for full database backup:
 

[oracle@rac1 ~]$ /u01/app/oracle/backups/scripts/rmanbackup.sh -f

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 8 16:40:05 2011

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

connected to target database: RACORA (DBID=4104435745)
using target database control file instead of recovery catalog

RMAN> 
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backups/controlspfile/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backups/controlspfile/%F';
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backups/controlspfile/snapcf_RACORA1.f';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backups/controlspfile/snapcf_RACORA1.f';
new RMAN configuration parameters are successfully stored

RMAN> 
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4 G;
new RMAN configuration parameters are successfully stored

RMAN> 

Starting backup at 08-JUL-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=RACORA1 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=31 RECID=6 STAMP=755973614
channel ORA_DISK_1: starting piece 1 at 08-JUL-11
channel ORA_DISK_1: finished piece 1 at 08-JUL-11
piece handle=/u01/app/oracle/backups/datafiles/RACORA_20110708_s3_s1 tag=TAG20110708T164015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JUL-11

Starting backup at 08-JUL-11
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/racora/datafile/system.260.729858643
input datafile file number=00002 name=+DATA/racora/datafile/sysaux.261.729858653
input datafile file number=00003 name=+DATA/racora/datafile/undotbs1.269.729858657
input datafile file number=00005 name=+DATA/racora/datafile/example.263.729858985
input datafile file number=00006 name=+DATA/racora/datafile/undotbs2.262.729859525
input datafile file number=00004 name=+DATA/racora/datafile/users.268.729858659
channel ORA_DISK_1: starting piece 1 at 08-JUL-11
channel ORA_DISK_1: finished piece 1 at 08-JUL-11
piece handle=/u01/app/oracle/backups/datafiles/RACORA_20110708_s4_s1 tag=TAG20110708T164017 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:07
Finished backup at 08-JUL-11

Starting backup at 08-JUL-11
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=32 RECID=7 STAMP=755973746
channel ORA_DISK_1: starting piece 1 at 08-JUL-11
channel ORA_DISK_1: finished piece 1 at 08-JUL-11
piece handle=/u01/app/oracle/backups/datafiles/RACORA_20110708_s5_s1 tag=TAG20110708T164226 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JUL-11

Starting Control File and SPFILE Autobackup at 08-JUL-11
piece handle=/u01/app/oracle/backups/controlspfile/c-4104435745-20110708-01 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JUL-11

RMAN> 
RMAN> 

Recovery Manager complete.

Convert 11gR2 RAC database to archivelog mode

July 5 2011 by egfh in Backup and Recovery |Comments Off on Convert 11gR2 RAC database to archivelog mode

This guide is intended for Oracle medium/low level administrators to have a reference for converting a non archive log Oracle 11gR2 RAC database into archive log mode.

To do this, first of all we connect to the machine as the “oracle” user and login the database as sysdba, then check the current mode of the database:
 

egfh@andromeda-ascendant:~$ ssh oracle@192.168.69.141
oracle@192.168.69.141's password:
Last login: Mon Jul  4 21:33:29 2011 from 192.168.69.187
[oracle@rac1 ~]$ sqlplus '/as sysdba' 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 5 11:11:49 2011 

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options 

SQL> archive log list
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     26
Current log sequence	       27
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Then, we create the desired ASM archive log destination:
 
[Note] We don’t really need to do this if we define on the next step just the diskgroup name (by default “+DATA”) on the SPFILE parameter “log_archive_dest_1”, but it’s very handy to know and control the location of the archive logs destination, specially if we want to play with it a bit afterwards as for creating a “Shell Scripted standby Oracle database”.[/Note]
 

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@rac1 ~]$ export ORACLE_SID='+ASM1';
[oracle@rac1 ~]$ asmcmd
ASMCMD> cd DATA/
ASMCMD> ls
ORATEST/
RACORA/
rac-scan/
ASMCMD> cd RACORA
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileRACORA.ora
ASMCMD> mkdir ARCHIVELOG
ASMCMD> cd ARCHIVELOG
ASMCMD> pwd
+DATA/RACORA/ARCHIVELOG
ASMCMD> exit

Remember to change again the ORACLE_HOME and ORACLE_SID environment variables:
 

[oracle@rac1 ~]$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
[oracle@rac1 ~]$ export ORACLE_SID=RACORA1

Now, we stop the cluster database:
 

[oracle@rac1 ~]$ srvctl stop database -d RACORA

We check that the other node/s are not running the database’s instance:
 

[oracle@rac1 ~]$ ps -ef|grep pmon
oracle    3666     1  0 10:47 ?        00:00:00 asm_pmon_+ASM1
oracle   13336 20632  0 12:02 pts/0    00:00:00 grep pmon
[oracle@rac1 ~]$ ssh rac2 ps -ef|grep pmon
oracle    3665     1  0 10:47 ?        00:00:00 asm_pmon_+ASM2

We start in nomount mode one single instance of the RAC:
 

[oracle@rac1 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 5 12:03:14 2011 

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

Connected to an idle instance. 

SQL> startup nomount;
ORACLE instance started. 

Total System Global Area  521936896 bytes
Fixed Size		    2214936 bytes
Variable Size		  318768104 bytes
Database Buffers	  197132288 bytes
Redo Buffers		    3821568 bytes

After that, we change the spfile parameters log_archive_start and log_archive_dest_1:
 

SQL> alter system set log_archive_start=TRUE SCOPE=SPFILE; 

System altered. 

SQL> alter system set log_archive_dest_1='location=+DATA/RACORA/ARCHIVELOG' SCOPE=SPFILE; 

System altered.

Now we mount the database instance:
 

SQL> alter database mount; 

Database altered.

And finally, we set the database to archive log mode:
 

SQL> alter database archivelog;
Database altered.

We can perform some checks after that…
 

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       +DATA/racora/archivelog
Oldest online log sequence     26
Next log sequence to archive   27
Current log sequence	       27
SQL> alter system switch logfile; 

System altered. 

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       +DATA/racora/archivelog
Oldest online log sequence     27
Next log sequence to archive   28
Current log sequence	       28 

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac1 ~]$ export ORACLE_SID=RACORA1
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@rac1 ~]$ export ORACLE_SID='+ASM1';
[oracle@rac1 ~]$ asmcmd
ASMCMD> cd DATA/RACORA/ARCHIVELOG
ASMCMD> ls
1_27_729858920.dbf
2011_07_05/

 

11gR2 Database Point-in-time recovery with ASM Snapshots.

March 11 2011 by egfh in Uncategorized |Comments Off on 11gR2 Database Point-in-time recovery with ASM Snapshots.

Hi all,

I was about to write a post explaining how to recover a database from corruption using the new ASMCFS snapshots feature, but as I opened the Oracle ASM Clustered File System documentation, I can read…

  • Oracle ASM is the preferred storage manager for all database files. It has been specifically designed and optimized to provide the best performance for database file types.
  • Oracle ACFS is the preferred file manager for non-database files. It is optimized for general purpose files.
  • Oracle ACFS does not support any file that can be directly stored in Oracle ASM.

Which means, I could document it, it would work (in theory) but Oracle does not support it… in other words, it’s not suitable for a production environment.

Well, we’ll have to wait till Oracle sees the advantages of having point-in-time instant physical backups for datafiles and controlfiles 🙁

Hello world!

March 4 2011 by egfh in Uncategorized |Comments Off on Hello world!

Hello all.

This is the first post of many that will be (hope so… xD) that I expect will help someone on this exciting world which is Oracle Databases administration.

Have a nice stay! (And don’t hesitate to visit My Oracle Cookbook) 🙂