Until recently, I didn’t think it mattered much. On a single instance database, who cares? But on RAC, I would briefly 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 184.108.40.206 regarding the backing up of the control file. Starting with that version, controlfile snapshots and backups must 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. 220.127.116.11 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. It was no big deal, except that 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
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 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.
This is nothing too ground breaking, but I thought it was strange and provided another reason why ADR should be on shared storage for RAC environments.
Interested in working with Andy? Schedule a tech call.