Should your ADR be on Shared Storage?
Jun 6, 2012 / By Andy Klock
Until recently I didn’t think it mattered much. On a single instance database, who cares? But on RAC, I would briefly give pause, contemplate that life would be easier if the ADR was on shared storage, and then I would quickly lose interest and go back to living my life.
The benefits are:
1) All of the ADR RAC data would be centralized in one location
2) You can utilize Data Recovery Advisor
This is no longer the whole story. But before proceeding, let me mention a change that was introduced in Oracle 126.96.36.199 regarding the backing up of the control file. Starting with that version, controlfile snapshots and backups were now required to be created on shared storage. This makes sense because any instance could be writing to the backup controlfile and therefore needs access to it. (see MOS 1263621.1 for more details) Fine. I’ve updated my RMAN scripts and reconfigured the snapshot location a while ago. 188.8.131.52 has been around since 2010.
So, what does this have to do with ADR? I was on an Exadata environment with four RAC instances, when a fellow DBA created an incident package. No big deal, except part of the adrci packaging procedure was to take a backup of the controlfile and include it in the incident package. Though the package was created successfully, the controlfile never made it:
adrci> ips create package incident 42
Created package 1 based on incident id 42, correlation level typical
Snip from the alert log:
Wed May 30 16:46:13 2012
ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/diag/rdbms/demodw/demodw2/incpkg/pkg_1/seq_1/rda/TMP_DFW/DFW_BR_T15688_01_control.tmp'
Wed May 30 16:46:54 2012
Errors in file /u01/app/oracle/diag/rdbms/demodw/demodw2/trace/demodw2_ora_25314.trc:
ORA-00245: control file backup operation failed
ORA-245 signalled during: ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/diag/rdbms/demodw/demodw2/incpkg/pkg_1/seq_1/rda/TMP_DFW/DFW_BR_T15688_01_control.tmp'...
Alert log snip from another node:
Wed May 30 16:46:17 2012
Control file backup creation failed.
Backup target file size found to be zero.
Errors in file /u01/app/oracle/diag/rdbms/demodw/demodw3/trace/demodw3_p000_31580.trc:
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Depending on whether that controlfile really needs to be included in the package will depend on the issue. In my environment, typically backed up controlfiles are created on ASM and then copied later to the local filesystem with dbms_file_transfer.
SQL> create directory RMAN_LOCAL_DIR as '/demodw1/rman/DEMODW';
SQL> create directory RMAN_ASM_DIR as '+RECO/DEMODW/rman';
SQL> alter database backup controlfile to ‘+RECO/DEMODW/rman/sqlplus_controlfile_backup’;
SQL> exec dbms_file_transfer.copy_file(‘RMAN_ASM_DIR’,'sqlplus_controlfile_backup’,'RMAN_LOCAL_DIR’,'sqlplus_controlfile_backup’);
PL/SQL procedure successfully completed.
SQL> !ls -l /demodw1/rman/DEMODW/sqlplus_controlfile_backup
-rw-r----- 1 oracle oinstall 177946624 May 30 16:51 sqlplus_controlfile_backup
If the controlfile really is needed in an incident package it can be copied to the ADR and added to the package before generating it with adrci’s ips add file package command.
Nothing too ground breaking, but I thought this was strange and another reason why ADR should be on shared storage for RAC environments.