DBA Maestro - Helpful DBA tips

Below is a script to accomplish copying files from ASM to nonASM (on Linux).  Below example is for copying archivelogs from inside ASM to NAS mount. #!/bin/bash # # This script copies files from FRA on ASM to local disk # ORACLE_SID=+ASM1  ORACLE_HOME=/opt/grid/11.2.0.2  ##{Grid OH} ASMLS=/users/apps/oracle/asm_ls.txt ##{ASM files list} FRA=+FRA/CODREP/ARCHIVELOG/`date +%Y_%m_%d` ##{source location of files} LOCALBACKUPDIR=/opt/dbadmin/CODREP/ARCHIVE_COPY   ## {destination filesystem} LOG=/users/apps/oracle/asm_log.txt  ## {log file} # # Get the list of files # $ORACLE_HOME/bin/asmcmd   > $ASMLS <<EOF ls $FRA exit EOF # # Clean the list by removing "ASMCMD>" # sed -i 's/ASMCMD> //g' $ASMLS ##cat $ASMLS echo `date` > $LOG # # Copy files one by one # for FILENAME in `cat $ASMLS` do if [[ ! -f $LOCALBACKUPDIR/${FILENAME} ]] then $ORACLE_HOME/bin/asmcmd >> $LOG <<EOF ...

To move your database controlfile into ASM, do the following: 1. show parameter on control_files to see current location.   2. set control_files parameter in database to desired new location(s).     alter system set control_files='+DG01','+FRA01' scope=spfile; 3. Shutdown database and startup nomount.     shutdown immediate;     startup nomount; 4 . R estore controlfile from old control_file via RMAN.        RMAN> restore controlfile from '+DG01/esridevw_stby/controlfile/current.9361.748436947'; 5. Mount and open the instance.     alter database mount;     alter database open;

Individual datafile(s): 1. Take the datafile(s) in question offline. (example)        alter database datafile 6 offline; 2. Login to ASM via asmcmd.  Then copy the datafiles from current diskgroup to destination diskgroup. (example)      asmcmd> cp +DG01/svc_ctm.dbf  +DG01/prmrydb/datafile/svc_ctm.dbf asmcmd> copy datafile ' +DG01/svc_ctm.dbf' to '+DG01'; 3. Login to database via sqlplus and rename the datafiles. (example)     ALTER DATABASE RENAME FILE '+DG01/svc_ctm.dbf' to '+DG01/prmrydb/datafile/svc_ctm.dbf'; 4. Login to RMAN or sqlplus to recover the datafile(s) and place datafile(s) online. (example)      rman target /      RECOVER DATAFILE 6;      SQL 'alter database datafile 6 online'; ---------------------------------------------- Individual tablespace(s): You can complete the following steps all within RMAN: connect target / run{...